<?php
/**
 * Controller name
 *
 * @var string
 */
App::uses('AppController', 'Controller');
class ApiController extends AppController {
	var $user;
	var $uses = array('Report');
	function beforeFilter(){
		$this->Auth->allow();
	}
	
	function index(){		
		exit;
	}
	
	/* function get_reports(){
		$tot_costomers = $this->Report->query("SELECT COUNT(id) AS tot_cust FROM customers WHERE status = 1");
		$params = $this->request->query;
		if(empty($params['from'])){
			$params['from'] = date('Y-m-d', strtotime("-7days"));
		}
		if(empty($params['to'])){
			$params['to'] = date('Y-m-d').' 23:59:59';
		}else{
			$params['to'] = $params['to'].' 23:59:59';
		}
		
		
		$total_call = $this->total_calls($params);
		//pr($total_call);
		$reports = array(
			'num_of_customers'=> isset($tot_costomers['0']['0']['tot_cust'])?$tot_costomers['0']['0']['tot_cust']:0,
			'visited_customers'=>$this->visited_customers($params),
			'non_visited_customers'=>$this->notvisited_customers($params),
			'no_of_created_customers'=>$this->created_customers($params),
			'customer_trend_up'=>$this->customers_trend($params,'up'),
			'customer_trend_same'=>$this->customers_trend($params,'same'),
			'customer_trend_down'=>$this->customers_trend($params,'down'),						
			'actual_users' => $this->total_users(),
			'active_users'=> $this->active_users($params),
			'non_active_users'=> $this->nonactive_users($params),
			'total_calls'=> isset($total_call['total_call'])?$total_call['total_call']:0,
			'target_calls'=> isset($total_call['call_target'])?$total_call['call_target']:0,
			'effective_sale_call'=> isset($total_call['effective_sale_call'])?$total_call['effective_sale_call']:0,
			'sale_inunit'=> isset($total_call['sale_unit'])?$total_call['sale_unit']:0,
			'rs_sale_inunit'=> isset($total_call['rdsale_unit'])?$total_call['rdsale_unit']:0,
			'expenses'=> $this->total_expense($params),
			'params'=>$this->request->query
		);
		echo json_encode($reports);
		exit;
	} */
	
