public function __construct() { $this->bootstrap = true; $this->table = 'product'; $this->className = 'Product'; $this->lang = true; $this->explicitSelect = true; $this->bulk_actions = array('delete' => array('text' => $this->l('Delete selected'), 'icon' => 'icon-trash', 'confirm' => $this->l('Delete selected items?'))); if (!Tools::getValue('id_product')) { $this->multishop_context_group = false; } parent::__construct(); $this->imageType = 'jpg'; $this->_defaultOrderBy = 'position'; $this->max_file_size = (int) (Configuration::get('PS_LIMIT_UPLOAD_FILE_VALUE') * 1000000); $this->max_image_size = (int) Configuration::get('PS_PRODUCT_PICTURE_MAX_SIZE'); $this->allow_export = true; // @since 1.5 : translations for tabs $this->available_tabs_lang = array('Informations' => $this->l('Information'), 'Pack' => $this->l('Pack'), 'VirtualProduct' => $this->l('Virtual Product'), 'Prices' => $this->l('Prices'), 'Seo' => $this->l('SEO'), 'Images' => $this->l('Images'), 'Associations' => $this->l('Associations'), 'Shipping' => $this->l('Shipping'), 'Combinations' => $this->l('Combinations'), 'Features' => $this->l('Features'), 'Customization' => $this->l('Customization'), 'Attachments' => $this->l('Attachments'), 'Quantities' => $this->l('Quantities'), 'Suppliers' => $this->l('Suppliers'), 'Warehouses' => $this->l('Warehouses')); $this->available_tabs = array('Quantities' => 6, 'Warehouses' => 14); if ($this->context->shop->getContext() != Shop::CONTEXT_GROUP) { $this->available_tabs = array_merge($this->available_tabs, array('Informations' => 0, 'Pack' => 7, 'VirtualProduct' => 8, 'Prices' => 1, 'Seo' => 2, 'Associations' => 3, 'Images' => 9, 'Shipping' => 4, 'Combinations' => 5, 'Features' => 10, 'Customization' => 11, 'Attachments' => 12, 'Suppliers' => 13)); } // Sort the tabs that need to be preloaded by their priority number asort($this->available_tabs, SORT_NUMERIC); /* Adding tab if modules are hooked */ $modules_list = Hook::getHookModuleExecList('displayAdminProductsExtra'); if (is_array($modules_list) && count($modules_list) > 0) { foreach ($modules_list as $m) { $this->available_tabs['Module' . ucfirst($m['module'])] = 23; $this->available_tabs_lang['Module' . ucfirst($m['module'])] = Module::getModuleName($m['module']); } } if (Tools::getValue('reset_filter_category')) { $this->context->cookie->id_category_products_filter = false; } if (Shop::isFeatureActive() && $this->context->cookie->id_category_products_filter) { $category = new Category((int) $this->context->cookie->id_category_products_filter); if (!$category->inShop()) { $this->context->cookie->id_category_products_filter = false; Tools::redirectAdmin($this->context->link->getAdminLink('AdminProducts')); } } /* Join categories table */ if ($id_category = (int) Tools::getValue('productFilter_cl!name')) { $this->_category = new Category((int) $id_category); $_POST['productFilter_cl!name'] = $this->_category->name[$this->context->language->id]; } else { if ($id_category = (int) Tools::getValue('id_category')) { $this->id_current_category = $id_category; $this->context->cookie->id_category_products_filter = $id_category; } elseif ($id_category = $this->context->cookie->id_category_products_filter) { $this->id_current_category = $id_category; } if ($this->id_current_category) { $this->_category = new Category((int) $this->id_current_category); } else { $this->_category = new Category(); } } $join_category = false; if (Validate::isLoadedObject($this->_category) && empty($this->_filter)) { $join_category = true; } $this->_join .= ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = a.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 ' . StockAvailable::addSqlShopRestriction(null, null, 'sav') . ') '; $alias = 'sa'; $alias_image = 'image_shop'; $id_shop = Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP ? (int) $this->context->shop->id : 'a.id_shop_default'; $this->_join .= ' JOIN `' . _DB_PREFIX_ . 'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = ' . $id_shop . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (' . $alias . '.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = ' . $id_shop . ') LEFT JOIN `' . _DB_PREFIX_ . 'shop` shop ON (shop.id_shop = ' . $id_shop . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = ' . $id_shop . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_download` pd ON (pd.`id_product` = a.`id_product`)'; $this->_select .= 'shop.name as shopname, a.id_shop_default, '; $this->_select .= 'MAX(' . $alias_image . '.id_image) id_image, cl.name `name_category`, ' . $alias . '.`price`, 0 AS price_final, a.`is_virtual`, pd.`nb_downloadable`, sav.`quantity` as sav_quantity, ' . $alias . '.`active`, IF(sav.`quantity`<=0, 1, 0) badge_danger'; if ($join_category) { $this->_join .= ' INNER JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = a.`id_product` AND cp.`id_category` = ' . (int) $this->_category->id . ') '; $this->_select .= ' , cp.`position`, '; } $this->_group = 'GROUP BY ' . $alias . '.id_product'; $this->fields_list = array(); $this->fields_list['id_product'] = array('title' => $this->l('ID'), 'align' => 'center', 'class' => 'fixed-width-xs', 'type' => 'int'); $this->fields_list['image'] = array('title' => $this->l('Image'), 'align' => 'center', 'image' => 'p', 'orderby' => false, 'filter' => false, 'search' => false); $this->fields_list['name'] = array('title' => $this->l('Name'), 'filter_key' => 'b!name'); $this->fields_list['reference'] = array('title' => $this->l('Reference'), 'align' => 'left'); if (Shop::isFeatureActive() && Shop::getContext() != Shop::CONTEXT_SHOP) { $this->fields_list['shopname'] = array('title' => $this->l('Default shop'), 'filter_key' => 'shop!name'); } else { $this->fields_list['name_category'] = array('title' => $this->l('Category'), 'filter_key' => 'cl!name'); } $this->fields_list['price'] = array('title' => $this->l('Base price'), 'type' => 'price', 'align' => 'text-right', 'filter_key' => 'a!price'); $this->fields_list['price_final'] = array('title' => $this->l('Final price'), 'type' => 'price', 'align' => 'text-right', 'havingFilter' => true, 'orderby' => false, 'search' => false); if (Configuration::get('PS_STOCK_MANAGEMENT')) { $this->fields_list['sav_quantity'] = array('title' => $this->l('Quantity'), 'type' => 'int', 'align' => 'text-right', 'filter_key' => 'sav!quantity', 'orderby' => true, 'badge_danger' => true); } $this->fields_list['active'] = array('title' => $this->l('Status'), 'active' => 'status', 'filter_key' => $alias . '!active', 'align' => 'text-center', 'type' => 'bool', 'class' => 'fixed-width-sm', 'orderby' => false); if ($join_category && (int) $this->id_current_category) { $this->fields_list['position'] = array('title' => $this->l('Position'), 'filter_key' => 'cp!position', 'align' => 'center', 'position' => 'position'); } }
/** * For a given product, get its "out of stock" flag * * @param int $id_product * @param int $id_shop Optional : gets context if null @see Context::getContext() * @return bool : depends on stock @see $depends_on_stock */ public static function outOfStock($id_product, $id_shop = null) { if (!Validate::isUnsignedId($id_product)) { return false; } $query = new DbQuery(); $query->select('out_of_stock'); $query->from('stock_available'); $query->where('id_product = ' . (int) $id_product); $query->where('id_product_attribute = 0'); $query = StockAvailable::addSqlShopRestriction($query, $id_shop); return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); }
public function isColorUnavailable($id_attribute, $id_shop) { return Db::getInstance()->getValue(' SELECT sa.id_product_attribute FROM ' . _DB_PREFIX_ . 'stock_available sa WHERE id_product=' . (int) $this->id . ' AND quantity <= 0 ' . StockAvailable::addSqlShopRestriction(null, $id_shop, 'sa') . ' AND EXISTS ( SELECT 1 FROM ' . _DB_PREFIX_ . 'product_attribute pa JOIN ' . _DB_PREFIX_ . 'product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop=' . (int) $id_shop . ') JOIN ' . _DB_PREFIX_ . 'product_attribute_combination pac ON (pac.id_product_attribute AND product_attribute_shop.id_product_attribute) WHERE sa.id_product_attribute = pa.id_product_attribute AND pa.id_product=' . (int) $this->id . ' AND pac.id_attribute=' . (int) $id_attribute . ' )'); }
/** * Create JOIN query with 'stock_available' table * * @param string $productAlias Alias of product table * @param string|int $productAttribute If string : alias of PA table ; if int : value of PA ; if null : nothing about PA * @param bool $innerJoin LEFT JOIN or INNER JOIN * @param Shop $shop * @return string */ public static function sqlStock($product_alias, $product_attribute = 0, $inner_join = false, Shop $shop = null) { $id_shop = $shop !== null ? (int) $shop->id : null; $sql = ($inner_join ? ' INNER ' : ' LEFT ') . ' JOIN ' . _DB_PREFIX_ . 'stock_available stock ON (stock.id_product = ' . pSQL($product_alias) . '.id_product'; if (!is_null($product_attribute)) { if (!Combination::isFeatureActive()) { $sql .= ' AND stock.id_product_attribute = 0'; } elseif (is_numeric($product_attribute)) { $sql .= ' AND stock.id_product_attribute = ' . $product_attribute; } elseif (is_string($product_attribute)) { $sql .= ' AND stock.id_product_attribute = IFNULL(`' . bqSQL($product_attribute) . '`.id_product_attribute, 0)'; } } $sql .= StockAvailable::addSqlShopRestriction(null, $id_shop, 'stock') . ' )'; return $sql; }
public function getFilterBlock($selected_filters = array()) { global $cookie; static $cache = null; $context = Context::getContext(); $id_lang = $context->language->id; $currency = $context->currency; $id_shop = (int) $context->shop->id; $alias = 'product_shop'; if (is_array($cache)) { return $cache; } $home_category = Configuration::get('PS_HOME_CATEGORY'); $id_parent = (int) Tools::getValue('id_category', Tools::getValue('id_category_layered', $home_category)); if ($id_parent == $home_category) { return; } $parent = new Category((int) $id_parent, $id_lang); /* Get the filters for the current category */ $filters = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT type, id_value, filter_show_limit, filter_type FROM ' . _DB_PREFIX_ . 'layered_category WHERE id_category = ' . (int) $id_parent . ' AND id_shop = ' . $id_shop . ' GROUP BY `type`, id_value ORDER BY position ASC'); /* Create the table which contains all the id_product in a cat or a tree */ Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DROP TEMPORARY TABLE IF EXISTS ' . _DB_PREFIX_ . 'cat_restriction', false); Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('CREATE TEMPORARY TABLE ' . _DB_PREFIX_ . 'cat_restriction ENGINE=MEMORY SELECT DISTINCT cp.id_product, p.id_manufacturer, product_shop.condition, p.weight FROM ' . _DB_PREFIX_ . 'category_product cp INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category AND ' . (Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= ' . (int) $parent->nleft . ' AND c.nright <= ' . (int) $parent->nright : 'c.id_category = ' . (int) $id_parent) . ' AND c.active = 1) INNER JOIN ' . _DB_PREFIX_ . 'product_shop product_shop ON (product_shop.id_product = cp.id_product AND product_shop.id_shop = ' . (int) $context->shop->id . ') INNER JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product=cp.id_product) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog")', false); Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('ALTER TABLE ' . _DB_PREFIX_ . 'cat_restriction ADD PRIMARY KEY (id_product), ADD KEY `id_manufacturer` (`id_manufacturer`,`id_product`) USING BTREE, ADD KEY `condition` (`condition`,`id_product`) USING BTREE, ADD KEY `weight` (`weight`,`id_product`) USING BTREE', false); // Remove all empty selected filters foreach ($selected_filters as $key => $value) { switch ($key) { case 'price': case 'weight': if ($value[0] === '' && $value[1] === '') { unset($selected_filters[$key]); } break; default: if ($value == '') { unset($selected_filters[$key]); } break; } } $filter_blocks = array(); foreach ($filters as $filter) { $sql_query = array('select' => '', 'from' => '', 'join' => '', 'where' => '', 'group' => '', 'second_query' => ''); switch ($filter['type']) { case 'price': $sql_query['select'] = 'SELECT p.`id_product`, psi.price_min, psi.price_max '; // price slider is not filter dependent $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['join'] = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = ' . (int) $context->currency->id . ' AND psi.id_shop=' . (int) $context->shop->id . ')'; $sql_query['where'] = 'WHERE 1'; break; case 'weight': $sql_query['select'] = 'SELECT p.`id_product`, p.`weight` '; // price slider is not filter dependent $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['where'] = 'WHERE 1'; break; case 'condition': $sql_query['select'] = 'SELECT p.`id_product`, product_shop.`condition` '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['where'] = 'WHERE 1'; $sql_query['from'] .= Shop::addSqlAssociation('product', 'p'); break; case 'quantity': $sql_query['select'] = 'SELECT p.`id_product`, sa.`quantity`, sa.`out_of_stock` '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['join'] .= 'LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sa ON (sa.id_product = p.id_product AND sa.id_product_attribute=0 ' . StockAvailable::addSqlShopRestriction(null, null, 'sa') . ') '; $sql_query['where'] = 'WHERE 1'; break; case 'manufacturer': $sql_query['select'] = 'SELECT COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer, m.name '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p INNER JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer) '; $sql_query['where'] = 'WHERE 1'; $sql_query['group'] = ' GROUP BY p.id_manufacturer ORDER BY m.name'; if (!Configuration::get('PS_LAYERED_HIDE_0_VALUES')) { $sql_query['second_query'] = ' SELECT m.name, 0 nbr, m.id_manufacturer FROM ' . _DB_PREFIX_ . 'cat_restriction p JOIN INNER JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer) WHERE 1 GROUP BY p.id_manufacturer ORDER BY m.name'; } break; case 'id_attribute_group': // attribute group $sql_query['select'] = ' SELECT COUNT(DISTINCT lpa.id_product) nbr, lpa.id_attribute_group, a.color, al.name attribute_name, agl.public_name attribute_group_name , lpa.id_attribute, ag.is_color_group, liagl.url_name name_url_name, liagl.meta_title name_meta_title, lial.url_name value_url_name, lial.meta_title value_meta_title'; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'layered_product_attribute lpa INNER JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute = lpa.id_attribute INNER JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $id_lang . ' INNER JOIN ' . _DB_PREFIX_ . 'cat_restriction p ON p.id_product = lpa.id_product INNER JOIN ' . _DB_PREFIX_ . 'attribute_group ag ON ag.id_attribute_group = lpa.id_attribute_group INNER JOIN ' . _DB_PREFIX_ . 'attribute_group_lang agl ON agl.id_attribute_group = lpa.id_attribute_group AND agl.id_lang = ' . (int) $id_lang . ' LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_attribute_group_lang_value liagl ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = ' . (int) $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_attribute_lang_value lial ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = ' . (int) $id_lang . ') '; $sql_query['where'] = 'WHERE lpa.id_attribute_group = ' . (int) $filter['id_value']; $sql_query['where'] .= ' AND lpa.`id_shop` = ' . (int) $context->shop->id; $sql_query['group'] = ' GROUP BY lpa.id_attribute ORDER BY ag.`position` ASC, a.`position` ASC'; if (!Configuration::get('PS_LAYERED_HIDE_0_VALUES')) { $sql_query['second_query'] = ' SELECT 0 nbr, lpa.id_attribute_group, a.color, al.name attribute_name, agl.public_name attribute_group_name , lpa.id_attribute, ag.is_color_group, liagl.url_name name_url_name, liagl.meta_title name_meta_title, lial.url_name value_url_name, lial.meta_title value_meta_title FROM ' . _DB_PREFIX_ . 'layered_product_attribute lpa' . Shop::addSqlAssociation('product', 'lpa') . ' INNER JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute = lpa.id_attribute INNER JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $id_lang . ' INNER JOIN ' . _DB_PREFIX_ . 'product as p ON p.id_product = lpa.id_product INNER JOIN ' . _DB_PREFIX_ . 'attribute_group ag ON ag.id_attribute_group = lpa.id_attribute_group INNER JOIN ' . _DB_PREFIX_ . 'attribute_group_lang agl ON agl.id_attribute_group = lpa.id_attribute_group AND agl.id_lang = ' . (int) $id_lang . ' LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_attribute_group_lang_value liagl ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = ' . (int) $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_attribute_lang_value lial ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = ' . (int) $id_lang . ') WHERE lpa.id_attribute_group = ' . (int) $filter['id_value'] . ' AND lpa.`id_shop` = ' . (int) $context->shop->id . ' GROUP BY lpa.id_attribute ORDER BY id_attribute_group, id_attribute'; } break; case 'id_feature': $sql_query['select'] = 'SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value, COUNT(DISTINCT p.id_product) nbr, lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'feature_product fp INNER JOIN ' . _DB_PREFIX_ . 'cat_restriction p ON p.id_product = fp.id_product LEFT JOIN ' . _DB_PREFIX_ . 'feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = ' . $id_lang . ') INNER JOIN ' . _DB_PREFIX_ . 'feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0)) LEFT JOIN ' . _DB_PREFIX_ . 'feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = ' . $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_feature_lang_value lifl ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = ' . $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_feature_value_lang_value lifvl ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = ' . $id_lang . ') '; $sql_query['where'] = 'WHERE fp.id_feature = ' . (int) $filter['id_value']; $sql_query['group'] = 'GROUP BY fv.id_feature_value '; if (!Configuration::get('PS_LAYERED_HIDE_0_VALUES')) { $sql_query['second_query'] = ' SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value, 0 nbr, lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title FROM ' . _DB_PREFIX_ . 'feature_product fp' . Shop::addSqlAssociation('product', 'fp') . ' INNER JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = fp.id_product) LEFT JOIN ' . _DB_PREFIX_ . 'feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = ' . (int) $id_lang . ') INNER JOIN ' . _DB_PREFIX_ . 'feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0)) LEFT JOIN ' . _DB_PREFIX_ . 'feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = ' . (int) $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_feature_lang_value lifl ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = ' . (int) $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_feature_value_lang_value lifvl ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = ' . (int) $id_lang . ') WHERE fp.id_feature = ' . (int) $filter['id_value'] . ' GROUP BY fv.id_feature_value'; } break; case 'category': if (Group::isFeatureActive()) { $this->user_groups = $this->context->customer->isLogged() ? $this->context->customer->getGroups() : array(Configuration::get('PS_UNIDENTIFIED_GROUP')); } $depth = Configuration::get('PS_LAYERED_FILTER_CATEGORY_DEPTH'); if ($depth === false) { $depth = 1; } $sql_query['select'] = ' SELECT c.id_category, c.id_parent, cl.name, (SELECT count(DISTINCT p.id_product) # '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'category_product cp LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = cp.id_product) '; $sql_query['where'] = ' WHERE cp.id_category = c.id_category AND ' . $alias . '.active = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")'; $sql_query['group'] = ') count_products FROM ' . _DB_PREFIX_ . 'category c LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = c.id_category AND cl.`id_shop` = ' . (int) Context::getContext()->shop->id . ' and cl.id_lang = ' . (int) $id_lang . ') '; if (Group::isFeatureActive()) { $sql_query['group'] .= 'RIGHT JOIN ' . _DB_PREFIX_ . 'category_group cg ON (cg.id_category = c.id_category AND cg.`id_group` IN (' . implode(', ', $this->user_groups) . ')) '; } $sql_query['group'] .= 'WHERE c.nleft > ' . (int) $parent->nleft . ' AND c.nright < ' . (int) $parent->nright . ' ' . ($depth ? 'AND c.level_depth <= ' . ($parent->level_depth + (int) $depth) : '') . ' AND c.active = 1 GROUP BY c.id_category ORDER BY c.nleft, c.position'; $sql_query['from'] .= Shop::addSqlAssociation('product', 'p'); } foreach ($filters as $filter_tmp) { $method_name = 'get' . ucfirst($filter_tmp['type']) . 'FilterSubQuery'; if (method_exists('BlockLayered', $method_name) && ($filter['type'] != 'price' && $filter['type'] != 'weight' && $filter['type'] != $filter_tmp['type'] || $filter['type'] == $filter_tmp['type'])) { if ($filter['type'] == $filter_tmp['type'] && $filter['id_value'] == $filter_tmp['id_value']) { $sub_query_filter = self::$method_name(array(), true); } else { if (!is_null($filter_tmp['id_value'])) { $selected_filters_cleaned = $this->cleanFilterByIdValue(@$selected_filters[$filter_tmp['type']], $filter_tmp['id_value']); } else { $selected_filters_cleaned = @$selected_filters[$filter_tmp['type']]; } $sub_query_filter = self::$method_name($selected_filters_cleaned, $filter['type'] == $filter_tmp['type']); } foreach ($sub_query_filter as $key => $value) { $sql_query[$key] .= $value; } } } $products = false; if (!empty($sql_query['from'])) { $products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql_query['select'] . "\n" . $sql_query['from'] . "\n" . $sql_query['join'] . "\n" . $sql_query['where'] . "\n" . $sql_query['group']); } // price & weight have slidebar, so it's ok to not complete recompute the product list if (!empty($selected_filters['price']) && $filter['type'] != 'price' && $filter['type'] != 'weight') { $products = self::filterProductsByPrice(@$selected_filters['price'], $products); } if (!empty($sql_query['second_query'])) { $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql_query['second_query']); if ($res) { $products = array_merge($products, $res); } } switch ($filter['type']) { case 'price': if ($this->showPriceFilter()) { $price_array = array('type_lite' => 'price', 'type' => 'price', 'id_key' => 0, 'name' => $this->l('Price'), 'slider' => true, 'max' => '0', 'min' => null, 'values' => array('1' => 0), 'unit' => $currency->sign, 'format' => $currency->format, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); if (isset($products) && $products) { foreach ($products as $product) { if (is_null($price_array['min'])) { $price_array['min'] = $product['price_min']; $price_array['values'][0] = $product['price_min']; } else { if ($price_array['min'] > $product['price_min']) { $price_array['min'] = $product['price_min']; $price_array['values'][0] = $product['price_min']; } } if ($price_array['max'] < $product['price_max']) { $price_array['max'] = $product['price_max']; $price_array['values'][1] = $product['price_max']; } } } if ($price_array['max'] != $price_array['min'] && $price_array['min'] != null) { if ($filter['filter_type'] == 2) { $price_array['list_of_values'] = array(); $nbr_of_value = $filter['filter_show_limit']; if ($nbr_of_value < 2) { $nbr_of_value = 4; } $delta = ($price_array['max'] - $price_array['min']) / $nbr_of_value; $current_step = $price_array['min']; for ($i = 0; $i < $nbr_of_value; $i++) { $price_array['list_of_values'][] = array((int) ($price_array['min'] + $i * $delta), (int) ($price_array['min'] + ($i + 1) * $delta)); } } if (isset($selected_filters['price']) && isset($selected_filters['price'][0]) && isset($selected_filters['price'][1])) { $price_array['values'][0] = $selected_filters['price'][0]; $price_array['values'][1] = $selected_filters['price'][1]; } $filter_blocks[] = $price_array; } } break; case 'weight': $weight_array = array('type_lite' => 'weight', 'type' => 'weight', 'id_key' => 0, 'name' => $this->l('Weight'), 'slider' => true, 'max' => '0', 'min' => null, 'values' => array('1' => 0), 'unit' => Configuration::get('PS_WEIGHT_UNIT'), 'format' => 5, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); if (isset($products) && $products) { foreach ($products as $product) { if (is_null($weight_array['min'])) { $weight_array['min'] = $product['weight']; $weight_array['values'][0] = $product['weight']; } else { if ($weight_array['min'] > $product['weight']) { $weight_array['min'] = $product['weight']; $weight_array['values'][0] = $product['weight']; } } if ($weight_array['max'] < $product['weight']) { $weight_array['max'] = $product['weight']; $weight_array['values'][1] = $product['weight']; } } } if ($weight_array['max'] != $weight_array['min'] && $weight_array['min'] != null) { if (isset($selected_filters['weight']) && isset($selected_filters['weight'][0]) && isset($selected_filters['weight'][1])) { $weight_array['values'][0] = $selected_filters['weight'][0]; $weight_array['values'][1] = $selected_filters['weight'][1]; } $filter_blocks[] = $weight_array; } break; case 'condition': $condition_array = array('new' => array('name' => $this->l('New'), 'nbr' => 0), 'used' => array('name' => $this->l('Used'), 'nbr' => 0), 'refurbished' => array('name' => $this->l('Refurbished'), 'nbr' => 0)); if (isset($products) && $products) { foreach ($products as $product) { if (isset($selected_filters['condition']) && in_array($product['condition'], $selected_filters['condition'])) { $condition_array[$product['condition']]['checked'] = true; } } } foreach ($condition_array as $key => $condition) { if (isset($selected_filters['condition']) && in_array($key, $selected_filters['condition'])) { $condition_array[$key]['checked'] = true; } } if (isset($products) && $products) { foreach ($products as $product) { if (isset($condition_array[$product['condition']])) { $condition_array[$product['condition']]['nbr']++; } } } $filter_blocks[] = array('type_lite' => 'condition', 'type' => 'condition', 'id_key' => 0, 'name' => $this->l('Condition'), 'values' => $condition_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); break; case 'quantity': $quantity_array = array(0 => array('name' => $this->l('Not available'), 'nbr' => 0), 1 => array('name' => $this->l('In stock'), 'nbr' => 0)); foreach ($quantity_array as $key => $quantity) { if (isset($selected_filters['quantity']) && in_array($key, $selected_filters['quantity'])) { $quantity_array[$key]['checked'] = true; } } if (isset($products) && $products) { foreach ($products as $product) { //If oosp move all not available quantity to available quantity if ((int) $product['quantity'] > 0 || Product::isAvailableWhenOutOfStock($product['out_of_stock'])) { $quantity_array[1]['nbr']++; } else { $quantity_array[0]['nbr']++; } } } $filter_blocks[] = array('type_lite' => 'quantity', 'type' => 'quantity', 'id_key' => 0, 'name' => $this->l('Availability'), 'values' => $quantity_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); break; case 'manufacturer': if (isset($products) && $products) { $manufaturers_array = array(); foreach ($products as $manufacturer) { if (!isset($manufaturers_array[$manufacturer['id_manufacturer']])) { $manufaturers_array[$manufacturer['id_manufacturer']] = array('name' => $manufacturer['name'], 'nbr' => $manufacturer['nbr']); } if (isset($selected_filters['manufacturer']) && in_array((int) $manufacturer['id_manufacturer'], $selected_filters['manufacturer'])) { $manufaturers_array[$manufacturer['id_manufacturer']]['checked'] = true; } } $filter_blocks[] = array('type_lite' => 'manufacturer', 'type' => 'manufacturer', 'id_key' => 0, 'name' => $this->l('Manufacturer'), 'values' => $manufaturers_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } break; case 'id_attribute_group': $attributes_array = array(); if (isset($products) && $products) { foreach ($products as $attributes) { if (!isset($attributes_array[$attributes['id_attribute_group']])) { $attributes_array[$attributes['id_attribute_group']] = array('type_lite' => 'id_attribute_group', 'type' => 'id_attribute_group', 'id_key' => (int) $attributes['id_attribute_group'], 'name' => $attributes['attribute_group_name'], 'is_color_group' => (bool) $attributes['is_color_group'], 'values' => array(), 'url_name' => $attributes['name_url_name'], 'meta_title' => $attributes['name_meta_title'], 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } if (!isset($attributes_array[$attributes['id_attribute_group']]['values'][$attributes['id_attribute']])) { $attributes_array[$attributes['id_attribute_group']]['values'][$attributes['id_attribute']] = array('color' => $attributes['color'], 'name' => $attributes['attribute_name'], 'nbr' => (int) $attributes['nbr'], 'url_name' => $attributes['value_url_name'], 'meta_title' => $attributes['value_meta_title']); } if (isset($selected_filters['id_attribute_group'][$attributes['id_attribute']])) { $attributes_array[$attributes['id_attribute_group']]['values'][$attributes['id_attribute']]['checked'] = true; } } $filter_blocks = array_merge($filter_blocks, $attributes_array); } break; case 'id_feature': $feature_array = array(); if (isset($products) && $products) { foreach ($products as $feature) { if (!isset($feature_array[$feature['id_feature']])) { $feature_array[$feature['id_feature']] = array('type_lite' => 'id_feature', 'type' => 'id_feature', 'id_key' => (int) $feature['id_feature'], 'values' => array(), 'name' => $feature['feature_name'], 'url_name' => $feature['name_url_name'], 'meta_title' => $feature['name_meta_title'], 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } if (!isset($feature_array[$feature['id_feature']]['values'][$feature['id_feature_value']])) { $feature_array[$feature['id_feature']]['values'][$feature['id_feature_value']] = array('nbr' => (int) $feature['nbr'], 'name' => $feature['value'], 'url_name' => $feature['value_url_name'], 'meta_title' => $feature['value_meta_title']); } if (isset($selected_filters['id_feature'][$feature['id_feature_value']])) { $feature_array[$feature['id_feature']]['values'][$feature['id_feature_value']]['checked'] = true; } } //Natural sort foreach ($feature_array as $key => $value) { $temp = array(); foreach ($feature_array[$key]['values'] as $keyint => $valueint) { $temp[$keyint] = $valueint['name']; } natcasesort($temp); $temp2 = array(); foreach ($temp as $keytemp => $valuetemp) { $temp2[$keytemp] = $feature_array[$key]['values'][$keytemp]; } $feature_array[$key]['values'] = $temp2; } $filter_blocks = array_merge($filter_blocks, $feature_array); } break; case 'category': $tmp_array = array(); if (isset($products) && $products) { $categories_with_products_count = 0; foreach ($products as $category) { $tmp_array[$category['id_category']] = array('name' => $category['name'], 'nbr' => (int) $category['count_products']); if ((int) $category['count_products']) { $categories_with_products_count++; } if (isset($selected_filters['category']) && in_array($category['id_category'], $selected_filters['category'])) { $tmp_array[$category['id_category']]['checked'] = true; } } if ($categories_with_products_count || !Configuration::get('PS_LAYERED_HIDE_0_VALUES')) { $filter_blocks[] = array('type_lite' => 'category', 'type' => 'category', 'id_key' => 0, 'name' => $this->l('Categories'), 'values' => $tmp_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } } break; } } // All non indexable attribute and feature $non_indexable = array(); // Get all non indexable attribute groups foreach (Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT public_name FROM `' . _DB_PREFIX_ . 'attribute_group_lang` agl LEFT JOIN `' . _DB_PREFIX_ . 'layered_indexable_attribute_group` liag ON liag.id_attribute_group = agl.id_attribute_group WHERE indexable IS NULL OR indexable = 0 AND id_lang = ' . (int) $id_lang) as $attribute) { $non_indexable[] = Tools::link_rewrite($attribute['public_name']); } // Get all non indexable features foreach (Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT name FROM `' . _DB_PREFIX_ . 'feature_lang` fl LEFT JOIN `' . _DB_PREFIX_ . 'layered_indexable_feature` lif ON lif.id_feature = fl.id_feature WHERE indexable IS NULL OR indexable = 0 AND id_lang = ' . (int) $id_lang) as $attribute) { $non_indexable[] = Tools::link_rewrite($attribute['name']); } //generate SEO link $param_selected = ''; $param_product_url = ''; $option_checked_array = array(); $param_group_selected_array = array(); $title_values = array(); $meta_values = array(); //get filters checked by group foreach ($filter_blocks as $type_filter) { $filter_name = !empty($type_filter['url_name']) ? $type_filter['url_name'] : $type_filter['name']; $filter_meta = !empty($type_filter['meta_title']) ? $type_filter['meta_title'] : $type_filter['name']; $attr_key = $type_filter['type'] . '_' . $type_filter['id_key']; $param_group_selected = ''; $lower_filter = strtolower($type_filter['type']); $filter_name_rewritten = Tools::link_rewrite($filter_name); if (($lower_filter == 'price' || $lower_filter == 'weight') && (double) $type_filter['values'][0] > (double) $type_filter['min'] && (double) $type_filter['values'][1] > (double) $type_filter['max']) { $param_group_selected .= $this->getAnchor() . str_replace($this->getAnchor(), '_', $type_filter['values'][0]) . $this->getAnchor() . str_replace($this->getAnchor(), '_', $type_filter['values'][1]); $param_group_selected_array[$filter_name_rewritten][] = $filter_name_rewritten; if (!isset($title_values[$filter_meta])) { $title_values[$filter_meta] = array(); } $title_values[$filter_meta][] = $filter_meta; if (!isset($meta_values[$attr_key])) { $meta_values[$attr_key] = array('title' => $filter_meta, 'values' => array()); } $meta_values[$attr_key]['values'][] = $filter_meta; } else { foreach ($type_filter['values'] as $key => $value) { if (is_array($value) && array_key_exists('checked', $value)) { $value_name = !empty($value['url_name']) ? $value['url_name'] : $value['name']; $value_meta = !empty($value['meta_title']) ? $value['meta_title'] : $value['name']; $param_group_selected .= $this->getAnchor() . str_replace($this->getAnchor(), '_', Tools::link_rewrite($value_name)); $param_group_selected_array[$filter_name_rewritten][] = Tools::link_rewrite($value_name); if (!isset($title_values[$filter_meta])) { $title_values[$filter_meta] = array(); } $title_values[$filter_meta][] = $value_name; if (!isset($meta_values[$attr_key])) { $meta_values[$attr_key] = array('title' => $filter_meta, 'values' => array()); } $meta_values[$attr_key]['values'][] = $value_meta; } else { $param_group_selected_array[$filter_name_rewritten][] = array(); } } } if (!empty($param_group_selected)) { $param_selected .= '/' . str_replace($this->getAnchor(), '_', $filter_name_rewritten) . $param_group_selected; $option_checked_array[$filter_name_rewritten] = $param_group_selected; } // select only attribute and group attribute to display an unique product combination link if (!empty($param_group_selected) && $type_filter['type'] == 'id_attribute_group') { $param_product_url .= '/' . str_replace($this->getAnchor(), '_', $filter_name_rewritten) . $param_group_selected; } } if ($this->page > 1) { $param_selected .= '/page-' . $this->page; } $blacklist = array('weight', 'price'); if (!Configuration::get('PS_LAYERED_FILTER_INDEX_CDT')) { $blacklist[] = 'condition'; } if (!Configuration::get('PS_LAYERED_FILTER_INDEX_QTY')) { $blacklist[] = 'quantity'; } if (!Configuration::get('PS_LAYERED_FILTER_INDEX_MNF')) { $blacklist[] = 'manufacturer'; } if (!Configuration::get('PS_LAYERED_FILTER_INDEX_CAT')) { $blacklist[] = 'category'; } $global_nofollow = false; $categorie_link = Context::getContext()->link->getCategoryLink($parent, null, null); foreach ($filter_blocks as &$type_filter) { $filter_name = !empty($type_filter['url_name']) ? $type_filter['url_name'] : $type_filter['name']; $filter_link_rewrite = Tools::link_rewrite($filter_name); if (count($type_filter) > 0 && !isset($type_filter['slider'])) { foreach ($type_filter['values'] as $key => $values) { $nofollow = false; if (!empty($values['checked']) && in_array($type_filter['type'], $blacklist)) { $global_nofollow = true; } $option_checked_clone_array = $option_checked_array; // If not filters checked, add parameter $value_name = !empty($values['url_name']) ? $values['url_name'] : $values['name']; if (!in_array(Tools::link_rewrite($value_name), $param_group_selected_array[$filter_link_rewrite])) { // Update parameter filter checked before if (array_key_exists($filter_link_rewrite, $option_checked_array)) { $option_checked_clone_array[$filter_link_rewrite] = $option_checked_clone_array[$filter_link_rewrite] . $this->getAnchor() . str_replace($this->getAnchor(), '_', Tools::link_rewrite($value_name)); if (in_array($type_filter['type'], $blacklist)) { $nofollow = true; } } else { $option_checked_clone_array[$filter_link_rewrite] = $this->getAnchor() . str_replace($this->getAnchor(), '_', Tools::link_rewrite($value_name)); } } else { // Remove selected parameters $option_checked_clone_array[$filter_link_rewrite] = str_replace($this->getAnchor() . str_replace($this->getAnchor(), '_', Tools::link_rewrite($value_name)), '', $option_checked_clone_array[$filter_link_rewrite]); if (empty($option_checked_clone_array[$filter_link_rewrite])) { unset($option_checked_clone_array[$filter_link_rewrite]); } } $parameters = ''; ksort($option_checked_clone_array); // Order parameters foreach ($option_checked_clone_array as $key_group => $value_group) { $parameters .= '/' . str_replace($this->getAnchor(), '_', $key_group) . $value_group; } // Add nofollow if any blacklisted filters ins in parameters foreach ($filter_blocks as $filter) { $name = Tools::link_rewrite(!empty($filter['url_name']) ? $filter['url_name'] : $filter['name']); if (in_array($filter['type'], $blacklist) && strpos($parameters, $name . '-') !== false) { $nofollow = true; } } // Check if there is an non indexable attribute or feature in the url foreach ($non_indexable as $value) { if (strpos($parameters, '/' . $value) !== false) { $nofollow = true; } } $type_filter['values'][$key]['link'] = $categorie_link . '#' . ltrim($parameters, '/'); $type_filter['values'][$key]['rel'] = $nofollow ? 'nofollow' : ''; } } } $n_filters = 0; if (isset($selected_filters['price'])) { if ($price_array['min'] == $selected_filters['price'][0] && $price_array['max'] == $selected_filters['price'][1]) { unset($selected_filters['price']); } } if (isset($selected_filters['weight'])) { if ($weight_array['min'] == $selected_filters['weight'][0] && $weight_array['max'] == $selected_filters['weight'][1]) { unset($selected_filters['weight']); } } foreach ($selected_filters as $filters) { $n_filters += count($filters); } $cache = array('layered_show_qties' => (int) Configuration::get('PS_LAYERED_SHOW_QTIES'), 'id_category_layered' => (int) $id_parent, 'selected_filters' => $selected_filters, 'n_filters' => (int) $n_filters, 'nbr_filterBlocks' => count($filter_blocks), 'filters' => $filter_blocks, 'title_values' => $title_values, 'meta_values' => $meta_values, 'current_friendly_url' => $param_selected, 'param_product_url' => $param_product_url, 'no_follow' => !empty($param_selected) || $global_nofollow); return $cache; }
public function getWsStockAvailables() { return Db::getInstance()->executeS('SELECT `id_stock_available` id, `id_product_attribute` FROM `' . _DB_PREFIX_ . 'stock_available` WHERE `id_product`=' . $this->id . StockAvailable::addSqlShopRestriction()); }
public function getProductsByCategory($id_category, $count = false, $orderBy = '', $orderWay = '', $filter = '', $start = '', $pagination = '') { if ($orderBy && $orderWay) { $_GET['productsListOrderby'] = pSQL($orderBy); $_GET['productsListOrderway'] = pSQL($orderWay); } else { $orderBy = 'id_product'; $orderWay = 'ASC'; } $filtering = ''; $filter_array = array(); if ($filter) { $filtering = 'HAVING'; foreach ($filter as $row => $item) { if ($filtering == 'HAVING') { $filtering .= ' `' . pSQL($row) . '` LIKE \'%' . pSQL($item) . '%\''; } else { $filtering .= ' AND `' . pSQL($row) . '` LIKE \'%' . pSQL($item) . '%\''; } $this->context->cookie->__set('productsListFilter_' . pSQL($row), pSQL($item)); array_push($filter_array, pSQL($row)); } } if (!$count && (!$orderBy && !$orderWay)) { $keys_array = array('id_product', 'reference', 'price', 'category', 'quantity', 'active', 'name', 'final_price'); foreach ($keys_array as $key => $value) { if ($this->context->cookie->__isset('productsListFilter_' . $value) && !in_array($value, $filter_array)) { $this->context->cookie->__unset('productsListFilter_' . $value); } } } $id_shop = $this->context->shop->id; if (version_compare(_PS_VERSION_, '1.5', '<')) { $products = DB::getInstance()->executeS("\n\t\t\t\tSELECT\n\t\t\t\t\tp.`id_product` \t\tAS `id_product`,\n\t\t\t\t\tIFNULL((SELECT SUM(`at`.`quantity`) FROM `ps_product_attribute` `at` WHERE `at`.`id_product` = `cp`.`id_product`), p.`quantity`) AS `quantity`,\n\t\t\t\t\tp.`reference` \t\tAS `reference`,\n\t\t\t\t\tp.`price` \t\t\tAS `price`,\n\t\t\t\t\tcl.`name` \t\t\tAS `category`,\n\t\t\t\t\tp.`active` \t\t\tAS `active`,\n\t\t\t\t\tpl.`name` \t\t\tAS `name`,\n\t\t\t\t\tpl.`link_rewrite` \tAS `link_rewrite`,\n\t\t\t\t\ti.`id_image` \t\tAS `id_image`,\n\t\t\t\t\tp.`price` \t\t\tAS `final_price`\n\t\t\t\tFROM `" . _DB_PREFIX_ . "category_product` cp\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product` p ON (p.`id_product` = cp.`id_product`)\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "image` i ON (i.`id_product` = cp.`id_product` AND i.`cover` = '1')\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "category_lang` cl ON (cl.`id_category` = cp.`id_category` AND cl.`id_lang` = '" . (int) $this->context->language->id . "')\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product_lang` pl ON (pl.`id_product` = cp.`id_product` AND pl.`id_lang` = '" . (int) $this->context->language->id . "')\n\n\t\t\t\tWHERE cp.`id_category` = '" . (int) $id_category . "' " . pSQL($filtering) . "\n ORDER BY `" . pSQL($orderBy) . "` " . pSQL($orderWay) . ($start && $pagination ? " LIMIT " . (int) $start . ", " . (int) $pagination : '')); } else { $products = DB::getInstance()->executeS("\n\t\t\t\tSELECT\n\t\t\t\t\tp.`id_product` \t\tAS `id_product`,\n\t\t\t\t\tp.`reference` \t\tAS `reference`,\n\t\t\t\t\tp.`price` \t\t\tAS `price`,\n\t\t\t\t\tpsh.`id_shop` \tAS `id_shop`,\n\t\t\t\t\tcl.`name` \t\t\tAS `category`,\n\t\t\t\t\tsav.`quantity` \t\tAS `quantity`,\n\t\t\t\t\tp.`active` \t\t\tAS `active`,\n\t\t\t\t\tpl.`name` \t\t\tAS `name`,\n\t\t\t\t\tpl.`link_rewrite` \tAS `link_rewrite`,\n\t\t\t\t\ti.`id_image` \t\tAS `id_image`,\n\t\t\t\t\tp.`price` \t\t\tAS `final_price`\n\t\t\t\tFROM `" . _DB_PREFIX_ . "category_product` cp\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product` p ON (p.`id_product` = cp.`id_product`)\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product_shop` psh ON (psh.`id_product` = p.`id_product`)\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "image` i ON (i.`id_product` = cp.`id_product` AND i.`cover` = '1')\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "category_lang` cl ON (cl.`id_category` = cp.`id_category` AND cl.`id_lang` = '" . (int) $this->context->language->id . "' AND cl.`id_shop` = '" . (int) $this->context->shop->id . "')\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product_lang` pl ON (pl.`id_product` = cp.`id_product` AND pl.`id_lang` = '" . (int) $this->context->language->id . "' AND pl.`id_shop` = '" . (int) $this->context->shop->id . "')\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "stock_available` sav ON (sav.`id_product` = cp.`id_product` AND sav.`id_product_attribute` = 0 " . StockAvailable::addSqlShopRestriction(null, null, "sav") . ")\n\t\t\t\tWHERE cp.`id_category` = '" . (int) $id_category . "' " . pSQL($filtering) . " AND psh.`id_shop` = " . (int) $id_shop . "\n\t\t\t\tORDER BY `" . pSQL($orderBy) . "` " . pSQL($orderWay) . ($start && $pagination ? " LIMIT " . (int) $start . ", " . (int) $pagination : '')); } if (!$products) { $products = array(); } foreach ($products as $row => &$product) { $product['price'] = Tools::displayPrice($product['price']); $product['image'] = $this->getImage($product['id_product'], $product['id_image'], $product['link_rewrite']); $product['final_price'] = Tools::displayPrice(Product::getPriceStatic($product['id_product'], true, null, 2, null, false, true, 1, true)); } return $products; }
public function getFilterBlock($selected_filters = array(), $compute_range_filters = true) { global $cookie; // Remove all empty selected filters foreach ($selected_filters as $key => $value) { switch ($key) { case 'price': case 'weight': if ($value[0] === '' && $value[1] === '') { unset($selected_filters[$key]); } break; default: if ($value == '' || $value == array()) { unset($selected_filters[$key]); } break; } } static $latest_selected_filters = null; static $latest_cat_restriction = null; static $productCache = array(); $context = Context::getContext(); $id_lang = $context->language->id; $currency = $context->currency; $id_shop = (int) $context->shop->id; $alias = 'product_shop'; $id_parent = (int) Tools::getValue('id_category', Tools::getValue('id_category_layered', Configuration::get('PS_HOME_CATEGORY'))); $parent = new Category((int) $id_parent, $id_lang); /* Get the filters for the current category */ $filters = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT type, id_value, filter_show_limit, filter_type FROM ' . _DB_PREFIX_ . 'layered_category WHERE id_category = ' . (int) $id_parent . ' AND id_shop = ' . $id_shop . ' GROUP BY `type`, id_value ORDER BY position ASC'); /* Create the table which contains all the id_product in a cat or a tree */ $current_cat_restriction = 'parent_' . ($this->ps_layered_full_tree ? (int) $parent->nleft . '_' . (int) $parent->nright : (int) $id_parent . '_context_' . (int) $context->shop->id); if ($current_cat_restriction != $latest_cat_restriction) { Db::getInstance()->execute('DROP TEMPORARY TABLE IF EXISTS ' . _DB_PREFIX_ . 'cat_restriction', false); Db::getInstance()->execute('CREATE TEMPORARY TABLE ' . _DB_PREFIX_ . 'cat_restriction ENGINE=MEMORY SELECT DISTINCT cp.id_product, p.id_manufacturer, product_shop.condition, p.weight FROM ' . _DB_PREFIX_ . 'category c STRAIGHT_JOIN ' . _DB_PREFIX_ . 'category_product cp ON (c.id_category = cp.id_category AND ' . ($this->ps_layered_full_tree ? 'c.nleft >= ' . (int) $parent->nleft . ' AND c.nright <= ' . (int) $parent->nright : 'c.id_category = ' . (int) $id_parent) . ' AND c.active = 1) STRAIGHT_JOIN ' . _DB_PREFIX_ . 'product_shop product_shop ON (product_shop.id_product = cp.id_product AND product_shop.id_shop = ' . (int) $context->shop->id . ') STRAIGHT_JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product=cp.id_product) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog")', false); Db::getInstance()->execute('ALTER TABLE ' . _DB_PREFIX_ . 'cat_restriction ADD PRIMARY KEY (id_product), ADD KEY `id_manufacturer` (`id_manufacturer`,`id_product`) USING BTREE, ADD KEY `condition` (`condition`,`id_product`) USING BTREE, ADD KEY `weight` (`weight`,`id_product`) USING BTREE', false); $latest_cat_restriction = $current_cat_restriction; } $filter_blocks = array(); foreach ($filters as $filter) { $cacheKey = $filter['type'] . '-' . $filter['id_value']; if ($current_cat_restriction == $latest_cat_restriction && $latest_selected_filters == $selected_filters && isset($productCache[$cacheKey])) { $products = $productCache[$cacheKey]; } else { $sql_query = array('select' => '', 'from' => '', 'join' => '', 'where' => '', 'group' => ''); switch ($filter['type']) { case 'price': $sql_query['select'] = 'SELECT p.`id_product`, psi.price_min, psi.price_max '; // price slider is not filter dependent $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['join'] = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = ' . (int) $context->currency->id . ' AND psi.id_shop=' . (int) $context->shop->id . ')'; $sql_query['where'] = 'WHERE 1'; break; case 'weight': $sql_query['select'] = 'SELECT p.`id_product`, p.`weight` '; // price slider is not filter dependent $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['where'] = 'WHERE 1'; break; case 'condition': $sql_query['select'] = 'SELECT DISTINCT p.`id_product`, product_shop.`condition` '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['where'] = 'WHERE 1'; $sql_query['from'] .= Shop::addSqlAssociation('product', 'p'); break; case 'quantity': $sql_query['select'] = 'SELECT DISTINCT p.`id_product`, sa.`quantity`, sa.`out_of_stock` '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p'; $sql_query['join'] .= 'LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sa ON (sa.id_product = p.id_product AND sa.id_product_attribute=0 ' . StockAvailable::addSqlShopRestriction(null, null, 'sa') . ') '; $sql_query['where'] = 'WHERE 1'; break; case 'manufacturer': $sql_query['select'] = 'SELECT COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer, m.name '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'cat_restriction p INNER JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer) '; $sql_query['where'] = 'WHERE 1'; $sql_query['group'] = ' GROUP BY p.id_manufacturer ORDER BY m.name'; break; case 'id_attribute_group': // attribute group $sql_query['select'] = ' SELECT COUNT(DISTINCT lpa.id_product) nbr, lpa.id_attribute_group, a.color, al.name attribute_name, agl.public_name attribute_group_name , lpa.id_attribute, ag.is_color_group, liagl.url_name name_url_name, liagl.meta_title name_meta_title, lial.url_name value_url_name, lial.meta_title value_meta_title'; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'layered_product_attribute lpa INNER JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute = lpa.id_attribute INNER JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $id_lang . ' INNER JOIN ' . _DB_PREFIX_ . 'cat_restriction p ON p.id_product = lpa.id_product INNER JOIN ' . _DB_PREFIX_ . 'attribute_group ag ON ag.id_attribute_group = lpa.id_attribute_group INNER JOIN ' . _DB_PREFIX_ . 'attribute_group_lang agl ON agl.id_attribute_group = lpa.id_attribute_group AND agl.id_lang = ' . (int) $id_lang . ' LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_attribute_group_lang_value liagl ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = ' . (int) $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_attribute_lang_value lial ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = ' . (int) $id_lang . ') '; $sql_query['where'] = 'WHERE lpa.id_attribute_group = ' . (int) $filter['id_value']; $sql_query['where'] .= ' AND lpa.`id_shop` = ' . (int) $context->shop->id; $sql_query['group'] = ' GROUP BY lpa.id_attribute ORDER BY ag.`position` ASC, a.`position` ASC'; break; case 'id_feature': $id_lang = (int) $id_lang; $sql_query['select'] = 'SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value, COUNT(DISTINCT p.id_product) nbr, lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'feature_product fp INNER JOIN ' . _DB_PREFIX_ . 'cat_restriction p ON p.id_product = fp.id_product LEFT JOIN ' . _DB_PREFIX_ . 'feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = ' . $id_lang . ') INNER JOIN ' . _DB_PREFIX_ . 'feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0)) LEFT JOIN ' . _DB_PREFIX_ . 'feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = ' . $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_feature_lang_value lifl ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = ' . $id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'layered_indexable_feature_value_lang_value lifvl ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = ' . $id_lang . ') '; $sql_query['where'] = 'WHERE fp.id_feature = ' . (int) $filter['id_value']; $sql_query['group'] = 'GROUP BY fv.id_feature_value '; break; case 'category': if (Group::isFeatureActive()) { $this->user_groups = $this->context->customer->isLogged() ? $this->context->customer->getGroups() : array(Configuration::get('PS_UNIDENTIFIED_GROUP')); } $depth = Configuration::get('PS_LAYERED_FILTER_CATEGORY_DEPTH'); if ($depth === false) { $depth = 1; } $sql_query['select'] = ' SELECT c.id_category, c.id_parent, cl.name, (SELECT count(DISTINCT p.id_product) # '; $sql_query['from'] = ' FROM ' . _DB_PREFIX_ . 'category_product cp LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = cp.id_product) '; $sql_query['where'] = ' WHERE cp.id_category = c.id_category AND ' . $alias . '.active = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")'; $sql_query['group'] = ') count_products FROM ' . _DB_PREFIX_ . 'category c LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = c.id_category AND cl.`id_shop` = ' . (int) Context::getContext()->shop->id . ' and cl.id_lang = ' . (int) $id_lang . ') '; if (Group::isFeatureActive()) { $sql_query['group'] .= 'RIGHT JOIN ' . _DB_PREFIX_ . 'category_group cg ON (cg.id_category = c.id_category AND cg.`id_group` IN (' . implode(', ', $this->user_groups) . ')) '; } $sql_query['group'] .= 'WHERE c.nleft > ' . (int) $parent->nleft . ' AND c.nright < ' . (int) $parent->nright . ' ' . ($depth ? 'AND c.level_depth <= ' . ($parent->level_depth + (int) $depth) : '') . ' AND c.active = 1 GROUP BY c.id_category ORDER BY c.nleft, c.position'; $sql_query['from'] .= Shop::addSqlAssociation('product', 'p'); } /* * Loop over the filters again to add their restricting clauses to the sql * query being built. */ foreach ($filters as $filter_tmp) { $method_name = 'get' . ucfirst($filter_tmp['type']) . 'FilterSubQuery'; if (method_exists('Ps_Facetedsearch', $method_name)) { $no_subquery_necessary = $filter['type'] == $filter_tmp['type'] && $filter['id_value'] == $filter_tmp['id_value'] && ($filter['id_value'] || $filter['type'] === 'category' || $filter['type'] === 'condition' || $filter['type'] === 'quantity'); if ($no_subquery_necessary) { // Do not apply the same filter twice, i.e. when the primary filter // and the sub filter have the same type and same id_value. $sub_query_filter = array(); } else { // The next part is hard to follow, but here's what I think this // bit of code does: // It checks whether some filters in the current facet // (our current iterator, $filter_tmp), which // is part of the "template" for this category, were selected by the // user. // If so, it formats the current facet // in yet another strange way that is appropriate // for calling get***FilterSubQuery. // For instance, if inside $selected_filters I have: // [id_attribute_group] => Array // ( // [8] => 3_8 // [11] => 3_11 // ) // And $filter_tmp is: // Array // ( // [type] => id_attribute_group // [id_value] => 3 // [filter_show_limit] => 0 // [filter_type] => 0 // ) // Then $selected_filters_cleaned will be: // Array // ( // [0] => 8 // [1] => 11 // ) // The strategy employed is different whether we're dealing with // a facet with an "id_value" (this is the most complex case involving // the usual underscore-encoded values deserialization witchcraft) // such as "id_attribute_group" or with a facet without id_value. // In the latter case we're in luck because we can just use the // facet in $selected_filters directly. if (!is_null($filter_tmp['id_value'])) { $selected_filters_cleaned = $this->cleanFilterByIdValue(@$selected_filters[$filter_tmp['type']], $filter_tmp['id_value']); } else { $selected_filters_cleaned = @$selected_filters[$filter_tmp['type']]; } $ignore_join = $filter['type'] == $filter_tmp['type']; // Prepare the new bits of SQL query. // $ignore_join is set to true when the sub-facet // is of the same "type" as the main facet. This way // the method ($method_name) knows that the tables it needs are already // there and don't need to be joined again. $sub_query_filter = self::$method_name($selected_filters_cleaned, $ignore_join); } // Now we "merge" the query from the subfilter with the main query foreach ($sub_query_filter as $key => $value) { $sql_query[$key] .= $value; } } } $products = false; if (!empty($sql_query['from'])) { $assembled_sql_query = implode("\n", array($sql_query['select'], $sql_query['from'], $sql_query['join'], $sql_query['where'], $sql_query['group'])); $products = Db::getInstance()->executeS($assembled_sql_query, true, false); } // price & weight have slidebar, so it's ok to not complete recompute the product list if (!empty($selected_filters['price']) && $filter['type'] != 'price' && $filter['type'] != 'weight') { $products = self::filterProductsByPrice(@$selected_filters['price'], $products); } $productCache[$cacheKey] = $products; } switch ($filter['type']) { case 'price': if ($this->showPriceFilter()) { $price_array = array('type_lite' => 'price', 'type' => 'price', 'id_key' => 0, 'name' => $this->trans('Price', array(), 'Modules.FacetedSearch.Shop'), 'slider' => true, 'max' => '0', 'min' => null, 'unit' => $currency->sign, 'format' => $currency->format, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type'], 'list_of_values' => array()); if ($compute_range_filters && isset($products) && $products) { $rangeAggregator = new Ps_FacetedsearchRangeAggregator(); $aggregatedRanges = $rangeAggregator->aggregateRanges($products, 'price_min', 'price_max'); $price_array['min'] = $aggregatedRanges['min']; $price_array['max'] = $aggregatedRanges['max']; $mergedRanges = $rangeAggregator->mergeRanges($aggregatedRanges['ranges'], 10); $price_array['list_of_values'] = array_map(function (array $range) { return array(0 => $range['min'], 1 => $range['max'], 'nbr' => $range['count']); }, $mergedRanges); $price_array['values'] = array($price_array['min'], $price_array['max']); } $filter_blocks[] = $price_array; } break; case 'weight': $weight_array = array('type_lite' => 'weight', 'type' => 'weight', 'id_key' => 0, 'name' => $this->trans('Weight', array(), 'Modules.FacetedSearch.Shop'), 'slider' => true, 'max' => '0', 'min' => null, 'unit' => Configuration::get('PS_WEIGHT_UNIT'), 'format' => 5, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type'], 'list_of_values' => array()); if ($compute_range_filters && isset($products) && $products) { $rangeAggregator = new Ps_FacetedsearchRangeAggregator(); $aggregatedRanges = $rangeAggregator->getRangesFromList($products, 'weight'); $weight_array['min'] = $aggregatedRanges['min']; $weight_array['max'] = $aggregatedRanges['max']; $mergedRanges = $rangeAggregator->mergeRanges($aggregatedRanges['ranges'], 10); $weight_array['list_of_values'] = array_map(function (array $range) { return array(0 => $range['min'], 1 => $range['max'], 'nbr' => $range['count']); }, $mergedRanges); if (empty($weight_array['list_of_values']) && isset($selected_filters['weight'])) { // in case we don't have a list of values, // add the original one. // This may happen when e.g. all products // weigh 0. $weight_array['list_of_values'] = array(array(0 => $selected_filters['weight'][0], 1 => $selected_filters['weight'][1], 'nbr' => count($products))); } $weight_array['values'] = array($weight_array['min'], $weight_array['max']); } $filter_blocks[] = $weight_array; break; case 'condition': $condition_array = array('new' => array('name' => $this->trans('New', array(), 'Modules.FacetedSearch.Shop'), 'nbr' => 0), 'used' => array('name' => $this->trans('Used', array(), 'Modules.FacetedSearch.Shop'), 'nbr' => 0), 'refurbished' => array('name' => $this->trans('Refurbished', array(), 'Modules.FacetedSearch.Shop'), 'nbr' => 0)); if (isset($products) && $products) { foreach ($products as $product) { if (isset($selected_filters['condition']) && in_array($product['condition'], $selected_filters['condition'])) { $condition_array[$product['condition']]['checked'] = true; } } } foreach ($condition_array as $key => $condition) { if (isset($selected_filters['condition']) && in_array($key, $selected_filters['condition'])) { $condition_array[$key]['checked'] = true; } } if (isset($products) && $products) { foreach ($products as $product) { if (isset($condition_array[$product['condition']])) { ++$condition_array[$product['condition']]['nbr']; } } } $filter_blocks[] = array('type_lite' => 'condition', 'type' => 'condition', 'id_key' => 0, 'name' => $this->trans('Condition', array(), 'Modules.FacetedSearch.Shop'), 'values' => $condition_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); break; case 'quantity': $quantity_array = array(0 => array('name' => $this->trans('Not available', array(), 'Modules.FacetedSearch.Shop'), 'nbr' => 0), 1 => array('name' => $this->trans('In stock', array(), 'Modules.FacetedSearch.Shop'), 'nbr' => 0)); foreach ($quantity_array as $key => $quantity) { if (isset($selected_filters['quantity']) && in_array($key, $selected_filters['quantity'])) { $quantity_array[$key]['checked'] = true; } } if (isset($products) && $products) { foreach ($products as $product) { //If oosp move all not available quantity to available quantity if ((int) $product['quantity'] > 0 || Product::isAvailableWhenOutOfStock($product['out_of_stock'])) { ++$quantity_array[1]['nbr']; } else { ++$quantity_array[0]['nbr']; } } } $filter_blocks[] = array('type_lite' => 'quantity', 'type' => 'quantity', 'id_key' => 0, 'name' => $this->trans('Availability', array(), 'Modules.FacetedSearch.Shop'), 'values' => $quantity_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); break; case 'manufacturer': if (isset($products) && $products) { $manufaturers_array = array(); foreach ($products as $manufacturer) { if (!isset($manufaturers_array[$manufacturer['id_manufacturer']])) { $manufaturers_array[$manufacturer['id_manufacturer']] = array('name' => $manufacturer['name'], 'nbr' => $manufacturer['nbr']); } if (isset($selected_filters['manufacturer']) && in_array((int) $manufacturer['id_manufacturer'], $selected_filters['manufacturer'])) { $manufaturers_array[$manufacturer['id_manufacturer']]['checked'] = true; } } $filter_blocks[] = array('type_lite' => 'manufacturer', 'type' => 'manufacturer', 'id_key' => 0, 'name' => $this->trans('Brand', array(), 'Modules.FacetedSearch.Shop'), 'values' => $manufaturers_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } break; case 'id_attribute_group': $attributes_array = array(); if (isset($products) && $products) { foreach ($products as $attributes) { if (!isset($attributes_array[$attributes['id_attribute_group']])) { $attributes_array[$attributes['id_attribute_group']] = array('type_lite' => 'id_attribute_group', 'type' => 'id_attribute_group', 'id_key' => (int) $attributes['id_attribute_group'], 'name' => $attributes['attribute_group_name'], 'is_color_group' => (bool) $attributes['is_color_group'], 'values' => array(), 'url_name' => $attributes['name_url_name'], 'meta_title' => $attributes['name_meta_title'], 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } if (!isset($attributes_array[$attributes['id_attribute_group']]['values'][$attributes['id_attribute']])) { $attributes_array[$attributes['id_attribute_group']]['values'][$attributes['id_attribute']] = array('color' => $attributes['color'], 'name' => $attributes['attribute_name'], 'nbr' => (int) $attributes['nbr'], 'url_name' => $attributes['value_url_name'], 'meta_title' => $attributes['value_meta_title']); } if (isset($selected_filters['id_attribute_group'][$attributes['id_attribute']])) { $attributes_array[$attributes['id_attribute_group']]['values'][$attributes['id_attribute']]['checked'] = true; } } $filter_blocks = array_merge($filter_blocks, $attributes_array); } break; case 'id_feature': $feature_array = array(); if (isset($products) && $products) { foreach ($products as $feature) { if (!isset($feature_array[$feature['id_feature']])) { $feature_array[$feature['id_feature']] = array('type_lite' => 'id_feature', 'type' => 'id_feature', 'id_key' => (int) $feature['id_feature'], 'values' => array(), 'name' => $feature['feature_name'], 'url_name' => $feature['name_url_name'], 'meta_title' => $feature['name_meta_title'], 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } if (!isset($feature_array[$feature['id_feature']]['values'][$feature['id_feature_value']])) { $feature_array[$feature['id_feature']]['values'][$feature['id_feature_value']] = array('nbr' => (int) $feature['nbr'], 'name' => $feature['value'], 'url_name' => $feature['value_url_name'], 'meta_title' => $feature['value_meta_title']); } if (isset($selected_filters['id_feature'][$feature['id_feature_value']])) { $feature_array[$feature['id_feature']]['values'][$feature['id_feature_value']]['checked'] = true; } } //Natural sort foreach ($feature_array as $key => $value) { $temp = array(); foreach ($feature_array[$key]['values'] as $keyint => $valueint) { $temp[$keyint] = $valueint['name']; } natcasesort($temp); $temp2 = array(); foreach ($temp as $keytemp => $valuetemp) { $temp2[$keytemp] = $feature_array[$key]['values'][$keytemp]; } $feature_array[$key]['values'] = $temp2; } $filter_blocks = array_merge($filter_blocks, $feature_array); } break; case 'category': $tmp_array = array(); if (isset($products) && $products) { $categories_with_products_count = 0; foreach ($products as $category) { $tmp_array[$category['id_category']] = array('name' => $category['name'], 'nbr' => (int) $category['count_products']); if ((int) $category['count_products']) { ++$categories_with_products_count; } if (isset($selected_filters['category']) && in_array($category['id_category'], $selected_filters['category'])) { $tmp_array[$category['id_category']]['checked'] = true; } } if ($categories_with_products_count) { $filter_blocks[] = array('type_lite' => 'category', 'type' => 'category', 'id_key' => 0, 'name' => $this->trans('Categories', array(), 'Modules.FacetedSearch.Shop'), 'values' => $tmp_array, 'filter_show_limit' => $filter['filter_show_limit'], 'filter_type' => $filter['filter_type']); } } break; } } $latest_selected_filters = $selected_filters; return array('filters' => $filter_blocks); }
public function __construct() { $this->table = 'product'; $this->className = 'Product'; $this->lang = true; $this->bulk_actions = array('delete' => array('text' => $this->l('Delete selected'), 'confirm' => $this->l('Delete selected items?'))); if (!Tools::getValue('id_product')) { $this->multishop_context_group = false; } parent::__construct(); $this->imageType = 'jpg'; $this->_defaultOrderBy = 'position'; $this->max_file_size = (int) (Configuration::get('PS_LIMIT_UPLOAD_FILE_VALUE') * 1000000); $this->max_image_size = (int) Configuration::get('PS_PRODUCT_PICTURE_MAX_SIZE'); $this->fields_list = array(); $this->fields_list['id_product'] = array('title' => $this->l('ID'), 'align' => 'center', 'width' => 20); $this->fields_list['image'] = array('title' => $this->l('Photo'), 'align' => 'center', 'image' => 'p', 'width' => 70, 'orderby' => false, 'filter' => false, 'search' => false); $this->fields_list['name'] = array('title' => $this->l('Name'), 'filter_key' => 'b!name'); $this->fields_list['reference'] = array('title' => $this->l('Reference'), 'align' => 'left', 'width' => 80); if (Shop::isFeatureActive() && Shop::getContext() != Shop::CONTEXT_SHOP) { $this->fields_list['shopname'] = array('title' => $this->l('Default Shop'), 'width' => 230, 'filter_key' => 'shop!name'); } else { $this->fields_list['name_category'] = array('title' => $this->l('Category'), 'width' => 230, 'filter_key' => 'cl!name'); } $this->fields_list['price'] = array('title' => $this->l('Base price'), 'width' => 90, 'type' => 'price', 'align' => 'right', 'filter_key' => 'a!price'); $this->fields_list['price_final'] = array('title' => $this->l('Final price'), 'width' => 90, 'type' => 'price', 'align' => 'right', 'havingFilter' => true, 'orderby' => false); $this->fields_list['sav_quantity'] = array('title' => $this->l('Quantity'), 'width' => 90, 'align' => 'right', 'filter_key' => 'sav!quantity', 'orderby' => true, 'hint' => $this->l('This is the quantity available in the current shop/group')); $this->fields_list['active'] = array('title' => $this->l('Displayed'), 'width' => 70, 'active' => 'status', 'filter_key' => 'sa!active', 'align' => 'center', 'type' => 'bool', 'orderby' => false); if ((int) Tools::getValue('id_category')) { $this->fields_list['position'] = array('title' => $this->l('Position'), 'width' => 70, 'filter_key' => 'cp!position', 'align' => 'center', 'position' => 'position'); } // @since 1.5 : translations for tabs $this->available_tabs_lang = array('Informations' => $this->l('Information'), 'Pack' => $this->l('Pack'), 'VirtualProduct' => $this->l('Virtual Product'), 'Prices' => $this->l('Prices'), 'Seo' => $this->l('SEO'), 'Images' => $this->l('Images'), 'Associations' => $this->l('Associations'), 'Shipping' => $this->l('Shipping'), 'Combinations' => $this->l('Combinations'), 'Features' => $this->l('Features'), 'Customization' => $this->l('Customization'), 'Attachments' => $this->l('Attachments'), 'Quantities' => $this->l('Quantities'), 'Suppliers' => $this->l('Suppliers'), 'Warehouses' => $this->l('Warehouses')); $this->available_tabs = array('Quantities' => 6, 'Warehouses' => 14); if ($this->context->shop->getContext() != Shop::CONTEXT_GROUP) { $this->available_tabs = array_merge($this->available_tabs, array('Informations' => 0, 'Pack' => 7, 'VirtualProduct' => 8, 'Prices' => 1, 'Seo' => 2, 'Associations' => 3, 'Images' => 9, 'Shipping' => 4, 'Combinations' => 5, 'Features' => 10, 'Customization' => 11, 'Attachments' => 12, 'Suppliers' => 13)); } // Sort the tabs that need to be preloaded by their priority number asort($this->available_tabs, SORT_NUMERIC); /* Adding tab if modules are hooked */ $modules_list = Hook::getHookModuleExecList('displayAdminProductsExtra'); if (is_array($modules_list) && count($modules_list) > 0) { foreach ($modules_list as $m) { $this->available_tabs['Module' . ucfirst($m['module'])] = 23; $this->available_tabs_lang['Module' . ucfirst($m['module'])] = Module::getModuleName($m['module']); } } /* Join categories table */ if ($id_category = (int) Tools::getValue('productFilter_cl!name')) { $this->_category = new Category((int) $id_category); $_POST['productFilter_cl!name'] = $this->_category->name[$this->context->language->id]; } elseif ($id_category = Tools::getvalue('id_category')) { $this->_category = new Category((int) $id_category); } else { $this->_category = new Category(); } $join_category = false; if (Validate::isLoadedObject($this->_category) && empty($this->_filter)) { $join_category = true; } if (Shop::isFeatureActive()) { $alias = 'sa'; if (Shop::getContext() == Shop::CONTEXT_SHOP) { $this->_join .= ' JOIN `' . _DB_PREFIX_ . 'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = ' . (int) $this->context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (' . $alias . '.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = ' . (int) $this->context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'shop` shop ON (shop.id_shop = ' . (int) $this->context->shop->id . ') '; } else { $this->_join .= ' LEFT JOIN `' . _DB_PREFIX_ . 'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default) LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (' . $alias . '.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default) LEFT JOIN `' . _DB_PREFIX_ . 'shop` shop ON (shop.id_shop = a.id_shop_default) '; } $this->_select .= 'shop.name as shopname, '; } else { $alias = 'a'; $this->_join .= 'LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (' . $alias . '.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = 1)'; } $this->_join .= ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = a.`id_product` AND i.`cover` = 1) ' . ($join_category ? 'INNER JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = a.`id_product` AND cp.`id_category` = ' . (int) $this->_category->id . ')' : '') . ' LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (' . $alias . '.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = ' . (int) $this->context->country->id . ' AND tr.`id_state` = 0) LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`) LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 ' . StockAvailable::addSqlShopRestriction(null, null, 'sav') . ') '; $this->_select .= 'cl.name `name_category` ' . ($join_category ? ', cp.`position`' : '') . ', i.`id_image`, ' . $alias . '.`price`, (' . $alias . '.`price` * ((100 + (t.`rate`))/100)) AS price_final, sav.`quantity` as sav_quantity, ' . $alias . '.`active`'; }
/** * For a given id_product, synchronizes StockAvailable::quantity with Stock::usable_quantity * * @param int $product_id * @param int $order_shop_id * @return bool */ public static function synchronize($product_id, $order_shop_id = null) { if (!JeproshopTools::isUnsignedInt($product_id)) { return false; } $db = JFactory::getDBO(); // gets warehouse ids grouped by shops $warehouse_ids = JeproshopWarehouseModelWarehouse::getWarehousesGroupedByShops(); if ($order_shop_id !== null) { $order_warehouses = array(); $warehouses = JeproshopWarehouseModelWarehouse::getWarehouses(false, (int) $order_shop_id); foreach ($warehouses as $warehouse) { $order_warehouses[] = $warehouse->warehouse_id; } } // gets all product attributes ids $product_attribute_ids = array(); foreach (JeproshopProductModelProduct::getProductAttributesIds($product_id) as $product_attribute_id) { $product_attribute_ids[] = $product_attribute_id->product_attribute_id; } // Allow to order the product when out of stock? $out_of_stock = JeproshopStockAvailableModelStockAvailable::outOfStock($product_id); $manager = JeproshopStockManagerFactory::getManager(); // loops on $ids_warehouse to synchronize quantities foreach ($warehouse_ids as $shop_id => $warehouses) { // first, checks if the product depends on stock for the given shop $id_shop if (JeproshopStockAvailableModelStockAvailable::dependsOnStock($product_id, $shop_id)) { // init quantity $product_quantity = 0; // if it's a simple product if (empty($product_attribute_ids)) { $allowed_warehouse_for_product = JeproshopWarehouseModelWarehouse::getProductWarehouseList((int) $product_id, 0, (int) $shop_id); $allowed_warehouse_for_product_clean = array(); foreach ($allowed_warehouse_for_product as $warehouse) { $allowed_warehouse_for_product_clean[] = (int) $warehouse->warehouse_id; } $allowed_warehouse_for_product_clean = array_intersect($allowed_warehouse_for_product_clean, $warehouses); if ($order_shop_id != null && !count(array_intersect($allowed_warehouse_for_product_clean, $order_warehouses))) { continue; } $product_quantity = $manager->getProductRealQuantities($product_id, null, $allowed_warehouse_for_product_clean, true); /*Hook::exec('actionUpdateQuantity', array( 'id_product' => $id_product, 'id_product_attribute' => 0, 'quantity' => $product_quantity ) );*/ } else { // else this product has attributes, hence loops on $ids_product_attribute foreach ($product_attribute_ids as $product_attribute_id) { $allowed_warehouse_for_combination = JeproshopWarehouseModelWarehouse::getProductWarehouseList((int) $product_id, (int) $product_attribute_id, (int) $shop_id); $allowed_warehouse_for_combination_clean = array(); foreach ($allowed_warehouse_for_combination as $warehouse) { $allowed_warehouse_for_combination_clean[] = (int) $warehouse->warehouse_id; } $allowed_warehouse_for_combination_clean = array_intersect($allowed_warehouse_for_combination_clean, $warehouses); if ($order_shop_id != null && !count(array_intersect($allowed_warehouse_for_combination_clean, $order_warehouses))) { continue; } $quantity = $manager->getProductRealQuantities($product_id, $product_attribute_id, $allowed_warehouse_for_combination_clean, true); $query = new DbQuery(); $query->select('COUNT(*)'); $query->from('stock_available'); $query->where('id_product = ' . (int) $product_id . ' AND id_product_attribute = ' . (int) $product_attribute_id . StockAvailable::addSqlShopRestriction(null, $shop_id)); if ((int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query)) { $query = array('table' => 'stock_available', 'data' => array('quantity' => $quantity), 'where' => 'id_product = ' . (int) $product_id . ' AND id_product_attribute = ' . (int) $product_attribute_id . StockAvailable::addSqlShopRestriction(null, $shop_id)); Db::getInstance()->update($query['table'], $query['data'], $query['where']); } else { $query = array('table' => 'stock_available', 'data' => array('quantity' => $quantity, 'depends_on_stock' => 1, 'out_of_stock' => $out_of_stock, 'id_product' => (int) $id_product, 'id_product_attribute' => (int) $id_product_attribute)); StockAvailable::addSqlShopParams($query['data']); Db::getInstance()->insert($query['table'], $query['data']); } $product_quantity += $quantity; Hook::exec('actionUpdateQuantity', array('id_product' => $id_product, 'id_product_attribute' => $id_product_attribute, 'quantity' => $quantity)); } } // updates // if $id_product has attributes, it also updates the sum for all attributes $query = array('table' => 'stock_available', 'data' => array('quantity' => $product_quantity), 'where' => 'id_product = ' . (int) $id_product . ' AND id_product_attribute = 0' . StockAvailable::addSqlShopRestriction(null, $shop_id)); Db::getInstance()->update($query['table'], $query['data'], $query['where']); } } // In case there are no warehouses, removes product from StockAvailable if (count($warehouse_ids) == 0 && JeproshopStockAvailableModelStockAvailable::dependsOnStock((int) $product_id)) { $query = "UPDATE " . $db->quoteName('#__jeproshop_stock_available') . " SET " . $db->quoteName('quantity') . " = 0 "; $query .= " WHERE " . $db->quoteName('product_id') . " = " . (int) $product_id; $db->setQuery($query); $db->query(); } JeproshopCache::clean('jeproshop_stock_available_get_quantity_available_by_product_' . (int) $product_id . '_*'); }
public function postSave() { if ($this->id_product_attribute == 0) { return true; } $id_shop = Shop::getContext() != Shop::CONTEXT_GROUP && $this->id_shop ? $this->id_shop : null; if (!Configuration::get('PS_DISP_UNAVAILABLE_ATTR')) { $combination = new Combination((int) $this->id_product_attribute); if ($colors = $combination->getColorsAttributes()) { $product = new Product((int) $this->id_product); foreach ($colors as $color) { if ($product->isColorUnavailable((int) $color['id_attribute'], (int) $this->id_shop)) { Tools::clearColorListCache($product->id); break; } } } } $total_quantity = (double) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(quantity) + SUM(quantity_remainder) as quantity FROM ' . _DB_PREFIX_ . 'stock_available WHERE id_product = ' . (int) $this->id_product . ' AND id_product_attribute <> 0 ' . StockAvailable::addSqlShopRestriction(null, $id_shop)); $this->setQuantity($this->id_product, 0, $total_quantity, $id_shop); return true; }