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

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

class SaleActivity extends AppModel {
	public $useTable = false;
	
	/*
	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{
			$limitStr = "";
		}
		$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);
	}
	*/
	
	public function paginate( $conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
		$result = array();
		$start = ($page-1)*$limit;
		$limitStr = "LIMIT $start,  $limit";
		
		switch($extra['model']){
			case 'customer':
				
				$date = $extra['date'];				
				
				$result = $this->query("SELECT Customer.id, Customer.first_name, Customer.last_name, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0)) AS total_sale,
				(IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount 				
				FROM customers AS Customer INNER JOIN sales AS Sale ON Sale.customer_id = Customer.id 
				LEFT JOIN (SELECT customer_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY customer_id ) AS rdSale ON rdSale.customer_id = Customer.id 
				WHERE Sale.sale_date LIKE '$date%' GROUP BY Sale.`customer_id` ORDER BY total_amount DESC $limitStr");
		
			break;
			
			case 'user':
				$date = $extra['date'];				
				
				$result = $this->query("SELECT User.id, User.first_name, User.last_name, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0)) AS total_sale,
				(IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount 				
				FROM users AS User INNER JOIN sales AS Sale ON Sale.user_id = User.id 
				LEFT JOIN (SELECT user_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY user_id ) AS rdSale ON rdSale.user_id = User.id 
				WHERE Sale.sale_date LIKE '$date%' GROUP BY Sale.`user_id` ORDER BY total_amount DESC $limitStr");
			break;
		}
		
		return $result;
	}
	
	public function paginateCount($conditions = null, $recursive = 0,$extra = array()) {
		$result = array();
		
		switch($extra['model']){
			case 'customer':
				
				$date = $extra['date'];
				
				$result = $this->query("SELECT Customer.id, Customer.first_name, Customer.last_name, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0)) AS total_sale,
				(IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount 					
				FROM customers AS Customer INNER JOIN sales AS Sale ON Sale.customer_id = Customer.id 
				LEFT JOIN (SELECT customer_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY customer_id ) AS rdSale ON rdSale.customer_id = Customer.id 
				WHERE Sale.sale_date LIKE '$date%' GROUP BY Sale.`customer_id`");
			break;
			
			case 'user':
				$date = $extra['date'];				
				
				$result = $this->query("SELECT User.id, User.first_name, User.last_name, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0)) AS total_sale,
				(IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount 				
				FROM users AS User INNER JOIN sales AS Sale ON Sale.user_id = User.id 
				LEFT JOIN (SELECT user_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY user_id ) AS rdSale ON rdSale.user_id = User.id 
				WHERE Sale.sale_date LIKE '$date%' GROUP BY Sale.`user_id`");
			break;
		}
		return count($result);
	}
}