	function get_reports(){
		
		$params = $this->request->query;
		if(empty($params['from'])){
			$params['from'] = date('Y-m-d', strtotime("-7days"));
		}
		if(empty($params['to'])){
			$params['to'] = date('Y-m-d').' 23:59:59';
		}else{
			$params['to'] = $params['to'].' 23:59:59';
		}
		
		$tot_costomers = $this->Report->query("SELECT COUNT(id) AS tot_cust FROM customers WHERE status = 1");
		$num_of_customers = isset($tot_costomers['0']['0']['tot_cust'])?$tot_costomers['0']['0']['tot_cust']:0;
		$total_call = $this->total_calls($params);
		$visited_customers = $this->visited_customers($params);
		$no_of_created_customers = $this->created_customers($params);
		$actual_users = $this->total_users();
		$active_users = $this->active_users($params);
		
		$reports = array(
			'num_of_customers'=> $num_of_customers,
			'visited_customers'=> $visited_customers,
			'non_visited_customers'=> $num_of_customers - $visited_customers,
			'no_of_created_customers'=> $no_of_created_customers,
			'customer_trend_up'=> $this->customers_trend($params,'up'),
			'customer_trend_same'=>$this->customers_trend($params,'same'),
			'customer_trend_down'=>$this->customers_trend($params,'down'),						
			'actual_users' => $actual_users,
			'active_users'=> $active_users,
			'per_active_users' => ($active_users/$actual_users) * 100,
			'non_active_users'=> $actual_users - $active_users,
			'total_calls'=> isset($total_call['total_call'])?$total_call['total_call']:0,
			'target_calls'=> isset($total_call['call_target'])?$total_call['call_target']:0,
			'effective_sale_call'=> isset($total_call['effective_sale_call'])?$total_call['effective_sale_call']:0,
			'sale_inunit'=> isset($total_call['sale_unit'])?$total_call['sale_unit']:0,
			'rs_sale_inunit'=> isset($total_call['rdsale_unit'])?$total_call['rdsale_unit']:0,
			'expenses'=> $this->total_expense($params),
			'params'=>$this->request->query
		);
		echo json_encode($reports);
		exit;
	}
	
	
	function getrp(){
		$tot_costomers = $this->Report->query("SELECT COUNT(id) AS tot_cust FROM customers WHERE status = 1");
		$params = $this->request->query;
		if(empty($params['from'])){
			$params['from'] = date('Y-m-d', strtotime("-7days"));
		}
		if(empty($params['to'])){
			$params['to'] = date('Y-m-d').' 23:59:59';
		}else{
			$params['to'] = $params['to'].' 23:59:59';
		}
		$total_call = $this->total_calls($params);
		
		$targetQry = $this->Report->query("SELECT target FROM user_targets AS Target WHERE type='Call'");
		
		//pr($total_call);
		$reports = array(
			'num_of_customers'=> isset($tot_costomers['0']['0']['tot_cust'])?$tot_costomers['0']['0']['tot_cust']:0,
			'visited_customers'=>$this->visited_customers($params),
			'non_visited_customers'=>$this->notvisited_customers($params),
			'no_of_created_customers'=>$this->created_customers($params),
			'customer_trend_up'=>$this->customers_trend($params,'up'),
			'customer_trend_same'=>$this->customers_trend($params,'same'),
			'customer_trend_down'=>$this->customers_trend($params,'down'),						
			'actual_users' => $this->total_users(),
			'active_users'=> $this->active_users($params),
			'non_active_users'=> $this->nonactive_users($params),
			'total_calls'=> isset($total_call['total_call'])?$total_call['total_call']:0,
			'target_calls'=> isset($total_call['call_target'])?$total_call['call_target']:0,
			'effective_sale_call'=> isset($total_call['effective_sale_call'])?$total_call['effective_sale_call']:0,
			'sale_inunit'=> isset($total_call['sale_unit'])?$total_call['sale_unit']:0,
			'rs_sale_inunit'=> isset($total_call['rdsale_unit'])?$total_call['rdsale_unit']:0,
			'expenses'=> $this->total_expense($params),
			'params'=>$this->request->query
		);
		
		$this->set('reports', $reports);
	}
	
	
	function get_customers_visits_report(){
		
		
		$params = $this->request->query; 
		if(empty($params['from'])){
			$params['from'] = date('Y-m-01');
		}
		if(empty($params['to'])){
			$params['to'] = date('Y-m-d').' 23:59:59';
		}else{
			$params['to'] = $params['to'].' 23:59:59';
		}
		
		$d1 = $params['from']; 
		$d2 = $params['to']; 
		
		if(!empty($params['t-month'])){
            $d1Arrar['1'] = $params['t-month'];
        }else{
            $d1Arrar = explode("-", $params['from']); //pr($d1Arrar);
        }
		
		
		//pr($d1Arrar['1']);
		
		$tot_costomers = $this->Report->query("SELECT COUNT(id) AS tot_cust FROM customers WHERE status = 1");
		
		//Get month names between two dates ($d1 and $d2)
		$months =array();
		/* $time   = strtotime($d1);
		$last   = date('M-Y', strtotime($d2));
		do {
			$month = date('M-Y', $time);
			$total = date('t', $time);
			
			if(!in_array(date('m', $time), $months)){
				$months[]=date('m', $time);
			}

			$time = strtotime('+1 month', $time);
		} while ($month != $last); //pr($months); die(); */
		//pr(date('Y', strtotime($d1))); pr(date('Y', strtotime($d2)));
		if(date('Y', strtotime($d1)) === date('Y', strtotime($d2))){
			$start   = date('m', strtotime($d1)); //pr($start);
			$end   = date('m', strtotime($d2)); //pr($end); 
			
			foreach (range($start, $end) as $number){
				$months[]=sprintf('%02d', $number);
			}
		}else{
			$start   = date('m', strtotime($d1));
			$end   = date('m', strtotime($d2));
			
			foreach (range($start, 12) as $number){
				if(!in_array(sprintf('%02d', $number), $months)){
					$months[]=sprintf('%02d', $number);
				}
			}
			
			foreach (range(01, $end ) as $number){
				if(!in_array(sprintf('%02d', $number), $months)){
					$months[]=sprintf('%02d', $number);
				}
			}
		}
		//pr($months);
			 
		
		$targets['Call'] = $targets['Sale'] = $targets['Redistribution'] = $targets['Customer'] = 0;
		
		$allTargetQry = $this->Report->query("SELECT target, type FROM user_targets AS Target WHERE user_id IN (SELECT id FROM users WHERE status = 1)");
				
		if($allTargetQry){
			foreach($allTargetQry as $target){		
				
				switch($target['Target']['type']){
					case 'Customer':
						$targets['Customer'] += $target['Target']['target'];
						break;
						
					default:
						$userTargets = @unserialize($target['Target']['target']); //pr($userTargets);
						$targets[$target['Target']['type']] += !empty($userTargets[$d1Arrar['1']]) ? $userTargets[$d1Arrar['1']] : 0;
						
						/* foreach($months as $m){ 
							$targets[$target['Target']['type']] += !empty($userTargets[$m]) ? $userTargets[$m] : 0;
						} */
				}
			}
		}
		
		$calls = array();
		
		$orderEntry = $this->Report->query("SELECT count(DISTINCT sl.order_id) as oentry, SUM(sl.cal_qty) AS totQty FROM sales sl WHERE sl.sale_date between '$d1' AND '$d2' ");
		
		$rSaleEntry = $this->Report->query("SELECT count(DISTINCT rsi.redistributor_sale_id) as rsentry, SUM(rsi.cal_qty) AS totQty FROM redistributor_saleitems rsi WHERE rsi.sale_date between '$d1' AND '$d2'");
		
		$consorderEntry = $this->Report->query("SELECT count(DISTINCT sl.consumer_order_id) as oentry, SUM(sl.cal_qty) AS totQty FROM consumer_sales sl WHERE sl.sale_date between '$d1' AND '$d2' ");
		
		$rConsSaleEntry = $this->Report->query("SELECT count(DISTINCT rsi.consumer_redistribution_order_id) as rsentry, SUM(rsi.cal_qty) AS totQty FROM consumer_redistribution_sales rsi WHERE rsi.sale_date between '$d1' AND '$d2'");
		
		$cvisityear_tables = customerVisitYearTables(@$params['from'], @$params['to']); //pr($cvisityear_tables);
		
		if(!empty($cvisityear_tables)){
            if(count($cvisityear_tables)>1){
                $cvisityear_tabSqlArray = array();
                foreach ($cvisityear_tables as $year => $cv_tableName){
                    $cvisityear_tabSqlArray[] = " SELECT count(cv.user_id) as ventry FROM $cv_tableName as cv WHERE cv.visiting_date between '$d1' AND '$d2'";
                }
                $cvisityear_tabSql = " ".implode(" UNION ALL ", $cvisityear_tabSqlArray)." "; // UNION operation For Multiple customer_visits table as per year wise organized

            }else{
               $cv_tableName = $cvisityear_tables[key($cvisityear_tables)]; // Single customer_visits table as per year wise organized
			   $cvisityear_tabSql = "SELECT count(cv.user_id) as ventry FROM $cv_tableName cv  WHERE  cv.visiting_date between '$d1' AND '$d2'";
            }
          
        }else{
            $cvisityear_tabSql = "SELECT count(cv.user_id) as ventry FROM customer_visits cv  WHERE  cv.visiting_date between '$d1' AND '$d2'";
        }
			  //pr($cvisityear_tabSql);	
		/* $visitEntry = $this->Report->query("SELECT count(cv.user_id) as ventry FROM customer_visits cv  WHERE  cv.visiting_date between '$d1' AND '$d2'");  */
		$visitEntry = $this->Report->query($cvisityear_tabSql);
		
		$calls['sale'] = !empty($orderEntry['0']['0']['oentry']) ? $orderEntry['0']['0']['oentry'] : 0;
		$calls['r_sale'] = !empty($rSaleEntry['0']['0']['rsentry']) ? $rSaleEntry['0']['0']['rsentry'] : 0;
		$calls['con_sale'] = !empty($consorderEntry['0']['0']['oentry']) ? $consorderEntry['0']['0']['oentry'] : 0;
		$calls['con_r_sale'] = !empty($rConsSaleEntry['0']['0']['rsentry']) ? $rConsSaleEntry['0']['0']['rsentry'] : 0;
		
		$calls['sale_qty'] = !empty($orderEntry['0']['0']['totQty']) ? $orderEntry['0']['0']['totQty'] : 0;
		$calls['r_sale_qty'] = !empty($rSaleEntry['0']['0']['totQty']) ? $rSaleEntry['0']['0']['totQty'] : 0;
		$calls['con_sale_qty'] = !empty($consorderEntry['0']['0']['totQty']) ? $consorderEntry['0']['0']['totQty'] : 0;
		$calls['con_r_sale_qty'] = !empty($rConsSaleEntry['0']['0']['totQty']) ? $rConsSaleEntry['0']['0']['totQty'] : 0;
		
		$calls['visits'] = !empty($visitEntry['0']['0']['ventry']) ? $visitEntry['0']['0']['ventry'] : 0;
		
	
		$reports = array(
			'num_of_customers'=> isset($tot_costomers['0']['0']['tot_cust']) ? $tot_costomers['0']['0']['tot_cust'] : 0,
			'target'=> isset($targets) ? $targets : array(),
			'calls'=> $calls,
		);
		echo json_encode($reports);
		exit;
	}
	
	
	function get_active_users_report(){
		
		$params = $this->request->query;
		if(empty($params['from'])){
			$params['from'] = date('Y-m-d', strtotime("-7days"));
		}
		if(empty($params['to'])){
			$params['to'] = date('Y-m-d').' 23:59:59';
		}else{
			$params['to'] = $params['to'].' 23:59:59';
		}
		//pr($total_call);
		$reports = array(				
			'actual_users' => $this->total_users(),
			'active_users'=> $this->active_users($params),
			'params'=>$this->request->query
		);
		echo json_encode($reports);
		exit;
	}
	
