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?

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.

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 ;).

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

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 --

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

Thanks -

Missy.

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

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

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

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,, !_,

It exports this to the file:

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

Why does the date function not execute?

Thanks.

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.

@pondster Best way to export attributes is to go to the Adjustments tab on your Edit product page and make a different SKU for each option. So your shirt g2000 RED Small could look like SKU: g2000-Red-Small

See attached screenshot.

bixwilson -- I've been working to make some time/space to install and work with xtuple. It looks quite promising! Hope to ping you back before Christmas (as opposed to 4 months later, like this note is in relation to your Aug 4 note).

Thank you!

XML doesn't export. I've setup the uc_edi-6.z.a5.tar_.gz version of this module and have configured it to export as XML. The only thing that exports is the 2 letter state abbreviation with line breaks, in this fashion:

UT
CA
NY

XML Prefix:

<?xml version="1.0" ?>
<Print mlns="http://stamps.com/xml/namespace/2009/8/Client/BatchProcessingV1">

XML Suffix:

</Print>

Order export pattern:

<Item>!_
		<OrderDate>today</OrderDate>!_
		<OrderID>!order_id(6)</OrderID>!_
		<PrintedMemo>!user_comment || !products
		</PrintedMemo>!_
		<Services>!_
			<DeliveryConfirmation />!_ 
		</Services>!_
	<Recipient>!_
		<AddressFields>!_
			<FirstName>!delivery_first_name(50)</FirstName>!_
			<LastName>!delivery_last_name(50)</LastName>!_
			<MultilineAddress>!_
				<Line>!delivery_street1(50)</Line>!_
				<Line>!delivery_street2(50)</Line>!_
			</MultilineAddress>!_
			<City>!delivery_city(30)</City>!_
			<State>!delivery_zone_code(2)</State>!_
			<ZIP>!delivery_postal_code(13)</ZIP>!_
			<Country>!delivery_country_name(30)</Country>!_
			<OrderedEmailAddresses>!_
				<Address>!primary_email(41)</Address>!_
			</OrderedEmailAddresses>!_
			<OrderedPhoneNumbers>!_
				<Number>!delivery_phone(15)</Number>!_
			</OrderedPhoneNumbers>!_
		</AddressFields>!_
	</Recipient>!_
	<MailClass>first class</MailClass>!_
	<Mailpiece>package</Mailpiece>!_
	<WeightOz>14</WeightOz>!_
	<PackageDimensionSet>A-type Widget Box</PackageDimensionSet>!_
	</Item>!_

EDIT: Adding a comma after the export patterns exports the actual export pattern but not actual order info.

XML Import?

On the settings page at: /admin/store/settings/edi its not possible to change the delimiter to none like that found in the export settings. How do we import via XML?

Comment #44 Solved, I had to add a preceding comma to the export settings:

	<Item>
		<OrderDate>today</OrderDate>
		<OrderID>,!order_id,</OrderID>
		<PrintedMemo>,!user_comment,</PrintedMemo>
		<PrintedMemo2>,!products,</PrintedMemo2>
		<Services>
			<DeliveryConfirmation />
		</Services>
	<Recipient>
		<AddressFields>
			<FirstName>,!delivery_first_name,</FirstName>
			<LastName>,!delivery_last_name,</LastName>
			<MultilineAddress>
				<Line>,!delivery_street1,</Line>
				<Line>,!delivery_street2,</Line>
			</MultilineAddress>
			<City>,!delivery_city,</City>
			<State>,!delivery_zone_code,</State>
			<ZIP>,!delivery_postal_code,</ZIP>
			<Country>,!delivery_country_name,</Country>
			<OrderedEmailAddresses>
				<Address>,!primary_email,</Address>
			</OrderedEmailAddresses>
			<OrderedPhoneNumbers>
				<Number>,!delivery_phone,</Number>
			</OrderedPhoneNumbers>
		</AddressFields>
	</Recipient>
	<MailClass>first class</MailClass>
	<Mailpiece>package</Mailpiece>
	<WeightOz>14</WeightOz>
	<PackageDimensionSet>A-type Widget Box</PackageDimensionSet>
	</Item>

