Calculate the exact margin from orders and purchase orders

Question

I am trying to create a report that calculates the margin from the underlying database. The problem is that the cost (existing in the buy_order_products table) of the product may change.

The cost of the product with identifier 4022 as of 2017-06-08 is 1110, however its cost is 1094 as of 2017-07-25. This confuses. I can’t get the exact cost for every product I sell.

I wrote a PHP algorithm that processes all orders and purchase orders and uses the oldest value for the newest value. but the algorithm has a very high time complexity. Is it possible to do this only with a mysql query?

Please check below scenario:

The company created an order for the supply of product X: quantity 3, value 10. per day 1

Buyers bought 2 products X selling price: 12 per day 1 (there are still 1 item in inventory with a cost of 10)

The company created an order for the supply of product X: quantity 4, value 9. per day 2

Customers bought 3 products. X Sale price: 12 on day 2.

Buyers bought 2 products. X Sale price: 12 per day. 3

The company created an order for the supply of product X: quantity 2, value 11. per day 3

Buyers bought 2 products. X Sale price: 12 per day. 3

Report:

day 1: sold 2 products X for 12, cost 10, profit: 2 * (12 - 10)

Day 2: 3 products X sold for 12, 1 pc. costs 10, 2 pcs. costs 9,

profit: 1 * (12 - 10) + 2 * (12 - 9)

day 3: sold 2 products X for 12, cost 9, profit: 2 * (12 - 9)

sold 2 products X for 12, cost 11, profit: 2 * (12 - 11)

Thus, the profit from newly sold goods is calculated using their corresponding value. I hope you understand my point.

Databse Structure:

4 Products from the database

Products Purchase Orders for the Above Products

Products Sold

Dump file attached here

Answer 1

The price of an object for a given time is determined by the formula: "total stock price / total quantity in stock".

To get this, you have two queries:

• The first one who knows the amount of goods sold (total value and quantity):

SQL:

SELECT SUM(row_total) sale_total_cost, SUM(quantity_ordered) sale_total_number FROM sales_order_item soi JOIN sales_order so ON soi.sales_order_id=so.id WHERE so.purchase_date<'2017-06-07 15:03:30' AND soi.product_id=4160; 

• the second is to find out how many products you bought.

SQL:

 SELECT SUM(pop.cost * pop.quantity) purchase_total_price, SUM(pop.quantity) purchase_total_number FROM purchase_order_products pop JOIN purchase_order po ON pop.purchase_order_id=po.id WHERE po.created_at<'2017-06-07 15:03:30' AND pop.product_id=4160; 

• The price of the product 4160 at 2017-02-01 14:23:35 is equal to:

(purchase_total_price - sale_total_cost) / (purchase_total_number - sale_total_number)

The problem is that the table "sales_order" starts from 2017-02-01 14:23:35 , and the table "purchase_order" starts from 2017-06-07 08:55:48 . Thus, the result will be inconsistent if you can not track all your purchases from the very beginning.

---

EDIT:

If you can change the structure of the tables and are only interested in future sales.

Adding the quantity of goods sold in the table purchase_order_products

You must modify purchase_order_products to have consumption for each product:

ALTER TABLE `purchase_order_products` ADD COLUMN sold_items INT DEFAULT 0;

Data initialization

For it to work, you must make the sold_items column reflect your real stock.

You must initialize your table with the following UPDATE `purchase_order_products` SET sold_items=quantity; query UPDATE `purchase_order_products` SET sold_items=quantity;