	/*function visited_customers($params){
		$visited_cust = $this->Report->query("SELECT COUNT(DISTINCT c.id) tot_vc FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.order_date between '".$params['from']."' AND '".$params['to']."' LEFT JOIN customer_visits cv ON cv.customer_id = c.id AND  cv.visiting_date between '".$params['from']."' AND '".$params['to']."' WHERE c.status = 1 AND (o.id IS NOT null OR cv.id IS NOT null)");
		$tot = isset($visited_cust['0']['0']['tot_vc'])?$visited_cust['0']['0']['tot_vc']:0;
		return $tot;
	}*/
	
	function visited_customers($params){
		$visited_cust = $this->Report->query("SELECT COUNT(DISTINCT c.id) tot_vc FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.order_date between '".$params['from']."' AND '".$params['to']."' LEFT JOIN customer_visits cv ON cv.customer_id = c.id AND  cv.visiting_date between '".$params['from']."' AND '".$params['to']."' LEFT JOIN redistributor_sales rs ON rs.customer_id = c.id AND rs.sale_date between '".$params['from']."' AND '".$params['to']."' WHERE c.status = 1 AND (rs.id IS NOT null OR o.id IS NOT null OR cv.id IS NOT null)");
		$tot = isset($visited_cust['0']['0']['tot_vc'])?$visited_cust['0']['0']['tot_vc']:0;
		return $tot;
	}
	
