How to Manage FIFO rule for reducing stock in a Point of Sale-CodePudding

How to manage FIFO rule for reducing the stock after selling of their items.

The requirement is that when an item is sold in a large quantity let say 10000 units then system should reduce the stock according FIFO rule.

Now i explain in detail.

In below example when 10000 units of a same item are sold and system has 12000 units available in stock but these 12000 units were purchased in four different creditors at different stock i.e.

in 1st purchase 3000 units
in 2nd purchase 3000 units
in 3rd purchase 3000 units
in 4th purchase 3000 units

now when 10000 units are going to be sold i want that system should reduce the stock of these 10000 units of the item as following

3000 units (from 1st purchase) Current Stock will be 0 units
3000 units (from 2nd purchase) Current Stock will be 0 units
3000 units (from 3rd purchase) Current Stock will be 0 units
1000 units (from 4nd purchase) Current Stock will be 2000 units

how can I manage it?

I am using vb.net and mySQL 5.7 database server.

CodePudding user response：

Use ordered UPDATE and use user-defined variable.

Demo:

UPDATE stock
SET sell = CASE WHEN @sell < purchase - sell
                THEN sell   @sell   (@sell := 0)
                ELSE 0 * (@sell := @sell - purchase   sell)   purchase
                END
ORDER BY stock_id;

For parametrized query use

UPDATE stock
SET sell = CASE WHEN @sell < purchase - sell
                THEN sell   @sell   (@sell := 0)
                ELSE 0 * (@sell := @sell - purchase   sell)   purchase
                END
WHERE (@sell := ?)  --   <<- the amount to be sold
ORDER BY stock_id;

fiddle