<?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 WITH THIS DISTRIBUTION PACKAGE.            *
 ****************************************************************************/ //
//$Id: DevModel.php, v 1.0 2013/03/14 14:14:14
class SearchModel extends Model
{
    //Function to get admin details
    function listProducts($params=array())
    {
		extract($params);
                $updateToLang   =   isset($updateToLang)&&$updateToLang>0 ? $updateToLang : 1;
		$currTime           = date('Y-m-d H:i:s');
		$sql = "
			SELECT 
				product.productID AS objectID,
				product.productID,
				product.inventoryTrackLevel,
				product.productTypeID,
				product.brandID,
				product.businessID,
				product.productUrl,
				product.attributeSetID,
				product.productImage,
				product.productStatus,                                 
				proDet.productTitle,
                                bus.businessStatus, 
				busDet.businessName AS business,
				bra.brandKey,
				brandDet.brandName  AS brand,
				productOpt.productOptionID,
				productOpt.optionSKU,
				productOpt.productOptionProductPrice,
				productOpt.productOptionRegularPrice,
				productOpt.productOptionQuantity,
				productOpt.productQuantityUnlimited,
                                product.inventoryTrackLevel,
                                product.freeProductsCount,
				LEAST(
					COALESCE(
						varOffPri.offerPrice,
						productOpt.productOptionProductPrice
					),
					COALESCE(
						varGroPri.specialPrice,
						productOpt.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.isProductDeal,product.dealDiscount,ROUND((productOpt.productOptionRegularPrice)-(productOpt.productOptionRegularPrice*(product.dealDiscount/100))) AS dealPrice
				,IF(product.isProductDeal ='Yes', product.dealDiscount, ROUND(
					(
						(
							(
							productOpt.productOptionRegularPrice-
								LEAST(
									COALESCE(
										varOffPri.offerPrice,
										productOpt.productOptionProductPrice
									),
									COALESCE(
										varGroPri.specialPrice,
										productOpt.productOptionProductPrice
									) 
								)
							)*100
						)/productOpt.productOptionRegularPrice
					),2
				)) AS productOptionDiscount
			FROM 
					".DB_PREFIX."products AS product
				INNER JOIN
					".DB_PREFIX."product_details AS proDet	
						ON	(
							product.productID = proDet.productID
							AND
							proDet.languageID ='".$updateToLang."'
						) 
				LEFT JOIN
					".DB_PREFIX."brands bra
						ON	(
							bra.brandID = product.brandID
						)
				LEFT JOIN
					".DB_PREFIX."brand_details brandDet
						ON	(
							brandDet.brandID = bra.brandID
							AND
							brandDet.languageID ='".$updateToLang."'
						)
                                INNER JOIN
					".DB_PREFIX."business bus
						ON	(
							bus.businessID = product.businessID
                                
						)                
				INNER JOIN
					".DB_PREFIX."business_details busDet
						ON	(
							busDet.businessID = product.businessID
							AND
							busDet.languageID ='".$updateToLang."'
						)
				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."variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='".$userGroupID."'
						)	
				LEFT JOIN
					".DB_PREFIX."variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='".$userGroupID."'
						)	
				
			WHERE
				1
			";/*(productStatus='Active' OR productStatus='Inactive')*/
			if(isset($productID) && $productID>0){
				$sql.="
				AND
				product.productID = '".$productID."'
				";
			}
			$sql.="
				ORDER BY 
					product.productID
				LIMIT 
					".($page*3000)." , 3000
			";
                      //  echo $sql;exit; 
        return $this->ObjDatabase->readValues($sql,MYSQLI_ASSOC);
        
    } 
	
	function getReviews($params=array()){
		extract($params);
		$sql="
			SELECT 
				productRating 
			FROM
				".DB_PREFIX."product_reviews
			WHERE
				productID ='".$productID."'
				AND
				languageID = '1'
		";
		return $this->ObjDatabase->getFieldsSlave($sql);
		
		
		
	}
        public function fetchProductReviews($params=array())
	{
                 extract($params);
		 $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;
	}
	function listStoreProducts($params=array()){
		extract($params);
		$sql="
			SELECT
				proToWebSto.price,
				proToWebSto.sales_price ,
				COALESCE(
					proOffPri.offer_price,
					proToWebSto.sales_price
				)
				AS offerPrice,
				ROUND(
					(
						(
							(
							proToWebSto.price-
							COALESCE(
								proOffPri.offer_price,
								proToWebSto.sales_price
							)
							)*100
						)/proToWebSto.sales_price 
					),2
				) AS discount,
				COALESCE(
					proOffPri.usergroup_id,0
				) AS usergroup_id,
				proToWebSto.product_id,
				proToWebSto.store_id
			FROM
					".DB_PREFIX."product_to_website_store AS proToWebSto
				LEFT JOIN
					" . DB_PREFIX . "product_offer_price AS proOffPri
						ON	(
							proToWebSto.product_id = proOffPri.product_id
						)
			WHERE
				proToWebSto.product_id = '".$product_id."'
		";
		return $this->ObjDatabase->readValues($sql,MYSQLI_ASSOC);
		
	}
	
	function getOfferPrice($params=array()){
		extract($params);
		$sql="
			SELECT
				proToWebSto.price,
				COALESCE(
					proOffPri.offer_price,
					proToWebSto.sales_price
				)
				AS offerPrice,
				ROUND(
					(
						(
							(
							proToWebSto.price-
							COALESCE(
								proOffPri.offer_price,
								proToWebSto.sales_price
							)
							)*100
						)/proToWebSto.sales_price 
					),2
				) AS discount
			FROM
					".DB_PREFIX."product_to_website_store AS proToWebSto
				LEFT JOIN
					" . DB_PREFIX . "product_offer_price AS proOffPri
						ON	(
							proOffPri.usergroup_id ='".$usergroup_id."'
						)
			WHERE
				proToWebSto.product_id = '".$product_id."'
				AND
				proToWebSto.store_id = '".$store_id."'
			LIMIT 1
		";
		return $this->ObjDatabase->readValue($sql,MYSQLI_ASSOC);
		
	}
	
	
	
	
	function listCategoryNames($params=array()){
		extract($params);
                $updateCToLang = isset($updateCToLang) && $updateCToLang!='' ? $updateCToLang : 1;
		$sql="
			SELECT 
				CONCAT(proCatDet.productCategoryName,'~*',proCat.productCategoryKey,'~*',proCat.productCategoryID)  AS productCategoryName
			FROM 
					".DB_PREFIX."product_to_category proToCat
				INNER JOIN
					".DB_PREFIX."product_categories AS proCat
						ON(
						proToCat.productCategoryID = proCat.productCategoryID
						)
				INNER JOIN
					".DB_PREFIX."product_category_details AS proCatDet
						ON	(
							proCat.productCategoryID = proCatDet.productCategoryID
							AND
							proCatDet.languageID = ".$updateCToLang."
						)
			WHERE
				productID = '".$productID."'
		";
		return $this->ObjDatabase->getFields($sql);
		
		
	}
	
	function listAttributes($params=array()){
		extract($params);
		$sql="
			SELECT
				CASE 
					WHEN attributeKey= 'on_field' AND  attOptDet.attrOptName ='Yes' THEN 
						attrName
					ELSE
						attOptDet.attrOptName
				END 
			FROM 
					".DB_PREFIX."product_attribute_values AS proAttVal
				INNER JOIN
					".DB_PREFIX."attribute_option_details AS attOptDet
						ON	(
							proAttVal.attr_option_id = attOptDet.attr_option_id
							AND 
							attOptDet.language_id=1
						)
				INNER JOIN
					".DB_PREFIX."attributes AS att
						ON	(
							att.attribute_id = proAttVal.attribute_id
						)
				INNER JOIN
					".DB_PREFIX."attribute_details AS attDet
						ON	(
							att.attribute_id = attDet.attribute_id
							AND
							attDet.language_id = 1 
						)
			WHERE
				product_id = '".$product_id."'
		";		
		return $this->ObjDatabase->getFields($sql);
	}
	
	
	function listStores(){
		$sql="
			SELECT  
                `store_id` ,  
                `website_id` ,  
				`store_name`       
			FROM  
				".DB_PREFIX."website_stores AS store    
			WHERE
				store_status='Active'
		";
		return $this->ObjDatabase->readValues($sql,MYSQLI_ASSOC);
	}
	
