• 23rd, Jul 2010



Magento Customer by Orders Total Report PHP Script

mysql_connect("localhost","root","root", 3306) or die(mysql_error()); 
mysql_select_db("MagentoDatabase") or die(mysql_error());
//Get Customer Entity Type Id
$query = "SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = 'customer'";
$query_Row = mysql_query($query);
$qs =  mysql_fetch_assoc($query_Row);
$customerEntity = $qs["entity_type_id"];
//Get Customer Entity Address Id
$query = "SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = 'customer_address'";
$query_Row = mysql_query($query);
$qs =  mysql_fetch_assoc($query_Row);
$customerAddressEntity = $qs["entity_type_id"];

//Get Attributes Ids For Customer
$attributes = array("'email'", "'prefix'", "'firstname'", "'lastname'", "'default_billing'");
$query = "SELECT attribute_code, attribute_id FROM eav_attribute WHERE attribute_code IN ( ".implode(",", $attributes)." ) AND entity_type_id = ".$customerEntity;
$query_Row = mysql_query($query);
while($qs =  mysql_fetch_assoc($query_Row)){
	$attributeIds[$qs["attribute_code"]] = $qs["attribute_id"];	
}

//Get Attributes Ids For Customer Address
$attributes = array("'company'", "'street'", "'postcode'", "'city'", "'region'", "'country_id'", "'telephone'");
$query = "SELECT attribute_code, attribute_id FROM eav_attribute WHERE attribute_code IN ( ".implode(",", $attributes)." ) AND entity_type_id = ".$customerAddressEntity;
$query_Row = mysql_query($query);
while($qs =  mysql_fetch_assoc($query_Row)){
	$attributeIds[$qs["attribute_code"]] = $qs["attribute_id"];	
}

$query = "
SELECT e.customer_id AS customer_id, ROUND(SUM((e.base_subtotal-IFNULL(e.base_subtotal_refunded,0)-IFNULL(e.base_subtotal_canceled,0))*e.base_to_global_rate), 2) AS orders_sum_amount, ROUND(AVG((e.base_subtotal-IFNULL(e.base_subtotal_refunded,0)-IFNULL(e.base_subtotal_canceled,0))*e.base_to_global_rate),2) AS orders_avg_amount, COUNT( e.entity_id ) AS  `orders_count` ,  `_table_email`.`email`, `_table_prefix`.`value` AS `prefix`, `_table_firstname`.`value` AS `firstname`, `_table_lastname`.`value` AS `lastname`, `_table_default_billing`.`value` AS `default_billing`, `_table_billing_company`.`value` AS `billing_company`, `_table_billing_street`.`value` AS `billing_street`, `_table_billing_postcode`.`value` AS `billing_postcode`, `_table_billing_city`.`value` AS `billing_city`, `_table_billing_region`.`value` AS `billing_region`, `_table_billing_country_id`.`value` AS `billing_country_id`, `_table_billing_telephone`.`value` AS `billing_telephone`
FROM  `sales_order` AS  `e` 
INNER JOIN `customer_entity_varchar` AS `_table_prefix` ON (_table_prefix.entity_id = e.customer_id) AND (_table_prefix.attribute_id=".$attributeIds['prefix'].") 
INNER JOIN `customer_entity_varchar` AS `_table_firstname` ON (_table_firstname.entity_id = e.customer_id) AND (_table_firstname.attribute_id=".$attributeIds['firstname'].") 
INNER JOIN `customer_entity_varchar` AS `_table_lastname` ON (_table_lastname.entity_id = e.customer_id) AND (_table_lastname.attribute_id=".$attributeIds['lastname'].") 
INNER JOIN `customer_entity_int` AS `_table_default_billing` ON (_table_default_billing.entity_id = e.customer_id) AND (_table_default_billing.attribute_id=".$attributeIds['default_billing'].") 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_company` ON (_table_billing_company.entity_id = _table_default_billing.value) AND (_table_billing_company.attribute_id=".$attributeIds['company'].") 
LEFT JOIN `customer_address_entity_text` AS `_table_billing_street` ON (_table_billing_street.entity_id = _table_default_billing.value) AND (_table_billing_street.attribute_id=".$attributeIds['street'].") 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_postcode` ON (_table_billing_postcode.entity_id = _table_default_billing.value) AND (_table_billing_postcode.attribute_id=".$attributeIds['postcode'].") 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_city` ON (_table_billing_city.entity_id = _table_default_billing.value) AND (_table_billing_city.attribute_id=".$attributeIds['city'].") 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_region` ON (_table_billing_region.entity_id = _table_default_billing.value) AND (_table_billing_region.attribute_id=".$attributeIds['region'].") 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_country_id` ON (_table_billing_country_id.entity_id = _table_default_billing.value) AND (_table_billing_country_id.attribute_id=".$attributeIds['country_id'].") 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_telephone` ON (_table_billing_telephone.entity_id = _table_default_billing.value) AND (_table_billing_telephone.attribute_id=".$attributeIds['telephone'].") 
INNER JOIN `customer_entity` AS `_table_email` ON (_table_email.entity_id = e.customer_id) 
WHERE (
e.entity_type_id =  '11'
)
AND (
e.state !=  'canceled'
)
AND (
e.created_at >=  '".date('1900-01-01 00:00:00')."'
AND e.created_at <=  '".date('Y-m-d H:i:s')."'
)
GROUP BY  `e`.`customer_id` 
ORDER BY orders_sum_amount DESC
";
$query_Row = mysql_query($query);
$i=0;
echo "<pre>";
//Headers
$headers = array("customer_id", "email", "orders_sum_amount", "orders_avg_amount", "orders_count", "prefix", "firstname", "lastname", "default_billing","billing_company","billing_street1","billing_street2","billing_street3","billing_postcode","billing_city","billing_region","billing_country_id","billing_telephone");
foreach($headers as $header){
	echo addThis($header);
	if($header != $headers[count($headers) - 1])
		echo ",";
}
echo "\n";
//Rows
while($qs =  mysql_fetch_assoc($query_Row))
{
	foreach($headers as $header){
		//Split Address Lines In 3 Fields (billing_street1, billing_street2, billing_street3)
		if(($header == "billing_street1")||($header == "billing_street2")||($header == "billing_street3")) {
			if($header == "billing_street1") {
				$address = $qs["billing_street"];
		   		$addressLines = preg_split('/[\n\r]+/', str_replace(array('"', '\\'), array('""', '\\\\'), $address), 3);
	   			$i=0;
				while($i<3){
					if(isset($addressLines[$i]))
						echo '"'.$addressLines[$i].'"';
					else
						echo '""';
					$i++;
					echo ",";
				}
			}
		}
		else {
			echo addThis($qs[$header]);		
			//Add Comma
			if($header != $headers[count($headers) - 1])
				echo ",";
		}
	}
	echo "\n";
}
echo "</pre>";


