1

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 3 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 issues, 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 this code 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 fund 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
    Sorry, I was unaware of the ce-1.4.0.1 tag – MB34 Aug 8 '16 at 14:54
  • BTW, we do not have the flat order table available for some reason. – MB34 Feb 17 '17 at 17:47
  • " I have a need to query all the data that builds an order directly from the DB." - care to explain, why? Since you are doing it with a PHP script, I don't see a reason not to use Magento functions in that script. So it looks like you are making your task needlessly complicated. – Fabian Schmengler Feb 23 '17 at 8:40
  • @FabianSchmengler, The reason is because, for some unknown reason, the Mage functions are having issues with some of the data retrieval. – MB34 Feb 23 '17 at 14:54
  • From what I gather this is a band-aid until Magento it's upgraded to the latest version? 1.4.x is very old, and possibly you are encountering MySQL lock timeouts that were notorious in these older version. Is there any insight in the var/log/ files related to MySQL foreign key constraints? With using a 3rd party module for options have you reached out to them for any validations it should work in 1.4.x without issues? Here's a pure SQL query that may help and compatible with 1.4.x github.com/oasisfleeting/magento-snippets/blob/master/… – B00MER Feb 23 '17 at 18:55
1
+150

Unfortunately, the match of the Country Code with the Country Name is done in the Magento Locale XML files, for example:

lib/Zend/Locale/Data/en.xml

To get the order items you should be able to query the sales_flat_order_item table filtering by Order Id (Entity ID NOT Increment Id).

SELECT `main_table`.* FROM `magsales_flat_order_item` AS `main_table`
WHERE (order_id = '$orderId');

[EDIT]

For completeness, here is a modified version of your script, adapted to run in the shell folder of your Magento application.

<?php

require_once 'abstract.php';

class Mage_Shell_Extract extends Mage_Shell_Abstract {

    public function run() {
        $resource = Mage::getSingleton('core/resource');
        $readConnection = $resource->getConnection('core_read');

        $orderID = '100000001'; // will be passed into script via $_GET...

        $sql = "select entity_id from magsales_order where increment_id = $orderID";
        $result = $readConnection->fetchAll($sql);
        $orderEntityId = $result[0]['entity_id'];

        $sql = "select product_options from magsales_flat_order_item where order_id = $orderEntityId";

        $result = $readConnection->fetchAll($sql); // 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 = $orderEntityId) " .
            " and attribute_id = 275";

        $result = $readConnection->fetchAll($sql);
        $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 = 
                           $orderEntityId) 
                      and value like 'Payflow%'";
                $resultpp = $readConnection->fetchAll($sqlpp);
                $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 = $orderEntityId) 
                      and (attribute_id in(281, 279))";
                $resultpp = $readConnection->fetchAll($sqlpp);
                $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 = $orderEntityId)
            and (attribute_id in(216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228))";
        $result = $readConnection->fetchAll($sql);

        $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;

        $sql = "SELECT `main_table`.* FROM `magsales_flat_order_item` AS `main_table` WHERE (order_id = $orderEntityId);";
        $result = $readConnection->fetchAll($sql);

        foreach($result as $item) {
            echo ($item['sku'] ." ". $item["name"] ."\n");
        }
    }
}


$shell = new Mage_Shell_Extract();
$shell->run();

[/EDIT]

  • a comment on the question says that for some reason the OP does not have the flat tables available. It's just the old EAV orders version. – Marius Feb 22 '17 at 15:26
  • Thank you Marius, but the sales_flat_order_item table is present since Magento 1.1.1 (2008), so unless the database is broken - which is not specified - it must be present. See file app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-0.9.3-0.9.4.php. Moreover, the script from @MB34 itself refers to that table on line 4. – Enzo Perrotta Feb 22 '17 at 15:46
  • Damn. you are right. I was thinking of the flat order tables. – Marius Feb 22 '17 at 15:48
  • Yes, the flat_order_item table is avail, just not the flat_order table. – MB34 Feb 22 '17 at 19:35
  • @MB34 Did you try if this work for you? – Enzo Perrotta Feb 23 '17 at 21:23
1

I don't have a one size fits all query, but I can give you some smaller queries that you may play puzzle with.

All order related entities are kept in the sales_order_entity table and in the sales_order_* tables. Where * is int, decimal, .....
But you already know that.
The difference between them is the column entity_type_id.
This one is different depending on the entity. Orders have one value, invoices an other, order items an other and so on.

You can see the ids of every entity in the eav_entity_type table.
Let's say that the order items have the entity_type_id 12.
And the order you are processing has the increment id 10000099.

You can get all the order items ids (and a few other attributes) for your specific order with this query

SELECT * FROM
    sales_order_entity
WHERE 
    entity_type_id = 12 AND
    parent_id = 10000099

Let's say you get back on single row where the entity_id value is 88.
You can get all the integer attributes for that order item with this query.

SELECT * FROM 
    order_entity_int  
WHERE 
    entity_id = 88 AND
    entity_type_id = 12 -- order item type id here again

You can do the same for the other tables decimal, varchar, text, datetime.
I'm even sure there is a way to use UNION to get data from all these tables at once.

If you need certain attributes for the order item you can get all of them with

SELECT * FROM
    eav_attribute
WHERE 
    entity_type_id = 12 --replace with real order item id here  

[EDIT]
I may be wrong with the above. As Enzo Perrotta points out the order items might be stored in the sales_flat_order_item table, in which case you are in luck because you can get them just by running a query and making sure the order_id field matches your order number
[/EDIT]

For country is a bit difficult. Indeed the country name is not stored anywhere in the database. It comes from the ZF locale files.
But you can use this JSON to get the country name depending on the code you have in your database.

For regions is possible because there is a table called directory_country_region that has the region id and the default region name.

-1

You can try this, I am not sure it will work on Magento 1.4 version or not.

You have to make a file inside Magento root directory with this code and it will give you all order information.

<?php
require_once 'app/Mage.php';
Mage::app();
umask(0);


$_order = Mage::getModel('sales/order')->load(11);
$orderItem = $_order->getItemsCollection();
$billingAddress = $_order->getBillingAddress();
$shippingAddress = $_order->getShippingAddress();

echo '<pre>';print_r($_order->getData());
echo '<pre>';print_r($orderItem->getData());
echo '<pre>';print_r($billingAddress->getData());
echo '<pre>';print_r($shippingAddress->getData());

die;

?>
  • 1
    Did you not read the question? I do NOT want to use Mage functions to do this. I want to get the data directly from the database. Delete your answer, please because it does not deserve the bounty. – MB34 Feb 21 '17 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.