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

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

class Report extends AppModel {
	public $useTable = false;
	
	/**
	 * activeUsers 
	 * @params : array
	 * return array
	 */
	public function activeUsers($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$oC = "o.order_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']));
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";
			break;
			
			case 'between':
				$d1 = $params['query']['from'];
				if(empty( $params['query']['to'])){
					$d2 = date('Y-m-d');
				}else{
					$d2 =  $params['query']['to'];
				}		
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";
			break;
		}
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND u.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND u.depot_id = ".$params['query']['dp'];
		}
		
		$result = array();
		$orderEntry = $this->query("SELECT count(u.id) as oentry, o.user_id, u.first_name, u.last_name FROM orders o INNER JOIN users u ON u.id = o.user_id WHERE $oC $rgC $dpC GROUP BY u.id");
		$rSaleEntry = $this->query("SELECT count(u.id) as rsentry, rs.user_id, u.first_name, u.last_name FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $rdC $rgC $dpC GROUP BY u.id");
		$visitEntry = $this->query("SELECT count(u.id) as ventry, cv.user_id, u.first_name, u.last_name FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $cvC $rgC $dpC GROUP BY u.id");
		if ($orderEntry){
			foreach ($orderEntry as $oe){
				$uid = $oe['o']['user_id'];
				$result[$uid]['orderEntry'] = $oe;
				$result[$uid]['name'] = $oe['u']['first_name'].' '.$oe['u']['last_name'];
			}
		}
		
		if ($rSaleEntry){
			foreach ($rSaleEntry as $rse){
				$uid = $rse['rs']['user_id'];
				$result[$uid]['rsEntry'] = $rse;
				$result[$uid]['name'] = $rse['u']['first_name'].' '.$rse['u']['last_name'];
			}
		}
		
		if ($visitEntry){
			foreach ($visitEntry as $cve){
				$uid = $cve['cv']['user_id'];
				$result[$uid]['visitEntry'] = $cve;
				$result[$uid]['name'] = $cve['u']['first_name'].' '.$cve['u']['last_name'];
			}
		}
		
		return $result;
		
	}
	
	
