public static
getSqlParts
(mixed $type, mixed $search, mixed $param, mixed $params = array(), mixed $prefix = '')
/* Static part */
/*
* params ... search option parameters
*/
public static function getSqlParts($type, $search, $param, $params = array(), $prefix = '')
{
$in = '';
$where = '';
$left = '';
$db = Factory::getDBO();
switch ($type) {
case 'int':
$w = $param;
//$w = str_replace('%2C', ',', $w);
$a = explode(',', $w);
$inA = array();
if (!empty($a)) {
foreach ($a as $k => $v) {
$inA[] = (int) $v;
}
}
$inA = array_unique($inA);
$in = implode(',', $inA);
break;
case 'string':
$in = $param;
break;
case 'array':
$w = $param;
$inA = array();
if (!empty($w)) {
foreach ($w as $k => $v) {
$s = '';
//$v = str_replace('%2C', ',', $v);
$a = explode(',', $v);
if ($k != '' && $v != '' && !empty($a)) {
$a = array_unique($a);
if ($search == 'a') {
// Attributes
$inA[] = '(at2.alias = ' . $db->quote($k) . ' AND v2.alias IN (' . '\'' . implode('\',\'', $a) . '\'' . '))';
} else {
if ($search == 's') {
// Specifications
$inA[] = '(s2.alias = ' . $db->quote($k) . ' AND s2.alias_value IN (' . '\'' . implode('\',\'', $a) . '\'' . '))';
}
}
}
}
}
$in = $inA;
break;
default:
break;
}
if ($in != '') {
switch ($search) {
case 'tag':
$where = ' tr.tag_id IN (' . $in . ')';
$left = ' LEFT JOIN #__phocacart_tags_related AS tr ON a.id = tr.item_id';
break;
case 'label':
$where = ' lr.tag_id IN (' . $in . ')';
$left = ' LEFT JOIN #__phocacart_taglabels_related AS lr ON a.id = lr.item_id';
break;
// Custom parameters
case 'parameter':
$where = ' pr' . (int) $prefix . '.parameter_value_id IN (' . $in . ')';
$left = ' LEFT JOIN #__phocacart_parameter_values_related AS pr' . (int) $prefix . ' ON a.id = pr' . (int) $prefix . '.item_id';
break;
case 'manufacturer':
$where = ' m.id IN (' . $in . ')';
//$left = ' LEFT JOIN #__phocacart_manufacturers AS m ON m.id = a.manufacturer_id'; is asked as default
break;
case 'price_from':
case 'price_to':
$currency = PhocacartCurrency::getCurrency();
$price = PhocacartPrice::convertPriceCurrentToDefaultCurrency($in, $currency->exchange_rate);
if ($search == 'price_from') {
$where = ' a.price >= ' . $db->quote($price);
} else {
$where = ' a.price <= ' . $db->quote($price);
}
break;
case 'id':
// Category
$where = ' c.id IN (' . $in . ')';
$left = '';
//' LEFT JOIN #__phocacart_categories AS c ON c.id = a.catid';// Category always included
break;
case 'c':
// Category (c)
$where = ' c.id IN (' . $in . ')';
$left = '';
//' LEFT JOIN #__phocacart_categories AS c ON c.id = a.catid';// Category always included
break;
case 'a':
// Attributes
$where = '';
if (!empty($in)) {
$c = count($in);
$where = ' a.id IN (SELECT at2.product_id FROM #__phocacart_attributes AS at2' . ' LEFT JOIN #__phocacart_attribute_values AS v2 ON v2.attribute_id = at2.id' . ' WHERE ' . implode(' OR ', $in) . ' GROUP BY at2.product_id' . ' HAVING COUNT(at2.alias) >= ' . (int) $c . ')';
}
$left = '';
break;
case 's':
// Specifications
$where = '';
if (!empty($in)) {
$c = count($in);
$where = ' a.id IN (SELECT s2.product_id FROM #__phocacart_specifications AS s2' . ' WHERE ' . implode(' OR ', $in) . ' GROUP BY s2.product_id' . ' HAVING COUNT(s2.alias) >= ' . (int) $c . ')';
}
$left = '';
break;
case 'search':
// Search
$phrase = 'any';
if (isset($params['search_matching_option'])) {
$phrase = $params['search_matching_option'];
}
$where = '';
switch ($phrase) {
case 'exact':
$text = $db->quote('%' . $db->escape($in, true) . '%', false);
$wheresSub = array();
$wheresSub[] = 'a.title LIKE ' . $text;
$wheresSub[] = 'a.alias LIKE ' . $text;
$wheresSub[] = 'a.metakey LIKE ' . $text;
$wheresSub[] = 'a.metadesc LIKE ' . $text;
$wheresSub[] = 'a.description LIKE ' . $text;
$wheresSub[] = 'a.sku LIKE ' . $text;
$wheresSub[] = 'a.ean LIKE ' . $text;
// Search EAN, SKU in product attributes (advanced stock management) ... can be different for POS or Online Shop
if (isset($params['sql_search_skip_id_specific_type']) && $params['sql_search_skip_id_specific_type'] == 0) {
$wheresSub[] = 'ps.sku LIKE ' . $text;
$wheresSub[] = 'ps.ean LIKE ' . $text;
}
if (isset($params['search_deep']) && $params['search_deep'] == 1) {
$wheresSub[] = 'a.description_long LIKE ' . $text;
$wheresSub[] = 'a.features LIKE ' . $text;
}
$left = '';
// Custom Fields
if (isset($params['search_custom_fields']) && $params['search_custom_fields'] == 1) {
$user = Factory::getUser();
$groups = implode(',', $user->getAuthorisedViewLevels());
$query = $db->getQuery(true);
$wheresSub[] = 'jf.value LIKE ' . $text;
$left .= ' LEFT JOIN #__fields_values AS jf ON jf.item_id = ' . $query->castAsChar('a.id');
$left .= ' LEFT JOIN #__fields AS f ON f.id = jf.field_id and f.context = ' . $db->q('com_phocacart.phocacartitem') . ' and f.state = 1 and f.access IN (' . $groups . ')';
}
$where = '(' . implode(') OR (', $wheresSub) . ')';
break;
case 'all':
case 'any':
default:
$words = explode(' ', $in);
$wheres = array();
foreach ($words as $word) {
if (!($word = trim($word))) {
continue;
}
$word = $db->quote('%' . $db->escape($word, true) . '%', false);
$wheresSub = array();
$wheresSub[] = 'a.title LIKE ' . $word;
$wheresSub[] = 'a.alias LIKE ' . $word;
$wheresSub[] = 'a.metakey LIKE ' . $word;
$wheresSub[] = 'a.metadesc LIKE ' . $word;
$wheresSub[] = 'a.description LIKE ' . $word;
$wheresSub[] = 'a.sku LIKE ' . $word;
$wheresSub[] = 'a.ean LIKE ' . $word;
if (isset($params['sql_search_skip_id_specific_type']) && $params['sql_search_skip_id_specific_type'] == 0) {
$wheresSub[] = 'ps.sku LIKE ' . $word;
$wheresSub[] = 'ps.ean LIKE ' . $word;
}
if (isset($params['search_deep']) && $params['search_deep'] == 1) {
$wheresSub[] = 'a.description_long LIKE ' . $word;
$wheresSub[] = 'a.features LIKE ' . $word;
}
// Custom Fields
$left = '';
// don't repeat left
// Custom Fields
if (isset($params['search_custom_fields']) && $params['search_custom_fields'] == 1) {
$user = Factory::getUser();
$groups = implode(',', $user->getAuthorisedViewLevels());
$query = $db->getQuery(true);
$wheresSub[] = 'jf.value LIKE ' . $word;
$left .= ' LEFT JOIN #__fields_values AS jf ON jf.item_id = ' . $query->castAsChar('a.id');
$left .= ' LEFT JOIN #__fields AS f ON f.id = jf.field_id and f.context = ' . $db->q('com_phocacart.phocacartitem') . ' and f.state = 1 and f.access IN (' . $groups . ')';
}
$wheres[] = implode(' OR ', $wheresSub);
}
$where = '(' . implode($phrase == 'all' ? ') AND (' : ') OR (', $wheres) . ')';
break;
}
break;
default:
break;
}
}
$a = array();
$a['where'] = $where;
$a['left'] = $left;
return $a;
}