Calculate the exact margin from sales orders and purchase orders

Question

I am trying to generate a report which calculates the margin from the below database. The problem is that the cost (existing in purchase_order_products table) of the product may change.

The cost of product with id 4022 on 2017-06-08 is 1110, however its cost is 1094 on 2017-07-25. This is confusing. I am unable to get the exact cost for each product sold.

I wrote a PHP algorithm which loops through all orders and purchase orders and used the oldest cost to newest cost. but the algorithm has a very high time complexity. Can this be done just using mysql query?

Please check below scenario:

Company created a purchase order for product X: quantity 3, cost 10. on day 1

Customers bought 2 product X sell price: 12 on day 1 (still have 1 item in inventory with cost 10)

Company created a purchase order for product X : quantity 4, cost 9. on day 2

Customers bought 3 product X sell price: 12 on day 2

Customers bought 2 product X sell price: 12 on day 3

Company created a purchase order for product X : quantity 2, cost 11. on day 3

Customers bought 2 product X sell price: 12 on day 3

The report:

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

day 2: sold 3 product X for 12 , 1 item has a cost of 10, 2 items have a cost of 9 ,

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

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

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

Therefor the profit of newly sold products is calculated using the their corresponding cost. Hope you got my point.

Databse Structure:

4 Products From Database

Products Purchase orders for the above products

Sold Products

Dump File Attached here

Answer 1

Why don't you just take it easy and add a profit column to the orders table that is calculated in real time when a customer buys a product.This way you can calculate your marging solely from the sales orders given the fact that this is actualy already calculated somehow in order to generate the selling price. Of course this will work only for future sales but you can use your existing code with little modification to populate the profit column for old records and you will run this code only one time for the old transactions before the update.
To elaborate more:
Alter the table "sales_order" adding "profit" column . This way you can calculate the sum using the other related columns (total_paid, total_refund, total_due, grand_total) because you may want have more control over the report by including those monetary fields as needed in your calculation for example generating a report using total_payed only excluding tota_due or encluding it for different type of reports, in other words you can generate multiple reports types only from this table without overwhelming the DB system by adding this one column only.
Edit:
You can also add a cost column to this table for fast retrieving purpose and minimize joins and queries to other tables and if you want to take it a step further you can add a dedicated table for reports and it will be very helpful for example to generate a missing report from last month and checking old order status.

Answer 2

Some disclaimers:

• this is an attempt to assist with the logic, so it's rough code(open to SQL injection attacks, so don't copy and paste this)
• I can't test this query so there's probably mistakes in it, just trying to get you on the right track (and/or will make follow up edits)
• This won't work if you need profit per order, only for profit per product. You could probably get a date range with a BETWEEN clause if needed.

That being said, 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 thing here is using the HAVING clause after GROUP BY to determine where you cut off finding the sum of the purchase costs. You can sum them all as long as they're within that range, and you get the right dates ordering by created_at.

Again, I can't test this, and I wouldn't recommend using this code as is, just hoping this helps from a "here's a general idea of how to make this happen".

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