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

    public function afterFilter() {
        $this->ReportMonitor->end($this);
    }

    public function depotStock($type='', $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 = $this->paginate['order'] = array('Depot.title' => 'asc');
        $fields = $this->paginate['fields'] = array('Depot.id','Depot.title', 'Depot.opening_stocks', 'Depot.opening_data');
        $this->_roleConditions($conditions, 'Depot');
        if(isset($this->request->query['export'])){
            if(!empty($this->request->query['offset'])){
                $offset = (int) $this->request->query['offset'];
                if($offset > 10000){
                    $offset = 0;
                }
            }else{
                $offset = 0;
            }

            $joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
            $depots = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=> $fields ,'joins' => $joins ,'limit'=>10000, 'offset'=>$offset));
        }else{
            $depots = $this->paginate('Depot', $conditions);
        }
        $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';
        }

        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', date('M d, Y', $strtotime), date('Y-m-d', strtotime('+6 days', $strtotime)));
                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 '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);
                $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;
        }

        //$depoList = $this->Depot->getlist($region_id);
        if ($depots){
            $this->Stock->depotStock($depots, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
        }
        $this->set(compact('offset','depots', 'brands', 'brand_groups','type','opt', 'date', 'prev', 'next', 'current'));
        $this->_selectOptions2();
        //pr($depots);
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'depotStock_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'depotStock_'.date('F_Y', $strtotime);
            }

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

    }
    

    public function warehouseStock($type='', $opt=''){
        $this->loadModel('Warehouse');
        $this->loadModel('Stock');
        $this->loadModel('Division');
        $this->loadModel('Region');
        $this->loadModel('Brand');
        $this->loadModel('BrandGroup');
        $conditions = array();
        $query = $this->request->query;
        if ( !empty($this->request->query['wh'])){
            $conditions['Warehouse.id'] = $this->request->query['wh'];
        }
        if(!empty($this->request->query['dv']) && empty($this->request->query['rg'])){
            $division_id = $this->request->query['dv'];
            $divisionRegion = ClassRegistry::init('DivisionsRegion');
            $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' =>$this->request->query['dv'])));
            $conditions['Warehouse.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'])) {
                    $conditions[] = 'Warehouse.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . ')';
                }
            }
        }
        if (!empty($this->request->query['rg']) ){
            $conditions['Warehouse.region_id'] = $this->request->query['rg'];
        }
        $order = $this->paginate['order'] = array('Warehouse.title' => 'asc');
        $fields = $this->paginate['fields'] = array('Warehouse.id','Warehouse.title', 'Warehouse.opening_stocks');
        $this->_roleConditions($conditions, 'Warehouse');
        if(isset($this->request->query['export'])){
            if(!empty($this->request->query['offset'])){
                $offset = (int) $this->request->query['offset'];
                if($offset > 10000){
                    $offset = 0;
                }
            }else{
                $offset = 0;
            }

            $joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
            $warehouses = $this->Warehouse->find('all',array('conditions' =>$conditions,'order'=>$order,'fields' => $fields, 'joins' => $joins ,'limit'=>10000, 'offset'=>$offset));
        }else{
            $warehouses = $this->paginate('Warehouse', $conditions);
        }

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

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

        switch($type){
            case 'daily':
                //	$this->gradPermission(2);
                $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':
                //	$this->gradPermission(2);
                $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', date('M d, Y', $strtotime), date('Y-m-d', strtotime('+6 days', $strtotime)));
                break;
            case 'monthly':
                //	$this->gradPermission(2);
                $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 '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);
                $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;
        }
        $region_id = 0;
        if ($this->user['Role']['grade'] == 4){
            $region_id = $this->user['region_id'];
        }elseif ($this->user['Role']['grade'] == 2 ){
            $region_id = $this->user['region_id'];
        }
       /*  $divisions = $this->Division->getList();
        $division_id = !empty($this->request->query['dv']) ? $this->request->query['dv'] : '0';
        $regions = $this->Region->getList($division_id);
        $region_id = !empty($this->request->query['rg']) ? $this->request->query['rg'] : $region_id;

        $depoList = $this->Warehouse->getlist($region_id); */
		$Opdata =  array();
		$param = isset($querystring ) ? $querystring  : array();
		$this->Option->Options($Opdata, $this->user,$param);
        if ($warehouses){
            $this->Stock->warehouseStock($warehouses, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
        }
        $this->set(compact('offset','warehouses', 'brands','brand_groups','type','opt', 'date', 'prev', 'next', 'current'));

        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'warehouseStock_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'warehouseStock_'.date('F_Y', $strtotime);
            }

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


    function _roleConditions(&$conditions, $model){
        switch($this->user['Role']['grade']){
            case 2:
                if($this->user['role_id'] == DIVISIONMANAGER) {
                    $conditions[] = $model . '.region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->user['division_id'] . ')';
                }
                break;
            case 3:
                $conditions[$model.'.region_id'] = $this->user['region_id'];
                break;
            case 4:
                $conditions[$model.'.id'] = $this->user['depot_id'];
                break;
        }
        //pr($conditions);exit;
    }

    public function depotActivity($type='daily', $opt=''){
        $rIds = array();
        if (!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            if (empty($this->request->query['rg']) && empty($this->request->query['dp']) ) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('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']) && empty($this->request->query['dp'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $this->request->query['dv'])));
                }
            }
        }
        if(!empty($this->request->query['rg'])){
            $region_id = $this->request->query['rg'];
        }else{
            $region_id =0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
            if ($this->user['Role']['grade'] == 4){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $depot_id = $this->request->query['dp'];
        }elseif(!empty($this->user['depot_id']) && empty($this->request->query['dv']) && empty($this->request->query['rg'])){
            $depot_id = $this->user['depot_id'];
        }
        $this->loadModel('Division');
        $this->loadModel('Region');
        $this->loadModel('Depot');

        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
        if ($this->user['Role']['grade'] > 4){
            throw new MethodNotAllowedException();
        }elseif($this->user['Role']['grade'] == 4 && $depot_id != $this->user['depot_id'] ){
            throw new MethodNotAllowedException();
        }elseif($this->user['Role']['grade'] == 3){
            if(!empty($rIds)){
                $depotList = $this->Depot->getlistbyrIds($rIds);
            }else {
                $depotList = $this->Depot->getlist($this->user['region_id']);
            }

        }else{
            if(!empty($rIds)){
                $depotList = $this->Depot->getlistbyrIds($rIds);
            }else{
                $depotList = $this->Depot->getlist($region_id);
            }
        }
		

        if (!empty($depotList)){
            $depotIds = array_keys($depotList);
            $depot_id2 = $depotIds['0'];
        }

        $this->loadModel('Brand');
        $this->loadModel('Stock');
        $query = $this->request->query;
        $depots = array();
        $depot = @$this->Depot->findById($depot_id);
        if (empty($depot) && !empty($depot_id2)){
            $depot = $this->Depot->findById($depot_id2);
            $depot_id = $depot['Depot']['id'];
        }

        if($depot){
            $depots[] = $depot;
        }
        $brandAll = $brands = array();
        $brandList = $this->Brand->find('all', array('conditions'=>array('status !='=>DELETED)));
        foreach($brandList as $br){
            $brands[$br['Brand']['id']] = $br['Brand']['name'];
            $brandAll[$br['Brand']['id']] = $br['Brand'];
        } //pr($brands);

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

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

        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', date('M d, Y', $strtotime), date('Y-m-d', strtotime('+6 days', $strtotime)));
                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 '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);
                $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;
        }
