UPS WORLDSHIP INTEGRATION - AMAZING

[saho]

I just learned how to do the most awesome thing. Not sure if everyone is aware of this.

 

We had a tech come from UPS and show us how to set up the mysql odbc driver and through worldship we are now able to enter an order number and the fields now fill themselves out. You can even quantum view notify people automatically through their email address used to order through oscommerce.

 

A huge timesaver.... If anyone is interested I can try to direct them how to do it or they can get in touch with UPS and request to have a tech come out and show them how to do it.

[prayhard87]

Please tell me how to do this. I would really apreciate it!

[TampaTech]

This is exactly what I was looking for !! Please forward/post any ODBC settings information you may have for the MySQL ODBC setup for UPS WorldShip.

[TampaTech]

I have been trying to get UPS WorldShip to pull information from a remote MySQL database, but have been running into problems.

 

I have two MySQL database sitting up on shared hosted servers (one is on LunarPages and one is on IPowerWeb). Both of those web hosting companies do not support an ODBC DSN for remote connections.

 

MY QUESTION: Does anyone have UPS WorldShip set up to pull information from a remote MySQL server on one of these service providers, or similar provider that does not support DSNs? I hope there is a way to do it, but not sure at this point. I called UPS WorldShip technical support and they said they required a DSN in the ODBC Administrator for their application to work. I've tried connecting to the remote MySQL database through the ODBC administration panel (using the MySQL driver), but it didn't work. I may be out of luck, but wanted to see if someone hacked around this issue.......or if I am just missing something.

 

MANY THANKS IN ADVANCE.

[ronr1999]

I have been trying to get UPS WorldShip to pull information from a remote MySQL database, but have been running into problems.

 

I have two MySQL database sitting up on shared hosted servers (one is on LunarPages and one is on IPowerWeb). Both of those web hosting companies do not support an ODBC DSN for remote connections.

 

 

You can connect via MyODBC to LunarPages MySQL. In cPanel - MySql Databases you have to add your IP address to the list of ACCESS HOSTS. This works for me...

 

But.. I can't get WorldShip to see my Table Fields. It will show the Tables but no fields appear so they can be linked/connected for import. I'm working with UPS TS to fix this. If anyone's seen this please contact me.

 

RonR

[RL2000]

You can connect via MyODBC to LunarPages MySQL. In cPanel - MySql Databases you have to add your IP address to the list of ACCESS HOSTS. This works for me...

 

But.. I can't get WorldShip to see my Table Fields. It will show the Tables but no fields appear so they can be linked/connected for import. I'm working with UPS TS to fix this. If anyone's seen this please contact me.

 

RonR

 

The only way to make it work 100% of the time is using mysql odbc through access, and letting ups connect to the access database.

 

You have to use queries in the access database, and NOT linked tables, that won't work.

 

Hope that helps.

 

The only thing I don't have working yet, is the GOODS section og the US worldship, the european everything works(as we don't need the GOODS section).

[djmonkey1]

You can connect via MyODBC to LunarPages MySQL. In cPanel - MySql Databases you have to add your IP address to the list of ACCESS HOSTS. This works for me...

 

But.. I can't get WorldShip to see my Table Fields. It will show the Tables but no fields appear so they can be linked/connected for import. I'm working with UPS TS to fix this. If anyone's seen this please contact me.

 

RonR

 

I am having this exact same problem, even had UPS come in to help out and stumped them as well. The best answer I got from them was that they don't support MySQL (thanks for the help, guys). If anyone has any input on this one it would be greatly appreciated.

 

Cheers

Stew

[djmonkey1]

I have gotten this work thanks to a tip from RonR.

 

