<?php
/**
 * AjaxController
 *
 * @var string
 */
App::uses('AppController', 'Controller');
class AjaxReportController extends AppController {
	
	var $uses = array('Report');
	
	var $components = array('Option', 'ReportMonitor');
	
	public function beforeFilter() {
        parent::beforeFilter();
        $this->ReportMonitor->start($this);
    }

    public function afterFilter() {
        $this->ReportMonitor->end($this);
    }
	
	function index(){	
		exit;
	}
	
	
	
	function active_users($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();
		
		$query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$activeUsers = $this->Report->activeUsers($params);		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'activeUsers'));
		if(isset($this->request->query['export'])){ 
			$this->set('filename', 'ActiveUsers');	
			$this->render('export_activeusers','export_xls');
		}
	}
	
	function nonactive_users($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();		
		$query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);		
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$reportData = $this->Report->nonactiveUsers($params);
		$nonactiveUsers = $reportData['result'];
		$reportRoles = $reportData['reportRoles'];
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'nonactiveUsers', 'reportRoles'));
		if(isset($this->request->query['export'])){  
			$this->set('filename', 'NonActiveUsers');	
			$this->render('export_nonactive','export_xls');
		}
	}
	
	/* function day_calls($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('User');
		$userConditions = array('User.status'=>ACTIVE);
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
        if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$userConditions['User.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		
		}
		if (!empty($this->request->query['dp'])){
			$userConditions['User.depot_id'] =  $this->request->query['dp'];
		}else if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$userConditions['User.depot_id'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();		
		$query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);		
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		$dayCalls = $this->Report->dayCalls($params);	//pr($dayCalls);	
		
		$users = $this->User->find('all', array('conditions'=>$userConditions));
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'dayCalls', 'users'));
		
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'number_of_call_'.$type);
			$this->render('export_noofcallperday','export_xls');
			
		}
		if (isset($this->request->query['per']) && $this->request->query['per'] == 1){
			if(isset($this->request->query['export'])){   
				$this->set('filename', 'number_of_effective_call_'.$type);
				$this->render('effective_calls_export','export_xls');
			}else{
				$this->render('effective_calls');
			}
		}
	} */
	
	function day_calls($type='daily', $opt=''){
		
		if (isset($this->request->query['per']) && $this->request->query['per'] == 1){
			if(!empty($this->request->query['rdsale'])){
				$reportName = 'Number of effective - Redistribution sales calls';
			}elseif(!empty($this->request->query['combined'])){
				$reportName = 'Number of effective - Sales & Redistribution sales calls(Combined)';
			}else{
				$reportName = 'Number of effective - Sales calls';
			}
		}else{
			$reportName = 'Total Number of Calls';
		}
		
		
		$opt = !empty($opt) ? $opt : date('Y-m-d');
		
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');		
		
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
			$userConditions['User.region_id'] = $division_id;
		}else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
        if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$userConditions['User.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		
		}
		if (!empty($this->request->query['dp'])){
			$userConditions['User.depot_id'] =  $this->request->query['dp'];
		}else if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$userConditions['User.depot_id'] = $this->user['depot_id'];
		}
		
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();			
		$query = $this->request->query;
		$weeks = [];
		
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{

			/*code added for ptg calender*/
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				//pr($year);
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
						//$query['week'] = 1;
					}
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
			/*ptg calender */
			
			$cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		
		//$navParams = $this->_nav_params($type, $opt);	
		$dateParams = $this->_dateParams($type, $opt);
		$params = array_merge($dateParams, compact('dateParams','query', 'type','opt','cvisityear_tables'));
		
			//pr($dayCalls);
		
		$dayCalls = $this->Report->dayCalls($params);
		
		$this->loadModel('Role');
		$systemRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc']));
		$excludedRoles = array_intersect_key($systemRoles, array_flip(getExcludedUserRolesForSale()));
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'dayCalls', 'reportName', 'excludedRoles','weeks'));
		
		if(isset($this->request->query['export'])){   
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('filename', $fileName); 
			$this->render('export_noofcallperday','export_xls');
			
		}
		if (isset($this->request->query['per']) && $this->request->query['per'] == 1){
			if(isset($this->request->query['export'])){   
				$fileName = get_export_file_name($reportName, $dateParams);
				$this->set('filename', $fileName); 
				$this->render('effective_calls_export','export_xls');
			}else{
				$this->render('effective_calls');
			}
		}
	}
	 
	function callreports($type='monthly', $opt=''){
	    $reportName = 'User Monthly Calls Evaluation';
        $opt = !empty($opt) ? $opt : date('Y-m-d');

		$this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
		$regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();
		$query = $this->request->query;	
		$weeks = [];

		/*if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			$type = 'monthly';
		} */

        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            
            /*code added for ptg calender*/
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				//pr($year);
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
						//$query['week'] = 1;
					}
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
			/*ptg calender */
			
			$cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		//$navParams = $this->_nav_params($type, $opt);		
		//$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$dateParams = $this->_dateParams($type, $opt);
        $params = array_merge($dateParams, compact('dateParams','query', 'type','opt','cvisityear_tables'));

		$params['user'] = $this->user;
		$dayCalls = $this->Report->callReports($params);
		
		$this->loadModel('Role');
		$systemRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc']));
		$excludedRoles = array_intersect_key($systemRoles, array_flip(getExcludedUserRolesForVisit()));
		
		// Filter: roles in getExcludedUserRolesForSale but NOT already excluded for visit
		$distictExcludedForSale = array_diff(getExcludedUserRolesForSale(), array_keys($excludedRoles));
		// Get names of these remaining roles
		$distictExcludedSaleRoles = array_intersect_key($systemRoles, array_flip($distictExcludedForSale));
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'dayCalls','weeks','params', 'reportName', 'excludedRoles', 'distictExcludedSaleRoles'));
		
		if(isset($this->request->query['export'])){  
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('filename', $fileName); 
			$this->render('callreports_xls','export_xls');
		}
	}
	
	function getTargetnMonth(){
		$this->autoRender = false;
		$result = [
        	'error' => 0,
        	'message' => ''
    	];
		$type = $this->request->data['type'];
		//pr($this->request->data);
		if($type == 'between' && !empty($this->request->data['from']) && !empty($this->request->data['to'])){
			$fromDate = strtotime($this->request->data['from']);
            $toDate = strtotime($this->request->data['to']);
			
			 // Get PTG Calendar data for both dates
            $fromPTGCalendar = $this->_getPTGCalendarData($this->request->data['from']); 
            $toPTGCalendar = $this->_getPTGCalendarData($this->request->data['to']);
			
			// Check if the month and year are the same
            if ($fromPTGCalendar['month'] !== $toPTGCalendar['month'] || date('Y', $fromDate) !== date('Y', $toDate)) {
                // If months or years are different, display target month 
                $result = [
                	'error' => 1,
                	'message' => 'Please select the target month.'
            	];
            } else {
                // Same month and year
    
				//$month = $fromPTGCalendar['month'];
				$month = isset($fromPTGCalendar['month']) && strlen($fromPTGCalendar['month']) === 1 
    				?'0' . $fromPTGCalendar['month'] : $fromPTGCalendar['month'];
    				
				$result = [
                	'error' => 0,
                	'message' => 'Success',
        			'month' =>  $month // Pass the month to from data
            	];
			}
			
		}
		echo json_encode($result);
		exit;
	}
	
	function effectivecallreports($type='monthly', $opt=''){
	    $opt = !empty($opt) ? $opt : date('Y-m-d'); 
        $reportName = 'User Monthly Effective Calls Evaluation';

		$this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
		$divisions = $this->Division->getList();
		$regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();		
		$query = $this->request->query;
		$weeks = [];

        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            
            /*ptg code start */
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
			/*ptg code end */
			
			$cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
		
		}
        
		//$navParams = $this->_nav_params($type, $opt);		
		//$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$dateParams = $this->_dateParams($type, $opt); 
        $params = array_merge($dateParams, compact('dateParams','query', 'type','opt','cvisityear_tables'));

		$dayCalls = $this->Report->effectiveCallReports($params);
		
		switch($type){
			case 'daily':
				$CreatedVerifiedStr = "till ". date(DATE_FORMAT_SORT,strtotime($dateParams['date']));
			break;
			default:
				$CreatedVerifiedStr = "till ". date(DATE_FORMAT_SORT,strtotime($dateParams['to']));
			break;
		}

		$this->loadModel('Role');
		$systemRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc']));
		$excludedRoles = array_intersect_key($systemRoles, array_flip(getExcludedUserRolesForVisit()));
		
		// Filter: roles in getExcludedUserRolesForSale but NOT already excluded for visit
		$distictExcludedForSale = array_diff(getExcludedUserRolesForSale(), array_keys($excludedRoles));
		// Get names of these remaining roles
		$distictExcludedSaleRoles = array_intersect_key($systemRoles, array_flip($distictExcludedForSale));
		
		$this->set(compact('divisions','regions', 'depots', 'query', 'type','opt', 'dayCalls','weeks','params', 'reportName', 'excludedRoles', 'distictExcludedSaleRoles', 'CreatedVerifiedStr'));
						
		if(isset($this->request->query['export'])){ 
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('filename', $fileName); 
			$this->render('effectivecallreports_xls','export_xls');
        }else{
			$this->render('effectivecallreports');
		}
		
	}
	
	function customerreports($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		$navParams = $this->_nav_params($type, $opt);		
		$params = array_merge($navParams, compact('query', 'type','opt'));
		$customerReport = $this->Report->customerReports($params);		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'customerReport'));
		
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'created_customers_report_'.date('F_Y', $params['strtotime']));
			$this->set('month', date('F Y', $params['strtotime']));
			$this->render('customerreports_xls','export_xls');
		}
	}
/**
 * Action numberof_customer
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function numberof_customers($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();
		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['from']) ){
			$type = 'between';
		}
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
		
		$numberOfCustomers = $this->Report->numberOfCustomers($params);		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'numberOfCustomers'));
		if(isset($this->request->query['export'])){   
				$this->set('filename', 'Number_Of_Customer');	
				$this->render('export_numbercustomer','export_xls');
		}
	}
 /**
 * Action numberof_visits
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function numberof_visits($type='daily', $opt=''){
		
		$reportName = 'Number of Customers Visited';
		
		$opt = !empty($opt) ? $opt : date('Y-m-d');
		
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
		$this->loadModel('Route');
		$this->loadModel('User');

        $rcond = array();
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
			if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
				if (empty($this->request->query['rg'])) {
					$divisionRegion = ClassRegistry::init('DivisionsRegion');
					$rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
					$rcond['region_id IN'] = $rIds;
				}
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['Route.region_id'] = $region_id;
			}
		}
		
		if (!empty($this->request->query['dp'])){
			$depot_id = $this->request->query['dp'];
			$rcond['Route.depot_id'] = $depot_id;
		}else{
			$depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
				$rcond['depot_id'] = $depot_id;
			}
		}
		
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		$users = $this->User->usersList($depot_id); 
		
		$params = $conditions = array();
		
		$query = $this->request->query;
		$weeks = [];
		
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{            			
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
			
			$cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		
		
		//$navParams = $this->_nav_params($type, $opt);	
		//$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$dateParams = $this->_dateParams($type, $opt);	
		$params = array_merge($dateParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$params['isVisit'] = 1;
		
		if(empty($query['cvisit']) && empty($query['sale']) && empty($query['rsale'])){
			$query['cvisit'] = $query['sale'] = $query['rsale'] = 1;
		}
		
		$params['join_customer_visit'] = !empty($query['cvisit']) ? $query['cvisit'] : '';
		$params['join_order'] = !empty($query['sale']) ? $query['sale'] : '';
		$params['join_rsale'] = !empty($query['rsale']) ? $query['rsale'] : '';
		
		
		/*
		$numberofVisits = $this->Report->numberofVisits($params);		
		$this->set(compact('regions', 'depots', 'query', 'type','opt', 'numberofVisits'));
		if(isset($this->request->query['export'])){   
			$this->render('export_visitscustomer','export_xls');
		}*/
		$customerVistited = $this->Report->customerNotvisitConditions($params, $this->paginate, $conditions);
		
		$order=$this->paginate['order'] = array('Customer.first_name'=>'asc', 'Customer.last_name'=>'asc');	
		$group=$this->paginate['group'] = array('Customer.id');

		if(isset($this->request->query['export'])){ 
		    $joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
			}else{
				$offset = 0;
			}
			$customers = $this->Customer->find('all', array('conditions'=>$conditions, 'order'=>$order,'group'=>$group,'joins'=>$joins, 'limit'=>EXPORT_LIMIT, 'offset'=>$offset ));
		}else{
			$customers = $this->paginate('Customer', $conditions);
		}
		
		
		$this->set(compact('divisions', 'regions', 'depots' ,'routes', 'query', 'type','opt', 'customers','offset', 'users', 'weeks', 'reportName', 'dateParams'));
		if(isset($this->request->query['export'])){
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('reportTitle', $fileName);
			
			$this->set('filename', 'customer_visit');
			$this->render('export_visitscustomer','export_xls');
		}
	}
	