	/*function notvisited_customers($params){
		$visited_cust = $this->Report->query("SELECT COUNT(DISTINCT c.id) tot_vc FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.order_date between '".$params['from']."' AND '".$params['to']."' LEFT JOIN customer_visits cv ON cv.customer_id = c.id AND  cv.visiting_date between '".$params['from']."' AND '".$params['to']."' WHERE c.status = 1 AND (o.id IS null AND cv.id IS null)");
		$tot = isset($visited_cust['0']['0']['tot_vc'])?$visited_cust['0']['0']['tot_vc']:0;
		return $tot;
	}*/
	function notvisited_customers($params){
		$visited_cust = $this->Report->query("SELECT COUNT(DISTINCT c.id) tot_vc FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.order_date between '".$params['from']."' AND '".$params['to']."' LEFT JOIN customer_visits cv ON cv.customer_id = c.id AND  cv.visiting_date between '".$params['from']."' AND '".$params['to']."' LEFT JOIN redistributor_sales rs ON rs.customer_id = c.id AND rs.sale_date between '".$params['from']."' AND '".$params['to']."' WHERE c.status = 1 AND (rs.id IS null AND o.id IS null AND cv.id IS null)");
		$tot = isset($visited_cust['0']['0']['tot_vc'])?$visited_cust['0']['0']['tot_vc']:0;
		return $tot;
	}
	