//pr($depots);
        $depotCal = $this->Depot->depotReportCal($depot_id, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
        //pr($depotCal);

        //pr($depots);

        if(!empty($depots)){

            $this->Stock->depotStock($depots, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
        }
        $this->set(compact('depots','divisions','regions','depoList', 'brands', 'brandGroups', 'depotCal', 'brandAll', 'type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query','depotList'));

        //pr($depots);

        $this->set('depots', $depots);
        $querystring = $this->request->query;
        $this->set(compact('querystring'));
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'DepotActivity_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'DepotActivity_'.date('F_Y', $strtotime);
            }

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

    public function depotsales($type='daily', $opt=''){

        //	$this->gradPermission(4);
        $this->loadModel('Depot');
        $conditions = array();
        $incCond = $fiCond = $saleCond = $conSaleCond = '';
        $query = $this->request->query;

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

        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);

                $saleCond = "AND Sale.sale_date = '$date'";
                $saleCond1 = "AND sale_date = '$date'";
                //$saleCond1 = $redisCond = "AND sale_date = '$date'";
                $incCond = "AND incentive_date = '$date'";
                $fiCond = "AND sale_date = '$date'";
                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', date('M d, Y', $strtotime), date('Y-m-d', strtotime('+6 days', $strtotime)));

                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-d', strtotime('+6 days', $strtotime));

                $saleCond = "AND Sale.sale_date between '$d1' AND '$d2'";
                $saleCond1 = $redisCond = "AND sale_date between '$d1' AND '$d2'";
                $incCond = "AND incentive_date between '$d1' AND '$d2'";
                $fiCond = "AND sale_date between '$d1' AND '$d2'";

                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);

                $d1 = date('Y-m-d', $strtotime);
                //$d2 =  date('Y-m-d', strtotime('+1 month', $strtotime));
                $d2 =  date('Y-m-t', $strtotime);
                $saleCond = "AND Sale.sale_date between '$d1' AND '$d2'";
                $saleCond1 = $redisCond = "AND sale_date between '$d1' AND '$d2'";
                $incCond = "AND incentive_date between '$d1' AND '$d2'";
                $fiCond = "AND sale_date between '$d1' AND '$d2'";
                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');
                }

                $d1 = $date;
                $d2 = $date2;

                $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)));

                $saleCond = "AND Sale.sale_date between '$d1' AND '$d2'";
                $saleCond1 = $redisCond = "AND sale_date between '$d1' AND '$d2'";
                $incCond = "AND incentive_date between '$d1' AND '$d2'";
                $fiCond = "AND sale_date between '$d1' AND '$d2'";
                break;
        }
        //pr($saleCond);
        $this->Depot->virtualFields['total_sale'] = 'SUM(Sale.cal_qty)';

        /* $fields = $this->paginate['fields'] = array('Depot.id', 'Depot.title', 'IFNULL(SUM(Sale.cal_qty),0) AS total_sale', '(IFNULL((SELECT SUM(cal_qty) FROM incentives WHERE depot_id = Depot.id '.$incCond.'),0) + (IFNULL((SELECT SUM(cal_qty) FROM free_items WHERE depot_id = Depot.id '.$fiCond.'),0))) AS total_inc' ); */

        $fields = $this->paginate['fields'] = array('Depot.id', 'Depot.title', 'IFNULL(SUM(Sale.cal_qty),0) AS total_sale',  '(IFNULL((SELECT SUM(cal_qty) FROM consumer_sales WHERE depot_id = Depot.id '.$saleCond1.'),0)) AS total_consale',  '(IFNULL((SELECT SUM(cal_qty) FROM incentives WHERE depot_id = Depot.id '.$incCond.'),0) + (IFNULL((SELECT SUM(cal_qty) FROM free_items WHERE depot_id = Depot.id '.$fiCond.'),0))) AS total_inc' );

        /* $fields = $this->paginate['fields'] = array('Depot.id', 'Depot.title',
 '(IFNULL((SELECT SUM(cal_qty) FROM redistributor_saleitems WHERE depot_id = Depot.id '.$redisCond.'),0)+ (IFNULL((SELECT SUM(cal_qty) FROM sales WHERE depot_id = Depot.id '.$saleCond1.'),0))) AS total_sale', 
 '(IFNULL((SELECT SUM(cal_qty) FROM incentives WHERE depot_id = Depot.id '.$incCond.'),0) + (IFNULL((SELECT SUM(cal_qty) FROM free_items WHERE depot_id = Depot.id '.$fiCond.'),0))) AS total_inc' ); */




        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'];
                $conditions['Depot.region_id'] = $this->user['region_id'];
            }
        }

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

        $group = $this->paginate['group'] = array('Depot.id');
        $order = $this->paginate['order'] = array('total_sale'=>'desc');
        $join = $this->paginate['joins'] = array( array('table'=>'sales', 'type'=>'left', 'alias'=>'Sale', 'conditions'=>array("Depot.id = Sale.depot_id $saleCond" )) );

        $querystring = $this->request->query;
        $this->set(compact('querystring'));
        if(isset($this->request->query['export'])){
            if(!empty($this->request->query['offset'])){
                $offset = (int) $this->request->query['offset'];
                if($offset > 10000){
                    $offset = 0;
                }
            }else{
                $offset = 0;
            }

            $joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
            $depots = $this->Depot->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=>$fields,'joins' => $joins ,'limit'=>10000, 'offset'=>$offset,'group'=>$group));

        } else {
            $depots = $this->paginate('Depot', $conditions);
        }
        //pr($depots);

        $this->set(compact('offset','depots','date','type','query', 'prev', 'next', 'current', 'strtotime'));
        $this->_selectOptions2();
        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'DepoSales_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'DepoSales_'.date('F_Y', $strtotime);
            }

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

    function payments($type='daily', $opt=''){
        //	$this->gradPermission(4);
        $this->loadModel('Payment');
        $conditions = array();
        $incCond = $fiCond = $saleCond = '';
        $query = $this->request->query;

        if (!empty($this->request->query['dv']) ) {
            $division_id = $this->request->query['dv'];
            if(empty($this->request->query['rg']) && empty($this->request->query['dp'])) {
                $conditions[] = 'Payment.depot_id IN (SELECT id from depots WHERE 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']) && empty($this->request->query['dp'])) {
                    $conditions[] = 'Payment.depot_id IN (SELECT id from depots WHERE 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'];
            if(empty($this->request->query['dp'])){
                $conditions[] = 'Payment.depot_id IN (SELECT id from depots WHERE 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['Payment.depot_id'] = $this->request->query['dp'];
        }

        if ( !empty($this->request->query['bn'])){
            $conditions['Payment.bank_id'] = $this->request->query['bn'];
        }

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

        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);

                $conditions['Payment.payment_date'] = $date;
                //$saleCond = "AND Sale.sale_date = '$date'";
                //$incCond = "AND incentive_date = '$date'";
                //$fiCond = "AND sale_date = '$date'";;
                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', date('M d, Y', $strtotime), date('Y-m-d', strtotime('+6 days', $strtotime)));

                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-d', strtotime('+6 days', $strtotime));
                $conditions[]= "Payment.payment_date between '$d1' AND '$d2'";
                //$saleCond = "AND Sale.sale_date between '$d1' AND '$d2'";
                //$incCond = "AND incentive_date between '$d1' AND '$d2'";
                ///$fiCond = "AND sale_date between '$d1' AND '$d2'";

                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);

                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-d', strtotime('+1 month', $strtotime));
                $conditions[]= "Payment.payment_date between '$d1' AND '$d2'";
                //$saleCond = "AND Sale.sale_date between '$d1' AND '$d2'";
                //$incCond = "AND incentive_date between '$d1' AND '$d2'";
                //$fiCond = "AND sale_date between '$d1' AND '$d2'";
                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');
                }

                $d1 = $date;
                $d2 = $date2;

                $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)));

                $conditions[]= "Payment.payment_date between '$d1' AND '$d2'";
                break;
        }

        $this->Payment->virtualFields['total_payment'] = 'SUM(Payment.amount)';

        $fields = array('Payment.id','Bank.id','Bank.name', 'Distributor.id', 'Distributor.title', 'IFNULL(SUM(Payment.amount),0) AS total_payment' );
        $joins = array();
        if ($this->user['Role']['grade'] == 3){
            $joins = array(array('table'=>'depots', 'alias'=>'Depot', 'type'=>'left', 'conditions'=>array('Payment.depot_id = Depot.id')));
            $conditions['Depot.region_id'] = $this->user['region_id'];
        }
        $group = array('Payment.distributor_id', 'Payment.bank_id');
        $order = array('total_payment'=>'desc');

        $payments = array();
        $paymentList = $this->Payment->find('all', array('conditions'=> $conditions, 'fields'=>$fields, 'order'=>$order, 'group'=>$group,'joins'=> $joins));

        if ($paymentList){
            $total = array();
            foreach($paymentList as $payment){
                $distId = $payment['Distributor']['title'];
                $bankId = $payment['Bank']['name'];
                $payments[$distId][$bankId] = $payment['0']['total_payment'];
                if (!isset($total[$distId])){
                    $total[$distId] = 0;
                }
                $total[$distId] += $payment['0']['total_payment'];
            }
        }

        $this->loadModel('Depot');
        $this->loadModel('Bank');
        $banks = $this->Bank->find('list', array('fields'=>array('id', 'name'), 'order'=>array('name'=>'asc')));
        //$depots = $this->Depot->find('list', array('fields'=>array('id', 'title')));

        $this->set(compact('payments','total', 'banks', 'date','type','query', 'prev', 'next', 'current', 'strtotime'));
        $this->_selectOptions2();
    }

    function do_verify(){
        if (!empty($this->request->data['mId']) && !empty($this->request->data['mdl'])){
            $model = $this->request->data['mdl'];
            $id = $this->request->data['mId'];
            $this->loadModel($model);
            $this->$model->id = $id;
            if ($this->request->data['unverify']){
                $data[$model] = array('status'=>INACTIVE, 'verify_by'=>0);
                $status = INACTIVE;
            }else{
                $data[$model] = array('status'=>ACTIVE, 'verify_by'=>$this->user['id']);
                $status = ACTIVE;
            }
            if ($this->$model->save($data)){
                if ($model == "Order"){
                    $this->$model->query("UPDATE sales SET status = $status WHERE order_id = $id");
                    $this->$model->query("UPDATE free_items SET status = $status WHERE order_id = $id");
                }
                if ($model == "RedistributorSale"){
                    $this->$model->query("UPDATE redistributor_saleitems SET status = $status WHERE redistributor_sale_id = $id");
                }
                if ($this->request->data['unverify']){
                    echo "<a href='javascript:void(0);' id=$id mdl='$model' class='verify-it'>Verify</a>";
                }else{
                    echo '<span class="lft-span">'.__('Verified by %s', $this->user['first_name'].' '.$this->user['last_name']).'</span>';

                    echo "<a href='javascript:void(0);' id=$id mdl='$model' class='unverify-it'> Unverify</a>";
                }
            }else{
                echo __('Error: There are some server error, try latter');
            }
        }
        exit;
    }

    public function reimbursementoverexpense($type='daily', $opt=''){
        $this->loadModel('ExpenseReimbursement');
        $this->loadModel('User');
        $this->loadModel('Depot');
        $conditions = array('User.status'=>ACTIVE);
        if(!empty($this->request->query['ul'])){
            $conditions['User.id']=$this->request->query['ul'];
        }
        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'];
        }
        $query = $this->request->query;
        $fields = $this->paginate['fields'] = array('User.id','User.first_name','User.last_name','User.depot_id');
        if(isset($this->request->query['export'])){
            if(!empty($this->request->query['offset'])){
                $offset = (int) $this->request->query['offset'];
                if($offset > 10000){
                    $offset = 0;
                }
            }else{
                $offset = 0;
            }

            $joins = isset($this->paginate['joins']) ? $this->paginate['joins'] : array();
            $reimExp = $this->User->find('all',array('conditions' =>$conditions, 'joins' => $joins ,'limit'=>10000, 'offset'=>$offset));

        }else{
            $reimExp = $this->paginate('User', $conditions);
        }
        $params = array();
        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', date('M d, Y', $strtotime), date('Y-m-d', strtotime('+6 days', $strtotime)));
                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;

        }
        if ($reimExp){
            $this->ExpenseReimbursement->expenseReimbur($reimExp, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
        }
        $ulists = $this->User->usersList();

//        $depot = $this->Depot->getlist($region_id);
        $this->set(compact('offset','type','opt', 'date', 'prev', 'next', 'current','reimExp','ulists'));
        $this->_selectOptions2();
        if(isset($this->request->query['export'])){
            $this->set('filename', 'Expense_Reimbursement');

            $this->render('export_reimbursementoverexpense','export_xls');
        }
    }

    /**
     * Action Reports - balance_report
     * @params: $customer_id=(Int)
     * @function : customer balance if the sales is in credit
     * @table : customers
     * return : void()
     */
    public function balance_report($customer_id=0){
        $this->loadModel('Order');
        $this->loadModel('Customer');

        //$customers = $this->Customer->customerListByRole($this->user);
        if($this->request->query['cid']){
            $customer_id = $this->request->query['cid'];
        }
        if($customer_id){
            $this->Customer->unbindModelAll();
            $customerData = $this->Customer->findById($customer_id);
            $conditions = array('Order.customer_id'=>$customer_id);
            $currentBalance = $this->Order->getOrderBalance($customer_id);
            $this->Order->unbindModelAll();
            $this->paginate['joins'] = array(array('table'=>'sales', 'alias' => 'Sale', 'type'=>'inner', 'conditions' => 'Order.id = Sale.order_id'));
            $this->paginate['order'] = array('Order.id'=>'asc');
            $this->paginate['group'] = array('Order.id');
            $this->paginate['fields'] = array('Order.*','SUM(Sale.cal_qty) AS qty');
            $customerOrders = $this->paginate('Order', $conditions);

            if(!empty($this->params->paging['Order']['page']) && $this->params->paging['Order']['page'] > 1 && !empty($customerOrders['0']['Order']['id'])){
                $prevBalance = $this->Order->getOrderBalance($customer_id, $customerOrders['0']['Order']['id']);
            }
        }

        $this->set(compact('customers','customerOrders','customerData','currentBalance','prevBalance'));
    }

    public function mwddepotsales($type='monthly', $opt=''){
        $this->loadModel('Depot');
        $conditions = array();
        $query = $this->request->query;

        switch($type){
            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);
                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-d', strtotime('+1 month', $strtotime));

                if(empty( $query['from'])){
                    $d1 = date('Y-m-d', $strtotime);
                }else{
                    $d1 =  $query['from'];
                    //$prev = date('Y-m', strtotime('-1 month',  strtotime($d1)) );
                    //$next = date('Y-m', strtotime('+1 month',  strtotime($d1)) );
                }
                if(empty( $query['to'])){
                    $d2 =  date('Y-m-t', $strtotime);
                }else{
                    $d2 =  $query['to'];
                    //$prev = date('Y-m', strtotime('-1 month', strtotime($d2)) );
                    //$next = date('Y-m', strtotime('+1 month',  strtotime($d2)) );
                }

                break;
        }
        $rIds = array();
        if (!empty($this->request->query['dv'])){
            $division_id = $this->request->query['dv'];
            if (empty($this->request->query['rg']) && empty($this->request->query['dp']) ) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('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']) && empty($this->request->query['dp'])) {
                    $divisionRegion = ClassRegistry::init('DivisionsRegion');
                    $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $this->request->query['dv'])));
                }
            }
        }
        if(!empty($this->request->query['rg'])){
            $region_id = $this->request->query['rg'];
        }else{
            $region_id =0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
            if ($this->user['Role']['grade'] == 4){
                $region_id = $this->user['region_id'];
            }
        }

        if (!empty($this->request->query['dp'])){
            $depot_id = $this->request->query['dp'];
        }elseif(!empty($this->user['depot_id']) && empty($this->request->query['dv']) && empty($this->request->query['rg'])){
            $depot_id = $this->user['depot_id'];
        }
        $this->loadModel('Division');
        $this->loadModel('Region');

        $divisions = $this->Division->getList();
        $regions = $this->Region->getList($division_id);
        if ($this->user['Role']['grade'] > 4){
            throw new MethodNotAllowedException();
        }elseif($this->user['Role']['grade'] == 4 && $depot_id != $this->user['depot_id'] ){
            throw new MethodNotAllowedException();
        }elseif($this->user['Role']['grade'] == 3){
            if(!empty($rIds)){
                $depots = $this->Depot->getlistbyrIds($rIds);
            }else {
                $depots = $this->Depot->getlist($this->user['region_id']);
            }

        }else{
            if(!empty($rIds)){
                $depots = $this->Depot->getlistbyrIds($rIds);
            }else{
                $depots = $this->Depot->getlist($region_id);
            }
        }
        $querystring = $this->request->query;
        $this->set(compact('querystring'));
        $offset = 0;

        $params = array_merge(array('d1'=>$d1, 'd2'=>$d2), $this->request->query);
        $reportDatas = $this->Report->mwddepotsales($params, $depots);
        //	pr($reportDatas);

        $this->set(compact('divisions','regions','depots','date','type','query', 'prev', 'next', 'current', 'strtotime', 'd1', 'd2','offset','reportDatas'));
        if(isset($this->request->query['export'])){
            $this->set('filename', 'DepoSales_'.$date);
            $this->render('export_mwddepotsales','export_xls');
        }
    }

    /**
     * Action Reports - summary_reports
     * @function : customer balance if the sales is in credit
     * @table : customers
     * return : void()
     */
    function summary_reports(){
        $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'];
        }
        if ( !empty($this->request->query['dtype'])){
            $conditions['Depot.type'] = $this->request->query['dtype'];
        }
        if(empty( $query['from'])){
            $d1 = date('Y-m-d', strtotime("-1 month"));
        }else{
            $d1 =  $query['from'];
        }
        if(empty( $query['to'])){
            $d2 = date('Y-m-d');
        }else{
            $d2 =  $query['to'];
        }
        $params = array_merge(array('d1'=>$d1, 'd2'=>$d2), $this->request->query);
        $this->paginate['order'] = array('Depot.title' => 'asc');
        $this->paginate['fields'] = array('Depot.id','Depot.title');
        $this->_roleConditions($conditions, 'Depot');
        if(isset($this->request->query['export'])){
            /* $depots = $this->Depot->find('all',array('conditions'=>$conditions)); */
			$depots = $this->Depot->find('all',array('conditions'=>$conditions, 'fields'=>array('Depot.id','Depot.title'), 'order'=>array('Depot.title' => 'asc')));
        }else{
        $depots = $this->paginate('Depot', $conditions);
        }
        $reportData = $this->Report->summary_reports($params, $depots);


        //pr($reportData);



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

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

        foreach($depots as $depot){
            $depotid = $depot['Depot']['id'];
            if(isset($reportData[$depotid])){
                $sales_value = 0;
                // brand mergeing and sum of total amount for sale and rdsales
                foreach($brands as $key=>$brand){
                    if(isset($reportData[$depotid]['sales']['brand'][$key]) && isset($reportData[$depotid]['rdsales']['brand'][$key])){
                        if(array_key_exists($key, $reportData[$depotid]['sales']['brand']) && array_key_exists($key, $reportData[$depotid]['rdsales']['brand'])){
                            $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_qty'] = $reportData[$depotid]['sales']['brand'][$key]['total_qty']+ $reportData[$depotid]['rdsales']['brand'][$key]['total_qty'];
                            $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_amt'] = $reportData[$depotid]['sales']['brand'][$key]['total_amt']+ $reportData[$depotid]['rdsales']['brand'][$key]['total_amt'];

                            $sales_value += $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_amt'];
                        }
                    }
                    else{
                        if(isset($reportData[$depotid]['sales']['brand'][$key])){
                            if(array_key_exists($key, $reportData[$depotid]['sales']['brand'])){
                                $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_qty'] = $reportData[$depotid]['sales']['brand'][$key]['total_qty'] ;
                                $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_amt'] = $reportData[$depotid]['sales']['brand'][$key]['total_amt'];

                                $sales_value += $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_amt'];
                            }
                        }
                        if(isset($reportData[$depotid]['rdsales']['brand'][$key])){
                            if(array_key_exists($key, $reportData[$depotid]['rdsales']['brand'])){
                                $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_qty'] = $reportData[$depotid]['rdsales']['brand'][$key]['total_qty'] ;
                                $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_amt'] = $reportData[$depotid]['rdsales']['brand'][$key]['total_amt'];

                                $sales_value += $reportData[$depotid]['sale_redis_add']['brand'][$key]['total_amt'];
                            }
                        }
                    }
                }
                // total sale and rdsales value for a depot
                $reportData[$depotid]['sale_redis_add']['sales_value']=$sales_value;
            }
        }

        //pr($reportData);
        $this->set(compact('brands','depots', 'd1', 'd2', 'reportData'));
        $this->_selectOptions2();
        if(isset($this->request->query['export'])){
            $this->set('filename', 'summary_reports');
            $this->render('summary_reports_xls','export_xls');
        }
    }

    function verified_customers_report(){

        $this->autoRender = false;

        $this->loadModel('Customer');

        $customers = $this->Customer->find('all', array('conditions'=>array('not' => array('Customer.verified_latitude' => null, 'Customer.verified_longitude' => null)))); pr($customers);
    }
	
	function expense_summary_report($type='monthly', $opt=''){
        //	$this->gradPermission(4);
        $this->loadModel('ExpType');
        $this->loadModel('Expense');
        
        $conditions = $userCond = $expTypeCond = array();
		
		$userCond['User.status'] = ACTIVE;
       
        $query = $this->request->query;
		
		if (!empty($query['dv'])){
            if (empty($query['rg']) && empty($query['dp'])) {
                $divisionRegion=  ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$query['dv'])));
                
				$userCond['User.region_id IN'] = $conditions['Expense.region_id IN'] =  $rIds;
            }

        }
		
		if (!empty($query['rg'])){
            $userCond['User.region_id'] = $conditions['Expense.region_id'] = $query['rg'];
        }
		
		if (!empty($query['dp'])){
            $userCond['User.depot_id'] = $conditions['Expense.depot_id'] = $query['dp'];
        }

        if (!empty($query['et'])){
            $expTypeCond['ExpType.id'] = $conditions['Expense.exp_type_id'] = $query['et'];
        }

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

        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);
				
				$conditions['Expense.exp_date'] = $date;
                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', date('M d, Y', $strtotime), date('M d, Y', strtotime('+6 days', $strtotime)));

                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-d', strtotime('+6 days', $strtotime));
				
				$conditions['Expense.exp_date BETWEEN ? and ?'] = array($d1, $d2);
				
                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);

                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-t', $strtotime);
				
				$conditions['Expense.exp_date BETWEEN ? and ?'] = array($d1, $d2);
                break;

            case 'between':
                if(empty($query['from'])){
					$d1 = date('Y-m-d');
				}else{
					$d1 =  $query['from'];
				}
				if(empty($query['to'])){
					$d2 = date('Y-m-d');
				}else{
					$d2 =  $query['to'];
				}

                $strtotime = strtotime($d1);
                $prev = ''; //date('Y-m-d', strtotime('-1 week', $strtotime) );
                $next = ''; // date('Y-m-d', strtotime('+1 week', $strtotime) );
				$current = __('From %s to %s', date('M d, Y', $strtotime), date('M d, Y', strtotime($d2)));
				
				$conditions['Expense.exp_date BETWEEN ? and ?'] = array($d1, $d2);

                break;
        }
        //pr($expCond);
		
		
		//report role ids
		$this->loadModel('Role');
		$excluded_role_ids = $this->Role->find('list', array('fields' => array('id'), 'conditions' => array('Role.grade <' => DH))); //pr($excluded_role_ids );
		
		$this->Expense->virtualFields['total_exp_amount'] = 'SUM(Expense.exp_amount)';
        $fields = array('Expense.region_id', 'Expense.exp_type_id', 'Expense.depot_id', 'total_exp_amount','User.role_id');
        $group = array('Expense.exp_type_id','Expense.depot_id');
        $order =  array('ExpType.name'=>'asc');

        $expensData = $this->Expense->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=>$fields,'group'=>$group)); //pr($expensData ); //die();
		
		$expenses = array();
		if(!empty($expensData)){
			foreach($expensData as $exp){
				if(!in_array($exp['User']['role_id'],$excluded_role_ids)){
					$expenses[$exp['Expense']['exp_type_id']][$exp['Expense']['depot_id']] = $exp['Expense']['total_exp_amount'];
				}				
			}
		}
      //pr($expenses);
		$this->loadModel('User');
		$this->User->recursive = -1;
		$this->User->virtualFields['userCount'] = 'COUNT(User.id)';
        $sales_rep = $this->User->find('list', array('fields' => array('User.depot_id', 'userCount'), 'conditions' => $userCond, 'group' => 'User.depot_id')); //pr( $sales_rep );
		
		

        $expTypes = $this->ExpType->find('list', array('fields'=>array('id','name'), 'order'=>$order));

        $this->_options();
        
		$this->loadModel('Depot');
        $depotsH = $this->Depot->getDepotListByRole($this->user, $query);

        $this->set(compact('offset','sales_rep', 'expenses','expTypes','date','type','query', 'prev', 'next', 'current', 'strtotime','depotsH','opt'));

        if(isset($this->request->query['export'])){
            if($type == 'between'){
                $fileName = 'Expense_summary_report_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = 'Expense_summary_report_'.date('F_Y', $strtotime);
            }

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

    function user_expense_summary_report($type='monthly', $opt=''){
        $this->loadModel('ExpType');
        $this->loadModel('Expense');
        $this->loadModel('Role');
        $this->loadModel('User');
		
		$date = $opt = !empty($opt) ? $opt : date('Y-m-d');

        $conditions = $userCond = $roleCond = array();
        $userCond['User.status'] = ACTIVE;
       
		$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);

        if (!empty($query['dv'])){
            if (empty($query['rg']) && empty($query['dp'])) {
                $divisionRegion=  ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$query['dv'])));
                
                $userCond['User.region_id IN'] = $conditions['Expense.region_id IN'] =  $rIds;
            }
        }
        
        if (!empty($query['rg'])){
            $userCond['User.region_id'] = $conditions['Expense.region_id'] = $query['rg'];
        }
        
        if (!empty($query['dp'])){
            $userCond['User.depot_id'] = $conditions['Expense.depot_id'] = $query['dp'];
        }
        if (!empty($query['et'])){
            $conditions['Expense.exp_type_id'] = $query['et'];
        }

        if (!empty($query['r'])){
            $userCond['User.role_id'] = $query['r'];
        }
        if (!empty($query['usr'])){
            $userCond['User.id'] = $conditions['Expense.user_id'] = $query['usr'];
        }
        

        switch($type){
            case 'daily':
                $date = $dateParams['date'];               
                $conditions['Expense.exp_date'] = $date;
				
                break;
				
            case 'weekly':
			case 'monthly':
			case 'between':
                $d1 = $dateParams['from'];
				$d2 = $dateParams['to'];
                
                $conditions['Expense.exp_date BETWEEN ? and ?'] = array($d1, $d2);
                
                break;
        }   
		
		## DH can use other group expenses only. 
		## Supervisors expenses are visible till RM
		if($this->user['Role']['grade'] > 3){
			$query['exp_user_group'] = 'others';
		}
		
		## Find users based on User Groups
		// Call the function to get roles and user list
		$resultExpUserGroup = $this->_getDataforExpUserGroup($query, $userCond, $roleCond);

		// Extract roles and user list from the result
		$roles = $resultExpUserGroup['roles'];
		$userList = $resultExpUserGroup['userList'];
        
        $users = $this->User->usersListByRole($this->user, false, false, $userCond);

        if (empty($query['usr'])) {
            $conditions['Expense.user_id'] =  array_keys($userList);
        }
        $this->Expense->virtualFields['total_exp_amount'] = 'SUM(Expense.exp_amount)';
        $fields = array('Expense.region_id', 'Expense.exp_type_id', 'Expense.depot_id','Expense.user_id', 'total_exp_amount','User.role_id');
        $group = array('Expense.exp_type_id','Expense.user_id');
        $order =  array('ExpType.name'=>'asc');
        //pr($conditions);
        $expensData = $this->Expense->find('all',array('conditions' =>$conditions,'order'=>$order, 'fields'=>$fields,'group'=>$group)); //pr($expensData ); //die();
        
        $expenses = array();
        if(!empty($expensData)){
            foreach($expensData as $exp){
                $expenses[$exp['Expense']['exp_type_id']][$exp['Expense']['user_id']] = $exp['Expense']['total_exp_amount'];
            }
        }
        //pr($expenses);

        $expTypes = $this->ExpType->find('list', array('fields'=>array('id','name'), 'order'=>$order));
        
        $this->_options();
        
		if (!empty($query['exp_user_group'])){
			if($query['exp_user_group'] == 'supervisors'){
				$reportName = 'Supervision Expenses Report';
			}elseif($query['exp_user_group'] == 'others'){
				$reportName = 'Other Users Expenses Report';
			}
		}else{
			$reportName = 'All Users Expenses Report';
		}
		
		
        $this->set(compact('offset','sales_rep', 'expenses','expTypes','roles','userList','roleList','date','type','query', 'prev', 'next', 'current', 'strtotime','depotsH','opt','reportName', 'weeks','users'));

        if(isset($this->request->query['export'])){
			
			$fileName = $reportName;
			
            $fileName .= '_' . $dateParams['title'];
			if(!empty($dateParams['sub_title'])){
				$fileName .= '(' . $dateParams['sub_title'].')';
			}

            $this->set('filename', $fileName);
            $this->render('user_expense_summary_report_xls','export_xls');
        }
    }
	
	function _getDataforExpUserGroup($query, $userCond = [], $roleCond = []) {
		$userCond = !empty($userCond) ? $userCond : [];
        $roleCond = !empty($roleCond) ? $roleCond : [];
		if (!empty($query['exp_user_group'])){
			if($query['exp_user_group'] == 'supervisors'){
							
				switch($this->user['Role']['grade']){
					//Admin can access all roles till RM
					case 1 :
						$userCond['Role.grade <='] = RM;
						
						$roleCond['Role.grade <='] = RM;
					break;
					
					//DM can access own data and RM from his division  
					case 2 :
						if($this->user['role_id'] == DIVISIONMANAGER){
							$userCond['OR'] = array(
									array('User.id' => $this->user['id']), // For OWN data
									array(
										'AND' => array(
											'User.division_id' => $this->user['division_id'],
											'Role.grade' => RM // For RM role under the DM's division
										)
									)
								);		
							$roleCond['OR'] = array(
								'Role.id' => $this->user['role_id'],
								'Role.grade' => RM
							);
						}else{
							$userCond['Role.grade'] = [2,3];							
							$roleCond['Role.grade'] = [2,3];
						}
						
						
					break;
										
					//Regional Manager (RM) can see their own data
					//case 3 :
					default:
						$userCond['User.id'] = $this->user['id'];
						$roleCond['Role.grade'] = RM;
					break;
				} 
				
			}elseif($query['exp_user_group'] == 'others'){
				switch($this->user['Role']['grade']){
					//Admin can access DH and all roles below DH
					case 1 :
						$userCond['Role.grade >='] = DH;
						
						$roleCond['Role.grade >='] = DH;
					break;
					
					//DM can access DH and all roles below DH from his division  
					case 2 :
						if($this->user['role_id'] == DIVISIONMANAGER){
							$userCond['Role.grade >='] = DH;
							$userCond['User.division_id'] = $this->user['division_id'];
						}else{
							$userCond['Role.grade >='] = DH;
						}
						
						$roleCond['Role.grade >='] = DH;
					break;
					
					//RM can access DH and all roles below DH from his region  
					case 3 :
						$userCond['Role.grade >='] = DH;
						$userCond['User.region_id'] = $this->user['region_id'];
						
						$roleCond['Role.grade >='] = DH;
					break;
					
					//DH can see their own data and all roles under him from his depot
					case 4 :
						$userCond['OR'] = array(
									array('User.id' => $this->user['id']), // For OWN data
									array(
										'AND' => array(
											'User.depot_id' => $this->user['depot_id'],
											'Role.grade >' => DH // role under the DH
										)
									)
								);
								
						$roleCond['Role.grade >='] = DH;
					break;
					
					default:
						$userCond['User.id'] = $this->user['id'];
						
						$roleCond['Role.grade'] = $this->user['Role']['grade'];
					break;
				}
			}
        }else{
			switch($this->user['Role']['grade']){
				case 1 :
					$userCond = $roleCond = [];
				break;
				
				case 2 :
						if($this->user['role_id'] == DIVISIONMANAGER){
							$userCond['OR'] = array(
									array('User.id' => $this->user['id']), // For OWN data
									array(
										'AND' => array(
											'User.division_id' => $this->user['division_id'],
											'Role.grade >' => 2 // For all roles under the DM's division
										)
									)
								);		
							$roleCond['OR'] = array(
								'Role.id' => $this->user['role_id'],
								'Role.grade >' => 2
							);
						}else{
							$userCond['Role.grade >='] = 2;
							
							$roleCond['Role.grade >='] = 2;
						}						
				break;
				
				//RM can access DH and all roles below DH from his region  
				case 3 :
					$userCond['OR'] = array(
								array('User.id' => $this->user['id']), // For OWN data
								array(
									'AND' => array(
										'User.region_id' => $this->user['region_id'],
										'Role.grade >' => RM // role under the RM
									)
								)
							);
					$roleCond['OR'] = array(
								'Role.id' => $this->user['role_id'],
								'Role.grade >' => RM
							);
				break;
				
				//DH can see their own data and all roles under him from his depot
				case 4 :
					$userCond['OR'] = array(
								array('User.id' => $this->user['id']), // For OWN data
								array(
									'AND' => array(
										'User.depot_id' => $this->user['depot_id'],
										'Role.grade >' => DH // role under the DH
									)
								)
							);
					$roleCond['OR'] = array(
								'Role.id' => $this->user['role_id'],
								'Role.grade >' => DH
							);
				break;
				
				default:
					$userCond['User.id'] = $this->user['id'];
					
					$roleCond['Role.grade'] = $this->user['Role']['grade'];
					break;
				
			}
			
			
		}

		 //report role ids
        //$this->loadModel('Role');
        $role_threaded = $this->Role->find('threaded', array('conditions'=>$roleCond));  //pr($role_threaded );
		$roles = $this->Role->threaded_to_list($role_threaded);
       
        //$this->loadModel('User'); 
        //$userList = $this->User->usersListByRole($this->user, false, false, $userCond);

		$userListData = $this->User->find('all', array('fields' => array('User.id', 'CONCAT_WS(" ", User.first_name, User.last_name) as name', 'User.status', 'Role.name'), 'conditions'=>$userCond, 'order'=>array('name'=>'asc'))); //pr($users);
		$userList = array();
		if($userListData){
			foreach($userListData as $uData){
				$userList[$uData['User']['id']] = $uData;
			}
		}
        //pr($userList);
		
		return ['roles' => $roles, 'userList' => $userList];

	}
	
	public function getExpUserGroupFilters(){ 
		$this->loadModel('Role');
		$this->loadModel('User');
		$result = $roles = $userList = $query =  $userCond = $roleCond = array();
		$uoption = array("0"=>__('All Users'));
		$roption = array("0"=>__('All Roles'));
		
		if (isset($this->request->data['exp_user_group'])){
			$query['exp_user_group'] = $this->request->data['exp_user_group'];
		}
		
		$userCond['status'] = ACTIVE;
		
		if (!empty($this->request->data['division'])){
			$userCond['division_id'] = $this->request->data['division'];			
		}
		
		if (!empty($this->request->data['region'])){
			$userCond['region_id'] = $this->request->data['region'];			
		}
		
		if (!empty($this->request->data['depot'])){
			$userCond['depot_id'] = $this->request->data['depot'];			
		}
		
		
		$resultExpUserGroup = $this->_getDataforExpUserGroup($query, $userCond, $roleCond);

		$roles = $resultExpUserGroup['roles'];
		$userList = $resultExpUserGroup['userList'];
		

		$result['users'] = $uoption + $userList;
		$result['roles'] = $roption + $roles;
		
		echo json_encode($result);
		exit;
	}
	
	function asset_expense_report($type='monthly', $opt=''){
        //	$this->gradPermission(4);
        $this->loadModel('ExpType');
        $this->loadModel('Expense');
        
        $conditions = $userCond = $assetCond = $expTypeCond = array();
		
		//$assetCond['Asset.status'] = ACTIVE;
       
        $query = $this->request->query;
		
		if (!empty($query['dv'])){
            if (empty($query['rg']) && empty($query['dp'])) {
                $divisionRegion=  ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$query['dv'])));
                
				$userCond['User.region_id IN'] = $conditions['Expense.region_id IN'] =  $rIds;
            }

        }
		
		if (!empty($query['rg'])){
            $userCond['User.region_id'] = $conditions['Expense.region_id'] = $query['rg'];
        }
		
		if (!empty($query['dp'])){
            $userCond['User.depot_id'] = $conditions['Expense.depot_id'] = $query['dp'];
        }
		
		if (!empty($this->request->query['a_type'])){
			if($this->request->query['a_type'] == 'bike'){
				$conditions['ExpType.category_id'] = $expTypeCond['ExpType.category_id'] =  [EXP_CAT_MO_BIKE];
			}elseif($this->request->query['a_type'] == 'vehicle'){
				$conditions['ExpType.category_id'] = $expTypeCond['ExpType.category_id'] =  [EXP_CAT_MO_VEHICLE];
			}
        }else{
			$conditions['ExpType.category_id IN'] = $expTypeCond['ExpType.category_id IN'] =  [EXP_CAT_MO_BIKE, EXP_CAT_MO_VEHICLE];
		}
		
		if (!empty($this->request->query['asset_id'])){
            $conditions['Expense.asset_id'] = $this->request->query['asset_id'];
        }
		
		if (!empty($this->request->query['et'])){
            $conditions['ExpType.id'] = $expTypeCond['ExpType.id'] = $this->request->query['et'];
        }
		

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

        if (!empty($query['et'])){
            $expTypeCond['ExpType.id'] = $conditions['Expense.exp_type_id'] = $query['et'];
        }

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

        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);
				
				$conditions['Expense.exp_date'] = $date;
                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', date('M d, Y', $strtotime), date('M d, Y', strtotime('+6 days', $strtotime)));

                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-d', strtotime('+6 days', $strtotime));
				
				$conditions['Expense.exp_date BETWEEN ? and ?'] = array($d1, $d2);
				
                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);

                $d1 = date('Y-m-d', $strtotime);
                $d2 =  date('Y-m-t', $strtotime);
				
				$conditions['Expense.exp_date BETWEEN ? and ?'] = array($d1, $d2);
                break;

            case 'between':
                if(empty($query['from'])){
					$d1 = date('Y-m-d');
				}else{
					$d1 =  $query['from'];
				}
				if(empty($query['to'])){
					$d2 = date('Y-m-d');
				}else{
					$d2 =  $query['to'];
				}

                $strtotime = strtotime($d1);
                $prev = ''; //date('Y-m-d', strtotime('-1 week', $strtotime) );
                $next = ''; // date('Y-m-d', strtotime('+1 week', $strtotime) );
				$current = __('From %s to %s', date('M d, Y', $strtotime), date('M d, Y', strtotime($d2)));
				
				$conditions['Expense.exp_date BETWEEN ? and ?'] = array($d1, $d2);

                break;
        }
        //pr($expCond);
		$query['d1'] = $d1; 
		$query['d2'] = $d2;
		
		
		$this->Expense->virtualFields['total_exp_amount'] = 'SUM(Expense.exp_amount)';
		
        $fields = array('Expense.id','Expense.region_id', 'Expense.depot_id', 'Expense.asset_id', 'Expense.user_id', 'Expense.exp_type_id', 'total_exp_amount', 'User.first_name', 'User.last_name', 'ExpType.name', 'ExpType.name', 'Asset.id', 'Asset.number', 'Asset.type');
        $group = array('Expense.asset_id','Expense.user_id','Expense.exp_type_id');
        $order =  array('ExpType.name'=>'asc');
		
		
			
		$this->Expense->bindModel(
			array('belongsTo' => array(
					'Asset' => array(
						'className' => 'Asset',						
					)
				)
			)
		); 
		
		/* $this->Expense->Behaviors->load('Containable');
		
		$contain = array(
				'ExpType',
				'Asset',
				'User'
			); */
