<?php

App::uses('Model', 'Model');

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

class Consumer extends AppModel {
    
    var $virtualFields = array(
		'name' => "CONCAT(Consumer.first_name,' ', Consumer.last_name)"
	);
    
	public $belongsTo  = array(
		'State' => array('className' => 'State', 'foreignKey' => 'state_id', 'fields' => array('id', 'state'), 'type'=>'left'), 
		'LgArea' => array('className' => 'LgArea', 'foreignKey' => 'lg_area_id', 'fields' => array('id', 'name'), 'type'=>'left'), 
		'Division' => array('className' => 'Division', 'foreignKey' => 'division_id', 'fields' => array('id', 'title'), 'type'=>'left'),
		'Region' => array('className' => 'Region', 'foreignKey' => 'region_id', 'fields' => array('id', 'title'), 'type'=>'left'),
		'Depot' => array('className' => 'Depot', 'foreignKey' => 'depot_id', 'fields' => array('id', 'title','color_code'), 'type'=>'left'),
		'User' => array('className' => 'User', 'foreignKey' => 'user_id', 'fields' => array('id', 'first_name', 'last_name'), 'type'=>'inner'),
		'File' => array('className' => 'File', 'foreignKey' => 'file_id', 'fields' => array('id', 'name'), 'type'=>'left'),
		'Route' => array('className' => 'Route', 'foreignKey' => 'route_id','fields' => array('id','title'),'type'=>'left')
	);
	