/**
 * Action customer_notvisits
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function customer_notvisits($type='daily', $opt=''){
		
		$reportName = 'Number of Customers Not Visited';
		
		$opt = !empty($opt) ? $opt : date('Y-m-d');
		
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
		$this->loadModel('Route');
        $rcond = array();
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
			if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
				if (empty($this->request->query['rg'])) {
					$divisionRegion = ClassRegistry::init('DivisionsRegion');
					$rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
					$rcond['region_id IN'] = $rIds;
				}
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['Route.region_id'] = $region_id;
			}
		}
		
		if (!empty($this->request->query['dp'])){
            $depot_id = $this->request->query['dp'];
			$rcond['depot_id'] = $depot_id;
        }else{
			 $depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
				$rcond['depot_id'] = $depot_id;
			}
		}
		
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		
		$params = $conditions = array();
		$conditions = array('Customer.status' => ACTIVE);
		$conditions['Customer.first_name NOT'] = null;
		$conditions['Customer.last_name NOT'] = null;
		
		$query = $this->request->query;
		$weeks = [];
		
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{           			
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
			
			$cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		//$navParams = $this->_nav_params($type, $opt);	
		//$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$dateParams = $this->_dateParams($type, $opt);	
		$params = array_merge($dateParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$numberofNotVisits = $this->Report->customerNotvisitConditions($params, $this->paginate, $conditions);
		
		$order=$this->paginate['order'] = array('Customer.first_name'=>'asc', 'Customer.last_name'=>'asc');
		
		if(isset($this->request->query['export'])){
			$joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
			}else{
				$offset = 0;
			}
			
			$customers = $this->Customer->find('all', array('conditions'=>$conditions, 'order'=>$order,'joins'=>$joins, 'limit'=>EXPORT_LIMIT, 'offset'=>$offset ));
		}else{
			$customers = $this->paginate('Customer', $conditions);
		}
		//pr($customers);
		$this->set(compact('divisions', 'regions', 'depots', 'routes', 'query', 'type','opt', 'customers','offset', 'weeks', 'reportName', 'dateParams'));
		if(isset($this->request->query['export'])){
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('reportTitle', $fileName);
			
			$this->set('filename', 'customer_not_visit');
			$this->render('export_notvisitcustomer','export_xls');
		}
	}
/**
 * Action average_calls
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function average_calls($type='daily', $opt=''){
	    $reportName = 'Average calls ';
		$opt = !empty($opt) ? $opt : date('Y-m-d');


        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);		
		$params = $conditions = array();
        
	//	pr($params);
		$query = $this->request->query;
	    $weeks = [];

        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            
            if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
			
			$cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
	//	pr($query);
		//$navParams = $this->_nav_params($type, $opt); 
	//	pr($navParams);
		$dateParams = $this->_dateParams($type, $opt);	//pr($dateParams);
		$params = array_merge($dateParams, compact('query', 'type','opt','cvisityear_tables'));
		
		$averageCalls = $this->Report->averageCalls($params);
		//pr($averageCalls);
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'averageCalls','weeks', 'reportName', 'dateParams'));
	}
	
	function customer_trend($type='up', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
		$this->loadModel('User');
		$this->loadModel('Route');
        $rcond = array();

		if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
			if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
				if (empty($this->request->query['rg'])) {
					$divisionRegion = ClassRegistry::init('DivisionsRegion');
					$rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
					$rcond['region_id IN'] = $rIds;
				}
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['Route.region_id'] = $region_id;
			}
		}
		if (!empty($this->request->query['dp'])){
			$depot_id = $this->request->query['dp'];
			$rcond['depot_id'] = $depot_id;
		}else{
			$depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
				$rcond['depot_id'] = $depot_id;
			}
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);	
		$users = $this->User->usersList($depot_id); //pr($usrs);	
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		$query = $this->request->query;
		$etype='';
        if(!empty($query['from1']) && !empty($query['to1']) && !empty($query['from2']) && !empty($query['to2']) ){
            $etype = 'period';
        }
		$params = compact('query', 'type', 'opt','etype');	
		$date = ($opt)?$opt:date('Y-m-d');
		
		$this->Customer->unbindModelAll();
		
		$customerTrend = $this->Report->customerTrend($params, $this->paginate, $conditions);
		  		
		$customers = $this->paginate('Report', $conditions);
		
		//pr($customers);
		$this->set(compact('divisions', 'regions', 'depots','routes', 'users', 'query', 'type', 'customers','date','etype'));
		$this->set($customerTrend);
		if(!empty($this->request->query['export'])){ 
			$offset = $this->request->query['offset'];
 			$this->set('offset',$offset);
			$this->set('filename', 'TrendCustomer_'.$type);
			$this->render('export_trendcustomer','export_xls');
		}
	}
	function _nav_params($type, $opt){
		switch($type){
			case 'daily':			    
				$date = ($opt)?$opt:date('Y-m-d');
				$strtotime = strtotime($date);
				$prev = date('Y-m-d', strtotime('-1 day', $strtotime) );
				$next = date('Y-m-d', strtotime('+1 day', $strtotime) );
				$current = date('M d, Y', $strtotime);
			break;
			case 'weekly':			    
				$date = ($opt)?$opt:date('Y-m-d');
				$strtotime = strtotime('monday this week', strtotime($date));
				$prev = date('Y-m-d', strtotime('-1 week', $strtotime) );
				$next = date('Y-m-d', strtotime('+1 week', $strtotime) );				
				$current = __('From %s to %s', strftime('%b %d, %Y', $strtotime), strftime('%b %d, %Y', strtotime('+6 days', $strtotime)));
			break;
			
			case 'between':			    
				//$date = $this->request->query['from'];
				if (!empty($this->request->query['from'])){
					$date = $this->request->query['from'];
				}else{
					$date = date('Y-m-d');
				}
				
				if (!empty($this->request->query['to'])){
					$date2 = $this->request->query['to'];
				}else{
					$date2 = date('Y-m-d');
				}
				$strtotime = strtotime($date);
				$prev = ''; //date('Y-m-d', strtotime('-1 week', $strtotime) );
				$next = ''; // date('Y-m-d', strtotime('+1 week', $strtotime) );				
				$current = __('From %s to %s', strftime('%b %d, %Y', $strtotime), strftime('%b %d, %Y', strtotime($date2)));
			break;
			case 'monthly':			  
				$date = ($opt)?$opt:date('Y-m-01');
				$strtotime = strtotime($date);
				$prev = date('Y-m', strtotime('-1 month', $strtotime) );
				$next = date('Y-m', strtotime('+1 month', $strtotime) );
				$current = date('M, Y', $strtotime);
			break;			
		}
		$this->set(compact('date', 'strtotime', 'prev', 'next', 'current'));
		return compact('date', 'strtotime', 'prev', 'next', 'current');
	}
	
/******************************************* PK Taks ***********************************/
/**
 * Action total_sale
 * @params: @type (string), @opt(date)
 * return : void()
 */
// 	function total_sale($type='daily', $opt=''){
//         $this->loadModel('Division');
// 		$this->loadModel('Region');
// 		$this->loadModel('Depot');
//         if(!empty($this->request->query['dv'])){
//             $division_id = $this->request->query['dv'];
//         }else{
//             $division_id = 0;
//             if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
//                 $this->request->query['dv'] = $division_id = $this->user['division_id'];
//             }
//         }
// 		if (!empty($this->request->query['rg'])){
// 			$region_id = $this->request->query['rg'];
// 		}else{
// 			$region_id = 0;
// 			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
// 				$this->request->query['rg'] = $region_id = $this->user['region_id'];
// 			}
// 		}
// 		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
// 			$this->request->query['dp'] = $this->user['depot_id'];
// 		}
//         $divisions = $this->Division->getList();
//         $regions = $this->Region->getList($division_id);
// 		$depots = $this->Depot->getList($region_id);
		
// 		$params = array();
		
// 		$query = $this->request->query;		
// 		if(!empty($query['from']) || !empty($query['to']) ){
// 			$type = 'between';
// 		}
// 		$navParams = $this->_nav_params($type, $opt);	
// 		$params = array_merge($navParams, compact('query', 'type','opt'));
		
// 		$numberOfSale = $this->Report->numberOfQuantitySale($params);	//pr($numberOfSale);
		
// 		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'numberOfSale'));
// 		if(isset($this->request->query['export'])){   
// 			$this->set('filename', 'total_sales');
// 			$this->render('export_totlesale','export_xls');
// 		}
// 	}

    function total_sale($type='monthly', $opt=''){
        
        $reportName = 'Total Quantity of Sales';

		$opt = !empty($opt) ? $opt : date('Y-m-d');

        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$user = $this->Auth->user();

		$params = array();	
		$query = $this->request->query;
		$weeks = [];
		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		}
		
		$dateParams = $this->_dateParams($type, $opt);	
		$params = array_merge($dateParams, compact('query', 'type','opt','user'));
		
		$numberOfSale = $this->Report->numberOfQuantitySale($params);	//pr($numberOfSale);
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'weeks', 'numberOfSale', 'reportName'));
		if(isset($this->request->query['export'])){   
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('filename', $fileName);
			$this->render('export_totalsale','export_xls');
		}
	}

	/**
 * Action total_consumer_sale
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function total_consumer_sale($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
        if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();
		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
		
		$numberOfSale = $this->Report->numberOfQuantityConsumerSale($params);	
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'numberOfSale'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'total_consumer_sales');
			$this->render('export_totleconsumersale','export_xls');
		}
	}
	
	
	
/* Action ridistribtution_sales
 * @params: @type (string), @opt(date)
 * return : void()
 */
// 	function ridistribtution_sales($type='daily', $opt=''){
//         $this->loadModel('Division');
// 		$this->loadModel('Region');
// 		$this->loadModel('Depot');
//         if(!empty($this->request->query['dv'])){
//             $division_id = $this->request->query['dv'];
//         }else{
//             $division_id = 0;
//             if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
//                 $this->request->query['dv'] = $division_id = $this->user['division_id'];
//             }
//         }
// 		if (!empty($this->request->query['rg'])){
// 			$region_id = $this->request->query['rg'];
// 		}else{
// 			$region_id = 0;
// 			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
// 				$this->request->query['rg'] = $region_id = $this->user['region_id'];
// 			}
// 		}
// 		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
// 			$this->request->query['dp'] = $this->user['depot_id'];
// 		}
//         $divisions = $this->Division->getList();
//         $regions = $this->Region->getList($division_id);
// 		$depots = $this->Depot->getList($region_id);
		
// 		$params = array();
		
// 		$query = $this->request->query;		
// 		if(!empty($query['from']) || !empty($query['to']) ){
// 			$type = 'between';
// 		}
// 		$navParams = $this->_nav_params($type, $opt);	
// 		$params = array_merge($navParams, compact('query', 'type','opt'));
		
// 		$redistributorOfSale = $this->Report->ridistribtutionSale($params);		
// 		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'redistributorOfSale'));
// 		if(isset($this->request->query['export'])){   
// 			$this->set('filename', 'redistribtution_sales');
// 			$this->render('export_ridistribtutionsale','export_xls');
// 		}
// 	}

    function redistribution_sales($type='monthly', $opt=''){
        
        $reportName = 'Total Quantity of Redistribution Sales';

		$opt = !empty($opt) ? $opt : date('Y-m-d');

        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');

        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$user = $this->Auth->user();

		$params = array();	
		$query = $this->request->query;
		$weeks = [];
		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		}
		
		$dateParams = $this->_dateParams($type, $opt);	
		$params = array_merge($dateParams, compact('query', 'type','opt','user'));
		
		$redistributorOfSale = $this->Report->redistributionSale($params);		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type', 'opt', 'weeks', 'redistributorOfSale', 'reportName'));
		if(isset($this->request->query['export'])){   
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('filename', $fileName);
			$this->render('export_redistributionsale','export_xls');
		}
	}

/* Action totalamount_payment
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function totalamount_payment($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
        $depots = $this->Depot->getList($region_id);
		
		$params = array();
		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
		
		$totalamountOfSale = $this->Report->totalAmountonPayment($params);	//pr($totalamountOfSale);	
		$this->set(compact('divisions','regions', 'depots', 'query', 'type','opt', 'totalamountOfSale'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'total_payment');
			$this->render('export_totalamountpayment','export_xls');
		}
	}

 /* Action totalexpenceamount_sale
  * @params: @type (string), @opt(date)
  * return : void()
  */
// 	function totalexpenceamount($type='daily', $opt=''){
//         $this->loadModel('Division');
// 		$this->loadModel('Region');
// 		$this->loadModel('Depot');
//         if(!empty($this->request->query['dv'])){
//             $division_id = $this->request->query['dv'];
//         }else{
//             $division_id = 0;
//             if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
//                 $this->request->query['dv'] = $division_id = $this->user['division_id'];
//             }
//         }
// 		if (!empty($this->request->query['rg'])){
// 			$region_id = $this->request->query['rg'];
// 		}else{
// 			$region_id = 0;
// 			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
// 				$this->request->query['rg'] = $region_id = $this->user['region_id'];
// 			}
// 		}
// 		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
// 			$this->request->query['dp'] = $this->user['depot_id'];
// 		}
//         $divisions = $this->Division->getList();
//         $regions = $this->Region->getList($division_id);
// 		$depots = $this->Depot->getList($region_id);
		
// 		$params = array();
		
// 		$query = $this->request->query;	
// 		if(!empty($query['from']) || !empty($query['to']) ){
// 			$type = 'between';
// 		}		
// 		$navParams = $this->_nav_params($type, $opt);	
// 		$params = array_merge($navParams, compact('query', 'type','opt'));
		
// 		$totalexpenceamountOfSale = $this->Report->totalExpenceAmounton($params);		
// 		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'totalexpenceamountOfSale'));
// 		if(isset($this->request->query['export'])){   
// 			$this->set('filename', 'total_expense');
// 			$this->render('export_totalexpenceamount','export_xls');
// 		}
// 	}

    function total_expense_amount($type='monthly', $opt=''){
        
        $reportName = 'Total Amount of Expenses';

		$opt = !empty($opt) ? $opt : date('Y-m-d');

        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');

        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$user = $this->Auth->user();

		$params = array();	
		$query = $this->request->query;
		$weeks = [];
		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		}
		
		$dateParams = $this->_dateParams($type, $opt);	
		$params = array_merge($dateParams, compact('query', 'type','opt','user'));
		
		$totalExpenseAmount = $this->Report->totalExpenseAmount($params);		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type', 'opt', 'weeks', 'totalExpenseAmount', 'reportName'));
		if(isset($this->request->query['export'])){   
			$fileName = get_export_file_name($reportName, $dateParams);
			$this->set('filename', $fileName);
			$this->render('export_totalexpenseamount','export_xls');
		}
	}

/* Action averevenue_sale
 * @params: @type (string), @opt(date)
 * return : void()
 */
// 	function averevenue_sale($type='daily', $opt=''){
			
// 		$params = array();
		
// 		$query = $this->request->query;		
// 		if(!empty($query['from']) || !empty($query['to']) ){
// 			$type = 'between';
// 		}
// 		$navParams = $this->_nav_params($type, $opt);	
// 		$params = array_merge($navParams, compact('query', 'type','opt'));
		
// 		$totalaverevenueOfSale = $this->Report->averevenueonSale($params);		
       
// 		$this->set(compact('query', 'type','opt', 'totalaverevenueOfSale'));
		
// 	}

    function averevenue_sale($type='monthly', $opt=''){
		$reportName = 'Average Revenue per Case';

		$opt = !empty($opt) ? $opt : date('Y-m-d');
		
		$user = $this->Auth->user();

		$params = array();
		$query = $this->request->query;
		$weeks = [];

		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		}

		$dateParams = $this->_dateParams($type, $opt);	
		$params = array_merge($dateParams, compact('query', 'type','opt', 'user'));
		
		$totalaverevenueOfSale = $this->Report->averevenueonSale($params);		
       
		$this->set(compact('query', 'type','opt', 'weeks', 'totalaverevenueOfSale', 'reportName'));

	}

	
/* Action avecost_case
 * @params: @type (string), @opt(date)
 * return : void()
 */
	/*function avecost_case($type='daily', $opt=''){
			
		$params = array();		
		$query = $this->request->query;
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
		
		// $this->loadModel('Brand');
		//$brands = $this->Brand->find('list', array('fields' => array('id','case_price'))); 
		$avecostCase = $this->Report->avecostCase($params);		
       
		$this->set(compact('query', 'type','opt', 'avecostCase'));
	}*/
	
	function avecost_case($type='monthly', $opt=''){
		$reportName = 'Average Cost per Case';
		$opt = !empty($opt) ? $opt : date('Y-m-d');

		$user = $this->Auth->user();

		$params = array();		
		$query = $this->request->query;
		$weeks = [];

		/* ptg calender starts */
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			if (!empty($query['period'])) {
				
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));

				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);

				if (!empty($monthlyCalendar)) {
					
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach ($weekrange as $wk => $range) {
						$weeks[$wk] = 'Week ' . $wk . ' (' . date('d M', strtotime($range['start'])) . ' to ' . date('d M', strtotime($range['end'])) . ')';
					}
					if (!empty($query['week'])) {
						$opt = $weekrange[$query['week']]['start'];
						if ($type != 'weekly') {
							$type = 'weekly';
						}
					} else {
						$opt = $weekrange[1]['start'];
						if ($type != 'monthly') {
							$type = 'monthly';
						}
					}
				} else {
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}

			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		}
		/* ptg calender ends */
		$dateParams = $this->_dateParams($type, $opt);
		$params = array_merge($dateParams, compact('query', 'type','opt', 'user')); //pr($params);

		$params['join_incentive'] = !empty($query['inc']) ? $query['inc'] : '';
		$params['join_freeitem'] = !empty($query['fi']) ? $query['fi'] : '';
		//pr($params);


		$avecostCase = $this->Report->avecostCase($params);		
       
		$this->set(compact('query', 'type','opt','weeks','avecostCase','reportName'));
	}



