We have a Magento v1.4.0.1 installation that has an observer that takes order data and sends it via a web service to our Oracle back end.
Our installation of Magento has issues that we don't want to fix as we will be replacing it within the next 5 months. On occasion, and I cannot nor do I have the time to fully determine the cause, an order gets an exception because one of the custom attributes didn't get written to the database, outlined more below.
We use MageWorx Advanced Product Options and have a custom attribute on our products that stores some JSON data. The problem is that sometimes this option data does not get stored causing several things to fail, including the confirmation email and the observer creating the order data and sending the order data to Oracle.
I have to manually create the order data, import it into Oracle AND manually create a confirmation email to send to the customer. It takes between 5 and 15 minutes to do all of this. And, due to this issue, I am trying to write a script to help me out when an order encounters an exception and the order data is not sent to our web service that sends it to our Oracle system.
While I know and understand that it is preferable to use the Mage functions and calls to get order data, I have a need to query all the data that builds an order directly from the DB. I have the code below to get some of the data but am not sure how to get all the order items and how to build the Billing Address in the email. It seems I can't find where it picks "United States" in the billing address from the 'US" that is in the data; none of the country_region tables have it. If you need db_mysql.php, let me know.
<?php
require_once('db_mysql.php');
$orderID = 100144651; // will be passed into script via $_GET...
$sql = "select product_options from magsales_flat_order_item where order_id = (select entity_id from magsales_order where increment_id = $orderID)";
$result = Db::getInstance()->returnQuery($sql, MYSQL_ASSOC); // Returns associated array
$a = unserialize($result[0]['product_options']);
$s = str_replace('"', '"', $a["options"][0]['value']);
// $order_json will contain the decoded JSON needed to produce the Order data to send to Oracle
$order_json = json_decode($s, true);
// Let's determine the payment method
$sql = "select value from magsales_order_entity_varchar where entity_id in ".
" (select entity_id from magsales_order_entity where parent_id = ".
" (select entity_id from magsales_order where increment_id = $orderID)) ".
" and attribute_id = 275";
$result = Db::getInstance()->returnQuery($sql, MYSQL_ASSOC);
$PPTrans = 'None';
$pmtType = $result[0]['value'];
switch($pmtType) {
case "checkmo":
break;
case "paypaluk_direct":
/* Let's get the Paypal Trans and Credit Card Info */
$sqlpp = "select value from magsales_order_entity_text where entity_id in
(select entity_id from magsales_order_entity where parent_id =
(select entity_id from magsales_order where increment_id = $orderID))
and value like 'Payflow%'";
$resultpp = Db::getInstance()->returnQuery($sqlpp, MYSQL_ASSOC);
$x = $resultpp[0]['value'];
$PPTrans = substr($x, 16, 12);
$sqlpp = "select attribute_id, value from magsales_order_entity_varchar where entity_id in
(select entity_id from magsales_order_entity where parent_id =
(select entity_id from magsales_order where increment_id = $orderID))
and (attribute_id in(281, 279))";
$resultpp = Db::getInstance()->returnQuery($sqlpp, MYSQL_ASSOC);
$cardType = $resultpp[0]['value'];
$cardNum = $resultpp[1]['value'];
break;
}
// Now get Billing Address:
$sql = "select attribute_id, value from magsales_order_entity_varchar where entity_id in
(select entity_id from magsales_order_entity where parent_id =
(select entity_id from magsales_order where increment_id = $orderID))
and (attribute_id in(216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228))";
$result = Db::getInstance()->returnQuery($sql, MYSQL_ASSOC);
$billingAddress['line1'] = '';
$billingAddress['line2'] = '';
$billingAddress['line3'] = '';
$billingAddress['line4'] = '';
$billingAddress['line5'] = '';
for($i = 0; $i<count($result); $i++) {
switch($result[$i]['attribute_id']) {
case 216:
case 217:
case 218:
case 219:
case 220:
$billingAddress['line1'] .= ' '.$result[$i]['value'];
break;
case 222:
$billingAddress['line2'] = $result[$i]['value'];
break;
case 223:
case 224:
case 225:
case 226:
$billingAddress['line3'] .= ', '. $result[$i]['value'];
break;
case 227:
if($result[$i]['attribute_id'] = 227) {
// Not sure where to get Country Name from Abbrev
// $country_sql = "";
// $res = Db::getInstance()->returnQuery($country_sql, MYSQL_ASSOC);
}
$billingAddress['line4'] = $result[$i]['value'];
break;
case 228:
$billingAddress['line5'] = 'T:'.$result[$i]['value'];
break;
}
}
$billingAddress['line1'] = trim($billingAddress['line1']);
$billingAddress['line2'] = trim($billingAddress['line2']);
$billingAddress['line3'] = trim(substr($billingAddress['line3'],1));
$billingAddress['line4'] = trim($billingAddress['line4']);
$billingAddr = '';
foreach($billingAddress as $key =>$value) {
if(trim($value) != "") {
$billingAddr .= $value. "\n";
}
}
echo $billingAddr;
?>
