CodeIgniter + UPS Worldship

This is a post I have been to get around to for quite awhile. At work we have UPS Worldship installed on our desktops for shipping out packages. I had written a small php script that could open the UPS Worldship database, and report all of our tracking numbers with links. UPS had switched from being an unprotected Microsoft Access database file, to using a Microsoft SQL (MSSQL database). This kind of broke my little php script I had written, but I was eventual able to work something together. Recently, I installed the latest update UPS Worldship 2010 Version 12. Wouldn’t you know it, broken again. I couldn’t get my little script working again. ODBC access had been removed from this version. I could no longer connect to the MS SQL  dabatabe for UPS Worldship.

It seemed the only option was to buy some 3rd party software USP authorizes to connect to the database. Digging through the internet, I found a nice little utility call Microsoft SQL Server Management Studio Express that allows you to access the USP database instance. Once you install the software open the program.

Fill in the Server Name box (usually Servername\UPSWSDBSERVER), and click connect. In the left hand window, double click on the Security folder. You will see a list of logins. You now need to create a new user.

Right click on the right pane, and choose New Login. Type in your Login Name in the top, and now select the SQL Server Login radio box. Type in your password.

At the bottom for Default Database select “master” from the drop down box. In the left pane of the window, there is a link for Server Roles. Click on Server Roles, and check all boxes in the right pane to add all rights to your login. Hey you are into the database, you might as well give yourself full access right?

Save your login, and it’s time to setup an ODBC connection for Codeigniter to use. Open the Control Panel->Administrative Tools->Data Sources (ODBC).

Click on the System DSN tab, and then the Add button. Under Name type in a with no spaces. I used UPS_WS_12 for mine. In the description you can enter anything. In the server box click on the dropdown and select your UPS server databse instance. It should be something like ServerName\UPSWSDBSERVER. Click on the Next button.

Now fill out the options as shown below. Fill in the login credentials as entered in the UPS database. Then, click on the Next button.

Select upswsdb from the dropdown box as shown. Then, click on the Next button.

The options show will probably be set by default. Click on Finish button. Your connection should now be created. You may click on Test Data Source button, and you should connect successfully.

Close your the ODBC screen, and open up your codeigniter application folder. Here are the connections settings you will need to use for application/config/database.php

$active_group = "default";
 
$db['default']['hostname'] = "UPS_WS_12"; //System DSN name you created
$db['default']['username'] = "username"; //Username as entered in ODBC connection
$db['default']['password'] = "password"; //Password as entered in ODBC connection
$db['default']['database'] = "";
$db['default']['dbdriver'] = "odbc";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;

If you are interested in a SQL query to view completed shipments with all the shipping details for you shipping log try out this query:

$query = $this->db->query("SELECT * FROM upswsdb.dbo.calPkgAgent a 
   JOIN upswsdb.dbo.calShipment s ON a.m_primaryKey=s.m_foreignKey00 
   JOIN upswsdb.dbo.calPackage p ON s.m_primaryKey=p.m_foreignKey 
   WHERE s.m_shipDataSet='eod' AND s.m_isVoid='0' 
   ORDER BY s.m_shipDateTime DESC");

To view pending shipments that have not been processed by End of Day, change s.m_shipDataSet=’eod’ to s.m_shipDataSet != ‘eod’. I use this simple little codeigniter application to view all of my UPS Worldship data. This makes it very easy to paginate the results, and provide real time access to tracking data. Much easier than trying to open UPS Worldship on a workstation that does not have it open or security rights to view the shipping log.