public function _buildQuery() { $and = ""; // Shopper group - choose from manufactures Start $rsUserhelper = new rsUserhelper(); $shopper_group_manufactures = $rsUserhelper->getShopperGroupManufacturers(); if ($shopper_group_manufactures != "") { $shopper_group_manufactures = explode(',', $shopper_group_manufactures); JArrayHelper::toInteger($shopper_group_manufactures); $shopper_group_manufactures = implode(',', $shopper_group_manufactures); $and .= " AND p.manufacturer_id IN (" . $shopper_group_manufactures . ") "; } // Shopper group - choose from manufactures End if (isset($this->_catid) && $this->_catid != 0) { $and .= "AND pcx.category_id = " . (int) $this->_catid . " "; } $query = "SELECT p.*, c.category_id, c.category_name ,c.category_back_full_image,c.category_full_image , m.manufacturer_name,pcx.ordering " . "FROM " . $this->_table_prefix . "product AS p " . "LEFT JOIN " . $this->_table_prefix . "product_category_xref AS pcx ON pcx.product_id = p.product_id " . $and . "LEFT JOIN " . $this->_table_prefix . "manufacturer AS m ON m.manufacturer_id = p.manufacturer_id " . "LEFT JOIN " . $this->_table_prefix . "category AS c ON c.category_id = pcx.category_id " . "WHERE 1=1 " . "AND p.product_id = " . (int) $this->_id . " " . "LIMIT 0,1 "; return $query; }
public function getProductCategory($id = 0) { $rsUserhelper = new rsUserhelper(); $shopper_group_manufactures = $rsUserhelper->getShopperGroupManufacturers(); if ($shopper_group_manufactures != "") { // Sanitize groups $shopGroupsIds = explode(',', $shopper_group_manufactures); JArrayHelper::toInteger($shopGroupsIds); $and .= " AND p.manufacturer_id IN (" . implode(',', $shopGroupsIds) . ") "; } $query = "SELECT p.product_id FROM " . $this->_table_prefix . "product_category_xref pc" . " LEFT JOIN " . $this->_table_prefix . "product AS p ON pc.product_id=p.product_id " . " WHERE category_id = " . (int) $id . " " . $and; $this->_db->setQuery($query); $res = $this->_db->loadObjectlist(); return $res; }
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; }
public function _buildProductQuery($template_data = '') { $filter_by = JRequest::getVar('filter_by', 0); $and = ''; // Shopper group - choose from manufactures Start $rsUserhelper = new rsUserhelper(); $shopper_group_manufactures = $rsUserhelper->getShopperGroupManufacturers(); if ($shopper_group_manufactures != "") { $shopper_group_manufactures = explode(',', $shopper_group_manufactures); JArrayHelper::toInteger($shopper_group_manufactures); $shopper_group_manufactures = implode(',', $shopper_group_manufactures); $and .= " AND p.manufacturer_id IN (" . $shopper_group_manufactures . ") "; } // Shopper group - choose from manufactures End if ($filter_by != '0') { $and .= " AND c.category_id = " . (int) $filter_by; } $orderby = $this->_buildProductOrderBy($template_data); $query = "SELECT DISTINCT(p.product_id),p.*, c.category_name, c.category_id FROM " . $this->_table_prefix . "product AS p " . "LEFT JOIN " . $this->_table_prefix . "product_category_xref AS pc ON p.product_id=pc.product_id " . "LEFT JOIN " . $this->_table_prefix . "category AS c ON pc.category_id=c.category_id " . "WHERE p.published = 1 " . "AND p.manufacturer_id = " . (int) $this->_id . " " . "AND p.expired = 0 " . "AND p.product_parent_id = 0 " . $and . " GROUP BY p.product_id " . $orderby; return $query; }
public function onContentSearch($text, $phrase = '', $ordering = '', $areas = null) { $db = JFactory::getDbo(); $user = JFactory::getUser(); $searchText = $text; // Load plugin params info $pluginParams = $this->params; $limit = $pluginParams->def('search_limit', 50); $text = trim($text); if ($text == '') { return array(); } $section = JText::_('COM_REDSHOP_Products'); $wheres = array(); switch ($phrase) { case 'exact': $text = $db->Quote('%' . $db->getEscaped($text, true) . '%', false); $wheres2 = array(); $wheres2[] = 'a.product_name LIKE ' . $text; $wheres2[] = 'a.product_number LIKE ' . $text; $wheres2[] = 'c.data_txt LIKE ' . $text; $where = '(' . implode(') OR (', $wheres2) . ')'; break; case 'all': case 'any': default: $words = explode(' ', $text); $wheres = array(); foreach ($words as $word) { $word = $db->Quote('%' . $db->getEscaped($word, true) . '%', false); $wheres2 = array(); $wheres2[] = 'a.product_name LIKE ' . $word; $wheres2[] = 'a.product_number LIKE ' . $word; $wheres2[] = 'c.data_txt LIKE ' . $word; $wheres[] = implode(' OR ', $wheres2); } $where = '(' . implode($phrase == 'all' ? ') AND (' : ') OR (', $wheres) . ')'; break; } switch ($ordering) { case 'oldest': $order = 'a.product_id ASC'; break; case 'newest': default: $order = 'a.product_id DESC'; } // Shopper group - choose from manufactures Start $rsUserhelper = new rsUserhelper(); $shopper_group_manufactures = $rsUserhelper->getShopperGroupManufacturers(); $whereaclProduct = ""; if ($shopper_group_manufactures != "") { $whereaclProduct = " AND a.manufacturer_id IN (" . $shopper_group_manufactures . ") "; } // Shopper group - choose from manufactures End $query = 'SELECT c.data_txt as customtxt,a.product_id,a.product_name AS title,a.product_number as number,a.product_s_desc AS text,' . ' "2" AS browsernav,"Redshop product" as section,"" as created' . ' FROM #__redshop_product AS a LEFT join #__redshop_fields_data As c on c.itemid = a.product_id' . ' WHERE (' . $where . ') ' . $whereaclProduct . '' . ' AND a.published = 1' . ' ORDER BY ' . $order; $db->setQuery($query, 0, $limit); $rows = $db->loadObjectList(); $redhelper = new redhelper(); foreach ($rows as $key => $row) { $Itemid = $redhelper->getItemid($row->product_id); $rows[$key]->href = "index.php?option=com_redshop&view=product&pid=" . $row->product_id . "&Itemid=" . $Itemid; } $return = array(); foreach ($rows as $key => $weblink) { if (searchHelper::checkNoHTML($weblink, $searchText, array('url', 'text', 'title', 'number', 'customtxt'))) { $return[] = $weblink; } } return $return; }
public function getCategoryProduct($minmax = 0, $isSlider = false) { $app = JFactory::getApplication(); $menu = $app->getMenu(); $item = $menu->getActive(); $manufacturer_id = isset($item) ? intval($item->params->get('manufacturer_id')) : 0; $setproductfinderobj = new redhelper(); $order_by = $this->_buildProductOrderBy(); $manufacturer_id = JRequest::getInt('manufacturer_id', $manufacturer_id, '', 'int'); $sort = ""; $and = ""; // Shopper group - choose from manufactures Start $rsUserhelper = new rsUserhelper(); $shopper_group_manufactures = $rsUserhelper->getShopperGroupManufacturers(); if ($shopper_group_manufactures != "") { $shopper_group_manufactures = explode(',', $shopper_group_manufactures); JArrayHelper::toInteger($shopper_group_manufactures); $shopper_group_manufactures = implode(',', $shopper_group_manufactures); $and .= " AND p.manufacturer_id IN (" . $shopper_group_manufactures . ") "; } // Shopper group - choose from manufactures End if ($manufacturer_id && $manufacturer_id > 0) { $and .= " AND p.manufacturer_id = " . (int) $manufacturer_id . " "; } if ($minmax && !(strstr($order_by, "p.product_price ASC") || strstr($order_by, "p.product_price DESC"))) { $order_by = " ORDER BY p.product_price ASC"; } $finder_condition = $this->getredproductfindertags(); $query = "SELECT * FROM " . $this->_table_prefix . "product AS p " . "LEFT JOIN " . $this->_table_prefix . "product_category_xref AS pc ON pc.product_id=p.product_id " . "LEFT JOIN " . $this->_table_prefix . "category AS c ON c.category_id=pc.category_id " . "LEFT JOIN " . $this->_table_prefix . "manufacturer AS m ON m.manufacturer_id=p.manufacturer_id " . "WHERE p.published = 1 AND p.expired = 0 " . "AND pc.category_id = " . (int) $this->_id . " " . "AND p.product_parent_id = 0 " . $and . $finder_condition . $order_by; $this->_product = $this->_getList($query); $priceSort = false; if (strstr($order_by, "p.product_price ASC")) { $priceSort = true; for ($i = 0; $i < count($this->_product); $i++) { $ProductPriceArr = $this->producthelper->getProductNetPrice($this->_product[$i]->product_id); $this->_product[$i]->productPrice = $ProductPriceArr['product_price']; } $this->_product = $this->columnSort($this->_product, 'productPrice', 'ASC'); } elseif (strstr($order_by, "p.product_price DESC")) { $priceSort = true; $sort = "DESC"; for ($i = 0; $i < count($this->_product); $i++) { $ProductPriceArr = $this->producthelper->getProductNetPrice($this->_product[$i]->product_id); $this->_product[$i]->productPrice = $ProductPriceArr['product_price']; } $this->_product = $this->columnSort($this->_product, 'productPrice', 'DESC'); } if ($minmax > 0) { $min = 0; if (!empty($priceSort)) { if ($sort == "DESC") { $max = $this->_product[0]->productPrice + 100; $min = $this->_product[count($this->_product) - 1]->productPrice; } else { $min = $this->_product[0]->productPrice; $max = $this->_product[count($this->_product) - 1]->productPrice + 100; } } else { $ProductPriceArr = $this->producthelper->getProductNetPrice($this->_product[0]->product_id); $min = $ProductPriceArr['product_price']; $ProductPriceArr = $this->producthelper->getProductNetPrice($this->_product[count($this->_product) - 1]->product_id); $max = $ProductPriceArr['product_price']; if ($min >= $max) { $min = $this->_product[0]->product_price; $max = $max + 100; } } $this->_product[0]->minprice = floor($min); $this->_product[0]->maxprice = ceil($max); $this->setMaxMinProductPrice(array(floor($min), ceil($max))); } elseif ($isSlider) { $newProduct = array(); for ($i = 0; $i < count($this->_product); $i++) { $ProductPriceArr = $this->producthelper->getProductNetPrice($this->_product[$i]->product_id); $this->_product[$i]->sliderprice = $ProductPriceArr['product_price']; if ($this->_product[$i]->sliderprice >= $this->minmaxArr[0] && $this->_product[$i]->sliderprice <= $this->minmaxArr[1]) { $newProduct[] = $this->_product[$i]; } } $this->_product = $newProduct; $this->_total = count($this->_product); } else { $this->_total = count($this->_product); } return $this->_product; }