/**
 * activeUsers 
 * @params : array
 * return array
 */
	public function nonactiveUsers($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$oC = "AND o.order_date = '$date'";
				$rdC = "AND rs.sale_date = '$date'";
				$cvC = "AND 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']));
				
				$oC = "AND o.order_date between '$d1' AND '$d2'";
				$rdC = "AND rs.sale_date between '$d1' AND '$d2'";
				$cvC = "AND cv.visiting_date between '$d1' AND '$d2'";
				
			break;
			
			case 'between':
				$d1 = $params['query']['from'];
				if(empty( $params['query']['to'])){
					$d2 = date('Y-m-d');
				}else{
					$d2 =  $params['query']['to'];
				}		
				$oC = "AND o.order_date between '$d1' AND '$d2'";
				$rdC = "AND rs.sale_date between '$d1' AND '$d2'";
				$cvC = "AND cv.visiting_date between '$d1' AND '$d2'";
			break;
			
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$oC = "AND o.order_date between '$d1' AND '$d2'";
				$rdC = "AND rs.sale_date between '$d1' AND '$d2'";
				$cvC = "AND cv.visiting_date between '$d1' AND '$d2'";
			break;
		}
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND u.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND u.depot_id = ".$params['query']['dp'];
		}
		
		$result = array();
		$result = $this->query("SELECT u.id, u.first_name, u.last_name, r.name, rg.title, d.title
		FROM users u INNER JOIN roles r ON r.id = u.role_id INNER JOIN regions rg ON rg.id = u.region_id 
		INNER JOIN depots d ON d.id = u.depot_id
		LEFT JOIN orders o ON o.user_id = u.id $oC
		LEFT JOIN redistributor_sales rs ON rs.user_id = u.id $rdC
		LEFT JOIN customer_visits cv ON cv.user_id = u.id $cvC		
		WHERE u.status= ".ACTIVE." AND r.grade > 3 AND o.id IS NULL 
		AND cv.id IS NULL 
		AND rs.id IS NULL $rgC $dpC GROUP BY u.id");
			
		return $result;
		
	}
	
	function callReports($params){
		$output = array();
		$date = $params['date'];
		//pr($params);
		switch($params['type']){
			case 'daily':
				$oC = "sl.sale_date = '$date'";
				$rdC = "rsi.sale_date = '$date'";
				$cvC = "cv.visiting_date LIKE '".$date."%'";
			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'];
				}		
				
				$oC = "sl.sale_date between '$d1' AND '$d2'";
				$rdC = "rsi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";				
				$ccC = "c.created between '$d1' AND '$d2 23:59:59'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$oC = "sl.sale_date between '$d1' AND '$d2'";
				$rdC = "rsi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";				
				$ccC = "c.created between '$d1' AND '$d2 23:59:59'";				
			break;
		}
		$d1Arrar = explode("-", $d1);
		
		
		$userDC = $userRC = $rgC = $dpC = $crgC = $cdpC = $cvrgC = $cvdpC ="";
		if (!empty($params['query']['rg'])){
			$rgC = " AND d.region_id = ".$params['query']['rg'];
			$cvrgC = " AND us.region_id = ".$params['query']['rg'];
			$crgC = " AND c.region_id = ".$params['query']['rg'];
			$userRC = " AND u.region_id = ".$params['query']['rg'];
			
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND d.id = ".$params['query']['dp'];
			$cvdpC = " AND us.depot_id = ".$params['query']['dp'];
			$cdpC = " AND c.depot_id = ".$params['query']['dp'];
			$userDC = " AND u.depot_id = ".$params['query']['dp'];
		}
		
		
		$result = array();
		
		$orderEntry = $this->query("SELECT count(DISTINCT sl.order_id) as oentry, sl.user_id, sl.depot_id, d.title, d.region_id FROM sales sl INNER JOIN depots d ON d.id = sl.depot_id WHERE $oC $rgC $dpC GROUP BY sl.depot_id, sl.user_id");
		
		$rSaleEntry = $this->query("SELECT count(DISTINCT rsi.redistributor_sale_id) as rsentry, rsi.user_id, rsi.depot_id, d.title, d.region_id FROM redistributor_saleitems rsi INNER JOIN depots d ON d.id = rsi.depot_id WHERE $rdC $rgC $dpC GROUP BY rsi.depot_id, rsi.user_id");
		
		$visitEntry = $this->query("SELECT count(cv.user_id) as ventry, cv.user_id, cv.depot_id, us.depot_id, us.region_id FROM customer_visits cv INNER JOIN users us ON us.id = cv.user_id WHERE $cvC $cvrgC $cvdpC GROUP BY us.depot_id, cv.user_id");
		//pr($visitEntry);
		
		if ($orderEntry){
			foreach ($orderEntry as $oe){
				$uid = $oe['sl']['user_id'];
				$did = $oe['sl']['depot_id'];
				$rid = $oe['d']['region_id'];
				$result[$rid][$did][$uid]['orderEntry'] = $oe;
				//$result[$rid][$did][$uid]['name'] = $oe['u']['first_name'].' '.$oe['u']['last_name'];
				$result[$rid][$did][$uid]['saleEntry'] = $oe['0']['oentry'];
				//$result[$rid][$did][$uid]['dname'] = $oe['d']['title'];
				//$result[$rid][$did][$uid]['role_id'] = $oe['u']['role_id'];
			}
		}
		
		if ($rSaleEntry){
			foreach ($rSaleEntry as $rse){
				$uid = $rse['rsi']['user_id'];
				$did = $rse['rsi']['depot_id'];
				$rid = $rse['d']['region_id'];
				$result[$rid][$did][$uid]['rsEntry'] = $rse;
				//$result[$rid][$did][$uid]['name'] = $rse['u']['first_name'].' '.$rse['u']['last_name'];
				$result[$rid][$did][$uid]['rsSaleEntry'] = $rse['0']['rsentry'];
				//$result[$rid][$did][$uid]['dname'] = $rse['d']['title'];
				//$result[$rid][$did][$uid]['role_id'] = $rse['u']['role_id'];
			}
		}
		
		if ($visitEntry){
			foreach ($visitEntry as $cve){
				$uid = $cve['cv']['user_id'];
				$did = ($cve['us']['depot_id'])?$cve['us']['depot_id']:0;
				$rid = $cve['us']['region_id'];
				$result[$rid][$did][$uid]['visitEntry'] = $cve;
				//$result[$rid][$did][$uid]['name'] = $cve['u']['first_name'].' '.$cve['u']['last_name'];
				$result[$rid][$did][$uid]['ventry'] = $cve['0']['ventry'];
				//$result[$rid][$did][$uid]['dname'] = $cve['d']['title'];
				//$result[$rid][$did][$uid]['role_id'] = $cve['u']['role_id'];
			}
		}
	
		//Count customers created		
		$customerEntry = $this->query("SELECT count(c.id) as cCreated, c.user_id FROM customers c WHERE 1 $crgC $cdpC GROUP BY c.user_id");
		
		if ($customerEntry){
			foreach ($customerEntry as $cve){
				$uid = $cve['c']['user_id'];
				//$did = $cve['c']['depot_id'];
				//$rid = $cve['c']['region_id'];
				$result['cCreated'][$uid] = $cve;
				//$result[$rid][$did][$uid]['name'] = $cve['u']['first_name'].' '.$cve['u']['last_name'];
				$result['totalCustomer'][$uid] = $cve['0']['cCreated'];
				//$result[$rid][$did][$uid]['dname'] = $cve['d']['title'];
				//$result[$rid][$did][$uid]['role_id'] = $cve['u']['role_id'];
			}
		}
		
		//$output['totalCalls'] = $totalCalls;
		$output['result'] = $result;
		//Get roles 
		/*$roles = array();
		$roleData = $this->query("SELECT id, name FROM roles AS Role");
		foreach($roleData as $role){
			$id = $role['Role']['id'];
			$roles[$id] = $role['Role']['name'];
		}
		$output['roles'] = $roles;*/
		
		//Get All users		
		$users = array();
		$usersData = $this->query("SELECT u.id, u.first_name, u.last_name, u.region_id, u.depot_id, u.role_id, r.name, d.title FROM users AS u INNER JOIN roles AS r ON u.role_id = r.id LEFT JOIN depots d ON d.id = u.depot_id LEFT JOIN regions rg ON rg.id = u.region_id WHERE r.grade > 2 AND u.status = ".ACTIVE." $userDC $userRC ORDER BY rg.title ASC, u.role_id ASC");
		foreach($usersData as $user){
			
			$rid = ($user['u']['region_id'])?$user['u']['region_id']:0;
			$did = ($user['u']['depot_id'])?$user['u']['depot_id']:0;
			
			$uid = $user['u']['id'];
			$users[$rid][$did][$uid] = array('name'=> $user['u']['first_name'].' '.$user['u']['last_name'], 'rid'=>$user['u']['role_id'], 'role'=>$user['r']['name'], 'depot'=>$user['d']['title']);
		}
		$output['users'] = $users;
		//pr($users);
		//find monthly Targets
		$targets = array();
		$targetQry = $this->query("SELECT user_id, depot_id, target FROM user_targets AS Target WHERE type='Call'");
		//pr($targetQry);
		if($targetQry){
			foreach($targetQry as $target){
				$user_id = $target['Target']['user_id'];
				$depot_id = $target['Target']['depot_id'];
				$userTargets = @unserialize($target['Target']['target']);
				$targets[$user_id] = !empty($userTargets[$d1Arrar['1']])?$userTargets[$d1Arrar['1']]:0;
			}
		}
		$output['targets'] = $targets;
		return $output;
	}
	
	function effectiveCallReports($params){
		$output = array();
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$d1 = $date;
				$oC = "sl.sale_date = '$date'";
				$rdC = "rsi.sale_date = '$date'";
				$cvC = "cv.visiting_date LIKE '".$date."%'";
				
				$oC = "sl.sale_date = '$date'";
				$rdC = "rsi.sale_date = '$date'";
				$cvC = "cv.visiting_date LIKE '$date%'";				
				$ccC = "c.created LIKE '$date'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 = date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$oC = "sl.sale_date between '$d1' AND '$d2'";
				$rdC = "rsi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";				
				$ccC = "c.created 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'];
				}		
				
				$oC = "sl.sale_date between '$d1' AND '$d2'";
				$rdC = "rsi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";				
				$ccC = "c.created between '$d1' AND '$d2 23:59:59'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$oC = "sl.sale_date between '$d1' AND '$d2'";
				$rdC = "rsi.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2 23:59:59'";				
				$ccC = "c.created between '$d1' AND '$d2 23:59:59'";				
			break;
		}
		$d1Arrar = explode("-", $d1);
		
		
		$userDC = $userRC = $rgC = $dpC = $crgC = $cdpC = $cvrgC = $cvdpC ="";
		if (!empty($params['query']['rg'])){
			$rgC = " AND d.region_id = ".$params['query']['rg'];
			$cvrgC = " AND us.region_id = ".$params['query']['rg'];
			$crgC = " AND c.region_id = ".$params['query']['rg'];
			$userRC = " AND u.region_id = ".$params['query']['rg'];
			
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND d.id = ".$params['query']['dp'];
			$cvdpC = " AND us.depot_id = ".$params['query']['dp'];
			$cdpC = " AND c.depot_id = ".$params['query']['dp'];
			$userDC = " AND u.depot_id = ".$params['query']['dp'];
		}
		
		
		$result = array();
		
		$orderEntry = $this->query("SELECT SUM(sl.cal_qty) AS totQty, count(DISTINCT sl.order_id) as oentry, sl.user_id, sl.depot_id, d.title, d.region_id FROM sales sl INNER JOIN depots d ON d.id = sl.depot_id WHERE $oC $rgC $dpC GROUP BY sl.depot_id, sl.user_id");
		
		$rSaleEntry = $this->query("SELECT SUM(rsi.cal_qty) AS totQty, count(DISTINCT rsi.redistributor_sale_id) as rsentry, rsi.user_id, rsi.depot_id, d.title, d.region_id FROM redistributor_saleitems rsi INNER JOIN depots d ON d.id = rsi.depot_id WHERE $rdC $rgC $dpC GROUP BY rsi.depot_id, rsi.user_id");
		
		$visitEntry = $this->query("SELECT count(cv.user_id) as ventry, cv.user_id, cv.depot_id, us.depot_id, us.region_id FROM customer_visits cv INNER JOIN users us ON us.id = cv.user_id WHERE $cvC $cvrgC $cvdpC GROUP BY us.depot_id, cv.user_id");
		//pr($visitEntry);
		
		if ($orderEntry){
			foreach ($orderEntry as $oe){
				$uid = $oe['sl']['user_id'];
				$did = $oe['sl']['depot_id'];
				$rid = $oe['d']['region_id'];
				$result[$rid][$did][$uid]['orderEntry'] = $oe;
				$result[$rid][$did][$uid]['saleEntry'] = $oe['0']['oentry'];
				$result[$rid][$did][$uid]['saleTotQty'] = $oe['0']['totQty'];
			}
		}
		
		if ($rSaleEntry){
			foreach ($rSaleEntry as $rse){
				$uid = $rse['rsi']['user_id'];
				$did = $rse['rsi']['depot_id'];
				$rid = $rse['d']['region_id'];
				$result[$rid][$did][$uid]['rsEntry'] = $rse;
				$result[$rid][$did][$uid]['rsSaleEntry'] = $rse['0']['rsentry'];
				$result[$rid][$did][$uid]['rsSaleTotQty'] = $rse['0']['totQty'];
			}
		}
		
		if ($visitEntry){
			foreach ($visitEntry as $cve){
				$uid = $cve['cv']['user_id'];
				$did = ($cve['us']['depot_id'])?$cve['us']['depot_id']:0;
				$rid = $cve['us']['region_id'];
				$result[$rid][$did][$uid]['visitEntry'] = $cve;
				$result[$rid][$did][$uid]['ventry'] = $cve['0']['ventry'];
			}
		}
	
		//Count customers created		
		$customerEntry = $this->query("SELECT count(c.id) as cCreated, c.user_id FROM customers c WHERE 1 $crgC $cdpC GROUP BY c.user_id");
		
		if ($customerEntry){
			foreach ($customerEntry as $cve){
				$uid = $cve['c']['user_id'];
				$result['cCreated'][$uid] = $cve;
				$result['totalCustomer'][$uid] = $cve['0']['cCreated'];
			}
		}
		
		//$output['totalCalls'] = $totalCalls;
		$output['result'] = $result;
		
		//Get All users		
		$users = array();
		$usersData = $this->query("SELECT u.id, u.first_name, u.last_name, u.region_id, u.depot_id, u.role_id, r.name, d.title FROM users AS u INNER JOIN roles AS r ON u.role_id = r.id LEFT JOIN depots d ON d.id = u.depot_id LEFT JOIN regions rg ON rg.id = u.region_id WHERE r.grade > 2 AND u.status = ".ACTIVE."  $userRC $userDC ORDER BY rg.title ASC, u.role_id ASC");
		foreach($usersData as $user){
			
			$rid = ($user['u']['region_id'])?$user['u']['region_id']:0;
			$did = ($user['u']['depot_id'])?$user['u']['depot_id']:0;
			
			$uid = $user['u']['id'];
			$users[$rid][$did][$uid] = array('name'=> $user['u']['first_name'].' '.$user['u']['last_name'], 'rid'=>$user['u']['role_id'], 'role'=>$user['r']['name'], 'depot'=>$user['d']['title']);
		}
		$output['users'] = $users;
		//pr($users);
		//find monthly Targets
		$targets = array();
		$targetQry = $this->query("SELECT user_id, depot_id, target FROM user_targets AS Target WHERE type='Call'");
		//pr($targetQry);
		if($targetQry){
			foreach($targetQry as $target){
				$user_id = $target['Target']['user_id'];
				$depot_id = $target['Target']['depot_id'];
				$userTargets = @unserialize($target['Target']['target']);
				$targets[$user_id] = !empty($userTargets[$d1Arrar['1']])?$userTargets[$d1Arrar['1']]:0;
			}
		}
		$output['targets'] = $targets;
		return $output;
	}
	
	function customerReports($params){
		$output = array();
		$date = $params['date'];
		
		switch($params['type']){
			case 'daily':
			
			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'];
				}		
				
				
			break; 
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				$enddate = date('Y-m-d', strtotime("+1 day", strtotime($d2)));
				//$cdate = "c.created between '$d1' AND '$enddate'";
				$cdate = "1";
							
			break;
		}
		$d1Arrar = explode("-", $d1);
		
		
		$userDC = $userRC = $rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND c.region_id = ".$params['query']['rg'];
			$userRC = " AND u.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp'];
			$userDC = " AND u.depot_id = ".$params['query']['dp'];
		}
		
		
		$result = array();
		
		$customerByUser = $this->query("SELECT count(c.id) as totCustomer, c.user_id FROM customers c WHERE $cdate $rgC $dpC GROUP BY c.user_id");
			
		if ($customerByUser){
			foreach ($customerByUser as $cust){
				$uid = $cust['c']['user_id'];
				//$did = $cust['c']['depot_id'];
				//$rid = $cust['d']['region_id'];
				//$result[$rid][$did][$uid]['customerEntry'] = $cust;
				//$result[$rid][$did][$uid]['name'] = $cust['u']['first_name'].' '.$cust['u']['last_name'];
				$result['custCreated'][$uid] = $cust['0']['totCustomer'];
				//$result[$rid][$did][$uid]['dname'] = $cust['d']['title'];
				//$result[$rid][$did][$uid]['role_id'] = $cust['u']['role_id'];
			}
		}
		
		
		
		//$output['totalCalls'] = $totalCalls;
		$output['result'] = $result;
		//Get roles 
		/*$roles = array();
		$roleData = $this->query("SELECT id, name FROM roles AS Role");
		foreach($roleData as $role){
			$id = $role['Role']['id'];
			$roles[$id] = $role['Role']['name'];
		}
		$output['roles'] = $roles;
		*/
		$users = array();
		$usersData = $this->query("SELECT u.id, u.first_name, u.last_name, u.region_id, u.depot_id, u.role_id, r.name, d.title FROM users AS u INNER JOIN roles AS r ON u.role_id = r.id LEFT JOIN depots d ON d.id = u.depot_id LEFT JOIN regions rg ON rg.id = u.region_id WHERE r.grade > 2 AND u.status = ".ACTIVE." $userDC $userRC ORDER BY rg.title ASC, u.role_id ASC");
		foreach($usersData as $user){
			
			$rid = $user['u']['region_id'];
			$did = $user['u']['depot_id'];
			$uid = $user['u']['id'];
			$users[$rid][$did][$uid] = array('name'=> $user['u']['first_name'].' '.$user['u']['last_name'], 'rid'=>$user['u']['role_id'], 'role'=>$user['r']['name'], 'depot'=>$user['d']['title']);
		}
		$output['users'] = $users;
		
		//find monthly Targets
		$targets = array();
		$targetQry = $this->query("SELECT user_id, depot_id, target FROM user_targets AS Target WHERE type='Customer'");
		if($targetQry){
			foreach($targetQry as $target){
				$user_id = $target['Target']['user_id'];
				$depot_id = $target['Target']['depot_id'];
				//$userTargets = @unserialize($target['Target']['target']);
				$targets[$user_id] = $target['Target']['target'];
			}
		}
		$output['targets'] = $targets;
		return $output;
	}
	
	function dayCalls($params){
		$output = array();
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$oC = "o.order_date = '$date'";
				$rdC = "rs.sale_date = '$date'";
				$cvC = "cv.visiting_date LIKE '".$date."%'";
			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'];
				}		
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";				
			break;
		}
		
		
		
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND u.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND u.depot_id = ".$params['query']['dp'];
		}
		
		$orderCount = $this->query("SELECT count(o.id) as oentry FROM orders o INNER JOIN users u ON u.id = o.user_id WHERE $oC $rgC $dpC");
		$rSaleCount = $this->query("SELECT count(rs.id) as rsentry FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $rdC $rgC $dpC");
		$visitCount = $this->query("SELECT count(cv.id) as ventry FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $cvC $rgC $dpC");
		
		$output['effectiveSales'] = $orderCount['0']['0']['oentry'];
		$output['effectiveRdSales'] = $rSaleCount['0']['0']['rsentry'];
		$output['effectiveVisit'] = $visitCount['0']['0']['ventry'];
		
		$totalCalls = $orderCount['0']['0']['oentry'] + $rSaleCount['0']['0']['rsentry'] + $visitCount['0']['0']['ventry'];
		
		$result = array();
		$orderEntry = $this->query("SELECT count(u.id) as oentry, o.user_id, u.first_name, u.last_name FROM orders o INNER JOIN users u ON u.id = o.user_id WHERE $oC $rgC $dpC GROUP BY u.id");
		$rSaleEntry = $this->query("SELECT count(u.id) as rsentry, rs.user_id, u.first_name, u.last_name FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $rdC $rgC $dpC GROUP BY u.id");
		$visitEntry = $this->query("SELECT count(u.id) as ventry, cv.user_id, u.first_name, u.last_name FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $cvC $rgC $dpC GROUP BY u.id");
		if ($orderEntry){
			foreach ($orderEntry as $oe){
				$uid = $oe['o']['user_id'];
				$result[$uid]['orderEntry'] = $oe;
				$result[$uid]['name'] = $oe['u']['first_name'].' '.$oe['u']['last_name'];
			}
		}
		
		if ($rSaleEntry){
			foreach ($rSaleEntry as $rse){
				$uid = $rse['rs']['user_id'];
				$result[$uid]['rsEntry'] = $rse;
				$result[$uid]['name'] = $rse['u']['first_name'].' '.$rse['u']['last_name'];
			}
		}
		
		if ($visitEntry){
			foreach ($visitEntry as $cve){
				$uid = $cve['cv']['user_id'];
				$result[$uid]['visitEntry'] = $cve;
				$result[$uid]['name'] = $cve['u']['first_name'].' '.$cve['u']['last_name'];
			}
		}
		
		$output['totalCalls'] = $totalCalls;
		$output['result'] = $result;
		//pr($output);
		return $output;
	}
	
