<?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 Customer extends AppModel {
	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'), 
		'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 customerList($depot_id=0, $user=null, $isService=false, $params = null){
		$conditions = 1; // array();
		$joinsStr = "";
		if ($depot_id){
			//$conditions['Customer.depot_id'] = $depot_id;
			$conditions = "Customer.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;
			} */
		}
	//	$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";
	
	    $day =date('D', strtotime(date('Y-m-d'))); 
		$d = weekOfMonth(date('Y-m-d')); 
		
		if ($d == 5) {
			$week = 1;
		  } elseif ($d == 6) {
			 $week = 2; 
		  } else {
			 $week = $d; 
		  }
		  
		$joinsStr = " INNER JOIN (SELECT route_id FROM user_routes WHERE user_id = ".$user['id']."  AND week = '$week' AND day LIKE '$day%') AS UserRoute ON Customer.route_id = UserRoute.route_id";
	
	
		if (!empty($user['depot_id']) && $user['Role']['grade'] > 3){
			$conditions .= " AND Customer.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['minCustomerId']) && !empty($params['maxCustomerId'])){
					$conditions .= " AND ( Customer.id < ".$params['minCustomerId']." OR  Customer.id > ".$params['maxCustomerId'] . ")";
				}else if(!empty($params['minCustomerId']) && $params['minCustomerId'] > 0){
					$conditions .= " AND Customer.id < ".$params['minCustomerId'];
				} else if(!empty($params['maxCustomerId']) && $params['maxCustomerId'] > 0){
					$conditions .= " AND Customer.id > ".$params['maxCustomerId'];
				}
			}
			$qryString = '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 id desc LIMIT 1000';
		}else{
			$qryString = '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, created desc';
		}
		$customers = $this->query($qryString);
		$list = array();
		$listArr = array();
		$jsonList = array();
		//pr($customers);
		//die();
		if($customers){
			foreach($customers as $customer){
				$list[$customer['Customer']['id']] = $customer['0']['name'];
				$listArr[] = array('id'=> $customer['Customer']['id'], 'depot_id'=>$customer['Customer']['depot_id'], 'name'=>$customer['0']['name']);
				$jsonList[] = array("id"=>$customer['Customer']['id'], 'value'=> $customer['0']['name']);
			}
		}
		//pr($list);
		if ($isService){
			if (!$list){
				$list = null;
			}
			return array('customers'=>array(), 'customersList'=>$jsonList);
		}else{
			return $list;
		}
	}
	
	public function customerListByRole($user, $isService = false){
		return $this->customerList(0, $user, $isService);
	}
	
	public function moreCustomerListByRole($user, $params){
		return $this->customerList(0, $user, true, $params);
	}
	
	public function _userCondition(&$conditions, $user){
		switch($user['Role']['grade']){
			case 3:
				$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'].'))';
			break;
			case 4:
				$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'].'))';
			break;
			case 5:
			if(isset($conditions['OR'])){
				$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'].') ');
			}else{
				$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'].') ');
			}
			break;
			
			case 6:
				$conditions['Customer.user_id'] = $user['id']." OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = ".$user['id'].")";
			break;
			
		}
	}
	
	public function _userRoleCondition(&$conditions, $user, &$paginate){
		
		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 ";
				
				//$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('Customer.user_id = User2.id'));
				
				$paginate['joins'][] = array('table' => '(SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'CustomerAssign', 'conditions'=>array('Customer.id = CustomerAssign.customer_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', 'CustomerAssign.customer_id IS NOT NULL');
				}else{
					$conditions['OR'] = array('User2.id IS NOT NULL', 'CustomerAssign.customer_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 customer_id FROM customer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'CustomerAssign', 'conditions'=>array('Customer.id = CustomerAssign.customer_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('Customer.depot_id'=> $user['depot_id'], 'CustomerAssign.customer_id IS NOT NULL');
				}else{
					$conditions['OR'] = array('Customer.depot_id'=> $user['depot_id'], 'CustomerAssign.customer_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('Customer.user_id = UserRelation.uid2'));
				
				$paginate['joins'][] = array('table' => '(SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'CustomerAssign', 'conditions'=>array('Customer.id = CustomerAssign.customer_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('Customer.user_id'=> $user['id'], 'UserRelation.uid2 IS NOT NULL OR CustomerAssign.customer_id IS NOT NULL');
				}else{
					$conditions['OR'] = array('Customer.user_id'=> $user['id'], 'UserRelation.uid2 IS NOT NULL OR CustomerAssign.customer_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 customer_id FROM customer_assigns WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'CustomerAssign', 'conditions'=>array('Customer.id = CustomerAssign.customer_id'));
				if(isset($conditions['OR'])){
					$conditions[]['OR'] = array('Customer.user_id'=> $user['id'], 'CustomerAssign.customer_id IS NOT NULL' );
				}else{
					$conditions['OR'] = array('Customer.user_id'=> $user['id'], 'CustomerAssign.customer_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['Customer.depot_id'] = $user['depot_id'];
		}
	}
	
	function saleActivity(&$customers, $params){
		$cutomerIds = array();
		foreach($customers as $customer){
			$customerIds[] = $customer['Customer']['id'];
		}
		
		
		$qry = "SELECT customer_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 customer_id IN (".implode(",", $customerIds).") GROUP BY customer_id, brand_id  UNION
		SELECT customer_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 customer_id IN (".implode(",", $customerIds).") GROUP BY customer_id, brand_id";
			
		$sale_activity = $this->query($qry);
		$activity = $rdTotal = array();
		
		foreach($sale_activity as $sa){
			$cid = $sa['0']['customer_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($customers as $key=>$customer){
			$customer_id = $customer['Customer']['id'];
			if (!empty($activity[$customer_id])){
				$customers[$key]['sales'] = $activity[$customer_id];
				
			}
		}
		//pr($sale_activity);
	}
	
	public function verify_status($customer_id){
		if(!$customer_id){
			return;
		}
		$userRoles = array();
		$customerSales = $this->query("SELECT o.customer_id, o.user_id, r.grade, COUNT(o.id) AS totSale FROM `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.customer_id=$customer_id GROUP BY o.user_id");			
		$totalSale = count($customerSales);
		if($customerSales){
			foreach($customerSales as $cs){
				$uid = $cs['o']['user_id'];
				$grade = $cs['r']['grade'];
				$userRoles[$uid] = $grade;
			}
		}
		$customerRdSales = $this->query("SELECT rd.customer_id, rd.user_id, r.grade, COUNT(rd.id) AS totRdSale FROM `redistributor_sales` 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.customer_id=$customer_id GROUP BY rd.user_id");			
		$totalRdSale = count($customerRdSales);
		if($customerRdSales){
			foreach($customerRdSales as $crds){
				$uid1 = $crds['rd']['user_id'];
				$grade1 = $crds['r']['grade'];
				$userRoles[$uid1] = $grade1;
			}
		}
		
		$customerVisits = $this->query("SELECT cv.customer_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.customer_id=$customer_id GROUP BY cv.user_id");		
		$totalVisit = count($customerVisits);
		
		if($customerVisits){
			foreach($customerVisits as $cv){
				$uid2 = $cv['cv']['user_id'];
				$grade2 = $cv['r']['grade'];
				$userRoles[$uid2] = $grade2;
			}
		}
		
		$entrySum = $totalSale + $totalRdSale + $totalVisit;
		$status = 0;
		if( ($totalSale <= 1 && $totalRdSale <= 1 && $totalVisit <= 1) && $entrySum > 0 ){
			$status = 1;
		}elseif($totalSale > 1 || $totalRdSale > 1 || $totalVisit > 1){
			if( in_array(3, $userRoles) ){
				$status = 3;
			}else{
				$status = 2;
			}			
		}
		
		$check = $this->query("SELECT customer_id FROM customers_status WHERE customer_id = $customer_id");
		if($check ){
			$this->query("UPDATE customers_status SET verify_status = $status WHERE customer_id = $customer_id");
		}else{
			$this->query("INSERT INTO customers_status (customer_id, verify_status) VALUES ($customer_id, $status)");
		}
		
		return $status;
	}
}