FM / admin

/home/sharedstore/public_html/prestashop.sharedstore.ma/controllers/admin/

/home/sharedstore/public_html/prestashop.sharedstore.ma/controllers/admin

Name	Size	Mode	Actions
AdminAccessController.php	7951	0644	edit dl rm
AdminAttributesGroupsController.php	40294	0644	edit dl rm
AdminCarriersController.php	31487	0644	edit dl rm
AdminCarrierWizardController.php	41832	0644	edit dl rm
AdminCartRulesController.php	44377	0644	edit dl rm
AdminCartsController.php	45952	0644	edit dl rm
AdminCountriesController.php	21668	0644	edit dl rm
AdminCustomerThreadsController.php	55016	0644	edit dl rm
AdminDashboardController.php	20857	0644	edit dl rm
AdminFeaturesController.php	24929	0644	edit dl rm
AdminGendersController.php	8913	0644	edit dl rm
AdminGroupsController.php	28902	0644	edit dl rm
AdminImagesController.php	52404	0644	edit dl rm
AdminImportController.php	242510	0644	edit dl rm
AdminLegacyLayoutController.php	7951	0644	edit dl rm
AdminLoginController.php	22834	0644	edit dl rm
AdminModulesController.php	62356	0644	edit dl rm
AdminModulesPositionsController.php	26967	0644	edit dl rm
AdminNotFoundController.php	1719	0644	edit dl rm
AdminPdfController.php	9250	0644	edit dl rm
AdminProductsController.php	165767	0644	edit dl rm
AdminQuickAccessesController.php	9247	0644	edit dl rm
AdminRequestSqlController.php	21915	0644	edit dl rm
AdminReturnController.php	15449	0644	edit dl rm
AdminSearchConfController.php	24070	0644	edit dl rm
AdminSearchController.php	26113	0644	edit dl rm
AdminShopController.php	39360	0644	edit dl rm
AdminShopGroupController.php	16199	0644	edit dl rm
AdminShopUrlController.php	25095	0644	edit dl rm
AdminSpecificPriceRuleController.php	16646	0644	edit dl rm
AdminStatesController.php	12996	0644	edit dl rm
AdminStatsController.php	43947	0644	edit dl rm
AdminStatsTabController.php	11922	0644	edit dl rm
AdminStatusesController.php	30961	0644	edit dl rm
AdminStoresController.php	26330	0644	edit dl rm
AdminSuppliersController.php	24787	0644	edit dl rm
AdminTabsController.php	15255	0644	edit dl rm
AdminTagsController.php	5794	0644	edit dl rm
AdminTaxRulesGroupController.php	22533	0644	edit dl rm
AdminTranslationsController.php	151815	0644	edit dl rm
BoOrder.php	1371	0644	edit dl rm
DummyAdminController.php	2408	0644	edit dl rm
index.php	1375	0644	edit dl rm

Edit: /home/sharedstore/public_html/prestashop.sharedstore.ma/controllers/admin/AdminStatsController.php (43947B)

<?php
/**
 * Copyright since 2007 PrestaShop SA and Contributors
 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.md.
 * It is also available through the world-wide-web at this URL:
 * https://opensource.org/licenses/OSL-3.0
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to https://devdocs.prestashop.com/ for more information.
 *
 * @author    PrestaShop SA and Contributors <contact@prestashop.com>
 * @copyright Since 2007 PrestaShop SA and Contributors
 * @license   https://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
 */
use PrestaShop\PrestaShop\Core\Addon\Module\ModuleManagerBuilder;
use PrestaShop\PrestaShop\Core\Addon\Theme\Theme;
use PrestaShop\PrestaShop\Core\Addon\Theme\ThemeManagerBuilder;

class AdminStatsControllerCore extends AdminStatsTabController
{
    public static function getVisits($unique, $date_from, $date_to, $granularity = false)
    {
        $visits = ($granularity == false) ? 0 : [];

if ($granularity == 'day') {
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
                '
            SELECT date(`date_add`) as date, COUNT(' . ($unique ? 'DISTINCT id_guest' : '*') . ') as visits
            FROM `' . _DB_PREFIX_ . 'connections`
            WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
            ' . Shop::addSqlRestriction() . '
            GROUP BY date(`date_add`)'
            );
            foreach ($result as $row) {
                $visits[strtotime($row['date'])] = $row['visits'];
            }
        } elseif ($granularity == 'month') {
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
                '
            SELECT LEFT(LAST_DAY(`date_add`), 7) as date, COUNT(' . ($unique ? 'DISTINCT id_guest' : '*') . ') as visits
            FROM `' . _DB_PREFIX_ . 'connections`
            WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
            ' . Shop::addSqlRestriction() . '
            GROUP BY LAST_DAY(`date_add`)'
            );
            foreach ($result as $row) {
                $visits[strtotime($row['date'] . '-01')] = $row['visits'];
            }
        } else {
            $visits = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                '
            SELECT COUNT(' . ($unique ? 'DISTINCT id_guest' : '*') . ') as visits
            FROM `' . _DB_PREFIX_ . 'connections`
            WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
            ' . Shop::addSqlRestriction()
            );
        }

return $visits;
    }

public static function getAbandonedCarts($date_from, $date_to)
    {
        return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(DISTINCT id_guest)
		FROM `' . _DB_PREFIX_ . 'cart`
		WHERE `date_add` BETWEEN "' . pSQL($date_from) . '" AND "' . pSQL($date_to) . '"
		AND NOT EXISTS (SELECT 1 FROM `' . _DB_PREFIX_ . 'orders` WHERE `' . _DB_PREFIX_ . 'orders`.id_cart = `' . _DB_PREFIX_ . 'cart`.id_cart)
		' . Shop::addSqlRestriction()
        );
    }

public static function getInstalledModules()
    {
        return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(DISTINCT m.`id_module`)
		FROM `' . _DB_PREFIX_ . 'module` m
		' . Shop::addSqlAssociation('module', 'm')
        );
    }

public static function getDisabledModules()
    {
        return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(*)
		FROM `' . _DB_PREFIX_ . 'module` m
		' . Shop::addSqlAssociation('module', 'm', false) . '
		WHERE module_shop.id_module IS NULL OR m.active = 0'
        );
    }

public static function getModulesToUpdate()
    {
        $context = Context::getContext();
        $modules = Module::getModulesOnDisk(true, $context->employee->id);
        $upgrade_available = 0;
        foreach ($modules as $km => $module) {
            if ($module->installed && isset($module->version_addons) && $module->version_addons) { // SimpleXMLElement
                ++$upgrade_available;
            }
        }

return $upgrade_available;
    }

public static function getPercentProductStock()
    {
        $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow(
            '
		SELECT SUM(IF(IFNULL(stock.quantity, 0) > 0, 1, 0)) AS with_stock, COUNT(*) AS products
		FROM `' . _DB_PREFIX_ . 'product` p
		' . Shop::addSqlAssociation('product', 'p') . '
		LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON p.id_product = pa.id_product
		' . Product::sqlStock('p', 'pa') . '
		WHERE product_shop.active = 1'
        );

return round($row['products'] ? 100 * $row['with_stock'] / $row['products'] : 0, 2) . '%';
    }

public static function getPercentProductOutOfStock()
    {
        $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow(
            '
		SELECT SUM(IF(IFNULL(stock.quantity, 0) <= 0, 1, 0)) AS without_stock, COUNT(*) AS products
		FROM `' . _DB_PREFIX_ . 'product` p
		' . Shop::addSqlAssociation('product', 'p') . '
		LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON p.id_product = pa.id_product
		' . Product::sqlStock('p', 'pa') . '
		WHERE product_shop.active = 1'
        );

return round($row['products'] ? 100 * $row['without_stock'] / $row['products'] : 0, 2) . '%';
    }

public static function getProductAverageGrossMargin()
    {
        $sql = 'SELECT AVG(1 - (IF(IFNULL(product_attribute_shop.wholesale_price, 0) = 0, product_shop.wholesale_price,product_attribute_shop.wholesale_price) / (IFNULL(product_attribute_shop.price, 0) + product_shop.price)))
		FROM `' . _DB_PREFIX_ . 'product` p
		' . Shop::addSqlAssociation('product', 'p') . '
		LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON p.id_product = pa.id_product
		' . Shop::addSqlAssociation('product_attribute', 'pa', false) . '
		WHERE product_shop.active = 1';
        $value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);

return round(100 * $value, 2) . '%';
    }

public static function getDisabledCategories()
    {
        return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(*)
		FROM `' . _DB_PREFIX_ . 'category` c
		' . Shop::addSqlAssociation('category', 'c') . '
		WHERE c.active = 0'
        );
    }

public static function getTotalCategories()
    {
        return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(*)
		FROM `' . _DB_PREFIX_ . 'category` c
		' . Shop::addSqlAssociation('category', 'c')
        );
    }

public static function getDisabledProducts()
    {
        return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(*)
		FROM `' . _DB_PREFIX_ . 'product` p
		' . Shop::addSqlAssociation('product', 'p') . '
		WHERE product_shop.active = 0'
        );
    }

public static function getTotalProducts()
    {
        return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(*)
		FROM `' . _DB_PREFIX_ . 'product` p
		' . Shop::addSqlAssociation('product', 'p')
        );
    }

public static function getTotalSales($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $sales = [];
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
                '
			SELECT LEFT(`invoice_date`, 10) AS date, SUM((total_paid_tax_excl - total_shipping_tax_excl) / o.conversion_rate) AS sales
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o') . '
			GROUP BY LEFT(`invoice_date`, 10)'
            );
            foreach ($result as $row) {
                $sales[strtotime($row['date'])] = $row['sales'];
            }

return $sales;
        } elseif ($granularity == 'month') {
            $sales = [];
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
                '
			SELECT LEFT(`invoice_date`, 7) AS date, SUM((total_paid_tax_excl - total_shipping_tax_excl) / o.conversion_rate) AS sales
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o') . '
			GROUP BY LEFT(`invoice_date`, 7)'
            );
            foreach ($result as $row) {
                $sales[strtotime($row['date'] . '-01')] = $row['sales'];
            }

