<?
	class common_job_trackings
	{						
		public function get_all_job_trackings()
		{			
			global $database;
			
			$current_date = date('Y-m-d');
			
			$get_all_job_trackings = "
			SELECT *
				FROM job_trackings
			";
			$all_job_trackings = $database->getAll($get_all_job_trackings);
			
			return $all_job_trackings;			
		}		
		
		public function get_all_job_trackings_by_status($status)
		{			
			global $database;
			
			$current_date = date('Y-m-d');
			
			$get_all_job_trackings = "
			SELECT *
				FROM job_trackings
				WHERE status = '$status'
			";
			$all_job_trackings = $database->getAll($get_all_job_trackings);
			
			return $all_job_trackings;			
		}		
			
		public function get_job_tracking($id)
		{			
			global $database;
			
			$get_job_tracking = "
			SELECT *
				FROM job_trackings
				WHERE id='$id'
			";
			$job_tracking = $database->getRow($get_job_tracking);
			
			$job_tracking['start_time_normal'] = $job_tracking['start_time'];
			$job_tracking['end_time_normal'] = $job_tracking['end_time'];
			$job_tracking['start_time'] = dates::change_date_time($job_tracking['start_time']);
			if($job_tracking['end_time'] == "0000-00-00 00:00:00")
			{
				$job_tracking['end_time'] = "I am Still working !";
				$job_tracking['duration'] = common_timesheets::get_duration_not_end($job_tracking['start_time']);
			}
			else
			{
				$job_tracking['end_time'] = dates::change_date_time($job_tracking['end_time']);
				$job_tracking['duration'] = common_timesheets::get_duration($job_tracking['start_time'],$job_tracking['end_time']);
			}
			//$job_tracking['end_time'] = ($job_tracking['end_time'] == "I am Still working !") ? "I am Still working !" : dates::change_date_time($job_tracking['end_time']);
			
			return $job_tracking;			
		}
		
		public function get_job_tracking_by_job($job_id)
		{			
			global $database;
			
			$get_job_tracking = "
			SELECT *
				FROM job_trackings
				WHERE job_id='$job_id'
			";
			$job_tracking = $database->getAll($get_job_tracking);
			
			return $job_tracking;			
		}	
		
		public function get_job_tracking_by_job_and_staff($job_id,$user_id)
		{			
			global $database;
						
			$get_job_tracking = "
			SELECT *
				FROM job_trackings
				WHERE job_id='$job_id' AND user_id='$user_id'
			";
			$job_tracking = $database->getAll($get_job_tracking);
						
			return $job_tracking;			
		}
		
		public function get_total_duration_by_department_and_user($job_id,$user_id)
		{			
			global $database;
						
			$return_result[] = "";
			
			$get_job_tracking = "
			SELECT *
				FROM job_trackings
				WHERE user_id='$user_id' AND job_id='$job_id'
			";
			$job_trackings = $database->getAll($get_job_tracking);
			
			$departments = common_department::get_all_factory_department();
			
			foreach($departments AS $department)
			{
				foreach($job_trackings AS $job_tracking)
				{
					if($job_tracking['department'] == $department['id'])
					{
						$return_result[$department['id']]['id'] = $department['id'];
						$return_result[$department['id']]['value'] += self::get_duration_minute($job_tracking['start_time'],$job_tracking['end_time']);
					}
				}
			}
			
			return $return_result;			
		}	
		
		public function get_duration($start_time, $end_time)
		{			
			if($end_time == '0000-00-00 00:00:00')
			{
				$duration = "N/A";
			}
			else
			{
				$to_time=strtotime($end_time);
				$from_time=strtotime($start_time);
				$shift_minutes =round(abs($to_time - $from_time) / 60);
				
				$duration = floor($shift_minutes/60)."h ".($shift_minutes%60)."m";
			}	
			return $duration;	
		}	

				
		public function get_duration_minute($start_time, $end_time)
		{			
			if($end_time == '0000-00-00 00:00:00')
			{
				$duration = "N/A";
			}
			else
			{
				$to_time=strtotime($end_time);
				$from_time=strtotime($start_time);
				$shift_minutes =round(abs($to_time - $from_time) / 60);
			}	
			return $shift_minutes;	
		}		
									
		public function convert_time($time)
		{		
			if($time<0)
			{	
				$time = abs($time);
				$duration = "- ".floor($time/60)."h ".($time%60)."m";
			}
			else
			{	
				$duration = floor($time/60)."h ".($time%60)."m";
			}
			
			return $duration;	
		}
		
		
		public function get_job_budget($job_id)
		{
			global $database;
			
			$get_budget = "
			SELECT quotes_production_time.*, production_departments.*
				 FROM quotes_production_time
				 INNER JOIN production_departments
				 ON quotes_production_time.production_departments_id = production_departments.id
				 WHERE quote_id ='$job_id'
			";
			$job_budgets = $database->getAll($get_budget);
			
			foreach($job_budgets as $jb)
			{
				$results[$jb['production_departments_id']]=$jb['hours'];
			}
			
			return $job_budgets;			

		}

		public function get_installation_hourly_rate(){
			global $database;
			
			$get_rate = "
			SELECT rate
			FROM production_departments_sub
			WHERE labour_name = 'Installation labour'
			";
			$rate = $database->getOne($get_rate);
			return $rate;	
		}
		
		
		public function get_total_time_per_dept_job($dept,$job_id){
		
			global $database;
			
		 	$get_sum ="
			SELECT (
				SUM( (
				TIME_TO_SEC( end_time ) - TIME_TO_SEC( start_time ) )
				)
				) /3600 AS dates
				FROM job_trackings
				WHERE job_id ='$job_id' AND department='$dept'
	
			";
			
			  $get_sum ="
			SELECT sum( (
				(
				TIME_TO_SEC( TIMEDIFF( end_time, start_time ) ) )
				) /3600
				) AS dates
				FROM job_trackings
				WHERE job_id = '$job_id'
				AND department = '$dept'
					
			";
					
			$job_sum = $database->getRow($get_sum);
			
			return $job_sum['dates'];
		}
		
		
		public function get_total_time_per_job_overall($job_id){
		
			global $database;
		 
			$get_sum ="
			SELECT sum( (
			(
			TIME_TO_SEC( TIMEDIFF( end_time, start_time ) ) )
			) /3600
			) AS dates
			FROM job_trackings
			WHERE job_id = '$job_id' AND department <> 11 AND department <> 12 AND department <>13
				
				
			";
			
			  $get_sum ="
			SELECT sum( (
				(
				TIME_TO_SEC( TIMEDIFF( end_time, start_time ) ) )
				) /3600
				) AS dates
				FROM job_trackings
				WHERE job_id = '$job_id'
				 
					
			";
			

					
			$job_sum = $database->getOne($get_sum);
			
			return $job_sum ;
		}
		
		
		
		public function get_completed_job($dept_id,$job_id){
		
			global $database;
					
						
		    $get_complete ="
			SELECT *
				FROM job_trackings_completed
				WHERE job_id ='$job_id'
				AND department_id =  '$dept_id'
				AND status='0'
	
			";
					
			$job_complete = $database->getRow($get_complete);
			
			return $job_complete['id'];
		}
		
		
		public function get_locked_job($dept_id,$job_id){
		
			global $database;
			
			
			$get_locked ="
			SELECT *
				FROM job_trackings_completed
				WHERE job_id ='$job_id'
				AND department_id =  '$dept_id'
				AND status = 1
			
			";
			
			$job_locked = $database->getRow($get_locked);
			
			return $job_locked['id'];
		}
		
		
		
		public function get_current_dept($id)
		{
			global $database;
						
			$get_dept = "
			SELECT department
				FROM job_trackings 
				WHERE id = '$id'
			";
			$result=$database->getOne($get_dept);
			
			return $result;
		}
		
		
		public function lock_job_tracking_complete($job_id,$lock)
		{
			global $database;
			
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');			
			
			$insert = "
			INSERT INTO job_trackings_completed 
			SET
				job_id = '$job_id',
				job_lock = '$lock',
				submitter = '$submitter',
				date_submitted = '$current_date_time'
			";
			$result=$database->query($insert); 
			
			return $result;
		}
		
		
		public function unlock_job_tracking_complete($job_id)
		{
			global $database;
			
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');			
			
			
			$delete = "
			DELETE FROM job_trackings_completed 
			WHERE  job_id='$job_id' AND job_lock = '1'
			";
			
			$result=$database->query($delete);
			
			return $result;
		}
		
		
		public function job_tracking_complete_checked($job_id,$department_id,$job_status)
		{
			global $database;
			
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');			
			
			
			$insert = "
			INSERT INTO job_trackings_completed (job_id,department_id,status,submitter,date_submitted)
			VALUES ('$job_id','$department_id','$job_status','$submitter','$current_date_time')
			";
			$database->query($insert);
			$result= $database->insert_ID();
			
			return $result;
		}
		
		public function job_tracking_complete_unchecked($department_id )
		{
			global $database;
			
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');			
			
			
			$delete = "
			DELETE FROM job_trackings_completed 
			WHERE  department_id='$department_id' 
			";
			
			$result=$database->query($delete);
			 
			
			return $result;
		}
		
		
		public function update_job_tracking($context )
		{
			global $database;
			
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');			
			
			$job_id = $context['job_id'];
			$department = $context['department'];
			$start_time = $context['start_time'];
			$end_time = $context['end_time'];
			$alert = $context['alert'];
			$id = $context['id'];
			 
			
			$insert_job_trackings = "
			UPDATE job_trackings
			SET
				job_id = '$job_id',
				department = '$department',
				start_time = '$start_time',
				end_time = '$end_time',
				status = '1',
				alert = '$alert',
				submitter = '$submitter',
				date_submitted = '$current_date_time'
			WHERE id ='$id'
			";
			$results = $database->query($insert_job_trackings);
			 
			
			return $results;
		}
		
		
		public function insert_job_tracking($context )
		{
			global $database;
			
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');			
			
			$job_id = $context['job_id'];
			$department = $context['department'];
			$start_time = $context['start_time'];
			$end_time = $context['end_time'];
			$user_id = $context['user_id'];
			 
			 
			
			$insert_job_trackings = "
			INSERT INTO job_trackings
			SET
				user_id = '$user_id',
				job_id = '$job_id',
				department = '$department',
				start_time = '$start_time',
				end_time = '$end_time',
				status = '1',
				creator = '$submitter',
				submitter = '$submitter',
				date_created = '$current_date_time',
				date_submitted = '$current_date_time'
			";
			$results = $database->query($insert_job_trackings);
			 
			
			return $results;
		}
		
		public function get_start_job($id)
		{
			global $database;
			$db_name = DB_CLIENT;
			$get_job_tracking = "
				SELECT * 
				FROM $db_name"."job_trackings 
				WHERE user_id = '$id'
				ORDER BY id DESC LIMIT 1
			";
			$result=$database->getRow($get_job_tracking);
			
			$start_end = "start";
		
			if($result['end_time'] == "0000-00-00 00:00:00")
			{ 
				$start_end = 1; 
			} 
			else
			{ 
				$start_end = 0; 
			} 
			
			return $start_end;
		}
		

		public function has_job_budget($quote_id)
		{
			global $database;
			
			$get_budget = "
			SELECT *
				 FROM quotes_production_time
				 WHERE quote_id ='$quote_id'
			";
			$job_budgets = $database->getAll($get_budget);
			
			if($job_budgets){
				foreach($job_budgets as $bd) {
					if( $bd['hours'] >0 ) {
						return true;
					}
				}
			}
			
			return false;
		}
	}
?>