* @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 StatsProduct extends ModuleGraph
{
private $_html = '';
private $_query = '';
private $_option = 0;
private $_id_product = 0;
function __construct()
{
$this->name = 'statsproduct';
$this->tab = 'analytics_stats';
$this->version = 1.0;
$this->author = 'PrestaShop';
$this->need_instance = 0;
parent::__construct();
$this->displayName = $this->l('Product details');
$this->description = $this->l('Get detailed statistics for each product.');
}
public function install()
{
return (parent::install() AND $this->registerHook('AdminStatsModules'));
}
public function getTotalBought($id_product)
{
$dateBetween = ModuleGraph::getDateBetween();
return (int)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT SUM(od.`product_quantity`) AS total
FROM `'._DB_PREFIX_.'order_detail` od
LEFT JOIN `'._DB_PREFIX_.'orders` o ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = '.(int)($id_product).'
AND o.valid = 1
AND o.`date_add` BETWEEN '.$dateBetween.'');
}
public function getTotalSales($id_product)
{
$dateBetween = ModuleGraph::getDateBetween();
return (float)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT SUM(od.`product_quantity` * od.`product_price`) AS total
FROM `'._DB_PREFIX_.'order_detail` od
LEFT JOIN `'._DB_PREFIX_.'orders` o ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = '.(int)($id_product).'
AND o.valid = 1
AND o.`date_add` BETWEEN '.$dateBetween.'');
}
public function getTotalViewed($id_product)
{
$dateBetween = ModuleGraph::getDateBetween();
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT SUM(pv.`counter`) AS total
FROM `'._DB_PREFIX_.'page_viewed` pv
LEFT JOIN `'._DB_PREFIX_.'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `'._DB_PREFIX_.'page` p ON pv.`id_page` = p.`id_page`
LEFT JOIN `'._DB_PREFIX_.'page_type` pt ON pt.`id_page_type` = p.`id_page_type`
WHERE pt.`name` = \'product.php\'
AND p.`id_object` = '.(int)($id_product).'
AND dr.`time_start` BETWEEN '.$dateBetween.'
AND dr.`time_end` BETWEEN '.$dateBetween.'');
return isset($result['total']) ? $result['total'] : 0;
}
private function getProducts($id_lang)
{
return Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
SELECT p.`id_product`, p.reference, pl.`name`, IFNULL((SELECT SUM(pa.quantity) FROM '._DB_PREFIX_.'product_attribute pa WHERE pa.id_product = p.id_product), p.quantity) as quantity
FROM `'._DB_PREFIX_.'product` p
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON p.`id_product` = pl.`id_product`
'.(Tools::getValue('id_category') ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`' : '').'
WHERE pl.`id_lang` = '.(int)($id_lang).'
'.(Tools::getValue('id_category') ? 'AND cp.id_category = '.(int)(Tools::getValue('id_category')) : '').'
ORDER BY pl.`name`');
}
private function getSales($id_product, $id_lang)
{
return Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
SELECT o.date_add, o.id_order, o.id_customer, od.product_quantity, (od.product_price * od.product_quantity) as total, od.tax_name, od.product_name
FROM `'._DB_PREFIX_.'orders` o
LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
WHERE o.date_add BETWEEN '.$this->getDate().' AND o.valid = 1
AND od.product_id = '.(int)($id_product));
}
private function getCrossSales($id_product, $id_lang)
{
return Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
SELECT pl.name as pname, pl.id_product, SUM(od.product_quantity) as pqty, AVG(od.product_price) as pprice
FROM `'._DB_PREFIX_.'orders` o
LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (pl.id_product = od.product_id AND pl.id_lang = '.(int)($id_lang).')
WHERE o.id_customer IN (
SELECT o.id_customer
FROM `'._DB_PREFIX_.'orders` o
LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
WHERE o.date_add BETWEEN '.$this->getDate().'
AND o.valid = 1
AND od.product_id = '.(int)($id_product).'
)
AND o.date_add BETWEEN '.$this->getDate().'
AND o.valid = 1
AND od.product_id != '.(int)($id_product).'
GROUP BY od.product_id
ORDER BY pqty DESC');
}
public function hookAdminStatsModules($params)
{
global $cookie, $currentIndex;
$id_category = (int)(Tools::getValue('id_category'));
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
if (Tools::getValue('export'))
if (!Tools::getValue('exportType'))
$this->csvExport(array('layers' => 2, 'type' => 'line', 'option' => '42'));
$this->_html = '
';
return $this->_html;
}
public function setOption($option, $layers = 1)
{
$options = explode('-', $option);
if (count($options) === 2)
list($this->_option, $this->_id_product) = $options;
else
$this->_option = $option;
$dateBetween = $this->getDate();
switch ($this->_option)
{
case 1:
$this->_titles['main'][0] = $this->l('Popularity');
$this->_titles['main'][1] = $this->l('Sales');
$this->_titles['main'][2] = $this->l('Visits (x100)');
$this->_query[0] = '
SELECT o.`date_add`, SUM(od.`product_quantity`) AS total
FROM `'._DB_PREFIX_.'order_detail` od
LEFT JOIN `'._DB_PREFIX_.'orders` o ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = '.(int)($this->_id_product).'
AND o.valid = 1
AND o.`date_add` BETWEEN '.$dateBetween.'
GROUP BY o.`date_add`';
$this->_query[1] = '
SELECT dr.`time_start` AS date_add, (SUM(pv.`counter`) / 100) AS total
FROM `'._DB_PREFIX_.'page_viewed` pv
LEFT JOIN `'._DB_PREFIX_.'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `'._DB_PREFIX_.'page` p ON pv.`id_page` = p.`id_page`
LEFT JOIN `'._DB_PREFIX_.'page_type` pt ON pt.`id_page_type` = p.`id_page_type`
WHERE pt.`name` = \'product.php\'
AND p.`id_object` = '.(int)($this->_id_product).'
AND dr.`time_start` BETWEEN '.$dateBetween.'
AND dr.`time_end` BETWEEN '.$dateBetween.'
GROUP BY dr.`time_start`';
break;
case 3:
$this->_query = '
SELECT product_attribute_id, SUM(od.`product_quantity`) AS total
FROM `'._DB_PREFIX_.'orders` o
LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = '.(int)($this->_id_product).'
AND o.valid = 1
AND o.`date_add` BETWEEN '.$dateBetween.'
GROUP BY od.`product_attribute_id`';
$this->_titles['main'] = $this->l('Attributes');
break;
case 42:
$this->_titles['main'][1] = $this->l('Ref.');
$this->_titles['main'][2] = $this->l('Name');
$this->_titles['main'][3] = $this->l('Stock');
break;
}
}
protected function getData($layers)
{
global $cookie;
if ($this->_option == 42)
{
$products = $this->getProducts(intval($cookie->id_lang));
foreach ($products AS $product)
{
$this->_values[0][] = $product['reference'];
$this->_values[1][] = $product['name'];
$this->_values[2][] = $product['quantity'];
$this->_legend[] = $product['id_product'];
}
}
elseif ($this->_option != 3)
$this->setDateGraph($layers, true);
else
{
$product = new Product($this->_id_product, false, (int)($this->getLang()));
$combArray = array();
$assocNames = array();
$combinaisons = $product->getAttributeCombinaisons((int)($this->getLang()));
foreach ($combinaisons AS $k => $combinaison)
$combArray[$combinaison['id_product_attribute']][] = array('group' => $combinaison['group_name'], 'attr' => $combinaison['attribute_name']);
foreach ($combArray AS $id_product_attribute => $product_attribute)
{
$list = '';
foreach ($product_attribute AS $attribute)
$list .= trim($attribute['group']).' - '.trim($attribute['attr']).', ';
$list = rtrim($list, ', ');
$assocNames[$id_product_attribute] = $list;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query);
foreach ($result as $row)
{
$this->_values[] = $row['total'];
$this->_legend[] = @$assocNames[$row['product_attribute_id']];
}
}
}
protected function setAllTimeValues($layers)
{
for ($i = 0; $i < $layers; $i++)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query[$i]);
foreach ($result AS $row)
$this->_values[$i][(int)(substr($row['date_add'], 0, 4))] += $row['total'];
}
}
protected function setYearValues($layers)
{
for ($i = 0; $i < $layers; $i++)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query[$i]);
foreach ($result AS $row)
$this->_values[$i][(int)(substr($row['date_add'], 5, 2))] += $row['total'];
}
}
protected function setMonthValues($layers)
{
for ($i = 0; $i < $layers; $i++)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query[$i]);
foreach ($result AS $row)
$this->_values[$i][(int)(substr($row['date_add'], 8, 2))] += $row['total'];
}
}
protected function setDayValues($layers)
{
for ($i = 0; $i < $layers; $i++)
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query[$i]);
foreach ($result AS $row)
$this->_values[$i][(int)(substr($row['date_add'], 11, 2))] += $row['total'];
}
}
}