<?
	class common_department
	{						
		public function department_details($department_id)
		{			
			global $database;
			$db_name = DB_CLIENT;			
			
			 $get_department = "
			SELECT *
			FROM departments
			WHERE id = '$department_id'
			LIMIT 1
			";							
			$value = $database->getRow($get_department);;			
			
			$results['creator'] = 

			$value['creator'] = common_users::full_name($value['creator']);
			$value['submitter'] = common_users::full_name($value['submitter']);

			$value['date_created'] = dates::change_date_time($value['date_created']);	
			$value['date_submitted'] = dates::change_date_time($value['date_submitted']);										
			
			return $value;			
		}

		public function get_all_departments_users()
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$get_all_department = "
			SELECT *
			FROM $db_name"."departments_users
			";
			$results = $database->getAll($get_all_department);			

			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;	
			
			return $final_results;		
		}	
								
		public function get_all_department()
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$get_all_department = "
			SELECT 
				id, 
				department_name AS name
			FROM $db_name"."departments
			WHERE status = '1'
			";
			$results = $database->getAll($get_all_department);			

			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;	
			
			return $final_results;		
		}	
		
		public function get_all_departments()
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$get_all_department = "
			SELECT 
				id, 
				department_name AS name
			FROM $db_name"."departments
			WHERE status = '1'
			";
			$results = $database->getAll($get_all_department);			

			$final_results = array();
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}		
			
			return $final_results;		
		}			
		
		public function get_all_not_complete_factory_department()
		{
			global $database;
			
			$db_name = DB_CLIENT;

		 
			
			$get_all_factory_department = "SELECT id AS id, department_name AS name
											FROM production_departments
											WHERE id NOT
											IN (
											
											SELECT department_id
											FROM job_trackings_completed
											)
											AND status = '1'
											";
											
			$results = $database->getAll($get_all_factory_department);			

			$final_results = array();
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}	
			
			return $final_results;		
		}
		
		public function get_all_factory_department()
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$get_all_factory_department = "
			SELECT 
				id, 
				department_name AS name
			FROM $db_name"."production_departments
			WHERE status = '1'
			";
											
			$results = $database->getAll($get_all_factory_department);			

			$final_results = array();
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}	
			
			return $final_results;		
		}
		
		
		
		public function get_all_factory_department_with_quote($id)
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$sql = "
			SELECT quote_id FROM jobs
				WHERE id='$id'
			";
			
			$quote = $database->getRow($sql);
			$q_id = $quote['quote_id'];
			$get_all_factory_department = "
			SELECT   production_departments.id,production_departments.department_name as name
						 FROM quotes_production_time
						 INNER JOIN production_departments
						 ON quotes_production_time.production_departments_id = production_departments.id
						 WHERE quote_id ='$q_id'
			";
											
			$results = $database->getAll($get_all_factory_department);			

			$final_results = array();

			$final_results[] = array('id'=> 11,'name'=> 'Visual Art');
			$final_results[] = array('id'=> 12,'name'=> 'Installation');
			$final_results[] = array('id'=> 13,'name'=> 'Pick Up/Delivery');
			 $final_results[] = array('id'=> 14,'name'=> 'Footing');
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}	
			
			return $final_results;		
		}
		
		public function get_all_factory_department_with_quote_tracking($id)
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$sql = "
			SELECT quote_id FROM jobs
				WHERE id='$id'
			";
			
			$quote = $database->getRow($sql);
			$q_id = $quote['quote_id'];
			$get_all_factory_department = "
			SELECT   production_departments.id,production_departments.department_name as name
						 FROM quotes_production_time
						 INNER JOIN production_departments
						 ON quotes_production_time.production_departments_id = production_departments.id
						 WHERE quote_id ='$q_id'
			";
											
			$results = $database->getAll($get_all_factory_department);			

			$final_results = array();

			$final_results[] = array('id'=> 11,'name'=> 'Visual Art');
			$final_results[] = array('id'=> 12,'name'=> 'Installation');
			$final_results[] = array('id'=> 13,'name'=> 'Pick Up/Delivery');
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}	
			
			return $final_results;		
		}
		
		public function get_all_factory_department_with_quote_not_complete($job_id)
		{
			global $database;
			
			$db_name = DB_CLIENT;
			
			$get_quote_id = "
						SELECT quote_id
						FROM jobs
						WHERE id = '$job_id'
						LIMIT 1
						";
			$quote_id = $database->getOne($get_quote_id);

			$get_all_factory_department = "
			SELECT  production_departments.id,production_departments.department_name as name
			FROM quotes_production_time
				INNER JOIN production_departments ON quotes_production_time.production_departments_id = production_departments.id
			WHERE quote_id ='$quote_id' AND production_departments.id NOT IN (
											SELECT department_id
											FROM job_trackings_completed
											WHERE job_id = '$job_id'
										)
			";
											
			$results = $database->getAll($get_all_factory_department);			

			$final_results = array();
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}	
			
			return $final_results;		
		}
		
		public function get_all_factory_department_by_id()
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$get_all_factory_department = "
			SELECT 
				id AS id, 
				department_name AS name
			FROM $db_name"."production_departments
			WHERE status = '1'
			";						 											
			$results = $database->getAll($get_all_factory_department);			

			$final_results = array();
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}	
			
			return $final_results;		
		}
		
		public function get_all_factory_department_tracking()
		{
			global $database;
			
			$db_name = DB_CLIENT;

			$get_all_factory_department = "
			SELECT 
				id, 
				department_name AS name
			FROM $db_name"."production_departments
			WHERE status = '1'
				AND manual_entry = '0'
			";
			$results = $database->getAll($get_all_factory_department);			

			$final_results = array();
			
			foreach($results AS $result)
			{
				$final_results[] = array('id'=>$result['id'],'name'=>$result['name']);
			}	
			
			return $final_results;		
		}		

		public function department_name($department_id)
		{			
			global $database;
			$db_name = DB_CLIENT;			
			  $get_department = "
			SELECT 
				department_name  
			FROM departments
			WHERE id = '$department_id'
			LIMIT 1
			";							
			$results = $database->getOne($get_department);			
			
			return $results;			
		}
		
		public function production_department_name($id)
		{			
			global $database;
			$db_name = DB_CLIENT;	
					
			$get_department = "
			SELECT 
				department_name  
			FROM $db_name"."production_departments
			WHERE id = '$id'
			LIMIT 1
			";							
			$results = $database->getOne($get_department);			
			
			return $results;			
		}
		
		public function get_all_production_departments()
		{			
			global $database;
			$db_name = DB_CLIENT;	
					
			$get_department = "
			SELECT *
			FROM $db_name"."production_departments
			";							
			$results = $database->getAll($get_department);			

			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;
			
			return $final_results;							
		}		
		
		public function get_production_departments($id)
		{			
			global $database;
			$db_name = DB_CLIENT;	
					
			$get_department = "
			SELECT 
				department_name  
			FROM $db_name"."production_departments
			WHERE id = '$id'
			 
			";							
			$results = $database->getAll($get_department);			
			
			return $results;			
		}
		
		
		

		public function get_department_of_user($id)
		{
			global $database;
			
			$db_name = DB_CLIENT;

			  $get_department = "
			SELECT 				  
				department_id 
			FROM $db_name"."departments_users
			WHERE user_id = '$id'
			";
			$results = $database->getAll($get_department);			

			$final_results = array();
			
			foreach($results AS $result)
				$final_results[] = $result['department_id'];	
			
			return $final_results;		
		}
		
		
		public function get_department_action($department_id)
		{
			global $database;
			
			$db_name = DB_CLIENT;

		  	   $get_actions = "
			SELECT 
				  
				concat(id,':',department2_id,':',status) as id,action_name as name
			FROM $db_name"."actions
			WHERE department_id = '$department_id' and inactive=0
			";
			$results = $database->getAll($get_actions);			

			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result   ;	
			
			return $final_results;		
		}
		
		
		
		public function get_department_name_of_user($id)
		{
			global $database;
			
			$db_name = DB_CLIENT;

			   $get_department = "
			SELECT 
				  
				departments_users.department_id as id,
				departments.department_name as name
			FROM $db_name"."departments_users
			INNER JOIN departments
			ON departments_users.department_id = departments.id
			WHERE user_id = '$id'
			";
			$results = $database->getAll($get_department);			

			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;	
			
			return $final_results;		
		}
		
		
		
		public function get_all_group_user($group_name)
		{					
			global $database;
			
			$db_name = DB_CLIENT_ACCOUNTS;
			
			$group_id = common_department::get_group_id($group_name);

			$get_all_auditors = "
			SELECT 
				id, 
				concat(first_name,' ',last_name) AS name
			FROM $db_name"."department
			WHERE group_id = '$group_id'
			";
			$results = $database->getAll($get_all_auditors);			

			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;	
			
			return $final_results;			
		}
		
		public function get_user_group_id($user_id)
		{	
			global $database;
			
			$db_name = DB_CLIENT_ACCOUNTS;

			$get_group_id = "
			SELECT group_id
			FROM $db_name"."department
			WHERE id = '$user_id'
			LIMIT 1
			";
			$results = $database->getOne($get_group_id);			
			
			return $results;	
		}
		
		public function get_group_id($group_name)
		{	
			global $database;
			
			$db_name = DB_CLIENT_SETTINGS;
			
			$group_name = ucfirst($group_name);
			
			$get_group_id = "
			SELECT id, name
			FROM $db_name"."groups
			WHERE name = '$group_name'
			LIMIT 1
			";
			$results = $database->getOne($get_group_id);				
			
			return $results;
		}																																
	}

?>