0

I'm building a custom report on the basis of database.

I need to know how to get 'Grand Total' that is in 'sales/order' (in other words sales_flat_order) table by order items (from 'sales/order_item', sales_flat_order_item table).

I need a formula to get this value using the database and not using Magento methods.

I am building MySQL query

Am I right when I say that

base_grand_total =  (IFNULL(base_total_canceled,0)) +  (IFNULL(base_hidden_tax_amount,0) - IFNULL(base_hidden_tax_refunded,0)) + IFNULL(base_total_offline_refunded,0) + IFNULL(base_total_online_refunded,0) +  ( IFNULL(base_subtotal,0) - IFNULL(base_subtotal_canceled,0) - IFNULL(base_subtotal_refunded,0)) + (IFNULL(base_tax_amount,0) - IFNULL(base_tax_canceled,0) - IFNULL(base_tax_refunded,0)) + (IFNULL(base_shipping_amount,0) - IFNULL(base_shipping_canceled,0) - IFNULL(base_shipping_refunded,0)) + (IFNULL(base_discount_amount,0) - IFNULL(base_discount_canceled,0) - IFNULL(base_discount_refunded,0)) ?
  • Are you using MySQL query ? if so please post here – Anil Suthar May 6 '16 at 8:24
  • Thank you! I'm sorry. I had to immediately indicate this – Boris Ruvinsky May 6 '16 at 8:30
  • can you post that query here ? Basic query should look like: SELECT * FROM sales_flat_order as sfo INNER JOIN sales_flat_order_item as sfoi ON sfo.entity_id = sfoi.order_id WHERE sfoi.order_id = 42 – Anil Suthar May 6 '16 at 8:34
  • Thank you for your help! I can write sql query. I need to know how to calculate base_grand_total using Subtotal, Tax, Shipping and other.. – Boris Ruvinsky May 6 '16 at 8:48
1

You can pass the order_entity_id and get the grand total value

function get_order_details($order_entity_id)
{
$getsales = mysql_query("SELECT grand_total FROM `sales_flat_order` WHERE `entity_id`='$order_entity_id'");
if(mysql_num_rows($getsales) == 1)
{
return mysql_fetch_object($getsales);
}
elseif(mysql_num_rows($getsales) > 1)
{
return $getsales;
}
else
return FALSE;
}
  • Thank you! But I need calculate "grand total" by order items. I group products according to different parameters, so I need exactly the formula for calculating this value. I can use table sales_flat_order_item and some values (such as 'shipping...') from sales_flat_order – Boris Ruvinsky May 6 '16 at 8:19
  • A more specific question - how to calculate the 'grand total' on the base of the other fields of the table sales_flat_order ? – Boris Ruvinsky May 6 '16 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.