/* Action totalexpenceamount_sale
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function nomaplocation_customer($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
        if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$conditions = $params = array();
		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		$conditions = array("(Customer.latitude = \"\" OR Customer.latitude IS NULL) AND (Customer.longitude = \"\" OR Customer.longitude IS NULL )");

        if(!empty($this->request->query['dv'])){
            $conditions['Customer.division_id'] = $query['dv'];
        }
		if (!empty($query['rg'])){
			$conditions['Customer.region_id'] = $query['rg'];
		}
		if (!empty($query['dp'])){
			$conditions['Customer.depot_id'] = $query['dp'];
		}
		$this->paginate['paramType'] = 'querystring';
		//$this->paginate['limit'] = 50;
		if (!empty($this->request->query['export'])){
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			$noMapCust = $this->Customer->find('all',array('conditions'=>$conditions,  'limit'=>'10000', 'offset'=>$offset ));
		} else {
		    $noMapCust = $this->paginate('Customer', $conditions);
		}
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'noMapCust','offset'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'customer_not_on_map');
			$this->render('export_nomaplocationcustomer','export_xls');
		}
	}
/* Action active_customer
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function active_customer($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();
		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
		$activeCust = $conditions = array(); 

		$this->Report->totalNumberOfActiveCustomer($params, $this->paginate, $conditions, $this->Customer, $activeCust);		
		
		if(isset($this->request->query['export'])){
			$joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
			$order = isset($this->paginate['order']) ? $this->paginate['order'] : array();
			$group = isset($this->paginate['group']) ? $this->paginate['group'] : array();
			$fields = isset($this->paginate['fields']) ? $this->paginate['fields'] : array();
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			$activeCust = $this->Customer->find('all', array('conditions'=>$conditions, 'joins'=>$joins,'group'=>$group,'order'=>$order,'fields'=>$fields, 'limit'=>'10000', 'offset'=>$offset ));
         }else{			
			$activeCust = $this->paginate('Customer', $conditions);
		
		}
		
		$this->set(compact('divisions','regions', 'depots', 'query', 'type','opt', 'activeCust','offset'));
		if(isset($this->request->query['export'])){   
				$this->render('export_activecustomer','export_xls');
		}
		
	}
/* Action totalexpenceamount_sale
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function activepurch_customer($type='daily', $opt=''){
		$this->loadModel('Region');
		$this->loadModel('Depot');
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
		$regions = $this->Region->getList();
		$depots = $this->Depot->getList($region_id);
		
		$params = array();
		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
		
		$purchaseCustomers = $this->Report->purchaseCustomer($params);		
       
		$this->set(compact('regions', 'depots', 'query', 'type','opt', 'purchaseCustomers'));
	}
	
/* Action total_customers
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function total_customers($type='s', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
		//$this->Customer->unbindModelAll();
		/* $conditions = array('Customer.status!=2'); */
		$conditions = array('Customer.status' => ACTIVE);
		$conditions['Customer.first_name NOT'] = null;
		$conditions['Customer.last_name NOT'] = null;
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            $conditions['Customer.division_id'] =  $division_id;
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                $conditions['Customer.division_id'] =  $division_id;
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$conditions['Customer.region_id'] =  $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$conditions['Customer.region_id'] =  $region_id;
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		if (!empty($this->request->query['dp'])){
			$conditions['Customer.depot_id'] =  $this->request->query['dp'];
		}

		$order = $this->paginate['order'] = array('Customer.first_name'=>'asc', 'Customer.last_name'=>'asc');	
		if (!empty($this->request->query['export'])){
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			$customers = $this->Customer->find('all',array('conditions'=>$conditions,'order'=>$order,'limit'=>'10000', 'offset'=>$offset));
		} else {
		    $customers = $this->paginate('Customer', $conditions);
		}
		$this->set(compact('divisions', 'regions', 'depots', 'customers','offset'));
		if(isset($this->request->query['export'])){   
			$this->render('export_totalcustomers','export_xls');
		}
	}
	
/* Action total_redistributors
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function total_redistributors($type='', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Redistributor');
		//$this->Customer->unbindModelAll();
		$conditions = array('Redistributor.status!=2');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $conditions['Redistributor.region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                if (empty($this->request->query['rg'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                    $conditions['Redistributor.region_id IN'] = $rIds;
                }
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$conditions['Redistributor.region_id'] =  $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$conditions['Redistributor.region_id'] =  $region_id;
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		if (!empty($this->request->query['dp'])){
			$conditions['Redistributor.depot_id'] =  $this->request->query['dp'];
		}
		 $order = $this->paginate['order'] = array('Redistributor.first_name'=>'asc', 'Redistributor.last_name'=>'asc');	
       if (!empty($this->request->query['export'])){
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			$redistributors = $this->Redistributor->find('all',array('conditions'=>$conditions,'order'=>$order, 'limit'=>'10000', 'offset'=>$offset));
		} else {   	
		     $redistributors = $this->paginate('Redistributor', $conditions);
		}
		$this->set(compact('divisions', 'regions', 'depots', 'redistributors','offset'));
		if(isset($this->request->query['export'])){   
				$this->set('filename','redistributorscustomers');
				$this->render('export_redistributorscustomers','export_xls');
		}
	}
	
	public function customer_status(){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
		$this->loadModel('Route');
		$this->Customer->unbindModelAll();
		$rcond = $cids = $conditions = array();
		$conditions = array('Customer.status' => ACTIVE);
		$conditions['Customer.first_name NOT'] = null;
		$conditions['Customer.last_name NOT'] = null;
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            $conditions['Customer.division_id'] = $this->request->query['dv'];
			if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                $conditions['Customer.division_id'] = $this->request->query['dv'];
				if (empty($this->request->query['rg'])) {
					$divisionRegion = ClassRegistry::init('DivisionsRegion');
					$rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
					$rcond['region_id IN'] = $rIds;
				}
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$conditions['Customer.region_id'] = $this->request->query['rg'];
			$rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$conditions['Customer.region_id'] = $this->request->query['rg'];
				$rcond['Route.region_id'] = $region_id;
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
			$rcond['depot_id'] = $depot_id;
		}
		
		if (!empty($this->request->query['dp'])){
			$conditions['Customer.depot_id'] = $depot_id = $this->request->query['dp'];
			$rcond['depot_id'] = $depot_id;
		}
		if (!empty($this->request->query['rt'])){
            $conditions['Customer.route_id'] = $this->request->query['rt'];
        }
		if (!empty($this->request->query['sts'])){
			$conditions['cStatus.verify_status'] = $this->request->query['sts'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		$isAll = false;
		$joins = array(array('table'=>'depots', 'alias'=>'Depot2', 'type'=>'left', 'conditions'=>array('Customer.depot_id = Depot2.id') ), array('table'=>'customers_status', 'type'=>'left', 'alias' => 'cStatus','conditions'=> array('Customer.id = cStatus.customer_id') ) );
		$fields = array('Customer.id', 'Customer.first_name', 'Customer.last_name', 'Customer.phone', 'Depot2.title','Customer.route_id', 'cStatus.verify_status');
		$order = array('CONCAT_WS(" ", Customer.first_name, Customer.last_name)'=>'asc');
		if(isset($this->request->query['export'])){ 
			$isAll = true;
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			$customers = $this->Customer->find('all', array('conditions'=>$conditions, 'fields'=>$fields, 'joins'=>$joins, 'order'=>$order, 'limit'=>'10000', 'offset'=>$offset ));
		}else{
			$cids = $params = array();
			$this->paginate['fields'] = $fields;
			$this->paginate['order'] = $order;	
			$this->paginate['joins'] = $joins;	
			$customers = $this->paginate('Customer', $conditions);
			/*if ($customers){
				foreach ($customers as $customer){
					$cids[] = $customer['Customer']['id'];
				}
			}*/
		}
		$cstatus = array(1=>__('Unverified'), 2=>__('Verified'), 3=>__('Fully Verified') );
		//$customer_status = $this->Report->customer_status($cids, $isAll);
		$this->set(compact('divisions', 'regions', 'depots','routes', 'customers', 'customer_status','cstatus'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'customer_status');
			$this->set('offset', $offset);
			$this->render('customer_status_xls','export_xls');
			//$this->render('customer_status_xls');
		}
	}
	
