Thursday, May 6, 2010

How to add product skus (or payment method) to the orders grid

[UPDATE] The solution provided here works only for Magento 1.4.0.0 or lower. Starting Magento 1.4.0.1 the order tables are not EAV anymore. I think (but I'm not sure) the logic is still OK but you have to try it by adding a new column to sales_flat_order and sales_flat_order_grid tables instead of adding a new attribute. [/UPDATE] I've tried in different ways to add articles skus (or payment method) to the orders grid in admin. I've tried 'left joining' different tables, performing different actions after applying _prepareCollection() method. All of them worked in some way, but the website started working slow when it reached 10000-12000 orders. And when I say slow I mean over 2 minutes to render the orders grid in admin. This was because of the slow queries which also resulted in slow loading of the frontend pages.
I found an easier solution, it's somehow redundant but it works and it's pretty fast.
I added a new varchar attribute for the order object.
EDIT: This only works in versions < 1.4
INSERT INTO `eav_attribute` (`attribute_id`, `entity_type_id`, `attribute_code`, `attribute_model`, `backend_model`, `backend_type`, `backend_table`, `frontend_model`, `frontend_input`, `frontend_label`, `frontend_class`, `source_model`, `is_required`, `is_user_defined`, `default_value`, `is_unique`, `note`) VALUES
(NULL, 11, 'product_skus', NULL, NULL, 'varchar', NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, 0, '');

Clear the cache (contents of the folder var/cache)
Override the Mage_Sales_Model_Order class and in the _beforeSave() method add this:
if (!$this->getProductSkus()) {
$skus = array();
foreach ($this->getAllItems() as $item) {
$skus[] = $item->getSku();
}
$this->setProductSkus(implode(",", $skus));
}
This will add a string with all the skus separated by comma to the order model.
Now all you have to do is to show the string in the orders grid.
Override the Mage_Adminhtml_Block_Sales_Order_Grid block and in the _prepareColumns() method add a new column like this:
$this->addColumn('product_skus', array(
'header' => Mage::helper('sales')->__('Products'),
'index' => 'product_skus',
));
Added it where ever you want.
If you don't know how to overwrite a model or a class this is a good tutorial:
http://magedev.com/2009/06/03/magento-overriding-model-block-or-helper/

The only problem is that these changes will take effect only for the orders you make from now on.

You can add the skus for every order like this.
Create a new php file, let's call it orders.php and place it in the root of the application (same level as index.php) with this code:
<?php
$mageFilename = 'app/Mage.php';

require_once $mageFilename;

Varien_Profiler::enable();

Mage::setIsDeveloperMode(true);

ini_set('display_errors', 1);

umask(0);
Mage::app('default');
Mage::register('isSecureArea', 1);

$orders = Mage::getModel('sales/order')->getCollection()->addAttributeToSelect("*");
foreach ($orders as $order){
$order->save();
}
?>

and call it in the browser (http://yourwebsite.com/orders.php)

If you have many orders it will take some time to run but eventually ... you get my point.

If you want to do this with the payment method the system is the same. Add a new attribute:
INSERT INTO `eav_attribute` (`attribute_id`, `entity_type_id`, `attribute_code`, `attribute_model`, `backend_model`, `backend_type`, `backend_table`, `frontend_model`, `frontend_input`, `frontend_label`, `frontend_class`, `source_model`, `is_required`, `is_user_defined`, `default_value`, `is_unique`, `note`) VALUES
(NULL, 11, 'order_payment_method', NULL, NULL, 'varchar', NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, 0, '');
Clear the cache.
In the _beforeSave() method add this:
if (!$this->getOrderPaymentMethod()) {
$this->setOrderPaymentMethod($this->getPayment()->getMethodInstance()->getTitle()); //if you want the payment method's name (Check / money order)
//$this->setOrderPaymentMethod($this->getPayment()->getMethod());//if you want the payment method's code (checkmo)
}

In the Mage_Adminhtml_Block_Sales_Order_Grid block _prepareColumns() method add this:
$this->addColumn('order_payment_method', array(
'header' => Mage::helper('sales')->__('Payment'),
'index' => 'order_payment_method',
));

Same as for the product skus, this will now work for previous orders, but you can run the same script as above to update all the old orders.

That's it. Let me know how it turns out.

8 comments:

  1. Hi! Sorry for the newbie question.. Where do I have to put the first two code blocks? :)
    Thanks!

    ReplyDelete
  2. You have to put them in the classes that you create and override the ones I sugested: Mage_Sales_Model_Order and Mage_Adminhtml_Block_Sales_Order_Grid

    You can fing the proper way to override models, blocks and helpers here: http://magedev.com/2009/06/03/magento-overriding-model-block-or-helper/

    ReplyDelete
  3. i've implemented this code attempt 1. by overriding the classes ... no SKUS but column showing. 2. tried without overriding.. same problem column present but no SKUS.
    Magento v1.4.0.1

    ReplyDelete
  4. Hi Rizz. This is normal, because the previously created orders don't have an SKUS column. Try to create an new order and see it for that one the SKUS appear.
    If no...I'm sorry, but this is how it worked for me on Magento 1.1.6 and 1.4.0.1

    ReplyDelete
  5. Thank you for the swift reply.
    I first added a new order ( column remains empty ). And also ran your population script.

    What is noticed is that this is called: if(!$this->getProductSkus())
    I was wondering where this method is defined?
    Can i safely remove the if statement?

    ReplyDelete
  6. The method is not defined. It's called by the 'magic' of __call method in Varien_Object class. You can try removing it.

    ReplyDelete
  7. Hi,

    Unfortunately without the if statement it doesn't show.. does not seem to fill the attribute.
    Tried both overriding and hacking core.
    Both no go unfortunately.

    ReplyDelete
  8. Ok. I just realized my stupidity. This does not work for 1.4.x.x. I had it configured for 1.3.x.x and upon upgrading, the feature is still available. It does not work if you develop it like that for 1.4.x.x.

    Again, I'm sorry for this. I will post soon a way to add this for the 1.4.x.x version.

    ReplyDelete