<?php
class Order extends AppModel {
	var $name = 'Order';
	var $displayField = 'order_code';
	
    var $actsAs   = array('Transactional');

	//The Associations below have been created with all possible keys, those that are not needed can be removed

	public $belongsTo = array(
		'Employee' => array(
			'className' => 'Employee',
			'foreignKey' => 'employee_id',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),
		'StatusOrder' => array(
			'className' => 'Status',
			'foreignKey' => 'status_id',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),
		'Customer' => array(
			'className' => 'Customer',
			'foreignKey' => 'customer_id',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		)		
	);

	public $hasMany = array(
		'Evaluation' => array(
			'className' => 'Evaluation',
			'foreignKey' => 'order_id',
			'dependent' => false,
			'conditions' => '',
			'fields' => '',
			'order' => '',
			'limit' => '',
			'offset' => '',
			'exclusive' => '',
			'finderQuery' => '',
			'counterQuery' => ''
		)
	);

    function valNotZero( $field=array(), $customer_field=null ) 
    {
		$id_customer = $this->data[$this->name][ $customer_field ]; 
		//echo $id_customer;
		if ($id_customer == 0) { return false; 	}
		return true;
    }  	
	
	# returns last 10 open Orders
	function openOrders()
	{					
		return $this->find('all', array(
				'link' => array(
					'Customer',
					'Employee',
					'StatusOrder' => array(
								'conditions' => 'Order.status_id  = StatusOrder.id'),
					'Evaluation' => array(
										'Requester',
										'Service',
										'Status'										
										)
				),
				'fields' => array('Evaluation.id','Evaluation.order_id','Evaluation.communication_id','Evaluation.status_id',
							'Evaluation.test_serie', 'Evaluation.created', 'Evaluation.last_test',
//							'MAX(Evaluation.test_serie) AS maxtest',
							'Order.id','Order.order_code','Order.status_id','Order.created',
							'Customer.id','Customer.name',
							'Employee.id','Employee.first_name','Employee.last_name','Employee.email',
							'Service.id','Service.name'),
				'conditions' => array('Order.status_id' => 1, 'Evaluation.last_test' => 1),
//				'group'=>array('Order.id'),
				'order' => 'Order.order_code DESC',
				'limit' => 10			
			));						
	}

	
	
	# returns last 10 canceled Orders - Canceled by user
	function canceledUserOrders()
	{
		return $this->find('all', array(
				'link' => array(
					'Customer',
					'Employee',
					'StatusOrder' => array(
								'conditions' => 'Order.status_id  = StatusOrder.id'),
					'Evaluation' => array(
										'Requester',
										'Service',
										'Status'										
										)
				),
				'fields' => array('Evaluation.id','Evaluation.order_id','Evaluation.communication_id','Evaluation.status_id',
							'Evaluation.test_serie', 'Evaluation.created', 'Evaluation.last_test', 
//							'MAX(Evaluation.test_serie) AS maxtest',
							'Order.id','Order.order_code','Order.status_id','Order.created',
							'Customer.id','Customer.name',
							'Employee.id','Employee.first_name','Employee.last_name','Employee.email',
							'Service.id','Service.name'),
//				'conditions' => array('Order.status_id' => 2),
				'conditions' => array('Order.status_id' => 2, 'Evaluation.last_test' => 1),
//				'group'=>array('Order.id'),				
				'order' => 'Order.order_code DESC',
				'limit' => 10
			));
	}
		
	# returns last 10 canceled Orders - Canceled by Admin
	function canceledAdminOrders()
	{
		return $this->find('all', array(
				'link' => array(
					'Customer',
					'Employee',
					'StatusOrder' => array(
								'conditions' => 'Order.status_id  = StatusOrder.id'),
					'Evaluation' => array(
										'Requester',
										'Service',
										'Status'										
										)
				),
				'fields' => array('Evaluation.id','Evaluation.order_id','Evaluation.communication_id','Evaluation.status_id',
							'Evaluation.test_serie', 'Evaluation.created', 'Evaluation.last_test',
							'MAX(Evaluation.test_serie) AS maxtest',
							'Order.id','Order.order_code','Order.status_id','Order.created',
							'Customer.id','Customer.name',
							'Employee.id','Employee.first_name','Employee.last_name','Employee.email',
							'Service.id','Service.name'),
//				'conditions' => array('Order.status_id' => 3, 'Evaluation.last_test' => 1),
				'conditions' => array('Order.status_id' => 3),
				'group'=>array('Order.id'),
				'order' => 'Order.order_code DESC',
				'limit' => 10
			));
	}
	
