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()'); }
/** * @return int Current page ID */ public static function getCurrentId() { $controller = Dispatcher::getInstance()->getController(); $page_type_id = Page::getPageTypeByName($controller); // Some pages must be distinguished in order to record exactly what is being seen // @todo dispatcher module $special_array = array('product' => 'id_product', 'category' => 'id_category', 'order' => 'step', 'manufacturer' => 'id_manufacturer'); $where = ''; $insert_data = array('id_page_type' => $page_type_id); if (array_key_exists($controller, $special_array)) { $object_id = Tools::getValue($special_array[$controller], null); $where = ' AND `id_object` = ' . (int) $object_id; $insert_data['id_object'] = (int) $object_id; } $sql = 'SELECT `id_page` FROM `' . _DB_PREFIX_ . 'page` WHERE `id_page_type` = ' . (int) $page_type_id . $where; $result = Db::getInstance()->getRow($sql); if ($result['id_page']) { return $result['id_page']; } Db::getInstance()->insert('page', $insert_data, true); return Db::getInstance()->Insert_ID(); }