<?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 Stock extends AppModel {
	
	public $belongsTo = array(
		'User' => array('className' => 'User', 'foreignKey' => 'user_id', 'fields' => array('first_name', 'last_name'), 'type'=>'left'), 
		'Brand' => array('className' => 'Brand', 'foreignKey' => 'brand_id', 'type'=>'left'), 
		'BrandGroup' => array('className' => 'BrandGroup', 'foreignKey' => 'brand_group_id', 'type'=>'left'), 
		'Warehouse1' => array('className' => 'Warehouse', 'foreignKey' => 'source_id', 'fields' => array('id', 'title','region_id'),'conditions'=>array('Stock.source_entity'=>'Warehouse'), 'type'=>'left'),
		'Depot1' => array('className' => 'Depot', 'foreignKey' => 'source_id', 'conditions' => array('Stock.source_entity'=>'Depot'), 'fields' => array('id', 'title','region_id'), 'type'=>'left'),
		'Distributor1' => array('className' => 'Distributor', 'foreignKey' => 'source_id', 'conditions' => array('Stock.source_entity'=>'Distributor'), 'fields' => array('id', 'title'), 'type'=>'left'),
		'Warehouse2' => array('className' => 'Warehouse', 'foreignKey' => 'destination_id', 'fields' => array('id', 'title', 'region_id'),'conditions'=>array('Stock.destination_entity'=>'Warehouse'), 'type'=>'left'),
		'Depot2' => array('className' => 'Depot', 'foreignKey' => 'destination_id', 'conditions' => array('Stock.destination_entity'=>'Depot'), 'fields' => array('id', 'title', 'region_id'), 'type'=>'left'),
		'Distributor2' => array('className' => 'Distributor', 'foreignKey' => 'destination_id', 'conditions' => array('Stock.destination_entity'=>'Distributor'), 'fields' => array('id', 'title'), 'type'=>'left')
	);
	/**
	 * updateStockStatus method
	 * Update current stock status
	 */ 
	/* public function updateStockStatus($params){
		$brand_id = $params['brand_id'];
		$qty = $params['quantity'];
		if (isset($params['old_qty'])){
			$qty = $qty - $params['old_qty'];
		}
		$column = array('Case'=>'case', 'Roll'=>'roll', 'Packet'=>'pack');
		//$qtyColummn = $column[$params['pack_type']];
		$qtyColummn = 'case';
		//Increase destination stock
		if( isset($params['destination_entity']) && isset($params['destination_id']) ){
			
			$addRow = $this->query("SELECT id FROM current_stocks AS CurrentStock WHERE entity='".$params['destination_entity']."' AND entity_id = ".$params['destination_id']." AND brand_id = $brand_id ");
			
			if ($addRow){
				$this->query("UPDATE current_stocks SET `$qtyColummn` = `$qtyColummn` + $qty WHERE id = ".$addRow['0']['CurrentStock']['id']);
			}else{
				$this->query("INSERT INTO current_stocks (entity_id, entity, brand_id, `$qtyColummn`) VALUES (".$params['destination_id'].", '".$params['destination_entity']."', $brand_id, $qty)");
			}
		}
		
		//Decrease destination stock
		if( isset($params['source_entity']) && isset($params['source_id']) ){
			$decRow = $this->query("SELECT id FROM current_stocks AS CurrentStock WHERE entity='".$params['source_entity']."' AND entity_id = ".$params['source_id']." AND brand_id = $brand_id ");
			
			if ($decRow){
				$this->query("UPDATE current_stocks SET `$qtyColummn` = `$qtyColummn` - $qty WHERE id = ".$decRow['0']['CurrentStock']['id']);
			}
		}
		
	} */
	
	
	public function updateStockStatus($params){
		//$brand_id = $params['brand_id'];
		$brand_group_id = $params['brand_group_id'];
		$qty = $params['quantity'];
		if (isset($params['old_qty'])){
			$qty = $qty - $params['old_qty'];
		}
		$column = array('Case'=>'case', 'Roll'=>'roll', 'Packet'=>'pack');
		//$qtyColummn = $column[$params['pack_type']];
		$qtyColummn = 'case';
		//Increase destination stock
		if( isset($params['destination_entity']) && isset($params['destination_id']) ){
			
			$addRow = $this->query("SELECT id FROM current_stocks AS CurrentStock WHERE entity='".$params['destination_entity']."' AND entity_id = ".$params['destination_id']." AND brand_group_id = $brand_group_id ");
			
			if ($addRow){
				$this->query("UPDATE current_stocks SET `$qtyColummn` = `$qtyColummn` + $qty WHERE id = ".$addRow['0']['CurrentStock']['id']);
			}else{
				$this->query("INSERT INTO current_stocks (entity_id, entity, brand_group_id, `$qtyColummn`) VALUES (".$params['destination_id'].", '".$params['destination_entity']."', $brand_group_id, $qty)");
			}
		}
		
		//Decrease destination stock
		if( isset($params['source_entity']) && isset($params['source_id']) ){
			$decRow = $this->query("SELECT id FROM current_stocks AS CurrentStock WHERE entity='".$params['source_entity']."' AND entity_id = ".$params['source_id']." AND brand_group_id = $brand_group_id ");
			
			if ($decRow){
				$this->query("UPDATE current_stocks SET `$qtyColummn` = `$qtyColummn` - $qty WHERE id = ".$decRow['0']['CurrentStock']['id']);
			}
		}
		
	}
	
	
	/*
	 * associateCurrentStock method
	 * Associate Current stock with corresponding model data
	 */
	public function associateCurrentStock(&$entity, $model){
		$entIds = array();
		ClassRegistry::init('CurrentStock');
		foreach($entity as $ent){
			$entIds[] = $ent[$model]['id'];
		}
		//App::import('Model','CurrentStock');
		$currentStock = new CurrentStock();
		$entData = $currentStock->find('all', array('conditions'=>array("CurrentStock.entity"=>$model, "CurrentStock.entity_id"=>$entIds)));
		$stockData = array();
		if ($entData){
			foreach ($entData as $edata){
				$addFields = $edata['CurrentStock'];
				$addFields['Brand'] = $edata['Brand'];
				$stockData[$edata['CurrentStock']['entity_id']][] = $addFields;
			}
		}
		//pr($stockData);
		foreach($entity as $key=>$val){
			if (isset($stockData[$val[$model]['id']])){
				$entity[$key]['CurrentStock'] = $stockData[$val[$model]['id']];
			}else{
				$entity[$key]['CurrentStock'] = array();
			}
		}
	}
	
	
	public function depotStock(&$depots, $params){
		//pr($depots);
		foreach($depots as $depot){
			$depotIds[] = $depot['Depot']['id'];
		}
		$rsC = $siC = $soC = $sC = $fiC = $iC = '';
		$brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';
		$repotArr = array();
		//if ( !empty($query['dp']))
		$brandCond = '';
		/* if(!empty($params['query']['br'])){
			$brandCond = 'AND brand_id='.$params['query']['br'];
		} */
		
		if(!empty($params['query']['br'])){
			$brandCond = 'AND brand_group_id='.$params['query']['br'];
		}
		switch($params['type']){
			case 'daily':
				$rsC = $siC = " AND stock_date = '".$params['date']."'";
				$soC = " AND dispatch_date = '".$params['date']."'";
				$sC = $fiC = " AND sale_date = '".$params['date']."'";
				$iC = " AND incentive_date = '".$params['date']."'";
				$refDate = $params['date'];
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				$rsC = $siC = " AND stock_date between '$d1' AND '$d2'";
				$soC = " AND dispatch_date between '$d1' AND '$d2'";
				$sC = $fiC = " AND sale_date between '$d1' AND '$d2'";
				$iC = " AND incentive_date between '$d1' AND '$d2'";
				$refDate = $d1;
			break;
			case 'monthly':
				$d = date('Y-m', $params['strtotime']);
				
				$rsC = $siC = " AND stock_date LIKE '$d%'";
				$soC = " AND dispatch_date LIKE '$d%'";
				$sC = $fiC = " AND sale_date LIKE '$d%'";
				$iC = " AND incentive_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'];
				}	
				$rsC = $siC = " AND stock_date between '$d1' AND '$d2'";
				$soC = " AND dispatch_date between '$d1' AND '$d2'";
				$sC = $fiC = " AND sale_date between '$d1' AND '$d2'";
				$iC = " AND incentive_date between '$d1' AND '$d2'";
				$refDate = $d1;
			break;
		}
		//Total In stock till date
		$rsTilDate = $this->query("SELECT destination_id, brand_id, brand_group_id, SUM(cal_qty) AS rsTilDate FROM stocks AS Stock WHERE stock_date >= '".OPENING_DATE."' AND stock_date < '$refDate' AND destination_entity = 'Depot' $brandGroupCond $brandCond AND Stock.destination_id IN (".implode(",", $depotIds).") GROUP BY destination_id, brand_group_id" ); //pr($rsTilDate);
		
		foreach ($rsTilDate  as $recieved){
			$did = $recieved['Stock']['destination_id'];
			$bid = $recieved['Stock']['brand_group_id'];
			$repotArr[$did][$bid]['rsTilDate'] = $recieved['0']['rsTilDate'];
		} 
		
		//Total out stock till date
		$osTilDate = $this->query("SELECT source_id, brand_id, brand_group_id, SUM(cal_qty) AS osTilDate FROM stocks AS Stock WHERE dispatch_date >= '".OPENING_DATE."' AND dispatch_date < '$refDate' AND source_entity = 'Depot' $brandGroupCond $brandCond AND Stock.source_id IN (".implode(",", $depotIds).") GROUP BY source_id, brand_group_id" );
		
		foreach ($osTilDate  as $out){
			$did = $out['Stock']['source_id'];
			$bid = $out['Stock']['brand_group_id'];
			$repotArr[$did][$bid]['osTilDate'] = $out['0']['osTilDate'];
		} 
		
		//Total sales stock till date
		$salesTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS salesTilDate FROM sales AS Sale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND user_id != 0 AND customer_id != 0 AND sale_date >= '".OPENING_DATE."' AND sale_date < '$refDate' GROUP BY depot_id, brand_group_id" ); 
		foreach ($salesTilDate  as $sale){
			$did = $sale['Sale']['depot_id'];
			$bid = $sale['Sale']['brand_group_id'];
			$repotArr[$did][$bid]['salesTilDate'] = $sale['0']['salesTilDate'];
		}
		
		//Total Consumer sales stock till date
		$conSalesTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS conSalesTilDate FROM consumer_sales AS ConsumerSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND user_id != 0 AND consumer_id != 0 AND sale_date >= '".OPENING_DATE."' AND sale_date < '$refDate' GROUP BY depot_id, brand_group_id" ); 
		foreach ($conSalesTilDate  as $sale){
			$did = $sale['ConsumerSale']['depot_id'];
			$bid = $sale['ConsumerSale']['brand_group_id'];
			$repotArr[$did][$bid]['conSalesTilDate'] = $sale['0']['conSalesTilDate'];
		}
		
		//Total Freeitems till date
		$fiTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS fiTilDate FROM free_items as FreeItem WHERE  depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND sale_date >= '".OPENING_DATE."' AND sale_date < '$refDate' GROUP BY depot_id, brand_group_id"); 
		foreach ($fiTilDate  as $fi){
			$did = $fi['FreeItem']['depot_id'];
			$bid = $fi['FreeItem']['brand_group_id'];
			$repotArr[$did][$bid]['fiTilDate'] = $fi['0']['fiTilDate'];
		} 
		
		//Total Consumer Freeitems till date
		$conFiTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS fiTilDate FROM consumer_free_items as FreeItem WHERE  depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND sale_date >= '".OPENING_DATE."' AND sale_date < '$refDate' GROUP BY depot_id, brand_group_id" ); //pr($fiTilDate);
		foreach ($conFiTilDate  as $fi){
			$did = $fi['FreeItem']['depot_id'];
			$bid = $fi['FreeItem']['brand_group_id'];
			$repotArr[$did][$bid]['conFiTilDate'] = $fi['0']['fiTilDate'];
		} 
		
		
		
		
		
		//Total incentive till date
		$insentivesTilDate = $this->query( "SELECT Incentive.depot_id, brand_id, brand_group_id, SUM(cal_qty) AS insentivesTilDate FROM incentives as Incentive INNER JOIN customers as Customer on Incentive.customer_id = Customer.id WHERE Incentive.depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND Incentive.user_id != 0 AND customer_id != 0 AND incentive_date >= '".OPENING_DATE."' AND incentive_date < '$refDate' GROUP BY Incentive.depot_id,brand_group_id" ); 
		foreach ($insentivesTilDate  as $insentive){
			$did = $insentive['Incentive']['depot_id'];
			$bid = $insentive['Incentive']['brand_group_id'];
			$repotArr[$did][$bid]['insentivesTilDate'] = $insentive['0']['insentivesTilDate'];
		}
		
		//Recieved Stock
		$receivedStock = $this->query("SELECT destination_id, brand_id, brand_group_id, SUM(cal_qty) AS receivedStock FROM stocks AS Stock WHERE source_entity = 'Factory' $rsC $brandGroupCond $brandCond AND destination_entity = 'Depot' AND Stock.destination_id IN (".implode(",", $depotIds).") GROUP BY destination_id, brand_group_id" );
		
		foreach ($receivedStock  as $rs){
			$did = $rs['Stock']['destination_id'];
			$bid = $rs['Stock']['brand_group_id'];
			$repotArr[$did][$bid]['receivedStock'] = $rs['0']['receivedStock'];
		}
		//Stock In
		$stockIn = $this->query("SELECT destination_id, brand_id, brand_group_id, SUM(cal_qty) AS stockIn FROM stocks AS Stock WHERE (source_entity = 'Warehouse' OR source_entity = 'Depot' OR source_entity = 'Distributor') $brandGroupCond $brandCond $siC AND  destination_entity = 'Depot' AND Stock.destination_id IN (".implode(",", $depotIds).") GROUP BY destination_id, brand_group_id" );
		foreach ($stockIn  as $si){
			$did = $si['Stock']['destination_id'];
			$bid = $si['Stock']['brand_group_id'];
			$repotArr[$did][$bid]['stockIn'] = $si['0']['stockIn'];
		}
		//Stock out
		$stockOut = $this->query("SELECT source_id, brand_id, brand_group_id, SUM(cal_qty) AS stockOut FROM stocks AS Stock WHERE source_entity = 'Depot' $soC $brandGroupCond $brandCond AND Stock.source_id IN (".implode(",", $depotIds).") GROUP BY source_id, brand_group_id" );
		foreach ($stockOut  as $so){
			$did = $so['Stock']['source_id'];
			$bid = $so['Stock']['brand_group_id'];
			$repotArr[$did][$bid]['stockOut'] = $so['0']['stockOut'];
		}
		//sold items
		$sales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS sales FROM sales AS Sale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC AND user_id != 0 AND customer_id != 0 GROUP BY depot_id, brand_group_id" );
		foreach ($sales  as $sale){
			$did = $sale['Sale']['depot_id'];
			$bid = $sale['Sale']['brand_group_id'];
			$repotArr[$did][$bid]['sales'] = $sale['0']['sales'];
		}
		
		//consumer sold items
		$consumerSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerSales FROM consumer_sales AS ConsumerSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC AND user_id != 0 AND consumer_id != 0 GROUP BY depot_id, brand_group_id" );
		foreach ($consumerSales  as $sale){
			$did = $sale['ConsumerSale']['depot_id'];
			$bid = $sale['ConsumerSale']['brand_group_id'];
			$repotArr[$did][$bid]['consumerSales'] = $sale['0']['consumerSales'];
		}
		
		//Free items
		$freeItems = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS freeItems FROM free_items as FreeItem WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $fiC GROUP BY depot_id, brand_group_id" ); 
		foreach ($freeItems  as $freeItem){
			$did = $freeItem['FreeItem']['depot_id'];
			$bid = $freeItem['FreeItem']['brand_group_id'];
			$repotArr[$did][$bid]['freeItems'] = $freeItem['0']['freeItems'];
		}
		
		//Consumer Free items
		$consumerFreeItems = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS freeItems FROM consumer_free_items as FreeItem WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $fiC GROUP BY depot_id, brand_group_id" );
		foreach ($consumerFreeItems  as $freeItem){
			$did = $freeItem['FreeItem']['depot_id'];
			$bid = $freeItem['FreeItem']['brand_group_id'];
			$repotArr[$did][$bid]['consumerFreeItems'] = $freeItem['0']['freeItems'];
		}
		
		
		
		//insentives
		$insentives = $this->query( "SELECT Incentive.depot_id, brand_id, brand_group_id, SUM(cal_qty) AS incentive FROM incentives as Incentive INNER JOIN customers as Customer on Incentive.customer_id = Customer.id WHERE Incentive.depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $iC AND Incentive.user_id != 0 AND Incentive.customer_id != 0 GROUP BY Incentive.depot_id, Incentive.brand_group_id" ); //pr($insentives);
		foreach ($insentives  as $insentive){
			$did = $insentive['Incentive']['depot_id'];
			$bid = $insentive['Incentive']['brand_group_id'];
			$repotArr[$did][$bid]['incentive'] = $insentive['0']['incentive'];
		}
		
		$opening_datas = $this->query( "SELECT depot_id, brand_group_id, opening_stock, opening_finance, opening_budget FROM  depot_opening_datas as OpeningData WHERE opening_date = '".OPENING_DATE."'" ); //pr($opening_datas);
        foreach ($opening_datas  as $opening_data){
			$did = $opening_data['OpeningData']['depot_id'];
			$bid = $opening_data['OpeningData']['brand_group_id'];
			$repotArr[$did][$bid]['opening_data']['opening_stock'] = $opening_data['OpeningData']['opening_stock'];
			$repotArr[$did][$bid]['opening_data']['opening_finance'] = $opening_data['OpeningData']['opening_finance'];
			$repotArr[$did][$bid]['opening_data']['opening_budget'] = $opening_data['OpeningData']['opening_budget'];
		}
		//pr($repotArr);
		
		//pr($depots);
		foreach($depots as $key => $val){ 
			if (isset($repotArr[$val['Depot']['id']])){
				$depots[$key]['CurrentStock'] = $repotArr[$val['Depot']['id']];
			}else{
				$depots[$key]['CurrentStock'] = array();
			}
		}
	}