/* Action route_customers
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function route_customers($type='s', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
		$this->loadModel('Route');
		
		/* $conditions = array('Customer.status!=2'); */
		$conditions = array('Customer.status' => ACTIVE);
		$conditions['Customer.first_name NOT'] = null;
		$conditions['Customer.last_name NOT'] = null;
		$rcond = array();
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            $conditions['Customer.division_id'] =  $division_id;
            if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                if (empty($this->request->query['rg'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                    $rcond['region_id IN'] = $rIds;
                }
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$conditions['Customer.region_id'] =  $region_id;
			$rcond['region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		if (!empty($this->request->query['dp'])){
			$conditions['Customer.depot_id'] =  $this->request->query['dp'];
			$depot_id = $this->request->query['dp'];
			$rcond['depot_id'] = $depot_id;
		}
		if (!empty($this->request->query['rt'])){
			$conditions['Customer.route_id'] =  $this->request->query['rt'];
		}
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		$joins =array();// array(array('table'=>'routes', 'type'=>'left', 'alias'=>'Route', 'conditions'=>array('Customer.route_id = Route.id')));
		$fields = array('Customer.id', 'Customer.first_name', 'Customer.last_name','Customer.phone', 'Customer.created','Region.id', 'Region.title', 'Depot.id', 'Depot.title', 'Route.id', 'Route.title');
		$this->Customer->virtualFields['routeName'] = 'Route.title';
		$order = $this->paginate['order'] = array('routeName'=>'asc','Customer.first_name'=>'asc', 'Customer.last_name'=>'asc');	
		$this->paginate['fields'] = $fields;
		$this->paginate['joins'] = $joins;
		if (!empty($this->request->query['export'])){
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			
			$customers = $this->Customer->find('all',array('conditions'=>$conditions,'order'=>$order, 'fields'=>$fields, 'joins'=>$joins, 'limit'=>'10000', 'offset'=>$offset));
		} else {
		    $customers = $this->paginate('Customer', $conditions);
		}
		$this->set(compact('divisions', 'regions', 'depots', 'customers','routes','offset'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'route_customers');
			$this->render('export/route_customers','export_xls');
		}
	}
	
	
/**
 * Action routecustomer_notvisits
 * @params: @type (string), @opt(date)
 * return : void()
 */
	function routecustomer_notvisits($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Route');
		$this->loadModel('Customer');
		$rcond = array();
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                if (empty($this->request->query['rg'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                    $rcond['region_id IN'] = $rIds;
                }
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$rcond['region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['region_id'] = $region_id;
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}

        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = $conditions = array();
		$conditions = array('Customer.status' => ACTIVE);
		$conditions['Customer.first_name NOT'] = null;
		$conditions['Customer.last_name NOT'] = null;
		
		if (!empty($this->request->query['dp'])){
			$rcond['depot_id'] = $this->request->query['dp'];
		}
		if (!empty($this->request->query['rt'])){
			$conditions['Customer.route_id'] =  $this->request->query['rt'];
		}
		$query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		$numberofNotVisits = $this->Report->routeCustNotvisitCond($params, $this->paginate, $conditions);
		$fields = array('Customer.id', 'Customer.first_name', 'Customer.last_name','Customer.phone', 'Customer.created','User.first_name', 'User.last_name', 'Region.id', 'Region.title', 'Depot.id', 'Depot.title', 'Route.id', 'Route.title');
		$this->Customer->virtualFields['routeName'] = 'Route.title';
		$order = $this->paginate['order'] = array('routeName'=>'asc','Customer.first_name'=>'asc', 'Customer.last_name'=>'asc');	
		$this->paginate['fields'] = $fields;
		if(isset($this->request->query['export'])){
		    $joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			$customers = $this->Customer->find('all', array('conditions'=>$conditions, 'order'=>$order,'fields'=>$fields,'joins'=>$joins, 'limit'=>'10000', 'offset'=>$offset ));	
		}else{
			$customers = $this->paginate('Customer', $conditions);
		}
		//pr($customers);
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'customers','routes','offset'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'route_customer_not_visit');
			$this->render('export/routecustomer_notvisits','export_xls');
		}
	}
	
	public function sale_on_route($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Route');
		$this->loadModel('Brand');
		$rcond = array();
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['Route.region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                if (empty($this->request->query['rg'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $this->request->query['dv'])));
                    $rcond['Route.region_id IN'] = $rIds;
                }
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['Route.region_id'] = $region_id;
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
		
		if (!empty($this->request->query['dp'])){
			$rcond['Route.depot_id'] = $this->request->query['dp'];
		}
		if (!empty($this->request->query['rt'])){
			$rcond['Route.id'] = $this->request->query['rt'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$brands = $this->Brand->find('list', array('fields'=>array('id','name'), 'conditions'=>array('status'=>1)));
		$depots = $this->Depot->getList($region_id);
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		$params = array();
		
		$query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		$routeList = $this->Route->find('all', array('conditions'=>$rcond));
		$result = $this->Route->routeSalesReport($params);
		//pr($result);
		$this->set(compact('divisions', 'regions', 'depots', 'brands','query', 'type','opt', 'customers','routes','routeList','result'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'sale_on_route_in_unit');
			$this->render('export/sale_on_route','export_xls');
		}
	}
	
	public function call_route($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Route');
		$rcond = array();
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['Route.region_id IN'] = $rIds;
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                if (empty($this->request->query['rg'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $this->request->query['dv'])));
                    $rcond['Route.region_id IN'] = $rIds;
                }
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['Route.region_id'] = $region_id;
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
		if (!empty($this->request->query['dp'])){
			$rcond['Route.depot_id'] = $this->request->query['dp'];
		}
		if (!empty($this->request->query['rt'])){
			$rcond['Route.id'] = $this->request->query['rt'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));
		$params = array();
		
		$query = $this->request->query;		

        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }

        //pr($cvisityear_tables);
		$navParams = $this->_nav_params($type, $opt);
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		$routeList = $this->Route->find('all', array('conditions'=>$rcond));
		$result = $this->Route->routeSalesReport($params);
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'customers','routes','routeList', 'result'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'call_on_route');
			$this->render('export/call_route','export_xls');
		}
	}
	
	
	
	
	/* public function call_customer_route($type='daily', $opt='', $uid = ''){
	    $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Route');
		$this->loadModel('BrandGroup');
		$this->loadModel('User');
		
		
		
		if(!empty($uid) && empty($this->request->query['us'])){
            $this->request->query['us'] = $uid;
        }
		
		if(!empty($this->request->query['us'])){
            $uid = $this->request->query['us'];
        }
		
		
		$udata = $this->User->findById($uid); //pr($udata);
		
		$rcond = $userSelectCond = array();
		
		$date = $opt = !empty($opt) ? $opt : date('Y-m-d');
		
		
		if(!empty($this->request->query['dv'])){
            $userSelectCond['User.division_id'] = $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }else{
				$this->request->query['dv'] = $udata['User']['division_id'];
			}
        }

		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$userSelectCond['User.region_id'] = $rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['Route.region_id'] = $region_id;
			}else{
				$this->request->query['rg'] = $udata['User']['region_id'];
			}
		}
		
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$userSelectCond['User.depot_id'] = $this->request->query['dp'] = $this->user['depot_id'];
		}else{
			$this->request->query['dp'] = $udata['User']['depot_id'];
		}
		
		
		if (!empty($this->request->query['dp'])){
			$userSelectCond['User.depot_id'] = $rcond['Route.depot_id'] = $this->request->query['dp'];
		}
		if (!empty($this->request->query['rt'])){
			$rcond['Route.id'] = $this->request->query['rt'];
		}
		
		$params = $weeks = array();
		$query = $this->request->query;
		

        if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					

					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
					
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			//$dateParams = $this->_dateParams($type, $opt);
		}
		$dateParams = $this->_dateParams($type, $opt); //pr($dateParams );
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }

		
		$joins = array();
		if($type == 'daily' )
		{
		    $day =date('D', strtotime($dateParams['date'])); 
		    $PTGCalendar = $this->_getPTGCalendarData($dateParams['date']); 			
			$this->set('PTGCalendar',$PTGCalendar);
			
		    //$week = !empty($PTGCalendar['week']) ? getWeekForRoutePlan($PTGCalendar['week']) : getY3Week($date);
			
			## For route plan Anya wants to keep Y3 weeks
			$week = getY3Week($date); 
		    
		    $joins = array(array('table'=>"(SELECT * FROM user_routes WHERE user_id = $uid AND week = $week AND day LIKE '$day%' )", 'type'=>'inner', 'conditions'=>array('Route.id = UserRoute.route_id'), 'alias'=>'UserRoute'));
		}
		elseif($type == 'weekly')
		{			
			## For route plan Anya wants to keep Y3 weeks
			//$week = getY3Week($date);  
			
			//instead of fetching routes from the selected week no. we should find the routes for selected period 
			
			//pr($dateParams);
			$fromDate = strtotime($dateParams['from']);
            $toDate = strtotime($dateParams['to']);
			while ($fromDate <= $toDate) {
				## For route plan Anya wants to keep Y3 weeks
				$iWeek = getY3Week(date('Y-m-d', $fromDate)); 
				$iDay = date('l', $fromDate);
				$weekRoutes[$iWeek][] = $iDay;
				
				$fromDate = strtotime('+1 day', $fromDate);
			}
			
			// Build the joins condition
			$joinsConditions = array();
			foreach ($weekRoutes as $week => $days) {
				$daysList = "'" . implode("','", $days) . "'";
				$joinsConditions[] = "(week = $week AND day IN ($daysList))";
			}
	
			$conditionsString = implode(' OR ', $joinsConditions);
	
			$joins = array(array(
				'table' => "(SELECT * FROM user_routes WHERE user_id = $uid AND ($conditionsString))", 
				'type' => 'inner', 
				'conditions' => array('Route.id = UserRoute.route_id'), 
				'alias' => 'UserRoute'
			)); //pr($joins);
			
			
			//$week = getWeekForRoutePlan($dateParams['week']); pr($week );
		    
		    //$joins = array(array('table'=>"(SELECT route_id FROM user_routes WHERE user_id = $uid AND week = $week )", 'type'=>'inner', 'conditions'=>array('Route.id = UserRoute.route_id'), 'alias'=>'UserRoute'));
		}
		elseif ($type == 'between' && !empty($query['from']) && !empty($query['to'])) {
            $fromDate = strtotime($query['from']);
            $toDate = strtotime($query['to']);
            
            // Get PTG Calendar data for both dates
            $fromPTGCalendar = $this->_getPTGCalendarData($query['from']); 
            $toPTGCalendar = $this->_getPTGCalendarData($query['to']);
        
            // Check if the month and year are the same
            if ($fromPTGCalendar['month'] !== $toPTGCalendar['month'] || date('Y', $fromDate) !== date('Y', $toDate)) {
                // If months or years are different, get all routes for the user
                $joins = array(array('table'=>"(SELECT * FROM user_routes WHERE user_id = $uid)", 'type'=>'inner', 'conditions'=>array('Route.id = UserRoute.route_id'), 'alias'=>'UserRoute'));
            } else {
                // Same month and year, but different weeks or same week
								
				$PTGMonthCalendar = $fromPTGCalendar['calendar'][$fromPTGCalendar['month']];
				$this->set('PTGMonthCalendar',$PTGMonthCalendar); 
				

                $weekRoutes = array();
        
                // Get the week number for 'from' and 'to' dates
                //$fromWeek = getWeekNo($fromPTGCalendar['calendar'][$fromPTGCalendar['month']], date('Y-m-d', $fromDate));
                //$toWeek = getWeekNo($toPTGCalendar['calendar'][$toPTGCalendar['month']], date('Y-m-d', $toDate));
								
				## For route plan Anya wants to keep Y3 weeks
				$fromWeek = getY3Week(date('Y-m-d', $fromDate));
				$toWeek = getY3Week(date('Y-m-d', $toDate));
        
                if ($fromWeek == $toWeek) {
                    // If both dates fall in the same week
                    $iWeek = $fromWeek;
                    $iDay = date('l', $fromDate);
                    $weekRoutes[$iWeek][] = $iDay;
                    
                    // Process all days in this week
                    while ($fromDate <= $toDate) {
                        $iDay = date('l', $fromDate);
                        $weekRoutes[$iWeek][] = $iDay;
                        $fromDate = strtotime('+1 day', $fromDate);
                    }
                } else {
                    // Different weeks in the same month
                    while ($fromDate <= $toDate) {
                        //$iWeek = getWeekNo($fromPTGCalendar['calendar'][$fromPTGCalendar['month']], date('Y-m-d', $fromDate));
						## For route plan Anya wants to keep Y3 weeks
						$iWeek = getY3Week(date('Y-m-d', $fromDate)); 
                        $iDay = date('l', $fromDate);
                        $weekRoutes[$iWeek][] = $iDay;
                        
                        $fromDate = strtotime('+1 day', $fromDate);
                    }
                }
        
                // Build the joins condition
                $joinsConditions = array();
                foreach ($weekRoutes as $week => $days) {
                    $daysList = "'" . implode("','", $days) . "'";
                    $joinsConditions[] = "(week = $week AND day IN ($daysList))";
                }
        
                $conditionsString = implode(' OR ', $joinsConditions);
        
                $joins = array(array(
                    'table' => "(SELECT * FROM user_routes WHERE user_id = $uid AND ($conditionsString))", 
                    'type' => 'inner', 
                    'conditions' => array('Route.id = UserRoute.route_id'), 
                    'alias' => 'UserRoute'
                ));
            }
        }
		else
		{
		    $joins = array(array('table'=>"(SELECT * FROM user_routes WHERE user_id = $uid)", 'type'=>'inner', 'conditions'=>array('Route.id = UserRoute.route_id'), 'alias'=>'UserRoute')); 
		}
		//pr($joins);
		//$routes = $this->Route->find('list', array('conditions'=>$rcond,'joins' => $joins, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));  //pr($routes);
	
		$order = array(
			'UserRoute.week' => 'ASC',
			'FIELD(UserRoute.day, "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")' => 'ASC'
		);

		$assignedRoutes = $this->Route->find('all', array('joins' => $joins, 'conditions' => $rcond, 'order' => $order, 'fields' => array('Route.id', 'Route.title', 'UserRoute.week', 'UserRoute.day', 'UserRoute.route_id'))); //pr($assignedRoutes);
		
		
		if($type == 'monthly'){
			$PTGCalendar = $this->_getPTGCalendarData($dateParams['from']); //pr($PTGCalendar);
			$PTGMonthCalendar = !empty($PTGCalendar['calendar'][$dateParams['month']]) ? $PTGCalendar['calendar'][$dateParams['month']] : []; 
			
			$this->set('PTGMonthCalendar', $PTGMonthCalendar);
		}
		
		
		$routes = $routPlan = [];
		if(!empty($assignedRoutes)){

			foreach($assignedRoutes as $userRoute){
				$routes[$userRoute['Route']['id']] = $userRoute['Route']['title'];
				
				$routPlan[$userRoute['UserRoute']['week']][$userRoute['UserRoute']['day']] = $userRoute['Route']['title'];
			}
		}//pr($routes);
		$this->set('routPlan',$routPlan); //pr($routPlan);
		
	
		$navParams = $this->_nav_params($type, $opt);	
		
		$params = array_merge($navParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'uid'));
		
	####	$params = array_merge(compact('dateParams','query', 'type','opt','cvisityear_tables', 'uid'));

		
	//	$routeList = $this->Route->find('all', array('conditions'=>$rcond));
		$result = array();
		if(!empty($routes)){
			$result = $this->Route->customerRouteSalesReport($params , $routes); //pr($result);
			
			$brands = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));
			
			$this->loadModel('Customer'); 
			
			$this->Customer->unbindAllModels();
			$customers = $this->Customer->find('all', array('fields'=>array('id', 'name', 'route_id', 'status', 'user_id'), 'conditions'=>array('route_id'=>array_keys($routes)), 'order' => array('name')));  //pr($customers );

			
			$this->loadModel('User');
			$this->User->unbindAllModels();
			$allusers = $this->User->find('list', array('fields'=>array('id', 'name'), 'order' => array('name')));  //pr($allusers );  
			//die();
			
			
			if($type ==  "daily"){
				$this->loadModel('Order');
				$tdata['o'] = $this->Order->find('first', array('fields'=>array('MIN(Order.created) AS minTime', 'MAX(Order.created) AS maxTime'), 'conditions'=>array('Order.user_id'=>$uid, 'Order.created LIKE'=>$params['date'].'%' ))); 
				
				$this->loadModel('RedistributorSale');
				$tdata['rs'] = $this->RedistributorSale->find('first', array('fields'=>array('MIN(RedistributorSale.created) AS minTime', 'MAX(RedistributorSale.created) AS maxTime'), 'conditions'=>array('RedistributorSale.user_id'=>$uid, 'RedistributorSale.created LIKE'=>$params['date'].'%'  ))); 
				
				$this->loadModel('CustomerVisit');
				//$tdata['cv'] = $this->CustomerVisit->find('first', array('fields'=>array('MIN(CustomerVisit.created) AS minTime', 'MAX(CustomerVisit.created) AS maxTime'), 'conditions'=>array('CustomerVisit.user_id'=>$uid, 'CustomerVisit.created LIKE'=>$params['date'].'%' ))); 
				
				if(!empty($cvisityear_tables)){
					$cv_tableName = $cvisityear_tables[key($cvisityear_tables)]; // Single customer_visits table as per year wise organized
				}else{
					$cv_tableName ='customer_visits';
				}//echo $cv_tableName;
				$tdata_cv = $this->CustomerVisit->query("SELECT MIN(CustomerVisit.created) AS minTime,MAX(CustomerVisit.created) AS maxTime FROM $cv_tableName as CustomerVisit WHERE CustomerVisit.user_id = '$uid' AND CustomerVisit.created LIKE '".$params['date']."%'  LIMIT 1");
				$tdata['cv'] = isset($tdata_cv['0'])? $tdata_cv['0'] : array();
				
				//pr($tdata);
				
				$minTime = $maxTime = "00:00:00";
				if(!empty($tdata)){
						if(!empty($tdata['o'][0]['minTime'])){
							$time['o_in_time'] = $tdata['o'][0]['minTime'];
						}
						if(!empty($tdata['o'][0]['maxTime'])){
							$time['o_out_time'] = $tdata['o'][0]['maxTime'];
						}
						
						if(!empty($tdata['rs'][0]['minTime'])){
							$time['rs_in_time'] = $tdata['rs'][0]['minTime'];
						}
						if(!empty($tdata['rs'][0]['maxTime'])){
							$time['rs_out_time'] = $tdata['rs'][0]['maxTime'];
						}
						
						if(!empty($tdata['cv'][0]['minTime'])){
							$time['cv_in_time'] = $tdata['cv'][0]['minTime'];
						}
						if(!empty($tdata['cv'][0]['maxTime'])){
							$time['cv_out_time'] = $tdata['cv'][0]['maxTime'];
						}					
					//pr($time);
					$time = isset($time) ? $time : array(null);
					
					$min = min($time);
					$inTime = array_search($min,$time);
					$minTime = $time[$inTime];
					
					$max = max($time);
					$outTime = array_search($max,$time);
					$maxTime = $time[$outTime];
				}
			}
		}
		
		
		$this->_selectOptions($userSelectCond);
		//$this->set(compact('divisions', 'regions', 'depots', 'users'));
		
		$this->set(compact('udata', 'query', 'type','opt', 'brands', 'customers', 'assignedRoutes','routes','routeList', 'result', 'uid', 'allusers', 'minTime', 'maxTime', 'weeks'));
		if(isset($this->request->query['export'])){
			$fileName = 'User\'s Route Customer Activity';

			$fileName .= '_' . $dateParams['title'];
			if(!empty($dateParams['sub_title'])){
				$fileName .= '(' . $dateParams['sub_title'].')';
			}
			
			$this->set('filename', $fileName);
			$this->render('export/customer_call_route','export_xls');
		}
	} */
	
	public function call_customer_route($type='daily', $opt='', $uid = ''){
	    $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Route');
		$this->loadModel('UserRoute');
		$this->loadModel('BrandGroup');
		$this->loadModel('User');
		
		
		
		if(!empty($uid) && empty($this->request->query['us'])){
            $this->request->query['us'] = $uid;
        }
		
		if(!empty($this->request->query['us'])){
            $uid = $this->request->query['us'];
        }
		
		
		$udata = $this->User->findById($uid); //pr($udata);
		
		$rcond = $userSelectCond = array();
		
		$date = $opt = !empty($opt) ? $opt : date('Y-m-d');
		
		
		if(!empty($this->request->query['dv'])){
            $userSelectCond['User.division_id'] = $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }else{
				$this->request->query['dv'] = $udata['User']['division_id'];
			}
        }

		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$userSelectCond['User.region_id'] = $rcond['Route.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
				$rcond['Route.region_id'] = $region_id;
			}else{
				$this->request->query['rg'] = $udata['User']['region_id'];
			}
		}
		
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$userSelectCond['User.depot_id'] = $this->request->query['dp'] = $this->user['depot_id'];
		}else{
			$this->request->query['dp'] = $udata['User']['depot_id'];
		}
		
		
		if (!empty($this->request->query['dp'])){
			$userSelectCond['User.depot_id'] = $rcond['Route.depot_id'] = $this->request->query['dp'];
		}
		
		
		$params = $weeks = array();
		$query = $this->request->query;
		
        if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}					

					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
					
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			//$dateParams = $this->_dateParams($type, $opt);
		}
		$dateParams = $this->_dateParams($type, $opt); //pr($dateParams );
		
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		

		/* $PTGCalendar = $this->_getPTGCalendarData($dateParams['date']); 			
		$this->set('PTGCalendar',$PTGCalendar); */
		
		$userRouteCond['UserRoute.user_id'] = $uid;
		
		if (in_array($type, ['daily', 'weekly', 'monthly'])) {
			
			$userRouteCond['UserRoute.year'] = ($type == 'daily') ? date('Y', strtotime($dateParams['date'])) : date('Y', strtotime($dateParams['from']));
			
			if($type == 'daily'){
				$userRouteCond['UserRoute.year'] = date('Y', strtotime($dateParams['date']));				
				$PTGCalendar = $this->_getPTGCalendarData($dateParams['date']);
				$userRouteCond['UserRoute.month'] = $PTGCalendar['month'];
				$userRouteCond['UserRoute.week'] = $PTGCalendar['week'];
				$userRouteCond['UserRoute.day'] = date('l', strtotime($dateParams['date']));
			}else{
				$userRouteCond['UserRoute.year'] = date('Y', strtotime($dateParams['from']));
				$userRouteCond['UserRoute.month'] = $dateParams['month'];
				
				if(!empty($dateParams['week'])){
					$userRouteCond['UserRoute.week'] = $dateParams['week'];
				}
			}			
			
		}elseif($type == 'between' && !empty($query['from']) && !empty($query['to'])) {
			############ NEED TO WORK ON THIS SECTION ####################
			/* $fromDate = strtotime($query['from']);
			$toDate = strtotime($query['to']);
			
			// Get PTG Calendar data for both dates
            $fromPTGCalendar = $this->_getPTGCalendarData($query['from']); 
            $toPTGCalendar = $this->_getPTGCalendarData($query['to']);	 */
				
		}
		
		$order = array(
			'UserRoute.year' => 'DESC', 'UserRoute.month' => 'DESC', 'UserRoute.week' => 'ASC',
			'FIELD(UserRoute.day, "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")' => 'ASC'
		);
		
		/* $joins = array(
			'table' => 'routes', 'type' => 'inner', 
			'conditions' => [
                // Using FIND_IN_SET to check if Route.id is in UserRoute.route_id
                'FIND_IN_SET(Route.id, UserRoute.route_ids) > 0',
            ], 'alias' => 'Route' 
		); */
		
		//pr($userRouteCond);
		
		$userRouteData = $this->UserRoute->find('all', array('conditions' => $userRouteCond, 'order' => $order)); 
	
		$assignedRoutes = $routeIds = $routeCond = [];
		if(!empty($userRouteData)){
			foreach ($userRouteData as $route) {
				$i_year = $route['UserRoute']['year'];
				$i_month = $route['UserRoute']['month'];
				$i_week = $route['UserRoute']['week'];
				$i_day = $route['UserRoute']['day'];
				$rids = explode(',', $route['UserRoute']['route_ids']);
				$assignedRoutes[$i_year][$i_month][$i_week][$i_day] = $rids;
				
				$routeIds = array_merge($routeIds, $rids);
			}
			
			$assignedRouteNames = $this->Route->find('list', array('fields' => ['id', 'title'], 'conditions' => ['id' =>$routeIds])); //pr(count($assignedRouteNames)); //pr($assignedRouteNames);
			
			//pr($routeIds); 	
			if (!empty($this->request->query['rt'])){
				$routeCond['Route.id'] = $this->request->query['rt'];
			}
		
			$reportUserRoutes = $this->Route->find('list', array('fields' => ['id', 'title'], 'conditions' => ['id' =>$routeIds, $routeCond])); //pr($assignedRoutes); //pr($reportUserRoutes); die();	
		}
		
		
		$params = array_merge($dateParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'uid'));
		
		
	####	$params = array_merge(compact('dateParams','query', 'type','opt','cvisityear_tables', 'uid'));

		
	//	$routeList = $this->Route->find('all', array('conditions'=>$rcond));
		$result = array();
		if(!empty($reportUserRoutes)){
			$result = $this->Route->customerRouteSalesReport($params , $reportUserRoutes); //pr($result);
			
			$brands = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));
			
			$this->loadModel('Customer'); 
			
			$this->Customer->unbindAllModels();
			$customers = $this->Customer->find('all', array('fields'=>array('id', 'name', 'route_id', 'status', 'user_id'), 'conditions'=>array('route_id'=>array_keys($reportUserRoutes)), 'order' => array('name')));  //pr($customers );

			
			$this->loadModel('User');
			$this->User->unbindAllModels();
			$allusers = $this->User->find('list', array('fields'=>array('id', 'name'), 'order' => array('name')));  //pr($allusers );  
			//die();
			
			
			/* if($type ==  "daily"){
				$this->loadModel('Order');
				$tdata['o'] = $this->Order->find('first', array('fields'=>array('MIN(Order.created) AS minTime', 'MAX(Order.created) AS maxTime'), 'conditions'=>array('Order.user_id'=>$uid, 'Order.created LIKE'=>$params['date'].'%' ))); 
				
				$this->loadModel('RedistributorSale');
				$tdata['rs'] = $this->RedistributorSale->find('first', array('fields'=>array('MIN(RedistributorSale.created) AS minTime', 'MAX(RedistributorSale.created) AS maxTime'), 'conditions'=>array('RedistributorSale.user_id'=>$uid, 'RedistributorSale.created LIKE'=>$params['date'].'%'  ))); 
				
				$this->loadModel('CustomerVisit');
				//$tdata['cv'] = $this->CustomerVisit->find('first', array('fields'=>array('MIN(CustomerVisit.created) AS minTime', 'MAX(CustomerVisit.created) AS maxTime'), 'conditions'=>array('CustomerVisit.user_id'=>$uid, 'CustomerVisit.created LIKE'=>$params['date'].'%' ))); 
				
				if(!empty($cvisityear_tables)){
					$cv_tableName = $cvisityear_tables[key($cvisityear_tables)]; // Single customer_visits table as per year wise organized
				}else{
					$cv_tableName ='customer_visits';
				}//echo $cv_tableName;
				$tdata_cv = $this->CustomerVisit->query("SELECT MIN(CustomerVisit.created) AS minTime,MAX(CustomerVisit.created) AS maxTime FROM $cv_tableName as CustomerVisit WHERE CustomerVisit.user_id = '$uid' AND CustomerVisit.created LIKE '".$params['date']."%'  LIMIT 1");
				$tdata['cv'] = isset($tdata_cv['0'])? $tdata_cv['0'] : array();
				
				//pr($tdata);
				
				$minTime = $maxTime = "00:00:00";
				if(!empty($tdata)){
						if(!empty($tdata['o'][0]['minTime'])){
							$time['o_in_time'] = $tdata['o'][0]['minTime'];
						}
						if(!empty($tdata['o'][0]['maxTime'])){
							$time['o_out_time'] = $tdata['o'][0]['maxTime'];
						}
						
						if(!empty($tdata['rs'][0]['minTime'])){
							$time['rs_in_time'] = $tdata['rs'][0]['minTime'];
						}
						if(!empty($tdata['rs'][0]['maxTime'])){
							$time['rs_out_time'] = $tdata['rs'][0]['maxTime'];
						}
						
						if(!empty($tdata['cv'][0]['minTime'])){
							$time['cv_in_time'] = $tdata['cv'][0]['minTime'];
						}
						if(!empty($tdata['cv'][0]['maxTime'])){
							$time['cv_out_time'] = $tdata['cv'][0]['maxTime'];
						}					
					//pr($time);
					$time = isset($time) ? $time : array(null);
					
					$min = min($time);
					$inTime = array_search($min,$time);
					$minTime = $time[$inTime];
					
					$max = max($time);
					$outTime = array_search($max,$time);
					$maxTime = $time[$outTime];
				}
			} */
			
			if ($type == "daily") {
				$minTime = $maxTime = "00:00:00";
				$firstCustomerId = $lastCustomerId = null;
				$tdata = ['o' => [], 'rs' => [], 'cv' => []];
				$dateLike = $params['date'] . '%';

				// -------------------
				// ORDERS
				// -------------------
				$this->loadModel('Order');
				$orders = $this->Order->query("
					(SELECT customer_id, created AS minTime, created AS maxTime FROM orders WHERE user_id = $uid AND created LIKE '$dateLike' ORDER BY created ASC LIMIT 1)
					UNION ALL
					(SELECT customer_id, created AS minTime, created AS maxTime FROM orders WHERE user_id = $uid AND created LIKE '$dateLike' ORDER BY created DESC LIMIT 1)
				"); //pr($orders);

				if (!empty($orders)) {
					$tdata['o']['minTime'] = $orders[0][0]['minTime'];
					$tdata['o']['maxTime'] = isset($orders[1]) ? $orders[1][0]['maxTime'] : $orders[0][0]['maxTime'];
					$firstCustomerId = $orders[0][0]['customer_id'];
					$lastCustomerId = isset($orders[1]) ? $orders[1][0]['customer_id'] : $orders[0][0]['customer_id'];
				}

				// -------------------
				// REDISTRIBUTOR SALES
				// -------------------
				$this->loadModel('RedistributorSale');
				$rsales = $this->RedistributorSale->query("
					(SELECT customer_id, created AS minTime, created AS maxTime FROM redistributor_sales WHERE user_id = $uid AND created LIKE '$dateLike' ORDER BY created ASC LIMIT 1)
					UNION ALL
					(SELECT customer_id, created AS minTime, created AS maxTime FROM redistributor_sales WHERE user_id = $uid AND created LIKE '$dateLike' ORDER BY created DESC LIMIT 1)
				");

				if (!empty($rsales)) {
					$tdata['rs']['minTime'] = $rsales[0][0]['minTime'];
					$tdata['rs']['maxTime'] = isset($rsales[1]) ? $rsales[1][0]['maxTime'] : $rsales[0][0]['maxTime'];

					// Update first/last customer if these times are earlier/later
					$times = [
						'min' => strtotime($rsales[0][0]['minTime']),
						'max' => isset($rsales[1][0]['maxTime']) ? strtotime($rsales[1][0]['maxTime']) : strtotime($rsales[0][0]['maxTime'])
					];
					if ($times['min'] < strtotime($tdata['o']['minTime'])) {
						$minTime = $rsales[0][0]['minTime'];
						$firstCustomerId = $rsales[0][0]['customer_id'];
					}
					if ($times['max'] > strtotime($tdata['o']['maxTime'])) {
						$maxTime = isset($rsales[1][0]['maxTime']) ? $rsales[1][0]['maxTime'] : $rsales[0][0]['maxTime'];
						$lastCustomerId = isset($rsales[1][0]['customer_id']) ? $rsales[1][0]['customer_id'] : $rsales[0][0]['customer_id'];
					}
				}

				// -------------------
				// CUSTOMER VISITS (dynamic table)
				// -------------------
				$this->loadModel('CustomerVisit');
				$cv_tableName = !empty($cvisityear_tables) ? $cvisityear_tables[key($cvisityear_tables)] : 'customer_visits';

				$cvisits = $this->CustomerVisit->query("
					(SELECT customer_id, created AS minTime, created AS maxTime FROM $cv_tableName WHERE user_id = $uid AND created LIKE '$dateLike' ORDER BY created ASC LIMIT 1)
					UNION ALL
					(SELECT customer_id, created AS minTime, created AS maxTime FROM $cv_tableName WHERE user_id = $uid AND created LIKE '$dateLike' ORDER BY created DESC LIMIT 1)
				");

				if (!empty($cvisits)) {
					$tdata['cv']['minTime'] = $cvisits[0][0]['minTime'];
					$tdata['cv']['maxTime'] = isset($cvisits[1]) ? $cvisits[1][0]['maxTime'] : $cvisits[0][0]['maxTime'];

					$times = [
						'min' => strtotime($cvisits[0][0]['minTime']),
						'max' => isset($cvisits[1][0]['maxTime']) ? strtotime($cvisits[1][0]['maxTime']) : strtotime($cvisits[0][0]['maxTime'])
					];
					if ($times['min'] < strtotime($minTime)) {
						$minTime = $cvisits[0][0]['minTime'];
						$firstCustomerId = $cvisits[0][0]['customer_id'];
					}
					if ($times['max'] > strtotime($maxTime)) {
						$maxTime = isset($cvisits[1][0]['maxTime']) ? $cvisits[1][0]['maxTime'] : $cvisits[0][0]['maxTime'];
						$lastCustomerId = isset($cvisits[1][0]['customer_id']) ? $cvisits[1][0]['customer_id'] : $cvisits[0][0]['customer_id'];
					}
				}

				// -------------------
				// Final overall minTime / maxTime
				// -------------------
				$allTimes = [];
				if (!empty($tdata['o'])) {
					$allTimes['o_in_time'] = $tdata['o']['minTime'];
					$allTimes['o_out_time'] = $tdata['o']['maxTime'];
				}
				if (!empty($tdata['rs'])) {
					$allTimes['rs_in_time'] = $tdata['rs']['minTime'];
					$allTimes['rs_out_time'] = $tdata['rs']['maxTime'];
				}
				if (!empty($tdata['cv'])) {
					$allTimes['cv_in_time'] = $tdata['cv']['minTime'];
					$allTimes['cv_out_time'] = $tdata['cv']['maxTime'];
				}

				if (!empty($allTimes)) {
					$minTime = min($allTimes);
					$maxTime = max($allTimes);
				}

				// $firstCustomerId and $lastCustomerId now have the customer_id for first and last call
				$firstLastcustomerData = $this->Customer->find('list', ['fields' => ['id', 'name'], 'conditions' => ['Customer.id' => [$firstCustomerId, $lastCustomerId]],'recursive' => -1]);
			}
		}
		
		
		$this->_selectOptions($userSelectCond);
		//$this->set(compact('divisions', 'regions', 'depots', 'users'));
		
		$this->set(compact('udata', 'query', 'type','opt', 'brands', 'customers', 'assignedRoutes', 'assignedRouteNames','reportUserRoutes', 'result', 'uid', 'allusers', 'minTime', 'maxTime', 'weeks', 'firstLastcustomerData', 'firstCustomerId', 'lastCustomerId'));
		
		if(isset($this->request->query['export'])){
			$fileName = 'User\'s Route Customer Activity';

			$fileName .= '_' . $dateParams['title'];
			if(!empty($dateParams['sub_title'])){
				$fileName .= '(' . $dateParams['sub_title'].')';
			}
			
			$this->set('filename', $fileName);
			$this->render('export/customer_call_route','export_xls');
		}
	}
	
	/*
	**	User time sheet
	**
	*/
	function day_user_calls($type='daily', $opt=''){ 
		$this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('User');
		$this->loadModel('Role');
		
		//$userConditions = array('User.status'=>ACTIVE);
		$userConditions  = array();

		if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            $userConditions['User.division_id'] = $division_id;
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$userConditions['User.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		
		}
		if (!empty($this->request->query['dp'])){
			$userConditions['User.depot_id'] =  $this->request->query['dp'];
		}else if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$userConditions['User.depot_id'] = $this->user['depot_id'];
		}
		
		if ( !empty($this->request->query['r'])){
			$userConditions['User.role_id'] = $this->request->query['r'];
		}
		
		$divisions = $this->Division->getList();
		$regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		$roles = $this->Role->getRolesList();
		
		$params = array();		
		$query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);		
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		$dayUserCalls = $this->Report->dayUserCalls($params);		
		
		$users = $this->User->find('all', array('conditions'=>$userConditions));

		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'dayUserCalls', 'users', 'roles'));
		
	
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'UserTimesheet'.$type);
			$this->render('export_day_user_calls','export_xls');
		}
	}
	
	function sync_calls($type='daily', $opt=''){
		$this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('User');
		$userConditions = array('User.status'=>ACTIVE);
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            $userConditions['User.division_id'] = $division_id;
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
			$userConditions['User.region_id'] = $region_id;
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		
		}
		if (!empty($this->request->query['dp'])){
			$userConditions['User.depot_id'] =  $this->request->query['dp'];
		}else if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$userConditions['User.depot_id'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
		$regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();		
		$query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);		
		$params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
		$dayUserCalls = $this->Report->syncCalls($params);		
		
		$users = $this->User->find('all', array('conditions'=>$userConditions));
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'dayUserCalls', 'users'));
		
	
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'UserCallSyncSheet'.$type);
			$this->render('export_sync_calls','export_xls');
		}
	}
	
	function target_report($type='monthly', $calltype='sale', $opt=''){ 
		//pr($calltype);
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}

        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}else{
			$type = 'monthly';
		}

		$navParams = $this->_nav_params($type, $opt);
		$params = array_merge($navParams, compact('query', 'type','opt','calltype'));
		$params['user'] = $this->user;
		$dayCalls = $this->Report->targetReports($params);	//pr($dayCalls);

		$this->loadModel('BrandGroup');
		$brandGroup = $this->BrandGroup->find('list', array('fields'=>array('id','name')));
		
		$this->set(compact('divisions', 'regions', 'depots', 'query', 'type','opt', 'dayCalls','brandGroup','calltype'));
		
		if(isset($this->request->query['export'])){   
			if($type == 'between'){	
				$fileName = 'target_report_'.$query['from'].'_to_'.$query['to'];
			}else{
				$fileName = 'target_report_'.date('F_Y', $params['strtotime']);
			}			
			$this->set('filename', $fileName);
			$this->set('month', date('F Y', $params['strtotime']));
			
			if($calltype == 'summary'){
				$this->render('targetsummaryreports_xls','export_xls');
			}else{
				$this->render('targetreport_xls','export_xls');
			}
		}else{
			if($calltype == 'summary'){
				$this->render('target_summary_report');
			}
		}
		
		
		
	}
	
	/**
	function visits_per_customer($type='daily', $opt=''){
        $this->loadModel('Division');
		$this->loadModel('Region');
		$this->loadModel('Depot');
		$this->loadModel('Customer');
		
		
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
		$depots = $this->Depot->getList($region_id);
		
		$params = array();
		
		$query = $this->request->query;		
		if(!empty($query['from']) || !empty($query['to']) ){
			$type = 'between';
		}
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
		$activeCust = $conditions =  array(); 

		$this->Report->totalNumberOfVisitsPerCustomer($params, $this->paginate, $conditions, $this->Customer, $activeCust);		
		
		//pr($conditions);
		//pr($this->request->query); die();
		if(isset($this->request->query['export'])){
			$joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
			$order = isset($this->paginate['order']) ? $this->paginate['order'] : array();
			$group = isset($this->paginate['group']) ? $this->paginate['group'] : array();
			$fields = isset($this->paginate['fields']) ? $this->paginate['fields'] : array();
			
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
				if($offset > 10000){
					$offset = 0;
				}
			}else{
				$offset = 0;
			}
			//pr($offset);
			$activeCust = $this->Customer->find('all', array('conditions'=>$conditions, 'joins'=>$joins,'group'=>$group,'order'=>$order,'fields'=>$fields));
         }else{			
			$activeCust = $this->paginate('Customer', $conditions);
		
		}
		//pr($activeCust); die();
		
		$this->set(compact('divisions','regions', 'depots', 'query', 'type','opt', 'activeCust','offset'));
		if(isset($this->request->query['export'])){   
				$this->render('export_visitspercustomer','export_xls');
		}
		
	} **/
	
	function visits_per_customer($type='daily', $opt=''){
        $this->loadModel('Division');
        $this->loadModel('Region');
        $this->loadModel('Depot');
        $this->loadModel('Customer');
        $this->loadModel('User');
        $this->loadModel('Route');
        $rcond = array();
        $dv_regC = $rgC = $dpC = $susC = $rsusC = $cvucond ='';
        //$conditions =  array();
        $conditions = array('Customer.status' => ACTIVE);
		$conditions['Customer.first_name NOT'] = null;
		$conditions['Customer.last_name NOT'] = null;
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            $conditions['Customer.division_id'] = $this->request->query['dv'];
            if (empty($this->request->query['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                $rcond['region_id IN'] = $rIds;
                $dv_regC = " AND d.region_id IN (".implode(',',$rIds).") ";
            }
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
                $conditions['Customer.division_id'] = $this->request->query['dv'];
                if (empty($this->request->query['rg'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
                    $rcond['region_id IN'] = $rIds;
                    $dv_regC = " AND d.region_id IN (".implode(',',$rIds).") ";
                }
            }
        }
        if (!empty($this->request->query['rg'])){
            $region_id = $this->request->query['rg'];
            $conditions['Customer.region_id'] = $this->request->query['rg'];
            $rcond['Route.region_id'] = $region_id;
            $rgC = " AND d.region_id = ".$this->request->query['rg'];
        }else{
            $region_id = 0;
            if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
                $this->request->query['rg'] = $region_id = $this->user['region_id'];
                $conditions['Customer.region_id'] = $this->request->query['rg'];
                $rcond['Route.region_id'] = $region_id;
                $rgC = " AND d.region_id = ".$this->request->query['rg'];
            }
        }
        

        if(!empty($this->request->query['dp'])){
            $depot_id = $this->request->query['dp'];
            $conditions['Customer.depot_id'] = $depot_id;
            $rcond['depot_id'] = $depot_id;
            $dpC = " AND d.id = ".$this->request->query['dp'];
        }else{
            $depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
				$conditions['Customer.depot_id'] = $this->user['depot_id'];
				$rcond['depot_id'] = $depot_id;
				$dpC = " AND d.id = ".$this->request->query['dp'];
			}
        }

        if (!empty($this->request->query['q']) ){
            $conditions['OR']['concat_ws(" ", Customer.first_name, Customer.last_name) LIKE'] =  '%'.$this->request->query['q'].'%';
        }

        if (!empty($this->request->query['us'])){
            //$conditions['cv.user_id'] = $this->request->query['us'];
            $susC = " AND sl.user_id =".$this->request->query['us'];
            $rsusC = " AND rsi.user_id =".$this->request->query['us'];
            $cvucond =' AND cv.user_id ='.$this->request->query['us'];

            $this->loadModel('UserRoute');
            $rtIds = $this->UserRoute->find('list', array('conditions'=>array('user_id'=>$this->request->query['us']), 'fields'=>array('route_id','user_id')));
            if(!empty($rtIds)){
				$rtIds = array_keys ($rtIds);
                $rcond['Route.id'] = $rtIds;
				if(empty($this->request->query['rt'])){
                    $conditions['Customer.route_id'] = $rtIds;
                }
            }
        }
        if (!empty($this->request->query['rt'])){
            $conditions['Customer.route_id'] = $this->request->query['rt'];
        }

        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
        $depots = $this->Depot->getList($region_id);
        $users = $this->User->usersList($depot_id);

        $routes = $this->Route->find('list', array('conditions'=>$rcond, 'fields'=>array('id','title'), 'order'=>array('title'=>'asc')));

        $params = array();

        $query = $this->request->query;
        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$query['from'], @$query['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
        $navParams = $this->_nav_params($type, $opt);
        $params = array_merge($navParams, compact('query', 'type','opt','cvisityear_tables'));
        $activeCust = array();

        $customer = $this->Customer;

        $date = $params['date'];

        switch($params['type']){
            case 'daily':
                $sC = "sl.sale_date LIKE '".$date."%'";
                $rsC = "rsi.sale_date LIKE '".$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 = "sl.sale_date between '$d1' AND '$d2'";
                $rsC = "rsi.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;
            case 'between':
                if(empty( $params['query']['from'])){
                    $d1 = date('Y-m-d');
                }else{
                    $d1 =  $params['query']['from'];
                }
                if(empty( $params['query']['to'])){
                    $d2 = date('Y-m-d');
                }else{
                    $d2 =  $params['query']['to'];
                }
                $sC = "sl.sale_date between '$d1' AND '$d2'";
                $rsC = "rsi.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 = "sl.sale_date between '$d1' AND '$d2'";
                $rsC = "rsi.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;
        }

        $totSalesSQL = "( SELECT count(DISTINCT sl.order_id) FROM sales sl LEFT JOIN depots d ON d.id = sl.depot_id WHERE Customer.id = sl.customer_id AND $sC $dv_regC $rgC $dpC $susC )"; //pr($totSalesSQL);
        $totrSalesSQL = "( SELECT count(DISTINCT rsi.redistributor_sale_id) FROM redistributor_saleitems rsi LEFT JOIN depots d ON d.id = rsi.depot_id WHERE Customer.id = rsi.customer_id AND $rsC $dv_regC $rgC $dpC $rsusC )";
        $totVisitSQL = "( SELECT count(cv.id) FROM `customer_visits` AS cv WHERE Customer.id=cv.customer_id AND $cvC $cvucond)";

        //$customer->virtualFields['totVisits'] = "COUNT(cv.id)";
        $customer->virtualFields['totVisits'] = $totVisitSQL;
        //$customer->virtualFields['totSales'] = $totSalesSQL;
        //$customer->virtualFields['totrSales'] = $totrSalesSQL;
        $this->paginate['order'] = array("Customer.first_name" => "asc", "Customer.last_name" => "asc");


        //$this->paginate['fields'] = array("Customer.id", "Customer.first_name", "Customer.last_name","Customer.route_id", "COUNT(cv.id) as totVisits");
        //$this->paginate['fields'] = array("Customer.id", "Customer.first_name", "Customer.last_name","Customer.route_id", "Customer.totVisits","Customer.totSales","Customer.totrSales");
        $this->paginate['fields'] = array("Customer.id", "Customer.first_name", "Customer.last_name","Customer.route_id", "Customer.totVisits");

        //$joins=$this->paginate['joins'][] = array('table' => 'customer_visits', 'type'=>'LEFT', 'alias'=>'cv', 'conditions'=>array("Customer.id=cv.customer_id AND $cvC "));
        if(!empty($cvisityear_tables)){
            if(count($cvisityear_tables)>1){
                $cvisityear_tabSqlArray = array();
                foreach ($cvisityear_tables as $year => $cv_tableName){
                    $cvisityear_tabSqlArray[] = " ( SELECT * FROM $cv_tableName as cv WHERE $cvC )";
                }
                $cvisityear_tabSql = "( ".implode(" UNION ALL ", $cvisityear_tabSqlArray)." )"; // UNION operation For Multiple customer_visits table as per year wise organized

            }else{
                $cvisityear_tabSql = $cvisityear_tables[key($cvisityear_tables)]; // Single customer_visits table as per year wise organized
            }
            //pr($cvisityear_tabSql);
        }else{
            $cvisityear_tabSql ='customer_visits';
        }
        $joins=$this->paginate['joins'][] = array('table' => $cvisityear_tabSql, 'type'=>'LEFT', 'alias'=>'cv', 'conditions'=>array("Customer.id=cv.customer_id AND $cvC "));
        $this->paginate['limit'] = 50;
        if (!empty($this->request->query['vst']) || (isset($this->request->query['vst']) && $this->request->query['vst'] == '0')){
            if($this->request->query['vst']=='3'){
                //$this->paginate['group'] = array('Customer.id HAVING COUNT(cv.id) >= '.$params['query']['vst']);
                $conditions[]= '('.$totSalesSQL.'+'.$totrSalesSQL.'+'.$totVisitSQL.' >='.  $params['query']['vst'].')';
            }else{
                //$this->paginate['group'] = array('Customer.id HAVING COUNT(cv.id) = '.$params['query']['vst']);
                $conditions[] = '('.$totSalesSQL.'+'.$totrSalesSQL.'+'.$totVisitSQL.' ='.  $params['query']['vst'].')';
            }
        }/*else{
            $this->paginate['group'] = array('Customer.id');
        }*/
        $this->paginate['group'] = array('Customer.id');

        //pr($conditions);
        //pr($this->request->query); die();
        $activeCust = null;
        if(((!empty($this->request->query['dv']) && $this->request->query['dv'] > 0) && (!empty($this->request->query['rg']) && $this->request->query['rg'] > 0) && (!empty($this->request->query['dp']) && $this->request->query['dp'] > 0)  ) || ((!empty($this->request->query['rg']) && $this->request->query['rg'] > 0) && (!empty($this->request->query['dp']) && $this->request->query['dp'] > 0))  || ((!empty($this->request->query['dp']) && $this->request->query['dp'] > 0))  ) {

            if (isset($this->request->query['export'])) {
                $joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
                $order = isset($this->paginate['order']) ? $this->paginate['order'] : array();
                $group = isset($this->paginate['group']) ? $this->paginate['group'] : array();
                $fields = isset($this->paginate['fields']) ? $this->paginate['fields'] : array();

                if (!empty($this->request->query['offset'])) {
                    $offset = (int)$this->request->query['offset'];
                    if ($offset > 10000) {
                        $offset = 0;
                    }
                } else {
                    $offset = 0;
                }
                //pr($offset);
                $activeCust = $this->Customer->find('all', array('conditions' => $conditions, 'joins' => $joins, 'group' => $group, 'order' => $order, 'fields' => $fields, 'limit' => '10000', 'offset' => $offset));
            } else {
                $activeCust = $this->paginate('Customer', $conditions);

            }
        }
        //pr($activeCust); //die();
        if($activeCust){
            $this->Report->totalNumberOfVisitsPerCustomer($params, $this->paginate, $conditions, $this->Customer, $activeCust);
        }
        //pr($users);exit();
        //pr($activeCust); die();

        $this->set(compact('users','divisions','regions', 'depots','routes', 'query', 'type','opt', 'activeCust','offset'));
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'visits_per_customer_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'visits_per_customer_'.date('F_Y', $params['strtotime']);
            }
            $this->set('filename', $fileName);
            $this->render('export_visitspercustomer','export_xls');
        }
		
	}
	
	
	
	function users_calls_frequency_summary_v1($type='daily', $opt='', $user_id=''){ 
		$opt = !empty($opt) ? $opt : date('Y-m-d');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
			$this->request->query['dp'] = $this->user['depot_id'];
		}
		
		if(empty($user_id)){
			$this->loadModel('Division');
			$this->loadModel('Region');
			$this->loadModel('Depot');
			$this->loadModel('User');
		
			$divisions = $this->Division->getList();
			$regions = $this->Region->getList($division_id);
			$depots = $this->Depot->getList($region_id);
			$users = $this->User->usersList();
			$this->set(compact('divisions', 'regions', 'depots', 'users'));
		}else{
			$this->request->query['us'] = $user_id;
			/* $this->loadModel('Customer');
			$customerData = $this->Customer->find('all', array('fields' => array('id', 'name', 'Route.title'), 'conditions' => array('user_id' => $user_id))); 
			foreach($customerData as $customer){
				$customers[$customer['Customer']['id']] = $customer;
			} //pr($customers);
			$this->set(compact('customers')); */
		}
        
		
		$params = array();		
		$query = $this->request->query;
		
		
		
		$weeks = [];
		
		/* if($this->user['id'] == 186){
			$query['from'] = '2023-11-03';
			$query['to'] = '2023-11-06';
		} */
		
		if(!empty($query['from']) && !empty($query['to']) ){
            $type = 'between';
						
			$betweenParams = $this->_getBetweenParams($query['from'], $query['to']);
			
			$dateParams['from'] = $betweenParams['from'];
			$dateParams['to'] = $betweenParams['to'];
			
			/* if (!empty($query['from'])){
				$dateParams['from'] = $query['from'];
			}else{
				$dateParams['from'] = date('Y-m-d');
			}
			
			if (!empty($query['to'])){
				$dateParams['to'] = $query['to'];
			}else{
				$dateParams['to'] = date('Y-m-d');
			} */
            
        }else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					/* if($type =='weekly' && empty($query['week'])){
						$query['week'] = 1;
					}elseif($type =='monthly'){
						unset($query['week']);
					} */
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
						//$query['week'] = 1;
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
					
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			$dateParams = $this->_dateParams($type, $opt);
		}
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            //$type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt);
		
		
		## get weekNo for daily
		## For route plan Anya wants to keep Y3 weeks, so in model Y3 week function has been used
		if($type == 'daily' && empty($dateParams['week'])){
			$RPTGCalendar = $this->_getPTGCalendarData($opt); //pr($RPTGCalendar['week']);
			$dateParams['week'] =  !empty($RPTGCalendar['week']) ? getWeekForRoutePlan($RPTGCalendar['week']) : getY3Week($opt); //pr($week);
		}
		
		$params = array_merge($navParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'user_id'));
		$params['user'] = $this->user;
		$userCalls = $this->Report->usersCallsFrequencySummary_V1($params); 	//pr($userCalls); die();
		$this->set(compact('query', 'type','opt', 'userCalls', 'user_id', 'weeks'));
		
		
		if(isset($this->request->query['export'])){   
			if($type == 'between'){	
				$fileName = 'users_calls_frequency_summary_report_v1_'.$query['from'].'_to_'.$query['to'];
			}else{
				$fileName = 'users_calls_frequency_summary_report_v1_'.date('F_Y', $params['strtotime']);
			}			
			$this->set('filename', $fileName);
			$this->set('month', date('F Y', $params['strtotime']));
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v1_xls','export_xls');
			}else{
				$this->render('users_calls_frequency_v1_summary_xls','export_xls');
			}
			
		}else{ 
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v1');
			}
		}
		
	}
	
	function users_calls_frequency_summary_v2($type='daily', $opt='', $user_id=''){
		$opt = !empty($opt) ? $opt : date('Y-m-d');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		
		if (!empty($this->request->query['dp'])){
			$depot_id = $this->request->query['dp'];
		}else{
			$depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
			}elseif(!empty($division_id)){
				$this->loadModel('DivisionsRegion');
				$rIds = $this->DivisionsRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$division_id)));
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$rIds))); 
			}elseif(!empty($region_id)){
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$region_id))); 
			}
		}
		
		if(empty($user_id)){
			$this->loadModel('Division');
			$this->loadModel('Region');
			$this->loadModel('Depot');
			$this->loadModel('User');
		
			$divisions = $this->Division->getList();
			$regions = $this->Region->getList($division_id);
			$depots = $this->Depot->getList($region_id);
			$dIds = !empty($dIds) ? $dIds : $depot_id;
			$users = $this->User->usersList($dIds); 
			$this->set(compact('divisions', 'regions', 'depots', 'users'));
		}else{
			$this->request->query['us'] = $user_id;
			/* $this->loadModel('Customer');
			$customerData = $this->Customer->find('all', array('fields' => array('id', 'name', 'Route.title'), 'conditions' => array('user_id' => $user_id))); 
			foreach($customerData as $customer){
				$customers[$customer['Customer']['id']] = $customer;
			} //pr($customers);
			$this->set(compact('customers')); */
		}
        
		
		$params = array();		
		$query = $this->request->query;
		
		//pr($query); //die();
		
		$weeks = [];
		
		/* if($this->user['id'] == 186){
			$query['from'] = '2023-11-03';
			$query['to'] = '2023-11-06';
		} */
		
		if(!empty($query['periodtype']) && $query['periodtype'] != 'ptg_calendar' ){
            $type = 'between';

			$betweenParams = $this->_getPTGCalendarRangeParams($query); //pr($betweenParams); die();
			
			$dateParams['betweenParams'] = $betweenParams;
			$dateParams['from'] = $betweenParams['from'];
			$dateParams['to'] = $betweenParams['to'];						           
        }else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					/* if($type =='weekly' && empty($query['week'])){
						$query['week'] = 1;
					}elseif($type =='monthly'){
						unset($query['week']);
					} */
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
						//$query['week'] = 1;
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
															
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			$dateParams = $this->_dateParams($type, $opt);						
		}
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            //$type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt); 

		## get weekNo for daily
		## For route plan Anya wants to keep Y3 weeks, so in model Y3 week function has been used
		/* if($type == 'daily' && empty($dateParams['week'])){
			$RPTGCalendar = $this->_getPTGCalendarData($opt); //pr($RPTGCalendar['week']);
			$dateParams['week'] =  !empty($RPTGCalendar['week']) ? getWeekForRoutePlan($RPTGCalendar['week']) : getY3Week($opt); //pr($week);
		} */
		
		if($type =='daily'){
			//Added only for this repot, To display PTG Caledar Week in User Route Plan
			$reportCalendar = $this->_getPTGCalendarData($opt); //pr($RPTGCalendar['week']);
			$dateParams['week'] =  $reportCalendar['week']; 
			$dateParams['from'] = $dateParams['to'] = $opt;
		}
		elseif($type == 'monthly'){ //pr($dateParams);
			$RPTGCalendar = $this->_getPTGCalendarData($opt);
			$navParams['RPTGCalendar_month'] = $RPTGCalendar['calendar'][$dateParams['month']];  //pr($navParams['RPTGCalendar_month']);
		}
		
		$params = array_merge($navParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'user_id')); //pr($params);
		$params['user'] = $this->user;
		
		$userCalls = [];
		//if(!empty($query)){
			$userCalls = $this->Report->usersCallsFrequencySummary_V2($params); 
		//}
		
		//pr($userCalls); die();
		
		$this->loadModel('Role');
		$includedRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc'], 'conditions' => ['grade >' => 3]));
		
		
		$this->set(compact('query', 'type','opt', 'userCalls', 'user_id', 'weeks', 'includedRoles'));
		
		
		if(isset($this->request->query['export'])){   
			if($type == 'between'){	
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v2_'.$query['from'].'_to_'.$query['to'];
				}else{
					$fileName = 'users_calls_frequency_summary_report_v2_'.$query['from'].'_to_'.$query['to'];
				}				
			}else{
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v2_'.date('F_Y', $params['strtotime']);
				}else{
					$fileName = 'users_calls_frequency_summary_report_v2_'.date('F_Y', $params['strtotime']);
				}				
			}			
			$this->set('filename', $fileName);
			$this->set('month', date('F Y', $params['strtotime']));
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v2_xls','export_xls');
			}else{
				$this->render('users_calls_frequency_summary_v2_xls','export_xls');
			}
			
		}else{ 
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v2');
			}
		}
		
	}
	
	function users_calls_frequency_summary_v3($type='daily', $opt='', $user_id=''){
		$opt = !empty($opt) ? $opt : date('Y-m-d');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		
		if (!empty($this->request->query['dp'])){
			$depot_id = $this->request->query['dp'];
		}else{
			$depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
			}elseif(!empty($division_id)){
				$this->loadModel('DivisionsRegion');
				$rIds = $this->DivisionsRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$division_id)));
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$rIds))); 
			}elseif(!empty($region_id)){
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$region_id))); 
			}
		}
		
		if(empty($user_id)){
			$this->loadModel('Division');
			$this->loadModel('Region');
			$this->loadModel('Depot');
			$this->loadModel('User');
		
			$divisions = $this->Division->getList();
			$regions = $this->Region->getList($division_id);
			$depots = $this->Depot->getList($region_id);
			$dIds = !empty($dIds) ? $dIds : $depot_id;
			$users = $this->User->usersList($dIds); 
			$this->set(compact('divisions', 'regions', 'depots', 'users'));
		}else{
			$this->request->query['us'] = $user_id;
			/* $this->loadModel('Customer');
			$customerData = $this->Customer->find('all', array('fields' => array('id', 'name', 'Route.title'), 'conditions' => array('user_id' => $user_id))); 
			foreach($customerData as $customer){
				$customers[$customer['Customer']['id']] = $customer;
			} //pr($customers);
			$this->set(compact('customers')); */
		}
        
		
		$params = array();		
		$query = $this->request->query;
		
		//pr($query); //die();
		
		$weeks = [];
		
		/* if($this->user['id'] == 186){
			$query['from'] = '2023-11-03';
			$query['to'] = '2023-11-06';
		} */
		
		if(!empty($query['periodtype']) && $query['periodtype'] != 'ptg_calendar' ){
            $type = 'between';

			$betweenParams = $this->_getPTGCalendarRangeParams($query); //pr($betweenParams); die();
			
			$dateParams['betweenParams'] = $betweenParams;
			$dateParams['from'] = $betweenParams['from'];
			$dateParams['to'] = $betweenParams['to'];						           
        }else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					/* if($type =='weekly' && empty($query['week'])){
						$query['week'] = 1;
					}elseif($type =='monthly'){
						unset($query['week']);
					} */
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
						//$query['week'] = 1;
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
															
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			$dateParams = $this->_dateParams($type, $opt);						
		}
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            //$type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt); 

		## get weekNo for daily
		## For route plan Anya wants to keep Y3 weeks, so in model Y3 week function has been used
		/* if($type == 'daily' && empty($dateParams['week'])){
			$RPTGCalendar = $this->_getPTGCalendarData($opt); //pr($RPTGCalendar['week']);
			$dateParams['week'] =  !empty($RPTGCalendar['week']) ? getWeekForRoutePlan($RPTGCalendar['week']) : getY3Week($opt); //pr($week);
		} */
		
		if($type =='daily'){
			//Added only for this repot, To display PTG Caledar Week in User Route Plan
			$reportCalendar = $this->_getPTGCalendarData($opt); //pr($reportCalendar);
			$dateParams['month'] =  $reportCalendar['month'];
			$dateParams['week'] =  $reportCalendar['week']; 
			$dateParams['from'] = $dateParams['to'] = $opt;
		}
		/* elseif($type == 'monthly'){ //pr($dateParams);
			$RPTGCalendar = $this->_getPTGCalendarData($opt);
			$navParams['RPTGCalendar_month'] = $RPTGCalendar['calendar'][$dateParams['month']];  //pr($navParams['RPTGCalendar_month']);
		} */
		
		### NOTE : $navParams['RPTGCalendar']['week'] for MONTHLY CASE has been processed in Model
		$RPTGCalendar = $this->_getPTGCalendarData($opt);
		$navParams['RPTGCalendar'] = $RPTGCalendar;  //pr($navParams['RPTGCalendar']);
		
		$params = array_merge($navParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'user_id')); //pr($params);
		$params['user'] = $this->user;
		
		$userCalls = [];
		//if(!empty($query)){
			$userCalls = $this->Report->usersCallsFrequencySummary_V3($params); 
		//}
		
		//pr($userCalls); die();
		
		$this->loadModel('Role');
		$includedRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc'], 'conditions' => ['grade >' => 3]));
		
		
		$this->set(compact('query', 'type','opt', 'userCalls', 'user_id', 'weeks', 'includedRoles', 'dateParams'));
		
		
		if(isset($this->request->query['export'])){   
			if($type == 'between'){	
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v3_'.$query['from'].'_to_'.$query['to'];
				}else{
					$fileName = 'users_calls_frequency_summary_report_v3_'.$query['from'].'_to_'.$query['to'];
				}				
			}else{
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v3_'.date('F_Y', $params['strtotime']);
				}else{
					$fileName = 'users_calls_frequency_summary_report_v3_'.date('F_Y', $params['strtotime']);
				}				
			}			
			$this->set('filename', $fileName);
			$this->set('month', date('F Y', $params['strtotime']));
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v3_xls','export_xls');
			}else{
				$this->render('users_calls_frequency_summary_v3_xls','export_xls');
			}
			
		}else{ 
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v3');
			}
		}
		
	}
	
	function users_calls_frequency_summary_v4($type='daily', $opt='', $user_id=''){
		$opt = !empty($opt) ? $opt : date('Y-m-d');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		
		if (!empty($this->request->query['dp'])){
			$depot_id = $this->request->query['dp'];
		}else{
			$depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
			}elseif(!empty($division_id)){
				$this->loadModel('DivisionsRegion');
				$rIds = $this->DivisionsRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$division_id)));
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$rIds))); 
			}elseif(!empty($region_id)){
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$region_id))); 
			}
		}
		
		if(empty($user_id)){
			$this->loadModel('Division');
			$this->loadModel('Region');
			$this->loadModel('Depot');
			$this->loadModel('User');
		
			$divisions = $this->Division->getList();
			$regions = $this->Region->getList($division_id);
			$depots = $this->Depot->getList($region_id);
			$dIds = !empty($dIds) ? $dIds : $depot_id;
			$users = $this->User->usersList($dIds); 
			$this->set(compact('divisions', 'regions', 'depots', 'users'));
		}else{
			$this->request->query['us'] = $user_id;
			/* $this->loadModel('Customer');
			$customerData = $this->Customer->find('all', array('fields' => array('id', 'name', 'Route.title'), 'conditions' => array('user_id' => $user_id))); 
			foreach($customerData as $customer){
				$customers[$customer['Customer']['id']] = $customer;
			} //pr($customers);
			$this->set(compact('customers')); */
		}
        
		
		$params = array();		
		$query = $this->request->query;
		
		//pr($query); //die();
		
		$weeks = [];
		
		/* if($this->user['id'] == 186){
			$query['from'] = '2023-11-03';
			$query['to'] = '2023-11-06';
		} */
		
		if(!empty($query['periodtype']) && $query['periodtype'] != 'ptg_calendar' ){
            $type = 'between';

			$betweenParams = $this->_getPTGCalendarRangeParams($query); //pr($betweenParams); die();
			
			$dateParams['betweenParams'] = $betweenParams;
			$dateParams['from'] = $betweenParams['from'];
			$dateParams['to'] = $betweenParams['to'];						           
        }else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					/* if($type =='weekly' && empty($query['week'])){
						$query['week'] = 1;
					}elseif($type =='monthly'){
						unset($query['week']);
					} */
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
						//$query['week'] = 1;
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
															
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			$dateParams = $this->_dateParams($type, $opt);						
		}
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            //$type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt); 

		## get weekNo for daily
		## For route plan Anya wants to keep Y3 weeks, so in model Y3 week function has been used
		/* if($type == 'daily' && empty($dateParams['week'])){
			$RPTGCalendar = $this->_getPTGCalendarData($opt); //pr($RPTGCalendar['week']);
			$dateParams['week'] =  !empty($RPTGCalendar['week']) ? getWeekForRoutePlan($RPTGCalendar['week']) : getY3Week($opt); //pr($week);
		} */
		
		if($type =='daily'){
			//Added only for this repot, To display PTG Caledar Week in User Route Plan
			$reportCalendar = $this->_getPTGCalendarData($opt); //pr($reportCalendar);
			$dateParams['month'] =  $reportCalendar['month'];
			$dateParams['week'] =  $reportCalendar['week']; 
			$dateParams['from'] = $dateParams['to'] = $opt;
		}
		/* elseif($type == 'monthly'){ //pr($dateParams);
			$RPTGCalendar = $this->_getPTGCalendarData($opt);
			$navParams['RPTGCalendar_month'] = $RPTGCalendar['calendar'][$dateParams['month']];  //pr($navParams['RPTGCalendar_month']);
		} */ ##### COMMENT THIS ONCE CHANGES ARE COMPLETED
		
		
		### NOTE : $navParams['RPTGCalendar']['week'] for MONTHLY CASE has been processed in Model
		$RPTGCalendar = $this->_getPTGCalendarData($opt);
		$navParams['RPTGCalendar'] = $RPTGCalendar;  //pr($navParams['RPTGCalendar']);
		
		
		
		//pr($dateParams); 
		
		$params = array_merge($navParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'user_id')); //pr($params);
		$params['user'] = $this->user;
		
		$userCalls = [];
		
		if($this->user['username'] == 'mum'){
			//if(!empty($query)){
				$userCalls = $this->Report->usersCallsFrequencySummary_V4_NEW($params); 
			//}
		}else{
			$userCalls = $this->Report->usersCallsFrequencySummary_V4($params); 
		}
		
		
			
		
		
		//pr($userCalls); die();
		
		$this->loadModel('Role');
		$includedRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc'], 'conditions' => ['grade >' => 3]));
		
		
		$this->set(compact('query', 'type','opt', 'userCalls', 'user_id', 'weeks', 'includedRoles', 'dateParams'));
		
		
		if(isset($this->request->query['export'])){   
			if($type == 'between'){	
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v3_'.$query['from'].'_to_'.$query['to'];
				}else{
					$fileName = 'users_calls_frequency_summary_report_v3_'.$query['from'].'_to_'.$query['to'];
				}				
			}else{
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v3_'.date('F_Y', $params['strtotime']);
				}else{
					$fileName = 'users_calls_frequency_summary_report_v3_'.date('F_Y', $params['strtotime']);
				}				
			}			
			$this->set('filename', $fileName);
			$this->set('month', date('F Y', $params['strtotime']));
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v4_xls','export_xls');
			}else{
				$this->render('users_calls_frequency_summary_v4_xls','export_xls');
			}
			
		}else{ 
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v4');
			}
		}
		
	}
	
	function users_calls_frequency_summary_v5($type='daily', $opt='', $user_id=''){
		$opt = !empty($opt) ? $opt : date('Y-m-d');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		
		if (!empty($this->request->query['dp'])){
			$depot_id = $this->request->query['dp'];
		}else{
			$depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
			}elseif(!empty($division_id)){
				$this->loadModel('DivisionsRegion');
				$rIds = $this->DivisionsRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$division_id)));
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$rIds))); 
			}elseif(!empty($region_id)){
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$region_id))); 
			}
		}
		
		if(empty($user_id)){
			$this->loadModel('Division');
			$this->loadModel('Region');
			$this->loadModel('Depot');
			$this->loadModel('User');
		
			$divisions = $this->Division->getList();
			$regions = $this->Region->getList($division_id);
			$depots = $this->Depot->getList($region_id);
			$dIds = !empty($dIds) ? $dIds : $depot_id;
			$users = $this->User->usersList($dIds); 
			$this->set(compact('divisions', 'regions', 'depots', 'users'));
		}else{
			$this->request->query['us'] = $user_id;
			/* $this->loadModel('Customer');
			$customerData = $this->Customer->find('all', array('fields' => array('id', 'name', 'Route.title'), 'conditions' => array('user_id' => $user_id))); 
			foreach($customerData as $customer){
				$customers[$customer['Customer']['id']] = $customer;
			} //pr($customers);
			$this->set(compact('customers')); */
		}
        
		
		$params = array();		
		$query = $this->request->query;
		
		//pr($query); //die();
		
		$weeks = [];
		
		/* if($this->user['id'] == 186){
			$query['from'] = '2023-11-03';
			$query['to'] = '2023-11-06';
		} */
		
		if(!empty($query['periodtype']) && $query['periodtype'] != 'ptg_calendar' ){
            $type = 'between';

			$betweenParams = $this->_getPTGCalendarRangeParams($query); //pr($betweenParams); die();
			
			$dateParams['betweenParams'] = $betweenParams;
			$dateParams['from'] = $betweenParams['from'];
			$dateParams['to'] = $betweenParams['to'];						           
        }else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					/* if($type =='weekly' && empty($query['week'])){
						$query['week'] = 1;
					}elseif($type =='monthly'){
						unset($query['week']);
					} */
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
						//$query['week'] = 1;
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
															
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			$dateParams = $this->_dateParams($type, $opt);						
		}
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            //$type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt); 

		## get weekNo for daily
		## For route plan Anya wants to keep Y3 weeks, so in model Y3 week function has been used
		/* if($type == 'daily' && empty($dateParams['week'])){
			$RPTGCalendar = $this->_getPTGCalendarData($opt); //pr($RPTGCalendar['week']);
			$dateParams['week'] =  !empty($RPTGCalendar['week']) ? getWeekForRoutePlan($RPTGCalendar['week']) : getY3Week($opt); //pr($week);
		} */
		
		if($type =='daily'){
			//Added only for this repot, To display PTG Caledar Week in User Route Plan
			$reportCalendar = $this->_getPTGCalendarData($opt); //pr($reportCalendar);
			$dateParams['month'] =  $reportCalendar['month'];
			$dateParams['week'] =  $reportCalendar['week']; 
			$dateParams['from'] = $dateParams['to'] = $opt;
		}
		/* elseif($type == 'monthly'){ //pr($dateParams);
			$RPTGCalendar = $this->_getPTGCalendarData($opt);
			$navParams['RPTGCalendar_month'] = $RPTGCalendar['calendar'][$dateParams['month']];  //pr($navParams['RPTGCalendar_month']);
		} */ ##### COMMENT THIS ONCE CHANGES ARE COMPLETED
		
		
		### NOTE : $navParams['RPTGCalendar']['week'] for MONTHLY CASE has been processed in Model
		$RPTGCalendar = $this->_getPTGCalendarData($opt);
		$navParams['RPTGCalendar'] = $RPTGCalendar;  //pr($navParams['RPTGCalendar']);
		
		
		
		//pr($dateParams); 
		
		$params = array_merge($navParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'user_id')); //pr($params);
		$params['user'] = $this->user;
		
		$userCalls = [];
		
		$userCalls = $this->Report->usersCallsFrequencySummary_V5($params); 
		
		
			
		
		
		//pr($userCalls); die();
		
		$this->loadModel('Role');
		$includedRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc'], 'conditions' => ['grade >' => 3]));
		
		
		$this->set(compact('query', 'type','opt', 'userCalls', 'user_id', 'weeks', 'includedRoles', 'dateParams'));
		
		
		if(isset($this->request->query['export'])){   
			if($type == 'between'){	
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v5_'.$query['from'].'_to_'.$query['to'];
				}else{
					$fileName = 'users_calls_frequency_summary_report_v5_'.$query['from'].'_to_'.$query['to'];
				}				
			}else{
				if(!empty($user_id)){
					$fileName = 'users_calls_frequency_v5_'.date('F_Y', $params['strtotime']);
				}else{
					$fileName = 'users_calls_frequency_summary_report_v5_'.date('F_Y', $params['strtotime']);
				}				
			}			
			$this->set('filename', $fileName);
			$this->set('month', date('F Y', $params['strtotime']));
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v5_xls','export_xls');
			}else{
				$this->render('users_calls_frequency_summary_v5_xls','export_xls');
			}
			
		}else{ 
			if(!empty($user_id)){
				$this->render('user_calls_frequency_v5');
			}
		}
		
	}
	
	function users_monitoring_report($type='daily', $opt='', $user_id=''){
		$reportName = 'Users Monitoring Report';
		$opt = !empty($opt) ? $opt : date('Y-m-d');
        if(!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
        }else{
            $division_id = 0;
            if(!empty($this->user['division_id']) && $this->user['role_id'] == DIVISIONMANAGER){
                $this->request->query['dv'] = $division_id = $this->user['division_id'];
            }
        }
		if (!empty($this->request->query['rg'])){
			$region_id = $this->request->query['rg'];
		}else{
			$region_id = 0;
			if(!empty($this->user['region_id']) && $this->user['Role']['grade'] >=3){
				$this->request->query['rg'] = $region_id = $this->user['region_id'];
			}
		}
		
		if (!empty($this->request->query['dp'])){
			$depot_id = $this->request->query['dp'];
		}else{
			$depot_id = 0;
			if(!empty($this->user['depot_id']) && $this->user['Role']['grade'] >=4){
				$this->request->query['dp'] = $depot_id = $this->user['depot_id'];
			}elseif(!empty($division_id)){
				$this->loadModel('DivisionsRegion');
				$rIds = $this->DivisionsRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$division_id)));
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$rIds))); 
			}elseif(!empty($region_id)){
				$this->loadModel('Depot');
				$dIds = $this->Depot->find('list', array('fields'=>array('Depot.id'),'conditions' => array('Depot.region_id' =>$region_id))); 
			}
		}
		
		if(empty($user_id)){
			$this->loadModel('Division');
			$this->loadModel('Region');
			$this->loadModel('Depot');
			$this->loadModel('User');
		
			$divisions = $this->Division->getList();
			$regions = $this->Region->getList($division_id);
			$depots = $this->Depot->getList($region_id);
			$dIds = !empty($dIds) ? $dIds : $depot_id;
			$users = $this->User->usersList($dIds); 
			$this->set(compact('divisions', 'regions', 'depots', 'users'));
		}else{
			$this->request->query['us'] = $user_id;
		}
        
		
		$params = array();		
		$query = $this->request->query;
		
		//pr($query); //die();
		
		$weeks = [];
		
		/* if($this->user['id'] == 186){
			$query['from'] = '2023-11-03';
			$query['to'] = '2023-11-06';
		} */
		
		if(!empty($query['periodtype']) && $query['periodtype'] != 'ptg_calendar' ){
            $type = 'between';

			$betweenParams = $this->_getPTGCalendarRangeParams($query); //pr($betweenParams); die();
			
			$dateParams['betweenParams'] = $betweenParams;
			$dateParams['from'] = $betweenParams['from'];
			$dateParams['to'] = $betweenParams['to'];						           
        }else{
			if (!empty($query['period'])) {
				$year = date('Y', strtotime($query['period']));
				$month = date('n', strtotime($query['period']));
				
				$monthlyCalendar = $this->getMonthlyCalendar($year, $month);
				
				if(!empty($monthlyCalendar)){
					$weekrange = getWeekrange($monthlyCalendar); //pr($weekrange);
					foreach($weekrange as $wk => $range){
						$weeks[$wk] = 'Week '.$wk.' ('. date('d M', strtotime($range['start'])).' to '.date('d M', strtotime($range['end'])).')';
					}
					if($type =='weekly' && empty($query['week'])){
						$type = 'monthly';
					}
					
					if(!empty($query['week'])){
						$opt = $weekrange[$query['week']]['start'];
						if($type !='weekly'){
							$type = 'weekly';
						}
					}else{
						$opt = $weekrange[1]['start'];
						if($type !='monthly'){
							$type = 'monthly';
						}
					}
															
				}else{
					$this->Flash->warning(__('The calendar has not been set up for the given time period. Displaying the current date report.'));
					$query['period'] = date('Y-m');
				}
				
			}else{
				if($type == 'monthly'){
					unset($query['week']);
				}
			}
		
			$dateParams = $this->_dateParams($type, $opt);						
		}
		
        if(!empty($dateParams['from']) || !empty($dateParams['to']) ){
            //$type = 'between';
            $cvisityear_tables = customerVisitYearTables(@$dateParams['from'], @$dateParams['to']);
        }else{
            $cvisityear_tables = customerVisitYearTables(@$opt, @$opt);
        }
		$navParams = $this->_nav_params($type, $opt); 

		
		if($type =='daily'){
			//Added only for this repot, To display PTG Caledar Week in User Route Plan
			$reportCalendar = $this->_getPTGCalendarData($opt); //pr($reportCalendar);
			$dateParams['month'] =  $reportCalendar['month'];
			$dateParams['week'] =  $reportCalendar['week']; 
			$dateParams['from'] = $dateParams['to'] = $opt;
		}
		
		
		### NOTE : $navParams['RPTGCalendar']['week'] for MONTHLY CASE has been processed in Model
		$RPTGCalendar = $this->_getPTGCalendarData($opt);
		$navParams['RPTGCalendar'] = $RPTGCalendar;  //pr($navParams['RPTGCalendar']);
		
		
		
		//pr($dateParams); 
		
		$params = array_merge($navParams, compact('dateParams','query', 'type','opt','cvisityear_tables', 'user_id')); //pr($params);
		$params['user'] = $this->user;
		
		$params['datewise_monitoring'] = true;
		$dateRange = array();

		if (!empty($dateParams['from']) && !empty($dateParams['to'])) {

			$current = strtotime($dateParams['from']);
			$end     = strtotime($dateParams['to']);

			while ($current <= $end) {
				$dateRange[] = date('Y-m-d', $current);
				$current = strtotime('+1 day', $current);
			}
		}//pr($dateRange);
		$this->set('dateRange', $dateRange);
		
		$userCalls = [];
		
		$userCalls = $this->Report->usersMonitoringReport($params); 
		
		//pr($userCalls); die();
		
		$this->loadModel('Role');
		$includedRoles = $this->Role->find('list', array('order' => ['grade' => 'asc', 'id' => 'asc', 'name' => 'asc'], 'conditions' => ['grade >' => 3]));
		
		
		$this->set(compact('query', 'type','opt', 'userCalls', 'user_id', 'weeks', 'includedRoles', 'dateParams', 'reportName'));
		
		
		if(isset($this->request->query['export'])){   
			if($type == 'between'){	
				$fileName = $reportName.'_'.$query['from'].'_to_'.$query['to'];				
			}else{
				//$fileName = 'users_monitoring_'.date('F_Y', $params['strtotime']);
				$fileName = get_export_file_name($reportName, $dateParams);
				$this->set('filename', $fileName); 				
			}			
			$this->set('filename', $fileName);
			$this->set('month', date('F Y', $params['strtotime']));

			$this->render('users_monitoring_report_xls','export_xls');
			$this->render('users_monitoring_report_xls');
		}
		
	}
	
	
	function _selectOptions($userCond = array(), $varified=false){

		$this->loadModel('User');	
		$users = $this->User->usersListByRole($this->user, false, $varified, $userCond);
		$this->set(compact('users'));
		$Opdata = isset($this->request->data['Report'] ) ? $this->request->data['Report']  : array();
		$param = !empty($this->request->query ) ? $this->request->query  : array();
		$this->Option->Options($Opdata, $this->user, $param);
	}
	
	//user weekly route customer sales
	
	public function user_weekly_route_sales($uid = ''){

		$this->loadModel('Route');
		$this->loadModel('UserRoute');
		$this->loadModel('Customer');
		$this->loadModel('Brand');
		$this->loadModel('User');
		
		$params = $conditions = $customers = array();
		$rcond = array();
		 
		/* if (!empty($this->request->query['date'])){
			$Day = date('0w', strtotime($this->request->query['date'])); 
		}else{
		    $Day = date('0w'); 
		    $this->request->query['date'] = date('Y-m-d');
		} */
		
		if ( !empty($this->request->query['date'])){
			$date = $this->request->query['date'];
		}else{
		    $date = $this->request->query['date'] = date('Y-m-d'); 
		}
		
		$PTGCalendar = $this->_getPTGCalendarData($date);
		$rpPeriod = getRoutePlanPeriod($date, $PTGCalendar);
		$year  = $rpPeriod['year'];
		$month = $rpPeriod['month'];
		$week  = $rpPeriod['week'];
		
		$day = date('l', strtotime($date));
		

		if (!empty($this->request->query['brand_id'])) {
			$this->request->query['brand_id'] = $this->request->query['brand_id'];
		}

		if (!empty($this->request->query['rt'])){
			$rcond['Route.id'] = $this->request->query['rt'];
		}

		$query = $this->request->query;
		//pr($query);
		
		if($query['date']){
			$dateBuckets = [];
			$baseDate = $query['date'];
			for ($i = 6; $i >= 1; $i--) {

			  	$weekDate  = date('Y-m-d', strtotime($baseDate . " -".($i * 7)." days"));
			  
			  	$PTGCalendar = $this->_getPTGCalendarData($weekDate);
			  	$ptgWeek  = $PTGCalendar['week'];

				$dateBuckets[] = [
					'key'   => 'day_' . $i,
					'date'  => $weekDate,
					'label' => date('M Y', strtotime($weekDate)) . ' '.', Week ' . $ptgWeek.  ' '. date('l', strtotime($weekDate)) .' - '.date('d/m/Y', strtotime($weekDate))
				];
			}
			$dateBuckets[] = ['key'=>'day_0','date' => $baseDate,'label' =>  date('M Y', strtotime($baseDate)) . ' '.', Week ' . $ptgWeek.  ' '. date('l', strtotime($baseDate)) .' - '.date('d/m/Y', strtotime($baseDate))];
		}
		
		
        
		$brands = $brandsData = [];
		$allBrands = $this->Brand->find('all', array('conditions'=>array('status'=>ACTIVE),'order'=>array('Brand.name' => 'Asc')));
		if($allBrands){
			foreach($allBrands as $b){
				$brands[$b['Brand']['id']] = $b['Brand']['name'];
				$brandsData[$b['Brand']['id']] = $b['Brand']; 
			}
		}
		
		$joins = array();
		$params = array_merge(compact('query','dateBuckets'));

		//pr($params); die();
	
		/*JOIN BETWEEN ROUTE AND USER ROUTE*/ 
		//$joins = array(array('table'=>"(SELECT * FROM user_routes WHERE user_id = $uid AND  week = '$Day')", 'type'=>'inner', 'conditions'=>array('Route.id = UserRoute.route_id'), 'alias'=>'UserRoute'));
		$joins = array(array('table'=>"(SELECT * FROM user_routes WHERE user_id = $uid AND year = $year AND month = $month AND week = $week AND day = '$day')", 'type'=>'inner', 'conditions'=>array('Route.id = UserRoute.route_ids'), 'alias'=>'UserRoute'));

		$assignedRoutes = $this->Route->find('all', array('joins' => $joins, 'conditions' => $rcond, 'fields' => array('Route.id','Route.title','UserRoute.week', 'UserRoute.route_ids')));  
		
		$routes = [];
		if(!empty($assignedRoutes)){
			foreach($assignedRoutes as $userRoute){
				$routes[$userRoute['Route']['id']] = $userRoute['Route']['title'];
				$routPlan[$userRoute['UserRoute']['week']] = $userRoute['Route']['title'];
			}
		}
		$result = array();
		if(!empty($routes)){
			$this->loadModel('Customer'); 
			$this->Customer->unbindModel(
				array('belongsTo' => array('State','LgArea','Region','Depot','User','File'))
			);
			
			$customers = $this->Customer->find('all', array('fields'=>array('id','name','route_id'), 'conditions'=>array('route_id'=>array_keys($routes),'Customer.status' => ACTIVE),'order' => array('Customer.first_name ASC', 'Customer.last_name ASC')));  //pr($customers); die();
			
			$result = $this->Route->weeklyRouteSalesReport($params,$routes); //pr($result);
		}
			$udata = $this->User->find('first', array('conditions'=>array('User.id'=>$uid)));
			$reportName = $udata['User']['first_name'].' '.$udata['User']['last_name'].' Weekly Route Sales';

			//pr($result); die();
			$this->set(compact('brands','brandsData','query','dateBuckets','assignedRoutes','result','customers','reportName','uid','routes'));

		if(isset($this->request->query['export'])){  
			$this->set('filename', $reportName);
			$this->render('export_weekly_route_sales','export_xls');
		}

	}
	
}
