* @copyright Since 2007 PrestaShop SA and Contributors * @license https://opensource.org/licenses/AFL-3.0 Academic Free License 3.0 (AFL-3.0) */ if (!defined('_PS_VERSION_')) { exit; } class statspersonalinfos extends ModuleGraph { private $html = ''; private $option; public function __construct() { $this->name = 'statspersonalinfos'; $this->tab = 'analytics_stats'; $this->version = '2.0.4'; $this->author = 'PrestaShop'; $this->need_instance = 0; parent::__construct(); $this->displayName = $this->trans('Registered customer information', array(), 'Modules.Statspersonalinfos.Admin'); $this->description = $this->trans('Enrich your stats, add information about your registered customers and learn more about them!', array(), 'Modules.Statspersonalinfos.Admin'); $this->ps_versions_compliancy = array('min' => '1.7.1.0', 'max' => _PS_VERSION_); } public function install() { return (parent::install() && $this->registerHook('displayAdminStatsModules')); } public function hookDisplayAdminStatsModules() { $this->html = '
'.$this->displayName.'

'.$this->trans('Guide', array(), 'Admin.Global').'

'.$this->trans('Target your audience', array(), 'Modules.Statspersonalinfos.Admin').'

'. $this->trans('In order for each message to have an impact, you need to know who it is being addressed to. ', array(), 'Modules.Statspersonalinfos.Admin'). '
'. $this->trans('Defining your target audience is essential when choosing the right tools to win them over.', array(), 'Modules.Statspersonalinfos.Admin'). '
'. $this->trans('It is best to limit an action to a group -- or to groups -- of clients.', array(), 'Modules.Statspersonalinfos.Admin'). '
'. $this->trans('Storing registered customer information allows you to accurately define customer profiles so you can adapt your special deals and promotions.', array(), 'Modules.Statspersonalinfos.Admin').'

'.$this->trans('You can increase your sales by:', array(), 'Modules.Statspersonalinfos.Admin').'
'; $has_customers = (bool)Db::getInstance()->getValue('SELECT id_customer FROM '._DB_PREFIX_.'customer'); if ($has_customers) { if (Tools::getValue('export')) { if (Tools::getValue('exportType') == 'gender') { $this->csvExport(array( 'type' => 'pie', 'option' => 'gender' )); } elseif (Tools::getValue('exportType') == 'age') { $this->csvExport(array( 'type' => 'pie', 'option' => 'age' )); } elseif (Tools::getValue('exportType') == 'country') { $this->csvExport(array( 'type' => 'pie', 'option' => 'country' )); } elseif (Tools::getValue('exportType') == 'currency') { $this->csvExport(array( 'type' => 'pie', 'option' => 'currency' )); } elseif (Tools::getValue('exportType') == 'language') { $this->csvExport(array( 'type' => 'pie', 'option' => 'language' )); } } $this->html .= '
'.$this->engine(array( 'type' => 'pie', 'option' => 'gender' )).'

'.$this->trans('Gender distribution allows you to determine the percentage of men and women shoppers on your store.', array(), 'Modules.Statspersonalinfos.Admin').'

'.$this->trans('CSV Export', array(), 'Modules.Statspersonalinfos.Admin').'
'.$this->engine(array( 'type' => 'pie', 'option' => 'age' )).'

'.$this->trans('Age ranges allow you to better understand target demographics.', array(), 'Modules.Statspersonalinfos.Admin').'

'.$this->trans('CSV Export', array(), 'Modules.Statspersonalinfos.Admin').'
'.$this->engine(array( 'type' => 'pie', 'option' => 'country' )).'

'.$this->trans('Country distribution allows you to analyze which part of the World your customers are shopping from.', array(), 'Modules.Statspersonalinfos.Admin').'

'.$this->trans('CSV Export', array(), 'Modules.Statspersonalinfos.Admin').'
'.$this->engine(array( 'type' => 'pie', 'option' => 'currency' )).'

'.$this->trans('Currency range allows you to determine which currency your customers are using.', array(), 'Modules.Statspersonalinfos.Admin').'

'.$this->trans('CSV Export', array(), 'Modules.Statspersonalinfos.Admin').'
'.$this->engine(array( 'type' => 'pie', 'option' => 'language' )).'

'.$this->trans('Language distribution allows you to analyze the browsing language used by your customers.', array(), 'Modules.Statspersonalinfos.Admin').'

'.$this->trans('CSV Export', array(), 'Modules.Statspersonalinfos.Admin').'
'; } else { $this->html .= '

'.$this->trans('No customers have registered yet.', array(), 'Modules.Statspersonalinfos.Admin').'

