Tracking numbers for batch print

[♥John W]

@Dan Cole asked me about how I send out tracking info in Batch Print, so here's some of the details.

 

I created a table called orders_tracking with an order number field and tracking number field.  But, I have been inserting this manually with a sql statement I generate from the tracking info my partners email me.
 

CREATE TABLE IF NOT EXISTS `orders_tracking` (
  `orders_id` int(6) NOT NULL,
  `fedex_track_num` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `orders_id_2` (`orders_id`),
  KEY `orders_id` (`orders_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In functions/general.php add to bottom.  I have it in the catalog functions/general.php also.

// LINK TO FEDEX SHIPMENT TRACKER
  function tep_track_fedex($order_id) {
    $fedex_query = tep_db_query("select fedex_track_num  from orders_tracking where orders_id = '" . (int)$order_id . "'");
    $fedexArray = tep_db_fetch_array($fedex_query);
    $fedex_tracking = $fedexArray['fedex_track_num'];
    $trackLink = false;
    if ($fedex_tracking) {
      $trackLink = '<a class="textlink" href="http://www.fedex.com/Tracking?tracknumbers=' . $fedex_tracking .'&cntry_code=us&language=english&clienttype=ivother">FedEx tracking number:'. ' ' . $fedex_tracking .'</a>';
      return $trackLink;
    }
    return false;
  }

in admin/batch_print after

        while ($orders = tep_db_fetch_array($orders_query)) {

          $order = new order($orders['orders_id']);

add

          // fedex tracking
          $order_id = $orders['orders_id'] ;
          $trackLink = tep_track_fedex($order_id);
          if (isset($trackLink)) {
            $contents[] = array('text' => '<br>' . $trackLink);
          } else {
            $contents[] = array('text' => '<br>' . 'no tracking' );
          }

Here are all the files I use it in.  On orders it shows the tracking number as a clickable link in the right column.  That way if someone calls I can access their number and quickly track wiht a click.  They can also access it in their account.

Usages of tep_track_fedex [4 occurrences]

account_history.php
    55:  $trackLink = tep_track_fedex($order_id);
account_history_info.php
    55:  $trackLink = tep_track_fedex($order_id);
batch_print.php
    97:  $trackLink = tep_track_fedex($order_id);
orders.php
  501:  $trackLink = tep_track_fedex($order_id);


Let me knwo if you want to see more.
 

[Dan Cole]

Thanks John....I like your approach to this....as I mentioned I'm thinking of doing the same sort of thing but maybe with a bit of an adjustment so that we can scan the tracking numbers in as the packages are labeled.  This will avoid the manual sql entry and allow us to scan the tracking numbers rather than manually entering them. My eye sight is such that I can hardly read those tracking numbers any more.  I'll share what I do when I get to it.   Thanks for posting this....it'll certainly help me streamline the tracking notification process.  :thumbsup:

 

Dan

[♥John W]

I see how scanning them in sounds like a good idea.  I'm not at our warehouse, so the numbers are sent to me.  Sounds like you might be in my age group.  My eyes used to be perfect, but not anymore.  Getting old sucks and I still have a ways to go.

 

Let me know if you'd like ot see how I have it display in the account files, as it's easy to add in.

[Dan Cole]

John...I had a few minutes to fiddle around with this.  I set up a separate tracking number batch update page so we can scan the tracking numbers in as the shipments are prepared.  My plan is to use that page to collect the tracking numbers and to send out the shipment confirmation emails in one step.  Initially I wasn't going to store the tracking numbers...I was just going to add them to the order status comments as we do now but I like the idea of making the tracking numbers clickable from within the order page and from some of the other places you added it to.  Looking are your table I noticed that you only store the tracking number and the order number and you are careful not to have duplicates.   We often have multiple shipments associated with an order so I'm thinking a date added field and maybe a primary key would be helpful.  If you were setting up the table again is there anything you would add to it?

 

Dan      

[♥John W]

If you are shipping mulitple methods, then you might want to set it up for UPS number, fedex, etc...  We only do one number and use a master number for mulitple packages.  You could do a foreach statement for each tracking number with the order to send multiple links. 

 

Also, the email has a clickable link in it too, so they don't have to log in to click it.  I'm dragging a little this morning but I'll think more on this.

[Dan Cole]

@@John W  John you should put down whatever it is that your dragging.  Your day will go much better, at least I found it so. :P

 

Adding the shipper is an excellent idea....we use a secondary shipper once and awhile so it'll come in handy. Thanks for that.

 

Here is what I ended up with....I can always change it as I go. :rolleyes:

CREATE TABLE tracking_numbers(
tracking_id INT NOT NULL AUTO_INCREMENT ,
order_id INT( 11 ) ,
shipped_by VARCHAR( 15 ) NOT NULL ,
tracking_number VARCHAR( 25 ) NOT NULL ,
date_added DATETIME,
PRIMARY KEY ( tracking_id )
);

Dan

[ArtcoInc]

@@Dan Cole @@John W

 

Is there a reason that this is a separate database table, and not additional fields added to the orders table?

 

Malcolm

[♥John W]

I like the idea (think it's better) of a separate table and the orders table is full of fields as it is.  This way not updates to the orders table will affect this and vice versa.  IMHO, there have been to many addons and such that modify existing tables. 

[ArtcoInc]

@@John W

I like the idea (think it's better) of a separate table and the orders table is full of fields as it is.  This way not updates to the orders table will affect this and vice versa.  IMHO, there have been to many addons and such that modify existing tables. 

 

Thank you. B)

 

Malcolm

[Dan Cole]

I like the idea (think it's better) of a separate table and the orders table is full of fields as it is.  This way not updates to the orders table will affect this and vice versa.  IMHO, there have been to many addons and such that modify existing tables. 

 

I agree and as long as you have a field to join the tables on, in this case the order_id, there is really no downside.

 

Dan

[Dan Cole]

Just an update, I'm now scanning in our tracking numbers.  :)  

 

I modified a copy of the batch update contribution and added a field for the tracking numbers and the ability to over ride our default shipping provider in case another shipped is used.  Now we can just scan the numbers in as we are adding the labels to the outgoing orders...hit submit and....

 

1. The information is recorded in the tracking table.

2.  The customer receives an email indicating the order is being shipped and is provided with the tracking information.

3. The order status is updated and the tracking number is also recorded in the comments field for quick reference should we get an inquiry.

 

Unfortunately what I is pretty unique to our operation so it's not likely to be of much use to anyone else but if anyone else is planning to go down this path and would like the code just PM me.

 

Dan

[ArtcoInc]

@@Dan Cole

 

Dan, I'd like to see what you've done. I've been considering writing a module that is a tab in the admin Orders page that does something like this:

 

1) You identify the shipper

2) You scan the tracking number

3) The program adds the shipper and tracking number to the order (in the new separate table)

4) The program updates the order status to Shipped

5) The program emails the customer stating 1) the order has shipped, 2) attaches the tracking number, and 3) provides a link to the shipper's website with the tracking number imbedded

 

Malcolm

[Dan Cole]

I'd be happy to send it to you Malcolm....that is pretty much what I did except for the tab idea.    Mind is set up so it can be done in bulk by whoever is packaging up the orders.  PM me if you haven't already and I'll send it to you.

 

Dan