Show Profit Margin on Admin Orders

[Eutanasio]

Hi all,

 

I'd like to be able to see in the orders sum up of the admin, under this:

<tr id="total_order">
  <td class="text-right"><strong>{l s='Total'}</strong></td>
  <td class="amount text-right">
  <strong>{displayPrice price=$order_total_price currency=$currency->id}</strong>
  </td>
  <td class="partial_refund_fields current-edit" style="display:none;"></td>
</tr>

I'd lie to have that total amount subtracted by the Total Wholesale Price of the order to know the profit margins I have. I have seen this code in the AdminStats.tpl but I don't know how to adapt it:
 

	public static function getProductAverageGrossMargin()
	{
		$value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
		SELECT AVG((IFNULL(product_attribute_shop.price, product_shop.price) - IFNULL(product_attribute_shop.wholesale_price, product_shop.wholesale_price)) / IFNULL(product_attribute_shop.price, product_shop.price))
		FROM `'._DB_PREFIX_.'product` p
		'.Shop::addSqlAssociation('product', 'p').'
		LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON p.id_product = pa.id_product
		'.Shop::addSqlAssociation('product_attribute', 'pa'));
		return round(100 * $value, 2).'%';
	}

Does anybody know how to do it?? I use PS 1.6. Thank you!

Edited August 27, 2014 by omar2886 (see edit history)

[Eutanasio]

If this functionality (highly useful) interests you, please VOTE for it so it can be implemented in future releases:
http://feedback.prestashop.com/forums/124931-general?query=profit%20margin

[Kaper]

I would be happy if someone can share solution for 1.6. 

It should be easy i think. In databse ps_order_details is supplier price for each product. 

 

Need querry to SUM supplier price of eaxh product MINUS total paid = margin/profit.

 

Is there anybody who can made this? I will pay for a beer for sure

[AddWeb Solution]

On 8/28/2014 at 3:52 AM, Eutanasio said:

  <strong>{displayPrice price=$order_total_price currency=$currency->id}</strong>

Hi,

You need to calculate $order_wholesale_total by summing up the wholesale prices of the products in the order. You can do this by modifying your code that handles order details.

{displayPrice price=($order_total_price - $order_wholesale_total) currency=$currency->id}

Thanks!

[AddWeb Solution]

13 hours ago, Kaper said:

I would be happy if someone can share solution for 1.6. 

It should be easy i think. In databse ps_order_details is supplier price for each product. 

 

Need querry to SUM supplier price of eaxh product MINUS total paid = margin/profit.

 

Is there anybody who can made this? I will pay for a beer for sure

Hi,

You can calculate the profit margin by subtracting the total supplier price from the total paid for an order

SELECT
    o.id_order,
    SUM(od.supplier_price * od.product_quantity) AS total_supplier_price,
    o.total_paid
FROM
    ps_orders o
JOIN
    ps_order_detail od ON o.id_order = od.id_order
GROUP BY
    o.id_order

 

In this query, od.supplier_price is the supplier price for each product in the ps_order_details table, and od.product_quantity is the quantity of each product. o.total_paid is the total paid for the order in the ps_orders table.

Subtract total_supplier_price from total_paid to get the profit margin for each order.

Let me know If this helps!

Thanks!

[Kaper]

Thanks, i think this should work, but i don't know where exactly to put this query. In ../classes/order/Order.php ? or OrderDetail.php ?

I doubt this is wrong: ( I paste this in order.php)

public static function getOrderMargin()
    {
        return (int)Db::getInstance()->getValue('
            SELECT
                o.id_order,
                SUM(od.supplier_price * od.product_quantity) AS total_supplier_price,
                o.total_paid
            FROM
                ps_orders o
            JOIN
                ps_order_detail od ON o.id_order = od.id_order
            GROUP BY
                o.id_order
        ');
    }

Then how to call it in ../adminfolder/themes/default/template/controllers/orders/helpers/view/view.tpl ?

{$order->getOrderMargin()}

I get "0".

Thanks :)

[AddWeb Solution]

14 hours ago, Kaper said:

Thanks, i think this should work, but i don't know where exactly to put this query. In ../classes/order/Order.php ? or OrderDetail.php ?

I doubt this is wrong: ( I paste this in order.php)

