uc_edi for Drupal 6

Thanks very much. We've done some updating for a client project as well and it would be great to get our code together. Can you post what you've done so far to this issue?

Here's what I have now. Everything I've tried appears to work correctly.
I haven't removed all of the comments inserted by Deadwood.
(The archive file contains 3 versions of most files - the original 5.x version (.v5),
the version produced by deadwood (.auto), and the current version.

My next step is to add ways to format dates & times into YYYYMMDD and hhmmss...

Here's what I'm thinking about date/time formatting:
1. it should be contained within uc_edi
2. PHP has a handy date function
3. "created" and "modified" are the keywords of interest, but others might be useful someday

So I've added !date(format)(key),
where format is any valid date format,
and key is resolved like any other keyword, and defaults to the current time().
!date will not support length the way other keywords do, since format can control length.

Here are the changes to uc_edi_generate_order_export to implement this:

Comments welcome!

I notice that delivery_zone and delivery_country are foreign keys, to uc_countries and uc_zones, respectively.

Are the FKs meaningful, or should they be resolved to name or code?
I will probably add keywords - e.g. delivery_country_name, delivery_country_iso2, delivery_country_iso3,

(FYI - SQL script with country codes)

Here is the updated version of uc_edi_generate_order_export(),
which formats dates and resolves zone and country.
Comments welcome!

Subscribing -- very interested in this module for Ubercart 2.x/Drupal 6.

Subscribing -- very interested too

It would be great if you could re-post your updated version of uc_edi that incorporates all of your latest changes. That would make it easier to compare to the current version so it can be reviewed and added as the D6 dev version :).

On another note, I hope to be able to give this more attention later this month -- I have a client with a hacked uc_edi :( that was updated to D6, and it would be great to generalize their customizations so I can contribute them back and merge development efforts here.

Thanks for the feedback and encouragement.
I'm on the road this week, but will try to post the full updated version ASAP.
Could someone please add a new version to the list of options?

A new version list won't show up in the issue queue until a new branch is tagged, which I'd like to do after your code is reviewed ;).

Here's my updated version. Sorry for the delay.
Again, it contains 3 versions of each file to facilitate comparison.
Clif

Subscribing - this could save me a ton of work! I'm going play with the latest version (as of today, 3/11). Thanks!

-dave.

Hi all,
Much appreciative of the work that you've done so far on the D6 version! Myself and a co-worker are going to be taking a rather indepth look at using this module in the upcoming week and hopefully providing you guys with some feedback. Does anyone have a set release date for the "official" D6 version? Even if it is -dev? Ezra-g?
Cheers!

kussmaul,

Thank you for developing this module. I'm using Drupal 6.10 and Ubercart 2.0 beta5.

I have some testing feedback for you.

I was able to successfully install your latest version posted on 3/9 above. I used the current version (not v.5 or auto).

I configured the settings, created the necessary folders on the server, set the order status criteria for export, set the delimeter to comma, and set the cron frequency. I left the default output as is. I created a few test orders. When the cron ran an export file was created and placed in the proper folder on my server. The status of the orders involved were correctly changed. Smooth sailing there.

Catch is, the export file was empty - 0 bytes - as best I can tell. I had 4 test orders in place.

I tried exporting as an .edi and as a .txt

Any suggestions? I'm hoping I've done something incorrectly or missed something simple.

Can I set any ascii comma delimited format for export (e.g. .txt or .csv)? Ultimately I have to send a .txt file to my fulfillment house.

snepa

kussmaul,

my apologies. Exported file contains data. Imported text file into Excel. Currently reviewing. I'll keep you posted.

Thanks,

snepa

Hi snepa,
I'm glad the module is working for you. Thanks for the feedback.
Clif Kussmaul

Clif,

Couple more questions.

