Export Shipping & Order Information

Hi I just downloaded the latest version of the "Export Shipping & Order Information" : export_shipping_information-ESIv1.5.0.
I am using the version for v1.5.7. I have no problem exporting for option : "1 Product per row" but when I select "1 Order per row" it throws out an error.
The message is :

"WARNING: An Error occurred, please refresh the page and try again.If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields."

I tried all options but still hit an error. Anyone facing the same issue? Would appreciate any help I can get.

Many thanks in advance.

Best regards

Noi

This means there's a debug log in your logs folder that shows the issue. Please post the log.

swguy:

This means there's a debug log in your logs folder that shows the issue. Please post the log.

I see. Below is the log:

[14-Feb-2024 21:43:16 Asia/Singapore] Request URI: /admin_noi123/index.php?cmd=shipping_export&page=2, IP address: 202.166.24.46
#1 trigger_error() called at [/home/beads304/public_html/includes/classes/db/mysql/query_factory.php:170]
#2 queryFactory->show_error() called at [/home/beads304/public_html/includes/classes/db/mysql/query_factory.php:142]
#3 queryFactory->set_error() called at [/home/beads304/public_html/includes/classes/db/mysql/query_factory.php:269]
#4 queryFactory->Execute() called at [/home/beads304/public_html/admin_noi123/shipping_export.php:211]
#5 require(/home/beads304/public_html/admin_noi123/shipping_export.php) called at [/home/beads304/public_html/admin_noi123/index.php:11]
--> PHP Fatal error: 1064: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 'ASC
ORDER BY max_num_of_products DESC
LIMIT 1' at line 4 :: SELECT COUNT( * ) AS max_num_of_products
FROM (orders o LEFT JOIN orders_products op ON o.orders_id = op.orders_id), orders_total ot
WHERE o.orders_id = ot.orders_id
AND ot.class = 'ot_shipping' GROUP BY o.orders_id ASC
ORDER BY max_num_of_products DESC
LIMIT 1 ==> (as called by) /home/beads304/public_html/admin_noi123/shipping_export.php on line 211 <== in /home/beads304/public_html/includes/classes/db/mysql/query_factory.php on line 170.

I don't understand though. Please advise.

Many Thanks

Noi

The query's GROUP BY clause doesn't support an ASC qualifier:

SELECT COUNT( * ) AS max_num_of_products
                FROM (orders o LEFT JOIN orders_products op ON o.orders_id = op.orders_id), orders_total ot
                WHERE o.orders_id = ot.orders_id
                AND ot.class = 'ot_shipping' GROUP BY o.orders_id [B]ASC[/B]
                ORDER BY max_num_of_products DESC
                LIMIT 1

I've got no clients who use this plugin, so don't know where in the code that "ASC" is added, but removing that will remove that MySQL error.

lat9:

The query's GROUP BY clause doesn't support an ASC qualifier:

SELECT COUNT( * ) AS max_num_of_products
FROM (orders o LEFT JOIN orders_products op ON o.orders_id = op.orders_id), orders_total ot
WHERE o.orders_id = ot.orders_id
AND ot.class = 'ot_shipping' GROUP BY o.orders_id [B]ASC[/B]
ORDER BY max_num_of_products DESC
LIMIT 1

> I've got no clients who use this plugin, so don't know where in the code that "ASC" is added, but removing that will remove that MySQL error.

I see. Guess I will not use the plugin then since i also have no idea. Appreciate your input.

SELECT COUNT( * ) AS max_num_of_products
FROM (orders o LEFT JOIN orders_products op ON o.orders_id = op.orders_id), orders_total ot
WHERE o.orders_id = ot.orders_id
AND ot.class = 'ot_shipping' GROUP BY o.orders_id ASC
ORDER BY max_num_of_products DESC
noi.ng:

I see. Guess I will not use the plugin then since i also have no idea. Appreciate your input.

That error in the code has been there since v1.2.2 with zc1.3.7. I'll fix it ASAP and post a link to my GitHub file when completed.

Can you tell me what database you are using? The code error exists but I haven't managed to trigger it with either MariaDB or MySQL.

The corrected Export Shipping & Order Information v1.5.0 file for zcV157 is located in https://github.com/OldNGreyBMR/export_shipping_information/tree/main/v1.5.7/files/admin

