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