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); }