* @copyright 2007-2011 PrestaShop SA
* @version Release: $Revision: 6798 $
* @license http://opensource.org/licenses/afl-3.0.php Academic Free License (AFL 3.0)
* International Registered Trademark & Property of PrestaShop SA
*/
if (!defined('_CAN_LOAD_FILES_'))
exit;
class StatsBestCustomers extends ModuleGrid
{
private $_html;
private $_query;
private $_columns;
private $_defaultSortColumn;
private $_defaultSortDirection;
private $_emptyMessage;
private $_pagingMessage;
function __construct()
{
$this->name = 'statsbestcustomers';
$this->tab = 'analytics_stats';
$this->version = 1.0;
$this->author = 'PrestaShop';
$this->need_instance = 0;
$this->_defaultSortColumn = 'totalMoneySpent';
$this->_defaultSortDirection = 'DESC';
$this->_emptyMessage = $this->l('Empty recordset returned');
$this->_pagingMessage = $this->l('Displaying').' {0} - {1} '.$this->l('of').' {2}';
$this->_columns = array(
array(
'id' => 'lastname',
'header' => $this->l('Lastname'),
'dataIndex' => 'lastname',
'width' => 50
),
array(
'id' => 'firstname',
'header' => $this->l('Firstname'),
'dataIndex' => 'firstname',
'width' => 50
),
array(
'id' => 'email',
'header' => $this->l('Email'),
'dataIndex' => 'email',
'width' => 120
),
array(
'id' => 'totalVisits',
'header' => $this->l('Visits'),
'dataIndex' => 'totalVisits',
'width' => 80,
'align' => 'right'),
array(
'id' => 'totalMoneySpent',
'header' => $this->l('Money spent'),
'dataIndex' => 'totalMoneySpent',
'width' => 80,
'align' => 'right')
);
parent::__construct();
$this->displayName = $this->l('Best customers');
$this->description = $this->l('A list of the best customers');
}
public function install()
{
return (parent::install() AND $this->registerHook('AdminStatsModules'));
}
public function hookAdminStatsModules($params)
{
$engineParams = array(
'id' => 'id_customer',
'title' => $this->displayName,
'columns' => $this->_columns,
'defaultSortColumn' => $this->_defaultSortColumn,
'defaultSortDirection' => $this->_defaultSortDirection,
'emptyMessage' => $this->_emptyMessage,
'pagingMessage' => $this->_pagingMessage
);
if (Tools::getValue('export'))
$this->csvExport($engineParams);
$this->_html = '
';
return $this->_html;
}
public function setOption($option)
{
}
public function getData()
{
$this->_query = '
SELECT SQL_CALC_FOUND_ROWS c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`,
COUNT(co.`id_connections`) as totalVisits,
IFNULL((
SELECT ROUND(SUM(IFNULL(o.`total_paid_real`, 0) / cu.conversion_rate), 2)
FROM `'._DB_PREFIX_.'orders` o
LEFT JOIN `'._DB_PREFIX_.'currency` cu ON o.id_currency = cu.id_currency
WHERE o.id_customer = c.id_customer
AND o.invoice_date BETWEEN '.$this->getDate().'
AND o.valid
), 0) as totalMoneySpent
FROM `'._DB_PREFIX_.'customer` c
LEFT JOIN `'._DB_PREFIX_.'guest` g ON c.`id_customer` = g.`id_customer`
LEFT JOIN `'._DB_PREFIX_.'connections` co ON g.`id_guest` = co.`id_guest`
WHERE co.date_add BETWEEN '.$this->getDate().'
GROUP BY c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`';
if (Validate::IsName($this->_sort))
{
$this->_query .= ' ORDER BY `'.$this->_sort.'`';
if (isset($this->_direction) AND Validate::IsSortDirection($this->_direction))
$this->_query .= ' '.$this->_direction;
}
if (($this->_start === 0 OR Validate::IsUnsignedInt($this->_start)) AND Validate::IsUnsignedInt($this->_limit))
$this->_query .= ' LIMIT '.$this->_start.', '.($this->_limit);
$this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query);
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
}