//pr($conditions); die();
        $expensData = $this->Expense->find('all',array( 'fields'=>$fields,'conditions' =>$conditions,'order'=>$order, 'group'=>$group)); //pr($expensData ); die();
		
		$expenses = array();
		if(!empty($expensData)){
			foreach($expensData as $exp){
				$expenses[$exp['Expense']['asset_id']]['e'] = $exp['Expense'];
				$expenses[$exp['Expense']['asset_id']]['Asset'] = $exp['Asset'];
				$expenses[$exp['Expense']['asset_id']]['users'][$exp['Expense']['user_id']] = $exp['User']['first_name'].' '.$exp['User']['last_name'];
				
				if(empty($expenses[$exp['Expense']['asset_id']]['data'][$exp['Expense']['exp_type_id']])){
					$expenses[$exp['Expense']['asset_id']]['data'][$exp['Expense']['exp_type_id']] = $exp['Expense']['total_exp_amount'];
				}else{
					$expenses[$exp['Expense']['asset_id']]['data'][$exp['Expense']['exp_type_id']] += $exp['Expense']['total_exp_amount'];
				}
				
			}
		}
      //pr($expenses); //die();
		

        $expTypes = $this->ExpType->find('list', array('fields'=>array('id','name'), 'conditions' => $expTypeCond, 'order'=>$order)); 
		
		$this->user = $this->Auth->user();
		$this->loadModel('User');
        $users = $this->User->usersListByRole($this->user);
		
		$this->loadModel('Asset');
		$assetCond['status'] = 1;
		$assets = $this->Asset->find('list', array('conditions'=>$assetCond, 'fields'=>array('id','number'), 'order'=>array('number'=>'asc')));

        $this->_options();


        $this->set(compact('offset','expenses','expTypes','date','type','query', 'prev', 'next', 'current', 'strtotime','opt','users', 'assets'));

        if(isset($this->request->query['export'])){
			$a_type = !empty($this->request->query['a_type']) ? $this->request->query['a_type'] : "bike_vehicle";
            if($type == 'between'){
                $fileName = $a_type . '_expense_report_'.$query['from'].'_to_'.$query['to'];
            }else{
                $fileName = $a_type . '_expense_report_'.date('F_Y', $strtotime);
            }

            $this->set('filename', $fileName);
            $this->render('asset_expense_report_xls','export_xls');
        }
		
    }
	
	public function free_issue_report(){

        $conditions = $fiCond = $inCondarray();

        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['usr'])){
            $fiCond['FreeItem.user_id'] = $inCond['Incentive.user_id'] = $this->request->query['usr'];
        }
		
		if (!empty($this->request->query['cid'])){
            $conditions['Sale.customer_id'] = $this->request->query['cid'];
        }
		
		if (!empty($this->request->query['dp'])){
            $conditions['Sale.depot_id'] = $this->request->query['dp'];
        }

        if (!empty($this->request->query['dv'])) {
            $division_id = $this->request->query['dv'];
            if (empty($this->request->query['rg']) && empty($this->request->query['dp'])) {
                $conditions[] = 'Sale.depot_id IN (SELECT id from depots WHERE 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']) && empty($this->request->query['dp'])) {
                    $conditions[] = 'Sale.depot_id IN (SELECT id from depots WHERE 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'];
            if(empty($this->request->query['dp'])) {
                $conditions[] = 'Sale.depot_id IN (SELECT id from depots WHERE region_id = ' . $this->request->query['rg'] . ')';
            }
        }else{
            $region_id = 0;
            if ($this->user['Role']['grade'] == 3){
                $region_id = $this->user['region_id'];
            }
        }
        //pr($this->user['Role']['grade']);
        if (!empty($this->request->query['dp'])){
            $conditions['Sale.depot_id'] = $this->request->query['dp'];
        }

        if ( !empty($this->request->query['brd'])){
            $conditions['Sale.brand_group_id'] = $this->request->query['brd'];
        }
        if ( !empty($this->request->query['unt'])){
            $conditions['Sale.unit'] = $this->request->query['unt'];
        }
		
		if (empty($this->request->query['from'])){
			$this->request->query['from'] = date('Y-m-01');
		}
		
        if ( !empty($this->request->query['from']) && !empty($this->request->query['to'])){
            $conditions[] = array('Sale.sale_date BETWEEN ? AND ?' => array($this->request->query['from'], $this->request->query['to']));

        }elseif (!empty($this->request->query['from'])){
            $conditions['Sale.sale_date >= '] = $this->request->query['from'];
        } elseif (!empty($this->request->query['to'])){
            $conditions['Sale.sale_date <= '] = $this->request->query['to'];
        }
        if (isset($this->request->query['sts'])&& $this->request->query['sts']!=''){
            $conditions['Sale.status'] = $this->request->query['sts'];
        }
		
		if (isset($this->request->query['via_app']) && $this->request->query['via_app']!='' ){
            $conditions['Sale.via_app'] = $this->request->query['via_app'];
        }
		
        //$order = $this->paginate['order'] = array('Sale.sale_date' => 'desc');
        $order = $this->paginate['order'] = array('Sale.id' => 'desc');
        //$this->_salesRoleConditions($conditions);
		die();
		$totals = $this->Sale->find('all', array('fields'=>array('SUM(Sale.amount) AS totalAmount', 'SUM(Sale.cal_qty) AS totalCalQty'), 'conditions'=>$conditions)); 
		
        $querystring = $this->request->query;
        $this->set(compact('querystring'));
        $offset = 0;
		if(isset($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('contain'=>$contain,'conditions' =>$conditions,'order'=>$order, 'joins' => $joins ,'limit'=>10000, 'offset'=>$offset, 'group' => $group));
		} else {
			//$this->paginate['limit'] = 10000;
			$customers = $this->paginate('Customer', $conditions);
		} 
		//pr($customers);
        $this->set(compact('customers'));

        $this->loadModel('Depot');
        $this->loadModel('BrandGroup');

