<?php
App::uses('Model', 'Model');

/**
 * Application model for Cake.
 *
 * Add your application-wide methods in the class below, your models
 * will inherit them.
 *
 * @package       app.Model
 */

class Route extends AppModel {
	
	public $belongsTo  = array(
						'Region' => array('className' => 'Region', 'foreignKey' => 'region_id'),
						'Depot' => array('className' => 'Depot', 'foreignKey' => 'depot_id')
					);
	public function userWeeklyRoute($user_id = 0,$week_id,$day_id){

		$result = array();

		if($user_id){			

			$userRoutes = $this->query("SELECT * FROM user_routes AS ur WHERE user_id = $user_id AND week = '$week_id' AND day ='$day_id'");

			if ($userRoutes){

				foreach ($userRoutes as $userRoute){

					$weekId = $userRoute['ur']['week'];
					//$dayId = $userRoute['ur']['day'];
					$routeId = $userRoute['ur']['route_id'];

					$result[$weekId][] = $routeId;

				}

			}

		}		

		return $result;
		

	}
	
	
	public function userRoutePlan($user_id = 0){

		$result = array();

		if($user_id){			

			$userRoutes = $this->query("SELECT ur.*, rt.title FROM user_routes AS ur INNER JOIN routes AS rt ON ur.route_id = rt.id WHERE ur.user_id = $user_id");

			if ($userRoutes){
					
				foreach ($userRoutes as $userRoute){

					$weekId = $userRoute['ur']['week'];
					
					$dayId=$userRoute['ur']['day'];

					$routeId = $userRoute['ur']['route_id'];

					$routeName = $userRoute['rt']['title'];
					
					$result[$weekId][$routeId] = $routeName;
					//$result[$dayId][$routeId] = $routeId;
//$result[$weekId][$dayId][$routeId] = $routeName;
				}

			}

		}		

		return $result;

	}
	
	public function routeSalesReport($params){
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$sC = "s.sale_date = '$date'";
				$rdC = "rs.sale_date = '$date'";
				$cvC = "cv.visiting_date LIKE '".$date."%'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
			
			case 'between':
				$d1 = $params['query']['from'];
				if(empty( $params['query']['to'])){
					$d2 = date('Y-m-d');
				}else{
					$d2 =  $params['query']['to'];
				}		
				
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
		}
		$rgC = $dpC = $rtC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND c.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp'];
		}
		if (!empty($params['query']['rt'])){
			$rtC = " AND c.route_id = ".$params['query']['rt'];
		}
		$result = array();
		
			
		$saleCount = $this->query("SELECT SUM(s.cal_qty) as saleQty, s.brand_id, c.route_id  FROM sales s INNER JOIN customers c ON c.id = s.customer_id WHERE $sC $rgC $dpC $rtC GROUP BY c.route_id, s.brand_id");		
		$saleCount2 = $this->query("SELECT count(Distinct s.order_id) as saleCount, c.route_id ,count(Distinct s.customer_id) as scustoCount FROM sales s INNER JOIN customers c ON c.id = s.customer_id WHERE $sC $rgC $dpC $rtC GROUP BY c.route_id");
		
		
		$consumersaleCount = $this->query("SELECT SUM(s.cal_qty) as saleQty, s.brand_id, c.route_id  FROM consumer_sales s INNER JOIN consumers c ON c.id = s.consumer_id WHERE $sC $rgC $dpC $rtC GROUP BY c.route_id, s.brand_id");		
		$consumersaleCount2 = $this->query("SELECT count(Distinct s.consumer_order_id) as saleCount, c.route_id ,count(Distinct s.consumer_id) as scustoCount FROM consumer_sales s INNER JOIN consumers c ON c.id = s.consumer_id WHERE $sC $rgC $dpC $rtC GROUP BY c.route_id");
		
		
		$rdsaleCount = $this->query("SELECT count(Distinct rs.redistributor_sale_id) as rsCount, SUM(rs.cal_qty) as rsQty, rs.brand_id, c.route_id  FROM redistributor_saleitems rs INNER JOIN customers c ON c.id = rs.customer_id WHERE $rdC $rgC $dpC $rtC GROUP BY c.route_id, rs.brand_id");
		$rdsaleCount2 = $this->query("SELECT count(Distinct rs.redistributor_sale_id) as rsCount, c.route_id , count(Distinct rs.customer_id) as rscustoCount FROM redistributor_saleitems rs INNER JOIN customers c ON c.id = rs.customer_id WHERE $rdC $rgC $dpC $rtC GROUP BY c.route_id");
		
		$crdsaleCount = $this->query("SELECT count(Distinct rs.consumer_redistribution_order_id) as rsCount, SUM(rs.cal_qty) as rsQty, rs.brand_id, c.route_id  FROM consumer_redistribution_sales rs INNER JOIN consumers c ON c.id = rs.consumer_id WHERE $rdC $rgC $dpC $rtC GROUP BY c.route_id, rs.brand_id");
		$crdsaleCount2 = $this->query("SELECT count(Distinct rs.consumer_redistribution_order_id) as rsCount, c.route_id , count(Distinct rs.consumer_id) as rscustoCount FROM consumer_redistribution_sales rs INNER JOIN consumers c ON c.id = rs.consumer_id WHERE $rdC $rgC $dpC $rtC GROUP BY c.route_id");
		
		$visitCount = $this->query("SELECT count(cv.id) as visitCount, c.route_id ,count(Distinct cv.customer_id) as cvcustoCount FROM customer_visits cv INNER JOIN customers c ON c.id = cv.customer_id WHERE $cvC $rgC $dpC $rtC GROUP BY c.route_id");

		$routCusto = $this->query("SELECT count(id) as custCnt,route_id FROM `customers` group by `route_id`");
		
		$routCons = $this->query("SELECT count(id) as consCnt,route_id FROM `consumers` group by `route_id`");