	public function consumerList($depot_id=0, $user=null, $isService=false, $params = null){
		$conditions = 1; // array();
		$joinsStr = "";
		
		$day =date('D', strtotime(date('Y-m-d'))); 
		/* $d = weekOfMonth(date('Y-m-d')); 
		
		
		$lastDayPrev = date('Y-m-d', strtotime('last day of previous month')); //pr($lastDayPrev);
		$dPrev = weekOfMonth($lastDayPrev); //pr($dPrev);
		
		
		 
		$firstday =date('D', strtotime(date('Y-m-01'))); //pr($firstday);
		if($dPrev == 5 && $firstday == 'Sun'){
			$d += 1;
		}elseif($dPrev == 6 && $firstday == 'Sun'){
			$d += 2;
		} 
		
		
		if ($d == 5) {
			$week = 1;
		  } elseif ($d == 6) {
			 $week = 2; 
		  } else {
			 $week = $d; 
		  } */
		  
		$week = getY3Week();
		
		//pr('CRM week no. - '.$week);
		  
		//pr($depot_id);
		if ($depot_id){
			//$conditions['Customer.depot_id'] = $depot_id;
			$conditions = "Consumer.depot_id  = $depot_id";
		}else if ($user){
		switch($user['Role']['grade']){
				
		/*		case 3:
					
					$joinsStr = " LEFT JOIN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].") AS CustomerAssign ON Customer.id = CustomerAssign.customer_id ";
					
					//$conditions = "Customer.region_id  = ".$user['region_id']." OR Customer.user_id = ".$user['id']." OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].")";
					$conditions = "(Customer.region_id  = ".$user['region_id']." OR Customer.user_id = ".$user['id']." OR CustomerAssign.customer_id IS NOT NULL)";
				break;
				case 4:
					
					$joinsStr = " LEFT JOIN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].") AS CustomerAssign ON Customer.id = CustomerAssign.customer_id ";
					
					//$conditions = "Customer.user_id  = ".$user['id']." OR CustomerAssign.customer_id IS NOT NULL";
					
					$conditions = "(Customer.depot_id  = ".$user['depot_id']." OR Customer.user_id = ".$user['id']." OR CustomerAssign.customer_id IS NOT NULL)";
				break;
				case 5:
					//$conditions = "Customer.user_id  = ".$user['id']." OR Customer.user_id IN (SELECT uid2 FROM user_relations WHERE user_id = ".$user['id'].") OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].")";
					
					$joinsStr = " LEFT JOIN (SELECT uid2 FROM user_relations WHERE user_id = ".$user['id'].") AS UserRelation ON Customer.user_id = UserRelation.uid2 LEFT JOIN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].") AS CustomerAssign ON Customer.id = CustomerAssign.customer_id ";
					
					$conditions = "(Customer.user_id  = ".$user['id']." OR UserRelation.uid2 IS NOT NULL OR CustomerAssign.customer_id IS NOT NULL )"; 
					
					
				break;
				case 6:
					
					$joinsStr = " LEFT JOIN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].") AS CustomerAssign ON Customer.id = CustomerAssign.customer_id ";
					
					$conditions = "(Customer.user_id  = ".$user['id']." OR CustomerAssign.customer_id IS NOT NULL)";
					
				break; */
				
				case 2 && $user['Role']['id'] == DIVISIONMANAGER:
				case 3:
				case 4:
				case 5:
				case 6:
				    $joinsStr = " INNER JOIN (SELECT route_id FROM user_routes WHERE user_id = ".$user['id']."  AND week = '$week' AND day LIKE '$day%') AS UserRoute ON Consumer.route_id = UserRoute.route_id";
				    
				    break;
				
			} 
		}
		//	$weekDay = date('0w');
		//	$joinsStr = " INNER JOIN (SELECT route_id FROM user_routes WHERE user_id = ".$user['id']."  AND week = '$weekDay') AS UserRoute ON Customer.route_id = UserRoute.route_id";

		
	
	
		if (!empty($user['depot_id']) && $user['Role']['grade'] > 3){
			$conditions .= " AND Consumer.depot_id  = ".$user['depot_id'];
		}
		//$customers = $this->find('all', array('fields' => array('Customer.id', 'CONCAT_WS(" ", Customer.first_name, Customer.last_name) as name'), 'conditions'=>$conditions, 'order'=>array('name'=>'asc')));
		//echo 'SELECT Customer.id, CONCAT_WS(" ", Customer.first_name, Customer.last_name) as name, Customer.depot_id FROM customers AS Customer'.$joinsStr.' WHERE '.$conditions.' ORDER BY name asc';
		$limit = "";
		if ($isService){
			//$limit = ' LIMIT 2000';
			if($params){
				if(!empty($params['minConsumerId']) && !empty($params['maxCustomerId'])){
					$conditions .= " AND ( Consumer.id < ".$params['minConsumerId']." OR  Consumer.id > ".$params['maxConsumerId'] . ")";
				}else if(!empty($params['minConsumerId']) && $params['minConsumerId'] > 0){
					$conditions .= " AND Consumer.id < ".$params['minCustomerId'];
				} else if(!empty($params['maxConsumerId']) && $params['maxConsumerId'] > 0){
					$conditions .= " AND Consumer.id > ".$params['maxCustomerId'];
				}
			}
			$qryString = 'SELECT Consumer.id, CONCAT_WS(" ", Consumer.first_name, Consumer.last_name) as name, Consumer.depot_id FROM consumers AS Consumer'.$joinsStr.' WHERE '.$conditions.' ORDER BY id desc LIMIT 1000';
		}else{
			$qryString = 'SELECT Consumer.id, CONCAT_WS(" ", Consumer.first_name, Consumer.last_name) as name, Consumer.depot_id FROM consumers AS Consumer'.$joinsStr.' WHERE '.$conditions.' ORDER BY name asc, created desc';
		}
		$consumers = $this->query($qryString);
		$list = array();
		$listArr = array();
		$jsonList = array();
		//pr($customers);
		//die();
		if($consumers){
			foreach($consumers as $consumer){
				$list[$consumer['Consumer']['id']] = $consumer['0']['name'];
				$listArr[] = array('id'=> $consumer['Consumer']['id'], 'depot_id'=>$consumer['Consumer']['depot_id'], 'name'=>$consumer['0']['name']);
				$jsonList[] = array("id"=>$consumer['Consumer']['id'], 'value'=> $consumer['0']['name']);
			}
		}
		//pr($list);
		if ($isService){
			if (!$list){
				$list = null;
			}
			return array('consumers'=>array(), 'consumersList'=>$jsonList);
		}else{
			return $list;
		}
	}
	
	public function consumerListByRole($user, $isService = false){
		return $this->consumerList(0, $user, $isService);
	}
	
