* @copyright 2007-2016 PrestaShop SA * @license http://opensource.org/licenses/afl-3.0.php Academic Free License (AFL 3.0) * International Registered Trademark & Property of PrestaShop SA */ if (!defined('_PS_VERSION_')) { exit; } class StatsForecast extends Module { private $html = ''; private $t1 = 0; private $t2 = 0; private $t3 = 0; private $t4 = 0; private $t5 = 0; private $t6 = 0; private $t7 = 0; private $t8 = 0; private $t9 = 0; public function __construct() { $this->name = 'statsforecast'; $this->tab = 'analytics_stats'; $this->version = '1.4.3'; $this->author = 'PrestaShop'; $this->need_instance = 0; parent::__construct(); $this->displayName = $this->l('Stats Dashboard'); $this->description = $this->l('This is the main module for the Stats dashboard. It displays a summary of all your current statistics.'); $this->ps_versions_compliancy = array('min' => '1.6', 'max' => '1.7.0.99'); } public function install() { return parent::install() && $this->registerHook('AdminStatsModules') && $this->registerHook('actionAdminControllerSetMedia'); } public function getContent() { Tools::redirectAdmin('index.php?controller=AdminStats&module=statsforecast&token='.Tools::getAdminTokenLite('AdminStats')); } public function hookActionAdminControllerSetMedia() { if (Tools::getValue('controller') == 'AdminStats' && (Tools::getValue('module') == false || Tools::getValue('module') == 'statsforecast') ) { $this->context->controller->addCSS($this->getPathUri().'views/css/conversion-graph.css'); $this->context->controller->addJS($this->getPathUri().'views/js/conversion-graph.js'); } } public function hookAdminStatsModules() { Context::getContext()->controller->addJqueryPlugin('connections'); $ru = AdminController::$currentIndex.'&module='.$this->name.'&token='.Tools::getValue('token'); $db = Db::getInstance(); if (!isset($this->context->cookie->stats_granularity)) { $this->context->cookie->stats_granularity = 10; } if (Tools::isSubmit('submitIdZone')) { $this->context->cookie->stats_id_zone = (int)Tools::getValue('stats_id_zone'); Tools::redirectAdmin($ru); } if (Tools::isSubmit('submitGranularity')) { $this->context->cookie->stats_granularity = Tools::getValue('stats_granularity'); Tools::redirectAdmin($ru); } $currency = $this->context->currency; $employee = $this->context->employee; $from = max(strtotime(_PS_CREATION_DATE_.' 00:00:00'), strtotime($employee->stats_date_from.' 00:00:00')); $to = strtotime($employee->stats_date_to.' 23:59:59'); $to2 = min(time(), $to); $interval = ($to - $from) / 60 / 60 / 24; $interval2 = ($to2 - $from) / 60 / 60 / 24; $prop30 = $interval / $interval2; if ($this->context->cookie->stats_granularity == 7) { $interval_avg = $interval2 / 30; } if ($this->context->cookie->stats_granularity == 4) { $interval_avg = $interval2 / 365; } if ($this->context->cookie->stats_granularity == 10) { $interval_avg = $interval2; } if ($this->context->cookie->stats_granularity == 42) { $interval_avg = $interval2 / 7; } $data_table = array(); if ($this->context->cookie->stats_granularity == 10) { for ($i = $from; $i <= $to2; $i = strtotime('+1 day', $i)) { $data_table[date('Y-m-d', $i)] = array( 'fix_date' => date('Y-m-d', $i), 'countOrders' => 0, 'totalRoomsBooked' => 0, 'totalSales' => 0, 'totalOperatingCost' => 0, ); } } $date_from_gadd = ($this->context->cookie->stats_granularity != 42 ? 'LEFT(date_add, '.(int)$this->context->cookie->stats_granularity.')' : 'IFNULL(MAKEDATE(YEAR(date_add),DAYOFYEAR(date_add)-WEEKDAY(date_add)), CONCAT(YEAR(date_add),"-01-01*"))'); $date_from_ginvoice = ($this->context->cookie->stats_granularity != 42 ? 'LEFT(invoice_date, '.(int)$this->context->cookie->stats_granularity.')' : 'IFNULL(MAKEDATE(YEAR(invoice_date),DAYOFYEAR(invoice_date)-WEEKDAY(invoice_date)), CONCAT(YEAR(invoice_date),"-01-01*"))'); $date_to_ginvoice = ''; if ($this->context->cookie->stats_granularity == 42) { $date_to_ginvoice = 'DATE_ADD('.$date_from_ginvoice.', INTERVAL 6 DAY) as end_date,'; } $result = $db->query( 'SELECT '.$date_from_ginvoice.' AS fix_date, '.($this->context->cookie->stats_granularity == 42 ? $date_to_ginvoice : '').' COUNT(o.`id_order`) AS countOrders, SUM((SELECT IFNULL(SUM(DATEDIFF(hbd.`date_to`, hbd.`date_from`)), 0) FROM `'._DB_PREFIX_.'htl_booking_detail` hbd WHERE o.`id_order` = hbd.`id_order`)) AS totalRoomsBooked, SUM(o.`total_paid_tax_excl` / o.`conversion_rate`) AS totalSales, SUM(( SELECT SUM(ROUND(DATEDIFF(hbd.`date_to`, hbd.`date_from`) * ( CASE WHEN od.`original_wholesale_price` <> "0.000000" THEN od.`original_wholesale_price` WHEN p.`wholesale_price` <> "0.000000" THEN p.`wholesale_price` ELSE 0 END ), 2)) FROM `'._DB_PREFIX_.'htl_booking_detail` hbd LEFT JOIN `'._DB_PREFIX_.'product` p ON (p.`id_product` = hbd.`id_product`) LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (od.`id_order_detail` = hbd.`id_order_detail`) WHERE hbd.`id_order` = o.`id_order` )) AS totalOperatingCost FROM '._DB_PREFIX_.'orders o WHERE o.`valid` = 1 AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().' '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').' GROUP BY '.$date_from_ginvoice ); while ($row = $db->nextRow($result)) { $data_table[$row['fix_date']] = $row; } $this->html .= '
'.$this->displayName.'
'.$this->l('The listed amounts do not include tax.').'
'; $visit_array = array(); $sql = 'SELECT '.$date_from_gadd.' as fix_date, COUNT(*) as visits FROM '._DB_PREFIX_.'connections c WHERE c.`date_add` BETWEEN '.ModuleGraph::getDateBetween().' '.Shop::addSqlRestriction(false, 'c').' GROUP BY '.$date_from_gadd; $visits = Db::getInstance()->query($sql); while ($row = $db->nextRow($visits)) { $visit_array[$row['fix_date']] = $row['visits']; } foreach ($data_table as $row) { $visits_today = (int)(isset($visit_array[$row['fix_date']]) ? $visit_array[$row['fix_date']] : 0); $date_from_greg = ($this->context->cookie->stats_granularity != 42 ? 'LIKE \''.$row['fix_date'].'%\'' : 'BETWEEN \''.Tools::substr($row['fix_date'], 0, 10).' 00:00:00\' AND DATE_ADD(\''.Tools::substr($row['fix_date'], 0, 8).Tools::substr($row['fix_date'], 8, 2).' 23:59:59\', INTERVAL 7 DAY)'); $row['registrations'] = Db::getInstance()->getValue(' SELECT COUNT(*) FROM '._DB_PREFIX_.'customer WHERE date_add BETWEEN '.ModuleGraph::getDateBetween().' AND date_add '.$date_from_greg .Shop::addSqlRestriction(Shop::SHARE_CUSTOMER)); $this->html .= ' '; $this->t1 += $visits_today; $this->t2 += (int)$row['registrations']; $this->t3 += (int)$row['countOrders']; $this->t4 += (int)$row['totalRoomsBooked']; $this->t8 += $row['totalSales']; $this->t9 += $row['totalOperatingCost']; } $this->html .= '
'.$this->l('Visits').' '.$this->l('Registrations').' '.$this->l('Orders placed').' '.$this->l('Booked rooms').' '.$this->l('Percentage of registrations').' '.$this->l('Conversion rate').' '.$this->l('Revenue').' '.$this->l('Margin').'
'.($this->context->cookie->stats_granularity == 42 ? (Tools::displayDate($row['fix_date']).' - '.Tools::displayDate($row['end_date'])) : $row['fix_date']).' '.$visits_today.' '.(int)$row['registrations'].' '.(int)$row['countOrders'].' '.(int)$row['totalRoomsBooked'].' '.sprintf('%0.2f', $visits_today ? ($row['registrations'] / $visits_today) * 100 : 0).' %'.' '.sprintf('%0.2f', $visits_today ? ($row['countOrders'] / $visits_today) * 100 : 0).' %'.' '.Tools::displayPrice($row['totalSales'], $currency).' '.Tools::displayPrice($row['totalOperatingCost'], $currency).'
'.$this->l('Visits').' '.$this->l('Registrations').' '.$this->l('Orders placed').' '.$this->l('Booked rooms').' '.$this->l('Percentage of registrations').' '.$this->l('Conversion rate').' '.$this->l('Revenue').' '.$this->l('Margin').'
'.$this->l('Total').' '.(int)$this->t1.' '.(int)$this->t2.' '.(int)$this->t3.' '.(int)$this->t4.' -- -- '.Tools::displayPrice($this->t8, $currency).' '.Tools::displayPrice($this->t9, $currency).'
'.$this->l('Average').' '.sprintf('%0.2f', $this->t1 / $interval_avg).' '.sprintf('%0.2f', $this->t2 / $interval_avg).' '.sprintf('%0.2f', $this->t3 / $interval_avg).' '.sprintf('%0.2f', $this->t4 / $interval_avg).' '.sprintf('%0.2f', ($this->t1 ? ($this->t2 / $this->t1) * 100 : 0)).' % '.sprintf('%0.2f', ($this->t1 ? ($this->t3 / $this->t1) * 100 : 0)).' % '.Tools::displayPrice($this->t8 / $interval_avg, $currency).' '.Tools::displayPrice($this->t9 / $interval_avg, $currency).'
'.$this->l('Forecast').' '.(int)($this->t1 * $prop30).' '.(int)($this->t2 * $prop30).' '.(int)($this->t3 * $prop30).' '.(int)($this->t4 * $prop30).' -- -- '.Tools::displayPrice($this->t8 * $prop30, $currency).' '.Tools::displayPrice($this->t9 * $prop30, $currency).'
'; $ca = $this->getRealCA(); $sql = 'SELECT COUNT(DISTINCT c.id_guest) FROM '._DB_PREFIX_.'connections c WHERE c.date_add BETWEEN '.ModuleGraph::getDateBetween(); $visitors = Db::getInstance()->getValue($sql); $sql = 'SELECT COUNT(DISTINCT g.`id_customer`) FROM '._DB_PREFIX_.'connections c INNER JOIN '._DB_PREFIX_.'guest g ON c.id_guest = g.id_guest WHERE g.id_customer != 0 AND c.date_add BETWEEN '.ModuleGraph::getDateBetween(); $customers = Db::getInstance()->getValue($sql); $sql = 'SELECT COUNT(DISTINCT g.`id_guest`) FROM '._DB_PREFIX_.'connections c INNER JOIN '._DB_PREFIX_.'guest g ON c.`id_guest` = g.`id_guest` WHERE g.`id_customer` = 0 AND c.`date_add` BETWEEN '.ModuleGraph::getDateBetween(); $guests = Db::getInstance()->getValue($sql); $sql = 'SELECT COUNT(DISTINCT c.`id_cart`) FROM '._DB_PREFIX_.'cart c WHERE c.`id_customer` != 0 AND (c.`date_add` BETWEEN '.ModuleGraph::getDateBetween().' OR c.`date_upd` BETWEEN '.ModuleGraph::getDateBetween().')'; $carts_registered = Db::getInstance()->getValue($sql); $sql = 'SELECT COUNT(DISTINCT c.`id_cart`) FROM '._DB_PREFIX_.'cart c WHERE c.`id_customer` = 0 AND (c.`date_add` BETWEEN '.ModuleGraph::getDateBetween().' OR c.`date_upd` BETWEEN '.ModuleGraph::getDateBetween().')'; $carts_unregistered = Db::getInstance()->getValue($sql); $sql = 'SELECT COUNT(*) FROM '._DB_PREFIX_.'orders o WHERE o.`valid` = 1 AND o.`date_add` BETWEEN '.ModuleGraph::getDateBetween(); $orders = Db::getInstance()->getValue($sql); $sql = 'SELECT COUNT(*) FROM '._DB_PREFIX_.'orders o INNER JOIN '._DB_PREFIX_.'customer c ON c.`id_customer` = o.`id_customer` WHERE o.`valid` = 1 AND c.`is_guest` = 0 AND o.`date_add` BETWEEN '.ModuleGraph::getDateBetween(); $orders_registered = Db::getInstance()->getValue($sql); $sql = 'SELECT COUNT(*) FROM '._DB_PREFIX_.'orders o INNER JOIN '._DB_PREFIX_.'customer c ON c.`id_customer` = o.`id_customer` WHERE o.`valid` = 1 AND c.`is_guest` = 1 AND o.`date_add` BETWEEN '.ModuleGraph::getDateBetween(); $orders_unregistered = Db::getInstance()->getValue($sql); $this->context->smarty->assign(array( 'conversion_graph_data' => array( 'values' => array( 'visitors' => $visitors, 'visitors_registered' => $customers, 'visitors_unregistered' => $guests, 'carts_registered' => $carts_registered, 'carts_unregistered' => $carts_unregistered, 'orders' => $orders, 'orders_registered' => $orders_registered, 'orders_unregistered' => $orders_unregistered, ), 'percentages' => array( 'visitors_registered' => sprintf('%0.2f', ($visitors ? $customers / $visitors : 0) * 100), 'visitors_unregistered' => sprintf('%0.2f', ($visitors ? $guests / $visitors : 0) * 100), 'orders' => sprintf('%0.2f', ($orders ? $carts_registered / $orders : 0) * 100), 'orders_top_registered' => sprintf('%0.2f', ($carts_registered ? $orders_registered / $carts_registered : 0) * 100), 'orders_top_unregistered' => sprintf('%0.2f', ($carts_unregistered ? $orders_unregistered / $carts_unregistered : 0) * 100), 'orders_bottom_registered' => sprintf('%0.2f', ($orders ? $orders_registered / $orders : 0) * 100), 'orders_bottom_unregistered' => sprintf('%0.2f', ($orders ? $orders_unregistered / $orders : 0) * 100), ), ), )); $this->html .= '