function addThis($text){
	return '"' . $text . '"';
}
/* This is the final SQL query generated */
SELECT 
e.customer_id AS customer_id, ROUND(SUM((e.base_subtotal-IFNULL(e.base_subtotal_refunded,0)-IFNULL(e.base_subtotal_canceled,0))*e.base_to_global_rate), 2) AS orders_sum_amount, ROUND(AVG((e.base_subtotal-IFNULL(e.base_subtotal_refunded,0)-IFNULL(e.base_subtotal_canceled,0))*e.base_to_global_rate),2) AS orders_avg_amount, COUNT( e.entity_id ) AS `orders_count` , `_table_email`.`email`, `_table_prefix`.`value` AS `prefix`, `_table_firstname`.`value` AS `firstname`, `_table_lastname`.`value` AS `lastname`, `_table_default_billing`.`value` AS `default_billing`, `_table_billing_company`.`value` AS `billing_company`, `_table_billing_street`.`value` AS `billing_street`, `_table_billing_postcode`.`value` AS `billing_postcode`, `_table_billing_city`.`value` AS `billing_city`, `_table_billing_region`.`value` AS `billing_region`, `_table_billing_country_id`.`value` AS `billing_country_id`, `_table_billing_telephone`.`value` AS `billing_telephone` 
FROM `sales_order` AS `e` 
INNER JOIN `customer_entity_varchar` AS `_table_prefix` ON (_table_prefix.entity_id = e.customer_id) AND (_table_prefix.attribute_id=4) 
INNER JOIN `customer_entity_varchar` AS `_table_firstname` ON (_table_firstname.entity_id = e.customer_id) AND (_table_firstname.attribute_id=5) 
INNER JOIN `customer_entity_varchar` AS `_table_lastname` ON (_table_lastname.entity_id = e.customer_id) AND (_table_lastname.attribute_id=7) 
INNER JOIN `customer_entity_int` AS `_table_default_billing` ON (_table_default_billing.entity_id = e.customer_id) AND (_table_default_billing.attribute_id=13) 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_company` ON (_table_billing_company.entity_id = _table_default_billing.value) AND (_table_billing_company.attribute_id=22) 
LEFT JOIN `customer_address_entity_text` AS `_table_billing_street` ON (_table_billing_street.entity_id = _table_default_billing.value) AND (_table_billing_street.attribute_id=23) 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_postcode` ON (_table_billing_postcode.entity_id = _table_default_billing.value) AND (_table_billing_postcode.attribute_id=28) 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_city` ON (_table_billing_city.entity_id = _table_default_billing.value) AND (_table_billing_city.attribute_id=24) 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_region` ON (_table_billing_region.entity_id = _table_default_billing.value) AND (_table_billing_region.attribute_id=26) 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_country_id` ON (_table_billing_country_id.entity_id = _table_default_billing.value) AND (_table_billing_country_id.attribute_id=25) 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_telephone` ON (_table_billing_telephone.entity_id = _table_default_billing.value) AND (_table_billing_telephone.attribute_id=29) 
INNER JOIN `customer_entity` AS `_table_email` ON (_table_email.entity_id = e.customer_id) WHERE ( e.entity_type_id = '11' ) AND ( e.state != 'canceled' ) AND ( e.created_at >= '2010-06-07 00:00:00' AND e.created_at <= '2020-01-01 23:59:59' ) 
GROUP BY `e`.`customer_id` 
ORDER BY orders_sum_amount DESC 

Tags: , , , , , , , ,

3 comments on “Magento Customer by Orders Total Report PHP Script

  1. Hey, awesome query, any chacne you will update it for the current 1.6.x version of magento?
    It is more than needed!

  2. This is far from the reccomended approach. All you are doing is executing raw SQL via PHP. This is never reccomended for Magento. There is an API for a reason.

  3. Ignacio Pascual on said:

    You are absolutely right, this is a 3 years old post :-|

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

HTML tags are not allowed.

© 2010 unexpected[it]. All Rights Reserved.