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);
    }
Example #2
0
    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 `' . $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 ' . $this->_start . ', ' . $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()');
    }
Example #3
0
    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()');
    }
Example #4
0
    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)) {
                $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()
    {
        $dateBetween = $this->getDate();
        $arrayDateBetween = explode(' AND ', $dateBetween);
        $this->_totalCount = $this->getTotalCount($dateBetween);
        $this->_query = '
		SELECT p.reference, p.id_product, pl.name, ROUND(AVG(od.product_price / c.conversion_rate), 2) as avgPriceSold, 
			(p.quantity + IFNULL((SELECT SUM(pa.quantity) FROM ' . _DB_PREFIX_ . 'product_attribute pa WHERE pa.id_product = p.id_product GROUP BY pa.id_product), 0)) 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) / c.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 = ' . intval($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
		LEFT JOIN ' . _DB_PREFIX_ . 'currency c ON o.id_currency = c.id_currency
		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()->ExecuteS($this->_query);
    }
Example #7
0
    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->_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);
    }
Example #9
0
    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()');
    }
Example #10
0
    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()');
    }
Example #11
0
 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);
 }
Example #12
0
    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()
    {
        $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'] .= '&bull; ';
                } 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()');
    }
Example #14
0
 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	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);
    }