//        $depots = $this->Depot->getList($region_id);
        $brands = $this->BrandGroup->find('list', array('fields'=>array('id','name')));
        $this->set(compact('offset','brands'));
        $this->_selectOptions2();
        if(isset($this->request->query['export'])){
            $this->set('filename', 'Sales');
            $this->render('export_sales','export_xls');
        } else {
            $this->render('index');
        }
    }
	
	
		//free_issues_report
	
	/* public function free_issues_report() { 
		$conditions = array();
		$fiCond = array(); // Renamed variable for FreeItem conditions
		$this->loadModel('Incentive');
		$this->loadModel('FreeItem'); // Load the FreeItem model

		// Build conditions based on the request query
		if (!empty($this->request->query['br'])) {
			$conditions['Incentive.brand_id'] = $this->request->query['br'];
			$fiCond['FreeItem.brand_id'] = $this->request->query['br'];
		}
		if (!empty($this->request->query['brg'])) {
			$conditions['Incentive.brand_group_id'] = $this->request->query['brg'];
			$fiCond['FreeItem.brand_group_id'] = $this->request->query['brg'];
		}
		if (!empty($this->request->query['ct'])) {
			$conditions['Incentive.customer_id'] = $this->request->query['ct'];
			$fiCond['FreeItem.customer_id'] = $this->request->query['ct'];
		}
		if (!empty($this->request->query['usr'])) {
			$conditions['Incentive.user_id'] = $this->request->query['usr'];
			$fiCond['FreeItem.user_id'] = $this->request->query['usr'];
		}
		if (!empty($this->request->query['dv']) || !empty($this->request->query['dp']) || !empty($this->request->query['rg'])) {
			if (!empty($this->request->query['dv']) && empty($this->request->query['rg']) && empty($this->request->query['dp'])) {
				$conditions[] = 'Incentive.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . '))';
				$fiCond[] = 'FreeItem.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . '))';
			}
			if (!empty($this->request->query['rg']) && empty($this->request->query['dp'])) {
				$conditions[] = 'Incentive.depot_id IN (SELECT id from depots WHERE region_id = ' . $this->request->query['rg'] . ')';
				$fiCond[] = 'FreeItem.depot_id IN (SELECT id from depots WHERE region_id = ' . $this->request->query['rg'] . ')';
			}
			$depot_id = "";
			if (!empty($this->request->query['dp'])) {
				$depot_id = $conditions['Incentive.depot_id'] = $this->request->query['dp'];
				$fiCond['FreeItem.depot_id'] = $this->request->query['dp'];
			}
		}

		if (!empty($this->request->query['it'])) {
			$conditions['Incentive.incentive_type'] = $this->request->query['it'];
		}

		if (empty($this->request->query['from'])) {
			$this->request->query['from'] = date('Y-m-01');
		}

		if (!empty($this->request->query['from']) && !empty($this->request->query['to'])) {
			$conditions[] = array('Incentive.incentive_date BETWEEN ? AND ?' => array($this->request->query['from'], $this->request->query['to']));
			$fiCond[] = array('FreeItem.sale_date BETWEEN ? AND ?' => array($this->request->query['from'], $this->request->query['to']));
		} elseif (!empty($this->request->query['from'])) {
			$conditions['Incentive.incentive_date >= '] = $this->request->query['from'];
			$fiCond['FreeItem.sale_date >= '] = $this->request->query['from'];
		} elseif (!empty($this->request->query['to'])) {
			$conditions['Incentive.incentive_date <= '] = $this->request->query['to'];
			$fiCond['FreeItem.sale_date <= '] = $this->request->query['to'];
		}

		if (isset($this->request->query['sts']) && $this->request->query['sts'] != '') {
			$conditions['Incentive.status'] = $this->request->query['sts'];
			$fiCond['FreeItem.status'] = $this->request->query['sts'];
		}

		if (isset($this->request->query['r']) && $this->request->query['r'] != '') {
			$conditions['User.role_id'] = $this->request->query['r'];
		}

		if (isset($this->request->query['via_app']) && $this->request->query['via_app'] != '') {
			$conditions['Incentive.via_app'] = $this->request->query['via_app'];
			$fiCond['FreeItem.via_app'] = $this->request->query['via_app'];
		}

		// Not On Day Route 
		if (isset($this->request->query['not_on_day_route']) && $this->request->query['not_on_day_route'] == 1) {
			$conditions['Incentive.not_on_day_route'] = $this->request->query['not_on_day_route'];
		}

		$this->Incentive->_incentiveConditions($conditions, $this->user);
		
		// Query for incentives
		$incentives = $this->Incentive->find('all', array(
			'conditions' => $conditions,
			'order' => array('Incentive.incentive_date' => 'desc'),
		));

		// Fetch active free items with the same conditions
		$freeItems = $this->FreeItem->find('all', array(
			'conditions' => $fiCond,
		));

		// Combine free items into incentives
		foreach ($freeItems as $item) {
			$incentives[] = array(
				'Incentive' => array(
					'id' => $item['FreeItem']['id'],
					'brand_group_id' => $item['FreeItem']['brand_group_id'],
					'brand_id' => $item['FreeItem']['brand_id'],
					'user_id' => $item['FreeItem']['user_id'],
					'customer_id' => $item['FreeItem']['customer_id'],
					'depot_id' => $item['FreeItem']['depot_id'],
					'incentive_date' => $item['FreeItem']['sale_date'],
					'incentive_type' => 'Sale Free Item',
					'unit' => $item['FreeItem']['unit'],
					'cal_qty' => $item['FreeItem']['cal_qty'],
					'quantity' => $item['FreeItem']['quantity'],
					'status' => $item['FreeItem']['status'],
					'created' => $item['FreeItem']['created'],
					'modified' => $item['FreeItem']['modified'],
				)
			);
		}

		// Sort combined results by incentive_date in descending order
		usort($incentives, function($a, $b) {
			return strtotime($b['Incentive']['incentive_date']) - strtotime($a['Incentive']['incentive_date']);
		});

		// Set data for the view
		$querystring = $this->request->query;
		$this->set(compact('querystring', 'incentives'));
		
		pr($incentives); die();

		// Existing code for user roles and exporting
		$this->loadModel('Role');
		$this->user = $this->Auth->user();
		$roles = $this->Role->getList($this->user);
		
		$this->loadModel('User');
		$users = $this->User->usersList();
		
		$this->set(compact('roles', 'users'));

		if (isset($this->request->query['export'])) {
			$this->set('filename', 'Incentive');
			$this->render('export_incentive', 'export_xls');
		}
	}	
	 */
	 