return $sales;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                '
			SELECT SUM((total_paid_tax_excl - total_shipping_tax_excl) / o.conversion_rate)
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o')
            );
        }
    }

public static function get8020SalesCatalog($date_from, $date_to)
    {
        $distinct_products = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            'SELECT COUNT(DISTINCT od.product_id)
		FROM `' . _DB_PREFIX_ . 'orders` o
		LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON o.id_order = od.id_order
		WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
		' . Shop::addSqlRestriction(false, 'o')
        );
        if (!$distinct_products) {
            return '0%';
        }

return round(100 * $distinct_products / AdminStatsController::getTotalProducts()) . '%';
    }

public static function getOrders($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $orders = [];
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
                '
			SELECT LEFT(`invoice_date`, 10) AS date, COUNT(*) AS orders
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o') . '
			GROUP BY LEFT(`invoice_date`, 10)'
            );
            foreach ($result as $row) {
                $orders[strtotime($row['date'])] = $row['orders'];
            }

return $orders;
        } elseif ($granularity == 'month') {
            $orders = [];
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
                '
			SELECT LEFT(`invoice_date`, 7) AS date, COUNT(*) AS orders
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o') . '
			GROUP BY LEFT(`invoice_date`, 7)'
            );
            foreach ($result as $row) {
                $orders[strtotime($row['date'] . '-01')] = $row['orders'];
            }

return $orders;
        } else {
            $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                '
			SELECT COUNT(*) AS orders
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o')
            );
        }

return $orders;
    }

public static function getEmptyCategories()
    {
        $total = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(*)
		FROM `' . _DB_PREFIX_ . 'category` c
		' . Shop::addSqlAssociation('category', 'c') . '
		AND c.`id_category` != ' . (int) Configuration::get('PS_ROOT_CATEGORY')
        );
        $used = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT COUNT(DISTINCT cp.id_category)
		FROM `' . _DB_PREFIX_ . 'category` c
		LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON c.id_category = cp.id_category
		' . Shop::addSqlAssociation('category', 'c') . '
		AND c.`id_category` != ' . (int) Configuration::get('PS_ROOT_CATEGORY')
        );

return (int) ($total - $used);
    }

public static function getCustomerMainGender()
    {
        $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow(
            '
		SELECT SUM(IF(g.id_gender IS NOT NULL, 1, 0)) AS total, SUM(IF(type = 0, 1, 0)) AS male, SUM(IF(type = 1, 1, 0)) AS female, SUM(IF(type = 2, 1, 0)) AS neutral
		FROM `' . _DB_PREFIX_ . 'customer` c
		LEFT JOIN `' . _DB_PREFIX_ . 'gender` g ON c.id_gender = g.id_gender
		WHERE c.active = 1 ' . Shop::addSqlRestriction()
        );

if (!$row['total']) {
            return false;
        } elseif ($row['male'] > $row['female'] && $row['male'] >= $row['neutral']) {
            return ['type' => 'male', 'value' => round(100 * $row['male'] / $row['total'])];
        } elseif ($row['female'] >= $row['male'] && $row['female'] >= $row['neutral']) {
            return ['type' => 'female', 'value' => round(100 * $row['female'] / $row['total'])];
        }

return ['type' => 'neutral', 'value' => round(100 * $row['neutral'] / $row['total'])];
    }

public static function getBestCategory($date_from, $date_to)
    {
        return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT ca.`id_category`
		FROM `' . _DB_PREFIX_ . 'category` ca
		LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr ON ca.`id_category` = capr.`id_category`
		LEFT JOIN (
			SELECT pr.`id_product`, t.`totalPriceSold`
			FROM `' . _DB_PREFIX_ . 'product` pr
			LEFT JOIN (
				SELECT pr.`id_product`,
					IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
					IFNULL(SUM(cp.`unit_price_tax_excl` * cp.`product_quantity`), 0) / o.conversion_rate AS totalPriceSold
				FROM `' . _DB_PREFIX_ . 'product` pr
				LEFT OUTER JOIN `' . _DB_PREFIX_ . 'order_detail` cp ON pr.`id_product` = cp.`product_id`
				LEFT JOIN `' . _DB_PREFIX_ . 'orders` o ON o.`id_order` = cp.`id_order`
				WHERE o.invoice_date BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
				GROUP BY pr.`id_product`
			) t ON t.`id_product` = pr.`id_product`
		) t	ON t.`id_product` = capr.`id_product`
		WHERE ca.`level_depth` > 1
		GROUP BY ca.`id_category`
		ORDER BY SUM(t.`totalPriceSold`) DESC'
        );
    }

