Get Customer's Custom Attribure in Order and Invoice Grids in Magento

Question

I have scoured the internet for an answer, including many SO questions but none of which seem to provide me with what I need.

I have a custom attribute for customers (customer_number). I need to be able to show this in both the Orders and Invoices grids in the Admin area.

I have already got the customer email by copying Mage_Adminhtml_Block_Orders_Grid to the local pool and using the code below

$collection->getSelect()
    ->join(
        'sales_flat_order_address',
        'main_table.entity_id = sales_flat_order_address.parent_id',
        array('email')
    );

This, obviously, only works for the Orders grid.

Can anyone help me with getting the customer_number attribute in these tables?

Using CE 1.9.

UPDATE

I got the column added with the following code in _prepareCollection():

$gen_number_attr = Mage::getSingleton('customer/customer')->getResource()->getAttribute('gen_number');
$collection->getSelect()->joinLeft(
    array(
       'table_customer_number' => $gen_number_attr->getBackend()->getTable()),
       'main_table.customer_id = table_customer_number.entity_id AND table_customer_number.attribute_id = '.$gen_number_attr->getId(). ' AND table_customer_number.entity_type_id = '.Mage::getSingleton('customer/customer')->getResource()->getTypeId(),
        array(
           'customer_number' =>'table_customer_number.value'
        )
    );

The problem now is that when I try to filter I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'customer_number' in 'where clause', query was: SELECT COUNT(*) FROM `sales_flat_order_grid` AS `main_table`
INNER JOIN `sales_flat_order_address` ON main_table.entity_id = sales_flat_order_address.parent_id WHERE (`customer_number` LIKE '%123%')

Any help would be appreciated!

Answer 1

Order:- Copy the default 'app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.phtml' and place it in 'app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.phtml'. Now Open the Grid.php file from its new local directory and look at the following block of code carefully:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
            /* adding term and condition section */
                 $customerterm_conditionAttr = Mage::getSingleton('customer/customer')->getResource()->getAttribute('term_condition');
                 $collection->getSelect()
                            ->joinLeft(
                                array('cusTerm_conditionTb' => $customerterm_conditionAttr->getBackend()->getTable()),
                                'main_table.customer_id = cusTerm_conditionTb.entity_id AND cusTerm_conditionTb.attribute_id = '.$customerterm_conditionAttr->getId(). ' AND cusTerm_conditionTb.entity_type_id = '.Mage::getSingleton('customer/customer')->getResource()->getTypeId(),
                                array('term_condition' =>'cusTerm_conditionTb.value')
                            ); 
            /* end adding term and condition section */  
    $this->setCollection($collection);
    return parent::_prepareCollection();
}
protected function _prepareColumns()
    {
      /* adding term and condition tab */
        $this->addColumn('term_condition', array(
            'header' => Mage::helper('sales')->__('Term and condition'),
            'index' => 'term_condition',
        ));
        /* adding term and condition tab */
    }

Invoice:- Copy the default 'app/code/core/Mage/Adminhtml/Block/Sales/Invoice/Grid.phtml' and place it in 'app/code/local/Mage/Adminhtml/Block/Sales/Invoice/Grid.phtml'. Now Open the Grid.php file from its new local directory and look at the following block of code carefully:

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
          /* adding term and condition section */
            $customerterm_conditionAttr = Mage::getSingleton('customer/customer')->getResource()->getAttribute('term_condition');
            $collection->getSelect()
                            ->joinLeft(
                                array('sales_flat_order'),
                                'main_table.order_id = sales_flat_order.entity_id',
                                array('customer_id' =>'customer_id')
                            );
            $collection->getSelect()
                            ->joinLeft(
                                array('cusTerm_conditionTb' => $customerterm_conditionAttr->getBackend()->getTable()),
                                'sales_flat_order.customer_id = cusTerm_conditionTb.entity_id AND cusTerm_conditionTb.attribute_id = '.$customerterm_conditionAttr->getId(). ' AND cusTerm_conditionTb.entity_type_id = '.Mage::getSingleton('customer/customer')->getResource()->getTypeId(),
                                array('term_condition' =>'cusTerm_conditionTb.value')
                            );                    
            /* end adding term and condition section */ 
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }
protected function _prepareColumns()
    {
       /* adding term and condition tab */
       $this->addColumn('term_condition', array(
            'header' => Mage::helper('sales')->__('Term and condition'),
            'index' => 'term_condition',
        ));
        /* adding term and condition tab */
    }