/* 	public function free_issues_report() { 
    $conditions = $fiCond = [];

    $this->loadModel('Incentive');
    $this->loadModel('FreeItem');

    // Build conditions based on the request query
    if (!empty($this->request->query['br'])) {
        $conditions['Incentive.brand_id'] = $this->request->query['br'];
        $fiCond['FreeItem.brand_id'] = $this->request->query['br'];
    }
    if (!empty($this->request->query['brg'])) {
        $conditions['Incentive.brand_group_id'] = $this->request->query['brg'];
        $fiCond['FreeItem.brand_group_id'] = $this->request->query['brg'];
    }
    if (!empty($this->request->query['ct'])) {
        $conditions['Incentive.customer_id'] = $this->request->query['ct'];
        $fiCond['FreeItem.customer_id'] = $this->request->query['ct'];
    }
    if (!empty($this->request->query['usr'])) {
        $conditions['Incentive.user_id'] = $this->request->query['usr'];
        $fiCond['FreeItem.user_id'] = $this->request->query['usr'];
    }
    if (!empty($this->request->query['dv']) || !empty($this->request->query['dp']) || !empty($this->request->query['rg'])) {
        if (!empty($this->request->query['dv']) && empty($this->request->query['rg']) && empty($this->request->query['dp'])) {
            $conditions[] = 'Incentive.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . '))';
            $fiCond[] = 'FreeItem.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $this->request->query['dv'] . '))';
        }
        if (!empty($this->request->query['rg']) && empty($this->request->query['dp'])) {
            $conditions[] = 'Incentive.depot_id IN (SELECT id from depots WHERE region_id = ' . $this->request->query['rg'] . ')';
            $fiCond[] = 'FreeItem.depot_id IN (SELECT id from depots WHERE region_id = ' . $this->request->query['rg'] . ')';
        }
        if (!empty($this->request->query['dp'])) {
            $conditions['Incentive.depot_id'] = $this->request->query['dp'];
            $fiCond['FreeItem.depot_id'] = $this->request->query['dp'];
        }
    }

    if (!empty($this->request->query['it'])) {
        $conditions['Incentive.incentive_type'] = $this->request->query['it'];
    }

    if (empty($this->request->query['from'])) {
        $this->request->query['from'] = date('Y-m-01');
    }

    // Date filters
    if (!empty($this->request->query['from'])) {
        $conditions['Incentive.incentive_date >= '] = $this->request->query['from'];
        $fiCond['FreeItem.sale_date >= '] = $this->request->query['from'];
    }
    
    if (!empty($this->request->query['to'])) {
        $conditions['Incentive.incentive_date <= '] = $this->request->query['to'];
        $fiCond['FreeItem.sale_date <= '] = $this->request->query['to'];
    }

    if (isset($this->request->query['sts']) && $this->request->query['sts'] != '') {
        $conditions['Incentive.status'] = $this->request->query['sts'];
        $fiCond['FreeItem.status'] = $this->request->query['sts'];
    }

    if (isset($this->request->query['r']) && $this->request->query['r'] != '') {
        $conditions['User.role_id'] = $this->request->query['r'];
    }

    if (isset($this->request->query['via_app']) && $this->request->query['via_app'] != '') {
        $conditions['Incentive.via_app'] = $this->request->query['via_app'];
        $fiCond['FreeItem.via_app'] = $this->request->query['via_app'];
    }

    // Not On Day Route 
    if (isset($this->request->query['not_on_day_route']) && $this->request->query['not_on_day_route'] == 1) {
        $conditions['Incentive.not_on_day_route'] = $this->request->query['not_on_day_route'];
    }
	
	//$this->Incentive->_incentiveConditions($conditions, $this->user);
    
	$order = $this->paginate['order'] = array('Incentive.created'=>'desc','Incentive.incentive_date'=>'desc');
	
	$joins = $this->paginate['joins'] = array(
				[
					'table' => 'free_items',
					'alias' => 'FreeItem',
					'type' => 'LEFT',
					'conditions' => [
						'FreeItem.sale_date >= ?' => $this->request->query['from'],
					],
				],
			);

    if(isset($this->request->query['export'])){
		if(!empty($this->request->query['offset'])){
			$offset = (int) $this->request->query['offset'];
			if($offset > 10000){
				$offset = 0;
			}
		}else{
			$offset = 0;
		}
		$incentives = $this->Incentive->find('all',array('conditions' =>$conditions,'order'=>$order, 'joins' => $joins ,'limit'=>10000, 'offset'=>$offset));
	} else {
		$incentives = $this->paginate('Incentive', $conditions);
	}
	
	pr($incentives); die();

    // Set data for the view
    $querystring = $this->request->query;
    $this->set(compact('querystring', 'incentives'));

    // Existing code for user roles and exporting
    $this->loadModel('Role');
    $this->user = $this->Auth->user();
    $roles = $this->Role->getList($this->user);

    $this->loadModel('User');
    $users = $this->User->usersList();

    $this->set(compact('roles', 'users'));

    if (isset($this->request->query['export'])) {
        $this->set('filename', 'Incentive');
        $this->render('export_incentive', 'export_xls');
    }
} */


	public function free_issues_report() {
		$this->loadModel('Incentive');
		$this->loadModel('FreeItem');

		$conditions = array();
		$fCond = array();
		$brCond['Brand.status'] = ACTIVE;
		
		// Bind related models to FreeItem
		$this->FreeItem->bindModel([
			'belongsTo' => [
				'User' => [
					'className' => 'User',
					'foreignKey' => 'user_id',
					'fields' => array('first_name', 'last_name'),
				],
				'Brand' => [
					'className' => 'Brand',
					'foreignKey' => 'brand_id',
				],
				'Depot' => [
					'className' => 'Depot',
					'foreignKey' => 'depot_id',
					'fields' => array('id', 'title','region_id'),
				],
				'Customer' => [
					'className' => 'Customer',
					'foreignKey' => 'customer_id',
					'fields' => array('id', 'first_name','last_name'),
				],
			]
		], false);

		// Common filters for both Incentive and FreeItem
		
		if (!empty($this->request->query['brg'])) {
			$brCond['Brand.brand_group_id'] = $conditions['Incentive.brand_group_id'] = $this->request->query['brg'];
			$fCond['FreeItem.brand_group_id'] = $this->request->query['brg'];
		}
		if (!empty($this->request->query['br'])) {
			$conditions['Incentive.brand_id'] = $this->request->query['br'];
			$fCond['FreeItem.brand_id'] = $this->request->query['br'];			
		}
		if (!empty($this->request->query['ct'])) {
			$conditions['Incentive.customer_id'] = $this->request->query['ct'];
			$fCond['FreeItem.customer_id'] = $this->request->query['ct'];
		}
		if (!empty($this->request->query['usr'])) {
			$conditions['Incentive.user_id'] = $this->request->query['usr'];
			$fCond['FreeItem.user_id'] = $this->request->query['usr'];
		}

		// Depot and Region filters
		if (!empty($this->request->query['dv'])) {
			$conditions[] = 'Incentive.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id FROM divisions_regions WHERE division_id = ' . $this->request->query['dv'] . '))';
			$fCond[] = 'FreeItem.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id FROM divisions_regions WHERE division_id = ' . $this->request->query['dv'] . '))';
		}
		if (!empty($this->request->query['rg'])) {
			$conditions[] = 'Incentive.depot_id IN (SELECT id FROM depots WHERE region_id = ' . $this->request->query['rg'] . ')';
			$fCond[] = 'FreeItem.depot_id IN (SELECT id FROM depots WHERE region_id = ' . $this->request->query['rg'] . ')';
		}
		if (!empty($this->request->query['dp'])) {
			$conditions['Incentive.depot_id'] = $this->request->query['dp'];
			$fCond['FreeItem.depot_id'] = $this->request->query['dp'];
		}

		// Date range filter
		if (empty($this->request->query['from'])) {
			$this->request->query['from'] = date('Y-m-01');
		}
		if (!empty($this->request->query['from']) && !empty($this->request->query['to'])) {
			$conditions[] = array('Incentive.incentive_date BETWEEN ? AND ?' => array($this->request->query['from'], $this->request->query['to']));
			$fCond[] = array('FreeItem.sale_date BETWEEN ? AND ?' => array($this->request->query['from'], $this->request->query['to']));
		} elseif (!empty($this->request->query['from'])) {
			$conditions['Incentive.incentive_date >= '] = $this->request->query['from'];
			$fCond['FreeItem.sale_date >= '] = $this->request->query['from'];
		} elseif (!empty($this->request->query['to'])) {
			$conditions['Incentive.incentive_date <= '] = $this->request->query['to'];
			$fCond['FreeItem.sale_date <= '] = $this->request->query['to'];
		}

		// Other filters for both tables
		if (isset($this->request->query['sts']) && $this->request->query['sts'] != '') {
			$conditions['Incentive.status'] = $this->request->query['sts'];
			$fCond['FreeItem.status'] = $this->request->query['sts'];
		}
		if (isset($this->request->query['r']) && $this->request->query['r'] != '') {
			$conditions['User.role_id'] = $this->request->query['r'];
			$fCond['User.role_id'] = $this->request->query['r'];
		}
		if (isset($this->request->query['via_app']) && $this->request->query['via_app'] != '') {
			$conditions['Incentive.via_app'] = $this->request->query['via_app'];
			$fCond['FreeItem.via_app'] = $this->request->query['via_app'];
		}

		// Incentive Type filter handling
		$incentiveType = !empty($this->request->query['it']) ? $this->request->query['it'] : null;
		
		// If filtering for Sale Free Item
		if ($incentiveType === 'Sale Free Item') {
			$freeItems = $this->FreeItem->find('all', [
				'conditions' => $fCond,
				//'fields' => ['FreeItem.*', "'Sale Free Item' AS incentive_type"],
			]);
			$freeIssues = $this->_prepareIncentiveFreeItems($freeItems);
		} else {
			// Prepare conditions for incentives
			if ($incentiveType) {
				$conditions['Incentive.incentive_type'] = $incentiveType;
			}

			// Fetch incentives
			$incentives = $this->Incentive->find('all', [
				'conditions' => $conditions,
			]);
			$freeIssues = $incentives;

			// Fetch free items if no specific incentive type is given
			if (!$incentiveType) {			
				$freeItems = $this->FreeItem->find('all', [
					'conditions' => $fCond,
					//'fields' => ['*', "'Sale Free Item' AS incentive_type"],
				]);
				$freeIssues = array_merge($incentives, $this->_prepareIncentiveFreeItems($freeItems));
			}
		}
		
		// Calculate the total quantity in cases, regardless of pagination
		$totalQtyCases = 0;
		foreach ($freeIssues as $issue) {
			$quantity = isset($issue['Incentive']['cal_qty']) ? $issue['Incentive']['cal_qty'] : 0;
			$totalQtyCases += $quantity;
		}

		// Set the total quantity for use in the view
		$this->set('totalQtyCases', $totalQtyCases);

		// Sort results by date in descending order
		usort($freeIssues, function($a, $b) {
			$dateA = isset($a['Incentive']['incentive_date']) ? $a['Incentive']['incentive_date'] : (isset($a['Incentive']['sale_date']) ? $a['Incentive']['sale_date'] : '1970-01-01');
			$dateB = isset($b['Incentive']['incentive_date']) ? $b['Incentive']['incentive_date'] : (isset($b['Incentive']['sale_date']) ? $b['Incentive']['sale_date'] : '1970-01-01');
			
			return strtotime($dateB) - strtotime($dateA); // Descending order
		});
		
		// Apply sorting here based on query parameter or default to the incentive_date
		/* $sortField = !empty($this->request->query['sort']) ? $this->request->query['sort'] : 'Incentive.incentive_date';
		$sortDirection = !empty($this->request->query['direction']) ? $this->request->query['direction'] : 'desc';

		usort($freeIssues, function ($a, $b) use ($sortField, $sortDirection) {
			// Extract the field values for comparison
			$fieldA = isset($a['Incentive'][$sortField]) ? $a['Incentive'][$sortField] : null;
			$fieldB = isset($b['Incentive'][$sortField]) ? $b['Incentive'][$sortField] : null;

			// If the field is not set, default to zero for comparison
			if (is_null($fieldA)) $fieldA = 0;
			if (is_null($fieldB)) $fieldB = 0;

			// Compare fields based on the direction (ascending or descending)
			if ($sortDirection === 'asc') {
				// Ascending order
				if ($fieldA == $fieldB) {
					return 0;
				} else {
					return ($fieldA < $fieldB) ? -1 : 1;
				}
			} else {
				// Descending order
				if ($fieldA == $fieldB) {
					return 0;
				} else {
					return ($fieldA > $fieldB) ? -1 : 1;
				}
			}
		}); */

		
		$querystring = $this->request->query;
		
		$this->_options();
		
		$this->loadModel('Customer');
		$this->loadModel('Brand');
		$this->loadModel('BrandGroup');
		
		
		$customers = $this->Customer->customerListByRole($this->user);
		
		$brandList = $this->Brand->find('list', array('fields'=>array('id', 'name'), 'conditions' => $brCond, 'order'=>array('name'=>'asc')));
		
		$groups = $this->BrandGroup->find('list', array('fields'=>array('id', 'name'), 'order'=>array('name'=>'asc')));
		
		$incentive_type = incentive_type();
		$incentive_type['Sale Free Item'] = 'Sale Free Item';
		
		asort($incentive_type);
		
		
		$this->loadModel('Role');
		$this->user = $this->Auth->user();
		$roles = $this->Role->getList($this->user);  //pr($roles);
		
		$this->loadModel('User');
		$users = $this->User->usersList();
		
		// Set additional variables
		$this->set(compact('querystring', 'customers','groups', 'brandList','incentive_type', 'roles', 'users'));

		// Pagination setup if not exporting
		if (!isset($this->request->query['export'])) {
			$page = isset($this->request->query['page']) ? (int)$this->request->query['page'] : 1;
			$limit = PAGE_LIMIT; // Number of items per page
			$offset = ($page - 1) * $limit;

			// Slice the results for pagination
			$total = count($freeIssues); // Total items for pagination
			$freeIssues = array_slice($freeIssues, $offset, $limit);			
			$pageCount = ceil($total / $limit); // Calculate the total number of pages
			$this->set('pagination', array('total' => $total, 'page' => $page, 'limit' => $limit, 'pageCount' => $pageCount));
		}else{
			$limit = EXPORT_LIMIT; 
			if(!empty($this->request->query['offset'])){
				$offset = (int) $this->request->query['offset'];
			}else{
				$offset = 0;
			}
			$freeIssues = array_slice($freeIssues, $offset, $limit);	
			$this->set(compact('offset'));
		}
	//pr($freeIssues); die();
		$this->set('freeIssues', $freeIssues);
		
		if(isset($this->request->query['export'])){
			$this->set('filename', 'Free Issues Report');
            $this->render('free_issues_report_xls','export_xls');
        }
		
	}


	function _prepareIncentiveFreeItems($freeItems) {
		$preparedItems = [];
		foreach ($freeItems as $item) {
			$preparedItems[] = [
				'Incentive' => array_merge($item['FreeItem'], ['incentive_type' => 'Sale Free Item', 'incentive_date' => $item['FreeItem']['sale_date']]),
				'User' => $item['User'],
				'Brand' => $item['Brand'],
				'Depot' => $item['Depot'],
				'Customer' => $item['Customer'],
			];
		}
		return $preparedItems;
	}
	
	
	
	

    function _options(){
        $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);
    }
	
	function _selectOptions2(){
        $Opdata = isset($this->request->data['Report'] ) ? $this->request->data['Report']  : array();
        $param = !empty($this->request->query ) ? $this->request->query  : array();
        $this->Option->Options2($Opdata, $this->user,$param);
    }
}