/*
sCall
csCall
rdsCall
cvCall
totCall
totSale
totBrandSale
*/
		if ($saleCount){
			foreach($saleCount as $sale){
				$rtId = $sale['c']['route_id'];
				$bId = $sale['s']['brand_id'];
				
				$saleQty = $sale['0']['saleQty'];
				
				if($rtId){					
					
					if(isset($result[$rtId]['totSale'])){
						$result[$rtId]['totSale'] = $result[$rtId]['totSale'] + $saleQty;
					}else{
						$result[$rtId]['totSale'] = $saleQty;
					}
					
					if(isset($result[$rtId]['totBrandSale'][$bId])){
						$result[$rtId]['totBrandSale'][$bId] = $result[$rtId]['totBrandSale'][$bId] + $saleQty;
					}else{
						$result[$rtId]['totBrandSale'][$bId] = $saleQty;
					}
					
				}
				
			}
		}
		
		if ($saleCount2){
			foreach($saleCount2 as $sale){
				$rtId = $sale['c']['route_id'];
				$saleCount = $sale['0']['saleCount'];
				if($rtId){
					if(isset($result[$rtId]['sCall'])){
						$result[$rtId]['sCall'] = $result[$rtId]['sCall'] + $saleCount;
					}else{
						$result[$rtId]['sCall'] = $saleCount;
					}
					
					if(isset($result[$rtId]['totCall'])){
						$result[$rtId]['totCall'] = $result[$rtId]['totCall'] + $saleCount;
					}else{
						$result[$rtId]['totCall'] = $saleCount;
					}
				}
			}
		}
		
		
		if ($consumersaleCount){
			foreach($consumersaleCount as $sale){
				$rtId = $sale['c']['route_id'];
				$bId = $sale['s']['brand_id'];
				
				$saleQty = $sale['0']['saleQty'];
				
				if($rtId){					
					
					if(isset($result[$rtId]['totSale'])){
						$result[$rtId]['totSale'] = $result[$rtId]['totSale'] + $saleQty;
					}else{
						$result[$rtId]['totSale'] = $saleQty;
					}
					
					if(isset($result[$rtId]['totBrandSale'][$bId])){
						$result[$rtId]['totBrandSale'][$bId] = $result[$rtId]['totBrandSale'][$bId] + $saleQty;
					}else{
						$result[$rtId]['totBrandSale'][$bId] = $saleQty;
					}
					
				}
				
			}
		}
		
		if ($consumersaleCount2){
			foreach($consumersaleCount2 as $sale){
				$rtId = $sale['c']['route_id'];
				$saleCount = $sale['0']['saleCount'];
				if($rtId){
					if(isset($result[$rtId]['csCall'])){
						$result[$rtId]['csCall'] = $result[$rtId]['csCall'] + $saleCount;
					}else{
						$result[$rtId]['csCall'] = $saleCount;
					}
					
					if(isset($result[$rtId]['totCall'])){
						$result[$rtId]['totCall'] = $result[$rtId]['totCall'] + $saleCount;
					}else{
						$result[$rtId]['totCall'] = $saleCount;
					}
				}
			}
		}
		
		if ($rdsaleCount){
			foreach($rdsaleCount as $rdsale){
				$rtId = $rdsale['c']['route_id'];
				$bId = $rdsale['rs']['brand_id'];
				$saleQty = $rdsale['0']['rsQty'];
				
				if($rtId){
					
					if(isset($result[$rtId]['totSale'])){
						$result[$rtId]['totSale'] = $result[$rtId]['totSale'] + $saleQty;
					}else{
						$result[$rtId]['totSale'] = $saleQty;
					}
					
					if(isset($result[$rtId]['totBrandSale'][$bId])){
						$result[$rtId]['totBrandSale'][$bId] = $result[$rtId]['totBrandSale'][$bId] + $saleQty;
					}else{
						$result[$rtId]['totBrandSale'][$bId] = $saleQty;
					}
					
				}
			}
		}
		if ($rdsaleCount2){
			foreach($rdsaleCount2 as $rdsale){
				$rtId = $rdsale['c']['route_id'];
				$saleCount = $rdsale['0']['rsCount'];
				if($rtId){
					if(isset($result[$rtId]['rdsCall'])){
						$result[$rtId]['rdsCall'] = $result[$rtId]['rdsCall'] + $saleCount;
					}else{
						$result[$rtId]['rdsCall'] = $saleCount;
					}
					if(isset($result[$rtId]['totCall'])){
						$result[$rtId]['totCall'] = $result[$rtId]['totCall'] + $saleCount;
					}else{
						$result[$rtId]['totCall'] = $saleCount;
					}
				}
			}
		}
		
		if ($crdsaleCount){
			foreach($crdsaleCount as $rdsale){
				$rtId = $rdsale['c']['route_id'];
				$bId = $rdsale['rs']['brand_id'];
				$saleQty = $rdsale['0']['rsQty'];
				
				if($rtId){
					
					if(isset($result[$rtId]['totSale'])){
						$result[$rtId]['totSale'] = $result[$rtId]['totSale'] + $saleQty;
					}else{
						$result[$rtId]['totSale'] = $saleQty;
					}
					
					if(isset($result[$rtId]['totBrandSale'][$bId])){
						$result[$rtId]['totBrandSale'][$bId] = $result[$rtId]['totBrandSale'][$bId] + $saleQty;
					}else{
						$result[$rtId]['totBrandSale'][$bId] = $saleQty;
					}
					
				}
			}
		}
		if ($crdsaleCount2){
			foreach($crdsaleCount2 as $rdsale){
				$rtId = $rdsale['c']['route_id'];
				$saleCount = $rdsale['0']['rsCount'];
				if($rtId){
					if(isset($result[$rtId]['crdsCall'])){
						$result[$rtId]['crdsCall'] = $result[$rtId]['crdsCall'] + $saleCount;
					}else{
						$result[$rtId]['crdsCall'] = $saleCount;
					}
					if(isset($result[$rtId]['totCall'])){
						$result[$rtId]['totCall'] = $result[$rtId]['totCall'] + $saleCount;
					}else{
						$result[$rtId]['totCall'] = $saleCount;
					}
				}
			}
		}
		
		
		
		
		if ($visitCount){
			foreach($visitCount as $visit){
				$rtId = $visit['c']['route_id'];
				$visitCount = $visit['0']['visitCount'];
				if($rtId){
					if(isset($result[$rtId]['cvCall'])){
						$result[$rtId]['cvCall'] = $result[$rtId]['cvCall'] + $visitCount;
					}else{
						$result[$rtId]['cvCall'] = $visitCount;
					}
					if(isset($result[$rtId]['totCall'])){
						$result[$rtId]['totCall'] = $result[$rtId]['totCall'] + $visitCount;
					}else{
						$result[$rtId]['totCall'] = $visitCount;
					}
				}
			}
		}


		if($routCusto){
			foreach($routCusto as $rc){ 
				$rtId = $rc['customers']['route_id'];
				if($rtId){
					$result[$rtId]['custCnt'] = $rc['0']['custCnt'];
				}
			}
		}
		if($routCons){
			foreach($routCons as $rc){ 
				$rtId = $rc['consumers']['route_id'];
				if($rtId){
					$result[$rtId]['consCnt'] = $rc['0']['consCnt'];
					
					/* if(isset($result[$rtId]['custCnt'])){
						$result[$rtId]['custCnt'] = $result[$rtId]['custCnt'] + $rc['0']['consCnt'];
					}else{
						$result[$rtId]['custCnt'] = $rc['0']['consCnt'];
					} */
				}
			}
		}
		return $result;
	}
	
	
	public function user_rotes($uid = 0){
		$user_rotes = $this->query("SELECT * FROM user_routes AS ur WHERE user_id = $uid"); //pr($user_rotes);
		$uRoutes = array();
		
		$listweek = listWeek(); //pr($listweek);
		$weekDays = getWeek(); //pr($weekDays);
		
		if(!empty($user_rotes)){
			foreach($user_rotes as $ur){
				$route_id = $ur['ur']['route_id'];
				$week = $ur['ur']['week']; 
				$day = $ur['ur']['day'];
				if(isset($listweek[$week]) && isset($weekDays[$day])){
					$uRoutes[$route_id][][$listweek[$week]] = $weekDays[$day];	
				    
				}
			}			
		} 
		return $uRoutes;
	}
	
	public function customerRouteSalesReport($params , $routes){//pr($routes);
		foreach($routes as $Rid => $rt){
			$route[]=$Rid;
		}//pr($route);
		$rids = implode(",", $route); 
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$sC = "s.sale_date = '$date'";
				$rdC = "rs.sale_date = '$date'";
				$cvC = "cv.visiting_date between '$date' AND '$date 23:59:59'";
				//$cvC = "cv.visiting_date LIKE '".$date."%'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
			
			case 'between':
				$d1 =$params['query']['from'];
				if(empty( $params['query']['to'])){
					$d2 = date('Y-m-d');
				}else{
					$d2 = $params['query']['to'];
				}		
				
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
			case 'monthly':
				$d1 =   date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
		}
		$rgC = $dpC = $rtC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND c.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp'];
		}
		if (!empty($params['query']['rt'])){
			$rtC = " AND c.route_id = ".$params['query']['rt'];
		}
		$result = array();
		$customer_name = "concat_ws(' ',c.first_name , c.last_name) as Cname";
		//$sdata = $this->query("SELECT count(s.id) as saleCnt, c.id, $customer_name , c.route_id  FROM  customers c left join sales s ON s.customer_id = c.id WHERE  c.route_id IN ($rids) GROUP BY c.route_id, c.id");
		
		// Check NULL depot_id
		$ndps = "AND s.depot_id != 0";
		
		$sdata = $this->query("SELECT count(Sale.sCnt) as sCnt ,c.id,$customer_name, c.route_id from (select DISTINCT(s.order_id) AS sCnt,s.customer_id as customer from sales s where $sC $ndps) as Sale right join customers as c on c.id=Sale.customer  WHERE  c.route_id IN ($rids)  group by c.route_id, c.id");
		
		// Check NULL redistributor_id
		$nRdId = "AND rs.redistributor_id != 0";
		
		$rsdata = $this->query("SELECT count(RSale.rsCnt) as rsCnt ,c.id,$customer_name,c.route_id from (select DISTINCT(rs.redistributor_sale_id) AS rsCnt,rs.customer_id as customer from redistributor_saleitems rs where $rdC $nRdId) as RSale right join customers as c on c.id=RSale.customer  WHERE  c.route_id IN ($rids)  group by c.route_id, c.id");
		
		// Check NULL depot_id
		$ndpcv = "AND cv.depot_id != 0";

		$cvdata = $this->query("SELECT count(CVisit.cvCnt) as cvCnt ,c.id, $customer_name ,c.route_id from (select cv.id AS cvCnt,cv.customer_id as customer from customer_visits cv where $cvC $ndpcv) as CVisit right join customers as c on c.id=CVisit.customer  WHERE  c.route_id IN ($rids)  group by c.route_id, c.id");
				
		
