Page 1 of 2 12 LastLast
Results 1 to 10 of 11
  1. #1
    Join Date
    Dec 2016
    Location
    Utah
    Posts
    13
    Plugin Contributions
    0

    Default Order History Export

    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.

  2. #2
    Join Date
    Oct 2008
    Location
    Croatia
    Posts
    1,158
    Plugin Contributions
    11

    Default Re: Order History Export

    How about Sales Report?
    Zen Cart Point of Sale? Sure: ZX POS
    My site - Pro ZC Help | My portfolio | My plugins

  3. #3
    Join Date
    Dec 2016
    Location
    Utah
    Posts
    13
    Plugin Contributions
    0

    Default Re: Order History Export

    Thanks balihr, i will give Sales Report a try!

  4. #4
    Join Date
    Dec 2016
    Location
    Utah
    Posts
    13
    Plugin Contributions
    0

    Default Re: Order History Export

    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!

  5. #5
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,299
    Plugin Contributions
    72

    Default Re: Order History Export

    Quote Originally Posted by rhenrie View Post
    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.

  6. #6
    Join Date
    Dec 2016
    Location
    Utah
    Posts
    13
    Plugin Contributions
    0

    Default Re: Order History Export

    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?

  7. #7
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,299
    Plugin Contributions
    72

    Default Re: Order History Export

    Quote Originally Posted by rhenrie View Post
    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.

  8. #8
    Join Date
    Dec 2016
    Location
    Utah
    Posts
    13
    Plugin Contributions
    0

    Default Re: Order History Export

    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)

  9. #9
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,299
    Plugin Contributions
    72

    Default Re: Order History Export

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

  10. #10
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,299
    Plugin Contributions
    72

    Default Re: Order History Export

    Here's that query. Note that multiple records will be returned for a single order if multiple tax-rates are present.
    Code:
    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;

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. v1.2.x Excel Export of Customer Contact Details and Customer Order History?
    By sbokkie12 in forum General Questions
    Replies: 3
    Last Post: 1 May 2013, 12:43 AM
  2. v138a Quick Re-order aka order_history.php history beyond last order
    By beyerg in forum Customization from the Admin
    Replies: 1
    Last Post: 24 Jan 2012, 11:14 PM
  3. My Order History and Order History Details - how do I get order comments in both?
    By trisha1581 in forum Templates, Stylesheets, Page Layout
    Replies: 0
    Last Post: 29 Nov 2007, 06:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR