1<?php 2/** 3 * Copyright (C) 2017-2019 thirty bees 4 * Copyright (C) 2007-2016 PrestaShop SA 5 * 6 * thirty bees is an extension to the PrestaShop software by PrestaShop SA. 7 * 8 * NOTICE OF LICENSE 9 * 10 * This source file is subject to the Academic Free License (AFL 3.0) 11 * that is bundled with this package in the file LICENSE.md. 12 * It is also available through the world-wide-web at this URL: 13 * https://opensource.org/licenses/afl-3.0.php 14 * If you did not receive a copy of the license and are unable to 15 * obtain it through the world-wide-web, please send an email 16 * to license@thirtybees.com so we can send you a copy immediately. 17 * 18 * @author thirty bees <modules@thirtybees.com> 19 * @author PrestaShop SA <contact@prestashop.com> 20 * @copyright 2017-2019 thirty bees 21 * @copyright 2007-2016 PrestaShop SA 22 * @license Academic Free License (AFL 3.0) 23 * PrestaShop is an internationally registered trademark of PrestaShop SA. 24 */ 25 26if (!defined('_TB_VERSION_')) { 27 exit; 28} 29 30class StatsBestCustomers extends StatsModule 31{ 32 protected $type = 'Grid'; 33 protected $html; 34 protected $query; 35 protected $columns; 36 protected $default_sort_column; 37 protected $default_sort_direction; 38 protected $empty_message; 39 protected $paging_message; 40 41 public function __construct() 42 { 43 $this->name = 'statsbestcustomers'; 44 $this->tab = 'analytics_stats'; 45 $this->version = '2.0.0'; 46 $this->author = 'thirty bees'; 47 $this->need_instance = 0; 48 49 parent::__construct(); 50 51 $this->default_sort_column = 'totalMoneySpent'; 52 $this->default_sort_direction = 'DESC'; 53 $this->empty_message = Translate::getModuleTranslation('statsmodule', 'Empty recordset returned', 'statsmodule'); 54 $this->paging_message = sprintf(Translate::getModuleTranslation('statsmodule', 'Displaying %1$s of %2$s', 'statsmodule'), '{0} - {1}', '{2}'); 55 56 $currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT')); 57 58 $this->columns = array( 59 array( 60 'id' => 'lastname', 61 'header' => Translate::getModuleTranslation('statsmodule', 'Last Name', 'statsmodule'), 62 'dataIndex' => 'lastname', 63 'align' => 'center', 64 ), 65 array( 66 'id' => 'firstname', 67 'header' => Translate::getModuleTranslation('statsmodule', 'First Name', 'statsmodule'), 68 'dataIndex' => 'firstname', 69 'align' => 'center', 70 ), 71 array( 72 'id' => 'email', 73 'header' => Translate::getModuleTranslation('statsmodule', 'Email', 'statsmodule'), 74 'dataIndex' => 'email', 75 'align' => 'center', 76 ), 77 array( 78 'id' => 'totalVisits', 79 'header' => Translate::getModuleTranslation('statsmodule', 'Visits', 'statsmodule'), 80 'dataIndex' => 'totalVisits', 81 'align' => 'center', 82 ), 83 array( 84 'id' => 'totalValidOrders', 85 'header' => Translate::getModuleTranslation('statsmodule', 'Valid orders', 'statsmodule'), 86 'dataIndex' => 'totalValidOrders', 87 'align' => 'center', 88 ), 89 array( 90 'id' => 'totalMoneySpent', 91 'header' => Translate::getModuleTranslation('statsmodule', 'Money spent', 'statsmodule').' ('.Tools::safeOutput($currency->iso_code).')', 92 'dataIndex' => 'totalMoneySpent', 93 'align' => 'center', 94 ), 95 ); 96 97 $this->displayName = Translate::getModuleTranslation('statsmodule', 'Best customers', 'statsmodule'); 98 $this->description = Translate::getModuleTranslation('statsmodule', 'Adds a list of the best customers to the Stats dashboard.', 'statsmodule'); 99 } 100 101 public function install() 102 { 103 return (parent::install() && $this->registerHook('AdminStatsModules')); 104 } 105 106 public function hookAdminStatsModules($params) 107 { 108 $engine_params = array( 109 'id' => 'id_customer', 110 'title' => $this->displayName, 111 'columns' => $this->columns, 112 'defaultSortColumn' => $this->default_sort_column, 113 'defaultSortDirection' => $this->default_sort_direction, 114 'emptyMessage' => $this->empty_message, 115 'pagingMessage' => $this->paging_message, 116 ); 117 118 if (Tools::getValue('export')) 119 $this->csvExport($engine_params); 120 121 $this->html = ' 122 <div class="panel-heading"> 123 '.$this->displayName.' 124 </div> 125 <h4>'.Translate::getModuleTranslation('statsmodule', 'Guide', 'statsmodule').'</h4> 126 <div class="alert alert-warning"> 127 <h4>'.Translate::getModuleTranslation('statsmodule', 'Develop clients\' loyalty', 'statsmodule').'</h4> 128 <div> 129 '.Translate::getModuleTranslation('statsmodule', 'Keeping a client can be more profitable than gaining a new one. That is one of the many reasons it is necessary to cultivate customer loyalty.', 'statsmodule').' <br /> 130 '.Translate::getModuleTranslation('statsmodule', 'Word of mouth is also a means for getting new, satisfied clients. A dissatisfied customer can hurt your e-reputation and obstruct future sales goals.', 'statsmodule').'<br /> 131 '.Translate::getModuleTranslation('statsmodule', 'In order to achieve this goal, you can organize:', 'statsmodule').' 132 <ul> 133 <li>'.Translate::getModuleTranslation('statsmodule', 'Punctual operations: commercial rewards (personalized special offers, product or service offered), non commercial rewards (priority handling of an order or a product), pecuniary rewards (bonds, discount coupons, payback).', 'statsmodule').'</li> 134 <li>'.Translate::getModuleTranslation('statsmodule', 'Sustainable operations: loyalty points or cards, which not only justify communication between merchant and client, but also offer advantages to clients (private offers, discounts).', 'statsmodule').'</li> 135 </ul> 136 '.Translate::getModuleTranslation('statsmodule', 'These operations encourage clients to buy products and visit your online store more regularly.', 'statsmodule').' 137 </div> 138 </div> 139 '.$this->engine($this->type, $engine_params).' 140 <a class="btn btn-default export-csv" href="'.Tools::safeOutput($_SERVER['REQUEST_URI'].'&export=').'1"> 141 <i class="icon-cloud-upload"></i> '.Translate::getModuleTranslation('statsmodule', 'CSV Export', 'statsmodule').' 142 </a>'; 143 144 return $this->html; 145 } 146 147 public function getData($layers = null) 148 { 149 $this->query = ' 150 SELECT SQL_CALC_FOUND_ROWS c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`, 151 COUNT(co.`id_connections`) AS totalVisits, 152 IFNULL(( 153 SELECT ROUND(SUM(IFNULL(op.`amount`, 0) / cu.conversion_rate), 2) 154 FROM `'._DB_PREFIX_.'orders` o 155 LEFT JOIN `'._DB_PREFIX_.'order_payment` op ON o.reference = op.order_reference 156 LEFT JOIN `'._DB_PREFIX_.'currency` cu ON o.id_currency = cu.id_currency 157 WHERE o.id_customer = c.id_customer 158 AND o.invoice_date BETWEEN '.$this->getDate().' 159 AND o.valid 160 ), 0) AS totalMoneySpent, 161 IFNULL(( 162 SELECT COUNT(*) 163 FROM `'._DB_PREFIX_.'orders` o 164 WHERE o.id_customer = c.id_customer 165 AND o.invoice_date BETWEEN '.$this->getDate().' 166 AND o.valid 167 ), 0) AS totalValidOrders 168 FROM `'._DB_PREFIX_.'customer` c 169 LEFT JOIN `'._DB_PREFIX_.'guest` g ON c.`id_customer` = g.`id_customer` 170 LEFT JOIN `'._DB_PREFIX_.'connections` co ON g.`id_guest` = co.`id_guest` 171 WHERE co.date_add BETWEEN '.$this->getDate() 172 .Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c'). 173 'GROUP BY c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`'; 174 175 if (Validate::IsName($this->_sort)) { 176 $this->query .= ' ORDER BY `'.bqSQL($this->_sort).'`'; 177 if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) 178 $this->query .= ' '.$this->_direction; 179 } 180 181 if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) 182 $this->query .= ' LIMIT '.(int) $this->_start.', '.(int) $this->_limit; 183 184 $this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query); 185 $this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()'); 186 } 187} 188