	# returns last 10 autoclosed Orders - Auto-closed(6 months)
	function autoclosedOrders()
	{
		return $this->find('all', array(
				'link' => array(
					'Customer',
					'Employee',
					'StatusOrder' => array(
								'conditions' => 'Order.status_id  = StatusOrder.id'),
					'Evaluation' => array(
										'Requester',
										'Service',
										'Status'										
										)
				),
				'fields' => array('Evaluation.id','Evaluation.order_id','Evaluation.communication_id','Evaluation.status_id',
							'Evaluation.test_serie', 'Evaluation.created', 'Evaluation.last_test',
//							'MAX(Evaluation.test_serie) AS maxtest',
							'Order.id','Order.order_code','Order.status_id','Order.created',
							'Customer.id','Customer.name',
							'Employee.id','Employee.first_name','Employee.last_name','Employee.email',
							'Service.id','Service.name'),		
				'conditions' => array('Order.status_id' => 4, 'Evaluation.last_test' => 1),
//				'group'=>array('Order.id'),				
				'order' => 'Order.order_code DESC',
				'limit' => 10
			));
	}
	
	# returns last 10 closed Orders - Auto-canceled(2nd reminder)
	function autocancelOrders()
	{
		return $this->find('all', array(
				'link' => array(
					'Customer',
					'Employee',
					'StatusOrder' => array(
								'conditions' => 'Order.status_id  = StatusOrder.id'),
					'Evaluation' => array(
										'Requester',
										'Service',
										'Status'										
										)
				),
				'fields' => array('Evaluation.id','Evaluation.order_id','Evaluation.communication_id','Evaluation.status_id',
							'Evaluation.test_serie', 'Evaluation.created', 'Evaluation.last_test',
//							'MAX(Evaluation.test_serie) AS maxtest',
							'Order.id','Order.order_code','Order.status_id','Order.created',
							'Customer.id','Customer.name',
							'Employee.id','Employee.first_name','Employee.last_name','Employee.email',
							'Service.id','Service.name'),
				'conditions' => array('Order.status_id' => 5, 'Evaluation.last_test' => 1),
//				'group'=>array('Order.id'),				
				'order' => 'Order.order_code DESC',
				'limit' => 10
			));
	}
	
	###################################################
	### returns the last Order Number
	###################################################
	function lastOrder()
	{
		return $this->find('first', array('fields' => 'MAX(Order.order_code) as order_code' ,'order' => 'Order.created DESC'));
		
	}	
		
	###################################################
	# returns the Order information
	###################################################
	function infoOrder($ord_id)
	{
		return $this->find('first', array(
				'link' => array(
					'Customer' => array(
							'City'  => array(
									'conditions' => 'Customer.city_id  = City.id'),
							'Province' => array(
									'conditions' => 'Customer.province_id  = Province.id'),
							'Team'),
					'Employee',
//					'Service',
					'StatusOrder' => array(
									'conditions' => 'Order.status_id  = StatusOrder.id')					
				),
				'fields' => array('Order.id','Order.order_code','Order.status_id', 'Order.status_date', 'Order.created', 'Order.comments', 'Customer.id','Customer.name',
									'Team.id','Team.name',
									'Employee.id','Employee.first_name','Employee.last_name','Employee.email'
//									,'Service.id','Service.name'
									),			
				'conditions' =>  array('Order.id' => $ord_id)				
			));			
	}
	
	###################################################
	# returns the number of Orders by Employee
	###################################################
	function countOrd($emp_id)
	{
		return $this->find('count', array('conditions' => array('Order.employee_id' => $emp_id)));
	}	
	
	# returns order stats for dashboard
	function stats()
	{
	  $stats = array();
		$stats['total'] = $this->find('count');
		$stats['open'] = $this->find('count', array('conditions' => array('Order.status_id' => 1)));
		$stats['canceleduser'] = $this->find('count', array('conditions' => array('Order.status_id' => 2)));
		$stats['canceledadmin'] = $this->find('count', array('conditions' => array('Order.status_id' => 3)));		
		$stats['autoclosed'] = $this->find('count', array('conditions' => array('Order.status_id' => 4)));		
		$stats['autocancel'] = $this->find('count', array('conditions' => array('Order.status_id' => 5)));		
		return $stats;
	}

/*

							'DspScore.type_q', 'DspScore.id', 'DspScore.type_q', 'DspScore.SOM', 'DspScore.DEP', 'DspScore.ALC', 
							'DspScore.ANX', 'DspScore.OBS', 'DspScore.PAN', 'DspScore.AGO', 'DspScore.TRA', 
							'DspScore.MIN', 'DspScore.RB', 'DspScore.GRD', 'DspScore.Q44'

*/	

