4

I need to get customer life time sales data. Both Qty and Amount.

I have tried below Mysql Query it's worked fine.

How can i change this Mysql query in Magento collection format.

Because this data showing in Magento Grid.

select CAST(SUM( t.qty )as UNSIGNED) AS `qty`,  SUM( t.grand_total )as grand_total, t.customer_id , t.customer_name, t.customer_email_id,  date_format(t.customer_dob, '%d-%m-%Y') as customer_dob from (

SELECT `main_table`.`entity_id`, `main_table`.`customer_id`, `main_table`.`grand_total`, `cusFirstnameTb`.`value` AS `customer_first_name`, `cusLastnameTb`.`value` AS `customer_last_name`, CONCAT(cusFirstnameTb.value, ' ', cusLastnameTb.value) AS `customer_name`, `cusDOBTb`.`value` AS `customer_dob`, `cusEmailTb`.`email` AS `customer_email_id`, `sub`.`qty_canceled`, `sub`.`qty_refunded`, `sub`.`qty_ordered`, CAST(SUM( qty_ordered )as UNSIGNED) AS qty, CAST(SUM( grand_total )as UNSIGNED) AS grand_total1, SUM( grand_total ) AS `grand_total2` FROM `sales_flat_order_grid` AS `main_table` 

INNER JOIN `customer_entity_varchar` AS `cusFirstnameTb` ON main_table.customer_id = cusFirstnameTb.entity_id AND cusFirstnameTb.attribute_id = 5 AND cusFirstnameTb.entity_type_id = 1 

INNER JOIN `customer_entity_varchar` AS `cusLastnameTb` ON main_table.customer_id = cusLastnameTb.entity_id AND cusLastnameTb.attribute_id = 7 AND cusLastnameTb.entity_type_id = 1 

INNER JOIN `customer_entity_datetime` AS `cusDOBTb` ON main_table.customer_id = cusDOBTb.entity_id AND cusDOBTb.attribute_id = 11 AND cusDOBTb.entity_type_id = 1 

INNER JOIN `customer_entity` AS `cusEmailTb` ON main_table.customer_id = cusEmailTb.entity_id AND cusEmailTb.entity_type_id = 1 

INNER JOIN `sales_flat_order_item` AS `sub` ON main_table.entity_id = sub.order_id 

WHERE ( cusDOBTb.value = "2014-08-21" ) 

GROUP BY `order_id` 

ORDER BY `order_id` ASC

) t GROUP BY t.customer_id 

This result i get from PhpMyadmin, run above query .

enter image description here

Thanks for your help

4
  • can you please in explain briefly..can you out sql result in question thus i will works on it...
    – Amit Bera
    Aug 21, 2014 at 10:57
  • @AmitBera I need particular customer total amount of sales and total qty of sales for each and every customer. And Want to display these details in Magento Grid.
    – VijayS91
    Aug 21, 2014 at 12:41
  • why you have added sales_order_item table??
    – Amit Bera
    Aug 21, 2014 at 15:24
  • @AmitBera i have tried to write "select t.* from ( select * from table ) t" this type query in Magento Collection.
    – VijayS91
    Aug 22, 2014 at 5:18

2 Answers 2

4

Doesn't seem that hard. Basically you would have to instantiate the customer collection, join it with the sales collections and apply some conditions. The sales table has most of the information you'll need except for the dob.

$collection = Mage::getResourceModel('customer/customer_collection')
        ->joinAttribute('dob','customer/dob', 'entity_id')
        ->join(array('orders'=>'sales_flat_order',
                     'orders.customer_id=main_table.entity_id', 
                     array('total_item_count','base_grand_total', 'customer_firstname', 'customer_lastname')))
        ->getSelect()
        ->reset(Zend_DB_Select::COLUMNS)
        ->columns(array(
                        'SUM(orders.total_item_count)'=>'qty',
                        'SUM(orders.base_grand_total)'=>'grand_total',
                        'orders.customer_firstname',
                        'orders.customer_lastname',
                        'dob'))
        ->group('main_table.entity_id')
        ->where('dob = SOMETHING');

I haven't tested it but this is how I would write it. Be sure to enable SQL logging in your Magento and adjust the collection according to the SQL result.

To format the dob as you wanted, I suggest using a renderer.

If you have any questions, feel free to ask.

4
  • This query its not working. showing below error : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.qty' in 'field list'
    – VijayS91
    Aug 21, 2014 at 14:01
  • There is no "e" table and I don't ask for qty field from anywhere in my query. Are you sure the error is generated by this collection? Aug 21, 2014 at 14:07
  • Yes @samuel see this line ->columns(array('SUM(orders.total_item_count)'=>'qty',
    – VijayS91
    Aug 21, 2014 at 14:11
  • That line is the Magento way of saying: "SELECT SUM(orders.total_item_count) AS qty" Aug 21, 2014 at 14:13
2

Try this

$firstname = Mage::getResourceModel('customer/customer')->getAttribute('firstname');     
    $lastname  = Mage::getResourceModel('customer/customer')->getAttribute('lastname');
    $dob = Mage::getResourceModel('customer/customer')->getAttribute('dob');     

        $Collection=Mage::getResourceModel('sales/order_collection')->addFieldToSelect(array('customer_id','customer_email'))
        ->addFieldToFilter('customer_id',array('notnull'=>true));
        $Collection->getSelect()

            ->columns(array('ordered_qty' => 'SUM(main_table.total_qty_ordered)'))
            ->columns(array('ordered_total' => 'SUM(main_table.grand_total)'))

            ->group('main_table.customer_id');
        $Collection->getSelect()
            ->joinLeft(
            array('customer_lastname_table' => $lastname->getBackend()->getTable()),
            'customer_lastname_table.entity_id = main_table.customer_id
            AND customer_lastname_table.attribute_id = '.(int) $lastname->getAttributeId() . '
            ',
            array('customer_lastname'=>'value')
            )
            ->joinLeft(
            array('customer_firstname_table' =>$firstname->getBackend()->getTable()),
            'customer_firstname_table.entity_id = main_table.customer_id
            AND customer_firstname_table.attribute_id = '.(int) $firstname->getAttributeId() . '
            ',
            array('customer_firstname'=>'value'))
            ->joinLeft(
            array('customer_dob_table' => $dob->getBackend()->getTable()),
            'customer_dob_table.entity_id = main_table.customer_id
            AND customer_dob_table.attribute_id = '.(int) $dob->getAttributeId() . '
            ',
            array('customer_dob'=>'value')
            )


            ->columns(new Zend_Db_Expr("CONCAT(IFNULL(customer_firstname_table.value,''),' ',IFNULL(customer_lastname_table.value,'')) AS fullname"))
            ->columns('*'); 
        echo $Collection->getSelect();
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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