Magento – How to add shipped date to admin sales order grid

adminadmin-panelgridsales-ordershipment

I am stuck with adding new column(Shipped date) to sales order admin grid. I copied the file Grid.php from app/code/core/Mage/Adminhtml/Block/Sales/Order to app/code/local/Mage/Adminhtml/Block/Sales/Order and in the _prepareCollection() function added the code

$collection->getSelect()->joinLeft('sales_flat_shipment_grid','sales_flat_shipment_grid.order_id=main_table.entity_id',array('shipped_date' => 'sales_flat_shipment_grid.created_at'));

and in _prepareColumns() added this

$this->addColumn('shipped_date', array(
      'header'    => Mage::helper('sales')->__('Shipped Date'),
      'index'     => 'shipped_date',
      'type'      => 'datetime',
      'filter_index'=>'sales_flat_shipment_grid.created_at',
));

My problem is when there are orders with multiple shipments and when I go to the sales order grid I get the error There has been an error processing your request which says there is a duplicate order id.
If there are no multiple shipments for orders, then the sales order grid is fine and shows the shipped date for the corresponding orders.

Could someone help me with this?

Best Answer

As the order can has multiple shipments, you may use sub-query:

in the _prepareCollection()

$table = Mage::getSingleton('core/resource')
    ->getTableName('sales_flat_shipment_grid');
$shipping = "((select max(t.created_at) from `$table` as t where t.order_id=main_table.entity_id))";
$collection->getSelect()->from('', "$shipping as shipped_date");

in the _prepareColumns():

$this->addColumn('shipped_date', array( 'header' => Mage::helper('sales')->__('Shipped Date'), 'index' => 'shipped_date', 'type' => 'datetime', ));

Related Solutions

Admin Order Grid Custom Fields – Filtering Issue

Hi Just add filter_index in increament_id field and it may works

        'filter_index' => 'main_table.increment_id',

and now code

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

));

Magento – How to add column for total orders in customer grid in magento

According to your Question asked, it seems like you have successfully added a Grand Total Column, you just need to add range boxes, that can help in filter.

Find the code below to add in customer Grid -

$this->addColumn('orders_count', array(
            'header'    => Mage::helper('customer')->__('Orders Count'),
            'width'     => '50px',
            'index'     => 'orders_count',
            'type'      => 'number',
        ));

[EDITED]

I have gone little bit deep in this Question and find that we can provide filter in the following manner -

$this->addColumn('orders_count', array(
            'header'    => Mage::helper('customer')->__('Orders Count'),
            'align'     => 'left',
            'width'     => '40px',
            'index'     => 'orders_count',
            'type'  => 'number',
            'sortable' => true,
            'filter_condition_callback' => array($this, '_filterHasOrderTotalBetweenCallback'),
        ));

and add the following function -

public function _filterHasOrderTotalBetweenCallback($collection, $column){

    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }
    if (!empty($value)) {
        $from = $value['from'];
        $to = $value['to'];           

        $this->getCollection()->getSelect()->having(
             "orders_count>=$from AND orders_count<=$to");
        Mage::log((string)$this->getCollection()->getSelect(),null,'customer.log');
    }        
    return $this;
}

Adding the above function is providing the result in the form of query which is running fine in MYSQL , but not accepted in Magento

Best Answer

Related Solutions

Admin Order Grid Custom Fields – Filtering Issue

Magento – How to add column for total orders in customer grid in magento

Related Topic