<?php
/***************************************************************************
 *                                                                          *
 * Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
 *                                                                          *
 * This  is  commercial  software,  only  users  who have purchased a valid *
 * license  and  accept  to the terms of the  License Agreement can install *
 * and use this program. 
 *                                                                          *
 ****************************************************************************
 * PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
 * "copyright.txt" FILE PROVIDED W
 ITH THIS DISTRIBUTION PACKAGcitDet.            *
 ****************************************************************************/
//
//$Id: ProductListModel.php, v 1.0 2011/06/14 20:52:11
//
class ProductListModel extends Model
{
	/* function to get the option count of particular product */
	public function getProductOptionCount($params){ 
		extract($params);
		$showAllProducts = !empty($showAllProducts)?$showAllProducts:'';
		if($showAllProducts=='Yes') {
			$updprodstatus = " IN ('Active','Inactive') ";
		} else {
			$updprodstatus = " = 'Active' ";				
		}
		$sql="
			SELECT 
				COUNT(productOptionID) AS productOptionCount
			FROM 
				".DB_PREFIX."product_options
			WHERE
				productID='".$productID."'
				AND
				productOptionStatus ".$updprodstatus."";
		return $this->ObjDatabase->getFieldSlave($sql);
	}
	public function getProductOptionQuantitySum($params=array()){
		extract($params);
		if($inventoryTrackLevel=='No'){
			return 1;
		}
		else{
			if($inventoryTrackLevel=='Product'){
				$sql="
					SELECT 
						SUM(productOptionQuantity) AS productOptionQuantitySum
					FROM 
						" . DB_PREFIX . "product_options
					WHERE 
						productID='".$productID."'
						AND 
						productOptionStatus='Active'
				";
			}
			else{
				$sql="
					SELECT 
						SUM(combinationQty)
					FROM 
						" . DB_PREFIX . "product_combination as proCom1
					WHERE 
						proCom1.productID='".$productID."'								
						AND 
						proCom1.status='A'
				";
			}
			return $this->ObjDatabase->getFieldSlave($sql);
		}
		
	}
	public function fetchProdCustomInfo($languageIDs,$prodID,$fields){
		$sql 		= "SELECT 
		".implode(",", $fields)." 
						FROM 
							".DB_PREFIX."products			AS product
						LEFT JOIN	
							".DB_PREFIX."product_details	AS productDet
								ON (
									product.productID = productDet.productID
									AND 
									productDet.languageID = '".$languageIDs['Current']."'
								)
						WHERE 
							product.productID='".$prodID."' 
						LIMIT 0,1";
		return $this->ObjDatabase->readValueSlave($sql);
	}
	
	/* Function to list the tier price of single product variant */
	public function listTierPrices($params=array()){
		extract($params);
		$sql = "
			SELECT
				quantity, 
				allGroups, 
				MIN(tierPrice) AS tierPrice, 
				userGroupID
			FROM
				".DB_PREFIX."variant_tier_price
			WHERE
				productOptionID = '".$productOptionID."'
				AND
				(
					userGroupID = '".$userGroupID."'
					OR
					allGroups='1'
				)
		";
		if(isset($offerPrice)){
			$sql.="
				AND
				tierPrice < '".$offerPrice."'
			";
		}
		$sql.="
			GROUP BY 
				quantity 
			ORDER BY 
				quantity, tierPrice
		";
		return $this->ObjDatabase->readValuesSlave($sql,MYSQLI_ASSOC);
	}
	
	/* Function to get the minimum tier price of  product variant  */
	public function getMinTierPrices($params=array()){
		extract($params);
		$sql = "
			SELECT
				MIN(tierPrice) AS tierPrice
			FROM
				".DB_PREFIX."variant_tier_price
			WHERE
				productOptionID = '".$productOptionID."'
				AND
				(
					userGroupID = '".$userGroupID."'
					OR
					allGroups='1'
				)
		";
		if(isset($offerPrice)){
			$sql.="
				AND
				tierPrice < '".$offerPrice."'
			";
		}
		$result = $this->ObjDatabase->readValueSlave($sql,MYSQLI_ASSOC);
		return $result['tierPrice'];
	}
	
	public function listHomePageProducts($params)
	{
		extract($params);
		$currDateTime 		= '';
		$currentLanguageID	= $languageIDs['Current'];
		$limitFrom			= ($limitFrom >0) ? $limitFrom : 0;
		//$inclCalcRows		= ($limitTo >= '0') ? "SQL_CALC_FOUND_ROWS" : "";
		$currDateTime		= ($currDateTime == "") ? "'" . date('Y-m-d') . "'" : "'" . $currDateTime . "'";
		$customWhere		= '';
		switch($customOrderBy) {
			case "featured":
				$customWhere	= " AND product.featuredStatus='Yes' ";
				$customOrderBy	= "
									product.productID DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC ";
				break;
			case "best_sellers":
				$customWhere	= " AND product.productSoldQuantity>0 ";
				$customOrderBy	= "	productOrdersCount DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC
									 ";
				break;
			case "spcl_offers":
				$customWhere	= " 
									AND 
									prodOptBus.productOptionRegularPrice!=LEAST(
										COALESCE(
											varOffPri.offerPrice,
											prodOptBus.productOptionProductPrice
										),
										COALESCE(
											varGroPri.specialPrice,
											prodOptBus.productOptionProductPrice
										) 
									) ";
				$customOrderBy	= "	productOptionOfferDiscount DESC,
									product.productEndTime ASC,
									product.productID DESC
									";
				break;
			case "new_products":
				$customOrderBy	= "product.productID DESC ";
				break;
			default:
				$customOrderBy 	= "product.productID ";
				break;
		}
		
		$sql	= "
				SELECT 
					product.productID,
					product.productUrl,
					product.productStartTime,
					product.productEndTime,
					product.InventoryTrackLevel,
					product.multiOptionsAvailable,
					product.showCountTimer,
					product.productDefaultCurrency,
					product.productImage,
					productDet.productTitle,
					productDet.productDescription,
					product.featuredStatus,
					productOpt.productOptionID,
					prodOptBus.productOptionRegularPrice,
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					AS offerPrice,
					ROUND(
						(
							(
								(
								prodOptBus.productOptionRegularPrice-
									LEAST(
										COALESCE(
											varOffPri.offerPrice,
											prodOptBus.productOptionProductPrice
										),
										COALESCE(
											varGroPri.specialPrice,
											prodOptBus.productOptionProductPrice
										) 
									)
								)*100
							)/prodOptBus.productOptionRegularPrice
						),2
					) AS productOptionOfferDiscount,
					
					product.productSoldQuantity AS productOrdersCount , 
					IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									SUM(productOptionQuantity)
								FROM 
									" . DB_PREFIX . "product_options
								WHERE 
									productID=product.productID 
									AND 
									productOptionStatus='Active'
							),
							(
								SELECT 
									SUM(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination AS proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status='A'
							)
						)
					)
					AS productOptionQuantitySum		
				FROM 
					" . DB_PREFIX . "products AS product 
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = '" . $currentLanguageID . "'
						)
				INNER JOIN 
					".DB_PREFIX."business AS bus 
						ON (
							product.businessID = bus.businessID 
							AND 
							bus.businessStatus = 'Active'
						)
				INNER JOIN 
					".DB_PREFIX."product_options AS productOpt 
						ON (
							product.productID = productOpt.productID
							AND
							productOpt.productOptionStatus = 'Active' 
							AND 
							productOpt.productOptionDefault = 'Yes'
						)  
				LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
				LEFT JOIN
					".DB_PREFIX."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".$userGroupID."'
                                                        AND varOffPri.businessID = prodOptBus.businessID
						)
				LEFT JOIN
					".DB_PREFIX."variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='".$userGroupID."'
                                                        AND varGroPri.businessID = prodOptBus.businessID
						)
			WHERE
				1 
					".$customWhere ."
				AND 
					product.productStatus = 'Active' 
				AND (
					product.productStartTime <= " . $currDateTime . " 
					AND
					(
						product.productEndTime = '0000-00-00'
						OR
						product.productEndTime >=	" . $currDateTime . "
					)
				)
		";
		if(isset($brandID) && $brandID>0 ){
			$sql.="
				AND
				product.brandID ='".$brandID."'
			";
		}
		
		if(isset($exceptProductIDs) && count($exceptProductIDs) > 0)
		{
			$sql	.= "
				AND 
					product.productID NOT IN (" . implode(",",$exceptProductIDs) . ") ";
		}

		if( isset($hide_out_of_stock_in_homepage) && $hide_out_of_stock_in_homepage=='Yes' ){
			$sql.="
			HAVING 
				productOptionQuantitySum > 0
			";
		}
		$sql 	.= " 
			
			ORDER BY 
				".$customOrderBy."
				
		";
	
		if ($limitTo >= '0')
		{
			$sql .= " LIMIT  " . $limitFrom . "," . $limitTo . " ";
		}
		//echo '<pre>'.$sql; exit;
		return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
		
	}
	/* END : listHomePageProducts */
	
	/* 	
		@function to list the products under search and catelog 
		Related Functions : listHomePageProducts()
	*/
	public function listActiveProducts($params)
	{
		extract($params);
		$currentLanguageID              = $languageIDs['Current'];
                $isProductDeal			= isset($isProductDeal)  ? $isProductDeal : 'No';
                $freeProducts			= isset($freeProducts)  ? $freeProducts : '';
                $freeProdCount			= isset($freeProdCount)  ? $freeProdCount : 0;
		$limitFrom			= (isset($limitFrom) && $limitFrom >0) ? $limitFrom : 0;
		$currDate			= (isset($currDateTime) && $currDateTime!= "") ? "'" . $currDateTime . "'" : "'" . date('Y-m-d') . "'";
		$currTime			= date('Y-m-d H:i:00');
		$customWhere                    = ' AND productStatus != "Buybox" ';
		$categoryID			= isset($categoryID)?$categoryID:'';
		$customOrderBy                  = isset($customOrderBy)?$customOrderBy:'';
                $backcustomOrderBy              = isset($customOrderBy)?$customOrderBy:'';
                $customGroupBy                  = isset($customGroupBy)?$customGroupBy:'';
                $bodyStyle                      = isset($bodyStyle)?$bodyStyle:'';
                $bmwCode                        = isset($bmwCode)?$bmwCode:'';
                $custom                         = isset($custom)?$custom:'';
                $custCode                       = isset($custCode)?$custCode:'';
                $frontEnd                       = isset($frontEnd)?$frontEnd:'Y';
                $categoryIDs                    = isset($categoryIDs)?$categoryIDs:'';
                $showAllProducts                = isset($showAllProducts)?$showAllProducts:'';
                if($businessID>0){
                    $sqlProdBusinessID          = "prodOptBus.businessID,";
                    $sqlProdOptBusiness         = " AND prodOptBus.businessID='".$businessID."'";
                }else{
                    if($freeProducts!="" && $freeProdCount>0){
                        $sqlProdBusinessID          = "prodOptBus.businessID,";
                        $sqlProdOptBusiness         = " AND (IF(product.InventoryTrackLevel='No',
                                                                                prodOptBus.businessOptionDefault='Yes',
                                                                                prodOptBus.productOptionQuantity>='".$freeProdCount."'))";
                    }else{
                        $sqlProdBusinessID          = "prodOptBus.businessID,";
                        //$sqlProdOptBusiness         = " AND prodOptBus.businessOptionDefault='Yes'";
                        $sqlProdOptBusiness         = " AND (IF(product.InventoryTrackLevel='No',
                                                                                prodOptBus.businessOptionDefault='Yes',
                                                                                prodOptBus.productOptionBusinessID= (SELECT productOptionBusinessID FROM " . DB_PREFIX . "products_options_business
                                                              WHERE productOptionID = productOpt.productOptionID
                                                              AND productOptionQuantity>0
                                                              ORDER BY FIELD (businessOptionDefault,'Yes','No')
                                                              LIMIT 1)))";
                    }
                }
               
                
		switch($customOrderBy) {
			case "featured":
				$customOrderBy	= "	FIELD(product.featuredStatus,'Yes',''),
									product.productID DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC ";
				break;
			case "best_sellers":
				$customOrderBy	= "	purchasePopularity DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC,
									FIELD(product.featuredStatus,'Yes','') ";
				break;
			case "spcl_offers":
                                if($isProductDeal=='Yes') {
                                    $customOrderBy	= " dealDiscount DESC,
									product.productEndTime ASC,
									product.productID DESC,
									FIELD(product.featuredStatus,'Yes','') ";
                                } else {
                                    $customOrderBy	= "	productOptionOfferDiscount DESC,
									product.productEndTime ASC,
									product.productID DESC,
									FIELD(product.featuredStatus,'Yes','') ";
                                }
				break;
			case "new_products":
				$customOrderBy	= "	product.productID DESC ";
				break;
			case "end_soon":
				$customOrderBy	= "	product.productEndTime ASC ";
				break;
			case "popularity":
				$customOrderBy	= "	popularity DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC,
									FIELD(product.featuredStatus,'Yes','') ";	// modify as required. now it's best_sellers
				break;	
			case "price_asc":
				$customOrderBy	= " offerPrice ASC ";
				break;
			case "price_desc":
				$customOrderBy	= " offerPrice DESC ";
				break;	
			case "recentViewed":  
				$customOrderBy	= " FIELD(product.productID,".implode(",",$includeProductIDs).")";
				break;	
                        case "custom":  
				$customOrderBy	= " -menIteProPri.product_sort_order DESC,product.productID";
				break;    
			default:
                            if (isset($menuItemID) && $menuItemID > 0) {
                                $customOrderBy = " -menIteProPri.product_sort_order DESC,product.productID ";
                            }else {
                                $customOrderBy = " product.productID ";
                            }
				break;
                            
		}
		if(isset($returnSql) && $returnSql){
			$SQL_CALC_FOUND_ROWS = '' ; 
		}
		else{
			$SQL_CALC_FOUND_ROWS = 'SQL_CALC_FOUND_ROWS';
		}
                /* for Attribute search*/
		$attrSearch                     =   1;
		$attrOptionIDs                  =   '';
		$attrQuerySearch                =   1;
		$attrQuerySearchOptionIDs       =   '';               
		if(isset($attrDataObj) && $attrDataObj!='' && !empty($attrDataObj))
		{
			$sqlAttr = "select prod.productID from " . DB_PREFIX . "products prod inner join " . DB_PREFIX . "product_to_category cat on (cat.productID=prod.productID) where ";//prod.productStatus='Active' ";
			if($showAllProducts=='Yes') {
				$sqlAttr  .= "  prod.productStatus IN ('Active','Inactive') ";
			} else {
				$sqlAttr  .= "  prod.productStatus = 'Active' ";				
			}
			if(isset($categoryID) && $categoryID!='' && $categoryID!='0')
			{
					$sqlAttr 	.= "    AND cat.productCategoryID IN (" . $categoryID . ") ";
			}                        
			foreach($attrDataObj as $attrData)
			{
						$attrOption = "'".implode("','",$attrData)."'";

						$sqlAttr 	.= " And prod.productID IN
								(
										SELECT
												productID
										FROM
												".DB_PREFIX."product_attribute_values
										WHERE
												attrOptionID IN(".$attrOption.")) ";                               

			}
			$sqlAttr 	.= " group by prod.productID ";
			$resAttr        =  $this->ObjDatabase->readValuesSlave($sqlAttr,MYSQLI_ASSOC);
			if(!empty($resAttr)) {                            
			   $prodArr = array();
			   foreach($resAttr as $pIDs) {
				   $prodArr[] = $pIDs['productID'];
			   }                           
			   $attrOptionIDs = "'".implode("','",$prodArr)."'";                            
			} else {
				$attrSearch = 0;    
				}
			}                    
			if(isset($attrQueryStringArray) && !empty($attrQueryStringArray))
			{

				$sqlAttr = "select prod.productID from " . DB_PREFIX . "products prod inner join " . DB_PREFIX . "product_to_category cat on (cat.productID=prod.productID) where ";//prod.productStatus='Active' ";
				if($showAllProducts=='Yes') {
					$sqlAttr  .= "  prod.productStatus IN ('Active','Inactive') ";
				} else {
					$sqlAttr  .= "  prod.productStatus = 'Active' ";				
				}
				if(isset($categoryID) && $categoryID!='' && $categoryID!='0')
				{
						$sqlAttr 	.= "    AND cat.productCategoryID IN (" . $categoryID . ") ";
				}                        
				foreach($attrQueryStringArray as $attrData)
				{
							$attrOption = "'".implode("','",$attrData)."'";

							$sqlAttr 	.= " And prod.productID IN
									(
											SELECT
													productAttrVal.productID
											FROM
													".DB_PREFIX."product_attribute_values productAttrVal
											WHERE
													productAttrVal.attrOptionID IN(".$attrOption.")) ";                                   
				} 
				$sqlAttr 	.= " group by prod.productID ";
				$resAttr        =  $this->ObjDatabase->readValuesSlave($sqlAttr,MYSQLI_ASSOC);
				if(!empty($resAttr)) {                            
				   $prodArr = array();
				   foreach($resAttr as $pIDs) {
					   $prodArr[] = $pIDs['productID'];
				   }                           
				   $attrQuerySearchOptionIDs = "'".implode("','",$prodArr)."'";                            
				} else {
					$attrQuerySearch = 0;    
				}	

			}
			if($attrQuerySearch<=0 || $attrSearch<=0) {
                    return array();
            }
		/* End */
                if($frontEnd!='') {
                    $sql = "SELECT ".$SQL_CALC_FOUND_ROWS." * from ( SELECT ";
                } else {
                    $sql = " SELECT ".$SQL_CALC_FOUND_ROWS." ";
                }
				if($showAllProducts=='Yes') {
					$updprodcombstatus  = " IN ('A','I') ";
					$updprodstatus = " IN ('Açtive','Inaçtive')";	
				} else {
					$updprodcombstatus  = "  = 'A' ";	
					$updprodstatus = " ='Açtive'";		
				}
		$sql	.= "		product.brandID,
					product.productID,
					'".$this->escape($categoryID)."' as categoryIDs,
					product.productUrl,					
					'".$currTime."' AS timeZoneConverted,
					product.productDefaultCurrency,
					product.productMinBuyLimit,
					product.productMaxBuyLimit,
                                        product.dealMaxBuyLimit,
					product.InventoryTrackLevel,
					product.showQuantityAvailable,
					product.multiOptionsAvailable,
					product.productCommissionRate,
					product.productServiceFee,
					product.showCountTimer, ";
                if($frontEnd!='') {
                    $sql.=              " veh_product.vehicleImage as productImage,";
                    $sql.=              " productDet.productShortDescKey as custbmwcode,";
                    //$sql.=              " (select GROUP_CONCAT(DISTINCT(optVar.bmwCode)) from " . DB_PREFIX . "product_options optVar inner join " . DB_PREFIX . "product_details proDetails on (optVar.productID=proDetails.productID and proDetails.languageID='".$currentLanguageID."')  where proDetails.productShortDescription=productDet.productShortDescription) as custbmwcode,";
                } else {
                    $sql.=              " product.productImage,";
                }
		$sql.=              " 
					product.attributeSetID,
					productDet.productTitle,
					productDet.productDescription,
                                        productDet.productShortDescription as productShortDescription,
                                        finance.shortDescription as financeDesc,
                                        finance.detailedDescription as financeDetailedDesc,
                                        product.bodyStyle,
                                        product.leaseAvail,
                                        product.financeAvail, 
					product.freeProductsCount,ROUND(prodOptBus.monthlyInstallment) as monthlyInstallment, ";
                if(!isset($apilisting) || $apilisting != "listing")
                {$sql.= "            productDet.productSizeChart as sizeChart,
                                    productDet.enableSizeChart,     ";}
                    $sql.= "       	
					product.featuredStatus,
					bus.businessKey,
					bra.brandKey,
					braDet.brandName,
					productOpt.productOptionID,
					productOpt.optionSKU,
                                        productOpt.bmwCode,
                                        productOpt.modelYear,
					prodOptBus.productOptionProductPrice,
					prodOptBus.productOptionRegularPrice,
					prodOptBus.productOptionQuantity,
					prodOptBus.productQuantityUnlimited,
                                        prodOptBus.businessID,
                                        prodOptBus.productOptionBusinessID,
                                        productOpt.productOptionCashBack,                                        
					ROUND((((prodOptBus.productOptionRegularPrice-prodOptBus.productOptionProductPrice)*100)/prodOptBus.productOptionRegularPrice),2) AS productOptionDiscount,
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					AS offerPrice, 
					ROUND(
						(
							(
								(
								prodOptBus.productOptionRegularPrice-
									LEAST(
										COALESCE(
											varOffPri.offerPrice,
											prodOptBus.productOptionProductPrice
										),
										COALESCE(
											varGroPri.specialPrice,
											prodOptBus.productOptionProductPrice
										) 
									)
								)*100
							)/prodOptBus.productOptionRegularPrice
						),2
					) AS productOptionOfferDiscount,
					product.productSoldQuantity AS productOrdersCount ,
					attrSet.comparable,
					IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									sum(pdtOptBus.productOptionQuantity)
								FROM 
									" . DB_PREFIX . "products_options_business pdtOptBus
                                                                INNER JOIN " . DB_PREFIX . "product_options pdtOpt ON
                                                                     (pdtOpt.productOptionID = pdtOptBus.productOptionID)
								WHERE 
									pdtOpt.productID=product.productID
                                                                         AND pdtOptBus.busProductStatus ".$updprodstatus."
                                                                        AND pdtOpt.productOptionStatus ".$updprodstatus."
							),
							(
								SELECT 
									sum(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination as proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status ".$updprodcombstatus."
							)
						)
					)
					as productOptionQuantitySum
				";
                                //((productOpt.productOptionRegularPrice)-(productOpt.productOptionRegularPrice*(product.dealDiscount/100))) AS dealPrice
				//if(isset($isProductDeal) && $isProductDeal=='Yes'){
				$sql	.=    " , product.isProductDeal,
                                                  product.dealDiscount,
                                                  IF(prodOptBus.dealPrice>0,prodOptBus.dealPrice,ROUND((prodOptBus.productOptionProductPrice)-(prodOptBus.productOptionProductPrice*(product.dealDiscount/100)))) AS dealPrice
				";
				//}

                                
                //if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {
                    $sql	.=    " , prodcat.productCategoryID, prodcat.productCategoryKey, prodcatdet.productCategoryName,prodcat.productCategorySortOrder";
                //}                      
                              

		$sql	.=      " FROM 
					" . DB_PREFIX . "products AS product 
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " . $currentLanguageID . "
						)
				LEFT JOIN
					" . DB_PREFIX . "attribute_set AS attrSet
					ON attrSet.attributeSetID = product.attributeSetID";
				
		/*if(($categoryID !='' &&  $categoryID))// || (isset($searchKeyword) && $searchKeyword!='')
		{	
			$sql 	.= "
				LEFT JOIN 
				" . DB_PREFIX . "product_to_category   	AS productToCat 
					ON (product.productID = productToCat.productID) 
					";
		} elseif( !empty($categoryIDs)) {
                    $sql 	.= "
				LEFT JOIN 
				" . DB_PREFIX . "product_to_category   	AS productToCat 
					ON (product.productID = productToCat.productID) 
					";
                }*/
                if($selectedProductID!='' && $freeProducts!='') {
                    $sql 	.= "
				INNER JOIN 
				" . DB_PREFIX . "product_link_free   	AS productFree 
					ON (product.productID = productFree.freeProductID) 
					";
                }