public static function getMainCountry($date_from, $date_to)
    {
        $total_orders = AdminStatsController::getOrders($date_from, $date_to);
        if (!$total_orders) {
            return false;
        }
        $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow(
            '
		SELECT a.id_country, COUNT(*) AS orders
		FROM `' . _DB_PREFIX_ . 'orders` o
		LEFT JOIN `' . _DB_PREFIX_ . 'address` a ON o.id_address_delivery = a.id_address
		WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
		' . Shop::addSqlRestriction()
        );
        $row['orders'] = round(100 * $row['orders'] / $total_orders, 1);

return $row;
    }

public static function getAverageCustomerAge()
    {
        $value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
            '
		SELECT AVG(DATEDIFF("' . date('Y-m-d') . ' 00:00:00", birthday))
		FROM `' . _DB_PREFIX_ . 'customer` c
		WHERE active = 1
		AND birthday IS NOT NULL AND birthday != "0000-00-00" ' . Shop::addSqlRestriction()
        );

return round($value / 365);
    }

public static function getPendingMessages()
    {
        return CustomerThread::getTotalCustomerThreads(
            'status LIKE "%pending%" OR status = "open"' . Shop::addSqlRestriction()
        );
    }

public static function getAverageMessageResponseTime($date_from, $date_to)
    {
        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
            '
		SELECT MIN(cm1.date_add) AS question, MIN(cm2.date_add) AS reply
		FROM `' . _DB_PREFIX_ . 'customer_message` cm1
		INNER JOIN `' . _DB_PREFIX_ . 'customer_message` cm2 ON (cm1.id_customer_thread = cm2.id_customer_thread AND cm1.date_add < cm2.date_add)
		JOIN `' . _DB_PREFIX_ . 'customer_thread` ct ON (cm1.id_customer_thread = ct.id_customer_thread)
		WHERE cm1.`date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
		AND cm1.id_employee = 0 AND cm2.id_employee != 0
		' . Shop::addSqlRestriction() . '
		GROUP BY cm1.id_customer_thread'
        );
        $total_questions = $total_replies = $threads = 0;
        foreach ($result as $row) {
            ++$threads;
            $total_questions += strtotime($row['question']);
            $total_replies += strtotime($row['reply']);
        }
        if (!$threads) {
            return 0;
        }

return round(($total_replies - $total_questions) / $threads / 3600, 1);
    }

public static function getMessagesPerThread($date_from, $date_to)
    {
        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
            '
		SELECT COUNT(*) AS messages
		FROM `' . _DB_PREFIX_ . 'customer_thread` ct
		LEFT JOIN `' . _DB_PREFIX_ . 'customer_message` cm ON (ct.id_customer_thread = cm.id_customer_thread)
		WHERE ct.`date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59"
		' . Shop::addSqlRestriction() . '
		AND STATUS = "closed"
		GROUP BY ct.id_customer_thread'
        );
        $threads = $messages = 0;
        foreach ($result as $row) {
            ++$threads;
            $messages += $row['messages'];
        }
        if (!$threads) {
            return 0;
        }

return round($messages / $threads, 1);
    }

public static function getPurchases($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $purchases = [];
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
                '
			SELECT
				LEFT(`invoice_date`, 10) as date,
				SUM(od.`product_quantity` * IF(
					od.`purchase_supplier_price` > 0,
					od.`purchase_supplier_price` / `conversion_rate`,
					od.`original_product_price` * ' . (int) Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN') . ' / 100
				)) as total_purchase_price
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON o.id_order = od.id_order
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o') . '
			GROUP BY LEFT(`invoice_date`, 10)'
            );
            foreach ($result as $row) {
                $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
            }

return $purchases;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                '
			SELECT SUM(od.`product_quantity` * IF(
				od.`purchase_supplier_price` > 0,
				od.`purchase_supplier_price` / `conversion_rate`,
				od.`original_product_price` * ' . (int) Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN') . ' / 100
			)) as total_purchase_price
			FROM `' . _DB_PREFIX_ . 'orders` o
			LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON o.id_order = od.id_order
			LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
			WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
			' . Shop::addSqlRestriction(false, 'o')
            );
        }
    }

