Orders Exporter Module

I had a DUH moment today. I didn't realize that I needed to edit the sort orders in the /admin/includes/language/english/orders_export.php file to align the proper datafields. I found the 'v_orders_comment' setting that you mentioned, changed to 0 and the duplicates disappeared.

Now to figure out how to prevent sorting so that the order attributes appear in the download file in the same order they appear on the printed invoice.

Looking good now. Knowing where and how to edit will make this very useful for almost anyone.

I will make the editing instructions a bit more explicit in the readme, to help people understand how to customize their reports. The attributes are output in the same order that the SQL query retrieves them, which probably has nothing to do with the way they are sorted for display in product pages or official invoices. It may be possible to add to the query to sort by attribute sort order where relevant... not something I had planned on doing, but I will keep it in mind as a future enhancement.

No problems. I thought that might be the case for sorting and must be a difference between the different ZC versions. There is no logic that I can see to the order.

Thank you for taking the time to make this valuable pluging compatable with the latest version and provide the options for easy customization.

It just occurred to me that your issue with the output not changing even when the source changes within a day may be due to some kind of query caching. If the system thinks the SQL query has already been run and is still good, it may fetch a cached copy instead of searching again. There was a reset button at the bottom of the page from the earliest version of the mod, and it might be useful for flushing the cache and forcing a new search... don't know enough about it to be sure.

