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