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

/**
 * Application model for Cake.
 *
 * Add your application-wide methods in the class below, your models
 * will inherit them.
 *
 * @package       app.Model
 */

class ExpenseReimbursement extends AppModel {
	public $belongsTo = array(
			'User1' => array('className' => 'User', 'foreignKey' => 'imburse_user_id', 'fields' => array('first_name', 'last_name'), 'type'=>'inner'),
		'User' => array('className' => 'User',  'foreignKey' => 'user_id', 'fields' => array('first_name', 'last_name'), 'type'=>'inner'), 
		);
	
	function _userCondition(&$conditions, $user){
		switch($user['Role']['grade']){
			
			case 3:
				//$conditions[] = 'ExpenseReimbursement.depot_id IN (SELECT id FROM depots WHERE region_id = '.$user['region_id'].')';
			$conditions['ExpenseReimbursement.region_id'] = $user['region_id'];
			break;
			case 4:
				$conditions['ExpenseReimbursement.depot_id'] = $user['depot_id'];
			break;
			case 5:
				$conditions['ExpenseReimbursement.user_id'] = $user['id'];
			break;
			case 6:
				$conditions['ExpenseReimbursement.user_id'] = $user['id'];
			break;
			
		}
	}
	public function expenseReimbur(&$reimExp,$params=null){
		$reportArr = array();
		$rd1 =$rd2 = $date = $params['date'];
		foreach($reimExp as $reim){
			$imburseUserIds[] = $reim['User']['id'];
		}
		
		switch($params['type']){
			case 'daily':
				 $ExpDt = "Expense.exp_date = '".$date."'";
			  	 $ReimDt = "Reimbuse.exp_date = '".$date."'";
			break;
			case 'weekly':
			$rd1 = $d1 = date('Y-m-d', $params['strtotime']);
			$rd2 = $d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
				$ExpDt = "Expense.exp_date between '$d1' AND '$d2'";
				$ReimDt = "Reimbuse.exp_date between '$d1' AND '$d2'";
				
			break;
			case 'between':				
				if(empty( $params['query']['from'])){
				$rd1 =	$d1 = date('Y-m-d');
				}else{
				$rd1 =	$d1 =  $params['query']['from'];
				}	
				if(empty( $params['query']['to'])){
				$rd2 =	$d2 = date('Y-m-d');
				}else{
				$rd2 =	$d2 =  $params['query']['to'];
				}	
				$ExpDt = "Expense.exp_date between '$d1' AND '$d2'";
				$ReimDt = "Reimbuse.exp_date between '$d1' AND '$d2'";
			break;
			case 'monthly':
			$rd1 = $d1 = date('Y-m-01', $params['strtotime']);
			$rd2 = $d2 = date('Y-m-t', $params['strtotime']);
				$ExpDt = "Expense.exp_date between '$d1' AND '$d2'";				
				$ReimDt = "Reimbuse.exp_date between '$d1' AND '$d2'";
			break;
		}
		//Expense as per date selection
		$ExpenseAmount = $this->query("SELECT user_id, SUM(exp_amount) AS ExpenseAmount FROM expenses AS Expense WHERE $ExpDt AND  Expense.user_id IN (".implode(",", $imburseUserIds).") GROUP BY user_id" );
		
		foreach ($ExpenseAmount  as $amt){
			$uid = $amt['Expense']['user_id'];
			$reportArr[$uid]['ExpenseAmount'] = $amt['0']['ExpenseAmount'];
		}
		//Reimbusement as per date selection		
		$ReimbusAmount = $this->query("SELECT imburse_user_id, SUM(exp_amount) AS ReimbuseAmount FROM expense_reimbursements AS Reimbuse WHERE $ReimDt AND Reimbuse.imburse_user_id IN (".implode(",", $imburseUserIds).") GROUP BY imburse_user_id" );

		foreach ($ReimbusAmount  as $amt){
			$uid = $amt['Reimbuse']['imburse_user_id'];
			$reportArr[$uid]['ReimbuseAmount'] = $amt['0']['ReimbuseAmount'];
			
		}
		
	/*	//Overall Expense 
		$o_ExpenseAmount = $this->query("SELECT user_id, SUM(exp_amount) AS ExpenseAmount FROM expenses AS Expense WHERE Expense.user_id IN (".implode(",", $imburseUserIds).") GROUP BY user_id" );
		
		foreach ($o_ExpenseAmount  as $amt){
			$uid = $amt['Expense']['user_id'];
			$reportArr[$uid]['o_ExpenseAmount'] = $amt['0']['ExpenseAmount'];
		}
		//Overall Reimbusement 	
		$o_ReimbusAmount = $this->query("SELECT imburse_user_id, SUM(exp_amount) AS ReimbuseAmount FROM expense_reimbursements AS Reimbuse WHERE Reimbuse.imburse_user_id IN (".implode(",", $imburseUserIds).") GROUP BY imburse_user_id" );

		foreach ($o_ReimbusAmount  as $amt){
			$uid = $amt['Reimbuse']['imburse_user_id'];
			$reportArr[$uid]['o_ReimbuseAmount'] = $amt['0']['ReimbuseAmount'];
		}*/
		foreach($reimExp as $key => $val){
			if(isset($reportArr[$val['User']['id']])){
				$reimExp[$key]['Expense'] = $reportArr[$val['User']['id']];
				$reimExp[$key]['date'] = array('date1' => $rd1, 'date2' => $rd2);
			}else{
				$reimExp[$key]['Expense'] = array();	
				$reimExp[$key]['date'] = array('date1' => $rd1, 'date2' => $rd2);
			}
		}
		
	}
}
