I have developed a POS system with purchasing and selling. When purchasing i am saving cost price for each products as a GRN with other details and update purchase quantity as a total for each item in the stocks table. But i dont have an idea to calculate costs for these products according to FIFO or LIFO.
GRN TABLE
`id`
`department_id`
`grn_no`
`grn_date`
`supplier_id`
`reference`
`description`
GRN DETAILS TABLE
`id`
`grn_id`
`item_id`
`quantity`
`expiry_date`
`cost_price`
Selling price set to newest price always.
STOCKS TABLE
`id`
`item_id`
`department_id`
`price`
`wholesale_price`
`quantity`
When i am selling, i am reducing stock quantity from stocks table. But i dont have idea to calculate cost price according to LIFO or FIFO. If someone know it, please guide me through.
DATA SAMPLE
Jan 1 - Purchased item1 - 100 units at $10
Jan 10 - Purchased item1 - 100 Units at $11
Jan 20 - Purchased item1 - 100 Units at $9
Jan 30 - Purchased item1 - 100 Units at $12
EXPECTED RESULT
Costing Report for Jan 10
Total sales for item1 is 25 Units at XX cost according to FIFO
Total sales for item1 is 30 Units at YY cost according to FIFO
System has 150 Units from item1 as current stock and current cost is ZZ according to FIFO