The zc158 version is located in https://github.com/OldNGreyBMR/export_shipping_information/tree/main/v1.5.8/zc_plugins/ExportShippingInformation/v1.5.0/admin

OldNGrey:

That error in the code has been there since v1.2.2 with zc1.3.7. I'll fix it ASAP and post a link to my GitHub file when completed.

Can you tell me what database you are using? The code error exists but I haven't managed to trigger it with either MariaDB or MySQL.

Thank you!

It says : Version mysqlnd 5.0.12-dev - 20150407 - $Id:

Hope this helps.
Otherwise, please tell me where I can retrieve the data.

Best regards

Noi

OldNGrey:

The corrected Export Shipping & Order Information v1.5.0 file for zcV157 is located in https://github.com/OldNGreyBMR/export_shipping_information/tree/main/v1.5.7/files/admin

The zc158 version is located in https://github.com/OldNGreyBMR/export_shipping_information/tree/main/v1.5.8/zc_plugins/ExportShippingInformation/v1.5.0/admin

Thanks so much for the corrections!
It works for "1 order per row' now as well.

However, a new error pops up. This time it pertains to the date range chosen.
The custom date range is supposedly optional. Here is the error msg:

--> PHP Fatal error: 1064: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 'BY o.orders_id ORDER BY orders_id ASC' at line 5 :: SELECT DISTINCT o.orders_id, sum(-ABS(round(ot.value,2))) AS value
FROM (orders o LEFT JOIN orders_total ot ON o.orders_id = ot.orders_id)
WHERE o.orders_id = ot.orders_id
AND ot.class IN ('ot_coupon' , 'ot_custom', 'ot_group_pricing', 'ot_payment_type', 'ot_paymentmodulefee')
AND ot.orders_id = 1GROUP BY o.orders_id ORDER BY orders_id ASC ==> (as called by) /home//public_html/admin/shipping_export.php on line 585 <== in /home//public_html/includes/classes/db/mysql/query_factory.php on line 170.

Note: Path has been edited to hide actual.

Prior to your version, I would export for all orders to date with by leaving them blank, but now I have to specifically specify the date range. Not a big issue now as your revised version still meet my needs but I just point out.

Many thanks for your help & all the inputs from the other members!

Noi

Aaargh. ```
AND ot.orders_id = 1GROUP BY o.orders_id ORDER BY orders_id ASC ==> (as call

There is a space missing from the front of the GROUP statement.
I have updated the GitHub file <https://github.com/OldNGreyBMR/export_shipping_information/tree/main/v1.5.7/files/admin>

Please let me know if you have any other problems with this. I couldn't operate without it for monthly reconciliations.

OldNGrey:

Aaargh. ```
AND ot.orders_id = 1GROUP BY o.orders_id ORDER BY orders_id ASC ==> (as call

> There is a space missing from the front of the GROUP statement.
> I have updated the GitHub file <https://github.com/OldNGreyBMR/export_shipping_information/tree/main/v1.5.7/files/admin>
> 
> Please let me know if you have any other problems with this. I couldn't operate without it for monthly reconciliations.

Great it works now for the blank options too! Yes this program helps a lot with accounting :) 

However I looked at the exported data more closely just now for '1 Order per row', i think there is some errors in the exported data.
Some orders are duplicated. I couldn't possibly run the whole years check now but I did quick random 2024 checks.
Once the duplicated orders are removed, the sums & orders tallied.
Any idea where is the bug?

Thanks again for your quick resolution.

Noi

noi.ng:

However I looked at the exported data more closely just now for '1 Order per row', i think there is some errors in the exported data.
Some orders are duplicated.
A problem arises with the differences between MariaDB and MySQL and the many optional SQL Modes set by the user/administrator.
MySQL allows the ANY_VALUE setting to get around the GROUP BY clause when ONLY_FULL_GROUP_BY setting is used but the ANY_VALUE setting does not exist in MariaDB. [ I've learned a lot in the past few weeks.]
In the past I removed the ANY_VALUE on comments to allow the export to run on MariaDB. This caused duplicate records for "1 Order per row" when multiple comments existed against an order.

In test I can now get "1 Order per row" without duplicates even if comments are included.
I'm also parsing more so quotation marks in fields do not break the csv export.

OldNGrey:

A problem arises with the differences between MariaDB and MySQL and the many optional SQL Modes set by the user/administrator.
MySQL allows the ANY_VALUE setting to get around the GROUP BY clause when ONLY_FULL_GROUP_BY setting is used but the ANY_VALUE setting does not exist in MariaDB. [ I've learned a lot in the past few weeks.]
In the past I removed the ANY_VALUE on comments to allow the export to run on MariaDB. This caused duplicate records for "1 Order per row" when multiple comments existed against an order.

In test I can now get "1 Order per row" without duplicates even if comments are included.
I'm also parsing more so quotation marks in fields do not break the csv export.

That's interesting to note. Thanks for the information!

Best Regards, Noi

I have an updated version (v1.5.1.c) in my GitHub https://github.com/OldNGreyBMR/export_shipping_information/blob/1.5.1.c/v1.5.7/files/admin/shipping_export.php

This corrects issues:
#8 unicode names are not being parsed correctly, and
#9 additional blank columns are created on some records when one product per row is selected

Could you download this latest version and run it in your test system? If you detect any issue please post them here or as an issue in my Github.

OldNGrey:

I have an updated version (v1.5.1.c) in my GitHub https://github.com/OldNGreyBMR/export_shipping_information/blob/1.5.1.c/v1.5.7/files/admin/shipping_export.php

This corrects issues:
#8 unicode names are not being parsed correctly, and
#9 additional blank columns are created on some records when one product per row is selected

Could you download this latest version and run it in your test system? If you detect any issue please post them here or as an issue in my Github.

Sorry I missed this thread. I have just downloaded. I will try in a few days when I can avail the time. Many thanks for the updated version.

noi.ng:

Sorry I missed this thread. I have just downloaded. I will try in a few days when I can avail the time. Many thanks for the updated version.

An Update as at 24 Feb 24:
@OldNGrey

"I have an updated version (v1.5.1.c) in my GitHub https://github.com/OldNGreyBMR/expor...ing_export.php"

Hi, I assumed only this file "shipping_export.php"is changed.
I have replaced this file from the last version & it looks good now.
No more duplicates also for OneRowPerOrder too.

Just wondering, when I tried to get the complete version v1.5.1.c once more from this link below:

https://github.com/OldNGreyBMR/export_shipping_information/archive/refs/tags/ESIv1.5.1.zip

I actually noted that the "shipping_export.php" is still the older version (so I took the single file from the link you gave & replace).
Am I taking from the wrong link? Other than that all is good now.

Many thanks! This plugin is really useful

Noi

Hi, I assumed only this file "shipping_export.php"is changed.
That is correct. That is the only file that changed for the zc157 version.

Thanks for testing it. Now that we have no errors I'll create a new release as a zip file and submit it to the admins.

Testing out ZC 2.0.1. Uploaded Version 1.5.1 of this plugin. If I install V1.5.0 (Part of the plugin), I get the menu listed under tools. If I upgrade to V1.5.1 using the Plugin Manager, the menu under tools disappears. Doing a fresh install of V1.5.1 in plugin manager does not add the menu under tools.

I can get to the plugin page by manually typing the URL. But it removed the menu when I apply V1.5.1.

Thank you

V1.5.2 is in the GitHub repository https://github.com/OldNGreyBMR/export_shipping_information/releases/tag/ESIv1.5.2
and it fixes that problem.
It displays under the Tools menu as "Export Shipping & Order Info II"

IF I select 1 order per row I am getting error , whow to fix ? version of zencart is 1.5.7c
ASC
ORDER BY max_num_of_products DESC
LIMIT 1' at line 4 :: SELECT COUNT( * ) AS max_num_of_products
FROM (orders o LEFT JOIN orders_products op ON o.orders_id = op.orders_id), orders_total ot
WHERE o.orders_id = ot.orders_id
AND ot.class = 'ot_shipping' AND date_purchased BETWEEN '2024-07-01 00:00' AND '2024-08-28 23:59' GROUP BY o.orders_id ASC
ORDER BY max_num_of_products DESC
LIMIT 1