FYI I needed orders to export at the same time every day so I changed the module in this manner (starting around line 105):

  // Decide if it's time to export orders.
/*Original export
  if (variable_get('uc_edi_order_export_freq', '1 day')) {
    $time = strtotime(variable_get('uc_edi_order_export_freq', '1 day') .' ago');
    if (variable_get('uc_edi_last_order_export', 0) < $time) {
      _export_orders();
    }
  }
}
*/

//This will export everytime cron.php is run from 9:00am to 9:59am
	$date = date('H');
	if($date == '09'){
		_export_orders();
	}

My cron.php runs at :05 after the hour.

Very interested. Subscribing.

Just an idea - how about putting the D6 code in a -dev branch and handling bugs and issues in different posts connected to that as opposed to all in this one HUGE thread? :) Would make it so much nicer and we could all easily see what the last version is :)

Anyway, very interested, subscribing.

would subscribe if only I knew how to?

Can't find any subscribelink on this page (or any page)?

Frode Løtvedt
Norway

Subbing.

THink there's a bug in EDI when a mix of shippable and non shippable products are purchased.

The EDI export exports the non shippable products as well?

Tested this out when we mixed our physical products and digital downloads

Cheers :)
Ken

Is there anyone still working on this module?
I have been able to implement DDate to allow customers to select a requested delivery date,
and I have been working with code to export a single option (like size) .

My problem right now that I need to get fixed ASAP is that our industry specific ERP software is a pain and I need to export ALL the options for size in order for the correct quantities to import correctly,

So I can export the following as Size Small with a Quantity of 5

Size S: 5

But I need the following, ALL sizes available

Size S: 3
Size M: 4
Size L: 2
Size XL: 24
Size XXL:
Size XXXL:

Any help would be greatly appreciated.

Hello!

I'm trying to set up the info that is exported and need some help, as I am not a programmer.

Is there a way to add column headers (like username, product name, SKU, etc...) to the export file?

Is there a way to export separate files for each order? I really need this one.

Is there an instruction or list of available order field keywords? I found some at http://www.xtuple.org/ubercart-integration Maybe there is more?

Thank you!

I tested Jason's general purpose #33 on Drupal 6.x and it works flawlessly!
uc_edi is a great module... lets get this checked into uc_edi page ASAP!

Cliff or kussmaul or... are you updating uc_edi? Or shall I?

