<?
	class common_quotes
	{						
		public function get_details($id)
		{			
			global $database;
					
			$sql = "
			SELECT *
			FROM quotes
			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']);

			$priorities[0] = "Medium";
			$priorities[1] = "High";
			$priorities[2] = "Medium";
			$priorities[3] = "Low";

			$results['priority_nice'] = $priorities[$results['priority']];
					
			$client = common_clients::get_details($results['client_id']);

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

			$location_details = common_ref_client::get_client_location($results['location_id']);			
			
			$results['address_nice'] = $location_details['address'].", ".$location_details['suburb'].", ".$location_details['state'].", ".$location_details['post_code'];

			$results['address'] = $location_details['address'];
			$results['suburb'] = $location_details['suburb'];
			$results['state'] = $location_details['state'];
			$results['post_code'] = $location_details['post_code'];
						
			$site_location_details = common_ref_client::get_client_location($results['site_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'];
												
			$results['description_clean'] = nl2br( stripslashes( htmlentities( $results['description'], ENT_QUOTES, 'UTF-8' ) ) );	
			$results['other_note_clean'] = nl2br( stripslashes( htmlentities( $results['other_note'], ENT_QUOTES, 'UTF-8' ) ) );	

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

		public function get_sales_alloc($id)
		{
			global $database;
			
			$sql = "
			SELECT sales_alloc
			FROM quotes
			WHERE id = '$id'			
			";
			return $database->getOne($sql);				
		}
		
		public function get_status($id)
		{
			global $database;
				
			$sql = "
			SELECT status
			FROM quotes
			WHERE id = '$id'
			";
			return $database->getOne($sql);
		}
				
		public function get_contacts($quote_id)
		{
			global $database;
						
			$sql = "
			SELECT *
			FROM quotes_contacts
			WHERE quote_id = '$quote_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 quotes
			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_quotes_details($quote_id)
		{			
			global $database;							
			 			 
			$get_quotes = "
			SELECT 
				quotes.*,
				clients.company_name, 
				clients.primary_location_id, 
				clients.primary_contact_id
			FROM quotes 			 
			INNER JOIN clients ON quotes.client_id = clients.id 
			WHERE quotes.id = '$quote_id'";
			$client = $database->getRow($get_quotes);
						
			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 	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);
			//$results = $database->getAll($get_name);
			//bug::bug_array('',$results);
			 
			
			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 get_job_id_by_quote($quote_id)
		{
			global $database;
		
			$get_job = "
			SELECT 	id,status
			FROM jobs
			WHERE quote_id = '$quote_id'
			";
			$results = $database->getRow($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` = '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_all_quotes()
		{
			global $database;
						
			$get_request = "
			SELECT id,job_type as name
			FROM quotes
			";
			$results = $database->getAll($get_request);	
			 	
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;	
						
			return $final_results;				
		}
				
		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;
			
			//$database->debug = true;
			$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_quote_labour($quote_id)
		{
			global $database;
			
			$sql = "
			SELECT *
			FROM quotes_labour
			WHERE quote_id = '$quote_id'
			LIMIT 1
			";
			$result = $database->getRow($sql);
			
			return $result;
		}

		public function get_quote_labour_summary($quote_id)
		{
			global $database;
			
			$labours = self::get_quote_labour($quote_id);
			
			return $result;
		}
		
		public function get_quote_labour_times($quote_id)
		{
			global $database;
			/** Kai 01/11/2012*/
			$sql = "
			SELECT 	SUM(hours)
			FROM 	quotes_production_time
			WHERE 	quote_id = '$quote_id' AND
					production_departments_id <> 12 AND
					production_departments_id <> 13
			";
			
			$sql = "
			SELECT 	SUM(hours)
			FROM 	quotes_production_time
			WHERE 	quote_id = '$quote_id'
			";
			$hours = $database->getOne($sql);
			return $hours;
		}		

		public function get_quote_install($quote_id)
		{
			global $database;
						
			$sql = "
			SELECT install_total
			FROM quotes_labour
			WHERE quote_id = '$quote_id'
			";
			$install_total = $database->getOne($sql);
			
			return $install_total;
		}	
				
		public function get_quote_materials($quote_id)
		{
			global $database;
			
			$sql = "
			SELECT *
			FROM quotes_materials
			WHERE quote_id = '$quote_id'
			";
			$result = $database->getAll($sql);
			
			return $result;
		}		
		
		public function get_quote_materials_summary($quote_id)
		{
			$materials = self::get_quote_materials($quote_id);
			
			$return_values = array();

			$return_values['total_quote'] = 0;
			$return_values['total_job'] = 0;
			
			foreach($materials AS $material)
			{
				$return_values['total_quote'] += ($material['quantity'] * $material['price']);
				$return_values['total_job'] += ($material['actual_used'] * $material['price']);				
			}
			
			return $return_values;
		}
		
		
		public function get_quote_id($id)
		{
			global $database;
			
			$sql = "
			SELECT quote_id
			FROM jobs
			WHERE id = '$id'
			LIMIT 1
			";
			$result = $database->getOne($sql);
			
			return $result;
		}
		
		public function get_checklist($id)
		{	
			global $database;
			
			$checklist = "
			SELECT *
			FROM quotes_checklist
			WHERE quote_id = '$id'
			LIMIT 1
			";				
			$result=$database->getRow($checklist);			

			return $result;
		}
		
		public function get_job_name($id)
		{
			global $database;
			
			$sql = "
			SELECT job_name
			FROM quotes
			WHERE id = '$id'
			LIMIT 1
			";
			$result = $database->getOne($sql);
			
			return $result;
		}
		
		public function getSalesQuotesCreated($user_id,$start_date,$end_date){
			global $database;
			
			$sql = "SELECT quotes.*
					FROM quotes
					INNER JOIN  project_workflow a ON a.foreign_id = quotes.id
					INNER JOIN  project_tasks b ON a.id = b.project_workflow_id
					WHERE a.table='quotes'
					AND b.workflow_task_id IN (7,30,566,59,574,674,758,759,760)
					AND quotes.status = 1
					AND quotes.sales_alloc = '$user_id'
					AND b.date_created >= '$start_date 00:00:00'
					AND b.date_created <= '$end_date 23:59:59'
					GROUP BY quotes.id";
			$result = $database->getAll($sql);
				
			return $result;
		}
		
		public function getSalesQuotesAccepted($user_id,$start_date,$end_date){
			global $database;
		
			$sql = "SELECT quotes.* FROM quotes
							INNER JOIN project_workflow ON project_workflow.foreign_id = quotes.id
							WHERE project_workflow.table = 'quotes' 
							AND project_workflow.direction = 'yes' 
							AND project_workflow.workflow_id IN (6,360,406,433)
							AND quotes.status = '3'
							AND quotes.sales_alloc = '$user_id' 
							AND project_workflow.date_submitted >= '$start_date 00:00:00' 
							AND project_workflow.date_submitted <= '$end_date 23:59:59'
							GROUP BY quotes.id";
			$result = $database->getAll($sql);
		
			return $result;
		}
		
		public function getSalesQuotesRejected($user_id,$start_date,$end_date){
			global $database;
		
			$sql = "SELECT quotes.* FROM quotes
					INNER JOIN project_workflow ON project_workflow.foreign_id = quotes.id
					WHERE ((project_workflow.table = 'quotes' AND project_workflow.direction = 'no' AND project_workflow.workflow_id IN (6,360,406,433)
					AND quotes.status = '3' AND project_workflow.date_submitted >= '$start_date 00:00:00' AND project_workflow.date_submitted <= '$end_date 23:59:59')
					OR (quotes.status = '5' AND quotes.date_submitted >= '$start_date 00:00:00' AND quotes.date_submitted <= '$end_date 23:59:59'))
					AND quotes.sales_alloc = '$user_id'
					group by quotes.id";
			$result = $database->getAll($sql);
		
			return $result;
		}
		
		public function getQuotesNoSale($start_date,$end_date){
			global $database;
		
			$sql = "SELECT *
			FROM quotes
			WHERE sales_alloc = '0'
			AND date_created >= '$start_date 00:00:00'
			AND date_created <= '$end_date 23:59:59'
			GROUP BY quotes.id";
			$result = $database->getAll($sql);
		
			return $result;
		}
		
		public function getQuotesNoSaleCreated($start_date,$end_date){
			global $database;
				
			$sql = "SELECT quotes.*
			FROM quotes
			INNER JOIN  project_workflow a ON a.foreign_id = quotes.id
			INNER JOIN  project_tasks b ON a.id = b.project_workflow_id
			WHERE a.table='quotes'
			AND b.workflow_task_id IN (7,30,566,59,574,674,758,759,760)
			AND quotes.status = 1
			AND quotes.sales_alloc = '0'
			AND b.date_created >= '$start_date 00:00:00'
			AND b.date_created <= '$end_date 23:59:59'
			GROUP BY quotes.id";
			$result = $database->getAll($sql);
		
			return $result;
		}
		
		public function getQuotesNoSaleAccepted($start_date,$end_date){
			global $database;
		
			$sql = "SELECT quotes.* FROM quotes
			INNER JOIN project_workflow ON project_workflow.foreign_id = quotes.id
			WHERE project_workflow.table = 'quotes'
			AND project_workflow.direction = 'yes'
			AND project_workflow.workflow_id IN (6,360,406,433)
			AND quotes.status = '3'
			AND quotes.sales_alloc = '0'
			AND project_workflow.date_submitted >= '$start_date 00:00:00'
			AND project_workflow.date_submitted <= '$end_date 23:59:59'
			GROUP BY quotes.id";
			$result = $database->getAll($sql);
		
			return $result;
		}
		
		public function getQuotesNoSaleRejected($start_date,$end_date){
			global $database;
		
			$sql = "SELECT quotes.* FROM quotes
			INNER JOIN project_workflow ON project_workflow.foreign_id = quotes.id
			WHERE ((project_workflow.table = 'quotes' AND project_workflow.direction = 'no' AND project_workflow.workflow_id IN (6,360,406,433)
			AND quotes.status = '3' AND project_workflow.date_submitted >= '$start_date 00:00:00' AND project_workflow.date_submitted <= '$end_date 23:59:59')
			OR (quotes.status = '5' AND quotes.date_submitted >= '$start_date 00:00:00' AND quotes.date_submitted <= '$end_date 23:59:59'))
			AND quotes.sales_alloc = '0'
			group by quotes.id";
			$result = $database->getAll($sql);
		
			return $result;
		}
																
	}

?>