Fifo Method Calculation php

[Billz]

Please help me let me explain with this.....

purchase table

id      purchase_id                   product_id        qty          net_unit_cost             created_at

1                  1                                   1                   10                      10                        2022-10-10

2                  2                                  1                    20                      12                      2022-10-10

 

Sale table

sale_id      product_id                 qty             net_unit_price            created_at

1                      1                            11                      15                           2022-10-10

in this, if i sold '11' units then how can i subtract from the rows to get remaining units? i've to subtract '10' units from first row and '1' unit from second row. Please help how to calculate fifo in php

[Barand]

They are both the same date so why not take all 11 items from the one with 20 in stock? FIFO hardly applies in your example.

[Billz]

Please help me let me explain with this.....

purchase table

id      purchase_id                   product_id        qty          net_unit_cost             created_at

1                  1                                   1                   10                      10                        2022-10-1

2                  2                                  1                    20                      12                      2022-10-10

 

Sale table

sale_id      product_id                 qty             net_unit_price            created_at

1                      1                            11                      15                           2022-10-15

in this, if i sold '11' units then how can i subtract from the rows to get remaining units? i've to subtract '10' units from first row and '1' unit from second row. Please help how to calculate fifo in php

[Barand]

What have you tried so far?

[Billz]

i want to subtraction sum of all rows in php. can you write code?

[Barand]

Yes, but his is a "we'll help you with your code" forum, not a "we'll write it and do your job for you" forum.

[Barand]

Someone has wanted the net_unit_cost and net_unit_price stored in the records. If, as you said, you subtract the 10 and the 1 from the quantities in the purchase records, how will you know which sales were sold from each purchase batch. When the sales department wants a report showing the profit on each sale, how do you intend producing it?

The answer is to do the processing as part of the sales input process and add a "purchase_id" column to the sales. When you decide 10 are sold from purchase record #1, write a sales record with qty = 10 and prchase_id = 1. Then write another sales record with qty = 1 and purchase_id = 2. Now you know from which batch of purchases each sale was made. And no need to update the purchase records to reduce the quantities.

+---------------------+                +---------------------+
| purchase            |                | sale                |
+---------------------+                +---------------------+
| purchase_id         |-------+        | sale_id             |
| product_id          |       |        | product_id          |
| qty                 |       |        | qty                 |
| net_unit_cost       |       |        | net_unit_price      |
| created_at          |       +-------<| purchase_id         |
+---------------------+                | created_at          |
                                       +---------------------+

To get the available quantities for the next sale (from each purchase, what was sold?)

SELECT p.purchase_id
     , p.qty - coalesce(s.qty, 0) as qty
FROM purchase p
     LEFT JOIN (
        SELECT purchase_id
             , sum(qty) as qty
        FROM sale 
        GROUP BY purchase_id      
        ) s ON p.purchase_id = s.purchase_id
WHERE p.product_id = 1 AND p.qty - coalesce(s.qty, 0) > 0
ORDER BY created_at;

For today's sales (for each sale, what batch was it from?)

SELECT s.product_id
     , s.qty
     , p.net_unit_cost
     , s.net_unit_price
     , s.net_unit_price - p.net_unit_cost as net_unit_profit
     , s.qty * (s.net_unit_price - p.net_unit_cost) as profit
     , s.created_at as date_sold
FROM sale s 
     JOIN purchase p USING (purchase_id)
WHERE s.created_at = CURDATE()
ORDER BY product_id;

The process for determining how many from each purchase batch is simple maths.

foreach purchase record
    how many of the sales qty can I sell from this record?
    write sales record for that qty with purchase id
    reduce sales qty by that amount
end for each