<?
	class common_schedules
	{						
		public function get_job_schedule($id)
		{			
			global $database;
					
			$sql ="
		 		SELECT job_schedules.id,job_schedules.department_id,job_schedules.job_id,job_schedules.hours,job_schedules.users,
						job_schedules.start_date,job_schedules.end_date,job_schedules.hours, production_departments.department_name as title
				FROM job_schedules
				INNER JOIN  production_departments ON job_schedules.department_id = production_departments.id
				WHERE job_schedules.job_id = '$id' 
				";	
			$results = $database->getAll($sql);
				 
			return $results;		
		}

		public function get_all_schedule()
		{			
			global $database;
					
			$sql ="
					SELECT job_schedules.id, job_schedules.department_id, job_schedules.job_id, job_schedules.hours,job_schedules.users, 
							job_schedules.start_date, job_schedules.end_date, job_schedules.hours,   production_departments.department_name AS title,production_departments.color,production_departments.textcolor,jobs.job_name
					FROM job_schedules
					INNER JOIN production_departments ON job_schedules.department_id = production_departments.id
					INNER JOIN jobs ON job_schedules.job_id = jobs.id
					";
			$results = $database->getAll($sql);

			return $results;		
		}
		
		
		
		public function get_all_schedule_filtered($keys)
		{			
			global $database;
					
			$sql ="
					SELECT job_schedules.id, job_schedules.department_id, job_schedules.job_id, job_schedules.hours,job_schedules.users, 
							job_schedules.start_date, job_schedules.end_date, job_schedules.hours,   production_departments.department_name AS title,production_departments.color,production_departments.textcolor,jobs.job_name
					FROM job_schedules
					INNER JOIN production_departments ON job_schedules.department_id = production_departments.id
					INNER JOIN jobs ON job_schedules.job_id = jobs.id
					WHERE department_id IN (".$keys.")
					";
			$results = $database->getAll($sql);

			return $results;		
		}
		
		
		
		public function get_total_time_job($job_id)
		{
			global $database;
		
		  	$get_sum ="
				SELECT SUM( UNIX_TIMESTAMP( end_time ) - UNIX_TIMESTAMP( start_time ) ) /3600 AS dates
				FROM job_trackings
				WHERE job_id ='$job_id'
				";	
			$job_sum = $database->getRow($get_sum);
		
			return $job_sum['dates'];
		}
		
		public function create_schedule($context=array()){
		
			global $database;
			
			$job_id 		= $context['job_id'];
			$dept_id		= $context['department_id'];
			$start_date		= $context['start_date'];
			$end_date		= $context['end_date'];
			$hours			= $context['hours'];
			$users			= $context['users'];
			$submitter		= $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');
			
			$insert  = "
						INSERT INTO job_schedules
							SET
						job_id = '$job_id',
						department_id = '$dept_id',
						start_date = '$start_date',
						end_date = '$end_date',
						hours = '$hours',
						users = '$users', 
						creator = '$submitter',
						date_created = '$current_date_time',
						submitter = '$submitter',
						date_submitted = '$current_date_time'
						";
					
			$database->query($insert);
			$last_user_id = $database->insert_ID();
		
			return $last_user_id;
		}
		
		public function get_labour_variance_reports($page_limit=""){
		
		
			global $database;
				
			/*$sql =	"
			 SELECT
			id,quote_id,status
			FROM jobs
			WHERE status != '5' AND status != '2' AND status != '3'
			";
			$sql =	"
			SELECT
			id,quote_id,status
			FROM jobs
			WHERE status = '1'
			";*/
			$sql =	"
					SELECT
					id,quote_id,status
					FROM jobs WHERE status = '1'
					".$page_limit;
			$results= $database->getAll($sql);
			
			foreach($results as $result)
			{
				$quote_id= $result['quote_id'];
				$job_id = $result['id'];
				$get_variance ="
				SELECT sum( (
				SELECT sum( hours )
				FROM `quotes_production_time`
				WHERE quote_id ='$quote_id' ) - (
				SELECT (
				SUM( (
				TIME_TO_SEC( end_time ) - TIME_TO_SEC( start_time ) )
				)
				) /3600 AS dates
				FROM job_trackings
				WHERE job_id ='$job_id' )
				) AS totals
		
				";
				$job_variance = $database->getRow($get_variance);
		
				$jobs[$job_id]=$job_variance['totals'];
			}
			return $jobs;
		}
		
		public function get_labour_variance_reports_count(){
			global $database;
			$sql =	"
					SELECT
					count(*) as total
					FROM jobs WHERE status = '1'
					";
			$result= $database->getRow($sql);
			
			return $result['total'];
		}
		
		
		public function get_total_labour_variance(){
		
		
			global $database;
					
			/*$sql =	"
					SELECT 
					id,quote_id,status
					FROM jobs
					WHERE status != '5' AND status != '2' AND status != '3'
					";			
			$sql =	"
					SELECT 
					id,quote_id,status
					FROM jobs
					WHERE status = '1'
					";*/
			/** kai 07/11/2012
			 * no status because giving the completed job to view labour, overall labour, materials status bar
			 * */
			$sql =	"
					SELECT
					id,quote_id,status
					FROM jobs
					";
			$results= $database->getAll($sql);
			
			foreach($results as $result)
			{
				$quote_id= $result['quote_id'];
				$job_id = $result['id'];
				
				$working_hours = "SELECT SUM( TIME_TO_SEC( end_time ) - TIME_TO_SEC( start_time ) )/3600 AS dates
						FROM job_trackings
						WHERE job_id ='$job_id'";
				
			 	$get_variance ="
					SELECT sum( (
						SELECT sum(hours) FROM `quotes_production_time` WHERE quote_id ='$quote_id' ) - ($working_hours
						 )
						) AS totals
		
					";
			 	$job_variance = $database->getRow($get_variance);
			 	$jobs[$job_id]=$job_variance['totals'];			
			}
			return $jobs;
		}
		
		
		
		
		public function get_indiv_labour_variance($quote_id,$job_id,$dept_array){
		
		
			global $database;
					
			 foreach($dept_array as $k=>$dept_id)
			 {
			 $dept= $dept_id['dept_id'];
			 $get_variance ="
					SELECT sum( (

						SELECT sum( hours )
						FROM `quotes_production_time`
						WHERE quote_id = '$quote_id'
						AND production_departments_id = '$dept' ) - (
						SELECT (
						SUM( (
						TIME_TO_SEC(TIMEDIFF( end_time ,start_time ))
						 ) )
						) /3600 AS dates
						FROM job_trackings
						WHERE job_id = '$job_id'
						AND department = '$dept'
						)
						) AS totals

		
					";
			 	$job_variance = $database->getRow($get_variance);
			 
			 	$jobs[$dept]=$job_variance['totals'];			
			 }
		  
				return $jobs;
		}
		
		
		
		
		public function get_indiv_labour_variance_overview($quote_id,$job_id){
		
		
			global $database;
				$sql ="
			SELECT production_departments_id as dept_id
			FROM quotes_production_time
				WHERE quote_id = '$quote_id'
			
			";
			
			
			
			$dept_array=$database->getAll($sql);
				
			 foreach($dept_array as $k=>$dept_id)
			 {
			 $dept= $dept_id['dept_id'];
			echo $get_variance ="
					SELECT sum( (

						SELECT sum( hours )
						FROM `quotes_production_time`
						WHERE quote_id = '$quote_id'
						AND production_departments_id = '$dept' ) - (
						SELECT (
						SUM( (
						TIME_TO_SEC(TIMEDIFF( end_time ,start_time ))
						 ) )
						) /3600 AS dates
						FROM job_trackings
						WHERE job_id = '$job_id'
						AND department = '$dept'
						)
						) AS totals

		
					";
			 	$job_variance = $database->getRow($get_variance);
			 
			 	$jobs[$dept]=$job_variance['totals'];			
			 }
		  
				return $jobs;
		}
		
		
		
		

		public function get_total_material_variance($quote_id)
		{
			global $database;
			 /*$get_variance ="
				SELECT sum(quantity) as total_quantity, 
						sum(actual_used) as total_actual 
				FROM quotes_materials 
				WHERE quote_id='$quote_id'
			";*/
			
			 //check count of quoted vs job installed
			// $sql = "SELECT count(id)  counter1 FROM quotes_materials WHERE quote_id='$quote_id'
			// 		AND job_flag='0' AND actual_used > '0'
			//		";
			//$count_quoted = $database->getOne($sql);
			
			//$sql = "SELECT count(id) counter2 FROM quotes_materials WHERE quote_id='$quote_id'
			//		AND  job_flag='1' AND actual_used > '0'
			//		";
			//$count_job = $database->getOne($sql);
			
			//$arr = array_intersect($count_quoted['counter1'],$count_job['counter2']);		
			
			//if($count_quoted  < $count_job )
			//{
				//send a red flag/color
			//	$material_variance1['total_quantity']=0;
			//	$material_variance2['total_actual']=1;
				
				 
			//} 
			//else
			//{
				 /*$get_variance ="
					SELECT sum(quantity) as total_quantity  
							  
					FROM quotes_materials 
					WHERE quote_id='$quote_id' AND job_flag=0 AND actual_used > '0'
				";
				
				$material_variance1 = $database->getRow($get_variance);
				
				 $get_variance ="
					SELECT   
							sum(actual_used) as total_actual 
					FROM quotes_materials 
					WHERE quote_id='$quote_id'
				";
				
				$material_variance2 = $database->getRow($get_variance);
	  			}*/
				
				/*$get_variance ="
				SELECT sum(quantity*price) as total_quantity
				FROM quotes_materials
				WHERE quote_id='$quote_id' AND job_flag=0 AND actual_used > '0'
				";*/
					
				$get_variance ="
				SELECT sum(quantity*price) as total_quantity
				FROM quotes_materials
				WHERE quote_id='$quote_id' AND job_flag=0
				";
				
				$material_variance1 = $database->getRow($get_variance);
				
				$get_variance ="
				SELECT
				sum(actual_used*price) as total_actual
				FROM quotes_materials
				WHERE quote_id='$quote_id' AND actual_used > 0
				";
				
				$material_variance2 = $database->getRow($get_variance);
			//}
			
			$material_variance[] = $material_variance1['total_quantity'];
			$material_variance[] = $material_variance2['total_actual'];
			return $material_variance;		
		}
		
		
		
		public function get_total_material_variance_per_item($quote_id)
		{
			global $database;
			$sql = "SELECT id FROM quotes_materials WHERE quote_id='$quote_id'";
			
			$all_materials = $database->getAll($sql);
			
			if(is_array($all_materials))
			{
			
				foreach($all_materials as $mats)
				{
					$id= $mats['id'];
					  $get_variance ="
						SELECT quantity-actual_used  as total_actual 
						FROM quotes_materials 
						WHERE id='$id'
					";
					$material_var[$mats['id']] = $database->getOne($get_variance);
				}
			
			}
			
			
  
			return $material_var;		
		}
		
		
		

		public function get_completed_job($job_id,$quote_id)
		{
			global $database;
		
		    $get_complete ="
						SELECT SUM(
						(SELECT count(*) FROM quotes_production_time WHERE quote_id = '$quote_id') -
						(SELECT count(*) FROM job_trackings_completed WHERE job_id='$job_id')) as totals
						";
						
			 $get_complete ="
						 
						 SELECT count(*) as totals FROM job_trackings_completed WHERE job_id='$job_id' 
						 AND status='1'
						";
			$job_complete = $database->getOne($get_complete);
		
			return $job_complete ;
		}
		
		public function total_quoted()
		{
			global $database;
			
		   	$get_complete ="
				 			SELECT count(*) as totals FROM quotes    
							";
				
			$job_complete = $database->getRow($get_complete);
		
			return $job_complete['totals'];
		}

		public function total_inprogress()
		{
			global $database;
		
		 	$get_complete ="
				 			SELECT count(*) as totals FROM quotes  WHERE status = '1'  
							";
			$job_complete = $database->getRow($get_complete);
		
			return $job_complete['totals'];
		}

		public function total_onhold()
		{
			global $database;
			 
			$get_complete ="
				 			SELECT count(*) as totals FROM quotes  WHERE status = '4'  
							";
			$job_complete = $database->getRow($get_complete);
		
			return $job_complete['totals'];
		}
		
		public function total_cancelled()
		{
			global $database;
		
			$get_complete ="
							SELECT count(*) as totals FROM quotes  WHERE status = '5'  
							";	
			$job_complete = $database->getRow($get_complete);
		
			return $job_complete['totals'];
		}
		
		
		public function total_completed()
		{
			global $database;
		
			$get_complete ="
							SELECT count(*) as totals FROM quotes  WHERE status = '3'  
							";	
			$job_complete = $database->getRow($get_complete);
		
			return $job_complete['totals'];
		}

		public function get_jobnumbers($client_id)
		{			
			global $database;
			
			$get_name = "
			SELECT id,job_number
			FROM quotes
			WHERE client_id = '$client_id'
			";
			$results = $database->getAll($get_name);
			
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[] = $result;	
			
			return $final_results;			
		}	
		
		
		function validate_precedence($job_id,$start,$dept)
		{
			global $database;
	
		   	$sql = "
					SELECT department_id, start_date, production_departments.department_name
					FROM job_schedules
						INNER JOIN production_departments ON job_schedules.department_id = production_departments.id
					WHERE department_id < '$dept'
						AND start_date > '$start'
						AND job_id   = '$job_id'
				";
		
			$check = $database->getAll($sql);
			$errors ='';
// 			if(count($check))
// 			{
// 				foreach($check as $ck )
// 				{
// 					$errors .= "You cannot schedule this before ".$ck['department_name'].'<br />';
// 				} 
// 			}

			return $errors;
		}
	
	
		
		
		function validate_succession($job_id,$start,$dept)
		{
			global $database;
	
		  	$sql = "
					SELECT job_schedules.id,department_id, start_date, production_departments.department_name
					FROM job_schedules
						INNER JOIN production_departments ON job_schedules.department_id = production_departments.id
					WHERE department_id > '$dept'
						AND start_date < '$start'
						AND job_id   = '$job_id'
				";
		
			$check = $database->getAll($sql);
			$errors ='';
			if(count($check))
			{
				foreach($check as $ck )
				{ 
					$str .= "id = '".$ck['id']."' OR ";
				}
				
				$str = substr($str, 0 ,-3); 
				  $sql = "
							UPDATE job_schedules
							SET start_date='$start'
							WHERE ".$str ;
				
				$results = $database->query($sql);
			}
		}
	

		public function update_schedule($context=array())
		{
			global $database;
			
			$job_id = $context['job_id'];
			$dept_id = $context['dept_id'];
			$mysql_start = $context['mysql_start'];
			$mysql_end = $context['mysql_end'];
			$hours = $context['hours'];
			$users = $context['users'];
			$id = $context['id'];
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');
			
			$update  = "
				UPDATE job_schedules
				SET
					department_id = '$dept_id',
					start_date = '$mysql_start',
					end_date = '$mysql_end',
					hours = '$hours',
					users = '$users',
					submitter = '$submitter',
					date_submitted = '$current_date_time'
				WHERE id = '$id'
			";
			
			$database->query($update);
		} 
		
		
		public function update_schedule_from_drag($context=array())
		{
			global $database;
			
			$mysql_start = $context['mysql_start'];
			$mysql_end = $context['mysql_end'];
			$id = $context['id'];
			$move = $context['move'];
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');
			$job_id=$context['job_id'];
			$delta=$context['delta'];
		 	
			if($move == 'all'){
			
			$update = "
				UPDATE job_schedules SET start_date= DATE_ADD(start_date, INTERVAL '$delta' DAY),end_date=DATE_ADD(end_date, INTERVAL '$delta' DAY) WHERE job_id='$job_id' 
			";
			
			
			}
			else
			{
			
			$update  = "
				UPDATE job_schedules
				SET
					start_date = '$mysql_start',
					end_date = '$mysql_end',
					submitter = '$submitter',
					date_submitted = '$current_date_time'
				WHERE id = '$id'
			";	
			}
			$database->query($update);
		}
		
		
		public function remove_schedule($id)
		{
			global $database;
			
			$update 	= "
				DELETE FROM job_schedules WHERE id = '$id'
			";
			$database->query($update);
		}
		
		 

		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 	quotes.qualification_notes,quotes.sitesurvey_notes,quotes.production_notes,
						quotes.management_notes,quotes.finance_notes, departments_quotes.id
				FROM quotes
				INNER JOIN departments_quotes ON quotes.id = departments_quotes.job_id
				WHERE departments_quotes.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 quotes 
				WHERE id = (SELECT job_id FROM departments_quotes WHERE id='$id')
				";
				 
			$results = $database->getOne($get_job);

			return $results;				 
		}

		public function get_job_number_by_quote($quote_id)
		{
			global $database;
						
			 $get_job = "
				SELECT 	job_number 
				FROM quotes 
				WHERE id = '$quote_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 quotes SET `client_id` = '$company_id', `job_name` = '$description', `job_number` = '$job_number',
							`jobtype` = '$signtype', `description` = '$description',
							`status` = '1', `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_quotes($status_id)
		{
			global $database;
						
			$get_request = "
					SELECT *
					FROM $db_name"."quotes
					WHERE status = '$status_id'   
					";
			$results = $database->getAll($get_request);	

			return $results;				
		}
		
		public function get_quotes_bystatus($dept_id_array,$dept="")
		{
			global $database;
					if($dept==""){
						$dept_query="";
					}else{
						$dept_query=$dept;
					}
	       $get_request = "
				SELECT departments_quotes.*, 
					quotes.client_id,
					quotes.job_name,
					quotes.job_number,
					quotes.description,
					quotes.stage_id,
					quotes.jobtype,
					quotes.submitter
				FROM $db_name"."departments_quotes
				INNER JOIN quotes
				ON departments_quotes.quote_id = quotes.id WHERE ".$dept_query."  departments_quotes.action_id=0";
				 
				//WHERE departments_quotes.department_id IN ('".implode("', '", array_unique($dept_id_array))."')     $dept
				//";
			$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 quotes 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" => "quotes", "action_type" => 2,"foreign_id" => $id, "description" => "Modified"));						
									
			$results = $database->CompleteTrans();
			return $results;
		}
		
		public function get_all_quotes_select()
		{	
			global $database;

			$get_quotes = "
						SELECT *
						FROM quotes
						ORDER BY job_name
						";
			$quotes = $database->getAll($get_quotes);			
			
			$quotes_list = array();
			
			foreach($quotes AS $result)
				$quotes_list[] = array('id'=>$result['id'],'name'=>$result['job_number']);
			
			return $quotes_list;
		}
		
		
		public function get_assigned_staff($id)
		{	
			global $database;

			$get_staff = "
					SELECT users
					FROM job_schedules
					WHERE id = '$id'  
			";
			$staff = $database->getRow($get_staff);			

			return $staff['users'];
		}
		
		
		
		public function get_colors($status='1')
		{	
			global $database;

			$get_color = "
					SELECT *
					FROM schedule_colors  
					WHERE status =  '$status'
					  
			";
			$color = $database->getAll($get_color);			

			return $color;
		}
		
		
		public function get_job_colors($id)
		{	
			global $database;

			$get_color = "
					SELECT *
					FROM schedule_colors where id='$id'  
					  
			";
			$color = $database->getRow($get_color);			

			return $color;
		}
		
		
		
		
		public function save_job_colors($POST)
		{	
			global $database;
			$job_id = $POST['job_id'];
			$color = "#".$POST['color'];
			$textcolor = "#".$POST['textcolor'];
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');
			
			$save_color = "
					INSERT INTO schedule_colors(job_id,color,textcolor,creator,date_created,submitter,date_submitted)
					VALUES('$job_id','$color','$textcolor','$submitter','$current_date_time','$submitter','$current_date_time')
			";
			$results = $database->query($save_color);			
			
			return $results;
		}
		
		
		public function check_duplicate($id)
		{	
			global $database;
			 
			
			$get_color = "
					SELECT job_id 
					FROM schedule_colors
					WHERE job_id='$id'
			";
			$results = $database->getOne($get_color);			
			
			return $results;
		}
		
		
		public function update_job_colors($POST)
		{	
			global $database;
			$job_id = $POST['job_id'];
			$id = $POST['id'];
			$color = "#".$POST['color'];
			$textcolor = "#".$POST['textcolor'];
			$submitter = $_SESSION['user']['id'];
			$current_date_time = date('Y-m-d G:i:s');
			
			$save_color = "
					UPDATE   schedule_colors
					SET `job_id`='$job_id',`color`='$color',`textcolor`='$textcolor',`submitter`='$submitter',`date_submitted`='$current_date_time'
					WHERE id = '$id'
			";
			$results = $database->query($save_color);			
			 
			return $results;
		}	
																
	}

?>