'.$this->l('Conversion').'

'. $this->context->smarty->fetch($this->getLocalPath().'views/templates/conversion.tpl').'

'.$this->l('A simple statistical calculation lets you know the monetary value of your visitors:').'

'.$this->l('On average, each visitor places an order for this amount:').' '.Tools::displayPrice($ca['ventil']['total'] / max(1, $visitors), $currency).'.

'.$this->l('On average, each registered visitor places an order for this amount:').' '.Tools::displayPrice($ca['ventil']['total'] / max(1, $customers), $currency).'.

'; $from = strtotime($employee->stats_date_from.' 00:00:00'); $to = strtotime($employee->stats_date_to.' 23:59:59'); $interval = ($to - $from) / 60 / 60 / 24; $this->html .= '

'.$this->l('Payment distribution').'

' .$this->l('The amounts in this section include taxes, so you can get an estimation of the commission due to the payment method.').'
'; if (is_array($ca['payment']) && count($ca['payment'])) { foreach ($ca['payment'] as $payment) { $this->html .= ' '; } } else { $this->html .= ' '; } $this->html .= '
'.$this->l('Module').' '.$this->l('Orders').' '.$this->l('Revenue').' '.$this->l('Percentage of orders').' '.$this->l('Percentage of revenue').' '.$this->l('Average order value').'
'.$payment['payment_method'].' '.(int)$payment['nb'].' '.Tools::displayPrice($payment['total'], $currency).' '.sprintf('%0.2f', ($ca['ventil']['nb'] ? ($payment['nb'] / $ca['ventil']['nb']) * 100 : 0)).'% '.sprintf('%0.2f', ($ca['ventil']['nb'] ? ($payment['total'] / $ca['ventil']['total_incl']) * 100 : 0)).'% '.Tools::displayPrice($payment['total'] / (int)$payment['nb'], $currency).'
'.$this->l('No data available.').'