On a Windows XP box I installed the MyODBC version 3.51.06 driver (the version number is important- 3.51.12 doesn't work) available here:

 

http://sunsite.mff.cuni.cz/MIRRORS/ftp.mys...yodbc-3.51.html

 

I configured it according to the instructions available here:

 

http://dev.mysql.com/doc/refman/5.0/en/odbc-connector.html

 

Also, I had to contact my web host and have them set up a username and password that can be used for remote connections such as this.

 

At this point I was able to use the Worldship Online Connection menu to finish the configuration. All I have to type in is the order number, and all the delivery information is filled out automatically.

 

This a HUGE timesaver and is highly recommended for anyone who uses Worldship.

 

Cheers

Stew

[djmonkey1]

p.s. According the UPS it is also possible to use Worldship to write information to the database- I have not set this up yet but intend to use it to allow our customers to track their package(s) directly from our website.

[RL2000]

Look here: http://www.oscommerce.com/forums/index.php?showtopic=192587&hl=

[ronr1999]

Just noticed in UPS Worldship something called "Hands-off Shipping". This is a routine within Worldship that polls a database for new orders (it appears that you can set the polling time). I looked at the Import mapping but can't see a way to get this to work with osC. It would have to look at the orders_status_history table to find pending orders and then determine the item(s) to ship and weight/size, etc.

 

This would be very cool to get working.

 

RonR

[djmonkey1]

Just noticed in UPS Worldship something called "Hands-off Shipping". This is a routine within Worldship that polls a database for new orders (it appears that you can set the polling time). I looked at the Import mapping but can't see a way to get this to work with osC. It would have to look at the orders_status_history table to find pending orders and then determine the item(s) to ship and weight/size, etc.

 

This would be very cool to get working.

 

RonR

 

That does sound cool- I'm still trying to find a way to write the tracking number to the database automatically.... :)

[ronr1999]

That does sound cool- I'm still trying to find a way to write the tracking number to the database automatically.... :)

 

I have done this. I added a field to mySQL - orders_status_history named tracking. I created a Worldship export map (again using mySQL odbc - see above) using the Worldship mapping as follows:

 

Shipment Information::Shipment ID -> orders_status_history::tracking

Shipment Information::Pick Up Date -> orders_status_history::date_added

Ship To::Customer ID -> orders_status_history::orders_id

 

Then in Worldship click on: UPS OnLine Connect - Automatic Export after Processing Shipment and select more to choose your export map.

 

Then hack up the orders.php to have this tracking field appear.

 

I didn't bother making this available for my customers to see. I just have it available to copy and paste into orders comments in Admin/Orders/Details then Update and it emails to my customers.

 

Hope this helps...

 

RonR

[djmonkey1]

I have done this. I added a field to mySQL - orders_status_history named tracking. I created a Worldship export map (again using mySQL odbc - see above) using the Worldship mapping as follows:

 

Shipment Information::Shipment ID -> orders_status_history::tracking

Shipment Information::Pick Up Date -> orders_status_history::date_added

Ship To::Customer ID -> orders_status_history::orders_id

 

Then in Worldship click on: UPS OnLine Connect - Automatic Export after Processing Shipment and select more to choose your export map.

 

Then hack up the orders.php to have this tracking field appear.

 

I didn't bother making this available for my customers to see. I just have it available to copy and paste into orders comments in Admin/Orders/Details then Update and it emails to my customers.

 

Hope this helps...

 

RonR

 

WHAT?????

 

My UPS tech guy told me Worldship couldn't do this!

 

In my table orders I have the field ups_track_num.

 

So my map would be

 

Ship To::Customer ID -> orders::orders_id

Shipment Information::Shipment ID -> orders::ups_track_num

 

and when I run the End off Day it will write the tracking number to the appropriate field in the database?

 

This sounds too good to be true.

[ronr1999]

WHAT?????

 

My UPS tech guy told me Worldship couldn't do this!

 

In my table orders I have the field ups_track_num.

 

So my map would be

 

Ship To::Customer ID -> orders::orders_id

Shipment Information::Shipment ID -> orders::ups_track_num

 

and when I run the End off Day it will write the tracking number to the appropriate field in the database?

 

This sounds too good to be true.

 

 

It absolutely works !

 

Remember to turn on the auto update:

 

Menu - UPS Online Connect | Automatic Export either 'after each shipment' or at 'end of day' "

 

::

 

How are you going to send the track_num to your customers ?

 

 

RonR

[djmonkey1]

It absolutely works !

 

Remember to turn on the auto update:

 

Menu - UPS Online Connect | Automatic Export either 'after each shipment' or at 'end of day' "

 

::

 

How are you going to send the track_num to your customers ?

 

 

RonR

 

I tried this and got the following error:

 

ODBC ERROR: State = 23000 Error = [MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Duplicate entry '558' for key 1

 

My UPS guy told me that in order for this to work correctly there would have to be a table set up just for this purpose, because Worldship was not capable of writing back to the database using the logic of matching up the orders_id to the Customer ID and then writing the tracking number into the field for tracking number that corresponded to that orders_id.

 

But it works for you? Worldship knows to put the tracking number in a certain place in your database, and it corresponds to the orders_id for that shipment?

[ronr1999]

Ok, I see your point. But...

 

 

When I ship an order I go into the Order Details screen and update the status to Shipped and send a message (via status UPDATE) to my customer with the UPS tracking number and delivery date. I'm only making it easy on myself to have the tracking number easily available.

 

 

The order_status_history table will auto increment when Worldship adds the tracking number. Then you can see it in the orders - details screen. Here's a snapshot:

 

 

This works for my purpose...

 

One comment, I'm only shipping less than a handfull of items a day (at +$500 each) so I can spend the time hand entering the status updates.

 

I use Quantum View to give me the scheduled delivery date. This date isn't provided via Worldship.

 

 

 

RonR

[djmonkey1]

I think I should just do what the UPS tech guy recommended, which is make a new table just for this purpose. It would only need two fields, one for orders_id and the other for the UPS tracking number. Once the data is in the database it's just a matter of pointing the website to it.

 

As for how to get the info to the customer, I use Quantum View Notify to automatically send an email with tracking info to the customer, and by using the UPS XML tracking contribution, when a customer logs in to their account they can track their packages directly from their Account History page. The one thing holding this up is that I need to be able to automatically fill in the tracking number....

[sirfergy]

Quick question. How did you setup the mappings to import an order?

[djmonkey1]

Quick question. How did you setup the mappings to import an order?

 

UPS Online Connect->

 

Create/Edit Map->

 

Select New Map, Import from ODBC database, and Shipment in the dropdown menu->

 

Type in a unique name for this map and click on Create->

 

Select the ODBC source, enter your username and password->

 

You should now see the mapping window with two columns, one for ODBC tables and another for Worldship fields->

 

Select one from each and hit the Connect button->

 

Continue until every field that you would like to map has been mapped->

 

Make sure you define a primary key and hit ok. Your map should now be listed as an existing map.

[djmonkey1]

Quick question. How did you setup the mappings to import an order?

 

UPS Online Connect->

 

Create/Edit Map->

 

Select New Map, Import from ODBC database, and Shipment in the dropdown menu->

 

Type in a unique name for this map and click on Create->

 

Select the ODBC source, enter your username and password->

 

You should now see the mapping window with two columns, one for ODBC tables and another for Worldship fields->

 

Select one from each and hit the Connect button->

 

Continue until every field that you would like to map has been mapped->

 

Make sure you define a primary key and hit ok. Your map should now be listed as an existing map.

[sirfergy]

I was thinking more of the exact mapping. :)

 

I had tried to do it myself, but it doesn't seem to work right. It isn't pulling out all the information I need, and I'm not sure what in the mapping I'm messing up.

[hydro]

UPS Online Connect->

 

Create/Edit Map->

 

Select New Map, Import from ODBC database, and Shipment in the dropdown menu->

 

Type in a unique name for this map and click on Create->

 

Select the ODBC source, enter your username and password->

 

You should now see the mapping window with two columns, one for ODBC tables and another for Worldship fields->

 

Select one from each and hit the Connect button->

 

Continue until every field that you would like to map has been mapped->

 

Make sure you define a primary key and hit ok. Your map should now be listed as an existing map.

 

 

Do you have the exact mapping? Anyone who have the exact mapping, can you please post it. Thanks.

[elementip]

Do you have the exact mapping? Anyone who have the exact mapping, can you please post it. Thanks.

 

I agree, this would be most helpful. I spent about 2 hours experimenting with it, and I got nothing but import errors when I tried to import the orders into worldship. I assume I just had the mappings wrong.

 

Can anyone offer their assistance?

Thanks!

[dr_lucas]

I have successfully made worldship export data to my oscommerce shop, and made the shop automatically update the orders statuses and send instant emails to my customers.

This is how:

 

I have created an export ODBC map that export my Master Tracking number and Order number into a temporary table in my MySQL db called orders_ups.

orders_ups has 2 columns: orders_id (if your orders numbers have more than 4 digits its type must be set to BIGINT, not INT, due to Worldship 4 digits INT limit, a bug) and tarcking_number

so all the orders tracking number and order IDs are updated in this table during End Of Day

Then I created a script that when running it, it matches the data in the temp table and the orders table and where match - it updates

- Order's status to shipped

- Tracking numbers updated in the orders table (I already had a tracking_no field before).

- Emails are sent to the customers with shipping information and tracking number.

 

Here is the script, it can be easily modified to fit your needs or even to be ran by a Cron job at a set time:

<?php
/*
 $Id: UPSship.php,v 1 2009/08/15 17:52:23 dr_lucas Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2009 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');

 $current_boxes = DIR_FS_ADMIN . DIR_WS_BOXES;

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF" onload="SetFocus();">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
</table></td>
<!-- body_text //-->	
<td width="100%" valign="top">
  <table border="0" width="100%" cellspacing="0" cellpadding="2">	 
  <tr>
	<td width="100%"><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
		<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
	  </tr>
	</table></td>
  </tr>
  <tr>
	<td>
		<table border="0" width="100%" cellspacing="0" cellpadding="2" align="center">
		  <tr class="dataTableHeadingRow">
			<td class="dataTableHeadingContent"><?php echo NAVBAR_TITLE; ?></td>
		  </tr>
		  <tr class="dataTableRow">
			<td align="left" class="dataTableContent">


<?php

define('EMAIL_SEPARATOR', '------------------------------------------------------');
define('EMAIL_TEXT_SUBJECT', 'Order Update');
define('EMAIL_TEXT_ORDER_NUMBER', 'Order Number:');
define('EMAIL_TEXT_INVOICE_URL', 'Detailed Invoice:');
define('EMAIL_TEXT_DATE_ORDERED', 'Date Ordered:');
define('EMAIL_TEXT_STATUS_UPDATE', 'Your order has been updated to the following status.' . "\n\n" . 'New status: %s' . "\n\n" . 'Please reply to this email if you have any questions.' . "\n");
define('EMAIL_TEXT_COMMENTS_UPDATE', 'The comments for your order are' . "\n\n%s\n\n");
define('EMAIL_TEXT_TRACKING_NO', 'Tracking Number: ');
define('EMAIL_TEXT_SHIP_DATE', 'Shipped On: ');

 require(DIR_WS_CLASSES . 'currencies.php');
 include(DIR_WS_CLASSES . 'order.php');

$i=0;

$ups_query = tep_db_query("select orders_id, tracking_number from orders_ups");
  while ($ups = tep_db_fetch_array($ups_query)){
  $tracking_no=$ups['tracking_number'];
  $oID=$ups['orders_id'];
		tep_db_query("update orders set orders_status='2', tracking_no = '" . $tracking_no . "', carrier_id = '2', last_modified = date_add(now(), INTERVAL " . TIME_ZONE_OFFSET . " HOUR) where orders_id = '" . $oID . "'");
		tep_db_query("insert into orders_status_history (orders_id, orders_status_id, date_added, customer_notified) values ('" . (int)$oID . "', '2', date_add(now(), INTERVAL " . TIME_ZONE_OFFSET . " HOUR), '1')");

		$check_status_query = tep_db_query("select customers_name, customers_email_address, orders_status, date_purchased, ipaddy, ipisp, tracking_no, real_shipping_cost, bogus_value from " . TABLE_ORDERS . " where orders_id = '" . $oID . "'");
  $check_status = tep_db_fetch_array($check_status_query);
		$today = date("F j, Y"); 
		$email = 'Dear Customer,<br><br> Your Order has beeen shipped.' . $orders_status_array[$status] . "\n" . 
EMAIL_SEPARATOR . "\n" . 
EMAIL_TEXT_ORDER_NUMBER . ' ' . $oID . "\n" . EMAIL_TEXT_INVOICE_URL . ' ' . tep_catalog_href_link(FILENAME_CATALOG_ACCOUNT_HISTORY_INFO, 'order_id=' . $oID, 'SSL') . "\n" . EMAIL_TEXT_DATE_ORDERED . ' ' . tep_date_long($check_status['date_purchased']) . "\n\n" . EMAIL_TEXT_SHIP_DATE . $today . "\n\n" . EMAIL_TEXT_TRACKING_NO . $tracking_no . "\n" . '

UPS Tracking homepage: 
<a href="http://www.ups.com/tracking/tracking.html">http://www.ups.com/tracking/tracking.html</a><br>';

tep_mail($check_status['customers_name'], $check_status['customers_email_address'], EMAIL_TEXT_SUBJECT . '- Tracking Number ' . $tracking_no . ' (UPS)' , $email, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);

echo 'Order Updated: ' . $oID . '<br>';
$i++;
  }
  tep_db_query("TRUNCATE TABLE orders_ups");
  echo 'Total orders updated: ' . $i . '<br>';
  echo 'DONE!'; 
?>

		 </td>
	   </tr>

		</table>
	</td>
  </tr>
</table></td>  
<!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

 

 

P.S. I know the code isn't very clean, but I have created this in 15 minutes and just made sure it does what I need, you may clean it, revise it, create a contribution from it or do whatever you enjoy doing ;)