/** * Get product accessories * * @param integer $id_lang Language id * @return array Product accessories */ public static function getItems($iIdProduct, $id_lang, $active = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` as manufacturer_name, cl.`name` AS category_default, DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 AS new FROM `' . _DB_PREFIX_ . 'now_ideas_or_tips` LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = `id_product_2` ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON ( product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (p.`id_manufacturer`= m.`id_manufacturer`) ' . Product::sqlStock('p', 0) . ' WHERE `id_product_1` = ' . (int) $iIdProduct . ($active ? ' AND product_shop.`active` = 1 AND product_shop.`visibility` != \'none\'' : '') . ' GROUP BY product_shop.id_product'; if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) { return false; } foreach ($result as &$row) { $row['id_product_attribute'] = Product::getDefaultAttribute((int) $row['id_product']); } return Product::getProductsProperties($id_lang, $result); }
public static function find_event($id_lang, $event_date, $pageNumber = 1, $pageSize = 1) { global $cookie; $id_lang = pSQL($id_lang); // TODO : smart page management if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 1; } $customer_select = ""; $customer_join = ""; $customer_where = ""; if ($cookie->id_customer) { $customer_select = ", cg.`id_group`"; $customer_join = "INNER JOIN `PREFIX_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)"; $customer_where = "cg.`id_customer` = {$cookie->id_customer} OR "; } $pageStart = ($pageNumber - 1) * $pageSize; $price_sql = Product::getProductPriceSql('p.id_product', 'pp'); $event_date_end = normalize_date("{$event_date} + 1 day"); // FIXME: Handle new products properly (see classes/Product.php:isNew() for corect join clause) $sql = "\n\t\t SELECT\n SQL_CALC_FOUND_ROWS p.*, pp.*, 0 as `new`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,\n\t\t t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name {$customer_select}\n\t\t FROM\n\t\t PREFIX_customization_field as f\n\t\t JOIN PREFIX_customization_field_schedule as s on\n\t\t not ( s.end_time < '{$event_date}'\n\t\t or s.start_time > '{$event_date_end}')\n\t\t and s.id_customization_field = f.id_customization_field\n\t \t JOIN PREFIX_product p on\n\t\t p.id_product = f.id_product\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 OUTER 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\n\t\t {$price_sql}\n\t\t \n\t\t LEFT JOIN `PREFIX_tax` t ON (pp.`id_tax` = t.`id_tax`)\n\n\t\t LEFT JOIN `PREFIX_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)\n\t\t LEFT JOIN `PREFIX_category_product` cp ON (cp.`id_product` = p.`id_product`)\n\t\t INNER JOIN `PREFIX_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)\n\t\t {$customer_join}\n\n\t\t WHERE\n\t\t p.active = 1\n\t\t AND ({$customer_where} ctg.`id_group` = 1)\n\n\t\t GROUP BY p.`id_product`\n\n\t\t ORDER BY start_time ASC\n\n\t\t LIMIT {$pageStart}, {$pageSize}"; $sql = str_replace('PREFIX_', _DB_PREFIX_, $sql); $result = Db::getInstance()->ExecuteS($sql); $total = Db::getInstance()->getValue('SELECT FOUND_ROWS()'); return array('total' => $total, 'result' => Product::getProductsProperties($id_lang, $result)); }
public static function getCrossSells($id_product, $id_lang, $limit = 12) { if (!$id_product || !$id_lang) { return; } $front = true; if (!in_array(Context::getContext()->controller->controller_type, array('front', 'modulefront'))) { $front = false; } $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.id_order = o.id_order) WHERE o.valid = 1 AND od.product_id = ' . (int) $id_product); if (count($orders)) { $list = ''; foreach ($orders as $order) { $list .= (int) $order['id_order'] . ','; } $list = rtrim($list, ','); $order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT DISTINCT od.product_id, p.id_product, pl.name, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price, cl.link_rewrite category, p.ean13, p.out_of_stock, p.id_category_default ' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute' : '') . ' FROM ' . _DB_PREFIX_ . 'order_detail od LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id) ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) Context::getContext()->shop->id . ')' : '') . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = od.product_id' . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = od.product_id) WHERE od.id_order IN (' . $list . ') AND pl.id_lang = ' . (int) $id_lang . ' AND cl.id_lang = ' . (int) $id_lang . ' AND od.product_id != ' . (int) $id_product . ' AND i.cover = 1 AND product_shop.active = 1' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' ORDER BY RAND() LIMIT ' . (int) $limit . ' ', true, false); $tax_calc = Product::getTaxCalculationMethod(); if (is_array($order_products)) { foreach ($order_products as &$order_product) { $order_product['image'] = Context::getContext()->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('medium')); $order_product['link'] = Context::getContext()->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']); if ($tax_calc == 0 || $tax_calc == 2) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null); } elseif ($tax_calc == 1) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null); } } return Product::getProductsProperties($id_lang, $order_products); } } }
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); }
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; }
/** * 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 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); }
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 $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 function getRecentProducts($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(); } $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'; } // $order_by = 'dateadd'; //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` ' . ($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 DISTINCT p.id_product, p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`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`, il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default, (SUM(pc.`grade`) / COUNT(pc.`grade`)) AS avg, pc.`date_add` as dateadd, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new, product_shop.price AS orderprice 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`)' . 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` LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pc ON p.`id_product` = pc.`id_product` AND product_shop.`id_shop` = ' . (int) $context->shop->id . ' AND (pa.id_product_attribute IS NULL OR product_attribute_shop.id_shop=' . (int) $context->shop->id . ') AND (i.id_image IS NULL OR image_shop.id_shop=' . (int) $context->shop->id . ') ' . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : ''); $sql .= $active ? ' WHERE p.`active` = 1' : ''; $sql .= ' GROUP BY pc.id_product '; if ($random === true) { $sql .= ' ORDER BY RAND()'; $sql .= ' LIMIT 0, ' . (int) $random_number_products; } else { $sql .= ' ORDER BY `' . 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 array(); } /* Modify SQL result */ return Product::getProductsProperties($id_lang, $result); }
public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null) { if (!$context) { $context = Context::getContext(); } if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } // no group by needed : there's only one attribute with default_on=1 for a given id_product + shop // same for image with cover=1 $sql = ' SELECT p.id_product, IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`, image_shop.`id_image` id_image, il.`legend`, ps.`quantity` AS sales, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable, IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock, product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new, product_shop.`on_sale`, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity FROM `' . _DB_PREFIX_ . 'product_sale` ps LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product` ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (product_attribute_shop.id_product_attribute=pa.id_product_attribute) LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON cl.`id_category` = product_shop.`id_category_default` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . Product::sqlStock('p', 0); $sql .= ' WHERE product_shop.`active` = 1 AND p.`visibility` != \'none\''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql .= ' AND EXISTS(SELECT 1 FROM `' . _DB_PREFIX_ . 'category_product` cp JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . ') WHERE cp.`id_product` = p.`id_product`)'; } $sql .= ' ORDER BY ps.quantity DESC LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products; if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) { return false; } return Product::getProductsProperties($id_lang, $result); }
public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1, $checkAccess = true, Context $context = null) { global $cookie; if (!$checkAccess or !$this->checkAccess($cookie->id_customer)) { return false; } if (!$context) { $context = Context::getContext(); } $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } if (Module::isInstalled('agilemembership') and $this->id == Configuration::get('AGILE_MEMBERSHIP_CID')) { return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess); } if (Module::isInstalled('agileprepaidcredit') and $this->id == Configuration::getGlobalValue('AGILE_PCREDIT_CID')) { return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess); } if (Module::isInstalled('agilesellerlistoptions') and $this->id == Configuration::get('ASLO_CATEGORY_ID')) { return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess); } $agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p"); if (empty($agile_sql_parts['selects']) and empty($agile_sql_parts['joins']) and empty($agile_sql_parts['wheres'])) { return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess); } if (Module::isInstalled('agilesellerlistoptions')) { require_once _PS_ROOT_DIR_ . "/modules/agilesellerlistoptions/agilesellerlistoptions.php"; if ($this->id <= 1 or $this->id == 2 or $this->id == (int) Configuration::get('PS_HOME_CATEGORY')) { return AgileSellerListOptions::get_home_products($id_lang, $p, $n); } if (empty($orderBy) || $orderBy == 'position') { $orderBy = 'position2'; } } if ($p < 1) { $p = 1; } if (empty($orderBy)) { $orderBy = 'position'; } else { $orderBy = strtolower($orderBy); } if (empty($orderWay)) { $orderWay = 'ASC'; } if ($orderBy == 'id_product' or $orderBy == 'date_add' or $orderBy == 'date_upd') { $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'); if ($getTotal) { $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` ' . $agile_sql_parts['joins'] . ' WHERE cp.`id_category` = ' . (int) $this->id . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . $agile_sql_parts['wheres'] . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : ''); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql); return isset($result) ? $result['total'] : 0; } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, 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 AS orderprice ' . $agile_sql_parts['selects'] . ' 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`)' . 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` ' . $agile_sql_parts['joins'] . ' WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . ' ' . $agile_sql_parts['wheres'] . ' 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 : '') . ' GROUP BY product_shop.id_product'; 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 (!$result) { return array(); } $seller = array(); $id_seller = array(); $link_seller = array(); foreach ($result as $row) { $pid = $row['id_product']; $seller[$pid] = isset($row['seller']) ? $row['seller'] : ''; $id_seller[$pid] = isset($row['id_seller']) ? intval($row['id_seller']) : 0; $link_seller[$pid] = isset($row['has_sellerlink']) ? $row['has_sellerlink'] : ''; } $resultsArray = Product::getProductsProperties((int) $id_lang, $result); for ($idx = 0; $idx < count($resultsArray); $idx++) { $pid = $resultsArray[$idx]['id_product']; $resultsArray[$idx]['seller'] = $seller[$pid]; $resultsArray[$idx]['id_seller'] = $id_seller[$pid]; $resultsArray[$idx]['has_sellerlink'] = $link_seller[$pid]; } $resultsArray = AgileSellerManager::prepareSellerRattingInfo($resultsArray); return $resultsArray; }
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); }
/** * 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); }
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 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 find_adv($id_lang, $expr, $pageNumber = 1, $pageSize = 10, $orderBy = false, $orderWay = false) { if (!is_numeric($pageNumber) or !is_numeric($pageSize) or !Validate::isValidSearch($expr) or $orderBy and !$orderWay) { die(Tools::displayError()); } global $link; global $cookie; if ($orderBy == 'position') { $orderBy = 'name'; } if (!is_numeric($pageNumber) or !is_numeric($pageSize) or $orderBy and !$orderWay) { die(Tools::displayError()); } $alias = new Alias(NULL, $expr); if (Validate::isLoadedObject($alias)) { $expr = $alias->search; } if (!Validate::isValidSearch($expr)) { die(Tools::displayError()); } if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 10; } $expr = str_replace(' ', ' ', pSQL(str_replace('\'', ' ', $expr))) . ''; $words = explode(' ', AdvSearch::sanitize($expr, $id_lang)); /* else we search for the expression */ //$search_query=(Tools::getValue('search_queryy')!=null)?', '.$this->_getScore($expr).'':''; ///$search_query2=(Tools::getValue('search_queryy')!=null)?'AND '.$this->_getFilter($expr):''; /////////////////////////////// foreach ($words as $key => $word) { if (!empty($word)) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); $whereArray[] = ' p.id_product ' . ($word[0] == '-' ? 'NOT' : '') . ' IN ( SELECT id_product FROM ' . _DB_PREFIX_ . 'search_word sw LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word WHERE sw.id_lang = ' . intval($id_lang) . ' AND sw.word LIKE ' . ($word[0] == '-' ? ' \'' . pSQL(substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'' : '\'' . pSQL(substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'') . ' ) '; if ($word[0] != '-') { $scoreArray[] = 'sw.word LIKE \'' . pSQL(substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''; } } else { unset($words[$key]); } } if (!sizeof($words)) { $searchbywords = ''; } else { $searchbywords = 'AND ' . implode(' AND ', $whereArray); } /////////////////////////////// $displaysupp = AdvSearch::getSuppliers(Tools::getValue('supply'), 'id_supplier'); $displaymanuf = AdvSearch::getSuppliers(Tools::getValue('manufacturer'), 'id_manufacturer'); $attributes = ''; $attributes_val = ''; if ($attribu = Tools::getValue('atrybut_opcja')) { if (!is_array($attribu)) { $attrib = urldecode($attribu); $attrib = urldecode($attrib); $pos = strpos($attrib, ','); if ($pos === false) { $att = array($attrib); } else { $att = explode(",", $attrib); } $attributes = AdvSearch::attributesQuery($id_lang, $att, true); $attributes_val = AdvSearch::attributesQuery($id_lang, $att, false); } else { if ($attribu[0] > 0) { $attributes = AdvSearch::attributesQuery($id_lang, $attribu, true); $attributes_val = AdvSearch::attributesQuery($id_lang, $attribu, false); } } } $displaycategories = AdvSearch::queryCategories(Tools::getValue('categories')); $price_brutto = "CASE WHEN t.rate>0 THEN\n\tCASE WHEN pp.reduction_from!=pp.reduction_to THEN\n\t\tCASE WHEN pp.reduction_to>='" . date("Y-m-d") . "' and pp.reduction_from<='" . date("Y-m-d") . "' THEN\n\t\t\tCASE WHEN pp.reduction_price>0 THEN\n\t\t\t\tROUND(((pp.price*(1+(t.rate/100)))-pp.reduction_price)*cu.conversion_rate,2)\n\t\t\tWHEN pp.reduction_percent>0 THEN\n\t\t\t\tROUND(((pp.price*(1+(t.rate/100)))-((pp.price*(1+(t.rate/100)))*(pp.reduction_percent/100)))*cu.conversion_rate,2)\n\t\t\tELSE\n\t\t\t\tROUND((pp.price*(1+t.rate/100))*cu.conversion_rate,2)\n\t\t\tEND \n\t\tELSE\n\t\t\tROUND((pp.price*(1+t.rate/100))*cu.conversion_rate,2)\n\t\tEND \n\tELSE\n\t\tCASE WHEN pp.reduction_price>0 THEN\n\t\t\tROUND(((pp.price*(1+t.rate/100))-pp.reduction_price)*cu.conversion_rate,2)\n\t\tWHEN pp.reduction_percent>0 THEN\n\t\t\tROUND(((pp.price*(1+t.rate/100))-((pp.price*(1+(t.rate/100)))*(pp.reduction_percent/100)))*cu.conversion_rate,2)\n\t\tELSE\n\t\t\tROUND((pp.price*(1+t.rate/100))*cu.conversion_rate,2)\n\t\tEND \n\tEND \nELSE\n\tCASE WHEN pp.reduction_from!=pp.reduction_to THEN\n\t\tCASE WHEN pp.reduction_to>='" . date("Y-m-d") . "' and pp.reduction_from<='" . date("Y-m-d") . "' THEN\n\t\t\tCASE WHEN pp.reduction_price>0 THEN\n\t\t\t\tROUND((pp.price-pp.reduction_price)*cu.conversion_rate,2)\n\t\t\tWHEN pp.reduction_percent>0 THEN\n\t\t\t\tROUND((pp.price-(pp.price*(pp.reduction_percent/100)))*cu.conversion_rate,2)\n\t\t\tELSE\n\t\t\t\tROUND((pp.price)*cu.conversion_rate,2)\n\t\t\tEND \n\t\tELSE\n\t\t\tROUND((pp.price)*cu.conversion_rate,2)\n\t\tEND \t\n\tELSE\t\n\t\tCASE WHEN pp.reduction_price>0 THEN\n\t\t\tROUND((pp.price-pp.reduction_price)*cu.conversion_rate,2)\n\t\tWHEN pp.reduction_percent>0 THEN\n\t\t\tROUND((pp.price-(pp.price*(pp.reduction_percent/100)))*cu.conversion_rate,2)\n\t\tELSE\n\t\t\tROUND((pp.price)*cu.conversion_rate,2)\n\t\tEND \t\t\t\n\tEND \n\nEND "; $price_sql = str_replace('PREFIX_', _DB_PREFIX_, Product::getProductPriceSql('p.id_product', 'pp')); $sql = 'SELECT DISTINCT p.id_product,0 as `new`, ' . (Tools::getValue('price_from') > 0 || Tools::getValue('price_to') > 0 ? $price_brutto . ' as price_brutto,' : '') . ' p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, t.`rate`, i.`id_image`, il.`legend`, pp.* FROM `' . _DB_PREFIX_ . 'product` p ' . $attributes . $displaycategories . ' ' . (Tools::getValue('price_from') > 0 || Tools::getValue('price_to') > 0 ? 'JOIN `' . _DB_PREFIX_ . 'currency` cu ' : '') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ') LEFT OUTER 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) . ') ' . $price_sql . ' LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON pp.`id_tax` = t.`id_tax` WHERE p.`active` = 1 ' . $searchbywords . ' ' . (Tools::getValue('price_from') > 0 || Tools::getValue('price_to') > 0 ? 'AND cu.id_currency=' . intval($cookie->id_currency) : '') . ' ' . (Tools::getValue('price_from') > 0 ? 'AND ' . $price_brutto . ' >= ' . intval(Tools::getValue('price_from')) : '') . ' ' . (Tools::getValue('price_to') > 0 ? 'AND ' . $price_brutto . ' <= ' . intval(Tools::getValue('price_to')) : '') . ' ' . $displaysupp . $displaymanuf . $attributes_val . ' ' . ($orderBy ? ' ORDER BY ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . intval(($pageNumber - 1) * $pageSize) . ',' . intval($pageSize); $sql_count = 'SELECT DISTINCT p.id_product,' . (Tools::getValue('price_from') > 0 || Tools::getValue('price_to') > 0 ? $price_brutto . ' as price_brutto,' : '') . ' p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, t.`rate`, i.`id_image`, il.`legend`, pp.* FROM `' . _DB_PREFIX_ . 'product` p ' . $attributes . $displaycategories . ' ' . (Tools::getValue('price_from') > 0 || Tools::getValue('price_to') > 0 ? 'JOIN `' . _DB_PREFIX_ . 'currency` cu ' : '') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ') LEFT OUTER 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) . ') ' . $price_sql . ' LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON pp.`id_tax` = t.`id_tax` WHERE p.`active` = 1 ' . $searchbywords . ' ' . (Tools::getValue('price_from') > 0 || Tools::getValue('price_to') > 0 ? 'AND cu.id_currency=' . intval($cookie->id_currency) : '') . ' ' . (Tools::getValue('price_from') > 0 ? 'AND ' . $price_brutto . ' >= ' . intval(Tools::getValue('price_from')) : '') . ' ' . (Tools::getValue('price_to') > 0 ? 'AND ' . $price_brutto . ' <= ' . intval(Tools::getValue('price_to')) : '') . ' ' . $displaysupp . $displaymanuf . $attributes_val . ' ' . ($orderBy ? ' ORDER BY ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : ''); $result = Db::getInstance()->ExecuteS($sql); $result2 = Db::getInstance()->ExecuteS($sql_count); $total = count($result2); //$total = Db::getInstance()->getValue('SELECT FOUND_ROWS()'); Module::hookExec('search', array('expr' => $expr, 'total' => $total)); return array('total' => $total, 'result' => 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); }
public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null) { if (!$context) { $context = Context::getContext(); } if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'); } //Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage $products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`) WHERE cg.`id_group` ' . $sql_groups); $ids = array(); foreach ($products as $product) { $ids[$product['id_product']] = 1; } $ids = array_keys($ids); sort($ids); $ids = count($ids) > 0 ? implode(',', $ids) : 'NULL'; //Main query $sql = ' SELECT p.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`, MAX(image_shop.`id_image`) id_image, il.`legend`, ps.`quantity` AS sales, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable, IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock, product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new FROM `' . _DB_PREFIX_ . 'product_sale` ps LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product` ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON cl.`id_category` = product_shop.`id_category_default` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ' WHERE product_shop.`active` = 1 AND p.`visibility` != \'none\' AND p.`id_product` IN (' . $ids . ') GROUP BY product_shop.id_product ORDER BY sales DESC LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products; if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) { return false; } return Product::getProductsProperties($id_lang, $result); }
public function ajaxCall() { global $smarty, $cookie; $selectedFilters = $this->getSelectedFilters(); $products = $this->getProductByFilters($selectedFilters); $products = Product::getProductsProperties((int) $cookie->id_lang, $products); $smarty->assign('products', $products); /* We are sending an array in jSon to the .js controller, it will update both the filters and the products zones */ return Tools::jsonEncode(array('filtersBlock' => $this->generateFiltersBlock($selectedFilters), 'productList' => $smarty->fetch(_PS_THEME_DIR_ . 'product-list.tpl'))); // return '<div id="layered_ajax_column">'.$this->generateFiltersBlock($selectedFilters).'</div><div id="layered_ajax_products">'.$smarty->fetch(_PS_THEME_DIR_.'product-list.tpl').'</div>'; }
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 searchTag($id_lang, $tag, $count = false, $pageNumber = 0, $pageSize = 10, $orderBy = false, $orderWay = false, $useCookie = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } // Only use cookie if id_customer is not present if ($useCookie) { $id_customer = (int) $context->customer->id; } else { $id_customer = 0; } if (!is_numeric($pageNumber) || !is_numeric($pageSize) || !Validate::isBool($count) || !Validate::isValidSearch($tag) || $orderBy && !$orderWay || $orderBy && !Validate::isOrderBy($orderBy) || $orderWay && !Validate::isOrderBy($orderWay)) { return false; } if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 10; } $id = Context::getContext()->shop->id; $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT'); $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'); } if ($count) { return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT COUNT(DISTINCT pt.`id_product`) nb FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ') ' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)' : '') . ' WHERE product_shop.`active` = 1 AND p.visibility IN (\'both\', \'search\') AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . ' ' . $sql_groups . ' AND t.`name` LIKE \'%' . pSQL($tag) . '%\''); } $sql = 'SELECT DISTINCT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name, 1 position, DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 new FROM `' . _DB_PREFIX_ . 'product` p INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . Shop::addSqlAssociation('product', 'p', false) . ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`) ' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)' : '') . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ') ' . Product::sqlStock('p', 0) . ' WHERE product_shop.`active` = 1 AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . ' ' . $sql_groups . ' AND t.`name` LIKE \'%' . pSQL($tag) . '%\' GROUP BY product_shop.id_product ORDER BY position DESC' . ($orderBy ? ', ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize; if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) { return false; } return Product::getProductsProperties((int) $id_lang, $result); }
public function getProducts($selected_filters, &$products, &$nb_products, &$p, &$n, &$pages_nb, &$start, &$stop, &$range) { global $cookie; $products = $this->getProductByFilters($selected_filters); $products = Product::getProductsProperties((int) $cookie->id_lang, $products); $nb_products = $this->nbr_products; $range = 2; /* how many pages around page selected */ $n = (int) Tools::getValue('n', Configuration::get('PS_PRODUCTS_PER_PAGE')); $p = $this->page; if ($p < 0) { $p = 0; } if ($p > $nb_products / $n) { $p = ceil($nb_products / $n); } $pages_nb = ceil($nb_products / (int) $n); $start = (int) ($p - $range); if ($start < 1) { $start = 1; } $stop = (int) ($p + $range); if ($stop > $pages_nb) { $stop = (int) $pages_nb; } }
public static function find($id_lang, $expr, $page_number = 1, $page_size = 1, $order_by = 'position', $order_way = 'desc', $ajax = false, $use_cookie = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } $db = Db::getInstance(_PS_USE_SQL_SLAVE_); if ($page_number < 1) { $page_number = 1; } if ($page_size < 1) { $page_size = 1; } if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { return false; } $intersect_array = array(); $score_array = array(); $words = explode(' ', Search::sanitize($expr, $id_lang, false, $context->language->iso_code)); foreach ($words as $key => $word) { if (!empty($word) && strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); $start_search = Configuration::get('PS_SEARCH_START') ? '%' : ''; $end_search = Configuration::get('PS_SEARCH_END') ? '' : '%'; $intersect_array[] = 'SELECT si.id_product FROM ' . _DB_PREFIX_ . 'search_word sw LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word WHERE sw.id_lang = ' . (int) $id_lang . ' AND sw.id_shop = ' . $context->shop->id . ' AND sw.word LIKE ' . ($word[0] == '-' ? ' \'' . $start_search . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'' : ' \'' . $start_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\''); if ($word[0] != '-') { $score_array[] = 'sw.word LIKE \'' . $start_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\''; } } else { unset($words[$key]); } } if (!count($words)) { return $ajax ? array() : array('total' => 0, 'result' => array(), 'painters' => array()); } $score = ''; if (count($score_array)) { $score = ',( SELECT SUM(weight) FROM ' . _DB_PREFIX_ . 'search_word sw LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word WHERE sw.id_lang = ' . (int) $id_lang . ' AND sw.id_shop = ' . $context->shop->id . ' AND si.id_product = p.id_product AND (' . implode(' OR ', $score_array) . ') ) position'; } $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'); } $results = $db->executeS(' SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_product` cp ' . (Group::isFeatureActive() ? 'INNER JOIN `' . _DB_PREFIX_ . 'category_group` cg ON cp.`id_category` = cg.`id_category`' : '') . ' INNER JOIN `' . _DB_PREFIX_ . 'category` c ON cp.`id_category` = c.`id_category` INNER JOIN `' . _DB_PREFIX_ . 'product` p ON cp.`id_product` = p.`id_product` ' . Shop::addSqlAssociation('product', 'p', false) . ' WHERE c.`active` = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "search") AND product_shop.indexed = 1 ' . $sql_groups); $eligible_products = array(); foreach ($results as $row) { $eligible_products[] = $row['id_product']; } foreach ($intersect_array as $query) { $eligible_products2 = array(); foreach ($db->executeS($query) as $row) { $eligible_products2[] = $row['id_product']; } $eligible_products = array_intersect($eligible_products, $eligible_products2); if (!count($eligible_products)) { return $ajax ? array() : array('total' => 0, 'result' => array(), 'painters' => array()); } } $eligible_products = array_unique($eligible_products); $product_pool = ''; foreach ($eligible_products as $id_product) { if ($id_product) { $product_pool .= (int) $id_product . ','; } } if (empty($product_pool)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $product_pool = strpos($product_pool, ',') === false ? ' = ' . (int) $product_pool . ' ' : ' IN (' . rtrim($product_pool, ',') . ') '; if ($ajax) { $sql = 'SELECT DISTINCT p.id_product, pl.name pname, cl.name cname, cl.link_rewrite crewrite, pl.link_rewrite prewrite ' . $score . ' FROM ' . _DB_PREFIX_ . 'product p INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . Shop::addSqlAssociation('product', 'p') . ' INNER JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON ( product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ' ) WHERE p.`id_product` ' . $product_pool . ' ORDER BY position DESC LIMIT 10'; return $db->executeS($sql); } if (strpos($order_by, '.') > 0) { $order_by = explode('.', $order_by); $order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`'; } $alias = ''; if ($order_by == 'price') { $alias = 'product_shop.'; } elseif (in_array($order_by, array('date_upd', 'date_add'))) { $alias = 'p.'; } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name ' . $score . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute' : '') . ', DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 new' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ' FROM ' . _DB_PREFIX_ . 'product p ' . Shop::addSqlAssociation('product', 'p') . ' INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) : Product::sqlStock('p', 'product', false, Context::getContext()->shop)) . ' LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') WHERE p.`id_product` ' . $product_pool . ' GROUP BY product_shop.id_product ' . ($order_by ? 'ORDER BY ' . $alias . $order_by : '') . ($order_way ? ' ' . $order_way : '') . ' LIMIT ' . (int) (($page_number - 1) * $page_size) . ',' . (int) $page_size; $result = $db->executeS($sql); $helper = new Helper(); $categoryTree = $helper->renderCategoryTree(); $sql = 'SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'product p ' . Shop::addSqlAssociation('product', 'p') . ' INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE p.`id_product` ' . $product_pool; $total = $db->getValue($sql); if (!$result) { $result_properties = false; } else { $result_properties = Product::getProductsProperties((int) $id_lang, $result); } $sql = 'SELECT c.*, c_lang.* FROM ' . _DB_PREFIX_ . 'category c INNER JOIN ' . _DB_PREFIX_ . 'category_lang c_lang ON ( c.id_category = c_lang.id_category) WHERE c_lang.name LIKE ' . ($word[0] == '-' ? ' \'' . $end_search . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'' : ' \'' . $end_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'') . 'AND c.id_parent = 14'; $painters = $db->executeS($sql); return array('total' => $total, 'result' => $result_properties, 'painters' => $painters); }
public static function getProducts($id_seller, Context $context, $nb_only = false, $p = 1, $n = 10, $orderby = '', $orderway = '', $extraConditions = '') { ${"GLOBALS"}["vrclqkr"] = "i"; ${"GLOBALS"}["pbytiqghtow"] = "n"; ${"GLOBALS"}["ltsfev"] = "orderway"; $nwslymeftf = "n"; $dofdwuybzvoc = "where"; ${"GLOBALS"}["gpvwfaymmvk"] = "orderway"; ${"GLOBALS"}["qrldutq"] = "orderby"; ${"GLOBALS"}["seyfsbnox"] = "where"; $hjbgtfixonq = "select"; $lkrxunixc = "currency"; ${"GLOBALS"}["ksintkloxpt"] = "extraConditions"; $knllieqm = "where"; $iuyficiewy = "res"; ${$hjbgtfixonq} = "\n\t\t SELECT distinct p.*, pl.name,pl.link_rewrite, cl.name `name_category`, i.`id_image`, 0 AS price_final, sav.`quantity` as sav_quantity\n\t\t ,po.approved\n\t "; $wdbndclu = "extraConditions"; $zkhbgqxbt = "p"; ${"GLOBALS"}["uhjxezafnxq"] = "id_seller"; ${${"GLOBALS"}["ejkvrndxo"]} = "\n\t\t\t\tFROM `" . _DB_PREFIX_ . "product` p\n\t\t\t\tINNER JOIN " . _DB_PREFIX_ . "product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = " . Configuration::get("PS_SHOP_DEFAULT") . ")\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product_lang` pl ON (pl.id_lang=" . $context->language->id . " AND p.`id_product` = pl.`id_product` " . Shop::addSqlRestrictionOnLang("pl") . ")\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`\n\t\t \t\t AND tr.`id_country` = " . (int) $context->country->id . "\n\t\t \t\t AND tr.`id_state` = 0)\n\t \t\t \tLEFT JOIN `" . _DB_PREFIX_ . "tax` t ON (t.`id_tax` = tr.`id_tax`)\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)\n\t\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "supplier` s ON (s.`id_supplier` = p.`id_supplier`)\n\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)\n\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0\n\t\t\t\tAND sav.`id_shop` = " . (int) $context->shop->id . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "category_lang` cl ON (p.id_category_default=cl.id_category AND cl.id_lang=" . $context->language->id . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "product_owner` po ON p.id_product=po.id_product\n\t\t"; $glfdkvtum = "p"; $qpmjux = "orderby"; ${$dofdwuybzvoc} = " WHERE po.id_owner>0 AND po.id_owner = " . intval(${${"GLOBALS"}["uhjxezafnxq"]}) . " \n\t\t"; $wvnwfslh = "sql"; if (${${"GLOBALS"}["ksintkloxpt"]} != "") { ${${"GLOBALS"}["dmjivkgtcqq"]} = ${$knllieqm} . ${$wdbndclu}; } if (${${"GLOBALS"}["armbnpyqs"]}) { return Db::getInstance()->getValue("SELECT COUNT(*) " . ${${"GLOBALS"}["ejkvrndxo"]} . ${${"GLOBALS"}["dmjivkgtcqq"]}); } if (${$glfdkvtum} < 1) { ${${"GLOBALS"}["eadhxox"]} = 1; } if (${${"GLOBALS"}["pbytiqghtow"]} < 1) { ${$nwslymeftf} = 10; } if (empty(${$qpmjux})) { ${${"GLOBALS"}["qrldutq"]} = "p.date_add"; } if (empty(${${"GLOBALS"}["ltsfev"]})) { ${${"GLOBALS"}["gpvwfaymmvk"]} = "DESC"; } ${"GLOBALS"}["ketioqxhsm"] = "n"; ${${"GLOBALS"}["ubgqiwk"]} = " ORDER BY " . ${${"GLOBALS"}["ejmgkwlhbp"]} . " " . ${${"GLOBALS"}["zzomqmcyjd"]} . " \n "; ${"GLOBALS"}["bgbiyfppmhgw"] = "res"; ${"GLOBALS"}["clbwndk"] = "sql"; ${${"GLOBALS"}["nzpalqljyny"]} = "LIMIT " . ((int) ${$zkhbgqxbt} - 1) * (int) ${${"GLOBALS"}["ketioqxhsm"]} . "," . (int) ${${"GLOBALS"}["mmtkcpcxthp"]} . "\n\t\t"; ${$wvnwfslh} = ${${"GLOBALS"}["ypljby"]} . ${${"GLOBALS"}["ejkvrndxo"]} . ${${"GLOBALS"}["seyfsbnox"]} . ${${"GLOBALS"}["ubgqiwk"]} . ${${"GLOBALS"}["nzpalqljyny"]}; ${${"GLOBALS"}["bgbiyfppmhgw"]} = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(${${"GLOBALS"}["clbwndk"]}); if (empty(${${"GLOBALS"}["nsginhlnhc"]})) { return ${${"GLOBALS"}["nsginhlnhc"]}; } ${"GLOBALS"}["cphdwvwffwdp"] = "res"; ${$lkrxunixc} = new Currency((int) Configuration::get("PS_CURRENCY_DEFAULT")); ${${"GLOBALS"}["nsginhlnhc"]} = Product::getProductsProperties(Context::getContext()->cookie->id_lang, ${${"GLOBALS"}["cphdwvwffwdp"]}); for (${${"GLOBALS"}["jhgjtjy"]} = 0; ${${"GLOBALS"}["vrclqkr"]} < count(${$iuyficiewy}); ${${"GLOBALS"}["jhgjtjy"]}++) { ${${"GLOBALS"}["nsginhlnhc"]}[${${"GLOBALS"}["jhgjtjy"]}]["price_final"] = Product::getPriceStatic(${${"GLOBALS"}["nsginhlnhc"]}[${${"GLOBALS"}["jhgjtjy"]}]["id_product"], true, null, 6, null, false, true, 1, true) * $currency->conversion_rate / Context::getContext()->currency->conversion_rate; } return ${${"GLOBALS"}["nsginhlnhc"]}; }
/** * 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); }
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); }
/** * Returns module content for left column */ public function hookProductFooter($params) { $cache_id = 'crossselling_mod|productfooter|' . (int) $params['product']->id; if (!$this->isCached('crossselling_mod.tpl', $this->getCacheId($cache_id))) { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.id_order = o.id_order) WHERE o.valid = 1 AND od.product_id = ' . (int) $params['product']->id); if (count($orders)) { $list = ''; foreach ($orders as $order) { $list .= (int) $order['id_order'] . ','; } $list = rtrim($list, ','); if (Group::isFeatureActive()) { $sql_groups_join = ' LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = product_shop.id_category_default AND cp.id_product = product_shop.id_product) LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)'; $groups = FrontController::getCurrentCustomerGroups(); $sql_groups_where = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '=' . (int) Group::getCurrent()->id); } $order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT DISTINCT od.product_id, pl.name, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price, product_shop.id_category_default, p.available_for_order, p.customizable, cl.link_rewrite category, p.ean13, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM ' . _DB_PREFIX_ . 'order_detail od LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id) ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $this->context->shop) : Product::sqlStock('p', 'product', false, $this->context->shop)) . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = od.product_id' . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = od.product_id) ' . (Group::isFeatureActive() ? $sql_groups_join : '') . ' WHERE od.id_order IN (' . $list . ') AND pl.id_lang = ' . (int) $this->context->language->id . ' AND cl.id_lang = ' . (int) $this->context->language->id . ' AND od.product_id != ' . (int) $params['product']->id . ' AND i.cover = 1 AND product_shop.active = 1 ' . (Group::isFeatureActive() ? $sql_groups_where : '') . ' ORDER BY RAND() LIMIT ' . (int) Configuration::get('CROSSSELLING_NBR_M')); $tax_calc = Product::getTaxCalculationMethod(); foreach ($order_products as &$order_product) { $order_product['id_product'] = (int) $order_product['product_id']; $order_product['image'] = $this->context->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('home')); $order_product['link'] = $this->context->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']); if ($tax_calc == 0 || $tax_calc == 2) { $order_product['price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null); } elseif ($tax_calc == 1) { $order_product['price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null); } $order_product['allow_oosp'] = Product::isAvailableWhenOutOfStock((int) $order_product['out_of_stock']); } $order_products = Product::getProductsProperties((int) $this->context->language->id, $order_products); $this->smarty->assign(array('order' => false, 'orderProducts' => $order_products, 'homeSize' => Image::getSize(ImageType::getFormatedName('home')), 'middlePosition_crossselling' => round(count($order_products) / 2, 0), 'crossDisplayPrice' => Configuration::get('CROSSSELLING_DISPLAY_PRICE_M'))); } } return $this->display(__FILE__, 'crossselling_mod.tpl', $this->getCacheId($cache_id)); }
public static function searchTag($id_lang, $tag, $count = false, $pageNumber = 0, $pageSize = 10, $orderBy = false, $orderWay = false, $useCookie = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } if (!Module::isInstalled('agilemultipleseller') and !Module::isInstalled('agilesellerlistoptions')) { return parent::searchTag($id_lang, $expr, $pageNumber, $pageSize, $orderBy, $orderWay, $ajax, $useCookie, $context); } $agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p"); if ($useCookie) { $id_customer = (int) $context->customer->id; } else { $id_customer = 0; } if (!is_numeric($pageNumber) || !is_numeric($pageSize) || !Validate::isBool($count) || !Validate::isValidSearch($tag) || $orderBy && !$orderWay || $orderBy && !Validate::isOrderBy($orderBy) || $orderWay && !Validate::isOrderBy($orderWay)) { return false; } if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 10; } $id = Context::getContext()->shop->id; $id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT'); if ($count) { $sql = 'SELECT COUNT(DISTINCT pt.`id_product`) nb FROM `' . _DB_PREFIX_ . 'product` p ' . $agile_sql_parts['joins'] . ' ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`) WHERE product_shop.`active` = 1 ' . $agile_sql_parts['wheres'] . ' AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . ' AND cg.`id_group` ' . (!$id_customer ? '= ' . (int) Configuration::get('PS_UNIDENTIFIED_GROUP') : 'IN ( SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $id_customer . ')') . ' AND t.`name` LIKE \'%' . pSQL($tag) . '%\''; return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } $sql = 'SELECT DISTINCT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name, 1 position, DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 new ' . $agile_sql_parts['selects'] . ' FROM `' . _DB_PREFIX_ . 'product` p ' . $agile_sql_parts['joins'] . ' INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . Shop::addSqlAssociation('product', 'p', false) . ' LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`) LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cg.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ') ' . Product::sqlStock('p', 0) . ' WHERE product_shop.`active` = 1 ' . $agile_sql_parts['wheres'] . ' AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . ' AND cg.`id_group` ' . (!$id_customer ? '= ' . (int) Configuration::get('PS_UNIDENTIFIED_GROUP') : 'IN ( SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $id_customer . ')') . ' AND t.`name` LIKE \'%' . pSQL($tag) . '%\' GROUP BY product_shop.id_product ORDER BY position DESC' . ($orderBy ? ', ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize; if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) { return false; } $results = Product::getProductsProperties((int) $id_lang, $result); $results = AgileSellerManager::prepareSellerRattingInfo($results); return $results; }
public function getProducts($selected_filters, &$products, &$nb_products, &$p, &$n, &$pages_nb, &$start, &$stop, &$range, &$combinations) { global $cookie; $products = $this->getProductByFilters($selected_filters); $products = Product::getProductsProperties((int) $cookie->id_lang, $products); $nb_products = $this->nbr_products; $range = 2; /* how many pages around page selected */ $n = (int) Tools::getValue('n', Configuration::get('PS_PRODUCTS_PER_PAGE')); if ($n <= 0) { $n = 1; } $p = $this->page; if ($p < 0) { $p = 0; } if ($p > $nb_products / $n) { $p = ceil($nb_products / $n); } $pages_nb = ceil($nb_products / (int) $n); $start = (int) ($p - $range); if ($start < 1) { $start = 1; } $stop = (int) ($p + $range); if ($stop > $pages_nb) { $stop = (int) $pages_nb; } foreach ($products as &$product) { if ($product['id_product_attribute'] && isset($product['product_attribute_minimal_quantity'])) { $product['minimal_quantity'] = $product['product_attribute_minimal_quantity']; } $product_obj = new Product((int) $product['id_product'], $this->context->language->id); $product['groups'] = $product_obj->getProductCombinationsGroups((int) $product['id_product']); $product['obj'] = $product_obj; $combinations[$product['id_product']] = $product_obj->getProductAttributeCombinations($product['id_product']); $id_group = (int) Group::getCurrent()->id; $group_reduction = GroupReduction::getValueForProduct($product['id_product'], $id_group); if ($group_reduction === false) { $group_reduction = Group::getReduction((int) $this->context->cookie->id_customer) / 100; } $product['group_reduction'] = $group_reduction; $address = new Address($this->context->cart->{Configuration::get('PS_TAX_ADDRESS_TYPE')}); $product['no_tax'] = Tax::excludeTaxeOption() || !$product_obj->getTaxesRate($address); $product['customer_group_without_tax'] = Group::getPriceDisplayMethod($this->context->customer->id_default_group); $tax = (double) $product_obj->getTaxesRate(new Address((int) $this->context->cart->{Configuration::get('PS_TAX_ADDRESS_TYPE')})); $product['tax_rate'] = $tax; $product['unit_price_ratio'] = $product_obj->unit_price_ratio; $ecotax_rate = (double) Tax::getProductEcotaxRate($this->context->cart->{Configuration::get('PS_TAX_ADDRESS_TYPE')}); $product['ecotaxTax_rate'] = $ecotax_rate; } }