Order History Export

[rhenrie]

This year I moved from v134 to v156. Now that it is tax time I am looking for my old "Order History Export" under "Reports". Anybody know if that functionality still exists in a plugin? I can't find it..

I tried installing Order Exporter but I still can't see tax class, tax rate, totals excluding taxes ,tax paid, and total for each order.

Any help would be appreciated.

[balihr]

How about Sales Report?

[rhenrie]

Thanks balihr, i will give Sales Report a try!

[rhenrie]

That does work! I almost gave up on it because I couldn't get an order date. To get an "order date" to show up in the export you have to select "Daily" time frame and send it to CVS (not screen). Note that I also had to fix the extra_functions/sales_report.php file as described here: https://www.zen-cart.com/showthread....ort-2-0/page79

Thanks again balihr!

[lat9]

That does work! I almost gave up on it because I couldn't get an order date. To get an "order date" to show up in the export you have to select "Daily" time frame and send it to CVS (not screen). Note that I also had to fix the extra_functions/sales_report.php file as described here: https://www.zen-cart.com/showthread....ort-2-0/page79

Thanks again balihr!

... and thanks to you @rhenrie for the reminder! I've just submitted v3.3.3 of Sales Report for review which corrects that (and other) issues.

[rhenrie]

Hmm. I guess I also still need "payment_method" and "billing_state" to correctly calculate my income and sales tax. I tried using phpMyAdmin but the "orders" table needs to be combined with the orders_total table to get the dollar amounts for the orders (for some reason some of the orders have dollar amounts and some don't when you just look at the orders table.

I think I found the original plug-in I used with 1.3.8: https://www.zen-cart.com/downloads.php?do=file&id=77

As you can see it only worked with 1.3.8.

Any other ideas on how to get an "orders" historical dump?

[lat9]

Hmm. I guess I also still need "payment_method" and "billing_state" to correctly calculate my income and sales tax. I tried using phpMyAdmin but the "orders" table needs to be combined with the orders_total table to get the dollar amounts for the orders (for some reason some of the orders have dollar amounts and some don't when you just look at the orders table.

I think I found the original plug-in I used with 1.3.8: https://www.zen-cart.com/downloads.php?do=file&id=77

As you can see it only worked with 1.3.8.

Any other ideas on how to get an "orders" historical dump?

If you identify which fields you need for your report, I'll post back with the SQL that you can cut/paste into phpMyAdmin's SQL tab.

[rhenrie]

Thank you lat9. I hate to make you do extra work. The complication I have is there are multiple rows from the orders_total table that I want for each row in the orders table.


Here are the columns I need for your reference (i.e., these are the Column Headings):
"Order", "Date", "Payment Method", "Customer Name", "Tax Class", "Tax Rate", "Subtotal", "Tax Paid", "Total"


I use these abbreviations below (which I see being used in the .php files). I would doubt that I can really use them in the SQL:
o. = orders table
op. = orders_products table
ot. = orders_total table


Here are the fields I would use to make up those columns:

o.orders_id, (note: everything below (i.e., everything on one row) should be tied to this orders_id)
o.date_purchased,
o.payment_method,
o.customers_name,
ot.title (where o.order_id = ot.orders_id AND ot.class = "ot_tax") (or blank field if the row doesn't exist),
op.products_tax (where o.order_id = op.orders_id),
ot.value (where o.order_id = ot.orders_id AND ot.class = "ot_subtotal") (or blank field if the row doesn't exist),
ot.value (where o.order_id = ot.orders_id AND ot.class = "ot_tax") (or blank field if the row doesn't exist),
ot.value (where o.order_id = ot.orders_id AND ot.class = "ot_total") (or blank field if the row doesn't exist)

[lat9]

What do you expect as results if there were multiple products in an order and there were also multiple tax rates?

[lat9]

Here's that query. Note that multiple records will be returned for a single order if multiple tax-rates are present.

SELECT DISTINCT o.orders_id, o.date_purchased, o.payment_method, o.customers_name, ot_tax.`title`, op.products_tax, ot_sub.`value`, ot_tax.`value`, ot_total.`value`
  FROM orders AS o
    LEFT JOIN orders_products AS op
        ON op.orders_id = o.orders_id
    LEFT JOIN orders_total AS ot_tax
        ON ot_tax.orders_id = o.orders_id
       AND ot_tax.`class` = 'ot_tax'
    LEFT JOIN orders_total AS ot_sub
        ON ot_sub.orders_id = o.orders_id
       AND ot_sub.`class` = 'ot_subtotal'
    LEFT JOIN orders_total AS ot_total
        ON ot_total.orders_id = o.orders_id
       AND ot_total.`class` = 'ot_total'
 WHERE o.date_purchased BETWEEN '2019-01-01' AND '2019-12-31'
 ORDER BY o.date_purchased ASC;