Australia Post - Automatic Order 'Completion' when Delivered

Mish · #1 04-13-2013, 09:58 PM

Hi guys,

I've developed a short PHP script that will automatically changed an order status from QUEUED or PROCESSED to COMPLETE once the package has been delivered to the customer.

Note that it will not send an email to the customer or you (if you have that configured).

PRE-REQUISITES TO WORK:
You must enter your Australia Post shipping number in the Tracking number field on the order page.

The script not only automatically updates your order status, but can be loaded in your webbrowser to see a shipping summary:

http://s2.postimg.org/9t6lc5w7t/Screen_Shot_2013_04_14_at_6_16_44_PM.png

Code:

<?php

function fetchOrders()
{
    //echo"Pre connect";
    mysql_connect(localhost,'<DATABASE USERNAME>','<DATABASE USER PASSWORD>');
    //echo "attempting to select database";
    @mysql_select_db('<XCART DATABASE NAME>') or die( "Unable to select database");
    $query="SELECT * FROM xcart_orders where status in ('P','Q') and LENGTH(tracking) > 3";
    //echo "Executing query";
    $result=mysql_query($query);

    $num=mysql_numrows($result);
    return $result;
    mysql_close();
}

function checkOrderStatus()
{
    $orders = fetchOrders();
    $num=mysql_numrows($orders);
    $i = 0;
    if ($num > 0)
    {
    echo ('<table border="1">
        <tr>
            <th style="background-color:blue; color: gold">Order ID</th>
        <th style="background-color:blue; color: gold">Customer</th>
            <th style="background-color:blue; color: gold">Status</th>
            <th style="background-color:blue; color: gold">Date</th>
            <th style="background-color:blue; color: gold">Location</th>
        <th style="background-color:blue; color: gold">Destination</th>
        <th style="background-color:blue; color: gold">Shipping Type</th>
        </tr>');
        
    }
    while ($i < $num)
    {
    $id = mysql_result($orders,$i,"orderid");
    $trackingNumber = mysql_result($orders,$i,"tracking");
    $destination = strtoupper(mysql_result($orders,$i,"s_city"));
    $s_state = strtoupper(mysql_result($orders,$i,"s_state"));
    $shippingType = mysql_result($orders,$i,"shipping");
    $customer = mysql_result($orders,$i,"b_lastname");
    //echo $id . ": " . $trackingNumber;
    if (isDelivered($trackingNumber, $lastStatus, $lastDate, $lastLocation))
    {
        mysql_connect(localhost,'<DATABASE USERNAME>','<DATABASE USER PASSWORD>');
        //echo "attempting to select database";
        @mysql_select_db('<XCART DATABASE NAME>') or die( "Unable to select database");
        //echo " CLOSING ORDER #" . $id ;
        $query="UPDATE xcart_orders SET status='C' WHERE orderid='$id'";
        //echo " | " . $query;
        mysql_query($query);
        echo " >>> " . mysql_affected_rows() . " <<< ";
        echo mysql_error();
        mysql_close();
    }
    else
        echo  ("<tr><td>{$id}</td><td>{$customer}</td><td>{$lastStatus}</td><td>{$lastDate}</td><td>{$lastLocation}</td><td>{$destination},  {$s_state}</td><td>{$shippingType}</td></tr>");
    $i++;
    }
    if ($num > 0) echo ('</table>');
    echo $num . " orders currently in shipment";
    
}

function get_content($url)  
{  
    $ch = curl_init();  
    $fakeAgent =   'IE 6 - Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322';
    curl_setopt ($ch, CURLOPT_URL, $url);  
    curl_setopt ($ch, CURLOPT_HEADER, 0); 
    curl_setopt($ch, CURLOPT_USERAGENT,$fakeAgent ) ;
    curl_setopt ($ch, CURLOPT_COOKIEJAR, 'cookie.txt');

    ob_start();  
    curl_exec ($ch);  
    curl_close ($ch);  
    $string = ob_get_contents();  
    ob_end_clean();
    return $string;      
  
}

function isDelivered($trackingNumber, &$lastStatus, &$lastDate, &$lastLocation)
{

    $content = get_content('http://auspost.com.au/track/track.html?id=' . $trackingNumber);
    
       
    $doc = new DOMDocument();
    @$doc->loadHTML($content);
    
    $cells = $doc->getElementsByTagName('td');
    
    foreach ($cells as $cell){
    $event[] = $cell->nodeValue;
    
    
    }
    
    $date = $event[0];
    $status = $event[1];
    $location = $event[2];
    if ($status == "Delivered")
    return true;
    else
    {
    $lastStatus = strlen($status) > 1 ? $status : "No Tracking Information";
    $lastDate = strlen($date) > 1 ? $date : "N/A";
    $lastLocation = strlen($location) > 1 ? $location : "N/A";
    foreach ($event as $e)
    {
      if ($e == "Delivered") return true; 
    }
    }
    return false;

}

checkOrderStatus();




//echo $tbody->length;
                                                                    



?>

I called the file AP-Delivery.php , and placed it in my home www directory.

Once you have put the file there and edited it to have your database name, database username and password, you have to configure a CRON JOB to run every 30 minutes.

In cPanel this is down the bottom under the advanced tab.

1> Click on CRONJOBS
2> Under ADD NEW CRONJOB, select the COMMON SETTINGS dropbox and choose "Twice an hour".
3> In the COMMAND text entry field, type the following:

php -f /home/<USERNAME>/public_html/AP-Delivery.php >/dev/null 2>&1

Where the username is your cPanel username.

The script will run every half hour. It will look for orders that have a TRACKING NUMBER and have a status of PROCESSED or QUEUED. It will go to the Australia Post website and check whether the order has been delivered. If it has, it will (directly by accessing the back end database) change the status to COMPLETE.

To load the shipping summary, just go to https://yourdomain.com//AP-Delivery.php

Hope this helps.

thebluedoorboutique · #2 04-29-2013, 05:47 AM

That's awesome. I wish someone would develop something like this for USPS and UPS!

Mish · #3 05-05-2013, 11:14 PM

Does UPS have a website where you enter a tracking number? I could probably alter the code reasonably quickly [when I have the spare time]

thebluedoorboutique · #4 05-13-2013, 02:05 PM

https://tools.usps.com/go/TrackConfirmAction!input.action
http://www.ups.com/tracking/tracking.html

Mish · #5 05-19-2013, 07:52 PM

Can you provide me with a recent tracking number (or one you've just shipped)?
If you prefer you can private message me with the details.

I need to look at the format of the output

thebluedoorboutique · #6 05-20-2013, 12:40 PM

9405510200829774216112

Mish · #7 05-20-2013, 08:44 PM

ok.. I understand that this isn't a complete modification however I'd appreciate some latitude from the moderators!

ok.. Here is where I'm at. Please note the following:

1> It won't yet change the order to COMPLETE as the example tracking item hasn't been delivered yet (therefore I don't know what the delivered text looks like!)
2> I've disabled the part of the code that automatically sends an e-mail to the customer when the order tracking number is entered [so that whilst testing it doesn't spam them!]
Here is where I'm at.

Follow the instructions in the first post. Perhaps you could call this USPS-Delivery.php and place it in your www directory.

Code:

<?php
$databaseUserName = 'insert_databaseusername';
$databasePassword = 'insert_databasepassword';
$databaseName = 'insert_databasename';
 
function fetchOrders()
{
    //echo"Pre connect";
    mysql_connect(localhost,$databaseUserName,$databasePassword);
    //echo "attempting to select database";
    @mysql_select_db($databaseName) or die( "Unable to select database");
    $query="SELECT * FROM xcart_orders where status in ('P','Q') and LENGTH(tracking) > 3";
    //echo "Executing query";
    $result=mysql_query($query);
    $num=mysql_numrows($result);
    return $result;
    mysql_close();
}
function checkOrderStatus()
{
    $orders = fetchOrders();
    $num=mysql_numrows($orders);
    $i = 0;
    if ($num > 0)
    {
 echo ('<table border="1">
     <tr>
         <th style="background-color:blue; color: gold">Order ID</th>
  <th style="background-color:blue; color: gold">Customer</th>
         <th style="background-color:blue; color: gold">Status</th>
         <th style="background-color:blue; color: gold">Date</th>
         <th style="background-color:blue; color: gold">Location</th>
  <th style="background-color:blue; color: gold">Destination</th>
  <th style="background-color:blue; color: gold">Shipping Type</th>
     </tr>');
 
    }
    while ($i < $num)
    {
 $id = mysql_result($orders,$i,"orderid");
 $trackingNumber = mysql_result($orders,$i,"tracking");
 $destination = strtoupper(mysql_result($orders,$i,"s_city"));
 $s_state = strtoupper(mysql_result($orders,$i,"s_state"));
 $shippingType = mysql_result($orders,$i,"shipping");
 $customer = mysql_result($orders,$i,"lastname");
 $customerFirstName = mysql_result($orders, $i, "firstname");
 $dispatchedEmailSent = mysql_result($orders,$i, "notes");
 $emailAddress = mysql_result($orders,$i, "email");
 $orderBaseURL = 'https://www.mydomain.com/admin/order.php?orderid=';
 //echo $id . ": " . $trackingNumber;
 if (isDelivered($trackingNumber, $lastStatus, $lastDate, $lastLocation, $url))
 {
     mysql_connect(localhost,$databaseUserName,$databasePassword);
     //echo "attempting to select database";
     @mysql_select_db($databaseName) or die( "Unable to select database");
     //echo " CLOSING ORDER #" . $id ;
     $query="UPDATE xcart_orders SET status='C' WHERE orderid='$id'";
     //echo " | " . $query;
     mysql_query($query);
     echo " >>> " . mysql_affected_rows() . " <<< ";
     echo mysql_error();
     mysql_close();
 }
 else
 {
     echo ("<tr><td><a href=\"{$orderBaseURL}{$id}\" target=\"_blank\">{$id}</a></td><td><a href=\"<a href="mailto:{$emailAddress}\">{$customer}</a></td><td><a">mailto:{$emailAddress}\">{$customer}</a></td><td><a href=\"{$url}\" target=\"_blank\">{$lastStatus}</a></td><td>{$lastDate}</td><td>{$lastLocation}</td><td>{$destination}, {$s_state}</td><td>{$shippingType}</td></tr>");
     if (!preg_match("/Shipping Email Sent/", $dispatchedEmailSent, $sent))
     {
  $customer = ucwords(strtolower($customer));
  $customerFirstName = ucwords(strtolower($customerFirstName));
  $to = $emailAddress;
  $subject = "MyCompanyName - Order Shipped";
  $message = "<p>Good afternoon {$customerFirstName} {$customer}, <br /><br />Your order has been despatched by MyCompanyName.</p>
  <p>As of <b><span style=\"color: blue;\">{$lastDate}</span></b>,USPS recorded the last tracking event as <b><span style=\"color: blue;\">{$lastStatus}</span></b> in <b><span style=\"color: blue;\">{$lastLocation}</span></b>.</p><p>You can track your order status here: http://auspost.com.au/track/track.html?id={$trackingNumber}</p><p><superscript>***</superscript>Please note that occasionally Australia Post will neglect to scan your item at the time of lodgement. This may result in 'No events found' being displayed. Generally, after the first scan at lodgement, no further updates are provided until the day of delivery.</p><p>Thank you for shopping with My company Name</p>";
  $header = "From:sales@yourdomain.com \r\n";
  $header .= "MIME-Version: 1.0\r\n";
  $header .= "Content-type: text/html\r\n";
  //$retval = mail ($to,$subject,$message,$header);
  $retval = false; // DELETE ME WHEN FINAL VERSION
  if( $retval == true )  
  {
      date_default_timezone_set('Australia/Melbourne');
      $timeStamp = date('m/d/Y h:i:s a', time());
      echo "Message sent successfully...";
      mysql_connect(localhost,$databaseUserName,$databasePassword);
      //echo "attempting to select database";
      @mysql_select_db($databaseName) or die( "Unable to select database");
      //echo " CLOSING ORDER #" . $id ;
      $query="UPDATE xcart_orders SET notes='{$dispatchedEmailSent} \r\n <<< Shipping Email Sent @ {$timeStamp} >>> \r\n' WHERE orderid='$id'";
      //echo " | " . $query;
      mysql_query($query);
      echo mysql_error();
      mysql_close();
  }
  else
  {
      echo "Message could not be sent...";
  }
     }
 
 }
 $i++;
    }
    if ($num > 0) echo ('</table>');
    echo $num . " orders currently in shipment";
 
}
function get_content($url)  
{  
 $ch = curl_init();  
 $fakeAgent =   'IE 6 - Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322';
 curl_setopt ($ch, CURLOPT_URL, $url);  
 curl_setopt ($ch, CURLOPT_HEADER, 0); 
 curl_setopt($ch, CURLOPT_USERAGENT,$fakeAgent ) ;
 curl_setopt ($ch, CURLOPT_COOKIEJAR, 'cookie.txt');
 ob_start();  
 curl_exec ($ch);  
 curl_close ($ch);  
 $string = ob_get_contents();  
 ob_end_clean();
 return $string;      
 
}
function getElementsByClassName(\DOMDocument $DOMDocument, $ClassName) {
 $Elements = $DOMDocument->getElementsByTagName("*");
 $Matched = array();
 for($i=0;$i<$Elements->length;$i++) {
 if($Elements->item($i)->attributes->getNamedItem('class')->nodeValue == $ClassName) {
 $Matched[]=$Elements->item($i);
 }
 }
 return $Matched;
} 
function isDelivered($trackingNumber, &$lastStatus, &$lastDate, &$lastLocation, &$url)
{
    //$trackingNumber = '9405510200829774216112'; // TEMP!!!
    $url = 'https://tools.usps.com/go/TrackConfirmAction_input?qtc_tLabels1=' . $trackingNumber;
    $content = get_content($url);
 
 
    $doc = new DOMDocument();
    @$doc->loadHTML($content);
 
    $cells = getElementsByClassName($doc, 'td-status');
 
    $status = $cells[0]->nodeValue;
   // $event[] = $cell->nodeValue;
   // echo $status;
    $cells = getElementsByClassName($doc, 'date-time sortable');
    if (cells-count())
 $date = $cells[0]->nodeValue;
  //  echo $date;
    $cells = getElementsByClassName($doc, 'location sortable');  
    if (cells-count())
 $location = $cells[0]->nodeValue;
  //  echo $location;
    if ($status == "Delivered")
 return true;
    else
    {
 $lastStatus = strlen($status) > 1 ? $status : "No Tracking Information";
 $lastDate = strlen($date) > 1 ? $date : "N/A";
 $lastLocation = strlen($location) > 1 ? $location : "N/A";
 //foreach ($event as $e)
 //{
 //  if ($e == "Delivered") return true; 
 //}
    }
    return false;
}
checkOrderStatus();
//$delivered = isDelivered('0', $status, $lastDate, $lastLocation, $url);
 
//echo $tbody->length;
 
 
?>