I have the order comments output rebuilt and working correctly on my test site, and the readme brought fully up to date. I want to test it on my live site before submitting to Plugins just to make sure. There is one nagging item, which does not cause any problems I can see, but ought not to be happening. When selecting an export range, two odd elements are added to the URL in the address bar. They vary seemingly at random in the numbers, but always look like ...&x=23&y=9.
For example:```

Glenn . . . Future Relics Jewelry, NY Faerie Festival
Categories Dressing, Images for Titles, Smart Backgrounds, Additional Img Titles, Flexible Attributes, Ezpages Footer Columns, Model List...

This may be something above both our paygrades.

Not sure where the logic gets its order but the data in the csv is not in the same order as the databaseID or invoice. At first I thought it was the attributes but just looked and see that the products are not in the same order as the invoice.

I cannot find any consistency to the order. Sometimes A-Z, sometimes Z-A, sometimes neither by product name.

In our case, it makes things very difficult when using the CSV data and it is not in the same order as the invoice. Since we jumped from ZC1.3.9h to ZC1.5.3, I have no way of knowing if it is due to changes in DB structure or changes in how the file gets generated from the data.

We use the CSV to generate product labels and it can be a nightmare when the labels do not match the invoices.

The construction of the SQL query, aside from range limitations, has not changed since its original version. Did you have or notice this issue before?

I don't know how mySQL decides to order its results in the absence of specific "order by" commands... that needs someone with expert knowledge. Is there actually a definite order, or does it depend on some random access effects from efficient searching techniques? It might be possible to add an "order by products_id" clause in the right sequence so the overall results keep orders together. I would have to research the sequence of operation of multiple items in the "order by".

Are you wanting the CSV product output to match the invoice order? Does the invoice order match the db stored order, and does that match the order in which the customer actually added the products to the cart?

I appreciate everything you've done and realize sometimes there are limitations.

gjh42:

The construction of the SQL query, aside from range limitations, has not changed since its original version. Did you have or notice this issue before?

It may be differences caused by database upgrades.

CSV generated from within ZC v1.3.9h was always in the same order as invoices
Previous version also included customer and order info on a line by line basis. Now it seems to be only associated with the first line of an order.

gjh42:

I don't know how mySQL decides to order its results in the absence of specific "order by" commands... that needs someone with expert knowledge. Is there actually a definite order, or does it depend on some random access effects from efficient searching techniques? It might be possible to add an "order by products_id" clause in the right sequence so the overall results keep orders together. I would have to research the sequence of operation of multiple items in the "order by".

I'll have to do a bit more detailed looks but from what I can tell, the csv now appears to be in alpha-numerical order by product name.

I made a lame attempt at using the orders_products_id which I believe to be associated with specific attribute order that matches the invoice order.

gjh42:

Are you wanting the CSV product output to match the invoice order? Does the invoice order match the db stored order, and does that match the order in which the customer actually added the products to the cart?

Yes, the CSV output matched invoice order before now. The invoice order matches the db as best I have been able to tell.

I checked my files to see if I had added any ordering by product, and I had not. So I don't know why it should now be doing that. I used a natsort() on the query fields, but that is before the query is run, so it can't affect the returned results.

I did add processing to strip the redundant order and customer info from rows after the first in each order. This should make it easier to read the output and understand which products are in the same order. Is there some reason this causes a problem for you, or was it just an observation?

gjh42:

I did add processing to strip the redundant order and customer info from rows after the first in each order. This should make it easier to read the output and understand which products are in the same order. Is there some reason this causes a problem for you, or was it just an observation?
Just an observation.

Is this module compatible with v 1.5.1?

I don't know but it looks like it doesn't work with 1.5.4. Can anyone suggest an alternative?

delia:

I don't know but it looks like it doesn't work with 1.5.4. Can anyone suggest an alternative?

Go back to around post #38 and see if that zip helps. There are a few edits afterwards also. Looks like Glenn might have gotten busy and didn't upload an official update.

It has been working for me in v1.5.3 and looks OK in my 1.5.4 sandbox.

Thanks Rick!!

Well, I don't have this working completely. I got to the point of removing the order comments because of the doubling of the items. But changing the language with 0 for comments removed the comments and now I have varying duplications - up to 7 of each line. There's obviously an error in the code somewhere.

My client asked me why this kind of thing isn't part of zen cart and I told him that is it right and proper that the zen cart core team work on the core code and that our wonderful developers who frequently work for free are the one's that create these mods.

But something as basic as grabbing the orders from the database? I'm horrified that we don't have a good working version of this. I'm actually thinking it would be easier for me to write something from scratch than deal with this thing.

Any suggestions?

delia:

Well, I don't have this working completely. I got to the point of removing the order comments because of the doubling of the items. But changing the language with 0 for comments removed the comments and now I have varying duplications - up to 7 of each line. There's obviously an error in the code somewhere.

My client asked me why this kind of thing isn't part of zen cart and I told him that is it right and proper that the zen cart core team work on the core code and that our wonderful developers who frequently work for free are the one's that create these mods.

But something as basic as grabbing the orders from the database? I'm horrified that we don't have a good working version of this. I'm actually thinking it would be easier for me to write something from scratch than deal with this thing.

Any suggestions?

My guess is that grabbing orders from the database is not the major problems. It's probably all the attributes that is generating the additional line items.

Since I only use a very small subset of the available data (OID, Name, Product name, Qty and Values) and commented out the data not needed, that might be why I have learned to live with the inconveniences.

IIRC, there were several 'switches' added within the code to enable/disable various options. I did upgrade from ZC1.5.3 to ZC1.5.4 over the weekend and have not seen any difference in functionality of the data we use to generate product labels.

I went back to the old code to see if I could update it. There's very little to update to msqli. I got it partly done but just don't know enough to finish it. This is the best way to take care of this mod for the moment. Anyone able to help out a bit?

I replaced $row = mysql_fetch_array($result); with $row = $result -> fetch_assoc(); in two places in ordersExport.php and changed the mysql to mysqli in the extra functions file but more is needed in that file.

This what I have ```php
function mat_query($query) {
// global $mat_debug_logging, $mat_debug_logging_all, $mat_stack_sql_error;
global $mat_sql_errors_msgs, $mat_stack_sql_error;
$result = mysqli_query($query);
if (mysqli_errno()) {
$mat_stack_sql_error = true;
$mat_sql_errors_msgs .= "<br />".mysqli_errno() . ": " . mysqli_error() . "\n";
}
return $result;
}

errors returned are
```php
[14-Jul-2015 07:42:37 America/New_York] PHP Warning:  mysqli_query() expects at least 2 parameters, 1 given in /home/domain/public_html/admin/includes/functions/extra_functions/ordersExport_functions.php on line 29
[14-Jul-2015 07:42:37 America/New_York] PHP Warning:  mysqli_errno() expects exactly 1 parameter, 0 given in /home/domain/public_html/admin/includes/functions/extra_functions/ordersExport_functions.php on line 30
[14-Jul-2015 07:42:37 America/New_York] PHP Fatal error:  Call to a member function fetch_assoc() on a non-object in /home/domain/public_html/admin/ordersExport.php on line 264

So is the problem in the main file or the functions file?

A better way to handle this update is to modify the plugin to use the built-in Zen Cart database object ($db) to perform those database manipulations. That way, a single version of the plugin will operate on all versions of Zen Cart. The path you're heading down will result in a plugin that will work properly only on Zen Cart v1.5.2 (where the mysql->mysqli change was first implemented) and later.

okay, so I'm simply not able to do this. That's what I suspected. I'm talking with mc12345678 about incorporating this in easy populate.