cancel
Showing results for 
Search instead for 
Did you mean: 

Obtain all order data from DB vs. using Mage functions

Obtain all order data from DB vs. using Mage functions

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('&quot;', '"', $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;

?>
1 REPLY

Re: Obtain all order data from DB vs. using Mage functions

I can understand not wanting to use magento core functions, at times it can be faster to sample and join MySQL data outside of magento.

 

Let me try and get this right..

  1. You want to find the billing information for a flat_order,  this is stored in two parts in the sales_flat_order_address table and can be joined to the SQL on sales_flat_order_address.parent_id=sales_flat_order.entity_id if I remember correctly.
  2. Country Codes can be converted really easily 
    $names = json_decode(file_get_contents("http://country.io/names.json"), true);
    echo $names['GB'];
  3. Order Items are stored in sales_flat_order_item so also easily joined.

If you need some more help I will be able to write a better post or private message later today when I'm free from work.

Regards
Sven