Note: I added a feature to #33 to support export of product title, and ANY product custom attributes/options.
With this can now simply add '!title' and '!attributes' to the product export pattern.
The code change is backward compatible (optional to utilize... won't get in the way of previous implementations).

As soon as this is published, I'd love to see the FTP feature noted at http://drupal.org/node/559642 integrated into the full version.

I'm using this in production. One problem we're having is that commas are allowed to be posted in the order fields and are causing problems with our edi import process. How hard would it be to have these filtered out?

Thanks.

subscribing

Subscribing, I really hope a stable module will be announced soon. Or am I too hopefull?

subscribing

I like the ideas many of you have posted to this issue, but if we are to be successfull in creating a Drupal 6.x release, we need to come up with a road map. Below is a suggested road map based on issues and feature request both raised in this issue and that I have found. I have completed the goals laid out for 6.x-1.0 and have attached an archive to this comment. The next step is to get this commited in a 6.x-1.x branch. After that we can begin working through the road map. Any feedback/suggestions welcome.

ezra-g, rszrama,
Please give me co-maintainer access for this project.

Ubercart EDI 6.x Road Map

Goals for 6.x-1.0

- Port uc_edi 5.x-1.x-dev to Drupal 6
- Provide upgrade path from 5.x to 6.x
- Code Cleanup per http://drupal.org/coding-standards
- The file_move function doesn't seem to handle files outside of the drupal root, so changing this to use PHP's rename() function.

Goals for 6.x-1.1 (Bug Fix and Ubercart 2.x compatability update)

- Add error if Conditional Actions do not include setting the order status to the value set in the 'uc_edi_order_export_status' variable.
- Fix order status being updated when export fails due to file creation failure.
- Fix message on admin/store/orders/edi that file was created when file creation fails.
- Fix any other bugs reported in issues.
- Create a predicate for updating the status of an order after exporting.
- Replace the uc_edi_get_delimiter() function with a string, no need for function call overhead.
- Replace the uc_edi_default_order_import_rules() function with a string, no need for function call overhead.
- Change the $order parameter of the uc_edi_generate_order_export() function from an order object to an order_id for efficiency. No need to pass an entire object when an int will do.
- Replace the uc_edi_default_order_export_pattern() function with a string, no need for function call overhead.
- Replace the uc_edi_default_order_product_export_pattern() function with a string, no need for function call overhead.
- Replace 'uc_edi_order_export_product_line_number' Drupal variable with php variable.
- Replace the uc_edi_admin_help() function with hook_help().
- Rename all 'import' related variables, functions, comments, etc. to 'update' since that is what it actualy does; Updates an existing order.
- Make comma delimited safe by enclosing fields in double quotes ("").
- Add README.txt
- Add CHANGELOG.txt
- Add TODO.txt

Goals for 6.x-1.2 (features to discuss/implement)

- Allow date format specification.
- Add additional country formats (name, iso2, iso3, etc.).
- Add additional product pattners (title, type, etc.).
- Add shipping patterns (method, title, weight, etc.).
- Add date patterns (created, modified, etc.).
- Add a user comment pattern.
- Add attribute and option patterns?
- Make order patterns accessible by the product pattern function?
- Allow notification of new comments added to an order on import.
- Add export prefix and suffix fields.
- Export file on order creation.
- Add list of available patterns.
- Add FTP functionality.

Goals for 6.x-2.0 & 7.x-2.0

- Port to Drupal 7.
- Replace
- Create a hook other modules can implement for providing replacement patterns. Hook should except the object name as a parameter (order, user, etc.).
- Use tokens for replacement patterns? Might conflict with #1. Probably need to choose one or the other.
- jQuery UI for building export patterns (drag & drop fields).
- UI for creating field translations?

Cheers,

Antoine

@AntoineSolutions, I've given you all maintainer permissions. I apologize that my lack of attention here has delayed progress. Thanks so much for stepping up!

The code from comment #63 has been committed to HEAD. I have created a new release (6.x-1.x-dev) from HEAD but It can take up to 12 hours for Drupal to build the release. As soon as the release is built, I will make it available on the project page. If a particular roadmap item is important to you, please create an issue against the new 6.x-1.x-dev branch and tag it with "roadmap".

I am marking this issue as fixed!

Cheers,

Antoine

Although this thread is closed, I think replying hear is a quick way to alert everyone who was interested in an Ubercart EDI Drupal 6 release of the current project status.

Most posts in this thread are related to making additional fields available to the export patterns. I am happy to announce that I have just finished the first draft of a patch that utilizes the Token module for replacement tokens AND replaces the export patterns with export template files:
#957384: Use token hooks for replacement tokens and use template files instead of pattern variables..

This grants complete control over exported data allowing for inclusion any data associated with an order and the user. Included with this new patch is a default template file that demonstrates how to use headers and footers and export simple variables.

Another cool feature is that a new hook is available that allows other modules to define templates. This would allow for the creation of say a uc_edi_quickbooks module that could define template files for QuickBooks 2008, 2009, 2010, etc.

I am eager to get this module updated to Drupal 6 and quickly into Drupal 7, but there are currently 4 other patches in addition to this one that need tested and reviewed before a 6.x-1.0 release can be made. I would appreciate any testing and reviews from those who are available.

Cheers,

Antoine

Yousername.... The Xtuple EDI does export shipping and order info.... including SKU for each product in the ubercart store. The only issue I personally seem to be having is it is not filling out the sales tax information in the sales orders it generates after an import. I don't know if I'm somehow reading everything wrong, or if this function is in fact broken in some way. Your post is over 6 months old but I don't think anyone answered it. You just have to make sure that the SKU's match one another in Xtuple, and the Ubercart catalog. it's pretty slick barring the issue I can't seem to get resolved at the moment.

6.x-1.0-alpha1 is now available for download.

Cheers,

Antoine