//		if(isset($searchKeyword) && !empty($searchKeyword)){
//			$sql 	.= " 
//				LEFT JOIN
//					" . DB_PREFIX . "product_category_details	AS productCatDet 
//						ON (
//							productToCat.productCategoryID = productCatDet.productCategoryID
//							AND 
//							productCatDet.languageID = " . $currentLanguageID . "
//						)";
//			}
               	
	
		
		$sql.="      LEFT JOIN 
				".DB_PREFIX."product_options	AS productOpt 
					ON (
						product.productID = productOpt.productID
						AND
						productOpt.productOptionStatus ".$updprodstatus."
						AND 
						productOpt.productOptionDefault = 'Yes'
					) 
			LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							$sqlProdOptBusiness
						)
                        LEFT JOIN 
				".DB_PREFIX."finance_rate	AS finance 
					ON (
						finance.variantCode = productOpt.optionSKU						
					)                         
			LEFT JOIN
				".DB_PREFIX."variant_offer_price AS varOffPri
					ON	(
						varOffPri.productOptionID = productOpt.productOptionID
						AND
						varOffPri.userGroupID='".$userGroupID."'
                                                AND varOffPri.businessID = prodOptBus.businessID
					)	
			LEFT JOIN
				".DB_PREFIX."variant_group_price AS varGroPri
					ON	(
						varGroPri.productOptionID = productOpt.productOptionID
						AND
						varGroPri.userGroupID='".$userGroupID."'
                                                AND varGroPri.businessID = prodOptBus.businessID
					)	
			LEFT JOIN
				" . DB_PREFIX . "brands AS bra
					ON	(
						bra.brandID=product.brandID 
					)
			LEFT JOIN
				" . DB_PREFIX . "brand_details AS braDet
					ON	(
						braDet.brandID=bra.brandID 
						AND 
						braDet.languageID=" . $currentLanguageID . "
					)
			
			";
                    if($frontEnd!='') {

                        $sql.=              " LEFT JOIN " . DB_PREFIX . "vehicle_images AS veh_product
                                            ON (veh_product.variantCode = SUBSTRING_INDEX(productOpt.optionSKU,'-',1) and veh_product.imageType='png')";

                    }    
                    //if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {
                        //if($customGroupBy=='bodyStyle') {
                            $sql 	.= " LEFT JOIN " . DB_PREFIX . "product_to_category productToCat on (productToCat.productID=product.productID)";
                        //} 
                        $sql 	.= " LEFT join " . DB_PREFIX . "product_categories prodcat on (prodcat.productCategoryID=productToCat.productCategoryID )
                                    LEFT JOIN " . DB_PREFIX . "product_category_details AS prodcatdet
					ON	(
						prodcatdet.productCategoryID=prodcat.productCategoryID 
						AND 
						prodcatdet.languageID=" . $currentLanguageID . "
					)
                                    ";

                    //}    
                    if(isset($businessID) && $businessID > 0 ){
                            $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."business 	AS bus 
                                            ON (
                                                    prodOptBus.businessID = bus.businessID 
                                                    AND 
                                                    bus.businessStatus = 'Active'
                                                    AND
                                                    bus.businessID='".$businessID."')";
                    }else{
                        $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."business 	AS bus 
                                            ON (
                                                    product.businessID = bus.businessID 
                                                    AND 
                                                    bus.businessStatus = 'Active')";
                    }
			
		if (isset($menuItemID) && $menuItemID > 0 || $backcustomOrderBy=='custom') {
                    $sql .= "
                                        LEFT JOIN
                                                " . DB_PREFIX . "menu_item_product_priority AS menIteProPri
                                                        ON (
                                                                menIteProPri.product_id=product.productID 
                                                                AND 
                                                                menuItemID='" . $menuItemID . "'
                                                        )
                                                        ";
                }   
                if(!empty($custCode)) {
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_link 	AS prodcustlink 
                                            ON (
                                                    prodcustlink.productID = product.productID 
                                                   )";
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_variant 	AS prodCustVar 
                                            ON (
                                                    prodCustVar.customOptionID = prodcustlink.customOptionID 
                                                   )";
                }

		$sql	.= " 
			WHERE
					1 ".$customWhere;
					
		if(isset($productType['productTypeID']) && $productType['productTypeID'] > 0)
		{
			$sql	.= "
				AND 
					product.productTypeID = '".$productType['productTypeID']."' ";
		}

                //if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {

                    $sql	.= " AND prodcat.productCategoryStatus='Active' ";
                //}
		if(!empty($bodyStyle)) {
                   
                    $bodyStyle   =  "'".implode("','",$bodyStyle)."'";
                    $sql	.= " AND  product.bodyStyle IN (".$bodyStyle.")";
                   
                }
                if($frontEnd!='') {
                    $sql	.= " AND  prodOptBus.monthlyInstallment >0";
                }
                if(!empty($custCode)) {
                    
                    $custCode   =  "'".implode("','",$custCode)."'";
                    $sql	.= " AND  prodCustVar.variantCode IN (".$custCode.")";
                    
                }
                if(!empty($bmwCode)) {
                    if($frontEnd!='') {
                        /*$bmwCode = $this->encryptDecrypt("decrypt",$bmwCode);
                        $bmwCode = explode(",",$bmwCode);
                        $sql	.= " AND  productOpt.bmwCode IN ('" . implode("','",$bmwCode) . "') ";*/
                        $sql	.= " AND  productDet.productShortDescKey = '" . $bmwCode . "' ";
                    } else {
                        $sql	.= " AND  productOpt.bmwCode = '".$bmwCode."'";
                    }
                }
                
		if (isset($previewOnly) && $previewOnly == "Yes")
		{
			$sql 	.= "	
				AND 
					product.productID = '" . $selectedProductID . "'
				AND 
					product.productStartTime = " . $currDate . " ";
		}
		else
		{
                    if($showpage==''){
			if(isset($isProductDeal) && $isProductDeal=='No'){
			$sql 	.= "
				AND 
					product.productStatus ".$updprodstatus."
				
			";
			}
			else{
				$sql 	.= "
				AND 
					product.productStatus ".$updprodstatus." ";
                                
			}
                    }
                    else{
                       
                    $sql 	.= " AND 
					(product.productStatus ".$updprodstatus." OR 
                         product.productStatus = 'Buybox' )
                         ";
                
                    }
		}
		
		if(isset($exceptProductIDs) && count($exceptProductIDs) > 0)
		{
			$sql	.= "
				AND 
					product.productID NOT IN ('" . implode("','",$exceptProductIDs) . "') ";
		}
		
		if($selectedProductID!='' && $freeProducts!='') {
                    $sql 	.= " AND productFree.productID ='".$selectedProductID."'";
                }
		if(isset($includeProductIDs) && count($includeProductIDs) > 0)
		{
			$sql	.= "
				AND 
					product.productID IN ('" . implode("','",$includeProductIDs) . "') ";
		}
		
		if(isset($brandID) && $brandID>0){
			$sql 	.= "
				AND 
					(bra.brandID ='".$brandID."' || bra.parentID ='".$brandID."')";
		}
		if(isset($brandIDs) && $brandIDs!='' && !empty($brandIDs)){
                    if($custom!='') {
                        $sql 	.= "
				AND 
					product.brandID IN (".$brandIDs.")";
                    } else {
                        $sql 	.= "
				AND 
					product.brandID IN ('".implode("','",$brandIDs)."')";
                    }
			
		}
		
		
		if(isset($categoryID) && $categoryID!='' && $categoryID!='0')
		{
			$sql 	.= "
				AND 
					productToCat.productCategoryID IN (" . $categoryID . ") ";
		}
                else {
                    if(!empty($categoryIDs))
                    {
                            $categoryIDs   =  "'".implode("','",$categoryIDs)."'";
                            $sql 	.= "
                                    AND 
                                            productToCat.productCategoryID IN (" . $categoryIDs . ") ";
                    }
                }
		if($attrSearch>0 && $attrOptionIDs!='') {                         
			$sql	.= " AND product.productID IN (" . $attrOptionIDs . ") ";
		}
		if($attrQuerySearch>0 && $attrQuerySearchOptionIDs!='') {                         
			$sql	.= " AND product.productID IN (" . $attrQuerySearchOptionIDs . ") ";
		}   
		if(isset($priceRangeMin) && $priceRangeMin >= 0 && $priceRangeMax > 0)
		{
			$sql	.= "
				AND 
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					) BETWEEN ".$priceRangeMin." AND ".$priceRangeMax;
		}
                if(isset($emiRangeMin) && $emiRangeMin >= 0 && $emiRangeMax > 0)
		{
			$sql	.= " AND prodOptBus.monthlyInstallment BETWEEN ".$emiRangeMin." AND ".$emiRangeMax;
		}
		if(isset($discountRange) && $discountRange > 0)
		{
			$sql	.= "
				AND 
					ROUND(
						(
							(
								(
								productOpt.productOptionRegularPrice-
									LEAST(
										COALESCE(
											varOffPri.offerPrice,
											prodOptBus.productOptionProductPrice
										),
										COALESCE(
											varGroPri.specialPrice,
											prodOptBus.productOptionProductPrice
										) 
									)
								)*100
							)/productOpt.productOptionRegularPrice
						),2
					) >= ".$discountRange;
		}
		
		if(isset($searchKeyword) && !empty($searchKeyword)){

			$productTitle			=	array();
			$productCategoryName	=	array();
			$searchTags			=	array();
			$productOptionName		=	array();
			foreach($searchKeyword as $keyword){
				$keyword=$this->escape($keyword);
				$productTitle[]		=	" productDet.productTitle LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				//$productCategoryName[]	=	" productCatDet.productCategoryName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$searchTags[]		=	" productDet.searchTags LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$productOptionName[]	=	" productOptDet2.productOptionName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
			}
			$sql.= " 
				AND (
					(".implode(" OR ",$productTitle ).")					
					OR
					(".implode(" OR ",$searchTags ).")
					OR EXISTS (
						SELECT productOpt2.productID
						FROM 
							".DB_PREFIX."product_options			AS productOpt2
						LEFT JOIN 
							".DB_PREFIX."product_option_details	AS productOptDet2
								ON (
								productOpt2.productOptionID = productOptDet2.productOptionID
								AND
								productOptDet2.languageID = '".$currentLanguageID."'
							)
						WHERE
							productOpt2.productID = product.productID
							AND
							(".implode(" OR ",$productOptionName ).")
							AND
							productOpt2.productOptionStatus ".$updprodstatus."
					)
				) ";
		}
		$selectedProductID	= (isset($selectedProductID) && $selectedProductID >0) ? $selectedProductID : 0;
		/*if((isset($categoryID) && $categoryID!='')){ // || (isset($searchKeyword) && $searchKeyword!='')
                   
                    if($customGroupBy=='bodyStyle') {
                        $sql 	.= " GROUP BY product.$customGroupBy";
                    } elseif($customGroupBy=='optionSku' || $customGroupBy=='bmwCode' ) {
                        $sql 	.= " GROUP BY productOpt.$customGroupBy";
                    } else {
                         $sql 	.= " GROUP BY product.productID";
                    }
		} else {
                    if($customGroupBy=='bodyStyle') {
                        $sql 	.= " GROUP BY product.$customGroupBy";
                    } elseif($customGroupBy=='optionSku' || $customGroupBy=='bmwCode' ) {
                        $sql 	.= " GROUP BY productOpt.$customGroupBy";
                    }
                }*/
                if($frontEnd!='') {
                    $sql .= " ORDER BY ".$customOrderBy."";
                    
                    $sql .= " ) as result  ";                    
                    if($customGroupBy=='bodyStyle') {
                        $sql 	.= " GROUP BY result.productCategoryID,result.bodyStyle";
                     } elseif($customGroupBy=='optionSku' || $customGroupBy=='bmwCode' ) {
                         if($customGroupBy=='bmwCode') {
                             $sql 	.= " GROUP BY result.productShortDescription";
                         } else {
                             $sql 	.= " GROUP BY result.$customGroupBy"; 
                         }
                     } else {
                         
                         $sql 	.= " GROUP BY result.productID"; 
                     }
                    
                    
                } else {
                   $sql 	.= " GROUP BY product.productID";
                   
                }            
                if($selectedProductID!='' && $freeProducts!='') {
                    $sql.=" ORDER BY productFree.priority,".$customOrderBy.",productOptionQuantitySum DESC";
                            
                } else { 
                    
                    if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {
                        if(!empty($selectedProductID) && $selectedProductID>0) {
                             $sql.=" ORDER BY prodcat.productCategorySortOrder asc,
				FIELD(product.productID,".$selectedProductID.") DESC,				
				".$customOrderBy.",
				productOptionQuantitySum DESC";
                        } else {
                            
                            if($frontEnd!='') {                               
                                $sql.=" ORDER BY result.productCategorySortOrder,result.offerPrice ASC ";
                            } else {
                                $sql.=" ORDER BY 								
				".$customOrderBy.",
				productOptionQuantitySum DESC";
                            }
                             
                        }
                        
                       
                    } else {
                        if(!empty($selectedProductID) && $selectedProductID>0) {
                            $sql.="
                            ORDER BY 
                                    FIELD(product.productID,".$selectedProductID.") DESC,				
                                    ".$customOrderBy.",
                                    productOptionQuantitySum DESC";
                        } else {
                            if($frontEnd!='') {
                                $sql.=" ORDER BY result.productCategorySortOrder,result.offerPrice ASC ";                                                                
                            } else {
                                $sql.=" ORDER BY ".$customOrderBy.",productOptionQuantitySum DESC";
                            }
                             
                        }
                    }
                    
                }
                //if($frontEnd=='') {
                    if (isset($limitTo) && $limitTo >= '0')
                    {
                            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . " ";
                    }
               // }
                              
               
		/* if true for brand category wise listing */
		if(isset($returnSql) && $returnSql){
			return $sql; 
		}else{
			return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
		}
	}
	/* END : listActiveProducts */
	public function listGoogleProductCategory($productID='') {
            $sql = "SELECT googleCat.google_category_name FROM ".DB_PREFIX."product_to_category AS productToCat
                    LEFT JOIN ".DB_PREFIX."product_categories AS proCat ON ( proCat.productCategoryID = productToCat.productCategoryID ) 
                    LEFT JOIN ".DB_PREFIX."google_categories AS googleCat ON ( proCat.google_category_id = googleCat.google_category_id ) 
                    WHERE productToCat.productID =  '".$productID."' ORDER BY proCat.productCategoryID DESC LIMIT 0 , 1 ";
            return $this->ObjDatabase->readValue($sql);
        }
	public function listCategoryWiseProducts($args){
		$brandCategories	= 	$args['brandCategories'];
		$sqlArray 			= 	array();
		foreach($brandCategories as $categoryID){
			$args['categoryID'] = 	$categoryID;
			$sqlArray[] 		=  	$this->listActiveProducts($args);
		}
		$sql = '('. implode(') UNION (' ,$sqlArray).")";
		$resProducts 	= $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
		$listProducts 	= array();
		foreach($resProducts as $key=>$product){
			$listProducts[$product['categoryIDs']][$key] = $product;
		}
		return $listProducts;
	}
	
	/* function to get the complete category ID of brands */
	public function listCategoriesUnderBrands($params)
	{
		extract($params);
		$now						=	date('Y-m-d');
		$hide_outofstock_products 	= 	false;
			
		$sql = "
			SELECT  
				DISTINCT(productToCat.productCategoryID)
			FROM  
					".DB_PREFIX."products AS product 
				INNER JOIN
					".DB_PREFIX."business AS bus
						ON	(
							product.businessID = bus.businessID 
							AND 
							bus.businessStatus = 'Active'
						)
				INNER JOIN  
					".DB_PREFIX."product_to_category AS productToCat  
						ON (
							product.productID = productToCat.productID
						)
			WHERE product.brandID IN (".$brandID.")
				
			";
			if($showAllProducts=='Yes') {
				$sql  .= " and product.productStatus IN ('Active','Inactive') ";
			} else {
				$sql  .= " and product.productStatus = 'Active' ";				
			}
		if($hide_outofstock_products){
			$sql.="
				AND
					IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									sum(productOptionQuantity)
								FROM 
									" . DB_PREFIX . "product_options
								WHERE 
									productID=product.productID 
									AND 
									productOptionStatus='Active'
							),
							(
								SELECT 
									sum(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination as proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status='A'
							)
						)
					) > 0
			";
		}
		//echo $sql;exit;
		return $this->ObjDatabase->getFieldsSlave($sql);				
	}
	
	/* Function to get the complete brandID of particular category */
	public function listActiveBrandsUnderCategory($params)
	{
		extract($params);
		$currentLanguageID = (isset($languageIDs['Content']) && $languageIDs['Content']>0)? $languageIDs['Content'] : $languageIDs['Current'];
		$now						=	date('Y-m-d');
		$hide_outofstock_products 	= 	false;
		if(isset($categoryIDs) && $categoryIDs!='')	{
			$sql = "
				SELECT 
					brand.brandID,
					brandDet.brandName,
					brand.brandKey
				FROM
					(
					SELECT  
						DISTINCT(product.brandID)
					FROM  
							".DB_PREFIX."products AS product 
						INNER JOIN
							".DB_PREFIX."business AS bus
								ON	(
									product.businessID = bus.businessID 
									AND 
									bus.businessStatus = 'Active'
								)
						INNER JOIN  
							".DB_PREFIX."product_to_category AS productToCat  
								ON (
									product.productID = productToCat.productID
								)
					WHERE 
						product.productStatus = 'Active'  
						AND 
						( 
							product.productStartTime <= '".$now."' 
							AND 
							( 
								product.productEndTime = '0000-00-00' 
								OR 
								product.productEndTime >=    '".$now."'
							) 
						)
						AND 
						productToCat.productCategoryID  IN  (".$categoryIDs.")
					";
			if($hide_outofstock_products){
				$sql.="
						AND
							IF(product.InventoryTrackLevel ='No',
								1,
								IF(product.InventoryTrackLevel ='Product',
									(
										SELECT 
											sum(productOptionQuantity)
										FROM 
											" . DB_PREFIX . "product_options
										WHERE 
											productID=product.productID 
											AND 
											productOptionStatus='Active'
									),
									(
										SELECT 
											sum(combinationQty)
										FROM 
											" . DB_PREFIX . "product_combination as proCom1
										WHERE 
											proCom1.productID=product.productID									
											AND 
											proCom1.status='A'
									)
								)
							) > 0
				";
			}
			
			$sql.="	) AS products
					INNER JOIN
						".DB_PREFIX."brands AS brand
							ON	(
								brand.brandID =products.brandID
								AND
								brand.status='A'
							)
					INNER JOIN
						".DB_PREFIX."brand_details AS brandDet
							ON	(
								brand.brandID =brandDet.brandID
								AND
								brandDet.languageID='".$currentLanguageID."'
							)
			";
			return $this->ObjDatabase->readValuesSlave($sql);
		}
		else{
			return array();
		}		
	}
	
	
	/* 
		@function to list the recently viewed products based on cookie value , the browser stores the id of recently viewd products	 
	*/
	public function recentProductDetails($params=array()){
		extract($params);
		$params=array(
			'languageIDs'	=>	$languageIDs,
			'customOrderBy'	=>	'recentViewed',
			'includeProductIDs'=> 	explode(",",$recentViewedItems),
			'userGroupID'	=>	$userGroupID
		);
		return  $this->listActiveProducts($params);
	}
	/* END : recentProductDetails */
	
	public function getProductsMaxCount()
	{
		return $this->ObjDatabase->getRecordMaxCount();
	}
	
	public function fetchProductOptionsInfo($params=array())
	{
		extract($params);
		$inventoryTrackLevel=isset($inventoryTrackLevel)?$inventoryTrackLevel:'';
		$showQuantityAvailable=isset($showQuantityAvailable)?$showQuantityAvailable:'';
		$productQuantityUnlimited = ($inventoryTrackLevel =='No') ? 1 : 0;
		$showAllProducts = !empty($showAllProducts)?$showAllProducts:'';
		if($showAllProducts=='Yes') {
			$updprodcombstatus = " IN ('A','I') ";
			$updprodstatus = " IN ('Active','Inactive') ";
		} else {
			$updprodcombstatus = " = 'A' ";		
			$updprodstatus = " = 'Active' ";			
		}
                //((productOpt.productOptionRegularPrice*(100-product.dealDiscount))/100) AS dealPrice,
                
                $sqlProdOptBusiness         = " AND (IF(product.InventoryTrackLevel='No',
                                                        prodOptBus.businessOptionDefault='Yes',
                                                        prodOptBus.productOptionBusinessID= (SELECT productOptionBusinessID FROM " . DB_PREFIX . "products_options_business
                                                              WHERE productOptionID = productOpt.productOptionID
                                                              AND productOptionQuantity>0
                                                              ORDER BY FIELD (businessOptionDefault,'Yes','No')
                                                              LIMIT 1)))";
                
		$sql	= "
			SELECT 
				productOpt.productOptionID,
				prodOptBus.productOptionRegularPrice,
				prodOptBus.productOptionProductPrice,
                                product.isProductDeal,
                                prodOptBus.businessID as optBusinessID,
                                IF(prodOptBus.dealPrice>0,prodOptBus.dealPrice,ROUND((prodOptBus.productOptionProductPrice)-(prodOptBus.productOptionProductPrice*(product.dealDiscount/100)))) AS dealPrice,
                                productOpt.productOptionCashBack,
                                LEAST(
					COALESCE(
						varOffPri.offerPrice,
						prodOptBus.productOptionProductPrice
					),
					COALESCE(
						varGroPri.specialPrice,
						prodOptBus.productOptionProductPrice
					) 
				)
				AS offerPrice,
				ROUND(
					(
						(
							(
							prodOptBus.productOptionRegularPrice-
								LEAST(
									COALESCE(
										varOffPri.offerPrice,
										prodOptBus.productOptionProductPrice
									),
									COALESCE(
										varGroPri.specialPrice,
										prodOptBus.productOptionProductPrice
									) 
								)
							)*100
						)/prodOptBus.productOptionRegularPrice
					),2
				) AS productOptionOfferDiscount,
				'".$inventoryTrackLevel."' as InventoryTrackLevel,
				'".$showQuantityAvailable."' as showQuantityAvailable, ";
				
		if($inventoryTrackLevel=='Options'){
			$sql.="	
				SUM(CAST(IFNULL(proComBus.businessCombQuantity,0) AS UNSIGNED)) as productOptionQuantity,
				".$productQuantityUnlimited." as productQuantityUnlimited,";
		}
		else{
			$sql.="	
				prodOptBus.productOptionQuantity,
				".$productQuantityUnlimited." as productQuantityUnlimited,";
		}
		$sql.= " 
				productOptDet.productOptionName,
				(prodOptBus.productOptionRegularPrice-prodOptBus.productOptionProductPrice) AS productOptionSavePrice,
				ROUND(
					(((prodOptBus.productOptionRegularPrice-prodOptBus.productOptionProductPrice)*100)/prodOptBus.productOptionRegularPrice)) 
				AS productOptionDiscount,
				productOpt.soldQuantity AS productOrderItemsCount,";
		if($inventoryTrackLevel=='Options'){
			$sql.=" 
				SUM(CAST(IFNULL(proComBus.businessCombQuantity,0) AS UNSIGNED)) AS productOptionQuantityQuantitySum";
		}
		else if($inventoryTrackLevel=='Product'){
			$sql.=" 
				SUM(CAST(prodOptBus.productOptionQuantity AS UNSIGNED)) AS productOptionQuantityQuantitySum";
		}
		else{
			$sql.=" 
				1 AS productOptionQuantityQuantitySum";
		}
		$sql.= "	
			FROM 
					" . DB_PREFIX . "product_options AS productOpt 
                                INNER JOIN 
								" . DB_PREFIX . "products AS product 
									ON (
										product.productID = productOpt.productID
									) 
				LEFT JOIN 
					" . DB_PREFIX . "product_option_details AS productOptDet 
						ON (
							productOptDet.productOptionID=productOpt.productOptionID  
							AND 
							productOptDet.languageID = '" . $languageIDs['Current'] . "'
						) 
				LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
                                                        $sqlProdOptBusiness
						)
				LEFT JOIN
					".DB_PREFIX."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".$userGroupID."'
                                                        AND varOffPri.businessID = prodOptBus.businessID
						)	
				LEFT JOIN
					".DB_PREFIX."variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='".$userGroupID."'
                                                        AND varGroPri.businessID = prodOptBus.businessID
						)
							
							";
		if($inventoryTrackLevel=='Options'){
			$sql.= " 
				LEFT JOIN 
					" . DB_PREFIX . "product_combination AS proCom 
						ON (
							proCom.productID='" . $productID . "'   
							AND 
							proCom.status ".$updprodcombstatus."
						) 
				LEFT JOIN 
					" . DB_PREFIX . "product_combination_business AS proComBus 
						ON (
							proComBus.combinationID= proCom.combinationID   
							AND 
							proComBus.businessID = product.businessID
						)
				LEFT JOIN 
					" . DB_PREFIX . "product_combination AS proCom1 
						ON (
							proCom1.productID='" . $productID . "'  
							AND 
							proCom1.status ".$updprodcombstatus." AND proCom1.combinationQty=''
						)";
			}			
		$sql.=" 
			WHERE 
				productOpt.productOptionStatus ".$updprodstatus."
				AND 
				productOpt.productID='" . $productID . "' 
			GROUP BY 
				productOpt.productOptionID 
			ORDER BY ";
			if($productOptionID >0)
			{
				//$sql .=" FIELD(productOpt.productOptionID,".$productOptionID.") DESC , ";
			}
			$sql .=" productOpt.productOptionDefault DESC ,
				productOptionQuantityQuantitySum DESC , productOpt.productOptionID ASC ";
		return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
	}
	public function fetchProductOptionsQtyInfo($productID,$InventoryTrackLevel='No')
	{
		if($InventoryTrackLevel=='No')
		{
			$sql="SELECT 
					1 AS productQuantityUnlimited,
					SUM(productOptionQuantity) AS productOptionQuantity,
					(
						SELECT
							SUM(soldQuantity)   
						FROM 
							".DB_PREFIX."product_options
						WHERE	
							productID = '" . $productID . "'
					) AS productOrdersCount
				FROM 
					" . DB_PREFIX . "product_options AS productOpt		
				WHERE 
					productID=" . $productID . " 
					AND 
					productOptionStatus='Active'";
		}
		else if($InventoryTrackLevel=='Options')
		{
			$sql="SELECT 
					0 AS productQuantityUnlimited,
					SUM(combinationQty) AS productOptionQuantity,
					(
						SELECT
							SUM(soldQuantity)   
						FROM 
							".DB_PREFIX."product_options
						WHERE	
							productID = '" . $productID . "'
					) AS productOrdersCount
				FROM 
					" . DB_PREFIX . "product_options as productOpt1
				LEFT JOIN 
					" . DB_PREFIX . "product_combination as proCom1
					ON(proCom1.productID='" . $productID . "')
				WHERE 
					productOpt1.productID='" . $productID . "'								
					AND 
					proCom1.status='A'";
		}
		else
		{	
			$sql="SELECT 
					0 AS productQuantityUnlimited,
					SUM(CAST(productOptionQuantity AS UNSIGNED)) AS productOptionQuantity,
					(
						SELECT
							SUM(soldQuantity)   
						FROM 
							".DB_PREFIX."product_options
						WHERE	
							productID = '" . $productID . "'
					) AS productOrdersCount
				FROM 
					" . DB_PREFIX . "product_options AS productOpt		
				WHERE 
					productID=" . $productID . " 
					AND 
					productOptionStatus='Active'";
		}
		return $this->ObjDatabase->readValueSlave($sql, MYSQLI_ASSOC);
	}
	public function fetchProductDetails($languageIDs,$productID)
	{
		$sql	= "
			SELECT 
				product.productVideo,
				product.productShowVideo,
				productDet.productFinePrint,
				productDet.productHighlights,
				productDet.productDescription,
                                productDet.productShortDescription,
				productDet.productMetaTitle,
				productDet.productMetaDescription,
				productDet.productMetaKeywords,
				product.productReturnable,
				product.productReturnPeriod,productDispatchTime
			FROM 
				" . DB_PREFIX . "products 			AS product 
			LEFT JOIN 
				" . DB_PREFIX . "product_details 	AS productDet 
					ON (
						product.productID = productDet.productID 
						AND 
						productDet.languageID=".$languageIDs['Current']."
					)
			WHERE
				product.productID='".$productID."' ";
		return $this->ObjDatabase->readValueSlave($sql, MYSQLI_ASSOC);
	}
	public function fetchBusinessDetails($languageIDs,$businessID)
	{		
		$sql	= "
			SELECT 
				bus.businessID,
				bus.businessLogo,
				bus.businessWebsite,
				busDet.businessName,
				busDet.businessProfile,
				busLoc.locLatitude,
				busLoc.locLongitude,
				busLoc.locZip,
				busLocDet.locAddress1,
				busLocDet.locAddress2,
				busLocDet.locCity,
				IF (
					(busLoc.locStateID <> 0),
					staDet.stateName,
					busLocDet.locProvince
				) AS stateName,
				cntryDet.countryName
			FROM
				" . DB_PREFIX . "business AS bus
			LEFT JOIN 
				" . DB_PREFIX . "business_details AS busDet 
					ON 	(
						bus.businessID=busDet.businessID 
						AND 
						busDet.languageID='" . $languageIDs['Current'] . "'
					)
			LEFT JOIN 
				" . DB_PREFIX . "business_locations busLoc 
					ON 	(
						bus.businessID=busLoc.businessID 
						AND 
						busLoc.isLocationBasic='Yes'
					)
			LEFT JOIN 
				" . DB_PREFIX . "business_location_details busLocDet 
					ON	( 
						busLoc.businessLocID=busLocDet.businessLocID 
						AND  
						busLocDet.languageID = '" . $languageIDs['Current'] . "' 
					)
			LEFT JOIN 
				" . DB_PREFIX . "state_details AS staDet 
					ON 	(
						busLoc.locStateID=staDet.stateID 
						AND staDet.languageID=" . $languageIDs['Current'] . "
					)
			LEFT JOIN 
				" . DB_PREFIX . "country_details AS cntryDet 
					ON 	(
						busLoc.locCountryID=cntryDet.countryID 
						AND 
						cntryDet.languageID=" . $languageIDs['Current'] . "
					) 
			WHERE
				bus.businessID='".$businessID."' ";
		return $this->ObjDatabase->readValueSlave($sql, MYSQLI_ASSOC);
	}
	public function fetchProductReviews($productID)
	{
		 $sql	= "
				SELECT 
					COUNT(ProductReviewID) AS productReviewsCount,
					SUM(productRating) ratingTotal
				FROM 
					" . DB_PREFIX . "product_reviews AS productRev
				LEFT JOIN
					". DB_PREFIX."users As usr
					ON usr.userID	=	productRev.userID
				WHERE 
					productID=" . $productID . " 
					AND usr.userStatus = 'Active' 
					AND  productRev.reviewStatus =	'Active'
					GROUP BY productRev.ProductID
					";
			$result								= $this->ObjDatabase->readValueSlave($sql);
			$resSelectProduct['productReviewsCount']	= $result['productReviewsCount'];
			$resSelectProduct['ratingTotal']		= $result['ratingTotal'];
			return $resSelectProduct;
	}
	public function fetchUserFollowBusinessStatus($userID,$businessID)
	{	
		$sql	= "
			SELECT 
				COUNT(*) AS userBusinesssFollowed
			FROM 
				" . DB_PREFIX . "users_follow_businesss 
			WHERE 
				businessID='".$businessID."' 
				AND 
				userID='" . $userID . "' ";
		$result	= $this->ObjDatabase->readValueSlave($sql, MYSQLI_ASSOC);
		
		return $result['userBusinesssFollowed'];
	}
	public function fetchProductImages($productID,$productOptionID=0)
	{
		
		$sql	= "
				SELECT
					productMediaID,
					productMedia as productImage
				FROM 
					" . DB_PREFIX . "product_media
				WHERE 
					(productMediaStatus='A' OR productMediaStatus IS NULL)
					AND 
					productID=" . $productID . "
					AND 
					productObjectType='DO'";
		if($productOptionID >0)
		{
			$sql.=	" 
					AND 
					productObjectID=".(int)$productOptionID;
		}
		$sql.=	" 
				ORDER BY 
					productMediaPriority";
		return $this->ObjDatabase->readValuesSlave($sql);
	}
	public function fetchProductOrderInfo($productID)
	{	
		$sql	= "
				SELECT
					productOpt.soldQuantity AS productOrdersCount
				FROM
					" . DB_PREFIX . "order_masters 	AS ordMas
				LEFT JOIN 
					" . DB_PREFIX . "order_items 	AS ordIte
					ON
					(
						ordMas.masterOrderID=ordIte.masterOrderID
					)
				LEFT JOIN
					".DB_PREFIX."product_options AS productOpt 
					ON 
					(
						ordIte.productID = productOpt.productID
						AND
						productOpt.productOptionStatus = 'Active' 
						AND 
						productOpt.productOptionDefault = 'Yes'
					)
				WHERE
					ordMas.orderStatus IN ('Process','Completed')
					AND
					ordMas.orderRemoved='No'
					AND
					ordMas.orderNumber>0
					AND
					ordIte.productID=" . $productID;
		$result	= $this->ObjDatabase->readValueSlave($sql);
		
		return ($result['productOrdersCount'] < 1) ? 0 : $result['productOrdersCount'];
	}
	
	/* START : function to get the related Products list */
	function listRelatedProducts($params=array()){
		extract($params);
		$defaultLanguageID		= $languageIDs['Default'];
		$currentLanguageID		= $languageIDs['Current'];
		switch($type){
			case 'related':
				$query="
					INNER JOIN
				".DB_PREFIX."product_link_relation  AS proLinRel
					ON 	(
						proLinRel.relatedProductID = product.productID
						AND
						proLinRel.productID ='".$productID."'
					)
				";
				$orderBy ="proLinRel.priority ASC";
				break;
			case 'upsell':
				$query="
					INNER JOIN
				".DB_PREFIX."product_link_up_sell  AS proLinUpSel
					ON 	(
						proLinUpSel.upSellProductID = product.productID
						AND
						proLinUpSel.productID ='".$productID."'
					)
				";
				$orderBy ="proLinUpSel.priority ASC";
				break;
			case 'crossel':
				$query="
					INNER JOIN
				".DB_PREFIX."product_link_cross_sell  AS proLinCroSel
					ON 	(
						proLinCroSel.crossSellProductID = product.productID
						AND
						proLinCroSel.productID ='".$productID."'
					)
				";
				$orderBy ="proLinCroSel.priority ASC";
				break;
			
		}
		
		$sql	= "
			SELECT 
				product.productID,
				product.productUrl,					
				product.productImage,
				productDet.productTitle,					
				prodOptBus.productOptionProductPrice,
				product.productDefaultCurrency,
                product.sellAsBatch,
                product.batchType,
                product.qtyPerBatch, 
				LEAST(
					COALESCE(
						varOffPri.offerPrice,
						prodOptBus.productOptionProductPrice
					),
					COALESCE(
						varGroPri.specialPrice,
						prodOptBus.productOptionProductPrice
					) 
				)
				AS offerPrice, 
				prodOptBus.productOptionRegularPrice
			FROM 
				".DB_PREFIX."products AS product  
				".$query."
			INNER JOIN 
				".DB_PREFIX."product_details	AS productDet 
					ON (
						product.productID = productDet.productID 
						AND 
						productDet.languageID = ".$currentLanguageID."
					)
			INNER JOIN 
				".DB_PREFIX."business AS bus 
					ON 	(
						product.businessID = bus.businessID 
						AND 
						bus.businessStatus = 'Active'						
				)
			INNER JOIN 
				".DB_PREFIX."product_options AS productOpt 
					ON (
						product.productID = productOpt.productID
						AND 
						productOpt.productOptionDefault = 'Yes'
						AND
						productOpt.productOptionStatus = 'Active' 
					) 
			LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
			LEFT JOIN
					".DB_PREFIX."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".(int)$userGroupID."'
                                                        AND varOffPri.businessID = prodOptBus.businessID
						)
			LEFT JOIN
				".DB_PREFIX."variant_group_price AS varGroPri
					ON	(
						varGroPri.productOptionID = productOpt.productOptionID
						AND
						varGroPri.userGroupID='".(int)$userGroupID."'
                                                AND varGroPri.businessID = prodOptBus.businessID
					)
			WHERE 
				product.productStatus='Active'
				AND 
				(
					product.productStartTime <= '" . date('Y-m-d') . "' 
					AND
					(
						product.productEndTime = '0000-00-00'
						OR
						product.productEndTime >=	'" . date('Y-m-d') . "'
					)
				)
			ORDER BY
				".$orderBy."
			";
		if (!empty($productLimit) && $productLimit > 0)
		{
			$sql .= " LIMIT  0," . $productLimit . " ";
		}
		return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
	}
	/* END : function to get the related Products list */
	
	
	
	//GET ACTIVE Products
	public function getProductsForRelated($paramArray) 
	{
		
		$languageID		=	$paramArray['currentLanguageID'];
		$relIDs			=	isset($paramArray['relIDs'])?$paramArray['relIDs']:'';
		$productID 		= 	$paramArray['productID'];
		$searchKeyword	=	(isset($paramArray['searchKeyword']))?$paramArray['searchKeyword']:'';
		$searchItems	=	(isset($paramArray['searchItems']))?$paramArray['searchItems']:'';		
		$list			=	(isset($paramArray['selectOpt']))?$paramArray['selectOpt']:'';
		$productStatus 	=	(isset($paramArray['productStatus']))?$paramArray['productStatus']:'';
		$searchSetID= (isset($paramArray['searchSetID']))?$paramArray['searchSetID']:'';
		$businessID=(isset($paramArray['businessID']))?$paramArray['businessID']:'';		
		$sql =	"
				SELECT 
					productdet.productTitle,
					productdet.productID,
					product.productStatus";
		$sql.=	"
				FROM 
						".DB_PREFIX."product_details AS productdet 
					INNER JOIN 
						".DB_PREFIX."products as product 
							ON productdet.productID=product.productID";
		if($list=='L'){
		$sql.=	"	LEFT JOIN 
						".DB_PREFIX."product_to_category AS productToCat 
							ON 	(
								productToCat.productID=product.productID
							)
					LEFT JOIN 
						" . DB_PREFIX . "product_options AS productOptFull
							ON 	(	
								productOptFull.productID = product.productID
							)";
		}
		$sql.=	" 
				WHERE 
					productdet.languageID='".$languageID."'";
		if($productStatus){
		$sql	.= " 
					AND 
					product.productStatus='".$productStatus."'";		
		}		
		if($relIDs!=''){
			$relIDs	="'".implode("','",$relIDs)."'";
			if($list!='L')
			$sql	.= " 
					AND 
					product.productID IN(".$relIDs.")";
		}
		
		if($productID){
			$sql	.= " 
					AND 
					product.productID!='" . $productID . "'";
		}
		if(empty($relIDs)){
			$relIDs	=	"'".$relIDs."'";
		}
		if($list=='L'){				
			if($searchKeyword){
				$sql.= " 
					AND  (
						productOptFull.optionSKU LIKE '%".addslashes($searchKeyword)."%' 
						OR 
						productdet.productTitle  LIKE '%".addslashes($searchKeyword)."%'
					) ";
			}
			if($searchItems!='') {

				$sql .= " 
					AND  
					productToCat.productCategoryID IN(".$searchItems.") ";
			}
			if($searchSetID!='') {

				$sql .= " 
					AND  
					product.attributeSetID='".$searchSetID."'";
			}
			if($businessID!='') {

				$sql .= " 
					AND  
					product.businessID='".$businessID."'";
			}			
		}
		$sql .= " 	AND  
					product.productStatus!='Deleted' 
				GROUP BY 
					product.productID";		

		$sql.= " 
				ORDER BY
					field (product.productID,".$relIDs."), product.productPriority ASC";					
		return	$result	=	$this->ObjDatabase->readValuesSlave($sql);
	}
	//END OF FUNCTION

