<?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_up($params),
			'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 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);
		//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_up($params),
			'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 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 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 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_up($params){
		$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";
		$customers_up = $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_up)?count($customers_up):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'");
		$targets = 0;
		$d1Arrar = explode("-", $d1);
		if($targetQry){
			foreach($targetQry as $target){				
				$userTargets = @unserialize($target['Target']['target']);
				$targets += !empty($userTargets[$d1Arrar['1']])?$userTargets[$d1Arrar['1']]:0;
			}
		}
		
		$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;
	}
}