How to know total orders without tax

andreasuriani
Topic Author
Offline

9 years 3 months ago #270505 by andreasuriani

How to know total orders without tax was created by andreasuriani

-- HikaShop version -- : 3.1.0
-- Joomla version -- : latest

Hi guys,
i need to know trough SQL query how many orders i have in my db and calculate the total amount.

Looking inside db i discover that in the table _hikashop_order there is only the column named "order_full_price" that include all taxes. Is it right?
How can i know the total without taxes?

My query is:

Code:

SELECT SUM(order_full_price) FROM `#####_hikashop_order` WHERE order_status = "confirmed" 

Please Log in or Create an account to join the conversation.

nicolas
Away

9 years 3 months ago #270512 by nicolas

Replied by nicolas on topic How to know total orders without tax

Hi,

There is no such data readily available in the database.
You would have to load the order_tax_info data, json_decode it, sum the different tax_amounts and substract the sum on the order_full_price.
If you have only one tax_amount per order all the time, you could do it in MySQL with some regex if it's imperative that it would be done in MySQL only.

Please Log in or Create an account to join the conversation.

andreasuriani
Topic Author
Offline

9 years 3 months ago #270555 by andreasuriani

Replied by andreasuriani on topic How to know total orders without tax

Hi Nicolas,
any help for the code?

I don't know hot to start to do it...

Thanks

Andrea

Please Log in or Create an account to join the conversation.

nicolas
Away

9 years 3 months ago #270556 by nicolas

Replied by nicolas on topic How to know total orders without tax

Run the query:
SELECT * FROM `#__hikashop_order` WHERE order_status = "confirmed"
the you get an array of orders in PHP.
You loop on them like that:

Code:

$total without tax = 0;
foreach($orders as $order){
 $taxes = reset(json_decode($order->order_tax_info));
 $total without tax+= $order->order_full_price - $taxes->tax_amount;
}
echo $total without tax;

Of course that code is not tested. It's just an example of what it should look like. Please understand that this is outside the scope of our support.

Please Log in or Create an account to join the conversation.

andreasuriani
Topic Author
Offline

9 years 3 months ago - 9 years 3 months ago #270897 by andreasuriani

Replied by andreasuriani on topic How to know total orders without tax

HI , sorry to be late.

I've tested this code:

Code:

<?php  $db = JFactory::getDbo(); 
$result = $db->setQuery("SELECT * FROM `*****_hikashop_order` WHERE order_status = 'confirmed'")->loadObject(); 
$total_without_tax = 0;
foreach($orders as $order){
 $taxes = reset(json_decode($order->order_tax_info));
 $total_without_tax+= $order->order_full_price - $taxes->tax_amount;
}
echo $total_without_tax;


?>

The output is 0 but is not true....

Any help ?

Last edit: 9 years 3 months ago by andreasuriani.

Please Log in or Create an account to join the conversation.

nicolas
Away

9 years 3 months ago #270901 by nicolas

Replied by nicolas on topic How to know total orders without tax

Hi,

Please understand that we're not here to help you with PHP coding.
This is basic PHP...
The query results are stored in $result in your code, so you need to replace $orders by $result in your code.
Also, you're using loadObject which only returns one result instead of loadObjectList which will return all of them.

Please Log in or Create an account to join the conversation.

Time to create page: 0.160 seconds