One of the key pieces of functionality required by SCW was the ability to export orders from Ubercart in a format compatible with Mail Order Manager, where they handle order processing/fulfillment.
Fortunately, MOM provides an Import feature which accepts a particular CSV format, so this was mostly a matter of understanding this format and populating the fields with ubercart's internal representations of the relevant data.
As mentioned here, I would ultimately like to see this module evolve into a generalized order export interface which leverages Drupal's Import/Export API to provide any number of export formats, and to populate them in a less hackish way than my current implementation.
Having said that, if you are using MOM for order fulfillment, this module should work for you as-is. If not, it shouldn't be too hard to extend to a different format simply by replacing the uc_order_export_form_submit function with one that generates whatever you need.
As it stands, this module is far from complete- the interface is very clunky, simply dumping the export data in a big textarea from which you need to cut'n'paste into a text file to feed into MOM. This should be updated within the next few days to a mechanism which initiates a download of a CSV-formatted text file through the browser. Also, there should be a secondary step after the initial export where the admin can confirm that the orders were successfully imported/processed, and have their status updated accordingly.
In the longer-term, I'd like to extract the MOM-specific routines from uc_order_export.module itself, making it extensible to multiple different export formats (at present, three options are provided in the Export Orders page, but they are ignored). Ideally, these would make use of the Import/Export API entity definitions, and implement Import/Export "put" engines to actually generate the CSV/XML/whatever.
| Attachment | Size |
|---|---|
| uc_order_export.r582.tar.gz | 10.15 KB |
Re: Order Export (for Mail Order Manager)
Awesome, spiderman. Thanks for posting this. Having test cases helps when creating any sort of API, so this should even help us w/ the Export API when it gets worked on. We're all ears for any ideas on how that should proceed.
Error in SQL syntax
I installed it but I get the following error:
user warning: 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 ')' at line 1 query: SELECT * FROM _ubercart_uc_orders o WHERE order_id IN () in /home/domain.nl/public_html/ubercart/includes/database.mysql.inc on line 172.Did I do something wrong? (Using Ubercart Alpha 7d)
Thanks in advance, Joris
re: Error in SQL syntax
hi joris,
can you tell me exactly what you were doing when you got this error?
my first guess is that you were trying to export orders, but didn't select any for exporting- in which case, i'm missing some error-checking to avoid running a bad SQL query when there are no orders selected. try selecting a few orders before clicking export, and see if that works better.
i'll have a closer look at this later today, if you can let me know some more details.
Re: Order Export (for Mail Order Manager)
I have this error, when I try to export as CSV or XML :
<?phpuser warning: 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 ')' at line 1 query: uc_order_export_form_submit SELECT * FROM dp_uc_orders o WHERE order_id IN () in /includes/database.mysqli.inc on line 151.
?>
In fact, I think I should see the order list, and I see "No orders available." Even with no filters applied.
It it can help you...
Re: Re: Order Export (for Mail Order Manager)
We're having the same problem:
user warning: 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 ')' at line 1 query: SELECT * FROM uc_orders o WHERE order_id IN () in XXXXXX\includes\database.mysql.inc on line 172.When I try and select orders by status I get nothing. Then when I click "export" I get this error.
Re: Order Export (for Mail Order Manager)
I've tested this in alpha 8E and 1.0 with the same errors as above and no orders showing up in the sort.
Re: Order Export (for Mail Order Manager)
I have the same problem as Insurrectus (using r582). Despite having orders of all statuses, I have nothing showing up in the order list on the 'export orders' page.
Re: Re: Order Export (for Mail Order Manager)
After a little investigation, there is some code on lines 200-202 of uc_order_export.module that sets up a SQL query to get the orders to put in the list. Unless a sort_status has previously been set (lines 190-199), the code has the following clause:
WHERE o.order_status != 0I'm not an expert on SQL, but the order_status column is a varchar (ie text) and this line is trying to compare it to a number. Removing the WHERE clause from the SQL statement gives a full list of orders, so this appears to fix the problem.
Original line 202:
.'ON o.order_status = os.order_status_id WHERE '. $status . (($sort == NULL) ? ' ORDER BY o.created DESC' : $sort);Modified line 202:
.'ON o.order_status = os.order_status_id /*WHERE '. $status . '*/ ' . (($sort == NULL) ? ' ORDER BY o.created DESC' : $sort);Due to the lack of any form of comments in this function's code, it is not clear to me why 'o.order_status != 0' was used. Perhaps the module's author can shed some light on this?
XML export fixed
I have fixed up a very limited XML export version, as attached. Please use at your own risk. This was something I needed for a site I was working on and I have not tested it in all situations. The code has been tidied in places and SQL statements are clearer. This version has no CSV or MOM export options, and the XML format is fixed, but fairly universal.
Carrying forward
Thanks to spiderman and Anarres for this module. We helped move Syracuse Cultural Workers to May First People Link, so we got acquainted it when it went missing with an upgrade of Ubercart, heh.
Is anyone else modifying it and would like to share, and do people think it should be moved to a module of its own hosted on Drupal.org, or made part of import_export_api, or made part of ubercart (or a combination of the latter)?
benjamin, Agaric Design Collective
Fixed the root cause
The problem is just above that in the section that builds the where. The values should be in quotes because the field values are stings not integers so it's not matching because of the data type:
<?phpif (arg(3) == 'sort' && is_numeric(arg(4))) {
$_SESSION['sort_status'] = arg(4);
$status = "o.order_status = '" . arg(4) . "'";
} else {
if (isset($_SESSION['sort_status']) && is_numeric($_SESSION['sort_status'])) {
$status = "o.order_status = '" . $_SESSION['sort_status'] . "'";
} else {
$status = "o.order_status != '0'";
}
}
?>
So that fixes it, but now when I export I get a tone of errors (for each row) about missing tables:
* user warning: Table 'seanr.uc_payment_paypal_wps' doesn't exist query: SELECT * FROM uc_payment_paypal_wps WHERE order_id = 262 in /home/seanr/public_html/includes/database.mysql.inc on line 174.* user warning: Table 'seanr.uc_order_quotes' doesn't exist query: SELECT * FROM uc_order_quotes WHERE oid=262 in /home/seanr/public_html/includes/database.mysql.inc on line 174.
* user warning: Table 'seanr.uc_order_quotes' doesn't exist query: SELECT rate FROM uc_order_quotes WHERE oid = 262 in /home/seanr/public_html/includes/database.mysql.inc on line 174.
I don't believe I'm even using any modules which would have created those tables, so why are they hard coded into this module?
Furthermore, I'd like an easy way to dump all orders of a chosen status, rather than only selected on (which on a site with a lot of orders means I have to do a dump for each page of the paginated table - that gets ugly quickly).
As for putting this on drupal.org - absolutely, should be done with ALL ubercart modules, IMO.
Fixes
I added some modifications to get a rid of some error messages, see the patch.
Patched but still getting errors ...
I've patched per post #12 but I'm still getting some error messages. Here are the details. Any help/suggestions would be most appreciated:
1) Click export using MOM CSV
2) The orders show up in the export box but I also get the following errors:
* warning: join() [function.join]: Invalid arguments passed in /var/www/vhosts/sitename/httpdocs/sites/all/modules/ubercart/uc_order_export/uc_order_export.module on line 700.* warning: join() [function.join]: Invalid arguments passed in /var/www/vhosts/sitename/httpdocs/sites/all/modules/ubercart/uc_order_export/uc_order_export.module on line 700.
* warning: join() [function.join]: Invalid arguments passed in /var/www/vhosts/sitename/httpdocs/sites/all/modules/ubercart/uc_order_export/uc_order_export.module on line 700.
* warning: join() [function.join]: Invalid arguments passed in /var/www/vhosts/sitename/httpdocs/sites/all/modules/ubercart/uc_order_export/uc_order_export.module on line 700.
3) Can we add a "save as" to prompt the user to download the CSV via a dialog box?
Thanks in advance.
-backdrifting
export comments section?
That looks like this line in my code:
return split("\n",wordwrap(join('',$comments),80));I'll try commenting that Comments section out and try it again.
-backdrifting
Here's the fix ... I think
Yeah, commenting out this line seems to have fixed it:
//return split("\n",wordwrap(join('',$comments),80));How do I go about submitting this as a patch?
-backdrifting
Export to CSV button?
So now what I'm looking for is a method of clicking on a button to download/save the exported CSV file to your desktop - is this easy to implement? It would be nice from a usability perspective to have a clickable button to download the CSV file.
-backdrifting