public static function getExpenses($date_from, $date_to, $granularity = false)
    {
        $expenses = ($granularity == 'day' ? [] : 0);

$orders = Db::getInstance()->executeS(
            '
		SELECT
			LEFT(`invoice_date`, 10) AS date,
			total_paid_tax_incl / o.conversion_rate AS total_paid_tax_incl,
			total_shipping_tax_excl / o.conversion_rate AS total_shipping_tax_excl,
			o.module,
			a.id_country,
			o.id_currency,
			c.id_reference AS carrier_reference
		FROM `' . _DB_PREFIX_ . 'orders` o
		LEFT JOIN `' . _DB_PREFIX_ . 'address` a ON o.id_address_delivery = a.id_address
		LEFT JOIN `' . _DB_PREFIX_ . 'carrier` c ON o.id_carrier = c.id_carrier
		LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
		WHERE `invoice_date` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1
		' . Shop::addSqlRestriction(false, 'o')
        );
        foreach ($orders as $order) {
            // Add flat fees for this order
            $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                $order['id_currency'] == Currency::getDefaultCurrencyId()
                    ? Configuration::get('CONF_' . strtoupper($order['module']) . '_FIXED')
                    : Configuration::get('CONF_' . strtoupper($order['module']) . '_FIXED_FOREIGN')
                );

// Add variable fees for this order
            $var_fees = $order['total_paid_tax_incl'] * (
                $order['id_currency'] == Currency::getDefaultCurrencyId()
                    ? Configuration::get('CONF_' . strtoupper($order['module']) . '_VAR')
                    : Configuration::get('CONF_' . strtoupper($order['module']) . '_VAR_FOREIGN')
                ) / 100;

// Add shipping fees for this order
            $shipping_fees = $order['total_shipping_tax_excl'] * (
                $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                    ? Configuration::get('CONF_' . strtoupper($order['carrier_reference']) . '_SHIP')
                    : Configuration::get('CONF_' . strtoupper($order['carrier_reference']) . '_SHIP_OVERSEAS')
                ) / 100;

// Tally up these fees
            if ($granularity == 'day') {
                if (!isset($expenses[strtotime($order['date'])])) {
                    $expenses[strtotime($order['date'])] = 0;
                }
                $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
            } else {
                $expenses += $flat_fees + $var_fees + $shipping_fees;
            }
        }

return $expenses;
    }