//FETCHING ALL DEALS
	public function listProducts($params)
	{
		extract($params);
		
		$defaultLanguageID	= $languageIDs['Default'];
		$currentLanguageID	= $languageIDs['Current'];
                $searchFrom             = isset($searchFrom) ? $searchFrom : "";
                $getCnt                 = (isset($getCnt) && !empty($getCnt)) ? $getCnt : "";
                $businessID             = isset($businessID) ? $businessID : 0;
		$sortBy = isset($sortBy)?$sortBy:'';
		$sortCase = $sortBy;
		$bindCombinationTable = 0;
		switch ($sortBy)
		{
			case "sendsoon":
				$sortBy = "product.productEndTime";
				break;
			case "screated":
				$sortBy = "product.productCreatedDate";
				break;
			case "stitle":
				$sortBy = "productDet.productTitle";
				break;
			case "sbusiness":
				$sortBy = "busDet.businessName";
				break;
			case "sprice":
				$sortBy		= "productOptFull.productOptionProductPrice";
				break;
			case "sdiscount":
				$sortBy		= "productOptionDiscount";
				break;
			case "sfeatured":
				$sortBy		= "FIELD(product.featuredStatus,'Yes','')";
				break;
			case "spopular":
				$sortBy		= "productOrdersCount";
			default:
				if(isset($orderBy) && $orderBy!=''){
					$sortBy = "product.productID ";
				}
				else{
					$sortBy = "product.productID DESC";
				}
				break;
		}
		$sql 	= "
				SELECT ";
		if (isset($limitTo))
		{
			$sql .= "
					SQL_CALC_FOUND_ROWS ";
		}
		$sql 	.= "		
					product.productID,					
					veh_product.vehicleImage as productImage,					
					product.productStatus,					
                                        SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) as variantCode ,
                                        brandDet.brandName,productOptAll.modelYear,";
                if($getCnt=='Yes') {
                        $sql 	.= " productOptBusiness.monthlyInstallment,productOptAll.optionSKU,SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) as variantCode,productOptAll.modelYear, ";
                        $sql 	.= " (Select count(variantCode) FROM ".DB_PREFIX."product_lease_price where variantCode=productOptAll.optionSKU and mileage!='') as leasePriceCnt,";                 
                        $sql 	.= " (Select count(optionProfileID) FROM ".DB_PREFIX."product_option_profiles where variantCode=SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) and year=productOptAll.modelYear and profileCode!='') as optionProfileCnt,";                 
                } else {
                    $sql 	.= "    product.multiOptionsAvailable,					
					product.businessID,					
                                        product.buyboxStatus,
                                        product.isProductDeal,
					product.productCreatedDate,
					product.InventoryTrackLevel,
					product.productDefaultCurrency,
                                        productOptBusiness.productOptionRegularPrice,                                        
					IF((product.productEndTime = '0000-00-00'OR DATE_FORMAT(product.productEndTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-%d')), 'ongoing', 'over') as productLife,
					productDet.productTitle,
                                        product.enableBuybox,product.bodyStyle,SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) as variantCode ,
                                        brandDet.brandName,productOptAll.modelYear,";
                }
                                        
                if($searchFrom=='admin') {
                    $sql.=" (select GROUP_CONCAT( catDet.productCategoryName SEPARATOR ',' )  from ".DB_PREFIX."product_to_category prodcat inner join  ".DB_PREFIX."product_categories cat on (prodcat.productCategoryID=cat.productCategoryID) inner join 
                        
".DB_PREFIX."product_category_details catDet on (catDet.productCategoryID=cat.productCategoryID and catDet.languageID='" . $currentLanguageID . "') where prodcat.productID=product.productID) AS catName, ";
                }
		if($sortCase == "spopular")
		{
			$sql.="
					(
						SELECT 
							SUM(soldQuantity)
						FROM
							".DB_PREFIX."product_options
						WHERE 
							productID = product.productID
					) AS productOrdersCount,";
		}
                        
		if($sortCase == "sdiscount")
		{
			$sql.="
					ROUND((((productOptFull.productOptionRegularPrice-productOptFull.productOptionProductPrice)*100)
					/productOptFull.productOptionRegularPrice),2) 
					AS productOptionDiscount,";
		}
		$sql.="	
					busDet.businessName
				FROM 
					" . DB_PREFIX . "products AS product 
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
					ON product.productID=productDet.productID 
					AND productDet.languageID='" . $currentLanguageID . "'";
                
		//if (!empty($brandKey) && $brandKey!=''){
			$sql.="LEFT JOIN
					" . DB_PREFIX . "brands AS brands
						ON	(
							brands.brandID = product.brandID
						)
				LEFT JOIN
					" . DB_PREFIX . "brand_details AS brandDet
						ON	(
							product.brandID = brandDet.brandID
						)
			";
		//}
		
                if((!empty($businessKey) && $businessKey!='') || (isset($businessIDs) && !empty($businessIDs))){
                   
		$sql .= "					
				LEFT JOIN 
                                            " . DB_PREFIX . "product_options AS productOptAll
                                            ON  productOptAll.productID = product.productID
                                            AND productOptAll.productOptionStatus='Active'
                                    LEFT JOIN 
                                            " . DB_PREFIX . "products_options_business AS productOptBusiness
                                            ON  productOptBusiness.productOptionID = productOptAll.productOptionID
                                            AND productOptBusiness.busProductStatus='Active'";
                    
                    $sql .= "					
				LEFT JOIN 
					" . DB_PREFIX . "business_details AS busDet 
					ON busDet.businessID = productOptBusiness.businessID 
					AND busDet.languageID='" . $currentLanguageID . "'";
                }else{
                    
                    if($searchFrom=="business" || $businessID>0)
                    {
                        
                        $sql .= "
                                LEFT JOIN 
                                            " . DB_PREFIX . "product_options AS productOptAll
                                            ON  productOptAll.productID = product.productID
                                            AND productOptAll.productOptionStatus='Active'
                                LEFT JOIN 
                                            " . DB_PREFIX . "products_options_business AS productOptBusiness
                                            ON  productOptBusiness.productOptionID = productOptAll.productOptionID
                                            AND productOptBusiness.busProductStatus!='Delete'
				LEFT JOIN 
					" . DB_PREFIX . "business_details AS busDet 
					ON busDet.businessID = productOptBusiness.businessID 
					AND busDet.languageID='" . $currentLanguageID . "'";		
                    }else{
                       
                        $sql .= "	
                             LEFT JOIN 
                                            " . DB_PREFIX . "product_options AS productOptAll
                                            ON  productOptAll.productID = product.productID
                                            AND productOptAll.productOptionStatus='Active'
                                             LEFT JOIN 
                                            " . DB_PREFIX . "products_options_business AS productOptBusiness
                                            ON  productOptBusiness.productOptionID = productOptAll.productOptionID
                                            AND productOptBusiness.busProductStatus!='Delete'
				LEFT JOIN 
					" . DB_PREFIX . "business_details AS busDet 
					ON busDet.businessID = product.businessID 
					AND busDet.languageID='" . $currentLanguageID . "'";		
                    }
                }
                
                $sql.=              " LEFT JOIN " . DB_PREFIX . "vehicle_images AS veh_product
                                            ON (veh_product.variantCode = SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) and veh_product.imageType='png')";
