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

/**
 * Application model for Report.
 *
 * Add your report-wide methods in the class below, your models
 * @package       app.Model
 */

class BrandReport extends AppModel {
    public $useTable = false;
	
	public function calStickQty($brand_id, $qty){
		
		$brand = $this->query("SELECT * FROM brands as Brand WHERE id = $brand_id  LIMIT 0,1"); //pr($brand);
		
		$qty = $qty * $brand[0]['Brand']['case_roll'] * $brand[0]['Brand']['roll_pack'] * $brand[0]['Brand']['pack_sticks'];
		
		return $qty; 
	}
	
	public function saleQuantity($params){
        //pr($params);
		$date = $params['date'];
        $result = array();
        switch($params['type']){
            case 'daily':
                $sC = "s.sale_date = '".$date."'";
				$rdC = "rs.sale_date = '".$date."'";
                break;
            case 'weekly':
                $d1 = date('Y-m-d', $params['strtotime']);
                $d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));

                $sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				
                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'];
                }
				
                $sC = "s.sale_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
                break;
            case 'monthly':
                $d1 = date('Y-m-01', $params['strtotime']);
                $d2 = date('Y-m-t', $params['strtotime']);

                $sC = "s.sale_date between '$d1' AND '$d2'";
				
				$rdC = "rs.sale_date between '$d1' AND '$d2'";

                break;
        }
		
		$dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";
        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }
		
		if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
			$brdC = " AND rs.brand_group_id = ".$params['query']['br'];
        }
        

        // Check null depot id
        $ndpC = "AND s.depot_id != 0";
		$nrdpC = "AND rs.depot_id != 0";
		
		$result['total_sale'] = $result['total_rsale'] = $result['total_consale'] = $result['total_conrsale'] =0;
		
		/** SALE **/
		
		$saleQty = "SELECT s.depot_id, s.brand_id, s.brand_group_id, SUM(cal_qty) AS total_qty FROM sales AS s LEFT JOIN users u ON u.id = s.user_id LEFT JOIN depots d ON s.depot_id = d.id WHERE $sC $dv_regC $rgC $dpC $ndpC $bsC GROUP BY s.brand_group_id, s.brand_id, s.depot_id";
        $sales = $this->query($saleQty); //pr($sales );
		
		foreach($sales as $sale){
			$bgid = $sale['s']['brand_group_id'];
			$bid = $sale['s']['brand_id'];
			$did = $sale['s']['depot_id'];
			//pr($result['total_sale']);
			if(!empty($sale[0]['total_qty'])){
				$sale[0]['total_qty'] = $this->calStickQty($bid, $sale[0]['total_qty']); 
				$result['total_sale'] += $sale[0]['total_qty'];
			}
			

			if(!empty($result[0][$did][0][$bgid]['sales'])){
				$result[0][$did][0][$bgid]['sales'] += $sale[0]['total_qty'];
			}else{
				$result[0][$did][0][$bgid]['sales'] = $sale[0]['total_qty'];
			}
		}
		
		/** RSALE **/
		$rsaleQty = "SELECT rs.depot_id, rs.brand_id, rs.brand_group_id, SUM(cal_qty) AS total_qty FROM redistributor_saleitems AS rs LEFT JOIN users u ON u.id = rs.user_id LEFT JOIN depots d ON rs.depot_id = d.id WHERE $rdC $dv_regC $rgC $dpC $nrdpC $brdC GROUP BY rs.brand_group_id, rs.brand_id, rs.depot_id";
        $rsales = $this->query($rsaleQty); //pr($sales);
		
		foreach($rsales as $sale){
			$bgid = $sale['rs']['brand_group_id'];
			$bid = $sale['rs']['brand_id'];
			$did = $sale['rs']['depot_id'];
			
			if(!empty($sale[0]['total_qty'])){
				$sale[0]['total_qty'] = $this->calStickQty($bid, $sale[0]['total_qty']);
				$result['total_rsale'] += $sale[0]['total_qty'];
			}
			
			if(!empty($result[0][$did][0][$bgid]['rsales'])){
				$result[0][$did][0][$bgid]['rsales'] += $sale[0]['total_qty'];
			}else{
				$result[0][$did][0][$bgid]['rsales'] = $sale[0]['total_qty'];
			}
		}
		
		/** Consumer SALE **/
		$consaleQty = "SELECT s.depot_id, s.brand_id, s.brand_group_id, SUM(cal_qty) AS total_qty FROM consumer_sales AS s LEFT JOIN users u ON u.id = s.user_id LEFT JOIN depots d ON s.depot_id = d.id WHERE $sC $dv_regC $rgC $dpC $ndpC $bsC GROUP BY s.brand_group_id, s.brand_id, s.depot_id";
        $sales = $this->query($consaleQty); //pr($sales);
		
		foreach($sales as $sale){
			$bgid = $sale['s']['brand_group_id'];
			$bid = $sale['s']['brand_id'];
			$did = $sale['s']['depot_id'];
			
			if(!empty($sale[0]['total_qty'])){
				$sale[0]['total_qty'] = $this->calStickQty($bid, $sale[0]['total_qty']);
				$result['total_consale'] += $sale[0]['total_qty'];
			}
			
			if(!empty($result[0][$did][0][$bgid]['consales'])){
				$result[0][$did][0][$bgid]['consales'] += $sale[0]['total_qty'];
			}else{
				$result[0][$did][0][$bgid]['consales'] = $sale[0]['total_qty'];
			}
		}
		
		/** Consumer RSALE **/
		$rsaleQty = "SELECT rs.depot_id, rs.brand_id, rs.brand_group_id, SUM(cal_qty) AS total_qty FROM consumer_redistribution_sales AS rs LEFT JOIN users u ON u.id = rs.user_id LEFT JOIN depots d ON rs.depot_id = d.id WHERE $rdC $dv_regC $rgC $dpC $nrdpC $brdC GROUP BY rs.brand_group_id, rs.brand_id, rs.depot_id";
        $rsales = $this->query($rsaleQty); //pr($sales);
		
		foreach($rsales as $sale){
			$bgid = $sale['rs']['brand_group_id'];
			$bid = $sale['rs']['brand_id'];
			$did = $sale['rs']['depot_id'];
			
			if(!empty($sale[0]['total_qty'])){
				$sale[0]['total_qty'] = $this->calStickQty($bid, $sale[0]['total_qty']);
				$result['total_conrsale'] += $sale[0]['total_qty'];
			}
			
			if(!empty($result[0][$did][0][$bgid]['conrsales'])){
				$result[0][$did][0][$bgid]['conrsales'] += $sale[0]['total_qty'];
			}else{
				$result[0][$did][0][$bgid]['conrsales'] = $sale[0]['total_qty'];
			}
		}

        //set region_id and division_id

        if(!empty($result['0']) && count($result['0'])>0){
            $dIds = implode(',', array_keys($result['0']));

            $dRegDiv = $this->query("SELECT d.id as depot_id ,d.region_id as region_id,dr.division_id as division_id FROM depots d INNER JOIN divisions_regions dr ON d.region_id = dr.region_id AND d.id IN($dIds)");
            $dRegDivArray = array();
            foreach($dRegDiv as $dData){
                $dRegDivArray[$dData['d']['depot_id']]['region_id'] = $dData['d']['region_id'];
                $dRegDivArray[$dData['d']['depot_id']]['division_id'] = $dData['dr']['division_id'];
            }

            foreach($result['0'] as $did => $brand_result){
                if(empty($result[0][$did]['region_id'])){
                    $result[0][$did]['region_id'] = $dRegDivArray[$did]['region_id'] ;
                }
                if(empty($result[0][$did]['division_id'])){
                    $result[0][$did]['division_id'] = $dRegDivArray[$did]['division_id'] ;
                }
            }
        }

       //pr($result);exit;
       return $result;
    }
	
	public function noSaleCustomers($params){
        //pr($params);
        $date = $params['date'];
        $result = array();
        switch($params['type']){
            case 'daily':
                $sC = "s.sale_date = '".$date."'";
                $rdC = "rs.sale_date = '".$date."'";
                $cvC = "cv.visiting_date LIKE '".$date."%'";
                break;
            case 'weekly':
                $d1 = date('Y-m-d', $params['strtotime']);
                $d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));

                $sC = "s.sale_date between '$d1' AND '$d2'";
                $rdC = "rs.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";

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

                $sC = "s.sale_date between '$d1' AND '$d2'";
                $rdC = "rs.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;
            case 'monthly':
                $d1 = date('Y-m-01', $params['strtotime']);
                $d2 = date('Y-m-t', $params['strtotime']);

                $sC = "s.sale_date between '$d1' AND '$d2'";

                $rdC = "rs.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";

                break;
        }

        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";
        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
        }


        // Check null depot id
        $ndpC = "AND s.depot_id != 0";
        $nrdpC = "AND rs.depot_id != 0";

        $result['total_sale'] = $result['total_rsale'] = 0;

        /** SALE **/

        $saleQty = "SELECT d.region_id, s.depot_id, s.brand_group_id, s.brand_id, SUM(s.cal_qty) as total_qty FROM sales s LEFT JOIN customers c ON c.id = s.customer_id LEFT JOIN depots d ON s.depot_id = d.id WHERE $sC $dv_regC $rgC $dpC $ndpC $bsC GROUP BY s.depot_id, s.brand_group_id, s.brand_id";
        $sales = $this->query($saleQty);
        //pr($sales);exit;

        foreach($sales as $sale){
            $bgid = $sale['s']['brand_group_id'];
            $bid = $sale['s']['brand_id'];
            $did = $sale['s']['depot_id'];
            //pr($result['total_sale']);

            /*Sale Quantity*/
            if(!empty($sale[0]['total_qty'])){
                $sale[0]['total_qty'] = $this->calStickQty($bid, $sale[0]['total_qty']);
                $result['total_sale'] += $sale[0]['total_qty'];
            }

            if(!empty($result[0][$did][0][$bgid]['sales'])){
                $result[0][$did][0][$bgid]['sales'] += $sale[0]['total_qty'];
            }else{
                $result[0][$did][0][$bgid]['sales'] = $sale[0]['total_qty'];
            }


        }

        /** RSALE **/
        $rsaleQty = "SELECT d.region_id, rs.depot_id, rs.brand_id, rs.brand_group_id,  SUM(cal_qty) AS total_qty FROM redistributor_saleitems AS rs LEFT JOIN customers c ON c.id = rs.customer_id LEFT JOIN depots d ON rs.depot_id = d.id WHERE $rdC $dv_regC $rgC $dpC $nrdpC $brdC GROUP BY rs.depot_id, rs.brand_group_id, rs.brand_id";
        $rsales = $this->query($rsaleQty);

        foreach($rsales as $sale){
            $bgid = $sale['rs']['brand_group_id'];
            $bid = $sale['rs']['brand_id'];
            $did = $sale['rs']['depot_id'];

            /*RSale Quantity*/
            if(!empty($sale[0]['total_qty'])){
                $sale[0]['total_qty'] = $this->calStickQty($bid, $sale[0]['total_qty']);
                $result['total_rsale'] += $sale[0]['total_qty'];
            }

            if(!empty($result[0][$did][0][$bgid]['rsales'])){
                $result[0][$did][0][$bgid]['rsales'] += $sale[0]['total_qty'];
            }else{
                $result[0][$did][0][$bgid]['rsales'] = $sale[0]['total_qty'];
            }

        }
        //pr($result);
        /** CUSTOMER VISIT **/
        $visitCount = $this->query("SELECT c.depot_id , count(Distinct cv.customer_id) as cvcustoCount FROM customer_visits cv LEFT JOIN customers c ON c.id = cv.customer_id LEFT JOIN depots d ON c.depot_id = d.id WHERE $cvC $dv_regC $rgC $dpC GROUP BY c.depot_id");
        //pr($visitCount);exit;

        foreach($visitCount as $visit){
            $did = $visit['c']['depot_id'];
            $visitCount = $visit['0']['cvcustoCount'];
            if($did){

                if(empty($result[0][$did][0])){
                    $result[0][$did][0] = array(); // No sales and No Rsales for this depot
                }

                if(isset($result[0][$did]['total_visit'])){
                    $result[0][$did]['total_visit'] = $result[$did]['total_visit'] + $visitCount;
                }else{
                    $result[0][$did]['total_visit'] = $visitCount;
                }

            }
        }


        //set region_id and division_id

        if(!empty($result['0']) && count($result['0'])>0){
            $dIds = implode(',', array_keys($result['0']));

            $dRegDiv = $this->query("SELECT d.id as depot_id ,d.region_id as region_id,dr.division_id as division_id FROM depots d INNER JOIN divisions_regions dr ON d.region_id = dr.region_id AND d.id IN($dIds)");
            $dRegDivArray = array();
            foreach($dRegDiv as $dData){
                $dRegDivArray[$dData['d']['depot_id']]['region_id'] = $dData['d']['region_id'];
                $dRegDivArray[$dData['d']['depot_id']]['division_id'] = $dData['dr']['division_id'];
            }

            foreach($result['0'] as $did => $brand_result){
                if(empty($result[0][$did]['region_id'])){
                    $result[0][$did]['region_id'] = $dRegDivArray[$did]['region_id'] ;
                }
                if(empty($result[0][$did]['division_id'])){
                    $result[0][$did]['division_id'] = $dRegDivArray[$did]['division_id'] ;
                }
            }
        }
        //pr($result);exit;
        return $result;
    }
	
	public function stock(&$depots_Arr,$params){
        //pr($params);
        foreach($depots_Arr as $depot){
            $depotIds[] = $depot['Depot']['id'];
        }
		
		
        $date = $params['date'];
        $result = array();
		
		$rsC = $siC = $soC = $sC = $fiC = $iC = '';
		
		$brandCond = '';
        $brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';
		
        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']));
                $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;
        }

        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";
        
        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
            $brandCond = 'AND brand_group_id='.$params['query']['br'];
        }


        // Check null depot id
        $ndpC = "AND s.depot_id != 0";
        $nrdpC = "AND rs.depot_id != 0";

        $result['total_sale'] = $result['total_rsale'] = 0;

        //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 < '$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_id'];
            $bgid = $recieved['Stock']['brand_group_id'];

            if(!empty($recieved['0']['rsTilDate'])){
               $recieved['0']['rsTilDate'] = $this->calStickQty($bid, $recieved['0']['rsTilDate']);
            }

             $repotArr[$did][$bgid]['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 = '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_id'];
            $bgid = $out['Stock']['brand_group_id'];

            if(!empty($out['0']['osTilDate']) && !empty($bid)){
               $out['0']['osTilDate'] = $this->calStickQty($bid, $out['0']['osTilDate']);
            }

            $repotArr[$did][$bgid]['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 sale_date < '$refDate' GROUP BY depot_id, brand_group_id" );

        foreach ($salesTilDate  as $sale){
            $did = $sale['Sale']['depot_id'];
            $bid = $sale['Sale']['brand_id'];
            $bgid = $sale['Sale']['brand_group_id'];

            if(!empty($sale['0']['salesTilDate'])){
                $sale['0']['salesTilDate'] = $this->calStickQty($bid, $sale['0']['salesTilDate']);
            }

            $repotArr[$did][$bgid]['salesTilDate'] = $sale['0']['salesTilDate'];
        }
		
		//Total Rdsales stock till date
        $rdSalesTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS rdSalesTilDate FROM redistributor_saleitems AS rdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND sale_date < '$refDate' GROUP BY depot_id, brand_group_id" );

        foreach ($rdSalesTilDate  as $sale){
            $did = $sale['rdSale']['depot_id'];
            $bid = $sale['rdSale']['brand_id'];
            $bgid = $sale['rdSale']['brand_group_id'];

            if(!empty($sale['0']['rdSalesTilDate'])){
                $sale['0']['rdSalesTilDate'] = $this->calStickQty($bid, $sale['0']['rdSalesTilDate']);
            }

            $repotArr[$did][$bgid]['rdSalesTilDate'] = $sale['0']['rdSalesTilDate'];
        }

        //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 sale_date < '$refDate' GROUP BY depot_id, brand_group_id" );
        foreach ($conSalesTilDate  as $sale){
            $did = $sale['ConsumerSale']['depot_id'];
            $bid = $sale['ConsumerSale']['brand_id'];
            $bgid = $sale['ConsumerSale']['brand_group_id'];
            if(!empty($sale['0']['conSalesTilDate'])){
				$sale['0']['conSalesTilDate'] = $this->calStickQty($bid, $sale['0']['conSalesTilDate']);
            }

            $repotArr[$did][$bgid]['conSalesTilDate'] = $sale['0']['conSalesTilDate'];
        }
		
		//Total Consumer rdSales stock till date
        $conRdSalesTilDate = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS conRdSalesTilDate FROM consumer_redistribution_sales AS ConsumerRdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND sale_date < '$refDate' GROUP BY depot_id, brand_group_id" );
        foreach ($conRdSalesTilDate  as $sale){
            $did = $sale['ConsumerRdSale']['depot_id'];
            $bid = $sale['ConsumerRdSale']['brand_id'];
            $bgid = $sale['ConsumerRdSale']['brand_group_id'];
            if(!empty($sale['0']['conRdSalesTilDate'])){
				$sale['0']['conRdSalesTilDate'] = $this->calStickQty($bid, $sale['0']['conRdSalesTilDate']);
            }

            $repotArr[$did][$bgid]['conRdSalesTilDate'] = $sale['0']['conRdSalesTilDate'];
        }

        //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 < '$refDate' GROUP BY depot_id, brand_group_id" ); //pr($fiTilDate);
        foreach ($fiTilDate  as $fi){
            $did = $fi['FreeItem']['depot_id'];
            $bid = $fi['FreeItem']['brand_id'];
            $bgid = $fi['FreeItem']['brand_group_id'];
            if(!empty($fi['0']['fiTilDate'])){
                $fi['0']['fiTilDate'] = $this->calStickQty($bid, $fi['0']['fiTilDate']);
            }

            $repotArr[$did][$bgid]['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 < '$refDate' GROUP BY depot_id, brand_group_id" ); //pr($fiTilDate);
        foreach ($conFiTilDate  as $fi){
            $did = $fi['FreeItem']['depot_id'];
            $bid = $fi['FreeItem']['brand_id'];
            $bgid = $fi['FreeItem']['brand_group_id'];
            if(!empty($fi['0']['fiTilDate'])){
                $fi['0']['fiTilDate'] = $this->calStickQty($bid, $fi['0']['fiTilDate']);
            }

            $repotArr[$did][$bgid]['conFiTilDate'] = $fi['0']['fiTilDate'];
        }





        //Total incentive till date
        $insentivesTilDate = $this->query( "SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS insentivesTilDate FROM incentives as Incentive WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond AND incentive_date < '$refDate' GROUP BY depot_id,brand_group_id" );
        foreach ($insentivesTilDate  as $insentive){
            $did = $insentive['Incentive']['depot_id'];
            $bid = $insentive['Incentive']['brand_id'];
            $bgid = $insentive['Incentive']['brand_group_id'];
            if(!empty($insentive['0']['insentivesTilDate'])){
                $insentive['0']['insentivesTilDate'] = $this->calStickQty($bid, $insentive['0']['insentivesTilDate']);
            }

            $repotArr[$did][$bgid]['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_id'];
            $bgid = $rs['Stock']['brand_group_id'];
            if(!empty($rs['0']['receivedStock'])){
                $rs['0']['receivedStock'] = $this->calStickQty($bid, $rs['0']['receivedStock']);
            }

            $repotArr[$did][$bgid]['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_id'];
            $bgid = $si['Stock']['brand_group_id'];
            if(!empty($si['0']['stockIn'])){
                $si['0']['stockIn'] = $this->calStickQty($bid, $si['0']['stockIn']);
            }

            $repotArr[$did][$bgid]['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_id'];
            $bgid = $so['Stock']['brand_group_id'];
            if(!empty($so['0']['stockOut'])){
                $so['0']['stockOut'] = $this->calStickQty($bid, $so['0']['stockOut']);
            }

            $repotArr[$did][$bgid]['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 GROUP BY depot_id, brand_group_id" );
        foreach ($sales  as $sale){
            $did = $sale['Sale']['depot_id'];
            $bid = $sale['Sale']['brand_id'];
            $bgid = $sale['Sale']['brand_group_id'];

            if(!empty($sale['0']['sales'])){
                $sale['0']['sales'] = $this->calStickQty($bid, $sale['0']['sales']);
            }

            $repotArr[$did][$bgid]['sales'] = $sale['0']['sales'];
        }
		
		 //RD sold items
        $rdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS rdSales FROM redistributor_saleitems AS rdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
        foreach ($rdSales  as $sale){
            $did = $sale['rdSale']['depot_id'];
            $bid = $sale['rdSale']['brand_id'];
            $bgid = $sale['rdSale']['brand_group_id'];

            if(!empty($sale['0']['rdSales'])){
                $sale['0']['rdSales'] = $this->calStickQty($bid, $sale['0']['rdSales']);
            }

            $repotArr[$did][$bgid]['rdSales'] = $sale['0']['rdSales'];
        }

        //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 GROUP BY depot_id, brand_group_id" );
        foreach ($consumerSales  as $sale){
            $did = $sale['ConsumerSale']['depot_id'];
            $bid = $sale['ConsumerSale']['brand_id'];
            $bgid = $sale['ConsumerSale']['brand_group_id'];
            if(!empty($sale['0']['consumerSales'])){
                $sale['0']['consumerSales'] = $this->calStickQty($bid, $sale['0']['consumerSales']);
            }

            $repotArr[$did][$bgid]['consumerSales'] = $sale['0']['consumerSales'];
        }
		
		 //consumer RD sold items
        $consumerRdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerRdSales FROM consumer_redistribution_sales AS ConsumerRdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
        foreach ($consumerRdSales  as $sale){
            $did = $sale['ConsumerRdSale']['depot_id'];
            $bid = $sale['ConsumerRdSale']['brand_id'];
            $bgid = $sale['ConsumerRdSale']['brand_group_id'];
            if(!empty($sale['0']['consumerRdSales'])){
                $sale['0']['consumerRdSales'] = $this->calStickQty($bid, $sale['0']['consumerRdSales']);
            }

            $repotArr[$did][$bgid]['consumerRdSales'] = $sale['0']['consumerRdSales'];
        }

        //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_id'];
            $bgid = $freeItem['FreeItem']['brand_group_id'];
            if(!empty($freeItem['0']['freeItems'])){
                $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
            }

            $repotArr[$did][$bgid]['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_id'];
            $bgid = $freeItem['FreeItem']['brand_group_id'];

            if(!empty($freeItem['0']['freeItems'])){
                $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
            }

            $repotArr[$did][$bgid]['consumerFreeItems'] = $freeItem['0']['freeItems'];
        }



        //insentives
        $insentives = $this->query( "SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS incentive FROM incentives as Incentive WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $iC GROUP BY depot_id,brand_group_id" );
        foreach ($insentives  as $insentive){
            $did = $insentive['Incentive']['depot_id'];
            $bid = $insentive['Incentive']['brand_id'];
            $bgid = $insentive['Incentive']['brand_group_id'];

            if(!empty($insentive['0']['incentive'])){
                $insentive['0']['incentive'] = $this->calStickQty($bid, $insentive['0']['incentive']);
            }

            $repotArr[$did][$bgid]['incentive'] = $insentive['0']['incentive'];
        }
        //pr($repotArr);


        foreach($depots_Arr as $key => $val){
            if (isset($repotArr[$val['Depot']['id']])){
                $depots_Arr[$key]['CurrentStock'] = $repotArr[$val['Depot']['id']];
            }else{
                $depots_Arr[$key]['CurrentStock'] = array();
            }
        }

        //pr($depots_Arr);exit;

    }
	public function runRate(&$depots_Arr,$params){
        //pr($params);
        foreach($depots_Arr as $depot){
            $depotIds[] = $depot['Depot']['id'];
        }


        $date = $params['date'];
        $result = array();

        $rsC = $siC = $soC = $sC = $fiC = $iC = '';

        $brandCond = '';
        $brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';

        switch($params['type']){
            case 'daily':
                $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']));
                $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']);

                $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'];
                }
                $sC = $fiC = " AND sale_date between '$d1' AND '$d2'";
                $iC = " AND incentive_date between '$d1' AND '$d2'";
                $refDate = $d1;
                break;
        }

        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";

        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
            $brandCond = 'AND brand_group_id='.$params['query']['br'];
        }


        // Check null depot id
        $ndpC = "AND s.depot_id != 0";
        $nrdpC = "AND rs.depot_id != 0";

        $result['total_sale'] = $result['total_rsale'] = 0;

        //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 GROUP BY depot_id, brand_group_id" );
        foreach ($sales  as $sale){
            $did = $sale['Sale']['depot_id'];
            $bid = $sale['Sale']['brand_id'];
            $bgid = $sale['Sale']['brand_group_id'];

            if(!empty($sale['0']['sales'])){
                $sale['0']['sales'] = $this->calStickQty($bid, $sale['0']['sales']);
            }

            $repotArr[$did][$bgid]['sales'] = $sale['0']['sales'];
        }

        //RD sold items
        $rdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS rdSales FROM redistributor_saleitems AS rdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
        foreach ($rdSales  as $sale){
            $did = $sale['rdSale']['depot_id'];
            $bid = $sale['rdSale']['brand_id'];
            $bgid = $sale['rdSale']['brand_group_id'];

            if(!empty($sale['0']['rdSales'])){
                $sale['0']['rdSales'] = $this->calStickQty($bid, $sale['0']['rdSales']);
            }

            $repotArr[$did][$bgid]['rdSales'] = $sale['0']['rdSales'];
        }

        //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 GROUP BY depot_id, brand_group_id" );
        foreach ($consumerSales  as $sale){
            $did = $sale['ConsumerSale']['depot_id'];
            $bid = $sale['ConsumerSale']['brand_id'];
            $bgid = $sale['ConsumerSale']['brand_group_id'];
            if(!empty($sale['0']['consumerSales'])){
                $sale['0']['consumerSales'] = $this->calStickQty($bid, $sale['0']['consumerSales']);
            }

            $repotArr[$did][$bgid]['consumerSales'] = $sale['0']['consumerSales'];
        }

        //consumer RD sold items
        $consumerRdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerRdSales FROM consumer_redistribution_sales AS ConsumerRdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
        foreach ($consumerRdSales  as $sale){
            $did = $sale['ConsumerRdSale']['depot_id'];
            $bid = $sale['ConsumerRdSale']['brand_id'];
            $bgid = $sale['ConsumerRdSale']['brand_group_id'];
            if(!empty($sale['0']['consumerRdSales'])){
                $sale['0']['consumerRdSales'] = $this->calStickQty($bid, $sale['0']['consumerRdSales']);
            }

            $repotArr[$did][$bgid]['consumerRdSales'] = $sale['0']['consumerRdSales'];
        }


        //pr($repotArr);


        foreach($depots_Arr as $key => $val){
            if (isset($repotArr[$val['Depot']['id']])){
                $depots_Arr[$key]['RunRate'] = $repotArr[$val['Depot']['id']];
            }else{
                $depots_Arr[$key]['RunRate'] = array();
            }
        }

        //pr($depots_Arr);exit;

    }
	
	/*----------------------------------------
     * Function totalSalesAnalysis
     * Description Here,
     * CMonth = Current Month
     * LM = Last Month, L3M = Last 3 months, L6M = Last 6 months, L12M = Last 6 months, SPLY  = Same period last year
     * ----------------------------------------**/
    public function totalSalesAnalysis(&$depots_Arr,$params){
        //pr($params);
        foreach($depots_Arr as $depot){
            $depotIds[] = $depot['Depot']['id'];
        }


        $date = $params['date'];

        $rsC = $siC = $soC = $sC = $fiC = $iC = '';

        $brandCond = '';
        $brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';


        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";

        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
            $brandCond = 'AND brand_group_id='.$params['query']['br'];
        }


        $repotArr = array();

        $lastRefMonthArr = $params['lastRefMonthArr'];
        /*-------------------------------FOR Current and LAST REF MONTH--------------------------------*/

        foreach ($lastRefMonthArr as $refMonth){ //pr($refMonth);

            switch($refMonth) {
                case 'CMonth':
                    $d = date('Y-m', $params['strtotime']);

                    $sC = $fiC = " AND sale_date LIKE '$d%'";
                    $iC = " AND incentive_date LIKE '$d%'";
                    break;
                case 'LM':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 1 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 1 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'L3M':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 3 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 3 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'L6M':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 6 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 6 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'L12M':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 12 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 12 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'SPLY':
                    $d =  date('Y-m', strtotime('-1 year', $params['strtotime']));
                    $sC = $fiC = " AND sale_date LIKE '$d%'";
                    $iC = " AND incentive_date LIKE '$d%'";

                    break;
            }
            //sold items
            $sales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS sales , count(Distinct order_id) as saleCount FROM sales AS Sale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );


            foreach ($sales  as $sale){
                $did = $sale['Sale']['depot_id'];
                $bid = $sale['Sale']['brand_id'];
                $bgid = $sale['Sale']['brand_group_id'];

                if(!empty($sale['0']['sales'])){
                    $sale['0']['sales'] = $this->calStickQty($bid, $sale['0']['sales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'sales'] = $sale['0']['sales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'salesCount'] = $sale['0']['saleCount'];
            }

            //RD sold items
            $rdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS rdSales, count(Distinct redistributor_sale_id) as rdSalesCount FROM redistributor_saleitems AS rdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($rdSales  as $sale){
                $did = $sale['rdSale']['depot_id'];
                $bid = $sale['rdSale']['brand_id'];
                $bgid = $sale['rdSale']['brand_group_id'];

                if(!empty($sale['0']['rdSales'])){
                    $sale['0']['rdSales'] = $this->calStickQty($bid, $sale['0']['rdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'rdSales'] = $sale['0']['rdSales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'rdSalesCount'] = $sale['0']['rdSalesCount'];
            }

            //consumer sold items
            $consumerSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerSales,count(Distinct consumer_order_id) as consumerSalesCount FROM consumer_sales AS ConsumerSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerSales  as $sale){
                $did = $sale['ConsumerSale']['depot_id'];
                $bid = $sale['ConsumerSale']['brand_id'];
                $bgid = $sale['ConsumerSale']['brand_group_id'];
                if(!empty($sale['0']['consumerSales'])){
                    $sale['0']['consumerSales'] = $this->calStickQty($bid, $sale['0']['consumerSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerSales'] = $sale['0']['consumerSales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'consumerSalesCount'] = $sale['0']['consumerSalesCount'];

            }

            //consumer RD sold items
            $consumerRdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerRdSales,count(Distinct consumer_redistribution_order_id) as consumerRdSalesCount FROM consumer_redistribution_sales AS ConsumerRdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerRdSales  as $sale){
                $did = $sale['ConsumerRdSale']['depot_id'];
                $bid = $sale['ConsumerRdSale']['brand_id'];
                $bgid = $sale['ConsumerRdSale']['brand_group_id'];
                if(!empty($sale['0']['consumerRdSales'])){
                    $sale['0']['consumerRdSales'] = $this->calStickQty($bid, $sale['0']['consumerRdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerRdSales'] = $sale['0']['consumerRdSales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'consumerRdSalesCount'] = $sale['0']['consumerRdSalesCount'];
            }

            //Free items
            $freeItems = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS freeItems,count(Distinct order_id) as freeItemsCount 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_id'];
                $bgid = $freeItem['FreeItem']['brand_group_id'];
                if(!empty($freeItem['0']['freeItems'])){
                    $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'freeItems'] = $freeItem['0']['freeItems'];
                $repotArr[$did][$bgid][$refMonth.'_'.'freeItemsCount'] = $freeItem['0']['freeItemsCount'];
            }

            //Consumer Free items
            $consumerFreeItems = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS freeItems,count(Distinct consumer_order_id) as freeItemsCount 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_id'];
                $bgid = $freeItem['FreeItem']['brand_group_id'];

                if(!empty($freeItem['0']['freeItems'])){
                    $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerFreeItems'] = $freeItem['0']['freeItems'];
                $repotArr[$did][$bgid][$refMonth.'_'.'consumerFreeItemsCount'] = $freeItem['0']['freeItemsCount'];
            }



            //insentives
            $insentives = $this->query( "SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS incentive,count(id) as incentiveCount FROM incentives as Incentive WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $iC GROUP BY depot_id,brand_group_id" );
            foreach ($insentives  as $insentive){
                $did = $insentive['Incentive']['depot_id'];
                $bid = $insentive['Incentive']['brand_id'];
                $bgid = $insentive['Incentive']['brand_group_id'];

                if(!empty($insentive['0']['incentive'])){
                    $insentive['0']['incentive'] = $this->calStickQty($bid, $insentive['0']['incentive']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'incentive'] = $insentive['0']['incentive'];
                $repotArr[$did][$bgid][$refMonth.'_'.'incentiveCount'] = $insentive['0']['incentiveCount'];
            }
        }

        //pr($repotArr);


        foreach($depots_Arr as $key => $val){
            if (isset($repotArr[$val['Depot']['id']])){
                $depots_Arr[$key]['SaleAnalysis'] = $repotArr[$val['Depot']['id']];
            }else{
                $depots_Arr[$key]['SaleAnalysis'] = array();
            }
        }

        //pr($depots_Arr);exit;

    }
	
	public function averageSalesAnalysis(&$depots_Arr,$params){
        //pr($params);
        foreach($depots_Arr as $depot){
            $depotIds[] = $depot['Depot']['id'];
        }


        $date = $params['date'];

        $rsC = $siC = $soC = $sC = $fiC = $iC = '';

        $brandCond = '';
        $brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';


        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";

        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
            $brandCond = 'AND brand_group_id='.$params['query']['br'];
        }


        $repotArr = array();

        $lastRefMonthArr = $params['lastRefMonthArr'];
        /*-------------------------------FOR Current and LAST REF MONTH--------------------------------*/

        foreach ($lastRefMonthArr as $refMonth){ //pr($refMonth);

            switch($refMonth) {
                case 'CMonth':
                    $d = date('Y-m', $params['strtotime']);

                    $sC = $fiC = " AND sale_date LIKE '$d%'";
                    $iC = " AND incentive_date LIKE '$d%'";
                    break;
                case 'LM':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 1 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 1 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'L3M':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 3 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 3 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'L6M':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 6 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 6 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'L12M':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 12 MONTH)  AND sale_date <  '$d' ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 12 MONTH)  AND incentive_date <  '$d' ";
                    break;
                case 'SPLY':
                    $d =  date('Y-m', strtotime('-1 year', $params['strtotime']));
                    $sC = $fiC = " AND sale_date LIKE '$d%'";
                    $iC = " AND incentive_date LIKE '$d%'";

                    break;
            }
            //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 GROUP BY depot_id, brand_group_id" );


            foreach ($sales  as $sale){
                $did = $sale['Sale']['depot_id'];
                $bid = $sale['Sale']['brand_id'];
                $bgid = $sale['Sale']['brand_group_id'];

                if(!empty($sale['0']['sales'])){
                    $sale['0']['sales'] = $this->calStickQty($bid, $sale['0']['sales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'sales'] = $sale['0']['sales'];
            }

            //RD sold items
            $rdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS rdSales FROM redistributor_saleitems AS rdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($rdSales  as $sale){
                $did = $sale['rdSale']['depot_id'];
                $bid = $sale['rdSale']['brand_id'];
                $bgid = $sale['rdSale']['brand_group_id'];

                if(!empty($sale['0']['rdSales'])){
                    $sale['0']['rdSales'] = $this->calStickQty($bid, $sale['0']['rdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'rdSales'] = $sale['0']['rdSales'];
            }

            //consumer sold items
            $consumerSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerSales,count(Distinct consumer_order_id) as consumerSalesCount FROM consumer_sales AS ConsumerSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerSales  as $sale){
                $did = $sale['ConsumerSale']['depot_id'];
                $bid = $sale['ConsumerSale']['brand_id'];
                $bgid = $sale['ConsumerSale']['brand_group_id'];
                if(!empty($sale['0']['consumerSales'])){
                    $sale['0']['consumerSales'] = $this->calStickQty($bid, $sale['0']['consumerSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerSales'] = $sale['0']['consumerSales'];

            }

            //consumer RD sold items
            $consumerRdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerRdSales FROM consumer_redistribution_sales AS ConsumerRdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerRdSales  as $sale){
                $did = $sale['ConsumerRdSale']['depot_id'];
                $bid = $sale['ConsumerRdSale']['brand_id'];
                $bgid = $sale['ConsumerRdSale']['brand_group_id'];
                if(!empty($sale['0']['consumerRdSales'])){
                    $sale['0']['consumerRdSales'] = $this->calStickQty($bid, $sale['0']['consumerRdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerRdSales'] = $sale['0']['consumerRdSales'];
            }
        /**
            //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_id'];
                $bgid = $freeItem['FreeItem']['brand_group_id'];
                if(!empty($freeItem['0']['freeItems'])){
                    $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'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_id'];
                $bgid = $freeItem['FreeItem']['brand_group_id'];

                if(!empty($freeItem['0']['freeItems'])){
                    $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerFreeItems'] = $freeItem['0']['freeItems'];
            }



            //insentives
            $insentives = $this->query( "SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS incentive FROM incentives as Incentive WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $iC GROUP BY depot_id,brand_group_id" );
            foreach ($insentives  as $insentive){
                $did = $insentive['Incentive']['depot_id'];
                $bid = $insentive['Incentive']['brand_id'];
                $bgid = $insentive['Incentive']['brand_group_id'];

                if(!empty($insentive['0']['incentive'])){
                    $insentive['0']['incentive'] = $this->calStickQty($bid, $insentive['0']['incentive']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'incentive'] = $insentive['0']['incentive'];
            }   **/
        }

        //pr($repotArr);


        foreach($depots_Arr as $key => $val){
            if (isset($repotArr[$val['Depot']['id']])){
                $depots_Arr[$key]['SaleAnalysis'] = $repotArr[$val['Depot']['id']];
            }else{
                $depots_Arr[$key]['SaleAnalysis'] = array();
            }
        }

        //pr($depots_Arr);exit;

    }
	
	 /*----------------------------------------
     * Function quarterlyTotalSalesAnalysis
     * Description Here,
     * LQ = Last quarter,  L2Q = Last 2 quarters, L3Q = Last 3 quarters, L4Q = Last 4 quarters , SPLY = Same period last year
     * ----------------------------------------**/
    public function quarterlyTotalSalesAnalysis(&$depots_Arr,$params){
        //pr($params);
        foreach($depots_Arr as $depot){
            $depotIds[] = $depot['Depot']['id'];
        }


        $date = $params['date'];

        $rsC = $siC = $soC = $sC = $fiC = $iC = '';

        $brandCond = '';
        $brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';


        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";

        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
            $brandCond = 'AND brand_group_id='.$params['query']['br'];
        }


        $repotArr = array();

        $lastRefMonthArr = $params['lastRefMonthArr'];
        /*-------------------------------FOR Current quarter and LAST REF quarter MONTHS--------------------------------*/

        foreach ($lastRefMonthArr as $refMonth){ //pr($refMonth);

            switch($refMonth) {
                case 'CQuarter':
                    $d = date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND sale_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND incentive_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";
                    break;
                case 'LQ':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 5 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 5 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'L2Q':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 8 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 8 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'L3Q':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 11 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 11 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'L4Q':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 14 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 14 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'SPLY':
                    $d =  date('Y-m-01', strtotime('-1 year', $params['strtotime']));
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND sale_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND incentive_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";

                    break;
            }
            //sold items
            $sales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS sales , count(Distinct order_id) as saleCount FROM sales AS Sale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );


            foreach ($sales  as $sale){
                $did = $sale['Sale']['depot_id'];
                $bid = $sale['Sale']['brand_id'];
                $bgid = $sale['Sale']['brand_group_id'];

                if(!empty($sale['0']['sales'])){
                    $sale['0']['sales'] = $this->calStickQty($bid, $sale['0']['sales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'sales'] = $sale['0']['sales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'salesCount'] = $sale['0']['saleCount'];
            }

            //RD sold items
            $rdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS rdSales, count(Distinct redistributor_sale_id) as rdSalesCount FROM redistributor_saleitems AS rdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($rdSales  as $sale){
                $did = $sale['rdSale']['depot_id'];
                $bid = $sale['rdSale']['brand_id'];
                $bgid = $sale['rdSale']['brand_group_id'];

                if(!empty($sale['0']['rdSales'])){
                    $sale['0']['rdSales'] = $this->calStickQty($bid, $sale['0']['rdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'rdSales'] = $sale['0']['rdSales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'rdSalesCount'] = $sale['0']['rdSalesCount'];
            }

            //consumer sold items
            $consumerSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerSales,count(Distinct consumer_order_id) as consumerSalesCount FROM consumer_sales AS ConsumerSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerSales  as $sale){
                $did = $sale['ConsumerSale']['depot_id'];
                $bid = $sale['ConsumerSale']['brand_id'];
                $bgid = $sale['ConsumerSale']['brand_group_id'];
                if(!empty($sale['0']['consumerSales'])){
                    $sale['0']['consumerSales'] = $this->calStickQty($bid, $sale['0']['consumerSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerSales'] = $sale['0']['consumerSales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'consumerSalesCount'] = $sale['0']['consumerSalesCount'];

            }

            //consumer RD sold items
            $consumerRdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerRdSales,count(Distinct consumer_redistribution_order_id) as consumerRdSalesCount FROM consumer_redistribution_sales AS ConsumerRdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerRdSales  as $sale){
                $did = $sale['ConsumerRdSale']['depot_id'];
                $bid = $sale['ConsumerRdSale']['brand_id'];
                $bgid = $sale['ConsumerRdSale']['brand_group_id'];
                if(!empty($sale['0']['consumerRdSales'])){
                    $sale['0']['consumerRdSales'] = $this->calStickQty($bid, $sale['0']['consumerRdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerRdSales'] = $sale['0']['consumerRdSales'];
                $repotArr[$did][$bgid][$refMonth.'_'.'consumerRdSalesCount'] = $sale['0']['consumerRdSalesCount'];
            }

            //Free items
            $freeItems = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS freeItems,count(Distinct order_id) as freeItemsCount 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_id'];
                $bgid = $freeItem['FreeItem']['brand_group_id'];
                if(!empty($freeItem['0']['freeItems'])){
                    $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'freeItems'] = $freeItem['0']['freeItems'];
                $repotArr[$did][$bgid][$refMonth.'_'.'freeItemsCount'] = $freeItem['0']['freeItemsCount'];
            }

            //Consumer Free items
            $consumerFreeItems = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS freeItems,count(Distinct consumer_order_id) as freeItemsCount 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_id'];
                $bgid = $freeItem['FreeItem']['brand_group_id'];

                if(!empty($freeItem['0']['freeItems'])){
                    $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerFreeItems'] = $freeItem['0']['freeItems'];
                $repotArr[$did][$bgid][$refMonth.'_'.'consumerFreeItemsCount'] = $freeItem['0']['freeItemsCount'];
            }



            //insentives
            $insentives = $this->query( "SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS incentive,count(id) as incentiveCount FROM incentives as Incentive WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $iC GROUP BY depot_id,brand_group_id" );
            foreach ($insentives  as $insentive){
                $did = $insentive['Incentive']['depot_id'];
                $bid = $insentive['Incentive']['brand_id'];
                $bgid = $insentive['Incentive']['brand_group_id'];

                if(!empty($insentive['0']['incentive'])){
                    $insentive['0']['incentive'] = $this->calStickQty($bid, $insentive['0']['incentive']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'incentive'] = $insentive['0']['incentive'];
                $repotArr[$did][$bgid][$refMonth.'_'.'incentiveCount'] = $insentive['0']['incentiveCount'];
            }
        }

        //pr($repotArr);


        foreach($depots_Arr as $key => $val){
            if (isset($repotArr[$val['Depot']['id']])){
                $depots_Arr[$key]['SaleAnalysis'] = $repotArr[$val['Depot']['id']];
            }else{
                $depots_Arr[$key]['SaleAnalysis'] = array();
            }
        }

        //pr($depots_Arr);exit;

    }

    public function quarterlyAverageSalesAnalysis(&$depots_Arr,$params){
        //pr($params);
        foreach($depots_Arr as $depot){
            $depotIds[] = $depot['Depot']['id'];
        }


        $date = $params['date'];

        $rsC = $siC = $soC = $sC = $fiC = $iC = '';

        $brandCond = '';
        $brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';


        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";

        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
            $brandCond = 'AND brand_group_id='.$params['query']['br'];
        }


        $repotArr = array();

        $lastRefMonthArr = $params['lastRefMonthArr'];
        /*-------------------------------FOR Current quarter and LAST REF quarter MONTHS--------------------------------*/

        foreach ($lastRefMonthArr as $refMonth){ //pr($refMonth);

            switch($refMonth) {
                case 'CQuarter':
                    $d = date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND sale_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND incentive_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";
                    break;
                case 'LQ':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 5 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 5 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'L2Q':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 8 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 8 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'L3Q':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 11 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 11 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'L4Q':
                    $d =  date('Y-m-01', $params['strtotime']);
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 14 MONTH)  AND sale_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 14 MONTH)  AND incentive_date <  DATE_SUB('$d', INTERVAL 2 MONTH) ";
                    break;
                case 'SPLY':
                    $d =  date('Y-m-01', strtotime('-1 year', $params['strtotime']));
                    $sC = $fiC = " AND sale_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND sale_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";
                    $iC = " AND incentive_date >= DATE_SUB('$d', INTERVAL 2 MONTH)  AND incentive_date <  DATE_ADD('$d', INTERVAL 1 MONTH) ";

                    break;
            }
            //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 GROUP BY depot_id, brand_group_id" );


            foreach ($sales  as $sale){
                $did = $sale['Sale']['depot_id'];
                $bid = $sale['Sale']['brand_id'];
                $bgid = $sale['Sale']['brand_group_id'];

                if(!empty($sale['0']['sales'])){
                    $sale['0']['sales'] = $this->calStickQty($bid, $sale['0']['sales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'sales'] = $sale['0']['sales'];
            }

            //RD sold items
            $rdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS rdSales FROM redistributor_saleitems AS rdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($rdSales  as $sale){
                $did = $sale['rdSale']['depot_id'];
                $bid = $sale['rdSale']['brand_id'];
                $bgid = $sale['rdSale']['brand_group_id'];

                if(!empty($sale['0']['rdSales'])){
                    $sale['0']['rdSales'] = $this->calStickQty($bid, $sale['0']['rdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'rdSales'] = $sale['0']['rdSales'];
            }

            //consumer sold items
            $consumerSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerSales,count(Distinct consumer_order_id) as consumerSalesCount FROM consumer_sales AS ConsumerSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerSales  as $sale){
                $did = $sale['ConsumerSale']['depot_id'];
                $bid = $sale['ConsumerSale']['brand_id'];
                $bgid = $sale['ConsumerSale']['brand_group_id'];
                if(!empty($sale['0']['consumerSales'])){
                    $sale['0']['consumerSales'] = $this->calStickQty($bid, $sale['0']['consumerSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerSales'] = $sale['0']['consumerSales'];

            }

            //consumer RD sold items
            $consumerRdSales = $this->query("SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS consumerRdSales FROM consumer_redistribution_sales AS ConsumerRdSale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $sC GROUP BY depot_id, brand_group_id" );
            foreach ($consumerRdSales  as $sale){
                $did = $sale['ConsumerRdSale']['depot_id'];
                $bid = $sale['ConsumerRdSale']['brand_id'];
                $bgid = $sale['ConsumerRdSale']['brand_group_id'];
                if(!empty($sale['0']['consumerRdSales'])){
                    $sale['0']['consumerRdSales'] = $this->calStickQty($bid, $sale['0']['consumerRdSales']);
                }

                $repotArr[$did][$bgid][$refMonth.'_'.'consumerRdSales'] = $sale['0']['consumerRdSales'];
            }
            /**
            //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_id'];
            $bgid = $freeItem['FreeItem']['brand_group_id'];
            if(!empty($freeItem['0']['freeItems'])){
            $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
            }

            $repotArr[$did][$bgid][$refMonth.'_'.'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_id'];
            $bgid = $freeItem['FreeItem']['brand_group_id'];

            if(!empty($freeItem['0']['freeItems'])){
            $freeItem['0']['freeItems'] = $this->calStickQty($bid, $freeItem['0']['freeItems']);
            }

            $repotArr[$did][$bgid][$refMonth.'_'.'consumerFreeItems'] = $freeItem['0']['freeItems'];
            }



            //insentives
            $insentives = $this->query( "SELECT depot_id, brand_id, brand_group_id, SUM(cal_qty) AS incentive FROM incentives as Incentive WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $iC GROUP BY depot_id,brand_group_id" );
            foreach ($insentives  as $insentive){
            $did = $insentive['Incentive']['depot_id'];
            $bid = $insentive['Incentive']['brand_id'];
            $bgid = $insentive['Incentive']['brand_group_id'];

            if(!empty($insentive['0']['incentive'])){
            $insentive['0']['incentive'] = $this->calStickQty($bid, $insentive['0']['incentive']);
            }

            $repotArr[$did][$bgid][$refMonth.'_'.'incentive'] = $insentive['0']['incentive'];
            }   **/
        }

        //pr($repotArr);


        foreach($depots_Arr as $key => $val){
            if (isset($repotArr[$val['Depot']['id']])){
                $depots_Arr[$key]['SaleAnalysis'] = $repotArr[$val['Depot']['id']];
            }else{
                $depots_Arr[$key]['SaleAnalysis'] = array();
            }
        }

        //pr($depots_Arr);exit;

    }
	public function availabilityReport($params){
        //pr($params);
        $date = $params['date'];
        $result = array();
        switch($params['type']){
            case 'daily':
                $sC = "s.sale_date = '".$date."'";
                $rdC = "rs.sale_date = '".$date."'";
                $cvC = "cv.visiting_date LIKE '".$date."%'";
                $bscC = "bsc.check_date = '".$date."'";
                break;
            case 'weekly':
                $d1 = date('Y-m-d', $params['strtotime']);
                $d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));

                $sC = "s.sale_date between '$d1' AND '$d2'";
                $rdC = "rs.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
                $bscC = "bsc.check_date between '$d1' AND '$d2 23:59:59'";

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

                $sC = "s.sale_date between '$d1' AND '$d2'";
                $rdC = "rs.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
                $bscC = "bsc.check_date between '$d1' AND '$d2 23:59:59'";
                break;
            case 'monthly':
                $d1 = date('Y-m-01', $params['strtotime']);
                $d2 = date('Y-m-t', $params['strtotime']);

                $sC = "s.sale_date between '$d1' AND '$d2'";

                $rdC = "rs.sale_date between '$d1' AND '$d2'";
                $cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";
                $bscC = "bsc.check_date between '$d1' AND '$d2 23:59:59'";

                break;
        }

        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";
        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
        }


        // Check null depot id
        $ndpC = "AND s.depot_id != 0";
        $nrdpC = "AND rs.depot_id != 0";
        $nbscdpC = "AND bsc.depot_id != 0";

        $result['total_sale'] = $result['total_rsale'] = 0;

        /** SALE **/

        $saleCnt = "SELECT d.region_id, s.depot_id, s.brand_group_id, s.brand_id,count(Distinct s.order_id) as total_cnt FROM sales s LEFT JOIN customers c ON c.id = s.customer_id LEFT JOIN depots d ON s.depot_id = d.id WHERE $sC $dv_regC $rgC $dpC $ndpC $bsC GROUP BY s.depot_id, s.brand_group_id, s.brand_id";
        $sales = $this->query($saleCnt);
        //pr($sales);exit;

        foreach($sales as $sale){
            $bgid = $sale['s']['brand_group_id'];
            $bid = $sale['s']['brand_id'];
            $did = $sale['s']['depot_id'];
            //pr($result['total_sale']);

            /*Sale Count*/
            if(!empty($sale[0]['total_cnt'])){
                $result['total_sale'] += $sale[0]['total_cnt'];
            }

            if(!empty($result[0][$did][0][$bgid]['sales'])){
                $result[0][$did][0][$bgid]['sales'] += $sale[0]['total_cnt'];
            }else{
                $result[0][$did][0][$bgid]['sales'] = $sale[0]['total_cnt'];
            }


        }

        /** RSALE **/
        $rsaleCnt = "SELECT d.region_id, rs.depot_id, rs.brand_id, rs.brand_group_id,  count(Distinct redistributor_sale_id) AS total_cnt FROM redistributor_saleitems AS rs LEFT JOIN customers c ON c.id = rs.customer_id LEFT JOIN depots d ON rs.depot_id = d.id WHERE $rdC $dv_regC $rgC $dpC $nrdpC $brdC GROUP BY rs.depot_id, rs.brand_group_id, rs.brand_id";
        $rsales = $this->query($rsaleCnt);

        foreach($rsales as $sale){
            $bgid = $sale['rs']['brand_group_id'];
            $bid = $sale['rs']['brand_id'];
            $did = $sale['rs']['depot_id'];

            /*RSale Count*/
            if(!empty($sale[0]['total_cnt'])){
                $result['total_rsale'] += $sale[0]['total_cnt'];
            }

            if(!empty($result[0][$did][0][$bgid]['rsales'])){
                $result[0][$did][0][$bgid]['rsales'] += $sale[0]['total_cnt'];
            }else{
                $result[0][$did][0][$bgid]['rsales'] = $sale[0]['total_cnt'];
            }

        }
        //pr($result);
        /** CUSTOMER VISIT **/
        $visitCount = $this->query("SELECT c.depot_id , count(Distinct cv.customer_id) as cvcustoCount FROM customer_visits cv LEFT JOIN customers c ON c.id = cv.customer_id LEFT JOIN depots d ON c.depot_id = d.id WHERE $cvC $dv_regC $rgC $dpC GROUP BY c.depot_id");
        //pr($visitCount);exit;

        foreach($visitCount as $visit){
            $did = $visit['c']['depot_id'];
            $visitCount = $visit['0']['cvcustoCount'];
            if($did){

                if(empty($result[0][$did][0])){
                    $result[0][$did][0] = array(); // No sales and No Rsales for this depot
                }

                if(isset($result[0][$did]['total_visit'])){
                    $result[0][$did]['total_visit'] = $result[$did]['total_visit'] + $visitCount;
                }else{
                    $result[0][$did]['total_visit'] = $visitCount;
                }

            }
        }


        /**Available Brand - checklists**/
        $brandAvailableCnt = "SELECT d.region_id, bsc.depot_id,bsc.customer_id, 
            SUM(IF(bsc.yes_available = 'Yes', 1, 0)) AS yes_available_cnt,
            SUM(IF(bsc.pf_available = 'Yes', 1, 0)) AS pf_available_cnt,
            SUM(IF(bsc.as_available = 'Yes', 1, 0)) AS as_available_cnt,
            SUM(IF(bsc.asm_available = 'Yes', 1, 0)) AS asm_available_cnt
              FROM bikesupervisor_checklists AS bsc LEFT JOIN customers c ON c.id = bsc.customer_id LEFT JOIN depots d ON bsc.depot_id = d.id WHERE $bscC $dv_regC $rgC $dpC $nbscdpC GROUP BY bsc.depot_id";
        $brandAvailables = $this->query($brandAvailableCnt);

        //pr($brandAvailables);exit;

        foreach($brandAvailables as $brandAvailable){
            $did = $brandAvailable['bsc']['depot_id'];
            /**index values of brand_available_cnt  are brand group ids**/
            $brand_available_cnt[1] = $brandAvailable['0']['yes_available_cnt'];
            $brand_available_cnt[2] = $brandAvailable['0']['pf_available_cnt'];
            $brand_available_cnt[3] = $brandAvailable['0']['as_available_cnt'];
            $brand_available_cnt[4] = $brandAvailable['0']['asm_available_cnt'];
            if (!empty($params['query']['br'])){
                $availableAllCount = $brand_available_cnt[$params['query']['br']];
            }else{
                $availableAllCount = $brand_available_cnt[1]+$brand_available_cnt[2]+$brand_available_cnt[3]+$brand_available_cnt[4];
            }
            if($did){

                if(empty($result[0][$did][0])){
                    $result[0][$did][0] = array(); // No sales and No Rsales for this depot
                }

                if(isset($result[0][$did]['total_brand_available_cnt'])){
                    $result[0][$did]['total_brand_available_cnt'] = $result[$did]['total_brand_available_cnt'] + $availableAllCount;
                }else{
                    $result[0][$did]['total_brand_available_cnt'] = $availableAllCount;
                }

                if (!empty($params['query']['br'])){
                    $result[0][$did][0][$params['query']['br']]['brand_available_cnt'] = $brand_available_cnt[$params['query']['br']];
                }else{
                    $result[0][$did][0][1]['brand_available_cnt'] = $brand_available_cnt[1];  /*YES*/
                    $result[0][$did][0][2]['brand_available_cnt'] = $brand_available_cnt[2];  /*PETERFIELD*/
                    $result[0][$did][0][3]['brand_available_cnt'] = $brand_available_cnt[3];  /*ALLSTARS*/
                    $result[0][$did][0][4]['brand_available_cnt'] = $brand_available_cnt[4]; /*ALLSTARS MENTOL*/
                }




            }

        }
        //pr($result);exit;

        //set region_id and division_id

        if(!empty($result['0']) && count($result['0'])>0){
            $dIds = implode(',', array_keys($result['0']));

            $dRegDiv = $this->query("SELECT d.id as depot_id ,d.region_id as region_id,dr.division_id as division_id FROM depots d INNER JOIN divisions_regions dr ON d.region_id = dr.region_id AND d.id IN($dIds)");
            $dRegDivArray = array();
            foreach($dRegDiv as $dData){
                $dRegDivArray[$dData['d']['depot_id']]['region_id'] = $dData['d']['region_id'];
                $dRegDivArray[$dData['d']['depot_id']]['division_id'] = $dData['dr']['division_id'];
            }

            foreach($result['0'] as $did => $brand_result){
                if(empty($result[0][$did]['region_id'])){
                    $result[0][$did]['region_id'] = $dRegDivArray[$did]['region_id'] ;
                }
                if(empty($result[0][$did]['division_id'])){
                    $result[0][$did]['division_id'] = $dRegDivArray[$did]['division_id'] ;
                }
            }
        }
        //pr($result);exit;
        return $result;

    }
	
	public function depotSalesDetails(&$depots_Arr,$params){
        //pr($params);
        foreach($depots_Arr as $depot){
            $depotIds[] = $depot['Depot']['id'];
        }


        $date = $params['date'];
        $result = array();

        $rsC = $siC = $soC = $sC = $fiC = $iC = '';

        $brandCond = '';
        $brandGroupCond = 'AND brand_group_id IS NOT NULL AND brand_group_id != 0';
        $unitCond = '';

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

        $dv_regC = $rgC = $dpC = $bsC = $brsC = $brdC =  "";

        if (!empty($params['query']['dv'])){
            if (empty($params['query']['rg'])) {
                $divisionRegion = ClassRegistry::init('DivisionsRegion');
                $rIds = $divisionRegion->find('list', array('fields' => array('region_id'), 'conditions' => array('division_id' => $params['query']['dv'])));
                $rIds = implode(",", $rIds); //pr($rIds);exit;
                $dv_regC = " AND d.region_id IN ( " . $rIds . ") ";
            }

        }
        if (!empty($params['query']['rg'])){
            $rgC = " AND d.region_id = ".$params['query']['rg'];
        }
        if (!empty($params['query']['dp'])){
            $dpC = " AND d.id = ".$params['query']['dp'];
        }

        if (!empty($params['query']['br'])){
            $bsC = " AND s.brand_group_id = ".$params['query']['br'];
            $brdC = " AND rs.brand_group_id = ".$params['query']['br'];
            $brandCond = 'AND brand_group_id='.$params['query']['br'];
        }

        if ( !empty($params['query']['unt'])){
            $unitCond =" AND unit = '".$params['query']['unt']."'";
        }


        // Check null depot id
        $ndpC = "AND s.depot_id != 0";
        $nrdpC = "AND rs.depot_id != 0";

        $result['total_sale'] = $result['total_rsale'] = 0;


        //Sale
        $sales = $this->query("SELECT depot_id, brand_id, brand_group_id,unit,unit_price, SUM(quantity) AS quantities, SUM(amount) AS amounts FROM sales AS Sale WHERE depot_id IN (".implode(",", $depotIds).") $brandGroupCond $brandCond $unitCond $sC GROUP BY depot_id, brand_group_id,unit" );

        foreach ($sales  as $sale){
            $did = $sale['Sale']['depot_id'];
            $bid = $sale['Sale']['brand_id'];
            $bgid = $sale['Sale']['brand_group_id'];
            $unit = $sale['Sale']['unit'];



            $repotArr[$did][$bgid][$unit]['unit_price'] = $sale['Sale']['unit_price'];
            $repotArr[$did][$bgid][$unit]['quantity'] = $sale['0']['quantities'];
            $repotArr[$did][$bgid][$unit]['amount'] = $sale['0']['amounts'];
        }




        foreach($depots_Arr as $key => $val){
            if (isset($repotArr[$val['Depot']['id']])){
                $depots_Arr[$key]['SaleDetails'] = $repotArr[$val['Depot']['id']];
            }else{
                $depots_Arr[$key]['SaleDetails'] = array();
            }
        }

        //pr($depots_Arr);exit;

    }
}