1. If I try to archive a file listed on the /admin/store/orders/edi/download page I get an error message stating, "The selected file ../edi_export/ could not be uploaded, because the destination is not properly configured" where "edi_export" is the name of the export directory I created (and "edi_export_archive" is the name of the archive directory). Do you think it is a permission issue with either the directory or the actual file itself? Seems export files get automatically set with a 644 permission when created and directories were set at 755. I tried to change permissions in cpanel to 777 for directories and files to test. Still get same error message when I left click the "archive" word to the right of the listed export file. It seems the module had no problem placing the file in "edi_export" folder just in moving it over to the archive folder. I know I'm missing something simple. Suggestions?

2. After installing the module all US states & territories and Canadian provinces are listed in duplicate in any pull down menu that calls for them. The only other change I can think of that I've made is the install of UC 2.0 beta5 but I think that was before this duplicate issue popped up. Related to uc_edi-6.x-0.1a2 or coincidental?

3. I don't have any php programming experience (last was 15 yrs ago in college - Pascal) but I've been trying to reverse engineer your module in an effort to learn. I can see where you make what I think is the db_query call for customer details from the "uc_orders" table around line 948 under function _export_orders. I expected I'd find a similar db_query command for grabbing product line item data from "uc_order_products", but didn't. Where in the module is the call for line item order details? I'm trying to learn and understand how you communicate with the database.

4. The reason I ask #3 above is because I really need to also grab 1) the product type for each item ordered which is stored in the "node" table under the "type" field and 2) the shipping method which is stored in "uc_order_line_items" table in the "title" field. The former helps me know how to split the line items since I used multiple fulfillment houses based on product type. The primary key for that query would be "nid" cross referenced in the "uc_order_products" table. The latter is necessary to inform each fulfillment house on how to ship (ground, 3-day, 2-day, overnight, etc.). To grab that I'd need to cross reference "order_id" and the "uc_orders" table. Not wanting to be foolish but trying to decide if I could learn enough to build into the module the extraction of those two pieces of data. I could create a vlookup workaround in MS Excel for the product type on the back end but not the shipping method. I have to get the shipping method piece for each order somehow because it is a fresh piece of data in each order. My backup plan is to write queries directly to the mysql tables. I tell you, though, I'd love to make a go of it with this uc_edi for Drupal 6.

5. Outside of my needs and questions above I've changed the order_export and order_product_export patterns to meet my needs. Module works beautifully.

Thanks in advance for any feedback you can provide.

If in my novice optimism I can offer any support let me know.

Regards,

Stephen Nepa

Hi Stephen,
In answer to your questions:
1) I agree that this sounds like a permission problem.

2) I can't think of any reason why uc_edi would duplicate states/territories/provinces,
so the recent installation of ubercart 2.0b5 seems more likely to cause trouble.

3) Most of this module was written by other people; I just updated it for drupal6 and uc2.
Compare uc_edi.module.auto and uc_edi.module to see what I changed.
It's possible that the module uses code from other ubercart modules to get line item info.

4) These seem like reasonable things to add to the module, and not too difficult.
See comment 5 for examples of how I inserted other info.
Can you (or others) think of other data that should be available?
I'd rather add a bunch of things at one time.
I'm not sure how quickly I'll be able to do this on my own, however.

5) Thanks for the feedback and encouragement!

Clif

Hi --

Help! I'm getting directory invalid every time I try to extract the latest version :/ I'm using Winzip under Vista.

Thanks -

Missy.

Hi Missy,
I run Drupal under Linux, so I'm not sure how much I can help.
I have had trouble in Vista with WinZip because of Vista's paranoid security.
You might try unzipping to a different location (e.g. a temp directory),
and then moving the extracted files into your Drupal directory.
Clif

Our fulfillment house wants a non-delimited format where the values are separated by *s only. Like so:

N3*5233 N. Clark St.~N4*Chicago*IL*60640*840~N1*SD*Amy Razeghi~N3*3856 McDonald Ave.*Apt. 1F~