	/* function listUsergroups($website_id){
		$sql="
			SELECT 
				useGro.userGroupID AS usergroup_id,
				userGroupName AS usergroup_name
			FROM 
					".DB_PREFIX."user_group useGro
				INNER JOIN
					".DB_PREFIX."user_group_details useGroDet
						ON	(
							useGro.userGroupID = useGroDet.userGroupID
							AND
							language_id= 1
						)
			WHERE 
				`website_id`='".$website_id."'
				AND
				userGroupStatus='Active'
		";
		return $this->ObjDatabase->readValues($sql,MYSQLI_ASSOC);
	} */
	
	function listWarehouses($store_id){
		$sql="
			SELECT 
				warehouse_id
			FROM 
				".DB_PREFIX."webstore_to_warehouse 
			WHERE  
				store_id='".$store_id."'
		";
		return $this->ObjDatabase->getFields($sql,MYSQLI_ASSOC);
	}
	
	function productInventory($params=array()){
		extract($params);
		$sql="
			SELECT ";
				
			if($enable_presale =='Yes'){
				$sql.="
				SUM(prodInv.quantity_available + prodInv.quantity_on_order + quantity_back_ordered )";
			}else{
				$sql.="
				SUM(prodInv.quantity_available - prodInv.quantity_back_ordered)";
			}
			$sql.="	
				AS availableQuantity 
			FROM
				" . DB_PREFIX . "product_inventory AS prodInv
			WHERE
				product_id ='".$product_id."'
				AND
				warehouse_id IN ('" . implode("','", $warehouse_ids) . "')
			GROUP BY 
				product_id
			LIMIT 1
			";
		return $this->ObjDatabase->getField($sql,MYSQLI_ASSOC);
		
	}
	
	function getProductCombinations($productId){
		$sql="
			SELECT  
                `combination`   
			FROM  
				".DB_PREFIX."product_combination AS pComb    
			WHERE
				(combinationQty >0 ||  combinationQty  = '') AND status = 'A' AND productID ='".$productId."'";
				
		return $this->ObjDatabase->readValues($sql,MYSQLI_ASSOC);
	}

	function getOptionVariantNames($variantID){
		$sql="	SELECT 	optionDet.customOptionName as optName,
						variantDet.value as varName	
				FROM 
						".DB_PREFIX."product_custom_option_variant as variant
				LEFT JOIN 
						".DB_PREFIX."product_custom_option_variant_details as variantDet 
				ON (
					variant.customOptionVariantID = variantDet.customOptionVariantID AND variantDet.languageID = '1'
				)
				
				LEFT JOIN ".DB_PREFIX."product_custom_option_details as optionDet 
				ON (
					variant.customOptionID = optionDet.customOptionID AND optionDet.languageID = '1'
				)
				
				WHERE variant.customOptionVariantID = '".$variantID."'";
				
			return $this->ObjDatabase->readValue($sql,MYSQLI_ASSOC);
	
	}
	
	function elastic_cron_execution_details(){
		$sql="
			SELECT 
				*,
				HOUR(TIMEDIFF(`executionTime`,NOW())) hour_diff
			FROM 
				" . DB_PREFIX . "crone_execution_details
			WHERE 
				cronType='elastic_search'
		";
		return $this->ObjDatabase->readValue($sql,MYSQLI_ASSOC);
	}
	
	function elastic_cron_execution_update($page=1){
		$sql="
			SELECT 
				COUNT(*) AS exist
			FROM 
				" . DB_PREFIX . "crone_execution_details
			WHERE 
				cronType='elastic_search'
		";
		$result = $this->ObjDatabase->readValue($sql,MYSQLI_ASSOC);
		if($result['exist']>0){
			$sql="
				UPDATE 
					" . DB_PREFIX . "crone_execution_details
				SET
					executionTime=NOW(),
					rowsAffected=0,
					lastAffectedID='".$page."'
				WHERE
					cronType='elastic_search'
			";
			
		}else{
			$sql="
				INSERT INTO	" . DB_PREFIX . "crone_execution_details
					(
					executionTime,
					rowsAffected,
					lastAffectedID,
					cronType
					)
				VALUES(
					NOW(),
					0,
					'".$page."',
					'elastic_search'
				)
			";
		}
		return $this->ObjDatabase->setQuery($sql);
	}

}