How the process goes?
I successfully created a daily order importation script from Magento database to the fulfillment center last week as part of client requirements. The script dumped a csv file containing daily orders and passed it in fulfillment in a daily basis. Since it is a one way process, client also want me to create a script which will update the order status based on the fulfillment confirmation file. The fulfillment is sending back an confirmation file which formated in comma separated value (csv) that contains completed orders. Base on that file, the script will update the order status in Magento automatically(of course by setting up a cron job).
Preparing the script
The whole process need two files:
1. Confirmation file (csv or a text file)
2. php script
Confirmation file – it comma separated format, where order and tracking numbers is include.
Example.
InvoiceNumber,ShipCharge,ShipWeight,ShipDate,TrackingNumber,ShipMethod
100000222,8.8200,2.0,2010-09-22 14:40:02.000,01805213907832456279,upsprt
The first line is your csv header, and next lines onward are their values.
php script - a script which will do the process. The script use Magento api.
Step 1 - create a database connection
function sql_connect($server_host, $mysql_user, $mysql_password,$own_db){
$link = mysql_connect($server_host, $mysql_user, $mysql_password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($own_db, $link);
if (!$db_selected) {
die ('Can\'t use ' . $own_db . ' : ' . mysql_error());
}else{
echo 'Connect Successfully'." <br>";
}
}
Step 2 - Call the sql_connect function and replace the credentials variable with the correct one.
/*
* prepare credentials -
* then connect through sql_connect function
*/
$server_host = '';
$sql_username = '';
$sql_password = '';
$own_db = '';
sql_connect($server_host, $sql_username,$sql_password,$own_db);
Step 3 - include the mage.php in order to use Magento api
/*
* include the mage.php file and the app() class
* set default value for email and comment
*/
require_once("../store/app/Mage.php"); // please change with correct path...
Mage::app();
$comment = null;
$email = false;
$includeComment = false;
Step 4 - make sure you have standard naming for your csv file.
Example
shipment20110521.csv
shipment20110520.csv
Notice that the name is change only in date. In that case you can apply the following code. The code is setup to get yesterdays order.
/*
* get the date of the yesterday orders
* this is the basis of the order file name
* shipment.$last_order is the standard name of csv file.
*/
$last_order = date('Ymd', strtotime('yesterday'));
$source = "shipment".$last_order.".csv";
Step 5 - do a looping for each order in the csv and create shipment for each order.
/*
* get information from the csv file
* declare variables
* look for the order no., tracking no., and method
*/
$row = 1;
$row_info = array();
if (($handle = fopen($source, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
if ($row > 1){
for ($c=0; $c < $num; $c++) {
switch ($c) {
case 0:
$row_info['invoice_no'] = $data[$c];
$order_id = trim($row_info['invoice_no']);
break;
case 1:
$row_info['ship_charge'] = $data[$c];
break;
case 2:
$row_info['ship_weight'] = $data[$c];
break;
case 3:
$row_info['ship_date'] = $data[$c];
break;
case 4:
$row_info['tracking_no'] = $data[$c];
$tracking_no = $row_info['tracking_no'];
break;
case 5:
$row_info['ship_method'] = $data[$c];
if (preg_match("/ups/", $row_info['ship_method']))
{
$row_ship_method = "United Parcel Service";
$row_carrier_code = "ups";
}
if (preg_match("/USP/", $row_info['ship_method']))
{
$row_ship_method = "United States Postal Service";
$row_carrier_code = "usps";
}
break;
} //end of switch
} //end of for
/*
* query the customer email based on the order number
* found on the csv file.
*/
$sql = "SELECT sfo.customer_email FROM sales_flat_order as sfo WHERE sfo.increment_id = $order_id ";
$result = mysql_query($sql);
$sfo_row = mysql_fetch_assoc($result);
$email = $sfo_row['customer_email'];
/*
* magento api coding start
* load the sales order model by $order_id defined in the csv
* then evaluate if it can ship the order.
*/
$order = Mage::getModel('sales/order')->loadByIncrementId($order_id);
if($order->canShip()){
$convertor = Mage::getModel('sales/convert_order');
$shipment = $convertor->toShipment($order);
/*
* load all the items in specified order.
* then ship it.
*/
foreach ($order->getAllItems() as $orderItem) {
if (!$orderItem->getQtyToShip()) {
continue;
}
if ($orderItem->getIsVirtual()) {
continue;
}
$item = $convertor->itemToShipmentItem($orderItem);
$qty = $orderItem->getQtyToShip();
$item->setQty($qty);
$shipment->addItem($item);
} //end of foreach
/*
* prepare for adding the carrier code, title and number.
* save the shipment and send an email confirmation.
*/
$data = array();
$data['carrier_code'] = $row_carrier_code;
$data['title'] = $row_ship_method;
$data['number'] = $tracking_no;
$track = Mage::getModel('sales/order_shipment_track')->addData($data);
$shipment->addTrack($track);
Mage::register('current_shipment', $shipment);
$shipment->register();
$shipment->addComment($comment, $email && $includeComment);
$shipment->setEmailSent(true);
$shipment->getOrder()->setIsInProcess(true);
$transactionSave = Mage::getModel('core/resource_transaction')
->addObject($shipment)
->addObject($shipment->getOrder())
->save();
$send = $shipment->sendEmail($email, ($includeComment ? $comment : ''));
if(!$send){
echo "Sending of confirmation email failed.!";
}
else{
echo "Shipment for Order Number:" . $order_id . "is successfully created.";
}
}else{
echo "can't create order";
}
} //end of second if
$row++;
} // end of while
fclose($handle);
}
else{
echo "Shipping CSV Information not found!";
/*
* send an email notification if shipment file was not found
* send this to janzell1989@gmail.com
*/
$headers = "From: info@ownproducts.com \r\n";
$headers .= "Reply-To: name@domain.com \r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
$email_to ='youremail@email.com' ;
$email_subject = "Shipping CSV Information Failure";
$email_message = $source .' Not Found!!! <br> <br><br> CSV Information Failed!!! Please check the file in FTP directory <br><br> Directory : own/shipping <br><br><br><br>';
$email_message .= 'Possible: Failure of uploading the file in specified directory. <br><br><br><br>Thank you!';
$ok = mail($email_to, $email_subject, $email_message, $headers);
}
You can download the full script
here. If there's any problem, please feel free to comment below