public function buildQuery($ignoreFilters = array())
{
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$where = $this->_buildContentWhere($ignoreFilters, $query);
$orderby = $this->_buildContentOrderBy($query);
$attributes = $this->getAttributes(true);
$textSearch = array();
$filters = $this->getState('filter.customattribute');
$searches = $this->getState('filter.customsearch');
$globalSearch = $this->getState('filter.search');
//$query->select('distinct i.*');
$list_select = $this->getState('list.select', 'distinct i.*, CASE WHEN (i.special_price > 0.0 AND i.special_price < i.price) THEN i.special_price ELSE i.price END as final_price');
$ids_only = $list_select == 'i.id' ? true : false;
if ($ids_only) {
$list_select = 'distinct i.id';
}
$query->select($list_select);
$query->from('#__djc2_items as i');
if (!$ids_only) {
$query->select('c.id as _category_id, c.name as category, c.published as publish_category, c.alias as category_alias');
}
$query->join('left', '#__djc2_categories AS c ON c.id = i.cat_id');
if (!$ids_only) {
$query->select('p.id as _producer_id, p.name as producer, p.published as publish_producer, p.alias as producer_alias');
}
$query->join('left', '#__djc2_producers AS p ON p.id = i.producer_id');
if (!$ids_only) {
$query->select('ua.name AS author, ua.email AS author_email');
}
$query->join('left', '#__users AS ua ON ua.id = i.created_by');
if (!$ids_only) {
$query->select('countries.country_name');
}
$query->join('left', '#__djc2_countries AS countries ON countries.id = i.country');
if (!$ids_only) {
$query->select('states.name as state_name');
}
$query->join('left', '#__djc2_countries_states AS states ON states.id = i.state');
/*if (!$ids_only) {
$query->select('group_concat(distinct ic.category_id order by ic.category_id asc separator \'|\') AS categorylist');
$query->join('left', '#__djc2_items_categories AS ic ON ic.item_id=i.id');
}*/
$globalSearch = trim(JString::strtolower($globalSearch));
if (JString::substr($globalSearch, 0, 1) == '"' && JString::substr($globalSearch, -1) == '"') {
$globalSearch = JString::substr($globalSearch, 1, -1);
}
if (JString::strlen($globalSearch) > 0 && JString::strlen($globalSearch) < 2 || JString::strlen($globalSearch) > 40) {
$globalSearch = null;
}
$doTextSearch = !in_array('search', $ignoreFilters);
if ($doTextSearch && $globalSearch) {
$includeFalang = (bool) (Djcatalog2Helper::isFalang() && Djcatalog2Helper::isDefaultLanguage() === false);
if ($includeFalang) {
$langId = Djcatalog2Helper::getLangId();
$query->join('LEFT', '#__falang_content AS falc ON falc.reference_table="djc2_items" AND falc.language_id=' . (int) $langId . ' AND falc.reference_id = i.id');
}
$words = explode(' ', $globalSearch);
$wheres = array();
$phrase = 'all';
foreach ($words as $word) {
$word = $db->quote('%' . $db->escape($word, true) . '%', false);
$wheres2 = array();
$wheres2[] = 'i.description LIKE ' . $word;
$wheres2[] = 'i.intro_desc LIKE ' . $word;
$wheres2[] = 'i.name LIKE ' . $word;
$wheres2[] = 'p.name LIKE ' . $word;
$wheres2[] = 'c.name LIKE ' . $word;
if ($includeFalang) {
$wheres2[] = '(falc.reference_field="name" AND falc.value LIKE ' . $word . ')';
$wheres2[] = '(falc.reference_field="description" AND falc.value LIKE ' . $word . ')';
$wheres2[] = '(falc.reference_field="intro_desc" AND falc.value LIKE ' . $word . ')';
}
$wheres[] = implode(' OR ', $wheres2);
}
$textSearch[] = '(' . implode($phrase == 'all' ? ') AND (' : ') OR (', $wheres) . ')';
$optionsSearch = ' select i.id ' . ' from #__djc2_items as i ' . ' inner join #__djc2_items_extra_fields_values_int as efv on efv.item_id = i.id' . ' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 ' . ' inner join #__djc2_items_extra_fields_options as efo on efo.id = efv.value and lower(efo.value) like ' . $db->quote('%' . $db->escape($globalSearch, true) . '%', false) . ' union ' . 'select i.id ' . ' from #__djc2_items as i ' . ' inner join #__djc2_items_extra_fields_values_text as efv on efv.item_id = i.id' . ' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 and lower(efv.value) like ' . $db->quote('%' . $db->escape($globalSearch, true) . '%', false);
$query->join('LEFT', '(' . $optionsSearch . ') AS customattribute_search ON customattribute_search.id = i.id');
$textSearch[] = 'i.id = customattribute_search.id';
}
$doCustomSearch = !in_array('custom_fields', $ignoreFilters);
if ($doCustomSearch) {
$filter_unions = array();
foreach ($attributes as $key => $attribute) {
$attributes[$key]->alias = str_replace('-', '_', $attribute->alias);
if (!empty($filters[$attribute->alias])) {
$filter = $filters[$attribute->alias];
if ($attribute->filterable == 1) {
if (is_scalar($filter) && strpos($filter, ',') !== false) {
$filter = explode(',', $filter);
} else {
if (is_scalar($filter) && strpos($filter, '-') !== false) {
$filter = explode('-', $filter);
}
}
if (is_array($filter)) {
if ($attribute->type == 'text') {
$attribute->filter_type = 'minmax_text';
}
if ($attribute->filter_type == 'minmax' || $attribute->filter_type == 'minmax_text') {
$min = false;
$max = false;
if (count($filter) == 2) {
$min = strlen($filter[0]) > 0 ? $filter[0] : false;
$max = strlen($filter[1]) > 0 ? $filter[1] : false;
if ($min === false && $max === false) {
continue;
}
if ($min === false) {
if ($attribute->type == 'text') {
$filter_unions[] = '(select v.* ' . ' from #__djc2_items_extra_fields_values_text as v ' . ' where v.field_id=' . $attribute->id . ' and CAST(v.value AS DECIMAL(10,4)) <= ' . $db->quote(floatval($max)) . ' group by v.item_id)';
} else {
$filter_unions[] = '(select v.* ' . ' from #__djc2_items_extra_fields_values_int as v ' . ' left join #__djc2_items_extra_fields_options as o on o.field_id=v.field_id and o.id = v.value ' . ' where v.field_id=' . $attribute->id . ' and CAST(o.value AS DECIMAL(10,4)) <= ' . $db->quote(floatval($max)) . ' group by v.item_id)';
}
} else {
if ($max === false) {
if ($attribute->type == 'text') {
$filter_unions[] = '(select v.* ' . ' from #__djc2_items_extra_fields_values_text as v ' . ' where v.field_id=' . $attribute->id . ' and CAST(v.value AS DECIMAL(10,4)) >= ' . $db->quote(floatval($min)) . ' group by v.item_id)';
} else {
$filter_unions[] = '(select v.* ' . ' from #__djc2_items_extra_fields_values_int as v ' . ' left join #__djc2_items_extra_fields_options as o on o.field_id=v.field_id and o.id = v.value ' . ' where v.field_id=' . $attribute->id . ' and CAST(o.value AS DECIMAL(10,4)) >= ' . $db->quote(floatval($min)) . ' group by v.item_id)';
}
} else {
if ($attribute->type == 'text') {
$filter_unions[] = '(select v.* ' . ' from #__djc2_items_extra_fields_values_text as v ' . ' where v.field_id=' . $attribute->id . ' and CAST(v.value AS DECIMAL(10,4)) <= ' . $db->quote(floatval($max)) . ' and CAST(v.value AS DECIMAL(10,4)) >= ' . $db->quote(floatval($min)) . ' group by v.item_id)';
} else {
$filter_unions[] = '(select v.* ' . ' from #__djc2_items_extra_fields_values_int as v ' . ' left join #__djc2_items_extra_fields_options as o on o.field_id=v.field_id and o.id = v.value ' . ' where v.field_id=' . $attribute->id . ' and CAST(o.value AS DECIMAL(10,4)) <= ' . $db->quote(floatval($max)) . ' and CAST(o.value AS DECIMAL(10,4)) >= ' . $db->quote(floatval($min)) . ' group by v.item_id)';
}
}
}
}
} else {
if ($attribute->type == 'checkbox') {
foreach ($filter as $key => $opt) {
if (is_scalar($opt)) {
$filter_unions[] = '(select * from #__djc2_items_extra_fields_values_int where field_id=' . $attribute->id . ' and value=' . (int) $opt . ')';
}
}
} else {
$terms = array();
foreach ($filter as $key => $opt) {
if (is_scalar($opt)) {
$terms[] = 'value = ' . (int) $opt;
}
}
if (count($terms) > 0) {
$condition = implode(' OR ', $terms);
$filter_unions[] = '(select * from #__djc2_items_extra_fields_values_int where field_id=' . $attribute->id . ' and (' . $condition . '))';
}
}
}
} else {
$filter_unions[] = '(select * from #__djc2_items_extra_fields_values_int where field_id=' . $attribute->id . ' and value=' . (int) $filter . ')';
}
}
}
// Custom search
if (!empty($searches[$attribute->alias]) && is_string($searches[$attribute->alias])) {
$filter = $searches[$attribute->alias];
if ($attribute->searchable == 1) {
$tblAls = 'djcs' . $attribute->id;
if ($attribute->type == 'checkbox' || $attribute->type == 'select' || $attribute->type == 'radio') {
$subQuerySearch = ' select i.id ' . ' from #__djc2_items as i ' . ' inner join #__djc2_items_extra_fields_values_int as efv on efv.item_id = i.id' . ' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 ' . ' inner join #__djc2_items_extra_fields_options as efo on efo.id = efv.value and lower(efo.value) like ' . $db->quote('%' . $db->escape($searches[$attribute->alias], true) . '%', false);
$query->join('INNER', '(' . $subQuerySearch . ') AS ' . $tblAls . ' ON ' . $tblAls . '.id = i.id');
} else {
if ($attribute->type == 'calendar') {
$subQuerySearch = 'select i.id ' . ' from #__djc2_items as i ' . ' inner join #__djc2_items_extra_fields_values_date as efv on efv.item_id = i.id' . ' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 and lower(efv.value) like ' . $db->quote('%' . $db->escape($searches[$attribute->alias], true) . '%', false);
$query->join('INNER', '(' . $subQuerySearch . ') AS ' . $tblAls . ' ON ' . $tblAls . '.id = i.id');
} else {
$subQuerySearch = 'select i.id ' . ' from #__djc2_items as i ' . ' inner join #__djc2_items_extra_fields_values_text as efv on efv.item_id = i.id' . ' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 and lower(efv.value) like ' . $db->quote('%' . $db->escape($searches[$attribute->alias], true) . '%', false);
$query->join('INNER', '(' . $subQuerySearch . ') AS ' . $tblAls . ' ON ' . $tblAls . '.id = i.id');
}
}
}
}
}
if (count($filter_unions) > 0) {
$unionQuery = 'select * from (select count(*) as c, item_id from (' . implode(' union ', $filter_unions) . ') as f group by f.item_id) as filter_counter where filter_counter.c=' . count($filter_unions);
$query->join('inner', '(' . $unionQuery . ') as filters on filters.item_id = i.id');
}
}
if ($doTextSearch && count($textSearch)) {
$where[] = ' ( ' . implode(' OR ', $textSearch) . ' ) ';
}
if (count($where) > 0) {
$query->where($where);
}
//$query->group('i.id');
$query->order($orderby);
//echo str_replace('#_','jos',$query).'<br/>';die();
return $query;
}
