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 getCarts($dateBetween)
{
$price_sql = Product::getProductPriceSql('p.id_product', 'pp', 'c.id_currency');
$attribute_price_sql = Product::getProductPriceSql('pa.id_product_attribute', 'pap', 'c.id_currency');
$sql = "\n\t\t SELECT AVG(cartsum) as average, MAX(cartsum) as highest, MIN(cartsum) as lowest\n\t\t FROM (\n\t\t\t SELECT SUM(\n\t\t\t\t ((1+t.rate/100) * pp.`price` + IF(cp.id_product_attribute IS NULL OR cp.id_product_attribute = 0, 0, (\n\t\t\t\t\t SELECT IFNULL(pap.price, 0) \n\t\t\t\t\t FROM PREFIX_product_attribute pa {$attribute_price_sql}\n\t\t\t\t\t WHERE pa.id_product = cp.id_product\n\t\t\t\t\t AND pa.id_product_attribute = cp.id_product_attribute\n\t\t\t\t ))) * cp.quantity) / cu.conversion_rate as cartsum\n\t\t\t FROM `PREFIX_cart` c\n\t\t\t LEFT JOIN `PREFIX_currency` cu ON c.id_currency = cu.id_currency\n\t\t\t LEFT JOIN `PREFIX_cart_product` cp ON c.`id_cart` = cp.`id_cart`\n\t\t\t LEFT JOIN `PREFIX_product` p ON p.`id_product` = cp.`id_product`\n\t\t\t {$price_sql}\n\t\t\t LEFT JOIN `PREFIX_tax` t ON pp.`id_tax` = t.`id_tax`\n\t\t\t WHERE c.`date_upd` BETWEEN {$dateBetween}\n\t\t\t GROUP BY c.`id_cart`\n\t\t ) carts\n\t\t";
$sql = str_replace('PREFIX_', _DB_PREFIX_, $sql);
return Db::getInstance()->getRow($sql);
}
public static function getBestSales($id_lang, $pageNumber = 0, $nbProducts = 10, $orderBy = NULL, $orderWay = NULL)
{
global $link, $cookie;
if ($pageNumber < 0) {
$pageNumber = 0;
}
if ($nbProducts < 1) {
$nbProducts = 10;
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'sales';
}
if (empty($orderWay)) {
$orderWay = 'DESC';
}
$price_sql = Product::getProductPriceSql('p.id_product', 'pp');
$customer_join = '';
$customer_where = '';
if ($cookie->id_customer) {
$customer_join = "\n\t\t INNER JOIN `PREFIX_customer_group` cg ON\n\t\t cg.`id_group` = ctg.`id_group`\n\t\t ";
$customer_where = "cg.`id_customer` = {$cookie->id_customer} OR";
}
if (isset($orderByPrefix)) {
$orderByPrefix .= '.';
} else {
$orderByPrefix = '';
}
$orderBy = pSQL($orderBy);
$orderWay = pSQL($orderWay);
$pageStart = intval($pageNumber * $nbProducts);
$nbProducts = intval($nbProducts);
$sql = "\n\t\t SELECT\n\t\t p.*, pp.*,\n\t\t pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`,\n\t\t i.`id_image`, il.`legend`,\n\t\t ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`\n\t\t FROM\n\t\t `PREFIX_product_sale` ps \n\t\t LEFT JOIN `PREFIX_product` p ON\n\t\t ps.`id_product` = p.`id_product`\n\t\t {$price_sql}\n\t\t LEFT JOIN `PREFIX_product_lang` pl ON\n\t\t p.`id_product` = pl.`id_product` AND pl.`id_lang` = {$id_lang}\n\t\t LEFT JOIN `PREFIX_image` i ON\n\t\t i.`id_product` = p.`id_product` AND i.`cover` = 1\n\t\t LEFT JOIN `PREFIX_image_lang` il ON\n\t\t i.`id_image` = il.`id_image` AND il.`id_lang` = {$id_lang}\n\t\t LEFT JOIN `PREFIX_tax` t ON\n\t\t t.`id_tax` = pp.`id_tax`\n\t\t LEFT JOIN `PREFIX_category_product` cp ON\n\t\t cp.`id_product` = p.`id_product`\n\t\t INNER JOIN `PREFIX_category_group` ctg ON\n\t\t ctg.`id_category` = cp.`id_category`\n\t\t {$customer_join}\n\t\t WHERE\n\t\t p.`active` = 1\n\t\t AND ({$customer_where} ctg.`id_group` = 1)\n\t\t GROUP BY p.`id_product`\n\t\t ORDER BY {$orderByPrefix}`{$orderBy}` {$orderWay}\n\t\t LIMIT {$pageStart}, {$nbProducts}\n\t\t";
$sql = str_replace('PREFIX_', _DB_PREFIX_, $sql);
$result = Db::getInstance()->ExecuteS($sql);
if ($orderBy == 'price') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getPacksTable($id_product, $id_lang, $full = false, $limit = NULL)
{
$price_sql = Product::getProductPriceSql('p.id_product', 'pp');
$sql = "\n\t\t SELECT\n p.*, pl.*, i.`id_image`, il.`legend`, t.`rate`\n\t\t FROM\n `PREFIX_product` p\n\t\t NATURAL LEFT JOIN `PREFIX_product_lang` pl\n\t\t LEFT JOIN `PREFIX_image` i ON\n i.`id_product` = p.`id_product` AND i.`cover` = 1\n\t\t LEFT JOIN `PREFIX_image_lang` il ON\n i.`id_image` = il.`id_image` AND il.`id_lang` = {$id_lang}\n {$price_sql}\n\t\t LEFT JOIN `PREFIX_tax` t ON\n t.`id_tax` = pp.`id_tax`\n\t\t WHERE pl.`id_lang` = {$id_lang}\n\t\t AND p.`id_product` IN (\n\t\t\t SELECT a.`id_product_pack`\n\t\t\t FROM `PREFIX_pack` a\n\t\t\t WHERE a.`id_product_item` = {$id_product})\n\t\t";
$sql = str_replace('PREFIX_', _DB_PREFIX_, $sql);
$result = Db::getInstance()->ExecuteS($sql);
if ($limit) {
$sql .= ' LIMIT ' . intval($limit);
}
$result = Db::getInstance()->ExecuteS($sql);
if (!$full) {
return $result;
}
$arrayResult = array();
foreach ($result as $row) {
if (!Pack::isPacked($row['id_product'])) {
$arrayResult[] = Product::getProductProperties($id_lang, $row);
}
}
return $arrayResult;
}
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));
}
public static function find($id_lang, $expr, $pageNumber = 1, $pageSize = 1, $orderBy = 'position', $orderWay = 'desc', $ajax = false)
{
global $cookie;
// TODO : smart page management
if ($pageNumber < 1) {
$pageNumber = 1;
}
if ($pageSize < 1) {
$pageSize = 1;
}
if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$whereArray = array();
$scoreArray = array();
$words = explode(' ', Search::sanitize($expr, $id_lang));
foreach ($words as $key => $word) {
if (!empty($word)) {
$word = str_replace('%', '\\%', $word);
$word = str_replace('_', '\\_', $word);
$whereArray[] = ' p.id_product ' . ($word[0] == '-' ? 'NOT' : '') . ' IN (
SELECT id_product
FROM ' . _DB_PREFIX_ . 'search_word sw
LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = ' . intval($id_lang) . '
AND sw.word LIKE ' . ($word[0] == '-' ? ' \'' . pSQL(substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'' : '\'' . pSQL(substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'') . '
) ';
if ($word[0] != '-') {
$scoreArray[] = 'sw.word LIKE \'' . pSQL(substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'';
}
} else {
unset($words[$key]);
}
}
if (!sizeof($words)) {
return $ajax ? array() : array('total' => 0, 'result' => array());
}
$score = '';
if (sizeof($scoreArray)) {
$score = ',(
SELECT SUM(weight)
FROM ' . _DB_PREFIX_ . 'search_word sw
LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = ' . intval($id_lang) . '
AND si.id_product = p.id_product
AND (' . implode(' OR ', $scoreArray) . ')
) as position';
}
if ($ajax) {
$queryResults = '
SELECT p.id_product, pl.name as pname, IF(cl.name REGEXP "^[0-9]{2}\\.", SUBSTRING(cl.name, 4), cl.name) as cname ' . $score . ', cl.link_rewrite as crewrite, pl.link_rewrite as prewrite
FROM ' . _DB_PREFIX_ . 'product p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . intval($id_lang) . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`)
INNER JOIN `' . _DB_PREFIX_ . 'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
' . ($cookie->id_customer ? 'INNER JOIN `' . _DB_PREFIX_ . 'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '') . '
WHERE ' . implode(' AND ', $whereArray) . '
AND p.active = 1
AND (' . ($cookie->id_customer ? 'cg.`id_customer` = ' . intval($cookie->id_customer) . ' OR' : '') . ' ctg.`id_group` = 1)
GROUP BY p.`id_product`
ORDER BY position DESC
LIMIT 10';
return Db::getInstance()->ExecuteS($queryResults);
}
$price_sql = str_replace('PREFIX_', _DB_PREFIX_, Product::getProductPriceSql('p.id_product', 'pp'));
// FIXME: Handle new products properly (see classes/Product.php:isNew() for corect join clause)
$queryResults = '
SELECT SQL_CALC_FOUND_ROWS p.*, pp.*, 0 as `new`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name ' . ($cookie->id_customer ? ', cg.`id_group`' : '') . '
' . $score . '
FROM ' . _DB_PREFIX_ . 'product p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ')
LEFT OUTER JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ')
' . $price_sql . '
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (pp.`id_tax` = t.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`)
INNER JOIN `' . _DB_PREFIX_ . 'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
' . ($cookie->id_customer ? 'INNER JOIN `' . _DB_PREFIX_ . 'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '') . '
WHERE ' . implode(' AND ', $whereArray) . '
AND p.active = 1
AND (' . ($cookie->id_customer ? 'cg.`id_customer` = ' . intval($cookie->id_customer) . ' OR' : '') . ' ctg.`id_group` = 1)
GROUP BY p.`id_product`
' . ($orderBy ? 'ORDER BY ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . '
LIMIT ' . intval(($pageNumber - 1) * $pageSize) . ',' . intval($pageSize);
$result = Db::getInstance()->ExecuteS($queryResults);
$total = Db::getInstance()->getValue('SELECT FOUND_ROWS()');
Module::hookExec('search', array('expr' => $expr, 'total' => $total));
return array('total' => $total, 'result' => Product::getProductsProperties($id_lang, $result));
}
private function getRelatedProducts($id_lang, $idProduct)
{
global $link, $cookie;
$pab_pic_quant = Configuration::get('PAB_PIC_QUANT');
if (Configuration::get('PAB_MODE') == 1) {
$price_sql = Product::getProductPriceSql('PREFIX_product.id_product', 'pp');
$sql = "\r\n\t\t SELECT DISTINCT\r\n\t\t PREFIX_order_detail.product_id AS OID,\r\n\t\t PREFIX_order_detail.product_name,\r\n\t\t PREFIX_product.reference,\r\n\t\t PREFIX_product_lang.link_rewrite,\r\n\t\t PREFIX_image.id_image,\r\n\t\t PREFIX_product.id_product,\r\n\t\t PREFIX_product_lang.name,\r\n\t\t pp.price\r\n\t\t FROM\r\n\t\t PREFIX_order_detail\r\n\t\t LEFT JOIN PREFIX_product ON\r\n\t\t PREFIX_product.id_product = PREFIX_order_detail.product_id\r\n\t\t LEFT JOIN PREFIX_product_lang ON\r\n\t\t PREFIX_product.id_product = PREFIX_product_lang.id_product AND PREFIX_product_lang.id_lang = {$id_lang}\r\n\t\t LEFT JOIN PREFIX_image ON\r\n\t\t PREFIX_product.id_product = PREFIX_image.id_product AND PREFIX_image.cover = 1\r\n {$price_sql}\r\n\t\t WHERE\r\n\t\t PREFIX_order_detail.id_order IN (SELECT PREFIX_order_detail.id_order AS TAB1 FROM PREFIX_order_detail WHERE PREFIX_order_detail.product_id = {$idProduct} )\r\n\t\t AND PREFIX_product.active = 1\r\n\t\t AND PREFIX_product.id_product != {$idProduct}\r\n\t\t GROUP BY PREFIX_product.id_product\r\n\t\t ORDER BY RAND() LIMIT {$pab_pic_quant}\r\n\t\t";
$sql = str_replace('PREFIX_', _DB_PREFIX_, $sql);
$result = Db::getInstance()->ExecuteS($sql);
}
if (Configuration::get('PAB_MODE') == 0) {
$price_sql = Product::getProductPriceSql('PREFIX_product.id_product', 'pp');
$sql = "\r\n\t\tSELECT DISTINCT\r\n PREFIX_order_detail.product_id AS OID,\r\n\t\t PREFIX_order_detail.product_name,\r\n\t\t PREFIX_product.reference,\r\n\t\t PREFIX_product_lang.link_rewrite,\r\n\t\t PREFIX_image.id_image,\r\n\t\t PREFIX_product.id_product,\r\n\t\t PREFIX_product_lang.name,\r\n\t\t pp.price,\r\n\t\t COUNT(*) AS quantity\r\n\t\tFROM\r\n PREFIX_order_detail\r\n\t\t LEFT JOIN PREFIX_product ON\r\n\t\t PREFIX_product.id_product = PREFIX_order_detail.product_id\r\n\t\t LEFT JOIN PREFIX_product_lang ON\r\n\t\t PREFIX_product.id_product = PREFIX_product_lang.id_product AND PREFIX_product_lang.id_lang = {$id_lang}\r\n\t\t LEFT JOIN PREFIX_image ON\r\n\t\t PREFIX_product.id_product = PREFIX_image.id_product AND PREFIX_image.cover = 1\r\n {$price_sql}\r\n\t\tWHERE\r\n PREFIX_order_detail.id_order IN (SELECT PREFIX_order_detail.id_order AS TAB1 FROM PREFIX_order_detail WHERE PREFIX_order_detail.product_id = {$idProduct} )\r\n\t\t AND PREFIX_product.active = 1\r\n\t\t AND PREFIX_product.id_product != {$idProduct}\r\n\t\tGROUP BY PREFIX_product.id_product\r\n\t\tORDER BY quantity DESC LIMIT {$pab_pic_quant}\r\n\t\t";
$sql = str_replace('PREFIX_', _DB_PREFIX_, $sql);
$result = Db::getInstance()->ExecuteS($sql);
}
return $result;
}