/**
  * warehouseStock : get cuurent warehouse stock status
  * @params : $warehouse array(), $params array()
  * return: mixed array
  */ 
	
	public function warehouseStock(&$warehouses, $params){
		//pr($params);
		foreach($warehouses as $warehouse){
			$warehouseIds[] = $warehouse['Warehouse']['id'];
		}
		$rsC = $siC = $soC = $sC = $fiC = $iC = '';
		
		$brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';
		
		$reportArr = array();
		//if ( !empty($query['dp']))
		$brandCond = '';
		if(!empty($params['query']['br'])){
			//$brandCond = 'AND brand_id='.$params['query']['br'];
			$brandCond = 'AND brand_group_id='.$params['query']['br'];
		}
		switch($params['type']){
			case 'daily':
				$rsC = $siC = $soC = " AND stock_date = '".$params['date']."'";
				$sC = $fiC = " AND sale_date = '".$params['date']."'";
				$iC = " AND incentive_date = '".$params['date']."'";
				$refDate = $params['date'];
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				//__('From %s to %s', date('M d, Y', $strtotime), date('Y-m-d', strtotime('+6 days', $strtotime)));
				$rsC = $siC = $soC = " AND stock_date between '$d1' AND '$d2'";
				$sC = $fiC = " AND sale_date between '$d1' AND '$d2'";
				$iC = " AND incentive_date between '$d1' AND '$d2'";
				$refDate = $d1;
			break;
			case 'monthly':
				$d = date('Y-m', $params['strtotime']);
				
				$rsC = $siC = $soC = " AND stock_date LIKE '$d%'";
				$sC = $fiC = " AND sale_date LIKE '$d%'";
				$iC = " AND incentive_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'];
				}	
				$rsC = $siC = $soC = " AND stock_date between '$d1' AND '$d2'";
				$sC = $fiC = " AND sale_date between '$d1' AND '$d2'";
				$iC = " AND incentive_date between '$d1' AND '$d2'";
				$refDate = $d1;
			break;
		}
		//Total In stock till date
		//$rsTilDate = $this->query("SELECT destination_id, brand_id, SUM(cal_qty) AS rsTilDate FROM stocks AS Stock WHERE stock_date < '$refDate' AND destination_entity = 'Warehouse' $brandCond AND Stock.destination_id IN (".implode(",", $warehouseIds).") GROUP BY destination_id, brand_id" );
		
		$rsTilDate = $this->query("SELECT destination_id, brand_id, brand_group_id, SUM(cal_qty) AS rsTilDate FROM stocks AS Stock WHERE stock_date < '$refDate' AND destination_entity = 'Warehouse' $brandGroupCond $brandCond AND Stock.destination_id IN (".implode(",", $warehouseIds).") GROUP BY destination_id, brand_group_id" ); //pr($rsTilDate);
		
		foreach ($rsTilDate  as $recieved){
			$did = $recieved['Stock']['destination_id'];
			$bid = $recieved['Stock']['brand_group_id'];
			$reportArr[$did][$bid]['rsTilDate'] = $recieved['0']['rsTilDate'];
		}
		
		//Total out stock till date
		$osTilDate = $this->query("SELECT source_id, brand_id, brand_group_id, SUM(cal_qty) AS osTilDate FROM stocks AS Stock WHERE stock_date < '$refDate' AND source_entity = 'Warehouse' $brandGroupCond $brandCond AND Stock.source_id IN (".implode(",", $warehouseIds).") GROUP BY source_id, brand_group_id" );
		
		foreach ($osTilDate  as $out){
			$did = $out['Stock']['source_id'];
			$bid = $out['Stock']['brand_group_id'];
			$reportArr[$did][$bid]['osTilDate'] = $out['0']['osTilDate'];
		}
		
		//Total incentive till date
		$insentivesTilDate = $this->query( "SELECT warehouse_id, brand_id, brand_group_id, SUM(cal_qty) AS insentivesTilDate FROM warehouse_incentives as WarehouseIncentive WHERE warehouse_id IN (".implode(",", $warehouseIds).") $brandGroupCond $brandCond AND incentive_date < '$refDate' GROUP BY warehouse_id,brand_group_id" );
		foreach ($insentivesTilDate  as $insentive){
			$did = $insentive['WarehouseIncentive']['warehouse_id'];
			$bid = $insentive['WarehouseIncentive']['brand_group_id'];
			$reportArr[$did][$bid]['insentivesTilDate'] = $insentive['0']['insentivesTilDate'];
		}
		
		
		//Recieved Stock
		$receivedStock = $this->query("SELECT destination_id, brand_id, brand_group_id, SUM(cal_qty) AS receivedStock FROM stocks AS Stock WHERE source_entity = 'Factory' $rsC $brandGroupCond $brandCond AND destination_entity = 'Warehouse' AND Stock.destination_id IN (".implode(",", $warehouseIds).") GROUP BY destination_id, brand_group_id" );
		
		foreach ($receivedStock  as $rs){
			$did = $rs['Stock']['destination_id'];
			$bid = $rs['Stock']['brand_group_id'];
			$reportArr[$did][$bid]['receivedStock'] = $rs['0']['receivedStock'];
		}
		//Stock In
		$stockIn = $this->query("SELECT destination_id, brand_id, brand_group_id, SUM(cal_qty) AS stockIn FROM stocks AS Stock WHERE (source_entity = 'Warehouse' OR source_entity = 'Depot' OR source_entity = 'Distributor') $brandGroupCond $brandCond $siC AND  destination_entity = 'Warehouse' AND Stock.destination_id IN (".implode(",", $warehouseIds).") GROUP BY destination_id, brand_group_id" );
		foreach ($stockIn  as $si){
			$did = $si['Stock']['destination_id'];
			$bid = $si['Stock']['brand_group_id'];
			$reportArr[$did][$bid]['stockIn'] = $si['0']['stockIn'];
		}
		//Stock out
		$stockOut = $this->query("SELECT source_id, brand_id, brand_group_id, SUM(cal_qty) AS stockOut FROM stocks AS Stock WHERE source_entity = 'Warehouse' $soC $brandGroupCond $brandCond AND Stock.source_id IN (".implode(",", $warehouseIds).") GROUP BY source_id, brand_group_id" );
		foreach ($stockOut  as $so){
			$did = $so['Stock']['source_id'];
			$bid = $so['Stock']['brand_group_id'];
			$reportArr[$did][$bid]['stockOut'] = $so['0']['stockOut'];
		}
		
		//insentives
		$insentives = $this->query( "SELECT warehouse_id, brand_id, brand_group_id, SUM(cal_qty) AS incentive FROM warehouse_incentives as WarehouseIncentive WHERE warehouse_id IN (".implode(",", $warehouseIds).") $brandGroupCond $brandCond $iC GROUP BY warehouse_id,brand_group_id" );
		foreach ($insentives  as $insentive){
			$did = $insentive['WarehouseIncentive']['warehouse_id'];
			$bid = $insentive['WarehouseIncentive']['brand_group_id'];
			$reportArr[$did][$bid]['incentive'] = $insentive['0']['incentive'];
		}
		//pr($reportArr);
		foreach($warehouses as $key => $val){
			if (isset($reportArr[$val['Warehouse']['id']])){
				$warehouses[$key]['CurrentStock'] = $reportArr[$val['Warehouse']['id']];
			}else{
				$warehouses[$key]['CurrentStock'] = array();
			}
		}
	}
	
	
}