'; } return $this->html; } public function setOption($option, $layers = 1) { $this->option = $option; } protected function getData($layers) { switch ($this->option) { case 'gender': $this->_titles['main'] = $this->trans('Gender distribution', array(), 'Modules.Statspersonalinfos.Admin'); $genders = array( 0 => $this->trans('Male', array(), 'Admin.Shopparameters.Feature'), 1 => $this->trans('Female', array(), 'Admin.Shopparameters.Feature'), 2 => $this->trans('Unknown', array(), 'Admin.Shopparameters.Feature'), ); $sql = 'SELECT g.type, c.id_gender, COUNT(c.id_customer) AS total FROM '._DB_PREFIX_.'customer c LEFT JOIN '._DB_PREFIX_.'gender g ON c.id_gender = g.id_gender WHERE 1 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c').' GROUP BY c.id_gender'; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); $genders_results = array(); foreach ($result as $row) { $type = (is_null($row['type'])) ? 2 : $row['type']; if (!isset($genders_results[$type])) { $genders_results[$type] = 0; } $genders_results[$type] += $row['total']; } foreach ($genders_results as $type => $total) { $this->_values[] = $total; $this->_legend[] = $genders[$type]; } break; case 'age': $this->_titles['main'] = $this->trans('Age range', array(), 'Modules.Statspersonalinfos.Admin'); // 0 - 18 years $sql = 'SELECT COUNT(`id_customer`) as total FROM `'._DB_PREFIX_.'customer` WHERE `birthday` IS NOT NULL AND `birthday` != "0000-00-00" AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) < 18 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (isset($result['total']) && $result['total']) { $this->_values[] = $result['total']; $this->_legend[] = $this->trans('0-18', array(), 'Modules.Statspersonalinfos.Admin'); } // 18 - 24 years $sql = 'SELECT COUNT(`id_customer`) as total FROM `'._DB_PREFIX_.'customer` WHERE `birthday` IS NOT NULL AND `birthday` != "0000-00-00" AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) >= 18 AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) < 25 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (isset($result['total']) && $result['total']) { $this->_values[] = $result['total']; $this->_legend[] = $this->trans('18-24', array(), 'Modules.Statspersonalinfos.Admin'); } // 25 - 34 years $sql = 'SELECT COUNT(`id_customer`) as total FROM `'._DB_PREFIX_.'customer` WHERE `birthday` IS NOT NULL AND `birthday` != "0000-00-00" AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) >= 25 AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) < 35 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (isset($result['total']) && $result['total']) { $this->_values[] = $result['total']; $this->_legend[] = $this->trans('25-34', array(), 'Modules.Statspersonalinfos.Admin'); } // 35 - 49 years $sql = 'SELECT COUNT(`id_customer`) as total FROM `'._DB_PREFIX_.'customer` WHERE `birthday` IS NOT NULL AND `birthday` != "0000-00-00" AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) >= 35 AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) < 50 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (isset($result['total']) && $result['total']) { $this->_values[] = $result['total']; $this->_legend[] = $this->trans('35-49', array(), 'Modules.Statspersonalinfos.Admin'); } // 50 - 59 years $sql = 'SELECT COUNT(`id_customer`) as total FROM `'._DB_PREFIX_.'customer` WHERE `birthday` IS NOT NULL AND `birthday` != "0000-00-00" AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) >= 50 AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) < 60 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (isset($result['total']) && $result['total']) { $this->_values[] = $result['total']; $this->_legend[] = $this->trans('50-59', array(), 'Modules.Statspersonalinfos.Admin'); } // More than 60 years $sql = 'SELECT COUNT(`id_customer`) as total FROM `'._DB_PREFIX_.'customer` WHERE `birthday` IS NOT NULL AND `birthday` != "0000-00-00" AND (YEAR(CURDATE()) - YEAR(`birthday`)) - (RIGHT(CURDATE(), 5) < RIGHT(`birthday`, 5)) >= 60 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (isset($result['total']) && $result['total']) { $this->_values[] = $result['total']; $this->_legend[] = $this->trans('60+', array(), 'Modules.Statspersonalinfos.Admin'); } // Total unknown $sql = 'SELECT COUNT(`id_customer`) as total FROM `'._DB_PREFIX_.'customer` WHERE `birthday` IS NULL OR `birthday` = "0000-00-00" '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (isset($result['total']) && $result['total']) { $this->_values[] = $result['total']; $this->_legend[] = $this->trans('Unknown', array(), 'Admin.Shopparameters.Feature'); } break; case 'country': $this->_titles['main'] = $this->trans('Country distribution', array(), 'Modules.Statspersonalinfos.Admin'); $sql = 'SELECT cl.`name`, COUNT(c.`id_country`) AS total FROM `'._DB_PREFIX_.'address` a LEFT JOIN `'._DB_PREFIX_.'customer` cu ON cu.id_customer = a.id_customer LEFT JOIN `'._DB_PREFIX_.'country` c ON a.`id_country` = c.`id_country` LEFT JOIN `'._DB_PREFIX_.'country_lang` cl ON (c.`id_country` = cl.`id_country` AND cl.`id_lang` = ' . (int) $this->context->language->id . ') WHERE a.id_customer != 0 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'cu').' GROUP BY c.`id_country`'; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); foreach ($result as $row) { $this->_values[] = $row['total']; $this->_legend[] = $row['name']; } break; case 'currency': $this->_titles['main'] = $this->trans('Currency distribution', array(), 'Modules.Statspersonalinfos.Admin'); $sql = 'SELECT cl.name, c.`iso_code`, COUNT(c.`id_currency`) AS total FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'currency` c ON o.`id_currency` = c.`id_currency` LEFT JOIN `'._DB_PREFIX_.'currency_lang` cl ON (cl.`id_currency` = c.`id_currency` AND cl.`id_lang` = ' . (int) $this->context->language->id . ') WHERE 1 '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').' GROUP BY c.`id_currency`'; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); foreach ($result as $row) { $this->_values[] = $row['total']; $this->_legend[] = $row['name'] . ' (' . $row['iso_code'] . ')'; } break; case 'language': $this->_titles['main'] = $this->trans('Language distribution', array(), 'Modules.Statspersonalinfos.Admin'); $sql = 'SELECT c.`name`, COUNT(c.`id_lang`) AS total FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'lang` c ON o.`id_lang` = c.`id_lang` WHERE 1 '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').' GROUP BY c.`id_lang`'; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); foreach ($result as $row) { $this->_values[] = $row['total']; $this->_legend[] = $row['name']; } break; } } }