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

  • can you please in explain briefly..can you out sql result in question thus i will works on it... – Amit Bera Aug 21 '14 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 '14 at 12:41
  • why you have added sales_order_item table?? – Amit Bera Aug 21 '14 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 '14 at 5:18
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.

  • This query its not working. showing below error : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.qty' in 'field list' – VijayS91 Aug 21 '14 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? – Samuel Coman Aug 21 '14 at 14:07
  • Yes @samuel see this line ->columns(array('SUM(orders.total_item_count)'=>'qty', – VijayS91 Aug 21 '14 at 14:11
  • That line is the Magento way of saying: "SELECT SUM(orders.total_item_count) AS qty" – Samuel Coman Aug 21 '14 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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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