Order Export (for Mail Order Manager)

Submitted by spiderman on Thu, 10/04/2007 - 16:05

Contrib type:

Module

Status:

Initial upload

Downloads

Latest version:

r582

Compatibility:

Ubercart 1.x

View all releases

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.

Development

View pending patches

Preview	Attachment	Size
	uc_order_export.r582.tar.gz	10.15 KB

Fri, 10/05/2007 - 08:02

#1 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15450

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.

Wed, 10/10/2007 - 04:38

#2 View page

Jorisvt

Offline

Joined: 10/07/2007

Juice: 11

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

Wed, 10/10/2007 - 10:18

#3 View page

spiderman

Offline

Joined: 08/17/2007

Juice: 61

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.

Tue, 12/18/2007 - 06:19

#4 View page

zmove

Offline

Joined: 08/13/2007

Juice: 1193

Re: Order Export (for Mail Order Manager)

I have this error, when I try to export as CSV or XML :

<?php
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: 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...

Mon, 07/21/2008 - 17:43

#5 View page

Insurrectus

Offline

Spreading the word - Ubercart for president.

Joined: 08/22/2007

Juice: 364

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.

Mon, 07/21/2008 - 18:16

#6 View page

Insurrectus

Offline

Joined: 08/22/2007

Juice: 364

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.

Tue, 09/16/2008 - 08:09

#7 View page

chris.cohen

Offline

Joined: 09/08/2008

Juice: 17

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.

Tue, 09/16/2008 - 09:02

#8 View page

chris.cohen

Offline

Joined: 09/08/2008

Juice: 17

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 != 0

I'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?

Thu, 09/18/2008 - 11:21

#9 View page

chris.cohen

Offline

Joined: 09/08/2008

Juice: 17

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.

Attachment	Size
uc_order_export_chris.cohen_.zip	9.59 KB

Sun, 11/09/2008 - 22:14

#10 View page

Benjamin MelanÃ§on

Offline

Joined: 12/28/2007

Juice: 6

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

Wed, 01/07/2009 - 20:10

#11 View page

Sean Robertson

Offline

Joined: 01/07/2009

Juice: 2

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:

<?php
  if (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.

Fri, 02/20/2009 - 09:18

#12 View page

Yorirou@drupal.org

Offline

Joined: 02/20/2009

Juice: 2

Fixes

I added some modifications to get a rid of some error messages, see the patch.

Attachment	Size
uc_order_export.diff	1.53 KB

Mon, 04/20/2009 - 10:31

#13 View page

backdrifting

Offline

Joined: 10/03/2008

Juice: 373

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

Mon, 04/20/2009 - 10:34

#14 View page

backdrifting

Offline

Joined: 10/03/2008

Juice: 373

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

Mon, 04/20/2009 - 10:44

#15 View page

backdrifting

Offline

Joined: 10/03/2008

Juice: 373

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

Mon, 04/20/2009 - 11:07

#16 View page

backdrifting

Offline

Joined: 10/03/2008

Juice: 373

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

Fri, 05/14/2010 - 08:49

#17 View page

bass28

Offline

Joined: 05/10/2010

Juice: 19

Is this module still active?

I see the comments are rather old but I need to be able to export orders to a csv based on order status. If there are no other modules that handle this I am willing to move this to Drupal 6 and add my options.

Mon, 05/02/2011 - 11:02

#18 View page

roknrod12

Offline

Joined: 02/11/2011

Juice: 23

Does this module work?

I noticed that this is pretty old. Does this module work with current Drupal and UberCart releases?

Thanks,
rh

Mon, 05/02/2011 - 11:16

#19 View page

bass28

Offline

Joined: 05/10/2010

Juice: 19

Found a better way

I used views bulk operations (http://drupal.org/project/views_bulk_operations) in conjunction with ubercart views bulk operations. It is very easy to add an export as another bulk operation.

Mon, 05/02/2011 - 11:52

#20 View page

roknrod12

Offline

Joined: 02/11/2011

Juice: 23

Re: Found a better way

bass28 - Would you mind sharing details of how you went about this?

thanks

I'm using Drupal 6, btw. Are you using 6 or 7?

Mon, 05/02/2011 - 11:59

#21 View page

bass28

Offline

Joined: 05/10/2010

Juice: 19

Example

Sure. I've done several but this is one to export a csv file for FedEx using Drupal 6 and UC 2.4

uc_views_bulk_operations.module:

alter function uc_views_bulk_operations_order_operations to include

    'fedex_export_orders' => array(
      'label' => t('Fedex Export'),
      'callback' => 'uc_views_bulk_operations_orders_export_to_fedex',
      'disabled' => TRUE,
    ),  

add new function to the end of the file

function uc_views_bulk_operations_orders_export_to_fedex($orders) {
	require_once('uc_views_bulk_fedexexport.inc');
  $myexport = new FedexExport();
  $myexport->exportToCsv($orders);	
}

create a new file uc_views_bulk_fedexexport.inc with the export class

class FedexExport {
  public function exportToCsv($selectedOrders) {
    $csv = '';
    foreach ($selectedOrders as $orderId) {
      $order = uc_order_load($orderId);
      $shipper_zone = uc_get_zone_code($order->delivery_zone);
      $country = uc_get_country_data(array('country_id' => $order->delivery_country));
      $csv .= "\r";
      $csv .= '"' . str_replace(',',' ',$order->order_id) . '",';
      $csv .= '"' . str_replace(',',' ',$order->delivery_company) . '",';
      $csv .= '"' . str_replace(',',' ',$order->delivery_first_name) . ' ' . str_replace(',',' ',$order->delivery_last_name) . '",';
      $csv .= '"' . str_replace(',',' ',$order->delivery_street1) . '",';
      $csv .= '"' . str_replace(',',' ',$order->delivery_street2) . '",';
      $csv .= '"' . str_replace(',',' ',$order->delivery_city) . '",';
      $csv .= '"' . $shipper_zone . '",';
      $csv .= '"' . $country[0]['country_iso_code_2'] . '",';
      $csv .= '"' . $order->delivery_postal_code . '",';
      $csv .= '"' . str_replace(',',' ',$order->delivery_phone) . '",';
      if (isset($order->shipping_options['address_type'])) {
        if ($order->shipping_options['address_type'] == 'residential') {
          $csv .= '1,';
        }
        else {
          $csv .= '0,';
        }
      }
      else {
        $csv .= '1,';
      }
      $csv .= '"' . $order->primary_email . '"';
    }
    $fedexFile = file_directory_path() . '/fedex.csv';
    
    // Add header row if this is a new file.
    if (!file_exists($fedexFile)) {
   		$csv = 'OrderNbr,Company,Contact,Address1,Address2,City,State,CountryCode,PostalCode,Phone,Residential,EMail' . $csv;
    }
    
    $fh = fopen($fedexFile, 'a+');
    if ($fh) {
      fwrite($fh, $csv);
      fclose($fh);
      file_transfer($fedexFile, array(
          'Content-Type: application/text; charset=utf-8',
          'Content-Length: '. filesize($fedexFile),
          'Content-Disposition: attachment; filename="fedex.csv"',
      ));
    } 
  }
}

Mon, 05/02/2011 - 12:03

#22 View page

roknrod12

Offline

Joined: 02/11/2011

Juice: 23

Re: Example

Wow! Thanks. I've never updated any modules. Thanks for sharing.

Mon, 05/02/2011 - 12:20

#23 View page

roknrod12

Offline

Joined: 02/11/2011

Juice: 23

Re: Example

where can I find uc_views_bulk_operations.module?

Thanks.

Mon, 05/02/2011 - 12:52

#24 View page

bass28

Offline

Joined: 05/10/2010

Juice: 19

Re: Re: Example

http://drupal.org/project/uc_views

Mon, 05/02/2011 - 14:49

#25 View page

roknrod12

Offline

Joined: 02/11/2011

Juice: 23

Thank you

bass28 - thank you. this is great!

	User login
	Log in using OpenID: What is OpenID? Username or e-mail: * Password: * Remember me Log in using OpenID Cancel OpenID login Create new account Request new password

Ubercart