/**
 * numberOfCustomers 
 * @params : array
 * return array
 */
	public function numberOfCustomers($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "c.created LIKE '".$date."%'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));				
				$cC = "c.created 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'];
				}	
				$cC = "c.created between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$cC = "c.created between '$d1' AND '$d2 23:59:59'";
				
			break;
		}
		$rgC = $dpC = "";
		if(!empty($params['query']['rg'])){
			$rgC = " AND c.region_id = ".$params['query']['rg']; 
		}
		if(!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp']; 
		}
		$customerCreated = $this->query("SELECT COUNT(*) as totCustomer FROM customers c WHERE $cC $rgC $dpC");
		
		$numberOfCustomer = $this->query("SELECT u.id, u.first_name, u.last_name, COUNT(c.id) as totCust FROM users u INNER JOIN customers c ON c.user_id = u.id WHERE $cC $rgC $dpC GROUP BY c.user_id");
		
		$result['totCustomer'] = $customerCreated['0']['0']['totCustomer'];
		$result['userCustomer'] = $numberOfCustomer;
		
		return $result;
	}
/**
 * numberOfCustomers 
 * @params : array
 * return array
 */
	public function numberofVisits($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "c.created LIKE '".$date."%'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));				
				$cC = "c.created 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'];
				}	
				$cC = "c.created between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$cC = "c.created between '$d1' AND '$d2'";
				
			break;
		}
		
		$customerVists = $this->query("SELECT COUNT(*) as totVisits FROM customer_visits c WHERE $cC");
		
		$numberOfVists = $this->query("SELECT u.id, u.first_name, u.last_name, COUNT(c.id) as totVisit FROM users u INNER JOIN customer_visits c ON c.user_id = u.id WHERE $cC GROUP BY c.user_id");
		
		$result['totVisits'] = $customerVists['0']['0']['totVisits'];
		$result['userVisits'] = $numberOfVists;
		
		return $result;
	}
