<?php
/**
 * Controller name
 *
 * @var string
 */
App::uses('AppController', 'Controller');
class ReportsController extends AppController {
    
	
	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['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')));
		$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;
		}
		$depoList = $this->Depot->getlist();
		if ($depots){
		$this->Stock->depotStock($depots, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
		}
		$this->set(compact('offset','depots','depoList', 'brands','type','opt', 'date', 'prev', 'next', 'current'));
		//pr($depots);
		if(isset($this->request->query['export'])){	
			$this->set('filename', 'depotStock_'.$date);		
			$this->render('export_stocks','export_xls');
		}
				
	}
	
	public function warehouseStock($type='', $opt=''){
		$this->loadModel('Warehouse');
		$this->loadModel('Stock');
		$this->loadModel('Brand');
		$conditions = array();
		$query = $this->request->query;
		if ( !empty($this->request->query['wh'])){
			$conditions['Warehouse.id'] = $this->request->query['wh'];
		}
		$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')));
		$params = array();
		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;
		}
		$region_id = 0;
		if ($this->user['Role']['grade'] == 4){
			$region_id = $this->user['region_id'];
		}
		$depoList = $this->Warehouse->getlist($region_id);
		if ($warehouses){
		$this->Stock->warehouseStock($warehouses, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
		}
		$this->set(compact('offset','warehouses','depoList', 'brands','type','opt', 'date', 'prev', 'next', 'current'));

		if(isset($this->request->query['export'])){	
			$this->set('filename', 'warehouseStock_'.$date);			
			$this->render('export_stockswarehouse','export_xls');
		}
	}
	
	
	function _roleConditions(&$conditions, $model){
		switch($this->user['Role']['grade']){
			case 3:
				$conditions[$model.'.region_id'] = $this->user['region_id'];
			break;
			case 4:
				$conditions[$model.'.id'] = $this->user['depot_id'];
			break;
		}
	}
	
	public function depotActivity($type='daily', $opt=''){
		
		if (!empty($this->request->query['dp'])){
		//pr($this->request->query);
		  $depot_id = $this->request->query['dp'];
		}elseif(!empty($this->user['depot_id'])){
			$depot_id = $this->user['depot_id'];
		}
		$this->loadModel('Depot');
		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){
			$depotList = $this->Depot->getlist($this->user['region_id']);
			
		}else{
			$depotList = $this->Depot->getlist();
		}
		

		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'];
		}
		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;
		}

		$depotCal = $this->Depot->depotReportCal($depot_id, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
		pr($depotCal);
		if(!empty($depots)){
		
			$this->Stock->depotStock($depots, compact('type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query'));
		}
		$this->set(compact('depots','depoList', 'brands', 'depotCal', 'brandAll', 'type','opt', 'date', 'prev', 'next', 'current', 'strtotime','query','depotList'));
		
		$this->set('depots', $depots);
		$querystring = $this->request->query;
		$this->set(compact('querystring'));
		if(isset($this->request->query['export'])){	
			$this->set('filename', 'DepotActivity_'.$date);			
			$this->render('export_depotactivity','export_xls');
		}
	}
	
	public function depotsales($type='daily', $opt=''){
		
	//	$this->gradPermission(4);
		$this->loadModel('Depot');
		$conditions = array();
		$incCond = $fiCond = $saleCond = '';
		$query = $this->request->query;
		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));
				
				$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;
		}
		
		$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((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 ($this->user['Role']['grade'] == 3){
			$conditions['Depot.region_id'] = $this->user['region_id'];
		}
		$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);
	    }
		
		$this->set(compact('offset','depots','date','type','query', 'prev', 'next', 'current', 'strtotime'));
		if(isset($this->request->query['export'])){		
			$this->set('filename', 'DepoSales_'.$date);	
			$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['dp'])){
			$conditions['Payment.depot_id'] = $this->request->query['dp'];
		}	
		if ( !empty($this->request->query['bn'])){
			$conditions['Payment.bank_id'] = $this->request->query['bn'];
		}
		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;
		}
		
		$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');
		$depoList = $this->Depot->getlist();
		$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','depoList', 'total', 'banks', 'date','type','query', 'prev', 'next', 'current', 'strtotime'));
	}
	
	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['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();
		$this->set(compact('offset','depot','type','opt', 'date', 'prev', 'next', 'current','reimExp','ulists'));

		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;
		}
		$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('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['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));				
		}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')));
		
		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);
		
		$depoList = $this->Depot->getList();
		$this->set(compact('brands','depots', 'depoList', 'd1', 'd2', 'reportData'));
		if(isset($this->request->query['export'])){   	
			$this->set('filename', 'summary_reports');
			$this->render('summary_reports_xls','export_xls');
		}
	}
}
