/** * Build an SQL query for the specified search terms. * * @param array Array of search terms * @param string String of fields to match * @param string The field to sort by * @param string The order to sort results by * @return array An array containing the query to count the number of results and a query to perform the search */ function BuildProductSearchQuery($searchTerms, $fields = "", $sortField = array("score", "proddateadded"), $sortOrder = "desc") { $queryWhere = array(); $joinQuery = ''; $queryWhere1 = array(); $joinQuery1 = ''; $qualifier_flag = 0; $qualifier_category = 0; $v_cols = array(); $p_cols = array(); /*$q_qry = "select * from isc_import_variations limit 0,1"; $q_res = $GLOBALS['ISC_CLASS_DB']->Query($q_qry); $q_row = $GLOBALS['ISC_CLASS_DB']->Fetch($q_res); foreach($q_row as $q_key => $q_val) { if(eregi('^(vq)', $q_key)) { $v_cols[] = $q_key; } else if(eregi('^(pq)', $q_key)) { $p_cols[] = $q_key; } }*/ $v_cols = isset($GLOBALS['sidev_cols']) ? $GLOBALS['sidev_cols'] : array(); $p_cols = isset($GLOBALS['sidep_cols']) ? $GLOBALS['sidep_cols'] : array(); $common_fields_product = " b.callbestprice bbestprice, c.callbestprice cbestprice, bs.callbestprice sbestprice,p.prodcode, p.productid , b.brandname , c.categoryid, c.catname , c.catuniversal,csi.icon_file "; $common_fields_variation = " p.prodcode, v.productid, v.prodstartyear, v.prodendyear , v.prodmodel, v.prodsubmodel, v.prodmake "; $new_fields = " p.proddesc , p.prodname , p.proddescfeature , prodwarranty , p.prodvariationid,p.prodconfigfields,p.prodeventdaterequired,p.prodvendorid,p.prodprice,p.prodretailprice,p.prodsaleprice,p.prodistaxable,p.prodcatids,p.prodhideprice,p.prodinvtrack,p.prodcurrentinv,p.prodallowpurchases,p.prodbrandid,p.prodcalculatedprice,prodweight,prodheight "; // Construct the full text search part of the query /*$fulltext_fields = array("ps.prodname", "ps.prodcode", "ps.proddesc", "ps.prodsearchkeywords", "ps.prodalternates", "ps.prodmake", "ps.prodmodel", "ps.prodsubmodel", "ps.prodstartyear", "ps.prodendyear");*/ $fulltext_fields = array("ps.prodname", "ps.prodcode", "ps.proddesc", "ps.prodsearchkeywords"); if (!$fields) { $fields = "{$new_fields}, FLOOR(p.prodratingtotal/p.prodnumratings) AS prodavgrating, " . GetProdCustomerGroupPriceSQL() . ", "; $fields .= "pi.imageisthumb, pi.imagefile "; if (isset($searchTerms['search_query']) && $searchTerms['search_query'] != "") { //$fields .= ', '.$GLOBALS['ISC_CLASS_DB']->FullText($fulltext_fields, $searchTerms['search_query'], false) . " as score "; } } $fields1 = $fields . " ,v. " . implode(",v.", $v_cols); //$fields .= " ,v. ".implode(",v. ",$v_cols).", group_concat(distinct PQcolor,', ',PQmaterial,', ',PQstyle separator '~') as productoption "; //$fields .= " , group_concat(distinct PQcolor,', ',PQmaterial,', ',PQstyle separator '~') as productoption "; if (isset($searchTerms['categoryid'])) { $searchTerms['category'] = array($searchTerms['categoryid']); } // If we're searching by category, we need to completely // restructure the search query - so do that first $categorySearch = false; $categoryIds = array(); if (isset($searchTerms['category']) && is_array($searchTerms['category'])) { foreach ($searchTerms['category'] as $categoryId) { // All categories were selected, so don't continue if ($categoryId == 0) { $categorySearch = false; break; } $categoryIds[] = (int) $categoryId; // If searching sub categories automatically, fetch & tack them on if (isset($searchTerms['searchsubs']) && $searchTerms['searchsubs'] == 'ON') { $categoryIds = array_merge($categoryIds, GetChildCats($categoryId)); } } $categoryIds = array_unique($categoryIds); if (!empty($categoryIds)) { $categorySearch = true; } } /* this below is condition is used when category is mentioned in search keyword */ if (isset($searchTerms['srch_category'])) { $categorySearch = true; $categoryIds = $searchTerms['srch_category']; } if ($categorySearch == true) { $qualifier_category = 1; $qualifier_flag = 1; /*$fromTable = 'isc_categoryassociations a, isc_products p '; $queryWhere[] = 'a.productid=p.productid AND a.categoryid IN ('.implode(',', $categoryIds).')'; $fromTable1 = 'isc_categoryassociations a, isc_import_variations v '; $queryWhere1[] = 'a.productid=v.productid AND a.categoryid IN ('.implode(',', $categoryIds).')';*/ if ($searchTerms['flag_srch_category'] == 1) { $fromTable = 'isc_products p USE INDEX (PRIMARY) '; } else { $fromTable = 'isc_products p '; } // $fromTable = 'isc_products p USE INDEX (categoryid) '; $queryWhere[] = 'c.categoryid IN (' . implode(',', $categoryIds) . ')'; $fromTable1 = 'isc_import_variations v '; $queryWhere1[] = 'c.categoryid IN (' . implode(',', $categoryIds) . ')'; } else { $fromTable = 'isc_products p'; $fromTable1 = 'isc_import_variations v'; $queryWhere[] = " c.categoryid is not null "; } /* this code is commented as we are no longer checking in search table as the records are split in product and variations table. if (isset($searchTerms['search_query']) && $searchTerms['search_query'] != "") { // Only need the product search table if we have a search query $joinQuery .= "INNER JOIN [|PREFIX|]product_search ps ON (p.productid=ps.productid) "; } else if ($sortField == "score") { // If we don't, we better make sure we're not sorting by score $sortField = "p.prodname"; $sortOrder = "ASC"; } */ /* Below condition has been added if any product detail page is seen directly , need to assign it as array */ if (!isset($searchTerms['dynfilters'])) { $searchTerms['dynfilters'] = array(); } $otherkeys = array_keys($searchTerms['dynfilters'], "others"); $others_factor = ''; /* if ( isset($searchTerms['partnumber']) || ( isset($searchTerms['flag_srch_category']) && $searchTerms['flag_srch_category'] == 1 ) || (isset($searchTerms['flag_srch_brand']) && $searchTerms['flag_srch_brand'] == 1 ) || isset($searchTerms['series']) || isset($searchTerms['subcategory']) ) { // listing page } else { foreach ($otherkeys as $otherkey) { //$others_factor .= " AND (".$otherkey." = '' OR ".$otherkey." IS NULL )"; $others_factor .= " AND (".$otherkey." = '' OR ".$otherkey." IS NULL )"; } } */ $partnumber_condition = ""; if (!isset($searchTerms['partnumber'])) { //$partnumber_condition = "and c.catvisible = 1 "; } $joinQuery .= " LEFT JOIN [|PREFIX|]import_variations AS v ON v.productid = p.productid " . $others_factor . "\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]categoryassociations ca on ca.productid = p.productid \n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]categories c on c.categoryid = ca.categoryid " . $partnumber_condition . " \n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]brands b on prodbrandid = b.brandid \n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]brand_series AS bs ON bs.seriesid = p.brandseriesid \n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid AND pi.imageisthumb=1)\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]product_finalprice fp ON p.productid = fp.productid\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]prodcut_cap_size_images csi ON p.prodcode = csi.productcode and p.prodvendorprefix = csi.vendor_prix "; $joinQuery1 .= " LEFT JOIN [|PREFIX|]import_variations AS v ON v.productid = p.productid " . $others_factor . "\n\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]categoryassociations ca on ca.productid = p.productid \n\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]categories c on c.categoryid = ca.categoryid " . $partnumber_condition . " \n\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]brands b on prodbrandid = b.brandid \n\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]brand_series AS bs ON bs.seriesid = p.brandseriesid "; $queryWhere1[] = $queryWhere[] = "p.prodvisible='1'"; // Add in the group category restrictions $permissionSql = GetProdCustomerGroupPermissionsSQL(null, false); if ($permissionSql) { $queryWhere1[] = $queryWhere[] = $permissionSql; } // Do we need to filter on brand? if (isset($searchTerms['brand']) && $searchTerms['brand'] != "") { $brand_query = "select brandid from [|PREFIX|]brands WHERE brandname='" . $searchTerms['brand'] . "'"; $brand_result = $GLOBALS['ISC_CLASS_DB']->Query($brand_query); $brandId = $GLOBALS['ISC_CLASS_DB']->FetchOne($brand_result); if ((int) $brandId > 0) { $qualifier_flag = 1; $queryWhere1[] = $queryWhere[] = "p.prodbrandid='" . $GLOBALS['ISC_CLASS_DB']->Quote($brandId) . "'"; } } // Do we need to filter on brand series? if (isset($searchTerms['series']) && $searchTerms['series'] != "") { $qualifier_flag = 1; if (isset($GLOBALS['seriesid'])) { $brand_series_id = (int) $GLOBALS['seriesid']; } else { $series_qry = "select s.seriesid from isc_brand_series s where s.seriesname = '" . $searchTerms['series'] . "' "; $series_res = $GLOBALS['ISC_CLASS_DB']->Query($series_qry); $series_arr = $GLOBALS['ISC_CLASS_DB']->Fetch($series_res); $brand_series_id = (int) $series_arr['seriesid']; } $queryWhere1[] = $queryWhere[] = " p.brandseriesid = " . $GLOBALS['ISC_CLASS_DB']->Quote($brand_series_id); } if (isset($searchTerms['partnumber'])) { $qualifier_flag = 1; $prod_code = $searchTerms['partnumber']; $queryWhere1[] = $queryWhere[] = " p.prodcode like '" . $prod_code . "%'"; } // Do we need to filter on price? if (isset($searchTerms['price'])) { $queryWhere1[] = $queryWhere[] = "p.prodcalculatedprice='" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['price']) . "'"; } else { /*if (isset($searchTerms['price_from']) && is_numeric($searchTerms['price_from'])) { $queryWhere1[] = $queryWhere[] = "p.prodcalculatedprice >= '".$GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['price_from'])."'"; } if (isset($searchTerms['price_to']) && is_numeric($searchTerms['price_to'])) { $queryWhere1[] = $queryWhere[] = "p.prodcalculatedprice <= '".$GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['price_to'])."'"; }*/ } // Do we need to filter on rating? if (isset($searchTerms['rating'])) { $queryWhere1[] = $queryWhere[] = "FLOOR(p.prodratingtotal/p.prodnumratings) = '" . (int) $searchTerms['rating'] . "'"; } else { if (isset($searchTerms['rating_from']) && is_numeric($searchTerms['rating_from'])) { $queryWhere1[] = $queryWhere[] = "FLOOR(p.prodratingtotal/p.prodnumratings) >= '" . (int) $searchTerms['rating_from'] . "'"; } if (isset($searchTerms['rating_to']) && is_numeric($searchTerms['rating_to'])) { $queryWhere1[] = $queryWhere[] = "FLOOR(p.prodratingtotal/p.prodnumratings) <= '" . (int) $searchTerms['rating_to'] . "'"; } } // Do we need to filter on featured? if (isset($searchTerms['featured']) && $searchTerms['featured'] != "") { $featured = (int) $searchTerms['featured']; if ($featured == 1) { $queryWhere1[] = $queryWhere[] = "p.prodfeatured=1"; } else { $queryWhere1[] = $queryWhere[] = "p.prodfeatured=0"; } } // Do we need to filter on free shipping? if (isset($searchTerms['shipping']) && $searchTerms['shipping'] != "") { $shipping = (int) $searchTerms['shipping']; if ($shipping == 1) { $queryWhere1[] = $queryWhere[] = "p.prodfreeshipping='1' "; } else { $queryWhere1[] = $queryWhere[] = "p.prodfreeshipping='0' "; } } // Do we need to filter only products we have in stock? if (isset($searchTerms['instock']) && $searchTerms['instock'] != "") { $stock = (int) $searchTerms['instock']; if ($stock == 1) { $queryWhere1[] = $queryWhere[] = "(p.prodcurrentinv>0 or p.prodinvtrack=0) "; } } // Do we need to filter for make of the product if (isset($searchTerms['make']) && $searchTerms['make'] != "" && (!isset($searchTerms['catuniversal']) || $searchTerms['catuniversal'] != 1)) { $make = $searchTerms['make']; if (!empty($make)) { $qualifier_flag = 1; $ext = ""; //if(isset($searchTerms['is_catg'])) $ext .= " OR v.prodmake = 'NON-SPEC VEHICLE' "; $queryWhere[] = " ( v.prodmake = '" . $make . "' {$ext} ) "; $queryWhere1[] = " ( v.prodmake = '" . $make . "' {$ext} ) "; } } // Do we need to filter for model of the product if (isset($searchTerms['model']) && $searchTerms['model'] != "" && (!isset($searchTerms['catuniversal']) || $searchTerms['catuniversal'] != 1)) { $model = $searchTerms['model']; if (!empty($model)) { $qualifier_flag = 1; $ext = ""; //if(isset($searchTerms['is_catg'])) $ext .= " OR v.prodmodel = 'ALL' "; //if(isset($_REQUEST['model'])) { if (!isset($searchTerms['model_flag']) || isset($searchTerms['model_flag']) && $searchTerms['model_flag'] == 1) { $queryWhere[] = " ( v.prodmodel = '" . $model . "' {$ext} ) "; $queryWhere1[] = " ( v.prodmodel = '" . $model . "' {$ext} ) "; } else { $queryWhere[] = " ( v.prodmodel like '" . $model . "%' {$ext} ) "; $queryWhere1[] = " ( v.prodmodel like '" . $model . "%' {$ext} ) "; } } } // Do we need to filter for submodel of the product if (isset($searchTerms['submodel']) && $searchTerms['submodel'] != "") { $submodel = $searchTerms['submodel']; if (!empty($model)) { $qualifier_flag = 1; $queryWhere[] = " ( v.prodsubmodel = '" . $submodel . "' OR v.prodsubmodel = '' ) "; $queryWhere1[] = " ( v.prodsubmodel = '" . $submodel . "' OR v.prodsubmodel = '' ) "; } } // Do we need to filter for year of the product if (isset($searchTerms['year']) && $searchTerms['year'] != "" && (!isset($searchTerms['catuniversal']) || $searchTerms['catuniversal'] != 1)) { $year = $searchTerms['year']; if (!empty($year)) { $qualifier_flag = 1; if (is_numeric($year)) { $ext = ""; //if(isset($searchTerms['is_catg'])) $ext .= " OR v.prodstartyear = 'ALL' "; $queryWhere[] = " ( " . $year . " between v.prodstartyear and v.prodendyear {$ext} ) "; $queryWhere1[] = " ( " . $year . " between v.prodstartyear and v.prodendyear {$ext} ) "; } else { $queryWhere[] = " ( v.prodstartyear = '{$year}' OR v.prodendyear = '{$year}' ) "; $queryWhere1[] = " ( v.prodstartyear = '{$year}' OR v.prodendyear = '{$year}' ) "; } } } /*if (isset($searchTerms['weight']) && $searchTerms['weight'] != "") { $weight = $searchTerms['weight']; if (!empty($year)) { $queryWhere[] = " p.prodweight = ".$weight; } } if (isset($searchTerms['height']) && $searchTerms['height'] != "") { $height = $searchTerms['height']; if (!empty($year)) { $queryWhere[] = " p.prodheight = ".$height; } } if (isset($searchTerms['brand']) && $searchTerms['brand'] != "") { $brand = $searchTerms['brand']; if (!empty($brand)) { $queryWhere[] = " p.prodbrandid = ".$brand; $queryWhere1[] = " p.prodbrandid = ".$brand; } }*/ if (isset($searchTerms['price_from']) && isset($searchTerms['price_to'])) { $from_price = $searchTerms['price_from']; $to_price = $searchTerms['price_to']; if ($from_price != "" && $to_price != "") { $queryWhere1[] = $queryWhere[] = " p.prodcalculatedprice between " . $from_price . " and " . $to_price; } } else { if (isset($searchTerms['price_from'])) { $from_price = $searchTerms['price_from']; if (!empty($from_price)) { $queryWhere1[] = $queryWhere[] = " p.prodcalculatedprice >= " . $from_price; } } else { if (isset($searchTerms['price_to'])) { $to_price = $searchTerms['price_to']; if (!empty($to_price)) { $queryWhere1[] = $queryWhere[] = " p.prodcalculatedprice <= " . $from_price; } } } } /* --------- these below coditions are commented as they now belongs to qualifiers table ------------ if (isset($searchTerms['bedsize']) && $searchTerms['bedsize'] != "") { $bedsize = $searchTerms['bedsize']; if (!empty($bedsize)) { $queryWhere1[] = $queryWhere[] = " p.prodbedsize like '%".$bedsize."%'"; } } if (isset($searchTerms['cabsize']) && $searchTerms['cabsize'] != "") { $cabsize = $searchTerms['cabsize']; if (!empty($cabsize)) { $queryWhere1[] = $queryWhere[] = " p.prodcabsize like '%".$cabsize."%'"; } } if (isset($searchTerms['tracksystem']) && $searchTerms['tracksystem'] != "") { $tracksystem = $searchTerms['tracksystem']; if (!empty($tracksystem)) { $queryWhere1[] = $queryWhere[] = " p.truckbedtracksystemtype like '%".$tracksystem."%'"; } } if (isset($searchTerms['material']) && $searchTerms['material'] != "") { $material = $searchTerms['material']; if (!empty($material)) { $queryWhere1[] = $queryWhere[] = " p.prodmaterial like '%".$material."%'"; } } if (isset($searchTerms['style']) && $searchTerms['style'] != "") { $style = $searchTerms['style']; if (!empty($style)) { $queryWhere1[] = $queryWhere[] = " p.prodstyle like '%".$style."%'"; } } if (isset($searchTerms['color']) && $searchTerms['color'] != "") { $color = $searchTerms['color']; if (!empty($color)) { $queryWhere1[] = $queryWhere[] = " p.prodcolor like '%".$color."%'"; } } /*---------------------------------------------------------------------------------------------*/ /*---- the below variables are used for displaying submodels in sideproductfilters.php --- */ $GLOBALS['qualifiercategory'] = $qualifier_category; $GLOBALS['wherecondition'] = $queryWhere; $GLOBALS['wherecondition1'] = $queryWhere1; /*--------- creating conditions for dynamic filters----------*/ $havingquery = array(); $outer_condition = ""; if (!empty($searchTerms['dynfilters'])) { $dynfilters = $searchTerms['dynfilters']; foreach ($dynfilters as $dynkey => $dynval) { $qualifier_flag = 1; $orgdynkey = $dynkey; //Added by Simha $str_to_check_pqvq = ""; if (!isset($searchTerms['catuniversal']) || $searchTerms['catuniversal'] == 0) { $str_to_check_pqvq = '^(vq|pq)'; } else { $str_to_check_pqvq = '^pq'; } if (eregi($str_to_check_pqvq, $dynkey)) { $dynkey = " v.{$dynkey} "; //$outer_condition .= " AND $dynkey like '%".$dynval."%'"; if ($dynval == 'others') { $havingquery[] = "( {$orgdynkey} = '' OR {$orgdynkey} IS NULL OR {$orgdynkey} = '~' )"; // here included '~' as in left navi query will return ~ } else { if (strcasecmp($dynkey, ' v.VQbedsize ') == 0) { $outer_condition .= " AND ( ( ( {$dynkey} = '" . $dynval . "') OR ( {$dynkey} regexp ';' AND {$dynkey} regexp '" . $dynval . "' ) ) OR ( ( v.bedsize_generalname = '" . $dynval . "' ) OR ( v.bedsize_generalname regexp ';' AND v.bedsize_generalname regexp '" . $dynval . "' ) ) ) "; } else { if (strcasecmp($dynkey, ' v.VQcabsize ') == 0) { $outer_condition .= " AND ( ( ( {$dynkey} = '" . $dynval . "') OR ( {$dynkey} regexp ';' AND {$dynkey} regexp '" . $dynval . "' ) ) OR ( ( v.cabsize_generalname = '" . $dynval . "' ) OR ( v.cabsize_generalname regexp ';' AND v.cabsize_generalname regexp '" . $dynval . "' ) ) ) "; } else { $outer_condition .= " AND ( ( {$dynkey} regexp ';' AND {$dynkey} regexp '" . $dynval . "' ) OR ( {$dynkey} not regexp ';' AND {$dynkey} = '" . $dynval . "') )"; } } } } } } if (isset($searchTerms['vqsbedsize'])) { $qualifier_flag = 1; $outer_condition .= " AND ( v.VQbedsize like '%" . $searchTerms['vqsbedsize'] . "%' OR v.bedsize_generalname like '%" . $searchTerms['vqsbedsize'] . "%' ) "; } if (isset($searchTerms['vqscabsize'])) { $qualifier_flag = 1; $outer_condition .= " AND ( v.VQcabsize like '%" . $searchTerms['vqscabsize'] . "%' OR v.cabsize_generalname like '%" . $searchTerms['vqscabsize'] . "%' ) "; } if (($qualifier_flag == 0 || isset($searchTerms['search'])) && (eregi('search.php', $_SERVER['REQUEST_URI']) || isset($GLOBALS['PathInfo']) && count($GLOBALS['PathInfo']) > 0) && !isset($_REQUEST['change'])) { //$joinQuery .= "INNER JOIN isc_product_search ps ON (p.productid=ps.productid)"; //$joinQuery1 .= "INNER JOIN isc_product_search ps ON (p.productid=ps.productid)"; if (isset($searchTerms['search_string'])) { $searchTerms['search_query'] = $searchTerms['search_string']; } if (isset($searchTerms['search_query']) && $searchTerms['search_query'] != "" && $searchTerms['search_query'] != "categories" && $searchTerms['search_query'] != "brands") { //$termQuery = "(" . $GLOBALS['ISC_CLASS_DB']->FullText($fulltext_fields, $searchTerms['search_query'], true); //$termQuery = " ( "; $termQuery = " p.prodname = '" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "' "; //$termQuery .= " p.prodname like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; /*$termQuery .= "OR p.proddesc like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR p.prodsearchkeywords like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR ps.prodalternates like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR ps.prodmake like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR ps.prodmodel like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR ps.prodsubmodel like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR ps.prodstartyear like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR ps.prodendyear like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' "; $termQuery .= "OR p.prodcode = '" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "') ";*/ $queryWhere1[] = $queryWhere[] = $termQuery; } } if (!is_array($sortField)) { $sortField = array($sortField); } if (!is_array($sortOrder)) { $sortOrder = array($sortOrder); } $sortField = array_filter($sortField); $sortOrder = array_filter($sortOrder); if (count($sortOrder) < count($sortField)) { $missing = count($sortField) - count($sortOrder); $sortOrder += array_fill(count($sortOrder), $missing, 'desc'); } else { if (count($sortOrder) > count($sortField)) { $sortOrder = array_slice($sortOrder, 0, count($sortField)); } } if (!empty($sortField)) { $orderBy = array(); $sortField = array_values($sortField); $sortOrder = array_values($sortOrder); foreach ($sortField as $key => $field) { $orderBy[] = $field . ' ' . $sortOrder[$key]; } $orderBy = ' ORDER BY ' . implode(',', $orderBy); } else { $orderBy = ''; } $orderBy = ''; /* the below query is used to check whether any series exist under the selected brand */ if (isset($searchTerms['flag_srch_brand']) && $searchTerms['flag_srch_brand'] == 0) { $brand_series_qry = "select p.brandseriesid from isc_products p LEFT JOIN isc_categoryassociations ca on ca.productid = p.productid LEFT JOIN isc_categories c on c.categoryid = ca.categoryid LEFT JOIN isc_import_variations AS v ON p.productid = v.productid\nWHERE prodbrandid = " . $brandId . " and brandseriesid != 0 " . GetWhereWithAnd($queryWhere) . " {$outer_condition} group by brandseriesid"; $brand_series_res = $GLOBALS['ISC_CLASS_DB']->Query($brand_series_qry); if ($GLOBALS['ISC_CLASS_DB']->CountResult($brand_series_res) > 0) { $GLOBALS['BRAND_SERIES_FLAG'] = 0; } else { $GLOBALS['BRAND_SERIES_FLAG'] = 1; } // series not exist under a brand } $qualifiers_where = ""; ##Added by Simha // for "others" values in qualifier filters //$otherkeys = array_keys($searchTerms['dynfilters'], "others"); $extrasearch = ''; foreach ($p_cols as $key => $value) { if (in_array($value, $otherkeys)) { $extrasearch .= ',MAX(' . $value . ') as ' . $value . ''; unset($p_cols[$key]); $p_cols = array_values($p_cols); } } foreach ($v_cols as $key => $value) { if (in_array($value, $otherkeys)) { $extrasearch .= ',MAX(' . $value . ') as ' . $value . ''; unset($v_cols[$key]); $v_cols = array_values($v_cols); } } ##Added by Simha Ends $v_cols_new = ''; $p_cols_new = ''; if (empty($p_cols)) { $p_cols_new = " '' as productoption "; } else { //$p_cols = " CONCAT_WS('~',".implode(' , ' , $p_cols).") as productoption "; //$p_cols = implode(' , ' , $p_cols); for ($k = 0; $k < count($p_cols); $k++) { //$p_cols = implode(' , ' , $p_cols); $p_cols_new .= " group_concat(DISTINCT " . $p_cols[$k] . " separator '~') as " . $p_cols[$k] . " ,"; } } if (empty($v_cols)) { $v_cols_new = " '' as vehicleoption "; } else { //$v_cols = " CONCAT_WS('~',".implode(' , ' , $v_cols).") as vehicleoption "; //$v_cols = implode(' , ' , $v_cols); for ($k = 0; $k < count($v_cols); $k++) { //$p_cols = implode(' , ' , $p_cols); //2010-11-15 Ronnie modify,VQcabsize,VQbedsize not is Special /*if( $v_cols[$k] == 'VQbedsize' ) { $v_cols_new .= " group_concat( DISTINCT if( v.bedsize_generalname != '', v.bedsize_generalname, v.VQbedsize ) separator '~' ) as VQbedsize ,"; } else if( $v_cols[$k] == 'VQcabsize' ) { //$v_cols_new .= " group_concat( DISTINCT if( v.cabsize_generalname != '', v.cabsize_generalname, v.VQcabsize ) separator '~' ) as VQcabsize ,"; $v_cols_new .= " group_concat( DISTINCT v.VQcabsize separator '~' ) as VQcabsize ,"; } else {*/ $v_cols_new .= " group_concat(DISTINCT " . $v_cols[$k] . " separator '~') as " . $v_cols[$k] . " ,"; //} } } $v_cols_new = trim($v_cols_new, ','); $p_cols_new = trim($p_cols_new, ','); //lguan_20100612: Added rating into select fields list if (isset($searchTerms['flag_srch_brand']) && $GLOBALS['BRAND_SERIES_FLAG'] == 0 && (!isset($searchTerms['srch_category']) || isset($searchTerms['category'])) && !isset($searchTerms['partnumber'])) { $GLOBALS['srch_where'] = "c.catname , c.categoryid , c.catuniversal , group_concat(DISTINCT ca.categoryid separator '~') as subcatgids , pa.catname as parentcatname , group_concat(DISTINCT brandname separator '~') as brandname , min(fp.prodfinalprice) as prodminprice , max(fp.prodfinalprice) as prodmaxprice , bs.seriesphoto as imagefile , p.proddesc , prodwarranty , bs.seriesname, bs.displayname, p.brandseriesid , count(distinct p.productid) as totalproducts , bs.feature_points1 , bs.feature_points2 , bs.feature_points3 , bs.feature_points4 , bs.feature_points , bs.seriesimagealt , b.brandimagefile , b.brandlargefile , b.branddescription , b.brandfooter, bs.serieshoverimagefile,floor(SUM(p.prodratingtotal)/SUM(p.prodnumratings)) AS prodavgrating "; $joinQuery .= "LEFT JOIN isc_categories pa on pa.categoryid = c.catparentid"; $orderBy = ' ORDER BY bs.seriessort ASC , bs.seriesname ASC '; $queryWhere1[] = $queryWhere[] = " p.brandseriesid != 0 "; if (!empty($havingquery)) { $queryWhere1[] = $queryWhere[] = implode(' AND ', $havingquery); unset($havingquery); $extrasearch = ""; } } else { if (isset($searchTerms['flag_srch_category']) && $searchTerms['flag_srch_category'] == 0 && (!isset($GLOBALS['BRAND_SERIES_FLAG']) || isset($GLOBALS['BRAND_SERIES_FLAG']) && $GLOBALS['BRAND_SERIES_FLAG'] == 0) && !isset($searchTerms['partnumber'])) { //wirror_20100809: add a return field for custom category filter //wirror_20101011: remove the group_concat products $GLOBALS['srch_where'] = "c.catname , c.categoryid , c.catuniversal , c.catimagealt , c.featurepoints , group_concat(DISTINCT brandname separator '~') as brandname , group_concat(DISTINCT p.brandseriesid separator '~') as seriesids , min(fp.prodfinalprice) as prodminprice , max(fp.prodfinalprice) as prodmaxprice , c.catimagefile as imagefile , c.cathoverimagefile , p.proddesc , prodwarranty , bs.seriesname, bs.displayname, p.brandseriesid , count(distinct p.productid) as totalproducts,floor(SUM(p.prodratingtotal)/SUM(p.prodnumratings)) AS prodavgrating "; $orderBy = ' ORDER BY c.catdeptid ASC, c.catsort ASC, c.catname ASC '; if (!empty($havingquery)) { $queryWhere1[] = $queryWhere[] = implode(' AND ', $havingquery); unset($havingquery); $extrasearch = ""; } } else { $GLOBALS['srch_where'] = $common_fields_product . "," . $fields . " , {$p_cols_new} , {$v_cols_new} , bs.seriesname, bs.displayname, p.brandseriesid , bs.seriesdescription , bs.seriesfooter , b.brandimagefile, bs.serieslogoimage "; $GLOBALS['srch_where'] .= $extrasearch; } } $query = "select " . $GLOBALS['srch_where'] . " from\n\t{$fromTable} {$joinQuery} WHERE 1=1 " . GetWhereWithAnd($queryWhere) . " {$outer_condition} "; $countQuery = "SELECT count(p.productid) " . $extrasearch . " FROM {$fromTable}\n\t{$joinQuery1} WHERE 1=1 " . GetWhereWithAnd($queryWhere) . " {$outer_condition} "; //wirror_yin20101102: put the join statement to $_GLOBALS $GLOBALS['join_query'] = $joinQuery; if (isset($havingquery) && count($havingquery) > 0) { $GLOBALS['having_query'] = " HAVING (" . implode(' AND ', $havingquery) . ")"; } else { $GLOBALS['having_query'] = ''; } return array('query' => $query, 'countQuery' => $countQuery, 'orderby' => $orderBy); }
function GetProductQuery($fields = "") { $searchTerms = $this->_searchterms; $queryWhere = array(); $v_cols = array(); $p_cols = array(); $v_cols = isset($GLOBALS['sidev_cols']) ? $GLOBALS['sidev_cols'] : array(); $p_cols = isset($GLOBALS['sidep_cols']) ? $GLOBALS['sidep_cols'] : array(); //2011-3-23 Ronnie add, add fields {b.callbestprice bbestprice, c.callbestprice cbestprice, bs.callbestprice sbestprice} $common_fields_product = " b.callbestprice bbestprice, c.callbestprice cbestprice, bs.callbestprice sbestprice, p.prodcode, p.productid , b.brandname , c.categoryid, c.catname , c.catuniversal,p.proddateadded,csi.icon_file "; $common_fields_variation = " p.prodcode, v.productid, v.prodstartyear, v.prodendyear , v.prodmodel, v.prodsubmodel, v.prodmake "; $new_fields = " p.proddesc , p.prodname , p.proddescfeature , prodwarranty , p.prodvariationid,p.prodconfigfields,p.prodeventdaterequired,p.prodvendorid,p.prodprice,p.prodretailprice,p.prodsaleprice,p.prodistaxable,p.prodcatids,p.prodhideprice,p.prodinvtrack,p.prodcurrentinv,p.prodallowpurchases,p.prodbrandid,p.prodcalculatedprice,prodweight,prodheight "; // Construct the full text search part of the query /*$fulltext_fields = array("ps.prodname", "ps.prodcode", "ps.proddesc", "ps.prodsearchkeywords", "ps.prodalternates", "ps.prodmake", "ps.prodmodel", "ps.prodsubmodel", "ps.prodstartyear", "ps.prodendyear");*/ $fulltext_fields = array("ps.prodname", "ps.prodcode", "ps.proddesc", "ps.prodsearchkeywords"); if (!$fields) { $fields = "{$new_fields}, FLOOR(p.prodratingtotal/p.prodnumratings) AS prodavgrating, " . GetProdCustomerGroupPriceSQL() . ", "; $fields .= "pi.imageisthumb, pi.imagefile "; if (isset($searchTerms['search_query']) && $searchTerms['search_query'] != "") { //$fields .= ', '.$GLOBALS['ISC_CLASS_DB']->FullText($fulltext_fields, $searchTerms['search_query'], false) . " as score "; } } $fields1 = $fields . " ,v. " . implode(",v.", $v_cols); if (isset($searchTerms['categoryid'])) { $searchTerms['category'] = array($searchTerms['categoryid']); } // If we're searching by category, we need to completely // restructure the search query - so do that first $categorySearch = false; $categoryIds = array(); if (isset($searchTerms['category']) && is_array($searchTerms['category'])) { foreach ($searchTerms['category'] as $categoryId) { // All categories were selected, so don't continue if ($categoryId == 0) { $categorySearch = false; break; } $categoryIds[] = (int) $categoryId; // If searching sub categories automatically, fetch & tack them on if (isset($searchTerms['searchsubs']) && $searchTerms['searchsubs'] == 'ON') { $categoryIds = array_merge($categoryIds, GetChildCats($categoryId)); } } $categoryIds = array_unique($categoryIds); if (!empty($categoryIds)) { $categorySearch = true; } } /* this below is condition is used when category is mentioned in search keyword */ if (isset($searchTerms['srch_category'])) { $categorySearch = true; $categoryIds = $searchTerms['srch_category']; } if ($categorySearch == true) { $qualifier_category = 1; $qualifier_flag = 1; /*$fromTable = 'isc_categoryassociations a, isc_products p '; $queryWhere[] = 'a.productid=p.productid AND a.categoryid IN ('.implode(',', $categoryIds).')'; $fromTable1 = 'isc_categoryassociations a, isc_import_variations v '; $queryWhere1[] = 'a.productid=v.productid AND a.categoryid IN ('.implode(',', $categoryIds).')';*/ if ($searchTerms['flag_srch_category'] == 1) { $fromTable = 'isc_products p USE INDEX (PRIMARY) '; } else { $fromTable = 'isc_products p '; } // $fromTable = 'isc_products p USE INDEX (categoryid) '; $queryWhere[] = 'c.categoryid IN (' . implode(',', $categoryIds) . ')'; //$fromTable1 = 'isc_import_variations v '; $queryWhere1[] = 'c.categoryid IN (' . implode(',', $categoryIds) . ')'; } else { $fromTable = 'isc_products p'; //$fromTable1 = 'isc_import_variations v'; $queryWhere[] = " c.categoryid is not null "; } $GLOBALS['qualifiercategory'] = $qualifier_category; /* this code is commented as we are no longer checking in search table as the records are split in product and variations table. if (isset($searchTerms['search_query']) && $searchTerms['search_query'] != "") { // Only need the product search table if we have a search query $joinQuery .= "INNER JOIN [|PREFIX|]product_search ps ON (p.productid=ps.productid) "; } else if ($sortField == "score") { // If we don't, we better make sure we're not sorting by score $sortField = "p.prodname"; $sortOrder = "ASC"; } */ /* Below condition has been added if any product detail page is seen directly , need to assign it as array */ if (!isset($searchTerms['dynfilters'])) { $searchTerms['dynfilters'] = array(); } $otherkeys = array_keys($searchTerms['dynfilters'], "others"); $others_factor = ''; /* if ( isset($searchTerms['partnumber']) || ( isset($searchTerms['flag_srch_category']) && $searchTerms['flag_srch_category'] == 1 ) || (isset($searchTerms['flag_srch_brand']) && $searchTerms['flag_srch_brand'] == 1 ) || isset($searchTerms['series']) || isset($searchTerms['subcategory']) ) { // listing page } else { foreach ($otherkeys as $otherkey) { //$others_factor .= " AND (".$otherkey." = '' OR ".$otherkey." IS NULL )"; $others_factor .= " AND (".$otherkey." = '' OR ".$otherkey." IS NULL )"; } } */ $partnumber_condition = ""; if (!isset($searchTerms['partnumber'])) { //$partnumber_condition = "and c.catvisible = 1 "; } $queryWhereTemp = $this->GetWhereBySearchItems($outer_condition, $havingquery); $queryWhere = array_merge($queryWhere, $queryWhereTemp); $where = ""; if (count($queryWhere) > 0) { $where = " and " . implode(' AND ', $queryWhere); } $qualifiers_where = ""; ##Added by Simha // for "others" values in qualifier filters //$otherkeys = array_keys($searchTerms['dynfilters'], "others"); $extrasearch = ''; foreach ($p_cols as $key => $value) { if (in_array($value, $otherkeys)) { $extrasearch .= ',MAX(' . $value . ') as ' . $value . ''; unset($p_cols[$key]); $p_cols = array_values($p_cols); } } foreach ($v_cols as $key => $value) { if (in_array($value, $otherkeys)) { $extrasearch .= ',MAX(' . $value . ') as ' . $value . ''; unset($v_cols[$key]); $v_cols = array_values($v_cols); } } ##Added by Simha Ends $v_cols_new = ''; $p_cols_new = ''; if (empty($p_cols)) { $p_cols_new = " '' as productoption "; } else { $p_cols_new = " '' as temp_1088 "; //just for a empty column,don't cause sql error } if (empty($v_cols)) { $v_cols_new = " '' as vehicleoption "; } else { $v_cols_new = " '' as temp_1089 "; //just for a empty column,don't cause sql error } /*if(empty($p_cols)) { $p_cols_new = " '' as productoption "; } else { //$p_cols = " CONCAT_WS('~',".implode(' , ' , $p_cols).") as productoption "; //$p_cols = implode(' , ' , $p_cols); for($k=0;$k<count($p_cols);$k++) { //$p_cols = implode(' , ' , $p_cols); $p_cols_new .= " group_concat(DISTINCT ".$p_cols[$k]." separator '~') as ".$p_cols[$k]." ,"; } } if(empty($v_cols)) { $v_cols_new = " '' as vehicleoption "; } else { //$v_cols = " CONCAT_WS('~',".implode(' , ' , $v_cols).") as vehicleoption "; //$v_cols = implode(' , ' , $v_cols); for($k=0;$k<count($v_cols);$k++) { //$p_cols = implode(' , ' , $p_cols); //2010-11-15 Ronnie modify,VQcabsize,VQbedsize not is Special /*if( $v_cols[$k] == 'VQbedsize' ) { $v_cols_new .= " group_concat( DISTINCT if( v.bedsize_generalname != '', v.bedsize_generalname, v.VQbedsize ) separator '~' ) as VQbedsize ,"; } else if( $v_cols[$k] == 'VQcabsize' ) { //$v_cols_new .= " group_concat( DISTINCT if( v.cabsize_generalname != '', v.cabsize_generalname, v.VQcabsize ) separator '~' ) as VQcabsize ,"; $v_cols_new .= " group_concat( DISTINCT v.VQcabsize separator '~' ) as VQcabsize ,"; } else {*/ /*$v_cols_new .= " group_concat(DISTINCT ".$v_cols[$k]." separator '~') as ".$v_cols[$k]." ,"; //} } } $v_cols_new = trim($v_cols_new, ','); $p_cols_new = trim($p_cols_new, ',');*/ //$GLOBALS['srch_where'] = $common_fields_product.",".$fields." , $p_cols_new , $v_cols_new , bs.seriesname, bs.displayname, p.brandseriesid , bs.seriesdescription , bs.seriesfooter , b.brandimagefile, bs.serieslogoimage "; $GLOBALS['srch_where'] = $common_fields_product . "," . $fields . " , {$p_cols_new} , {$v_cols_new} , bs.seriesname, bs.displayname, p.brandseriesid , bs.seriesdescription , bs.seriesfooter , b.brandimagefile, bs.serieslogoimage "; $GLOBALS['srch_where'] .= $extrasearch; ##Added by mike $v_cols_new2 = ''; $p_cols_new2 = ''; if (!empty($p_cols)) { for ($k = 0; $k < count($p_cols); $k++) { $p_cols_new2 .= " " . $p_cols[$k] . " as " . $p_cols[$k] . " ,"; } } if (!empty($v_cols)) { for ($k = 0; $k < count($v_cols); $k++) { $v_cols_new2 .= " " . $v_cols[$k] . " as " . $v_cols[$k] . " ,"; } } /* $v_cols_new2 = trim($v_cols_new2, ','); $p_cols_new2 = trim($p_cols_new2, ',');*/ $PVCols = array_merge($p_cols, $v_cols); $PVColumns = $p_cols_new2 . $v_cols_new2; $PVColumns = trim($PVColumns, ','); $PVQuery = ''; if (count($PVCols) > 0) { $PVQuery = " select p.productid," . $PVColumns . " FROM {$fromTable}\n\t\t\t" . $this->GetJoinCountQueryTables() . " WHERE 1=1 {$where} {$outer_condition} "; } $query = "select " . $GLOBALS['srch_where'] . " from\n\t\t{$fromTable} " . $this->GetJoinQueryTables() . " WHERE 1=1 {$where} {$outer_condition} "; $countQuery = "SELECT count(p.productid) " . $extrasearch . " FROM {$fromTable}\n\t\t" . $this->GetJoinCountQueryTables() . " WHERE 1=1 {$where} {$outer_condition} "; //wirror_yin20101102: put the join statement to $_GLOBALS $GLOBALS['join_query'] = $this->GetJoinQueryTables(); return array('query' => $query, 'countQuery' => $countQuery, 'orderby' => "", 'PVCols' => $PVCols, 'PVQuery' => $PVQuery); }