	function created_customers($params){
		$created_costomers = $this->Report->query("SELECT COUNT(id) AS tot_cust FROM customers WHERE status = 1 AND created between '".$params['from']."' AND '".$params['to']."'");
		$tot = isset($created_costomers['0']['0']['tot_cust'])?$created_costomers['0']['0']['tot_cust']:0;
		return $tot;
	}
	
	function customers_trend($params , $type){
		$strtotime = strtotime($params['from']);
		$dt1 = date('Y-m-d', strtotime('-30 days', $strtotime) );
		$dt2 = $params['from'];
		$d4 = date('Y-m-d', strtotime('-31 days', $strtotime) );
		$d3 = date('Y-m-d', strtotime('-60 days', $strtotime) );
		$having = "totPurchase > totPurchase2";
		if($type == 'up'){
			$having = "totPurchase > totPurchase2";
		}elseif($type == 'same'){
			$having = "totPurchase = totPurchase2";
		}elseif($type == 'down'){
			$having = "totPurchase < totPurchase2";
		}
		$customers = $this->Report->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 '".$d3."' AND '".$d4."' GROUP BY s2.customer_id ) AS Sale2 ON Sale.customer_id = Sale2.customer_id WHERE Sale.sale_date between '".$dt1."' AND '".$dt2."' GROUP BY Sale.`customer_id` having $having");
		$tot = isset($customers)?count($customers):0;
		return $tot;
	}
	
	function total_users(){
		$tot_users = $this->Report->query("SELECT count(id) as tot_user FROM users WHERE status=".ACTIVE);
		$tot = isset($tot_users['0']['0']['tot_user'])?$tot_users['0']['0']['tot_user']:0;
		return $tot;
	}
	
	function active_users($params){
		$d1 = $params['from'];
		$d2 = $params['to'];
				
		$result = array();
		$orderEntry = $this->Report->query("SELECT o.user_id FROM orders o WHERE o.order_date between '$d1' AND '$d2'");
		
		$rSaleEntry = $this->Report->query("SELECT rs.user_id FROM redistributor_sales rs WHERE rs.sale_date between '$d1' AND '$d2'");
		$visitEntry = $this->Report->query("SELECT cv.user_id FROM customer_visits cv WHERE cv.visiting_date between '$d1' AND '$d2'");
		if ($orderEntry){
			foreach ($orderEntry as $oe){
				$uid = $oe['o']['user_id'];
				$result[$uid]['orderEntry'] = $oe;
			}
		}
		
		if ($rSaleEntry){
			foreach ($rSaleEntry as $rse){
				$uid = $rse['rs']['user_id'];
				$result[$uid]['rsEntry'] = $rse;
			}
		}
		
		if ($visitEntry){
			foreach ($visitEntry as $cve){
				$uid = $cve['cv']['user_id'];
				$result[$uid]['visitEntry'] = $cve;
			}
		}
		
		$tot = !empty($result)?count($result):0;
		return $tot;
	}
	
	function nonactive_users($params){
		$d1 = $params['from'];
		$d2 = $params['to'];
		
		$active_users = $this->Report->query("SELECT COUNT(u.id) AS nact_users
		FROM users u INNER JOIN roles r ON r.id = u.role_id 
		LEFT JOIN (SELECT id, user_id FROM orders WHERE order_date between '$d1' AND '$d2') AS o ON o.user_id = u.id
		LEFT JOIN (SELECT id, user_id FROM redistributor_sales WHERE sale_date between '$d1' AND '$d2') AS rs ON rs.user_id = u.id
		LEFT JOIN (SELECT id, user_id FROM customer_visits WHERE visiting_date between '$d1' AND '$d2') AS  cv ON cv.user_id = u.id 
		WHERE u.status= ".ACTIVE." AND r.grade > 3 AND o.id IS NULL 
		AND cv.id IS NULL 
		AND rs.id IS NULL");
		$tot = isset($active_users['0']['0']['nact_users'])?$active_users['0']['0']['nact_users']:0;
		return $tot;
	}
	
	function total_calls($params){
		$result = array();
		$d1 = $params['from'];
		$d2 = $params['to'];
		
		
		
		
		$orderEntry = $this->Report->query("SELECT SUM(sl.cal_qty) AS totQty, count(DISTINCT sl.order_id) as oentry FROM sales sl WHERE sl.sale_date between '$d1' AND '$d2' ");
		
		$rSaleEntry = $this->Report->query("SELECT SUM(rsi.cal_qty) AS totQty, count(DISTINCT rsi.redistributor_sale_id) as rsentry FROM redistributor_saleitems rsi WHERE rsi.sale_date between '$d1' AND '$d2'");
		
		$visitEntry = $this->Report->query("SELECT count(cv.user_id) as ventry FROM customer_visits cv  WHERE  cv.visiting_date between '$d1' AND '$d2'");
		$result['total_call'] = $orderEntry['0']['0']['oentry'] + $rSaleEntry['0']['0']['rsentry']+$visitEntry['0']['0']['ventry'];
		$result['sale_unit'] = $orderEntry['0']['0']['totQty'];
		$result['rdsale_unit'] = $rSaleEntry['0']['0']['totQty'];
		//$targetQry = $this->Report->query("SELECT target FROM user_targets AS Target WHERE type='Call'");
		$targetQry = $this->Report->query("SELECT target FROM user_targets AS Target WHERE type='Call' AND user_id IN (SELECT id FROM users WHERE status = 1)");
		$targets = 0;
		
		
		if(!empty($params['t-month'])){
            $d1Arrar['1'] = $params['t-month'];
        }else{
            $d1Arrar = explode("-", $d1); //pr($d1Arrar);
        }
		//pr($d1Arrar);
		if($targetQry){
			foreach($targetQry as $target){				
				$userTargets = @unserialize($target['Target']['target']);
				$targets += !empty($userTargets[$d1Arrar['1']])?$userTargets[$d1Arrar['1']]:0;
			}
		}
		//pr($targets);
		$result['call_target'] = $targets;
		if(!empty($result['total_call']) && $result['total_call'] > 0){
			$result['effective_sale_call'] = ( $orderEntry['0']['0']['oentry']/$result['total_call']) * 100;
		}else{
			$result['effective_sale_call'] = 0;
		}
		return $result;
	}
	
	function total_expense($params){
		$d1 = $params['from'];
		$d2 = $params['to'];
		$exp_amount = $this->Report->query("SELECT SUM(exp_amount) as exp_amount FROM expenses WHERE exp_date between '$d1' AND '$d2'");
		$exp_amount = isset($exp_amount['0']['0']['exp_amount'])?$exp_amount['0']['0']['exp_amount']:0;
		return $exp_amount;
	}
}