I want inventory management system in C# .NET windows form application and want to sell product on base of LEFO(last in first out) terms

Question

1.00/5 (3 votes)

See more:

Hi All Respected Developers!

i have 2 same product in stock table. 1st product quantity is 100 and 2nd is 120 now if a person buy 120 quantity i want to reduce 100 from 1st and other 20 form 2nd row of SQL table but i can't understand how its work.

What I have tried:

C#

<pre>            if (ItemNameTextBox.Text.Trim() != String.Empty)
            {
                DbSQLServer mydb = new DbSQLServer(AppSetting.ConnectionString());
                DataTable ProductTbl = mydb.GetDataList("usp_ProductSelectProdutStockDetails",
                new DbParameter { Parameter = "@ProductName", Value = ItemNameTextBox.Text });


                if (ProductTbl.Rows.Count > 0)
                {
                    DataRow row = ProductTbl.Rows[0];
                    this.stockid = Convert.ToInt32(row["StockID"]);
                    this.ProductId = Convert.ToInt32(row["ProductID"]);
                    CurrentStockTextBox.Text = row["ReminnigQuantity"].ToString();
                    SalesTaxTextBox.Text = row["SalesTax"].ToString();
                    UnitPriceTextBox.Text = row["Price"].ToString();
                    if (QuantityTextBox.Text != string.Empty && Convert.ToInt32(QuantityTextBox.Text) != 0)
                    {
                        this.QuantityfromDb = Convert.ToInt32(row["ReminnigQuantity"]) - Convert.ToInt32(QuantityTextBox.Text);
                    }

                }
                else
                {
                    CurrentStockTextBox.Text = 0.ToString();
                    SalesTaxTextBox.Text = 0.ToString();
                    UnitPriceTextBox.Text = 0.ToString();
                    JIMessageBox.ShowErrorMessage("Product out of Stock");
                }

this works fine but if a person buys 120 the 1st row of SQL is -20.
Note:

this is custom Ado.net framework for CURD opretions.

C#

DbSQLServer mydb = new DbSQLServer(AppSetting.ConnectionString());
                DataTable ProductTbl = mydb.GetDataList("usp_ProductSelectProdutStockDetails",
                new DbParameter { Parameter = "@ProductName", Value = ItemNameTextBox.Text });

Posted 16-Apr-21 3:37am

Fahim ullah

Updated 16-Apr-21 5:19am

v3

Add a Solution

2 solutions

Solution 1

Quote:
but i can't understand how its work.

There is no way to teach you Databases design and its management in the scope of the little textbox.
You can get started with a tutorial.

Quote:
i search a lot on internet but I can't find right solution

I fear that without technical background, you have to hire a professional or turn to commercial solution.
Usually, finding the right solution imply selecting solutions close to your need and trying them to see which one will fulfill the needs.

Posted 16-Apr-21 4:10am

Patrice T

Comments

Fahim ullah 17-Apr-21 10:26am

I know about sql queries but i cant understand how use it for this purpose.
i can loop,declare variables,write a subqueries etc

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2021-04-16T05:19:00

It is possible to do this using a loop - BUT just because you can do something does not mean that you should.

First, a working example. This code snippet will do what you want assuming that you assign id's to each row based on the order they are added to the database. You could just as easily use a datetime as long as you have something that implies order.

SQL

declare @prod table (id int identity(1,1), Product char(1), amount int);
insert into @prod values ('A', 100), ('A', 100);
declare @buy int = 120;
declare @work int;
declare @id int;

WHILE @buy > 0
BEGIN
	SELECT TOP 1 @id = id, @work = amount FROM @prod WHERE Product = 'A' AND amount > 0 ORDER BY id DESC
	IF @work <= @buy 
		UPDATE @prod SET amount = 0 WHERE id = @id
	ELSE
		UPDATE @prod SET amount = amount - @buy WHERE id = @id

	SET @buy = @buy - @work
END
select * from @prod

SQL is a set-based language so you really shouldn't be doing this loop SQL-side but in your UI perhaps.

The principle here is
- Make sure you access the rows in the table in the "correct" order - I have used ORDER BY id in the example, but you could use a datetime column to record when the stock arrived. The DESC ensures LIFO of which more below
- Remove as much as you need from the first row that has a positive amount
- keep removing stock from those rows (in that order) until you have used up all that you wanted to extract - see variable @buy
- "remove" means adjust the stock level (amount in my example) by the maximum possible - hence if the first row encountered does not fulfill the amount then set it to 0

Now to a couple of other points.
- Inventory Management usually handles stock in FIFO order (First In, First Out) so that stock does not get out of date, so I question your logic.
- Having two rows per product on a table only makes sense if you are also capturing a batch number or similar, and you are using this query to "pick" boxes from the inventory in the correct order. If you are just concerned with the amount of each product in your "warehouse" then you would only have ONE row per product and the amount is adjusted accordingly with each sale or delivery.