public function displayAjaxGetKpi()
    {
        if (!$this->access('view')) {
            return die(json_encode(['error' => 'You do not have the right permission']));
        }

$currency = Currency::getDefaultCurrency();
        $tooltip = null;
        $value = false;
        switch (Tools::getValue('kpi')) {
            case 'conversion_rate':
                $visitors = AdminStatsController::getVisits(
                    true,
                    date('Y-m-d', strtotime('-31 day')),
                    date('Y-m-d', strtotime('-1 day')),
                    false /*'day'*/
                );
                $orders = AdminStatsController::getOrders(
                    date('Y-m-d', strtotime('-31 day')),
                    date('Y-m-d', strtotime('-1 day')),
                    false /*'day'*/
                );

$visits_sum = $visitors;
                $orders_sum = $orders;
                if ($visits_sum) {
                    $value = round(100 * $orders_sum / $visits_sum, 2);
                } elseif ($orders_sum) {
                    $value = '&infin;';
                } else {
                    $value = 0;
                }
                $value .= '%';

ConfigurationKPI::updateValue('CONVERSION_RATE', $value);
                ConfigurationKPI::updateValue(
                    'CONVERSION_RATE_EXPIRE',
                    strtotime(date('Y-m-d 00:00:00', strtotime('+1 day')))
                );

break;

case 'abandoned_cart':
                $value = AdminStatsController::getAbandonedCarts(
                    date('Y-m-d H:i:s', strtotime('-2 day')),
                    date('Y-m-d H:i:s', strtotime('-1 day'))
                );
                ConfigurationKPI::updateValue('ABANDONED_CARTS', $value);
                ConfigurationKPI::updateValue('ABANDONED_CARTS_EXPIRE', strtotime('+1 hour'));

break;

case 'installed_modules':
                $value = AdminStatsController::getInstalledModules();
                ConfigurationKPI::updateValue('INSTALLED_MODULES', $value);
                ConfigurationKPI::updateValue('INSTALLED_MODULES_EXPIRE', strtotime('+2 min'));

break;

case 'disabled_modules':
                $value = AdminStatsController::getDisabledModules();
                ConfigurationKPI::updateValue('DISABLED_MODULES', $value);
                ConfigurationKPI::updateValue('DISABLED_MODULES_EXPIRE', strtotime('+2 min'));

break;

case 'update_modules':
                $value = AdminStatsController::getModulesToUpdate();
                ConfigurationKPI::updateValue('UPDATE_MODULES', $value);
                ConfigurationKPI::updateValue('UPDATE_MODULES_EXPIRE', strtotime('+2 min'));

break;

case 'percent_product_stock':
                $value = AdminStatsController::getPercentProductStock();
                ConfigurationKPI::updateValue('PERCENT_PRODUCT_STOCK', $value);
                ConfigurationKPI::updateValue('PERCENT_PRODUCT_STOCK_EXPIRE', strtotime('+4 hour'));

break;

case 'percent_product_out_of_stock':
                $value = AdminStatsController::getPercentProductOutOfStock();
                $tooltip = $this->trans(
                    '%value% of your products for sale are out of stock.',
                    ['%value%' => $value],
                    'Admin.Stats.Help'
                );
                ConfigurationKPI::updateValue('PERCENT_PRODUCT_OUT_OF_STOCK', $value);
                ConfigurationKPI::updateValue('PERCENT_PRODUCT_OUT_OF_STOCK_EXPIRE', strtotime('+4 hour'));

break;

case 'product_avg_gross_margin':
                $value = AdminStatsController::getProductAverageGrossMargin();
                $tooltip = $this->trans(
                    'Gross margin expressed in percentage assesses how cost-effectively you sell your goods. Out of $100, you will retain $%value% to cover profit and expenses.',
                    ['%value%' => $value],
                    'Admin.Stats.Help'
                );
                ConfigurationKPI::updateValue('PRODUCT_AVG_GROSS_MARGIN', $value);
                ConfigurationKPI::updateValue('PRODUCT_AVG_GROSS_MARGIN_EXPIRE', strtotime('+6 hour'));

break;

case 'disabled_categories':
                $value = AdminStatsController::getDisabledCategories();
                ConfigurationKPI::updateValue('DISABLED_CATEGORIES', $value);
                ConfigurationKPI::updateValue('DISABLED_CATEGORIES_EXPIRE', strtotime('+2 hour'));

break;

case 'disabled_products':
                $value = round(
                        100 * AdminStatsController::getDisabledProducts() / AdminStatsController::getTotalProducts(),
                        2
                    ) . '%';
                $tooltip = $this->trans(
                    '%value% of your products are disabled and not visible to your customers',
                    ['%value%' => $value],
                    'Admin.Stats.Help'
                );
                ConfigurationKPI::updateValue('DISABLED_PRODUCTS', $value);
                ConfigurationKPI::updateValue('DISABLED_PRODUCTS_EXPIRE', strtotime('+2 hour'));

break;

case '8020_sales_catalog':
                $value = AdminStatsController::get8020SalesCatalog(date('Y-m-d', strtotime('-30 days')), date('Y-m-d'));
                $tooltip = $this->trans(
                    'Within your catalog, %value% of your products have had sales in the last 30 days',
                    ['%value%' => $value],
                    'Admin.Stats.Help'
                );
                $value = $this->trans('%value%% of your Catalog', ['%value%' => $value], 'Admin.Stats.Feature');
                ConfigurationKPI::updateValue('8020_SALES_CATALOG', $value);
                ConfigurationKPI::updateValue('8020_SALES_CATALOG_EXPIRE', strtotime('+12 hour'));

break;

case 'empty_categories':
                $value = AdminStatsController::getEmptyCategories();
                ConfigurationKPI::updateValue('EMPTY_CATEGORIES', $value);
                ConfigurationKPI::updateValue('EMPTY_CATEGORIES_EXPIRE', strtotime('+2 hour'));

break;

case 'customer_main_gender':
                $value = AdminStatsController::getCustomerMainGender();

if ($value === false) {
                    $value = $this->trans('No customers', [], 'Admin.Stats.Feature');
                } elseif ($value['type'] == 'female') {
                    $value = $this->trans('%percentage%% Female Customers', ['%percentage%' => $value['value']], 'Admin.Stats.Feature');
                } elseif ($value['type'] == 'male') {
                    $value = $this->trans('%percentage%% Male Customers', ['%percentage%' => $value['value']], 'Admin.Stats.Feature');
                } else {
                    $value = $this->trans('%percentage%% Neutral Customers', ['%percentage%' => $value['value']], 'Admin.Stats.Feature');
                }

ConfigurationKPI::updateValue('CUSTOMER_MAIN_GENDER', [$this->context->language->id => $value]);
                ConfigurationKPI::updateValue(
                    'CUSTOMER_MAIN_GENDER_EXPIRE',
                    [$this->context->language->id => strtotime('+1 day')]
                );

break;

case 'avg_customer_age':
                $value = $this->trans('%value% years', ['%value%' => AdminStatsController::getAverageCustomerAge()], 'Admin.Stats.Feature');
                ConfigurationKPI::updateValue('AVG_CUSTOMER_AGE', [$this->context->language->id => $value]);
                ConfigurationKPI::updateValue(
                    'AVG_CUSTOMER_AGE_EXPIRE',
                    [$this->context->language->id => strtotime('+1 day')]
                );

break;

case 'pending_messages':
                $value = (int) AdminStatsController::getPendingMessages();
                ConfigurationKPI::updateValue('PENDING_MESSAGES', $value);
                ConfigurationKPI::updateValue('PENDING_MESSAGES_EXPIRE', strtotime('+5 min'));

break;

case 'avg_msg_response_time':
                $value = $this->trans('%average% hours', ['%average%' => AdminStatsController::getAverageMessageResponseTime(
                    date('Y-m-d', strtotime('-31 day')),
                    date('Y-m-d', strtotime('-1 day'))
                )], 'Admin.Stats.Feature');
                ConfigurationKPI::updateValue('AVG_MSG_RESPONSE_TIME', $value);
                ConfigurationKPI::updateValue('AVG_MSG_RESPONSE_TIME_EXPIRE', strtotime('+4 hour'));

break;

case 'messages_per_thread':
                $value = round(
                    AdminStatsController::getMessagesPerThread(
                        date('Y-m-d', strtotime('-31 day')),
                        date('Y-m-d', strtotime('-1 day'))
                    ),
                    1
                );
                ConfigurationKPI::updateValue('MESSAGES_PER_THREAD', $value);
                ConfigurationKPI::updateValue('MESSAGES_PER_THREAD_EXPIRE', strtotime('+12 hour'));

break;

case 'newsletter_registrations':
                $moduleManagerBuilder = ModuleManagerBuilder::getInstance();
                $moduleManager = $moduleManagerBuilder->build();

$value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                    '
				SELECT COUNT(*)
				FROM `' . _DB_PREFIX_ . 'customer`
				WHERE newsletter = 1
				' . Shop::addSqlRestriction(Shop::SHARE_ORDER)
                );
                if ($moduleManager->isInstalled('ps_emailsubscription')) {
                    $value += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                        '
					SELECT COUNT(*)
					FROM `' . _DB_PREFIX_ . 'emailsubscription`
					WHERE active = 1
					' . Shop::addSqlRestriction(Shop::SHARE_ORDER)
                    );
                }

