<?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 userWeeklyRoute($user_id = 0, $period ='', $week = '', $day = ''){

		$result = array();

		if($user_id){
			$condition = "user_id = '$user_id'"; 

			if (!empty($period)) {
				$year = date('Y', strtotime($period));
				$month = date('n', strtotime($period));
				$condition .= " AND year = '$year' AND month = '$month'";
			}

			if (!empty($week)) {
				$condition .= " AND week = '$week'";
			}

			if (!empty($day)) {
				$condition .= " AND day = '$day'";
			}

			// Execute the query with the built condition
			$userRoutes = $this->query("SELECT * FROM user_routes AS ur WHERE $condition");
			
			if ($userRoutes){
				foreach ($userRoutes as $userRoute){

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

					//$result[$weekId][] = $routeId;
					$result[$week] = explode(',',$routeIds );

				}

			}
		}
		return $result;
		

	}
	
	//** NO LONGER USED **//
	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;
		}
        $dvC = $rgC = $dpC = $rtC = "";
        if (!empty($params['query']['dv'])) {
            $dvC = " AND c.division_id = " . $params['query']['dv'];
        }
		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 $dvC $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 $dvC $rgC  $dpC $rtC GROUP BY c.route_id");
		$saleCount = $this->query("SELECT SUM(s.cal_qty) as saleQty, s.brand_id, c.route_id,c.status  FROM sales s INNER JOIN customers c ON c.id = s.customer_id WHERE $sC $dvC $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,c.status ,count(Distinct s.customer_id) as scustoCount FROM sales s INNER JOIN customers c ON c.id = s.customer_id WHERE $sC $dvC $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 $dvC $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 $dvC $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 $dvC $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 $dvC $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,c.status  FROM redistributor_saleitems rs INNER JOIN customers c ON c.id = rs.customer_id WHERE $rdC $dvC $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,c.status , count(Distinct rs.customer_id) as rscustoCount FROM redistributor_saleitems rs INNER JOIN customers c ON c.id = rs.customer_id WHERE $rdC $dvC $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 $dvC $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 $dvC $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 $dvC $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 $dvC $rgC $dpC $rtC GROUP BY c.route_id");

        if(!empty($params['cvisityear_tables'])){
            foreach ($params['cvisityear_tables'] as $year => $cv_tableName){
                $visitCounts[$year] = $this->query("SELECT count(cv.id) as visitCount, c.route_id ,count(Distinct cv.customer_id) as cvcustoCount FROM $cv_tableName cv INNER JOIN customers c ON c.id = cv.customer_id WHERE $cvC $dvC $rgC $dpC $rtC GROUP BY c.route_id");
            }
            //pr($visitCounts);
            //pr($params['cvisityear_tables']);
        }
		$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'];
				$custStatus = $sale['c']['status'];

                #For Inactive Customers
                if($custStatus != ACTIVE && $saleQty =='0'){
                    continue;
                }
				
				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'];
				$custStatus = $sale['c']['status']; 

                #For Inactive Customers
                if($custStatus != ACTIVE && $saleCount =='0'){
                    continue;
                }
				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'];
				$custStatus = $rdsale['c']['status'];

                #For Inactive Customers
                if($custStatus != ACTIVE && $saleQty =='0'){
                    continue;
                }
				
				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'];
				$custStatus = $rdsale['c']['status'];

                #For Inactive Customers
                if($custStatus != ACTIVE && $saleCount =='0'){
                    continue;
                }
				
				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 (!empty($visitCounts)){
            foreach ($visitCounts as $year => $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($params);//pr($routes);
	    
	    $dateParams = $params['dateParams'];
	    
		foreach($routes as $Rid => $rt){
			$route[]=$Rid;
		}//pr($route);
		$rids = implode(",", $route); 
		
		switch($params['type']){
			case 'daily':
			    $date = $dateParams['date'];
				$d1 = $date;
				
				$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 = $dateParams['from'];
				$d2 = $dateParams['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 'between':
				// $d1 =$params['query']['from'];
				// if(empty( $params['query']['to'])){
					// $d2 = date('Y-m-d');
				// }else{
					// $d2 = $params['query']['to'];
				// }
				
				$d1 = $dateParams['from'];
				$d2 = $dateParams['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 = $dateParams['from'];
				$d2 = $dateParams['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;
		}
		$dvC = $rgC = $dpC = $rtC = "";
		if (!empty($params['query']['dv'])){
			$dvC = " AND c.division_id = ".$params['query']['dv'];
		}
		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";
		
		$suid = "AND s.user_id = ".$params['uid'];
		
		//$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");
		$sdata = $this->query("SELECT count(Sale.sCnt) as sCnt ,c.id,$customer_name, c.route_id,c.status from (select DISTINCT(s.order_id) AS sCnt,s.customer_id as customer from sales s where $sC $ndps $suid) 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";
		
		$rsuid = "AND rs.user_id = ".$params['uid'];
		
		//$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");
		$rsdata = $this->query("SELECT count(RSale.rsCnt) as rsCnt ,c.id,$customer_name,c.route_id,c.status from (select DISTINCT(rs.redistributor_sale_id) AS rsCnt,rs.customer_id as customer from redistributor_saleitems rs where $rdC $nRdId $rsuid) 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";
		
		$cvuid = "AND cv.user_id = ".$params['uid'];

		//$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");
		//$cvdata = $this->query("SELECT count(CVisit.cvCnt) as cvCnt ,c.id, $customer_name ,c.route_id,c.status 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");
		//pr($params);
        if(!empty($params['cvisityear_tables'])){
            foreach ($params['cvisityear_tables'] as $year => $cv_tableName){ 	
                $cvdatas[$year] = $this->query("SELECT count(CVisit.cvCnt) as cvCnt ,c.id, $customer_name ,c.route_id,c.status from (select cv.id AS cvCnt,cv.customer_id as customer from $cv_tableName cv where $cvC $ndpcv $cvuid) 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"); //pr($cvdatas);
            }
        }

		
		
		if ($sdata){
			foreach($sdata as $sale){
				$rtId = $sale['c']['route_id'];
				$cId = $sale['0']['Cname'];
				$saleCnt = $sale['0']['sCnt'];
				$custStatus = $sale['c']['status'];
                $result[$rtId][$cId]['saleEntry'] = $sale;
				
				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($custStatus){
                    $result[$rtId][$cId]['custStatus'] = $custStatus;
                }else{
                    if(empty($result[$rtId][$cId]['custStatus'])){
                        $result[$rtId][$cId]['custStatus'] = 0;
                    }
                }
			}
		}
		if ($rsdata){
			foreach($rsdata as $rsale){
				$rtId = $rsale['c']['route_id'];
				$cId = $rsale['0']['Cname'];
				$rsaleCnt = $rsale['0']['rsCnt'];
				$custStatus = $rsale['c']['status'];
                $result[$rtId][$cId]['rSaleEntry'] = $rsale;
				
				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($custStatus){
					$result[$rtId][$cId]['custStatus'] = $custStatus;
				}else{
					if(empty($result[$rtId][$cId]['custStatus'])){
						$result[$rtId][$cId]['custStatus'] = 0;
					}
				}
			}
		}

        if (!empty($cvdatas)) {
            foreach ($cvdatas as $year => $cvdata) {
                foreach($cvdata as $cv){
                    $rtId = $cv['c']['route_id'];
                    $cId = $cv['0']['Cname'];
                    $cvCnt = $cv['0']['cvCnt'];
                    $custStatus = $cv['c']['status'];
                    $result[$rtId][$cId]['cVisitEntry'] = $cv;

                    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;
                        }
                    }
                    if($custStatus){
                        $result[$rtId][$cId]['custStatus'] = $custStatus;
                    }else{
                        if(empty($result[$rtId][$cId]['custStatus'])){
                            $result[$rtId][$cId]['custStatus'] = 0;
                        }
                    }
                }
            }
        }

		//pr($result);
		return $result;
	} */
	
	
	public function customerRouteSalesReport($params , $routes){ //pr($params);//pr($routes);
	    
	    $dateParams = $params['dateParams'];
	    
		foreach($routes as $Rid => $rt){
			$route[]=$Rid;
		} //pr($route);
		$rids = implode(",", $route); 
		
		switch($params['type']){
			case 'daily':
			    $date = $dateParams['date'];
				$d1 = $date;
				
				$inC = "inc.incentive_date = '$date'";
				$sC = "s.sale_date = '$date'";
				$fiC = "fi.sale_date = '$date'";
				$rdC = "rs.sale_date = '$date'";
				$rfiC = "rfi.sale_date = '$date'";
				$cvC = "cv.visiting_date between '$date' AND '$date 23:59:59'";
			break;
			case 'weekly':
				$d1 = $dateParams['from'];
				$d2 = $dateParams['to'];
				
				$inC = "inc.incentive_date between '$d1' AND '$d2'";
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$fiC = "fi.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$rfiC = "rfi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
			
			case 'between':

				$d1 = $dateParams['from'];
				$d2 = $dateParams['to'];
				
				$inC = "inc.incentive_date between '$d1' AND '$d2'";
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$fiC = "fi.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$rfiC = "rfi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
			case 'monthly':
				$d1 = $dateParams['from'];
				$d2 = $dateParams['to'];
				
				$inC = "inc.incentive_date between '$d1' AND '$d2'";
				$sC = "s.sale_date between '$d1' AND '$d2'";
				$fiC = "fi.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$rfiC = "rfi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
			break;
		}
		$dvC = $rgC = $dpC = $rtC = "";
		if (!empty($params['query']['dv'])){
			$dvC = " AND c.division_id = ".$params['query']['dv'];
		}
		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";
		
		// Check NULL depot_id
		$ndps = "AND s.depot_id != 0";		
		$suid = "AND s.user_id = ".$params['uid'];
		
		$sldata = $this->query("SELECT SUM(s.cal_qty) AS qtySale, s.customer_id, s.sale_date, s.brand_group_id, c.id, c.route_id FROM sales AS s INNER JOIN customers AS c ON c.id = s.customer_id WHERE s.via_app = 1 AND c.route_id IN ($rids) AND $sC $ndps $suid GROUP BY c.route_id, c.id, s.brand_group_id "); //pr($sldata); 
		

		
				
		if ($sldata){			
			foreach ($sldata as $sd){
				$rid = $sd['c']['route_id'];
				$cid = $sd['c']['id'];
				$bid = $sd['s']['brand_group_id'];
				$callDate = $sd['s']['sale_date'];
				

				$result[$rid][$cid]['brands'][$bid]['sldata']['qty'] = $sd['0']['qtySale'];
				
				
				// Ensure 'call_dates' is initialized as an array
				if (!isset($result[$rid][$cid]['sale_dates'])) {
					$result[$rid][$cid]['sale_dates'] = [];
				}
				// Only add the date if it's not already in the array
				if (!in_array($callDate, $result[$rid][$cid]['sale_dates'])) {
					$result[$rid][$cid]['sale_dates'][] = $callDate;
				}
				
				// Ensure 'call_dates' is initialized as an array
				if (!isset($result[$rid][$cid]['call_dates'])) {
					$result[$rid][$cid]['call_dates'] = [];
				}
				// Only add the date if it's not already in the array
				if (!in_array($callDate, $result[$rid][$cid]['call_dates'])) {
					$result[$rid][$cid]['call_dates'][] = $callDate;
				}
				
			} 
		}

		$ndps = "AND fi.depot_id != 0";		
		$suid = "AND fi.user_id = ".$params['uid'];
		
		$fidata = $this->query("SELECT SUM(fi.cal_qty) AS qtyFi, fi.brand_group_id, c.id, c.route_id FROM free_items AS fi INNER JOIN customers AS c ON c.id = fi.customer_id WHERE fi.via_app = 1 AND c.route_id IN ($rids) AND $fiC $ndps $suid GROUP BY c.route_id, c.id, fi.brand_group_id ");
		
		if ($fidata){			
			foreach ($fidata as $sd){
				$rid = $sd['c']['route_id'];
				$cid = $sd['c']['id'];
				$bid = $sd['fi']['brand_group_id'];
				$result[$rid][$cid]['brands'][$bid]['fidata']['qty'] = $sd['0']['qtyFi'];			
			} 
		}


		//rsale

		
		// Check NULL redistributor_id
		$nRdId = "AND rs.redistributor_id != 0";
		$ndps = "AND rs.depot_id != 0";		
		$suid = "AND rs.user_id = ".$params['uid'];

		
		$rsdata = $this->query("SELECT SUM(rs.cal_qty) AS qtyRSale, rs.sale_date, rs.brand_group_id, c.id, c.route_id FROM redistributor_saleitems AS rs INNER JOIN customers AS c ON c.id = rs.customer_id WHERE rs.via_app = 1 AND c.route_id IN ($rids) AND $rdC $ndps $suid $nRdId GROUP BY c.route_id, c.id, rs.brand_group_id "); //pr($rsdata);
		
		if ($rsdata){			
			foreach ($rsdata as $sd){
				$rid = $sd['c']['route_id'];
				$cid = $sd['c']['id'];
				$bid = $sd['rs']['brand_group_id'];
				$callDate = $sd['rs']['sale_date'];
				
				$result[$rid][$cid]['brands'][$bid]['rsdata']['qty'] = $sd['0']['qtyRSale'];
				
				// Ensure 'call_dates' is initialized as an array
				if (!isset($result[$rid][$cid]['rsale_dates'])) {
					$result[$rid][$cid]['rsale_dates'] = [];
				}
				// Only add the date if it's not already in the array
				if (!in_array($callDate, $result[$rid][$cid]['rsale_dates'])) {
					$result[$rid][$cid]['rsale_dates'][] = $callDate;
				}
				
				// Ensure 'call_dates' is initialized as an array
				if (!isset($result[$rid][$cid]['call_dates'])) {
					$result[$rid][$cid]['call_dates'] = [];
				}
				// Only add the date if it's not already in the array
				if (!in_array($callDate, $result[$rid][$cid]['call_dates'])) {
					$result[$rid][$cid]['call_dates'][] = $callDate;
				}				
			} 
		}
		
		/* $ndps = "AND rfi.depot_id != 0";		
		$suid = "AND rfi.user_id = ".$params['uid'];
		
		$rfidata = $this->query("SELECT SUM(rfi.cal_qty) AS qtyRFi, rfi.brand_id, c.id, c.route_id FROM rd_free_items AS rfi INNER JOIN customers AS c ON c.id = rfi.customer_id WHERE rfi.via_app = 1 AND c.route_id IN ($rids) AND $rfiC $ndps $suid GROUP BY c.route_id, c.id, rfi.brand_id ");
		
		if ($rfidata){			
			foreach ($rfidata as $sd){
				$rid = $sd['c']['route_id'];
				$cid = $sd['c']['id'];
				$bid = $sd['fi']['brand_id'];
				
				$result[$rid][$cid]['brands'][$bid]['rfidata']['qty'] = $sd['0']['qtyRFi'];			
			} 
		} */
		
		
		
		// Check NULL depot_id
		$ndpcv = "AND cv.depot_id != 0";
		
		$cvuid = "AND cv.user_id = ".$params['uid'];

        if(!empty($params['cvisityear_tables'])){
            foreach ($params['cvisityear_tables'] as $year => $cv_tableName){ 	
				
				$cvdatas[$year] =  $this->query("SELECT DATE(cv.visiting_date) as visiting_date, c.id, c.route_id FROM $cv_tableName AS cv INNER JOIN customers AS c ON c.id = cv.customer_id WHERE cv.via_app = 1 AND c.route_id IN ($rids) AND $cvC $ndpcv $cvuid GROUP BY c.route_id, c.id");


            }
        }

		


        if (!empty($cvdatas)) {
            foreach ($cvdatas as $year => $cvdata) {
                foreach($cvdata as $cv){ 
					$rid = $cv['c']['route_id'];
					$cid = $cv['c']['id'];
					$callDate = $cv['0']['visiting_date']; 
					
					
					// Ensure 'call_dates' is initialized as an array
					if (!isset($result[$rid][$cid]['cvisit_dates'])) {
						$result[$rid][$cid]['cvisit_dates'] = [];
					}
					// Only add the date if it's not already in the array
					if (!in_array($callDate, $result[$rid][$cid]['cvisit_dates'])) {
						$result[$rid][$cid]['cvisit_dates'][] = $callDate;
					}
				
					// Ensure 'call_dates' is initialized as an array
					if (!isset($result[$rid][$cid]['call_dates'])) {
						$result[$rid][$cid]['call_dates'] = [];
					}
					// Only add the date if it's not already in the array
					if (!in_array($callDate, $result[$rid][$cid]['call_dates'])) {
						$result[$rid][$cid]['call_dates'][] = $callDate;
					}
                }
            }
        }
		
		
		// Check NULL depot_id
		$ndps = "AND inc.depot_id != 0";		
		$inuid = "AND inc.user_id = ".$params['uid'];
		
		$incdata = $this->query("SELECT SUM(inc.cal_qty) AS qtyInc, inc.incentive_date, inc.brand_group_id, c.id, c.route_id FROM incentives AS inc INNER JOIN customers AS c ON c.id = inc.customer_id WHERE inc.via_app = 1 AND c.route_id IN ($rids) AND $inC $ndps $inuid GROUP BY c.route_id, c.id, inc.brand_group_id "); //pr($sldata);
		
				
		if ($incdata){			
			foreach ($incdata as $sd){
				$rid = $sd['c']['route_id'];
				$cid = $sd['c']['id'];
				$bid = $sd['inc']['brand_group_id'];
				$callDate = $sd['inc']['incentive_date'];
				
				$result[$rid][$cid]['brands'][$bid]['incdata']['qty'] = $sd['0']['qtyInc'];
				
				// Ensure 'call_dates' is initialized as an array
				if (!isset($result[$rid][$cid]['inc_dates'])) {
					$result[$rid][$cid]['inc_dates'] = [];
				}
				// Only add the date if it's not already in the array
				if (!in_array($callDate, $result[$rid][$cid]['inc_dates'])) {
					$result[$rid][$cid]['inc_dates'][] = $callDate;
				}
					
				// Ensure 'call_dates' is initialized as an array
				if (!isset($result[$rid][$cid]['call_dates'])) {
					$result[$rid][$cid]['call_dates'] = [];
				}
				// Only add the date if it's not already in the array
				if (!in_array($callDate, $result[$rid][$cid]['call_dates'])) {
					$result[$rid][$cid]['call_dates'][] = $callDate;
				}
			} 
		}

	//pr($result); //die();
		return $result;
	}
	
	/* public function getUserDayRouteIds($uid,$date){

		$day =date('D', strtotime($date));
		$week = getY3Week($date);

		$userDayRoutes = $this->query("SELECT route_id FROM user_routes WHERE user_id = $uid AND week = $week AND day LIKE '$day%'");	
		$routeIds = [];
		foreach ($userDayRoutes as $row) {
			$routeIds[] = $row['user_routes']['route_id'];
		}
		return $routeIds;
	} */
	
	public function getUserDayRouteIds($uid = null,$date="",$PTGCalendar=[]){		
		
		$userRouteCond['UserRoute.user_id'] = $uid;
			
		/* $userRouteCond['UserRoute.year'] = date('Y', strtotime($date));
		$userRouteCond['UserRoute.month'] = $PTGCalendar['month'];
		$userRouteCond['UserRoute.week']= $PTGCalendar['week']; */
		
		$rpPeriod = getRoutePlanPeriod($date, $PTGCalendar);
		$userRouteCond['UserRoute.year'] = $rpPeriod['year'];
		$userRouteCond['UserRoute.month'] = $rpPeriod['month'];
		$userRouteCond['UserRoute.week']= $rpPeriod['week'];
		
		
		$userRouteCond['UserRoute.day']= date('l', strtotime($date));

		$UserRoute =  ClassRegistry::init('UserRoute');
		$rtIds = $UserRoute->find('list', array('conditions'=>$userRouteCond, 'fields'=>array('user_id', 'route_ids')));
		$routeIds = [];
		if(!empty($rtIds)){
			$routeIds = explode(',', $rtIds[$uid]);
		}
		return $routeIds;
	}
	
	public function weeklyRouteSalesReport($params,$routes){
		//pr($params); die();
		foreach($routes as $Rid => $rt){
			$route[]=$Rid;
		}//pr($route);
		$rids = implode(",", $route);

		$rgC = $dpC = $rtC = $brd = "";
		
		if (!empty($params['query']['brand_id'])){
			$brd = " AND brand_id = ".$params['query']['brand_id'];
		}
		$result = array();
		$customer_name = "concat_ws(' ',c.first_name , c.last_name) as Cname";
		
		

		foreach ($params['dateBuckets'] as $bucket) {
			$date = $bucket['date'];

			$sC = "s.sale_date = '$date'";
			$rdC = "rs.sale_date = '$date'";

			$sldata = $this->query("SELECT SUM(s.cal_qty) as qtySale, s.brand_id, s.unit, c.id, $customer_name, c.route_id from sales as s INNER JOIN customers as c on c.id = s.customer_id WHERE  c.route_id IN ($rids) AND $sC $brd group by c.route_id, c.id, s.brand_id"); //pr($sldata); die();

			if ($sldata){
				foreach ($sldata as $sd){
					$rid = $sd['c']['route_id'];
					$cid = $sd['c']['id'];
					$bid = $sd['s']['brand_id'];
					$qty = $sd[0]['qtySale'];
					$cname = $sd[0]['Cname'];

					$result[$cid]['customer_name'] = $cname;
					//$result[$cid]['brands'][$bid]  = $qty;

					$result[$cid][$bucket['key']]['brands'][$bid] = $qty;
					
				} 
			}

			$rsdata = $this->query("SELECT SUM(rs.cal_qty) as qtyRSale,  rs.brand_id, c.id, $customer_name, c.route_id from redistributor_saleitems as rs INNER JOIN customers as c on c.id = rs.customer_id WHERE  c.route_id IN ($rids) AND $rdC $brd group by c.route_id, c.id, rs.brand_id");//pr($rsdata);
		
			if ($rsdata){
				$previousCid = null;
				foreach ($rsdata as $rs){
					$rid = $rs['c']['route_id'];
					$cid = $rs['c']['id'];
					$bid = $rs['rs']['brand_id'];
					$qty = $rs['0']['qtyRSale'];
					$cname = $rs['0']['Cname'];
					$result[$cid]['customer_name'] = $cname ;
					//$result[$cid]['brands'][$bid]= $qty;
					$result[$cid][$bucket['key']]['brands'][$bid] = $qty;
				} 
			}
		}
		//pr($dateBuckets); die();
		
		return $result;
	}
}
