Export Shipping & Order Information

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!

swguy:

Thanks for your contribution!

Well, you're welcome swguy, but I haven't contributed anything yet, :smile:, but I will.

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?

Dashizna:

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?

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/export_shipping_information/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.

n8pbm:

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.
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/export_shipping_information/tree/ESIv1.4.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.