Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Posts
    27
    Plugin Contributions
    0

    Default Products Purchased report no longer works in v156a

    Just upgraded to v156a. The standard Products Purchased report doesn't work any more. I have 316 products, the report says there are 6480. There are 324 pages of reports but all except the first 15 are blank. When I compared the number of each product sold to the report in v155, even the numbers are off.

    Is this a known bug in 156a? Is there a SQL join issue?

    TO install the database for 156a, I copied over the 155 database and upgraded via zc_install per instructions. Everything else seems to work fine.

  2. #2
    Join Date
    Dec 2009
    Location
    Amersfoort, The Netherlands
    Posts
    2,846
    Plugin Contributions
    25

    Default Re: Products Purchased report no longer works in v156a

    The number of results can be different than before, as the new query uses other data than the old query. I can for now not explain the empty page. I need to investigate that.
    You can revert to the old query, by opening admin/stats_products_purchased.php in a text editor (like notepad++), and look for this code block starting at line 171 in Zen Cart 1.5.6:
    PHP Code:
    // The following OLD query only considers the "products_ordered" value from the products table.
    // Thus this older query is somewhat deprecated
              
    $products_query_raw1 "SELECT p.products_id, SUM(p.products_ordered) AS products_ordered, pd.products_name
                                      FROM " 
    TABLE_PRODUCTS " p,
                                           " 
    TABLE_PRODUCTS_DESCRIPTION " pd
                                      WHERE pd.products_id = p.products_id
                                      AND pd.language_id = " 
    . (int)$_SESSION['languages_id'] . "
                                      AND p.products_ordered > 0
                                      GROUP BY p.products_id, pd.products_name
                                      ORDER BY p.products_ordered DESC, pd.products_name"
    ;

    // The new query uses real order info from the orders_products table, and is theoretically more accurate.
    // To use this newer query, remove the "1" from the following line ($products_query_raw1 becomes $products_query_raw )
              
    $products_query_raw "SELECT SUM(op.products_quantity) AS products_ordered, pd.products_name, op.products_id
                                     FROM " 
    TABLE_ORDERS_PRODUCTS " op
                                     LEFT JOIN " 
    TABLE_PRODUCTS_DESCRIPTION " pd ON (pd.products_id = op.products_id )
                                     WHERE pd.language_id = " 
    . (int)$_SESSION['languages_id'] . "
                                     GROUP BY op.products_id, pd.products_name
                                     ORDER BY products_ordered DESC, products_name"

    Here yo change products_query_raw1 to products_query_raw1, on line 173, and products_query_raw1 to products_query_raw1 on line 184

  3. #3
    Join Date
    Dec 2009
    Location
    Amersfoort, The Netherlands
    Posts
    2,846
    Plugin Contributions
    25

    Default Re: Products Purchased report no longer works in v156a

    I may have found the cause of the empty pages. But i have a question first. Do the empty pages contain sold products numbers, but no names?

  4. #4
    Join Date
    Dec 2009
    Location
    Amersfoort, The Netherlands
    Posts
    2,846
    Plugin Contributions
    25

    Default Re: Products Purchased report no longer works in v156a

    I have made a PR on Github with the possible solution: https://github.com/zencart/zencart/pull/2229

  5. #5
    Join Date
    Jan 2012
    Posts
    27
    Plugin Contributions
    0

    Default Re: Products Purchased report no longer works in v156a

    Thanks, Design75!

    The newer query may be more accurate if it excludes deleted orders. I will have to look, although I am a SQL lightweight.

    The blank pages are indeed blank--only the page/column headings.
    Name:  Annotation 2019-03-26 085440.jpg
Views: 301
Size:  14.6 KB

  6. #6
    Join Date
    Jul 2012
    Posts
    16,719
    Plugin Contributions
    17

    Default Re: Products Purchased report no longer works in v156a

    The new query actually ignores the product name as it is/has been assigned. It reports the product name as it was named when it was purchased.

    You mentioned deleted orders. Would it seem like the number of deleted orders would account for the additional number of pages? What about deleted product? Or in some sort of update to the store has the product been rebuilt instead of importing the entire database and running zc_install?

    Basically the question is what has happened with the database to present this issue?
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

  7. #7
    Join Date
    Jan 2012
    Posts
    27
    Plugin Contributions
    0

    Default Re: Products Purchased report no longer works in v156a

    Quote Originally Posted by Design75 View Post
    I have made a PR on Github with the possible solution: https://github.com/zencart/zencart/pull/2229
    I think you may have solved it. Ido ocassionally change product description even though the product_id stays the same.

    I reverted to the earlier query in the admin/stats_products_purchased.php file. I'll wait new file is released in the next update to replace the file completely. Thanks!

  8. #8
    Join Date
    Dec 2009
    Location
    Amersfoort, The Netherlands
    Posts
    2,846
    Plugin Contributions
    25

    Default Re: Products Purchased report no longer works in v156a

    Thanks for reporting back

  9. #9
    Join Date
    Jan 2012
    Posts
    27
    Plugin Contributions
    0

    Default Re: Products Purchased report no longer works in v156a

    I just upgraded to 1.5.6c. Your post prompted me to check. The problem remains.

    The report says there are something like 6848 products (I have a little over 300); pages in the report are 343 although everything after 19 are blank. The last product on the list shows 1 sold but an actual count (by clicking on the product ID in the left column) shows 42 were bought.

    To be honest, I hadn't checked on the issue (I thought it had been fixed in 1.5.6b) because the contribution Sales Report 3.2.2 meets my needs.

  10. #10
    Join Date
    Jan 2012
    Posts
    27
    Plugin Contributions
    0

    Default Re: Products Purchased report no longer works in v156a

    I think I may have found a clue to the error in the report. The orders_products table contains 6848 rows.

    SO when the report says (bottom left) there are 6848 products, it is reporting 6848 instances of a product being ordered--each row in the orders_products table.

 

 

Similar Threads

  1. Products Purchased report in admin qestion
    By marcopolo in forum General Questions
    Replies: 1
    Last Post: 6 Dec 2015, 01:27 AM
  2. v150 Products Purchased Report
    By swguy in forum Bug Reports
    Replies: 0
    Last Post: 4 Apr 2012, 12:42 PM
  3. Products Purchased Report, breakdown by Attributes
    By sapsaj in forum All Other Contributions/Addons
    Replies: 1
    Last Post: 14 Oct 2009, 09:31 PM
  4. Customizing Products Purchased Report
    By balthajd in forum Managing Customers and Orders
    Replies: 0
    Last Post: 18 Sep 2009, 04:56 PM
  5. Customize Products Purchased Reports & Order Report
    By hologram33 in forum Managing Customers and Orders
    Replies: 0
    Last Post: 25 Sep 2008, 06:16 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