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
