Wednesday, November 21, 2012

Magento – Customize Backend Order Grid to have SKU, E-mail Address, and Phone Number

Sometimes magento’s default back-end order grid doesn’t cut it.  I recently had a customer that wanted to be able to sort orders by sku, shipping address, shipping state, and customer e-mail address on their magento site.  Why did they want to do this?  To quickly generate reports I guess, who knows? By default you can’t sort by these items.  You have to add the fields to magento which can be some what tricky.
Anyway if you want to change the order grid from this:
Magento Order Grid on a Magento Web site with SKUs
To this on your magento web site:
Magento Order Grid with SKU
Just follow these few easy steps.
Step one create a new directory on your server at app/code/local/Mage/Adminhtml/Block/Sales/Order
Here we will be creating a file called Grid.php.
First navigate to app/code/core/Mage/Adminhtml/Block/Sales/Order and copy\paste Grid.php into the directory you just created.
Now open up app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php
Look for the following:
1
2
3
4
protected function _getCollectionClass()
{
return 'sales/order_grid_collection';
}
Now change it to :
1
2
3
4
protected function _getCollectionClass()
{
return 'sales/order_grid_collection';
}
Next find:
1
2
3
4
5
6
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
$this->setCollection($collection);
return parent::_prepareCollection();
}
and make it:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass())
->join(
'sales/order_item',
'`sales/order_item`.order_id=`main_table`.entity_id',
array(
'skus' => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ",")'),
)
);

$collection->getSelect()->group('main_table.entity_id');

$collection->getSelect()->joinLeft(array('sfog' => 'sales_flat_order_grid'),
'main_table.entity_id = sfog.entity_id',array('sfog.shipping_name','sfog.billing_name'));

$collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),
'sfo.entity_id=main_table.entity_id',array('sfo.customer_email','sfo.weight',
'sfo.discount_description','sfo.increment_id','sfo.store_id','sfo.created_at','sfo.status',
'sfo.base_grand_total','sfo.grand_total'));

$collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),
'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street',
'sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone'));

$this->setCollection($collection);

return parent::_prepareCollection();
}
Finally find:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
protected function _prepareColumns()
{

$this->addColumn('real_order_id', array(
'header'=> Mage::helper('sales')->__('Order #'),
'width' => '80px',
'type' => 'text',
'index' => 'sfo.increment_id',
));

if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn('store_id', array(
'header' => Mage::helper('sales')->__('Purchased From (Store)'),
'index' => 'store_id',
'type' => 'store',
'store_view'=> true,
'display_deleted' => true,
));
}

$this->addColumn('created_at', array(
'header' => Mage::helper('sales')->__('Purchased On'),
'index' => 'created_at',
'type' => 'datetime',
'width' => '100px',
));

$this->addColumn('billing_name', array(
'header' => Mage::helper('sales')->__('Bill to Name'),
'index' => 'billing_name',
));

$this->addColumn('shipping_name', array(
'header' => Mage::helper('sales')->__('Ship to Name'),
'index' => 'shipping_name',
));

$this->addColumn('base_grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Base)'),
'index' => 'base_grand_total',
'type' => 'currency',
'currency' => 'base_currency_code',
));

$this->addColumn('grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
'index' => 'grand_total',
'type' => 'currency',
'currency' => 'order_currency_code',
));

$this->addColumn('status', array(
'header' => Mage::helper('sales')->__('Status'),
'index' => 'status',
'type' => 'options',
'width' => '70px',
'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
));
Change it to:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
protected function _prepareColumns()
{

$this->addColumn('real_order_id', array(
'header'=> Mage::helper('sales')->__('Order #'),
'width' => '80px',
'type' => 'text',
'index' => 'increment_id',
'filter_index' => 'sfo.increment_id',
));

if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn('store_id', array(
'header' => Mage::helper('sales')->__('Purchased From (Store)'),
'index' => 'store_id',
'type' => 'store',
'store_view'=> true,
'display_deleted' => true,
));
}

$this->addColumn('created_at', array(
'header' => Mage::helper('sales')->__('Purchased On'),
'index' => 'created_at',
'filter_index' => 'sfo.created_at',
'type' => 'datetime',
'width' => '50px',
));

$this->addColumn('skus', array(
'header' => Mage::helper('Sales')->__('Skus'),
'width' => '350px',
'index' => 'skus',
'filter_index' => 'sku',
'type' => 'text',

));
$this->addColumn('shipping_street', array(
'header' => Mage::helper('sales')->__('Shipping Street'),
'index' => 'street',
'type' => 'text',
'filter_index' => 'sfoa.street',
'width' => '100px',
));
$this->addColumn('shipping_city', array(
'header' => Mage::helper('sales')->__('Shipping City'),
'index' => 'city',
'type' => 'text',
'filter_index' => 'sfoa.city',
'width' => '50px',
));
$this->addColumn('shipping_region', array(
'header' => Mage::helper('sales')->__('Shipping Region'),
'index' => 'region',
'type' => 'text',
'filter_index' => 'sfoa.region',
'width' => '50px',
));
$this->addColumn('customer_email', array(
'header' => Mage::helper('sales')->__('Customer Email'),
'index' => 'customer_email',
'type' => 'text',
'filter_index' => 'sfo.customer_email',
));

$this->addColumn('grand_total', array(
'header' => Mage::helper('sales')->__('Total'),
'index' => 'grand_total',
'filter_index' => 'sfo.grand_total',
'type' => 'currency',
'currency' => 'order_currency_code',
));

$this->addColumn('status', array(
'header' => Mage::helper('sales')->__('Status'),
'index' => 'status',
'filter_index' => 'sfo.status',
'type' => 'options',
'width' => '70px',
'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
));
That is it, if you wanted to add lets say product weight you would use the following:
1
2
3
4
5
6
$this->addColumn('order_weight', array(
'header' => Mage::helper('sales')->__('Weight'),
'index' => 'order_weight',
'type' => 'text',
'filter_index' => 'sfo.weight',
));
Let’s say for some reason when you are doing searches in your brand new boxes and you get an error like
1
SQLSTATE[23000]: Integrity constraint violation: 1052 Column'status' in where clause is ambiguous
This means you did not add a filter type properly go back a line like this:
1
'filter_index' => 'sfo.itemname',
This should fix the error and fix your magento web site’s order grid!  Now you should be able to sort by sku, address, name, e-mail address. Shoot you can easily add the telephone number to this order grid if you want!
In case you are wondering I referenced these posted.  It took me a while to get the SKU’s to play nicely with all of the other information.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)