/**
 * customer_notvisits 
 * @params : array
 * return array
 */
	public function customerNotvisitConditions($params, &$paginate, &$conditions){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){	
			case 'daily':
				$oC = "AND Order.order_date = '".$date."'";
				$cvC = "AND CustomerVisit.visiting_date LIKE '".$date."%'";
				$Rc = " AND RSale.sale_date LIKE '".$date."%'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));				
				$oC = "AND Order.order_date between '$d1' AND '$d2'";
				$cvC = "AND CustomerVisit.visiting_date between '$d1' AND '$d2'";
				$Rc = " AND RSale.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'];
				}	
				$oC = "AND Order.order_date between '$d1' AND '$d2'";
				$cvC = "AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
				$Rc = " AND RSale.sale_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']);
				
				$oC = "AND Order.order_date between '$d1' AND '$d2'";
				$cvC = "AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
				$Rc = " AND RSale.sale_date between '$d1' AND '$d2 23:59:59'";
			break;
		}
		
		if (!empty($params['query']['rg'])){
			$conditions['Customer.region_id'] = $params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$conditions['Customer.depot_id'] = $params['query']['dp'];
		}
		
		$paginate['paramType'] = 'querystring';
		
		$paginate['joins'][] = array('table' => 'orders', 'type'=>'left', 'alias'=>'Order', 'conditions'=>array('Customer.id=Order.customer_id '.$oC));
		
		$paginate['joins'][] = array('table' => 'customer_visits', 'type'=>'left', 'alias'=>'CustomerVisit', 'conditions'=>array('Customer.id=CustomerVisit.customer_id '.$cvC));
		
		$paginate['joins'][] = array('table' => 'redistributor_sales', 'type' => 'left', 'alias' => 'RSale', 'conditions' => array('Customer.id = RSale.customer_id'.$Rc));
		if(!empty($params['isVisit']) && $params['isVisit'] == 1){
			$conditions['OR'] = array('RSale.id IS NOT null','Order.id IS NOT null', 'CustomerVisit.id IS NOT null');
		}else{
			$conditions['CustomerVisit.id'] = null;
			$conditions['Order.id'] = null;
			$conditions['RSale.id'] = null;			
		}
		$paginate['limit'] = 50;
		
	}
	
	function averageCalls($params){
		$output = array();
		$date = $params['date'];
		switch($params['type']){
			case 'daily':
				$oC = "o.order_date = '$date'";
				$rdC = "rs.sale_date = '$date'";
				$cvC = "cv.visiting_date LIKE '".$date."%'";
			break;
			
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";				
			break;
		}
		
		
		
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND u.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND u.depot_id = ".$params['query']['dp'];
		}
		$result = array();
		$orderCount = $this->query("SELECT count(o.id) as oentry FROM orders o INNER JOIN users u ON u.id = o.user_id WHERE $oC $rgC $dpC");
		$rSaleCount = $this->query("SELECT count(rs.id) as rsentry FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $rdC $rgC $dpC");
		$visitCount = $this->query("SELECT count(cv.id) as ventry FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $cvC $rgC $dpC");
		
		$output['effectiveSales'] = $orderCount['0']['0']['oentry'];
		$output['effectiveRdSales'] = $rSaleCount['0']['0']['rsentry'];
		$output['effectiveVisit'] = $visitCount['0']['0']['ventry'];
		
		$result['totalCalls'] = $orderCount['0']['0']['oentry'] + $rSaleCount['0']['0']['rsentry'] + $visitCount['0']['0']['ventry'];
		
		$users = $this->query("SELECT COUNT(*) AS totUser FROM users u INNER JOIN roles r ON r.id = u.role_id WHERE u.status = ".ACTIVE." AND r.grade > 3 $rgC $rgC");
		//$customers = $this->query("SELECT COUNT(*) AS totCustomer FROM customers u WHERE status!=2 $rgC $rgC");
		//$result['totCustomer'] = $customers['0']['0']['totCustomer'];
		$result['totUser'] = $users['0']['0']['totUser'];
		$result['avgCall'] = 0;
		if ($result['totUser'] > 0){
			$result['avgCall'] = number_format( ($result['totalCalls'] / $result['totUser']), 2);
		}
		return $result;
	}
	
	
	function customerTrend($params, &$paginate, &$conditions){
		$output = array();
		
		$date = ($params['opt'])?$params['opt']:date('Y-m-d');
		$strtotime = strtotime($date);
		
		$d1 = date('Y-m-d', strtotime('-30 days', $strtotime) );
		$d2 = $date;
		
		$d4 = date('Y-m-d', strtotime('-31 days', $strtotime) );
		$d3 = date('Y-m-d', strtotime('-60 days', $strtotime) );
		
		if (!empty($params['query']['rg'])){
			$conditions['Customer.region_id'] = $params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$conditions['Customer.depot_id'] = $params['query']['dp'];
		}
		
		
	
		//$paginate['limit'] = 20;
		$paginate['type'] = $params['type'];
		$paginate['model'] = 'Cutomer';
		
		$paginate['query'] = $params['query'];
		$paginate['dates'] = compact('d1', 'd2', 'd3', 'd4');
		
		return compact('d1', 'd2', 'd3', 'd4');
	}
	
	public function paginate( $conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
		$order = 'totPurchase DESC';
		if($extra['type'] == 'up'){
			$having = "totPurchase > totPurchase2";
		}elseif($extra['type'] == 'same'){
			$having = "totPurchase = totPurchase2";
		}elseif($extra['type'] == 'down'){
			$having = "totPurchase < totPurchase2";
			$order = 'totPurchase2 DESC';
		}
		$conditions = '';
		//pr($extra);
		if (!empty($extra['query']['rg'])){
			$conditions .= ' AND Customer.region_id = '.$extra['query']['rg'];
		}
		if (!empty($extra['query']['dp'])){
			$conditions .= ' AND Customer.depot_id = '.$extra['query']['dp'];
		}
		if(empty($extra['query']['export'])){
			$start = ($page-1)*$limit;
			$limitStr = "LIMIT $start,  $limit";
		}else{
			$offset = $extra['query']['offset'];
			if($offset == ''){$offset = 0;}	
			$limitStr = "LIMIT $offset,  10000";
		}
		$result = $this->query("SELECT Customer.id, Customer.first_name, Customer.last_name,  SUM(Sale.`cal_qty`) AS totPurchase, Sale2.tot2 as totPurchase2 FROM `customers` AS Customer LEFT JOIN `sales` AS Sale ON Sale.customer_id = Customer.id LEFT JOIN (SELECT s2.customer_id AS customer_id, SUM(s2.cal_qty) as tot2 FROM sales as s2 WHERE s2.sale_date between '".$extra['dates']['d3']."' AND '".$extra['dates']['d4']."' GROUP BY s2.customer_id ) AS Sale2 ON Sale.customer_id = Sale2.customer_id WHERE Sale.sale_date between '".$extra['dates']['d1']."' AND '".$extra['dates']['d2']."' $conditions GROUP BY Sale.`customer_id` having $having ORDER BY $order $limitStr");
		return $result;
	}

	public function paginateCount($conditions = null, $recursive = 0,$extra = array()) {
		
		if($extra['type'] == 'up'){
			$having = "totPurchase > totPurchase2";
		}elseif($extra['type'] == 'same'){
			$having = "totPurchase = totPurchase2";
		}elseif($extra['type'] == 'down'){
			$having = "totPurchase < totPurchase2";
			$order = 'totPurchase2 DESC';
		}
		$conditions = '';
		
		if (!empty($extra['query']['rg'])){
			$conditions .= ' AND Customer.region_id = '.$extra['query']['rg'];
		}
		if (!empty($extra['query']['dp'])){
			$conditions .= ' AND Customer.depot_id = '.$extra['query']['dp'];
		}
		$countResult = $this->query("SELECT Customer.id, Customer.first_name, Customer.last_name,  SUM(Sale.`cal_qty`) AS totPurchase, Sale2.tot2 as totPurchase2 FROM `customers` AS Customer LEFT JOIN `sales` AS Sale ON Sale.customer_id = Customer.id LEFT JOIN (SELECT s2.customer_id AS customer_id, SUM(s2.cal_qty) as tot2 FROM sales as s2 WHERE s2.sale_date between '".$extra['dates']['d3']."' AND '".$extra['dates']['d4']."' GROUP BY s2.customer_id ) AS Sale2 ON Sale.customer_id = Sale2.customer_id WHERE Sale.sale_date between '".$extra['dates']['d1']."' AND '".$extra['dates']['d2']."' $conditions GROUP BY Sale.`customer_id` having $having");
		return count($countResult);
}
/********************************************************* PK Task *****************************************************/