and then manually update the table with the exact stock for each product (which means the quantity_ordered-sold_items should reflect your real stock.

This needs to be done only once.

adding a purchase price to the sales_order_item table

ALTER TABLE sales_order_item ADD total_purchase_price INT DEFAULT NULL

entering a new sales order

When entering a new sales order, you will need to get the oldest purchase order with the remaining items using the following command:

SELECT * FROM `purchase_order_products` WHERE quantity!=sold_items where product_id=4160 ORDER BY `purchase_order_id` LIMIT 1;

Then you must increase the value of sold_items and calculate the total purchase price (amount) to fill the column total_purchase_price.

margin calculation

The margin will be easily calculated with the difference between row_total and total_purchase_price in the sales_order_item table.

Answer 2

Why don't you just simplify and add a profit column to the order table, which is calculated in real time when a customer buys a product. Thus, you can calculate the margin solely from sales orders, given the fact that it is actually already calculated in some way to generate a selling price. Of course, this will only work for future sales, but you can use your existing code with a few changes to populate the profit column for old records, and you will run this code only once for old transactions before upgrading.
More details:
Modify the "sales_order" table by adding the "profit" column. Thus, you can calculate the amount using other related columns (total_paid, total_refund, total_due, grand_total) , because you may need more control over the report by including these money fields in your calculation as necessary, for example, by creating a report using total_payed , only excluding tota_due or enclosing it for different types of reports, in other words, you can create several types of reports only from this table, without suppressing the database system, adding only this one column.
Edit:
You can also add a cost column to this table to quickly retrieve the target and minimize joins and queries to other tables, and if you want to take another step, you can add a dedicated table for reports, and this will be very useful, for example, to generate a missing one report for the last month and checking the status of the old order.

Answer 3

Some failures:

• This is an attempt to help with the logic, therefore it is rude code (open for SQL injection, so do not copy or paste this)
• I cannot test this query so that there are probably errors in it, just trying to find you on the right path (and / or make subsequent changes)
• This will not work if you need profit per order, only for profit per product. You could probably get a date range with a BETWEEN clause if necessary.

Speaking, I think something like this should work for you:

  $productsIds = array('4022', '4023', '4160', '4548', '4601'); foreach($productIds as $pid){ $sql = "SELECT (soi.revenue - sum(pop.cost)) AS profit, sum(pop.cost) AS total_cost, sum(pop.quantity) AS total_purchased, soi.revenue, soi.total_sold FROM purchase_order_products pop JOIN (SELECT sum(price) AS revenue, sum(quantity_ordred) AS total_sold FROM sales_order_item WHERE product_id = ".$pid.") AS soi ON soi.product_id = pop.product_id WHERE pop.product_id = ".$pid." GROUP BY pop.product_id HAVING sum(pop.quantity) < soi.total_sold ORDER BY pop.created_at ASC;"; $conn->query($sql); //do what you want with results } 

The key point here is to use the HAVING clause after GROUP BY to determine where you stopped finding the amount of purchase costs. You can sum them up as long as they are within this range, and you will get the correct date order created by created_at.

Again, I cannot verify this, and I would not recommend using this code as is, just hoping that it will help from "here's a general idea of ​​how to do this."

If I had time to recreate your databases, I would, or if you provided sql dump files with sample data, I could try to get you a working example.

Answer 4

I appreciate that you use the FIFO method for inventory management. However, this does not mean that you need to use FIFO to calculate the fields. The article https://en.wikipedia.org/wiki/Inventory_valuation provides an overview of the parameters. (The rules in your country may exclude some options.)

I find the recurring FIFO margin calculation solution for a single sale to be complex. There are difficulties opening balances, returns, partial deliveries, partial shipments, out-of-order processing, inventory adjustments, damaged goods, etc.

These problems do not seem to be addressed by the database structures in your question.

Typically, these problems are solved by calculating the margin / profit of the period (day, month, etc.) by calculating the change in the value of the inventory for the period.

If you can use the average cost method, you can calculate the margin using pure SQL. I believe that other methods seem to require some iteration since there is no built-in order in SQL. (You could improve performance by creating a new table and retaining the previous period values.)

I would not worry too much about getting the whole solution out in SQL, as this does not seem to reduce the computational complexity of the problem. However, there may be speed advantages when performing most of the calculations in a database block, especially if the data set is large.

You may find this article interesting: Set-based Speed ​​Phreakery: FIFO SQL Inventory Stock Problem . (There are some smart people!)