/**
* 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);
}
