Printable View
In the SQL Query for that you would want to add an ORDER BY clause to it then specify the Order / Product Name as the criteria. That should do it.
Quote:
I would like to export my orders sorted by Order/product Name. Instead they are exported by Order/order entered in cart.
Any suggestions/code on how to do this?
Thanks,
Thanks, for your support/reply.
Got it working!
With Ajeh's help I figured out how to sort my address books by Company and I applied that same tweak to the ESOI
shipping_export.php around line 115. Need to test a bit more but it seems to work GREAT for my purposes!
$order_info = $order_info . " ORDER BY orders_id ASC, products_name";
//echo $order_info;
I'm only exporting 4 columns that I mapped to custom headers for import into QB.
Customer,RefNum,Item,QTY
RefNum is the order ID
I'm using a app by B.. S.... TIP5 to import CSV into QB which works very well.
I do have a question. I have to use the TXT format as I have leading zeros in my Products Name (Items)
I changed the delimiter in the TXT export to a comma (otherwise my headers were not delimited correct) and the headers are OK but the data is still using the tab as the delimiter so I have to manipulate the file with Excel before importing into QB. If I change the \t to a comma strange thins happen.
I can't use the CSV export because many of my item numbers have leading zeros and they get stripped when exported.
Any thoughts?
What if the items that come over as numbers (with leading zeros) are enclosed in quotes? Does that make them 'text' fields in excel when opening (if a text field is used the zeros will not be dropped by excel)? If you look at the raw CSV file my guess is the leading zeros are there but when opening in excel those rows are inserted as 'numbers' and thus the leading zero is dropped.
Other options would be to open the CSV in a program other than Excel (maybe notepad?) and / or setting the fields in excel to "text" for those items that have leading zeros.
Let me know if that helps.
Ahh... I was missing the quotes part so Yes that should solve my problem.Quote:
Originally Posted by econcepts
Currently we give the file a *.txt extension and open it with excel so we can make that a text field and that save it as a *.csv file but I was trying to skip all that and just import the file directly into QB.
Thanks You very much,
Anytime. Glad you got it working.
I am having a similar problem from a few posts back (post 385)
I only want to export orders that have a certain status and the module is marking everything exported.
Would you take a look at my code just to make sure I haven't overlooked anything?
I've got a fix for that here in a newer version. Send me a PM and I'll email it to you. Will be uploading the newer version here with that fix at some point.
Has anyone got any suggestions to my post? I would really like to stop the keep alive timer coding from interfering with this module.
I also have an issue with one of my carts duplicating lines. Any ideas as to where to start to fix the issues?
Apparently Export Shipping & Order Information version 1.3.2.1 is not compatible with Zencart 1.5.3 .
When exporting a CSV file, Order Subtotal and Order Status fields does return with any results.
I have two separate shopping carts with two different domains behaving the same after upgrading to ZC 1.5.3. Worked fine in ZC 1.5.1
Anyone else having the same problem?
Has anything else changed other than the version upgrade. I can take a look and see what it might be on this end. Let me know that answer and I'll see what I can do.
Non-really. Just reapplied all the modules I've used in 1.51.
Only real changes were: Easy populate 1.3.9gp + 4.0, Walk-in Pickup shipping module, and Order Total optionals insurance.
Easy Populate 1.3.9gp, and Easy Populate 4.0 stopped working in ZC 1.5.3. So I've gotten rid of EP 1.3.9gp, and updated EP 4 to the latest patch.
The built in Walk-in store pickup module was broken so you can't restrict by regions with 1.5.3, applied modification fix.
Finally, there's Order Total Insurance, was added shipping insurance even if Walk-in option was selected. Also a modification fix, by Ajeh.
Just really puzzled at why only the two fields aren't being exported, and the most important fields for accounting at that! No errors were thrown on the screen during export either. But ZC 1.5.3 seems to be quite the buggy release.
Quote:
Originally Posted by econcepts
The Export Shipping & Orders Information module is not compatible with Zen Cart 1.5.3+. If one wishes to use this module in newer versions of Zen Cart, the module will need to be updated.Quote:
Originally Posted by chibipaw
NOTE 1: In particular, this module uses direct "mysql_*" functions (mixed with $db method calls). Best practice is for Zen Cart plugins (modules) to only use $db methods for accessing the database. This provides better long term compatibility with Zen Cart (and future versions).
NOTE 2: Zen Cart 1.5.3+ no longer uses "mysql_*" functions (these functions were removed from upstream versions of PHP). Instead Zen Cart now uses the newer "mysqli_*" functions. This means modules using "mysql_*" functions and relying on Zen Cart to open a MySQL connection for the "mysql_*" functions (instead of only using $db) will fail in Zen Cart 1.5.3+.
Ummm that could be a problem. Are there any other alternative to dump details into a CSV file?
right now Im using Sales Report 2.0. It does seem to do that, but lack detailed information, like order status. payment methods etc.
Is anyone known to be working on a fix for this? Or can anyone recommend an alternative? I've barely enough experience with Zen Cart to create a site, but boy was this every a helpful module for the last site I created on 1.5.1...Quote:
Originally Posted by lhungil
With the amount of cricket noise out there, it's pretty safe to say this is abandoned-ware.
Quote:
Originally Posted by kzabbott
This awesome plugin has been a workhorse for me.
I have modified it heavily as I only wanted to export Customer, Order ID, Part Name & Quantity.
Recently I added the Date of purchase using "date_purchase" as shown below to my exported data (*.csv file)
+++++++++++++++++++++++
I need some help with this:
The date is the full date and time stamp: 1/14/2015 1:52:31 PM
I only want the date: 1/14/2015
+++++++++++++++
I modified ordersExport.php accordingly and In shipping_export.php I added this line around line 370 to populate the spread sheet date column:
$str_export .= $FIELDSEPARATOR . $FIELDSTART . $order_details->fields['date_purchased'] . $FIELDEND;
Here's a bigger slice of the code where I added that line.
//*************eof State Abbr Codes********************//
/******************************************************************************** ***************************************************/
if ($_POST['product_details'] == 1) { // Order details should be added to the export string.
if ($_POST['filelayout'] == 2) { // 1 PPR RADIO
$str_export .= $FIELDSEPARATOR . $FIELDSTART . $order_details->fields['products_name'] . $FIELDEND;
// $str_export .= $FIELDSEPARATOR . $FIELDSTART . $order_details->fields['final_price'] . $FIELDEND;
$str_export .= $FIELDSEPARATOR . $FIELDSTART . $order_details->fields['products_quantity'] . $FIELDEND;
// $str_export .= $FIELDSEPARATOR . $FIELDSTART . $order_details->fields['products_model'] . $FIELDEND . $FIELDSEPARATOR;
$str_export .= $FIELDSEPARATOR . $FIELDSTART . $order_details->fields['date_purchased'] . $FIELDEND;
$product_attributes_rows="SELECT Count(*) as num_rows
I'm going to work on an update for this. I've been working on a few other updates to other plugins of mine and am now just getting around to this one.
Woo hoo! Excited to see this get resurrected!
Quote:
Originally Posted by econcepts
Quote:
Originally Posted by linuxguy2
Still working on this and I know the solution is simple but I can't get it sorted... :blush:
Any takers?
I have another Issue: :(
Although I have this in my code around lines 675 I am not getting the date displayed on the main screen after I do an export.
<td class="dataTableContent"><?php echo $date_purchased; ?></td>
Got it figured out.
To eliminate the timestamp from exported orders.
Replaced this:
$str_export .= $FIELDSEPARATOR . $FIELDSTART . $order_details->fields['date_purchased'] . $FIELDEND;
With this.
$str_export .= $FIELDSEPARATOR . $FIELDSTART . zen_date_short($order_details->fields['date_purchased']) . $FIELDEND;
On the date display I had to move the date_purchased from the end of the query to the appropriate position in this code as I am not displaying all the fields.
$query = "SELECT o.orders_id, customers_email_address, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, date_purchased, shipping_method, customers_telephone, order_total
FROM ". TABLE_ORDERS ." o
WHERE downloaded_ship='no'
ORDER BY orders_id ASC";
Trying to export customer info to Excel: name, mailing address in full.
Tried to download a plugin, it`s not working.
Anyone?
What plugin?Quote:
Originally Posted by lolo1
What plugin?Quote:
Originally Posted by lolo1
How/where is it failing?
ZC 1.51 with several plugins and modified core files.
I have duplicate products names (items) but some are in a hidden category prefaced with a "H" for a special customer.
When I export orders I would like to parse the product names and strip the "H" from the front of certain items.
But not all of them.
EXAMPLE:
H10001 after export 10001
H10002 after export H10002
H10003 after export 10003
H10004 after export H10004
etc.
I've already made some mods to sort on product names in shipping_export.php but I could use some help on this one.
Thanks
Is there anyone out there who's prepared to upgrade this mod? My PHP skills are not adequate...Quote:
Originally Posted by lhungil
The "orders export" plugin might provide similar functionality? Though not updated as a plugin for 1.5.4, the support thread has an updated RC that has been working for me in 1.5.3 for a year & 1.5.4 since early July.
I was the original author of the module. I can look at updating it to work with newer versions. Let me see what it would take to do that here.Quote:
Originally Posted by schoolboy
Please do! My monthly sales analysis is a nightmare without it!Quote:
Originally Posted by econcepts
So, uhm... I'm not sure if I'm missing something obvious here, but it took me 5 minutes to upgrade it and have it working with 1.5.4...
I'll submit it to the plugins in a few minutes since everything seems to be working fine on my test install. In the meantime, for those who can't wait for it to be approved, here's a direct link to the file on my server:
click here
The update has been approved and can be downloaded from the Plugins section. I'm removing it from my server so please use the official download section.
Excellent. Thanks. I'll take a look here. I have a version that a few have used (even more automated than the one on the site I believe) which has some nice features in it. I'll compare that with the 'newest' version here and merge any updates so users get those updates as well. Will compare the two soon.
Hi guys.
Loving the module... just a small question that has not been answered, CRON JOB. I know it is possible but i do not know how.I am using cron job for other modules and that works
I am using zen-cart 1.5.4 and i modified the php file so that some boxes are ticked/checked by default.
Any help would be very appreciated.
Greetings Kevin
Hi Kevin,
I've setup the CRON version of this for a number of stores. I have a totally separate file I wrote for that (as the current file has to many areas where it requires being run from the 'admin panel' directly.)
Have you made any updates to the current version you are using?
I can adjust this file and send it your way based on any adjustments you might have made or need.
Send me a private message and we can exchange details on how to get that file adjusted and sent your way.
Eric
Hi
Will this addon work with 1.5.5b and PHP7?
Thanks
Dene
Thank you Eric.Quote:
Originally Posted by econcepts
I have send you an PM...
Greetings
Kevin
Kevin,
Just replied to your email. Gave you the email to send your file to.
Thanks
Hi Dene,Quote:
Originally Posted by Deno
It should work (but may need a little modification.)
Are you looking for the CRON version or the native admin version?
Hello there,
Following on from Kevin, I am interested to learn about the cron edition of this module please.
Regards,
James
James, I'm working on a customized copy of the CRON edition for Kevin. What version are you using currently of the software?
Eric
I have it working on zc.1.5.5.b , but my server says the php version is 5.2.26 .Quote:
Originally Posted by Deno
Hello Eric,Quote:
Originally Posted by econcepts
I am currently on 1.3.2, but it shows an error on the bottom of the page and doesn't allow order export. But I am on php 7, so I think that is the reason. Also 1.55b zenart.
Regards,
James
Out of curiosity.
Anybody noticed the latest 1.3.3's version definition is incorrect? I mean it doesnt really affect functionality..
admin/shipping_export.php
define('VERSION', '1.3.2');
I've been using Export Order for years, but after a ZC update to v 1.55, it's behaving a little differently than it used to. This is causing me difficulty when using the resulting CSV for UPS Worldship batch imports.
First, the file datetime name format now has a "-" separating the date from the time, i.e. Orders081517-1423.csv, which UPS Worldship doesn't like. It used to be simply in the format "Orders0815171423.csv". How can I change the file format? I realize I could tell my fulfillment people to remove the "-", when they save the CSV file but they're not computer people, and I want to have as few changes in the old process as possible.
Second, and even more problematic is that the header row is exporting with spaces between words:
Order ID,Customer Email,Delivery Name,Delivery Street,Delivery Suburb,Delivery City,Delivery State,Delivery Post Code,Delivery Country,Ship Dest Type
Looking at the database tables, the columns are named thusly: Order_ID,Customer_Email,Delivery_Name,Delivery_Street, etc. I need the headers to export as they are in the database (and as UPS Worldship can read them) with "_ " between header column words. How can I change the exported .CSV to respect the original table column names?
Thanks very much for any guidance.
Looks like during the upgrade of your plugins you wiped out the customizations you'd made to that plugin's code.Quote:
Originally Posted by greg_beyer
To fix the hyphen in the filename, you'll need to remove it from line 51:And for the spaces that you want to be underscores, starting at line 181 you'll see the column headings at the end of most of the lines:Code:$file = (isset($_POST['filename']) ? $_POST['filename'] : "Orders".date('mdy-Hi'). $file_extension ."");
You'll need to adjust those again to suit your needs.Code:/******************Begin Set Header Row Information*****************************/
$str_header = "Order ID,Customer Email";
if ($_POST['split_name'] == 1) { //If name split is desired then split it.
$str_header = $str_header . ",First Name,Last Name";
} else {
$str_header = $str_header . ",Delivery Name";
}
$str_header = $str_header . ",Company,Delivery Street,Delivery Suburb,Delivery City,Delivery State,Delivery Post Code,Delivery Country,Ship Dest Type"; // swguy
if ($_POST['shipmethod'] == 1) { $str_header = $str_header . ",Shipping Method"; };
if ($_POST['shiptotal'] == 1) { $str_header = $str_header . ",Shipping Total"; };
if ($_POST['customers_telephone'] == 1) { $str_header = $str_header . ",Customers Telephone"; };
if ($_POST['order_total'] == 1) { $str_header = $str_header . ",Order Total"; };
if ($_POST['date_purchased'] == 1) { $str_header = $str_header . ",Order Date"; };
if ($_POST['order_comments'] == 1) { $str_header = $str_header . ",Order Notes"; };
if ($_POST['order_tax'] == 1) { $str_header = $str_header . ",Order Tax"; };
if ($_POST['order_subtotal'] == 1) { $str_header = $str_header . ",Order Subtotal"; };
if ($_POST['order_discount'] == 1) { $str_header = $str_header . ",Order Discount"; };
if ($_POST['payment_method'] == 1) { $str_header = $str_header . ",Payment Method"; };
if ($_POST['orders_status_export'] == 1) { $str_header = $str_header . ",Order Status"; };
if ($_POST['iso_country2_code'] == 1) { $str_header = $str_header . ",ISO Country Code 2"; };
if ($_POST['iso_country3_code'] == 1) { $str_header = $str_header . ",ISO Country Code 3"; };
// if ($_POST['abbr_state_code'] == 1) { $str_header = $str_header . ",Abbr State Code"; };
if ($_POST['product_details'] == 1) { // add to header row
if ($_POST['filelayout'] == 2) { // 1 Product Per row RADIO
$str_header = $str_header . ",Product Qty,Products Price,Product Name,Product Model,Product Attributes";
} else { // File layout is 1 OPR
/**************the following exports 1 OPR attribs****************/
$oID = zen_db_prepare_input($order_details->fields['orders_id']);
$oIDME = $order_details->fields['orders_id'];
$order = new order($oID);
for ($i = 0, $n = $max_products; $i < $n; $i++) {
$str_header = $str_header . ",Product " . $i . " Qty";
$str_header = $str_header . ",Product " . $i . " Price";
$str_header = $str_header . ",Product " . $i . " Name";
$str_header = $str_header . ",Product " . $i . " Model";
$str_header = $str_header . ",Product " . $i . " Attributes";
}
/*****************************************************************/
} // End if to determine which header to use
} // end Row header if product details selected
$str_header = $str_header . "\n";
/******************End Header Row Information*****************************/
Thanks very much, Doc! Tweaking those lines did the trick, .CSV is exporting perfectly.
Just looking at using this plugin/module - and I can see that there is talk of a cron version - is that still available - maybe I am searching wrong but I couldn't seem to find it in plugins directory
Thank you to the guys that have been creating and updating this though might just be a life saver!
I've been using this this module for several years now (with a slight formatting mod) & it's GREAT!
Guess I'm late to the party as I haven't heard of these two other versions.
What is the difference between CRON version and the native admin version?
Where would one acquire each of these?
Thanks, Lucien
I built the CRON version for a number of sites and never uploaded the code for that. It was basically customized for different drop shippers and their suppliers. If you are using the standard version of the plugin (with no customizations) then I could get you a copy of that CRON version. If you have customized your version at all or require any special updates for your supplier just let me know and I can add those as well.
The difference between the CRON version and the Admin version is that the CRON version runs automatically at set intervals so you do not have to login to the admin and click anything. This comes in handy for let's say, drop shippers who want to send orders to their suppliers for fulfillment. I have also customized a few CRON versions for myself that export the file and either email it to the supplier automatically and / or FTP it to the supplier once run.
Let me know what either of you two are looking for and I'll see if what I have here needs to be altered to work.
Hi Eric - so sorry the system didn't tell me there was a reply! I am in discussion about what exactly is needed however a CRON version on the non customised version is certainly perfect - however I have got a CSV Template that want me to work too or an XML version (did the XML version ever get done??) - so if you are able I would happily send over the CSV file so you can determine is its possible to achieve what they are asking for with customisation and cron too - as that would be my ideal??Quote:
Originally Posted by econcepts
Thank you
Sarah
Uploaded a version to work with Php 7.x
Version is now 1.3.4
Indispensable Plugin!
My client wants to NOT see all the RSS stuff and I don't want it running in the background.
How do I gracefully remove all the RSS feed stuff.
I butchered the code on a previous install BUT I don't want it running in the background.
Thanks, Lucien
I'm using the checkbox option in the attributes
When downloading CSV file I want to make a separate row for each products WITH the size attribute appended.
Is this possible in exporter or would this require a ZC code change when generating the order?
THIS:
Product Name
4400407S
4400407L
NOT THIS:
Product Name Product Model Product Attributes
4400407 6003 Size: S | Size: L |
Package in zen repository, export_shipping_information_V1.3.5.zip, file admin/shipping_export.php has version number 1.3.2 (it wasn't updated) so wrong version number shows in admin interface.
Updated to remove marketing information, clean out dead code and reformat.
The variable "TEXT_PREVIOUS_EXPORTS" is defined twice in \admin\includes\languages\english\extra_definitions\shipping_export.php
lines 32 and 33
PHP 7.1
zen-cart-v1.5.6a-01042019
[CloneClassic Template]
one_page_checkout-2.0.5
extra_field_on_customer_sign_up_0
EasyPopulate-4.master-ZC
export_shipping_information_V1.3.5
Several core mods.
The issue I'm having with ESI is when the Company name has a comma in it (My Big Company, Inc.)
As expected the CSV file sees the comma as an additional delimiter (See attached example)
Is it possible to delete the commas in the "Customer" column during exporting.
OR is there some other option to eliminate the comma.
Thank You for your time!
I think your problem is Excel, not the CSV file. Open the CSV file with Notepad and see how it looks. If the data is something like "My Big Company, Inc.","12670","L-01"... then it's definitely Excel causing headaches. If exported file does have correct field delimiters and fields are properly enclosed with double-quotes, the results ARE valid, and it's just Excel being Excel. I find Excel almost impossible to use with CSV files and have become a huge fan of OpenOffice Calc (plus it's free).Quote:
Originally Posted by linuxguy2
As a workaround, you can change the delimiter to a semicolon - it's a trick I used before OpenOffice Calc. Just edit shipping_export.php and change line 36 from
toCode:$FIELDSEPARATOR = ',';
and when you open the file in Excel, you'll have to set your custom delimiter when you choose to convert data to columns.Code:$FIELDSEPARATOR = ';';
if you are drop shipping to different providers, the manufacturer may be the most important part of the export. Has anyone added that in at any point? I might be able to figure out how but I'm a little surprised this isn't in there.
So far this module is great. i just want to ask if i can setup a way to export the billing address for customers? also if there is a way to set all the boxes up so that i don't have to do it every time i export because we use the same method every time.
Thanks
Version number in the main file is set back at 1.3.2 it should be 1.3.5 by now
Hello, I'm using v 1.3.3 of the plug-in. This is a fantastic tool for doing all kinds of free-style analysis of orders. Thanks to all who created it and contributed to it. I'd like to learn how to extend it just a bit. In the Select File Layout to Export section, when I select the " 1 Product per row" radio, I would like to include the Category of the product in the CSV. Can someone tell me what file(s) I need to hack up? I'm no php dev, but I think I can follow existing code, and do this myself, given a little lead. I'll post my code once I nut it out, and if swguy thinks it's worthwhile, you can publish an update to the plugin.
Thanks for the help!
Thanks for your contribution!
Well, you're welcome swguy, but I haven't contributed anything yet, :smile:, but I will.Quote:
Originally Posted by swguy
So I downloaded the latest ver and took a look at the files, and surprisingly there are only four .php's It looks like /admin/shipping_export.php is the file to tweak. It looks like lines 73-75 is where the solution is, and I'd stick the category name at the bold.
if ($_POST['filelayout'] == 2) { // 1 Product Per row RADIO
$order_info = "SELECT o.orders_id, customers_email_address, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, shipping_method, customers_telephone, order_total, op.products_model, products_name, categories_name, op.products_price, final_price, op.products_quantity, date_purchased, ot.value, orders_products_id, order_tax, o.orders_status, o.payment_method";
Simple enought, BUT . . .
I said I'm not a PHP dev, but I now see ya gotta know your way around mysql, too. :shocking: (Note the twitch, ha-ha) I do know that anywere you have a "SELECT" you have to have a matching FROM which I see a little further down. Got no idea how to insert CATEGORIES_DESCRIPTIONS.categories_name into this:
$order_info = $order_info . " FROM (" . TABLE_ORDERS . " o LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " op ON o.orders_id = op.orders_id), " . TABLE_ORDERS_TOTAL . " ot";
if ($_POST['iso_country2_code'] == 1 || $_POST['iso_country3_code'] == 1) {
$order_info = $order_info . ", " . TABLE_COUNTRIES . " cc";
};
Anyone?
Well, simply adding categories_name to the SELECT string does not work, I get a column not found error. That would be because the FROM statement does not include the CATEGORIES_DESCRIPTIONS table, categories_name column.
$order_info = $order_info . " FROM (" . TABLE_ORDERS . " o LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " op ON o.orders_id = op.orders_id), " . TABLE_ORDERS_TOTAL . " ot";
I've pasted the original select / from statement in my my phpmyadmin sql window and cleaned out the PHP coding, and tried about 30 different combos of sql runs but cannot get just this original query to run. Do not find the "o", "op" and "ot" syntax in mysql documentation, so don't know their significance.
Much less do I know how to add the CATEGORIES_DESCRIPTION.categories_name to the FROM. So I guess hacking this myself is beyond my capabilities. Swguy, I'd sure appreciate your help in getting the FROM statement right. Then I think the adding categories_name to the select will work.
Hadda at least try. Thanks for any help.
No, you can't retrieve category information from that table, because that table doesn't contain those details.
In fact, none of the "order"-related tables contain anything related to the category in which a product existed at any point in time.
If the product actually still exists in the database, then you could manually query for the "master" category ID that the product belongs to, but you can't get what "linked" category the customer might have been shopping in when adding the item to their cart.
To do that, you could use:
zen_get_category_name(zen_get_products_category_id($products_id)) ... but you'll have to also get $products_id when querying the orders_products table by adding products_id to the appropriate query.
Is there a way i can set it up so that i don't have to go threw and pick each box every time? If so how can i do that?
Anyone?Quote:
Originally Posted by Dashizna
From memory it is covered in this thread. Run a search for defaults.
Hi All,
Just installed Export shipping / order information and it works a treat but what I want to do is add some more fields for it to download which are:
Product shipping weight
Product length
Product width
Product height
If anyone could assist on where and what files I need to adjust it would be a great help.
I am using Zen cart version 1.5.8 and PHP 7.4.3
I have a clean install and I installed the plugin Export Shipping & Order Information.
Plugins installed are:
EasyPopulate-4.master-ZC
backup_mysql_plugin_v1-5g
I am getting a PHP error (see below) right after install of the plugin
--> PHP Fatal error: 1054:Unknown column 'downloaded_ship' in 'where clause' :: SELECT count(*) as total FROM (select o.orders_id, customers_email_address, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, shipping_method, customers_telephone, order_total, date_purchased from zenlq_orders o where downloaded_ship='no' order by orders_id asc) countresults ==> (as called by) /control/includes/classes/split_page_results.php on line 220 <== in /includes/classes/db/mysql/query_factory.php on line 667.
I am not a pro when it comes to PHP so a simple explanation would be greatly appreciated.
Thank you.
Did you run the sql statement in the INSTALL.sql file ? This adds the column "downloaded_ship" to the orders table.
I have an updated version of Export Shipping & Order Information in my GitHub at https://github.com/OldNGreyBMR/expor...tree/SEO1.4.1c. This version has been tested with ZenCart v157 and v158a and PHP 7.4 and PHP 8.2.
BE WARNED! I have changed the order of the columns output in the CSV file to match reconciliation spreadsheets I have been using for years.
The default order in this version is:
Order ID, Customer Email, First Name, Last Name, Company, Delivery Street, Delivery Suburb, Delivery City, Delivery State, Delivery Post Code, Delivery Country, Ship Dest Type, Order Date, Product Qty, Product Model, Product Name, Products Price, Product Attributes, Line cost, Line tax, Order Subtotal, Shipping Total, Order Discount, Order Total, Order Tax, Payment Method
Other changes are: all discounts are now included; line tax is now calculated; version number is displayed in admin; defaulted the most commonly used checkboxes (by me) to "Checked" .
If you select an option that is not checked by default the order of the column heading may be incorrect. I am working on it for a future release.
thanks for maintaining this module @OldNGrey.
I have updated https://www.zen-cart.com/downloads.php?do=file&id=406 to point to your repo.
Hi, i am running Zencart v1.5.7d as a fresh install.
I have just installed this modification and it works very well. however if i select the "1st Order Comment / Note" field to include with the csv file the process crashes.
I get the error "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." displayed.
Has there been a change to the comments field in later versions of zencart?
This modification worked perfectly on older zen stores.
Also how easy is it to add extra fields to this csv file. I need to add Customer ID and the 3 Category levels.
My PHP skills aren't that good and im getting a bit lost looking into the code for this so don't mind paying for help.
Thank you.
Did you run the sql statement in the INSTALL.sql file ? This adds the column "downloaded_ship" to the orders table.
This is the only error I can create on a fresh install on 157d with PHP 8.0.
Running ZC1.5.8a and updated to version 1.4.1d. In my tax item I am getting additional text in some of my line items. See below:
Line tax
0"Year: 2010 | Make: Jayco | Model: baja 14f | VIN # (17 Characters Long): 1UJAJOBM9A15F0080 |
0
0"Year: 2007 | Make: Palomino | Model (3 or 4 number + letters): P-283 | VIN # (17 Characters Long): 4X4CPN3158N035619 | Storage Compartment: Front |
0"Year: 1997 | Make: Jayco | Model: TL | VIN # (17 Characters Long): 1UJAJ02H8V1BR0073 |
0"Year: 1997 | Make: Jayco | Model: TL | VIN # (17 Characters Long): 1UJAJ02H8V1BR0073 |
The 0 is correct but the attributes/options for the items from the order appear to be included as well.
I can replicate the issue and am working on it.Quote:
Originally Posted by n8pbm
I don't use attributes so this error wasn't detected in my testing.
I have updated export shipping information and correct the attributes mixed with product name issue.
The latest version of the main file is in my GitHub https://github.com/OldNGreyBMR/expor....3/files/admin.
This can be downloaded and tested in a test environment. Test on zc158a and PHP 7.4 to 8.2
For the complete release I have also updated the sample documents and the language files. These will be provided to moderators soon.
I have updated export shipping information and submitted it for moderation. When it is approved I will post here.
Version 1.5.0 supports zc1.5.7 and zc1.5.8+ and will run on PHP 7.4 to PHP 8.3
For zc1.5.8 the installer is in the zc_plugins structure and installs under admin Tools as "Export Shipping & Order Info II".
For zc157 the files follow the older file structure.
The latest version of the main file is in my GitHub https://github.com/OldNGreyBMR/expor....3/files/admin.
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.
I see. Below is the log:Quote:
Originally Posted by swguy
[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:
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.Code: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 see. Guess I will not use the plugin then since i also have no idea. Appreciate your input.Quote:
Originally Posted by lat9
Quote:
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
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.Quote:
Originally Posted by noi.ng
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/expor....7/files/admin
The zc158 version is located in https://github.com/OldNGreyBMR/expor...n/v1.5.0/admin
Thank you!Quote:
Originally Posted by OldNGrey
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
Thanks so much for the corrections!Quote:
Originally Posted by OldNGrey
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.There is a space missing from the front of the GROUP statement.Code:AND ot.orders_id = 1GROUP BY o.orders_id ORDER BY orders_id ASC ==> (as call
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.
Great it works now for the blank options too! Yes this program helps a lot with accounting :)Quote:
Originally Posted by OldNGrey
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
A problem arises with the differences between MariaDB and MySQL and the many optional SQL Modes set by the user/administrator.Quote:
Originally Posted by noi.ng
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!Quote:
Originally Posted by OldNGrey
Best Regards, Noi
I have an updated version (v1.5.1.c) in my GitHub https://github.com/OldNGreyBMR/expor...ing_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.Quote:
Originally Posted by OldNGrey
An Update as at 24 Feb 24:Quote:
Originally Posted by noi.ng
@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/expor.../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
That is correct. That is the only file that changed for the zc157 version.Quote:
Hi, I assumed only this file "shipping_export.php"is changed.
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/expor.../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