ConfigurationKPI::updateValue('NEWSLETTER_REGISTRATIONS', $value);
                ConfigurationKPI::updateValue('NEWSLETTER_REGISTRATIONS_EXPIRE', strtotime('+6 hour'));

break;

case 'enabled_languages':
                $value = Language::countActiveLanguages();
                ConfigurationKPI::updateValue('ENABLED_LANGUAGES', $value);
                ConfigurationKPI::updateValue('ENABLED_LANGUAGES_EXPIRE', strtotime('+1 min'));

break;

case 'frontoffice_translations':
                $themes = (new ThemeManagerBuilder($this->context, Db::getInstance()))
                    ->buildRepository()
                    ->getList();
                $languages = Language::getLanguages();
                $total = $translated = 0;
                foreach ($themes as $theme) {
                    /* @var Theme $theme */
                    foreach ($languages as $language) {
                        $kpi_key = substr(strtoupper($theme->getName() . '_' . $language['iso_code']), 0, 16);
                        $total += ConfigurationKPI::get('TRANSLATE_TOTAL_' . $kpi_key);
                        $translated += ConfigurationKPI::get('TRANSLATE_DONE_' . $kpi_key);
                    }
                }
                $value = 0;
                if ($translated) {
                    $value = round(100 * $translated / $total, 1);
                }
                $value .= '%';
                ConfigurationKPI::updateValue('FRONTOFFICE_TRANSLATIONS', $value);
                ConfigurationKPI::updateValue('FRONTOFFICE_TRANSLATIONS_EXPIRE', strtotime('+2 min'));

break;

case 'main_country':
                if (!($row = AdminStatsController::getMainCountry(
                    date('Y-m-d', strtotime('-30 day')),
                    date('Y-m-d')
                ))
                ) {
                    $value = $this->trans('No orders', [], 'Admin.Stats.Feature');
                } else {
                    $country = new Country($row['id_country'], $this->context->language->id);
                    $value = $this->trans(
                        '%d%% %s',
                        ['%d%%' => $row['orders'], '%s' => $country->name],
                        'Admin.Stats.Feature'
                    );
                }

ConfigurationKPI::updateValue('MAIN_COUNTRY', [$this->context->language->id => $value]);
                ConfigurationKPI::updateValue(
                    'MAIN_COUNTRY_EXPIRE',
                    [$this->context->language->id => strtotime('+1 day')]
                );

break;

case 'orders_per_customer':
                $value = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                    '
				SELECT COUNT(*)
				FROM `' . _DB_PREFIX_ . 'customer` c
				WHERE c.active = 1
				' . Shop::addSqlRestriction()
                );
                if ($value) {
                    $orders = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
                        '
					SELECT COUNT(*)
					FROM `' . _DB_PREFIX_ . 'orders` o
					WHERE o.valid = 1
					' . Shop::addSqlRestriction()
                    );
                    $value = round($orders / $value, 2);
                }

ConfigurationKPI::updateValue('ORDERS_PER_CUSTOMER', $value);
                ConfigurationKPI::updateValue('ORDERS_PER_CUSTOMER_EXPIRE', strtotime('+1 day'));

break;

case 'average_order_value':
                $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow(
                    '
				SELECT
					COUNT(`id_order`) AS orders,
					SUM(`total_paid_tax_excl` / `conversion_rate`) AS total_paid_tax_excl
				FROM `' . _DB_PREFIX_ . 'orders`
				WHERE `invoice_date` BETWEEN "' . pSQL(date('Y-m-d', strtotime('-31 day'))) . ' 00:00:00" AND "' . pSQL(
                        date('Y-m-d', strtotime('-1 day'))
                    ) . ' 23:59:59"
				' . Shop::addSqlRestriction()
                );
                $value = $this->context->getCurrentLocale()->formatPrice(
                    $row['orders'] ? $row['total_paid_tax_excl'] / $row['orders'] : 0,
                    $currency->iso_code
                );
                ConfigurationKPI::updateValue('AVG_ORDER_VALUE', $value);
                ConfigurationKPI::updateValue(
                    'AVG_ORDER_VALUE_EXPIRE',
                    strtotime(date('Y-m-d 00:00:00', strtotime('+1 day')))
                );

break;

case 'netprofit_visit':
                $date_from = date('Y-m-d', strtotime('-31 day'));
                $date_to = date('Y-m-d', strtotime('-1 day'));