The ~ is a line break (for them - not on our docuemnt) and the code after the tilde is their identifier.

How would you suggest we use the drupal field keywords. Like this?: *!delivery_street1*!delivery_city*!delivery_postal_code* ....etc.

I tried adding the commas like so: *,!delivery_street1(20),*,!delivery_city(15),*,!delivery_postal_code(9),*,

This gave me the data but was tab delimited. And trying to use the !date like so:
!date(Ymd),*,!date(Hi),!_,*~,!_,

Got me nowhere.

I hope I am making sense. Please help. Anyone. If you can.

Thanks for your time.

Copper IT

Hi Copper IT,
I think the commas are necessary to clearly separate the various fields,
but in "Store Administration / Configuration / EDI Import/Export"
you can set the "Export pattern delimiter" to be "nothing" instead of "tab",
which should give you most of what you need.

In some cases it makes sense to write separate code (outside of the module)
to customize the export data. For example, for my order fulfillment system
all of the order lines need to be in one file and all of the item lines in a separate file -
so I have a PHP program that reads the file and copies each line into the correct file.

Clif

ezra-g and jacobroufa,
Any update on your work with this module?
Clif

Subscribing. Very interested in this module. We're working on importing/exporting data between ubercart and PostBooks.

The best thing people can do to advance a drupal 6 release is to test the code in this issue. Thanks!

Thanks for your quick response. The notes I gave you were my attempts to manipulate the Order Export Pattern. I think I got the tabbed problem solved but how do I get the date and time to be yyyymmdd and hhmm like you suggested above. I tired the following:

GS*PO*20B0877*1697978*,!date(Ymd),*,!date(Hi),*1*X*4010~,!_,
*,!delivery_street1(),*,!delivery_city(),*,!_,

and I get this:

GS*PO*20B0877*1697978*!date(Ymd)*!date(Hi)*1*X*4010~
*5577 Bodega Ave.*Sebastopol*

Any suggestions?

Thanks again for your time.

Copper IT

Hi Copper IT,
The date function (see code above) requires two arguments - the format, and the date field to be formatted, e.g.:

!date(YmdHis)(!created)

Clif

Can I get it to read *yyyymmdd*hhmm* ?

I would think that would require something like:

P,!date(Ymd)(!created),*,!date(Hi)(!created),*,

but you'll have to try it to find out.
Clif

I've been testing this module and I find that it works very well for Drupal 6.10, Ubercart 2b5.

I have been creating an export file for import into PostBooks, an open source accounting package, and I've got a template that works pretty well, although I still need to get individual sales lines to import.

I'm having an issue because PostBooks expects to receive a value for Scheduled Date for each product line of the order. But when I try to insert a date into that section of the template, it will not execute the date function. For example, if I enter this as the Order product export pattern:

, !_,
,!order_id,, !_,
,!#,, !_,
,!model,, !_,
,!qty,, !_,
,!price,, !_,
,!date(Y-m-d)(),, !_,

It exports this to the file:

9
2
00002
1
25.000
!date(Y-m-d)()

Why does the date function not execute?

Thanks.

bixwilson,
Please read the previous few comments for help with the date function.
It's often a good idea to review previous postings before posting new questions.
Clif

Actually, I had read the previous comments pretty carefully. They provided all the information I needed for inserting a date in the order, but not in each product line.

The confusion seems to be the difference between displaying a date in the
function uc_edi_order_values() vs. uc_edi_order_product_values()

uc_edi_order_values() has many values predefined, including a !date value, but order_product_values does not. To get the result I needed, I had to add a couple new value definitions to the product_values function. In particular, the format I'm exporting to requires a "scheduled_date" value for each product line, which I've set to be 24 hours from the order date. See below:

// Returns a list of replacement values for product export patterns.
function uc_edi_order_product_values($product) {
$product = (array) $product;

foreach (array_keys($product) as $key) {
if (!is_array($product[$key]) && !is_object($product[$key])) {
$values['!'. $key] = $product[$key];
}
}

$values['!batch_id'] = variable_get('uc_edi_order_next_batch_id', 1000);
$values['!#'] = variable_get('uc_edi_order_product_line_number', 1);
variable_set('uc_edi_order_product_line_number', $values['!#'] + 1);

$values['!spacer'] = ' ';

// Allows insertion of order date, e.g. '2009-04-09'

$values['!created_date'] = date('Y-m-d');

// scheduled date
$values['!scheduled_date'] = date('Y-m-d', time() + (60 * 60 * 24));

return $values;
}

I'd like to suggest that this snippet be added to the core for this module, to assist anyone who needs dates in the product lines. I've posted an updated version of the module, based on uc_edi-6.x-0.1a2.tgz, with the values defined for !created_date and !scheduled_date in the product_values function.

I've gone through the code and removed all the remaining TODO items left over from deadwood and have also tested it pretty extensively. And since we're working on making it better I fixed the bug where trying to archive an export file (via the web interface) resulted in an error if the directory was outside the drupal root. Additionally, I've added a few new features:

• New operator for import "on" which creates an order comment and notifies the user of it via email.
• !user_comment field for export which output the User Comment from checkout (if present).
• !uc_shipping_title which outputs the description of the shipping method
• !uc_shipping_weight which outputs the total weight of the items for the order
• A new page "EDI file upload" which allows users with appropriate permissions to upload an import file.
• If "comma" is chosen for the import delimiter, it reads regular CSV files with quotes as well as just plain comma delimited files.

These are in addition to the new date features that were added above.

Subscribing.

I'm uploading a new version of the uc_edi module that works with Drupal 6.

I downloaded the latest version that jrust posted above (#33) and added two new fields, an xmlprefix and xmlsuffix. I needed these optional fields in order to be able to export a valid xml file that contained multiple orders.

The new file is called uc_edi-6.x.a5.zip

@ezra-g: Since you are listed as the maintainer of this project, I'd like to ask your help to get this latest file posted as an "official" release for D6 on the uc_edi project page. I've tested the file and it works--it outputs a valid xml that I am able to import into PostBooks, my erp software.

If you have any questions, please contact me.

Thanks.

Here's the file in proper tar.gz form.

@bixwilson: Thanks for posting an updated version of this module! I plan to review this and the other rewrites in this issue in the next few weeks.

Hey folks,

I know the edi issue was initially developed for fulfillment houses back in 2007; can the current version now be used to export order and shipping information for use in an ERP system like MAS-90?

MAS-90 has an eCommerce ("eBusiness") module, but not sure that it's something the client wants to implement -- the client seems to want to get a store up now, and add the ERP stuff later. Would love to use the EDI module for both exporting and importing order and shipping / stock level changes, so that integration with a brick-and-mortar store's inventory and accounting could be balanced.

How close is this EDI to being able to do such a thing...?

Grey Hawk, et al.

I have been continuing to modify the uc_edi module, specifically with the intention of getting it able to export a file that can be imported into our company's ERP product, xTuple.

xTuple PostBooks Edition has an XML Import feature that allows it to import customer, order and payment information from an XML file. The problem with uc_edi is that it didn't allow for a prefix and suffix on the document, so you couldn't put in a top level xml node. We've added these fields to the module so you can now open and close the top node that encloses all the orders.

The latest module is attached. We're distributing this on our site, but I'd love for it, or something like it, to become the official distribution of uc_edi, so that we can benefit from others' contributions as well. You can find information about configuring it for xTuple here: http://www.xtuple.org/ubercart-integration

It looks like you have made tremendous progress with the edi export feature, I do have a question I hope someone can answer. How can I export options? I need to export say a shirt g2000 with the option of RED in Size Small - What is the easiest way to do this?

Subscribing - very interested in this module for Ubercart 2.x/Drupal 6.