<?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 {
    
    var $virtualFields = array(
		'name' => "CONCAT(Customer.first_name,' ', Customer.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', 'status'), 'type'=>'left'),
		'VerifiedBy' => array('className' => 'User', 'foreignKey' => 'verified_by', 'fields' => array('id', 'first_name', 'last_name'), 'type'=>'left'),
		'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();
		$conditions = "Customer.status  = 1 AND Customer.first_name IS NOT NULL AND Customer.last_name IS NOT NULL";
		$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'){
			// $week += 1; //
			$d += 1;
		}elseif($dPrev == 6 && $firstday == 'Sun'){
			// $week += 2; //
			$d += 2;
		} 

		
		if ($d == 5) {
			$week = 1;
		  } elseif ($d == 6) {
			 $week = 2; 
		  } else {
			 $week = $d; 
		 } */
		 
		//$week = getY3Week(); 
		 //pr('Y3 CRM week no. - '.$week);
		 
		/* $calendar = $user['current_year_calendar'];
		$year = date('Y');
		$month = getMonthNo($calendar, date('Y-m-d'));
		$monthArray = $calendar[$month];
		$week = getWeekNo($monthArray, date('Y-m-d'));
		$day = date('l'); */
		
		
		/* $year = date('Y');
		$month = $user['current_month_no'];
		$week = $user['current_week_no']; */
		
		
		## Route plan is set as per PTG calendar
		
		$currentCal['calendar'] = $user['current_year_calendar'];
		$currentCal['month'] 	= $user['current_month_no'];
		$currentCal['week'] 	= $user['current_week_no'];
		$rpPeriod = getRoutePlanPeriod(date('Y-m-d'), $currentCal);
		$year  = $rpPeriod['year']; 
		$month = $rpPeriod['month'];
		$week  = $rpPeriod['week'];
		
		$day = date('l');
		
		//pr($year . ' ' . $month . ' ' . $week . ' ' . $day);
		
		if ($depot_id){
			//$conditions['Customer.depot_id'] = $depot_id;
			$conditions .= " AND 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 .= " AND (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 .= " AND (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 .= " AND (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 .= " AND (Customer.user_id  = ".$user['id']." OR CustomerAssign.customer_id IS NOT NULL)";
					
				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 ";

					$conditions .= " AND (Customer.region_id  = ".$user['region_id']." OR 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 Customer.route_id = UserRoute.route_id"; //pr($joinsStr);
					
					$joinsStr = " INNER JOIN (SELECT route_ids FROM user_routes WHERE user_id = ".$user['id']."  AND year = $year AND month = $month AND week = $week AND day = '$day') AS UserRoute ON FIND_IN_SET(Customer.route_id, UserRoute.route_ids)";
				    
				    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 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, Customer.verified_latitude, Customer.verified_longitude, Customer.latitude, Customer.longitude, Customer.location_updated FROM customers AS Customer'.$joinsStr.' WHERE '.$conditions.' ORDER BY name asc, created desc LIMIT 1000'; //pr($qryString);
		}else{ //pr($conditions);
			$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(count($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']);
				if ($isService){
					$jsonList[] = array("id"=>$customer['Customer']['id'], 'value'=> $customer['0']['name'], "verified_latitude"=>$customer['Customer']['verified_latitude'], "verified_longitude"=>$customer['Customer']['verified_longitude'], "cust_latitude"=>$customer['Customer']['latitude'], "cust_longitude"=>$customer['Customer']['longitude'], "location_updated"=>$customer['Customer']['location_updated']);
				}
			}
		}
		//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 2:
                if($user['role_id'] == DIVISIONMANAGER){
                    $conditions['Customer.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('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);
	}
**/
	
	function saleActivity(&$customers, $params){ //pr($params);
        $date = $params['date'];
        $cutomerIds = array();
        foreach($customers as $customer){
            $customerIds[] = $customer['Customer']['id'];
        }

        $cond ='';

        if(isset($params['type'])){
            switch($params['type']){
                case "daily":
                    $cond .= " AND sale_date = '$date'";
                    break;
                case 'weekly':
                    $d1 = date('Y-m-d', $params['strtotime']);
                    $d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
                    $cond .= " AND sale_date between '".$d1."' AND '".$d2."'";
                    break;
                case "between":
                    if ( !empty( $params['query']['from']) && !empty( $params['query']['to'])){
                        $cond .= ' AND sale_date BETWEEN "'. $params['query']['from'].'" AND "'. $params['query']['to'].'"';

                    } elseif (!empty( $params['query']['from'])){
                        $cond .= ' AND sale_date >= "'. $params['query']['from'].'"';

                    } elseif (!empty( $params['query']['to'])){
                        $d1 = date('Y-m-d', strtotime('-1 day', strtotime($params['query']['to'])));
                        $cond .= ' AND sale_date BETWEEN "'. $d1.'" AND "'. $params['query']['to'].'"';
                        //$cond .= ' AND sale_date <= "'. $params['query']['to'].'"';

                    }

                    break;
                case "monthly":
                    $d1 = date('Y-m-01', $params['strtotime']);
                    $d2 = date('Y-m-t', $params['strtotime']);

                    $cond .= " AND sale_date between '".$d1."' AND '".$d2."'";

                    break;
            }
        }



        if (!empty($params['query']['dv']) || !empty($params['depot_id']) || !empty($params['region_id']) ){
            if (!empty($params['query']['dv']) && empty($params['region_id']) && empty($params['depot_id']) ){
                $cond .=' AND depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ' . $params['query']['dv'] . '))';
            }
            if (!empty($params['region_id']) && empty($params['depot_id']) ){
                $cond .=' AND depot_id IN (SELECT id from depots WHERE region_id = '.$params['region_id'].')';
            }
            if (!empty($params['depot_id'])){
                $cond .=' AND depot_id='.$params['depot_id'];
            }
        }

        if(!empty($params['brand_group_id'])){
            $cond .= ' AND brand_group_id='.$params['brand_group_id'];
        }

		//pr($cond);
		$qry = $saletype = $table = "";
		
		if(!empty($params['query']['saletype'])){
			$saletype = $params['query']['saletype'] ;
			$table = ($params['query']['saletype'] == 'Sale') ? 'sales' : 'redistributor_saleitems';
		}
		
		switch($saletype){
			case 'Sale':
				$qry = "SELECT customer_id, brand_id,brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM sales WHERE  customer_id IN (".implode(",", $customerIds).") $cond GROUP BY customer_id, brand_group_id";
				break;
				
			case 'Redistribution Sales':
				$qry = "SELECT customer_id, brand_id,brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM redistributor_saleitems WHERE  customer_id IN (".implode(",", $customerIds).") $cond GROUP BY customer_id, brand_group_id";
				break;
				
			default:
				$qry = "SELECT customer_id, brand_id,brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM sales WHERE  customer_id IN (".implode(",", $customerIds).") $cond GROUP BY customer_id, brand_group_id  UNION
		SELECT customer_id, brand_id,brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM redistributor_saleitems WHERE  customer_id IN (".implode(",", $customerIds).") $cond GROUP BY customer_id, brand_group_id";
				break;
				
		}
        
		
		//pr($qry);

        $sale_activity = $this->query($qry); //pr($sale_activity);
        $activity = $rdTotal = array();
		
		if(!empty($table)){
			foreach($sale_activity as $sa){
				$cid = $sa[$table]['customer_id'];
				//$bid = $sa['0']['brand_id'];
				$bid = $sa[$table]['brand_group_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'];
				}
			}
			
		}else{
			foreach($sale_activity as $sa){
				$cid = $sa['0']['customer_id'];
				//$bid = $sa['0']['brand_id'];
				$bid = $sa['0']['brand_group_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;
	}
	
	function customerLastVisit($cust_ids){
		
		$cust_idStr = implode(", ",$cust_ids); //pr($cust_idStr);
		
		$custCon = "customer_id IN ($cust_idStr)";
		
		$visitData = array();
		
		//$orderEntry = $this->query("SELECT MAX(o.order_date) as last_visit , o.user_id, o.customer_id, u.first_name, u.last_name FROM orders o INNER JOIN users u ON u.id = o.user_id WHERE $custCon GROUP BY o.customer_id"); //pr($orderEntry);
		$orderEntry = $this->query("SELECT o.order_date as last_visit, o.user_id, o.customer_id, u.first_name, u.last_name, u.status, u.role_id FROM orders o INNER JOIN (SELECT customer_id, MAX(order_date) as last_visit FROM orders WHERE $custCon GROUP BY customer_id) latest ON latest.customer_id = o.customer_id AND latest.last_visit = o.order_date INNER JOIN users u ON u.id = o.user_id"); //pr($orderEntry);
		
		if(!empty($orderEntry)){
			foreach($orderEntry as $val){
				$visitData[$val['o']['customer_id']]['sale']['user_id'] = $val['o']['user_id'];
				$visitData[$val['o']['customer_id']]['sale']['user_name'] = $val['u']['first_name'].' '.$val['u']['last_name'];
				$visitData[$val['o']['customer_id']]['sale']['user_status'] = $val['u']['status'];
				$visitData[$val['o']['customer_id']]['sale']['user_role_id'] = $val['u']['role_id'];
				$visitData[$val['o']['customer_id']]['last_visit']['sale'] = $val['o']['last_visit'];
				
			}
		}
		
		//$rSaleEntry = $this->query("SELECT MAX(rs.sale_date) as last_visit , rs.user_id, rs.customer_id, u.first_name, u.last_name FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $custCon GROUP BY rs.customer_id"); //pr($rSaleEntry);
		$rSaleEntry = $this->query("SELECT rs.sale_date as last_visit, rs.user_id, rs.customer_id, u.first_name, u.last_name, u.status, u.role_id FROM redistributor_sales rs INNER JOIN (SELECT customer_id, MAX(sale_date) as last_visit FROM redistributor_sales WHERE $custCon GROUP BY customer_id) latest ON latest.customer_id = rs.customer_id AND latest.last_visit = rs.sale_date INNER JOIN users u ON u.id = rs.user_id");
		
		if(!empty($rSaleEntry)){
			foreach($rSaleEntry as $val){
				$visitData[$val['rs']['customer_id']]['rsale']['user_id'] = $val['rs']['user_id'];
				$visitData[$val['rs']['customer_id']]['rsale']['user_name'] = $val['u']['first_name'].' '.$val['u']['last_name'];
				$visitData[$val['rs']['customer_id']]['rsale']['user_status'] = $val['u']['status'];
				$visitData[$val['rs']['customer_id']]['rsale']['user_role_id'] = $val['u']['role_id'];
				$visitData[$val['rs']['customer_id']]['last_visit']['rsale'] = $val['rs']['last_visit'];
				
			}
		}
		
		//$visitEntry = $this->query("SELECT MAX(cv.visiting_date) as last_visit ,  cv.user_id, cv.customer_id, u.first_name, u.last_name FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $custCon GROUP BY cv.customer_id"); //pr($visitEntry);
		$visitEntry = $this->query("SELECT cv.visiting_date as last_visit, cv.user_id, cv.customer_id, u.first_name, u.last_name, u.status, u.role_id FROM customer_visits cv INNER JOIN (SELECT customer_id, MAX(visiting_date) as last_visit FROM customer_visits WHERE $custCon GROUP BY customer_id) latest ON latest.customer_id = cv.customer_id AND latest.last_visit = cv.visiting_date INNER JOIN users u ON u.id = cv.user_id");
		
		if(!empty($visitEntry)){
			foreach($visitEntry as $val){
				$visitData[$val['cv']['customer_id']]['visit']['user_id'] = $val['cv']['user_id'];
				$visitData[$val['cv']['customer_id']]['visit']['user_name'] = $val['u']['first_name'].' '.$val['u']['last_name'];
				$visitData[$val['cv']['customer_id']]['visit']['user_status'] = $val['u']['status'];
				$visitData[$val['cv']['customer_id']]['visit']['user_role_id'] = $val['u']['role_id'];
				$visitData[$val['cv']['customer_id']]['last_visit']['visit'] = $val['cv']['last_visit'];
				
			}
		}
		//pr($visitData); pr(count($cust_ids)); pr(count($visitData));
		$remainingCustIds = array();
		if(!empty($visitData)){
			foreach($cust_ids as $id){ //pr($id);
				if(empty($visitData[$id])){
					array_push($remainingCustIds,$id);
				}
			}
			
			if(!empty($remainingCustIds)){
				$visitData += $this->findVisitsFrmOldTbl($remainingCustIds) ;
			}			
			
		}
		return $visitData;
		
	}
	
	function findVisitsFrmOldTbl($remainingCustIds){ 
		$to_remove = $visitData = array();
		
		$cust_idStr = implode(", ",$remainingCustIds);  //pr($cust_idStr); 
		$custCon = "customer_id IN ($cust_idStr)"; //pr($custCon); 
		$i=1;
		do{
			
			$tblYr = date('Y') - $i;
			
			// Prevent infinite loop if tblYr goes below start year
			if ($tblYr < CVTBLSTARTYEAR) {
				break;
			}
			
			$cvTbl = 'customer_visits_'.$tblYr;  //pr($cvTbl); 
			$result = $this->query("SHOW TABLES LIKE '".$cvTbl."'"); //pr($result);
			
			if (!empty($result)) {
				//$visitEntry = $this->query("SELECT MAX(cv.visiting_date) as last_visit ,  cv.user_id, cv.customer_id, u.first_name, u.last_name FROM $cvTbl cv INNER JOIN users u ON u.id = cv.user_id WHERE $custCon GROUP BY cv.customer_id"); //pr($visitEntry);
				$visitEntry = $this->query("SELECT cv.visiting_date as last_visit, cv.user_id, cv.customer_id, u.first_name, u.last_name, u.status, u.role_id FROM $cvTbl cv INNER JOIN (SELECT customer_id, MAX(visiting_date) as last_visit FROM $cvTbl WHERE $custCon GROUP BY customer_id) latest ON latest.customer_id = cv.customer_id AND latest.last_visit = cv.visiting_date INNER JOIN users u ON u.id = cv.user_id");
			} 
			//die();
			
			
			
			if(!empty($visitEntry)){
				foreach($visitEntry as $val){
					//$to_remove = array($val['cv']['customer_id']);
					$to_remove[] = $val['cv']['customer_id'];
					$visitData[$val['cv']['customer_id']]['visit']['user_id'] = $val['cv']['user_id'];
					$visitData[$val['cv']['customer_id']]['visit']['user_name'] = $val['u']['first_name'].' '.$val['u']['last_name'];
					$visitData[$val['cv']['customer_id']]['visit']['user_status'] = $val['u']['status'];
					$visitData[$val['cv']['customer_id']]['visit']['user_role_id'] = $val['u']['role_id'];
					$visitData[$val['cv']['customer_id']]['last_visit']['visit'] = $val['cv']['last_visit'];
				}
			}
			
			$remainingCustIds = array_diff($remainingCustIds, $to_remove); //pr('$remainingCustIds'); pr($remainingCustIds );
			
			if($tblYr == CVTBLSTARTYEAR){
				$remainingCustIds = array();
			}
			
			$i++;
		} while (!empty($remainingCustIds));
		
		return $visitData;
		
		
	}
}