'.$this->l('Language distribution').'

'; if (is_array($ca['lang']) && count($ca['lang'])) { foreach ($ca['lang'] as $ophone => $amount) { $this->html .= ' '; } } else { $this->html .= ' '; } $this->html .= '
'.$this->l('Language').' '.$this->l('Revenue').' '.$this->l('Percentage of revenue').'
'.$ophone.' '.Tools::displayPrice($amount, $currency).' '.sprintf('%0.2f', ($ca['ventil']['total'] ? ($amount / $ca['ventil']['total']) * 100 : 0)).'%
'.$this->l('No data available.').'

'.$this->l('Zone distribution').'

'; if (is_array($ca['zones']) && count($ca['zones'])) { foreach ($ca['zones'] as $zone) { $this->html .= ' '; } } else { $this->html .= ' '; } $this->html .= '
'.$this->l('Zone').' '.$this->l('Orders').' '.$this->l('Revenue').' '.$this->l('Percentage of orders').' '.$this->l('Percentage of revenue').'
'.(isset($zone['name']) ? $zone['name'] : $this->l('Undefined')).' '.(int)($zone['nb']).' '.Tools::displayPrice($zone['total'], $currency).' '.sprintf('%0.2f', $ca['ventil']['nb'] ? ($zone['nb'] / $ca['ventil']['nb']) * 100 : 0).'% '.sprintf('%0.2f', $ca['ventil']['total'] ? ($zone['total'] / $ca['ventil']['total']) * 100 : 0).'%
'.$this->l('No data available.').'