	public function moreConsumerListByRole($user, $params){
		return $this->consumerList(0, $user, true, $params);
	}
	
	public function _userCondition(&$conditions, $user){
		switch($user['Role']['grade']){
			case 3:
				$conditions[] = '(Consumer.user_id IN (SELECT id FROM users WHERE region_id = '.$user['region_id'].') OR Consumer.id IN (SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].'))';
			break;
			case 4:
				$conditions[] = '(Consumer.user_id IN (SELECT id FROM users WHERE depot_id = '.$user['depot_id'].') OR Consumer.id IN (SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].'))';
			break;
			case 5:
			if(isset($conditions['OR'])){
				$conditions[]['OR'] = array('Consumer.user_id'=> $user['id'], 'Consumer.user_id IN (SELECT uid2 FROM user_relations WHERE user_id = '.$user['id'].') OR Consumer.id IN (SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].') ');
			}else{
				$conditions['OR'] = array('Consumer.user_id'=> $user['id'], 'Consumer.user_id IN (SELECT uid2 FROM user_relations WHERE user_id = '.$user['id'].') OR Consumer.id IN (SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].') ');
			}
			break;
			
			case 6:
				$conditions['Consumer.user_id'] = $user['id']." OR Consumer.id IN (SELECT consumer_id FROM consumer_assigns WHERE user_id = ".$user['id'].")";
			break;
			
		}
	}
	
	public function _userRoleCondition(&$conditions, $user, &$paginate){
		
		switch($user['Role']['grade']){
			case 2:
                if($user['role_id'] == DIVISIONMANAGER){
                    $conditions['Consumer.division_id'] = $user['division_id'];
                }
                break;
			case 3:
				//$joinsStr = " LEFT JOIN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].") AS CustomerAssign ON Customer.id = CustomerAssign.customer_id ";
				
				//$paginate['joins'][] = array('table' => 'customer_assigns', 'type'=>'left', 'alias'=>'CustomerAssign', 'conditions'=>array('Customer.id=Order.customer_id '.$oC));
				$paginate['joins'][] = array('table' => '(SELECT id FROM users WHERE region_id = '.$user['region_id'].')', 'type'=>'left', 'alias'=>'User2', 'conditions'=>array('Consumer.user_id = User2.id'));
				
				$paginate['joins'][] = array('table' => '(SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'ConsumerAssign', 'conditions'=>array('Consumer.id = ConsumerAssign.consumer_id'));
				//$conditions[] = 'Customer.user_id IN (SELECT id FROM users WHERE region_id = '.$user['region_id'].') OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].')';
				if(isset($conditions['OR'])){
					$conditions[]['OR'] = array('User2.id IS NOT NULL', 'ConsumerAssign.consumer_id IS NOT NULL');
				}else{
					$conditions['OR'] = array('User2.id IS NOT NULL', 'ConsumerAssign.consumer_id IS NOT NULL');
				}
			break;
			case 4:
				//$paginate['joins'][] = array('table' => '(SELECT id FROM users WHERE depot_id = '.$user['depot_id'].')', 'type'=>'left', 'alias'=>'User2', 'conditions'=>array('Customer.user_id = User2.id'));
				
				$paginate['joins'][] = array('table' => '(SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'ConsumerAssign', 'conditions'=>array('Consumer.id = ConsumerAssign.consumer_id'));
				
				//$conditions[] = 'Customer.user_id IN (SELECT id FROM users WHERE depot_id = '.$user['depot_id'].') OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].')';
				if(isset($conditions['OR']) ){
					$conditions[]['OR']= array('Consumer.depot_id'=> $user['depot_id'], 'ConsumerAssign.consumer_id IS NOT NULL');
				}else{
					$conditions['OR'] = array('Consumer.depot_id'=> $user['depot_id'], 'ConsumerAssign.consumer_id IS NOT NULL');
				}
			break;
			case 5:
								
				$paginate['joins'][] = array('table' => '(SELECT uid2 FROM user_relations WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'UserRelation', 'conditions'=>array('Consumer.user_id = UserRelation.uid2'));
				
				$paginate['joins'][] = array('table' => '(SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'ConsumerAssign', 'conditions'=>array('Consumer.id = ConsumerAssign.consumer_id'));
				
				//$conditions['OR'] = array('Customer.user_id'=> $user['id'], 'Customer.user_id IN (SELECT uid2 FROM user_relations WHERE user_id = '.$user['id'].') OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].') ');
				if(isset($conditions['OR'])){
					$conditions[]['OR'] = array('Consumer.user_id'=> $user['id'], 'UserRelation.uid2 IS NOT NULL OR ConsumerAssign.consumer_id IS NOT NULL');
				}else{
					$conditions['OR'] = array('Consumer.user_id'=> $user['id'], 'UserRelation.uid2 IS NOT NULL OR ConsumerAssign.consumer_id IS NOT NULL');
				}
				
				//$conditions = "Customer.user_id  = ".$user['id']." OR UserRelation.uid2 IS NOT NULL OR CustomerAssign.customer_id IS NOT NULL";
			break;
			
			case 6:
				$paginate['joins'][] = array('table' => '(SELECT consumer_id FROM consumer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'ConsumerAssign', 'conditions'=>array('Consumer.id = ConsumerAssign.consumer_id'));
				if(isset($conditions['OR'])){
					$conditions[]['OR'] = array('Consumer.user_id'=> $user['id'], 'ConsumerAssign.consumer_id IS NOT NULL' );
				}else{
					$conditions['OR'] = array('Consumer.user_id'=> $user['id'], 'ConsumerAssign.consumer_id IS NOT NULL' );
				}
				
				//$conditions['Customer.user_id'] = $user['id']." OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].")";
			break;
			
		}
		
		if (!empty($user['depot_id']) && $user['Role']['grade'] > 3){
			$conditions['Consumer.depot_id'] = $user['depot_id'];
		}
	}
	
	function saleActivity(&$consumers, $params){
		$consumerIds = array();
		foreach($consumers as $consumer){
			$consumerIds[] = $consumer['Consumer']['id'];
		}
		
		
		$qry = "SELECT consumer_id, brand_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM sales WHERE sale_date LIKE '".$params['date']."%' AND consumer_id IN (".implode(",", $consumerIds).") GROUP BY consumer_id, brand_id  UNION
		SELECT consumer_id, brand_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '".$params['date']."%' AND consumer_id IN (".implode(",", $consumerIds).") GROUP BY consumer_id, brand_id";
			
		$sale_activity = $this->query($qry);
		$activity = $rdTotal = array();
		
		foreach($sale_activity as $sa){
			$cid = $sa['0']['consumer_id'];
			$bid = $sa['0']['brand_id'];
			if(isset($activity[$cid][$bid]['total_sale'])){
				$activity[$cid][$bid]['total_sale'] += $sa['0']['total_sale'];
			}else{
				$activity[$cid][$bid]['total_sale'] = $sa['0']['total_sale'];
			}
			
			if(isset($activity[$cid][$bid]['total_amount'])){
				$activity[$cid][$bid]['total_amount'] += $sa['0']['total_amount'];
			}else{
				$activity[$cid][$bid]['total_amount'] = $sa['0']['total_amount'];
			}
		}
		
		
		
		foreach($consumers as $key=>$consumer){
			$consumer_id = $consumer['Consumer']['id'];
			if (!empty($activity[$consumer_id])){
				$consumers[$key]['sales'] = $activity[$consumer_id];
				
			}
		}
		//pr($sale_activity);
	}
	
	public function verify_status($consumer_id){
		if(!$consumer_id){
			return;
		}
		$userRoles = array();
		$consumerSales = $this->query("SELECT o.consumer_id, o.user_id, r.grade, COUNT(o.id) AS totSale FROM `consumer_orders` AS o INNER JOIN users AS u ON u.id = o.user_id INNER JOIN roles AS r ON r.id=u.role_id WHERE o.consumer_id=$consumer_id GROUP BY o.user_id");			
		$totalSale = count($consumerSales);
		if($consumerSales){
			foreach($consumerSales as $cs){
				$uid = $cs['o']['user_id'];
				$grade = $cs['r']['grade'];
				$userRoles[$uid] = $grade;
			}
		}
		$consumerRdSales = $this->query("SELECT rd.consumer_id, rd.user_id, r.grade, COUNT(rd.id) AS totRdSale FROM `consumer_redistribution_orders` AS rd INNER JOIN users AS u ON u.id = rd.user_id INNER JOIN roles AS r ON r.id=u.role_id WHERE rd.consumer_id=$consumer_id GROUP BY rd.user_id");			
		$totalRdSale = count($consumerRdSales);
		if($consumerRdSales){
			foreach($consumerRdSales as $crds){
				$uid1 = $crds['rd']['user_id'];
				$grade1 = $crds['r']['grade'];
				$userRoles[$uid1] = $grade1;
			}
		}
		
		/* $consumerVisits = $this->query("SELECT cv.consumer_id, cv.user_id, r.grade, COUNT(cv.id) AS totVisits FROM `customer_visits` AS cv INNER JOIN users AS u ON u.id = cv.user_id INNER JOIN roles AS r ON r.id=u.role_id WHERE cv.consumer_id=$consumer_id GROUP BY cv.user_id");		
		$totalVisit = count($consumerVisits);
		
		if($consumerVisits){
			foreach($consumerVisits as $cv){
				$uid2 = $cv['cv']['user_id'];
				$grade2 = $cv['r']['grade'];
				$userRoles[$uid2] = $grade2;
			}
		} */
		
		//$entrySum = $totalSale + $totalRdSale + $totalVisit;
		$entrySum = $totalSale + $totalRdSale;
		$status = 0;
		if( ($totalSale <= 1 && $totalRdSale <= 1 ) && $entrySum > 0 ){
			$status = 1;
		}elseif($totalSale > 1 || $totalRdSale > 1 ){
			if( in_array(3, $userRoles) ){
				$status = 3;
			}else{
				$status = 2;
			}			
		}
		
		$check = $this->query("SELECT consumer_id FROM consumers_status WHERE consumer_id = $consumer_id");
		if($check ){
			$this->query("UPDATE consumers_status SET verify_status = $status WHERE consumer_id = $consumer_id");
		}else{
			$this->query("INSERT INTO consumers_status (consumer_id, verify_status) VALUES ($consumer_id, $status)");
		}
		
		return $status;
	}


	function consumerLastVisit($cust_ids){
		
		$cust_idStr = implode(", ",$cust_ids); //pr($cust_idStr); die();
		
		$custCon = "consumer_id IN ($cust_idStr)";
		
		$visitData = array();
		
		$consorderEntry = $this->query("SELECT MAX(o.order_date) as last_visit , o.user_id, o.consumer_id, u.first_name, u.last_name FROM consumer_orders o INNER JOIN users u ON u.id = o.user_id WHERE $custCon GROUP BY o.consumer_id"); //pr($orderEntry);
		//pr($consorderEntry); die();
		
		if(!empty($consorderEntry)){
			foreach($consorderEntry as $val){
				$visitData[$val['o']['consumer_id']]['sale']['user_id'] = $val['o']['user_id'];
				$visitData[$val['o']['consumer_id']]['sale']['user_name'] = $val['u']['first_name'].' '.$val['u']['last_name'];
				$visitData[$val['o']['consumer_id']]['last_visit']['sale'] = $val[0]['last_visit'];
			}
		}
		
		$rConsSaleEntry = $this->query("SELECT MAX(rs.sale_date) as last_visit , rs.user_id, rs.consumer_id, u.first_name, u.last_name FROM consumer_redistribution_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $custCon GROUP BY rs.consumer_id"); //pr($rSaleEntry);
		
		if(!empty($rConsSaleEntry)){
			foreach($rConsSaleEntry as $val){
				$visitData[$val['rs']['consumer_id']]['rsale']['user_id'] = $val['rs']['user_id'];
				$visitData[$val['rs']['consumer_id']]['rsale']['user_name'] = $val['u']['first_name'].' '.$val['u']['last_name'];
				$visitData[$val['rs']['consumer_id']]['last_visit']['rsale'] = $val[0]['last_visit'];
			}
		}
		
		
		return $visitData;
		
	}
	
	
	
	
}