	###################################################################################
	# Get the History DSP values scales from an specific Employee
	# By an specific Order and Test xId (show all the tests that are higher than xId)
	# Called from view_dsp_score (dsp_score controller)
	###################################################################################	
	function infoHistScaleDsp($order_id, $eval_id)
	{						
		return $this->find('all', array(
				'contain' => array(
						'Evaluation' => array(
								'DspScore' => array('conditions' =>  array('DspScore.evaluation_id' => 'Evaluation.id'),
													'fields' => array('DspScore.type_q', 'DspScore.id', 'DspScore.type_q', 'DspScore.SOM', 'DspScore.DEP', 'DspScore.ALC', 
																	'DspScore.ANX', 'DspScore.OBS', 'DspScore.PAN', 'DspScore.AGO', 'DspScore.TRA', 
																	'DspScore.MIN', 'DspScore.RB', 'DspScore.GRD', 'DspScore.Q44'
																	)			
													),
								'conditions' =>  array('Evaluation.order_id' => 'Order.id',																
													'Evaluation.test_serie <=' => $eval_id,
													array('NOT' => array('Evaluation.status_id' => array(7,14,15,16,18)))													
													),
								'fields' => array('Evaluation.id','Evaluation.test_serie', 'Evaluation.created', 'Evaluation.order_id',
												'Evaluation.service_id', 'Evaluation.requester_id', 'Evaluation.status_id'
												)
											)
							),
				'fields' => array('Order.id','Order.order_code'),					
				'conditions' =>  array('Order.id' => $order_id)
				)
			);
	}
	
	###################################################################################
	# Get the History HSQ values scales from an specific Employee
	# By an specific Order and Test xId (show all the tests that are higher than xId)
	# Called from view_dsp_score (hsq_scores controller)
	###################################################################################	
	function infoHistScaleHsq($order_id, $eval_id)
	{						
		return $this->find('all', array(
				'contain' => array(
						'Evaluation' => array(
								'HsqScore' => array('conditions' =>  array('HsqScore.evaluation_id' => 'Evaluation.id'),
													'fields' => array('HsqScore.id', 'HsqScore.HP', 'HsqScore.PF', 'HsqScore.RP', 
																	'HsqScore.RE', 'HsqScore.SF', 'HsqScore.MH', 'HsqScore.BP', 'HsqScore.EF'
																	)			
													),
								'conditions' =>  array('Evaluation.order_id' => 'Order.id',																
													'Evaluation.test_serie <=' => $eval_id,
													array('NOT' => array('Evaluation.status_id' => array(7,14,15,16,18)))													
													),
								'fields' => array('Evaluation.id','Evaluation.test_serie', 'Evaluation.created', 'Evaluation.order_id',
												'Evaluation.service_id', 'Evaluation.requester_id', 'Evaluation.status_id'
												)
											)
							),
				'fields' => array('Order.id','Order.order_code'),
				'conditions' =>  array('Order.id' => $order_id)
				)
			);
	}
	
