<?php 	
class ModelCatalogProduct extends Model {
	public function updateViewed($product_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int)$product_id . "'");
	}
	public function getProductMetal($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_metalcontent   WHERE product_id = '" . (int)$product_id . "' order by metal ");
		return $query->rows;
	}
	
	public function getProductMetalAvail($product_id,$metal) {
		$query = $this->db->query("SELECT a.*,b.*,b.image as image1 ,a.image as metal_image FROM " . DB_PREFIX . "product_metalavail a, metal b  WHERE product_id = '" . (int)$product_id . "' and metal = '" . (int)$metal . "' and a.metal_id=b.metal_id  order by sort_order	");
		return $query->rows;
	}
	
	/*
	public function getProductWeightCost($product_metalavail_id) {
		// Metal Weight Calculation
		$query1 = $this->db->query("SELECT  b.weight * c.pricepergram * c.silver_ratio  as weight FROM " . DB_PREFIX . "product_metalavail a , product_metalcontent b, metal c WHERE a.product_metalavail_id = '" . (int)$product_metalavail_id . "'  and a.metal=c.metal and a.metal_id = b.metal_id " );
		return $query->row['weight'] ;
	}
	*/
	public function getProductWeightCost_old($product_id) {
		// Metal Weight Calculation
		$query1 = $this->db->query("SELECT sum(weight) as metalweight FROM " . DB_PREFIX . "product_metalcontent a  WHERE product_id = '" . (int)$product_id . "'" );
		
		$query2 = $this->db->query("SELECT sum(weight * count)  as gemweight  FROM " . DB_PREFIX . "product_gemcontent a  WHERE product_id = '" . (int)$product_id . "' ");
		$totalweight = $query1->row['metalweight'] + $query2->row['gemweight'];
		$this->log->write("Metal Weight : " . $query1->row['metalweight'] . "; Gems Weight : " . $query2->row['gemweight']); 

		$query = $this->db->query("SELECT " . $totalweight . " * cost as labourweightcost,ccurrency as currency  FROM " . DB_PREFIX . "labour_weight_based_costs a  WHERE low_weight <'" .  $totalweight . "' and high_weight >='" .  $totalweight . "' ");
		
		return round($this->currency->convert($query->row['labourweightcost'],$query->row['currency'], $this->currency->getCode()),2);
	}
		public function getGemWeight($product_gemavail_id) {

			$query2 = $this->db->query("SELECT b.count , c.weight, (b.count * c.weight ) as gemWeight FROM " . DB_PREFIX . "product_gemavail a , product_gemcontent b, gemstone_weight c WHERE a.product_gemavail_id = '" . (int)$product_gemavail_id . "'  and a.product_id=b.product_id and a.gem=b.gem and b.size=c.size_code and b.shape_id=c.shape and a.gemstone_id = c.gemstone_id " );
			return $query2->row['gemWeight'] ;
					
		}
		public function getGemQuality($gemstone_id) {

			$query2 = $this->db->query("SELECT b.colour_descr as colour_descr ,c.clarity_descr as clarity_descr FROM " . DB_PREFIX . "colour_clarity a,colour b,clarity c  WHERE a.colour_id=b.colour_id and a.clarity_id=c.clarity_id and a.gemstone_id = '" . (int)$gemstone_id . "'" );
			return $query2->row['colour_descr'] . ' '  . $query2->row['clarity_descr']  ;
			
			//return 'InProgress';
					
		}
		
		public function getProductWeightCost($product_id) {
			$product_details = $this->session->data['product_details'];
			$metalWeight=0;
			$gemWeight=0;
			$totalweight=0;
			foreach ($product_details as $product_detail) {
				if($product_detail['type']=='M')	// Process the selected metal
			  	{
					$this->log->write("product_detail['value'] : " . $product_detail['value'] );
					$query1 = $this->db->query("SELECT b.weight , c.silver_ratio, b.weight * c.silver_ratio as metalWeight FROM " . DB_PREFIX . "product_metalavail a , product_metalcontent b, metal c WHERE a.product_metalavail_id = '" . (int)$product_detail['value'] . "' and a.product_id=b.product_id and a.metal=b.metal and a.metal_id = c.metal_id " ); 
					$this->log->write("Weight : " .$query1->row['weight'] . " ; Silver Ratio : " .$query1->row['silver_ratio']   );
					$metalWeight +=  $query1->row['metalWeight'];
				}else{									// if Metal Selected;
					$query2 = $this->db->query("SELECT b.count , c.weight, (b.count * c.weight / 5) as gemWeight FROM " . DB_PREFIX . "product_gemavail a , product_gemcontent b, gemstone_weight c WHERE a.product_gemavail_id = '" . (int)$product_detail['value'] . "'  and a.product_id=b.product_id and a.gem=b.gem and b.size=c.size_code and b.shape_id=c.shape and a.gemstone_id = c.gemstone_id " );
					$this->log->write("Gem Count : " .$query2->row['count'] . " ; Gem Weight : " .$query2->row['weight']   );
					$gemWeight += $query2->row['gemWeight'] ;
				}
			}
			$this->log->write("Metal Weight : " . $metalWeight . "; Gems Weight : " . $gemWeight ); 
			$totalweight = $metalWeight + $gemWeight ;

			$query = $this->db->query("SELECT " . $totalweight . " * cost as labourweightcost,cost,ccurrency as currency  FROM " . DB_PREFIX . "labour_weight_based_costs a  WHERE low_weight <'" .  $totalweight . "' and high_weight >='" .  $totalweight . "' ");
			$this->log->write("Labout Weight Based Cost : " . $query->row['labourweightcost'] . "; Currency : " . $query->row['currency'] . "; Cost : " . $query->row['cost'] ); 
	//		return round($this->currency->convert($query->row['labourweightcost'],$query->row['currency'], $this->currency->getCode()),2);
			return round($this->currency->convert($query->row['labourweightcost'],$query->row['currency'], $this->currency->getCode()),2); ;
	}
	
	
	public function getProductSettingCost($product_id) {
		$cost = 0;
		$query = $this->db->query("SELECT a.count * b.cost as settingcost,b.ccurrency,a.count,a.setting_id,b.cost as price  FROM " . DB_PREFIX . "product_gemcontent a,laboursettingcost b   WHERE a.setting_id = b.setting_id and a.product_id = '" . (int)$product_id . "'" );
		foreach ($query->rows as $setting) {
			$this->log->write( ' Setting Id : ' . $setting['setting_id'] . ' ; count : '. $setting['count']  . ' ; Currency : '. $setting['ccurrency']  . ' ; Price : '. $setting['price'] . ' ; Converted Price : ' . $this->currency->convert($setting['settingcost'],$setting['ccurrency'], $this->currency->getCode())  );
			$cost += $this->currency->convert($setting['settingcost'],$setting['ccurrency'], $this->currency->getCode());
		}
		return round($cost,2);
	}
	public function getProductMetalAvalability($product_id) {
		$query = $this->db->query("SELECT a.*,b.sort_order,b.description FROM " . DB_PREFIX . "product_metalavail a,metal b  WHERE a.metal_id=b.metal_id and a.product_id = '" . (int)$product_id . "' order by a.metal");
		return $query->rows;
	}
	
	public function getProductMetalCost($product_metalavail_id) {
		$cost=0;
		$sql = "SELECT a.pricepergram * c.weight * a.silver_ratio as metalcost,a.pricepergram,c.weight as weight,a.*,b.*,c.*  from `metal` a, product_metalavail b, product_metalcontent c WHERE a.metal_id = b.metal_id AND b.product_id = c.product_id AND b.metal = c.metal AND b.product_metalavail_id = '" . (int)$product_metalavail_id . "' ";
		//$this->log->write($sql); 

		$query = $this->db->query($sql);
		if ($query->num_rows) {
			$this->log->write( ' Metal : ' . $query->row['metal_code'] . ' ; Weight : '. $query->row['weight']  . ' ; Silver Ratio : '. $query->row['silver_ratio']   . ' ; Price Per Gram : '. $query->row['pricepergram']    . ' ; Currency : '. $query->row['ccurrency'] );
			$cost=$query->row['metalcost'];

		}else{
			$this->log->write($sql); 
			$this->log->write( ' $product_metalavail_id : ' . $product_metalavail_id . 'metalcost : '. ' pricepergram : '. 'weight : '   );
 		}
								

		return round($this->currency->convert($cost,$query->row['ccurrency'], $this->currency->getCode()),2);

		//return $cost;
	}

	public function getProductGemstoneCost($product_gemavail_id) {
		//$query = $this->db->query("SELECT a.pricepergram * c.weight as metalcost FROM metal a, product_metalavail b, product_metalcontent c WHERE a.metal_id = b.metal_id AND b.product_id = c.product_id AND b.metal = c.metal AND b.product_metalavail_id = '" . (int)$metal . "' ");
		$cost=0;
		
//		$sql="select a.pricepercarat,	a.priceperpiece,d.weight,d.count,b.price	from gemstone_price a, product_gemavail b,colour_clarity c, product_gemcontent d  where  a.gemstone_id	= b.gemstone_id	and a.gemstone_id=c.gemstone_id	and b.product_id  = d.product_id and b.gem=d.gem and a.lowclarity > c.clarity_id and a.highclarity <= c.clarity_id and  a.lowcolour > c.colour_id and a.highcolour <= c.colour_id and b.product_gemavail_id=  '" . $product_gemavail_id . "'" ;
//		$sql="select a.pricepercarat, a.priceperpiece,d.weight,d.count,b.price,c.pricebycart	from gemstone_price a, product_gemavail b, colour_clarity c, product_gemcontent d  where  a.gemstone_id	= b.gemstone_id		and b.product_id  = d.product_id and b.gem=d.gem   and a.gemstone_id=c.gemstone_id and c.clarity_id >= a.lowclarity and c.clarity_id <= a.highclarity and c.colour_id>= a.lowcolour  and c.colour_id <= a.highcolour and d.shape_id = a.shape and b.product_gemavail_id= '" . $product_gemavail_id . "'" ;

    $rapnetFlag_Failed=1;
	
	$sql="select ga.gemstone_id as gemstone_id,ga.rapnet as rapnet, gp.pricetype,gp.price as gcprice,gc.shape_id, gw.weight,gc.count  ,gc.size, gcc.clarity_id, 	gcc.colour_id , gc.setting_id, gp.ccurrency,ga.product_id
	from gemstone_price gp, product_gemavail ga, colour_clarity gcc, product_gemcontent gc , gemstone_weight gw
	where  gp.gemstone_id = ga.gemstone_id and  ga.gemstone_id = gcc.gemstone_id and ga.product_id = gc.product_id 
	and ga.gem=gc.gem and gw.gemstone_id= ga.gemstone_id and ga.product_gemavail_id=  '" . $product_gemavail_id . "'
	and gc.shape_id = gp.shape and gw.shape = gc.shape_id and gw.size_code = gc.size  
	and gcc.clarity_id >= gp.lowclarity and gcc.clarity_id <= gp.highclarity and gcc.colour_id>= gp.lowcolour 
	and gw.weight > gp.lowweight and gw.weight <= gp.highweight
	and gcc.colour_id <= gp.highcolour ";
	//$this->log->write(  $sql );
	$query = $this->db->query($sql);
    if($query->row['rapnet']==1){
		$this->log->write( 'RapNet Processing Weight : ' . $query->row['weight'] . ' ; Size : ' . $query->row['size'] );
		$weight = $query->row['weight'];

		$sql5="SELECT * FROM `size` where size_code = '" . $query->row['size'] . "'";
		$query5 = $this->db->query($sql5);
		
		$measlength = $query5->row['measlength'];
		$measwidth = $query5->row['measwidth'];

		$sql1="SELECT *,b.shape as shape_data FROM `rapnet_default` a,shape b where a.shape=b.shape_id and lowweight <= " . $weight ."  and highweight>=" . $weight . " limit 1";
		$query1 = $this->db->query($sql1);
		if ($query1->num_rows) {
				$this->log->write( 'RapNet variation'  . $query1->row['variation']);

				$variation = $query1->row['variation'];	
				//weight >= " . $query1->row['lowweight'] ."  and weight <= " . $query1->row['highweight'] . " and
				$sql2="SELECT * FROM `rapnet_tbl`  where  clarity_id >= " . $query1->row['lowclarity'] . " and clarity_id <= " . $query1->row['highclarity'] . " and colour_id >= " . $query1->row['lowcolour'] . " and colour_id <= " . $query1->row['highcolour'] . " and shape = '" . $query1->row['shape_data'] . "' and  measwidth >= " . ((float)$measwidth - (float)$variation) . " and measwidth <= " .  ((float)$measwidth + (float)$variation) . "   and measlength >= " . ((float)$measlength - (float)$variation) . " and measlength <= " . ((float)$measlength + (float)$variation) . " order by clarity_id desc,colour_id desc,rapnet_final_price asc limit 1";
				$this->log->write( 'RapNet SQL : '  . $sql2 );
				$query2 = $this->db->query($sql2);
				if ($query2->num_rows) {
					//$cost = $query2->row['rapnet_final_price'] * ( 1 + $query2->row['rapnet_discount_price']);
					$cost = $query2->row['rapnet_final_price'] ;
					$cost = $cost * $query->row['count']; 
					$rapnetFlag_Failed = 0;
					
					$this->session->data[ 'LotNo-' . $query->row['product_id'] . '-' . $product_gemavail_id] = $query2->row['rapnet_lot_no'];
					
					$this->log->write( ' RapNet Price(USD) : '. $query2->row['rapnet_final_price'] . '  ' . ' ; LotNo-' . $query->row['product_id'] . '-' . $product_gemavail_id );
					return round($this->currency->convert($cost,'USD', $this->currency->getCode()),2); 
				}else{
					//return round($this->currency->convert(1000,'USD', $this->currency->getCode()),2);
					$rapnetFlag_Failed = 1;
				}
		}else{
			//return round($this->currency->convert(1000,'USD', $this->currency->getCode()),2);
			$rapnetFlag_Failed = 1;
		}
		
	}
	if($rapnetFlag_Failed){
	//G1-Gemstone-Shape-Size-Count-Weight-Clarity-Colour-Setting-PriceCaratOrPiece
		$this->log->write( ' GemStoneId : ' . $query->row['gemstone_id'] . ' ; Size : '. $query->row['size']  . ' ; Shape : '. $query->row['shape_id']  . ' ; Count : '. $query->row['count']  . ' ; Weight : '. $query->row['weight']  );
		$this->log->write( ' Colour : '. $query->row['colour_id']   . ' Clarity : '. $query->row['clarity_id']   . ' ; Setting : '. $query->row['setting_id']    . ' ; Price : '. $query->row['gcprice'] . ' ; ' . ' ; Price : '. $query->row['ccurrency'] . ' ; ' );

	// pricetype=='0' , then priceperpiece or  pricetype=='1' , then pricepercarat 
		if ($query->num_rows) {
			if($query->row['pricetype']=='1'){
				$this->log->write( ' Price Type : Price Per Piece'  );

				//$cost=$query->row['priceperpiece'] * $query->row['count'];
				$cost = $query->row['gcprice'] * $query->row['count'];
			}else{
				$this->log->write( ' Price Type : Price Per Carat'  );
				//$cost=$query->row['pricepercarat'] * $query->row['weight'];
				$cost = $query->row['gcprice'] * $query->row['weight'] * $query->row['count'];
			}
		}else{
			$cost=123;
		}
		
		//return $query->row['metalcost'];
	//	return $cost;
		return round($this->currency->convert($cost,$query->row['ccurrency'], $this->currency->getCode()),2);
	 }
		
	}
	
	
	public function getGemstoneIdImage($gemname) {
		$query = $this->db->query("SELECT gemstone_id FROM " . DB_PREFIX . "gemstone WHERE gemname = '" .  $gemname . "'");
		if ($query->num_rows) {
			return $query->row['gemstone_id'];
		}else{
				return 0;
		}
	}

	public function getMetalIdImage($metalname) {
		$query = $this->db->query("SELECT metal_id FROM " . DB_PREFIX . "metal WHERE metal_code = '" .  $metalname . "'");
		if ($query->num_rows) {
			return $query->row['metal_id'];
		}else{
				return 0;
		}
	}

	public function getProductGemstoneImage($gemstone_id,$product_id) {
		$query = $this->db->query("SELECT image,product_gemavail_id FROM " . DB_PREFIX . "product_gemavail WHERE gem=1 and product_id = '" .  $product_id . "' and gemstone_id = '" .  $gemstone_id . "'");
		if ($query->num_rows) {
			return $query->row;
		}else{
				return "";
		}
	}

	public function getProductMetalImage($metal_id,$product_id) {
		$query = $this->db->query("SELECT image,product_metalavail_id FROM " . DB_PREFIX . "product_metalavail WHERE metal=1 and product_id = '" .  $product_id . "' and metal_id = '" .  $metal_id . "'");
		if ($query->num_rows) {
			return $query->row;
		}else{
				return "";
		}
	}

	
	
	public function getProductnewproduct($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product  WHERE product_id = '" . (int)$product_id . "'");
		return $query->rows;
	}
	
	
	public function updateProductnewproduct($product_id,$price,$image,$optionaname) {
		
		//$this->db->query("INSERT INTO " . DB_PREFIX . "rea_product SET image = '" . $this->db->escape($data['image']) . "', price = '" . $this->db->escape($data['product_cost']) . "' ");

$this->db->query("insert into rea_product(parent_product_id,model,sku,upc,location,quantity,stock_status_id,image,manufacturer_id,shipping,price,additionalCosts,overriding_mark_up_ubsolute,overriding_markup_percentage,points,tax_class_id,supplier_group_id,date_available,weight,weight_class_id,length,width,height,length_class_id,subtract,minimum,sort_order,status,metal,m_weight,date_added,date_modified,viewed)   select ". $product_id . ",model,sku,upc,location,quantity,stock_status_id,'". str_replace('image/','',$image) ."',manufacturer_id,shipping,'" . $price . "' ,additionalCosts,overriding_mark_up_ubsolute,overriding_markup_percentage,points,tax_class_id,supplier_group_id,date_available,weight,weight_class_id,length,width,height,length_class_id,subtract,minimum,sort_order,status,metal,m_weight,date_added,date_modified,viewed  from product where product_id = ". $product_id );
		$new_product_id = $this->db->getLastId();
		
$this->db->query("insert into rea_product_description(product_id,language_id,name,optionname,commonname,description,meta_description,meta_keyword)   select " . $new_product_id .  " ,language_id,name,'" . $optionaname . "',commonname,description,meta_description,meta_keyword from product_description where product_id = ". $product_id );

$this->db->query("insert into rea_product_to_category select " . $new_product_id . ",category_id from product_to_category where product_id = ". $product_id );
/*

$this->db->query("insert into rea_product_gemavail( product_id,gemstone_id,gem,image,defaultnew,statusnew)   select  " . $new_product_id .  ",gemstone_id,gem,image,defaultnew,statusnew from product_gemavail where product_id = ". $product_id );

$this->db->query("insert into rea_product_gemcontent( product_id,setting_id,gem,weight,count,size,shape_id)   select  " . $new_product_id .  ",setting_id,gem,weight,count,size,shape_id from product_gemcontent where product_id = ". $product_id );

$this->db->query("insert into rea_product_image( product_id,image,sort_order)   select  " . $new_product_id .  ",image,sort_order from product_image where product_id = ". $product_id );

$this->db->query("insert into rea_product_metalavail( product_id,metal,metal_id,image,defaultnew,price,currency)   select  " . $new_product_id .  ",metal,metal_id,image,defaultnew,price,currency from product_metalavail where product_id = ". $product_id );

$this->db->query("insert into rea_product_metalcontent(product_id,metal,weight)   select   " . $new_product_id .  ",metal,weight from product_metalcontent where product_id = ". $product_id );

$this->db->query("insert into rea_product_related(product_id,related_id )   select " . $new_product_id .  ",related_id from product_related where product_id = ". $product_id );

$this->db->query("insert into rea_product_reward( product_id,customer_group_id,points )   select  " . $new_product_id .  ",customer_group_id,points  from product_reward where product_id = ". $product_id );
*/
$this->db->query("insert into rea_product_to_store(product_id, store_id )  values  ( " . $new_product_id .  ",0 )" );
	
		//$this->session->data['checkout_product_id']=$new_product_id;
		return $new_product_id;
	}
	
	public function getProductGemstones($product_id) {
		$query = $this->db->query("SELECT a.*,c.setting,b.shape FROM " . DB_PREFIX . "product_gemcontent a, shape b,laboursettingcost c  WHERE a.setting_id = c.setting_id and a.shape_id=b.shape_id and a.product_id = '" . (int)$product_id . "' order by gem");
		return $query->rows;
	}
	
	public function getProductImageCount($product_id) {
		$query = $this->db->query("SELECT count(*) as imagecount FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");
		if ($query->num_rows) {
			return $query->row['imagecount'];
		}else{
				return 0;
		}
	}


	public function getProductMarkupCostOld($product_id, $product_cost) {
		$query = $this->db->query("SELECT overriding_mark_up_ubsolute,overriding_markup_percentage FROM " . DB_PREFIX . "product_vw  WHERE product_id = '" . (int)$product_id . "'");
		if($query->row['overriding_mark_up_ubsolute']){
			$cost =$query->row['overriding_mark_up_ubsolute'] + $product_cost;
			$this->log->write( ' Product Level Markup Absolute : ' . $query->row['overriding_mark_up_ubsolute']  . '  ; Markup Cost Cost : ' . $cost );
			return $query->row['overriding_mark_up_ubsolute'] + $product_cost ;
		}else{
			if($query->row['overriding_markup_percentage']){
				$cost = $product_cost + ($query->row['overriding_markup_percentage']  * $product_cost /100);
				$this->log->write( ' Product Level Markup Percentage : ' . $query->row['overriding_markup_percentage']  . '  ; Markup Cost Cost : ' . $cost );
				return ($product_cost + ($query->row['overriding_markup_percentage'] * $product_cost / 100));
			}else{
				$cost = $product_cost + (($this->config->get('overriding_markup_percentage') - 100)  * $product_cost / 100);
				$this->log->write( ' Product Level Markup Percentage : ' . $this->config->get('overriding_markup_percentage')  . '  ; Markup Cost Added : ' .  $cost );
				return ($product_cost + ($this->config->get('overriding_markup_percentage')  * $product_cost /100));
			}
		}
	}
	
	
	public function getProductMarkupCost($product_id, $product_cost) {
		$query = $this->db->query("SELECT overriding_mark_up_ubsolute,overriding_markup_percentage FROM " . DB_PREFIX . "product_vw  WHERE product_id = '" . (int)$product_id . "'");
		if($query->row['overriding_mark_up_ubsolute']){
			$cost =$query->row['overriding_mark_up_ubsolute'] + $product_cost;
			$this->log->write( 'Product Level Markup Absolute : ' . $query->row['overriding_mark_up_ubsolute']  . '  ; Markup Cost Cost : ' . $cost );
			return $query->row['overriding_mark_up_ubsolute'] + $product_cost ;
		}else{
			if($query->row['overriding_markup_percentage']){
				$cost = $product_cost + ($query->row['overriding_markup_percentage']  * $product_cost /100);
				$this->log->write( 'Product Level Markup Percentage : ' . $query->row['overriding_markup_percentage']  . '  ; Markup Cost Cost : ' . $cost );
				return ($product_cost + ($query->row['overriding_markup_percentage'] * $product_cost / 100));
			}else{
				if( (int)$this->config->get('pricelimit_four') <= $product_cost ){
					$ovrd_percentage = (int)$this->config->get('overriding_five') ; 
				}else if( (int)$this->config->get('pricelimit_three') <= $product_cost ){
					$ovrd_percentage = (int)$this->config->get('overriding_four') ; 
				}else if( (int)$this->config->get('pricelimit_two') <= $product_cost ){
					$ovrd_percentage = (int)$this->config->get('overriding_three') ; 
				}else if( (int)$this->config->get('pricelimit_one') <= $product_cost ){
					$ovrd_percentage = (int)$this->config->get('overriding_two') ; 
				}else{
					$ovrd_percentage = (int)$this->config->get('overriding_one') ; 
				} 
				$cost = $product_cost + (($ovrd_percentage - 100)  * $product_cost / 100);
				$this->log->write( 'Store Level Markup Percentage : ' . $ovrd_percentage  . '  ; Markup Cost Added : ' .  $cost );
				return ($product_cost + ($ovrd_percentage  * $product_cost /100));
			}
		}
	}
	
	public function getProductPrice($product_id ) {
		$query = $this->db->query("select  round(price) as price   from " . DB_PREFIX . "product_vw  WHERE product_id = '" . (int)$product_id . "'");
		
		$this->log->write("select  round(price) as price   from " . DB_PREFIX . "product  WHERE product_id = '" . (int)$product_id . "'");

		
		return $query->row['price'];
	}
	public function updateProductPrice($product_id,$price,$prdoptionname) {
		$query = $this->db->query("update product set price =  " . $price . " WHERE product_id = '" . (int)$product_id . "'");
		$query = $this->db->query("update product_description set optionname =  '" . $prdoptionname . "' WHERE product_id = '" . (int)$product_id . "'");
		
	}
	public function getProductGemstonesAvail($product_id,$gemstone) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_gemavail a, gemstone b WHERE product_id = '" . (int)$product_id . "' and gem = '" . (int)$gemstone . "' and a.gemstone_id=b.gemstone_id order by b.sort_order");
		//return $query->rows;
		$gemstone_available = array();
		foreach ($query->rows as $row){
			if($row['rapnet']==1){
				if(! isset($this->session->data[ 'LotNo-' . $row['product_id'] . '-' . $row['product_gemavail_id']])){
					$this->getProductGemstoneCost( $row['product_gemavail_id'] );
				}
				//if(1){
					$LotNo = $this->session->data[ 'LotNo-' . $row['product_id'] . '-' . $row['product_gemavail_id']];
					$this->log->write( ' LotNo :  ' . $LotNo );
					$rapnetrow = $this->db->query("SELECT * FROM rapnet_tbl where rapnet_lot_no=" . $LotNo );
					$row['rapnet_size'] =  $rapnetrow->row['measlength'] . ' x '  .  $rapnetrow->row['measwidth'] . '  mm ' ;
					$row['rapnet_quality'] =  $rapnetrow->row['color'] . ' colour ; '  .  $rapnetrow->row['clarity'] . ' clarity ' ;
					$row['rapnet_weight'] =  $rapnetrow->row['weight']  . '  mm ' ;
					//$row['rapnet_url'] =  "  <a href='". $rapnetrow->row['url'] . "' target='_blank' >".$rapnetrow->row['lab'] . ' ' . $rapnetrow->row['cert_no']. "</a>";
					if($rapnetrow->row['url']!=''){
						$row['rapnet_url'] =  "<br><a onClick=window.open('". $rapnetrow->row['url'] . "','Certification','height=550,width=920,top=100,left=200,toolbars=no,menubar=no,location=no,scrollbars=yes,resizable=yes,status=yes')>".$rapnetrow->row['lab']. ' Certificate: ' . $rapnetrow->row['cert_no'] ."</a>";
					}else{
						$row['rapnet_url'] =  "<br>".$rapnetrow->row['lab']. ' Certificate: ' . $rapnetrow->row['cert_no'] ;
					}
				/*}else{
					$row['rapnet_size'] = '' ;
					$row['rapnet_quality'] =  '' ;
					$row['rapnet_weight'] =  '' ;
					$row['rapnet_url'] = "";					
				}
				*/
			}
			$gemstone_available[] = $row;
		}
		
		return $gemstone_available;
	}
	
	public function getProductGemstonesAvalability($product_id) {
		$query = $this->db->query("SELECT a.*,b.gemname FROM " . DB_PREFIX . "product_gemavail a, gemstone b  WHERE a.gemstone_id=b.gemstone_id and  a.product_id = '" . (int)$product_id . "' order by gem");
		return $query->rows;
	}
	
	public function getProductGemName($product_id) {
		$query = $this->db->query("SELECT DISTINCT b.gemname FROM " . DB_PREFIX . "product_gemavail a, gemstone b  WHERE a.gemstone_id=b.gemstone_id and  a.product_id = '" . (int)$product_id . "' and a.defaultnew =  '1'");
		return $query->rows;
	}
	
	public function UpdateOptionName($product_id,$optionname) {
		$query = $this->db->query("update rea_product_description set optionname='". $optionname ."' where product_id ='" . $product_id . "'");
	}

	public function getProductGemNameDefault($product_id) {
		$query = $this->db->query("SELECT DISTINCT b.gemname FROM " . DB_PREFIX . "product_gemavail a, gemstone b  WHERE a.gemstone_id=b.gemstone_id and  a.product_id = '" . (int)$product_id . "' and a.defaultnew =  '1' order by a.gem ");
		return $query->rows;
	}
	public function getProductMetalName($product_id) {
		$query = $this->db->query("SELECT DISTINCT b.metalname FROM " . DB_PREFIX . "product_metalavail a, metal b  WHERE a.metal_id=b.metal_id and  a.product_id = '" . (int)$product_id . "' and a.defaultnew =  '1'");
		return $query->rows;
	}

	public function getShapeName($shape_id) {
		$query = $this->db->query("SELECT  shape FROM  shape WHERE  shape_id= '" . (int)$shape_id . "' ");
		if ($query->num_rows) {
			return $query->row['shape'];
		}else{
			return '';
		}
	}
	public function getMetalName($metal_id) {
		$query = $this->db->query("SELECT  metalname FROM  metal WHERE  metal_id= '" . (int)$metal_id . "' ");
		if ($query->num_rows) {
			return $query->row['metalname'];
		}else{
			return '';
		}
	}
	
	public function getGemName($gemstone_id) {
		$query = $this->db->query("SELECT gemname FROM  gemstone WHERE  gemstone_id= '" . (int)$gemstone_id . "' ");
		if ($query->num_rows) {
			return $query->row['gemname'];
		}else{
			return '';
		}
	}
	public function getGem2Name($product_id) {
		$query = $this->db->query("SELECT a.gemname FROM  gemstone a,product_gemavail b WHERE b.product_id='" . (int)$product_id . "' and b.gem=2 and b.defaultnew=1 and b.gemstone_id= a.gemstone_id ");
		if ($query->num_rows) {
			return '/'.$query->row['gemname'];
		}else{
			return '';
		}
	}
	
	
	public function getProduct($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
				
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
		
		if ($query->num_rows) {
			$query->row['price'] = ($query->row['discount'] ? $query->row['discount'] : $query->row['price']);
			$query->row['rating'] = (int)$query->row['rating'];
			
			return $query->row;
		} else {
			return false;
		}
	}
	
	public function getProductCombo($product_id,$metal_id,$gemstone_id,$shape_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
				
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
		
		if ($query->num_rows) {
			$query->row['price'] = ($query->row['discount'] ? $query->row['discount'] : $query->row['price']);
			$query->row['rating'] = (int)$query->row['rating'];
			
			return $query->row;
		} else {
			return false;
		}
	}

	
	public function getProduct_newold($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
				
//		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount_vw pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special_vw ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward_vw pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product_vw p LEFT JOIN " . DB_PREFIX . "product_description_vw pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store_vw p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, 0 AS discount, 0 AS special, 0 AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, 0 AS weight_class, 0 AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product_vw p LEFT JOIN " . DB_PREFIX . "product_description_vw pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store_vw p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
		
		if ($query->num_rows) {
			$query->row['price'] = ($query->row['discount'] ? $query->row['discount'] : $query->row['price']);
			$query->row['rating'] = (int)$query->row['rating'];
			
			return $query->row;
		} else {
			return false;
		}
	}

	
	public function getProduct_new($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
				
//		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, '0' AS discount, '0' AS special, '0' AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, '0' AS weight_class, '0' AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product_vw p LEFT JOIN " . DB_PREFIX . "product_description_vw pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store_vw p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
		
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, '' AS manufacturer, '0' AS discount, '0' AS special, '0' AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, '0' AS weight_class, '0' AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product_vw p LEFT JOIN " . DB_PREFIX . "product_description_vw pd ON (p.product_id = pd.product_id)  WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() ");
		if ($query->num_rows) {
			$query->row['price'] = ($query->row['discount'] ? $query->row['discount'] : $query->row['price']);
			$query->row['rating'] = (int)$query->row['rating'];
			
			return $query->row;
		} else {
			return false;
		}
	}

	
	public function getProductsOld($data = array()) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$cache = md5(http_build_query($data));
		
		$product_data = $this->cache->get('product.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache);
		
		if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)"; 
			
			if (!empty($data['filter_tag'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";			
			}
						
			if (!empty($data['filter_category_id'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";			
			}
			
			$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"; 
			
			if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
				$sql .= " AND (";
											
				if (!empty($data['filter_name'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_name']);
					
					foreach ($words as $word) {
						if (!empty($data['filter_description'])) {
							$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
						} else {
							$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
						}				
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
				
				if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
					$sql .= " OR ";
				}
				
				if (!empty($data['filter_tag'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_tag']);
					
					foreach ($words as $word) {
						$implode[] = "LCASE(pt.tag) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%' AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
			
				$sql .= ")";
			}
			
			if (!empty($data['filter_category_id'])) {
				if (!empty($data['filter_sub_category'])) {
					$implode_data = array();
					
					$implode_data[] = "p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
					
					$this->load->model('catalog/category');
					
					$categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);
										
					foreach ($categories as $category_id) {
						$implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
					}
								
					$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
				} else {
					$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
				}
			}		
					
			if (!empty($data['filter_manufacturer_id'])) {
				$sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
			}
			
			$sql .= " GROUP BY p.product_id";
			
			$sort_data = array(
				'pd.name',
				'p.model',
				'p.quantity',
				'p.price',
				'rating',
				'p.sort_order',
				'p.date_added'
			);	
			
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
					$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
				} else {
					$sql .= " ORDER BY " . $data['sort'];
				}
			} else {
				$sql .= " ORDER BY p.sort_order";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}				
	
				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}
			
			$product_data = array();
					
			$query = $this->db->query($sql);
		
			foreach ($query->rows as $result) {
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
			
			$this->cache->set('product.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache, $product_data);
		}
		
		return $product_data;
	}
	
	
	
	public function getProducts($data = array()) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$cache = md5(http_build_query($data));
		
		$product_data = $this->cache->get('product.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache);
//		if (!$product_data) {
//			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)"; 
			$sql = "SELECT p.product_id, p.tax_class_id ,p.type , pc.final_price as price,pc.metal_id,pc.gemstone_id,pc.image,0 as reviews,0 as special, 0 AS rating, pd.name,pd.description FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)"; 
			 
			if (!empty($data['filter_tag'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";			
			}
						
			if (!empty($data['filter_category_id'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";			
			}
			//if (!empty($data['filter_category_id'])) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "product_combinations pc ON (p.product_id = pc.product_id)";			

			//}
			
			$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"; 
			
			if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
				$sql .= " AND (";
											
				if (!empty($data['filter_name'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_name']);
					
					foreach ($words as $word) {
						if (!empty($data['filter_description'])) {
							$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
						} else {
							$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
						}				
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
				
				if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
					$sql .= " OR ";
				}
				
				if (!empty($data['filter_tag'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_tag']);
					
					foreach ($words as $word) {
						$implode[] = "LCASE(pt.tag) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%' AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
			
				$sql .= ")";
			}
			
			if (!empty($data['filter_category_id'])) {
				if (!empty($data['filter_sub_category'])) {
					$implode_data = array();
					
					$implode_data[] = "p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
					
					$this->load->model('catalog/category');
					
					$categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);
										
					foreach ($categories as $category_id) {
						$implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
					}
								
					$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
				} else {
					$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
				}
			}		
					
			if (!empty($data['filter_manufacturer_id'])) {
				$sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
			}
			
			

			if ($data['filter_metal_id']) { 
				$sql .= " and pc.metal_id ='". $data['filter_metal_id'] ."' " ;			
			}else{
				$sql .= " and pc.metal_default = 1 " ;			
			}

			if ($data['filter_gemstone_id']) {
				$sql .= " and pc.gemstone_id ='". $data['filter_gemstone_id'] ."' " ;			
			}else{
				$sql .= " and pc.gem_default = 1 " ;			
			}
			if ($data['filter_shape_id']) {
				$sql .= " and pc.shape ='". $data['filter_shape_id'] ."' " ;			
			}
			
			if ($data['filter_price_low']) {
				$sql .= " and pc.final_price >= '". $data['filter_price_low'] ."' " ;			
			}
			if ($data['filter_price_high']!=10000000) {
				$sql .= " and pc.final_price <='". $data['filter_price_high'] ."' " ;			
			}
			
			
			
		//	$sql .= " GROUP BY p.product_id";
			
			$sort_data = array(
				'pd.name',
				'p.model',
				'p.quantity',
				'p.price',
				'rating',
				'p.sort_order',
				'p.date_added'
			);	
			/*
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
					$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
				} else {
					$sql .= " ORDER BY " . $data['sort'];
				}
			} else {
				$sql .= " ORDER BY p.sort_order";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		*/
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}				
	
				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}
			$product_data = array();
		$this->log->write($sql);
			$query = $this->db->query($sql);
			return $query->rows;
/*
			foreach ($query->rows as $result) {
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
			$this->cache->set('product.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache, $product_data);
		}
		
		return $product_data;
*/
	}
	
	public function getProductSpecials($data = array()) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
				
		$sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) GROUP BY ps.product_id";

		$sort_data = array(
			'pd.name',
			'p.model',
			'ps.price',
			'rating',
			'p.sort_order'
		);
		
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} else {
				$sql .= " ORDER BY " . $data['sort'];
			}
		} else {
			$sql .= " ORDER BY p.sort_order";	
		}
		
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
	
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}				

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
		
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$product_data = array();
		
		$query = $this->db->query($sql);
		
		foreach ($query->rows as $result) { 		
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}
		
		return $product_data;
	}
		
	public function getLatestProducts($limit) {
		$product_data = $this->cache->get('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$limit);

		if (!$product_data) { 
			$query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.date_added DESC LIMIT " . (int)$limit);
		 	 
			foreach ($query->rows as $result) {
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
			
			$this->cache->set('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$limit, $product_data);
		}
		
		return $product_data;
	}
	
	public function getPopularProducts($limit) {
		$product_data = array();
		
		$query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.viewed, p.date_added DESC LIMIT " . (int)$limit);
		
		foreach ($query->rows as $result) { 		
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}
					 	 		
		return $product_data;
	}

	public function getBestSellerProducts($limit) {
		$product_data = $this->cache->get('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$limit);

		if (!$product_data) { 
			$product_data = array();
			
			$query = $this->db->query("SELECT op.product_id, COUNT(*) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);
			
			foreach ($query->rows as $result) { 		
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
			
			$this->cache->set('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$limit, $product_data);
		}
		
		return $product_data;
	}
	
	public function getProductAttributes($product_id) {
		$product_attribute_group_data = array();
		
		$product_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE pa.product_id = '" . (int)$product_id . "' AND agd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");
		
		foreach ($product_attribute_group_query->rows as $product_attribute_group) {
			$product_attribute_data = array();
			
			$product_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, pa.text FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE pa.product_id = '" . (int)$product_id . "' AND a.attribute_group_id = '" . (int)$product_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . (int)$this->config->get('config_language_id') . "' AND pa.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");
			
			foreach ($product_attribute_query->rows as $product_attribute) {
				$product_attribute_data[] = array(
					'attribute_id' => $product_attribute['attribute_id'],
					'name'         => $product_attribute['name'],
					'text'         => $product_attribute['text']		 	
				);
			}
			
			$product_attribute_group_data[] = array(
				'attribute_group_id' => $product_attribute_group['attribute_group_id'],
				'name'               => $product_attribute_group['name'],
				'attribute'          => $product_attribute_data
			);			
		}
		
		return $product_attribute_group_data;
	}
			
	public function getProductOptions($product_id) {
		$product_option_data = array();

		$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order");
		
		foreach ($product_option_query->rows as $product_option) {
			if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
				$product_option_value_data = array();
			
				$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = '" . (int)$product_id . "' AND pov.product_option_id = '" . (int)$product_option['product_option_id'] . "' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order");
				
				foreach ($product_option_value_query->rows as $product_option_value) {
					$product_option_value_data[] = array(
						'product_option_value_id' => $product_option_value['product_option_value_id'],
						'option_value_id'         => $product_option_value['option_value_id'],
						'name'                    => $product_option_value['name'],
						'image'                   => $product_option_value['image'],
						'quantity'                => $product_option_value['quantity'],
						'subtract'                => $product_option_value['subtract'],
						'price'                   => $product_option_value['price'],
						'price_prefix'            => $product_option_value['price_prefix'],
						'weight'                  => $product_option_value['weight'],
						'weight_prefix'           => $product_option_value['weight_prefix']
					);
				}
									
				$product_option_data[] = array(
					'product_option_id' => $product_option['product_option_id'],
					'option_id'         => $product_option['option_id'],
					'name'              => $product_option['name'],
					'type'              => $product_option['type'],
					'option_value'      => $product_option_value_data,
					'required'          => $product_option['required']
				);
			} else {
				$product_option_data[] = array(
					'product_option_id' => $product_option['product_option_id'],
					'option_id'         => $product_option['option_id'],
					'name'              => $product_option['name'],
					'type'              => $product_option['type'],
					'option_value'      => $product_option['option_value'],
					'required'          => $product_option['required']
				);				
			}
      	}
		
		return $product_option_data;
	}
		
	public function getProductOptions_NEW($product_id) {
		$product_option_data = array();
		return $product_option_data;
		$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_vw po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order");
		
		foreach ($product_option_query->rows as $product_option) {
			if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
				$product_option_value_data = array();
			
				$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value_vw pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = '" . (int)$product_id . "' AND pov.product_option_id = '" . (int)$product_option['product_option_id'] . "' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order");
				
				foreach ($product_option_value_query->rows as $product_option_value) {
					$product_option_value_data[] = array(
						'product_option_value_id' => $product_option_value['product_option_value_id'],
						'option_value_id'         => $product_option_value['option_value_id'],
						'name'                    => $product_option_value['name'],
						'image'                   => $product_option_value['image'],
						'quantity'                => $product_option_value['quantity'],
						'subtract'                => $product_option_value['subtract'],
						'price'                   => $product_option_value['price'],
						'price_prefix'            => $product_option_value['price_prefix'],
						'weight'                  => $product_option_value['weight'],
						'weight_prefix'           => $product_option_value['weight_prefix']
					);
				}
									
				$product_option_data[] = array(
					'product_option_id' => $product_option['product_option_id'],
					'option_id'         => $product_option['option_id'],
					'name'              => $product_option['name'],
					'type'              => $product_option['type'],
					'option_value'      => $product_option_value_data,
					'required'          => $product_option['required']
				);
			} else {
				$product_option_data[] = array(
					'product_option_id' => $product_option['product_option_id'],
					'option_id'         => $product_option['option_id'],
					'name'              => $product_option['name'],
					'type'              => $product_option['type'],
					'option_value'      => $product_option['option_value'],
					'required'          => $product_option['required']
				);				
			}
      	}
		
		return $product_option_data;
	}
	
	public function getProductDiscounts($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity > 1 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");

		return $query->rows;		
	}
		
	public function getProductImages($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");

		return $query->rows;
	}
	
	public function isRing($product_id) { 
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "' AND category_id =59 union SELECT * FROM " . DB_PREFIX . "rea_product_to_category WHERE product_id = '" . (int)$product_id . "' AND category_id =59 ");
		if ($query->num_rows) {
			return "Yes";
		}else{
			return "No";
		}
	}
	
	public function getProductRelated($product_id) {
		$product_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related pr LEFT JOIN " . DB_PREFIX . "product p ON (pr.related_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pr.product_id = '" . (int)$product_id . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
		
		foreach ($query->rows as $result) { 
			$product_data[$result['related_id']] = $this->getProduct($result['related_id']);
		}
		
		return $product_data;
	}
		
	public function getProductTags($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_tag WHERE product_id = '" . (int)$product_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

		return $query->rows;
	}
		
	public function getProductLayoutId($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "'");
		
		if ($query->num_rows) {
			return $query->row['layout_id'];
		} else {
			return  $this->config->get('config_layout_product');
		}
	}
	
	
	public function getProductImage($product_id) {
		$query = $this->db->query("SELECT image FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "'");
		
		if ($query->num_rows) {
			return $query->row['image'];
		} else {
			return  '';
		}
	}
	
	public function getProductRotation($product_id) {
		$query = $this->db->query("SELECT rotation FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "'");
		
		if ($query->num_rows) {
			return $query->row['rotation'];
		} else {
			return  0;
		}
	}
	
	
	
	public function getCategories($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
		
		return $query->rows;
	}	
		
	public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";

		if (!empty($data['filter_category_id'])) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";			
		}
		
		if (!empty($data['filter_tag'])) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";			
		}
					
		$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
		
		if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
			$sql .= " AND (";
								
			if (!empty($data['filter_name'])) {
				$implode = array();
				
				$words = explode(' ', $data['filter_name']);
				
				foreach ($words as $word) {
					if (!empty($data['filter_description'])) {
						$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					} else {
						$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					}				
				}
				
				if ($implode) {
					$sql .= " " . implode(" OR ", $implode) . "";
				}
			}
			
			if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
				$sql .= " OR ";
			}
			
			if (!empty($data['filter_tag'])) {
				$implode = array();
				
				$words = explode(' ', $data['filter_tag']);
				
				foreach ($words as $word) {
					$implode[] = "LCASE(pt.tag) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%' AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
				}
				
				if ($implode) {
					$sql .= " " . implode(" OR ", $implode) . "";
				}
			}
		
			$sql .= ")";
		}
		
		if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$implode_data = array();
				
				$implode_data[] = "p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
				
				$this->load->model('catalog/category');
				
				$categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);
					
				foreach ($categories as $category_id) {
					$implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
				}
							
				$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
			} else {
				$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
			}
		}		
		
		if (!empty($data['filter_manufacturer_id'])) {
			$sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
		}
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}
			
	public function getTotalProductSpecials() {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}		
		
		$query = $this->db->query("SELECT COUNT(DISTINCT ps.product_id) AS total FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))");
		
		if (isset($query->row['total'])) {
			return $query->row['total'];
		} else {
			return 0;	
		}
	}	
}
?>