Export Shipping & Order Information

[noi.ng]

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

[swguy]

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

[noi.ng]

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

[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 ASC
                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.

[noi.ng]

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 ASC
                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.

[OldNGrey]

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

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.

[OldNGrey]

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

The zc158 version is located in https://github.com/OldNGreyBMR/expor...n/v1.5.0/admin

[noi.ng]

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

[noi.ng]

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

The zc158 version is located in https://github.com/OldNGreyBMR/expor...n/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

[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/expor....7/files/admin

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