/**
 * numberOfQuantitySale 
 * @params : array
 * return array
 */
	public function numberOfQuantitySale($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "c.sale_date = '".$date."'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$cC = "c.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'];
				}	
				$cC = "c.sale_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$cC = "c.sale_date between '$d1' AND '$d2'";
				
			break;
		}
		
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND d.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp'];
		}
		
		$saleCreated = $this->query("SELECT SUM(c.cal_qty) as quantity FROM sales c LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC");
        
		$numberOfSale = $this->query("SELECT u.id, u.first_name, u.last_name, u.depot_id, SUM(c.cal_qty) as totSale FROM users u INNER JOIN sales c ON c.user_id = u.id LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC GROUP BY c.user_id");
		
		$result['totSale'] = $saleCreated['0']['0']['quantity'];
		$result['userSale'] = $numberOfSale;
		
		return $result;
	}

  /**
 * ridistribtutionSale 
 * @params : array
 * return array
 */
	public function ridistribtutionSale($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "c.sale_date = '".$date."'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$cC = "c.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'];
				}	
				$cC = "c.sale_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$cC = "c.sale_date between '$d1' AND '$d2'";
				
			break;
		}
		
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND d.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp'];
		}
		
		$redistributorCreated = $this->query("SELECT SUM(c.cal_qty) as quantity FROM redistributor_saleitems c LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC");
        
		$numberOfSale = $this->query("SELECT u.id, u.first_name, u.last_name, u.depot_id, SUM(c.cal_qty) as totSale FROM users u INNER JOIN redistributor_saleitems c ON c.user_id = u.id LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC GROUP BY c.user_id");
		
		$result['totSale'] = $redistributorCreated['0']['0']['quantity'];
		$result['userSale'] = $numberOfSale;
		
		return $result;
	}
 /**
 * totalAmountonPayment 
 * @params : array
 * return array
 */
	public function totalAmountonPayment($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "c.payment_date = '".$date."'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$cC = "c.payment_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'];
				}	
				$cC = "c.payment_date between '$d1' AND '$d2'";
			break;

			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$cC = "c.payment_date between '$d1' AND '$d2'";
				
			break;
		}
		
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND d.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp'];
		}
		
		$totalAmountCreated = $this->query("SELECT SUM(c.amount) as amount FROM payments c LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC");
        
		$amountOfSale = $this->query("SELECT u.id, u.first_name, u.last_name, u.depot_id, SUM(c.amount) as totSale FROM payments c Left JOIN users u ON c.user_id = u.id LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC GROUP BY c.user_id");
		
		$result['totSale'] = $totalAmountCreated['0']['0']['amount'];
		$result['userSale'] = $amountOfSale;
		
		return $result;
	}

 /**
 * totalExpenceAmountonSale 
 * @params : array
 * return array
 */
	public function totalExpenceAmounton($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "c.exp_date = '".$date."'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$cC = "c.exp_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'];
				}	
				$cC = "c.exp_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$cC = "c.exp_date between '$d1' AND '$d2'";
				
			break;
		}
		
		$rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$rgC = " AND d.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$dpC = " AND c.depot_id = ".$params['query']['dp'];
		}
		
		$totalExpAmountCreated = $this->query("SELECT SUM(c.exp_amount) as amount FROM expenses c LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC");
        
		$amountOfExp = $this->query("SELECT u.id, u.first_name, u.last_name, u.depot_id, SUM(c.exp_amount) as totSale FROM users u INNER JOIN expenses c ON c.user_id = u.id LEFT JOIN depots d ON c.depot_id = d.id WHERE $cC $rgC $dpC GROUP BY c.user_id");
		
		$result['totSale'] = $totalExpAmountCreated['0']['0']['amount'];
		$result['userSale'] = $amountOfExp;
		
		return $result;
	}
 /**
 * totalExpenceAmountonSale 
 * @params : array
 * return array
 */
	public function averevenueonSale($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
				
		$totalSaleCreated = $this->query("SELECT SUM(cal_qty) as qty,SUM(amount) as amount FROM sales c WHERE 1");
		$totalRedistCreated = $this->query("SELECT SUM(cal_qty) as qty,SUM(amount) as amount FROM redistributor_saleitems c WHERE 1");
		        
		$result['totSaleAmount'] = $totalSaleCreated['0']['0']['amount'] + $totalRedistCreated['0']['0']['amount'];
		$result['totSaleQty'] = $totalSaleCreated['0']['0']['qty'] + $totalRedistCreated['0']['0']['qty'];
      
		return $result;
	}


