public function getData()
{
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
$this->query = 'SELECT SQL_CALC_FOUND_ROWS cr.code, ocr.name, COUNT(ocr.id_cart_rule) as total, ROUND(SUM(o.total_paid_real) / o.conversion_rate,2) as ca
FROM ' . _DB_PREFIX_ . 'order_cart_rule ocr
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = ocr.id_order
LEFT JOIN ' . _DB_PREFIX_ . 'cart_rule cr ON cr.id_cart_rule = ocr.id_cart_rule
WHERE o.valid = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
AND o.invoice_date BETWEEN ' . $this->getDate() . '
GROUP BY ocr.id_cart_rule';
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . bqSQL($this->_sort) . '`';
if (isset($this->_direction) && (Tools::strtoupper($this->_direction) == 'ASC' || Tools::strtoupper($this->_direction) == 'DESC')) {
$this->query .= ' ' . pSQL($this->_direction);
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->query .= ' LIMIT ' . (int) $this->_start . ', ' . (int) $this->_limit;
}
$values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
foreach ($values as &$value) {
$value['ca'] = Tools::displayPrice($value['ca'], $currency);
}
$this->_values = $values;
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
public static function engine($params)
{
if (!($render = Configuration::get('PS_STATS_GRID_RENDER'))) {
return Tools::displayError('No grid engine selected');
}
if (!file_exists(dirname(__FILE__) . '/../modules/' . $render . '/' . $render . '.php')) {
return Tools::displayError('Grid engine selected unavailable');
}
$grider = 'grider.php?render=' . $render . '&module=' . Tools::getValue('module');
global $cookie;
$grider .= '&id_employee=' . intval($cookie->id_employee);
$grider .= '&id_lang=' . intval($cookie->id_lang);
if (!isset($params['width']) or !Validate::IsUnsignedInt($params['width'])) {
$params['width'] = 600;
}
if (!isset($params['height']) or !Validate::IsUnsignedInt($params['height'])) {
$params['height'] = 920;
}
if (!isset($params['start']) or !Validate::IsUnsignedInt($params['start'])) {
$params['start'] = 0;
}
if (!isset($params['limit']) or !Validate::IsUnsignedInt($params['height'])) {
$params['limit'] = 40;
}
$grider .= '&width=' . $params['width'];
$grider .= '&height=' . $params['height'];
if (isset($params['start']) and Validate::IsUnsignedInt($params['start'])) {
$grider .= '&start=' . $params['start'];
}
if (isset($params['limit']) and Validate::IsUnsignedInt($params['limit'])) {
$grider .= '&limit=' . $params['limit'];
}
if (isset($params['type']) and Validate::IsName($params['type'])) {
$grider .= '&type=' . $params['type'];
}
if (isset($params['option']) and Validate::IsGenericName($params['option'])) {
$grider .= '&option=' . $params['option'];
}
if (isset($params['sort']) and Validate::IsName($params['sort'])) {
$grider .= '&sort=' . $params['sort'];
}
if (isset($params['dir']) and Validate::IsSortDirection($params['dir'])) {
$grider .= '&dir=' . $params['dir'];
}
require_once dirname(__FILE__) . '/../modules/' . $render . '/' . $render . '.php';
return call_user_func(array($render, 'hookGridEngine'), $params, $grider);
}
public function getData()
{
$this->_totalCount = $this->getTotalCount();
$this->_query = '
SELECT od.name, COUNT(od.id_discount) as total, SUM(o.total_paid_real) / c.conversion_rate as ca
FROM ' . _DB_PREFIX_ . 'order_discount od
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = od.id_order
LEFT JOIN `' . _DB_PREFIX_ . 'currency` c ON o.id_currency = c.id_currency
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $this->getDate() . '
GROUP BY od.id_discount';
if (Validate::IsName($this->_sort)) {
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction)) {
$this->_query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 or Validate::IsUnsignedInt($this->_start)) and Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance()->ExecuteS($this->_query);
}
public function getData()
{
$this->_totalCount = $this->getTotalCount();
$this->_query = '
SELECT s.name, SUM(od.product_quantity) as quantity, ROUND(SUM(od.product_quantity * od.product_price) / c.conversion_rate, 2) as sales
FROM ' . _DB_PREFIX_ . 'order_detail od
LEFT JOIN ' . _DB_PREFIX_ . 'product p ON p.id_product = od.product_id
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = od.id_order
LEFT JOIN ' . _DB_PREFIX_ . 'currency c ON c.id_currency = o.id_currency
LEFT JOIN ' . _DB_PREFIX_ . 'supplier s ON s.id_supplier = p.id_supplier
WHERE o.invoice_date BETWEEN ' . $this->getDate() . ' AND o.valid = 1
AND s.id_supplier IS NOT NULL
GROUP BY p.id_supplier';
if (Validate::IsName($this->_sort)) {
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) and Validate::IsSortDirection($this->_direction)) {
$this->_query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 or Validate::IsUnsignedInt($this->_start)) and Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance()->ExecuteS($this->_query);
}
public function getData()
{
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
$date_between = $this->getDate();
$id_lang = $this->getLang();
// If a shop is selected, get all children categories for the shop
$categories = array();
if (Shop::getContext() != Shop::CONTEXT_ALL) {
$sql = 'SELECT c.nleft, c.nright
FROM ' . _DB_PREFIX_ . 'category c
WHERE c.id_category IN (
SELECT s.id_category
FROM ' . _DB_PREFIX_ . 'shop s
WHERE s.id_shop IN (' . implode(', ', Shop::getContextListShopID()) . ')
)';
if ($result = Db::getInstance()->executeS($sql)) {
$ntree_restriction = array();
foreach ($result as $row) {
$ntree_restriction[] = '(nleft >= ' . $row['nleft'] . ' AND nright <= ' . $row['nright'] . ')';
}
if ($ntree_restriction) {
$sql = 'SELECT id_category
FROM ' . _DB_PREFIX_ . 'category
WHERE ' . implode(' OR ', $ntree_restriction);
if ($result = Db::getInstance()->executeS($sql)) {
foreach ($result as $row) {
$categories[] = $row['id_category'];
}
}
}
}
}
// Get best categories
$this->query = '
SELECT SQL_CALC_FOUND_ROWS ca.`id_category`, CONCAT(parent.name, \' > \', calang.`name`) as name,
IFNULL(SUM(t.`totalQuantitySold`), 0) AS totalQuantitySold,
ROUND(IFNULL(SUM(t.`totalPriceSold`), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.`counter`), 0)
FROM `' . _DB_PREFIX_ . 'page` p
LEFT JOIN `' . _DB_PREFIX_ . 'page_viewed` pv ON p.`id_page` = pv.`id_page`
LEFT JOIN `' . _DB_PREFIX_ . 'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `' . _DB_PREFIX_ . 'product` pr ON CAST(p.`id_object` AS UNSIGNED INTEGER) = pr.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr2 ON capr2.`id_product` = pr.`id_product`
WHERE capr.`id_category` = capr2.`id_category`
AND p.`id_page_type` = 1
AND dr.`time_start` BETWEEN ' . $date_between . '
AND dr.`time_end` BETWEEN ' . $date_between . '
) AS totalPageViewed
FROM `' . _DB_PREFIX_ . 'category` ca
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` calang ON (ca.`id_category` = calang.`id_category` AND calang.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('calang') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` parent ON (ca.`id_parent` = parent.`id_category` AND parent.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('parent') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr ON ca.`id_category` = capr.`id_category`
LEFT JOIN (
SELECT pr.`id_product`, t.`totalQuantitySold`, t.`totalPriceSold`
FROM `' . _DB_PREFIX_ . 'product` pr
LEFT JOIN (
SELECT pr.`id_product`,
IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
IFNULL(SUM(cp.`product_price` * 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.valid = 1
AND o.invoice_date BETWEEN ' . $date_between . '
GROUP BY pr.`id_product`
) t ON t.`id_product` = pr.`id_product`
) t ON t.`id_product` = capr.`id_product`
' . ($categories ? 'WHERE ca.id_category IN (' . implode(', ', $categories) . ')' : '') . '
GROUP BY ca.`id_category`
HAVING ca.`id_category` != 1';
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
$this->query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
foreach ($values as &$value) {
$value['totalPriceSold'] = Tools::displayPrice($value['totalPriceSold'], $currency);
}
$this->_values = $values;
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
public function getData()
{
$this->_totalCount = $this->getTotalCount();
$this->query = 'SELECT s.name, SUM(od.product_quantity) as quantity, ROUND(SUM(od.product_quantity * od.product_price) / o.conversion_rate, 2) as sales
FROM ' . _DB_PREFIX_ . 'order_detail od
LEFT JOIN ' . _DB_PREFIX_ . 'product p ON p.id_product = od.product_id
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = od.id_order
LEFT JOIN ' . _DB_PREFIX_ . 'supplier s ON s.id_supplier = p.id_supplier
WHERE o.invoice_date BETWEEN ' . $this->getDate() . '
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
AND o.valid = 1
AND s.id_supplier IS NOT NULL
GROUP BY p.id_supplier';
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
$this->query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
}
public function getData()
{
$this->_query = '
SELECT SQL_CALC_FOUND_ROWS c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`,
COUNT(co.`id_connections`) as totalVisits,
IFNULL((
SELECT ROUND(SUM(IFNULL(o.`total_paid_real`, 0) / cu.conversion_rate), 2)
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'currency` cu ON o.id_currency = cu.id_currency
WHERE o.id_customer = c.id_customer
AND o.invoice_date BETWEEN ' . $this->getDate() . '
AND o.valid
), 0) as totalMoneySpent
FROM `' . _DB_PREFIX_ . 'customer` c
LEFT JOIN `' . _DB_PREFIX_ . 'guest` g ON c.`id_customer` = g.`id_customer`
LEFT JOIN `' . _DB_PREFIX_ . 'connections` co ON g.`id_guest` = co.`id_guest`
WHERE co.date_add BETWEEN ' . $this->getDate() . Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c') . 'GROUP BY c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`';
if (Validate::IsName($this->_sort)) {
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
$this->_query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->_query);
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
public function getData()
{
$this->_totalCount = $this->getTotalCount();
$this->query = 'SELECT m.name, SUM(od.product_quantity) as quantity, ROUND(SUM(od.product_quantity * od.product_price) / c.conversion_rate, 2) as sales
FROM ' . _DB_PREFIX_ . 'order_detail od
LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id)
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON (o.id_order = od.id_order)
LEFT JOIN ' . _DB_PREFIX_ . 'currency c ON (c.id_currency = o.id_currency)
LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE o.invoice_date BETWEEN ' . $this->getDate() . '
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
AND o.valid = 1
AND m.id_manufacturer IS NOT NULL
GROUP BY p.id_manufacturer';
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . bqSQL($this->_sort) . '`';
if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
$this->query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->query .= ' LIMIT ' . (int) $this->_start . ', ' . (int) $this->_limit;
}
$this->_values = Db::getInstance()->executeS($this->query);
}
public function engine($params)
{
if (!($render = Configuration::get('PS_STATS_GRID_RENDER'))) {
return Tools::displayError('No grid engine selected');
}
if (!Validate::isModuleName($render)) {
die(Tools::displayError());
}
if (!file_exists(_PS_ROOT_DIR_ . '/modules/' . $render . '/' . $render . '.php')) {
return Tools::displayError('Grid engine selected is unavailable.');
}
$grider = 'grider.php?render=' . $render . '&module=' . Tools::safeOutput(Tools::getValue('module'));
$context = Context::getContext();
$grider .= '&id_employee=' . (int) $context->employee->id;
$grider .= '&id_lang=' . (int) $context->language->id;
if (!isset($params['width']) || !Validate::IsUnsignedInt($params['width'])) {
$params['width'] = 600;
}
if (!isset($params['height']) || !Validate::IsUnsignedInt($params['height'])) {
$params['height'] = 920;
}
if (!isset($params['start']) || !Validate::IsUnsignedInt($params['start'])) {
$params['start'] = 0;
}
if (!isset($params['limit']) || !Validate::IsUnsignedInt($params['limit'])) {
$params['limit'] = 40;
}
$grider .= '&width=' . $params['width'];
$grider .= '&height=' . $params['height'];
if (isset($params['start']) && Validate::IsUnsignedInt($params['start'])) {
$grider .= '&start=' . $params['start'];
}
if (isset($params['limit']) && Validate::IsUnsignedInt($params['limit'])) {
$grider .= '&limit=' . $params['limit'];
}
if (isset($params['type']) && Validate::IsName($params['type'])) {
$grider .= '&type=' . $params['type'];
}
if (isset($params['option']) && Validate::IsGenericName($params['option'])) {
$grider .= '&option=' . $params['option'];
}
if (isset($params['sort']) && Validate::IsName($params['sort'])) {
$grider .= '&sort=' . $params['sort'];
}
if (isset($params['dir']) && Validate::isSortDirection($params['dir'])) {
$grider .= '&dir=' . $params['dir'];
}
require_once _PS_ROOT_DIR_ . '/modules/' . $render . '/' . $render . '.php';
return call_user_func(array($render, 'hookGridEngine'), $params, $grider);
}
public function getData()
{
$dateBetween = $this->getDate();
$id_lang = intval($this->getLang());
$this->_totalCount = $this->getTotalCount();
$this->_query = '
SELECT ca.`id_category`, CONCAT(parent.name, \' > \', calang.`name`) as name,
IFNULL(SUM(t.`totalQuantitySold`), 0) AS totalQuantitySold,
ROUND(IFNULL(SUM(t.`totalPriceSold`), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.`counter`), 0)
FROM `' . _DB_PREFIX_ . 'page` p
LEFT JOIN `' . _DB_PREFIX_ . 'page_viewed` pv ON p.`id_page` = pv.`id_page`
LEFT JOIN `' . _DB_PREFIX_ . 'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `' . _DB_PREFIX_ . 'product` pr ON CAST(p.`id_object` AS UNSIGNED INTEGER) = pr.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr2 ON capr2.`id_product` = pr.`id_product`
WHERE capr.`id_category` = capr2.`id_category`
AND p.`id_page_type` = 1
AND dr.`time_start` BETWEEN ' . $dateBetween . '
AND dr.`time_end` BETWEEN ' . $dateBetween . '
) AS totalPageViewed
FROM `' . _DB_PREFIX_ . 'category` ca
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` calang ON (ca.`id_category` = calang.`id_category` AND calang.`id_lang` = ' . $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` parent ON (ca.`id_parent` = parent.`id_category` AND parent.`id_lang` = ' . $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr ON ca.`id_category` = capr.`id_category`
LEFT JOIN (
SELECT pr.`id_product`, t.`totalQuantitySold`, t.`totalPriceSold`
FROM `' . _DB_PREFIX_ . 'product` pr
LEFT JOIN (
SELECT pr.`id_product`,
IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
IFNULL(SUM(pr.`price` * cp.`product_quantity`), 0) / c.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`
LEFT JOIN `' . _DB_PREFIX_ . 'currency` c ON o.id_currency = c.id_currency
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $dateBetween . '
GROUP BY pr.`id_product`
) t ON t.`id_product` = pr.`id_product`
) t ON t.`id_product` = capr.`id_product`
GROUP BY ca.`id_category`
HAVING ca.`id_category` != 1';
if (Validate::IsName($this->_sort)) {
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) and Validate::IsSortDirection($this->_direction)) {
$this->_query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 or Validate::IsUnsignedInt($this->_start)) and Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance()->ExecuteS($this->_query);
}
public function getData()
{
$dateBetween = $this->getDate();
$arrayDateBetween = explode(' AND ', $dateBetween);
$this->_query = '
SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, pl.name, ROUND(AVG(od.product_price / o.conversion_rate), 2) as avgPriceSold,
IFNULL((SELECT SUM(pa.quantity) FROM ' . _DB_PREFIX_ . 'product_attribute pa WHERE pa.id_product = p.id_product GROUP BY pa.id_product), p.quantity) as quantity,
IFNULL(SUM(od.product_quantity), 0) AS totalQuantitySold,
ROUND(IFNULL(IFNULL(SUM(od.product_quantity), 0) / (1 + LEAST(TO_DAYS(' . $arrayDateBetween[1] . '), TO_DAYS(NOW())) - GREATEST(TO_DAYS(' . $arrayDateBetween[0] . '), TO_DAYS(p.date_add))), 0), 2) as averageQuantitySold,
ROUND(IFNULL(SUM((od.product_price * od.product_quantity) / o.conversion_rate), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.counter), 0)
FROM ' . _DB_PREFIX_ . 'page pa
LEFT JOIN ' . _DB_PREFIX_ . 'page_viewed pv ON pa.id_page = pv.id_page
LEFT JOIN ' . _DB_PREFIX_ . 'date_range dr ON pv.id_date_range = dr.id_date_range
WHERE pa.id_object = p.id_product AND pa.id_page_type = 1
AND dr.time_start BETWEEN ' . $dateBetween . '
AND dr.time_end BETWEEN ' . $dateBetween . '
) AS totalPageViewed
FROM ' . _DB_PREFIX_ . 'product p
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->getLang() . ')
LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON od.product_id = p.id_product
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON od.id_order = o.id_order
WHERE p.active = 1 AND o.valid = 1
AND o.invoice_date BETWEEN ' . $dateBetween . '
GROUP BY od.product_id';
if (Validate::IsName($this->_sort)) {
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) and Validate::IsSortDirection($this->_direction)) {
$this->_query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 or Validate::IsUnsignedInt($this->_start)) and Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query);
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
public function getData()
{
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
$date_between = $this->getDate();
$id_lang = $this->getLang();
//If column 'order_detail.original_wholesale_price' does not exist, create it
Db::getInstance(_PS_USE_SQL_SLAVE_)->query('SHOW COLUMNS FROM `' . _DB_PREFIX_ . 'order_detail` LIKE "original_wholesale_price"');
if (Db::getInstance()->NumRows() == 0) {
Db::getInstance()->execute('ALTER TABLE `' . _DB_PREFIX_ . 'order_detail` ADD `original_wholesale_price` DECIMAL( 20, 6 ) NOT NULL DEFAULT "0.000000"');
}
// If a shop is selected, get all children categories for the shop
$categories = array();
if (Shop::getContext() != Shop::CONTEXT_ALL) {
$sql = 'SELECT c.nleft, c.nright
FROM ' . _DB_PREFIX_ . 'category c
WHERE c.id_category IN (
SELECT s.id_category
FROM ' . _DB_PREFIX_ . 'shop s
WHERE s.id_shop IN (' . implode(', ', Shop::getContextListShopID()) . ')
)';
if ($result = Db::getInstance()->executeS($sql)) {
$ntree_restriction = array();
foreach ($result as $row) {
$ntree_restriction[] = '(nleft >= ' . $row['nleft'] . ' AND nright <= ' . $row['nright'] . ')';
}
if ($ntree_restriction) {
$sql = 'SELECT id_category
FROM ' . _DB_PREFIX_ . 'category
WHERE ' . implode(' OR ', $ntree_restriction);
if ($result = Db::getInstance()->executeS($sql)) {
foreach ($result as $row) {
$categories[] = $row['id_category'];
}
}
}
}
}
$onlyChildren = '';
if ((int) Tools::getValue('onlyChildren') == 1) {
$onlyChildren = 'AND NOT EXISTS (SELECT NULL FROM ' . _DB_PREFIX_ . 'category WHERE id_parent = ca.id_category)';
}
// Get best categories
if (version_compare(_PS_VERSION_, '1.6.1.1', '>=')) {
$this->query = '
SELECT SQL_CALC_FOUND_ROWS ca.`id_category`, CONCAT(parent.name, \' > \', calang.`name`) as name,
IFNULL(SUM(t.`totalQuantitySold`), 0) AS totalQuantitySold,
ROUND(IFNULL(SUM(t.`totalPriceSold`), 0), 2) AS totalPriceSold,
ROUND(IFNULL(SUM(t.`totalWholeSalePriceSold`), 0), 2) AS totalWholeSalePriceSold,
(
SELECT IFNULL(SUM(pv.`counter`), 0)
FROM `' . _DB_PREFIX_ . 'page` p
LEFT JOIN `' . _DB_PREFIX_ . 'page_viewed` pv ON p.`id_page` = pv.`id_page`
LEFT JOIN `' . _DB_PREFIX_ . 'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `' . _DB_PREFIX_ . 'product` pr ON CAST(p.`id_object` AS UNSIGNED INTEGER) = pr.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr2 ON capr2.`id_product` = pr.`id_product`
WHERE capr.`id_category` = capr2.`id_category`
AND p.`id_page_type` = 1
AND dr.`time_start` BETWEEN ' . $date_between . '
AND dr.`time_end` BETWEEN ' . $date_between . '
) AS totalPageViewed,
(
SELECT COUNT(id_category) FROM ' . _DB_PREFIX_ . 'category WHERE `id_parent` = ca.`id_category`
) AS hasChildren
FROM `' . _DB_PREFIX_ . 'category` ca
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` calang ON (ca.`id_category` = calang.`id_category` AND calang.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('calang') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` parent ON (ca.`id_parent` = parent.`id_category` AND parent.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('parent') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr ON ca.`id_category` = capr.`id_category`
LEFT JOIN (
SELECT pr.`id_product`, t.`totalQuantitySold`, t.`totalPriceSold`, t.`totalWholeSalePriceSold`
FROM `' . _DB_PREFIX_ . 'product` pr
LEFT JOIN (
SELECT pr.`id_product`, pa.`wholesale_price`,
IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
IFNULL(SUM(cp.`product_price` * cp.`product_quantity`), 0) / o.conversion_rate AS totalPriceSold,
IFNULL(SUM(
CASE
WHEN cp.`original_wholesale_price` <> "0.000000"
THEN cp.`original_wholesale_price` * cp.`product_quantity`
WHEN pa.`wholesale_price` <> "0.000000"
THEN pa.`wholesale_price` * cp.`product_quantity`
WHEN pr.`wholesale_price` <> "0.000000"
THEN pr.`wholesale_price` * cp.`product_quantity`
END
), 0) / o.conversion_rate AS totalWholeSalePriceSold
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`
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON pa.`id_product_attribute` = cp.`product_attribute_id`
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $date_between . '
GROUP BY pr.`id_product`
) t ON t.`id_product` = pr.`id_product`
) t ON t.`id_product` = capr.`id_product`
' . ($categories ? 'WHERE ca.id_category IN (' . implode(', ', $categories) . ')' : '') . '
' . $onlyChildren . '
GROUP BY ca.`id_category`
HAVING ca.`id_category` != 1';
} else {
$this->query = '
SELECT SQL_CALC_FOUND_ROWS ca.`id_category`, CONCAT(parent.name, \' > \', calang.`name`) as name,
IFNULL(SUM(t.`totalQuantitySold`), 0) AS totalQuantitySold,
ROUND(IFNULL(SUM(t.`totalPriceSold`), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.`counter`), 0)
FROM `' . _DB_PREFIX_ . 'page` p
LEFT JOIN `' . _DB_PREFIX_ . 'page_viewed` pv ON p.`id_page` = pv.`id_page`
LEFT JOIN `' . _DB_PREFIX_ . 'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `' . _DB_PREFIX_ . 'product` pr ON CAST(p.`id_object` AS UNSIGNED INTEGER) = pr.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr2 ON capr2.`id_product` = pr.`id_product`
WHERE capr.`id_category` = capr2.`id_category`
AND p.`id_page_type` = 1
AND dr.`time_start` BETWEEN ' . $date_between . '
AND dr.`time_end` BETWEEN ' . $date_between . '
) AS totalPageViewed,
(
SELECT COUNT(id_category) FROM ' . _DB_PREFIX_ . 'category WHERE `id_parent` = ca.`id_category`
) AS hasChildren
FROM `' . _DB_PREFIX_ . 'category` ca
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` calang ON (ca.`id_category` = calang.`id_category` AND calang.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('calang') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` parent ON (ca.`id_parent` = parent.`id_category` AND parent.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('parent') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr ON ca.`id_category` = capr.`id_category`
LEFT JOIN (
SELECT pr.`id_product`, t.`totalQuantitySold`, t.`totalPriceSold`
FROM `' . _DB_PREFIX_ . 'product` pr
LEFT JOIN (
SELECT pr.`id_product`,
IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
IFNULL(SUM(cp.`product_price` * 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`
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $date_between . '
GROUP BY pr.`id_product`
) t ON t.`id_product` = pr.`id_product`
) t ON t.`id_product` = capr.`id_product`
' . ($categories ? 'WHERE ca.id_category IN (' . implode(', ', $categories) . ')' : '') . '
' . $onlyChildren . '
GROUP BY ca.`id_category`
HAVING ca.`id_category` != 1';
}
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . bqSQL($this->_sort) . '`';
if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
$this->query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->query .= ' LIMIT ' . (int) $this->_start . ', ' . (int) $this->_limit;
}
$values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
foreach ($values as &$value) {
if ((int) Tools::getIsset('export') == false) {
$parts = explode('>', $value['name']);
$value['name'] = '<i class="icon-folder-open"></i> ' . trim($parts[0]) . ' > ';
if ((int) $value['hasChildren'] == 0) {
$value['name'] .= '• ';
} else {
$value['name'] .= '<i class="icon-folder-open"></i> ';
}
$value['name'] .= trim($parts[1]);
}
if (isset($value['totalWholeSalePriceSold'])) {
$value['totalWholeSalePriceSold'] = Tools::displayPrice($value['totalPriceSold'] - $value['totalWholeSalePriceSold'], $currency);
}
$value['totalPriceSold'] = Tools::displayPrice($value['totalPriceSold'], $currency);
}
$this->_values = $values;
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
public function getData()
{
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
$date_between = $this->getDate();
$array_date_between = explode(' AND ', $date_between);
$this->query = 'SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, pl.name,
ROUND(AVG(od.product_price / o.conversion_rate), 2) as avgPriceSold,
IFNULL(stock.quantity, 0) as quantity,
IFNULL(SUM(od.product_quantity), 0) AS totalQuantitySold,
ROUND(IFNULL(IFNULL(SUM(od.product_quantity), 0) / (1 + LEAST(TO_DAYS(' . $array_date_between[1] . '), TO_DAYS(NOW())) - GREATEST(TO_DAYS(' . $array_date_between[0] . '), TO_DAYS(product_shop.date_add))), 0), 2) as averageQuantitySold,
ROUND(IFNULL(SUM((od.product_price * od.product_quantity) / o.conversion_rate), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.counter), 0)
FROM ' . _DB_PREFIX_ . 'page pa
LEFT JOIN ' . _DB_PREFIX_ . 'page_viewed pv ON pa.id_page = pv.id_page
LEFT JOIN ' . _DB_PREFIX_ . 'date_range dr ON pv.id_date_range = dr.id_date_range
WHERE pa.id_object = p.id_product AND pa.id_page_type = ' . (int) Page::getPageTypeByName('product') . '
AND dr.time_start BETWEEN ' . $date_between . '
AND dr.time_end BETWEEN ' . $date_between . '
) AS totalPageViewed,
product_shop.active
FROM ' . _DB_PREFIX_ . 'product p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->getLang() . ' ' . Shop::addSqlRestrictionOnLang('pl') . ')
LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON od.product_id = p.id_product
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON od.id_order = o.id_order
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
' . Product::sqlStock('p', 0) . '
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $date_between . '
GROUP BY od.product_id';
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . bqSQL($this->_sort) . '`';
if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
$this->query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->query .= ' LIMIT ' . (int) $this->_start . ', ' . (int) $this->_limit;
}
$values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
foreach ($values as &$value) {
$value['avgPriceSold'] = Tools::displayPrice($value['avgPriceSold'], $currency);
$value['totalPriceSold'] = Tools::displayPrice($value['totalPriceSold'], $currency);
}
unset($value);
$this->_values = $values;
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
public function getData()
{
$this->_totalCount = $this->getTotalCount();
$this->_query = '
SELECT c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`,
COUNT(DISTINCT co.`id_connections`) AS totalVisits,
COUNT(cop.`id_page`) AS totalPageViewed, (
SELECT ROUND(SUM(IFNULL(o.`total_paid_real`, 0) / cu.conversion_rate), 2)
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'currency` cu ON o.id_currency = cu.id_currency
WHERE o.id_customer = c.id_customer
AND o.invoice_date BETWEEN ' . $this->getDate() . '
AND o.valid
) AS totalMoneySpent
FROM `' . _DB_PREFIX_ . 'customer` c
LEFT JOIN `' . _DB_PREFIX_ . 'guest` g ON c.`id_customer` = g.`id_customer`
LEFT JOIN `' . _DB_PREFIX_ . 'connections` co ON g.`id_guest` = co.`id_guest`
LEFT JOIN `' . _DB_PREFIX_ . 'connections_page` cop ON co.`id_connections` = cop.`id_connections`
WHERE co.date_add BETWEEN ' . $this->getDate() . '
GROUP BY c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`';
if (Validate::IsName($this->_sort)) {
if ($this->_sort == 'total') {
$this->_sort = 'totalMoneySpent';
}
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) and Validate::IsSortDirection($this->_direction)) {
$this->_query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 or Validate::IsUnsignedInt($this->_start)) and Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance()->ExecuteS($this->_query);
}
public function getData()
{
$this->_query = 'SELECT SQL_CALC_FOUND_ROWS ocr.name as name, COUNT(ocr.id_cart_rule) as total, ROUND(SUM(o.total_paid_real) / o.conversion_rate, 2) as ca
FROM ' . _DB_PREFIX_ . 'order_cart_rule ocr
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = ocr.id_order
WHERE o.valid = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
AND o.invoice_date BETWEEN ' . $this->getDate() . '
GROUP BY ocr.id_cart_rule';
if (Validate::IsName($this->_sort)) {
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) && (strtoupper($this->_direction) == 'ASC' || strtoupper($this->_direction) == 'DESC')) {
$this->_query .= ' ' . pSQL($this->_direction);
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->_query);
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
public function getData()
{
$this->_query = '
SELECT SQL_CALC_FOUND_ROWS od.name, COUNT(od.id_discount) as total, SUM(o.total_paid_real) / o.conversion_rate as ca
FROM ' . _DB_PREFIX_ . 'order_discount od
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = od.id_order
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $this->getDate() . '
GROUP BY od.id_discount';
if (Validate::IsName($this->_sort)) {
$this->_query .= ' ORDER BY `' . $this->_sort . '`';
if (isset($this->_direction) && (strtoupper($this->_direction) == 'ASC' || strtoupper($this->_direction) == 'DESC')) {
$this->_query .= ' ' . pSQL($this->_direction);
}
}
if (($this->_start === 0 or Validate::IsUnsignedInt($this->_start)) and Validate::IsUnsignedInt($this->_limit)) {
$this->_query .= ' LIMIT ' . $this->_start . ', ' . $this->_limit;
}
$this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($this->_query);
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
