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