A few weeks ago, I had quite a few orders go sideways on me. The orders that were a mix of wholesale product and items we stock were missing our stocking items from the deliveries. With a little investigation, it seemed the culprit was likely that tickets had been prematurely flushed to status B. As my picking configurations are set to pick up statuses 6 to 9, there were several hours’ worth of orders that had failed to show up on the pick manifests.
The merits of my setup are debatable. There are pluses and minuses to being set up to operate the way we do, but we are following the advice of some of the best and brightest at ECI2. So, my dilemma was to come up with an idea that might allow to me head off this situation in the future. Now that I’ve seen the mistake made once, it’s reasonable to assume it might happen again. To solve it, I created a ‘Flushed Today’ report that emails me counts on tickets flushed to status B per route. I have it set to run 15 minutes after flushes are done (although I am at the mercy of my people that actually do the flushing, since we don’t autoflush.)
First things first, we need a simple method of emailing reports. While PHP can handle email natively, it’s a bit clunky handling email attachments. For that, I prefer the Swift Mailer framework, found at http://swiftmailer.org/. If you haven’t already, download and install 7-Zip, from http://www.7-zip.org. Unzip the gzip archive, then extract the tar file. Within the extracted folder, find the lib directory. Create a Classes folder in your /php/pear directory and put a copy of the Swift lib directory inside.
For the php file itself, you can download a zip file, or have a look at it:
"Ensite","UID"=>"NetClient","PWD"=>"db_user", "ReturnDatesAsStrings" => 1);
$idConn = sqlsrv_connect($serverName, $connectionOptions);
if($idConn === false ) {
die(var_dump(sqlsrv_errors()));
}
$tsql = "select count(SalesOrder.Id) as 'Count', SalesOrder.OrderRoute
from SalesOrder
where SalesOrder.OrderRoute like (?)
and SalesOrder.OrderRoute not like (?)
group by SalesOrder.OrderRoute";
$params = array('5%','50%');
//print_r ($params);
$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
//print_r ($options);
$getReport = sqlsrv_query($idConn, $tsql, $params, $options);
if ($getReport === false) {
die(print_r(sqlsrv_errors()));
}
$numRows = 0;
$numRows = sqlsrv_num_rows($getReport);
if ($numRows == 0) {
$fromName = "Sandbox";
$fromAddr = "you@yourcompany.com";
$replyTo = "you@yourcompany.com";
$subj = "Error in $filename";
$body = "flushed today report error";
$recp = "you@yourcompany"; // PRODUCTION
$message = "The $filename for $fnDate was not found.";
$semi_rand = md5(time());
$mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";
$headers = "From: $fromAddr";
$headers .= "\nMIME-Version: 1.0\n" .
"Content-Type: multipart/mixed;\n" .
" boundary=\"{$mime_boundary}\"";
"-{$mime_boundary}\n" .
"Content-Type: text/plain; charset=\iso-8859-1\n" .
"Content-Transfer-Encoding: 7bit\n\n";
$message .= "\n\n";
mail($recp, $subj, $message, $headers);
} else {
ob_start();
echo ("
<table border = '0' cellspacing='0' cellpadding='4' style='font-family:Arial, sans-serif;'>
<thead>
<tr><th style='background-color:#ECECEC;font-size:12px;font-weight:bold;color:#000000;'>Count</th>
<th style='background-color:#ECECEC;font-size:12px;font-weight:bold;color:#000000;'>Route</th>
</tr></thead><tbody>
");
$i = 1;
while ($row = sqlsrv_fetch_array($getReport, SQLSRV_FETCH_NUMERIC)) {
// color alternate rows
if( $odd = $i%2 ) { // $odd == 1; the remainder of dividing an odd number
$bgColor = 'BEBEBE';
} else { // $odd == 0; nothing remains
$bgColor = 'ECECEC';
}
$item = trim($row[0]).trim($row[1]);
echo ("
<tr><td style='background-color:#$bgColor;font-size:12px;color:#000000;'>$row[0]</td>
<td style='background-color:#$bgColor;font-size:12px;color:#000000;'>$row[1]</td>
");
$i++;
}
echo ("</tbody></table></form></html>");
$reportOutput = ob_get_contents();
ob_end_clean();
// Clean up
sqlsrv_free_stmt($getReport);
sqlsrv_close($idConn);
// Create the Transport
$transport = Swift_MailTransport::newInstance();
// Create the Mailer using your created Transport
$mailer = Swift_Mailer::newInstance($transport);
$message = Swift_Message::newInstance()
// Give the message a subject
->setSubject($filename)
// Set the From address with an associative array
->setFrom(array('you@yourcompany.com' => 'Lastname, Firstname'))
// Set the To addresses with an associative array
->setTo(array('you@yourcompany.com'))
// Give it a body
->setBody($reportOutput, 'text/html');
$numSent = $mailer->send($message);
}
exit;
} // END WEEKEND CHECK
?>
I’ve left my original mailing code in there, which handles the report not being found. While overkill for sending a plain text email, it is a fair demonstration of sending emails with attachments with native PHP. Near the bottom of the code, you see how much more simply Swift handles email. To add an attachment in Swift, it is only one extra line of code.
The key to this is, of course, our SQL query, which looks like this:
$tsql = "select count(SalesOrder.Id) as 'Count', SalesOrder.OrderRoute
from SalesOrder
where SalesOrder.OrderRoute like (?)
and SalesOrder.OrderRoute not like (?)
group by SalesOrder.OrderRoute";
$params = array('5%','50%');
The count function is self-descriptive – it’s simply counting the number of tickets that match the criteria. Next to that, we need to know to which OrderRoute each count belongs. The group by clause is what matches the counts to their corresponding routes. The parameters I’m passing are if the route begins with 5, but not starting with 50. In this case, routes that begin with 50 are drop-ship (or NED, if you prefer) routes, and so don’t get stock from my warehouse anyway. The percent symbol (%) is a wild-card meaning ‘0 or more characters’. If the routes I was concerned with all ended with a 5, I would simply reverse the characters – %5.
Most of the rest is simply presentation – we lay the data out in an HTML table that has alternating colors on the rows for easy readability. We enclose the report generation within ob_start (output buffer) and dump it out as the html body of the email Swift Mailer creates for us.
Save this file as ‘flushed-today-cli.php’. You can put it in the /htdocs directory within XAMPP, or put it in a folder near the root, C:\scripts, perhaps.
If you haven’t already, now is a good time to add the PHP directory to your windows path. This greatly simplifies running PHP scripts on the command line. For instructions on how to do that, http://www.php.net/manual/en/faq.installation.php#faq.installation.addtopath is the easiest guide to follow.
Now open a command window and cd (eg, ‘cd /d C:\xampp\htdocs’ and the prompt will change to ‘C:\xampp\htdocs>’) to the folder containing this script. enter ‘php flushed-today-cli.php’ and press Enter. You’ll see the time echo to the screen. After a moment, you should have an email containing a report of all the tickets from today that are in status B. For me, it isn’t unusual to see a handful through the day that have been taken to B. A credit and rebill, paying on a prepay that got hung up due to a bad card number, or possibly something being kept out of the usual pick process will all show up on this report. If you see an unusual number of tickets on a given route or all these routes, that would be your heads up to look into the situation, which we can do with another script I’ll show you later.
In production, I use Windows Task Scheduler for these sorts of reports. I simply create a task (instructions for Windows 7 are at http://windows.microsoft.com/en-US/windows7/schedule-a-task or for Windows XP at http://support.microsoft.com/kb/308569) to run it whenever I want it.
Here I’ve shown you your first report, which will email you counts on tickets flushed to B for today. It is easily modifiable to allow you set your own email address(es) and routes. Of course, you are always free to email 
for help in getting this working.
Recent Comments