/** * Get product accessories * * @param integer $id_lang Language id * @return array Product accessories */ public static function getItems($iIdProduct, $id_lang, $active = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` as manufacturer_name, cl.`name` AS category_default, DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 AS new FROM `' . _DB_PREFIX_ . 'now_ideas_or_tips` LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = `id_product_2` ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON ( product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (p.`id_manufacturer`= m.`id_manufacturer`) ' . Product::sqlStock('p', 0) . ' WHERE `id_product_1` = ' . (int) $iIdProduct . ($active ? ' AND product_shop.`active` = 1 AND product_shop.`visibility` != \'none\'' : '') . ' GROUP BY product_shop.id_product'; if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) { return false; } foreach ($result as &$row) { $row['id_product_attribute'] = Product::getDefaultAttribute((int) $row['id_product']); } return Product::getProductsProperties($id_lang, $result); }
/** * Gets the name of a given product, in the given lang * HAI : override method to record product name with sort * * @since 1.5.0 * @param int $id_product * @param int $id_product_attribute Optional * @param int $id_lang Optional * @return string */ public static function getProductName($id_product, $id_product_attribute = null, $id_lang = null) { // use the lang in the context if $id_lang is not defined if (!$id_lang) { $id_lang = (int) Context::getContext()->language->id; } // creates the query object $query = new DbQuery(); // selects different names, if it is a combination if ($id_product_attribute) { $query->select('IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name ORDER BY agl.`name`, \' - \', al.name ASC SEPARATOR \', \')),pl.name) as name'); } else { $query->select('DISTINCT pl.name as name'); } // adds joins & where clauses for combinations if ($id_product_attribute) { $query->from('product_attribute', 'pa'); $query->join(Shop::addSqlAssociation('product_attribute', 'pa')); $query->innerJoin('product_lang', 'pl', 'pl.id_product = pa.id_product AND pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl')); $query->leftJoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute'); $query->leftJoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute'); $query->leftJoin('attribute_lang', 'al', 'al.id_attribute = atr.id_attribute AND al.id_lang = ' . (int) $id_lang); $query->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . (int) $id_lang); $query->where('pa.id_product = ' . (int) $id_product . ' AND pa.id_product_attribute = ' . (int) $id_product_attribute); } else { $query->from('product_lang', 'pl'); $query->where('pl.id_product = ' . (int) $id_product); $query->where('pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl')); } return Db::getInstance()->getValue($query); }
public function renderList() { $this->_select = 'b.*'; $this->_join = 'INNER JOIN `' . _DB_PREFIX_ . 'carrier_lang` b ON a.id_carrier = b.id_carrier' . Shop::addSqlRestrictionOnLang('b') . ' AND b.id_lang = ' . $this->context->language->id . ' LEFT JOIN `' . _DB_PREFIX_ . 'carrier_tax_rules_group_shop` ctrgs ON (a.`id_carrier` = ctrgs.`id_carrier` AND ctrgs.id_shop=' . (int) $this->context->shop->id . ')'; $this->_use_found_rows = false; return parent::renderList(); }
/** * AdminController::renderList() override * @see AdminController::renderList() */ public function renderList() { // removes toolbar btn $this->toolbar_btn = array(); // overrides select $this->_select = ' CONCAT(pl.name, \' \', GROUP_CONCAT(IFNULL(al.name, \'\'), \'\')) product_name, CONCAT(a.employee_lastname, \' \', a.employee_firstname) as employee, mrl.name as reason, stock.reference as product_reference, stock.ean13 as product_ean13, stock.upc as product_upc, w.id_currency as id_currency, w.name as warehouse_name'; // overrides join $this->_join = 'INNER JOIN ' . _DB_PREFIX_ . 'stock stock ON a.id_stock = stock.id_stock LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( stock.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->context->language->id . Shop::addSqlRestrictionOnLang('pl') . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'stock_mvt_reason_lang` mrl ON ( a.id_stock_mvt_reason = mrl.id_stock_mvt_reason AND mrl.id_lang = ' . (int) $this->context->language->id . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'warehouse` w ON (w.id_warehouse = stock.id_warehouse) LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.id_product_attribute = stock.id_product_attribute) LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON ( al.id_attribute = pac.id_attribute AND pac.id_product_attribute <> 0 AND al.id_lang = ' . (int) $this->context->language->id . ' )'; // overrides group $this->_group = 'GROUP BY a.id_stock_mvt'; // overrides where depending on the warehouse $id_warehouse = (int) $this->getCurrentWarehouseId(); if ($id_warehouse > 0) { $this->_where = ' AND w.id_warehouse = ' . $id_warehouse; self::$currentIndex .= '&id_warehouse=' . $id_warehouse; } // sets the current warehouse $this->tpl_list_vars['current_warehouse'] = $this->getCurrentWarehouseId(); // sets the list of warehouses $warehouses = Warehouse::getWarehouses(true); array_unshift($warehouses, array('id_warehouse' => -1, 'name' => $this->l('All Warehouses'))); $this->tpl_list_vars['list_warehouses'] = $warehouses; // sets toolbar $this->initToolbar(); // renders list $list = parent::renderList(); // if export requested if (Tools::isSubmit('csv')) { if (count($this->_list) > 0) { $this->renderCSV(); die; } else { $this->displayWarning($this->l('There is nothing to export as a CSV.')); } } return $list; }
public function renderContent($setting) { $t = array('product_id' => 0, 'image_height' => '320', 'image_width' => 300); $setting = array_merge($t, $setting); $id_lang = (int) $this->lang_id; $id_product = $setting['product_id']; $sql = 'SELECT p.*, product_shop.*, stock.`out_of_stock` out_of_stock, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`, p.`upc`, MAX(image_shop.`id_image`) id_image, il.`legend`, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') ' . Product::sqlStock('p', 0) . ' WHERE p.id_product = ' . (int) $id_product . ' GROUP BY product_shop.id_product'; $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); if (!$row) { return false; } if (isset($row['id_product_attribute']) && $row['id_product_attribute']) { $row['id_product_attribute'] = $row['id_product_attribute']; } $p = Product::getProductProperties($id_lang, $row); $setting['product'] = $p; $output = array('type' => 'product', 'data' => $setting); return $output; }
public static function getSubCategories($id_lang, $active = true, $id_category = 2, $p = 0, $n = 6) { $sql_groups_where = ''; $sql_groups_join = ''; if (Group::isFeatureActive()) { $sql_groups_join = 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = c.`id_category`)'; $groups = FrontController::getCurrentCustomerGroups(); $sql_groups_where = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . pSQL(implode(',', $groups)) . ')' : '=' . (int) Group::getCurrent()->id); } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description FROM `' . _DB_PREFIX_ . 'category` c ' . Shop::addSqlAssociation('category', 'c') . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = ' . (int) $id_lang . ' ' . Shop::addSqlRestrictionOnLang('cl') . ') ' . $sql_groups_join . ' WHERE `id_parent` = ' . (int) $id_category . ' ' . ($active ? 'AND `active` = 1' : '') . ' ' . $sql_groups_where . ' GROUP BY c.`id_category` ORDER BY `level_depth` ASC, category_shop.`position` ASC LIMIT ' . (int) $p . ', ' . (int) $n); foreach ($result as &$row) { $row['id_image'] = Tools::file_exists_cache(_PS_CAT_IMG_DIR_ . $row['id_category'] . '.jpg') ? (int) $row['id_category'] : Language::getIsoById($id_lang) . '-default'; $row['legend'] = 'no picture'; } return $result; }
public function renderList() { $this->displayInformation(' <b>' . $this->l('How do I create a new carrier?') . '</b> <br /> <ul> <li>' . $this->l('Click "Add New."') . '<br /></li> <li>' . $this->l('Fill in the fields and click "Save."') . '</li> <li>' . $this->l('You need to set a price range -- or weight range -- for which the new carrier will be available.') . ' ' . $this->l('Under the "Shipping" menu, click either "Price ranges" or "Weight ranges.".') . ' </li> <li>' . $this->l('Click "Add New."') . '</li> <li>' . $this->l('Select the name of the carrier before defining the price or weight range.') . ' ' . $this->l('For example, the carrier can be made available for a weight range between 0 and 5lbs. Another carrier can have a range between 5 and 10lbs.') . ' </li> <li>' . $this->l('When you\'re done, click "Save."') . '</li> <li>' . $this->l('Click on the "Shipping" menu.') . '</li> <li>' . $this->l('You need to set the fees that will be applied for this carrier.') . ' ' . $this->l('At the bottom on the page -- in the "Fees" section -- select the name of the carrier.') . ' </li> <li>' . $this->l('For each zone, enter a price and then click "Save."') . '</li> <li>' . $this->l('You\'re all set! The new carrier will now be displayed to customers.') . '</li> </ul>'); $this->_select = 'b.*'; $this->_join = 'LEFT JOIN `' . _DB_PREFIX_ . 'carrier_lang` b ON a.id_carrier = b.id_carrier' . Shop::addSqlRestrictionOnLang('b') . ' LEFT JOIN `' . _DB_PREFIX_ . 'carrier_tax_rules_group_shop` ctrgs ON (a.`id_carrier` = ctrgs.`id_carrier` AND ctrgs.id_shop=' . (int) $this->context->shop->id . ')'; $this->_where = 'AND b.id_lang = ' . $this->context->language->id; return parent::renderList(); }
/** * Load default routes group by languages */ protected function loadRoutes($id_shop = null) { $context = Context::getContext(); // Load custom routes from modules $modules_routes = Hook::exec('moduleRoutes', array('id_shop' => $id_shop), null, true, false); if (is_array($modules_routes) && count($modules_routes)) { foreach ($modules_routes as $module_route) { if (is_array($module_route) && count($module_route)) { foreach ($module_route as $route => $route_details) { if (array_key_exists('controller', $route_details) && array_key_exists('rule', $route_details) && array_key_exists('keywords', $route_details) && array_key_exists('params', $route_details)) { if (!isset($this->default_routes[$route])) { $this->default_routes[$route] = array(); $this->default_routes[$route] = array_merge($this->default_routes[$route], $route_details); } } } } } } // Set default routes //new edit by Ha!*!*y :: Select only active languages foreach (Language::getLanguages(TRUE) as $lang) { foreach ($this->default_routes as $id => $route) { $this->addRoute($id, $route['rule'], $route['controller'], $lang['id_lang'], $route['keywords'], isset($route['params']) ? $route['params'] : array(), $id_shop); } } if ($this->use_routes) { // Get iso lang $iso_lang = Tools::getValue('isolang'); $id_lang = $context->language->id; if (!empty($iso_lang)) { $id_lang = Language::getIdByIso($iso_lang); } // Load routes from meta table $sql = 'SELECT m.page, ml.url_rewrite, ml.id_lang FROM `' . _DB_PREFIX_ . 'meta` m LEFT JOIN `' . _DB_PREFIX_ . 'meta_lang` ml ON (m.id_meta = ml.id_meta' . Shop::addSqlRestrictionOnLang('ml', $id_shop) . ') ORDER BY LENGTH(ml.url_rewrite) DESC'; if ($results = Db::getInstance()->executeS($sql)) { foreach ($results as $row) { if ($row['url_rewrite']) { $this->addRoute($row['page'], $row['url_rewrite'], $row['page'], $row['id_lang'], array(), array(), $id_shop); } } } // Set default empty route if no empty route (that's weird I know) if (!$this->empty_route) { $this->empty_route = array('routeID' => 'index', 'rule' => '', 'controller' => 'index'); } // Load custom routes foreach ($this->default_routes as $route_id => $route_data) { if ($custom_route = Configuration::get('PS_ROUTE_' . $route_id, null, null, $id_shop)) { foreach (Language::getLanguages() as $lang) { $this->addRoute($route_id, $custom_route, $route_data['controller'], $lang['id_lang'], $route_data['keywords'], isset($route_data['params']) ? $route_data['params'] : array(), $id_shop); } } } } }
public static function getGroupReductions($id_group, $id_lang) { $lang = $id_lang . Shop::addSqlRestrictionOnLang('cl'); return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT gr.`id_group_reduction`, gr.`id_group`, gr.`id_category`, gr.`reduction`, cl.`name` AS category_name FROM `' . _DB_PREFIX_ . 'group_reduction` gr LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (cl.`id_category` = gr.`id_category` AND cl.`id_lang` = ' . (int) $lang . ') WHERE `id_group` = ' . (int) $id_group); }
/** * Get the user's journey * * @param integer $id_category Category ID * @param string $path Path end * @param boolean $linkOntheLastItem Put or not a link on the current category * @param string [optionnal] $categoryType defined what type of categories is used (products or cms) */ public static function getPath($id_category, $path = '', $link_on_the_item = false, $category_type = 'products', Context $context = null) { if (!$context) { $context = Context::getContext(); } $id_category = (int) $id_category; if ($id_category == 1) { return '<div itemscope itemtype="http://data-vocabulary.org/Breadcrumb"><span class="navigation_end">' . $path . '</span></div>'; } $pipe = Configuration::get('PS_NAVIGATION_PIPE'); if (empty($pipe)) { $pipe = '>'; } $full_path = ''; if ($category_type === 'products') { $interval = Category::getInterval($id_category); $id_root_category = $context->shop->getCategory(); $interval_root = Category::getInterval($id_root_category); if ($interval) { $sql = 'SELECT c.id_category, cl.name, cl.link_rewrite FROM ' . _DB_PREFIX_ . 'category c LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = c.id_category' . Shop::addSqlRestrictionOnLang('cl') . ') ' . Shop::addSqlAssociation('category', 'c') . ' WHERE c.nleft <= ' . $interval['nleft'] . ' AND c.nright >= ' . $interval['nright'] . ' AND c.nleft >= ' . $interval_root['nleft'] . ' AND c.nright <= ' . $interval_root['nright'] . ' AND cl.id_lang = ' . (int) $context->language->id . ' AND c.active = 1 AND c.level_depth > ' . (int) $interval_root['level_depth'] . ' ORDER BY c.level_depth ASC'; $categories = Db::getInstance()->executeS($sql); $n = 1; $n_categories = count($categories); foreach ($categories as $category) { $full_path .= '<div itemscope itemtype="http://data-vocabulary.org/Breadcrumb">' . ($n < $n_categories || $link_on_the_item ? '<a href="' . Tools::safeOutput($context->link->getCategoryLink((int) $category['id_category'], $category['link_rewrite'])) . '" title="' . htmlentities($category['name'], ENT_NOQUOTES, 'UTF-8') . '" itemprop="url">' : '') . '<span itemprop="title">' . htmlentities($category['name'], ENT_NOQUOTES, 'UTF-8') . '</span>' . ($n < $n_categories || $link_on_the_item ? '</a>' : '') . '</div>' . ($n++ != $n_categories || !empty($path) ? '<span class="navigation-pipe">' . $pipe . '</span>' : ''); } return $full_path . $path; } } else { if ($category_type === 'CMS') { $category = new CMSCategory($id_category, $context->language->id); if (!Validate::isLoadedObject($category)) { die(Tools::displayError()); } $category_link = $context->link->getCMSCategoryLink($category); if ($path != $category->name) { $full_path .= '<div itemscope itemtype="http://data-vocabulary.org/Breadcrumb"><a href="' . Tools::safeOutput($category_link) . '" itemprop="url"><span itemprop="title">' . htmlentities($category->name, ENT_NOQUOTES, 'UTF-8') . '</span></a><span class="navigation-pipe">' . $pipe . '</span>' . $path; } else { $full_path = ($link_on_the_item ? '<a href="' . Tools::safeOutput($category_link) . '" itemprop="url">' : '') . '<span itemprop="title">' . htmlentities($path, ENT_NOQUOTES, 'UTF-8') . '</span>' . ($link_on_the_item ? '</a>' : ''); } return Tools::getPath($category->id_parent, $full_path, $link_on_the_item, $category_type); } } }
/** * Get product width light information * * @param array $iIdProduct Product id * @return array Product */ public static function getProductLight($iIdProduct) { $sql = 'SELECT p.`id_product`, p.`reference`, pl.`name` FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('pl') . ' ) WHERE p.`id_product` = ' . $iIdProduct; return Db::getInstance()->getRow($sql); }
public function hookRightColumn($params) { $productsViewed = isset($params['cookie']->viewed) && !empty($params['cookie']->viewed) ? array_slice(array_reverse(explode(',', $params['cookie']->viewed)), 0, Configuration::get('PRODUCTS_VIEWED_NBR')) : array(); if (count($productsViewed)) { $defaultCover = Language::getIsoById($params['cookie']->id_lang) . '-default'; $productIds = implode(',', array_map('intval', $productsViewed)); $productsImages = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT MAX(image_shop.id_image) id_image, p.id_product, il.legend, product_shop.active, pl.name, pl.description_short, pl.link_rewrite, cl.link_rewrite AS category_rewrite FROM ' . _DB_PREFIX_ . 'product p ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = p.id_product)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN ' . _DB_PREFIX_ . 'image_lang il ON (il.id_image = image_shop.id_image AND il.id_lang = ' . (int) $params['cookie']->id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ') WHERE p.id_product IN (' . $productIds . ') AND pl.id_lang = ' . (int) $params['cookie']->id_lang . ' AND cl.id_lang = ' . (int) $params['cookie']->id_lang . ' GROUP BY product_shop.id_product'); $productsImagesArray = array(); foreach ($productsImages as $pi) { $productsImagesArray[$pi['id_product']] = $pi; } $productsViewedObj = array(); foreach ($productsViewed as $productViewed) { $obj = (object) 'Product'; if (!isset($productsImagesArray[$productViewed]) || !($obj->active = $productsImagesArray[$productViewed]['active'])) { continue; } else { $obj->id = (int) $productsImagesArray[$productViewed]['id_product']; $obj->id_image = (int) $productsImagesArray[$productViewed]['id_image']; $obj->cover = (int) $productsImagesArray[$productViewed]['id_product'] . '-' . (int) $productsImagesArray[$productViewed]['id_image']; $obj->legend = $productsImagesArray[$productViewed]['legend']; $obj->name = $productsImagesArray[$productViewed]['name']; $obj->description_short = $productsImagesArray[$productViewed]['description_short']; $obj->link_rewrite = $productsImagesArray[$productViewed]['link_rewrite']; $obj->category_rewrite = $productsImagesArray[$productViewed]['category_rewrite']; // $obj is not a real product so it cannot be used as argument for getProductLink() $obj->product_link = $this->context->link->getProductLink($obj->id, $obj->link_rewrite, $obj->category_rewrite); if (!isset($obj->cover) || !$productsImagesArray[$productViewed]['id_image']) { $obj->cover = $defaultCover; $obj->legend = ''; } $productsViewedObj[] = $obj; } } if (!count($productsViewedObj)) { return; } $this->smarty->assign(array('productsViewedObj' => $productsViewedObj, 'mediumSize' => Image::getSize('medium'))); return $this->display(__FILE__, 'blockviewed.tpl'); } return; }
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()'); }
/** * Get Each parent category of this category until the root category * * @param integer $id_lang Language ID * @return array Corresponding categories */ public function _getParentsCategories($id_current = NULL) { $context = Context::getContext()->cloneContext(); $context->shop = clone $context->shop; $id_lang = $context->language->id; $categories = null; if (count(Category::getCategoriesWithoutParent()) > 1 && Configuration::get('PS_MULTISHOP_FEATURE_ACTIVE') && count(Shop::getShops(true, null, true)) != 1) { $context->shop->id_category = Category::getTopCategory()->id; } elseif (!$context->shop->id) { $context->shop = new Shop(Configuration::get('PS_SHOP_DEFAULT')); } $id_shop = $context->shop->id; while (true) { $sql = ' SELECT c.*, cl.* FROM `' . _DB_PREFIX_ . 'category` c LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')'; if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP) { $sql .= ' LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ')'; } $sql .= ' WHERE c.`id_category` = ' . (int) $id_current; if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP) { $sql .= ' AND cs.`id_shop` = ' . (int) $context->shop->id; } $root_category = Category::getRootCategory(); if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP && (!Tools::isSubmit('id_category') || (int) Tools::getValue('id_category') == (int) $root_category->id || (int) $root_category->id == (int) $context->shop->id_category)) { $sql .= ' AND c.`id_parent` != 0'; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if (isset($result[0])) { $categories[] = $result[0]; } else { if (!$categories) { $categories = array(); } } if (!$result || $result[0]['id_category'] == $context->shop->id_category) { return $categories; } $id_current = $result[0]['id_parent']; } }
public function renderList() { $this->_select = 'b.*'; $this->_join = 'INNER JOIN `' . _DB_PREFIX_ . 'carrier_lang` b ON a.id_carrier = b.id_carrier' . Shop::addSqlRestrictionOnLang('b') . ' AND b.id_lang = ' . (int) $this->context->language->id . ' LEFT JOIN `' . _DB_PREFIX_ . 'carrier_tax_rules_group_shop` ctrgs ON (a.`id_carrier` = ctrgs.`id_carrier` AND ctrgs.id_shop=' . (int) $this->context->shop->id . ')'; $this->_use_found_rows = false; // Removes the Recommended modules button unset($this->page_header_toolbar_btn['modules-list']); // test if need to show header alert. $sql = 'SELECT COUNT(1) FROM `' . _DB_PREFIX_ . 'carrier` WHERE deleted = 0 AND id_reference > 2'; $showHeaderAlert = Db::getInstance()->query($sql)->fetchColumn(0) == 0; // Assign them in two steps! Because renderModulesList needs it before to be called. $this->context->smarty->assign('panel_title', $this->trans('Use one of our recommended carrier modules', array(), 'Admin.Shipping.Feature')); $this->context->smarty->assign(array('showHeaderAlert' => $showHeaderAlert, 'modules_list' => $this->renderModulesList('back-office,AdminCarriers,new'))); return parent::renderList(); }
public function renderContent($setting) { $t = array('categ_root_category' => '1', 'categ_max_depth' => 4, 'categ_dhtml' => 1, 'categ_sort' => 0, 'categ_sort_way' => 0); $setting = array_merge($t, $setting); $context = Context::getContext(); $this->setLastVisitedCategory(); $max_depth = $setting['categ_max_depth']; $result_ids = array(); $result_parents = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite FROM `' . _DB_PREFIX_ . 'category` c INNER JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_lang` = ' . (int) $context->language->id . Shop::addSqlRestrictionOnLang('cl') . ') INNER JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cs.`id_category` = c.`id_category` AND cs.`id_shop` = ' . (int) $context->shop->id . ') WHERE (c.`active` = 1 OR c.`id_category` = ' . (int) Configuration::get('PS_HOME_CATEGORY') . ') AND c.`id_category` != ' . (int) Configuration::get('PS_ROOT_CATEGORY') . ((int) $max_depth != 0 ? ' AND `level_depth` <= ' . (int) $max_depth : '') . ' AND c.id_category IN ( SELECT id_category FROM `' . _DB_PREFIX_ . 'category_group` WHERE `id_group` IN (' . pSQL(implode(', ', Customer::getGroupsStatic((int) $context->customer->id))) . ') ) ORDER BY `level_depth` ASC, ' . ($setting['categ_sort'] ? 'cl.`name`' : 'cs.`position`') . ' ' . ($setting['categ_sort_way'] ? 'DESC' : 'ASC')); foreach ($result as &$row) { $result_parents[$row['id_parent']][] =& $row; $result_ids[$row['id_category']] =& $row; } $block_categ_tree = $this->getTree($result_parents, $result_ids, $max_depth, null); $setting['blockCategTree'] = $block_categ_tree; if ((Tools::getValue('id_product') || Tools::getValue('id_category')) && isset($context->cookie->last_visited_category) && $context->cookie->last_visited_category) { $category = new Category($context->cookie->last_visited_category, $context->language->id); if (Validate::isLoadedObject($category)) { $setting['currentCategory'] = $category; $setting['currentCategoryId'] = $category->id; } } $setting['isDhtml'] = $setting['categ_dhtml']; if (file_exists(_PS_THEME_DIR_ . 'modules/pspagebuilder/views/templates/front/widgets/sub/category-tree-branch.tpl')) { $setting['branche_tpl_path'] = _PS_THEME_DIR_ . 'modules/pspagebuilder/views/templates/front/widgets/sub/category-tree-branch.tpl'; } else { $setting['branche_tpl_path'] = _PS_MODULE_DIR_ . 'pspagebuilder/views/templates/front/widgets/sub/category-tree-branch.tpl'; } $output = array('type' => 'categoriesblock', 'data' => $setting); return $output; }
/** * Get data source: */ function getProducts($where = '', $limiStart = 0, $limit = 10, $order = '') { global $cookie, $link; $id_lang = intval($cookie->id_lang); $context = Context::getContext(); $id_country = (int) $context->country->id; $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new, (product_shop.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = ' . (int) $context->country->id . ' AND tr.`id_state` = 0 AND tr.`zipcode_from` = 0) LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`) LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` LEFT JOIN `' . _DB_PREFIX_ . 'product_sale` ps ON ps.`id_product` = p.`id_product` LEFT JOIN `' . _DB_PREFIX_ . 'feature_product` fp ON fp.`id_product` = p.`id_product` WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . ' AND ((product_attribute_shop.id_product_attribute IS NOT NULL OR pa.id_product_attribute IS NULL) OR (product_attribute_shop.id_product_attribute IS NULL AND pa.default_on=1)) AND product_shop.`active` = 1' . $where . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' ORDER BY ' . $order . ' LIMIT ' . $limiStart . ',' . $limit; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); return Product::getProductsProperties($id_lang, $result); }
public static function getFavoriteProducts($id_customer, $id_lang) { return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT DISTINCT p.`id_product`, fp.`id_shop`, pl.`description_short`, pl.`link_rewrite`, pl.`name`, i.`id_image`, CONCAT(p.`id_product`, \'-\', i.`id_image`) as image FROM `' . _DB_PREFIX_ . 'favorite_product` fp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (p.`id_product` = fp.`id_product`) ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' LEFT OUTER JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false) . ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') WHERE product_shop.`active` = 1 ' . ($id_customer ? ' AND fp.id_customer = ' . (int) $id_customer : '') . ' ' . Shop::addSqlRestriction(false, 'fp')); }
public static function getProductMetas($id_product, $id_lang, $page_name) { $context = Context::getContext(); $sql = 'SELECT `name`, `meta_title`, `meta_description`, `meta_keywords`, `description_short` FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = p.`id_product`' . Shop::addSqlRestrictionOnLang('pl') . ') ' . Shop::addSqlAssociation('product', 'p') . ' WHERE pl.id_lang = ' . (int) $id_lang . ' AND pl.id_product = ' . (int) $id_product . ' AND product_shop.active = 1'; if ($row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql)) { if ($context->language->id != 1) { $row['meta_title'] = Tools::rus2translit($row['meta_title']); $row['name'] = Tools::rus2translit($row['name']); } if (empty($row['meta_description'])) { $row['meta_description'] = strip_tags($row['description_short']); } return Meta::completeMetaTags($row, $row['name']); } return Meta::getHomeMetas($id_lang, $page_name); }
public static function searchTag($id_lang, $tag, $count = false, $pageNumber = 0, $pageSize = 10, $orderBy = false, $orderWay = false, $useCookie = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } if (!Module::isInstalled('agilemultipleseller') and !Module::isInstalled('agilesellerlistoptions')) { return parent::searchTag($id_lang, $expr, $pageNumber, $pageSize, $orderBy, $orderWay, $ajax, $useCookie, $context); } $agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p"); if ($useCookie) { $id_customer = (int) $context->customer->id; } else { $id_customer = 0; } if (!is_numeric($pageNumber) || !is_numeric($pageSize) || !Validate::isBool($count) || !Validate::isValidSearch($tag) || $orderBy && !$orderWay || $orderBy && !Validate::isOrderBy($orderBy) || $orderWay && !Validate::isOrderBy($orderWay)) { return false; } if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 10; } $id = Context::getContext()->shop->id; $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT'); if ($count) { $sql = 'SELECT COUNT(DISTINCT pt.`id_product`) nb FROM `' . _DB_PREFIX_ . 'product` p ' . $agile_sql_parts['joins'] . ' ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`) WHERE product_shop.`active` = 1 ' . $agile_sql_parts['wheres'] . ' AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . ' AND cg.`id_group` ' . (!$id_customer ? '= ' . (int) Configuration::get('PS_UNIDENTIFIED_GROUP') : 'IN ( SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $id_customer . ')') . ' AND t.`name` LIKE \'%' . pSQL($tag) . '%\''; return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } $sql = 'SELECT DISTINCT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name, 1 position, DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 new ' . $agile_sql_parts['selects'] . ' FROM `' . _DB_PREFIX_ . 'product` p ' . $agile_sql_parts['joins'] . ' INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . Shop::addSqlAssociation('product', 'p', false) . ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`) LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cg.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ') ' . Product::sqlStock('p', 0) . ' WHERE product_shop.`active` = 1 ' . $agile_sql_parts['wheres'] . ' AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . ' AND cg.`id_group` ' . (!$id_customer ? '= ' . (int) Configuration::get('PS_UNIDENTIFIED_GROUP') : 'IN ( SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $id_customer . ')') . ' AND t.`name` LIKE \'%' . pSQL($tag) . '%\' GROUP BY product_shop.id_product ORDER BY position DESC' . ($orderBy ? ', ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize; if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) { return false; } $results = Product::getProductsProperties((int) $id_lang, $result); $results = AgileSellerManager::prepareSellerRattingInfo($results); return $results; }
public static function getPacksTable($id_product, $id_lang, $full = false, $limit = null) { if (!Pack::isFeatureActive()) { return array(); } $packs = Db::getInstance()->getValue(' SELECT GROUP_CONCAT(a.`id_product_pack`) FROM `' . _DB_PREFIX_ . 'pack` a WHERE a.`id_product_item` = ' . (int) $id_product); if (!(int) $packs) { return array(); } $context = Context::getContext(); $sql = ' SELECT p.*, product_shop.*, pl.*, image_shop.`id_image` id_image, il.`legend`, IFNULL(product_attribute_shop.id_product_attribute, 0) id_product_attribute FROM `' . _DB_PREFIX_ . 'product` p NATURAL LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') WHERE pl.`id_lang` = ' . (int) $id_lang . ' ' . Shop::addSqlRestrictionOnLang('pl') . ' AND p.`id_product` IN (' . $packs . ') GROUP BY p.id_product'; if ($limit) { $sql .= ' LIMIT ' . (int) $limit; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if (!$full) { return $result; } $array_result = array(); foreach ($result as $row) { if (!Pack::isPacked($row['id_product'])) { $array_result[] = Product::getProductProperties($id_lang, $row); } } return $array_result; }
public function getProducts($refresh = false, $id_product = false, $id_country = null) { /* * EU-Legal * 1) correct calculation of prices -> Problem with inaccuracy at high number of items * 2) assign standard delivery times to products */ if (!$this->id) { return array(); } // Product cache must be strictly compared to NULL, or else an empty cart will add dozens of queries if ($this->_products !== null && !$refresh) { // Return product row with specified ID if it exists if (is_int($id_product)) { foreach ($this->_products as $product) { if ($product['id_product'] == $id_product) { return array($product); } } return array(); } return $this->_products; } // Build query $sql = new DbQuery(); // Build SELECT $sql->select('cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`delivery_now`, pl.`delivery_later`, product_shop.`id_category_default`, p.`id_supplier`, p.`id_manufacturer`, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`, product_shop.`available_for_order`, product_shop.`price`, product_shop.`active`, product_shop.`unity`, product_shop.`unit_price_ratio`, stock.`quantity` AS quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`weight`, p.`date_add`, p.`date_upd`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category, CONCAT(LPAD(cp.`id_product`, 10, 0), LPAD(IFNULL(cp.`id_product_attribute`, 0), 10, 0), IFNULL(cp.`id_address_delivery`, 0)) AS unique_id, cp.id_address_delivery, product_shop.advanced_stock_management, ps.product_supplier_reference supplier_reference, IFNULL(sp.`reduction_type`, 0) AS reduction_type'); // Build FROM $sql->from('cart_product', 'cp'); // Build JOIN $sql->leftJoin('product', 'p', 'p.`id_product` = cp.`id_product`'); $sql->innerJoin('product_shop', 'product_shop', '(product_shop.`id_shop` = cp.`id_shop` AND product_shop.`id_product` = p.`id_product`)'); $sql->leftJoin('product_lang', 'pl', ' p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('pl', 'cp.id_shop')); $sql->leftJoin('category_lang', 'cl', ' product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('cl', 'cp.id_shop')); $sql->leftJoin('product_supplier', 'ps', 'ps.`id_product` = cp.`id_product` AND ps.`id_product_attribute` = cp.`id_product_attribute` AND ps.`id_supplier` = p.`id_supplier`'); $sql->leftJoin('specific_price', 'sp', 'sp.`id_product` = cp.`id_product`'); // AND 'sp.`id_shop` = cp.`id_shop` // @todo test if everything is ok, then refactorise call of this method $sql->join(Product::sqlStock('cp', 'cp')); // Build WHERE clauses $sql->where('cp.`id_cart` = ' . (int) $this->id); if ($id_product) { $sql->where('cp.`id_product` = ' . (int) $id_product); } $sql->where('p.`id_product` IS NOT NULL'); // Build GROUP BY $sql->groupBy('unique_id'); // Build ORDER BY $sql->orderBy('cp.`date_add`, p.`id_product`, cp.`id_product_attribute` ASC'); if (Customization::isFeatureActive()) { $sql->select('cu.`id_customization`, cu.`quantity` AS customization_quantity'); $sql->leftJoin('customization', 'cu', 'p.`id_product` = cu.`id_product` AND cp.`id_product_attribute` = cu.`id_product_attribute` AND cu.`id_cart` = ' . (int) $this->id); } else { $sql->select('NULL AS customization_quantity, NULL AS id_customization'); } if (Combination::isFeatureActive()) { $sql->select(' product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr, IF (IFNULL(pa.`reference`, \'\') = \'\', p.`reference`, pa.`reference`) AS reference, (p.`weight`+ pa.`weight`) weight_attribute, IF (IFNULL(pa.`ean13`, \'\') = \'\', p.`ean13`, pa.`ean13`) AS ean13, IF (IFNULL(pa.`upc`, \'\') = \'\', p.`upc`, pa.`upc`) AS upc, pai.`id_image` as pai_id_image, il.`legend` as pai_legend, IFNULL(product_attribute_shop.`minimal_quantity`, product_shop.`minimal_quantity`) as minimal_quantity, IF(product_attribute_shop.wholesale_price > 0, product_attribute_shop.wholesale_price, product_shop.`wholesale_price`) wholesale_price '); $sql->leftJoin('product_attribute', 'pa', 'pa.`id_product_attribute` = cp.`id_product_attribute`'); $sql->leftJoin('product_attribute_shop', 'product_attribute_shop', '(product_attribute_shop.`id_shop` = cp.`id_shop` AND product_attribute_shop.`id_product_attribute` = pa.`id_product_attribute`)'); $sql->leftJoin('product_attribute_image', 'pai', 'pai.`id_product_attribute` = pa.`id_product_attribute`'); $sql->leftJoin('image_lang', 'il', 'il.`id_image` = pai.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang); } else { $sql->select('p.`reference` AS reference, p.`ean13`, p.`upc` AS upc, product_shop.`minimal_quantity` AS minimal_quantity, product_shop.`wholesale_price` wholesale_price'); } $result = Db::getInstance()->executeS($sql); // Reset the cache before the following return, or else an empty cart will add dozens of queries $products_ids = array(); $pa_ids = array(); if ($result) { foreach ($result as $row) { $products_ids[] = $row['id_product']; $pa_ids[] = $row['id_product_attribute']; } } // Thus you can avoid one query per product, because there will be only one query for all the products of the cart Product::cacheProductsFeatures($products_ids); Cart::cacheSomeAttributesLists($pa_ids, $this->id_lang); $this->_products = array(); if (empty($result)) { return array(); } $cart_shop_context = Context::getContext()->cloneContext(); foreach ($result as &$row) { if (isset($row['ecotax_attr']) && $row['ecotax_attr'] > 0) { $row['ecotax'] = (double) $row['ecotax_attr']; } $row['stock_quantity'] = (int) $row['quantity']; // for compatibility with 1.2 themes $row['quantity'] = (int) $row['cart_quantity']; if (isset($row['id_product_attribute']) && (int) $row['id_product_attribute'] && isset($row['weight_attribute'])) { $row['weight'] = (double) $row['weight_attribute']; } if (Configuration::get('PS_TAX_ADDRESS_TYPE') == 'id_address_invoice') { $address_id = (int) $this->id_address_invoice; } else { $address_id = (int) $row['id_address_delivery']; } if (!Address::addressExists($address_id)) { $address_id = null; } if ($cart_shop_context->shop->id != $row['id_shop']) { $cart_shop_context->shop = new Shop((int) $row['id_shop']); } $address = Address::initialize($address_id, true); $id_tax_rules_group = Product::getIdTaxRulesGroupByIdProduct((int) $row['id_product'], $cart_shop_context); $tax_calculator = TaxManagerFactory::getManager($address, $id_tax_rules_group)->getTaxCalculator(); $row['price'] = Product::getPriceStatic((int) $row['id_product'], false, isset($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 6, null, false, true, $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, $address_id, $specific_price_output, false, true, $cart_shop_context); switch (Configuration::get('PS_ROUND_TYPE')) { case Order::ROUND_TOTAL: $row['total'] = $row['price'] * (int) $row['cart_quantity']; $row['total_wt'] = $tax_calculator->addTaxes($row['price']) * (int) $row['cart_quantity']; break; case Order::ROUND_LINE: $row['total'] = Tools::ps_round($row['price'] * (int) $row['cart_quantity'], _PS_PRICE_COMPUTE_PRECISION_); $row['total_wt'] = Tools::ps_round($tax_calculator->addTaxes($row['price']) * (int) $row['cart_quantity'], _PS_PRICE_COMPUTE_PRECISION_); break; case Order::ROUND_ITEM: default: $row['total'] = Tools::ps_round($row['price'], _PS_PRICE_COMPUTE_PRECISION_) * (int) $row['cart_quantity']; $row['total_wt'] = Tools::ps_round($tax_calculator->addTaxes($row['price']), _PS_PRICE_COMPUTE_PRECISION_) * (int) $row['cart_quantity']; break; } $row['price_wt'] = $tax_calculator->addTaxes($row['price']); $row['description_short'] = Tools::nl2br($row['description_short']); if (!isset($row['pai_id_image']) || $row['pai_id_image'] == 0) { $cache_id = 'Cart::getProducts_' . '-pai_id_image-' . (int) $row['id_product'] . '-' . (int) $this->id_lang . '-' . (int) $row['id_shop']; if (!Cache::isStored($cache_id)) { $row2 = Db::getInstance()->getRow(' SELECT image_shop.`id_image` id_image, il.`legend` FROM `' . _DB_PREFIX_ . 'image` i JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (i.id_image = image_shop.id_image AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $row['id_shop'] . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang . ') WHERE i.`id_product` = ' . (int) $row['id_product'] . ' AND image_shop.`cover` = 1'); Cache::store($cache_id, $row2); } $row2 = Cache::retrieve($cache_id); if (!$row2) { $row2 = array('id_image' => false, 'legend' => false); } else { $row = array_merge($row, $row2); } } else { $row['id_image'] = $row['pai_id_image']; $row['legend'] = $row['pai_legend']; } $row['reduction_applies'] = $specific_price_output && (double) $specific_price_output['reduction']; $row['quantity_discount_applies'] = $specific_price_output && $row['cart_quantity'] >= (int) $specific_price_output['from_quantity']; $row['id_image'] = Product::defineProductImage($row, $this->id_lang); $row['allow_oosp'] = Product::isAvailableWhenOutOfStock($row['out_of_stock']); $row['features'] = Product::getFeaturesStatic((int) $row['id_product']); if (array_key_exists($row['id_product_attribute'] . '-' . $this->id_lang, self::$_attributesLists)) { $row = array_merge($row, self::$_attributesLists[$row['id_product_attribute'] . '-' . $this->id_lang]); } /* * EU-Legal * assign standard delivery times to products */ $row['delivery_now'] = !empty($row['delivery_now']) ? $row['delivery_now'] : Configuration::get('LEGAL_DELIVERY_NOW', $this->id_lang); $row['delivery_later'] = !empty($row['delivery_later']) ? $row['delivery_later'] : Configuration::get('LEGAL_DELIVERY_LATER', $this->id_lang); $row = Product::getTaxesInformations($row, $cart_shop_context); $this->_products[] = $row; } return $this->_products; }
/** * Get all carriers in a given language * * @param int $id_lang Language id * @param int $modules_filters Possible values: * - PS_CARRIERS_ONLY * - CARRIERS_MODULE * - CARRIERS_MODULE_NEED_RANGE * - PS_CARRIERS_AND_CARRIER_MODULES_NEED_RANGE * - ALL_CARRIERS * @param bool $active Returns only active carriers when true * * @return array Carriers */ public static function getCarriers($id_lang, $active = false, $delete = false, $id_zone = false, $ids_group = null, $modules_filters = self::PS_CARRIERS_ONLY) { // Filter by groups and no groups => return empty array if ($ids_group && (!is_array($ids_group) || !count($ids_group))) { return array(); } $sql = ' SELECT c.*, cl.delay FROM `' . _DB_PREFIX_ . 'carrier` c LEFT JOIN `' . _DB_PREFIX_ . 'carrier_lang` cl ON (c.`id_carrier` = cl.`id_carrier` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'carrier_zone` cz ON (cz.`id_carrier` = c.`id_carrier`)' . ($id_zone ? 'LEFT JOIN `' . _DB_PREFIX_ . 'zone` z ON (z.`id_zone` = ' . (int) $id_zone . ')' : '') . ' ' . Shop::addSqlAssociation('carrier', 'c') . ' WHERE c.`deleted` = ' . ($delete ? '1' : '0'); if ($active) { $sql .= ' AND c.`active` = 1 '; } if ($id_zone) { $sql .= ' AND cz.`id_zone` = ' . (int) $id_zone . ' AND z.`active` = 1 '; } if ($ids_group) { $sql .= ' AND EXISTS (SELECT 1 FROM ' . _DB_PREFIX_ . 'carrier_group WHERE ' . _DB_PREFIX_ . 'carrier_group.id_carrier = c.id_carrier AND id_group IN (' . implode(',', array_map('intval', $ids_group)) . ')) '; } switch ($modules_filters) { case 1: $sql .= ' AND c.is_module = 0 '; break; case 2: $sql .= ' AND c.is_module = 1 '; break; case 3: $sql .= ' AND c.is_module = 1 AND c.need_range = 1 '; break; case 4: $sql .= ' AND (c.is_module = 0 OR c.need_range = 1) '; break; } $sql .= ' GROUP BY c.`id_carrier` ORDER BY c.`position` ASC'; $cache_id = 'Carrier::getCarriers_' . md5($sql); if (!Cache::isStored($cache_id)) { $carriers = Db::getInstance()->executeS($sql); Cache::store($cache_id, $carriers); } else { $carriers = Cache::retrieve($cache_id); } foreach ($carriers as $key => $carrier) { if ($carrier['name'] == '0') { $carriers[$key]['name'] = Carrier::getCarrierNameFromShopName(); } } return $carriers; }
public function hookAdminStatsModules() { if (Tools::isSubmit('submitCategory')) { $this->context->cookie->statsstock_id_category = Tools::getValue('statsstock_id_category'); } $ru = AdminController::$currentIndex . '&module=' . $this->name . '&token=' . Tools::getValue('token'); $currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT')); $filter = (int) $this->context->cookie->statsstock_id_category ? ' AND p.id_product IN (SELECT cp.id_product FROM ' . _DB_PREFIX_ . 'category_product cp WHERE cp.id_category = ' . (int) $this->context->cookie->statsstock_id_category . ')' : ''; $sql = 'SELECT p.id_product, p.reference, pl.name, IFNULL(( SELECT AVG(product_attribute_shop.wholesale_price) FROM ' . _DB_PREFIX_ . 'product_attribute pa ' . Shop::addSqlAssociation('product_attribute', 'pa') . ' WHERE p.id_product = pa.id_product AND product_attribute_shop.wholesale_price != 0 ), product_shop.wholesale_price) as wholesale_price, IFNULL(stock.quantity, 0) as quantity FROM ' . _DB_PREFIX_ . 'product p ' . Shop::addSqlAssociation('product', 'p') . ' INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->context->language->id . Shop::addSqlRestrictionOnLang('pl') . ') ' . Product::sqlStock('p', 0) . ' WHERE 1 = 1 ' . $filter; $products = Db::getInstance()->executeS($sql); foreach ($products as $key => $p) { $products[$key]['stockvalue'] = $p['wholesale_price'] * $p['quantity']; } $this->html .= ' <script type="text/javascript">$(\'#calendar\').slideToggle();</script> <div class="panel-heading">' . $this->l('Evaluation of available quantities for sale') . '</div> <form action="' . Tools::safeOutput($ru) . '" method="post" class="form-horizontal"> <div class="row row-margin-bottom"> <label class="control-label col-lg-3">' . $this->l('Category') . '</label> <div class="col-lg-6"> <select name="statsstock_id_category" onchange="this.form.submit();"> <option value="0">- ' . $this->l('All') . ' -</option>'; foreach (Category::getSimpleCategories($this->context->language->id) as $category) { $this->html .= '<option value="' . (int) $category['id_category'] . '" ' . ($this->context->cookie->statsstock_id_category == $category['id_category'] ? 'selected="selected"' : '') . '>' . $category['name'] . ' </option>'; } $this->html .= ' </select> <input type="hidden" name="submitCategory" value="1" /> </div> </div> </form>'; if (!count($products)) { $this->html .= '<p>' . $this->l('Your catalog is empty.') . '</p>'; } else { $rollup = array('quantity' => 0, 'wholesale_price' => 0, 'stockvalue' => 0); $this->html .= ' <table class="table"> <thead> <tr> <th><span class="title_box active">' . $this->l('ID') . '</span></th> <th><span class="title_box active">' . $this->l('Ref.') . '</span></th> <th><span class="title_box active">' . $this->l('Item') . '</span></th> <th><span class="title_box active">' . $this->l('Available quantity for sale') . '</span></th> <th><span class="title_box active">' . $this->l('Price*') . '</span></th> <th><span class="title_box active">' . $this->l('Value') . '</span></th> </tr> </thead> <tbody>'; foreach ($products as $product) { $rollup['quantity'] += $product['quantity']; $rollup['wholesale_price'] += $product['wholesale_price']; $rollup['stockvalue'] += $product['stockvalue']; $this->html .= '<tr> <td>' . $product['id_product'] . '</td> <td>' . $product['reference'] . '</td> <td>' . $product['name'] . '</td> <td>' . $product['quantity'] . '</td> <td>' . Tools::displayPrice($product['wholesale_price'], $currency) . '</td> <td>' . Tools::displayPrice($product['stockvalue'], $currency) . '</td> </tr>'; } $this->html .= ' </tbody> <tfoot> <tr> <th colspan="3"></th> <th><span class="title_box active">' . $this->l('Total quantities') . '</span></th> <th><span class="title_box active">' . $this->l('Average price') . '</span></th> <th><span class="title_box active">' . $this->l('Total value') . '</span></th> </tr> <tr> <td colspan="3"></td> <td>' . $rollup['quantity'] . '</td> <td>' . Tools::displayPrice($rollup['wholesale_price'] / count($products), $currency) . '</td> <td>' . Tools::displayPrice($rollup['stockvalue'], $currency) . '</td> </tr> </tfoot> </table> <i class="icon-asterisk"></i> ' . $this->l('This section corresponds to the default wholesale price according to the default supplier for the product. An average price is used when the product has attributes.'); return $this->html; } }
public function getProductRuleDisplay($product_rule_group_id, $product_rule_id, $product_rule_type, $selected = array()) { Context::getContext()->smarty->assign(array('product_rule_group_id' => (int) $product_rule_group_id, 'product_rule_id' => (int) $product_rule_id, 'product_rule_type' => $product_rule_type)); switch ($product_rule_type) { case 'attributes': $attributes = array('selected' => array(), 'unselected' => array()); $results = Db::getInstance()->executeS(' SELECT CONCAT(agl.name, " - ", al.name) as name, a.id_attribute as id FROM ' . _DB_PREFIX_ . 'attribute_group_lang agl LEFT JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute_group = agl.id_attribute_group LEFT JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON (a.id_attribute = al.id_attribute AND al.id_lang = ' . (int) Context::getContext()->language->id . ') WHERE agl.id_lang = ' . (int) Context::getContext()->language->id . ' ORDER BY agl.name, al.name'); foreach ($results as $row) { $attributes[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row; } Context::getContext()->smarty->assign('product_rule_itemlist', $attributes); $choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch(); Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content); break; case 'products': $products = array('selected' => array(), 'unselected' => array()); $results = Db::getInstance()->executeS(' SELECT DISTINCT name, p.id_product as id FROM ' . _DB_PREFIX_ . 'product p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('pl') . ') ' . Shop::addSqlAssociation('product', 'p') . ' WHERE id_lang = ' . (int) Context::getContext()->language->id . ' ORDER BY name'); foreach ($results as $row) { $products[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row; } Context::getContext()->smarty->assign('product_rule_itemlist', $products); $choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch(); Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content); break; case 'manufacturers': $products = array('selected' => array(), 'unselected' => array()); $results = Db::getInstance()->executeS(' SELECT name, id_manufacturer as id FROM ' . _DB_PREFIX_ . 'manufacturer ORDER BY name'); foreach ($results as $row) { $products[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row; } Context::getContext()->smarty->assign('product_rule_itemlist', $products); $choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch(); Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content); break; case 'suppliers': $products = array('selected' => array(), 'unselected' => array()); $results = Db::getInstance()->executeS(' SELECT name, id_supplier as id FROM ' . _DB_PREFIX_ . 'supplier ORDER BY name'); foreach ($results as $row) { $products[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row; } Context::getContext()->smarty->assign('product_rule_itemlist', $products); $choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch(); Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content); break; case 'categories': $categories = array('selected' => array(), 'unselected' => array()); $results = Db::getInstance()->executeS(' SELECT DISTINCT name, c.id_category as id FROM ' . _DB_PREFIX_ . 'category c LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('cl') . ') ' . Shop::addSqlAssociation('category', 'c') . ' WHERE id_lang = ' . (int) Context::getContext()->language->id . ' ORDER BY name'); foreach ($results as $row) { $categories[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row; } Context::getContext()->smarty->assign('product_rule_itemlist', $categories); $choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch(); Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content); break; default: Context::getContext()->smarty->assign('product_rule_itemlist', array('selected' => array(), 'unselected' => array())); Context::getContext()->smarty->assign('product_rule_choose_content', ''); } return $this->createTemplate('controllers/cart_rules/product_rule.tpl')->fetch(); }
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 getBestSales($where, $id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null) { if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } $final_order_by = $order_by; $order_table = ''; if (is_null($order_by) || $order_by == 'position' || $order_by == 'price') { $order_by = 'sales'; } if ($order_by == 'date_add' || $order_by == 'date_upd') { $order_table = 'product_shop'; } if (is_null($order_way) || $order_by == 'sales') { $order_way = 'DESC'; } $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'WHERE cp.`id_product` IS NOT NULL AND ' . $where . ' cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'); } $interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20; // Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage $products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg INNER JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) ' . $sql_groups); $ids = array(); foreach ($products as $product) { if (Validate::isUnsignedId($product['id_product'])) { $ids[$product['id_product']] = 1; } } $ids = array_keys($ids); $ids = array_filter($ids); sort($ids); $ids = count($ids) > 0 ? implode(',', $ids) : 'NULL'; //Main query $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer, MAX(image_shop.`id_image`) id_image, il.`legend`, ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . $interval . ' DAY)) > 0 AS new FROM `' . _DB_PREFIX_ . 'product_sale` ps LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product` ' . Shop::addSqlAssociation('product', 'p', false) . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`) AND tr.`id_country` = ' . (int) Context::getContext()->country->id . ' AND tr.`id_state` = 0 LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`) ' . Product::sqlStock('p') . ' WHERE product_shop.`active` = 1 AND p.`visibility` != \'none\' AND p.`id_product` IN (' . $ids . ') GROUP BY product_shop.id_product ORDER BY ' . (!empty($order_table) ? '`' . pSQL($order_table) . '`.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way) . ' LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if ($final_order_by == 'price') { Tools::orderbyPrice($result, $order_way); } if (!$result) { return false; } return Product::getProductsProperties($id_lang, $result); }
private function getSimpleProducts($id_lang, $limit_from = false, $limit_to = 500) { if (version_compare(_PS_VERSION_, '1.5', '>')) { $context = Context::getContext(); if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } else { $front = true; } $sql = 'SELECT p.`id_product`, pl.`name` FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` ' . Shop::addSqlRestrictionOnLang('pl') . ') WHERE pl.`id_lang` = ' . (int) $id_lang . ' AND p.`active`= 1 AND p.`available_for_order`= 1 ' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' ORDER BY pl.`name`'; if ($limit_from !== false) { $sql .= ' LIMIT ' . (int) $limit_from . ', ' . (int) $limit_to; } } else { $sql = 'SELECT p.`id_product`, pl.`name` FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product`) WHERE pl.`id_lang` = ' . (int) $id_lang . ' AND p.`active`= 1 AND p.`available_for_order`= 1 ORDER BY pl.`name`'; } return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); }
public static function getCrossSells($id_product, $id_lang, $limit = 12) { if (!$id_product || !$id_lang) { return; } $front = true; if (!in_array(Context::getContext()->controller->controller_type, array('front', 'modulefront'))) { $front = false; } $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.id_order = o.id_order) WHERE o.valid = 1 AND od.product_id = ' . (int) $id_product); if (count($orders)) { $list = ''; foreach ($orders as $order) { $list .= (int) $order['id_order'] . ','; } $list = rtrim($list, ','); $order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT DISTINCT od.product_id, p.id_product, pl.name, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price, cl.link_rewrite category, p.ean13, p.out_of_stock, p.id_category_default ' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute' : '') . ' FROM ' . _DB_PREFIX_ . 'order_detail od LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id) ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) Context::getContext()->shop->id . ')' : '') . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = od.product_id' . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = od.product_id) WHERE od.id_order IN (' . $list . ') AND pl.id_lang = ' . (int) $id_lang . ' AND cl.id_lang = ' . (int) $id_lang . ' AND od.product_id != ' . (int) $id_product . ' AND i.cover = 1 AND product_shop.active = 1' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' ORDER BY RAND() LIMIT ' . (int) $limit . ' ', true, false); $tax_calc = Product::getTaxCalculationMethod(); if (is_array($order_products)) { foreach ($order_products as &$order_product) { $order_product['image'] = Context::getContext()->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('medium')); $order_product['link'] = Context::getContext()->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']); if ($tax_calc == 0 || $tax_calc == 2) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null); } elseif ($tax_calc == 1) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null); } } return Product::getProductsProperties($id_lang, $order_products); } } }
private function getCrossSales($id_product, $id_lang) { $sql = 'SELECT pl.name as pname, pl.id_product, SUM(od.product_quantity) as pqty, AVG(od.product_price) as pprice FROM `' . _DB_PREFIX_ . 'orders` o LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON o.id_order = od.id_order LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.id_product = od.product_id AND pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') WHERE o.id_customer IN ( SELECT o.id_customer FROM `' . _DB_PREFIX_ . 'orders` o LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON o.id_order = od.id_order WHERE o.date_add BETWEEN ' . $this->getDate() . ' AND o.valid = 1 AND od.product_id = ' . (int) $id_product . ' ) ' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . ' AND o.date_add BETWEEN ' . $this->getDate() . ' AND o.valid = 1 AND od.product_id != ' . (int) $id_product . ' GROUP BY od.product_id ORDER BY pqty DESC'; return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); }