/**
 * totalExpenceAmountonSale 
 * @params : array
 * return array
 */
	public function totalNumberOfActiveCustomer($params, &$paginate, &$conditions, &$customer, &$activeCust){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "Order.order_date = '".$date."'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));				
				$cC = "Order.order_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'];
				}	
				$cC = "Order.order_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);				
				$cC = "Order.order_date between '$d1' AND '$d2'";				
			break;
		}
        
		$customer->virtualFields['totPurchase'] = "SUM(Order.total)";
		$paginate['order'] = array("totPurchase" => "desc");
		
		
		$paginate['fields'] = array("Customer.id", "Customer.first_name", "Customer.last_name", "SUM(Order.total) as totPurchase");
		
		if (!empty($params['query']['rg'])){
			$conditions['Customer.region_id'] = $params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$conditions['Customer.depot_id'] = $params['query']['dp'];
		}
		
		$join=$paginate['joins'][] = array('table' => 'orders', 'type'=>'inner', 'alias'=>'Order', 'conditions'=>array("Customer.id=Order.customer_id AND $cC "));	
		$paginate['limit'] = 50;
		$group=$paginate['group'] = array('Customer.id');

		if (!empty($this->request->query['export'])){
			$activeCust = $this->Customer->find('all',array('conditions'=>$conditions,'joins'=>$join,'group'=>$group));
			
		}
		
	}
/**
 * totalExpenceAmountonSale 
 * @params : array
 * return array
 */
	public function purchaseCustomer($params){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){
			case 'daily':
				$cC = "c.sale_date = '".$date."'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				
				$cC = "c.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'];
				}	
				$cC = "c.sale_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$cC = "c.sale_date between '$d1' AND '$d2'";
				
			break;
		}
        
		
		$totalCountData = $this->query("SELECT SUM(c.cal_qty) as tot FROM sales c");
		$totalCountData2 = $this->query("SELECT COUNT(DISTINCT(c.customer_id)) as cust FROM sales c");

		if(isset($totalCountData['0']['0']['tot']))
		$result['totAmount'] = $totalCountData['0']['0']['tot'];		

		if(isset($totalCountData2['0']['0']['cust']))
		$result['totCustomer'] = $totalCountData2['0']['0']['cust'];
		return $result;
	}
	
	
	 /**
 * totalExpenceAmountonSale 
 * @params : array
 * return array
 */
	public function avecostCase($params){
		
		$result = array();
				
		$totalSaleCreated = $this->query("SELECT SUM(cal_qty) as qty,SUM(amount) as amount FROM sales c WHERE 1");
		//$totalRedistCreated = $this->query("SELECT SUM(cal_qty) as qty,SUM(amount) as amount FROM redistributor_saleitems c WHERE 1");
		$totalExpense = $this->query("SELECT SUM(exp_amount) totExp FROM expenses");        
		/*Tot expenses + total value of free issue through campaign + tot value of incentive/ tot quantity of sales (note redistributions sales is should not be part of tot sales) */
		/*Yes (ID = 1): 	Case = 21000
				Roll = 420
				Pack = 42
		Peter Field (ID = 3): Case = 20000
					 Roll = 400
					 Pack = 20
		Allstars (ID = 4): 	Case = 17500
					Roll = 350
					Pack = 17.5 */
		$fItemAmt = $incentiveAmt = 0;	
		$freeItems = $this->query("SELECT brand_id, SUM(cal_qty) as totQty FROM free_items FreeItem GROUP BY brand_id");
		$incentives = $this->query("SELECT brand_id, SUM(cal_qty) as totQty FROM incentives Incentive GROUP BY brand_id");
		
		if($freeItems){
			foreach ($freeItems as $fi){
				if (!empty($fr['FreeItem']['brand_id'])){
					if ($fr['FreeItem']['brand_id'] == 1){
						$fItemAmt += 21000 * $fr['0']['totQty'];
					}elseif($fr['FreeItem']['brand_id'] == 3){
						$fItemAmt += 20000 * $fr['0']['totQty'];
					}elseif($fr['FreeItem']['brand_id'] == 4){
						$fItemAmt += 17500 * $fr['0']['totQty'];
					}
				}
			}
		}
		
		if($incentives){
			foreach ($incentives as $inc){
				if (!empty($inc['Incentive']['brand_id'])){
					if ($inc['Incentive']['brand_id'] == 1){
						$incentiveAmt += 21000 * $inc['0']['totQty'];
					}elseif($inc['Incentive']['brand_id'] == 3){
						$incentiveAmt += 20000 * $inc['0']['totQty'];
					}elseif($inc['Incentive']['brand_id'] == 4){
						$incentiveAmt += 17500 * $inc['0']['totQty'];
					}
				}
			}
		}
		//$result['totSaleAmount'] = $totalSaleCreated['0']['0']['amount'] ;
		$totalAmount = $totalExpense['0']['0']['totExp'] + $fItemAmt + $incentiveAmt;
		$result['totSaleQty'] = $totalSaleCreated['0']['0']['qty'];
		$result['aveCaseCost'] = 0;
		if ($result['totSaleQty'] > 0){
			$result['aveCaseCost'] = $totalAmount / $result['totSaleQty'];
		}
		
		return $result;
	}
	
	public function customer_status($cids=array(), $isAll=false){
		$sales_cond = 1;
		$visits_cond = 1;
		$rdsales_cond = 1;
		if(count($cids) > 0 && $isAll == false){
			$sales_cond = "o.customer_id IN (".implode(",", $cids).")";
			$visits_cond = "cv.customer_id IN (".implode(",", $cids).")";
			$rdsales_cond = "rs.customer_id IN (".implode(",", $cids).")";
		}
		$result = array();
		
		$customerSales = $this->query("SELECT o.customer_id, o.user_id, u.role_id FROM `orders` AS o INNER JOIN users as u ON o.user_id = u.id WHERE $sales_cond GROUP BY o.customer_id, o.user_id");		
		if($customerSales){
			foreach ($customerSales as $customerSale){
				$cid = $customerSale['o']['customer_id'];
				$uid = $customerSale['o']['user_id'];				
				$result[$cid]['sales'][$uid] = $customerSale['u']['role_id'];
			}
		}
		
		$customerVisits = $this->query("SELECT cv.customer_id, cv.user_id, u.role_id FROM `customer_visits` AS cv INNER JOIN users as u ON cv.user_id = u.id WHERE $visits_cond GROUP BY cv.customer_id, cv.user_id");		
		if($customerVisits){
			foreach ($customerVisits as $customerVisit){
				$cid = $customerVisit['cv']['customer_id'];
				$uid = $customerVisit['cv']['user_id'];				
				$result[$cid]['visits'][$uid] = $customerVisit['u']['role_id'];
			}
		}
		
		$customerRdSales = $this->query("SELECT rs.customer_id, rs.user_id, u.role_id FROM `redistributor_sales` AS rs INNER JOIN users as u ON rs.user_id = u.id WHERE $rdsales_cond GROUP BY rs.customer_id, rs.user_id");		
		if($customerRdSales){
			foreach ($customerRdSales as $customerRdSale){
				$cid = $customerRdSale['rs']['customer_id'];
				$uid = $customerRdSale['rs']['user_id'];				
				$result[$cid]['rdsales'][$uid] = $customerRdSale['u']['role_id'];
			}
		}
		
		return $result;
	}
	
	