public static function getOrderMargin()
    {
        return (int)Db::getInstance()->getValue('
            SELECT
                o.id_order,
                SUM(od.supplier_price * od.product_quantity) AS total_supplier_price,
                o.total_paid
            FROM
                ps_orders o
            JOIN
                ps_order_detail od ON o.id_order = od.id_order
            GROUP BY
                o.id_order
        ');
    }

Then how to call it in ../adminfolder/themes/default/template/controllers/orders/helpers/view/view.tpl ?

{$order->getOrderMargin()}

I get "0".

Thanks

Hi,

Your Method definition should look like,

public static function getOrderMargin()
{
    $query = "
        SELECT
            o.id_order,
            SUM(od.supplier_price * od.product_quantity) AS total_supplier_price,
            o.total_paid
        FROM
            "._DB_PREFIX_."orders o
        JOIN
            "._DB_PREFIX_."order_detail od ON o.id_order = od.id_order
        GROUP BY
            o.id_order
    ";
    $result = Db::getInstance()->executeS($query);
    
    return $result;
}

In view.tpl file, You need to loop through the result of your query in the template to display the information

you can use something like this in view.tpl:

Assuming you're on the order detail page (/adminfolder/index.php?controller=AdminOrders&id_order=X),

{foreach from=$order->getOrderMargin() key=id_order item=orderMargin}
    ID Order: {$orderMargin.id_order}<br>
    Total Supplier Price: {$orderMargin.total_supplier_price}<br>
    Total Paid: {$orderMargin.total_paid}<br>
    Margin: {$orderMargin.total_paid - $orderMargin.total_supplier_price}<br>
{/foreach}

Take backup of the actual file and then make changes. Else use custom module to implement this.

Let me know If this helps!

Thanks!

[Kaper]

Is this method for 1.6, right? I have tried your code, as you mentioned, but without success. Now i am not sure, if i am doing smthing wrong, or there is just a small error and needs a little tweak. Thanks,

Function i pasted in order.php , and foreach into view.tpl

 

 

//edit:

 

I think there is an error in sql querry: od.supplier_price shoud lbe --> od.purchase_supplier_price

Then it shows result, but like this:

Edited August 11, 2023 by Kaper (see edit history)

[AddWeb Solution]

24 minutes ago, Kaper said:

Is this method for 1.6, right? I have tried your code, as you mentioned, but without success. Now i am not sure, if i am doing smthing wrong, or there is just a small error and needs a little tweak. Thanks,

Function i pasted in order.php , and foreach into view.tpl

 

 

 

May be we can create a custom module and implement the feature in custom module.

Enable the module from BO.

[Kaper]

See my edit upper. It works, but there is some mistake in foreach loop. How to show just margin for the opened / one order? Now showing loop for every order in the current order view.

Edited August 11, 2023 by Kaper (see edit history)

[AddWeb Solution]

1 minute ago, Kaper said:

See my edit upper. It works need, but there is some mistake in foreach loop. How to show just margin for the opened / one order?

That's great!

May be due to cache issue earlier it didnt display the result.

If you want to display the margin for the opened/selected order,

{foreach from=$order->getOrderMargin() item=orderMargin}
    {if $orderMargin.id_order == $order->id}
        <div class="panel">
            <h3>Order Margin Information</h3>
            <p>Margin: {$orderMargin.total_paid - $orderMargin.total_supplier_price}</p>
        </div>
    {/if}
{/foreach}

The {if} condition checks if the id_order from the array matches the id of the currently opened order ($order->id). If it's a match, it displays the margin calculation for that specific order.

This way, the margin information will be displayed only for the opened/selected order, and not for all orders in the loop.

Let me know If it works.

Thanks!

[Kaper]

Finally! It works, i just have to change right calculation method (with / or without taxes, shipping...) to show the real margin and it works!

Really appreciate your time to help me, can i send you some beer? Paypal for ex. ?

 

Here is the final view.tpl code to show this:

{foreach from=$order->getOrderMargin() item=orderMargin}
	{if $orderMargin.id_order == $order->id}
		<div class="panel">
		<h3>Order Margin Information</h3>
		<p>Margin: {displayPrice price=$orderMargin.total_paid / 1.2 - $orderMargin.total_supplier_price - $order_shipping_price / 1.2}</p>													            
		</div>
	{/if}
{/foreach}

 

Edited August 11, 2023 by Kaper (see edit history)

[AddWeb Solution]

14 minutes ago, Kaper said:

Really appreciate your time to help me, can i send you some beer? Paypal for ex. ?

 

I'm glad I could assist you!

The cup which you gave for my post is more than enough. I appreciate it.

Thanks again!