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 initFieldsetFeaturesDetachables()
{
$this->fields_form[2]['form'] = array('legend' => array('title' => $this->l('Optional features'), 'icon' => 'icon-puzzle-piece'), 'description' => $this->l('Some features can be disabled in order to improve performance.'), 'input' => array(array('type' => 'hidden', 'name' => 'features_detachables_up'), array('type' => 'switch', 'label' => $this->l('Combinations'), 'name' => 'combination', 'is_bool' => true, 'disabled' => Combination::isCurrentlyUsed(), 'values' => array(array('id' => 'combination_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'combination_0', 'value' => 0, 'label' => $this->l('No'))), 'hint' => $this->l('Choose "No" to disable Product Combinations.'), 'desc' => Combination::isCurrentlyUsed() ? $this->l('You cannot set this parameter to No when combinations are already used by some of your products') : null), array('type' => 'switch', 'label' => $this->l('Features'), 'name' => 'feature', 'is_bool' => true, 'values' => array(array('id' => 'feature_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'feature_0', 'value' => 0, 'label' => $this->l('No'))), 'hint' => $this->l('Choose "No" to disable Product Features.')), array('type' => 'switch', 'label' => $this->l('Customer Groups'), 'name' => 'customer_group', 'is_bool' => true, 'disabled' => Group::isCurrentlyUsed(), 'values' => array(array('id' => 'group_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'group_0', 'value' => 0, 'label' => $this->l('No'))), 'hint' => $this->l('Choose "No" to disable Customer Groups.'))), 'submit' => array('title' => $this->l('Save')));
$this->fields_value['combination'] = Combination::isFeatureActive();
$this->fields_value['feature'] = Feature::isFeatureActive();
$this->fields_value['customer_group'] = Group::isFeatureActive();
}
public static function getCustomerGroups()
{
$groups = array();
if (version_compare(_PS_VERSION_, '1.5.0.0', '>=') && Group::isFeatureActive()) {
if (Validate::isLoadedObject(Context::getContext()->customer)) {
$groups = FrontController::getCurrentCustomerGroups();
} else {
$groups = array((int) Configuration::get('PS_UNIDENTIFIED_GROUP'));
}
} else {
if (version_compare(_PS_VERSION_, '1.4.0.2', '>=') && version_compare(_PS_VERSION_, '1.5.0.0', '<')) {
global $cookie;
$groups = Customer::getGroupsStatic((int) $cookie->id_customer);
} else {
if (version_compare(_PS_VERSION_, '1.4.0.2', '<') && version_compare(_PS_VERSION_, '1.2.5.0', '>=')) {
global $cookie;
$result = Db::getInstance()->ExecuteS('SELECT cg.`id_group` FROM ' . _DB_PREFIX_ . 'customer_group cg WHERE cg.`id_customer` = ' . (int) $cookie->id_customer);
if ($result && is_array($result)) {
foreach ($result as $group) {
$groups[] = (int) $group['id_group'];
}
}
}
}
}
sort($groups);
return $groups;
}
public function customGetNestedCategories($shop_id, $root_category = null, $id_lang = false, $active = false, $groups = null, $use_shop_restriction = true, $sql_filter = '', $sql_sort = '', $sql_limit = '')
{
if (isset($root_category) && !Validate::isInt($root_category)) {
die(Tools::displayError());
}
if (!Validate::isBool($active)) {
die(Tools::displayError());
}
if (isset($groups) && Group::isFeatureActive() && !is_array($groups)) {
$groups = (array) $groups;
}
$cache_id = 'Category::getNestedCategories_' . md5((int) $shop_id . (int) $root_category . (int) $id_lang . (int) $active . (int) $active . (isset($groups) && Group::isFeatureActive() ? implode('', $groups) : ''));
if (!Cache::isStored($cache_id)) {
$result = Db::getInstance()->executeS('
SELECT c.*, cl.*
FROM `' . _DB_PREFIX_ . 'category` c
INNER JOIN `' . _DB_PREFIX_ . 'category_shop` category_shop ON (category_shop.`id_category` = c.`id_category` AND category_shop.`id_shop` = "' . (int) $shop_id . '")
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_shop` = "' . (int) $shop_id . '")
WHERE 1 ' . $sql_filter . ' ' . ($id_lang ? 'AND cl.`id_lang` = ' . (int) $id_lang : '') . '
' . ($active ? ' AND (c.`active` = 1 OR c.`is_root_category` = 1)' : '') . '
' . (isset($groups) && Group::isFeatureActive() ? ' AND cg.`id_group` IN (' . implode(',', $groups) . ')' : '') . '
' . (!$id_lang || isset($groups) && Group::isFeatureActive() ? ' GROUP BY c.`id_category`' : '') . '
' . ($sql_sort != '' ? $sql_sort : ' ORDER BY c.`level_depth` ASC') . '
' . ($sql_sort == '' && $use_shop_restriction ? ', category_shop.`position` ASC' : '') . '
' . ($sql_limit != '' ? $sql_limit : ''));
$categories = array();
$buff = array();
foreach ($result as $row) {
$current =& $buff[$row['id_category']];
$current = $row;
if ($row['id_parent'] == 0) {
$categories[$row['id_category']] =& $current;
} else {
$buff[$row['id_parent']]['children'][$row['id_category']] =& $current;
}
}
Cache::store($cache_id, $categories);
}
return Cache::retrieve($cache_id);
}
public static function getProducts($id_supplier, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $active_category = true)
{
/*
* EU-Legal
* get standard shipping time from database pl.*
*/
$context = Context::getContext();
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
if ($p < 1) {
$p = 1;
}
if (empty($order_by) || $order_by == 'position') {
$order_by = 'name';
}
if (empty($order_way)) {
$order_way = 'ASC';
}
if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
die(Tools::displayError());
}
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'WHERE cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
/* Return only the number of products */
if ($get_total) {
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(DISTINCT ps.`id_product`)
FROM `' . _DB_PREFIX_ . 'product_supplier` ps
JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`)
' . Shop::addSqlAssociation('product', 'p') . '
WHERE ps.`id_supplier` = ' . (int) $id_supplier . '
AND ps.id_product_attribute = 0
' . ($active ? ' AND product_shop.`active` = 1' : '') . '
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_product` cp
' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)' : '') . '
' . ($active_category ? ' INNER JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1' : '') . '
' . $sql_groups . '
)');
}
$nb_days_new_product = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
if (strpos('.', $order_by) > 0) {
$order_by = explode('.', $order_by);
$order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`';
}
$alias = '';
if (in_array($order_by, array('price', 'date_add', 'date_upd'))) {
$alias = 'product_shop.';
} elseif ($order_by == 'id_product') {
$alias = 'p.';
} elseif ($order_by == 'manufacturer_name') {
$order_by = 'name';
$alias = 'm.';
}
/*
* EU-Legal
* get standard shipping time from database pl.*
*/
$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.`delivery_now`, pl.`delivery_later`,
MAX(image_shop.`id_image`) id_image,
il.`legend`,
s.`name` AS supplier_name,
DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . $nb_days_new_product . ' DAY)) > 0 AS new,
m.`name` AS manufacturer_name' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . '
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
JOIN `' . _DB_PREFIX_ . 'product_supplier` ps ON (ps.id_product = p.id_product
AND ps.id_product_attribute = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
ON (p.`id_product` = pa.`id_product`)
' . (Combination::isFeatureActive() ? Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') : '') . '
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_ . 'supplier` s ON s.`id_supplier` = p.`id_supplier`
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
' . Product::sqlStock('p', 0);
if (Group::isFeatureActive() || $active_category) {
$sql .= 'JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (p.id_product = cp.id_product)';
if (Group::isFeatureActive()) {
$sql .= 'JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category` AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . ')';
}
if ($active_category) {
$sql .= 'JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1';
}
}
$sql .= '
WHERE ps.`id_supplier` = ' . (int) $id_supplier . '
' . ($active ? ' AND product_shop.`active` = 1' : '') . '
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
GROUP BY product_shop.id_product
ORDER BY ' . $alias . pSQL($order_by) . ' ' . pSQL($order_way) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if (!$result) {
return false;
}
if ($order_by == 'price') {
Tools::orderbyPrice($result, $order_way);
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getNestedCategories($images, $root_category = null, $id_lang = false, $active = true, $groups = null, $use_shop_restriction = true, $sql_filter = '', $sql_sort = '', $sql_limit = '')
{
$protocol = !empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off' || $_SERVER['SERVER_PORT'] == 443 ? 'https://' : 'http://';
$url = Tools::htmlentitiesutf8($protocol . $_SERVER['HTTP_HOST'] . __PS_BASE_URI__);
$themeName = Context::getContext()->shop->getTheme();
$image_path = 'themes/' . $themeName . '/img/icontab/';
if (isset($root_category) && !Validate::isInt($root_category)) {
die(Tools::displayError());
}
if (!Validate::isBool($active)) {
die(Tools::displayError());
}
if (isset($groups) && Group::isFeatureActive() && !is_array($groups)) {
$groups = (array) $groups;
}
$cache_id = 'Category::getNestedCategories_' . md5((int) $root_category . (int) $id_lang . (int) $active . (int) $active . (isset($groups) && Group::isFeatureActive() ? implode('', $groups) : ''));
if (!Cache::isStored($cache_id)) {
$result = Db::getInstance()->executeS('
SELECT c.*, cl.*
FROM `' . _DB_PREFIX_ . 'category` c
' . ($use_shop_restriction ? Shop::addSqlAssociation('category', 'c') : '') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON c.`id_category` = cl.`id_category`' . Shop::addSqlRestrictionOnLang('cl') . '
' . (isset($groups) && Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON c.`id_category` = cg.`id_category`' : '') . '
' . (isset($root_category) ? 'RIGHT JOIN `' . _DB_PREFIX_ . 'category` c2 ON c2.`id_category` = ' . (int) $root_category . ' AND c.`nleft` >= c2.`nleft` AND c.`nright` <= c2.`nright`' : '') . '
WHERE 1 ' . $sql_filter . ' ' . ($id_lang ? 'AND `id_lang` = ' . (int) $id_lang : '') . '
' . ($active ? ' AND c.`active` = 1' : '') . '
' . (isset($groups) && Group::isFeatureActive() ? ' AND cg.`id_group` IN (' . implode(',', $groups) . ')' : '') . '
' . (!$id_lang || isset($groups) && Group::isFeatureActive() ? ' GROUP BY c.`id_category`' : '') . '
' . ($sql_sort != '' ? $sql_sort : ' ORDER BY c.`level_depth` ASC') . '
' . ($sql_sort == '' && $use_shop_restriction ? ', category_shop.`position` ASC' : '') . '
' . ($sql_limit != '' ? $sql_limit : ''));
$categories = array();
$buff = array();
if (!isset($root_category)) {
$root_category = 1;
}
foreach ($result as $row) {
//add image to a category
if (array_key_exists($row['id_category'], $images)) {
# validate module
$row['image'] = $url . $image_path . $images[$row['id_category']];
}
$row['id_image'] = $row['id_category'] && file_exists(_PS_CAT_IMG_DIR_ . (int) $row['id_category'] . '.jpg') ? (int) $row['id_category'] : false;
$current =& $buff[$row['id_category']];
$current = $row;
if ($row['id_category'] == $root_category) {
# validate module
$categories[$row['id_category']] =& $current;
} else {
# validate module
$buff[$row['id_parent']]['children'][$row['id_category']] =& $current;
}
}
Cache::store($cache_id, $categories);
}
return Cache::retrieve($cache_id);
}
public function getFilterBlock($selected_filters = array())
{
global $cookie;
static $cache = null;
$id_lang = Context::getContext()->language->id;
$currency = Context::getContext()->currency;
$id_shop = (int) Context::getContext()->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 * 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');
// 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']) {
// conditions + quantities + weight + price
case 'price':
case 'weight':
case 'condition':
case 'quantity':
$sql_query['select'] = 'SELECT p.`id_product`, product_shop.`condition`, p.`id_manufacturer`, sa.`quantity`, p.`weight` ';
$sql_query['from'] = '
FROM ' . _DB_PREFIX_ . 'product p ';
$sql_query['join'] = '
INNER JOIN ' . _DB_PREFIX_ . 'category_product cp ON (cp.id_product = p.id_product)
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) ';
$sql_query['join'] .= 'LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sa
ON (sa.id_product = p.id_product AND sa.id_shop = ' . (int) $this->context->shop->id . ') ';
$sql_query['where'] = 'WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ';
$sql_query['group'] = ' GROUP BY p.id_product ';
break;
case 'manufacturer':
$sql_query['select'] = 'SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer ';
$sql_query['from'] = '
FROM `' . _DB_PREFIX_ . 'category_product` cp
INNER JOIN `' . _DB_PREFIX_ . 'category` c ON (c.id_category = cp.id_category)
INNER JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = cp.id_product)
INNER JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer) ';
$sql_query['where'] = 'WHERE
' . (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
AND ' . $alias . '.active = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")';
$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_ . 'category_product` cp' . Shop::addSqlAssociation('product', 'cp') . '
INNER JOIN `' . _DB_PREFIX_ . 'category` c ON (c.id_category = cp.id_category)
INNER JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = cp.id_product)
INNER JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE ' . (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
AND ' . $alias . '.active = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")
GROUP BY p.id_manufacturer ORDER BY m.name';
}
break;
case 'id_attribute_group':
// attribute group
$sql_query['select'] = '
SELECT COUNT(DISTINCT p.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_ . '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 . ') ';
$sql_query['where'] = 'WHERE a.id_attribute_group = ' . (int) $filter['id_value'];
$sql_query['where'] .= ' AND lpa.`id_shop` = ' . (int) Context::getContext()->shop->id;
$sql_query['where'] .= ' AND ' . $alias . '.active = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")
AND p.id_product IN (
SELECT id_product
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)
) ';
$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 ' . $alias . '.active = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")
AND a.id_attribute_group = ' . (int) $filter['id_value'] . '
AND lpa.`id_shop` = ' . (int) Context::getContext()->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_ . '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 = ' . $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 ' . $alias . '.`active` = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")
AND fp.id_feature = ' . (int) $filter['id_value'] . '
AND p.id_product IN (
SELECT id_product
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)) ';
$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 ' . $alias . '.`active` = 1 AND ' . $alias . '.`visibility` IN ("both", "catalog")
AND 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';
}
foreach ($filters as $filter_tmp) {
$method_name = 'get' . ucfirst($filter_tmp['type']) . 'FilterSubQuery';
if (method_exists('BlockLayered', $method_name) && (!in_array($filter['type'], array('price', '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'])) {
$sql_query['from'] .= Shop::addSqlAssociation('product', 'p');
$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']);
}
foreach ($filters as $filter_tmp) {
$method_name = 'filterProductsBy' . ucfirst($filter_tmp['type']);
if (method_exists('BlockLayered', $method_name) && (!in_array($filter['type'], array('price', 'weight')) && $filter['type'] != $filter_tmp['type'] || $filter['type'] == $filter_tmp['type'])) {
if ($filter['type'] == $filter_tmp['type']) {
$products = self::$method_name(array(), $products);
} else {
$products = self::$method_name(@$selected_filters[$filter_tmp['type']], $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(StockAvailable::outOfStock($product['id_product']))) {
$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 = '';
if (in_array(strtolower($type_filter['type']), array('price', '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[Tools::link_rewrite($filter_name)][] = Tools::link_rewrite($filter_name);
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[Tools::link_rewrite($filter_name)][] = 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[Tools::link_rewrite($filter_name)][] = array();
}
}
}
if (!empty($param_group_selected)) {
$param_selected .= '/' . str_replace($this->getAnchor(), '_', Tools::link_rewrite($filter_name)) . $param_group_selected;
$option_checked_array[Tools::link_rewrite($filter_name)] = $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(), '_', Tools::link_rewrite($filter_name)) . $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;
foreach ($filter_blocks as &$type_filter) {
$filter_name = !empty($type_filter['url_name']) ? $type_filter['url_name'] : $type_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[Tools::link_rewrite($filter_name)])) {
// Update parameter filter checked before
if (array_key_exists(Tools::link_rewrite($filter_name), $option_checked_array)) {
$option_checked_clone_array[Tools::link_rewrite($filter_name)] = $option_checked_clone_array[Tools::link_rewrite($filter_name)] . $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[Tools::link_rewrite($filter_name)] = $this->getAnchor() . str_replace($this->getAnchor(), '_', Tools::link_rewrite($value_name));
}
} else {
// Remove selected parameters
$option_checked_clone_array[Tools::link_rewrite($filter_name)] = str_replace($this->getAnchor() . str_replace($this->getAnchor(), '_', Tools::link_rewrite($value_name)), '', $option_checked_clone_array[Tools::link_rewrite($filter_name)]);
if (empty($option_checked_clone_array[Tools::link_rewrite($filter_name)])) {
unset($option_checked_clone_array[Tools::link_rewrite($filter_name)]);
}
}
$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'] = Context::getContext()->link->getCategoryLink($parent, null, null) . '#' . 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 static function getMainTags($id_lang, $nb = 10)
{
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = '
AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)
WHERE cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
)';
}
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT t.name, COUNT(pt.id_tag) AS times
FROM `' . _DB_PREFIX_ . 'product_tag` pt
LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (t.id_tag = pt.id_tag)
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (p.id_product = pt.id_product)
' . Shop::addSqlAssociation('product', 'p') . '
WHERE t.`id_lang` = ' . (int) $id_lang . '
AND product_shop.`active` = 1
' . $sql_groups . '
GROUP BY t.id_tag
ORDER BY times DESC
LIMIT ' . (int) $nb);
}
/**
* @param array $products_id an array of product ids
* @return array
*/
protected function getOrderProducts(array $products_id)
{
$q_orders = '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 IN (' . implode(',', $products_id) . ')';
$orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($q_orders);
$final_products_list = array();
if (count($orders) > 0) {
$list = '';
foreach ($orders as $order) {
$list .= (int) $order['id_order'] . ',';
}
$list = rtrim($list, ',');
$list_product_ids = join(',', $products_id);
if (Group::isFeatureActive()) {
$sql_groups_join = '
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = product_shop.id_category_default
AND cp.id_product = product_shop.id_product)
LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)';
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups_where = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '=' . (int) Group::getCurrent()->id);
}
$order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT DISTINCT od.product_id, pl.name, pl.description_short, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price,
cl.link_rewrite category, p.ean13, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
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` 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, $this->context->shop) : Product::sqlStock('p', 'product', false, $this->context->shop)) . '
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)
' . (Group::isFeatureActive() ? $sql_groups_join : '') . '
WHERE od.id_order IN (' . $list . ')
AND pl.id_lang = ' . (int) $this->context->language->id . '
AND cl.id_lang = ' . (int) $this->context->language->id . '
AND od.product_id NOT IN (' . $list_product_ids . ')
AND i.cover = 1
AND product_shop.active = 1
' . (Group::isFeatureActive() ? $sql_groups_where : '') . '
ORDER BY RAND()
LIMIT ' . (int) Configuration::get('CROSSSELLING_NBR'));
$tax_calc = Product::getTaxCalculationMethod();
foreach ($order_products as &$order_product) {
$order_product['id_product'] = (int) $order_product['product_id'];
$order_product['image'] = $this->context->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('home'));
$order_product['link'] = $this->context->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']);
if (Configuration::get('CROSSSELLING_DISPLAY_PRICE') && ($tax_calc == 0 || $tax_calc == 2)) {
$order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null);
} elseif (Configuration::get('CROSSSELLING_DISPLAY_PRICE') && $tax_calc == 1) {
$order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null);
}
$order_product['allow_oosp'] = Product::isAvailableWhenOutOfStock((int) $order_product['out_of_stock']);
if (!isset($final_products_list[$order_product['product_id'] . '-' . $order_product['id_image']])) {
$final_products_list[$order_product['product_id'] . '-' . $order_product['id_image']] = $order_product;
}
}
}
return $final_products_list;
}
public static function getImagesByCategory($categories, $start, $limit, $id_lang, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
$sql = 'SELECT
i.`id_image`,
pl.`link_rewrite`
FROM
`' . _DB_PREFIX_ . 'image` i
INNER JOIN `' . _DB_PREFIX_ . 'image_lang` il ON(i.`id_image` = il.`id_image`)
INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON(
i.`id_product` = pl.`id_product`)
INNER JOIN `' . _DB_PREFIX_ . 'image_shop` ish ON (i.`id_image` = ish.`id_image`)';
$sql .= 'WHERE
i.`id_product` IN ( SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_product` cp
' . (Group::isFeatureActive() ? 'INNER JOIN `' . _DB_PREFIX_ . 'category_group` cg ON cp.`id_category` = cg.`id_category`' : '') . '
INNER JOIN `' . _DB_PREFIX_ . 'category` c ON cp.`id_category` = c.`id_category`
INNER JOIN `' . _DB_PREFIX_ . 'product` p ON cp.`id_product` = p.`id_product`
' . Shop::addSqlAssociation('product', 'p', false) . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` ' . Shop::addSqlRestrictionOnLang('pl') . ')
WHERE c.`active` = 1
AND product_shop.`active` = 1
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
' . $sql_groups . ' AND cp.id_category in (' . $categories . ')' . ' AND pl.id_lang =' . $id_lang . ')
AND il.`id_lang` =' . $id_lang . ' AND pl.id_lang = ' . $id_lang . ' AND pl.id_shop = ' . $context->shop->id . ' AND ish.id_shop = ' . $context->shop->id . ' AND ish.cover = 1
ORDER BY
i.`position` ASC' . ($limit > 0 ? ' LIMIT ' . (int) $start . ',' . (int) $limit : '');
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
}
public static function checkAccessStatic($id_product, $id_customer)
{
if (!Group::isFeatureActive()) {
return true;
}
$cache_id = 'Product::checkAccess_' . (int) $id_product . '-' . (int) $id_customer . (!$id_customer ? '-' . (int) Group::getCurrent()->id : '');
if (!Cache::isStored($cache_id)) {
if (!$id_customer) {
$result = (bool) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT ctg.`id_group`
FROM `' . _DB_PREFIX_ . 'category_product` cp
INNER JOIN `' . _DB_PREFIX_ . 'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
WHERE cp.`id_product` = ' . (int) $id_product . ' AND ctg.`id_group` = ' . (int) Group::getCurrent()->id);
} else {
$result = (bool) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT cg.`id_group`
FROM `' . _DB_PREFIX_ . 'category_product` cp
INNER JOIN `' . _DB_PREFIX_ . 'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
INNER JOIN `' . _DB_PREFIX_ . 'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
WHERE cp.`id_product` = ' . (int) $id_product . ' AND cg.`id_customer` = ' . (int) $id_customer);
}
Cache::store($cache_id, $result);
return $result;
}
return Cache::retrieve($cache_id);
}
public static function getMainTags($id_lang, $nb = 10)
{
$context = Context::getContext();
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT t.name, counter AS times
FROM `' . _DB_PREFIX_ . 'tag_count` pt
LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (t.id_tag = pt.id_tag)
WHERE pt.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
AND pt.`id_lang` = ' . (int) $id_lang . ' AND pt.`id_shop` = ' . (int) $context->shop->id . '
ORDER BY times DESC
LIMIT ' . (int) $nb);
} else {
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT t.name, counter AS times
FROM `' . _DB_PREFIX_ . 'tag_count` pt
LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (t.id_tag = pt.id_tag)
WHERE pt.id_group = 0 AND pt.`id_lang` = ' . (int) $id_lang . ' AND pt.`id_shop` = ' . (int) $context->shop->id . '
ORDER BY times DESC
LIMIT ' . (int) $nb);
}
}
protected function getCacheId($name = null)
{
$cache_array = array();
$cache_array[] = $name !== null ? $name : $this->name;
if (Configuration::get('PS_SSL_ENABLED')) {
$cache_array[] = (int) Tools::usingSecureMode();
}
if (Shop::isFeatureActive()) {
$cache_array[] = (int) $this->context->shop->id_shop_url;
}
if (Group::isFeatureActive()) {
$cache_array[] = (int) Group::getCurrent()->id;
}
if (Language::isMultiLanguageActivated()) {
$cache_array[] = (int) $this->context->language->id;
}
if (Currency::isMultiCurrencyActivated()) {
$cache_array[] = (int) $this->context->currency->id;
}
$cache_array[] = (int) $this->context->country->id;
return implode('|', $cache_array);
}
public static function getProducts_Special($id_lang, $arrCategory = array(), $params = null, $total = false, $limit = 0, $offset = 0)
{
$currentDate = date('Y-m-d');
$context = Context::getContext();
$id_address = $context->cart->{Configuration::get('PS_TAX_ADDRESS_TYPE')};
$ids = Address::getCountryAndState($id_address);
$id_country = (int) ($ids['id_country'] ? $ids['id_country'] : Configuration::get('PS_COUNTRY_DEFAULT'));
$where = "";
if ($arrCategory) {
$catIds = implode(', ', $arrCategory);
}
if ($params) {
if ($params->displayOnly == 'condition-new') {
$where .= " AND p.condition = 'new'";
} elseif ($params->displayOnly == 'condition-used') {
$where .= " AND p.condition = 'used'";
} elseif ($params->displayOnly == 'condition-refurbished') {
$where .= " AND p.condition = 'refurbished'";
}
}
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$where .= 'AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cp.id_category IN (' . $catIds . ') AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
)';
} else {
$where .= 'AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_product` cp
WHERE cp.id_category IN (' . $catIds . '))';
}
if ($total == true) {
$sql = 'SELECT COUNT(p.id_product)
FROM (`' . _DB_PREFIX_ . 'product` p
INNER JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.id_product = sp.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') . '
WHERE product_shop.`active` = 1
AND sp.`id_shop` IN(0, ' . (int) $context->shop->id . ')
AND sp.`id_currency` IN(0, ' . (int) $context->currency->id . ')
AND sp.`id_country` IN(0, ' . (int) $id_country . ')
AND sp.`id_group` IN(0, ' . (int) $context->customer->id_default_group . ')
AND sp.`id_customer` IN(0)
AND sp.`from_quantity` = 1
AND (sp.`from` = \'0000-00-00 00:00:00\' OR \'' . pSQL($currentDate) . '\' >= sp.`from`)
AND (sp.`to` = \'0000-00-00 00:00:00\' OR \'' . pSQL($currentDate) . '\' <= sp.`to`)
AND sp.`reduction` > 0
AND p.`visibility` != \'none\' ' . $where;
return DB::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
$sql = 'SELECT DISTINCT p.*, product_shop.*,
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, DATEDIFF(p.`date_add`, DATE_SUB(NOW(),INTERVAL ' . $interval . ' DAY)) > 0 AS new
FROM (`' . _DB_PREFIX_ . 'product` p
INNER JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.id_product = sp.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 sp.`id_shop` IN(0, ' . (int) $context->shop->id . ')
AND sp.`id_currency` IN(0, ' . (int) $context->currency->id . ')
AND sp.`id_country` IN(0, ' . (int) $id_country . ')
AND sp.`id_group` IN(0, ' . (int) $context->customer->id_default_group . ')
AND sp.`id_customer` IN(0)
AND sp.`from_quantity` = 1
AND (sp.`from` = \'0000-00-00 00:00:00\' OR \'' . pSQL($currentDate) . '\' >= sp.`from`)
AND (sp.`to` = \'0000-00-00 00:00:00\' OR \'' . pSQL($currentDate) . '\' <= sp.`to`)
AND sp.`reduction` > 0
AND p.`visibility` != \'none\' ' . $where . '
GROUP BY product_shop.id_product
ORDER BY sp.`reduction` DESC Limit ' . $offset . ', ' . $limit;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if (!$result) {
return false;
}
return $result;
}
function getProductsOrderView($id_lang, $arrCategory = array(), $params = null, $total = false, $short = true, $limit, $offset = 0, $getProperties = true)
{
$context = Context::getContext();
$order_by = 'date_add';
$order_way = 'DESC';
$where = "";
if ($arrCategory) {
$catIds = implode(', ', $arrCategory);
}
if ($params) {
$order_way = $params->orderType;
if ($params->displayOnly == 'condition-new') {
$where .= " AND p.condition = 'new'";
} elseif ($params->displayOnly == 'condition-used') {
$where .= " AND p.condition = 'used'";
} elseif ($params->displayOnly == 'condition-refurbished') {
$where .= " AND p.condition = 'refurbished'";
}
}
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$where .= 'AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cp.id_category IN (' . $catIds . ') AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
)';
} else {
$where .= 'AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_product` cp
WHERE cp.id_category IN (' . $catIds . '))';
}
if ($total == true) {
$sql = 'SELECT COUNT(p.id_product)
FROM `' . _DB_PREFIX_ . 'product` p
' . 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') . '
WHERE product_shop.`active` = 1 AND p.`visibility` != \'none\' ' . $where;
return (int) DB::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
$sql = 'SELECT p.id_product, p.ean13, p.reference, p.id_category_default, p.on_sale, p.quantity, p.minimal_quantity, p.price, p.wholesale_price, p.quantity_discount, p.show_price, p.condition,
product_shop.on_sale, product_shop.id_category_default, product_shop.minimal_quantity, product_shop.price, product_shop.wholesale_price, product_shop.show_price, product_shop.condition, product_shop.indexed,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,
pl.`available_later`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image,
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 AS orderprice';
$sql .= ' FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'groupcategory_product_view` AS gv
On gv.productId = p.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_ . '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') . '
WHERE
product_shop.`id_shop` = ' . (int) $context->shop->id . '
AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ' . $where . '
GROUP BY product_shop.id_product
ORDER BY gv.`total` DESC Limit ' . $offset . ', ' . $limit;
$result = DB::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if (!$result) {
return false;
}
if ($getProperties == false) {
return $result;
}
return Product::getProductsProperties($id_lang, $result);
}
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();
}
// Only use cookie if id_customer is not present
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');
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
if ($count) {
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT COUNT(DISTINCT pt.`id_product`) nb
FROM `' . _DB_PREFIX_ . 'product` p
' . 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 . ')
' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)' : '') . '
WHERE product_shop.`active` = 1
AND p.visibility IN (\'both\', \'search\')
AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . '
' . $sql_groups . '
AND t.`name` LIKE \'%' . pSQL($tag) . '%\'');
}
$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
FROM `' . _DB_PREFIX_ . 'product` p
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`)
' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)' : '') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ')
' . Product::sqlStock('p', 0) . '
WHERE product_shop.`active` = 1
AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . '
' . $sql_groups . '
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;
}
return Product::getProductsProperties((int) $id_lang, $result);
}
public static function find($id_lang, $expr, $page_number = 1, $page_size = 1, $order_by = 'position', $order_way = 'desc', $ajax = false, $use_cookie = true, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
$db = Db::getInstance(_PS_USE_SQL_SLAVE_);
if ($page_number < 1) {
$page_number = 1;
}
if ($page_size < 1) {
$page_size = 1;
}
if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
return false;
}
$intersect_array = array();
$score_array = array();
$words = explode(' ', Search::sanitize($expr, $id_lang, false, $context->language->iso_code));
foreach ($words as $key => $word) {
if (!empty($word) && strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) {
$word = str_replace('%', '\\%', $word);
$word = str_replace('_', '\\_', $word);
$start_search = Configuration::get('PS_SEARCH_START') ? '%' : '';
$end_search = Configuration::get('PS_SEARCH_END') ? '' : '%';
$intersect_array[] = 'SELECT si.id_product
FROM ' . _DB_PREFIX_ . 'search_word sw
LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = ' . (int) $id_lang . '
AND sw.id_shop = ' . $context->shop->id . '
AND sw.word LIKE
' . ($word[0] == '-' ? ' \'' . $start_search . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'' : ' \'' . $start_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'');
if ($word[0] != '-') {
$score_array[] = 'sw.word LIKE \'' . $start_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'';
}
} else {
unset($words[$key]);
}
}
if (!count($words)) {
return $ajax ? array() : array('total' => 0, 'result' => array(), 'painters' => array());
}
$score = '';
if (count($score_array)) {
$score = ',(
SELECT SUM(weight)
FROM ' . _DB_PREFIX_ . 'search_word sw
LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = ' . (int) $id_lang . '
AND sw.id_shop = ' . $context->shop->id . '
AND si.id_product = p.id_product
AND (' . implode(' OR ', $score_array) . ')
) position';
}
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
$results = $db->executeS('
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_product` cp
' . (Group::isFeatureActive() ? 'INNER JOIN `' . _DB_PREFIX_ . 'category_group` cg ON cp.`id_category` = cg.`id_category`' : '') . '
INNER JOIN `' . _DB_PREFIX_ . 'category` c ON cp.`id_category` = c.`id_category`
INNER JOIN `' . _DB_PREFIX_ . 'product` p ON cp.`id_product` = p.`id_product`
' . Shop::addSqlAssociation('product', 'p', false) . '
WHERE c.`active` = 1
AND product_shop.`active` = 1
AND product_shop.`visibility` IN ("both", "search")
AND product_shop.indexed = 1
' . $sql_groups);
$eligible_products = array();
foreach ($results as $row) {
$eligible_products[] = $row['id_product'];
}
foreach ($intersect_array as $query) {
$eligible_products2 = array();
foreach ($db->executeS($query) as $row) {
$eligible_products2[] = $row['id_product'];
}
$eligible_products = array_intersect($eligible_products, $eligible_products2);
if (!count($eligible_products)) {
return $ajax ? array() : array('total' => 0, 'result' => array(), 'painters' => array());
}
}
$eligible_products = array_unique($eligible_products);
$product_pool = '';
foreach ($eligible_products as $id_product) {
if ($id_product) {
$product_pool .= (int) $id_product . ',';
}
}
if (empty($product_pool)) {
return $ajax ? array() : array('total' => 0, 'result' => array());
}
$product_pool = strpos($product_pool, ',') === false ? ' = ' . (int) $product_pool . ' ' : ' IN (' . rtrim($product_pool, ',') . ') ';
if ($ajax) {
$sql = 'SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
cl.link_rewrite crewrite, pl.link_rewrite prewrite ' . $score . '
FROM ' . _DB_PREFIX_ . 'product p
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') . '
INNER 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') . '
)
WHERE p.`id_product` ' . $product_pool . '
ORDER BY position DESC LIMIT 10';
return $db->executeS($sql);
}
if (strpos($order_by, '.') > 0) {
$order_by = explode('.', $order_by);
$order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`';
}
$alias = '';
if ($order_by == 'price') {
$alias = 'product_shop.';
} elseif (in_array($order_by, array('date_upd', 'date_add'))) {
$alias = 'p.';
}
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name ' . $score . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute' : '') . ',
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' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_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) $id_lang . Shop::addSqlRestrictionOnLang('pl') . '
)
' . (Combination::isFeatureActive() ? '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) : Product::sqlStock('p', 'product', false, Context::getContext()->shop)) . '
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
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 . ')
WHERE p.`id_product` ' . $product_pool . '
GROUP BY product_shop.id_product
' . ($order_by ? 'ORDER BY ' . $alias . $order_by : '') . ($order_way ? ' ' . $order_way : '') . '
LIMIT ' . (int) (($page_number - 1) * $page_size) . ',' . (int) $page_size;
$result = $db->executeS($sql);
$helper = new Helper();
$categoryTree = $helper->renderCategoryTree();
$sql = 'SELECT COUNT(*)
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) $id_lang . Shop::addSqlRestrictionOnLang('pl') . '
)
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE p.`id_product` ' . $product_pool;
$total = $db->getValue($sql);
if (!$result) {
$result_properties = false;
} else {
$result_properties = Product::getProductsProperties((int) $id_lang, $result);
}
$sql = 'SELECT c.*, c_lang.*
FROM ' . _DB_PREFIX_ . 'category c
INNER JOIN ' . _DB_PREFIX_ . 'category_lang c_lang ON (
c.id_category = c_lang.id_category)
WHERE c_lang.name LIKE ' . ($word[0] == '-' ? ' \'' . $end_search . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'' : ' \'' . $end_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'') . 'AND c.id_parent = 14';
$painters = $db->executeS($sql);
return array('total' => $total, 'result' => $result_properties, 'painters' => $painters);
}
/**
* Return manufacturers
*
* @param boolean $get_nb_products [optional] return products numbers for each
* @param int $id_lang
* @param bool $active
* @param int $p
* @param int $n
* @param bool $all_group
* @return array Manufacturers
*/
public static function getManufacturers($get_nb_products = false, $id_lang = 0, $active = true, $p = false, $n = false, $all_group = false)
{
if (!$id_lang) {
$id_lang = (int) Configuration::get('PS_LANG_DEFAULT');
}
if (!Group::isFeatureActive()) {
$all_group = true;
}
$manufacturers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT m.*, ml.`description`, ml.`short_description`
FROM `' . _DB_PREFIX_ . 'manufacturer` m
' . Shop::addSqlAssociation('manufacturer', 'm') . '
INNER JOIN `' . _DB_PREFIX_ . 'manufacturer_lang` ml ON (m.`id_manufacturer` = ml.`id_manufacturer` AND ml.`id_lang` = ' . (int) $id_lang . ')
' . ($active ? 'WHERE m.`active` = 1' : '') . '
ORDER BY m.`name` ASC
' . ($p ? ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n : ''));
if ($manufacturers === false) {
return false;
}
if ($get_nb_products) {
$sql_groups = '';
if (!$all_group) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
}
foreach ($manufacturers as $key => $manufacturer) {
$manufacturers[$key]['nb_products'] = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(DISTINCT p.`id_product`)
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
WHERE p.`id_manufacturer` = ' . (int) $manufacturer['id_manufacturer'] . '
AND product_shop.`visibility` NOT IN ("none")
' . ($active ? ' AND product_shop.`active` = 1 ' : '') . '
' . ($all_group ? '' : ' AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cg.`id_group` ' . $sql_groups . '
)'));
}
}
$total_manufacturers = count($manufacturers);
$rewrite_settings = (int) Configuration::get('PS_REWRITING_SETTINGS');
for ($i = 0; $i < $total_manufacturers; $i++) {
$manufacturers[$i]['link_rewrite'] = $rewrite_settings ? Tools::link_rewrite($manufacturers[$i]['name']) : 0;
}
return $manufacturers;
}
/**
* Returns module content for left column
*/
public function hookProductFooter($params)
{
$cache_id = 'crossselling_mod|productfooter|' . (int) $params['product']->id;
if (!$this->isCached('crossselling_mod.tpl', $this->getCacheId($cache_id))) {
$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) $params['product']->id);
if (count($orders)) {
$list = '';
foreach ($orders as $order) {
$list .= (int) $order['id_order'] . ',';
}
$list = rtrim($list, ',');
if (Group::isFeatureActive()) {
$sql_groups_join = '
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = product_shop.id_category_default AND cp.id_product = product_shop.id_product)
LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)';
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups_where = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '=' . (int) Group::getCurrent()->id);
}
$order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT DISTINCT od.product_id, pl.name, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price, product_shop.id_category_default, p.available_for_order, p.customizable, cl.link_rewrite category, p.ean13, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
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` 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, $this->context->shop) : Product::sqlStock('p', 'product', false, $this->context->shop)) . '
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)
' . (Group::isFeatureActive() ? $sql_groups_join : '') . '
WHERE od.id_order IN (' . $list . ')
AND pl.id_lang = ' . (int) $this->context->language->id . '
AND cl.id_lang = ' . (int) $this->context->language->id . '
AND od.product_id != ' . (int) $params['product']->id . '
AND i.cover = 1
AND product_shop.active = 1
' . (Group::isFeatureActive() ? $sql_groups_where : '') . '
ORDER BY RAND()
LIMIT ' . (int) Configuration::get('CROSSSELLING_NBR_M'));
$tax_calc = Product::getTaxCalculationMethod();
foreach ($order_products as &$order_product) {
$order_product['id_product'] = (int) $order_product['product_id'];
$order_product['image'] = $this->context->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('home'));
$order_product['link'] = $this->context->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['price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null);
} elseif ($tax_calc == 1) {
$order_product['price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null);
}
$order_product['allow_oosp'] = Product::isAvailableWhenOutOfStock((int) $order_product['out_of_stock']);
}
$order_products = Product::getProductsProperties((int) $this->context->language->id, $order_products);
$this->smarty->assign(array('order' => false, 'orderProducts' => $order_products, 'homeSize' => Image::getSize(ImageType::getFormatedName('home')), 'middlePosition_crossselling' => round(count($order_products) / 2, 0), 'crossDisplayPrice' => Configuration::get('CROSSSELLING_DISPLAY_PRICE_M')));
}
}
return $this->display(__FILE__, 'crossselling_mod.tpl', $this->getCacheId($cache_id));
}
/**
* Get list of modules we can execute per hook
*
* @since 1.5.0
* @param string $hook_name Get list of modules for this hook if given
* @return array
*/
public static function getHookModuleExecList($hook_name = null)
{
$context = Context::getContext();
$cache_id = 'hook_module_exec_list_' . (isset($context->shop->id) ? '_' . $context->shop->id : '') . (isset($context->customer) ? '_' . $context->customer->id : '');
if (!Cache::isStored($cache_id) || $hook_name == 'displayPayment' || $hook_name == 'displayPaymentEU' || $hook_name == 'displayBackOfficeHeader') {
$frontend = true;
$groups = array();
$use_groups = Group::isFeatureActive();
if (isset($context->employee)) {
$frontend = false;
} else {
// Get groups list
if ($use_groups) {
if (isset($context->customer) && $context->customer->isLogged()) {
$groups = $context->customer->getGroups();
} elseif (isset($context->customer) && $context->customer->isLogged(true)) {
$groups = array((int) Configuration::get('PS_GUEST_GROUP'));
} else {
$groups = array((int) Configuration::get('PS_UNIDENTIFIED_GROUP'));
}
}
}
// SQL Request
$sql = new DbQuery();
$sql->select('h.`name` as hook, m.`id_module`, h.`id_hook`, m.`name` as module, h.`live_edit`');
$sql->from('module', 'm');
if ($hook_name != 'displayBackOfficeHeader') {
$sql->join(Shop::addSqlAssociation('module', 'm', true, 'module_shop.enable_device & ' . (int) Context::getContext()->getDevice()));
$sql->innerJoin('module_shop', 'ms', 'ms.`id_module` = m.`id_module`');
}
$sql->innerJoin('hook_module', 'hm', 'hm.`id_module` = m.`id_module`');
$sql->innerJoin('hook', 'h', 'hm.`id_hook` = h.`id_hook`');
if ($hook_name != 'displayPayment' && $hook_name != 'displayPaymentEU') {
$sql->where('h.`name` != "displayPayment" AND h.`name` != "displayPaymentEU"');
} elseif ($frontend) {
if (Validate::isLoadedObject($context->country)) {
$sql->where('((h.`name` = "displayPayment" OR h.`name` = "displayPaymentEU") AND (SELECT `id_country` FROM `' . _DB_PREFIX_ . 'module_country` mc WHERE mc.`id_module` = m.`id_module` AND `id_country` = ' . (int) $context->country->id . ' AND `id_shop` = ' . (int) $context->shop->id . ' LIMIT 1) = ' . (int) $context->country->id . ')');
}
if (Validate::isLoadedObject($context->currency)) {
$sql->where('((h.`name` = "displayPayment" OR h.`name` = "displayPaymentEU") AND (SELECT `id_currency` FROM `' . _DB_PREFIX_ . 'module_currency` mcr WHERE mcr.`id_module` = m.`id_module` AND `id_currency` IN (' . (int) $context->currency->id . ', -1, -2) LIMIT 1) IN (' . (int) $context->currency->id . ', -1, -2))');
}
}
if (Validate::isLoadedObject($context->shop)) {
$sql->where('hm.`id_shop` = ' . (int) $context->shop->id);
}
if ($frontend) {
if ($use_groups) {
$sql->leftJoin('module_group', 'mg', 'mg.`id_module` = m.`id_module`');
if (Validate::isLoadedObject($context->shop)) {
$sql->where('mg.`id_shop` = ' . (int) $context->shop->id . (count($groups) ? ' AND mg.`id_group` IN (' . implode(', ', $groups) . ')' : ''));
} elseif (count($groups)) {
$sql->where('mg.`id_group` IN (' . implode(', ', $groups) . ')');
}
}
}
$sql->groupBy('hm.id_hook, hm.id_module');
$sql->orderBy('hm.`position`');
$list = array();
if ($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql)) {
foreach ($result as $row) {
$row['hook'] = strtolower($row['hook']);
if (!isset($list[$row['hook']])) {
$list[$row['hook']] = array();
}
$list[$row['hook']][] = array('id_hook' => $row['id_hook'], 'module' => $row['module'], 'id_module' => $row['id_module'], 'live_edit' => $row['live_edit']);
}
}
if ($hook_name != 'displayPayment' && $hook_name != 'displayPaymentEU' && $hook_name != 'displayBackOfficeHeader') {
Cache::store($cache_id, $list);
// @todo remove this in 1.6, we keep it in 1.5 for backward compatibility
self::$_hook_modules_cache_exec = $list;
}
} else {
$list = Cache::retrieve($cache_id);
}
// If hook_name is given, just get list of modules for this hook
if ($hook_name) {
$retro_hook_name = strtolower(Hook::getRetroHookName($hook_name));
$hook_name = strtolower($hook_name);
$return = array();
$inserted_modules = array();
if (isset($list[$hook_name])) {
$return = $list[$hook_name];
}
foreach ($return as $module) {
$inserted_modules[] = $module['id_module'];
}
if (isset($list[$retro_hook_name])) {
foreach ($list[$retro_hook_name] as $retro_module_call) {
if (!in_array($retro_module_call['id_module'], $inserted_modules)) {
$return[] = $retro_module_call;
}
}
}
return count($return) > 0 ? $return : false;
} else {
return $list;
}
}
public static function getValueForProduct($id_product, $id_group)
{
if (!Group::isFeatureActive()) {
return 0;
}
if (!isset(self::$reduction_cache[$id_product . '-' . $id_group])) {
self::$reduction_cache[$id_product . '-' . $id_group] = Db::getInstance()->getValue('
SELECT `reduction`
FROM `' . _DB_PREFIX_ . 'product_group_reduction_cache`
WHERE `id_product` = ' . (int) $id_product . ' AND `id_group` = ' . (int) $id_group);
}
// Should return string (decimal in database) and not a float
return self::$reduction_cache[$id_product . '-' . $id_group];
}
if (isset($cookie->id_customer) && (int) $cookie->id_customer) {
$customer = new Customer($cookie->id_customer);
if (!Validate::isLoadedObject($customer)) {
$context->cookie->logout();
} else {
$customer->logged = true;
if ($customer->id_lang != $context->language->id) {
$customer->id_lang = $context->language->id;
$customer->update();
}
}
}
if (!isset($customer) || !Validate::isLoadedObject($customer)) {
$customer = new Customer();
/* Change the default group */
if (Group::isFeatureActive()) {
$customer->id_default_group = (int) Configuration::get('PS_UNIDENTIFIED_GROUP');
}
}
$customer->id_guest = $cookie->id_guest;
$context->customer = $customer;
}
/* Link should also be initialized in the context here for retrocompatibility */
$https_link = Tools::usingSecureMode() && Configuration::get('PS_SSL_ENABLED') ? 'https://' : 'http://';
$context->link = new Link($https_link, $https_link);
/**
* @deprecated
* USE : Configuration::get() method in order to getting the id of order status
*/
define('_PS_OS_CHEQUE_', Configuration::get('PS_OS_CHEQUE'));
define('_PS_OS_PAYMENT_', Configuration::get('PS_OS_PAYMENT'));
public static function getMenus($id_lang, $active = true, $get_from_all_shops = false, $groupRestrict = false)
{
$sql_groups_join = '';
$sql_groups_where = '';
if ($groupRestrict && (version_compare(_PS_VERSION_, '1.5.0.0', '>=') && Group::isFeatureActive() || version_compare(_PS_VERSION_, '1.5.0.0', '<') && version_compare(_PS_VERSION_, '1.2.5.0', '>='))) {
$groups = PM_AdvancedTopMenu::getCustomerGroups();
if (sizeof($groups)) {
$sql_groups_join = 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = ca.`id_category`)';
$sql_groups_where = 'AND IF (atp.`id_category` IS NULL OR atp.`id_category` = 0, 1, cg.`id_group` IN (' . implode(',', $groups) . '))';
}
}
$sql = 'SELECT atp.*, atpl.*,
cl.link_rewrite, cl.meta_title,
cal.link_rewrite as category_link_rewrite, cal.name as category_name,
m.name as manufacturer_name,
s.name as supplier_name
FROM `' . _DB_PREFIX_ . 'pm_advancedtopmenu` atp
' . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') && Shop::isFeatureActive() ? self::addSqlAssociation('pm_advancedtopmenu', 'atp', 'id_menu', true, null, $get_from_all_shops ? 'all' : false) : '') . '
LEFT JOIN `' . _DB_PREFIX_ . 'pm_advancedtopmenu_lang` atpl ON (atp.`id_menu` = atpl.`id_menu` AND atpl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN ' . _DB_PREFIX_ . 'cms c ON (c.id_cms = atp.`id_cms`)
' . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') ? Shop::addSqlAssociation('cms', 'c', false, true, null, $get_from_all_shops ? 'all' : false) : '') . '
LEFT JOIN ' . _DB_PREFIX_ . 'cms_lang cl ON (c.id_cms = cl.id_cms AND cl.id_lang = ' . intval($id_lang) . ')
LEFT JOIN ' . _DB_PREFIX_ . 'category ca ON (ca.id_category = atp.`id_category`)
' . $sql_groups_join . '
LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cal ON (ca.id_category = cal.id_category AND cal.id_lang = ' . intval($id_lang) . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') ? Shop::addSqlRestrictionOnLang('cal') : '') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (atp.`id_manufacturer` = m.`id_manufacturer`)
' . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') ? Shop::addSqlAssociation('manufacturer', 'm', false, true, null, $get_from_all_shops ? 'all' : false) : '') . '
LEFT JOIN `' . _DB_PREFIX_ . 'supplier` s ON (atp.`id_supplier` = s.`id_supplier`)
' . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') ? Shop::addSqlAssociation('supplier', 's', false, true, null, $get_from_all_shops ? 'all' : false) : '') . '
' . ($active ? '
WHERE atp.`active` = 1
AND ((atp.`id_manufacturer` = 0 AND atp.`id_supplier` = 0 AND atp.`id_category` = 0 AND atp.`id_cms` = 0)
OR c.id_cms IS NOT NULL OR m.id_manufacturer IS NOT NULL OR ca.id_category IS NOT NULL OR s.`id_supplier` IS NOT NULL)
' . $sql_groups_where : '') . '
GROUP BY atp.`id_menu`
ORDER BY atp.`position`';
return Db::getInstance()->ExecuteS($sql);
}
public static function getDefaultGroupId($id_customer)
{
if (!Group::isFeatureActive()) {
return Configuration::get('PS_CUSTOMER_GROUP');
}
if (!isset(self::$_defaultGroupId[(int) $id_customer])) {
self::$_defaultGroupId[(int) $id_customer] = Db::getInstance()->getValue('
SELECT `id_default_group`
FROM `' . _DB_PREFIX_ . 'customer`
WHERE `id_customer` = ' . (int) $id_customer);
}
return self::$_defaultGroupId[(int) $id_customer];
}
public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
//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_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)
WHERE cg.`id_group` ' . $sql_groups);
$ids = array();
foreach ($products as $product) {
$ids[$product['id_product']] = 1;
}
$ids = array_keys($ids);
sort($ids);
$ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';
//Main query
$sql = '
SELECT
p.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`,
MAX(image_shop.`id_image`) id_image, il.`legend`,
ps.`quantity` AS sales, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable,
IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock,
product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" 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') . '
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_ . '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_ . 'category_lang` cl
ON cl.`id_category` = product_shop.`id_category_default`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . '
WHERE product_shop.`active` = 1
AND p.`visibility` != \'none\'
AND p.`id_product` IN (' . $ids . ')
GROUP BY product_shop.id_product
ORDER BY sales DESC
LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products;
if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
/**
* @static
* @param Context|null $context
* @return mixed
*/
public static function autoAddToCart(Context $context = null)
{
if ($context === null) {
$context = Context::getContext();
}
if (!CartRule::isFeatureActive() || !Validate::isLoadedObject($context->cart)) {
return;
}
$sql = '
SELECT cr.*
FROM ' . _DB_PREFIX_ . 'cart_rule cr
LEFT JOIN ' . _DB_PREFIX_ . 'cart_rule_shop crs ON cr.id_cart_rule = crs.id_cart_rule
' . (!$context->customer->id && Group::isFeatureActive() ? ' LEFT JOIN ' . _DB_PREFIX_ . 'cart_rule_group crg ON cr.id_cart_rule = crg.id_cart_rule' : '') . '
LEFT JOIN ' . _DB_PREFIX_ . 'cart_rule_carrier crca ON cr.id_cart_rule = crca.id_cart_rule
' . ($context->cart->id_carrier ? 'LEFT JOIN ' . _DB_PREFIX_ . 'carrier c ON (c.id_reference = crca.id_carrier AND c.deleted = 0)' : '') . '
LEFT JOIN ' . _DB_PREFIX_ . 'cart_rule_country crco ON cr.id_cart_rule = crco.id_cart_rule
WHERE cr.active = 1
AND cr.code = ""
AND cr.quantity > 0
AND cr.date_from < "' . date('Y-m-d H:i:s') . '"
AND cr.date_to > "' . date('Y-m-d H:i:s') . '"
AND (
cr.id_customer = 0
' . ($context->customer->id ? 'OR cr.id_customer = ' . (int) $context->cart->id_customer : '') . '
)
AND (
cr.`carrier_restriction` = 0
' . ($context->cart->id_carrier ? 'OR c.id_carrier = ' . (int) $context->cart->id_carrier : '') . '
)
AND (
cr.`shop_restriction` = 0
' . (Shop::isFeatureActive() && $context->shop->id ? 'OR crs.id_shop = ' . (int) $context->shop->id : '') . '
)
AND (
cr.`group_restriction` = 0
' . ($context->customer->id ? 'OR 0 < (
SELECT cg.`id_group`
FROM `' . _DB_PREFIX_ . 'customer_group` cg
INNER JOIN `' . _DB_PREFIX_ . 'cart_rule_group` crg ON cg.id_group = crg.id_group
WHERE cr.`id_cart_rule` = crg.`id_cart_rule`
AND cg.`id_customer` = ' . (int) $context->customer->id . '
LIMIT 1
)' : (Group::isFeatureActive() ? 'OR crg.`id_group` = ' . (int) Configuration::get('PS_UNIDENTIFIED_GROUP') : '')) . '
)
AND (
cr.`reduction_product` <= 0
OR cr.`reduction_product` IN (
SELECT `id_product`
FROM `' . _DB_PREFIX_ . 'cart_product`
WHERE `id_cart` = ' . (int) $context->cart->id . '
)
)
AND cr.id_cart_rule NOT IN (SELECT id_cart_rule FROM ' . _DB_PREFIX_ . 'cart_cart_rule WHERE id_cart = ' . (int) $context->cart->id . ')
ORDER BY priority';
$result = Db::getInstance()->executeS($sql);
if ($result) {
$cart_rules = ObjectModel::hydrateCollection('CartRule', $result);
if ($cart_rules) {
foreach ($cart_rules as $cart_rule) {
if ($cart_rule->checkValidity($context, false, false)) {
$context->cart->addCartRule($cart_rule->id);
}
}
}
}
}
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);
}
public static function getNestedCategories($root_category = null, $id_lang = false, $active = true, $groups = null, $use_shop_restriction = true, $sql_filter = '', $sql_sort = '', $sql_limit = '')
{
global $cookie;
if (!Module::isInstalled('agilemultipleseller')) {
return parent::getNestedCategories($root_category, $id_lang, $active, $groups, $use_shop_restriction, $sql_filter, $sql_sort, $sql_limit);
}
if (isset($root_category) && !Validate::isInt($root_category)) {
die(Tools::displayError());
}
if (!Validate::isBool($active)) {
die(Tools::displayError());
}
if (isset($groups) && Group::isFeatureActive() && !is_array($groups)) {
$groups = (array) $groups;
}
if (intval($cookie->profile) == 0) {
$cache_id = 'Category::getNestedCategories_' . md5((int) $root_category . (int) $id_lang . (int) $active . (int) $active . (isset($groups) && Group::isFeatureActive() ? implode('', $groups) : ''));
} else {
$cache_id = 'Category::getNestedCategories_' . md5((int) $cookie->id_customer . (int) $root_category . (int) $id_lang . (int) $active . (int) $active . (isset($groups) && Group::isFeatureActive() ? implode('', $groups) : ''));
}
if (!Cache::isStored($cache_id)) {
$result = Db::getInstance()->executeS('
SELECT c.*, cl.*
FROM `' . _DB_PREFIX_ . 'category` c
' . ($use_shop_restriction ? Shop::addSqlAssociation('category', 'c') : '') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON c.`id_category` = cl.`id_category`' . Shop::addSqlRestrictionOnLang('cl') . '
' . (isset($groups) && Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON c.`id_category` = cg.`id_category`' : '') . '
' . (isset($root_category) ? 'RIGHT JOIN `' . _DB_PREFIX_ . 'category` c2 ON c2.`id_category` = ' . (int) $root_category . ' AND c.`nleft` >= c2.`nleft` AND c.`nright` <= c2.`nright`' : '') . '
WHERE 1 ' . $sql_filter . ' ' . ($id_lang ? 'AND `id_lang` = ' . (int) $id_lang : '') . '
' . ($active ? ' AND c.`active` = 1' : '') . '
' . (isset($groups) && Group::isFeatureActive() ? ' AND cg.`id_group` IN (' . implode(',', $groups) . ')' : '') . '
' . (!$id_lang || isset($groups) && Group::isFeatureActive() ? ' GROUP BY c.`id_category`' : '') . '
' . ($sql_sort != '' ? $sql_sort : ' ORDER BY c.`level_depth` ASC') . '
' . ($sql_sort == '' && $use_shop_restriction ? ', category_shop.`position` ASC' : '') . '
' . ($sql_limit != '' ? $sql_limit : ''));
$categories = array();
$buff = array();
if (!isset($root_category)) {
$root_category = 1;
}
foreach ($result as $row) {
$current =& $buff[$row['id_category']];
$current = $row;
if ($row['id_category'] == $root_category) {
$categories[$row['id_category']] =& $current;
} else {
$buff[$row['id_parent']]['children'][$row['id_category']] =& $current;
}
}
Cache::store($cache_id, $categories);
}
return Cache::retrieve($cache_id);
}
public static function getCurrentCustomerGroups()
{
if (!Group::isFeatureActive()) {
return array();
}
$context = Context::getContext();
if (!isset($context->customer) || !$context->customer->id) {
return array();
}
if (!is_array(self::$currentCustomerGroups)) {
self::$currentCustomerGroups = array();
$result = Db::getInstance()->executeS('SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $context->customer->id);
foreach ($result as $row) {
self::$currentCustomerGroups[] = $row['id_group'];
}
}
return self::$currentCustomerGroups;
}
public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
// no group by needed : there's only one attribute with default_on=1 for a given id_product + shop
// same for image with cover=1
$sql = '
SELECT
p.id_product, IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`,
image_shop.`id_image` id_image, il.`legend`,
ps.`quantity` AS sales, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable,
IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock,
product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new,
product_shop.`on_sale`, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity
FROM `' . _DB_PREFIX_ . 'product_sale` ps
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product`
' . 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_ . 'product_attribute` pa ON (product_attribute_shop.id_product_attribute=pa.id_product_attribute)
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_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 . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
ON cl.`id_category` = product_shop.`id_category_default`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . Product::sqlStock('p', 0);
$sql .= '
WHERE product_shop.`active` = 1
AND p.`visibility` != \'none\'';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql .= ' AND EXISTS(SELECT 1 FROM `' . _DB_PREFIX_ . 'category_product` cp
JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . ')
WHERE cp.`id_product` = p.`id_product`)';
}
$sql .= '
ORDER BY ps.quantity DESC
LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products;
if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