'.$this->l('Currency distribution').'

'; if (is_array($ca['currencies']) && count($ca['currencies'])) { foreach ($ca['currencies'] as $currency_row) { $this->html .= ' '; } } else { $this->html .= ' '; } $this->html .= '
'.$this->l('Currency').' '.$this->l('Orders').' '.$this->l('Revenue (converted)').' '.$this->l('Percentage of orders').' '.$this->l('Percentage of revenue').'
'.$currency_row['name'].' '.(int)($currency_row['nb']).' '.Tools::displayPrice($currency_row['total'], $currency).' '.sprintf('%0.2f', $ca['ventil']['nb'] ? ($currency_row['nb'] / $ca['ventil']['nb']) * 100 : 0).'% '.sprintf('%0.2f', $ca['ventil']['total'] ? ($currency_row['total'] / $ca['ventil']['total']) * 100 : 0).'%
'.$this->l('No data available.').'
'; return $this->html; } private function getRealCA() { $employee = $this->context->employee; $ca = array(); $where = $join = ''; if ((int)$this->context->cookie->stats_id_zone) { $join = ' LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_invoice = a.id_address LEFT JOIN `'._DB_PREFIX_.'country` co ON co.id_country = a.id_country'; $where = ' AND co.id_zone = '.(int)$this->context->cookie->stats_id_zone.' '; } $lang_values = ''; $sql = 'SELECT l.id_lang, l.iso_code FROM `'._DB_PREFIX_.'lang` l '.Shop::addSqlAssociation('lang', 'l').' WHERE l.active = 1'; $languages = Db::getInstance()->executeS($sql); foreach ($languages as $language) { $lang_values .= 'SUM(IF(o.id_lang = '.(int)$language['id_lang'].', total_paid_tax_excl / o.conversion_rate, 0)) as '.pSQL($language['iso_code']).','; } $lang_values = rtrim($lang_values, ','); if ($lang_values) { $sql = 'SELECT '.$lang_values.' FROM `'._DB_PREFIX_.'orders` o WHERE o.valid = 1 AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().' '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o'); $ca['lang'] = Db::getInstance()->getRow($sql); arsort($ca['lang']); } else { $ca['lang'] = array(); } $sql = 'SELECT reference FROM `'._DB_PREFIX_.'orders` o '.$join.' WHERE o.valid '.$where.' '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').' AND o.invoice_date BETWEEN '.ModuleGraph::getDateBetween().''; $result = Db::getInstance()->executeS($sql); if (count($result)) { $references = array(); foreach ($result as $r) { $references[] = $r['reference']; } $sql = 'SELECT op.payment_method, SUM(op.amount / op.conversion_rate) as total, COUNT(DISTINCT op.order_reference) as nb FROM `'._DB_PREFIX_.'order_payment` op WHERE op.`date_add` BETWEEN '.ModuleGraph::getDateBetween().' AND op.order_reference IN ( "'.implode('","', $references).'" ) GROUP BY op.payment_method ORDER BY total DESC'; $ca['payment'] = Db::getInstance()->executeS($sql); } else { $ca['payment'] = array(); } $sql = 'SELECT z.name, SUM(o.total_paid_tax_excl / o.conversion_rate) as total, COUNT(*) as nb FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_invoice = a.id_address LEFT JOIN `'._DB_PREFIX_.'country` c ON c.id_country = a.id_country LEFT JOIN `'._DB_PREFIX_.'zone` z ON z.id_zone = c.id_zone WHERE o.valid = 1 AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().' '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').' GROUP BY c.id_zone ORDER BY total DESC'; $ca['zones'] = Db::getInstance()->executeS($sql); $sql = 'SELECT cu.name, SUM(o.total_paid_tax_excl / o.conversion_rate) as total, COUNT(*) as nb FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'currency` cu ON o.id_currency = cu.id_currency '.$join.' WHERE o.valid = 1 AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().' '.$where.' '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').' GROUP BY o.id_currency ORDER BY total DESC'; $ca['currencies'] = Db::getInstance()->executeS($sql); $sql = 'SELECT SUM(total_paid_tax_excl / o.conversion_rate) as total, SUM(total_paid_tax_incl / o.conversion_rate) as total_incl, COUNT(*) AS nb FROM `'._DB_PREFIX_.'orders` o WHERE o.valid = 1 AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().' '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o'); $ca['ventil'] = Db::getInstance()->getRow($sql); $sql = 'SELECT /*pac.id_attribute,*/ agl.name as gname, al.name as aname, COUNT(*) as total FROM '._DB_PREFIX_.'orders o LEFT JOIN '._DB_PREFIX_.'order_detail od ON o.id_order = od.id_order INNER JOIN '._DB_PREFIX_.'product_attribute_combination pac ON od.product_attribute_id = pac.id_product_attribute INNER JOIN '._DB_PREFIX_.'attribute a ON pac.id_attribute = a.id_attribute INNER JOIN '._DB_PREFIX_.'attribute_group_lang agl ON (a.id_attribute_group = agl.id_attribute_group AND agl.id_lang = '.(int)$this->context->language->id.') INNER JOIN '._DB_PREFIX_.'attribute_lang al ON (a.id_attribute = al.id_attribute AND al.id_lang = '.(int)$this->context->language->id.') WHERE o.valid = 1 AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().' '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').' GROUP BY pac.id_attribute'; $ca['attributes'] = Db::getInstance()->executeS($sql); return $ca; } }