/**
* Find the IDs of all subcategories.
*
* @param array $ids The array of category IDs.
* @param array $excludes The array of IDs to exclude.
* @return array The array of all subcategory IDs
*/
public function getSubCategories($ids=0, $excludes=array())
{
if (is_array($ids) == false) {
$ids = array($ids);
}
// Find sub category IDs.
$cats = array();
$nestedSet = new ISC_NESTEDSET_CATEGORIES();
foreach ($ids as $id) {
if (in_array($id, $excludes) == true) {
continue;
}
$cats = array_merge($cats, $nestedSet->getTree(array('categoryid'), $id));
}
$subcats = array();
foreach ($cats as $cat) {
$subcats[] = $cat['categoryid'];
}
$res = array_diff($subcats, $excludes);
return $res;
}
public function BuildWhereFromVars($array)
{
$queryWhere = "";
$joinQuery = "";
$categorySearch = false;
// Is this a custom search?
if(!empty($array['searchId'])) {
$this->_customSearch = $GLOBALS['ISC_CLASS_ADMIN_CUSTOMSEARCH']->LoadSearch($array['searchId']);
$array = array_merge($array, (array)$this->_customSearch['searchvars']);
}
// Are we selecting a specific product?
if(isset($array['productId']) && $array['productId'] != '') {
$queryWhere .= " p.productid = '" . $array['productId'] . "' AND ";
// dont need to build a where if only one product searched
return array("query" => $queryWhere, "join" => $joinQuery, "categorySearch"=>$categorySearch);
}
// If we're searching by category, we need to completely
// restructure the search query - so do that first
$categoryIds = array();
$nestedSet = new ISC_NESTEDSET_CATEGORIES();
if(isset($array['category']) && is_array($array['category'])) {
foreach($array['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($array['subCats']) && $array['subCats'] == 1) {
foreach ($nestedSet->getTree(array('categoryid'), $categoryId) as $childCategory) {
$categoryIds[] = (int)$childCategory['categoryid'];
}
unset($childCategory);
}
}
$categoryIds = array_unique($categoryIds);
if(!empty($categoryIds)) {
$categorySearch = true;
}
}
if($categorySearch == true) {
$queryWhere .= "ca.categoryid IN (" . implode(',', $categoryIds) . ") AND ";
}
if(isset($array['searchQuery']) && $array['searchQuery'] != "") {
// Perform a full text based search on the products search table
$search_query = $array['searchQuery'];
$fulltext_fields = array("ps.prodname", "ps.prodcode");
$queryWhere .= "(" . $GLOBALS["ISC_CLASS_DB"]->FullText($fulltext_fields, $search_query, true);
$queryWhere .= "OR ps.prodname like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($search_query) . "%' ";
$queryWhere .= "OR ps.prodcode = '" . $GLOBALS['ISC_CLASS_DB']->Quote($search_query) . "' ";
if (isId($search_query)) {
$queryWhere .= "OR p.productid='" . (int)$search_query . "'";
}
$queryWhere .= ") AND ";
// Add the join for the fulltext column
$joinQuery .= " INNER JOIN [|PREFIX|]product_search ps ON p.productid=ps.productid ";
}
if(isset($array['letter']) && $array['letter'] != '') {
$letter = chr(ord($array['letter']));
if($array['letter'] == '0-9') {
$queryWhere .= " p.prodname NOT REGEXP('^[a-zA-Z]') AND ";
}
else if(isc_strlen($letter) == 1) {
$queryWhere .= " p.prodname LIKE '".$GLOBALS['ISC_CLASS_DB']->Quote($letter)."%' AND ";
}
}
if(isset($array['soldFrom']) && isset($array['soldTo']) && $array['soldFrom'] != "" && $array['soldTo'] != "") {
$sold_from = (int)$array['soldFrom'];
$sold_to = (int)$array['soldTo'];
$queryWhere .= sprintf("(prodnumsold >= '%d' and prodnumsold <= '%d') and ", $sold_from, $sold_to);
}
else if(isset($array['soldFrom']) && $array['soldFrom'] != "") {
$sold_from = (int)$array['soldFrom'];
$queryWhere .= sprintf("prodnumsold >= '%d' and ", $sold_from);
}
else if(isset($array['soldTo']) && $array['soldTo'] != "") {
$sold_to = (int)$array['soldTo'];
$queryWhere .= sprintf("prodnumsold <= '%d' and ", $sold_to);
}
if(isset($array['priceFrom']) && $array['priceFrom'] != "" && isset($array['priceTo']) && $array['priceTo'] != "") {
$price_from = (int)$array['priceFrom'];
$price_to = (int)$array['priceTo'];
$queryWhere .= sprintf(" prodcalculatedprice >= '%s' and prodcalculatedprice <= '%s' and ", $price_from, $price_to);
}
else if(isset($array['priceFrom']) && $array['priceFrom'] != "") {
$price_from = (int)$array['priceFrom'];
$queryWhere .= sprintf(" prodcalculatedprice >= '%s' and ", $price_from);
}
else if(isset($array['priceTo']) && $array['priceTo'] != "") {
$price_to = (int)$array['priceTo'];
$queryWhere .= sprintf(" prodcalculatedprice <= '%s' and ", $price_to);
}
if(isset($array['inventoryFrom']) && $array['inventoryFrom'] != "" && isset($array['inventoryTo']) && $array['inventoryTo'] != "") {
$inventory_from =(int)$array['inventoryFrom'];
$inventory_to = (int)$array['inventoryTo'];
$queryWhere .= sprintf("prodcurrentinv >= '%s' and prodcurrentinv <= '%s' and ", $inventory_from, $inventory_to);
}
else if(isset($array['inventoryFrom']) && $array['inventoryFrom'] != "") {
$inventory_from =(int) $array['inventoryFrom'];
$queryWhere .= sprintf("prodcurrentinv >= '%s' and ", $inventory_from);
}
else if(isset($array['inventoryTo']) && $array['inventoryTo'] != "") {
$inventory_to = (int)$array['inventoryTo'];
$queryWhere .= sprintf("prodcurrentinv <= '%s' and ", $inventory_to);
}
if (isset($array['inventoryLow']) && $array['inventoryLow'] != 0) {
$lowVarInvProdIds = array();
$inventoryLowVarQuery = "SELECT DISTINCT(vcproductid) FROM [|PREFIX|]product_variation_combinations WHERE vcstock<=vclowstock AND vclowstock > 0";
$result = $GLOBALS['ISC_CLASS_DB']->Query($inventoryLowVarQuery);
while ($lowVarInventory = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
$lowVarInvProdIds[]=$lowVarInventory['vcproductid'];
}
$queryWhere .= "(prodcurrentinv <= prodlowinv AND prodlowinv > 0 AND prodinvtrack=1) OR ( prodinvtrack=2 AND p.productid in ('".implode('\',\'', $lowVarInvProdIds)."')) AND ";
}
if(isset($array['brand']) && $array['brand'] != "") {
$brand = (int)$array['brand'];
$queryWhere .= sprintf("prodbrandid = '%d' AND ", $brand);
}
// Product visibility
if(isset($array['visibility'])) {
if($array['visibility'] == 1) {
$queryWhere .= "prodvisible=1 AND ";
}
else if($array['visibility'] === '0') {
$queryWhere .= "prodvisible=0 AND ";
}
}
// Featured products?
if(isset($array['featured'])) {
if($GLOBALS['ISC_CLASS_ADMIN_AUTH']->GetVendorId()) {
$featuredColumn = 'prodvendorfeatured';
}
else {
$featuredColumn = 'prodfeatured';
}
if($array['featured'] == 1) {
$queryWhere .= $featuredColumn."=1 AND ";
}
else if($_REQUEST['featured'] === '0') {
$queryWhere .= $featuredColumn."=0 AND ";
}
}
// Free shipping
if(isset($_REQUEST['freeShipping'])) {
if($_REQUEST['freeShipping'] == 1) {
$queryWhere .= "prodfreeshipping=1 AND ";
}
else if($_REQUEST['freeShipping'] === '0') {
$queryWhere .= "prodfreeshipping=0 AND ";
}
}
// Last imported products
if(isset($_REQUEST['lastImport']) && $_REQUEST['lastImport'] == 1) {
$queryWhere .= "last_import > 0 AND last_import in (select max(last_import) from [|PREFIX|]products) AND ";
}
return array("query" => $queryWhere, "join" => $joinQuery, "categorySearch" => $categorySearch);
}
/**
* 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 = '';
// Construct the full text search part of the query
$fulltext_fields = array("ps.prodname", "ps.prodcode", "ps.proddesc", "ps.prodsearchkeywords");
if (!$fields) {
$fields = "p.*, FLOOR(p.prodratingtotal/p.prodnumratings) AS prodavgrating, ".GetProdCustomerGroupPriceSQL().", ";
$fields .= "pi.* ";
if (isset($searchTerms['search_query']) && $searchTerms['search_query'] != "") {
$fields .= ', '.$GLOBALS['ISC_CLASS_DB']->FullText($fulltext_fields, $searchTerms['search_query'], false) . " as score ";
}
}
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();
$nestedset = new ISC_NESTEDSET_CATEGORIES;
if(isset($searchTerms['category']) && is_array($searchTerms['category'])) {
foreach($searchTerms['category'] as $categoryId) {
$categoryId = (int)$categoryId;
// All categories were selected, so don't continue
if($categoryId == 0) {
$categorySearch = false;
break;
}
$categoryIds[] = $categoryId;
// If searching sub categories automatically, fetch & tack them on
if(isset($searchTerms['searchsubs']) && $searchTerms['searchsubs'] == 'ON') {
foreach ($nestedset->getTree(array('categoryid'), $categoryId) as $childCategory) {
$categoryIds[] = (int)$childCategory['categoryid'];
}
unset($childCategory);
}
}
$categoryIds = array_unique($categoryIds);
if(!empty($categoryIds)) {
$categorySearch = true;
}
}
if($categorySearch == true) {
$fromTable = '[|PREFIX|]categoryassociations a, [|PREFIX|]products p';
$queryWhere[] = 'a.productid=p.productid AND a.categoryid IN ('.implode(',', $categoryIds).')';
}
else {
$fromTable = '[|PREFIX|]products p';
}
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";
}
$joinQuery .= "LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid AND pi.imageisthumb=1) ";
$queryWhere[] = "p.prodvisible='1'";
// Add in the group category restrictions
$permissionSql = GetProdCustomerGroupPermissionsSQL(null, false);
if($permissionSql) {
$queryWhere[] = $permissionSql;
}
// Do we need to filter on brand?
if (isset($searchTerms['brand']) && $searchTerms['brand'] != "") {
$brand_id = (int)$searchTerms['brand'];
$queryWhere[] = "p.prodbrandid='" . $GLOBALS['ISC_CLASS_DB']->Quote($brand_id) . "'";
}
// Do we need to filter on price?
if (isset($searchTerms['price'])) {
$queryWhere[] = "p.prodcalculatedprice='".$GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['price'])."'";
} else {
if (isset($searchTerms['price_from']) && is_numeric($searchTerms['price_from'])) {
$queryWhere[] = "p.prodcalculatedprice >= '".$GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['price_from'])."'";
}
if (isset($searchTerms['price_to']) && is_numeric($searchTerms['price_to'])) {
$queryWhere[] = "p.prodcalculatedprice <= '".$GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['price_to'])."'";
}
}
// Do we need to filter on rating?
if (isset($searchTerms['rating'])) {
$queryWhere[] = "FLOOR(p.prodratingtotal/p.prodnumratings) = '".(int)$searchTerms['rating']."'";
}
else {
if (isset($searchTerms['rating_from']) && is_numeric($searchTerms['rating_from'])) {
$queryWhere[] = "FLOOR(p.prodratingtotal/p.prodnumratings) >= '".(int)$searchTerms['rating_from']."'";
}
if (isset($searchTerms['rating_to']) && is_numeric($searchTerms['rating_to'])) {
$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) {
$queryWhere[] = "p.prodfeatured=1";
}
else {
$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) {
$queryWhere[] = "p.prodfreeshipping='1' ";
}
else {
$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) {
$queryWhere[] = "(p.prodcurrentinv>0 or p.prodinvtrack=0) ";
}
}
if (isset($searchTerms['search_query']) && $searchTerms['search_query'] != "") {
$termQuery = "(" . $GLOBALS['ISC_CLASS_DB']->FullText($fulltext_fields, $searchTerms['search_query'], true);
$termQuery .= "OR ps.prodname like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' ";
$termQuery .= "OR ps.proddesc like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' ";
$termQuery .= "OR ps.prodsearchkeywords like '%" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "%' ";
$termQuery .= "OR ps.prodcode = '" . $GLOBALS['ISC_CLASS_DB']->Quote($searchTerms['search_query']) . "') ";
$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 = '';
}
$query = "
SELECT ".$fields."
FROM ".$fromTable."
".$joinQuery."
WHERE 1=1 AND ".implode(' AND ', $queryWhere).$orderBy;
$countQuery = "
SELECT COUNT(p.productid)
FROM ".$fromTable."
".$joinQuery."
WHERE 1=1 AND ".implode(' AND ', $queryWhere);
return array(
'query' => $query,
'countQuery' => $countQuery
);
}
/**
* Update the root categories list in the data store.
*
* @return mixed The data that was saved if successful, false if there was a problem saving the data.
*/
public function UpdateRootCategories()
{
$nestedset = new ISC_NESTEDSET_CATEGORIES();
$data = array();
foreach ($nestedset->getTree(array('categoryid', 'catparentid', 'catname'), ISC_NESTEDSET_START_ROOT, GetConfig('CategoryListDepth') - 1, null, null, false, array('MIN(`parent`.`catvisible`) = 1')) as $category) {
$data[(int)$category['catparentid']][(int)$category['categoryid']] = $category;
}
$this->Save('ChildCategories', array());
return $this->Save('RootCategories', $data);
}
/**
* Search for products
*
* Method will search for all the products and return an array of product records records
*
* @access public
* @param array $searchQuery The search query array. Currently will only understand the 'search_query' option
* @param int &$totalAmount The referenced variable to store in the total amount of the result
* @param int $start The optional start position of the result total. Default is 0
* @param int $limit The optional limit position of the result total. Default is -1 (no limit)
* @param string $sortBy The optional order by. Default is GetConfig("SearchDefaultProductSort")
* @return array The array result set on success, FALSE on error
*/
static public function searchForItems($searchQuery, &$totalAmount, $start=0, $limit=-1, $sortBy="")
{
if (trim($sortBy) == "") {
$sortBy = GetConfig("SearchDefaultProductSort");
}
if (!is_array($searchQuery)) {
return false;
}
$totalAmount = 0;
if (!is_array($searchQuery) || empty($searchQuery)) {
return false;
}
$fullTextFields = array("ps.prodname", "ps.prodcode", "ps.proddesc", "ps.prodsearchkeywords");
$products = array();
$query = "SELECT SQL_CALC_FOUND_ROWS p.*, FLOOR(p.prodratingtotal/p.prodnumratings) AS prodavgrating,
" . GetProdCustomerGroupPriceSQL() . ", pi.* ";
if (isset($searchQuery["search_query"]) && trim($searchQuery["search_query"]) !== "") {
$query .= ", (IF(p.prodname='" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["search_query"]) . "', 10000, 0) +
IF(p.prodcode='" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["search_query"]) . "', 10000, 0) +
((" . $GLOBALS["ISC_CLASS_DB"]->FullText(array("ps.prodname"), $searchQuery["search_query"], false) . ") * 10) +
" . $GLOBALS["ISC_CLASS_DB"]->FullText($fullTextFields, $searchQuery["search_query"], false) . ") AS score ";
}
$query .= " FROM [|PREFIX|]products p
LEFT JOIN [|PREFIX|]product_images pi ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1) ";
// Sorting or filtering by price. Need to join the tax pricing table
if(!empty($searchQuery['price']) || !empty($searchQuery['price_from']) || !empty($searchQuery['price_to']) || $sortBy == 'priceasc' || $sortBy == 'pricedesc') {
$priceColumn = 'tp.calculated_price';
// Showing prices ex tax, so the tax zone ID = 0
if(getConfig('taxDefaultTaxDisplayCatalog') == TAX_PRICES_DISPLAY_EXCLUSIVE) {
$taxZone = 0;
}
// Showing prices inc tax, so we need to fetch the applicable tax zone
else {
$taxZone = getClass('ISC_TAX')->determineTaxZone();
}
$query .= '
JOIN [|PREFIX|]product_tax_pricing tp
ON (
tp.price_reference=p.prodcalculatedprice AND
tp.tax_zone_id='.$taxZone.' AND
tp.tax_class_id=p.tax_class_id
)
';
}
else {
$priceColumn = 'p.prodcalculatedprice';
}
if (isset($searchQuery["categoryid"])) {
$searchQuery["category"] = array($searchQuery["categoryid"]);
}
$searchTerms = $GLOBALS['ISC_CLASS_SEARCH']->readSearchSession();
$categorySearch = false;
$categoryIds = array();
$nestedset = new ISC_NESTEDSET_CATEGORIES;
if (isset($searchQuery["category"]) && is_array($searchQuery["category"])) {
foreach ($searchQuery["category"] as $categoryId) {
// All categories were selected, so don"t continue
if (!isId($categoryId)) {
$categorySearch = false;
break;
}
$categoryIds[] = (int)$categoryId;
// If searching sub categories automatically, fetch & tack them on
if (isset($searchQuery["searchsubs"]) && $searchQuery["searchsubs"] == "ON") {
foreach ($nestedset->getTree(array('categoryid'), $categoryId) as $childCategory) {
$categoryIds[] = (int)$childCategory['categoryid'];
}
unset($childCategory);
}
}
$categoryIds = array_unique($categoryIds);
if (!empty($categoryIds)) {
$categorySearch = true;
}
}
if ($categorySearch == true) {
$query .= " INNER JOIN [|PREFIX|]categoryassociations a ON a.productid = p.productid AND a.categoryid IN (" . implode(",", $categoryIds) . ") ";
}
if (isset($searchQuery["search_query"]) && trim($searchQuery["search_query"]) !== "") {
$query .= " INNER JOIN [|PREFIX|]product_search ps ON p.productid = ps.productid ";
}
$query .= " WHERE p.prodvisible = 1 " . GetProdCustomerGroupPermissionsSQL();
// Do we need to filter on brand?
if (isset($searchQuery["brand"]) && isId($searchQuery["brand"])) {
$query .= " AND p.prodbrandid = " . (int)$searchQuery["brand"];
}
// Do we need to filter on price?
if (isset($searchQuery["price"]) && is_numeric($searchQuery["price"])) {
$query .= " AND ".$priceColumn." ='" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchTerms["price"]) . "'";
} else {
if (isset($searchQuery["price_from"]) && is_numeric($searchQuery["price_from"])) {
$query .= " AND ".$priceColumn." >= '" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["price_from"]) . "'";
}
if (isset($searchQuery["price_to"]) && is_numeric($searchQuery["price_to"])) {
$query .= " AND ".$priceColumn." <= '" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["price_to"]) . "'";
}
}
// Do we need to filter on rating?
if (isset($searchQuery["rating"])) {
$query .= " AND FLOOR(p.prodratingtotal/p.prodnumratings) = " . (int)$searchQuery["rating"];
} else {
if (isset($searchQuery["rating_from"]) && is_numeric($searchQuery["rating_from"])) {
$query .= " AND FLOOR(p.prodratingtotal/p.prodnumratings) >= " . (int)$searchQuery["rating_from"];
}
if (isset($searchQuery["rating_to"]) && is_numeric($searchQuery["rating_to"])) {
$query .= " AND FLOOR(p.prodratingtotal/p.prodnumratings) <= " . (int)$searchQuery["rating_to"];
}
}
// Do we need to filter on featured?
if (isset($searchQuery["featured"]) && is_numeric($searchQuery["featured"])) {
if ((int)$searchQuery["featured"] == 1) {
$query .= " AND p.prodfeatured = 1 ";
} else {
$query .= " AND p.prodfeatured = 0 ";
}
}
// Do we need to filter on free shipping?
if (isset($searchQuery["shipping"]) && is_numeric($searchQuery["shipping"])) {
if ((int)$searchQuery["shipping"] == 1) {
$query .= " AND p.prodfreeshipping = 1 ";
}
else {
$query .= " AND p.prodfreeshipping = 0 ";
}
}
// Do we need to filter only products we have in stock?
if (isset($searchQuery["instock"]) && is_numeric($searchQuery["instock"])) {
if ((int)$searchQuery["instock"] == 1) {
$query .= " AND (p.prodcurrentinv > 0 OR p.prodinvtrack = 0) ";
}
}
if (isset($searchQuery["search_query"]) && trim($searchQuery["search_query"]) !== "") {
$searchPart = array();
if (GetConfig("SearchOptimisation") == "fulltext" || GetConfig("SearchOptimisation") == "both") {
$searchPart[] = $GLOBALS["ISC_CLASS_DB"]->FullText($fullTextFields, $searchQuery["search_query"], true);
}
if (GetConfig("SearchOptimisation") == "like" || GetConfig("SearchOptimisation") == "both") {
$searchPart[] = "p.prodname LIKE '%" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["search_query"]) . "%'";
$searchPart[] = "p.proddesc LIKE '%" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["search_query"]) . "%'";
$searchPart[] = "p.prodsearchkeywords LIKE '%" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["search_query"]) . "%'";
}
$query .= " AND (ps.prodcode = '" . $GLOBALS["ISC_CLASS_DB"]->Quote($searchQuery["search_query"]) . "' OR TRUE) ";
$query .= " AND (" . implode(" OR ", $searchPart) . ") ";
}
$orderBy = "";
switch (isc_strtolower($sortBy)) {
case "relevance":
if (isset($searchQuery["search_query"]) && trim($searchQuery["search_query"]) !== "") {
$orderBy = "score DESC";
}
break;
case "featured":
$orderBy = "p.prodfeatured DESC";
break;
case "newest":
$orderBy = "p.productid DESC";
break;
case "bestselling":
$orderBy = "p.prodnumsold DESC";
break;
case "alphaasc":
$orderBy = "p.prodname ASC";
break;
case "alphadesc":
$orderBy = "p.prodname DESC";
break;
case "avgcustomerreview":
$orderBy = "prodavgrating DESC";
break;
case "priceasc":
$orderBy = $priceColumn.' ASC';
break;
case "pricedesc":
$orderBy = $priceColumn.' DESC';
break;
}
if (trim($orderBy) !== "") {
$query .= " ORDER BY " . $orderBy;
} else {
$query .= " ORDER BY p.productid DESC";
}
if (is_numeric($limit) && $limit > 0) {
if (is_numeric($start) && $start > 0) {
$query .= " LIMIT " . (int)$start . "," . (int)$limit;
} else {
$query .= " LIMIT " . (int)$limit;
}
}
$result = $GLOBALS["ISC_CLASS_DB"]->Query($query);
$row = $GLOBALS["ISC_CLASS_DB"]->Fetch($result);
if (!$row) {
return array();
}
$totalAmount = $GLOBALS["ISC_CLASS_DB"]->FetchOne("SELECT FOUND_ROWS()");
$products[$row["productid"]] = $row;
while ($row = $GLOBALS["ISC_CLASS_DB"]->Fetch($result)) {
$products[$row["productid"]] = $row;
}
return $products;
}
