public function _buildQuery($manudata = 0)
{
$app = JFactory::getApplication();
$context = 'search';
$db = JFactory::getDbo();
$keyword = $app->getUserStateFromRequest($context . 'keyword', 'keyword', '');
$defaultSearchType = '';
if (!empty($manudata['search_type'])) {
$defaultSearchType = $manudata['search_type'];
$defaultSearchType_tmp = $manudata['search_type'];
}
if ($defaultSearchType == "") {
$defaultSearchType = 'product_name';
}
if ($defaultSearchType == "name_number") {
$defaultSearchField = "name_number";
$defaultSearchType = '(p.product_name LIKE ' . $db->quote('%' . $keyword . '%') . ' OR p.product_number LIKE ' . $db->quote('%' . $keyword . '%') . ')';
} elseif ($defaultSearchType == "name_desc") {
$defaultSearchField = "name_desc";
$defaultSearchType = '(p.product_name LIKE ' . $db->quote('%' . $keyword . '%') . ' OR p.product_desc LIKE ' . $db->quote('%' . $keyword . '%') . ' OR p.product_s_desc LIKE ' . $db->quote('%' . $keyword . '%') . ')';
} elseif ($defaultSearchType == "virtual_product_num") {
$defaultSearchField = "virtual_product_num";
$defaultSearchType = '(pa.property_number LIKE ' . $db->quote('%' . $keyword . '%') . ' OR ps.subattribute_color_number LIKE ' . $db->quote('%' . $keyword . '%') . ')';
} elseif ($defaultSearchType == "name_number_desc") {
$defaultSearchType = '(p.product_name LIKE ' . $db->quote('%' . $keyword . '%') . ' OR p.product_number LIKE ' . $db->quote('%' . $keyword . '%') . ' OR p.product_desc LIKE ' . $db->quote('%' . $keyword . '%') . ' OR p.product_s_desc LIKE ' . $db->quote('%' . $keyword . '%') . ' OR pa.property_number LIKE ' . $db->quote('%' . $keyword . '%') . ' OR ps.subattribute_color_number LIKE ' . $db->quote('%' . $keyword . '%') . ')';
} elseif ($defaultSearchType == "product_desc") {
$defaultSearchField = $defaultSearchType;
$defaultSearchType = '(p.' . $defaultSearchType . ' LIKE ' . $db->quote('%' . $keyword . '%') . ' OR p.product_s_desc LIKE ' . $db->quote('%' . $keyword . '%') . ' )';
} elseif ($defaultSearchType == "product_name") {
$main_sp_name = explode(" ", $keyword);
$defaultSearchField = $defaultSearchType;
for ($f = 0; $f < count($main_sp_name); $f++) {
$defaultSearchType1[] = " p.product_name LIKE " . $db->quote('%' . $main_sp_name[$f] . '%');
}
$defaultSearchType = "(" . implode("AND", $defaultSearchType1) . ")";
} elseif ($defaultSearchType == "product_number") {
$defaultSearchField = $defaultSearchType;
$defaultSearchType = '(p.product_number LIKE ' . $db->quote('%' . $keyword . '%') . ')';
}
$redconfig = $app->getParams();
$getorderby = urldecode(JRequest::getCmd('order_by', ''));
if (in_array($getorderby, $this->filter_fields)) {
$order_by = $getorderby;
} else {
$order_by = $redconfig->get('order_by', DEFAULT_PRODUCT_ORDERING_METHOD);
}
if ($order_by == 'pc.ordering ASC' || $order_by == 'c.ordering ASC') {
$order_by = 'p.product_id DESC';
}
$layout = JRequest::getVar('layout', 'default');
$category_helper = new product_category();
$producthelper = new producthelper();
$manufacture_id = JRequest::getInt('manufacture_id', 0);
$category_id = JRequest::getInt('category_id', 0);
$cat = $category_helper->getCategoryListArray(0, $category_id);
$cat_group = array();
for ($j = 0; $j < count($cat); $j++) {
$cat_group[$j] = $cat[$j]->category_id;
if ($j == count($cat) - 1) {
$cat_group[$j + 1] = $category_id;
}
}
JArrayHelper::toInteger($cat_group);
if ($cat_group) {
$cat_group = join(',', $cat_group);
} else {
$cat_group = $category_id;
}
$params = JComponentHelper::getParams('com_redshop');
$menu = $app->getMenu();
$item = $menu->getActive();
$days = 0;
$days = isset($item->query['newproduct']) ? $item->query['newproduct'] : 0;
$today = date('Y-m-d H:i:s', time());
$days_before = date('Y-m-d H:i:s', time() - $days * 60 * 60 * 24);
$aclProducts = $producthelper->loadAclProducts();
$whereaclProduct = "";
// Shopper group - choose from manufactures Start
$rsUserhelper = new rsUserhelper();
$shopper_group_manufactures = $rsUserhelper->getShopperGroupManufacturers();
if ($shopper_group_manufactures != "") {
// Sanitize ids
$manufacturerIds = explode(',', $shopper_group_manufactures);
JArrayHelper::toInteger($manufacturerIds);
$whereaclProduct .= " AND p.manufacturer_id IN (" . implode(',', $manufacturerIds) . ") ";
}
// Shopper group - choose from manufactures End
if ($aclProducts != "") {
// Sanitize ids
$productIds = explode(',', $aclProducts);
JArrayHelper::toInteger($productIds);
$whereaclProduct .= " AND p.product_id IN (" . implode(',', $productIds) . ") ";
}
if ($layout == 'productonsale') {
$categoryid = $item->params->get('categorytemplate');
$cat_array = "";
$left_join = "";
if ($categoryid) {
$cat_main = $category_helper->getCategoryTree($categoryid);
$cat_group_main = array();
for ($j = 0; $j < count($cat_main); $j++) {
$cat_group_main[$j] = $cat_main[$j]->category_id;
}
$cat_group_main[] = $categoryid;
JArrayHelper::toInteger($cat_group_main);
$cat_array = " AND pcx.category_id IN (" . implode(',', $cat_group_main) . ") AND pcx.product_id=p.product_id ";
$left_join = " LEFT JOIN " . $this->_table_prefix . "product_category_xref pcx ON pcx.product_id=p.product_id ";
}
$query = " SELECT * FROM " . $this->_table_prefix . "product AS p " . $left_join . "WHERE p.published = 1 " . "AND p.product_on_sale=1 " . "AND p.expired=0 " . "AND p.product_parent_id=0 " . $whereaclProduct . $cat_array . " ORDER BY " . $db->escape($order_by);
} elseif ($layout == 'featuredproduct') {
$query = " SELECT * FROM " . $this->_table_prefix . "product AS p " . "WHERE p.published = 1 " . "AND p.product_special=1 " . $whereaclProduct . " ORDER BY " . $db->escape($order_by);
} elseif ($layout == 'newproduct') {
$catid = $item->query['categorytemplate'];
$cat_main = $category_helper->getCategoryTree($catid);
$cat_group_main = array();
for ($j = 0; $j < count($cat_main); $j++) {
$cat_group_main[$j] = $cat_main[$j]->category_id;
}
$cat_group_main[] = $catid;
JArrayHelper::toInteger($cat_group_main);
$extracond = "";
if ($catid) {
$extracond = " AND pcx.category_id in (" . implode(',', $cat_group_main) . ") AND pcx.product_id=p.product_id ";
}
$query = " SELECT distinct p.* " . " FROM " . $this->_table_prefix . "product p " . "LEFT JOIN " . $this->_table_prefix . "product_category_xref pcx ON pcx.product_id = p.product_id " . "WHERE p.published = 1 " . "and p.publish_date BETWEEN " . $db->quote($days_before) . " AND " . $db->quote($today) . " AND p.expired = 0 AND p.product_parent_id = 0 " . $whereaclProduct . $extracond . " ORDER BY " . $db->escape($order_by);
} elseif ($layout == 'redfilter') {
// Get products for filtering
$products = $this->getRedFilterProduct();
$query = " SELECT * " . " FROM " . $this->_table_prefix . "product as p " . "WHERE p.published = 1 AND p.expired = 0 " . $whereaclProduct;
if ($products != "") {
// Sanitize ids
$productIds = explode(',', $products);
JArrayHelper::toInteger($productIds);
$query .= "AND p.product_id IN ( " . implode(',', $productIds) . " ) ";
}
$query .= " ORDER BY " . $db->escape($order_by);
} else {
if ($manufacture_id == 0) {
if (!empty($manudata['manufacturer_id'])) {
$manufacture_id = $manudata['manufacturer_id'];
}
}
$query = "SELECT distinct p.* " . " FROM " . $this->_table_prefix . "product as p ";
if ($category_id != 0) {
$query .= " LEFT JOIN " . $this->_table_prefix . "product_category_xref as pcx on p.product_id = pcx.product_id ";
}
if ($defaultSearchType_tmp == "name_number_desc" || $defaultSearchType_tmp == "virtual_product_num") {
$query .= "LEFT JOIN " . $this->_table_prefix . "product_attribute AS a ON a.product_id = p.product_id " . "LEFT JOIN " . $this->_table_prefix . "product_attribute_property AS pa ON pa.attribute_id = a.attribute_id " . "LEFT JOIN " . $this->_table_prefix . "product_subattribute_color AS ps ON ps.subattribute_id = pa.property_id ";
}
$query .= " WHERE 1=1 AND p.expired = 0 " . $whereaclProduct;
if ($category_id != 0) {
// Sanitize ids
$catIds = explode(',', $cat_group);
JArrayHelper::toInteger($catIds);
$query .= " AND pcx.category_id in (" . $cat_group . ")";
}
if ($manufacture_id != 0) {
$query .= " AND p.manufacturer_id =" . (int) $manufacture_id;
}
$query .= " AND " . $defaultSearchType . " AND p.published = 1" . " ORDER BY " . $db->escape($order_by);
}
return $query;
}
