<?php
App::uses('Model', 'Model');

/**
 * Application model for Cake.
 *
 * Add your application-wide methods in the class below, your models
 * will inherit them.
 *
 * @package       app.Model
 */

class Depot extends AppModel {
	
	public $belongsTo  = array(
						'State' => array('className' => 'State', 'foreignKey' => 'state_id'), 
						'Region' => array('className' => 'Region', 'foreignKey' => 'region_id')
					);
	
	public function getlist($region_id = 0){
		$conditions = array();
		if ($region_id){
			$conditions = array('region_id'=>$region_id);
		}
		
		return $this->find('list', array( 'fields'=>array('id', 'title'),'conditions'=>$conditions, 'order'=>array('title'=>'asc')));
	}
	
	public function getlistbyrIds($rIds = NULL){
        $conditions = array();
        if (!empty($rIds)){
            $conditions = array('region_id IN'=>$rIds);
        }
		//pr($conditions);
        return $this->find('list', array( 'fields'=>array('id', 'title'),'conditions'=>$conditions, 'order'=>array('title'=>'asc')));
    }
/**
 * depotReportCal : Get the sales/stock data for given date
 * @params: $depot_id (int), $date
 * return : @mixed array
 */

	public function depotReportCal($depot_id, $params){
		$reportArr = array();
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$pC = "payment_date = '$date'";
				$eC = "exp_date = '$date'";
				$bC = "budget_date = '".$date."'";
				$bTC = "budget_date < '".$date."'";
				$sC = "sale_date = '$date'";
				$refDate = $date;
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				$pC = "payment_date between '$d1' AND '$d2'";
				$eC = "exp_date between '$d1' AND '$d2'";
				$bC = "budget_date between '$d1' AND '$d2'";
				$bTC = "budget_date < '$d1'";
				$sC = "sale_date between '$d1' AND '$d2'";
				$refDate = $d1;
				
			break;
			case 'monthly':
				$d = date('Y-m', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$pC = "payment_date LIKE '$d%'";
				$eC = "exp_date LIKE '$d%'";
				$w1 = date('W', strtotime($d.'-01' ));
				$w2 = date('W', strtotime($d2 ));
				
				$bC = "budget_date between '$d-01' AND '$d2'";
				$bTC = "budget_date < '$d-01'";
				
				$sC = "sale_date LIKE '$d%'";
				
				$refDate = $d.'-01';
				
				
			break;
			
			case 'between':				
				if(empty( $params['query']['from'])){
					$d1 = date('Y-m-d');
				}else{
					$d1 =  $params['query']['from'];
				}	
				if(empty( $params['query']['to'])){
					$d2 = date('Y-m-d');
				}else{
					$d2 =  $params['query']['to'];
				}	
				
				$pC = "payment_date between '$d1' AND '$d2'";
				$eC = "exp_date between '$d1' AND '$d2'";
				$bC = "budget_date between '$d1' AND '$d2'";
				$bTC = "budget_date < '$d1'";
				$sC = "sale_date between '$d1' AND '$d2'";
				
				$refDate = $d1;
			break;
		}
		
		
		
		//Total Payments till date
		$paymentsTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(amount) AS paymentTilDate FROM payments AS Payment WHERE payment_date >= '".OPENING_DATE."' AND payment_date < '$refDate' AND depot_id = $depot_id AND user_id != 0 GROUP BY brand_group_id" ); //pr($paymentsTilDate);
		foreach ($paymentsTilDate  as $payment){
		
			$bid = $payment['Payment']['brand_group_id'];
			$reportArr[$bid]['paymentTilDate'] = $payment['0']['paymentTilDate'];
		}
		
		//Get payments
		/* $payments = $this->query("SELECT depot_id, brand_id, SUM(amount) AS payment FROM payments AS Payment WHERE $pC AND depot_id = $depot_id GROUP BY brand_id" ); */
		$payments = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(amount) AS payment FROM payments AS Payment WHERE $pC AND depot_id = $depot_id AND user_id != 0 GROUP BY brand_group_id" ); //pr($payments);
		
		foreach ($payments  as $payment){		
			$bid = $payment['Payment']['brand_group_id'];
			$reportArr[$bid]['payment'] = $payment['0']['payment'];
		}
		
		//Total Expenses till date
		$expencesTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(exp_amount) AS expTilDate FROM expenses AS Expense WHERE exp_date >= '".OPENING_DATE."' AND exp_date < '$refDate' AND depot_id = $depot_id AND user_id != 0 GROUP BY brand_group_id" );
		
		foreach ($expencesTilDate  as $expence){		
			$bid = $expence['Expense']['brand_group_id'];
			$reportArr[$bid]['expTilDate'] = $expence['0']['expTilDate'];
		}
		
		
		//Get expenses
		/* $expences = $this->query("SELECT depot_id, brand_id, SUM(exp_amount) AS exp FROM expenses AS Expense WHERE $eC AND depot_id = $depot_id GROUP BY brand_id" ); */
		$expences = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(exp_amount) AS exp FROM expenses AS Expense WHERE $eC AND depot_id = $depot_id AND user_id != 0 GROUP BY brand_group_id" );
		
		foreach ($expences  as $expence){
		
			$bid = $expence['Expense']['brand_group_id'];
			$reportArr[$bid]['expence'] = $expence['0']['exp'];
		}
		//Get Budget till date
		/* $budgetTilldate = $this->query("SELECT depot_id, brand_id, SUM(amount) AS budgetTillDate FROM budgets AS Budget WHERE depot_id = $depot_id AND $bTC GROUP BY brand_id" ); */
		$budgetTilldate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(amount) AS budgetTillDate FROM budgets AS Budget WHERE depot_id = $depot_id AND budget_date >= '".OPENING_DATE."' AND $bTC GROUP BY brand_group_id" );
		
		foreach ($budgetTilldate  as $budget){
		
			$bid = $budget['Budget']['brand_group_id'];
			$reportArr[$bid]['budgetTillDate'] = $budget['0']['budgetTillDate'];
		}
		//Get budget
		$budgets = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(amount) AS budget FROM budgets AS Budget WHERE depot_id = $depot_id AND $bC GROUP BY brand_group_id" );
		
		foreach ($budgets  as $budget){
		
			$bid = $budget['Budget']['brand_group_id'];
			$reportArr[$bid]['budget'] = $budget['0']['budget'];
		}
		
		
		//Total sales unit till date
		$salesTillDate = $this->query("SELECT depot_id, brand_id, brand_group_id, unit, SUM(cal_qty) AS qtyTillDate, SUM(amount) AS amtTillDate FROM sales AS Sale WHERE sale_date >= '".OPENING_DATE."' AND sale_date < '$refDate' AND depot_id = $depot_id AND user_id != 0 AND customer_id != 0 GROUP BY brand_group_id, unit" );
		foreach ($salesTillDate  as $sale){
		
			$bid = $sale['Sale']['brand_group_id'];
			$unit = $sale['Sale']['unit'];
			
			$reportArr[$bid]['saleTillDate'][$unit] = $sale['0']['qtyTillDate'];
			$reportArr[$bid]['saleValueTillDate'][$unit] = $sale['0']['amtTillDate'];
		}
		
		
		//Get sales unit wise
		$sales = $this->query("SELECT depot_id, brand_id, brand_group_id, unit, SUM(cal_qty) AS qty, SUM(amount) AS amt FROM sales AS Sale WHERE $sC AND depot_id = $depot_id AND user_id != 0 AND customer_id != 0 GROUP BY brand_group_id, unit" );
	//pr($sales);
		foreach ($sales  as $sale){
		
			$bid = $sale['Sale']['brand_group_id'];
			$unit = $sale['Sale']['unit'];
			
			$reportArr[$bid]['sale'][$unit] = $sale['0']['qty'];
			$reportArr[$bid]['saleValue'][$unit] = $sale['0']['amt'];
		}
		
		
		
		//Total consumersales unit till date
		$conSalesTillDate = $this->query("SELECT depot_id, brand_id, brand_group_id, unit, SUM(cal_qty) AS qtyTillDate, SUM(amount) AS amtTillDate FROM consumer_sales AS Sale WHERE sale_date >= '".OPENING_DATE."' AND sale_date < '$refDate' AND depot_id = $depot_id AND user_id != 0 AND consumer_id != 0 GROUP BY brand_group_id, unit" );
		foreach ($conSalesTillDate  as $sale){
		
			$bid = $sale['Sale']['brand_group_id'];
			$unit = $sale['Sale']['unit'];
			
			$reportArr[$bid]['conSaleTillDate'][$unit] = $sale['0']['qtyTillDate'];
			$reportArr[$bid]['conSaleValueTillDate'][$unit] = $sale['0']['amtTillDate'];
		}
		
		
		//Get consumersales unit wise
		$conSales = $this->query("SELECT depot_id, brand_id, brand_group_id, unit, SUM(cal_qty) AS qty, SUM(amount) AS amt FROM consumer_sales AS Sale WHERE $sC AND depot_id = $depot_id AND user_id != 0 AND consumer_id != 0 GROUP BY brand_group_id, unit" );
	//pr($sales);
		foreach ($conSales  as $sale){
		
			$bid = $sale['Sale']['brand_group_id'];
			$unit = $sale['Sale']['unit'];
			
			$reportArr[$bid]['conSale'][$unit] = $sale['0']['qty'];
			$reportArr[$bid]['conSaleValue'][$unit] = $sale['0']['amt'];
		}
		
		
		
		
		//pr($reportArr;);
		return $reportArr;
		
	}
	
	function getDepotListByRole($user, $query){
		$conditions = array();
		switch($user['Role']['grade']){
			case 3:
				$conditions['Depot.region_id'] = $user['region_id'];
			break;
			case 4:
			case 5:
			case 6:				
				$conditions['Depot.id'] = $user['depot_id'];
			break;
		}
		
		if (!empty($query['dv'])){
			$divisionRegion=  ClassRegistry::init('DivisionsRegion');
            $rIds = $divisionRegion->find('list', array('fields'=>array('region_id'),'conditions'=>array('division_id'=>$query['dv'])));
			$conditions['Depot.region_id IN'] =  $rIds;
		}
		
		if (!empty($query['rg'])){
			$conditions['Depot.region_id'] = $query['rg'];
		}
		
		if (!empty($query['dp'])){
			$conditions['Depot.id'] = $query['dp'];
		}
		
		$depots = $this->find('list', array('fields'=>array('id','title'), 'conditions' => $conditions,'order'=>array('title'=>'asc')));
		
		return $depots;
	}
}