Ok, we're getting closer! After applying the patch I can now see all of the product categories associated with the product variants that have been sold.
However, if I select only one category in the template I get the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY `order_number`' at line 22 SQL=SELECT `order_number`, `order_created`, `as`.`address_lastname` AS `shipping_address_lastname`, `as`.`address_firstname` AS `shipping_address_firstname`, `as`.`address_street` AS `shipping_address_street`, `as`.`address_street2` AS `shipping_address_street2`, `as`.`address_telephone` AS `shipping_address_telephone`, `as`.`address_city` AS `shipping_address_city`, `as`.`address_state` AS `shipping_address_state`, `as`.`address_post_code` AS `shipping_address_post_code`, `as`.`address_country` AS `shipping_address_country`, `user_email` FROM `#__hikashop_order` AS `o` LEFT JOIN `#__hikashop_order_product` AS `p` ON `p`.`order_id` = `o`.`order_id` LEFT JOIN `#__hikashop_user` AS `u` ON `u`.`user_id` = `o`.`order_user_id` LEFT JOIN `#__hikashop_address` AS `ab` ON `ab`.`address_id` = `o`.`order_billing_address_id` LEFT JOIN `#__hikashop_address` AS `as` ON `as`.`address_id` = `o`.`order_shipping_address_id` LEFT JOIN `#__hikashop_product` AS `prod` ON `prod`.`product_code` = `p`.`order_product_code` LEFT JOIN `#__users` AS `user` ON `user`.`id` = `u`.`user_cms_id` LEFT JOIN `#__hikashop_product_category` AS `pc` ON `pc`.`product_id` = `prod`.`product_id` LEFT JOIN `#__hikashop_category` AS `cat` ON `cat`.`category_id` = `pc`.`category_id` WHERE `pc`.`category_id` IN () GROUP BY `order_number`
The error is the same regardless of which category I select, as long as it is only one category.
If I select more than one category, I no longer get an SQL error, but the report always returns "No data has been found for the selections made" regardless of which categories I select. I removed all of the other options and only used the category filter, and the report is always empty. If I remove the category, the report works fine. Here is the log from running a report with 2 categories selected:
Details
Date 2016-10-10 15:24:11 UTC
Software Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT
Date Time Line Action Comment
Processed 93 lines
2016-10-10 15:24:11 0 [DEBUG] Clean up old logs. Found 25 logs and threshold is 25 logs
2016-10-10 15:24:11 0 [QUERY] SELECT csvi_log_id FROM j3lmc_csvi_logs ORDER BY csvi_log_id
2016-10-10 15:24:12 0 [DEBUG] Clean up old logs. Found 25 logs and threshold is 25 logs
2016-10-10 15:24:12 0 [QUERY] SELECT csvi_log_id FROM j3lmc_csvi_logs ORDER BY csvi_log_id
2016-10-10 15:24:12 0 [DEBUG] CSVI Pro Version: 6.6.4
2016-10-10 15:24:12 0 [DEBUG] Joomla! version: 3.6.2
2016-10-10 15:24:12 0 [DEBUG] PHP version: 5.6.22
2016-10-10 15:24:12 0 [DEBUG] =========================
2016-10-10 15:24:12 0 [DEBUG] action: export
2016-10-10 15:24:12 0 [DEBUG] component: com_hikashop
2016-10-10 15:24:12 0 [DEBUG] operation: order
2016-10-10 15:24:12 0 [DEBUG] override:
2016-10-10 15:24:12 0 [DEBUG] localpath: /home/lknprints/public_html
2016-10-10 15:24:12 0 [DEBUG] ftphost:
2016-10-10 15:24:12 0 [DEBUG] ftpport:
2016-10-10 15:24:12 0 [DEBUG] ftproot:
2016-10-10 15:24:12 0 [DEBUG] ftpfile:
2016-10-10 15:24:12 0 [DEBUG] export_email_subject:
2016-10-10 15:24:12 0 [DEBUG] export_email_body:
2016-10-10 15:24:12 0 [DEBUG] export_filename:
2016-10-10 15:24:12 0 [DEBUG] export_file: csv
2016-10-10 15:24:12 0 [DEBUG] field_delimiter: ,
2016-10-10 15:24:12 0 [DEBUG] text_enclosure:
2016-10-10 15:24:12 0 [DEBUG] include_column_headers: Yes
2016-10-10 15:24:12 0 [DEBUG] signature: No
2016-10-10 15:24:12 0 [DEBUG] publish_state: Yes
2016-10-10 15:24:12 0 [DEBUG] recordstart:
2016-10-10 15:24:12 0 [DEBUG] recordend:
2016-10-10 15:24:12 0 [DEBUG] export_date_format: m/d/Y
2016-10-10 15:24:12 0 [DEBUG] export_price_format_decimal: 2
2016-10-10 15:24:12 0 [DEBUG] export_price_format_decsep: .
2016-10-10 15:24:12 0 [DEBUG] export_price_format_thousep:
2016-10-10 15:24:12 0 [DEBUG] header:
2016-10-10 15:24:12 0 [DEBUG] body:
2016-10-10 15:24:12 0 [DEBUG] footer:
2016-10-10 15:24:12 0 [DEBUG] category_separator: /
2016-10-10 15:24:12 0 [DEBUG] splitorderline: No
2016-10-10 15:24:12 0 [DEBUG] ordernostart:
2016-10-10 15:24:12 0 [DEBUG] ordernoend:
2016-10-10 15:24:12 0 [DEBUG] orderlist:
2016-10-10 15:24:12 0 [DEBUG] orderdaterange:
2016-10-10 15:24:12 0 [DEBUG] orderdatestart:
2016-10-10 15:24:12 0 [DEBUG] orderdateend:
2016-10-10 15:24:12 0 [DEBUG] ordermdatestart:
2016-10-10 15:24:12 0 [DEBUG] ordermdateend:
2016-10-10 15:24:12 0 [DEBUG] 1:
2016-10-10 15:24:12 0 [DEBUG] 1: 41
2016-10-10 15:24:12 0 [DEBUG] orderpricestart:
2016-10-10 15:24:12 0 [DEBUG] orderpriceend:
2016-10-10 15:24:12 0 [DEBUG] groupbyfields: {"name":["order_number"]}
2016-10-10 15:24:12 0 [DEBUG] sortfields:
2016-10-10 15:24:12 0 [DEBUG] use_system_limits: No
2016-10-10 15:24:12 0 [DEBUG] max_execution_time:
2016-10-10 15:24:12 0 [DEBUG] memory_limit:
2016-10-10 15:24:12 0 [DEBUG] csvi_template_id: 5
2016-10-10 15:24:12 0 [DEBUG] tags:
2016-10-10 15:24:12 0 [DEBUG] =========================
2016-10-10 15:24:12 0 [DEBUG] Export field: Order Number
2016-10-10 15:24:12 0 [DEBUG] Export field: Order Date
2016-10-10 15:24:12 0 [DEBUG] Export field: Shipping Last Name
2016-10-10 15:24:12 0 [DEBUG] Export field: Shipping First Name
2016-10-10 15:24:12 0 [DEBUG] Export field: Shipping Address 1
2016-10-10 15:24:12 0 [DEBUG] Export field: Shipping Adress 2
2016-10-10 15:24:12 0 [DEBUG] Export field: Ship Phone
2016-10-10 15:24:12 0 [DEBUG] Export field: Shipping City
2016-10-10 15:24:12 0 [DEBUG] Export field: Ship State
2016-10-10 15:24:12 0 [DEBUG] Export field: Shipping Zip Code
2016-10-10 15:24:12 0 [DEBUG] Export field: Shipping Country
2016-10-10 15:24:12 0 [DEBUG] Export field: Ship Email
2016-10-10 15:24:12 0 [DEBUG] Export query
SELECT `order_number`,
`order_created`,
`as`.`address_lastname` AS `shipping_address_lastname`,
`as`.`address_firstname` AS `shipping_address_firstname`,
`as`.`address_street` AS `shipping_address_street`,
`as`.`address_street2` AS `shipping_address_street2`,
`as`.`address_telephone` AS `shipping_address_telephone`,
`as`.`address_city` AS `shipping_address_city`,
`as`.`address_state` AS `shipping_address_state`,
`as`.`address_post_code` AS `shipping_address_post_code`,
`as`.`address_country` AS `shipping_address_country`,
`user_email`
FROM `#__hikashop_order` AS `o`
LEFT JOIN `#__hikashop_order_product` AS `p` ON `p`.`order_id` = `o`.`order_id`
LEFT JOIN `#__hikashop_user` AS `u` ON `u`.`user_id` = `o`.`order_user_id`
LEFT JOIN `#__hikashop_address` AS `ab` ON `ab`.`address_id` = `o`.`order_billing_address_id`
LEFT JOIN `#__hikashop_address` AS `as` ON `as`.`address_id` = `o`.`order_shipping_address_id`
LEFT JOIN `#__hikashop_product` AS `prod` ON `prod`.`product_code` = `p`.`order_product_code`
LEFT JOIN `#__users` AS `user` ON `user`.`id` = `u`.`user_cms_id`
LEFT JOIN `#__hikashop_product_category` AS `pc` ON `pc`.`product_id` = `prod`.`product_id`
LEFT JOIN `#__hikashop_category` AS `cat` ON `cat`.`category_id` = `pc`.`category_id`
WHERE `pc`.`category_id` IN (41)
GROUP BY `order_number`
Let me know what you need from me or if you would like access to the site.
Thanks for all your help!