//		if($sortCase == "sprice" || $sortCase == "sdiscount")
//		{
//			$sql .= "
//				LEFT JOIN 
//					" . DB_PREFIX . "product_options AS productOpt 
//					ON productOpt.productID = product.productID 
//					";
//                        if($searchFrom != 'business' && $businessID<=0)
//                        {
//                            $sql .= " AND productOpt.productOptionDefault='Yes' ";
//                        }
//		}
                $sqlWithBusJoin = "";
                $sqlCombWithBusJoin = "";
                if($searchFrom=="business" && $businessID>0){
                    $sqlWithBusJoin = "AND productOptFull.businessID = '".$businessID."'";
                    $sqlCombWithBusJoin = "AND proComBus.businessID = '".$businessID."'";
                }
		if(
			(isset($searchPriceFrom) && $searchPriceFrom >0) || 
			(isset($searchPriceUpto) && $searchPriceUpto>0 ) || 
			(isset($searchDiscountFrom) && $searchDiscountFrom>0)  || 
			(isset($searchDiscountTo) && $searchDiscountTo >0 ) || 
			(isset($searchInventory) && $searchInventory=='nostock') || 
			(!empty($searchQuantityBelow)) || 
			(isset($searchProductKeyword) && $searchProductKeyword != '') || 
                        ($sortCase == "sdiscount") || 
                        ($sortCase == "sprice")
		)
		{
			$sql.="		
				LEFT JOIN  ";
                        if($searchFrom != 'business' && $businessID<=0){
                            $sql.="	" . DB_PREFIX . "product_options AS productOptFull   
                                        ON  productOptFull.productID = product.productID
					AND productOptFull.productOptionStatus='Active'";
                            $optStatus   =  "productOptFull.productOptionStatus";
                            $optSKU      =  "productOptFull.optionSKU";
                        }
                        else{
                            $sql.="	" . DB_PREFIX . "products_options_business AS productOptFull   
                                        ON  productOptFull.productOptionID = productOptAll.productOptionID
                                        $sqlWithBusJoin
					AND productOptFull.busProductStatus!='Delete'";
                            $optStatus   =  "productOptFull.busProductStatus";
                            $optSKU      =  "productOptFull.businessOptionSKU";
                        }
		
                        
		}			
		if((isset($searchProductKeyword) && $searchProductKeyword != '')
			|| (isset($searchInventory) && $searchInventory=='nostock')
			|| (!empty($searchQuantityBelow))
		)
		{
			$sql.="
				LEFT JOIN
					".DB_PREFIX."product_combination AS proCom 
					ON proCom.productID=product.productID 
					AND proCom.status='A'
                                LEFT JOIN
					".DB_PREFIX."product_combination_business AS proComBus 
					ON proComBus.combinationID=proCom.combinationID
                                        $sqlCombWithBusJoin";
		}			
		$sql .= " 
				WHERE 1 ";	
		if((is_array($searchProductCategories) && count(array_filter($searchProductCategories))>0))
		{
			$sql 	.= " 
					AND product.productID IN
					(
						SELECT
							productID
						FROM
							".DB_PREFIX."product_to_category
						WHERE
							productCategoryID IN ('".implode("','", $searchProductCategories)."')
					)";
		}			
			
		if(isset($searchProductStatus) && $searchProductStatus!='')
		{
                        if($searchProductStatus=="Buybox"){
                            $sql .= "
					AND product.enableBuybox = 'Yes'";
                        }else{
                            $sql .= "
					AND product.productStatus IN ('" . $searchProductStatus . "') ";
                        }
		}
		if(!empty($searchCloneIds))
		{
			$sql .= "
					AND product.productID IN ('" . implode("','", $searchCloneIds) . "') ";
		 }		 
		if (isset($searchProductKeyword) && $searchProductKeyword != '')
		{
			$searchProductKeyword = $this->escape($searchProductKeyword);
			$sql .= " 	
					AND 
					(
						productDet.productTitle LIKE '%" . $searchProductKeyword . "%'
						OR $optSKU LIKE '%" . $searchProductKeyword . "%'
						OR proCom.combinationSKU LIKE '%" . $searchProductKeyword . "%'
					) ";		
		}
		if (isset($businessID) && $businessID>0)
		{
                    if($searchFrom=="business" || $businessID>0){
                        $sql .= " 
					AND productOptBusiness.businessID = '" . $businessID . "' ";
                    }else{
                        $sql .= " 
					AND product.businessID = '" . $businessID . "' ";
                    }
			
		}
		if (!empty($businessKey) && $businessKey!='')
		{
			if(!empty($businessID))
			$sql .= " 	
					AND busDet.businessName='".$this->escape($businessKey) . "'";   
			else
			$sql .= " 	
					AND busDet.businessName LIKE '%" . $this->escape($businessKey) . "%' ";
		}
		if (!empty($brandKey) && $brandKey!='')
		{
			$sql .= " 	
					AND brandDet.brandName LIKE '%" . $this->escape($brandKey) . "%' AND brands.status!='D' AND brandDet.languageID='" . $currentLanguageID . "'";
		}
		if (isset($searchAttrSet) && $searchAttrSet!='')
		{
			$sql .= " 	
					AND product.attributeSetID = '" . $searchAttrSet . "' ";
		}
		if (isset($brandID) && $brandID>0)
		{
			$sql .= " 	
					AND product.brandID = '" . $brandID . "' ";
		}else{
			$sql .= " 	
					AND product.brandID = '2' ";
		}
		if (isset($brandIDs) && !empty($brandIDs))
		{
			$sql .= " 	
					AND product.brandID IN ('" . implode("','",$brandIDs). "') ";
		}
		if (isset($businessIDs) && !empty($businessIDs))
		{
			$sql .= " 	
					AND productOptBusiness.businessID IN ('" . implode("','",$businessIDs). "') ";
		}
		
		if (isset($productDateBetween) && $productDateBetween!='')
		{
			$searchProductToDate	= $productDateBetween;
			$searchProductFromDate	= $productDateBetween;
		}
		if (isset($searchProductToDate) && $searchProductToDate!='')
		{
			$sql .= " 	
					AND DATE_FORMAT(product.productStartTime, '%Y-%m-%d') <= '" . $searchProductToDate . "' ";
		}
		if (isset($searchProductFromDate) && $searchProductFromDate!='')
		{
			$sql .= " 	
					AND 
					(
						product.productEndTime = '0000-00-00'
						OR DATE_FORMAT(product.productEndTime, '%Y-%m-%d') >=	'" . $searchProductFromDate . "'
					) ";
		}		
		if(isset($searchPriceFrom) && $searchPriceFrom >0 && $searchPriceUpto >0)
		{
			$sql .= " 	
					AND productOptFull.productOptionProductPrice 
					BETWEEN '".(float)$this->escape($searchPriceFrom)."' 
					AND '".(float)$this->escape($searchPriceUpto)."' ";
		}
		elseif(isset($searchPriceFrom) && $searchPriceFrom>0 && $searchPriceUpto=='')
		{
			$sql .= "
					AND productOptFull.productOptionProductPrice>=".(float)$this->escape($searchPriceFrom);	
		}
		elseif(empty($searchPriceFrom) && isset($searchPriceUpto) && $searchPriceUpto>0)
		{
			$sql .= "
					AND productOptFull.productOptionProductPrice<=".(float)$this->escape($searchPriceUpto);
		}
		if(isset($searchDiscountFrom) && $searchDiscountFrom >0 && $searchDiscountTo >0)
		{
			$sql .= "
					AND (((productOptFull.productOptionRegularPrice-productOptFull.productOptionProductPrice)*100)/productOptFull.productOptionRegularPrice) 
					BETWEEN '".(float)$this->escape($searchDiscountFrom)."' 
					AND '".(float)$this->escape($searchDiscountTo)."' ";
		}
		elseif(isset($searchDiscountFrom) && $searchDiscountFrom>0 && $searchDiscountTo=='')
		{
			$sql .= "
					AND (((productOptFull.productOptionRegularPrice-productOptFull.productOptionProductPrice)*100)/productOptFull.productOptionRegularPrice) >= '".(float)$this->escape($searchDiscountFrom)."'";	
		}
		elseif(empty($searchDiscountFrom) && isset($searchDiscountTo) && $searchDiscountTo>0)
		{
			$sql .= "
					AND (((productOptFull.productOptionRegularPrice-productOptFull.productOptionProductPrice)*100)/productOptFull.productOptionRegularPrice) <= '".(float)$this->escape($searchDiscountTo)."'";
		}		
		if (isset($searchInventory) && $searchInventory=='nostock')
		{
			$sql .= " 	
					AND IF
					(
						product.InventoryTrackLevel ='Product',
						productOptFull.productOptionQuantity<IF(product.productQuantityNotifyAt>0,
						product.productQuantityNotifyAt,0 AND $optStatus='Active'),
						proComBus.businessCombQuantity<IF(product.productQuantityNotifyAt>0,product.productQuantityNotifyAt,0)
					)
					AND product.InventoryTrackLevel!='No' ";		
		}
		if (!empty($searchQuantityBelow))
		{
			$sql .= " 	
					AND IF
					(
						product.InventoryTrackLevel ='Product',
						productOptFull.productOptionQuantity<".(float)$this->escape($searchQuantityBelow).",
						proComBus.businessCombQuantity<".(float)$this->escape($searchQuantityBelow)."
					)
					AND product.InventoryTrackLevel!='No' ";		
		}
		if (isset($searchDeal) && $searchDeal!='')
		{
			$sql .= " 
					AND product.isProductDeal = '" . $searchDeal . "' ";
		}
                if (isset($searchBuybox) && $searchBuybox!='')
		{
			$sql .= " 
					AND product.enableBuybox = '" . $searchBuybox . "' ";
		}
		if(isset($status) && $status!='')
		{
			if(is_array($status)) {
				$sql .= "
					AND product.productStatus IN ('" . implode("','", ($status)) . "') ";
			}
			else
			$sql .= "
					AND product.productStatus='".$status."'";	
		}
		else if(isset($searchInventory) && $searchInventory=='nostock')
		{
			$sql .= " 
					AND product.productStatus='Active'";
		}
		else
		{                        
			$sql .= " AND product.productStatus!='Deleted' AND product.productStatus!='Pending' AND product.productStatus!='Rejected' AND product.productStatus!='Cancelled' AND product.productStatus!='Buybox' AND product.productStatus!='Review' ";	
//                        if(isset($page) && $page=='business'){
//                           $sql .= "  AND product.productStatus!='Inactive'";
//                        }
                        if($searchProductStatus!='Buybox' && $searchFrom!="admin" && $searchFrom!="business") { 
                            $sql .= " AND product.productStatus!='Buybox' ";
                        }
		}
		if (!empty($missingDashtype) && $missingDashtype!='')
		{
			if($missingDashtype == 'images') {
				 $sql .= " AND productOptAll.optionSKU NOT IN (
								SELECT CONCAT(variantCode, '-', year)
								FROM " . DB_PREFIX . "cosy_image_params AS image 
								WHERE status!='New'
							) ";
			}
			else if($missingDashtype == 'profiles') {
				 $sql .= " AND productOptAll.optionSKU NOT IN (
								SELECT CONCAT(variantCode, '-', year)
								FROM " . DB_PREFIX . "product_option_profiles AS profiles 
								WHERE profileCode!=''
							) ";
			}
			else if($missingDashtype == 'finance') {
				 $sql .= " AND productOptAll.optionSKU NOT IN (
								SELECT variantCode
								FROM " . DB_PREFIX . "finance_rate
								WHERE financeRate!=''
							) ";
			}
			else if($missingDashtype == 'lease') {
				 $sql .= " AND productOptAll.optionSKU NOT IN (
								SELECT variantCode
								FROM " . DB_PREFIX . "product_lease_price 
								WHERE mileage!=''
							) ";
			}
		}
		if($sortBy=="product.productEndTime")
		$sql .= " 
					AND product.productEndTime!='0000-00-00'";
		$sql .= " 	
				GROUP BY 
					product.productID ";		
		
		if (isset($sortBy) && isset($orderBy))
		{
			$sql .= " 
				ORDER BY 
					" . $sortBy . " " . $orderBy;
		}
		if (isset($limitTo) && !empty($limitTo))
		{
			$sql .= " 
				LIMIT 
					" . $limitFrom . "," . $limitTo . " ";
		}
		
		//echo $sql;
		//print_r($this->ObjDatabase->readValuesSlave($sql)); exit;
	//echo '<pre>'.$sql;exit;
		return $this->ObjDatabase->readValuesSlave($sql);		
	}
        public function listProductsInReview($params)
	{
		extract($params);
		
		$defaultLanguageID	= $languageIDs['Default'];
		$currentLanguageID	= $languageIDs['Current'];
                $searchFrom             = isset($searchFrom) ? $searchFrom : "";
                $getCnt                 = (isset($getCnt) && !empty($getCnt)) ? $getCnt : "";
                $businessID             = isset($businessID) ? $businessID : 0;
		$sortBy = isset($sortBy)?$sortBy:'';
		$sortCase = $sortBy;
		$bindCombinationTable = 0;
		$sortBy = "product.productID ";
		$sql 	= " SELECT ";
		if (isset($limitTo))
		{
			$sql .= " SQL_CALC_FOUND_ROWS ";
		}
		$sql 	.= " product.productID,	SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) as variantCode ,productOptAll.modelYear,";                
                $sql 	.= " productOptBusiness.monthlyInstallment,productOptAll.optionSKU,SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) as variantCode,productOptAll.modelYear, ";
                $sql 	.= " (Select count(variantCode) FROM ".DB_PREFIX."product_lease_price where variantCode=productOptAll.optionSKU and mileage!='') as leasePriceCnt,";                 
                $sql 	.= " (Select count(optionProfileID) FROM ".DB_PREFIX."product_option_profiles where variantCode=SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) and year=productOptAll.modelYear and profileCode!='') as optionProfileCnt, ";
                $sql 	.= " (Select count(cosyParamsId) FROM ".DB_PREFIX."cosy_image_params where variantCode=SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) and year=productOptAll.modelYear and status='New') as cosyImgNewCnt, ";                                  
                $sql 	.= " (Select count(cosyParamsId) FROM ".DB_PREFIX."cosy_image_params where variantCode=SUBSTRING_INDEX(productOptAll.optionSKU,'-',1) and year=productOptAll.modelYear and status!='New') as cosyImgExists ";                                  
               
		$sql.="	FROM " . DB_PREFIX . "products AS product LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
					ON product.productID=productDet.productID 
					AND productDet.languageID='" . $currentLanguageID . "'";
                $sql .= " LEFT JOIN 
                                            " . DB_PREFIX . "product_options AS productOptAll
                                            ON  productOptAll.productID = product.productID
                                            AND productOptAll.productOptionStatus='Active'
                                             LEFT JOIN 
                                            " . DB_PREFIX . "products_options_business AS productOptBusiness
                                            ON  productOptBusiness.productOptionID = productOptAll.productOptionID
                                            AND productOptBusiness.busProductStatus!='Delete' ";
				
                
                $sqlWithBusJoin = "";
                $sqlCombWithBusJoin = "";                	
		$sql .= " WHERE 1 ";			
		if(isset($status) && $status!='')
		{
			$sql .= " AND product.productStatus='".$status."'";	
		}		
		$sql .= " AND product.brandID = '2' GROUP BY product.productID ";		
		
		if (isset($sortBy) && isset($orderBy))
		{
			$sql .= " 
				ORDER BY 
					" . $sortBy . " " . $orderBy;
		}
		if (isset($limitTo) && !empty($limitTo))
		{
			$sql .= " 
				LIMIT 
					" . $limitFrom . "," . $limitTo . " ";
		}   
		return $this->ObjDatabase->readValuesSlave($sql);		
	}
	//END OF FUNCTION
	public function searchAutoSuggest($params)
	{
		extract($params);
		
		$limitFrom		= ($limitFrom >0) ? $limitFrom : 0;
		$inclCalcRows	= ($limitTo >= '0') ? "SQL_CALC_FOUND_ROWS" : "";
		$searchKeyword	= $this->escape($searchKeyword);
		$now			=	date('Y-m-d');
		
		$sql			= "
				SELECT 
					".$inclCalcRows."
					product.productID,
					productCatDet.productCategoryID,
					productCatDet.productCategoryName ,
					lower(productDet.productTitle) productTitle,
					lower(productOptDet.productOptionName) productOptionName,
					CASE 
						WHEN productOptDet.productOptionName LIKE '".$searchKeyword."%' THEN 1 
						ELSE 2 
					END orderField
				FROM 
					" . DB_PREFIX . "products   				AS product 
				LEFT JOIN
					" . DB_PREFIX . "product_details 			AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " . $languageIDs['Current'] . "
						)
				INNER JOIN 
							".DB_PREFIX."business AS bus 
						ON (
							product.businessID = bus.businessID 
							AND 
							bus.businessStatus = 'Active'
						) 
				LEFT JOIN 
					".DB_PREFIX."product_options				AS productOpt 
						ON (
							product.productID = productOpt.productID
							AND
							productOpt.productOptionStatus = 'Active' 
						)	
				LEFT JOIN 
					" . DB_PREFIX . "product_option_details 	AS productOptDet 
						ON (
							productOpt.productOptionID = productOptDet.productOptionID 
							AND 
							productOptDet.languageID = " . $languageIDs['Current'] . "
						)
				LEFT JOIN 
					" . DB_PREFIX . "product_to_category   	AS productToCat 
						ON (product.productID = productToCat.productID)   
				LEFT JOIN
					" . DB_PREFIX . "product_category_details	AS productCatDet 
						ON (
							productToCat.productCategoryID = productCatDet.productCategoryID
							AND 
							productCatDet.languageID = " . $languageIDs['Current'] . "
						) 
				WHERE
						product.productStatus = 'Active' 
					AND (
						product.productStartTime <= NOW() 
						AND
						(
							product.productEndTime = '0000-00-00'
							OR
							product.productEndTime >=	'".$now."'
						)
					) ";
		
		if($categoryID!='')
		{
			$sql 	.= "
				AND 
					productToCat.productCategoryID IN (" . $categoryID . ")";
		}
		
			$sql 	.= " 
				AND (
					productDet.productTitle LIKE '%".$searchKeyword."%' COLLATE utf8_general_ci
					OR productOptDet.productOptionName LIKE '%".$searchKeyword."%' COLLATE utf8_general_ci
				) 
				
			GROUP BY 
				productOpt.productOptionID 
			ORDER BY 
				productCatDet.productCategoryID,
				orderField ASC ";
	
		if ($limitTo >= '0')
		{
			$sql 	.= " LIMIT  " . $limitFrom . "," . $limitTo . " ";
		}
	
		return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
	}
        //Get EMI Price Range
	public function getEMIPriceRange($params)
	{
		extract($params);
		$now=date('Y-m-d');
		$showAllProducts = !empty($showAllProducts)?$showAllProducts:'';

		if($showAllProducts=='Yes') {
			$updprodstatus = " IN ('Active','Inactive') ";
		} else {
			$updprodstatus = " = 'Active' ";				
		}
		//Get params from paramArray
		//$productCategoryID		= 	$this->escape($productCategoryID);
		$userGroupID                    = 	isset($userGroupID)?$userGroupID:'';
		if(isset($searchKeyword) && !empty($searchKeyword)){
			$currentLanguageID 	=	isset($languageIDs['Content'])?$languageIDs['Content']:$languageIDs['Current'];
		}
		$sql = "
			SELECT  
				ROUND(MIN(prodOptBus.monthlyInstallment),-2)-100 AS MINIMUM,
				ROUND(MAX(prodOptBus.monthlyInstallment),-3) AS MAXIMUM
			FROM  
					".DB_PREFIX."products AS product";                   
			
			if((isset($searchKeyword) && !empty($searchKeyword))){
				$sql 	.= " 
				LEFT JOIN 
				" . DB_PREFIX . "product_details AS productDet 
					ON (
						product.productID = productDet.productID 
						AND 
						productDet.languageID = " . $currentLanguageID . "
					)";
			} elseif(!empty($bmwCode)) {
                            $sql 	.= " 
				LEFT JOIN 
				" . DB_PREFIX . "product_details AS productDet 
					ON (
						product.productID = productDet.productID 
						AND 
						productDet.languageID = " . $currentLanguageID . "
					)";
                        }
			$sql.="
				LEFT JOIN  
					".DB_PREFIX."product_to_category AS productToCat  
						ON 	(
							product.productID = productToCat.productID       
						)
				LEFT JOIN  
					".DB_PREFIX."product_options AS productOpt 
						ON 	(	
							productOpt.productID = productToCat.productID  
							AND 
							productOpt.productOptionDefault='Yes'
						)
				LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
				
				";
                      
		if(isset($searchKeyword) && !empty($searchKeyword)){
			$sql 	.= " 
				LEFT JOIN
					" . DB_PREFIX . "product_category_details	AS productCatDet 
						ON (
							productToCat.productCategoryID = productCatDet.productCategoryID
							AND 
							productCatDet.languageID = " . $currentLanguageID . "
						)";
		}
		if(!empty($custCode)) {
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_link 	AS prodcustlink 
                                            ON (
                                                    prodcustlink.productID = product.productID 
                                                   )";
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_variant 	AS prodCustVar 
                                            ON (
                                                    prodCustVar.customOptionID = prodcustlink.customOptionID 
                                                   )";
                }		
			
		$sql.="				
			WHERE 
				product.productStatus ".$updprodstatus."  
				";
		/*if(isset($productCategoryID) && $productCategoryID!='' && $productCategoryID!='0'){
			$sql.="
			AND productToCat.productCategoryID IN (".$productCategoryID.")	
			";
		}*/                
                if(!empty($productCategoryID)){
                        $productCategoryID   =  "'".implode("','",$productCategoryID)."'";
			$sql.="
			AND productToCat.productCategoryID IN (".$productCategoryID.")	
			";
		}
		if(isset($brandID) && $brandID>0){
                        $sql.="
			AND (product.brandID = '".$brandID."')
			";
			/*$sql.="
			AND (brand.brandID = '".$brandID."' || brand.parent_id = '".$brandID."')
			";*/
		}
                if(isset($brandIDs) && !empty($brandIDs)){
                        $sql.="
			AND product.brandID IN (".$brandIDs.")
			";
			
		}
		if(isset($businessID) && $businessID>0){
			$sql.="
			AND product.businessID = '".$businessID."'
			";
		}
                $sql	.= " AND  prodOptBus.monthlyInstallment >0";
		if(isset($searchKeyword) && count($searchKeyword)){
			foreach($searchKeyword as $keyword){
				$keyword=$this->escape($keyword);
				$productTitle[]		=	" productDet.productTitle LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$productCategoryName[]	=	" productCatDet.productCategoryName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$searchTags[]		=	" productDet.searchTags LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$productOptionName[]	=	" productOptDet2.productOptionName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
			}
			$sql.= " 
				AND (
					(".implode(" OR ",$productTitle ).")
					OR
					(".implode(" OR ",$productCategoryName ).")
					OR
					(".implode(" OR ",$searchTags ).")
					OR EXISTS (
						SELECT productOpt2.productID
						FROM 
							".DB_PREFIX."product_options			AS productOpt2
						LEFT JOIN 
							".DB_PREFIX."product_option_details	AS productOptDet2
								ON (
								productOpt2.productOptionID = productOptDet2.productOptionID
								AND
								productOptDet2.languageID = '".$currentLanguageID."'
							)
						WHERE
							productOpt2.productID = product.productID
							AND
							(".implode(" OR ",$productOptionName ).")
							AND
							productOpt2.productOptionStatus ".$updprodstatus."
					)
				) ";
		}
		
		$sql.="
				AND productOpt.productOptionDefault='Yes'";
                if(!empty($bodyStyle)) {
                   
                    $bodyStyle   =  "'".implode("','",$bodyStyle)."'";
                    $sql	.= " AND  product.bodyStyle IN (".$bodyStyle.")";
                   
                }                
                if(!empty($custCode)) {
                    
                    $custCode   =  "'".implode("','",$custCode)."'";
                    $sql	.= " AND  prodCustVar.variantCode IN (".$custCode.")";
                    
                }
                if(!empty($bmwCode)) {
                    /*$bmwCode = $this->encryptDecrypt("decrypt",$bmwCode);
                    $bmwCode = explode(",",$bmwCode);
                    $sql	.= " AND  productOpt.bmwCode IN ('" . implode("','",$bmwCode) . "') ";*/
                    $sql	.= " AND  productDet.productShortDescKey = '" .$bmwCode . "' ";
                }
		return $this->ObjDatabase->readValueSlave($sql);	
	}
	//Get Price Range
	public function getPriceRange($params)
	{
		extract($params);
		$now=date('Y-m-d');
		$showAllProducts = !empty($showAllProducts)?$showAllProducts:'';
		//Get params from paramArray
		//$productCategoryID		= 	$this->escape($productCategoryID);
		$userGroupID                    = 	isset($userGroupID)?$userGroupID:'';
		if(isset($searchKeyword) && !empty($searchKeyword)){
			$currentLanguageID 	=	isset($languageIDs['Content'])?$languageIDs['Content']:$languageIDs['Current'];
		}
		if($showAllProducts=='Yes') {
			$updprodstatus = " IN ('Active','Inactive') ";
		} else {
			$updprodstatus = " = 'Active' ";				
		}   
                
		/*$sql = "
			SELECT  
				ROUND(MIN(
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					
				),-4) AS MINIMUM,
				ROUND(MAX(
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
				),-3)+1000 AS MAXIMUM
			FROM  
					".DB_PREFIX."products AS product";   */  
                $sql = "
			SELECT  
				ROUND(MIN(
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					
				),-3)-1000 AS MINIMUM, 
				ROUND(MAX(
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
				),-3) AS MAXIMUM
			FROM  
					".DB_PREFIX."products AS product";                   
			
			
			if((isset($searchKeyword) && !empty($searchKeyword))){
				$sql 	.= " 
				LEFT JOIN 
				" . DB_PREFIX . "product_details AS productDet 
					ON (
						product.productID = productDet.productID 
						AND 
						productDet.languageID = " . $currentLanguageID . "
					)";
			}
                        elseif(!empty($bmwCode)) {
                            $sql 	.= " 
				LEFT JOIN 
				" . DB_PREFIX . "product_details AS productDet 
					ON (
						product.productID = productDet.productID 
						AND 
						productDet.languageID = " . $currentLanguageID . "
					)";
                        }
			$sql.="
				LEFT JOIN  
					".DB_PREFIX."product_to_category AS productToCat  
						ON 	(
							product.productID = productToCat.productID       
						)
				LEFT JOIN  
					".DB_PREFIX."product_options AS productOpt 
						ON 	(	
							productOpt.productID = productToCat.productID  
							AND 
							productOpt.productOptionDefault='Yes'
						)
				LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
				LEFT JOIN
					".DB_PREFIX."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".$userGroupID."'
                                                        AND varOffPri.businessID = prodOptBus.businessID
						)
				LEFT JOIN
					".DB_PREFIX."variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='".$userGroupID."'
                                                        AND varGroPri.businessID = prodOptBus.businessID
						)";
                      
		if(isset($searchKeyword) && !empty($searchKeyword)){
			$sql 	.= " 
				LEFT JOIN
					" . DB_PREFIX . "product_category_details	AS productCatDet 
						ON (
							productToCat.productCategoryID = productCatDet.productCategoryID
							AND 
							productCatDet.languageID = " . $currentLanguageID . "
						)";
		}
		if(!empty($custCode)) {
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_link 	AS prodcustlink 
                                            ON (
                                                    prodcustlink.productID = product.productID 
                                                   )";
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_variant 	AS prodCustVar 
                                            ON (
                                                    prodCustVar.customOptionID = prodcustlink.customOptionID 
                                                   )";
                }	
			
		$sql.="				
			WHERE 
				product.productStatus ".$updprodstatus."  
				AND 
				( 
					product.productStartTime <= NOW()  
					AND 
					( 
						product.productEndTime = '0000-00-00' 
						OR 
						product.productEndTime >=    '".$now."'
					) 
				)";
		/*if(isset($productCategoryID) && $productCategoryID!='' && $productCategoryID!='0'){
			$sql.="
			AND productToCat.productCategoryID IN (".$productCategoryID.")	
			";
		}*/                
                if(!empty($productCategoryID)){
                        $productCategoryID   =  "'".implode("','",$productCategoryID)."'";
			$sql.="
			AND productToCat.productCategoryID IN (".$productCategoryID.")	
			";
		}
                $sql	.= " AND  prodOptBus.monthlyInstallment >0";
		if(isset($brandID) && $brandID>0){
                        $sql.="
			AND (product.brandID = '".$brandID."')
			";
			/*$sql.="
			AND (brand.brandID = '".$brandID."' || brand.parent_id = '".$brandID."')
			";*/
		}
                if(isset($brandIDs) && !empty($brandIDs)){
                        $sql.="
			AND product.brandID IN (".$brandIDs.")
			";
			
		}
		if(isset($businessID) && $businessID>0){
			$sql.="
			AND product.businessID = '".$businessID."'
			";
		}
		if(isset($searchKeyword) && count($searchKeyword)){
			foreach($searchKeyword as $keyword){
				$keyword=$this->escape($keyword);
				$productTitle[]		=	" productDet.productTitle LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$productCategoryName[]	=	" productCatDet.productCategoryName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$searchTags[]		=	" productDet.searchTags LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$productOptionName[]	=	" productOptDet2.productOptionName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
			}
			$sql.= " 
				AND (
					(".implode(" OR ",$productTitle ).")
					OR
					(".implode(" OR ",$productCategoryName ).")
					OR
					(".implode(" OR ",$searchTags ).")
					OR EXISTS (
						SELECT productOpt2.productID
						FROM 
							".DB_PREFIX."product_options			AS productOpt2
						LEFT JOIN 
							".DB_PREFIX."product_option_details	AS productOptDet2
								ON (
								productOpt2.productOptionID = productOptDet2.productOptionID
								AND
								productOptDet2.languageID = '".$currentLanguageID."'
							)
						WHERE
							productOpt2.productID = product.productID
							AND
							(".implode(" OR ",$productOptionName ).")
							AND
							productOpt2.productOptionStatus ".$updprodstatus."  
					)
				) ";
		}
		
		$sql.="
				AND productOpt.productOptionDefault='Yes'";
                if(!empty($bodyStyle)) {
                   
                    $bodyStyle   =  "'".implode("','",$bodyStyle)."'";
                    $sql	.= " AND  product.bodyStyle IN (".$bodyStyle.")";
                   
                }                
                if(!empty($custCode)) {
                    
                    $custCode   =  "'".implode("','",$custCode)."'";
                    $sql	.= " AND  prodCustVar.variantCode IN (".$custCode.")";
                    
                }                
                if(!empty($bmwCode)) {
                    /*$bmwCode = $this->encryptDecrypt("decrypt",$bmwCode);
                    $bmwCode = explode(",",$bmwCode);
                    $sql	.= " AND  productOpt.bmwCode IN ('" . implode("','",$bmwCode) . "') ";*/
                    $sql	.= " AND  productDet.productShortDescKey = '" .$bmwCode . "' ";
                }
                //echo $sql;exit;
		return $this->ObjDatabase->readValueSlave($sql);	
	}
	public function checkProductOutOfStock($productID,$productQuantityNotifyAt=0,$InventoryTrackLevel)
	{
		if($InventoryTrackLevel=='Product')
		{
		
			$sql="	SELECT 
						productOptionID
					FROM 
						" . DB_PREFIX . "product_options
					WHERE 
						productID=".$productID."
						AND 
						productOptionStatus='Active'
						AND productOptionQuantity<".(int)$productQuantityNotifyAt;
		}
		else
		{
			$sql=" SELECT 
						combinationID
					FROM 
						" . DB_PREFIX . "product_options as productOpt
					LEFT JOIN 
						" . DB_PREFIX . "product_combination as proCom
						ON(proCom.productID=".$productID.")
					WHERE 
						productOpt.productID=".$productID."								
						AND 
						proCom.status='A'
						AND proCom.combinationQty<".(int)$productQuantityNotifyAt;
		}
		$resGetInventory = $this->ObjDatabase->readValueSlave($sql);
		if(count($resGetInventory)>0)
		{
			return 'Yes';
		}
		else
		{
			return 'No';
		}	
	}
	
	public function updateProductPopularity($productID,$mode,$boughtQty=0)
	{
		$today 	=	date('Y-m-d');
		$sql	=	"	SELECT 
							popularityID
						FROM
							" . DB_PREFIX . "product_popularity 
						WHERE
							productID=" .(int)$productID . " AND visitDate='".$today."' LIMIT 1";
		$resPopularity = $this->ObjDatabase->readValueSlave($sql);
		if($resPopularity['popularityID']>0)
		{
			switch ($mode) 
			{
				case 'view':
					$sql	= " UPDATE 
									`" . DB_PREFIX . "product_popularity` 
								SET 
									viewed=viewed+1 
								WHERE 
									popularityID=" . (int)$resPopularity['popularityID']." LIMIT 1";
					$resUpdate = $this->ObjDatabase->setQuery($sql);	
				break;
				case 'uniqueView':
					$sql	= " UPDATE 
									`" . DB_PREFIX . "product_popularity` 
								SET 
									viewed=viewed+1,uniqueViewed=uniqueViewed+1,total=total+2
								WHERE 
									popularityID=" . (int)$resPopularity['popularityID']." LIMIT 1";
					$resUpdate = $this->ObjDatabase->setQuery($sql);	
				break;
				case 'add':
					$sql	= " UPDATE 
									`" . DB_PREFIX . "product_popularity` 
								SET 
									added=added+1,total=total+3
								WHERE 
									popularityID=" . (int)$resPopularity['popularityID']." LIMIT 1";
					$resUpdate = $this->ObjDatabase->setQuery($sql);	
				break;
				case 'delete':
					$sql	= " UPDATE 
									`" . DB_PREFIX . "product_popularity` 
								SET 
									deleted=deleted+1,total=total
								WHERE 
									popularityID=" . (int)$resPopularity['popularityID']." LIMIT 1";
					$resUpdate = $this->ObjDatabase->setQuery($sql);	
				break;
				case 'purchase':
					$sql	= " UPDATE 
									`" . DB_PREFIX . "product_popularity` 
								SET 
									bought=bought+1,total=total+4,
									boughtQty=boughtQty+".$boughtQty."
								WHERE 
									popularityID=" . (int)$resPopularity['popularityID']." LIMIT 1";
					$resUpdate = $this->ObjDatabase->setQuery($sql);	
				break;
			}
		}
		else
		{
			switch ($mode) 
			{
				case 'view':
					$sql	= " INSERT INTO " . DB_PREFIX . "product_popularity						   
					 				(viewed,productID,visitDate)
					 			VALUES
					 				(1,".$productID.",'".$today."')";
					$this->ObjDatabase->setQuery($sql);
				break;
				case 'uniqueView':
					$sql	= " INSERT INTO " . DB_PREFIX . "product_popularity						   
					 				(viewed,uniqueViewed,productID,visitDate,total)
					 			VALUES
					 				(1,1,".$productID.",'".$today."',2)";
					$this->ObjDatabase->setQuery($sql);	
				break;
				case 'add':
					$sql	= " INSERT INTO " . DB_PREFIX . "product_popularity						   
					 				(viewed,uniqueViewed,added,productID,visitDate,total)
					 			VALUES
					 				(1,1,1,".$productID.",'".$today."',3)";
					$this->ObjDatabase->setQuery($sql);	
				break;
			}
		}
	}
	
	public function getProductPopularity($dateFrom)
	{
		$sql	=	"	SELECT 
							sum(total) as totalPopularity,
							sum(bought) as boughtPopularity,
							productID
						FROM
							" . DB_PREFIX . "product_popularity 
						WHERE
							visitDate>='".$dateFrom."' GROUP BY productID";
		$resPopularity = $this->ObjDatabase->readValuesSlave($sql);
		return $resPopularity;
	}
	public function updateProductPopularityCount($productID,$totalPopularity,$boughtPopularity)
	{
		$sql	= " UPDATE 
						`" . DB_PREFIX . "products` 
					SET 
						popularity=".$totalPopularity.",purchasePopularity=".$boughtPopularity."
					WHERE 
						productID=" . $productID;
		$resUpdate = $this->ObjDatabase->setQuery($sql);
	}
     public function listSitemapProducts($languageIDs, $categoryID)
	{
		$currDateTime		= ($currDateTime == "") ? "'" . date('Y-m-d') . "'" : "'" . $currDateTime . "'";
        $sql	= "
				SELECT 
					".$inclCalcRows."
					product.productID,
					product.productUrl
				FROM 
					" . DB_PREFIX . "products AS product";		
		if($categoryID !='0')
		{	
			$sql 	.= "
				INNER JOIN 
				" . DB_PREFIX . "product_to_category   	AS productToCat 
					ON  
					   product.productID = productToCat.productID
                    AND 
             		productToCat.productCategoryID IN (" . $categoryID . ")";
		}
		$sql 	.= "
			INNER JOIN 
				" . DB_PREFIX."business 	AS bus 
					ON (
						product.businessID = bus.businessID 
						AND 
						bus.businessStatus = 'Active'
				)
			";

		$sql 	.= "
				WHERE 
					product.productStatus = 'Active' 
				";
		$sql 	.= " 
			GROUP BY product.productID";
        return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
	} 
        public function listProductRewrite($languageIDs,$searchProductKeyword='',$searchProductCategory=0)
        {
			$currDateTime	=	'';
            $currDateTime		= ($currDateTime == "") ? "'" . date('Y-m-d') . "'" : "'" . $currDateTime . "'";
            $sql 		= "SELECT 
                                        product.productID,
                                        productDet.productTitle
						FROM 
							".DB_PREFIX."products			AS product
						LEFT JOIN	
							".DB_PREFIX."product_details	AS productDet
								ON (
									product.productID = productDet.productID
									AND 
									productDet.languageID = '".$languageIDs['Current']."'
								) ";
            if($searchProductKeyword!=''){
                 $sql 		.= "                      LEFT JOIN 
						" . DB_PREFIX . "product_options	AS productOptFull
							ON 	(
								productOptFull.productID = product.productID  AND productOptFull.productOptionStatus='Active'
								)
                                                LEFT JOIN
						".DB_PREFIX."product_combination as proCom 
                                                ON 
                                                (proCom.productID=product.productID AND proCom.status='A')  ";
            }
                        
            if($searchProductCategory!=''){
		$sql 	.= " 
					INNER JOIN ".DB_PREFIX."product_to_category productToCat 		
								ON (productToCat.productID = product.productID 	AND 
								productToCat.productCategoryID= '".$searchProductCategory."'  )";
            }
	    $sql	.= 	"	WHERE 
							product.productStatus='Active' 
                                                        AND
                                                        (
                                                                product.productEndTime = '0000-00-00'
                                                                OR
                                                                product.productEndTime >=	" . $currDateTime . "
                                                        )                                                        
						";
            
            if ($searchProductKeyword != '')
		{
			$searchProductKeyword = $this->escape($searchProductKeyword);
			$sql .= " 	AND 
						(productDet.productTitle LIKE '%" . $searchProductKeyword . "%' OR productOptFull.optionSKU LIKE '%" . $searchProductKeyword . "%' OR proCom.combinationSKU LIKE '%" . $searchProductKeyword . "%') ";
		}
                $sql	.= 	" GROUP BY product.productID ";  
		return $this->ObjDatabase->readValuesSlave($sql);
        }
	//Discount Schema
	public function listDiscountSchema()
	{
		$schema 	= array 
								(
									'10' => 'lblDiscount10Perc',
									'20' => 'lblDiscount20Perc',
									'30' => 'lblDiscount30Perc',
									'40' => 'lblDiscount40Perc',
									'50' => 'lblDiscount50Perc'
								);
		return 	$schema;
	}
	//Catalog Attribute Search Seperator Schema
	public function getCatalogUrlSeperatorSchema()
	{
		$schema	= array 
					(
						'price' => '-',
						'attributes' => '--',
						'attribute' => ':',
						'attrOption' => '^',
						'attrOptionID' => '~*'
					);
		return 	$schema;
	}
	
	//FETCHING ALL ACTIVE DEALS
	public function listAllActiveProducts()
	{
		$_settings 	= Registry::getInstance('_settings');		
		$this->ObjDatabase->setQuery("SET SESSION group_concat_max_len = 1000000");
		$sql 	= "SELECT 
					productID,
					productUrl,					
					(
						SELECT 
							GROUP_CONCAT(productCategoryID) 
						FROM 
							" . DB_PREFIX . "product_to_category productToCat
						WHERE 
							productToCat.productID=product.productID
					) AS productCategoryIDs
					FROM 
						" . DB_PREFIX . "products	AS product ";

		$sql .= " WHERE 1 ";				
		$sql .= " AND	product.productStatus!='Deleted'";	
		//$sql .= " GROUP BY product.productID";		// LIMIT 0,1
		return $this->ObjDatabase->readValuesSlave($sql);		
	}
	//END OF FUNCTION
	public function listRecommendedProductsNew($params){
		
		$itemCount				= 	$params['itemCount'];
		$itemCount   			= 	(isset($itemCount) && $itemCount>0) ? $itemCount: 15;	
		$recommendProdDtls1 	= 	$this->listRecommendedProducts1New($params);
		$recmmdPtds 			= 	$recommendProdDtls1;
		if(count($recommendProdDtls1) < $itemCount ) { 
			$recommendProdDtls2	= 	$this->listRecommendedProducts2($params);
			$recommendProdDtls3 = 	array_merge($recommendProdDtls1 , $recommendProdDtls2);
			$recommendProdDtls3 = 	array_unique($recommendProdDtls3, SORT_REGULAR);
			
			$recmmdPtds = $recommendProdDtls3;
			if(count($recommendProdDtls3) < $itemCount ) { 
				$recommendProdDtls4	= $this->listRecommendedProducts3($params);
				$recommendProdDtls5 = array_merge($recommendProdDtls3 , $recommendProdDtls4);
				$recommendProdDtls5 = array_unique($recommendProdDtls5, SORT_REGULAR);
				$recmmdPtds 		= $recommendProdDtls5;
			}
		} 
		return array_slice($recmmdPtds, 0, $itemCount);
		
	}
        //TO LIST RECOMMENDATIONS WITH SAME ATTRIBUTE SET AND ATTRIBUTE VALUES
	public function listRecommendedProducts1New($params) {
		extract($params);
		$itemCount   		= 	(isset($itemCount) && $itemCount>0) ? $itemCount: 15;	
		$currDateTime		= 	"'" . date('Y-m-d') . "'" ;
		$lowerPrice			= 	$minTierPrice - ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));
		$higherPrice		= 	$minTierPrice + ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));
	
		$sql = "SELECT 
					product.productID,
					productDet.productTitle,
					product.productImage,
					product.productDefaultCurrency,
					productOpt.productOptionID,
					productOpt.productOptionRegularPrice,
                                        product.productMinBuyLimit,                                                                                            
                                        product.InventoryTrackLevel, 
                                        product.featuredStatus,
                                        product.sellAsBatch,
                                        product.batchType,
                                        product.qtyPerBatch,
					prodOptBus.productOptionRegularPrice,
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					AS offerPrice,
					IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									sum(productOptionQuantity)
								FROM 
									" . DB_PREFIX . "product_options
								WHERE 
									productID=product.productID 
									AND 
									productOptionStatus='Active'
							),
							(
								SELECT 
									sum(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination as proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status='A'
							)
						)
					)
					AS productOptionQuantitySum
				FROM
					" . DB_PREFIX . "product_attribute_values productAttrVal
				INNER JOIN 
					".DB_PREFIX."products AS product
					ON product.productID = productAttrVal.productID
					AND product.productStatus = 'Active' 
					AND (
						product.productStartTime <= " . $currDateTime . " 
						AND	(
							product.productEndTime = '0000-00-00'
							OR
							product.productEndTime >=	" . $currDateTime . "
						)
					)
				INNER JOIN	(
					SELECT
						productAttrVal.attributeID,
						productAttrVal.attrOptionID
					FROM
						" . DB_PREFIX . "product_attribute_values productAttrVal
					LEFT JOIN	
						".DB_PREFIX."attributes attr
						ON productAttrVal.attributeID = attr.attributeID
						AND attr.attrStatus = 'A'
						AND useInRecomendation = 'Y'
					WHERE
						productAttrVal.productID = '".$productID."'
				) AS selectedProdAttrs
				ON 
					productAttrVal.attributeID = selectedProdAttrs.attributeID
				AND 
					productAttrVal.attrOptionID = selectedProdAttrs.attrOptionID 
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " . $languageIDs['Current'] . "
						) ";
                if(!empty($categoryID) && isset($categoryID)) {
                    $sql .= " INNER JOIN ".DB_PREFIX."product_to_category AS productCat ON (productCat.productID=product.productID and productCat.productCategoryID='".$categoryID."') ";
                }                
		$sql    .=      "INNER JOIN 
					".DB_PREFIX."business 	AS bus 
					ON (
						product.businessID = bus.businessID 
						AND 
						bus.businessStatus = 'Active'
					)
				LEFT JOIN 
					".DB_PREFIX."product_options	AS productOpt 
						ON (
							product.productID = productOpt.productID
							AND
								productOpt.productOptionStatus = 'Active' 
							AND 
								productOpt.productOptionDefault = 'Yes'
						) 
				LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
                                
				LEFT JOIN
					".DB_PREFIX."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".$userGroupID."'
                                                        AND varOffPri.businessID = prodOptBus.businessID
						)	
				LEFT JOIN
					".DB_PREFIX."variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='".$userGroupID."'
                                                        AND varGroPri.businessID = prodOptBus.businessID
						)	
				WHERE 1";
		if(isset($excludeProducts) && !empty($excludeProducts)){
			$sql .= "	AND
							product.productID NOT IN ('".implode("','",$excludeProducts)."') ";
		}               
		$sql .= "		
				AND 
				product.attributeSetID	= '".$attributeSetID."'
				AND 
				productAttrVal.productID <> '".$productID."'	
				AND LEAST(
					COALESCE(
						varOffPri.offerPrice,
						prodOptBus.productOptionProductPrice
					),
					COALESCE(
						varGroPri.specialPrice,
						prodOptBus.productOptionProductPrice
					) 
				)  BETWEEN ".$lowerPrice." AND ".$higherPrice."
				GROUP BY product.productID
				LIMIT 0, ".$itemCount." "; 
		
		return $this->ObjDatabase->readValuesSlave($sql,MYSQLI_ASSOC);		
	}
	public function listRecommendedProducts($params){
		
		$itemCount				= 	$params['itemCount'];
		$itemCount   			= 	(isset($itemCount) && $itemCount>0) ? $itemCount: 15;	
		$recommendProdDtls1 	= 	$this->listRecommendedProducts1($params);
		$recmmdPtds 			= 	$recommendProdDtls1;
		if(count($recommendProdDtls1) < $itemCount ) { 
			$recommendProdDtls2	= 	$this->listRecommendedProducts2($params);
			$recommendProdDtls3 = 	array_merge($recommendProdDtls1 , $recommendProdDtls2);
			$recommendProdDtls3 = 	array_unique($recommendProdDtls3, SORT_REGULAR);
			
			$recmmdPtds = $recommendProdDtls3;
			if(count($recommendProdDtls3) < $itemCount ) { 
				$recommendProdDtls4	= $this->listRecommendedProducts3($params);
				$recommendProdDtls5 = array_merge($recommendProdDtls3 , $recommendProdDtls4);
				$recommendProdDtls5 = array_unique($recommendProdDtls5, SORT_REGULAR);
				$recmmdPtds 		= $recommendProdDtls5;
			}
		} 
		return array_slice($recmmdPtds, 0, $itemCount);
		
	}
	//TO LIST RECOMMENDATIONS WITH SAME ATTRIBUTE SET AND ATTRIBUTE VALUES
	public function listRecommendedProducts1($params) {
		extract($params);
		$itemCount   		= 	(isset($itemCount) && $itemCount>0) ? $itemCount: 15;	
		$currDateTime		= 	"'" . date('Y-m-d') . "'" ;
		$lowerPrice			= 	$minTierPrice - ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));
		$higherPrice		= 	$minTierPrice + ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));
	
		$sql = "SELECT 
					product.productID,
					productDet.productTitle,
					product.productImage,
					product.productDefaultCurrency,
					productOpt.productOptionID,
					productOpt.productOptionRegularPrice,
                                        product.productMinBuyLimit,                                                                                                                     product.sellAsBatch,
                                        product.batchType,
                                        product.qtyPerBatch,
                                        product.InventoryTrackLevel,
                                        product.featuredStatus,
					prodOptBus.productOptionRegularPrice,
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					AS offerPrice,
					IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									sum(productOptionQuantity)
								FROM 
									" . DB_PREFIX . "product_options
								WHERE 
									productID=product.productID 
									AND 
									productOptionStatus='Active'
							),
							(
								SELECT 
									sum(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination as proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status='A'
							)
						)
					)
					AS productOptionQuantitySum,product.freeProductsCount
				FROM
					" . DB_PREFIX . "product_attribute_values productAttrVal
				INNER JOIN 
					".DB_PREFIX."products AS product
					ON product.productID = productAttrVal.productID
					AND product.productStatus = 'Active' 
					AND (
						product.productStartTime <= " . $currDateTime . " 
						AND	(
							product.productEndTime = '0000-00-00'
							OR
							product.productEndTime >=	" . $currDateTime . "
						)
					)
				INNER JOIN	(
					SELECT
						productAttrVal.attributeID,
						productAttrVal.attrOptionID
					FROM
						" . DB_PREFIX . "product_attribute_values productAttrVal
					LEFT JOIN	
						".DB_PREFIX."attributes attr
						ON productAttrVal.attributeID = attr.attributeID
						AND attr.attrStatus = 'A'
						AND useInRecomendation = 'Y'
					WHERE
						productAttrVal.productID = '".$productID."'
				) AS selectedProdAttrs
				ON 
					productAttrVal.attributeID = selectedProdAttrs.attributeID
				AND 
					productAttrVal.attrOptionID = selectedProdAttrs.attrOptionID 
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " . $languageIDs['Current'] . "
						) ";
                if(!empty($categoryID) && isset($categoryID)) {
                    $sql .= " INNER JOIN ".DB_PREFIX."product_to_category AS productCat ON (productCat.productID=product.productID and productCat.productCategoryID='".$categoryID."') ";
                }                
		$sql    .=      "INNER JOIN 
					".DB_PREFIX."business 	AS bus 
					ON (
						product.businessID = bus.businessID 
						AND 
						bus.businessStatus = 'Active'
					)
				LEFT JOIN 
					".DB_PREFIX."product_options	AS productOpt 
						ON (
							product.productID = productOpt.productID
							AND
								productOpt.productOptionStatus = 'Active' 
							AND 
								productOpt.productOptionDefault = 'Yes'
						) 
				LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
				LEFT JOIN
					".DB_PREFIX."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".$userGroupID."'
                                                        AND varOffPri.businessID = prodOptBus.businessID
						)	
				LEFT JOIN
					".DB_PREFIX."variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='".$userGroupID."'
                                                        AND varGroPri.businessID = prodOptBus.businessID
						)	
				WHERE 1";
		if(isset($excludeProducts) && !empty($excludeProducts)){
			$sql .= "	AND
							product.productID NOT IN ('".implode("','",$excludeProducts)."') ";
		}               
		$sql .= "		
				AND 
				product.attributeSetID	= '".$attributeSetID."'
				AND 
				productAttrVal.productID <> '".$productID."'	
				AND LEAST(
					COALESCE(
						varOffPri.offerPrice,
						prodOptBus.productOptionProductPrice
					),
					COALESCE(
						varGroPri.specialPrice,
						prodOptBus.productOptionProductPrice
					) 
				)  BETWEEN ".$lowerPrice." AND ".$higherPrice."
				GROUP BY product.productID
				LIMIT 0, ".$itemCount." "; 
		
		return $this->ObjDatabase->readValuesSlave($sql,MYSQLI_ASSOC);		
	}
	//TO LIST RECOMMENDATIONS WITH SAME ATTRIBUTE SET 
	public function listRecommendedProducts2($params) {
		extract($params);
		$itemCount   		= 	(isset($itemCount) && $itemCount>0) ? $itemCount: 15;	
		$currDateTime		=  	"'" . date('Y-m-d') . "'" ;
		$lowerPrice			= 	$minTierPrice - ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));
		$higherPrice		= 	$minTierPrice + ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));	
		$sql = " SELECT 
					product.productID,
					productDet.productTitle,
					product.productImage,
					product.productDefaultCurrency,
					productOpt.productOptionID,
					productOpt.productOptionRegularPrice,
                                        product.productMinBuyLimit,                                                                                                                     product.sellAsBatch,
                                        product.batchType,
                                        product.qtyPerBatch,
                                        product.InventoryTrackLevel,
                                        product.featuredStatus,
					prodOptBus.productOptionRegularPrice,
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					AS offerPrice,
					IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									sum(productOptionQuantity)
								FROM 
									" . DB_PREFIX . "product_options
								WHERE 
									productID=product.productID 
									AND 
									productOptionStatus='Active'
							),
							(
								SELECT 
									sum(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination as proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status='A'
							)
						)
					)
					AS productOptionQuantitySum,product.freeProductsCount
				FROM
					".DB_PREFIX."products AS product
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " . $languageIDs['Current'] . "
						) ";
                if(!empty($categoryID) && isset($categoryID)) {
                    $sql .= " INNER JOIN ".DB_PREFIX."product_to_category AS productCat ON (productCat.productID=product.productID and productCat.productCategoryID='".$categoryID."') ";
                }  
			$sql .= " INNER JOIN 
					".DB_PREFIX."business 	AS bus 
					ON (
						product.businessID = bus.businessID 
						AND 
						bus.businessStatus = 'Active'
					)
				LEFT JOIN 
					".DB_PREFIX."product_options	AS productOpt 
						ON (
							product.productID = productOpt.productID
							AND
							productOpt.productOptionStatus = 'Active' 
							AND 
							productOpt.productOptionDefault = 'Yes'
						) 
				LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
				LEFT JOIN
					".DB_PREFIX."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".$userGroupID."'
                                                        AND varOffPri.businessID = prodOptBus.businessID
						)	
				LEFT JOIN
					".DB_PREFIX."variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='".$userGroupID."'
                                                        AND varGroPri.businessID = prodOptBus.businessID
						)	
				WHERE 1";
		if(isset($excludeProducts) && !empty($excludeProducts)){
			$sql .= " AND
						product.productID NOT IN ('".implode("','",$excludeProducts)."') ";
		}
		$sql .= "		
				AND
				product.productStatus = 'Active' 
				AND product.attributeSetID = '" .$attributeSetID. "' 
				AND product.productID <> '".$productID."'
				AND (
					product.productStartTime <= " . $currDateTime . " 
					AND
					(
						product.productEndTime = '0000-00-00'
						OR
						product.productEndTime >=	" . $currDateTime . "
					)
				)
				AND LEAST(
					COALESCE(
						varOffPri.offerPrice,
						prodOptBus.productOptionProductPrice
					),
					COALESCE(
						varGroPri.specialPrice,
						prodOptBus.productOptionProductPrice
					) 
				)  BETWEEN ".$lowerPrice." AND ".$higherPrice."
				GROUP BY product.productID
				LIMIT 0, ".$itemCount." ";
		return $this->ObjDatabase->readValuesSlave($sql,MYSQLI_ASSOC);		
	}
	//TO LIST RECOMMENDATIONS WITH SAME CATEGORY
	public function listRecommendedProducts3($params) {
		extract($params);	
		$itemCount   		=  	(isset($itemCount) && $itemCount>0) ? $itemCount: 15;	
		$currDateTime		=  	"'" . date('Y-m-d') . "'" ;
		$lowerPrice			= 	$minTierPrice - ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));
		$higherPrice		= 	$minTierPrice + ($minTierPrice*(RECOMM_PRICE_PERCENTAGE/100));
		$sql = "SELECT 
					product.productID,
					productDet.productTitle,
					product.productImage,
					product.productDefaultCurrency,
					productOpt.productOptionID,
					productOpt.productOptionRegularPrice,
                                        product.productMinBuyLimit,                                                                                                                     product.sellAsBatch,
                                        product.batchType,
                                        product.qtyPerBatch,
                                        product.InventoryTrackLevel,
                                        product.featuredStatus,
                                        
					prodOptBus.productOptionRegularPrice,
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					)
					AS offerPrice,
					IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									sum(productOptionQuantity)
								FROM 
									" . DB_PREFIX . "product_options
								WHERE 
									productID=product.productID 
									AND 
									productOptionStatus='Active'
							),
							(
								SELECT 
									sum(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination as proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status='A'
							)
						)
					)
					AS productOptionQuantitySum,product.freeProductsCount
					FROM
					(	SELECT  
							DISTINCT productID
						FROM
							" . DB_PREFIX . "product_to_category
						WHERE
							productCategoryID IN 
							(
								SELECT
									productCategoryID
								FROM
									" . DB_PREFIX . "product_to_category
								WHERE
									productID = '".$productID."'
							) 
					) AS selectedProductIDS
					INNER JOIN 
						".DB_PREFIX."products AS product
						ON product.productID = selectedProductIDS.productID
						AND product.productStatus = 'Active' 
						AND (
							product.productStartTime <= " . $currDateTime . " 
							AND
							(
								product.productEndTime = '0000-00-00'
								OR
								product.productEndTime >=	" . $currDateTime . "
							)
						)
					LEFT JOIN 
						" . DB_PREFIX . "product_details AS productDet 
							ON (
								product.productID = productDet.productID 
								AND 
								productDet.languageID = " . $languageIDs['Current'] . "
							)";
                 if(!empty($categoryID) && isset($categoryID)) {
                    $sql .= " INNER JOIN ".DB_PREFIX."product_to_category AS productCat ON (productCat.productID=product.productID and productCat.productCategoryID='".$categoryID."') ";
                }  
			$sql .= " INNER JOIN 
						".DB_PREFIX."business 	AS bus 
						ON (
							product.businessID = bus.businessID 
							AND 
							bus.businessStatus = 'Active'
						)
					LEFT JOIN 
						".DB_PREFIX."product_options	AS productOpt 
							ON (
								product.productID = productOpt.productID
								AND
								productOpt.productOptionStatus = 'Active' 
								AND 
								productOpt.productOptionDefault = 'Yes'
							) 
					LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							AND
							prodOptBus.businessOptionDefault='Yes'
						)
					LEFT JOIN
						".DB_PREFIX."variant_offer_price AS varOffPri
							ON	(
								varOffPri.productOptionID = productOpt.productOptionID
								AND
								varOffPri.userGroupID='".$userGroupID."'
                                                                AND varOffPri.businessID = prodOptBus.businessID
							)	
					LEFT JOIN
						".DB_PREFIX."variant_group_price AS varGroPri
							ON	(
								varGroPri.productOptionID = productOpt.productOptionID
								AND
								varGroPri.userGroupID='".$userGroupID."'
                                                                AND varGroPri.businessID = prodOptBus.businessID
							)	
					WHERE 1";
		if(isset($excludeProducts) && !empty($excludeProducts)){
			$sql.="	AND
					product.productID NOT IN ('".implode("','",$excludeProducts)."') ";
		}
			$sql .= "	 
					AND product.productID <> '".$productID."'
					AND LEAST(
							COALESCE(
								varOffPri.offerPrice,
								prodOptBus.productOptionProductPrice
							),
							COALESCE(
								varGroPri.specialPrice,
								prodOptBus.productOptionProductPrice
							) 
						)  BETWEEN ".$lowerPrice." AND ".$higherPrice."
					LIMIT 0,".$itemCount." ";
		return $this->ObjDatabase->readValuesSlave($sql,MYSQLI_ASSOC);	
	}
	public function getUpSellProducts($productID)
	{
	$sql="SELECT 
		upSellProductID
			FROM 
				" . DB_PREFIX . "product_link_up_sell
						WHERE 
						productID=".$productID." ORDER BY priority ASC";
		return $this->ObjDatabase->getFieldsSlave($sql);
	}
	public function getCrossSellProducts($productID)
	{
	$sql="SELECT 
		crossSellProductID 
			FROM 
				" . DB_PREFIX . "product_link_cross_sell
						WHERE 
						productID=".$productID." ORDER BY priority ASC";
		return $this->ObjDatabase->getFieldsSlave($sql);
	}
        public function getFreeProducts($productID)
	{
	$sql="SELECT 
		freeProductID 
			FROM 
				" . DB_PREFIX . "product_link_free
						WHERE 
						productID=".$productID." ORDER BY priority ASC";
		return $this->ObjDatabase->getFieldsSlave($sql);
	}
	public function getRelatedProducts($productID)
	{
	$sql="SELECT 
		relatedProductID
			FROM 
				" . DB_PREFIX . "product_link_relation
						WHERE 
						productID=".$productID." ORDER BY priority ASC";
		return $this->ObjDatabase->getFieldsSlave($sql);
	}
	//FETCHING ALL Requested Products
	public function listRequestedProducts($params)
	{
		extract($params);
		$defaultLanguageID	= $languageIDs['Default'];
		$currentLanguageID	= $languageIDs['Current'];
		$sortBy = isset($sortBy)?$sortBy:'';
		$sortCase = $sortBy;
		$bindCombinationTable = 0;
		switch ($sortBy)
		{
			case "screated":
				$sortBy = "product.productCreatedDate";
				break;
			case "stitle":
				$sortBy = "productDet.productTitle";
				break;
			default:
				if(isset($orderBy) && $orderBy!=''){
					$sortBy = "product.productID ";
				}
				else{
					$sortBy = "product.productID DESC";
				}
				break;
		}
		$sql 	= "
				SELECT ";
		if (isset($limitTo))
		{
			$sql .= "
					SQL_CALC_FOUND_ROWS ";
		}
		$sql 	.= "		
					product.productID,
					product.multiOptionsAvailable,
					product.productImage,
					product.businessID,
					product.productStatus,
					product.productCreatedDate,
					product.InventoryTrackLevel,
					product.productDefaultCurrency,
					IF((product.productEndTime = '0000-00-00'OR DATE_FORMAT(product.productEndTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-%d')), 'ongoing', 'over') as productLife,
					productDet.productTitle,";
		
		$sql.="	
					busDet.businessName
				FROM 
					" . DB_PREFIX . "products AS product 
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
					ON product.productID=productDet.productID 
					AND productDet.languageID='" . $currentLanguageID . "'";
		
		
		$sql .= "					
				LEFT JOIN 
					" . DB_PREFIX . "business_details AS busDet 
					ON busDet.businessID = product.businessID 
					AND busDet.languageID='" . $currentLanguageID . "'";			
		if($sortCase == "sprice" || $sortCase == "sdiscount")
		{
			$sql .= "
				LEFT JOIN 
					" . DB_PREFIX . "product_options AS productOpt 
					ON productOpt.productID = product.productID 
					AND productOpt.productOptionDefault='Yes'";
		}
		if((isset($searchProductKeyword) && $searchProductKeyword != ''))
		{
			$sql.="		
				LEFT JOIN 
					" . DB_PREFIX . "product_options AS productOptFull
					ON  productOptFull.productID = product.productID
					AND productOptFull.productOptionStatus='Active'";
		}
		if((isset($searchProductKeyword) && $searchProductKeyword != '')
			|| (isset($searchInventory) && $searchInventory=='nostock')
			|| (!empty($searchQuantityBelow))
		)
		{
			$sql.="
				LEFT JOIN
					".DB_PREFIX."product_combination AS proCom 
					ON proCom.productID=product.productID 
					AND proCom.status='A'";
		}			
		$sql .= " 
				WHERE 1 ";	
			
		// if(isset($searchProductStatus) && $searchProductStatus!='')
		// {
			// $sql .= "
					// AND product.productStatus IN ('" . $searchProductStatus . "') ";
		// }
		if(!empty($searchCloneIds))
		{
			$sql .= "
					AND product.productID IN ('" . implode("','", $searchCloneIds) . "') ";
		 }		 
		if (isset($searchProductKeyword) && $searchProductKeyword != '')
		{
			$searchProductKeyword = $this->escape($searchProductKeyword);
			$sql .= " 	
					AND 
					(
						productDet.productTitle LIKE '%" . $searchProductKeyword . "%'
						OR productOptFull.optionSKU LIKE '%" . $searchProductKeyword . "%'
						OR proCom.combinationSKU LIKE '%" . $searchProductKeyword . "%'
					) ";		
		}
		if (isset($businessID) && $businessID!='')
		{
			$sql .= " 
					AND product.businessID = '" . $businessID . "' ";
		}
		if (!empty($businessKey) && $businessKey!='')
		{
			if(!empty($businessID))
			$sql .= " 	
					AND busDet.businessName='".$this->escape($businessKey) . "'";   
			else
			$sql .= " 	
					AND busDet.businessName LIKE '%" . $this->escape($businessKey) . "%' ";
		}
		if (isset($productDateBetween) && $productDateBetween!='')
		{
			$searchProductToDate	= $productDateBetween;
			$searchProductFromDate	= $productDateBetween;
		}
		if (isset($searchProductToDate) && $searchProductToDate!='')
		{
			$sql .= " 	
					AND DATE_FORMAT(product.productStartTime, '%Y-%m-%d') <= '" . $searchProductToDate . "' ";
		}
		if (isset($searchProductFromDate) && $searchProductFromDate!='')
		{
			$sql .= " 	
					AND 
					(
						product.productEndTime = '0000-00-00'
						OR DATE_FORMAT(product.productEndTime, '%Y-%m-%d') >=	'" . $searchProductFromDate . "'
					) ";
		}		
		if(isset($searchProductStatus) && $searchProductStatus!='')
		{
			$sql .= "
					AND product.productStatus='".$searchProductStatus."'";	
		}
		else if(isset($searchInventory) && $searchInventory=='nostock')
		{
			$sql .= " 
					AND product.productStatus='Active'";
		}
		else
		{
			$sql .= " 
					AND product.productStatus='Pending'";	
		}
		if($sortBy=="product.productEndTime")
		$sql .= " 
					AND product.productEndTime!='0000-00-00'";
		$sql .= " 	
				GROUP BY 
					product.productID ";		
		
		if (isset($sortBy) && isset($orderBy))
		{
			$sql .= " 
				ORDER BY 
					" . $sortBy . " " . $orderBy;
		}
		if (isset($limitTo))
		{
			$sql .= " 
				LIMIT 
					" . $limitFrom . "," . $limitTo . " ";
		}
		//echo $sql;exit;
		return $this->ObjDatabase->readValuesSlave($sql);
                
        }
        
        public function getProductOptionDetails($productID,$type='')
        {
             if($type=='') {
             $sql	= "SELECT 
                                    productOptionProductPrice AS productRegularPrice
                           FROM 
                                    " . DB_PREFIX . "product_options        
                           WHERE 
                                    productID=".$productID." AND productOptionDefault='Yes' AND productOptionStatus='Active'";
            //echo $sql;exit;
             } else {
                 $sql	= "SELECT prodOpt.productOptionProductPrice AS productRegularPrice
                              FROM 
                                       " . DB_PREFIX . "product_options prodOpt inner join " . DB_PREFIX . "products product ON (product.productID=prodOpt.productID)
                              inner join " . DB_PREFIX . "business bus ON (bus.businessID=product.businessID) WHERE 
                                       prodOpt.productID=".$productID." AND prodOpt.productOptionDefault='Yes' AND prodOpt.productOptionStatus='Active' AND product.productStatus='Active' AND bus.businessStatus='Active' ";
             }
            
            $resProductRegPrice = $this->ObjDatabase->readValuesSlave($sql);
            return $resProductRegPrice;
        }
		
        public function getCategoryIdByProductId($productID,$type=''){
            $fld       = ($type!='') ? 'cat.productCategoryKey' : 'prodcat.productCategoryID';
            $sql       = "SELECT ".$fld."
                           FROM
                                    " . DB_PREFIX . "product_to_category prodcat inner join " . DB_PREFIX . "product_categories cat on (cat.productCategoryID=prodcat.productCategoryID)

                           WHERE
                                    prodcat.productID=".$productID." ";
            if($type!='') {
                $sql .= " and cat.productCategoryStatus='Active' order by cat.productCategorySortOrder asc limit 0,1";
               
                $catID = $this->ObjDatabase->getFieldSlave($sql);
                if($catID>0) {
                    $sql = "select * FROM " . DB_PREFIX . "product_categories where productCategoryStatus='Active' and productCategoryParent='".$catID."' order by productCategorySortOrder  asc Limit 0,1 ";
                    $res = $this->ObjDatabase->readValue($sql);
                    if(!empty($res)) {
                        return $res['productCategoryKey'];
                    } else {
                        return $catID;
                    }
                } 
            }
            return $this->ObjDatabase->getFieldSlave($sql);
            //echo $sql;exit
        }
        //END OF FUNCTION
	/* function to get the complete category ID of brands */
	public function listProductsUnderCategories($categoryID)
	{
	
		$sql = "
			SELECT  
				product.productID
			FROM  
					".DB_PREFIX."products AS product 
				INNER JOIN
					".DB_PREFIX."business AS bus
						ON	(
							product.businessID = bus.businessID 
							AND 
							bus.businessStatus = 'Active'
						)
				INNER JOIN  
					".DB_PREFIX."product_to_category AS productToCat  
						ON (
							product.productID = productToCat.productID
						)
			WHERE 
				product.productStatus = 'Active'  
				AND 
				productToCat.productCategoryID IN (" . $categoryID . ")
				GROUP BY product.productID
			";
		return $this->ObjDatabase->getFieldsSlave($sql);				
	}
	//END OF FUNCTION
        
        
        /* Function to get the complete brandID of particular category */
	public function listActiveBrandsUnderStore($params)
	{
		extract($params);
		$currentLanguageID = (isset($languageIDs['Content']) && $languageIDs['Content']>0)? $languageIDs['Content'] : $languageIDs['Current'];
		$now						=	date('Y-m-d');
		$hide_outofstock_products 	= 	false;
		if(isset($businessID) && $businessID!='')	{
			$sql = "
				SELECT 
					brand.brandID,
					brandDet.brandName,
					brand.brandKey
				FROM
					(
					SELECT  
						DISTINCT(product.brandID)
					FROM  
							".DB_PREFIX."products AS product 
						INNER JOIN
							".DB_PREFIX."business AS bus
								ON	(
									product.businessID = bus.businessID 
									AND 
									bus.businessStatus = 'Active'
								)
					WHERE 
						product.productStatus = 'Active'  
						AND 
						( 
							product.productStartTime <= '".$now."' 
							AND 
							( 
								product.productEndTime = '0000-00-00' 
								OR 
								product.productEndTime >=    '".$now."'
							) 
						)
						AND 
						product.businessID  =  ".$businessID."
					";
			if($hide_outofstock_products){
				$sql.="
						AND
							IF(product.InventoryTrackLevel ='No',
								1,
								IF(product.InventoryTrackLevel ='Product',
									(
										SELECT 
											sum(productOptionQuantity)
										FROM 
											" . DB_PREFIX . "product_options
										WHERE 
											productID=product.productID 
											AND 
											productOptionStatus='Active'
									),
									(
										SELECT 
											sum(combinationQty)
										FROM 
											" . DB_PREFIX . "product_combination as proCom1
										WHERE 
											proCom1.productID=product.productID									
											AND 
											proCom1.status='A'
									)
								)
							) > 0
				";
			}
			
			$sql.="	) AS products
					INNER JOIN
						".DB_PREFIX."brands AS brand
							ON	(
								brand.brandID =products.brandID
								AND
								brand.status='A'
							)
					INNER JOIN
						".DB_PREFIX."brand_details AS brandDet
							ON	(
								brand.brandID =brandDet.brandID
								AND
								brandDet.languageID='".$currentLanguageID."'
							)
			";
                        //echo $sql; exit;
			return $this->ObjDatabase->readValues($sql);
		}
		else{
			return array();
		}		
	}
        
        public function getOtherSellersInfo($languageIDs,$productID){
            $sqlSel       =   "SELECT buyboxID FROM ".DB_PREFIX."buybox_details WHERE productID = '".$productID."' 
                               ORDER BY buyboxDetailsID DESC LIMIT 1";
            $buyBoxID     =   $this->ObjDatabase->getField($sqlSel);
            
            $sqlSelect    =   "SELECT productID FROM ".DB_PREFIX."buybox_details WHERE buyboxID = '".$buyBoxID."'
                               AND productID != '".$productID."'";
            $result       =   $this->ObjDatabase->readValues($sqlSelect);
            
            $businessDet  =   array();
            $incrVal      =   0;
            if(count($result)>0){
                foreach($result as $res){
                    $sqlSelBus   =   "SELECT product.productID,
                                             product.businessID,
                                             prodOpt.productOptionRegularPrice,
                                             prodOpt.productOptionProductPrice,
                                             businessDet.businessName
                                             FROM ".DB_PREFIX."products product 
                                             INNER JOIN ".DB_PREFIX."product_options prodOpt ON
                                             (prodOpt.productID = product.productID AND 
                                             prodOpt.productOptionStatus = 'Active' AND 
                                             prodOpt.productOptionDefault = 'Yes')
                                             INNER JOIN ".DB_PREFIX."business business ON
                                             (business.businessID = product.businessID)
                                             INNER JOIN ".DB_PREFIX."business_details businessDet ON
                                             (businessDet.businessID = business.businessID
                                             AND businessDet.languageID = '".$languageIDs['Current']."')
                                             WHERE product.productID = '".$res['productID']."'";
                    $resBus       =   $this->ObjDatabase->readValue($sqlSelBus);
                    $businessDet[$incrVal] = $resBus;
                    $incrVal      =   $incrVal+1;
                }
            }
            return $businessDet;
        }
        
        public function selectUserWareHouse($userSelWareHouse){
            $this->userSelectedWareHouse = $userSelWareHouse;
        }
		
	public function getSoldQuantity($productID) 
	{
        $sql    = " SELECT 
						SUM(orderItemQuantity) AS soldCount 
					FROM 
						" . DB_PREFIX . "order_items ordIte
					LEFT JOIN 
						" . DB_PREFIX . "order_masters ordMas
						ON ordMas.masterOrderID=ordIte.masterOrderID 
					WHERE
						ordIte.productID='" . $productID . "' AND (ordMas.orderStatus='Process' OR ordMas.orderStatus='Completed')";						
        $result = $this->ObjDatabase->readValueSlave($sql);
        return $result['soldCount'];
    }
    public function getBodyStyles() {
        $sql = "select distinct(bodyStyle) FROM " . DB_PREFIX . "products order by bodyStyle asc";
        $result = $this->ObjDatabase->readValues($sql);
        return $result;
    }
	public function listAllProducts($languageIDs)
	{
		$sql 	= "SELECT 
					product.productID,
					productOpt.optionSKU,
					brandDet.brandName
					FROM 
						" . DB_PREFIX . "products	AS product 
					INNER JOIN 
						" . DB_PREFIX . "product_options AS productOpt 
						ON  productOpt.productID = product.productID
							AND productOpt.productOptionStatus  != 'Delete'
							AND productOpt.productOptionDefault = 'Yes'
					INNER JOIN 
							" . DB_PREFIX . "brand_details AS brandDet 
							ON (
								brandDet.brandID = product.brandID 
								AND
								brandDet.languageID = " . $languageIDs['Current'] . "
							)
					INNER JOIN 
							" . DB_PREFIX . "brands AS brand 
							ON (
								brand.brandID = product.brandID 
								AND 
								status!='D'
							)	
						";
		$sql .= " WHERE 1 ";				

		$sql .= " AND	product.productStatus!='Deleted' AND (brand.brandKey='bmw' OR brand.brandKey='bmi' OR brand.brandKey='mini')
				ORDER BY product.productID DESC ";	
		return $this->ObjDatabase->readValuesSlave($sql);		
	}
	public function listAllProductBasicDtls($params)
	{
		extract($params);
		$currentLanguageID	= $languageIDs['Current'];
		
		if(isset($type) && $type=='shortDesc') {
			$fields 	= "DISTINCT productShortDescription";
			
		} else if(isset($type) && $type=='variant') {
			$fields 	= "productDet.productTitle,
							productOpt.optionSKU,
							prodOptBus.productOptionRegularPrice AS productOptionRegularPrice,
							product.productID	";
		}
		$sql 	= "	SELECT 
							" . $fields . "
								
							
						FROM 
							" . DB_PREFIX . "products AS product 
						INNER JOIN 
							" . DB_PREFIX . "product_details AS productDet 
							ON (
								product.productID = productDet.productID 
								AND 
								productDet.languageID = " . $currentLanguageID . "
							)	
						INNER JOIN 
							".DB_PREFIX."product_options	AS productOpt 
								ON (
									product.productID = productOpt.productID
									AND
									productOpt.productOptionStatus = 'Active' 
									AND 
									productOpt.productOptionDefault = 'Yes'
								) 
					   LEFT JOIN
						   ".DB_PREFIX."products_options_business AS prodOptBus
								   ON      (
										   prodOptBus.productOptionID = productOpt.productOptionID
										   AND prodOptBus.businessOptionDefault='Yes'
									)
					" ;
		if(($categoryID !='' &&  $categoryID))
		{	
			$sql 	.= "
				LEFT JOIN 
				" . DB_PREFIX . "product_to_category   	AS productToCat 
					ON (product.productID = productToCat.productID) 
					";
		}
		$sql 	.= "WHERE
						1  
					AND 
						product.productTypeID = '1'  
					AND 
						product.productStatus = 'Active' ";
		if(isset($categoryID) && $categoryID!='' && $categoryID!='0')
		{
			$sql 	.= "
				AND 
					productToCat.productCategoryID IN (" . $categoryID . ") ";
		}
		if(isset($shortDesc) && $shortDesc!='')
		{
			$sql 	.= "
				AND 
					productShortDescription = '" . $shortDesc . "' ";
		}
		$sql 	.= "GROUP BY product.productID ";
                if($orderby!=''){
                    $sql .= "	ORDER BY ".$orderby."  ".$sortby." ";
                }else{
                    $sql .= "	ORDER BY product.productID  DESC ";
                }
				
	//echo $sql;
	return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
	}
        public function testDriveProductModels($params)
	{
		extract($params);
		$currentLanguageID              = $languageIDs['Current'];
                $isProductDeal			= isset($isProductDeal)  ? $isProductDeal : 'No';
                $freeProducts			= isset($freeProducts)  ? $freeProducts : '';
                $freeProdCount			= isset($freeProdCount)  ? $freeProdCount : 0;
		$limitFrom			= (isset($limitFrom) && $limitFrom >0) ? $limitFrom : 0;
		$currDate			= (isset($currDateTime) && $currDateTime!= "") ? "'" . $currDateTime . "'" : "'" . date('Y-m-d') . "'";
		$currTime			= date('Y-m-d H:i:00');
		$customWhere                    = ' AND productStatus != "Buybox" ';
		$categoryID			= isset($categoryID)?$categoryID:'';
		$customOrderBy                  = isset($customOrderBy)?$customOrderBy:'';
                $backcustomOrderBy              = isset($customOrderBy)?$customOrderBy:'';
                $customGroupBy                  = isset($customGroupBy)?$customGroupBy:'';
                $bodyStyle                      = isset($bodyStyle)?$bodyStyle:'';
                $bmwCode                        = isset($bmwCode)?$bmwCode:'';
                $custom                         = isset($custom)?$custom:'';
                $custCode                       = isset($custCode)?$custCode:'';
                $frontEnd                       = isset($frontEnd)?$frontEnd:'Y';
                $categoryIDs                    = isset($categoryIDs)?$categoryIDs:'';
                
                if($businessID>0){
                    $sqlProdBusinessID          = "prodOptBus.businessID,";
                    $sqlProdOptBusiness         = " AND prodOptBus.businessID='".$businessID."'";
                }else{
                    if($freeProducts!="" && $freeProdCount>0){
                        $sqlProdBusinessID          = "prodOptBus.businessID,";
                        $sqlProdOptBusiness         = " AND (IF(product.InventoryTrackLevel='No',
                                                                                prodOptBus.businessOptionDefault='Yes',
                                                                                prodOptBus.productOptionQuantity>='".$freeProdCount."'))";
                    }else{
                        $sqlProdBusinessID          = "prodOptBus.businessID,";
                        //$sqlProdOptBusiness         = " AND prodOptBus.businessOptionDefault='Yes'";
                        $sqlProdOptBusiness         = " AND (IF(product.InventoryTrackLevel='No',
                                                                                prodOptBus.businessOptionDefault='Yes',
                                                                                prodOptBus.productOptionBusinessID= (SELECT productOptionBusinessID FROM " . DB_PREFIX . "products_options_business
                                                              WHERE productOptionID = productOpt.productOptionID
                                                              AND productOptionQuantity>0
                                                              ORDER BY FIELD (businessOptionDefault,'Yes','No')
                                                              LIMIT 1)))";
                    }
                }
               
                
		switch($customOrderBy) {
			case "featured":
				$customOrderBy	= "	FIELD(product.featuredStatus,'Yes',''),
									product.productID DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC ";
				break;
			case "best_sellers":
				$customOrderBy	= "	purchasePopularity DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC,
									FIELD(product.featuredStatus,'Yes','') ";
				break;
			case "spcl_offers":
                                if($isProductDeal=='Yes') {
                                    $customOrderBy	= " dealDiscount DESC,
									product.productEndTime ASC,
									product.productID DESC,
									FIELD(product.featuredStatus,'Yes','') ";
                                } else {
                                    $customOrderBy	= "	productOptionOfferDiscount DESC,
									product.productEndTime ASC,
									product.productID DESC,
									FIELD(product.featuredStatus,'Yes','') ";
                                }
				break;
			case "new_products":
				$customOrderBy	= "	product.productID DESC ";
				break;
			case "end_soon":
				$customOrderBy	= "	product.productEndTime ASC ";
				break;
			case "popularity":
				$customOrderBy	= "	popularity DESC,
									productOptionOfferDiscount DESC,
									product.productEndTime ASC,
									FIELD(product.featuredStatus,'Yes','') ";	// modify as required. now it's best_sellers
				break;	
			case "price_asc":
				$customOrderBy	= " offerPrice ASC ";
				break;
			case "price_desc":
				$customOrderBy	= " offerPrice DESC ";
				break;	
			case "recentViewed":  
				$customOrderBy	= " FIELD(product.productID,".implode(",",$includeProductIDs).")";
				break;	
                        case "custom":  
				$customOrderBy	= " -menIteProPri.product_sort_order DESC,product.productID";
				break;    
			default:
                            if (isset($menuItemID) && $menuItemID > 0) {
                                $customOrderBy = " -menIteProPri.product_sort_order DESC,product.productID ";
                            }else {
                                $customOrderBy = " product.productID ";
                            }
				break;
                            
		}
		if(isset($returnSql) && $returnSql){
			$SQL_CALC_FOUND_ROWS = '' ; 
		}
		else{
			$SQL_CALC_FOUND_ROWS = 'SQL_CALC_FOUND_ROWS';
		}
                /* for Attribute search*/
		$attrSearch                     =   1;
		$attrOptionIDs                  =   '';
		$attrQuerySearch                =   1;
		$attrQuerySearchOptionIDs       =   '';               
		if(isset($attrDataObj) && $attrDataObj!='' && !empty($attrDataObj))
		{
			$sqlAttr = "select prod.productID from " . DB_PREFIX . "products prod inner join " . DB_PREFIX . "product_to_category cat on (cat.productID=prod.productID) where prod.productStatus='Active' ";
			if(isset($categoryID) && $categoryID!='' && $categoryID!='0')
			{
					$sqlAttr 	.= "    AND cat.productCategoryID IN (" . $categoryID . ") ";
			}                        
			foreach($attrDataObj as $attrData)
			{
						$attrOption = "'".implode("','",$attrData)."'";

						$sqlAttr 	.= " And prod.productID IN
								(
										SELECT
												productID
										FROM
												".DB_PREFIX."product_attribute_values
										WHERE
												attrOptionID IN(".$attrOption.")) ";                               

			}
			$sqlAttr 	.= " group by prod.productID ";
			$resAttr        =  $this->ObjDatabase->readValuesSlave($sqlAttr,MYSQLI_ASSOC);
			if(!empty($resAttr)) {                            
			   $prodArr = array();
			   foreach($resAttr as $pIDs) {
				   $prodArr[] = $pIDs['productID'];
			   }                           
			   $attrOptionIDs = "'".implode("','",$prodArr)."'";                            
			} else {
				$attrSearch = 0;    
				}
			}                    
			if(isset($attrQueryStringArray) && !empty($attrQueryStringArray))
			{

				$sqlAttr = "select prod.productID from " . DB_PREFIX . "products prod inner join " . DB_PREFIX . "product_to_category cat on (cat.productID=prod.productID) where prod.productStatus='Active' ";
				if(isset($categoryID) && $categoryID!='' && $categoryID!='0')
				{
						$sqlAttr 	.= "    AND cat.productCategoryID IN (" . $categoryID . ") ";
				}                        
				foreach($attrQueryStringArray as $attrData)
				{
							$attrOption = "'".implode("','",$attrData)."'";

							$sqlAttr 	.= " And prod.productID IN
									(
											SELECT
													productAttrVal.productID
											FROM
													".DB_PREFIX."product_attribute_values productAttrVal
											WHERE
													productAttrVal.attrOptionID IN(".$attrOption.")) ";                                   
				} 
				$sqlAttr 	.= " group by prod.productID ";
				$resAttr        =  $this->ObjDatabase->readValuesSlave($sqlAttr,MYSQLI_ASSOC);
				if(!empty($resAttr)) {                            
				   $prodArr = array();
				   foreach($resAttr as $pIDs) {
					   $prodArr[] = $pIDs['productID'];
				   }                           
				   $attrQuerySearchOptionIDs = "'".implode("','",$prodArr)."'";                            
				} else {
					$attrQuerySearch = 0;    
				}	

			}
			if($attrQuerySearch<=0 || $attrSearch<=0) {
                    return array();
            }
		/* End */
		$sql	= "
				SELECT 
					".$SQL_CALC_FOUND_ROWS."
					product.productID, ";
               
		$sql.=              " 
					productDet.productTitle,
					productDet.productDescription, 
                                        product.bodyStyle,
                                        IF(product.InventoryTrackLevel ='No',
						1,
						IF(product.InventoryTrackLevel ='Product',
							(
								SELECT 
									sum(pdtOptBus.productOptionQuantity)
								FROM 
									" . DB_PREFIX . "products_options_business pdtOptBus
                                                                INNER JOIN " . DB_PREFIX . "product_options pdtOpt ON
                                                                     (pdtOpt.productOptionID = pdtOptBus.productOptionID)
								WHERE 
									pdtOpt.productID=product.productID
                                                                        AND pdtOptBus.busProductStatus = 'Active'
                                                                        AND pdtOpt.productOptionStatus = 'Active'
							),
							(
								SELECT 
									sum(combinationQty)
								FROM 
									" . DB_PREFIX . "product_combination as proCom1
								WHERE 
									proCom1.productID=product.productID									
									AND 
									proCom1.status='A'
							)
						)
					)
					as productOptionQuantitySum,
					 ";
                                
                if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {
                    $sql	.=    " prodcatdet.productCategoryName";
                }                      
                              

		$sql	.=      " FROM 
					" . DB_PREFIX . "products AS product 
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " . $currentLanguageID . "
						)
				LEFT JOIN
					" . DB_PREFIX . "attribute_set AS attrSet
					ON attrSet.attributeSetID = product.attributeSetID";
				
		if(($categoryID !='' &&  $categoryID))// || (isset($searchKeyword) && $searchKeyword!='')
		{	
			$sql 	.= "
				LEFT JOIN 
				" . DB_PREFIX . "product_to_category   	AS productToCat 
					ON (product.productID = productToCat.productID) 
					";
		} elseif( !empty($categoryIDs)) {
                    $sql 	.= "
				LEFT JOIN 
				" . DB_PREFIX . "product_to_category   	AS productToCat 
					ON (product.productID = productToCat.productID) 
					";
                }
                if($selectedProductID!='' && $freeProducts!='') {
                    $sql 	.= "
				INNER JOIN 
				" . DB_PREFIX . "product_link_free   	AS productFree 
					ON (product.productID = productFree.freeProductID) 
					";
                }