/**
 * routeCustNotvisitCond 
 * @params : array
 * return array
 */
	public function routeCustNotvisitCond($params, &$paginate, &$conditions){
		//pr($params);
		$reportArr = array();
		$date = $params['date'];
		$result = array();
		switch($params['type']){	
			case 'daily':
				$oC = "AND Order.order_date = '".$date."'";
				$cvC = "AND CustomerVisit.visiting_date LIKE '".$date."%'";
			break;
			case 'weekly':
				$d1 = date('Y-m-d', $params['strtotime']);
				$d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));				
				$oC = "AND Order.order_date between '$d1' AND '$d2'";
				$cvC = "AND CustomerVisit.visiting_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'];
				}	
				$oC = "AND Order.order_date between '$d1' AND '$d2'";
				$cvC = "AND CustomerVisit.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']);
				
				$oC = "AND Order.order_date between '$d1' AND '$d2'";
				$cvC = "AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
				
			break;
		}
		
		if (!empty($params['query']['rg'])){
			$conditions['Customer.region_id'] = $params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$conditions['Customer.depot_id'] = $params['query']['dp'];
		}
		
		$paginate['paramType'] = 'querystring';
		
		//$paginate['joins'][] = array('table' => 'routes', 'type'=>'left', 'alias'=>'Route', 'conditions'=>array('Customer.route_id=Route.id'));
		$paginate['joins'][] = array('table' => 'orders', 'type'=>'left', 'alias'=>'Order', 'conditions'=>array('Customer.id=Order.customer_id '.$oC));
		
		$paginate['joins'][] = array('table' => 'customer_visits', 'type'=>'left', 'alias'=>'CustomerVisit', 'conditions'=>array('Customer.id=CustomerVisit.customer_id '.$cvC));
		if(!empty($params['isVisit']) && $params['isVisit'] == 1){
			$conditions['OR'] = array('Order.id IS NOT null', 'CustomerVisit.id IS NOT null');
		}else{
			$conditions['CustomerVisit.id'] = null;
			$conditions['Order.id'] = null;	
		}
		$paginate['limit'] = 50;
		
	}
	
	// Time sheet
	function dayUserCalls($params){
		$output = array();
		$date = $params['date'];
				$date1 = $date.' 06:00:00';
		$date2 = $date.' 23:59:59';
		switch($params['type']){
			case 'daily':
				$oC = "o.order_date = '$date'";
				$toC =  "o.created LIKE '".$date."%'";
				$rdC = "rs.sale_date = '$date'";
				$roC =  "rs.created LIKE '".$date."%'";
				$cvC = "cv.visiting_date LIKE '".$date."%'";
				$trk = 'ut.created BETWEEN "'.$date1.'" AND "'.$date2.'"';
			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'];
				}		
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
				$d1 = date('Y-m-01', $params['strtotime']);
				$d2 = date('Y-m-t', $params['strtotime']);
				
				$oC = "o.order_date between '$d1' AND '$d2'";
				$rdC = "rs.sale_date between '$d1' AND '$d2'";
				$cvC = "cv.visiting_date between '$d1' AND '$d2'";				
			break;
		}
		
		
		
		$where = $rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$where = "where ";
			$rgC = " AND u.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$where = "where ";
			$dpC = " AND u.depot_id = ".$params['query']['dp'];
		}
		
		$orderCount = $this->query("SELECT count(o.id) as oentry FROM orders o INNER JOIN users u ON u.id = o.user_id WHERE $oC $rgC $dpC");
		$rSaleCount = $this->query("SELECT count(rs.id) as rsentry FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $rdC $rgC $dpC");
		$visitCount = $this->query("SELECT count(cv.id) as ventry FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $cvC $rgC $dpC");
		$totalCalls = $orderCount['0']['0']['oentry'] + $rSaleCount['0']['0']['rsentry'] + $visitCount['0']['0']['ventry'];
		
		$result = array();
		
		$orderEntry1 = $this->query("SELECT sum(s.quantity) as oentry, count(DISTINCT s.order_id) as ocnt, o.user_id, u.first_name, u.last_name FROM orders o INNER JOIN users u ON u.id = o.user_id inner join sales s on s.order_id = o.id  WHERE $oC $rgC $dpC GROUP BY u.id");
		$rSaleEntry1 = $this->query("SELECT sum(rsi.quantity) as rsentry , count(DISTINCT rsi.redistributor_sale_id) as rscnt, rs.user_id, u.first_name, u.last_name FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id inner join redistributor_saleitems rsi on rsi.redistributor_sale_id = rs.id WHERE $rdC $rgC $dpC GROUP BY u.id");
		$visitEntry1 = $this->query("SELECT count(u.id) as ventry, cv.user_id, u.first_name, u.last_name FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $cvC $rgC $dpC GROUP BY u.id");
	//		pr($orderCount);pr($rSaleCount);pr($visitCount);
		//pr($orderEntry1);pr($rSaleEntry1);pr($visitEntry1);
		if ($orderEntry1){
			foreach ($orderEntry1 as $oe){
				$uid = $oe['o']['user_id'];
				$result[$uid]['orderEntry1'] = $oe;
				$result[$uid]['date'] = $date;
			}
		}
		
		if ($rSaleEntry1){
			foreach ($rSaleEntry1 as $rse){
				$uid = $rse['rs']['user_id'];
				$result[$uid]['rsEntry1'] = $rse;
				$result[$uid]['date'] = $date;
			}
		}
		
		if ($visitEntry1){
			foreach ($visitEntry1 as $cve){
				$uid = $cve['cv']['user_id'];
				$result[$uid]['visitEntry1'] = $cve;
				$result[$uid]['date'] = $date;
			}
		}
		$orderEntry = $this->query("SELECT MIN(o.created)as in_time, MAX(o.created) as out_time , o.user_id, u.first_name, u.last_name FROM orders o INNER JOIN users u ON u.id = o.user_id WHERE $oC $rgC $dpC GROUP BY u.id");//pr($orderEntry);
	
		$rSaleEntry = $this->query("SELECT MIN(rs.created)as in_time, MAX(rs.created) as out_time , rs.user_id, u.first_name, u.last_name FROM redistributor_sales rs INNER JOIN users u ON u.id = rs.user_id WHERE $rdC $rgC $dpC GROUP BY u.id");
		$visitEntry = $this->query("SELECT MIN(cv.visiting_date)as in_time, MAX(cv.visiting_date) as out_time ,  cv.user_id, u.first_name, u.last_name FROM customer_visits cv INNER JOIN users u ON u.id = cv.user_id WHERE $cvC $rgC $dpC GROUP BY u.id");

		$gap_in_calls = $this->query("SELECT data.user_id ,data.dates as first_call, max(data1.dates) as second_call,timediff(data.dates,max(data1.dates)) as call_diff from (SELECT o.user_id, o.created as dates FROM `orders` o  WHERE $toC  UNION all SELECT cv.user_id, cv.visiting_date  FROM customer_visits cv WHERE $cvC  UNION ALL select rs.user_id, rs.created from redistributor_sales rs WHERE $roC) data , (SELECT o.user_id, o.created as dates FROM `orders` o  WHERE $toC   UNION all SELECT cv.user_id, cv.visiting_date  FROM customer_visits cv WHERE $cvC   UNION ALL select rs.user_id, rs.created from redistributor_sales rs WHERE $roC ) data1 WHERE data.dates > data1.dates AND data.user_id = data1.user_id GROUP BY data.dates HAVING timediff(data.dates,max(data1.dates)) > '00:30:00' ORDER by data.dates DESC");
		if($gap_in_calls){
			foreach($gap_in_calls as $calls){
				$firstcall = $calls['data']['first_call'];
				$secondcall = $calls['0']['second_call'];
				$uid = $calls['data']['user_id'];
				$result[$uid]['GapInCalls'][] = array('firstcall' =>$firstcall,'secondcall' => $secondcall , 'time_diff' => $calls['0']['call_diff']);
		
			}
		}
		// Difference between created and sync time data
		//$syncedTimediff = $this->query("SELECT data.cdate ,data.mdates , data.user_id , data.mdates - data.cdate as diff from ( SELECT o.user_id as user_id, o.created as cdate, o.modified as mdates FROM `orders` o WHERE $toC UNION all SELECT cv.user_id as user_id, cv.created as cdate, cv.modified as mdates FROM customer_visits cv WHERE $cvC UNION ALL select rs.user_id as user_id, rs.created as cdate, rs.modified as mdates from redistributor_sales rs WHERE $roC )data HAVING (data.mdates - data.cdate ) < '201'");

		/*$syncedTimediff = $this->query("SELECT data.user_id ,data.dates as first_call, max(data1.dates) as second_call,timediff(data.dates,max(data1.dates)) as call_diff from (SELECT o.user_id, o.modified as dates FROM `orders` o  WHERE $toC  UNION all SELECT cv.user_id, cv.modified  FROM customer_visits cv WHERE $cvC  UNION ALL select rs.user_id, rs.modified from redistributor_sales rs WHERE $roC) data , (SELECT o.user_id, o.modified as dates FROM `orders` o  WHERE $toC   UNION all SELECT cv.user_id, cv.modified  FROM customer_visits cv WHERE $cvC   UNION ALL select rs.user_id, rs.modified from redistributor_sales rs WHERE $roC ) data1 WHERE data.dates > data1.dates AND data.user_id = data1.user_id GROUP BY data.dates HAVING timediff(data.dates,max(data1.dates)) < '00:02:00' ORDER by data.dates DESC");
		
		if($syncedTimediff){
			foreach($syncedTimediff as $calls){
				$firstcall = $calls['data']['first_call'];
				$secondcall = $calls['0']['second_call'];
				$uid = $calls['data']['user_id'];
				$result[$uid]['synchTime'][] = array('firstcall' =>$firstcall,'secondcall' => $secondcall , 'time_diff' => $calls['0']['call_diff']);
		
			}
		}
	*/
		$historyResult = $this->query("SELECT * from user_trackings ut where $trk order by created desc");
		if($historyResult ){
		foreach($historyResult as $history){//pr($history);
			$user_id = $history['ut']['user_id'];
			$trkData[$user_id][] = array('lat'=>  $history['ut']['lat'] , 'long' => $history['ut']['long'],'created' => $history['ut']['created'], 'modified' => $history['ut']['modified']   );
		}
		if(isset($trkData)){
			foreach($trkData as $uid => $trk){ 
				 $IntimeKey = max(array_keys($trk));	
				 $outtimeKey = min(array_keys($trk));	
				 $result[$uid]['IntimeData'] = $trk[$IntimeKey];
				 $result[$uid]['OuttimeData'] = $trk[$outtimeKey];

			}
		}
		
		}
		//pr($order);pr($rSale);pr($visit);
		//die();
		if ($orderEntry){
			foreach ($orderEntry as $oe){
				$uid = $oe['o']['user_id'];
				$result[$uid]['orderEntry']['o_in_time']  = date('H:i:s',strtotime($oe['0']['in_time']));
				$result[$uid]['orderEntry']['o_out_time']  = date('H:i:s',strtotime($oe['0']['out_time']));
				$result[$uid]['name'] = $oe['u']['first_name'].' '.$oe['u']['last_name'];
			}
		}
		
		if ($rSaleEntry){
			foreach ($rSaleEntry as $rse){
				$uid = $rse['rs']['user_id'];
				$result[$uid]['orderEntry']['rs_in_time']  = date('H:i:s',strtotime($rse['0']['in_time']));
				$result[$uid]['orderEntry']['rs_out_time']  = date('H:i:s',strtotime($rse['0']['out_time']));
				$result[$uid]['name'] = $rse['u']['first_name'].' '.$rse['u']['last_name'];
			}
		}
		
		if ($visitEntry){
			foreach ($visitEntry as $cve){
				$uid = $cve['cv']['user_id'];
				$result[$uid]['orderEntry']['cv_in_time']  = date('H:i:s',strtotime($cve['0']['in_time']));
				$result[$uid]['orderEntry']['cv_out_time']  = date('H:i:s',strtotime($cve['0']['out_time']));
				$result[$uid]['name'] = $cve['u']['first_name'].' '.$cve['u']['last_name'];
			}
		}
		//pr($result);
		$output['totalCalls'] = $totalCalls;
		$output['result'] = $result;
		//pr($output);
		return $output;
	}

	public function syncCalls($params){
		$output = array();
		$date = $params['date'];
				$date1 = $date.' 06:00:00';
		$date2 = $date.' 23:59:59';
		switch($params['type']){
			case 'daily':
				//$oC = "o.order_date = '$date'";
				$oC =  "o.modified LIKE '".$date."%'";
				$rdC =  "rs.modified LIKE '".$date."%'";
				$cvC = "cv.modified LIKE '".$date."%'";
			break;
		}

		$where = $rgC = $dpC = "";
		if (!empty($params['query']['rg'])){
			$where = "where ";
			$rgC = " AND u.region_id = ".$params['query']['rg'];
		}
		if (!empty($params['query']['dp'])){
			$where = "where ";
			$dpC = " AND u.depot_id = ".$params['query']['dp'];
		}
	$result = array();
	$osycn = $this->query("SELECT min(o.modified) as date,  UNIX_TIMESTAMP(o.modified) DIV 120 as timestamp, o.user_id as user_id, count(o.id) as cnt , sum(s.quantity) as qty FROM orders o INNER JOIN sales s on s.order_id = o.id WHERE $oC GROUP BY  UNIX_TIMESTAMP(o.modified) DIV 120, o.user_id having cnt >= 2");
	
	if($osycn){
		foreach($osycn as $call){
			$first_call = $call['0']['date'];
			$uid = $call['o']['user_id'];
			$second_call = date("Y-m-d H:i:s",strtotime($first_call." +2 minutes"));
			$result[$uid]['oSync'][] = array('first_call' => $first_call , 'second_call' => $second_call,'ocount' => $call['0']['cnt'], 'qty' => $call['0']['qty']);
		}
	}
	$cvsycn = $this->query("SELECT min(cv.modified) as date,  UNIX_TIMESTAMP(cv.modified) DIV 120 as timestamp, cv.user_id , count(cv.id) as cnt FROM customer_visits cv WHERE $cvC GROUP BY  UNIX_TIMESTAMP(cv.modified) DIV 120, cv.user_id having cnt >= 2");

	if($cvsycn){
		foreach($cvsycn as $call){ 
			$first_call = $call['0']['date'];
			$uid = $call['cv']['user_id'];
			$second_call = date("Y-m-d H:i:s",strtotime($first_call." +2 minutes"));
			$result[$uid]['cvSync'][] = array('first_call' => $first_call , 'second_call' => $second_call,'ocount' => $call['0']['cnt']);
		}
	}

	$rssycn = $this->query("SELECT min(rs.modified) as date,  UNIX_TIMESTAMP(rs.modified) DIV 120 as timestamp, rs.user_id as user_id, count(rs.id) as cnt , sum(rsi.quantity) as qty FROM redistributor_sales rs  INNER JOIN redistributor_saleitems rsi on rsi.redistributor_sale_id = rs.id  WHERE $rdC GROUP BY  UNIX_TIMESTAMP(rs.modified) DIV 120, rs.user_id having cnt >= 2");

	if($rssycn){
		foreach($rssycn as $call){
			$first_call = $call['0']['date'];
			$uid = $call['rs']['user_id'];
			$second_call = date("Y-m-d H:i:s",strtotime($first_call." +2 minutes"));
			$result[$uid]['rsSync'][] = array('first_call' => $first_call , 'second_call' => $second_call,'ocount' => $call['0']['cnt'] ,'qty' => $call['0']['qty']);
		}
	}
	$output['totalCalls'] = count($osycn) + count($cvsycn) + count($rssycn);
	$output['result'] = $result;
	return $output;
	}
}
