1. bnrbranding
  2. RO CSVI
  3. Monday, 03 October 2016
  4.  Subscribe via email
I am trying to create an order export for Hikashop and I keep getting SQL errors every time I create the template.

This is the current error I am getting:

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 ')' 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 ()


It pretty much doesn't matter what fields I select, I continue to get a SQL error every time. What am I doing wrong?

Joomla Version: 3.6.2
CSVI Version: CSVI Pro 6.6.4
Hikashop Version: HikaShop Business 2.6.4
Accepted Answer Pending Moderation
Hello,
Can you try to load the attached patch file with CSVI maintenance menu and see if the SQL error is solved?


patch_hikashop_order_export_2016-10-04.zip
Attachments (1)
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
Accepted Answer Pending Moderation
Ok, that got the report working so we don't get a SQL error, thank you!

Now I have two other issues that I can't figure out -

1. I want to limit the report to orders from a single category. When I go to the Template > Options > Category, there are only 2 categories listed when we have over 20 categories in Hikashop (see attached images). I tried going to Maintenance > Tasks > Category [Hikashop] and resetting the task but that didn't seem to do anything. How do I get all of the categories to show up here?

2. In the report, we want the order number along with the shipping information, which we are getting. However, each order number is repeated once for each product on that order. If the order contains 5 different products, the order shows up in the report 5 times. We only need each order to show up once on the report. Is this possible?
Attachments (2)
  1. more than a month ago
  2. RO CSVI
  3. # 2
Accepted Answer Pending Moderation
Hello,

Ok, that got the report working so we don't get a SQL error, thank you!

Thank you for confirming.

1. I want to limit the report to orders from a single category. When I go to the Template > Options > Category, there are only 2 categories listed when we have over 20 categories in Hikashop (see attached images). I tried going to Maintenance > Tasks > Category [Hikashop] and resetting the task but that didn't seem to do anything. How do I get all of the categories to show up here?

Since you are doing an order export, categories of the products which are in HikaShop orders table are only listed in this option. There is no point in listing all the categories when we know there are no products for categories in orders table.

2. In the report, we want the order number along with the shipping information, which we are getting. However, each order number is repeated once for each product on that order. If the order contains 5 different products, the order shows up in the report 5 times. We only need each order to show up once on the report. Is this possible?

Check the document group by and sort by fields which will help you to avoid duplicates in your report.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 3
Accepted Answer Pending Moderation
Since you are doing an order export, categories of the products which are in HikaShop orders table are only listed in this option. There is no point in listing all the categories when we know there are no products for categories in orders table.
This makes sense, however, nearly all of the orders for the date range selected have products from the category I am trying to run the report for, which does not appear on the list. I checked the orders that appear in the report without any category filtering and the products from that category are definitely there. In fact, there are no products from either of the 2 categories that I can select. I tried selecting each category and running the report again, each time it resulted in the same orders being exported whether I selected a category or not. I turned on logging and the SQL query used did not seem to include a WHERE clause for the category I selected. SQL query from log is below.

Check the document group by and sort by fields which will help you to avoid duplicates in your report.
This worked perfectly, I was able to group by invoice number and I no longer have duplicates.

SQL 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 FROM_UNIXTIME(`o`.`order_created`, '%Y-%m-%d')
>= DATE_SUB(CURDATE(), INTERVAL 9 DAY) AND FROM_UNIXTIME(`o`.`order_created`, '%Y-%m-%d')
<= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
GROUP BY `order_number`
  1. more than a month ago
  2. RO CSVI
  3. # 4
Accepted Answer Pending Moderation
Hello,
This makes sense, however, nearly all of the orders for the date range selected have products from the category I am trying to run the report for, which does not appear on the list. I checked the orders that appear in the report without any category filtering and the products from that category are definitely there. In fact, there are no products from either of the 2 categories that I can select. I tried selecting each category and running the report again, each time it resulted in the same orders being exported whether I selected a category or not. I turned on logging and the SQL query used did not seem to include a WHERE clause for the category I selected. SQL query from log is below.

It will be very difficult for me to say on this issue unless i get to see your data. Can you send me an email of your hikashop tables mysql dump to tharuna@csvimproved.com? You can even send it as a private message.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 5
Accepted Answer Pending Moderation
I have sent you a dump of the Hikashop tables as well as a copy of the export log so you can check it out.

Thanks!
  1. more than a month ago
  2. RO CSVI
  3. # 6
Accepted Answer Pending Moderation
Hello.
Thank you for sending the data and that really helped.

This makes sense, however, nearly all of the orders for the date range selected have products from the category I am trying to run the report for, which does not appear on the list. I checked the orders that appear in the report without any category filtering and the products from that category are definitely there. In fact, there are no products from either of the 2 categories that I can select.

CSVI shows categories by linking with hikashop_order_product table and also from hikashop_product_category table. From your data i can see that there are products which are in hikashop_order_product but their category is not in hikashop_product_category table. You can check on product_ids 24 and 25 for examples. There are only 2 categories which are common for both of these tables and so you see them in your category options.

I tried selecting each category and running the report again, each time it resulted in the same orders being exported whether I selected a category or not. I turned on logging and the SQL query used did not seem to include a WHERE clause for the category I selected.

Yes, i could see an issue here, load the attached patch file, edit and save your export template once after loading the patch. That should solve the issue.


patch_HS_order_export_2016-10-05.zip
Attachments (1)
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 7
Accepted Answer Pending Moderation
CSVI shows categories by linking with hikashop_order_product table and also from hikashop_product_category table. From your data i can see that there are products which are in hikashop_order_product but their category is not in hikashop_product_category table. You can check on product_ids 24 and 25 for examples. There are only 2 categories which are common for both of these tables and so you see them in your category options.
Ok, I think I figured out what the issue is here. Almost all of the products in our store have variants (mostly size and color) so the products that are in the orders are not going to show up in the hikashop_product_category because variants are stored in another table. Category is not one of the fields that can be edited on a variant because it's always going to be in the category of the parent product. This causes a pretty big issue with any order reporting since I would imagine many stores would have products with variations. Is this something that you can take into account with the plugin?



Yes, i could see an issue here, load the attached patch file, edit and save your export template once after loading the patch. That should solve the issue.
Perfect, the patch fixed the category not being part of the SQL statement.
  1. more than a month ago
  2. RO CSVI
  3. # 8
Accepted Answer Pending Moderation
Hello,
I am looking into this issue with product variants categories, will keep you posted.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 9
Accepted Answer Pending Moderation
Hello,
The patch for showing products and its variants categories on HikaShop order export is ready. Please load the attached patch file using CSVI maintenance menu and see if the issue is solved.


patch_HS_order_categories_2016_10_10.zip
Attachments (1)
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 10
Accepted Answer Pending Moderation
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!
  1. more than a month ago
  2. RO CSVI
  3. # 11
Accepted Answer Pending Moderation
Hello,
Thank you for posting your feedback. There are few more issues which are fixed along with the one you have pointed and a new patch file is created and attached in this post. Can you loading this patch file and tell me how it goes?



patch_HS_order_categories_2016_10_11.zip
Attachments (1)
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 12
Accepted Answer Pending Moderation
That seems to have done it! Thank you for working so hard on this.
  1. more than a month ago
  2. RO CSVI
  3. # 13
Accepted Answer Pending Moderation
Glad to know that your issues are all solved.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 14
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!