$total_visitors = AdminStatsController::getVisits(false, $date_from, $date_to);
                $net_profits = AdminStatsController::getTotalSales($date_from, $date_to);
                $net_profits -= AdminStatsController::getExpenses($date_from, $date_to);
                $net_profits -= AdminStatsController::getPurchases($date_from, $date_to);

if ($total_visitors) {
                    $value = $this->context->getCurrentLocale()->formatPrice($net_profits / $total_visitors, $currency->iso_code);
                } elseif ($net_profits) {
                    $value = '&infin;';
                } else {
                    $value = $this->context->getCurrentLocale()->formatPrice(0, $currency->iso_code);
                }

ConfigurationKPI::updateValue('NETPROFIT_VISIT', $value);
                ConfigurationKPI::updateValue(
                    'NETPROFIT_VISIT_EXPIRE',
                    strtotime(date('Y-m-d 00:00:00', strtotime('+1 day')))
                );

break;

case 'products_per_category':
                $products = AdminStatsController::getTotalProducts();
                $categories = AdminStatsController::getTotalCategories();
                $value = round($products / $categories);
                ConfigurationKPI::updateValue('PRODUCTS_PER_CATEGORY', $value);
                ConfigurationKPI::updateValue('PRODUCTS_PER_CATEGORY_EXPIRE', strtotime('+1 hour'));

break;

case 'top_category':
                if (!($id_category = AdminStatsController::getBestCategory(
                    date('Y-m-d', strtotime('-1 month')),
                    date('Y-m-d')
                ))) {
                    $value = $this->trans('No category', [], 'Admin.Stats.Feature');
                } else {
                    $category = new Category($id_category, $this->context->language->id);
                    $value = $category->name;
                }

ConfigurationKPI::updateValue('TOP_CATEGORY', [$this->context->language->id => $value]);
                ConfigurationKPI::updateValue(
                    'TOP_CATEGORY_EXPIRE',
                    [$this->context->language->id => strtotime('+1 day')]
                );

break;

case 'shopping_cart_total':
                $cartId = Tools::getValue('cartId');
                $cart = new Cart((int) $cartId);
                if (Validate::isLoadedObject($cart)) {
                    $value = $this->context->getCurrentLocale()->formatPrice(
                        $cart->getCartTotalPrice(),
                        Currency::getIsoCodeById((int) $cart->id_currency)
                    );
                }
                break;
        }
        if ($value !== false) {
            die(json_encode([
                'value' => $value,
                'tooltip' => $tooltip,
            ]));
        }
        die(json_encode(['has_errors' => true]));
    }

/**
     * Display graphs on the stats page from module data.
     */
    public function displayAjaxGraphDraw()
    {
        if (!$this->access('view')) {
            return die(json_encode(['error' => 'You do not have the right permission']));
        }

$module = Tools::getValue('module');
        $render = Tools::getValue('render');
        $type = Tools::getValue('type');
        $option = Tools::getValue('option');
        $layers = Tools::getValue('layers');
        $width = Tools::getValue('width');
        $height = Tools::getValue('height');
        $id_employee = Tools::getValue('id_employee');
        $id_lang = Tools::getValue('id_lang');

/** @var ModuleGraph|false $graph */
        $graph = Module::getInstanceByName($module);
        if (false === $graph) {
            $this->ajaxRender(Tools::displayError('Graph module could not be loaded.'));

return;
        }

$graph->setEmployee($id_employee);
        $graph->setLang($id_lang);
        if ($option) {
            $graph->setOption($option, $layers);
        }

$graph->create($render, $type, $width, $height, $layers);
        $graph->draw();
    }

/**
     * Display grid with module data on the stats page.
     */
    public function displayAjaxGraphGrid()
    {
        if (!$this->access('view')) {
            return die(json_encode(['error' => 'You do not have the right permission']));
        }

$module = Tools::getValue('module');
        $render = Tools::getValue('render');
        $type = Tools::getValue('type');
        $option = Tools::getValue('option');
        $width = (int) (Tools::getValue('width', 600));
        $height = (int) (Tools::getValue('height', 920));
        $start = (int) (Tools::getValue('start', 0));
        $limit = (int) (Tools::getValue('limit', 40));
        $sort = Tools::getValue('sort', 0); // Should be a String. Default value is an Integer because we don't know what can be the name of the column to sort.
        $dir = Tools::getValue('dir', 0); // Should be a String : Either ASC or DESC
        $id_employee = (int) (Tools::getValue('id_employee'));
        $id_lang = (int) (Tools::getValue('id_lang'));

/** @var ModuleGrid|false $grid */
        $grid = Module::getInstanceByName($module);
        if (false === $grid) {
            $this->ajaxRender(Tools::displayError('Grid module could not be loaded.'));

return;
        }

$grid->setEmployee($id_employee);
        $grid->setLang($id_lang);
        if ($option) {
            $grid->setOption($option);
        }

$grid->create($render, $type, $width, $height, $start, $limit, $sort, $dir);
        $grid->render();
    }
}

cmd: