<?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()) {
		$result = array();
		$start = ($page-1)*$limit;
		$limitStr = "LIMIT $start,  $limit";
		//pr($conditions);
		$cond = '';
		
		switch($extra['model']){
			case 'customer':
				
				$date = $extra['date'];				
				$cond = '(Sale.sale_date LIKE "'.$date.'%" OR rdSale.sale_date LIKE "'.$date.'%")';
				$this->_userCondition($cond,  $extra['user']);
				$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 LEFT JOIN sales AS Sale ON Sale.customer_id = Customer.id AND Sale.sale_date LIKE '$date%'
				LEFT JOIN (SELECT customer_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY customer_id ) AS rdSale ON rdSale.customer_id = Customer.id 
				WHERE $cond GROUP BY Customer.`id` ORDER BY total_amount DESC $limitStr");
		
			break;
			
			case 'user':
				$date = $extra['date'];				
				$cond = '(Sale.sale_date LIKE "'.$date.'%" OR rdSale.sale_date LIKE "'.$date.'%")';
				$this->_userRoleConditions($cond,  $extra['user']);
				$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 LEFT JOIN sales AS Sale ON Sale.user_id = User.id AND Sale.sale_date LIKE '$date%'
				LEFT JOIN (SELECT user_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY user_id ) AS rdSale ON rdSale.user_id = User.id 
				WHERE $cond GROUP BY User.`id` ORDER BY total_amount DESC $limitStr");
				
			break;
		}
		
		return $result;
	}
	
	public function paginateCount($conditions = null, $recursive = 0,$extra = array()) {
		$result = array();
		$cond = '';
		/*if($conditions){
			$totalCond = count($conditions);
			$i = 1;
			foreach($conditions as $key => $val){
				if(is_numeric($key)){
					$cond .= "(".$val.")";
				}else{
					if(preg_match('/=/',$key)){
						$cond .= $key." " .$val;
					}else{
						$cond .= $key." = " .$val;
					}
				}
				
				if( $i<$totalCond){
					$cond .= " AND ";
				}
				$i++;
			}
		}*/
		switch($extra['model']){
			case 'customer':
				
				$date = $extra['date'];
				$cond = '(Sale.sale_date LIKE "'.$date.'%" OR rdSale.sale_date LIKE "'.$date.'%")';
				$this->_userCondition($cond,  $extra['user']);
				$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 LEFT JOIN sales AS Sale ON Sale.customer_id = Customer.id AND Sale.sale_date LIKE '$date%'
				LEFT JOIN (SELECT customer_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY customer_id ) AS rdSale ON rdSale.customer_id = Customer.id 
				WHERE $cond GROUP BY Customer.`id`");
			break;
			
			case 'user':
				$date = $extra['date'];				
				$cond = '(Sale.sale_date LIKE "'.$date.'%" OR rdSale.sale_date LIKE "'.$date.'%")';
				$this->_userRoleConditions($cond,  $extra['user']);
				$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 LEFT JOIN sales AS Sale ON Sale.user_id = User.id AND Sale.sale_date LIKE '$date%'
				LEFT JOIN (SELECT user_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '$date%' GROUP BY user_id ) AS rdSale ON rdSale.user_id = User.id 
				WHERE $cond GROUP BY User.`id`");
			break;
		}
		return count($result);
	}
	
	
	function _userRoleConditions(&$conditions, $user){
		switch($user['Role']['grade']){
			case 3:
				$conditions .= ' AND User.region_id = '.$user['region_id'];
			break;
			case 4:
				$conditions .= ' AND User.depot_id = '.$user['depot_id'];
			break;
		}
	}
	
	public function _userCondition(&$conditions, $user){
		switch($user['Role']['grade']){
			case 1:
			case 2:
				break;
			case 3:
				$conditions .= ' AND (Customer.user_id IN (SELECT id FROM users WHERE region_id = '.$user['region_id'].') OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].'))';
			break;
			case 4:
				$conditions .= ' AND (Customer.user_id IN (SELECT id FROM users WHERE depot_id = '.$user['depot_id'].') OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].'))';
			break;
			case 5:
				$conditions .= ' AND (Customer.user_id = '.$user['id']. ' OR Customer.user_id IN (SELECT uid2 FROM user_relations WHERE user_id = '.$user['id'].') OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].') )';
			break;
			
			case 6:
			default:
				$conditions .= ' AND ( Customer.user_id = ' .$user['id'].' OR Customer.id IN (SELECT customer_id FROM customer_assigns WHERE user_id = '.$user['id'].') )';
			break;
			
		}
	}
}