<?
	class common_jobs
	{						
		public function get_details($id)
		{			
			global $database;
					
			$sql = "
			SELECT *
			FROM jobs
			WHERE id = '$id'			
			";
			$results = $database->getRow($sql);			

			$results['creator'] = common_users::full_name($results['creator']);
			$results['date_created'] = dates::change_date_time($results['date_created']);	
			
			$results['submitter'] = common_users::full_name($results['submitter']);
			$results['date_submitted'] = dates::change_date_time($results['date_submitted']);			

			$results['status_nice'] = common_ref_status::get_status_name("jobs",$results['status']);			
									
			$client = common_clients::get_details($results['client_id']);

			$results['client_type'] = $client['client_type'];
			$results['company_name'] = $client['company_name'];


			$site_location_details = common_ref_client::get_client_location($results['location_id']);			

			$results['site_address_nice'] = $site_location_details['address'].", ".$site_location_details['suburb'].", ".$site_location_details['state'].", ".$site_location_details['post_code'];
			$results['site_address'] = $site_location_details['address'];
			$results['site_suburb'] = $site_location_details['suburb'];
			$results['site_state'] = $site_location_details['state'];
			$results['site_post_code'] = $site_location_details['post_code'];
			
			$quote_id = common_quotes::get_quote_id($id);
			$get_site_loc_from_quote = "
			SELECT site_location_id
			FROM quotes
			WHERE id = '$quote_id'			
			";
			$site_loc_from_quote = $database->getOne($get_site_loc_from_quote);	

			$site_loc_from_quote = common_ref_client::get_client_location($site_loc_from_quote);			

			$results['site_location_from_quote_nice'] = $site_loc_from_quote['address'].", ".$site_loc_from_quote['suburb'].", ".$site_loc_from_quote['state'].", ".$site_loc_from_quote['post_code'];
									
			
			$results['location_site_address']=$site_loc_from_quote['address'];
			$results['location_site_suburb']=$site_loc_from_quote['suburb'];
			$results['location_site_state']=$site_loc_from_quote['state'];
			$results['location_site_post_code']=$site_loc_from_quote['post_code'];
			
			
			$results['description_clean'] = nl2br( stripslashes( htmlentities( $results['description'], ENT_QUOTES, 'UTF-8' ) ) );	

			$results['contacts'] = self::get_contacts($results['id']);
									
			return $results;			
		}

		public function get_job_tracking($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_summary($job_id)
		{			
			global $database;
			
			$trackings = self::get_job_tracking($job_id);
			
			$total_time = 0;
			
			foreach($trackings AS $tracking)
			{
				/** Kai 01/11/2012*/
				if($tracking['department']!=12&&$tracking['department']!=13){
					$total_time += aging::get_time_by_hour($tracking['start_time'],$tracking['end_time']);
				}	
			}
			return $total_time;			
		}	
						
		public function get_status($id)
		{
			global $database;
					
			$sql = "
			SELECT status
			FROM jobs
			WHERE id = '$id'			
			";
			return $database->getOne($sql);				
		}
		
		public function get_num_child_jobs($job_id ,$parent_job = 0)
		{
			global $database;
						
			$sql = "
			SELECT count(id)
			FROM jobs
			WHERE parent_job_id  = '$job_id' AND parent_job = '$parent_job'
			";
			$results = $database->getOne($sql);
			
			return $results;								
		}	
		
		
		public function get_parent_job_id($job_id)
		{
			global $database;
						
			$sql = "
			SELECT parent_job_id
			FROM jobs
			WHERE id  = '$job_id'
			";
			$results = $database->getOne($sql);
			
			return $results;								
		}
		
		
		public function get_job_installation_estimated($labour_id,$job_id)
		{
			global $database;
						
			$sql = "
			SELECT estimated
			FROM job_installation
			WHERE job_id  = '$job_id' AND labour_id='$labour_id'
			";
			$results = $database->getOne($sql);
			
			return $results;								
		}	
		
		
		public function get_job_installation_actual($labour_id,$job_id)
		{
			global $database;
						
			$sql = "
			SELECT actual
			FROM job_installation
			WHERE job_id  = '$job_id' AND labour_id='$labour_id'
			";
			$results = $database->getOne($sql);
			
			return $results;								
		}
		
				
		public function get_contacts($id)
		{
			global $database;
						
			$sql = "
			SELECT *
			FROM jobs_contacts
			WHERE job_id = '$id'
			";
			$results = $database->getAll($sql);
			
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = common_contacts::get_details($result['contact_id']);	
			
			return $final_results;								
		}	
		
		
		public function get_jobnumbers($client_id)
		{			
			global $database;
			
			$get_name = "
			SELECT id,job_number
			FROM jobs
			WHERE client_id = '$client_id'
			
			";
			$results = $database->getAll($get_name);
			
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[] = $result;	
			
			return $final_results;			
		}	

		public function get_jobs_details($job_id)
		{			
			global $database;
						
		  	  $get_request = "
			SELECT 
				jobs.id,
				jobs.client_id,			
				jobs.job_name,			
				jobs.job_number,			
				jobs.description,			
				jobs.sitesurvey_notes,			
				jobs.qualification_notes,			
				jobs.visualmockup_notes,			
				jobs.production_notes,			
				jobs.management_notes,			
				jobs.finance_notes,			
				jobs.flag,			
							
				jobs.department_id,			
				jobs.stage_id,			
				jobs.jobtype,			
				jobs.submitter,			
				jobs.date_submitted,	
				clients.company_name,
				clients.website,
				
				stages.stage_name,
				departments.department_name
				
																
			FROM jobs 
				INNER JOIN clients ON jobs.client_id = clients.id
				INNER JOIN stages ON jobs.stage_id = stages.id
				INNER JOIN departments ON jobs.department_id = departments.id
			WHERE jobs.id = '$request_id'
			";	
			
		 	  $get_request = "
			SELECT 
				jobs.*,
				clients.company_name,
				clients.primary_location_id,
				clients.primary_contact_id,	
				stages.stage_name,
				departments.department_name				
			FROM jobs 
				INNER JOIN clients ON jobs.client_id = clients.id
				INNER JOIN stages ON jobs.stage_id = stages.id
				INNER JOIN departments ON jobs.department_id = departments.id
			WHERE jobs.id = '$request_id'
			";				
			
	 	  $get_job = "SELECT jobs.*,departments_jobs.status as djstatus,departments_jobs.id as djid,departments_jobs.department_id as djdepartment_id, clients.company_name, clients.primary_location_id, 
		 	 clients.primary_contact_id, stages.stage_name, departments.department_name 
			FROM jobs 
			INNER JOIN departments_jobs ON jobs.id = departments_jobs.job_id 
			INNER JOIN clients ON jobs.client_id = clients.id 
			INNER JOIN stages ON jobs.stage_id = stages.id 
			INNER JOIN departments ON jobs.department_id = departments.id
			 WHERE departments_jobs.id = '$request_id'";
			
			$get_job = "
						SELECT * FROM jobs
						WHERE id = '$job_id'
						";
			
			
			$client = $database->getRow($get_job);
			
			return $client;			
		}	
		
		public function get_clients()
		{
			global $database;
						
			$get_name = "
			SELECT id,company_name as name
			FROM clients
			";
			$results = $database->getAssoc($get_name);
			//bug::bug_array('',$results);
			return $results;			
		}

		public function get_all_clients()
		{
			global $database;
						
			$get_name = "
			SELECT id,company_name as name, address 
			FROM clients
			";
			$results = $database->getAll($get_name);
			//bug::bug_array('',$results);
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;	
			
			return $final_results;				
		}
		
		public function get_notes($id)
		{
			global $database;
						
			 $get_notes = "
				SELECT 	jobs.qualification_notes,jobs.sitesurvey_notes,jobs.production_notes,jobs.management_notes,jobs.finance_notes, departments_jobs.id
				FROM jobs
				INNER JOIN departments_jobs ON jobs.id = departments_jobs.job_id
				WHERE departments_jobs.id = '$id' LIMIT 1";
			$results = $database->getRow($get_notes);
			//$results = $database->getAll($get_name);
			//bug::bug_array('',$results);
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;	
			
			return $results;				
		}
		
		public function get_job_number($id)
		{
			global $database;
						
			  $get_job = "
				SELECT 	job_number FROM jobs WHERE id =  '$id' ";
				 
			$results = $database->getOne($get_job);
			//$results = $database->getAll($get_name);
			//bug::bug_array('',$results);
			 
			
			return $results;				 
		}
		
		
		public function get_quote_number($job_id)
		{
			global $database;
						
			  $get_quote = "
				SELECT 	quote_id FROM jobs WHERE id =  '$job_id' ";
				 
			$results = $database->getOne($get_quote);
			//$results = $database->getAll($get_name);
			//bug::bug_array('',$results);
			 
			
			return $results;				 
		}
		
		
		public function get_one_job_number($id)
		{
			global $database;
						
			 $get_job = "
				SELECT 	job_number 
				FROM jobs 
				WHERE id = '$id'
				";
				 
			$results = $database->getOne($get_job);

			return $results;				 
		}
		
		public function get_one_job_name($id)
		{
			global $database;
						
			 $get_job = "
				SELECT 	job_name 
				FROM jobs 
				WHERE id='$id'
				";
				 
			$results = $database->getOne($get_job);

			return $results;				 
		}
		
		public function create_new($company_id, $job_name, $job_number, $signtype, $description)
		{
			global $database;
			$current_date_time = date('Y-m-d G:i:s');
			$submitter = $_SESSION['user']['id'];
	 		
			  $create_new = "
			INSERT INTO jobs SET `client_id` = '$company_id', `job_name` = '$description', `job_number` = '$job_number',
			`jobtype` = '$signtype', `description` = '$description',
			`status` = '7', `department_id` = '1', `stage_id` = '1',
			`creator` = '$submitter', `submitter` = '$submitter',
			`date_created` = '$current_date_time' , `date_submitted` = '$current_date_time'
			
			";
			 $database->query($create_new);
			 
			$request_id = $database->insert_id();
			return $request_id;			
		}
		
		
		public function get_jobs($status_id)
		{
			global $database;
						
			$get_request = "
						SELECT *
						FROM $db_name"."jobs
						WHERE status = '$status_id'   
						";
			$results = $database->getAll($get_request);	
			 	
			
			return $results;				
		}
		
		public function get_jobs_bystatus($dept_id_array,$status,$dept="")
		{
			global $database;
			 	if($dept==""){
					$dept_query="";
					}else{
					$dept_query=$dept;
					}
	         $get_request = "
				SELECT departments_jobs.*, 
					jobs.client_id,	jobs.job_name 	,jobs.job_number 	,jobs.description , jobs.stage_id 	, 	jobs.jobtype
				FROM $db_name"."departments_jobs
				INNER JOIN jobs
				ON departments_jobs.job_id = jobs.id WHERE ".$dept_query."  departments_jobs.action_id=2 AND jobs.status='$status'";
				 
				//WHERE departments_jobs.department_id IN ('".implode("', '", array_unique($dept_id_array))."')     $dept
				//";
				
				$get_request ="
				SELECT * FROM jobs WHERE status='$status'
			
				";
							
			$results = $database->getAll($get_request);	
			 	
			
			$final_results = array();
			if(is_array($results)){
			 foreach($results AS $result){
				 $final_results[$result['id']] = $result;	
			 }
			 }
			return $final_results;					
		}
		
		
		public function update($company_id, $job_name, $job_number, $signtype, $description,$id) 
		{
			global $database;
			$submitter = $_SESSION['user']['id'];
	
			$current_date_time = date('Y-m-d G:i:s');
			
			$database->StartTrans();	
			$update_request = "
			UPDATE jobs SET
				`client_id` = '$company_id',
				`job_name` = '$job_name',
				`job_number`  = '$job_number',
				`description` = '$description',
				`stage_id`	= '1',
				`department_id` = '1',
				`jobtype` = '$signtype',
				`submitter` = '$submitter',
				`date_submitted` = '$current_date_time'
			WHERE id = '$id'";
			
			$database->query($update_request);	
			logs::submit_logs(array("table" => "jobs", "action_type" => 2,"foreign_id" => $id, "description" => "Modified"));						
									
			 $results = $database->CompleteTrans();
			return $results;
		}
		
		public function job_details($id)
		{	
			global $database;
			
			//$database->debug = true;
			$get_job_details = "
			SELECT *
				FROM jobs
				WHERE id = '$id'
				LIMIT 1
			";
			$job_details = $database->getRow($get_job_details);			
			
			return $job_details;
		}
		
		public function get_all_jobs()
		{	
			global $database;
			
			//$database->debug = true;
			$get_jobs = "
			SELECT *
				FROM jobs
			";
			$jobs = $database->getAll($get_jobs);			
			
			return $jobs;
		}
		
		public function get_all_active_jobs_for_tablet()
		{	
			global $database;
			
			//$database->debug = true;
			$get_jobs = "
			SELECT *
			FROM jobs
			WHERE status ='1' AND parent_job_id = '0' AND jobs.id NOT IN (
												SELECT job_id
												FROM job_trackings_completed
												WHERE job_id = jobs.id AND job_lock = 1
											)
			";
			$jobs = $database->getAll($get_jobs);			
			
			return $jobs;
		}
		
		public function get_all_active_jobs_for_tablet_sort_ordering()
		{	
			global $database;
			
			//$database->debug = true;
			$get_jobs = "
			SELECT *
			FROM jobs
			WHERE status ='1' AND parent_job_id = '0' AND (jobs.id NOT IN (
												SELECT job_id
												FROM job_trackings_completed
												WHERE job_id = jobs.id AND job_lock = 1
											) OR is_ptp = 1)
			ORDER BY jobs.ordering ASC
			";
			$jobs = $database->getAll($get_jobs);			
			
			return $jobs;
		}
		
		public function get_all_jobs_select()
		{	
			global $database;
			
			$get_jobs = "
			SELECT *
				FROM jobs
			WHERE status = '1'
			ORDER BY id
			";
			$jobs = $database->getAll($get_jobs);			
			
			$jobs_list = array();
			
			foreach($jobs AS $result)
				$jobs_list[] = array('id'=>$result['id'],'name'=>$result['id']);
			
			return $jobs_list;

		}
		
		public function check_job_lock($job_id)
		{	
			global $database;
			
			$get_jobs = "
			SELECT id
				FROM job_trackings_completed
			WHERE job_id = '$job_id' AND job_lock = '1' 
			";
			$result = $database->getOne($get_jobs);			

			return $result;
		}
		
		
		public function update_inspection($serial_data,$job_id)
		{	
			global $database;
			
			$sql = "
			UPDATE jobs
			SET labours = '$serial_data'
			WHERE id = '$job_id'
			";				
			
			$result=$database->query($sql);			

			return $result;
		}
		
		public function get_checklist($id)
		{	
			global $database;
			
			$checklist = "
			SELECT *
			FROM job_checklist
			WHERE job_id = '$id'
			LIMIT 1
			";				
			$result=$database->getRow($checklist);			

			return $result;
		}
				
		public function get_last_job_id($start_range,$end_range)
		{
			global $database;
			
			$where_sql = "";
			
			if(!empty($start_range) && !empty($end_range))
				$where_sql = "WHERE	id >= '$start_range' AND id <= '$end_range'";
			else if(!empty($start_range) && empty($end_range))
				$where_sql = "WHERE	id >= '$start_range'";
			else if(empty($start_range) && !empty($end_range))
				$where_sql = "WHERE	id <= '$end_range'";
															
			$checklist = "
			SELECT id
			FROM jobs
			$where_sql
			ORDER BY id DESC
			LIMIT 1
			";				
			$id = $database->getOne($checklist);			
		
			if(empty($id))
				$id = $start_range;
			else
				$id += 1;
/*		
$job_id = array("5516","5531","6010","6168","6218","6219","6220","6222","6223");
			foreach($job_id AS $new_id)
			{
				$check_exist = "
SELECT count(id)
FROM jobs
WHERE id='$new_id'
";
$exist = $database->getOne($check_exist);

if($exist == 0)
{
 $id = $new_id;
break;
}
			}
i*/
			return $id;		
		}		

		public function get_material_suppliers($job_id)
		{
				global $database;

				$get_quote_id = "
				SELECT quote_id
				FROM jobs
				WHERE id = '$job_id'
				LIMIT 1
				";
				$quote_id=$database->getOne($get_quote_id);

				$get_suppliers = "
				SELECT DISTINCT supplier
				FROM quotes_materials
				WHERE quote_id = '$quote_id'
				";
				$results=$database->getAll($get_suppliers);

				$suppliers = array();

				foreach($results AS $result)
				{
						$id= strtolower($result['supplier']);
						$id = str_replace(" ","_",$id);
						$suppliers[] = array('id'=>$id,'name'=>$result['supplier']);
				}

				return $suppliers;
		}

		public function get_materials_by_supplier($job_id,$supplier)
		{
				global $database;

				$supplier = str_replace("_"," ",$supplier);
				//$supplier = ucwords($supplier);

				$get_quote_id = "
				SELECT quote_id
				FROM jobs
				WHERE id = '$job_id'
				LIMIT 1
				";
				$quote_id=$database->getOne($get_quote_id);
				
				if($supplier == "all suppliers")
				{
					$get_materials = "
					SELECT *
					FROM quotes_materials
					WHERE quote_id = '$quote_id'
					";
					$results=$database->getAll($get_materials);
				}
				else
				{
					$get_materials = "
					SELECT *
					FROM quotes_materials
					WHERE supplier LIKE '%$supplier%' AND quote_id = '$quote_id'
					";
					$results=$database->getAll($get_materials);
				}

				return $results;
		}
		
		
		public function update_job_installation($str,$id)
		{
			global $database;
			
			$submitter = $_SESSION['user']['id'];
	
			$current_date_time = date('Y-m-d G:i:s');
			$database->StartTrans();
			
			$sql = "DELETE FROM job_installation WHERE job_id='$id'";
			
			$result=$database->query($sql);	
			
			$sql = "INSERT INTO job_installation (job_id,labour_id,estimated,actual,date_submitted,submitter) 
					VALUES ".$str;
			
			$result=$database->query($sql);
			
			$results = $database->CompleteTrans();
			
			return $results;
		}
		
		public function get_job_id($quote_id){
			global $database;
			$get_job_id = "
			SELECT id
			FROM jobs
			WHERE quote_id = '$quote_id'
			AND parent_job_id=0
			LIMIT 1
			";
			$result=$database->getOne($get_job_id);
			
			return $result;
		}

		public function proceed_to_production($job_id, $is_ptp = 1){
			global $database;
			
			$sql = "
			UPDATE jobs
			SET is_ptp = '$is_ptp'
			WHERE id = '$job_id'
			";				
			
			$result=$database->query($sql);			

			return $result;
		}

		public function get_is_ptp($job_id){
			global $database;
			
			$sql = "
			SELECT is_ptp
			FROM jobs
			WHERE id = '$job_id'
			";		
			
			$result=$database->getOne($sql);			

			return $result;
		}
							
	}

?>