	#############################################################################################
	# Returns all the "CLOSED" Evaluations for an specific Customer/Month/Year     				#
	# Used for the Invoice process                                                 				#
	#	13 = Completed / 7 = Canceled by user / 15 = Canceled by Admin                          #
	#	10 = Pend.to complete Quest.(1st reminder) / 11 = Pend.to complete Quest.(2nd reminder) #
	#	14 = Auto-canceled(2nd reminder)														#
	#############################################################################################	
	function infoEvalByCust($customer_id, $month_test, $year_test) 
	{
		$conditionsSubQuery['`Status`.`invoiced`'] = '1';

		$dbo = $this->Evaluation->Status->getDataSource();
		$subQuery = $dbo->buildStatement(
			array(
				'fields' => array('`Status`.`id`'),
				'table' => $dbo->fullTableName($this->Evaluation->Status),
				'alias' => 'Status',
				'limit' => null,
				'offset' => null,
				'joins' => array(),
				'conditions' => $conditionsSubQuery,
				'order' => null,
				'group' => null
			),
			$this->Status
		);
		$subQuery = ' `Evaluation`.`status_id` IN (' . $subQuery . ') ';
		$subQueryExpression = $dbo->expression($subQuery);
		
		return $this->find('all', array(
				'contain' => array(
						'Evaluation' => array(
								'conditions' =>  array(
												'YEAR(Evaluation.status_date)' => $year_test,
												'MONTH(Evaluation.status_date)' => $month_test,
												$subQueryExpression
												),
								'fields' => array('Evaluation.id','Evaluation.test_serie', 'Evaluation.created', 'Evaluation.status_date', 
												'Evaluation.order_id',
												'Evaluation.service_id', 'Evaluation.communication_id', 'Evaluation.status_id'
												),
								'order' =>  array('Evaluation.id')
											)
							),
				'fields' => array('Order.id','Order.order_code', 'Order.customer_id'),
				'conditions' =>  array('Order.customer_id = '.$customer_id),
				'group' =>  array('Order.order_code')				
				)
			);			
	}
	
	#############################################################################################
	# Returns all the "CLOSED" Evaluations for an specific Customer/Month/Year     				#
	# Used for the Invoice process                                                 				#
	#	13 = Completed / 7 = Canceled by user / 15 = Canceled by Admin                          #
	#	10 = Pend.to complete Quest.(1st reminder) / 11 = Pend.to complete Quest.(2nd reminder) #
	#	14 = Auto-canceled(2nd reminder)														#
	#############################################################################################	
	function infoEvalByCustTeam($team_id, $month_test, $year_test) 
	{
		$conditionsSubQuery['`Status`.`invoiced`'] = '1';

		$dbo = $this->Evaluation->Status->getDataSource();
		$subQuery = $dbo->buildStatement(
			array(
				'fields' => array('`Status`.`id`'),
				'table' => $dbo->fullTableName($this->Evaluation->Status),
				'alias' => 'Status',
				'limit' => null,
				'offset' => null,
				'joins' => array(),
				'conditions' => $conditionsSubQuery,
				'order' => null,
				'group' => null
			),
			$this->Status
		);
		$subQuery = ' `Evaluation`.`status_id` IN (' . $subQuery . ') ';
		$subQueryExpression = $dbo->expression($subQuery);
		
		return $this->find('all', array(
				'contain' => array(
					'Evaluation' => array(
						'Requester' => array(
							'conditions' => array('Evaluation.requester_id = Requester.id', 
													'Requester.team_id = '.$team_id),
							'fields' => array('Requester.id', 'Requester.team_id')
									),		
						'conditions' =>  array(
										'YEAR(Evaluation.status_date)' => $year_test,
										'MONTH(Evaluation.status_date)' => $month_test,
										'Evaluation.order_id' => 'Order.id',
										$subQueryExpression
									),
						'fields' => array('Evaluation.id','Evaluation.test_serie', 'Evaluation.created', 'Evaluation.order_id',
									'Evaluation.service_id', 'Evaluation.communication_id', 'Evaluation.status_id', 
									'Evaluation.requester_id'
									)
							)
					),
				'fields' => array('Order.id','Order.order_code', 'Order.customer_id')
//				'conditions' =>  array('Order.customer_id = Customer.id')
				)
			);	
	}	
	