/*
sCall
rsCall
cvCall
*/
		
		if ($sdata){
			foreach($sdata as $sale){
				$rtId = $sale['c']['route_id'];
				$cId = $sale['0']['Cname'];
				$saleCnt = $sale['0']['sCnt'];
				
				if($rtId){					
					if(isset($result[$rtId][$cId]['sCall'])){
						$result[$rtId][$cId]['sCall'] = $result[$rtId][$cId]['sCall'] + $saleCnt;
						$result[$rtId][$cId]['totCall'] = $result[$rtId][$cId]['sCall'] + $saleCnt;
					}else{
						$result[$rtId][$cId]['sCall'] = $saleCnt;
						$result[$rtId][$cId]['totCall'] = $saleCnt;
					}
				}
			}
		}
		if ($rsdata){
			foreach($rsdata as $rsale){
				$rtId = $rsale['c']['route_id'];
				$cId = $rsale['0']['Cname'];
				$rsaleCnt = $rsale['0']['rsCnt'];
				
				if($rtId){					
					if(isset($result[$rtId][$cId]['rsCall'])){
						$result[$rtId][$cId]['rsCall'] = $result[$rtId][$cId]['rsCall'] + $rsaleCnt;
						$result[$rtId][$cId]['totCall'] = $result[$rtId][$cId]['totCall'] + $rsaleCnt;
					}else{
						$result[$rtId][$cId]['rsCall'] = $rsaleCnt;
						$result[$rtId][$cId]['totCall'] =  $rsaleCnt;
					}
				}
			}
		}
		if ($cvdata){
			foreach($cvdata as $cv){
				$rtId = $cv['c']['route_id'];
				$cId = $cv['0']['Cname'];
				$cvCnt = $cv['0']['cvCnt'];
				
				if($rtId){					
					if(isset($result[$rtId][$cId]['cvCall'])){
						$result[$rtId][$cId]['cvCall'] = $result[$rtId][$cId]['cvCall'] + $cvCnt;
						$result[$rtId][$cId]['totCall'] = $result[$rtId][$cId]['totCall'] + $cvCnt;
					}else{
						$result[$rtId][$cId]['cvCall'] = $cvCnt;
						$result[$rtId][$cId]['totCall'] = $cvCnt;
					}
				}
			}
		}

		//pr($result);
		return $result;
	}
}