//		if(isset($searchKeyword) && !empty($searchKeyword)){
//			$sql 	.= " 
//				LEFT JOIN
//					" . DB_PREFIX . "product_category_details	AS productCatDet 
//						ON (
//							productToCat.productCategoryID = productCatDet.productCategoryID
//							AND 
//							productCatDet.languageID = " . $currentLanguageID . "
//						)";
//			}
               	
	
		
		$sql.="      LEFT JOIN 
				".DB_PREFIX."product_options	AS productOpt 
					ON (
						product.productID = productOpt.productID
						AND
						productOpt.productOptionStatus = 'Active' 
						AND 
						productOpt.productOptionDefault = 'Yes'
					) 
			LEFT JOIN
					".DB_PREFIX."products_options_business AS prodOptBus
						ON	(
							prodOptBus.productOptionID = productOpt.productOptionID
							$sqlProdOptBusiness
						)
			LEFT JOIN
				".DB_PREFIX."variant_offer_price AS varOffPri
					ON	(
						varOffPri.productOptionID = productOpt.productOptionID
						AND
						varOffPri.userGroupID='".$userGroupID."'
                                                AND varOffPri.businessID = prodOptBus.businessID
					)	
			LEFT JOIN
				".DB_PREFIX."variant_group_price AS varGroPri
					ON	(
						varGroPri.productOptionID = productOpt.productOptionID
						AND
						varGroPri.userGroupID='".$userGroupID."'
                                                AND varGroPri.businessID = prodOptBus.businessID
					)	
			LEFT JOIN
				" . DB_PREFIX . "brands AS bra
					ON	(
						bra.brandID=product.brandID 
					)
			LEFT JOIN
				" . DB_PREFIX . "brand_details AS braDet
					ON	(
						braDet.brandID=bra.brandID 
						AND 
						braDet.languageID=" . $currentLanguageID . "
					)
			
			";
                    if($frontEnd!='') {
                        $sql.=              " LEFT JOIN " . DB_PREFIX . "vehicle_images AS veh_product
                                            ON (veh_product.variantCode = SUBSTRING_INDEX(productOpt.optionSKU,'-',1) and veh_product.imageType='png')";
                    }    
                    if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {
                        if($customGroupBy=='bodyStyle') {
                            $sql 	.= " LEFT JOIN " . DB_PREFIX . "product_to_category productToCat on (productToCat.productID=product.productID)";
                        } 
                        $sql 	.= " LEFT join " . DB_PREFIX . "product_categories prodcat on (prodcat.productCategoryID=productToCat.productCategoryID )
                                    LEFT JOIN " . DB_PREFIX . "product_category_details AS prodcatdet
					ON	(
						prodcatdet.productCategoryID=prodcat.productCategoryID 
						AND 
						prodcatdet.languageID=" . $currentLanguageID . "
					)
                                    ";

                    }    
                    if(isset($businessID) && $businessID > 0 ){
                            $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."business 	AS bus 
                                            ON (
                                                    prodOptBus.businessID = bus.businessID 
                                                    AND 
                                                    bus.businessStatus = 'Active'
                                                    AND
                                                    bus.businessID='".$businessID."')";
                    }else{
                        $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."business 	AS bus 
                                            ON (
                                                    product.businessID = bus.businessID 
                                                    AND 
                                                    bus.businessStatus = 'Active')";
                    }
			
		if (isset($menuItemID) && $menuItemID > 0 || $backcustomOrderBy=='custom') {
                    $sql .= "
                                        LEFT JOIN
                                                " . DB_PREFIX . "menu_item_product_priority AS menIteProPri
                                                        ON (
                                                                menIteProPri.product_id=product.productID 
                                                                AND 
                                                                menuItemID='" . $menuItemID . "'
                                                        )
                                                        ";
                }   
                if(!empty($custCode)) {
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_link 	AS prodcustlink 
                                            ON (
                                                    prodcustlink.productID = product.productID 
                                                   )";
                    $sql 	.= "
                            INNER JOIN 
                                    ".DB_PREFIX."product_custom_option_variant 	AS prodCustVar 
                                            ON (
                                                    prodCustVar.customOptionID = prodcustlink.customOptionID 
                                                   )";
                }

		$sql	.= " 
			WHERE
					1 ".$customWhere;
					
		if(isset($productType['productTypeID']) && $productType['productTypeID'] > 0)
		{
			$sql	.= "
				AND 
					product.productTypeID = '".$productType['productTypeID']."' ";
		}

                if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {

                    $sql	.= " AND prodcat.productCategoryStatus='Active' ";
                }
		if(!empty($bodyStyle)) {
                   
                    $bodyStyle   =  "'".implode("','",$bodyStyle)."'";
                    $sql	.= " AND  product.bodyStyle IN (".$bodyStyle.")";
                   
                }
                if(!empty($custCode)) {
                    
                    $custCode   =  "'".implode("','",$custCode)."'";
                    $sql	.= " AND  prodCustVar.variantCode IN (".$custCode.")";
                    
                }
                if(!empty($bmwCode)) {
                    $sql	.= " AND  productOpt.bmwCode='".$bmwCode."'";
                }
                
		if (isset($previewOnly) && $previewOnly == "Yes")
		{
			$sql 	.= "	
				AND 
					product.productID = '" . $selectedProductID . "'
				AND 
					product.productStartTime = " . $currDate . " ";
		}
		else
		{
                    if($showpage==''){
			if(isset($isProductDeal) && $isProductDeal=='No'){
			$sql 	.= "
				AND 
					product.productStatus = 'Active' 
				
			";
			}
			else{
				$sql 	.= "
				AND 
					product.productStatus = 'Active' ";
                                
			}
                    }
                    else{
                       
                    $sql 	.= " AND 
					(product.productStatus = 'Active' OR 
                         product.productStatus = 'Buybox' )
                         ";
                
                    }
		}
		
		if(isset($exceptProductIDs) && count($exceptProductIDs) > 0)
		{
			$sql	.= "
				AND 
					product.productID NOT IN ('" . implode("','",$exceptProductIDs) . "') ";
		}
		
		if($selectedProductID!='' && $freeProducts!='') {
                    $sql 	.= " AND productFree.productID ='".$selectedProductID."'";
                }
		if(isset($includeProductIDs) && count($includeProductIDs) > 0)
		{
			$sql	.= "
				AND 
					product.productID IN ('" . implode("','",$includeProductIDs) . "') ";
		}
		
		if(isset($brandID) && $brandID>0){
			$sql 	.= "
				AND 
					(bra.brandID ='".$brandID."' || bra.parentID ='".$brandID."')";
		}
		if(isset($brandIDs) && $brandIDs!='' && !empty($brandIDs)){
                    if($custom!='') {
                        $sql 	.= "
				AND 
					product.brandID IN (".$brandIDs.")";
                    } else {
                        $sql 	.= "
				AND 
					product.brandID IN ('".implode("','",$brandIDs)."')";
                    }
			
		}
		
		
		if(isset($categoryID) && $categoryID!='' && $categoryID!='0')
		{
			$sql 	.= "
				AND 
					productToCat.productCategoryID IN (" . $categoryID . ") ";
		}
                else {
                    if(!empty($categoryIDs))
                    {
                            $categoryIDs   =  "'".implode("','",$categoryIDs)."'";
                            $sql 	.= "
                                    AND 
                                            productToCat.productCategoryID IN (" . $categoryIDs . ") ";
                    }
                }
		if($attrSearch>0 && $attrOptionIDs!='') {                         
			$sql	.= " AND product.productID IN (" . $attrOptionIDs . ") ";
		}
		if($attrQuerySearch>0 && $attrQuerySearchOptionIDs!='') {                         
			$sql	.= " AND product.productID IN (" . $attrQuerySearchOptionIDs . ") ";
		}   
		if(isset($priceRangeMin) && $priceRangeMin >= 0 && $priceRangeMax > 0)
		{
			$sql	.= "
				AND 
					LEAST(
						COALESCE(
							varOffPri.offerPrice,
							prodOptBus.productOptionProductPrice
						),
						COALESCE(
							varGroPri.specialPrice,
							prodOptBus.productOptionProductPrice
						) 
					) BETWEEN ".$priceRangeMin." AND ".$priceRangeMax;
		}
                if(isset($emiRangeMin) && $emiRangeMin >= 0 && $emiRangeMax > 0)
		{
			$sql	.= " AND prodOptBus.monthlyInstallment BETWEEN ".$emiRangeMin." AND ".$emiRangeMax;
		}
		if(isset($discountRange) && $discountRange > 0)
		{
			$sql	.= "
				AND 
					ROUND(
						(
							(
								(
								productOpt.productOptionRegularPrice-
									LEAST(
										COALESCE(
											varOffPri.offerPrice,
											prodOptBus.productOptionProductPrice
										),
										COALESCE(
											varGroPri.specialPrice,
											prodOptBus.productOptionProductPrice
										) 
									)
								)*100
							)/productOpt.productOptionRegularPrice
						),2
					) >= ".$discountRange;
		}
		
		if(isset($searchKeyword) && !empty($searchKeyword)){

			$productTitle			=	array();
			$productCategoryName	=	array();
			$searchTags			=	array();
			$productOptionName		=	array();
			foreach($searchKeyword as $keyword){
				$keyword=$this->escape($keyword);
				$productTitle[]		=	" productDet.productTitle LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				//$productCategoryName[]	=	" productCatDet.productCategoryName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$searchTags[]		=	" productDet.searchTags LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
				$productOptionName[]	=	" productOptDet2.productOptionName LIKE '%".$keyword."%' COLLATE utf8_general_ci ";
			}
			$sql.= " 
				AND (
					(".implode(" OR ",$productTitle ).")					
					OR
					(".implode(" OR ",$searchTags ).")
					OR EXISTS (
						SELECT productOpt2.productID
						FROM 
							".DB_PREFIX."product_options			AS productOpt2
						LEFT JOIN 
							".DB_PREFIX."product_option_details	AS productOptDet2
								ON (
								productOpt2.productOptionID = productOptDet2.productOptionID
								AND
								productOptDet2.languageID = '".$currentLanguageID."'
							)
						WHERE
							productOpt2.productID = product.productID
							AND
							(".implode(" OR ",$productOptionName ).")
							AND
							productOpt2.productOptionStatus = 'Active'
					)
				) ";
		}
		$selectedProductID	= (isset($selectedProductID) && $selectedProductID >0) ? $selectedProductID : 0;
		/*if((isset($categoryID) && $categoryID!='')){ // || (isset($searchKeyword) && $searchKeyword!='')
                   
                    if($customGroupBy=='bodyStyle') {
                        $sql 	.= " GROUP BY product.$customGroupBy";
                    } elseif($customGroupBy=='optionSku' || $customGroupBy=='bmwCode' ) {
                        $sql 	.= " GROUP BY productOpt.$customGroupBy";
                    } else {
                         $sql 	.= " GROUP BY product.productID";
                    }
		} else {
                    if($customGroupBy=='bodyStyle') {
                        $sql 	.= " GROUP BY product.$customGroupBy";
                    } elseif($customGroupBy=='optionSku' || $customGroupBy=='bmwCode' ) {
                        $sql 	.= " GROUP BY productOpt.$customGroupBy";
                    }
                }*/
                if($customGroupBy=='bodyStyle') {
                   $sql 	.= " GROUP BY productToCat.productCategoryID,product.bodyStyle";
                } elseif($customGroupBy=='optionSku' || $customGroupBy=='bmwCode' ) {
                    $sql 	.= " GROUP BY productOpt.$customGroupBy";
                } else {
                    $sql 	.= " GROUP BY product.productID";
                }
                if($selectedProductID!='' && $freeProducts!='') {
                    $sql.="ORDER BY productFree.priority,".$customOrderBy.",productOptionQuantitySum DESC";
                            
                } else { 

                    if($customGroupBy=='bodyStyle' || !empty($categoryID) || !empty($categoryIDs)) {

                        $sql.=" ORDER BY prodcat.productCategorySortOrder asc,
				FIELD(product.productID,".$selectedProductID.") DESC,				
				".$customOrderBy.",
				productOptionQuantitySum DESC";
                    } else {
                        $sql.="
			ORDER BY 
				FIELD(product.productID,".$selectedProductID.") DESC,				
				".$customOrderBy.",
				productOptionQuantitySum DESC";
                    }
                    
                }
		if (isset($limitTo) && $limitTo >= '0')
		{
			$sql .= " LIMIT  " . $limitFrom . "," . $limitTo . " ";
		}
               
                
               //echo $sql;exit;
		/* if true for brand category wise listing */
		if(isset($returnSql) && $returnSql){
			return $sql;
		}else{
			return $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
		}
	}
        public function getProductShortDescKey($key,$languageIDs) {
            $currentLanguageID = $languageIDs['Current'];
            $sql = "select productShortDescription from " . DB_PREFIX . "product_details where productShortDescKey='".$this->escape($key)."' and languageID='".$currentLanguageID."' ";
            $result = $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
            return $result['productShortDescription'];
        }
		public function saveLoanDetails($POST) {
	  $subtitle			 = 	isset($POST['subtitle'])?$this->escape($POST['subtitle']):'';
	  $firstname	     = 	isset($POST['firstname']) ? $this->escape($POST['firstname']) : '';
	  $lastname		 	 = 	isset($POST['lastname']) ? $this->escape($POST['lastname']) : '';
	  $phone			 = 	isset($POST['phone']) ? $this->escape($POST['phone']) : '';
	  $email			 = 	isset($POST['emailID']) ? $this->escape($POST['emailID']) : '';
	  $location			 = 	isset($POST['location']) ? $this->escape($POST['location']) : '';
	  $employmenttype	= 	isset($POST['employmenttype']) ? $this->escape($POST['employmenttype']) : '';
	  $monthlyincome	= 	isset($POST['monthlyincome']) ? $this->escape($POST['monthlyincome']) : '';
	  $totalcreditcardlimit	= 	isset($POST['totalcreditcardlimit']) ? $this->escape($POST['totalcreditcardlimit']) : '';
	  $loanmonthlyPayment	= 	isset($POST['loanmonthlyPayment']) ? $this->escape($POST['loanmonthlyPayment']) : '';
	  $productID	= 	isset($POST['productID']) ? $this->escape($POST['productID']) : '';
	  $brandID	= 	isset($POST['brandID']) ? $this->escape($POST['brandID']) : '';
	  $productTitle	= 	isset($POST['productTitle']) ? $this->escape($POST['productTitle']) : '';
            $sql = "
				INSERT INTO `" . DB_PREFIX . "loan_details`
                    (
					`subtitle`,
					`firstname`, 
					`lastname`, 
					`phone`,
					`emailID`,
					`location`,
                    `employmenttype`,
					`monthlyincome`,
					`totalcreditcardlimit`,
					`loanmonthlyPayment`,
					`productID`,
					`vehicleTitle`,
					`brandID`,
					`createdDate`
					)
				VALUES
					(
					'" . $subtitle . "',
					'" . $firstname . "',
					'" . $lastname . "',
					'" . $phone . "',
					'" . $email . "',
					'" . $location . "',
                    '".	$employmenttype ."',
                    '".	$monthlyincome ."',
                    '".	$totalcreditcardlimit ."',
                    '".	$loanmonthlyPayment ."',
					'". $productID ."',
					'". $productTitle ."',
					'". $brandID ."',
					NOW()
					)";
            $this->ObjDatabase->setQuery($sql);
            $loanID = $this->ObjDatabase->getInsertId();
        
        return $loanID;
	}
	public function listLoanDetails($limitFrom,$limitTo,$languageIDs,$searchName='',$searchEmail='',$brandID='',$export=''){
		  $sql = "	
					SELECT ";
		/* if ($limitTo)
		{ */
			$sql .= " 	
						SQL_CALC_FOUND_ROWS ";
		/*}*/
                $sql .= " loanDet.*,brandDet.brandName FROM " . DB_PREFIX . "loan_details AS loanDet ";
										
				$sql .= " LEFT JOIN 
						" . DB_PREFIX . "brands AS brand
							ON	(
								brand.brandID = loanDet.brandID
							)                     
                                        LEFT JOIN 
						" . DB_PREFIX . "brand_details AS brandDet
							ON	(
								brandDet.brandID = brand.brandID
								AND
								brandDet.languageID = '" . $languageIDs['Current'] . "'
							)   ";  						
										
				$sql .= " WHERE 1 ";
				if($searchName){
                      $sql .= " AND loanDet.firstname like '%" . $this->escape($searchName) . "%'
                      OR loanDet.lastname like '%" . $this->escape($searchName) . "%'";
                }
                if($searchEmail){
                      $sql .= " AND loanDet.emailID like '%" . $this->escape($searchEmail). "%'";
                }
				if($brandID!=''){
                      $sql .= " AND loanDet.brandID = '" . $brandID . "'";
                }
				$sql .= " ORDER BY loanDet.loanID DESC";
                if ($limitTo)
		{
			$sql .= " 
					LIMIT  
						" . $limitFrom . "," . $limitTo . "";
		}
		
		if ($export) {
            return $this->ObjDatabase->readValuesObject($sql, MYSQLI_ASSOC);
        }
            // echo $sql;exit;
                $result= $this->ObjDatabase->readValues($sql);
                return $result;
	}
	public function getProducturlfromID($productID)
	{
	 $sql="SELECT 
		productUrl 
			FROM 
				" . DB_PREFIX . "products
						WHERE 
						productID=".$productID." ";
		return $this->ObjDatabase->getField($sql);
	}
}

