public static function getBestSales($id_lang, $pageNumber = 0, $nbProducts = 10, $orderBy = NULL, $orderWay = NULL)
{
global $link, $cookie;
if ($pageNumber < 0) {
$pageNumber = 0;
}
if ($nbProducts < 1) {
$nbProducts = 10;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'sales';
}
if (empty($orderWay)) {
$orderWay = 'DESC';
}
$result = Db::getInstance()->ExecuteS('
SELECT p.*,
pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
i.`id_image`, il.`legend`,
ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`
FROM `' . _DB_PREFIX_ . 'product_sale` ps
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = p.`id_tax`)
WHERE p.`active` = 1
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` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . '
)
ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . intval($pageNumber * $nbProducts) . ', ' . intval($nbProducts));
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getBestSales($id_lang, $pageNumber = 0, $nbProducts = 10, $orderBy = NULL, $orderWay = NULL)
{
global $link, $cookie;
if ($pageNumber < 0) {
$pageNumber = 0;
}
if ($nbProducts < 1) {
$nbProducts = 10;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'sales';
}
if (empty($orderWay)) {
$orderWay = 'DESC';
}
$price_sql = Product::getProductPriceSql('p.id_product', 'pp');
$customer_join = '';
$customer_where = '';
if ($cookie->id_customer) {
$customer_join = "\n\t\t INNER JOIN `PREFIX_customer_group` cg ON\n\t\t cg.`id_group` = ctg.`id_group`\n\t\t ";
$customer_where = "cg.`id_customer` = {$cookie->id_customer} OR";
}
if (isset($orderByPrefix)) {
$orderByPrefix .= '.';
} else {
$orderByPrefix = '';
}
$orderBy = pSQL($orderBy);
$orderWay = pSQL($orderWay);
$pageStart = intval($pageNumber * $nbProducts);
$nbProducts = intval($nbProducts);
$sql = "\n\t\t SELECT\n\t\t p.*, pp.*,\n\t\t pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`,\n\t\t i.`id_image`, il.`legend`,\n\t\t ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`\n\t\t FROM\n\t\t `PREFIX_product_sale` ps \n\t\t LEFT JOIN `PREFIX_product` p ON\n\t\t ps.`id_product` = p.`id_product`\n\t\t {$price_sql}\n\t\t LEFT JOIN `PREFIX_product_lang` pl ON\n\t\t p.`id_product` = pl.`id_product` AND pl.`id_lang` = {$id_lang}\n\t\t LEFT JOIN `PREFIX_image` i ON\n\t\t i.`id_product` = p.`id_product` AND i.`cover` = 1\n\t\t LEFT JOIN `PREFIX_image_lang` il ON\n\t\t i.`id_image` = il.`id_image` AND il.`id_lang` = {$id_lang}\n\t\t LEFT JOIN `PREFIX_tax` t ON\n\t\t t.`id_tax` = pp.`id_tax`\n\t\t LEFT JOIN `PREFIX_category_product` cp ON\n\t\t cp.`id_product` = p.`id_product`\n\t\t INNER JOIN `PREFIX_category_group` ctg ON\n\t\t ctg.`id_category` = cp.`id_category`\n\t\t {$customer_join}\n\t\t WHERE\n\t\t p.`active` = 1\n\t\t AND ({$customer_where} ctg.`id_group` = 1)\n\t\t GROUP BY p.`id_product`\n\t\t ORDER BY {$orderByPrefix}`{$orderBy}` {$orderWay}\n\t\t LIMIT {$pageStart}, {$nbProducts}\n\t\t";
$sql = str_replace('PREFIX_', _DB_PREFIX_, $sql);
$result = Db::getInstance()->ExecuteS($sql);
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null)
{
$context = Context::getContext();
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 = 'quantity';
$order_table = 'ps';
}
if ($order_by == 'date_add' || $order_by == 'date_upd') {
$order_table = 'product_shop';
}
if (is_null($order_way) || $order_by == 'sales') {
$order_way = 'DESC';
}
$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
// 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.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
' . (Combination::isFeatureActive() ? 'product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute,' : '') . '
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`,
m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
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("' . date('Y-m-d') . ' 00:00:00",
INTERVAL ' . (int) $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);
if (Combination::isFeatureActive()) {
$sql .= ' 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 . ')';
}
$sql .= ' 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_ . '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->country->id . '
AND tr.`id_state` = 0
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
' . 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`)';
}
if ($final_order_by != 'price') {
$sql .= '
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);
}
/**
* Get new products
*
* @param integer $id_lang Language id
* @param integer $pageNumber Start from (optional)
* @param integer $nbProducts Number of products to return (optional)
* @return array New products
*/
public static function getNewProducts($id_lang, $page_number = 0, $nb_products = 10, $count = false, $order_by = null, $order_way = null, Context $context = null)
{
$days_np = 99999;
// Configuration::get('PS_NB_DAYS_NEW_PRODUCT')
if (!$context) {
$context = Context::getContext();
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
if (empty($order_by) || $order_by == 'position') {
$order_by = 'date_add';
}
if (empty($order_way)) {
$order_way = 'DESC';
}
if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add' || $order_by == 'date_upd') {
$order_by_prefix = 'p';
} else {
if ($order_by == 'name') {
$order_by_prefix = 'pl';
}
}
if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
die(Tools::displayError());
}
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = '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` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
)';
}
if (strpos($order_by, '.') > 0) {
$order_by = explode('.', $order_by);
$order_by_prefix = $order_by[0];
$order_by = $order_by[1];
}
if ($count) {
$sql = 'SELECT COUNT(p.`id_product`) AS nb
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
WHERE product_shop.`active` = 1
AND product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . ($days_np ? (int) $days_np : 20) . ' DAY')) . '"
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
' . $sql_groups;
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
$sql = new DbQuery();
$sql->select('p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,
product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . ($days_np ? (int) $days_np : 20) . ' DAY')) . '" as new');
$sql->from('product', 'p');
$sql->join(Shop::addSqlAssociation('product', 'p'));
$sql->leftJoin('product_lang', 'pl', '
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
$sql->leftJoin('image', 'i', 'i.`id_product` = p.`id_product`');
$sql->join(Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1'));
$sql->leftJoin('image_lang', 'il', 'i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang);
$sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`');
$sql->where('product_shop.`active` = 1');
if ($front) {
$sql->where('product_shop.`visibility` IN ("both", "catalog")');
}
$sql->where('product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . ($days_np ? (int) $days_np : 20) . ' DAY')) . '"');
if (Group::isFeatureActive()) {
$sql->where('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 . '
)');
}
$sql->groupBy('product_shop.id_product');
$sql->orderBy((isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way));
$sql->limit($nb_products, $page_number * $nb_products);
if (Combination::isFeatureActive()) {
$sql->select('MAX(product_attribute_shop.id_product_attribute) id_product_attribute');
$sql->leftOuterJoin('product_attribute', 'pa', 'p.`id_product` = pa.`id_product`');
$sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1'));
}
$sql->join(Product::sqlStock('p', Combination::isFeatureActive() ? 'product_attribute_shop' : 0));
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if ($order_by == 'price') {
Tools::orderbyPrice($result, $order_way);
}
if (!$result) {
return false;
}
$products_ids = array();
foreach ($result as $row) {
$products_ids[] = $row['id_product'];
}
// Thus you can avoid one query per product, because there will be only one query for all the products of the cart
Product::cacheFrontFeatures($products_ids, $id_lang);
return Product::getProductsProperties((int) $id_lang, $result);
}
public static function getBestSales($id_lang, $pageNumber = 0, $nbProducts = 10, $orderBy = null, $orderWay = null)
{
if ($pageNumber < 0) {
$pageNumber = 0;
}
if ($nbProducts < 1) {
$nbProducts = 10;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'sales';
}
if (empty($orderWay)) {
$orderWay = 'DESC';
}
$groups = FrontController::getCurrentCustomerGroups();
$sqlGroups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
SELECT p.*,
pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, m.`name` manufacturer_name, p.`id_manufacturer` as id_manufacturer,
i.`id_image`, il.`legend`,
ps.`quantity` sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`,
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_sale` ps
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) Country::getDefaultCountryId() . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
WHERE p.`active` = 1
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` ' . $sqlGroups . '
)
ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . (int) ($pageNumber * $nbProducts) . ', ' . (int) $nbProducts);
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public function getProductByFilters($selected_filters = array())
{
global $cookie;
if (!empty($this->products)) {
return $this->products;
}
/* If the current category isn't defined or if it's homepage, we have nothing to display */
$id_parent = (int) Tools::getValue('id_category', Tools::getValue('id_category_layered', 1));
if ($id_parent == 1) {
return false;
}
$alias_where = 'p';
if (version_compare(_PS_VERSION_, '1.5', '>')) {
$alias_where = 'product_shop';
}
$query_filters_where = ' AND ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")';
$query_filters_from = '';
$parent = new Category((int) $id_parent);
if (!count($selected_filters['category'])) {
if (Configuration::get('PS_LAYERED_FULL_TREE')) {
$query_filters_from .= ' INNER JOIN ' . _DB_PREFIX_ . 'category_product cp
ON p.id_product = cp.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)';
} else {
$query_filters_from .= ' INNER JOIN ' . _DB_PREFIX_ . 'category_product cp
ON p.id_product = cp.id_product
INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category
AND c.id_category = ' . (int) $id_parent . '
AND c.active = 1)';
}
}
foreach ($selected_filters as $key => $filter_values) {
if (!count($filter_values)) {
continue;
}
preg_match('/^(.*[^_0-9])/', $key, $res);
$key = $res[1];
switch ($key) {
case 'id_feature':
$sub_queries = array();
foreach ($filter_values as $filter_value) {
$filter_value_array = explode('_', $filter_value);
if (!isset($sub_queries[$filter_value_array[0]])) {
$sub_queries[$filter_value_array[0]] = array();
}
$sub_queries[$filter_value_array[0]][] = 'fp.`id_feature_value` = ' . (int) $filter_value_array[1];
}
foreach ($sub_queries as $sub_query) {
$query_filters_where .= ' AND p.id_product IN (SELECT `id_product` FROM `' . _DB_PREFIX_ . 'feature_product` fp WHERE ';
$query_filters_where .= implode(' OR ', $sub_query) . ') ';
}
break;
case 'id_attribute_group':
$sub_queries = array();
foreach ($filter_values as $filter_value) {
$filter_value_array = explode('_', $filter_value);
if (!isset($sub_queries[$filter_value_array[0]])) {
$sub_queries[$filter_value_array[0]] = array();
}
$sub_queries[$filter_value_array[0]][] = 'pac.`id_attribute` = ' . (int) $filter_value_array[1];
}
foreach ($sub_queries as $sub_query) {
$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
FROM `' . _DB_PREFIX_ . 'product_attribute_combination` pac
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
ON (pa.`id_product_attribute` = pac.`id_product_attribute`)' . Shop::addSqlAssociation('product_attribute', 'pa') . '
WHERE ' . implode(' OR ', $sub_query) . ') ';
}
break;
case 'category':
$query_filters_where .= ' AND p.id_product IN (SELECT id_product FROM ' . _DB_PREFIX_ . 'category_product cp WHERE ';
foreach ($selected_filters['category'] as $id_category) {
$query_filters_where .= 'cp.`id_category` = ' . (int) $id_category . ' OR ';
}
$query_filters_where = rtrim($query_filters_where, 'OR ') . ')';
break;
case 'quantity':
if (count($selected_filters['quantity']) == 2) {
break;
}
$query_filters_where .= ' AND sa.quantity ' . (!$selected_filters['quantity'][0] ? '<=' : '>') . ' 0 ';
$query_filters_from .= 'LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sa ON (sa.id_product = p.id_product AND sa.id_shop = ' . (int) Context::getContext()->shop->id . ') ';
break;
case 'manufacturer':
$query_filters_where .= ' AND p.id_manufacturer IN (' . implode($selected_filters['manufacturer'], ',') . ')';
break;
case 'condition':
if (count($selected_filters['condition']) == 3) {
break;
}
$query_filters_where .= ' AND ' . $alias_where . '.condition IN (';
foreach ($selected_filters['condition'] as $cond) {
$query_filters_where .= '\'' . $cond . '\',';
}
$query_filters_where = rtrim($query_filters_where, ',') . ')';
break;
case 'weight':
if ($selected_filters['weight'][0] != 0 || $selected_filters['weight'][1] != 0) {
$query_filters_where .= ' AND p.`weight` BETWEEN ' . (double) ($selected_filters['weight'][0] - 0.001) . ' AND ' . (double) ($selected_filters['weight'][1] + 0.001);
}
case 'price':
if (isset($selected_filters['price'])) {
if ($selected_filters['price'][0] !== '' || $selected_filters['price'][1] !== '') {
$price_filter = array();
$price_filter['min'] = (double) $selected_filters['price'][0];
$price_filter['max'] = (double) $selected_filters['price'][1];
}
} else {
$price_filter = false;
}
break;
}
}
$id_currency = (int) Context::getContext()->currency->id;
$price_filter_query_in = '';
// All products with price range between price filters limits
$price_filter_query_out = '';
// All products with a price filters limit on it price range
if (isset($price_filter) && $price_filter) {
$price_filter_query_in = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi
ON
(
psi.price_min >= ' . (int) $price_filter['min'] . '
AND psi.price_max <= ' . (int) $price_filter['max'] . '
AND psi.`id_product` = p.`id_product`
AND psi.`id_currency` = ' . $id_currency . '
)';
$price_filter_query_out = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi
ON
((psi.price_min < ' . (int) $price_filter['min'] . ' AND psi.price_max > ' . (int) $price_filter['min'] . ')
OR
(psi.price_max > ' . (int) $price_filter['max'] . ' AND psi.price_min < ' . (int) $price_filter['max'] . '))
AND psi.`id_product` = p.`id_product`
AND psi.`id_currency` = ' . $id_currency;
}
$query_filters_from .= Shop::addSqlAssociation('product', 'p');
$all_products_out = self::query('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p
' . $price_filter_query_out . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . ' GROUP BY id_product');
$all_products_in = self::query('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p
' . $price_filter_query_in . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . ' GROUP BY id_product');
$product_id_list = array();
while ($product = DB::getInstance()->nextRow($all_products_in)) {
$product_id_list[] = (int) $product['id_product'];
}
while ($product = DB::getInstance()->nextRow($all_products_out)) {
if (isset($price_filter) && $price_filter) {
$price = (int) Product::getPriceStatic($product['id_product'], Configuration::get('PS_LAYERED_FILTER_PRICE_USETAX'));
// Cast to int because we don't care about cents
if ($price < $price_filter['min'] || $price > $price_filter['max']) {
continue;
}
$product_id_list[] = (int) $product['id_product'];
}
}
$this->nbr_products = count($product_id_list);
if ($this->nbr_products == 0) {
$this->products = array();
} else {
$n = (int) Tools::getValue('n', Configuration::get('PS_PRODUCTS_PER_PAGE'));
$nb_day_new_product = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
$this->products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT
p.*,
' . ($alias_where == 'p' ? '' : 'product_shop.*,') . '
' . $alias_where . '.id_category_default,
pl.*,
MAX(image_shop.`id_image`) id_image,
il.legend,
m.name manufacturer_name,
DATEDIFF(' . $alias_where . '.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (int) $nb_day_new_product . ' DAY)) > 0 AS new
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category)
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . Shop::addSqlRestrictionOnLang('pl') . ' AND pl.id_lang = ' . (int) $cookie->id_lang . ')
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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")
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
AND p.id_product IN (' . implode(',', $product_id_list) . ')
GROUP BY product_shop.id_product
ORDER BY ' . Tools::getProductsOrder('by', Tools::getValue('orderby'), true) . ' ' . Tools::getProductsOrder('way', Tools::getValue('orderway')) . ' LIMIT ' . (((int) $this->page - 1) * $n . ',' . $n));
}
if (Tools::getProductsOrder('by', Tools::getValue('orderby'), true) == 'p.price') {
Tools::orderbyPrice($this->products, Tools::getProductsOrder('way', Tools::getValue('orderway')));
}
return $this->products;
}
public static function getProducts($id_supplier, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $active_category = true)
{
$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());
}
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
/* Return only the number of products */
if ($get_total) {
$sql = '
SELECT 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_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp 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' : '') . '
WHERE cg.`id_group` ' . $sql_groups . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
return (int) count($result);
}
$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.';
}
$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`,
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
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_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') . '
WHERE ps.`id_supplier` = ' . (int) $id_supplier . ($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_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp 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' : '') . '
WHERE cg.`id_group` ' . $sql_groups . '
)
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);
}
/**
* Get prices drop
*
* @param integer $id_lang Language id
* @param integer $pageNumber Start from (optional)
* @param integer $nbProducts Number of products to return (optional)
* @param boolean $count Only in order to get total number (optional)
* @return array Prices drop
*/
public static function getPricesDrop($id_lang, $pageNumber = 0, $nbProducts = 10, $count = false, $orderBy = NULL, $orderWay = NULL, $beginning = false, $ending = false)
{
if (!Validate::isBool($count)) {
die(Tools::displayError());
}
if ($pageNumber < 0) {
$pageNumber = 0;
}
if ($nbProducts < 1) {
$nbProducts = 10;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'price';
}
if (empty($orderWay)) {
$orderWay = 'DESC';
}
if ($orderBy == 'id_product' or $orderBy == 'price' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
}
if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$currentDate = date('Y-m-d H:i:s');
$ids_product = self::_getProductIdByDate(!$beginning ? $currentDate : $beginning, !$ending ? $currentDate : $ending);
$groups = FrontController::getCurrentCustomerGroups();
$sqlGroups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
if ($count) {
$sql = '
SELECT COUNT(DISTINCT p.`id_product`) AS nb
FROM `' . _DB_PREFIX_ . 'product` p
WHERE p.`active` = 1
AND p.`show_price` = 1
' . ((!$beginning and !$ending) ? ' AND p.`id_product` IN(' . ((is_array($ids_product) and sizeof($ids_product)) ? implode(', ', array_map('intval', $ids_product)) : 0) . ')' : '') . '
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` ' . $sqlGroups . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return (int) $result['nb'];
}
$sql = '
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
pl.`name`, p.`ean13`, p.`upc`, i.`id_image`, il.`legend`, t.`rate`, m.`name` AS manufacturer_name,
DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) Country::getDefaultCountryId() . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
WHERE 1
AND p.`active` = 1
AND p.`show_price` = 1
' . ((!$beginning and !$ending) ? ' AND p.`id_product` IN (' . ((is_array($ids_product) and sizeof($ids_product)) ? implode(', ', $ids_product) : 0) . ')' : '') . '
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` ' . $sqlGroups . '
)
ORDER BY ' . (isset($orderByPrefix) ? pSQL($orderByPrefix) . '.' : '') . '`' . pSQL($orderBy) . '`' . ' ' . pSQL($orderWay) . '
LIMIT ' . (int) ($pageNumber * $nbProducts) . ', ' . (int) $nbProducts;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
/**
* Return current category products
*
* @param integer $id_lang Language ID
* @param integer $p Page number
* @param integer $n Number of products per page
* @param boolean $getTotal return the number of results instead of the results themself
* @param boolean $active return only active products
* @param boolean $random active a random filter for returned products
* @param int $randomNumberProducts number of products to return if random is activated
* @return mixed Products or number of products
*/
public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
{
global $cookie;
if ($p < 1) {
$p = 1;
}
if (empty($orderBy)) {
$orderBy = 'position';
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if ($orderBy == 'id_product' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
} elseif ($orderBy == 'manufacturer') {
$orderByPrefix = 'm';
$orderBy = 'name';
} elseif ($orderBy == 'position') {
$orderByPrefix = 'cp';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$id_supplier = intval(Tools::getValue('id_supplier'));
/* Return only the number of products */
if ($getTotal) {
$result = Db::getInstance()->getRow('
SELECT COUNT(cp.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` = ' . intval($this->id) . ($active ? ' AND p.`active` = 1' : '') . '
' . ($id_supplier ? 'AND p.id_supplier = ' . $id_supplier : '') . '');
return isset($result) ? $result['total'] : 0;
}
if (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT'))) {
$days_new_product = Configuration::get('PS_NB_DAYS_NEW_PRODUCT');
} else {
$days_new_product = 20;
}
$active_clause = $active ? ' AND p.`active` = 1' : '';
$supplier_clause = $id_supplier ? 'AND p.id_supplier = ' . $id_supplier : '';
global $currency;
$product_groups_where = 'OR ' . Tools::slqIn("pp.id_group", Tools::colArray(Group::getGroupsForCustomer(), 'id_group'));
$default_currency = Configuration::get('PS_CURRENCY_DEFAULT');
$sql = "\n SELECT\n p.id_product,\n p.id_supplier,\n p.id_manufacturer,\n p.id_category_default,\n p.id_color_default,\n p.ean13,\n p.quantity,\n p.reference,\n p.supplier_reference,\n p.location,\n p.weight,\n p.out_of_stock,\n p.quantity_discount,\n p.customizable,\n p.uploadable_files,\n p.text_fields,\n p.active,\n p.indexed,\n p.date_add,\n p.date_upd,\n p.type,\n p.schedules,\n\t\t pa.`id_product_attribute`,\n\t\t pl.`description`,\n\t\t pl.`description_short`,\n\t\t pl.`available_now`,\n\t\t pl.`available_later`,\n\t\t pl.`link_rewrite`,\n\t\t pl.`meta_description`,\n\t\t pl.`meta_keywords`,\n\t\t pl.`meta_title`,\n\t\t pl.`name`,\n\t\t i.`id_image`,\n\t\t il.`legend`,\n\t\t m.`name` AS manufacturer_name,\n\t\t tl.`name` AS tax_name,\n\t\t t.`rate`,\n\t\t cl.`name` AS category_default,\n\t\t DATEDIFF(\n p.`date_add`,\n DATE_SUB(NOW(), INTERVAL {$days_new_product} DAY)\n ) > 0 AS new,\n pp3.id_currency,\n pp3.id_group,\n pp3.id_tax,\n pp3.on_sale,\n pp3.ecotax,\n pp3.price ,\n pp3.wholesale_price,\n pp3.reduction_price,\n pp3.reduction_percent,\n pp3.reduction_from,\n pp3.reduction_to,\n pp3.min_quantity,\n pp3.id_product_price,\n (pp3.price\n\t\t -\n\t\t IF\n\t\t ( ( DATEDIFF(pp3.reduction_from, CURDATE()) <= 0\n\t\t\t AND DATEDIFF(pp3.reduction_to, CURDATE()) >=0)\n\t\t OR pp3.reduction_from = pp3.reduction_to,\n\t\t IFNULL(\n\t\t pp3.reduction_price,\n\t\t (pp3.price * pp3.reduction_percent / 100)),\n\t\t 0)\n\t\t ) AS orderprice\n\t\t FROM\n\t\t `PREFIX_category_product` cp\n\t\t LEFT JOIN `PREFIX_product` p ON p.`id_product` = cp.`id_product`\n\t\t LEFT JOIN `PREFIX_product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)\n\t\t LEFT JOIN `PREFIX_category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = {$id_lang})\n\t\t LEFT JOIN `PREFIX_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = {$id_lang})\n\t\t LEFT JOIN `PREFIX_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)\n\t\t LEFT JOIN `PREFIX_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = {$id_lang})\n\t\t LEFT JOIN\n\t\t (SELECT pp.id_product, min(abs(pp.id_currency - {$currency->id})) as currency_diff\n\t\t FROM PREFIX_product_price pp\n\t\t WHERE (pp.id_currency in ({$currency->id}, {$default_currency}) AND pp.id_group IS NULL {$product_groups_where})\n GROUP BY pp.id_product) AS pp1 ON\n pp1.id_product = p.id_product\n\t\t LEFT JOIN\n\t\t (SELECT pp.id_product, pp.id_currency, min(pp.price) as min_price\n\t\t FROM PREFIX_product_price pp\n\t\t WHERE (pp.id_group IS NULL {$product_groups_where})\n GROUP BY pp.id_product, pp.id_currency) AS pp2 ON\n pp2.id_product = p.id_product\n AND abs(pp2.id_currency - {$currency->id}) = pp1.currency_diff\n LEFT JOIN `PREFIX_product_price` pp3 ON\n pp3.id_product = p.id_product\n AND abs(pp3.id_currency - {$currency->id}) = pp1.currency_diff\n AND pp3.price = pp2.min_price\n\t\t LEFT JOIN `PREFIX_tax` t ON t.`id_tax` = pp3.`id_tax`\n\t\t LEFT JOIN `PREFIX_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = {$id_lang})\n\t\t LEFT JOIN `PREFIX_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`\n\t\t WHERE\n\t\t cp.`id_category` = {$this->id}\n\t\t {$active_clause}\n\t\t {$supplier_clause}";
$sql = str_replace('PREFIX_', _DB_PREFIX_, $sql);
if ($random === true) {
$sql .= 'ORDER BY RAND()';
$sql .= 'LIMIT 0, ' . intval($randomNumberProducts);
} else {
$sql .= 'ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . (intval($p) - 1) * intval($n) . ',' . intval($n);
}
$result = Db::getInstance()->ExecuteS($sql);
if ($orderBy == 'orderprice') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
/* Modify SQL result */
return Product::getProductsProperties($id_lang, $result);
}
protected function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
{
$lgngixep = "orderBy";
${"GLOBALS"}["xbbxtwtpju"] = "orderBy";
${"GLOBALS"}["oswfwomb"] = "orderBy";
global $cookie, $smarty;
${"GLOBALS"}["sxnzkqefjkv"] = "p";
${"GLOBALS"}["kjemucya"] = "id_seller_country";
${"GLOBALS"}["plbenbkqq"] = "orderBy";
$suwenwdcxtu = "orderByPrefix";
${${"GLOBALS"}["kjemucya"]} = (int) Tools::getValue("id_seller_country");
$ljwznznuwp = "n";
${"GLOBALS"}["vwrkkkltgp"] = "orderWay";
${"GLOBALS"}["kgoezfvpcfs"] = "orderBy";
${"GLOBALS"}["iiuykexa"] = "requiredcond";
$eqwznppjxw = "orderWay";
${"GLOBALS"}["juwlrd"] = "orderWay";
$ycvcyogg = "n";
${"GLOBALS"}["ejwtvva"] = "wheres";
${"GLOBALS"}["lkqzhjp"] = "p";
${"GLOBALS"}["vmpjiojlv"] = "orderBy";
${"GLOBALS"}["qksmteq"] = "orderBy";
if (${${"GLOBALS"}["sxnzkqefjkv"]} < 1) {
${${"GLOBALS"}["lkqzhjp"]} = 1;
}
if (${$ycvcyogg} <= 0) {
${$ljwznznuwp} = 10;
}
$yciweepkl = "orderBy";
${"GLOBALS"}["xmtrrpsmnv"] = "wheres";
$hjhvngkubb = "location_conditions";
if (empty(${${"GLOBALS"}["sqkchfbmzgt"]})) {
${${"GLOBALS"}["sqkchfbmzgt"]} = "price";
} else {
${${"GLOBALS"}["vmpjiojlv"]} = strtolower(${${"GLOBALS"}["oswfwomb"]});
}
$qsfzjtrtvb = "result";
if (empty(${$eqwznppjxw})) {
${${"GLOBALS"}["vwrkkkltgp"]} = "ASC";
}
if (${${"GLOBALS"}["xbbxtwtpju"]} == "id_product" or ${$lgngixep} == "date_add") {
${${"GLOBALS"}["holuwdvhswv"]} = "p";
} elseif (${${"GLOBALS"}["kgoezfvpcfs"]} == "name") {
${$suwenwdcxtu} = "pl";
} elseif (${${"GLOBALS"}["sqkchfbmzgt"]} == "manufacturer") {
${"GLOBALS"}["obzythrk"] = "orderByPrefix";
${"GLOBALS"}["ijxsporb"] = "orderBy";
${${"GLOBALS"}["obzythrk"]} = "m";
${${"GLOBALS"}["ijxsporb"]} = "name";
}
if (${$yciweepkl} == "price") {
${${"GLOBALS"}["qksmteq"]} = "orderprice";
}
if (!Validate::isBool(${${"GLOBALS"}["mjjjizv"]}) or !Validate::isOrderBy(${${"GLOBALS"}["plbenbkqq"]}) or !Validate::isOrderWay(${${"GLOBALS"}["juwlrd"]})) {
die(Tools::displayError());
}
${"GLOBALS"}["durjvvw"] = "joins";
${"GLOBALS"}["byhruvgfcplf"] = "orderWay";
${${"GLOBALS"}["ccpfwfk"]} = "";
$kaqgrgykux = "sql";
if (intval(Configuration::get("AGILE_MS_PRODUCT_APPROVAL")) == 1) {
${${"GLOBALS"}["iiuykexa"]} = " AND po.approved = 1 ";
}
${${"GLOBALS"}["durjvvw"]} = "";
${${"GLOBALS"}["xmtrrpsmnv"]} = "";
if (Module::isInstalled("agilesellerlistoptions")) {
require_once _PS_ROOT_DIR_ . "/modules/agilesellerlistoptions/agilesellerlistoptions.php";
${"GLOBALS"}["yyvptwoqtsy"] = "wheres";
${${"GLOBALS"}["qjbfcmajvhyd"]} = ${${"GLOBALS"}["qjbfcmajvhyd"]} . "\n LEFT JOIN `" . _DB_PREFIX_ . "seller_listoption` slb ON (p.id_product = slb.id_product AND slb.id_option = " . AgileSellerListOptions::ASLO_OPTION_LIST . ")\n ";
$ghjluom = "aslo_list_prod_id";
${${"GLOBALS"}["ppufwe"]} = intval(Configuration::get("ASLO_PROD_FOR_OPTION" . AgileSellerListOptions::ASLO_OPTION_LIST));
${${"GLOBALS"}["hylelef"]} = ${${"GLOBALS"}["yyvptwoqtsy"]} . " \n \t\t AND (slb.status = " . AgileSellerListOptions::ASLO_STATUS_IN_EFFECT . " OR IFNULL(po.id_owner,0) = 0 OR " . ${$ghjluom} . "=" . AgileSellerListOptions::ASLO_ALWAYS_FREE . ")\n ";
}
${${"GLOBALS"}["lrjoesxigovv"]} = "";
switch ($this->location_level) {
case "country":
if ((int) $this->id_location > 0) {
${$hjhvngkubb} = " AND si.id_country=" . (int) $this->id_location;
}
break;
case "state":
if ((int) $this->id_location > 0) {
${${"GLOBALS"}["lrjoesxigovv"]} = " AND si.id_state=" . (int) $this->id_location;
}
break;
case "city":
if (!empty($this->id_location)) {
${${"GLOBALS"}["lrjoesxigovv"]} = " AND sil.city='" . $this->id_location . "'";
}
break;
case "sellertype":
if (!empty($this->id_location)) {
${${"GLOBALS"}["lrjoesxigovv"]} = " AND si.id_sellertype1=" . $this->id_location;
}
break;
case "custom":
if (!empty($this->id_location)) {
if (AgileMultipleShop::SHOP_BY_CUSTOM_LANG) {
${${"GLOBALS"}["lrjoesxigovv"]} = " AND sil." . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . "='" . $this->id_location . "'";
} else {
${${"GLOBALS"}["lrjoesxigovv"]} = " AND si." . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . "='" . $this->id_location . "'";
}
}
break;
}
if (${${"GLOBALS"}["jizkwekrhsnv"]}) {
$nlecsweh = "wheres";
${"GLOBALS"}["yngbfuljr"] = "location_conditions";
$dhfiohgz = "result";
$rgelmcniqy = "requiredcond";
$ccechvvirsfn = "result";
$htkduiwgscwl = "sql";
${$htkduiwgscwl} = "\n\t\t\tSELECT COUNT(po.`id_product`) AS total\n\t\t\tFROM `" . _DB_PREFIX_ . "product` p\n\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product_owner` po ON p.`id_product` = po.`id_product`\n\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo` si ON si.`id_seller` = po.`id_owner`\n\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = " . $cookie->id_lang . "\n\t\t\t" . ${${"GLOBALS"}["qjbfcmajvhyd"]} . "\n\t\t\tWHERE p.active=1 \n\t\t\t\t" . ${${"GLOBALS"}["yngbfuljr"]} . "\n\t\t\t " . (${${"GLOBALS"}["mjjjizv"]} ? " AND p.`active` = 1" : "") . "\n\t\t\t\t" . ${$rgelmcniqy} . "\n\t\t\t\t" . ${$nlecsweh} . "\n\t\t\t ";
${$dhfiohgz} = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow(${${"GLOBALS"}["zrrhonhgudxt"]});
$ygrlibnhobc = "result";
return isset(${$ygrlibnhobc}) ? ${$ccechvvirsfn}["total"] : 0;
}
${${"GLOBALS"}["zrrhonhgudxt"]} = "\n\t\t SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL " . (Validate::isUnsignedInt(Configuration::get("PS_NB_DAYS_NEW_PRODUCT")) ? Configuration::get("PS_NB_DAYS_NEW_PRODUCT") : 20) . " DAY)) > 0 AS new,\n\t\t\t (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice\n\t\t FROM `" . _DB_PREFIX_ . "product_owner` po\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo` si ON si.`id_seller` = po.`id_owner`\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = " . $cookie->id_lang . "\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "product` p ON p.`id_product` = po.`id_product`\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`\n\t\t AND tr.`id_country` = " . (int) (_PS_VERSION_ > "1.5" ? Context::getContext()->country->id : Country::getDefaultCountryId()) . "\n\t \t AND tr.`id_state` = 0)\n\t LEFT JOIN `" . _DB_PREFIX_ . "tax` t ON (t.`id_tax` = tr.`id_tax`)\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`\n\t\t\t\t" . ${${"GLOBALS"}["qjbfcmajvhyd"]} . "\n\t\t WHERE p.active=1 \n\t\t\t\t\t" . ${${"GLOBALS"}["lrjoesxigovv"]} . "\n \t\t\t" . ${${"GLOBALS"}["ccpfwfk"]} . "\n\t\t\t\t\t" . ${${"GLOBALS"}["ejwtvva"]} . "\n\t\t ";
if (${${"GLOBALS"}["urnpvrvfs"]} === true) {
${"GLOBALS"}["xeqntuoo"] = "sql";
$igirpiqeuqmm = "sql";
${${"GLOBALS"}["xeqntuoo"]} .= " ORDER BY RAND()";
${$igirpiqeuqmm} .= " LIMIT 0, " . (int) ${${"GLOBALS"}["qbznfx"]};
} else {
$mwoxesiw = "orderByPrefix";
${"GLOBALS"}["mokqqxt"] = "n";
$tydfnpg = "n";
${${"GLOBALS"}["zrrhonhgudxt"]} .= " ORDER BY " . (isset(${${"GLOBALS"}["holuwdvhswv"]}) ? ${$mwoxesiw} . "." : "") . "`" . pSQL(${${"GLOBALS"}["sqkchfbmzgt"]}) . "` " . pSQL(${${"GLOBALS"}["cfsjhhbwbqgs"]}) . "\n\t\t\tLIMIT " . ((int) ${${"GLOBALS"}["vtcbvfsc"]} - 1) * (int) ${${"GLOBALS"}["mokqqxt"]} . "," . (int) ${$tydfnpg};
}
${$qsfzjtrtvb} = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(${$kaqgrgykux});
if (${${"GLOBALS"}["sqkchfbmzgt"]} == "orderprice") {
Tools::orderbyPrice(${${"GLOBALS"}["hrmrbao"]}, ${${"GLOBALS"}["byhruvgfcplf"]});
}
if (!${${"GLOBALS"}["hrmrbao"]}) {
return false;
}
return Product::getProductsProperties(${${"GLOBALS"}["ytuoqlidml"]}, ${${"GLOBALS"}["hrmrbao"]});
}
protected function _getProducts($categoryIds = array(), $on_condition = 'all', $on_sale = 2, $on_new = 2, $on_discount = 2, $id_lang, $p, $n, $order_by = null, $order_way = null, $beginning = null, $ending = null, $deal = false, $get_total = false, $active = true, $random = false, $random_number_products = 1, Context $context = null)
{
if (!$categoryIds) {
return array();
}
$where = "";
if ($on_condition != 'all') {
$where .= " AND p.condition = '" . $on_condition . "' ";
}
if ($on_sale != 2) {
$where .= " AND p.on_sale = '" . $on_sale . "' ";
}
Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? $PS_NB_DAYS_NEW_PRODUCT = (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : ($PS_NB_DAYS_NEW_PRODUCT = 20);
if ($on_new == 0) {
$where .= " AND product_shop.`date_add` <= '" . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . "' ";
} elseif ($on_new == 1) {
$where .= " AND product_shop.`date_add` > '" . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . "' ";
}
$ids_product = '';
if ($on_discount == 0) {
$current_date = date('Y-m-d H:i:s');
$product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal);
if ($product_reductions) {
$ids_product = ' AND (';
foreach ($product_reductions as $product_reduction) {
$ids_product .= '( product_shop.`id_product` != ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' OR product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') AND';
}
$ids_product = rtrim($ids_product, 'AND') . ')';
}
} elseif ($on_discount == 1) {
$current_date = date('Y-m-d H:i:s');
$product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal);
if ($product_reductions) {
$ids_product = ' AND (';
foreach ($product_reductions as $product_reduction) {
$ids_product .= '( product_shop.`id_product` = ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' AND product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') OR';
}
$ids_product = rtrim($ids_product, 'OR') . ')';
} else {
if ($deal == true) {
return array();
}
}
} else {
if ($order_by == 'discount') {
$current_date = date('Y-m-d H:i:s');
$product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal);
if ($product_reductions) {
$ids_product = ' AND (';
foreach ($product_reductions as $product_reduction) {
$ids_product .= '( product_shop.`id_product` = ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' AND product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') OR';
}
$ids_product = rtrim($ids_product, 'OR') . ')';
}
}
}
if ($ids_product) {
$where .= $ids_product;
}
if (!$context) {
$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';
} else {
$order_by = strtolower($order_by);
}
if (empty($order_way)) {
$order_way = 'ASC';
}
$order_by_prefix = false;
$addJoin = '';
$addSelect = '';
if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd') {
$order_by_prefix = 'p';
} elseif ($order_by == 'name') {
$order_by_prefix = 'pl';
} elseif ($order_by == 'manufacturer' || $order_by == 'manufacturer_name') {
$order_by_prefix = 'm';
$order_by = 'name';
} elseif ($order_by == 'position') {
$order_by_prefix = 'cp';
} elseif ($order_by == 'discount') {
$order_by_prefix = 'sp';
$order_by = 'reduction';
$addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.`id_product` = sp.`id_product` ';
$addSelect = ', sp.reduction, sp.`from`, sp.`to`';
} elseif ($order_by == 'review') {
$order_by_prefix = '';
$order_by = 'total_review';
$addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pr ON pr.`id_product` = p.`id_product` ';
$addSelect = ', COUNT(pr.grade) as total_review';
} elseif ($order_by == 'view') {
$order_by_prefix = '';
$order_by = 'total_view';
$addJoin = ' LEFT JOIN ' . _DB_PREFIX_ . 'simplecategory_product_view as pv ON pv.`product_id` = p.`id_product` ';
$addSelect = ', pv.total as total_view';
} elseif ($order_by == 'rate') {
$order_by_prefix = '';
$order_by = 'total_avg';
$addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pr ON pr.`id_product` = p.`id_product` ';
$addSelect = ', (SUM(pr.`grade`) / COUNT(pr.`grade`)) AS total_avg';
} elseif ($order_by == 'seller') {
$order_by_prefix = '';
$order_by = 'sales';
$addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_sale` ps ON ps.`id_product` = p.`id_product` ';
$addSelect = ', ps.`quantity` AS sales';
}
if ($order_by != 'reduction' && $on_discount != 2) {
$addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.`id_product` = sp.`id_product` ';
$addSelect = ', sp.reduction, sp.`from`, sp.`to`';
}
if ($order_by == 'price') {
$order_by = 'orderprice';
}
if (!Validate::isBool($active) || !Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
die(Tools::displayError());
}
$id_supplier = (int) Tools::getValue('id_supplier');
if ($get_total) {
$sql = 'SELECT COUNT(cp.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . ' ' . $addJoin . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` IN (' . implode(', ', $categoryIds) . ') ' . $where . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . ($ids_product ? $ids_product : '') . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : '');
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
$sql = 'SELECT DISTINCT
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`, 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('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . '" as `new`,
product_shop.`on_sale`, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, product_shop.price AS orderprice ' . $addSelect . '
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p
ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . $addJoin . (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_ . 'category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i
ON (i.`id_product` = p.`id_product`) ' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . '
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_ . 'manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
AND cp.`id_category` IN (' . implode(', ', $categoryIds) . ') ' . $where . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '') . ' GROUP BY product_shop.id_product';
if ($random === true) {
$sql .= ' ORDER BY RAND() LIMIT ' . (int) $random_number_products;
} else {
$sql .= ' ORDER BY ' . (!empty($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . bqSQL($order_by) . '` ' . pSQL($order_way) . ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if ($order_by == 'orderprice') {
Tools::orderbyPrice($result, $order_way);
}
if (!$result) {
return array();
}
return Product::getProductsProperties($id_lang, $result);
}
public function getProductByFilters($selected_filters = array())
{
global $cookie;
if (!empty($this->products)) {
return $this->products;
}
$home_category = Configuration::get('PS_HOME_CATEGORY');
/* If the current category isn't defined or if it's homepage, we have nothing to display */
$id_parent = (int) Tools::getValue('id_category', Tools::getValue('id_category_layered', $home_category));
if ($id_parent == $home_category) {
return false;
}
$alias_where = 'p';
if (version_compare(_PS_VERSION_, '1.5', '>')) {
$alias_where = 'product_shop';
}
$query_filters_where = ' AND ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")';
$query_filters_from = '';
$parent = new Category((int) $id_parent);
foreach ($selected_filters as $key => $filter_values) {
if (!count($filter_values)) {
continue;
}
preg_match('/^(.*[^_0-9])/', $key, $res);
$key = $res[1];
switch ($key) {
case 'id_feature':
$sub_queries = array();
foreach ($filter_values as $filter_value) {
$filter_value_array = explode('_', $filter_value);
if (!isset($sub_queries[$filter_value_array[0]])) {
$sub_queries[$filter_value_array[0]] = array();
}
$sub_queries[$filter_value_array[0]][] = 'fp.`id_feature_value` = ' . (int) $filter_value_array[1];
}
foreach ($sub_queries as $sub_query) {
$query_filters_where .= ' AND p.id_product IN (SELECT `id_product` FROM `' . _DB_PREFIX_ . 'feature_product` fp WHERE ';
$query_filters_where .= implode(' OR ', $sub_query) . ') ';
}
break;
case 'id_attribute_group':
$sub_queries = array();
foreach ($filter_values as $filter_value) {
$filter_value_array = explode('_', $filter_value);
if (!isset($sub_queries[$filter_value_array[0]])) {
$sub_queries[$filter_value_array[0]] = array();
}
$sub_queries[$filter_value_array[0]][] = 'pac.`id_attribute` = ' . (int) $filter_value_array[1];
}
foreach ($sub_queries as $sub_query) {
$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
FROM `' . _DB_PREFIX_ . 'product_attribute_combination` pac
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
ON (pa.`id_product_attribute` = pac.`id_product_attribute`)' . Shop::addSqlAssociation('product_attribute', 'pa') . '
WHERE ' . implode(' OR ', $sub_query) . ') ';
}
break;
case 'category':
$query_filters_where .= ' AND p.id_product IN (SELECT id_product FROM ' . _DB_PREFIX_ . 'category_product cp WHERE ';
foreach ($selected_filters['category'] as $id_category) {
$query_filters_where .= 'cp.`id_category` = ' . (int) $id_category . ' OR ';
}
$query_filters_where = rtrim($query_filters_where, 'OR ') . ')';
break;
case 'quantity':
if (count($selected_filters['quantity']) == 2) {
break;
}
$query_filters_where .= ' AND sa.quantity ' . (!$selected_filters['quantity'][0] ? '<=' : '>') . ' 0 ';
$query_filters_from .= 'LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sa ON (sa.id_product = p.id_product ' . StockAvailable::addSqlShopRestriction(null, null, 'sa') . ') ';
break;
case 'manufacturer':
$query_filters_where .= ' AND p.id_manufacturer IN (' . implode($selected_filters['manufacturer'], ',') . ')';
break;
case 'condition':
if (count($selected_filters['condition']) == 3) {
break;
}
$query_filters_where .= ' AND ' . $alias_where . '.condition IN (';
foreach ($selected_filters['condition'] as $cond) {
$query_filters_where .= '\'' . pSQL($cond) . '\',';
}
$query_filters_where = rtrim($query_filters_where, ',') . ')';
break;
case 'weight':
if ($selected_filters['weight'][0] != 0 || $selected_filters['weight'][1] != 0) {
$query_filters_where .= ' AND p.`weight` BETWEEN ' . (double) ($selected_filters['weight'][0] - 0.001) . ' AND ' . (double) ($selected_filters['weight'][1] + 0.001);
}
break;
case 'price':
if (isset($selected_filters['price'])) {
if ($selected_filters['price'][0] !== '' || $selected_filters['price'][1] !== '') {
$price_filter = array();
$price_filter['min'] = (double) $selected_filters['price'][0];
$price_filter['max'] = (double) $selected_filters['price'][1];
}
} else {
$price_filter = false;
}
break;
}
}
$context = Context::getContext();
$id_currency = (int) $context->currency->id;
$price_filter_query_in = '';
// All products with price range between price filters limits
$price_filter_query_out = '';
// All products with a price filters limit on it price range
if (isset($price_filter) && $price_filter) {
$price_filter_query_in = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi
ON
(
psi.price_min <= ' . (int) $price_filter['max'] . '
AND psi.price_max >= ' . (int) $price_filter['min'] . '
AND psi.`id_product` = p.`id_product`
AND psi.`id_shop` = ' . (int) $context->shop->id . '
AND psi.`id_currency` = ' . $id_currency . '
)';
$price_filter_query_out = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi
ON
((psi.price_min < ' . (int) $price_filter['min'] . ' AND psi.price_max > ' . (int) $price_filter['min'] . ')
OR
(psi.price_max > ' . (int) $price_filter['max'] . ' AND psi.price_min < ' . (int) $price_filter['max'] . '))
AND psi.`id_product` = p.`id_product`
AND psi.`id_shop` = ' . (int) $context->shop->id . '
AND psi.`id_currency` = ' . $id_currency;
}
$query_filters_from .= Shop::addSqlAssociation('product', 'p');
Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DROP TEMPORARY TABLE IF EXISTS ' . _DB_PREFIX_ . 'cat_filter_restriction', false);
if (empty($selected_filters['category'])) {
/* Create the table which contains all the id_product in a cat or a tree */
Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('CREATE TEMPORARY TABLE ' . _DB_PREFIX_ . 'cat_filter_restriction ENGINE=MEMORY
SELECT cp.id_product, MIN(cp.position) position 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)
JOIN `' . _DB_PREFIX_ . 'product` p USING (id_product)
' . $price_filter_query_in . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . '
GROUP BY cp.id_product ORDER BY position, id_product', false);
} else {
$categories = array_map('intval', $selected_filters['category']);
Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('CREATE TEMPORARY TABLE ' . _DB_PREFIX_ . 'cat_filter_restriction ENGINE=MEMORY
SELECT cp.id_product, MIN(cp.position) position FROM ' . _DB_PREFIX_ . 'category_product cp
JOIN `' . _DB_PREFIX_ . 'product` p USING (id_product)
' . $price_filter_query_in . '
' . $query_filters_from . '
WHERE cp.`id_category` IN (' . implode(',', $categories) . ') ' . $query_filters_where . '
GROUP BY cp.id_product ORDER BY position, id_product', false);
}
Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('ALTER TABLE ' . _DB_PREFIX_ . 'cat_filter_restriction ADD PRIMARY KEY (id_product), ADD KEY (position, id_product) USING BTREE', false);
if (isset($price_filter) && $price_filter) {
static $ps_layered_filter_price_usetax = null;
static $ps_layered_filter_price_rounding = null;
if ($ps_layered_filter_price_usetax === null) {
$ps_layered_filter_price_usetax = Configuration::get('PS_LAYERED_FILTER_PRICE_USETAX');
}
if ($ps_layered_filter_price_rounding === null) {
$ps_layered_filter_price_rounding = Configuration::get('PS_LAYERED_FILTER_PRICE_ROUNDING');
}
if (empty($selected_filters['category'])) {
$all_products_out = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p JOIN ' . _DB_PREFIX_ . 'category_product cp USING (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)
' . $price_filter_query_out . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . ' GROUP BY cp.id_product');
} else {
$all_products_out = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p JOIN ' . _DB_PREFIX_ . 'category_product cp USING (id_product)
' . $price_filter_query_out . '
' . $query_filters_from . '
WHERE cp.`id_category` IN (' . implode(',', $categories) . ') ' . $query_filters_where . ' GROUP BY cp.id_product');
}
/* for this case, price could be out of range, so we need to compute the real price */
foreach ($all_products_out as $product) {
$price = Product::getPriceStatic($product['id_product'], $ps_layered_filter_price_usetax);
if ($ps_layered_filter_price_rounding) {
$price = (int) $price;
}
if ($price < $price_filter['min'] || $price > $price_filter['max']) {
// out of range price, exclude the product
$product_id_delete_list[] = (int) $product['id_product'];
}
}
if (!empty($product_id_delete_list)) {
Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DELETE FROM ' . _DB_PREFIX_ . 'cat_filter_restriction WHERE id_product IN (' . implode(',', $product_id_delete_list) . ')');
}
}
$this->nbr_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'cat_filter_restriction');
if ($this->nbr_products == 0) {
$this->products = array();
} else {
$n = (int) Tools::getValue('n', Configuration::get('PS_PRODUCTS_PER_PAGE'));
$nb_day_new_product = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true) {
$this->products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT
p.*,
' . ($alias_where == 'p' ? '' : 'product_shop.*,') . '
' . $alias_where . '.id_category_default,
pl.*,
image_shop.`id_image` id_image,
il.legend,
m.name manufacturer_name,
' . (Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '') . '
DATEDIFF(' . $alias_where . '.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00", INTERVAL ' . (int) $nb_day_new_product . ' DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity' . (Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '') . '
FROM ' . _DB_PREFIX_ . 'cat_filter_restriction cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')' : '') . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . Shop::addSqlRestrictionOnLang('pl') . ' AND pl.id_lang = ' . (int) $cookie->id_lang . ')
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) $cookie->id_lang . ')
LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
' . Product::sqlStock('p', 0) . '
WHERE ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")
ORDER BY ' . Tools::getProductsOrder('by', Tools::getValue('orderby'), true) . ' ' . Tools::getProductsOrder('way', Tools::getValue('orderway')) . ' , cp.id_product' . ' LIMIT ' . (((int) $this->page - 1) * $n . ',' . $n));
} else {
$this->products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT
p.*,
' . ($alias_where == 'p' ? '' : 'product_shop.*,') . '
' . $alias_where . '.id_category_default,
pl.*,
MAX(image_shop.`id_image`) id_image,
il.legend,
m.name manufacturer_name,
' . (Combination::isFeatureActive() ? 'MAX(product_attribute_shop.id_product_attribute) id_product_attribute,' : '') . '
DATEDIFF(' . $alias_where . '.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00", INTERVAL ' . (int) $nb_day_new_product . ' DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . '
FROM ' . _DB_PREFIX_ . 'cat_filter_restriction cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
' . 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 AND product_attribute_shop.id_shop=' . (int) $context->shop->id) : '') . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . Shop::addSqlRestrictionOnLang('pl') . ' AND pl.id_lang = ' . (int) $cookie->id_lang . ')
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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
' . Product::sqlStock('p', 0) . '
WHERE ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")
GROUP BY product_shop.id_product
ORDER BY ' . Tools::getProductsOrder('by', Tools::getValue('orderby'), true) . ' ' . Tools::getProductsOrder('way', Tools::getValue('orderway')) . ' , cp.id_product' . ' LIMIT ' . (((int) $this->page - 1) * $n . ',' . $n));
}
}
if (Tools::getProductsOrder('by', Tools::getValue('orderby'), true) == 'p.price') {
Tools::orderbyPrice($this->products, Tools::getProductsOrder('way', Tools::getValue('orderway')));
}
return $this->products;
}
function performSearch(Smarty $smarty, $page = 1, $perPage = 10, $orderBy = 'newest', $orderWay = 'DESC')
{
global $cookie;
$page = (int) $page;
$perPage = (int) $perPage;
if ($orderBy == 'newest') {
$orderBy = 'date_add';
}
$orderByPrefix = $orderBy == 'id_product' || $orderBy == 'date_add' ? 'p' : ($orderBy == 'name' ? 'pl' : ($orderBy == 'manufacturer' ? 'm' : ($orderBy == 'position' ? 'cp' : '')));
if ($orderBy == 'manufacturer') {
$orderBy = 'name';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
$sql = '
SELECT DISTINCT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`,
pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`,
cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.price - IF((DATEDIFF(reduction_from, CURDATE()) <= 0 AND DATEDIFF(reduction_to, CURDATE()) >=0) OR reduction_from = reduction_to, IFNULL(reduction_price, (p.price * reduction_percent / 100)),0)) AS orderprice
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE p.`id_product` IN(' . implode(',', count($this->productAssocIds()) > 0 ? $this->productAssocIds() : $this->productIds) . ')
AND p.`active` = 1 ORDER BY ' . (!empty($orderByPrefix) ? $orderByPrefix . '.' : '') . $orderBy . ' ' . $orderWay;
if ($fullResult = $this->db->ExecuteS($sql)) {
$limitedResult = array_slice($fullResult, ($page - 1) * $perPage, $perPage);
if ($orderBy == 'orderprice') {
Tools::orderbyPrice($limitedResult, $orderWay);
}
ob_start();
print $this->view('product-list', array('products' => Product::getProductsProperties($cookie->id_lang, $limitedResult)), $smarty, $this->name);
$products = ob_get_clean();
$filterCount = count($this->productAssocIds()) > 0 ? count($this->productAssocIds()) : count($this->productIds);
return array('products' => $products, 'filterCount' => $filterCount, 'disable' => $this->disableFilters($fullResult), 'enable' => $this->disableFilters($fullResult, TRUE));
} else {
return array('products' => '', 'filterCount' => '', 'disable' => '', 'enable' => '');
}
}
public static function getProducts($id_manufacturer, $id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true)
{
global $cookie;
if ($p < 1) {
$p = 1;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'name';
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
/* Return only the number of products */
if ($getTotal) {
$sql = '
SELECT p.`id_product`
FROM `' . _DB_PREFIX_ . 'product` p
WHERE p.id_manufacturer = ' . intval($id_manufacturer) . ($active ? ' AND p.`active` = 1' : '') . '
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` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . '
)';
$result = Db::getInstance()->ExecuteS($sql);
return intval(sizeof($result));
}
$sql = '
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE p.`id_manufacturer` = ' . intval($id_manufacturer) . ($active ? ' AND p.`active` = 1' : '') . '
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` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . '
)
ORDER BY ' . ($orderBy == 'id_product' ? 'p.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . (intval($p) - 1) * intval($n) . ',' . intval($n);
$result = Db::getInstance()->ExecuteS($sql);
if (!$result) {
return false;
}
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
return Product::getProductsProperties($id_lang, $result);
}
public function frontGetProductByIds($ids = array(), $id_lang, $order_by = null, $order_way = null, $active = true, Context $context = null)
{
if (!$ids) {
return array();
}
if (!$context) {
$context = Context::getContext();
}
//if($check_access && !$this->frontCheckAccess($context->customer->id)) return array();
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
if (empty($order_by)) {
$order_by = 'position';
} else {
$order_by = strtolower($order_by);
}
if (empty($order_way)) {
$order_way = 'ASC';
}
$order_by_prefix = false;
if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd') {
$order_by_prefix = 'p';
} elseif ($order_by == 'name') {
$order_by_prefix = 'pl';
} elseif ($order_by == 'manufacturer' || $order_by == 'manufacturer_name') {
$order_by_prefix = 'm';
$order_by = 'name';
} elseif ($order_by == 'position') {
$order_by_prefix = 'cp';
}
if ($order_by == 'price') {
$order_by = 'orderprice';
}
if (!Validate::isBool($active) || !Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
die(Tools::displayError());
}
$id_supplier = (int) Tools::getValue('id_supplier');
$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`, 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`, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, product_shop.price AS orderprice
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p
ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . (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_ . 'category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i
ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . '
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_ . 'manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
AND product_shop.id_product IN (' . implode(', ', $ids) . ') ' . ' AND product_shop.`active` = 1' . ' AND product_shop.`visibility` IN ("both", "catalog")' . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '') . ' GROUP BY product_shop.id_product';
$sql .= ' ORDER BY ' . (!empty($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . bqSQL($order_by) . '` ' . pSQL($order_way);
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if ($order_by == 'orderprice') {
Tools::orderbyPrice($result, $order_way);
}
if (!$result) {
return array();
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getBestSales($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;
if (empty($order_by) || $order_by == 'position' || ($order_by = 'price')) {
$order_by = 'sales';
}
if (empty($order_way)) {
$order_way = 'DESC';
}
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
$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,
image_shop.`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.`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 . '
)
AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))
ORDER BY `' . 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);
}
/**
* Return current category products
*
* @param integer $id_lang Language ID
* @param integer $p Page number
* @param integer $n Number of products per page
* @param boolean $getTotal return the number of results instead of the results themself
* @param boolean $active return only active products
* @param boolean $random active a random filter for returned products
* @param int $randomNumberProducts number of products to return if random is activated
* @param boolean $checkAccess set to false to return all products (even if customer hasn't access)
* @return mixed Products or number of products
*/
public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1, $checkAccess = true)
{
global $cookie;
if (!$checkAccess or !$this->checkAccess($cookie->id_customer)) {
return false;
}
if ($p < 1) {
$p = 1;
}
if (empty($orderBy)) {
$orderBy = 'position';
} else {
/* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
$orderBy = strtolower($orderBy);
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if ($orderBy == 'id_product' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
} elseif ($orderBy == 'manufacturer') {
$orderByPrefix = 'm';
$orderBy = 'name';
} elseif ($orderBy == 'position') {
$orderByPrefix = 'cp';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$id_supplier = (int) Tools::getValue('id_supplier');
/* Return only the number of products */
if ($getTotal) {
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT COUNT(cp.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` = ' . (int) $this->id . ($active ? ' AND p.`active` = 1' : '') . '
' . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : ''));
return isset($result) ? $result['total'] : 0;
}
$sql = '
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) Country::getDefaultCountryId() . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE cp.`id_category` = ' . (int) $this->id . ($active ? ' AND p.`active` = 1' : '') . '
' . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : '');
if ($random === true) {
$sql .= ' ORDER BY RAND()';
$sql .= ' LIMIT 0, ' . (int) $randomNumberProducts;
} else {
$sql .= ' ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
if ($orderBy == 'orderprice') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
/* Modify SQL result */
return Product::getProductsProperties($id_lang, $result);
}
public static function getProducts($id_supplier, $id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true)
{
global $cookie;
if ($p < 1) {
$p = 1;
}
if (empty($orderBy) or $orderBy == 'position') {
$orderBy = 'name';
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
/* Return only the number of products */
if ($getTotal) {
$sql = '
SELECT p.`id_product`
FROM `' . _DB_PREFIX_ . 'product` p
WHERE p.id_supplier = ' . intval($id_supplier) . ($active ? ' AND p.`active` = 1' : '') . '
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` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . '
)';
$result = Db::getInstance()->ExecuteS($sql);
return intval(sizeof($result));
}
$sql = '
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, s.`name` AS supplier_name, tl.`name` AS tax_name, t.`rate`
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'supplier` s ON s.`id_supplier` = p.`id_supplier`
WHERE p.`id_supplier` = ' . intval($id_supplier) . ($active ? ' AND p.`active` = 1' : '') . '
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` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . '
)
ORDER BY ' . ($orderBy == 'id_product' ? 'p.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . (intval($p) - 1) * intval($n) . ',' . intval($n);
$result = Db::getInstance()->ExecuteS($sql);
if (!$result) {
return false;
}
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
return Product::getProductsProperties($id_lang, $result);
}
/**
* Return current category products
*
* @param integer $id_lang Language ID
* @param integer $p Page number
* @param integer $n Number of products per page
* @param boolean $get_total return the number of results instead of the results themself
* @param boolean $active return only active products
* @param boolean $random active a random filter for returned products
* @param int $random_number_products number of products to return if random is activated
* @param boolean $check_access set to false to return all products (even if customer hasn't access)
* @return mixed Products or number of products
*/
public function getProducts($id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
if ($check_access && !$this->checkAccess($context->customer->id)) {
return false;
}
$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';
} else {
/* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
$order_by = strtolower($order_by);
}
if (empty($order_way)) {
$order_way = 'ASC';
}
if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd') {
$order_by_prefix = 'p';
} elseif ($order_by == 'name') {
$order_by_prefix = 'pl';
} elseif ($order_by == 'manufacturer') {
$order_by_prefix = 'm';
$order_by = 'name';
} elseif ($order_by == 'position') {
$order_by_prefix = 'cp';
}
if ($order_by == 'price') {
$order_by = 'orderprice';
}
if (!Validate::isBool($active) || !Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
die(Tools::displayError());
}
$id_supplier = (int) Tools::getValue('id_supplier');
/* Return only the number of products */
if ($get_total) {
$sql = 'SELECT COUNT(cp.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` = ' . (int) $this->id . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : '');
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`,
pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`,
il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default,
DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . '
DAY)) > 0 AS new,
(product_shop.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p
ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
ON (p.`id_product` = pa.`id_product`)
' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . '
' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i
ON (i.`id_product` = p.`id_product`
AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il
ON (i.`id_image` = il.`id_image`
AND il.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr
ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) $context->country->id . '
AND tr.`id_state` = 0
AND tr.`zipcode_from` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t
ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl
ON (t.`id_tax` = tl.`id_tax`
AND tl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
AND ((product_attribute_shop.id_product_attribute IS NOT NULL OR pa.id_product_attribute IS NULL)
OR (product_attribute_shop.id_product_attribute IS NULL AND pa.default_on=1))
AND cp.`id_category` = ' . (int) $this->id . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '');
if ($random === true) {
$sql .= ' ORDER BY RAND()';
$sql .= ' LIMIT 0, ' . (int) $random_number_products;
} else {
$sql .= ' ORDER BY ' . (isset($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if ($order_by == 'orderprice') {
Tools::orderbyPrice($result, $order_way);
}
if (!$result) {
return false;
}
/* Modify SQL result */
return Product::getProductsProperties($id_lang, $result);
}
protected function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
{
global $cookie, $smarty;
$id_seller = $this->seller->id;
if ($p < 1) {
$p = 1;
}
if ($n <= 0) {
$n = 10;
}
if (empty($orderBy)) {
$orderBy = 'price';
} else {
$orderBy = strtolower($orderBy);
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if ($orderBy == 'id_product' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
} elseif ($orderBy == 'manufacturer') {
$orderByPrefix = 'm';
$orderBy = 'name';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p", true);
if (Module::isInstalled('agilesellerlistoptions') && empty($orderby)) {
$orderby = 'position2';
}
if ($getTotal) {
$sql = '
SELECT COUNT(po.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_owner` po ON p.`id_product` = po.`id_product`
' . $agile_sql_parts['joins'] . '
WHERE p.id_category_default>0 AND po.`id_owner` = ' . (int) $this->seller->id . '
' . ($active ? ' AND p.`active` = 1' : '') . '
' . $agile_sql_parts['wheres'] . '
';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return isset($result) ? $result['total'] : 0;
}
$sql = '
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
' . $agile_sql_parts['selects'] . '
FROM `' . _DB_PREFIX_ . 'product_owner` po
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = po.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) (_PS_VERSION_ > '1.5' ? Context::getContext()->country->id : Country::getDefaultCountryId()) . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
' . $agile_sql_parts['joins'] . '
WHERE p.id_category_default>0 AND po.`id_owner` = ' . $id_seller . '
' . ($active ? ' AND p.`active` = 1' : '') . '
' . $agile_sql_parts['wheres'] . '
';
if ($random === true) {
$sql .= ' ORDER BY RAND()';
$sql .= ' LIMIT 0, ' . (int) $randomNumberProducts;
} else {
$sql .= ' ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
if ($orderBy == 'orderprice') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
$finalResults = Product::getProductsProperties($id_lang, $result);
$finalResults = AgileSellerManager::prepareSellerRattingInfo($finalResults);
return $finalResults;
}
public static function getProducts($id_manufacturer, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $active_category = true, Context $context = null)
{
/*
* EU-Legal
* get standard shipping time from database pl.*
*/
if (!$context) {
$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());
}
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
/* Return only the number of products */
if ($get_total) {
$sql = '
SELECT p.`id_product`
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
WHERE p.id_manufacturer = ' . (int) $id_manufacturer . ($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_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp 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' : '') . '
WHERE cg.`id_group` ' . $sql_groups . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
return (int) count($result);
}
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 ($order_by == 'name') {
$alias = 'pl.';
} elseif ($order_by == 'manufacturer_name') {
$order_by = 'name';
$alias = 'm.';
} elseif ($order_by == 'quantity') {
$alias = 'stock.';
} else {
$alias = 'p.';
}
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute
, 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`, pl.`delivery_now`, pl.`delivery_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,
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' . (Combination::isFeatureActive() ? ',MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ' FROM `' . _DB_PREFIX_ . 'product` p
' . 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') : '') . '
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`)
' . 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` ' . $sql_groups . ')';
}
if ($active_category) {
$sql .= 'JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1';
}
}
$sql .= '
WHERE p.`id_manufacturer` = ' . (int) $id_manufacturer . '
' . ($active ? ' AND product_shop.`active` = 1' : '') . '
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
GROUP BY product_shop.id_product
ORDER BY ' . $alias . '`' . bqSQL($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);
}
/**
* Get prices drop
*
* @param integer $id_lang Language id
* @param integer $pageNumber Start from (optional)
* @param integer $nbProducts Number of products to return (optional)
* @param boolean $count Only in order to get total number (optional)
* @return array Prices drop
*/
public static function getPricesDrop($id_lang, $pageNumber = 0, $nbProducts = 10, $count = false, $orderBy = NULL, $orderWay = NULL, $beginning = false, $ending = false)
{
global $link, $cookie;
if (!Validate::isBool($count)) {
die(Tools::displayError());
}
if ($pageNumber < 0) {
$pageNumber = 0;
}
if ($nbProducts < 1) {
$nbProducts = 10;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'myprice';
}
if (empty($orderWay)) {
$orderWay = 'DESC';
}
if ($orderBy == 'id_product' or $orderBy == 'price' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
}
if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$currentDate = date('Y-m-d H:m:i');
if ($count) {
$sql = '
SELECT COUNT(DISTINCT p.`id_product`) AS nb
FROM `' . _DB_PREFIX_ . 'product` p
WHERE p.`active` = 1
AND (`reduction_price` > 0 OR `reduction_percent` > 0)
' . ((!$beginning and !$ending) ? ' AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= \'' . pSQL($currentDate) . '\' AND `reduction_to` >= \'' . pSQL($currentDate) . '\'))' : ($beginning ? 'AND `reduction_from` <= \'' . pSQL($beginning) . '\'' : '') . ($ending ? 'AND `reduction_to` >= \'' . pSQL($ending) . '\'' : '')) . '
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` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . '
)';
$result = Db::getInstance()->getRow($sql);
return intval($result['nb']);
}
$sql = '
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`, i.`id_image`, il.`legend`, t.`rate`, (p.`reduction_price` + (p.`reduction_percent` * p.`price`)) AS myprice, m.`name` AS manufacturer_name
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = p.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
' . ((!$beginning and !$ending) ? 'AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= \'' . pSQL($currentDate) . '\' AND `reduction_to` >= \'' . pSQL($currentDate) . '\'))' : ($beginning ? 'AND `reduction_from` <= \'' . pSQL($beginning) . '\'' : '') . ($ending ? 'AND `reduction_to` >= \'' . pSQL($ending) . '\'' : '')) . '
AND p.`active` = 1
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` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . '
)
ORDER BY ' . (isset($orderByPrefix) ? pSQL($orderByPrefix) . '.' : '') . '`' . pSQL($orderBy) . '`' . ' ' . pSQL($orderWay) . '
LIMIT ' . intval($pageNumber * $nbProducts) . ', ' . intval($nbProducts);
$result = Db::getInstance()->ExecuteS($sql);
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getProducts($id_supplier, $id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $active_category = true)
{
if ($p < 1) {
$p = 1;
}
if (empty($orderBy) or $orderBy == 'position') {
$orderBy = 'name';
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$groups = FrontController::getCurrentCustomerGroups();
$sqlGroups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
/* Return only the number of products */
if ($getTotal) {
$sql = '
SELECT p.`id_product`
FROM `' . _DB_PREFIX_ . 'product` p
WHERE p.id_supplier = ' . (int) $id_supplier . ($active ? ' AND p.`active` = 1' : '') . '
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`)' . ($active_category ? ' INNER JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1' : '') . '
WHERE cg.`id_group` ' . $sqlGroups . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
return (int) sizeof($result);
}
$sql = '
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, s.`name` AS supplier_name, tl.`name` AS tax_name, t.`rate`, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.`price` * ((100 + (t.`rate`))/100)) AS orderprice, m.`name` AS manufacturer_name
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) Country::getDefaultCountryId() . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'supplier` s ON s.`id_supplier` = p.`id_supplier`
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE p.`id_supplier` = ' . (int) $id_supplier . ($active ? ' AND p.`active` = 1' : '') . '
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`)' . ($active_category ? ' INNER JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1' : '') . '
WHERE cg.`id_group` ' . $sqlGroups . '
)
ORDER BY ' . ($orderBy == 'id_product' ? 'p.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
if (!$result) {
return false;
}
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
return Product::getProductsProperties($id_lang, $result);
}
/**
* Return current category products
*
* @param integer $id_lang Language ID
* @param integer $p Page number
* @param integer $n Number of products per page
* @param boolean $getTotal return the number of results instead of the results themself
* @param boolean $active return only active products
* @param boolean $random active a random filter for returned products
* @param int $randomNumberProducts number of products to return if random is activated
* @return mixed Products or number of products
*/
public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
{
global $cookie;
if ($p < 1) {
$p = 1;
}
if (empty($orderBy)) {
$orderBy = 'position';
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if ($orderBy == 'id_product' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
} elseif ($orderBy == 'manufacturer') {
$orderByPrefix = 'm';
$orderBy = 'name';
} elseif ($orderBy == 'position') {
$orderByPrefix = 'cp';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$id_supplier = intval(Tools::getValue('id_supplier'));
/* Return only the number of products */
if ($getTotal) {
$result = Db::getInstance()->getRow('
SELECT COUNT(cp.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` = ' . intval($this->id) . ($active ? ' AND p.`active` = 1' : '') . '
' . ($id_supplier ? 'AND p.id_supplier = ' . $id_supplier : '') . '');
return isset($result) ? $result['total'] : 0;
}
$sql = '
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.price - IF((DATEDIFF(reduction_from, CURDATE()) <= 0 AND DATEDIFF(reduction_to, CURDATE()) >=0) OR reduction_from = reduction_to, IFNULL(reduction_price, (p.price * reduction_percent / 100)),0)) AS orderprice
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE cp.`id_category` = ' . intval($this->id) . ($active ? ' AND p.`active` = 1' : '') . '
' . ($id_supplier ? 'AND p.id_supplier = ' . $id_supplier : '');
if ($random === true) {
$sql .= 'ORDER BY RAND()';
$sql .= 'LIMIT 0, ' . intval($randomNumberProducts);
} else {
$sql .= 'ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . (intval($p) - 1) * intval($n) . ',' . intval($n);
}
$result = Db::getInstance()->ExecuteS($sql);
if ($orderBy == 'orderprice') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
/* Modify SQL result */
return Product::getProductsProperties($id_lang, $result);
}
/**
* Returns category products
*
* @param int $id_lang Language ID
* @param int $p Page number
* @param int $n Number of products per page
* @param string|null $order_by ORDER BY column
* @param string|null $order_way Order way
* @param bool $get_total If set to true, returns the total number of results only
* @param bool $active If set to true, finds only active products
* @param bool $random If true, sets a random filter for returned products
* @param int $random_number_products Number of products to return if random is activated
* @param bool $check_access If set tot rue, check if the current customer
* can see products from this category
* @param Context|null $context
*
* @return array|int|false Products, number of products or false (no access)
* @throws PrestaShopDatabaseException
*/
public function getProducts($id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
if ($check_access && !$this->checkAccess($context->customer->id)) {
return false;
}
$front = in_array($context->controller->controller_type, array('front', 'modulefront'));
$id_supplier = (int) Tools::getValue('id_supplier');
/** Return only the number of products */
if ($get_total) {
$sql = 'SELECT COUNT(cp.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` = ' . (int) $this->id . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : '');
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
if ($p < 1) {
$p = 1;
}
/** Tools::strtolower is a fix for all modules which are now using lowercase values for 'orderBy' parameter */
$order_by = Validate::isOrderBy($order_by) ? Tools::strtolower($order_by) : 'position';
$order_way = Validate::isOrderWay($order_way) ? Tools::strtoupper($order_way) : 'ASC';
$order_by_prefix = false;
if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd') {
$order_by_prefix = 'p';
} elseif ($order_by == 'name') {
$order_by_prefix = 'pl';
} elseif ($order_by == 'manufacturer' || $order_by == 'manufacturer_name') {
$order_by_prefix = 'm';
$order_by = 'name';
} elseif ($order_by == 'position') {
$order_by_prefix = 'cp';
}
if ($order_by == 'price') {
$order_by = 'orderprice';
}
$nb_days_new_product = Configuration::get('PS_NB_DAYS_NEW_PRODUCT');
if (!Validate::isUnsignedInt($nb_days_new_product)) {
$nb_days_new_product = 20;
}
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '') . ', pl.`description`, pl.`description_short`, pl.`available_now`,
pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
DATEDIFF(product_shop.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00",
INTERVAL ' . (int) $nb_days_new_product . ' DAY)) > 0 AS new, product_shop.price AS orderprice
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p
ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')' : '') . '
' . Product::sqlStock('p', 0) . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image_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_ . 'manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
AND cp.`id_category` = ' . (int) $this->id . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '');
if ($random === true) {
$sql .= ' ORDER BY RAND() LIMIT ' . (int) $random_number_products;
} else {
$sql .= ' ORDER BY ' . (!empty($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . bqSQL($order_by) . '` ' . pSQL($order_way) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql, true, false);
if (!$result) {
return array();
}
if ($order_by == 'orderprice') {
Tools::orderbyPrice($result, $order_way);
}
/** Modify SQL result */
return Product::getProductsProperties($id_lang, $result);
}
public function getProductByFilters($selected_filters = array())
{
global $cookie;
if (!empty($this->products)) {
return $this->products;
}
$home_category = Configuration::get('PS_HOME_CATEGORY');
/* If the current category isn't defined or if it's homepage, we have nothing to display */
$id_parent = (int) Tools::getValue('id_category', Tools::getValue('id_category_layered', $home_category));
if ($id_parent == $home_category) {
return false;
}
$alias_where = 'p';
if (version_compare(_PS_VERSION_, '1.5', '>')) {
$alias_where = 'product_shop';
}
$query_filters_where = ' AND ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")';
$query_filters_from = '';
$parent = new Category((int) $id_parent);
if (!count($selected_filters['category'])) {
if (Configuration::get('PS_LAYERED_FULL_TREE')) {
$query_filters_from .= ' INNER JOIN ' . _DB_PREFIX_ . 'category_product cp
ON p.id_product = cp.id_product
INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category AND
c.nleft >= ' . (int) $parent->nleft . ' AND c.nright <= ' . (int) $parent->nright . '
AND c.active = 1)
RIGHT JOIN ' . _DB_PREFIX_ . 'layered_category lc ON (lc.id_category = ' . (int) $id_parent . ' AND
lc.id_shop = ' . (int) Context::getContext()->shop->id . ')';
} else {
$query_filters_from .= ' INNER JOIN ' . _DB_PREFIX_ . 'category_product cp
ON p.id_product = cp.id_product
INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category
AND c.id_category = ' . (int) $id_parent . '
AND c.active = 1)';
}
}
foreach ($selected_filters as $key => $filter_values) {
if (!count($filter_values)) {
continue;
}
preg_match('/^(.*[^_0-9])/', $key, $res);
$key = $res[1];
switch ($key) {
case 'id_feature':
$sub_queries = array();
foreach ($filter_values as $filter_value) {
$filter_value_array = explode('_', $filter_value);
if (!isset($sub_queries[$filter_value_array[0]])) {
$sub_queries[$filter_value_array[0]] = array();
}
$sub_queries[$filter_value_array[0]][] = 'fp.`id_feature_value` = ' . (int) $filter_value_array[1];
}
foreach ($sub_queries as $sub_query) {
$query_filters_where .= ' AND p.id_product IN (SELECT `id_product` FROM `' . _DB_PREFIX_ . 'feature_product` fp WHERE ';
$query_filters_where .= implode(' OR ', $sub_query) . ') ';
}
break;
case 'id_attribute_group':
$sub_queries = array();
foreach ($filter_values as $filter_value) {
$filter_value_array = explode('_', $filter_value);
if (!isset($sub_queries[$filter_value_array[0]])) {
$sub_queries[$filter_value_array[0]] = array();
}
$sub_queries[$filter_value_array[0]][] = 'pac.`id_attribute` = ' . (int) $filter_value_array[1];
}
foreach ($sub_queries as $sub_query) {
$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
FROM `' . _DB_PREFIX_ . 'product_attribute_combination` pac
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
ON (pa.`id_product_attribute` = pac.`id_product_attribute`)' . Shop::addSqlAssociation('product_attribute', 'pa') . '
WHERE ' . implode(' OR ', $sub_query) . ') ';
}
break;
case 'category':
$query_filters_where .= ' AND p.id_product IN (SELECT id_product FROM ' . _DB_PREFIX_ . 'category_product cp WHERE ';
foreach ($selected_filters['category'] as $id_category) {
$query_filters_where .= 'cp.`id_category` = ' . (int) $id_category . ' OR ';
}
$query_filters_where = rtrim($query_filters_where, 'OR ') . ')';
break;
case 'quantity':
/*if (count($selected_filters['quantity']) == 2)
break;
*
*/
$quantity = 1;
//var_dump($selected_filters);
$i = 0;
$quantity_value;
$j = 0;
foreach ($selected_filters['quantity'] as $value) {
$q = explode("_", $value);
$quantity_value[$q[2]] = $q[3];
}
$query_color_where = '';
if (isset($selected_filters['id_attribute_group'])) {
foreach ($selected_filters['id_attribute_group'] as $attribute) {
$attribute = explode("_", $attribute);
if ($attribute[0] == 3) {
$color_id[$i++] = 'sai.color_id ="' . $attribute[1] . '"';
}
}
$query_color_where = implode(" OR ", $color_id);
}
if (isset($quantity_value[2]) && $quantity_value[2] != "") {
$quantity = $quantity_value[2];
}
if (isset($quantity_value[0]) && $quantity_value[2] == "") {
$quantity_value[2] = 1;
}
if (isset($quantity_value[0]) || isset($quantity_value[2]) && $quantity_value[2] != '') {
$query_filters_where .= ' AND (select sum(sai.quantity) from `' . _DB_PREFIX_ . 'stock_available_inventory` sai where sai.id_product = p.id_product ';
if ($query_color_where != '') {
$query_filters_where .= ' AND (' . $query_color_where . ')';
}
$query_filters_where .= ') ' . (isset($quantity_value[0]) ? '<=' : '>') . ' ' . $quantity . ' ';
}
if (isset($quantity_value[3]) && isset($quantity_value[4])) {
$query_filters_where .= ' AND sa.intransit';
if ($query_color_where != '') {
$query_filters_where .= ' AND ' . $query_color_where;
}
$query_filters_where .= ' >= 1';
$query_filters_where .= ' AND sa.intransit_available_date <= "' . $quantity_value[4] . '"';
}
//var_dump($query_filters_where);
$query_filters_from .= 'LEFT JOIN `' . _DB_PREFIX_ . 'stock_available_inventory` sa ON (sa.id_product = p.id_product AND sa.id_shop = ' . (int) Context::getContext()->shop->id . ') ';
break;
case 'manufacturer':
$query_filters_where .= ' AND p.id_manufacturer IN (' . implode($selected_filters['manufacturer'], ',') . ')';
break;
case 'condition':
if (count($selected_filters['condition']) == 3) {
break;
}
$query_filters_where .= ' AND ' . $alias_where . '.condition IN (';
foreach ($selected_filters['condition'] as $cond) {
$query_filters_where .= '\'' . pSQL($cond) . '\',';
}
$query_filters_where = rtrim($query_filters_where, ',') . ')';
break;
case 'weight':
if ($selected_filters['weight'][0] != 0 || $selected_filters['weight'][1] != 0) {
$query_filters_where .= ' AND p.`weight` BETWEEN ' . (double) ($selected_filters['weight'][0] - 0.001) . ' AND ' . (double) ($selected_filters['weight'][1] + 0.001);
}
break;
case 'price':
if (isset($selected_filters['price'])) {
if ($selected_filters['price'][0] !== '' || $selected_filters['price'][1] !== '') {
$price_filter = array();
$price_filter['min'] = (double) $selected_filters['price'][0];
$price_filter['max'] = (double) $selected_filters['price'][1];
}
} else {
$price_filter = false;
}
break;
}
}
if (Tools::getValue('new_products')) {
// $query_filters_from = Shop::addSqlAssociation('product', 'p');
$query_filters_where .= ' AND 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')) . '" ';
}
if (Tools::getValue('price_drop')) {
$query_filters_from .= 'LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` sp ON (sp.id_product = p.id_product AND sp.id_shop = ' . (int) Context::getContext()->shop->id . ')';
$query_filters_where .= ' AND sp.`reduction` > 0';
}
if (Tools::getValue('search_str')) {
$words = explode(' ', Tools::getValue('search_str'));
foreach ($words as $key => $word) {
if (!empty($word) && strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) {
$word = str_replace('%', '\\%', $word);
$word = str_replace('_', '\\_', $word);
$query_filters_where .= ' AND sw.word LIKE
' . ($word[0] == '-' ? ' \'' . pSQL(Tools::substr($word, 1, Configuration::get('PS_SEARCH_MAX_WORD_LENGTH'))) . '%\'' : '\'' . pSQL(Tools::substr($word, 0, Configuration::get('PS_SEARCH_MAX_WORD_LENGTH'))) . '%\'');
if ($word[0] != '-') {
$query_filters_where .= ' AND sw.word LIKE \'' . pSQL(Tools::substr($word, 0, Configuration::get('PS_SEARCH_MAX_WORD_LENGTH'))) . '%\'';
}
}
}
$query_filters_from .= ' LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON ' . '(si.id_product = p.id_product)' . ' LEFT JOIN ' . _DB_PREFIX_ . 'search_word sw ON (sw.id_word = si.id_word AND sw.id_shop = ' . (int) Context::getContext()->shop->id . ')';
}
$id_currency = (int) Context::getContext()->currency->id;
$price_filter_query_in = '';
// All products with price range between price filters limits
$price_filter_query_out = '';
// All products with a price filters limit on it price range
if (isset($price_filter) && $price_filter) {
$price_filter_query_in = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi
ON
(
psi.price_min >= ' . (int) $price_filter['min'] . '
AND psi.price_max <= ' . (int) $price_filter['max'] . '
AND psi.`id_product` = p.`id_product`
AND psi.`id_currency` = ' . $id_currency . '
)';
$price_filter_query_out = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi
ON
((psi.price_min < ' . (int) $price_filter['min'] . ' AND psi.price_max > ' . (int) $price_filter['min'] . ')
OR
(psi.price_max > ' . (int) $price_filter['max'] . ' AND psi.price_min < ' . (int) $price_filter['max'] . '))
AND psi.`id_product` = p.`id_product`
AND psi.`id_currency` = ' . $id_currency;
}
$query_filters_from .= Shop::addSqlAssociation('product', 'p');
$all_products_out = self::query('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p
' . $price_filter_query_out . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . ' GROUP BY id_product');
$all_products_in = self::query('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p
' . $price_filter_query_in . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . ' GROUP BY id_product');
// var_dump($all_products_in);
$product_id_list = array();
while ($product = DB::getInstance()->nextRow($all_products_in)) {
$product_id_list[] = (int) $product['id_product'];
}
while ($product = DB::getInstance()->nextRow($all_products_out)) {
if (isset($price_filter) && $price_filter) {
$price = (int) Product::getPriceStatic($product['id_product'], Configuration::get('PS_LAYERED_FILTER_PRICE_USETAX'));
// Cast to int because we don't care about cents
if ($price < $price_filter['min'] || $price > $price_filter['max']) {
continue;
}
$product_id_list[] = (int) $product['id_product'];
}
}
$this->nbr_products = count($product_id_list);
if ($this->nbr_products == 0) {
$this->products = array();
} else {
$n = (int) Tools::getValue('n', Configuration::get('PS_PRODUCTS_PER_PAGE'));
$nb_day_new_product = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
$this->products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT
p.*,
' . ($alias_where == 'p' ? '' : 'product_shop.*,') . '
' . $alias_where . '.id_category_default,
pl.*,
MAX(image_shop.`id_image`) id_image,
il.legend,
m.name manufacturer_name,
MAX(product_attribute_shop.id_product_attribute) id_product_attribute,
DATEDIFF(' . $alias_where . '.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (int) $nb_day_new_product . ' DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category)
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . '
' . Product::sqlStock('p', 'product_attribute_shop', false, Context::getContext()->shop) . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . Shop::addSqlRestrictionOnLang('pl') . ' AND pl.id_lang = ' . (int) $cookie->id_lang . ')
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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")
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
AND p.id_product IN (' . implode(',', $product_id_list) . ')
GROUP BY product_shop.id_product
ORDER BY ' . Tools::getProductsOrder('by', Tools::getValue('orderby'), true) . ' ' . Tools::getProductsOrder('way', Tools::getValue('orderway')) . ' LIMIT ' . (((int) $this->page - 1) * $n . ',' . $n));
}
if (Tools::getProductsOrder('by', Tools::getValue('orderby'), true) == 'p.price') {
Tools::orderbyPrice($this->products, Tools::getProductsOrder('way', Tools::getValue('orderway')));
}
return $this->products;
}
public static function getProducts($id_manufacturer, $id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $active_category = true, Context $context = NULL)
{
global $cookie;
$agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p");
if (empty($agile_sql_parts['joins']) or empty($agile_sql_parts['wheres'])) {
parent::getProducts($id_manufacturer, $id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $active_category);
}
if ($p < 1) {
$p = 1;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'name';
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$groups = FrontController::getCurrentCustomerGroups();
$sqlGroups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
if ($getTotal) {
$sql = '
SELECT p.`id_product`
FROM `' . _DB_PREFIX_ . 'product` p
' . $agile_sql_parts['joins'] . '
WHERE p.id_manufacturer = ' . (int) $id_manufacturer . ($active ? ' AND p.`active` = 1' : '') . '
' . $agile_sql_parts['wheres'] . '
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`)' . ($active_category ? ' INNER JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1' : '') . '
WHERE cg.`id_group` ' . $sqlGroups . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
return (int) sizeof($result);
}
$sql = '
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.`price` * ((100 + (t.`rate`))/100)) AS orderprice
' . $agile_sql_parts['selects'] . '
FROM `' . _DB_PREFIX_ . 'product` p
' . $agile_sql_parts['joins'] . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) (_PS_VERSION_ > '1.5' ? Context::getContext()->country->id : Country::getDefaultCountryId()) . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE p.`id_manufacturer` = ' . (int) $id_manufacturer . ($active ? ' AND p.`active` = 1' : '') . '
' . $agile_sql_parts['wheres'] . '
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`)' . ($active_category ? ' INNER JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1' : '') . '
WHERE cg.`id_group` ' . $sqlGroups . '
)
ORDER BY ' . ($orderBy == 'id_product' ? 'p.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
if (!$result) {
return false;
}
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
$finalResults = Product::getProductsProperties($id_lang, $result);
$finalResults = AgileSellerManager::prepareSellerRattingInfo($finalResults);
return $finalResults;
}
protected function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
{
global $cookie, $smarty;
$id_seller_country = (int) Tools::getValue('id_seller_country');
if ($p < 1) {
$p = 1;
}
if ($n <= 0) {
$n = 10;
}
if (empty($orderBy)) {
$orderBy = 'price';
} else {
$orderBy = strtolower($orderBy);
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if ($orderBy == 'id_product' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
} elseif ($orderBy == 'manufacturer') {
$orderByPrefix = 'm';
$orderBy = 'name';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$requiredcond = '';
if (intval(Configuration::get('AGILE_MS_PRODUCT_APPROVAL')) == 1) {
$requiredcond = ' AND po.approved = 1 ';
}
$joins = '';
$wheres = '';
if (Module::isInstalled('agilesellerlistoptions')) {
require_once _PS_ROOT_DIR_ . "/modules/agilesellerlistoptions/agilesellerlistoptions.php";
$joins = $joins . '
LEFT JOIN `' . _DB_PREFIX_ . 'seller_listoption` slb ON (p.id_product = slb.id_product AND slb.id_option = ' . AgileSellerListOptions::ASLO_OPTION_LIST . ')
';
$aslo_list_prod_id = intval(Configuration::get('ASLO_PROD_FOR_OPTION' . AgileSellerListOptions::ASLO_OPTION_LIST));
$wheres = $wheres . '
AND (slb.status = ' . AgileSellerListOptions::ASLO_STATUS_IN_EFFECT . ' OR IFNULL(po.id_owner,0) = 0 OR ' . $aslo_list_prod_id . '=' . AgileSellerListOptions::ASLO_ALWAYS_FREE . ')
';
}
$location_conditions = '';
switch ($this->location_level) {
case 'country':
if ((int) $this->id_location > 0) {
$location_conditions = ' AND si.id_country=' . (int) $this->id_location;
}
break;
case 'state':
if ((int) $this->id_location > 0) {
$location_conditions = ' AND si.id_state=' . (int) $this->id_location;
}
break;
case 'city':
if (!empty($this->id_location)) {
$location_conditions = ' AND sil.city=\'' . $this->id_location . '\'';
}
break;
case 'sellertype':
if (!empty($this->id_location)) {
$location_conditions = ' AND si.id_sellertype1=' . $this->id_location;
}
break;
case 'custom':
if (!empty($this->id_location)) {
if (AgileMultipleShop::SHOP_BY_CUSTOM_LANG) {
$location_conditions = ' AND sil.' . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . '=\'' . $this->id_location . '\'';
} else {
$location_conditions = ' AND si.' . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . '=\'' . $this->id_location . '\'';
}
}
break;
}
if ($getTotal) {
$sql = '
SELECT COUNT(po.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_owner` po ON p.`id_product` = po.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo` si ON si.`id_seller` = po.`id_owner`
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = ' . $cookie->id_lang . '
' . $joins . '
WHERE p.active=1
' . $location_conditions . '
' . ($active ? ' AND p.`active` = 1' : '') . '
' . $requiredcond . '
' . $wheres . '
';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return isset($result) ? $result['total'] : 0;
}
$sql = '
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new,
(p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice, si.`id_seller`
FROM `' . _DB_PREFIX_ . 'product_owner` po
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo` si ON si.`id_seller` = po.`id_owner`
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = ' . $cookie->id_lang . '
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = po.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) (_PS_VERSION_ > '1.5' ? Context::getContext()->country->id : Country::getDefaultCountryId()) . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
' . $joins . '
WHERE p.active=1
' . $location_conditions . '
' . $requiredcond . '
' . $wheres . '
';
if ($random === true) {
$sql .= ' ORDER BY RAND()';
$sql .= ' LIMIT 0, ' . (int) $randomNumberProducts;
} else {
$sql .= ' ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
if ($orderBy == 'orderprice') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
$last_result = Product::getProductsProperties($id_lang, $result);
foreach ($last_result as $key => $value) {
$seller_id = $value['id_seller'];
$flagIds = Product::getFlagsId($seller_id);
foreach ($flagIds as $k => $flgId) {
$last_result[$key]['img_exist'][$k] = file_exists(_PS_ROOT_DIR_ . DS . 'flag' . DS . 'mini' . DS . $flgId . '.jpg') ? 1 : 0;
$last_result[$key]['img_name'][$k] = $flgId . '.jpg';
}
}
// die('<pre>' . print_r($last_result, true));
return $last_result;
}
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);
}
/**
* Get prices drop
*
* @param integer $id_lang Language id
* @param integer $pageNumber Start from (optional)
* @param integer $nbProducts Number of products to return (optional)
* @param boolean $count Only in order to get total number (optional)
* @return array Prices drop
*/
public static function getPricesDrop($id_lang, $page_number = 0, $nb_products = 10, $count = false, $order_by = null, $order_way = null, $beginning = false, $ending = false, Context $context = null)
{
if (!Validate::isBool($count)) {
die(Tools::displayError());
}
if (!$context) {
$context = Context::getContext();
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
if (empty($order_by) || $order_by == 'position') {
$order_by = 'price';
}
if (empty($order_way)) {
$order_way = 'DESC';
}
if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add') {
$order_by_prefix = 'p';
} else {
if ($order_by == 'name') {
$order_by_prefix = 'pl';
}
}
if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
die(Tools::displayError());
}
$current_date = date('Y-m-d H:i:s');
$ids_product = Product::_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context);
$tab_id_product = array();
foreach ($ids_product as $product) {
if (is_array($product)) {
$tab_id_product[] = (int) $product['id_product'];
} else {
$tab_id_product[] = (int) $product;
}
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
if ($count) {
$sql = 'SELECT COUNT(DISTINCT p.`id_product`) AS nb
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
WHERE product_shop.`active` = 1
AND product_shop.`show_price` = 1
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
' . (!$beginning && !$ending ? 'AND p.`id_product` IN(' . (is_array($tab_id_product) && count($tab_id_product) ? implode(', ', $tab_id_product) : 0) . ')' : '') . '
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 . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return (int) $result['nb'];
}
if (strpos($order_by, '.') > 0) {
$order_by = explode('.', $order_by);
$order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`';
}
$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`, i.`id_image`, il.`legend`, t.`rate`, m.`name` AS manufacturer_name,
DATEDIFF(
p.`date_add`,
DATE_SUB(
NOW(),
INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY
)
) > 0 AS new
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
' . Product::sqlStock('p', 0, 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` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) Context::getContext()->country->id . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
WHERE product_shop.`active` = 1
AND product_shop.`show_price` = 1
' . ($front ? ' AND p.`visibility` IN ("both", "catalog")' : '') . '
' . (!$beginning && !$ending ? ' AND p.`id_product` IN (' . (is_array($tab_id_product) && count($tab_id_product) ? implode(', ', $tab_id_product) : 0) . ')' : '') . '
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 . '
)
ORDER BY ' . (isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . 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 ($order_by == 'price') {
Tools::orderbyPrice($result, $order_way);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
