<?php
/**
 * Controller name
 *
 * @var string
 */ 
App::uses('AppController', 'Controller');
class BrandReportsController extends AppController {
    var $components = array('Option');
	
	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;
			case 'quarterly':
                $date = ($opt)?$opt:date('Y-m-01');
                $strtotime = strtotime($date);
                $prev = date('Y-m', strtotime('-3 month', $strtotime));
                $next = date('Y-m', strtotime('+3 month', $strtotime) );
                $current = __('From %s to %s', strftime('%b %Y', strtotime('-2 month', $strtotime)), strftime('%b %Y',  $strtotime));
                break;
		}
		$this->set(compact('date', 'strtotime', 'prev', 'next', 'current'));
		return compact('date', 'strtotime', 'prev', 'next', 'current');
	}
	
	function _selectOptions(){
        $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);
    }
	
	
	public function sales($type='monthly', $opt=''){
		
		//$this->autoRender = false;
		
		/* $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->BrandReport->saleQuantity($params);	//pr($numberOfSale);
		
		$this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));
		
		$this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('fields'=>array('id', 'name')));
		
		$this->_selectOptions();
		
		$this->set(compact('query', 'type','opt', 'numberOfSale','brand_groups','brands'));
		if(isset($this->request->query['export'])){   
			$this->set('filename', 'Brand vol sales report');
			$this->render('export_sales','export_xls');
		}
	}
	
	public function no_sale_customers($type='monthly', $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'));

        $numberOfSale = $this->BrandReport->noSaleCustomers($params);	//pr($numberOfSale);

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('fields'=>array('id', 'name')));

        $this->_selectOptions();

        $this->set(compact('query', 'type','opt', 'numberOfSale','brand_groups','brands'));
        if(isset($this->request->query['export'])){
            $this->set('filename', 'No sale customers report');
            $this->render('export_no_sale_customers','export_xls');
        }

    }
	
	public function stock($type='monthly', $opt=''){
        //$this->gradPermission(4);
        $this->loadModel('Depot');
        $this->loadModel('Stock');

        $conditions = array();
        $query = $this->request->query;
       if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg'])){
                $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE 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'])) {
                    $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $region_id = $this->request->query['rg'];
            $conditions['Depot.region_id'] = $this->request->query['rg'];
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $conditions['Depot.id'] = $this->request->query['dp'];
        } 

        $order = array('Depot.title' => 'asc');
        $fields = array('Depot.id','Depot.title', 'Depot.region_id','DivisionRegion.division_id','Depot.opening_stocks', 'Depot.opening_data');
		$joins = array(array('table'=>'divisions_regions', 'alias'=>'DivisionRegion', 'conditions'=>'DivisionRegion.region_id=Depot.region_id'));
        
		
		$depot_Arr = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ));
		
		
        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('conditions'=>array('status !='=>DELETED), 'fields'=>array('id', 'name')));

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $params = array();

        if(!empty($query['from']) || !empty($query['to']) ){
            $type = 'between';
        }
		
		$navParams = $this->_nav_params($type, $opt);	
		$params = array_merge($navParams, compact('query', 'type','opt'));
//pr($depot_Arr);
        if ($depot_Arr){
            $this->BrandReport->stock($depot_Arr, $params);	
        }
        $this->set(compact('offset','depot_Arr', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current','query'));
        $this->_selectOptions();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Brand_Stock_Level_and_Cover_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Brand_Stock_Level_and_Cover_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_stock','export_xls');
        }

    }
	
	public function run_rate($type='monthly', $opt=''){
        //$this->gradPermission(4);
        $this->loadModel('Depot');
        $this->loadModel('Stock');

        $conditions = array();
        $query = $this->request->query;
        if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg'])){
                $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE 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'])) {
                    $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $region_id = $this->request->query['rg'];
            $conditions['Depot.region_id'] = $this->request->query['rg'];
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $conditions['Depot.id'] = $this->request->query['dp'];
        }

        $order = array('Depot.title' => 'asc');
        $fields = array('Depot.id','Depot.title', 'Depot.region_id','DivisionRegion.division_id','Depot.opening_stocks', 'Depot.opening_data');
        $joins = array(array('table'=>'divisions_regions', 'alias'=>'DivisionRegion', 'conditions'=>'DivisionRegion.region_id=Depot.region_id'));


        $depot_Arr = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ));


        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('conditions'=>array('status !='=>DELETED), 'fields'=>array('id', 'name')));

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $params = array();

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

        $navParams = $this->_nav_params($type, $opt);
        $working_days_results = $this->getWorkingDays($type, $opt); //echo $working_days; exit;
        $working_days = $working_days_results['working_days'];
        $default_working_days = $working_days_results['default_working_days'];
        $params = array_merge($navParams, compact('query', 'type','opt'));
//pr($depot_Arr);
        if ($depot_Arr){
            $this->BrandReport->runRate($depot_Arr, $params);
        }
        $this->set(compact('working_days','default_working_days','offset','depot_Arr', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current','query'));
        $this->_selectOptions();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'RR(Run Rate)_Report_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'RR(Run Rate)_Report_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_run_rate','export_xls');
        }

    }

    function getWorkingDays($type, $opt=''){
	    switch($type){
            case 'daily':
                $date = ($opt)?$opt:date('Y-m-d');
                $strtotime = strtotime($date);
                $date1 = $date;
                $date2 = date('Y-m-d', strtotime('+1 day', $strtotime) );
                break;
            case 'weekly':
                $date = ($opt)?$opt:date('Y-m-d');
                $strtotime = strtotime('monday this week', strtotime($date));
                $date1 = date('Y-m-d', $strtotime);
                $date2 = date('Y-m-d', strtotime('+1 week', $strtotime) );
                break;

            case 'between':
                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);
                $date1 = date('Y-m-d', $strtotime);
                break;
            case 'monthly':
                $date = ($opt)?$opt:date('Y-m-01');
                $strtotime = strtotime($date);
                $date1 = date('Y-m-d', $strtotime);
                $date2 = date('Y-m', strtotime('+1 month', $strtotime) );
                break;
        }

        $begin = new DateTime($date1);
        $end = new DateTime( $date2);
        //$end = $end->modify( '+1 day' );

        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);

        $dateDifference = $begin->diff($end);
        $total_days = $dateDifference->days;

        $number_of_sunday = 0;

        //calculate number of sundays
        foreach($daterange as $datetime_obj){
            if(in_array(date('D',strtotime($datetime_obj->format("Y-m-d"))),['Sun'])) {
                $number_of_sunday ++;
            }
        }

        $default_working_days = $total_days - $number_of_sunday;
        $working_days = (!empty($this->request->query['work_days'])) ? $this->request->query['work_days'] : $default_working_days  ;

        $results['working_days'] = $working_days;
        $results['default_working_days'] = $default_working_days;

        return $results;
    }
	
	public function total_sales_analysis($type='monthly', $opt=''){
        //$this->gradPermission(4);
        $this->loadModel('Depot');

        $conditions = array();
        $query = $this->request->query;
        if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg'])){
                $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE 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'])) {
                    $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $region_id = $this->request->query['rg'];
            $conditions['Depot.region_id'] = $this->request->query['rg'];
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $conditions['Depot.id'] = $this->request->query['dp'];
        }

        $order = array('Depot.title' => 'asc');
        $fields = array('Depot.id','Depot.title', 'Depot.region_id','DivisionRegion.division_id','Depot.opening_stocks', 'Depot.opening_data');
        $joins = array(array('table'=>'divisions_regions', 'alias'=>'DivisionRegion', 'conditions'=>'DivisionRegion.region_id=Depot.region_id'));


        $depot_Arr = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ));


        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('conditions'=>array('status !='=>DELETED), 'fields'=>array('id', 'name')));

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $params = array();

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

        $navParams = $this->_nav_params($type, $opt);
        $lastRefMonthArr = array('0'=>'CMonth','1'=>'LM','2'=>'L3M','3'=>'L6M','4'=>'L12M','5'=>'SPLY');
        $params = array_merge($navParams, compact('query', 'type','opt','lastRefMonthArr'));
//pr($depot_Arr);
        if ($depot_Arr){
            $this->BrandReport->totalSalesAnalysis($depot_Arr, $params);
        }
        $this->set(compact('offset','depot_Arr', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current','query','lastRefMonthArr'));
        $this->_selectOptions();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Total_sales_analysis_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Total_sales_analysis_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_total_sales_analysis','export_xls');
        }

    }
	
	public function average_sales_analysis($type='monthly', $opt=''){
        //$this->gradPermission(4);
        $this->loadModel('Depot');

        $conditions = array();
        $query = $this->request->query;
        if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg'])){
                $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE 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'])) {
                    $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $region_id = $this->request->query['rg'];
            $conditions['Depot.region_id'] = $this->request->query['rg'];
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $conditions['Depot.id'] = $this->request->query['dp'];
        }

        $order = array('Depot.title' => 'asc');
        $fields = array('Depot.id','Depot.title', 'Depot.region_id','DivisionRegion.division_id','Depot.opening_stocks', 'Depot.opening_data');
        $joins = array(array('table'=>'divisions_regions', 'alias'=>'DivisionRegion', 'conditions'=>'DivisionRegion.region_id=Depot.region_id'));


        $depot_Arr = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ));


        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('conditions'=>array('status !='=>DELETED), 'fields'=>array('id', 'name')));

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $params = array();

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

        $navParams = $this->_nav_params($type, $opt);
        $lastRefMonthArr = array('0'=>'CMonth','1'=>'LM','2'=>'L3M','3'=>'L6M','4'=>'L12M','5'=>'SPLY');
        $workingDaysArr  = $this->getWorkingDaysByRefMonth($type, $opt,$lastRefMonthArr);
        $params = array_merge($navParams, compact('query', 'type','opt','lastRefMonthArr'));
//pr($depot_Arr);
        if ($depot_Arr){
            $this->BrandReport->averageSalesAnalysis($depot_Arr, $params);
        }


        $this->set(compact('offset','depot_Arr', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current','query','lastRefMonthArr','workingDaysArr'));
        $this->_selectOptions();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Average_sales_analysis_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Average_sales_analysis_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_average_sales_analysis','export_xls');
        }

    }

    function getWorkingDaysByRefMonth($type, $opt='',$lastRefMonthArr=''){
        switch($type){
            case 'daily':
                $date = ($opt)?$opt:date('Y-m-d');
                $strtotime = strtotime($date);
                $date1 = $date;
                $date2 = date('Y-m-d', strtotime('+1 day', $strtotime) );
                break;
            case 'weekly':
                $date = ($opt)?$opt:date('Y-m-d');
                $strtotime = strtotime('monday this week', strtotime($date));
                $date1 = date('Y-m-d', $strtotime);
                $date2 = date('Y-m-d', strtotime('+1 week', $strtotime) );
                break;

            case 'between':
                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);
                $date1 = date('Y-m-d', $strtotime);
                break;
            case 'monthly':
                $date = ($opt)?$opt:date('Y-m-01');
                $strtotime = strtotime($date);
                $date1 = date('Y-m-d', $strtotime);
                $date2 = date('Y-m', strtotime('+1 month', $strtotime) );
                break;
            case 'quarterly':
                $date = ($opt)?$opt:date('Y-m-01');
                $strtotime = strtotime($date);
                $date1 = date('Y-m-d', strtotime('-2 month', $strtotime));
                $date2 = date('Y-m', strtotime('+1 month', $strtotime) );
                break;
        }

        if(!empty($type) && !empty($lastRefMonthArr)){
            foreach ($lastRefMonthArr as $refMonth){
                if($type=='monthly'){
                    switch($refMonth) {
                        case 'CMonth':
                            $date1 = date('Y-m-01', $strtotime);
                            $date2 = date('Y-m', strtotime('+1 month', $strtotime) );
                            break;
                        case 'LM':
                            $date1 = date('Y-m-01', strtotime('-1 month', $strtotime) );
                            $date2 = date('Y-m',$strtotime);
                            break;
                        case 'L3M':
                            $date1 = date('Y-m-01', strtotime('-3 month', $strtotime) );
                            $date2 = date('Y-m',$strtotime);
                            break;
                        case 'L6M':
                            $date1 = date('Y-m-01', strtotime('-6 month', $strtotime) );
                            $date2 = date('Y-m',$strtotime);
                            break;
                        case 'L12M':
                            $date1 = date('Y-m-01', strtotime('-12 month', $strtotime) );
                            $date2 = date('Y-m',$strtotime);
                            break;
                        case 'SPLY':
                            $date1 = date('Y-m-01', strtotime('-1 year', $strtotime) );
                            $date2 = date('Y-m', strtotime('+1 month', strtotime($date1)) );
                            break;
                    }
                }
                if($type=='quarterly'){
                    switch($refMonth) {
                        case 'CQuarter':
                            $date1 = date('Y-m-d', strtotime('-2 month', $strtotime));
                            $date2 = date('Y-m', strtotime('+1 month', $strtotime) );
                            break;
                        case 'LQ':
                            $date1 = date('Y-m-01', strtotime('-5 month', $strtotime) );
                            $date2 = date('Y-m', strtotime('-2 month', $strtotime));
                            break;
                        case 'L2Q':
                            $date1 = date('Y-m-01', strtotime('-8 month', $strtotime) );
                            $date2 = date('Y-m', strtotime('-2 month', $strtotime));
                            break;
                        case 'L3Q':
                            $date1 = date('Y-m-01', strtotime('-11 month', $strtotime) );
                            $date2 = date('Y-m', strtotime('-2 month', $strtotime));
                            break;
                        case 'L4Q':
                            $date1 = date('Y-m-01', strtotime('-14 month', $strtotime) );
                            $date2 = date('Y-m', strtotime('-2 month', $strtotime));
                            break;
                        case 'SPLY':
                            $strtotime2 = strtotime(date('Y-m-01', strtotime('-1 year', $strtotime) ));
                            $date1 = date('Y-m-01', strtotime('-2 month', $strtotime2) );
                            $date2 = date('Y-m', strtotime('+1 month', $strtotime2) );
                            break;
                    }
                }
                //echo $date1.','.$date2.nl2br("\n");
                $begin = new DateTime($date1);
                $end = new DateTime( $date2);

                $interval = new DateInterval('P1D');
                $daterange = new DatePeriod($begin, $interval ,$end);

                $dateDifference = $begin->diff($end);
                $total_days = $dateDifference->days;

                $number_of_sunday = 0;

                //calculate number of sundays
                foreach($daterange as $datetime_obj){ //if($refMonth == 'CMonth') pr($datetime_obj);
                    if(in_array(date('D',strtotime($datetime_obj->format("Y-m-d"))),['Sun'])) {
                        $number_of_sunday ++;
                    }
                }

                $default_working_days = $total_days - $number_of_sunday;
                $working_days = (!empty($this->request->query['work_days'][$refMonth])) ? $this->request->query['work_days'][$refMonth] : $default_working_days  ;

                $results[$refMonth]['working_days'] = $working_days;
                $results[$refMonth]['default_working_days'] = $default_working_days;
            }
        }
        //pr($results);exit;
        return $results;
    }
	
	public function quarterly_total_sales_analysis($type='quarterly', $opt=''){
        //$this->gradPermission(4);
        $this->loadModel('Depot');

        $conditions = array();
        $query = $this->request->query;
        if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg'])){
                $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE 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'])) {
                    $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $region_id = $this->request->query['rg'];
            $conditions['Depot.region_id'] = $this->request->query['rg'];
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $conditions['Depot.id'] = $this->request->query['dp'];
        }

        $order = array('Depot.title' => 'asc');
        $fields = array('Depot.id','Depot.title', 'Depot.region_id','DivisionRegion.division_id','Depot.opening_stocks', 'Depot.opening_data');
        $joins = array(array('table'=>'divisions_regions', 'alias'=>'DivisionRegion', 'conditions'=>'DivisionRegion.region_id=Depot.region_id'));


        $depot_Arr = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ));


        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('conditions'=>array('status !='=>DELETED), 'fields'=>array('id', 'name')));

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $params = array();

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

        $navParams = $this->_nav_params($type, $opt);
        $lastRefMonthArr = array('0'=>'CQuarter','1'=>'LQ','2'=>'L2Q','3'=>'L3Q','4'=>'L4Q','5'=>'SPLY');
        $params = array_merge($navParams, compact('query', 'type','opt','lastRefMonthArr'));
//pr($depot_Arr);
        if ($depot_Arr){
            $this->BrandReport->quarterlyTotalSalesAnalysis($depot_Arr, $params);
        }
        $this->set(compact('offset','depot_Arr', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current','query','lastRefMonthArr'));
        $this->_selectOptions();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Quarterly_total_sales_analysis_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Quarterly_total_sales_analysis_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_quarterly_total_sales_analysis','export_xls');
        }

    }

    public function quarterly_average_sales_analysis($type='quarterly', $opt=''){
        //$this->gradPermission(4);
        $this->loadModel('Depot');

        $conditions = array();
        $query = $this->request->query;
        if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg'])){
                $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE 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'])) {
                    $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $region_id = $this->request->query['rg'];
            $conditions['Depot.region_id'] = $this->request->query['rg'];
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $conditions['Depot.id'] = $this->request->query['dp'];
        }

        $order = array('Depot.title' => 'asc');
        $fields = array('Depot.id','Depot.title', 'Depot.region_id','DivisionRegion.division_id','Depot.opening_stocks', 'Depot.opening_data');
        $joins = array(array('table'=>'divisions_regions', 'alias'=>'DivisionRegion', 'conditions'=>'DivisionRegion.region_id=Depot.region_id'));


        $depot_Arr = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ));


        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('conditions'=>array('status !='=>DELETED), 'fields'=>array('id', 'name')));

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $params = array();

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

        $navParams = $this->_nav_params($type, $opt);
        $lastRefMonthArr = array('0'=>'CQuarter','1'=>'LQ','2'=>'L2Q','3'=>'L3Q','4'=>'L4Q','5'=>'SPLY');
        $workingDaysArr  = $this->getWorkingDaysByRefMonth($type, $opt,$lastRefMonthArr);
        $params = array_merge($navParams, compact('query', 'type','opt','lastRefMonthArr'));
//pr($depot_Arr);
        if ($depot_Arr){
            $this->BrandReport->quarterlyAverageSalesAnalysis($depot_Arr, $params);
        }


        $this->set(compact('offset','depot_Arr', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current','query','lastRefMonthArr','workingDaysArr'));
        $this->_selectOptions();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Quarterly_Average_sales_analysis_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Quarterly_Average_sales_analysis_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_quarterly_average_sales_analysis','export_xls');
        }

    }
	public function availability_report($type='monthly', $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'));

        $numberOfSale = $this->BrandReport->availabilityReport($params);	//pr($numberOfSale);exit;

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('fields'=>array('id', 'name')));

        $this->_selectOptions();

        $this->set(compact('query', 'type','opt', 'numberOfSale','brand_groups','brands'));
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Availability_report_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Availability_report_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_availability_report','export_xls');
        }

    }
	
	public function depot_sales_details($type='monthly', $opt=''){
        //$this->gradPermission(4);
        $this->loadModel('Depot');
        $this->loadModel('Stock');

        $conditions = array();
        $query = $this->request->query;
        if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg'])){
                $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE 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'])) {
                    $conditions[] = 'Depot.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $region_id = $this->request->query['rg'];
            $conditions['Depot.region_id'] = $this->request->query['rg'];
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $conditions['Depot.id'] = $this->request->query['dp'];
        }

        $order = array('Depot.title' => 'asc');
        $fields = array('Depot.id','Depot.title', 'Depot.region_id','DivisionRegion.division_id');
        $joins = array(array('table'=>'divisions_regions', 'alias'=>'DivisionRegion', 'conditions'=>'DivisionRegion.region_id=Depot.region_id'));


        $depot_Arr = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ));


        $this->loadModel('Brand');
        $brands = $this->Brand->find('list', array('conditions'=>array('status !='=>DELETED), 'fields'=>array('id', 'name')));

        $this->loadModel('BrandGroup');
        $brand_groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name')));

        $params = array();

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

        $navParams = $this->_nav_params($type, $opt);
        $params = array_merge($navParams, compact('query', 'type','opt'));
//pr($depot_Arr);
        if ($depot_Arr){
            $this->BrandReport->depotSalesDetails($depot_Arr, $params);
        }
        $this->set(compact('offset','depot_Arr', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current','query'));
        $this->_selectOptions();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Depot_Sales_Details_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Depot_Sales_Details_'.date('F_Y', $params['strtotime']);
            }

            $this->set('filename', $fileName);
            $this->render('export_depot_sales_details','export_xls');
        }

    }
	
	
}