	#############################################################################################
	# Returns all the "CLOSED" Evaluations for an specific Customer/Month/Year     				#
	# Used for the Invoice process                                                 				#
	#	13 = Completed / 7 = Canceled by user / 15 = Canceled by Admin                          #
	#	10 = Pend.to complete Quest.(1st reminder) / 11 = Pend.to complete Quest.(2nd reminder) #
	#	14 = Auto-canceled(2nd reminder)														#
	#############################################################################################	
	function infoEvalByCustTeamReq($requester_id, $month_test, $year_test) 
	{
		$conditionsSubQuery['`Status`.`invoiced`'] = '1';

		$dbo = $this->Evaluation->Status->getDataSource();
		$subQuery = $dbo->buildStatement(
			array(
				'fields' => array('`Status`.`id`'),
				'table' => $dbo->fullTableName($this->Evaluation->Status),
				'alias' => 'Status',
				'limit' => null,
				'offset' => null,
				'joins' => array(),
				'conditions' => $conditionsSubQuery,
				'order' => null,
				'group' => null
			),
			$this->Status
		);
		$subQuery = ' `Evaluation`.`status_id` IN (' . $subQuery . ') ';
		$subQueryExpression = $dbo->expression($subQuery);
		
		return $this->find('all', array(
				'contain' => array(
					'Evaluation' => array(
						'Requester' => array(
							'conditions' => array('Evaluation.requester_id = Requester.id', 
													'Requester.id = '.$requester_id),
							'fields' => array('Requester.id', 'Requester.team_id', 'Requester.first_name', 'Requester.last_name')
									),		
						'conditions' =>  array(
										'YEAR(Evaluation.status_date)' => $year_test,
										'MONTH(Evaluation.status_date)' => $month_test,
										'Evaluation.order_id' => 'Order.id',
										'Evaluation.invoice_id' => NULL,
										$subQueryExpression
									),
						'fields' => array('Evaluation.id','Evaluation.test_serie', 'Evaluation.created', 'Evaluation.order_id',
									'Evaluation.service_id', 'Evaluation.communication_id', 'Evaluation.status_id', 
									'Evaluation.requester_id'
									)
							)
					),
				'fields' => array('Order.id','Order.order_code', 'Order.customer_id')
//				'conditions' =>  array('Order.customer_id = Customer.id')
				)
			);	
	}	

	
	function beforeSave($opt=[]) 
	{
	### set todays date to 'status_date'
	$today = (date('Y-m-d'));

	
	### When an Insert Table is made
		if(empty($this->id))		 
		{
			## set status to Open on new Order - Evaluation
			$this->data['Order']['status_id'] = 1;		
		}
		else
		### When an Update is made		
		{
		## Update Evaluation - status - the Order has been canceled.
			# first get the id_evaluation:
			$evalinfo = $this->Evaluation->lastEvalinfo($this->data['Order']['id']);	

			
			# Administrator 'Cancel' options ***
			# second check the evaluation status (it has to be canceled if the evaluation has less the one week - before 1st reminder)
			if ($evalinfo['Evaluation']['status_id'] == 7 || $evalinfo['Evaluation']['status_id'] == 8 || $evalinfo['Evaluation']['status_id'] == 9
				|| $evalinfo['Evaluation']['status_id'] == 10 || $evalinfo['Evaluation']['status_id'] == 11 || $evalinfo['Evaluation']['status_id'] == 12 || $evalinfo['Evaluation']['status_id'] == 13 || $evalinfo['Evaluation']['status_id'] == 14 || $evalinfo['Evaluation']['status_id'] == 15 || $evalinfo['Evaluation']['status_id'] == 16)		
			{		

				//OLD CODE -  GIVING THE NOV/30/-0001 DATE ON THE CANCELLED EVALUATIONS AFTER CANCELLING ORDER BY ADMIN
				// # Update the Status and Last fields from the Last Evaluation			
				// $result0 = $this->Evaluation->updateAll(
				// 	array('Evaluation.last_test' => 0, 'status_date' => $today),
				// 	array('Evaluation.id' => $evalinfo['Evaluation']['id'])
				// 	);

				$testIdToCancel = $evalinfo['Evaluation']['id'];
				if($this->Evaluation->id = $testIdToCancel) {

					$this->data['Evaluation']['last_test'] = 0;
					$this->data['Evaluation']['status_id'] = 15;
					$this->data['Evaluation']['status_date'] = $today;
				}

				$result0 = $this->Evaluation->save($this->data['Evaluation']);

				if ($result0)
				{
					## Update Employee - ind - Release the Employee
					$result1 = $this->Employee->updateAll(
						array('Employee.ind' => 0),
						array('Employee.id' => $this->data['Order']['employee_id'])
						);
					if( $result1 == false) { return false; }						
				}
				else
				{
				return false;
				}
			}
			else
			{
			return false;			
			}
		}			  
		$this->data['Order']['status_date'] = $today;
		return true;
	}
	
	function afterSave($created) 
	{
	### When an Insert is made
		if($created)
		{
			## Update Employee - ind - An open order for the Employee
			$this->Employee->id = $this->data['Order']['employee_id'];
		    if (!$this->Employee->saveField('ind', 1))
			{
				return false;
			}
		}
		else
		{
			
		}
		return true;		
	}	
}
?>