/** * Get all available glossaries * * @param integer $id_lang Language id * @param array $criteria Criterias for where clause * @param integer $start Start number * @param integer $limit Number of glossaries to return * @param string $order_by Field for ordering * @param string $order_way Way for ordering (ASC or DESC) * @param boolean $only_active Returns only active glossaries if TRUE * @param Context|null $context * * @return array list of glossaries */ public static function getGlossaries($id_lang, $criteria = array(), $start = null, $limit = null, $order_by = null, $order_way = null, $only_active = false, Context $context = null) { $where = ''; if (!$context) { $context = Context::getContext(); } if ($order_by !== null) { if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { die(Tools::displayError()); } if ($order_by == 'id') { $order_by_prefix = 'l'; } else { $order_by_prefix = 'll'; } if (strpos($order_by, '.') > 0) { $order_by = explode('.', $order_by); $order_by_prefix = $order_by[0]; $order_by = $order_by[1]; } } if (isset($criteria['k'])) { $where .= " AND ll.name LIKE '" . pSQL($criteria['k']) . "%' "; } $sql = 'SELECT l.*, ll.* FROM `' . _DB_PREFIX_ . 'lexikotron` l LEFT JOIN `' . _DB_PREFIX_ . 'lexikotron_lang` ll ON (l.`id_lexikotron` = ll.`id_lexikotron`) WHERE ll.`id_lang` = ' . (int) $id_lang . ($only_active ? ' AND l.`active` = 1' : '') . ' ' . $where . ' ' . ($order_by != null ? 'ORDER BY ' . (isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way) : '') . ($limit > 0 ? ' LIMIT ' . (int) $start . ',' . (int) $limit : ''); $rows = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); return $rows; }
public function getList($order_by, $order_way, $filter, $start, $pagination) { $order_way = Validate::isOrderWay($order_way) ? $order_way : 'ASC'; $id_shop = (int) Context::getContext()->shop->id; $id_lang = (int) Context::getContext()->language->id; $list = DB::getInstance()->executeS(' SELECT p.`id_order` AS `id_order`, par.`waybill` AS `id_parcel`, CONCAT(a.`firstname`, " ", a.`lastname`) AS `receiver`, cl.`name` AS `country`, a.`postcode` AS `postcode`, a.`city` AS `city`, a.`address1` AS `address`, p.`date_add` AS `date_add` FROM `' . _DB_PREFIX_ . _DPDPOLAND_PARCEL_DB_ . '` par LEFT JOIN `' . _DB_PREFIX_ . _DPDPOLAND_PACKAGE_DB_ . '` p ON (p.`id_package_ws` = par.`id_package_ws`) LEFT JOIN `' . _DB_PREFIX_ . 'orders` o ON (o.`id_order` = p.`id_order`) LEFT JOIN `' . _DB_PREFIX_ . 'address` a ON (a.`id_address` = p.`id_address_delivery`) LEFT JOIN `' . _DB_PREFIX_ . 'country_lang` cl ON (cl.`id_country` = a.`id_country` AND cl.`id_lang` = "' . (int) $id_lang . '")' . (version_compare(_PS_VERSION_, '1.5', '<') ? ' ' : 'WHERE o.`id_shop` = "' . (int) $id_shop . '" ') . $filter . ($order_by && $order_way ? ' ORDER BY `' . bqSQL($order_by) . '` ' . pSQL($order_way) : '') . ($start !== null && $pagination !== null ? ' LIMIT ' . (int) $start . ', ' . (int) $pagination : '')); if (!$list) { $list = array(); } return $list; }
public function __construct() { global $cookie; $this->table = 'message'; $this->className = 'Message'; $this->view = 'noActionColumn'; $this->delete = true; $this->colorOnBackground = true; $start = 0; $this->_defaultOrderBy = 'date_add'; /* Manage default params values */ if (empty($limit)) { $limit = !isset($cookie->{$this->table . '_pagination'}) ? $this->_pagination[0] : ($limit = $cookie->{$this->table . '_pagination'}); } if (!Validate::isTableOrIdentifier($this->table)) { die(Tools::displayError('Table name is invalid:') . ' "' . $this->table . '"'); } if (empty($orderBy)) { $orderBy = Tools::getValue($this->table . 'Orderby', $this->_defaultOrderBy); } elseif ($orderBy == 'id_order') { $orderBy = 'm.id_order'; } if (empty($orderWay)) { $orderWay = Tools::getValue($this->table . 'Orderway', 'ASC'); } $limit = (int) Tools::getValue('pagination', $limit); $cookie->{$this->table . '_pagination'} = $limit; /* Check params validity */ if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay) or !is_numeric($start) or !is_numeric($limit)) { die(Tools::displayError('get list params is not valid')); } if ($orderBy == 'id_order') { $orderBy = 'm.id_order'; } /* Determine offset from current page */ if ((isset($_POST['submitFilter' . $this->table]) or isset($_POST['submitFilter' . $this->table . '_x']) or isset($_POST['submitFilter' . $this->table . '_y'])) and !empty($_POST['submitFilter' . $this->table]) and is_numeric($_POST['submitFilter' . $this->table])) { $start = (int) ($_POST['submitFilter' . $this->table] - 1) * $limit; } $sql = 'SELECT SQL_CALC_FOUND_ROWS m.id_message, m.id_cart, m.id_employee, IF(m.id_order > 0, m.id_order, \'--\') id_order, m.message, m.private, m.date_add, CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS customer, c.id_customer, count(m.id_message) nb_messages, (SELECT message FROM ' . _DB_PREFIX_ . 'message WHERE id_order = m.id_order ORDER BY date_add DESC LIMIT 1) last_message, (SELECT COUNT(m2.id_message) FROM ' . _DB_PREFIX_ . 'message m2 WHERE 1 AND m2.id_customer != 0 AND m2.id_order = m.id_order AND m2.id_message NOT IN (SELECT mr2.id_message FROM ' . _DB_PREFIX_ . 'message_readed mr2 WHERE mr2.id_employee = ' . (int) $cookie->id_employee . ') GROUP BY m2.id_order) nb_messages_not_read_by_me FROM ' . _DB_PREFIX_ . 'message m LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON (o.id_order = m.id_order) LEFT JOIN ' . _DB_PREFIX_ . 'customer c ON (c.id_customer = m.id_customer) GROUP BY m.id_order ORDER BY ' . (isset($orderBy) ? pSQL($orderBy) : 'date_add') . ' ' . (isset($orderWay) ? pSQL($orderWay) : 'DESC') . ' LIMIT ' . (int) $start . ',' . (int) $limit; $this->_list = Db::getInstance()->ExecuteS($sql); $this->_listTotal = Db::getInstance()->getValue('SELECT FOUND_ROWS() AS `' . md5($sql) . '`'); $this->fieldsDisplay = array('id_order' => array('title' => $this->l('Order ID'), 'align' => 'center', 'width' => 30), 'id_customer' => array('title' => $this->l('Customer ID'), 'align' => 'center', 'width' => 30), 'customer' => array('title' => $this->l('Customer'), 'width' => 100, 'filter_key' => 'customer', 'tmpTableFilter' => true), 'last_message' => array('title' => $this->l('Last message'), 'width' => 400, 'orderby' => false), 'nb_messages_not_read_by_me' => array('title' => $this->l('Unread message(s)'), 'width' => 30, 'align' => 'center'), 'nb_messages' => array('title' => $this->l('Number of messages'), 'width' => 30, 'align' => 'center')); parent::__construct(); }
public function getNewProducts($id_lang, $nbProducts = 4, $orderBy = NULL, $orderWay = NULL, $random = true, $randomNumberProducts = 4) { global $cookie; if (empty($orderBy) || $orderBy == 'position') { $orderBy = 'date_add'; } if (empty($orderWay)) { $orderWay = 'DESC'; } if ($orderBy == 'id_product' || $orderBy == 'price' || $orderBy == 'date_add') { $orderByPrefix = 'p'; } elseif ($orderBy == 'name') { $orderByPrefix = 'pl'; } if (!Validate::isOrderBy($orderBy) || !Validate::isOrderWay($orderWay)) { die(Tools::displayError()); } $sql = ' SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`, i.`id_image`, il.`legend`, t.`rate`, 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, (p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = p.`id_tax`) LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) WHERE p.`active` = 1 AND 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) 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) . ')') . ' )'; if ($random === true) { $sql .= ' ORDER BY RAND()'; $sql .= ' LIMIT 0, ' . intval($randomNumberProducts); } else { $sql .= ' ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . ' LIMIT 0, ' . intval($nbProducts); } $result = Db::getInstance()->ExecuteS($sql); if ($orderBy == 'price') { Tools::orderbyPrice($result, $orderWay); } if (!$result) { return false; } return Product::getProductsProperties(intval($id_lang), $result); }
public function getList($order_by, $order_way, $filter, $start, $pagination) { $order_way = Validate::isOrderWay($order_way) ? $order_way : 'ASC'; return Db::getInstance()->executeS(' SELECT m.`id_manifest_ws` AS `id_manifest_ws`, COUNT(p.`id_parcel`) AS `count_parcels`, COUNT(DISTINCT m.`id_package_ws`) AS `count_orders`, m.`date_add` AS `date_add` FROM `' . _DB_PREFIX_ . _DPDPOLAND_MANIFEST_DB_ . '` m LEFT JOIN `' . _DB_PREFIX_ . _DPDPOLAND_PARCEL_DB_ . '` p ON (p.`id_package_ws` = m.`id_package_ws`) GROUP BY `id_manifest_ws` ' . $filter . ' ORDER BY `' . bqSQL($order_by) . '` ' . pSQL($order_way) . ($start !== null && $pagination !== null ? ' LIMIT ' . (int) $start . ', ' . (int) $pagination : '')); }
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; }
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 find($id_lang, $expr, $pageNumber = 1, $pageSize = 1, $orderBy = 'position', $orderWay = 'desc', $ajax = false, $useCookie = true) { global $cookie; $db = Db::getInstance(_PS_USE_SQL_SLAVE_); // Only use cookie if id_customer is not present if ($useCookie) { $id_customer = (int) $cookie->id_customer; } else { $id_customer = 0; } // TODO : smart page management if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 1; } if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) { return false; } $intersectArray = array(); $scoreArray = array(); $words = explode(' ', Search::sanitize($expr, (int) $id_lang)); foreach ($words as $key => $word) { if (!empty($word) and strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); $intersectArray[] = '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 = ' . (int) $id_lang . ' AND sw.word LIKE ' . ($word[0] == '-' ? ' \'' . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'' : '\'' . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''); if ($word[0] != '-') { $scoreArray[] = 'sw.word LIKE \'' . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''; } } else { unset($words[$key]); } } if (!sizeof($words)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $score = ''; if (sizeof($scoreArray)) { $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 si.id_product = p.id_product AND (' . implode(' OR ', $scoreArray) . ') ) position'; } $result = $db->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` 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` WHERE c.`active` = 1 AND p.`active` = 1 AND indexed = 1 AND cg.`id_group` ' . (!$id_customer ? '= 1' : 'IN ( SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $id_customer . ' )'), false); $eligibleProducts = array(); $eligibleProductsSS = array(); $productsSuperSet = array(); while ($row = $db->nextRow($result)) { $eligibleProducts[] = $row['id_product']; $eligibleProductsSS[] = $row['id_product']; } foreach ($intersectArray as $query) { $result = $db->ExecuteS($query, false); $eligibleProducts2 = array(); while ($row = $db->nextRow($result)) { $eligibleProducts2[] = $row['id_product']; $productsSuperSet[] = $row['id_product']; } $eligibleProducts = array_intersect($eligibleProducts, $eligibleProducts2); //if (!count($eligibleProducts)) //return ($ajax ? array() : array('total' => 0, 'result' => array())); } $ssResults = 0; //replace eligible products with superset if OR query or zero results found if (Tools::getValue('ss') || sizeof($eligibleProducts) == 0) { $eligibleProducts = array_intersect($eligibleProductsSS, $productsSuperSet); $ssResults = 1; } array_unique($eligibleProducts); $productPool = ''; foreach ($eligibleProducts as $id_product) { if ($id_product) { $productPool .= (int) $id_product . ','; } } if (empty($productPool)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $productPool = strpos($productPool, ',') === false ? ' = ' . (int) $productPool . ' ' : ' IN (' . rtrim($productPool, ',') . ') '; if ($ajax) { return $db->ExecuteS(' 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 . ') INNER JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . ') WHERE p.`id_product` ' . $productPool . ' ORDER BY position DESC LIMIT 10'); } $queryResults = ' SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name ' . $score . ', 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, IF((p.quantity > 0), 1, 0) as instock 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 . ') 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` tax ON (tax.`id_tax` = tr.`id_tax`) 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` 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 . ') WHERE p.`id_product` ' . $productPool . ' ' . ($orderBy ? 'ORDER BY instock desc, ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize; $result = $db->ExecuteS($queryResults); $total = $db->getValue('SELECT COUNT(*) 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 . ') 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` tax ON (tax.`id_tax` = tr.`id_tax`) 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` 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 . ') WHERE p.`id_product` ' . $productPool); if (!$result) { $resultProperties = false; } else { $resultProperties = Product::getProductsProperties((int) $id_lang, $result); } return array('total' => $total, 'result' => $resultProperties, 'ssResults' => $ssResults); }
public static function find($id_lang, $expr, $pageNumber = 1, $pageSize = 1, $orderBy = 'position', $orderWay = 'desc', $ajax = false) { global $cookie; // TODO : smart page management if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 1; } if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) { die(Tools::displayError()); } $whereArray = array(); $scoreArray = array(); $words = explode(' ', Search::sanitize($expr, $id_lang)); 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)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $score = ''; if (sizeof($scoreArray)) { $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 = ' . intval($id_lang) . ' AND si.id_product = p.id_product AND (' . implode(' OR ', $scoreArray) . ') ) as position'; } if ($ajax) { $queryResults = ' SELECT p.id_product, pl.name as pname, IF(cl.name REGEXP "^[0-9]{2}\\.", SUBSTRING(cl.name, 4), cl.name) as cname ' . $score . ', cl.link_rewrite as crewrite, pl.link_rewrite as prewrite 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_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . intval($id_lang) . ') WHERE ' . implode(' AND ', $whereArray) . ' 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 position DESC LIMIT 10'; return Db::getInstance()->ExecuteS($queryResults); } $queryResults = ' SELECT SQL_CALC_FOUND_ROWS p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name ' . $score . ' 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 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) . ') LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (p.`id_tax` = t.`id_tax`) LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) WHERE ' . implode(' AND ', $whereArray) . ' 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) . ')') . ' ) ' . ($orderBy ? 'ORDER BY ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . intval(($pageNumber - 1) * $pageSize) . ',' . intval($pageSize); $result = Db::getInstance()->ExecuteS($queryResults); $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 $getTotal return the number of results instead of the results themself * @param boolean $active return only active products * @param boolean $random active a random filter for returned products * @param int $randomNumberProducts number of products to return if random is activated * @return mixed Products or number of products */ public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1) { global $cookie; if ($p < 1) { $p = 1; } if (empty($orderBy)) { $orderBy = 'position'; } if (empty($orderWay)) { $orderWay = 'ASC'; } if ($orderBy == 'id_product' or $orderBy == 'date_add') { $orderByPrefix = 'p'; } elseif ($orderBy == 'name') { $orderByPrefix = 'pl'; } elseif ($orderBy == 'manufacturer') { $orderByPrefix = 'm'; $orderBy = 'name'; } elseif ($orderBy == 'position') { $orderByPrefix = 'cp'; } if ($orderBy == 'price') { $orderBy = 'orderprice'; } if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) { die(Tools::displayError()); } $id_supplier = intval(Tools::getValue('id_supplier')); /* Return only the number of products */ if ($getTotal) { $result = Db::getInstance()->getRow(' SELECT COUNT(cp.`id_product`) AS total FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product` WHERE cp.`id_category` = ' . intval($this->id) . ($active ? ' AND p.`active` = 1' : '') . ' ' . ($id_supplier ? 'AND p.id_supplier = ' . $id_supplier : '') . ''); return isset($result) ? $result['total'] : 0; } $sql = ' SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 AS new, (p.price - IF((DATEDIFF(reduction_from, CURDATE()) <= 0 AND DATEDIFF(reduction_to, CURDATE()) >=0) OR reduction_from = reduction_to, IFNULL(reduction_price, (p.price * reduction_percent / 100)),0)) AS orderprice FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1) LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON t.`id_tax` = p.`id_tax` LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE cp.`id_category` = ' . intval($this->id) . ($active ? ' AND p.`active` = 1' : '') . ' ' . ($id_supplier ? 'AND p.id_supplier = ' . $id_supplier : ''); if ($random === true) { $sql .= 'ORDER BY RAND()'; $sql .= 'LIMIT 0, ' . intval($randomNumberProducts); } else { $sql .= 'ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . ' LIMIT ' . (intval($p) - 1) * intval($n) . ',' . intval($n); } $result = Db::getInstance()->ExecuteS($sql); if ($orderBy == 'orderprice') { Tools::orderbyPrice($result, $orderWay); } if (!$result) { return false; } /* Modify SQL result */ return Product::getProductsProperties($id_lang, $result); }
public static function getProducts($id_manufacturer, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $active_category = true, Context $context = null) { /* * EU-Legal * get standard shipping time from database pl.* */ if (!$context) { $context = Context::getContext(); } $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } if ($p < 1) { $p = 1; } if (empty($order_by) || $order_by == 'position') { $order_by = 'name'; } if (empty($order_way)) { $order_way = 'ASC'; } if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { die(Tools::displayError()); } $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'; /* Return only the number of products */ if ($get_total) { $sql = ' SELECT p.`id_product` FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' WHERE p.id_manufacturer = ' . (int) $id_manufacturer . ($active ? ' AND product_shop.`active` = 1' : '') . ' ' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' AND p.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)' . ($active_category ? ' INNER JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1' : '') . ' WHERE cg.`id_group` ' . $sql_groups . ' )'; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); return (int) count($result); } if (strpos($order_by, '.') > 0) { $order_by = explode('.', $order_by); $order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`'; } $alias = ''; if ($order_by == 'price') { $alias = 'product_shop.'; } elseif ($order_by == 'name') { $alias = 'pl.'; } elseif ($order_by == 'manufacturer_name') { $order_by = 'name'; $alias = 'm.'; } elseif ($order_by == 'quantity') { $alias = 'stock.'; } else { $alias = 'p.'; } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute , pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, pl.`delivery_now`, pl.`delivery_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, DATEDIFF( product_shop.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 AS new' . (Combination::isFeatureActive() ? ',MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ' FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') : '') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) ' . Product::sqlStock('p', 0); if (Group::isFeatureActive() || $active_category) { $sql .= 'JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (p.id_product = cp.id_product)'; if (Group::isFeatureActive()) { $sql .= 'JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category` AND cg.`id_group` ' . $sql_groups . ')'; } if ($active_category) { $sql .= 'JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1'; } } $sql .= ' WHERE p.`id_manufacturer` = ' . (int) $id_manufacturer . ' ' . ($active ? ' AND product_shop.`active` = 1' : '') . ' ' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' GROUP BY product_shop.id_product ORDER BY ' . $alias . '`' . bqSQL($order_by) . '` ' . pSQL($order_way) . ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if (!$result) { return false; } if ($order_by == 'price') { Tools::orderbyPrice($result, $order_way); } return Product::getProductsProperties($id_lang, $result); }
protected function _getProducts($categoryIds = array(), $on_condition = 'all', $on_sale = 2, $on_new = 2, $on_discount = 2, $id_lang, $p, $n, $order_by = null, $order_way = null, $beginning = null, $ending = null, $deal = false, $get_total = false, $active = true, $random = false, $random_number_products = 1, Context $context = null) { if (!$categoryIds) { return array(); } $where = ""; if ($on_condition != 'all') { $where .= " AND p.condition = '" . $on_condition . "' "; } if ($on_sale != 2) { $where .= " AND p.on_sale = '" . $on_sale . "' "; } Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? $PS_NB_DAYS_NEW_PRODUCT = (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : ($PS_NB_DAYS_NEW_PRODUCT = 20); if ($on_new == 0) { $where .= " AND product_shop.`date_add` <= '" . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . "' "; } elseif ($on_new == 1) { $where .= " AND product_shop.`date_add` > '" . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . "' "; } $ids_product = ''; if ($on_discount == 0) { $current_date = date('Y-m-d H:i:s'); $product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal); if ($product_reductions) { $ids_product = ' AND ('; foreach ($product_reductions as $product_reduction) { $ids_product .= '( product_shop.`id_product` != ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' OR product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') AND'; } $ids_product = rtrim($ids_product, 'AND') . ')'; } } elseif ($on_discount == 1) { $current_date = date('Y-m-d H:i:s'); $product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal); if ($product_reductions) { $ids_product = ' AND ('; foreach ($product_reductions as $product_reduction) { $ids_product .= '( product_shop.`id_product` = ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' AND product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') OR'; } $ids_product = rtrim($ids_product, 'OR') . ')'; } else { if ($deal == true) { return array(); } } } else { if ($order_by == 'discount') { $current_date = date('Y-m-d H:i:s'); $product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal); if ($product_reductions) { $ids_product = ' AND ('; foreach ($product_reductions as $product_reduction) { $ids_product .= '( product_shop.`id_product` = ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' AND product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') OR'; } $ids_product = rtrim($ids_product, 'OR') . ')'; } } } if ($ids_product) { $where .= $ids_product; } if (!$context) { $context = Context::getContext(); } $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } if ($p < 1) { $p = 1; } if (empty($order_by)) { $order_by = 'position'; } else { $order_by = strtolower($order_by); } if (empty($order_way)) { $order_way = 'ASC'; } $order_by_prefix = false; $addJoin = ''; $addSelect = ''; if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd') { $order_by_prefix = 'p'; } elseif ($order_by == 'name') { $order_by_prefix = 'pl'; } elseif ($order_by == 'manufacturer' || $order_by == 'manufacturer_name') { $order_by_prefix = 'm'; $order_by = 'name'; } elseif ($order_by == 'position') { $order_by_prefix = 'cp'; } elseif ($order_by == 'discount') { $order_by_prefix = 'sp'; $order_by = 'reduction'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.`id_product` = sp.`id_product` '; $addSelect = ', sp.reduction, sp.`from`, sp.`to`'; } elseif ($order_by == 'review') { $order_by_prefix = ''; $order_by = 'total_review'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pr ON pr.`id_product` = p.`id_product` '; $addSelect = ', COUNT(pr.grade) as total_review'; } elseif ($order_by == 'view') { $order_by_prefix = ''; $order_by = 'total_view'; $addJoin = ' LEFT JOIN ' . _DB_PREFIX_ . 'simplecategory_product_view as pv ON pv.`product_id` = p.`id_product` '; $addSelect = ', pv.total as total_view'; } elseif ($order_by == 'rate') { $order_by_prefix = ''; $order_by = 'total_avg'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pr ON pr.`id_product` = p.`id_product` '; $addSelect = ', (SUM(pr.`grade`) / COUNT(pr.`grade`)) AS total_avg'; } elseif ($order_by == 'seller') { $order_by_prefix = ''; $order_by = 'sales'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_sale` ps ON ps.`id_product` = p.`id_product` '; $addSelect = ', ps.`quantity` AS sales'; } if ($order_by != 'reduction' && $on_discount != 2) { $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.`id_product` = sp.`id_product` '; $addSelect = ', sp.reduction, sp.`from`, sp.`to`'; } if ($order_by == 'price') { $order_by = 'orderprice'; } if (!Validate::isBool($active) || !Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { die(Tools::displayError()); } $id_supplier = (int) Tools::getValue('id_supplier'); if ($get_total) { $sql = 'SELECT COUNT(cp.`id_product`) AS total FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' ' . $addJoin . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product` WHERE cp.`id_category` IN (' . implode(', ', $categoryIds) . ') ' . $where . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . ($ids_product ? $ids_product : '') . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : ''); return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } $sql = 'SELECT DISTINCT p.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`, MAX(image_shop.`id_image`) id_image, il.`legend`, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable, IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock, product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . '" as `new`, product_shop.`on_sale`, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, product_shop.price AS orderprice ' . $addSelect . ' FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` ' . Shop::addSqlAssociation('product', 'p') . $addJoin . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) : Product::sqlStock('p', 'product', false, Context::getContext()->shop)) . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`) ' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . ' AND cp.`id_category` IN (' . implode(', ', $categoryIds) . ') ' . $where . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '') . ' GROUP BY product_shop.id_product'; if ($random === true) { $sql .= ' ORDER BY RAND() LIMIT ' . (int) $random_number_products; } else { $sql .= ' ORDER BY ' . (!empty($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . bqSQL($order_by) . '` ' . pSQL($order_way) . ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if ($order_by == 'orderprice') { Tools::orderbyPrice($result, $order_way); } if (!$result) { return array(); } return Product::getProductsProperties($id_lang, $result); }
protected function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1) { $lgngixep = "orderBy"; ${"GLOBALS"}["xbbxtwtpju"] = "orderBy"; ${"GLOBALS"}["oswfwomb"] = "orderBy"; global $cookie, $smarty; ${"GLOBALS"}["sxnzkqefjkv"] = "p"; ${"GLOBALS"}["kjemucya"] = "id_seller_country"; ${"GLOBALS"}["plbenbkqq"] = "orderBy"; $suwenwdcxtu = "orderByPrefix"; ${${"GLOBALS"}["kjemucya"]} = (int) Tools::getValue("id_seller_country"); $ljwznznuwp = "n"; ${"GLOBALS"}["vwrkkkltgp"] = "orderWay"; ${"GLOBALS"}["kgoezfvpcfs"] = "orderBy"; ${"GLOBALS"}["iiuykexa"] = "requiredcond"; $eqwznppjxw = "orderWay"; ${"GLOBALS"}["juwlrd"] = "orderWay"; $ycvcyogg = "n"; ${"GLOBALS"}["ejwtvva"] = "wheres"; ${"GLOBALS"}["lkqzhjp"] = "p"; ${"GLOBALS"}["vmpjiojlv"] = "orderBy"; ${"GLOBALS"}["qksmteq"] = "orderBy"; if (${${"GLOBALS"}["sxnzkqefjkv"]} < 1) { ${${"GLOBALS"}["lkqzhjp"]} = 1; } if (${$ycvcyogg} <= 0) { ${$ljwznznuwp} = 10; } $yciweepkl = "orderBy"; ${"GLOBALS"}["xmtrrpsmnv"] = "wheres"; $hjhvngkubb = "location_conditions"; if (empty(${${"GLOBALS"}["sqkchfbmzgt"]})) { ${${"GLOBALS"}["sqkchfbmzgt"]} = "price"; } else { ${${"GLOBALS"}["vmpjiojlv"]} = strtolower(${${"GLOBALS"}["oswfwomb"]}); } $qsfzjtrtvb = "result"; if (empty(${$eqwznppjxw})) { ${${"GLOBALS"}["vwrkkkltgp"]} = "ASC"; } if (${${"GLOBALS"}["xbbxtwtpju"]} == "id_product" or ${$lgngixep} == "date_add") { ${${"GLOBALS"}["holuwdvhswv"]} = "p"; } elseif (${${"GLOBALS"}["kgoezfvpcfs"]} == "name") { ${$suwenwdcxtu} = "pl"; } elseif (${${"GLOBALS"}["sqkchfbmzgt"]} == "manufacturer") { ${"GLOBALS"}["obzythrk"] = "orderByPrefix"; ${"GLOBALS"}["ijxsporb"] = "orderBy"; ${${"GLOBALS"}["obzythrk"]} = "m"; ${${"GLOBALS"}["ijxsporb"]} = "name"; } if (${$yciweepkl} == "price") { ${${"GLOBALS"}["qksmteq"]} = "orderprice"; } if (!Validate::isBool(${${"GLOBALS"}["mjjjizv"]}) or !Validate::isOrderBy(${${"GLOBALS"}["plbenbkqq"]}) or !Validate::isOrderWay(${${"GLOBALS"}["juwlrd"]})) { die(Tools::displayError()); } ${"GLOBALS"}["durjvvw"] = "joins"; ${"GLOBALS"}["byhruvgfcplf"] = "orderWay"; ${${"GLOBALS"}["ccpfwfk"]} = ""; $kaqgrgykux = "sql"; if (intval(Configuration::get("AGILE_MS_PRODUCT_APPROVAL")) == 1) { ${${"GLOBALS"}["iiuykexa"]} = " AND po.approved = 1 "; } ${${"GLOBALS"}["durjvvw"]} = ""; ${${"GLOBALS"}["xmtrrpsmnv"]} = ""; if (Module::isInstalled("agilesellerlistoptions")) { require_once _PS_ROOT_DIR_ . "/modules/agilesellerlistoptions/agilesellerlistoptions.php"; ${"GLOBALS"}["yyvptwoqtsy"] = "wheres"; ${${"GLOBALS"}["qjbfcmajvhyd"]} = ${${"GLOBALS"}["qjbfcmajvhyd"]} . "\n LEFT JOIN `" . _DB_PREFIX_ . "seller_listoption` slb ON (p.id_product = slb.id_product AND slb.id_option = " . AgileSellerListOptions::ASLO_OPTION_LIST . ")\n "; $ghjluom = "aslo_list_prod_id"; ${${"GLOBALS"}["ppufwe"]} = intval(Configuration::get("ASLO_PROD_FOR_OPTION" . AgileSellerListOptions::ASLO_OPTION_LIST)); ${${"GLOBALS"}["hylelef"]} = ${${"GLOBALS"}["yyvptwoqtsy"]} . " \n \t\t AND (slb.status = " . AgileSellerListOptions::ASLO_STATUS_IN_EFFECT . " OR IFNULL(po.id_owner,0) = 0 OR " . ${$ghjluom} . "=" . AgileSellerListOptions::ASLO_ALWAYS_FREE . ")\n "; } ${${"GLOBALS"}["lrjoesxigovv"]} = ""; switch ($this->location_level) { case "country": if ((int) $this->id_location > 0) { ${$hjhvngkubb} = " AND si.id_country=" . (int) $this->id_location; } break; case "state": if ((int) $this->id_location > 0) { ${${"GLOBALS"}["lrjoesxigovv"]} = " AND si.id_state=" . (int) $this->id_location; } break; case "city": if (!empty($this->id_location)) { ${${"GLOBALS"}["lrjoesxigovv"]} = " AND sil.city='" . $this->id_location . "'"; } break; case "sellertype": if (!empty($this->id_location)) { ${${"GLOBALS"}["lrjoesxigovv"]} = " AND si.id_sellertype1=" . $this->id_location; } break; case "custom": if (!empty($this->id_location)) { if (AgileMultipleShop::SHOP_BY_CUSTOM_LANG) { ${${"GLOBALS"}["lrjoesxigovv"]} = " AND sil." . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . "='" . $this->id_location . "'"; } else { ${${"GLOBALS"}["lrjoesxigovv"]} = " AND si." . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . "='" . $this->id_location . "'"; } } break; } if (${${"GLOBALS"}["jizkwekrhsnv"]}) { $nlecsweh = "wheres"; ${"GLOBALS"}["yngbfuljr"] = "location_conditions"; $dhfiohgz = "result"; $rgelmcniqy = "requiredcond"; $ccechvvirsfn = "result"; $htkduiwgscwl = "sql"; ${$htkduiwgscwl} = "\n\t\t\tSELECT COUNT(po.`id_product`) AS total\n\t\t\tFROM `" . _DB_PREFIX_ . "product` p\n\t\t\tLEFT JOIN `" . _DB_PREFIX_ . "product_owner` po ON p.`id_product` = po.`id_product`\n\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo` si ON si.`id_seller` = po.`id_owner`\n\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = " . $cookie->id_lang . "\n\t\t\t" . ${${"GLOBALS"}["qjbfcmajvhyd"]} . "\n\t\t\tWHERE p.active=1 \n\t\t\t\t" . ${${"GLOBALS"}["yngbfuljr"]} . "\n\t\t\t " . (${${"GLOBALS"}["mjjjizv"]} ? " AND p.`active` = 1" : "") . "\n\t\t\t\t" . ${$rgelmcniqy} . "\n\t\t\t\t" . ${$nlecsweh} . "\n\t\t\t "; ${$dhfiohgz} = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow(${${"GLOBALS"}["zrrhonhgudxt"]}); $ygrlibnhobc = "result"; return isset(${$ygrlibnhobc}) ? ${$ccechvvirsfn}["total"] : 0; } ${${"GLOBALS"}["zrrhonhgudxt"]} = "\n\t\t SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL " . (Validate::isUnsignedInt(Configuration::get("PS_NB_DAYS_NEW_PRODUCT")) ? Configuration::get("PS_NB_DAYS_NEW_PRODUCT") : 20) . " DAY)) > 0 AS new,\n\t\t\t (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice\n\t\t FROM `" . _DB_PREFIX_ . "product_owner` po\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo` si ON si.`id_seller` = po.`id_owner`\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = " . $cookie->id_lang . "\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "product` p ON p.`id_product` = po.`id_product`\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`\n\t\t AND tr.`id_country` = " . (int) (_PS_VERSION_ > "1.5" ? Context::getContext()->country->id : Country::getDefaultCountryId()) . "\n\t \t AND tr.`id_state` = 0)\n\t LEFT JOIN `" . _DB_PREFIX_ . "tax` t ON (t.`id_tax` = tr.`id_tax`)\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = " . (int) $cookie->id_lang . ")\n\t\t LEFT JOIN `" . _DB_PREFIX_ . "manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`\n\t\t\t\t" . ${${"GLOBALS"}["qjbfcmajvhyd"]} . "\n\t\t WHERE p.active=1 \n\t\t\t\t\t" . ${${"GLOBALS"}["lrjoesxigovv"]} . "\n \t\t\t" . ${${"GLOBALS"}["ccpfwfk"]} . "\n\t\t\t\t\t" . ${${"GLOBALS"}["ejwtvva"]} . "\n\t\t "; if (${${"GLOBALS"}["urnpvrvfs"]} === true) { ${"GLOBALS"}["xeqntuoo"] = "sql"; $igirpiqeuqmm = "sql"; ${${"GLOBALS"}["xeqntuoo"]} .= " ORDER BY RAND()"; ${$igirpiqeuqmm} .= " LIMIT 0, " . (int) ${${"GLOBALS"}["qbznfx"]}; } else { $mwoxesiw = "orderByPrefix"; ${"GLOBALS"}["mokqqxt"] = "n"; $tydfnpg = "n"; ${${"GLOBALS"}["zrrhonhgudxt"]} .= " ORDER BY " . (isset(${${"GLOBALS"}["holuwdvhswv"]}) ? ${$mwoxesiw} . "." : "") . "`" . pSQL(${${"GLOBALS"}["sqkchfbmzgt"]}) . "` " . pSQL(${${"GLOBALS"}["cfsjhhbwbqgs"]}) . "\n\t\t\tLIMIT " . ((int) ${${"GLOBALS"}["vtcbvfsc"]} - 1) * (int) ${${"GLOBALS"}["mokqqxt"]} . "," . (int) ${$tydfnpg}; } ${$qsfzjtrtvb} = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(${$kaqgrgykux}); if (${${"GLOBALS"}["sqkchfbmzgt"]} == "orderprice") { Tools::orderbyPrice(${${"GLOBALS"}["hrmrbao"]}, ${${"GLOBALS"}["byhruvgfcplf"]}); } if (!${${"GLOBALS"}["hrmrbao"]}) { return false; } return Product::getProductsProperties(${${"GLOBALS"}["ytuoqlidml"]}, ${${"GLOBALS"}["hrmrbao"]}); }
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 find($id_lang, $expr, $pageNumber = 1, $pageSize = 1, $orderBy = 'position', $orderWay = 'desc', $ajax = false, $useCookie = true, Context $context = null) { global $cookie; if (!Module::isInstalled('agilemultipleseller') and !Module::isInstalled('agilesellerlistoptions')) { return parent::find($id_lang, $expr, $pageNumber, $pageSize, $orderBy, $orderWay, $ajax, $useCookie); } $agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p"); $db = Db::getInstance(_PS_USE_SQL_SLAVE_); if ($useCookie) { $id_customer = (int) $cookie->id_customer; } else { $id_customer = 0; } if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 1; } if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) { return false; } $intersectArray = array(); $scoreArray = array(); $words = explode(' ', Search::sanitize($expr, (int) $id_lang)); foreach ($words as $key => $word) { if (!empty($word) and strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); $intersectArray[] = '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 = ' . (int) $id_lang . ' AND sw.word LIKE ' . ($word[0] == '-' ? ' \'' . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'' : '\'' . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''); if ($word[0] != '-') { $scoreArray[] = 'sw.word LIKE \'' . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''; } } else { unset($words[$key]); } } if (!sizeof($words)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $score = ''; if (sizeof($scoreArray)) { $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 si.id_product = p.id_product AND (' . implode(' OR ', $scoreArray) . ') ) position'; } $result = $db->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` 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` WHERE c.`active` = 1 AND p.`active` = 1 AND indexed = 1 AND cg.`id_group` ' . (!$id_customer ? '= 1' : 'IN ( SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $id_customer . ' )'), false); $eligibleProducts = array(); while ($row = $db->nextRow($result)) { $eligibleProducts[] = $row['id_product']; } foreach ($intersectArray as $query) { $result = $db->ExecuteS($query, false); $eligibleProducts2 = array(); while ($row = $db->nextRow($result)) { $eligibleProducts2[] = $row['id_product']; } $eligibleProducts = array_intersect($eligibleProducts, $eligibleProducts2); if (!count($eligibleProducts)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } } array_unique($eligibleProducts); $productPool = ''; foreach ($eligibleProducts as $id_product) { if ($id_product) { $productPool .= (int) $id_product . ','; } } if (empty($productPool)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $productPool = strpos($productPool, ',') === false ? ' = ' . (int) $productPool . ' ' : ' IN (' . rtrim($productPool, ',') . ') '; if ($ajax) { $sql = 'SELECT DISTINCT p.id_product, pl.name pname, cl.name cname, cl.link_rewrite crewrite, pl.link_rewrite prewrite ' . $score . ' ' . $agile_sql_parts['selects'] . ' 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') . ' ) ' . $agile_sql_parts['joins'] . ' WHERE p.`id_product` ' . $productPool . ' ' . $agile_sql_parts['wheres'] . ' ORDER BY position DESC LIMIT 10'; return $db->executeS($sql); } $from_conds = ' 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 . ') 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` tax ON (tax.`id_tax` = tr.`id_tax`) 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` 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 . ') WHERE p.`id_product` ' . $productPool . ' ' . $agile_sql_parts['wheres'] . ' '; $sort_limit = ($orderBy ? 'ORDER BY ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize; $total = $db->getValue(' SELECT COUNT(*) ' . $from_conds); $queryResults = ' SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name ' . $score . ', 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_conds . ' ' . $sort_limit . ' '; $result = $db->ExecuteS($queryResults); if (!$result) { $resultProperties = false; } else { $resultProperties = Product::getProductsProperties((int) $id_lang, $result); } $resultProperties = AgileSellerManager::prepareSellerRattingInfo($resultProperties); return array('total' => $total, 'result' => $resultProperties); }
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_); // Only use cookie if id_customer is not present if ($use_cookie) { $id_customer = $context->customer->id; } else { $id_customer = 0; } // TODO : smart page management 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); $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] == '-' ? ' \'' . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'' : '\'' . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''); if ($word[0] != '-') { $score_array[] = 'sw.word LIKE \'' . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''; } } else { unset($words[$key]); } } if (!count($words)) { return $ajax ? array() : array('total' => 0, 'result' => 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 = 'SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg INNER JOIN `' . _DB_PREFIX_ . 'category_product` cp 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 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 . ' )'); $results = $db->executeS($sql); $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()); } } $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.'; } else { if ($order_by == 'date_upd') { $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 . ', 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 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_ . '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_ . '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); $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); } return array('total' => $total, 'result' => $result_properties); }
/** * 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 getPricesDrop($where, $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 == 'date_upd') { $order_by_prefix = 'p'; } else { if ($order_by == 'name') { $order_by_prefix = 'pl'; } } if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { die(Tools::displayError()); } $current_date = date('Y-m-d H:i:s'); $ids_product = $this->_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; } $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'AND p.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE ' . $where . ' cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . ' )'; } if ($count) { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(DISTINCT p.`id_product`) 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) . ')' : '') . ' ' . $sql_groups); } 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`, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, 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, 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') . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute pa ON (pa.id_product = p.id_product) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on=1') . ' ' . 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`)' . 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`) 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) . ')' : '') . ' ' . $sql_groups . ' GROUP BY product_shop.id_product 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 (!$result) { return false; } if ($order_by == 'price') { Tools::orderbyPrice($result, $order_way); } return Product::getProductsProperties($id_lang, $result); }
/** * Get the current objects' list form the database * * @param integer $id_lang Language used for display * @param string $orderBy ORDER BY clause * @param string $_orderWay Order way (ASC, DESC) * @param integer $start Offset in LIMIT clause * @param integer $limit Row count in LIMIT clause */ public function getList($id_lang, $orderBy = NULL, $orderWay = NULL, $start = 0, $limit = NULL) { global $cookie; /* Manage default params values */ if (empty($limit)) { $limit = !isset($cookie->{$this->table . '_pagination'}) ? $this->_pagination[1] : ($limit = $cookie->{$this->table . '_pagination'}); } if (!Validate::isTableOrIdentifier($this->table)) { die(Tools::displayError('Table name is invalid:') . ' "' . $this->table . '"'); } if (empty($orderBy)) { $orderBy = $cookie->__get($this->table . 'Orderby') ? $cookie->__get($this->table . 'Orderby') : $this->_defaultOrderBy; } if (empty($orderWay)) { $orderWay = $cookie->__get($this->table . 'Orderway') ? $cookie->__get($this->table . 'Orderway') : 'ASC'; } $limit = (int) Tools::getValue('pagination', $limit); $cookie->{$this->table . '_pagination'} = $limit; /* Check params validity */ if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay) or !is_numeric($start) or !is_numeric($limit) or !Validate::isUnsignedId($id_lang)) { die(Tools::displayError('get list params is not valid')); } /* Determine offset from current page */ if ((isset($_POST['submitFilter' . $this->table]) or isset($_POST['submitFilter' . $this->table . '_x']) or isset($_POST['submitFilter' . $this->table . '_y'])) and !empty($_POST['submitFilter' . $this->table]) and is_numeric($_POST['submitFilter' . $this->table])) { $start = (int) ($_POST['submitFilter' . $this->table] - 1) * $limit; } /* Cache */ $this->_lang = (int) $id_lang; $this->_orderBy = $orderBy; $this->_orderWay = Tools::strtoupper($orderWay); /* SQL table : orders, but class name is Order */ $sqlTable = $this->table == 'order' ? 'orders' : $this->table; /* Query in order to get results with all fields */ $sql = 'SELECT SQL_CALC_FOUND_ROWS ' . ($this->_tmpTableFilter ? ' * FROM (SELECT ' : '') . ' ' . ($this->lang ? 'b.*, ' : '') . 'a.*' . (isset($this->_select) ? ', ' . $this->_select . ' ' : '') . ' FROM `' . _DB_PREFIX_ . $sqlTable . '` a ' . ($this->lang ? 'LEFT JOIN `' . _DB_PREFIX_ . $this->table . '_lang` b ON (b.`' . $this->identifier . '` = a.`' . $this->identifier . '` AND b.`id_lang` = ' . (int) $id_lang . ')' : '') . ' ' . (isset($this->_join) ? $this->_join . ' ' : '') . ' WHERE 1 ' . (isset($this->_where) ? $this->_where . ' ' : '') . ($this->deleted ? 'AND a.`deleted` = 0 ' : '') . (isset($this->_filter) ? $this->_filter : '') . ' ' . (isset($this->_group) ? $this->_group . ' ' : '') . ' ' . (isset($this->_filterHaving) || isset($this->_having) ? 'HAVING ' : '') . (isset($this->_filterHaving) ? ltrim($this->_filterHaving, ' AND ') : '') . (isset($this->_having) ? $this->_having . ' ' : '') . ' ORDER BY ' . ($orderBy == $this->identifier ? 'a.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . ($this->_tmpTableFilter ? ') tmpTable WHERE 1' . $this->_tmpTableFilter : '') . ' LIMIT ' . (int) $start . ',' . (int) $limit; $this->_list = Db::getInstance()->ExecuteS($sql); $this->_listTotal = Db::getInstance()->getValue('SELECT FOUND_ROWS() AS `' . _DB_PREFIX_ . $this->table . '`'); }
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; }
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); }
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; }
/** * 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); }
/** * Get the current objects' list form the database * * @param integer $id_lang Language used for display * @param string $order_by ORDER BY clause * @param string $_orderWay Order way (ASC, DESC) * @param integer $start Offset in LIMIT clause * @param integer $limit Row count in LIMIT clause */ public function getList($id_lang, $order_by = null, $order_way = null, $start = 0, $limit = null, $id_lang_shop = false) { Hook::exec('action' . $this->controller_name . 'ListingFieldsModifier', array('select' => &$this->_select, 'join' => &$this->_join, 'where' => &$this->_where, 'group_by' => &$this->_groupBy, 'order_by' => &$this->_orderBy, 'order_way' => &$this->_orderWay, 'fields' => &$this->fields_list)); if (!isset($this->list_id)) { $this->list_id = $this->table; } /* Manage default params values */ $use_limit = true; if ($limit === false) { $use_limit = false; } elseif (empty($limit)) { if (isset($this->context->cookie->{$this->list_id . '_pagination'}) && $this->context->cookie->{$this->list_id . '_pagination'}) { $limit = $this->context->cookie->{$this->list_id . '_pagination'}; } else { $limit = $this->_default_pagination; } } if (!Validate::isTableOrIdentifier($this->table)) { throw new PrestaShopException(sprintf('Table name %s is invalid:', $this->table)); } $prefix = str_replace(array('admin', 'controller'), '', Tools::strtolower(get_class($this))); if (empty($order_by)) { if ($this->context->cookie->{$prefix . $this->list_id . 'Orderby'}) { $order_by = $this->context->cookie->{$prefix . $this->list_id . 'Orderby'}; } elseif ($this->_orderBy) { $order_by = $this->_orderBy; } else { $order_by = $this->_defaultOrderBy; } } if (empty($order_way)) { if ($this->context->cookie->{$prefix . $this->list_id . 'Orderway'}) { $order_way = $this->context->cookie->{$prefix . $this->list_id . 'Orderway'}; } elseif ($this->_orderWay) { $order_way = $this->_orderWay; } else { $order_way = $this->_defaultOrderWay; } } $limit = (int) Tools::getValue($this->list_id . '_pagination', $limit); if (in_array($limit, $this->_pagination) && $limit != $this->_default_pagination) { $this->context->cookie->{$this->list_id . '_pagination'} = $limit; } else { unset($this->context->cookie->{$this->list_id . '_pagination'}); } /* Check params validity */ if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way) || !is_numeric($start) || !is_numeric($limit) || !Validate::isUnsignedId($id_lang)) { throw new PrestaShopException('get list params is not valid'); } if (!isset($this->fields_list[$order_by]['order_key']) && isset($this->fields_list[$order_by]['filter_key'])) { $this->fields_list[$order_by]['order_key'] = $this->fields_list[$order_by]['filter_key']; } if (isset($this->fields_list[$order_by]) && isset($this->fields_list[$order_by]['order_key'])) { $order_by = $this->fields_list[$order_by]['order_key']; } /* Determine offset from current page */ $start = 0; if ((int) Tools::getValue('submitFilter' . $this->list_id)) { $start = ((int) Tools::getValue('submitFilter' . $this->list_id) - 1) * $limit; } elseif (empty($start) && isset($this->context->cookie->{$this->list_id . '_start'}) && Tools::isSubmit('export' . $this->table)) { $start = $this->context->cookie->{$this->list_id . '_start'}; } // Either save or reset the offset in the cookie if ($start) { $this->context->cookie->{$this->list_id . '_start'} = $start; } elseif (isset($this->context->cookie->{$this->list_id . '_start'})) { unset($this->context->cookie->{$this->list_id . '_start'}); } /* Cache */ $this->_lang = (int) $id_lang; $this->_orderBy = $order_by; if (preg_match('/[.!]/', $order_by)) { $order_by_split = preg_split('/[.!]/', $order_by); $order_by = bqSQL($order_by_split[0]) . '.`' . bqSQL($order_by_split[1]) . '`'; } elseif ($order_by) { $order_by = '`' . bqSQL($order_by) . '`'; } $this->_orderWay = Tools::strtoupper($order_way); /* SQL table : orders, but class name is Order */ $sql_table = $this->table == 'order' ? 'orders' : $this->table; // Add SQL shop restriction $select_shop = $join_shop = $where_shop = ''; if ($this->shopLinkType) { $select_shop = ', shop.name as shop_name '; $join_shop = ' LEFT JOIN ' . _DB_PREFIX_ . $this->shopLinkType . ' shop ON a.id_' . $this->shopLinkType . ' = shop.id_' . $this->shopLinkType; $where_shop = Shop::addSqlRestriction($this->shopShareDatas, 'a', $this->shopLinkType); } if ($this->multishop_context && Shop::isTableAssociated($this->table) && !empty($this->className)) { if (Shop::getContext() != Shop::CONTEXT_ALL || !$this->context->employee->isSuperAdmin()) { $test_join = !preg_match('#`?' . preg_quote(_DB_PREFIX_ . $this->table . '_shop') . '`? *sa#', $this->_join); if (Shop::isFeatureActive() && $test_join && Shop::isTableAssociated($this->table)) { $this->_where .= ' AND a.' . $this->identifier . ' IN ( SELECT sa.' . $this->identifier . ' FROM `' . _DB_PREFIX_ . $this->table . '_shop` sa WHERE sa.id_shop IN (' . implode(', ', Shop::getContextListShopID()) . ') )'; } } } /* Query in order to get results with all fields */ $lang_join = ''; if ($this->lang) { $lang_join = 'LEFT JOIN `' . _DB_PREFIX_ . $this->table . '_lang` b ON (b.`' . $this->identifier . '` = a.`' . $this->identifier . '` AND b.`id_lang` = ' . (int) $id_lang; if ($id_lang_shop) { if (!Shop::isFeatureActive()) { $lang_join .= ' AND b.`id_shop` = 1'; } elseif (Shop::getContext() == Shop::CONTEXT_SHOP) { $lang_join .= ' AND b.`id_shop` = ' . (int) $id_lang_shop; } else { $lang_join .= ' AND b.`id_shop` = a.id_shop_default'; } } $lang_join .= ')'; } $having_clause = ''; if (isset($this->_filterHaving) || isset($this->_having)) { $having_clause = ' HAVING '; if (isset($this->_filterHaving)) { $having_clause .= ltrim($this->_filterHaving, ' AND '); } if (isset($this->_having)) { $having_clause .= $this->_having . ' '; } } do { $this->_listsql = ' SELECT SQL_CALC_FOUND_ROWS ' . ($this->_tmpTableFilter ? ' * FROM (SELECT ' : ''); if ($this->explicitSelect) { foreach ($this->fields_list as $key => $array_value) { // Add it only if it is not already in $this->_select if (isset($this->_select) && preg_match('/[\\s]`?' . preg_quote($key, '/') . '`?\\s*,/', $this->_select)) { continue; } if (isset($array_value['filter_key'])) { $this->_listsql .= str_replace('!', '.', $array_value['filter_key']) . ' as ' . $key . ','; } elseif ($key == 'id_' . $this->table) { $this->_listsql .= 'a.`' . bqSQL($key) . '`,'; } elseif ($key != 'image' && !preg_match('/' . preg_quote($key, '/') . '/i', $this->_select)) { $this->_listsql .= '`' . bqSQL($key) . '`,'; } } $this->_listsql = rtrim($this->_listsql, ','); } else { $this->_listsql .= ($this->lang ? 'b.*,' : '') . ' a.*'; } $this->_listsql .= ' ' . (isset($this->_select) ? ', ' . rtrim($this->_select, ', ') : '') . $select_shop . ' FROM `' . _DB_PREFIX_ . $sql_table . '` a ' . $lang_join . ' ' . (isset($this->_join) ? $this->_join . ' ' : '') . ' ' . $join_shop . ' WHERE 1 ' . (isset($this->_where) ? $this->_where . ' ' : '') . ($this->deleted ? 'AND a.`deleted` = 0 ' : '') . (isset($this->_filter) ? $this->_filter : '') . $where_shop . ' ' . (isset($this->_group) ? $this->_group . ' ' : '') . ' ' . $having_clause . ' ORDER BY ' . (str_replace('`', '', $order_by) == $this->identifier ? 'a.' : '') . $order_by . ' ' . pSQL($order_way) . ($this->_tmpTableFilter ? ') tmpTable WHERE 1' . $this->_tmpTableFilter : '') . ($use_limit === true ? ' LIMIT ' . (int) $start . ',' . (int) $limit : ''); $this->_list = Db::getInstance()->executeS($this->_listsql, true, false); if ($this->_list === false) { $this->_list_error = Db::getInstance()->getMsgError(); break; } $this->_listTotal = Db::getInstance()->getValue('SELECT FOUND_ROWS() AS `' . _DB_PREFIX_ . $this->table . '`', false); if ($use_limit === true) { $start = (int) $start - (int) $limit; if ($start < 0) { break; } } else { break; } } while (empty($this->_list)); Hook::exec('action' . $this->controller_name . 'ListingResultsModifier', array('list' => &$this->_list, 'list_total' => &$this->_listTotal)); }
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); }
/** * 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); }
/** * Get the current objects' list form the database * * @param int $id_lang Language used for display * @param string $orderBy ORDER BY clause * @param string $_orderWay Order way (ASC, DESC) * @param int $start Offset in LIMIT clause * @param int $limit Row count in LIMIT clause */ public function getList($id_lang, $orderBy = null, $orderWay = null, $start = 0, $limit = null, $id_lang_shop = false) { /* Manage default params values */ if (empty($limit)) { $limit = !isset($this->context->cookie->{$this->table . '_pagination'}) ? $this->_pagination[1] : ($limit = $this->context->cookie->{$this->table . '_pagination'}); } if (!Validate::isTableOrIdentifier($this->table)) { $this->_errors[] = Tools::displayError('Table name is invalid:') . ' "' . $this->table . '"'; } if (empty($orderBy)) { $orderBy = $this->context->cookie->__get($this->table . 'Orderby') ? $this->context->cookie->__get($this->table . 'Orderby') : $this->_defaultOrderBy; } if (empty($orderWay)) { $orderWay = $this->context->cookie->__get($this->table . 'Orderway') ? $this->context->cookie->__get($this->table . 'Orderway') : 'ASC'; } $limit = (int) Tools::getValue('pagination', $limit); $this->context->cookie->{$this->table . '_pagination'} = $limit; /* Check params validity */ if (!Validate::isOrderBy($orderBy) || !Validate::isOrderWay($orderWay) || !is_numeric($start) || !is_numeric($limit) || !Validate::isUnsignedId($id_lang)) { die(Tools::displayError('get list params is not valid')); } /* Determine offset from current page */ if ((isset($_POST['submitFilter' . $this->table]) || isset($_POST['submitFilter' . $this->table . '_x']) || isset($_POST['submitFilter' . $this->table . '_y'])) && !empty($_POST['submitFilter' . $this->table]) && is_numeric($_POST['submitFilter' . $this->table])) { $start = (int) ($_POST['submitFilter' . $this->table] - 1) * $limit; } /* Cache */ $this->_lang = (int) $id_lang; $this->_orderBy = $orderBy; $this->_orderWay = Tools::strtoupper($orderWay); /* SQL table : orders, but class name is Order */ $sqlTable = $this->table == 'order' ? 'orders' : $this->table; // Add SQL shop restriction $selectShop = $joinShop = $whereShop = ''; if ($this->shopLinkType) { $selectShop = ', shop.name as shop_name '; $joinShop = ' LEFT JOIN ' . _DB_PREFIX_ . $this->shopLinkType . ' shop ON a.id_' . $this->shopLinkType . ' = shop.id_' . $this->shopLinkType; $whereShop = Shop::addSqlRestriction($this->shopShareDatas, 'a', $this->shopLinkType); } $asso = Shop::getAssoTable($this->table); if ($asso !== false && $asso['type'] == 'shop') { $filterKey = $asso['type']; $idenfierShop = Shop::getContextListShopID(); } $filterShop = ''; if (isset($filterKey)) { if (!$this->_group) { $this->_group = 'GROUP BY a.' . pSQL($this->identifier); } elseif (!preg_match('#(\\s|,)\\s*a\\.`?' . pSQL($this->identifier) . '`?(\\s|,|$)#', $this->_group)) { $this->_group .= ', a.' . pSQL($this->identifier); } if (Shop::isFeatureActive() && Shop::getContext() != Shop::CONTEXT_ALL && !preg_match('#`?' . preg_quote(_DB_PREFIX_ . $this->table . '_' . $filterKey) . '`? *sa#', $this->_join)) { $filterShop = 'JOIN `' . _DB_PREFIX_ . $this->table . '_' . $filterKey . '` sa ON (sa.' . $this->identifier . ' = a.' . $this->identifier . ' AND sa.id_' . $filterKey . ' IN (' . implode(', ', $idenfierShop) . '))'; } } /////////////////////// /* Query in order to get results with all fields */ $sql = 'SELECT SQL_CALC_FOUND_ROWS ' . ($this->_tmpTableFilter ? ' * FROM (SELECT ' : '') . ' ' . ($this->lang ? 'b.*, ' : '') . 'a.*' . (isset($this->_select) ? ', ' . $this->_select . ' ' : '') . $selectShop . ' FROM `' . _DB_PREFIX_ . $sqlTable . '` a ' . $filterShop . ' ' . ($this->lang ? 'LEFT JOIN `' . _DB_PREFIX_ . $this->table . '_lang` b ON (b.`' . $this->identifier . '` = a.`' . $this->identifier . '` AND b.`id_lang` = ' . (int) $id_lang . ($id_lang_shop ? ' AND b.`id_shop`=' . (int) $id_lang_shop : '') . ')' : '') . ' ' . (isset($this->_join) ? $this->_join . ' ' : '') . ' ' . $joinShop . ' WHERE 1 ' . (isset($this->_where) ? $this->_where . ' ' : '') . ($this->deleted ? 'AND a.`deleted` = 0 ' : '') . (isset($this->_filter) ? $this->_filter : '') . $whereShop . ' ' . (isset($this->_group) ? $this->_group . ' ' : '') . ' ' . (isset($this->_filterHaving) || isset($this->_having) ? 'HAVING ' : '') . (isset($this->_filterHaving) ? ltrim($this->_filterHaving, ' AND ') : '') . (isset($this->_having) ? $this->_having . ' ' : '') . ' ORDER BY ' . ($orderBy == $this->identifier ? 'a.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . ($this->_tmpTableFilter ? ') tmpTable WHERE 1' . $this->_tmpTableFilter : '') . ' LIMIT ' . (int) $start . ',' . (int) $limit; $this->_list = Db::getInstance()->executeS($sql); $this->_listTotal = Db::getInstance()->getValue('SELECT FOUND_ROWS() as `' . _DB_PREFIX_ . $this->table . '`'); }
/** * 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 function getList($order_by, $order_way, $filter, $start, $pagination) { $order_way = Validate::isOrderWay($order_way) ? $order_way : 'ASC'; $id_shop = (int) Context::getContext()->shop->id; $id_lang = (int) Context::getContext()->language->id; if (version_compare(_PS_VERSION_, '1.5', '<')) { $countries = DB::getInstance()->executeS(' SELECT c.`id_country` AS `id_country`, cl.`name` AS `name`, c.`iso_code` AS `iso_code`, IF(dpdc.`enabled` IS NULL, 1, dpdc.`enabled`) AS `enabled` FROM `' . _DB_PREFIX_ . 'country` c LEFT JOIN `' . _DB_PREFIX_ . _DPDPOLAND_COUNTRY_DB_ . '` dpdc ON (dpdc.`id_country` = c.`id_country` AND dpdc.`id_shop` = "' . (int) $id_shop . '") LEFT JOIN `' . _DB_PREFIX_ . 'country_lang` cl ON (cl.`id_country` = c.`id_country` AND cl.`id_lang` = "' . (int) $id_lang . '")' . $filter . ($order_by && $order_way ? ' ORDER BY `' . bqSQL($order_by) . '` ' . pSQL($order_way) : '') . ($start !== null && $pagination !== null ? ' LIMIT ' . (int) $start . ', ' . (int) $pagination : '')); } else { $countries = DB::getInstance()->executeS(' SELECT c.`id_country` AS `id_country`, cl.`name` AS `name`, c.`iso_code` AS `iso_code`, IF(dpdc.`enabled` IS NULL, 1, dpdc.`enabled`) AS `enabled` FROM `' . _DB_PREFIX_ . 'country` c LEFT JOIN `' . _DB_PREFIX_ . _DPDPOLAND_COUNTRY_DB_ . '` dpdc ON (dpdc.`id_country` = c.`id_country` AND dpdc.`id_shop` = "' . (int) $id_shop . '") LEFT JOIN `' . _DB_PREFIX_ . 'country_shop` cs ON (cs.`id_country` = c.`id_country`) LEFT JOIN `' . _DB_PREFIX_ . 'country_lang` cl ON (cl.`id_country` = c.`id_country` AND cl.`id_lang` = "' . (int) $id_lang . '") WHERE cs.`id_shop` = "' . (int) $id_shop . '" ' . $filter . ($order_by && $order_way ? ' ORDER BY `' . bqSQL($order_by) . '` ' . pSQL($order_way) : '') . ($start !== null && $pagination !== null ? ' LIMIT ' . (int) $start . ', ' . (int) $pagination : '')); } if (!$countries) { $countries = array(); } return $countries; }