* @copyright 2007-2011 PrestaShop SA
* @version Release: $Revision: 6594 $
* @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 StatsSales extends ModuleGraph
{
private $_html = '';
private $_query = '';
private $_query2 = '';
private $_option = '';
private $id_country = '';
function __construct()
{
$this->name = 'statssales';
$this->tab = 'analytics_stats';
$this->version = 1.0;
$this->author = 'PrestaShop';
$this->need_instance = 0;
parent::__construct();
$this->displayName = $this->l('Sales and orders');
$this->description = $this->l('Display the sales evolution and orders by statuses');
}
public function install()
{
return (parent::install() AND $this->registerHook('AdminStatsModules'));
}
public function hookAdminStatsModules($params)
{
global $cookie;
$totals = $this->getTotals();
$currency = new Currency((int)Configuration::get('PS_CURRENCY_DEFAULT'));
if (($id_export = (int)Tools::getValue('export')) == 1)
$this->csvExport(array('layers' => 2, 'type' => 'line', 'option' => '1-'.(int)Tools::getValue('id_country')));
elseif ($id_export == 2)
$this->csvExport(array('layers' => 0, 'type' => 'line', 'option' => '2-'.(int)Tools::getValue('id_country')));
elseif ($id_export == 3)
$this->csvExport(array('type' => 'pie', 'option' => '3-'.(int)Tools::getValue('id_country')));
$this->_html = '
';
return $this->_html;
}
private function getTotals()
{
$result1 = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT COUNT(o.`id_order`) as orderCount, SUM(o.`total_paid_real` / o.conversion_rate) as orderSum
FROM `'._DB_PREFIX_.'orders` o
'.((int)(Tools::getValue('id_country')) ? 'LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address' : '').'
WHERE o.valid = 1
'.((int)(Tools::getValue('id_country')) ? 'AND a.id_country = '.(int)Tools::getValue('id_country') : '').'
AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween());
$result2 = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT SUM(od.product_quantity) as products
FROM `'._DB_PREFIX_.'orders` o
LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON od.`id_order` = o.`id_order`
'.((int)(Tools::getValue('id_country')) ? 'LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address' : '').'
WHERE o.valid = 1
'.((int)Tools::getValue('id_country') ? 'AND a.id_country = '.(int)Tools::getValue('id_country') : '').'
AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween());
return array_merge($result1, $result2);
}
public function setOption($options, $layers = 1)
{
list($this->_option, $this->id_country) = explode('-', $options);
switch ($this->_option)
{
case 1:
$this->_titles['main'][0] = $this->l('Products and orders');
$this->_titles['main'][1] = $this->l('Orders');
$this->_titles['main'][2] = $this->l('Products');
break;
case 2:
$currency = new Currency((int)Configuration::get('PS_CURRENCY_DEFAULT'));
$this->_titles['main'] = $this->l('Sales in').' '.$currency->iso_code;
break;
case 3:
$this->_titles['main'] = $this->l('Percentage of orders by status');
break;
}
}
protected function getData($layers)
{
if ($this->_option == 3)
return $this->getStatesData();
$this->_query = '
SELECT o.`invoice_date`, o.`total_paid_real` / o.conversion_rate AS total_paid_real, SUM(od.product_quantity) as product_quantity
FROM `'._DB_PREFIX_.'orders` o
LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON od.`id_order` = o.`id_order`
'.((int)($this->id_country) ? 'LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address' : '').'
WHERE o.valid = 1
'.((int)($this->id_country) ? 'AND a.id_country = '.(int)$this->id_country : '').'
AND o.`invoice_date` BETWEEN ';
$this->_query2 = ' GROUP BY o.id_order';
$this->setDateGraph($layers, true);
}
protected function setAllTimeValues($layers)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query.$this->getDate().$this->_query2);
foreach ($result AS $row)
if ($this->_option == 1)
{
$this->_values[0][(int)(substr($row['invoice_date'], 0, 4))] += 1;
$this->_values[1][(int)(substr($row['invoice_date'], 0, 4))] += $row['product_quantity'];
}
else
$this->_values[(int)(substr($row['invoice_date'], 0, 4))] += $row['total_paid_real'];
}
protected function setYearValues($layers)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query.$this->getDate().$this->_query2);
foreach ($result AS $row) {
$mounth = (int)substr($row['invoice_date'], 5, 2);
if ($this->_option == 1)
{
if (!isset($this->_values[0][$mounth]))
$this->_values[0][$mounth] = 0;
if (!isset($this->_values[1][$mounth]))
$this->_values[1][$mounth] = 0;
$this->_values[0][$mounth] += 1;
$this->_values[1][$mounth] += $row['product_quantity'];
}
else
{
if (!isset($this->_values[$mounth]))
$this->_values[$mounth] = 0;
$this->_values[$mounth] += $row['total_paid_real'];
}
}
}
protected function setMonthValues($layers)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query.$this->getDate().$this->_query2);
foreach ($result AS $row)
if ($this->_option == 1)
{
$this->_values[0][(int)(substr($row['invoice_date'], 8, 2))] += 1;
$this->_values[1][(int)(substr($row['invoice_date'], 8, 2))] += $row['product_quantity'];
}
else
$this->_values[(int)(substr($row['invoice_date'], 8, 2))] += $row['total_paid_real'];
}
protected function setDayValues($layers)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query.$this->getDate().$this->_query2);
foreach ($result AS $row)
if ($this->_option == 1)
{
$this->_values[0][(int)(substr($row['invoice_date'], 11, 2))] += 1;
$this->_values[1][(int)(substr($row['invoice_date'], 11, 2))] += $row['product_quantity'];
}
else
$this->_values[(int)(substr($row['invoice_date'], 11, 2))] += $row['total_paid_real'];
}
private function getStatesData()
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
SELECT osl.`name`, COUNT(oh.`id_order`) as total
FROM `'._DB_PREFIX_.'order_state` os
LEFT JOIN `'._DB_PREFIX_.'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = '.(int)($this->getLang()).')
LEFT JOIN `'._DB_PREFIX_.'order_history` oh ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `'._DB_PREFIX_.'orders` o ON o.`id_order` = oh.`id_order`
'.((int)($this->id_country) ? 'LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address' : '').'
WHERE oh.`id_order_history` = (
SELECT ios.`id_order_history`
FROM `'._DB_PREFIX_.'order_history` ios
WHERE ios.`id_order` = oh.`id_order`
ORDER BY ios.`date_add` DESC, oh.`id_order_history` DESC
LIMIT 1
)
'.((int)($this->id_country) ? 'AND a.id_country = '.(int)($this->id_country) : '').'
AND o.`date_add` BETWEEN '.ModuleGraph::getDateBetween().'
GROUP BY oh.`id_order_state`');
foreach ($result as $row)
{
$this->_values[] = $row['total'];
$this->_legend[] = $row['name'];
}
}
}