<?
	class common_workflow
	{				
		public function get_details($id)
		{
			global $database;
								
			$query = "
			SELECT *
			FROM $db_name"."workflow
			WHERE id = '$id'
			LIMIT 1";
			$results = $database->getRow($query);					
						
			$results['departments'] = self::get_departments($id);	
			$results['groups'] = self::get_groups($id);	
										
			$results['tasks'] = self::get_tasks($id);	
								
			return $results;
		}

		public function getWorkflowName($id)
		{
			global $database;
			$query = "
			SELECT name
			FROM $db_name"."workflow
			WHERE id = '$id'
			LIMIT 1";
			$result = $database->getRow($query);	
			$name = $result['name'];
			if(!is_null($name)) {
				return $name;
			}					
			return null;
		}

		public function get_departments($id)
		{
			global $database;

	        $departments = common_department::get_all_department();		
									
			$query = "
			SELECT department_id
			FROM $db_name"."workflow_departments
			WHERE workflow_id = '$id'
			";
			$results = $database->getAll($query);
			
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['department_id']] = array("id" => $result['department_id'],"name" => $departments[$result['department_id']]['name']);	
			
			return $final_results;											
		}	

		public function get_groups($id)
		{
			global $database;

	        $groups = common_ref_global_client::get_all_groups("groups");
									
			$query = "
			SELECT group_id
			FROM $db_name"."workflow_groups
			WHERE workflow_id = '$id'
			";
			$results = $database->getAll($query);
			
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['group_id']] = array("id" => $result['group_id'],"name" => $groups[$result['group_id']]['name']);	
			
			return $final_results;											
		}
				
		public function get_tasks($id)
		{
			global $database;
							
			$query = "
			SELECT *
			FROM $db_name"."workflow_tasks
			WHERE workflow_id = '$id'
			ORDER BY `trigger`,`stage` ASC
			";
			$results = $database->getAll($query);
					
			$final_results = array();

	        $groups = common_ref_global_client::get_all_groups("groups");
	        $departments = common_department::get_all_department();			
			
			foreach($results AS $result)
			{				
				$final_results[$result['id']] = $result;
				$final_results[$result['id']]['departments'] = self::get_tasks_departments($result['id'],$departments);					
				$final_results[$result['id']]['groups'] = self::get_tasks_groups($result['id'],$groups);									
			}
			
			return $final_results;											
		}

		public function getTasksByIdAndTriggerType($id,$type=1){
			global $database;
			$query = "
			SELECT *
			FROM $db_name"."workflow_tasks
			WHERE workflow_id = $id AND  `trigger` = $type
			ORDER BY `trigger`,`stage` ASC
			";
			$results = $database->getAll($query);
					
			$final_results = array();

	        $groups = common_ref_global_client::get_all_groups("groups");
	        $departments = common_department::get_all_department();			
			
			foreach($results AS $result)
			{	
				$final_results[$result['id']] = $result;
				$final_results[$result['id']]['departments'] = self::get_tasks_departments($result['id'],$departments);					
				$final_results[$result['id']]['groups'] = self::get_tasks_groups($result['id'],$groups);									
			}
			
			return $final_results;	
		}			
		
		public function get_tasks_departments($id,$departments)
		{
			global $database;
									
			$query = "
			SELECT department_id
			FROM $db_name"."workflow_tasks_departments
			WHERE template_id = '$id'
			";
			$results = $database->getAll($query);
			
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[] = array("id" => $result['department_id'],"name" => $departments[$result['department_id']]['name']);	
			
			return $final_results;											
		}	

		public function get_tasks_groups($id,$groups)
		{
			global $database;
									
			$query = "
			SELECT group_id
			FROM $db_name"."workflow_tasks_groups
			WHERE template_id = '$id'
			";
			$results = $database->getAll($query);
			
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[] = array("id" => $result['group_id'],"name" => $groups[$result['group_id']]['name']);	
			
			return $final_results;											
		}				

		public function get_all_workflow($master_id = "")
		{	
			global $database;
			
			$sql = (!empty($master_id)) ? "WHERE master_id = '$master_id'" : "";
			
			$query = "
			SELECT *
			FROM workflow
			$sql
			ORDER BY stage ASC
			";
			$results = $database->getAll($query);
						
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['id']] = $result;				
			
			return $final_results;
		}	

		public function get_all_workflow_departments()
		{
			global $database;
			
			$query = "
			SELECT *
			FROM workflow_departments
			";
			$results = $database->getAll($query);
						
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['workflow_id']][] = $result['department_id'];				
			
			return $final_results;			
		}

		public function get_all_workflow_groups()
		{
			global $database;
		
			$query = "
			SELECT *
			FROM workflow_groups
			";
			$results = $database->getAll($query);
						
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['workflow_id']][] = $result['group_id'];				
			
			return $final_results;			
		}
				
		public function get_all_departments($table)
		{
			global $database;
		
			$query = "
			SELECT *
			FROM $table
			";
			$results = $database->getAll($query);
						
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['template_id']][] = $result['department_id'];				
			
			return $final_results;			
		}

		public function get_all_groups($table)
		{
			global $database;
		
			$query = "
			SELECT *
			FROM $table
			";
			$results = $database->getAll($query);
						
			$final_results = array();
			
			foreach($results AS $result)
				$final_results[$result['template_id']][] = $result['group_id'];				
			
			return $final_results;			
		}
		
		public function action_access($id)
		{
			global $database;

			return true;

			$my_group = $_SESSION['user']['group_id'];
			$my_departments = $_SESSION['user']['department_id'];
			
			$department_ids = "";
			
			foreach($my_departments AS $my_department)
				$department_ids .= "'".$my_department."',";
			
			$department_ids = "(".substr($department_ids,0,strlen($department_ids) - 1).")";
								 				
			$check_departments = "
			SELECT count(workflow_id)
			FROM workflow_departments
			WHERE workflow_id = '$id' AND department_id IN $department_ids
			";
			$num_departments = $database->getOne($check_departments);		
						
			if($num_departments > 0)
				return true;
			else
				return false;					
		}	
				
		public function task_access($id)
		{
			global $database;
			
			return true;
			
			$my_group = $_SESSION['user']['group_id'];
			$my_departments = $_SESSION['user']['department_id'];

			$department_ids = "";
			
			foreach($my_departments AS $my_department)
				$department_ids .= "'".$my_department."',";
			
			$department_ids = "(".substr($department_ids,0,strlen($department_ids) - 1).")";
							
			$check_departments = "
			SELECT count(template_id)
			FROM workflow_tasks_departments
			WHERE template_id = '$id' AND department_id IN $department_ids
			";
			$num_departments = $database->getOne($check_departments);		
			
			//echo $check_departments."<br />";
			
			if($num_departments > 0)
				return true;
			else
				return false;
								
			/*return true;
						
			$my_group = $_SESSION['user']['group_id'];
			$my_departments = $_SESSION['user']['department_id'];
				
			$check_group = "
			SELECT count(template_id)
			FROM workflow_tasks_groups
			WHERE template_id = '$id' AND group_id = '$my_group'
			";
			$num_group = $database->getOne($check_group);
			
			if($num_group == 1)
			{
				$department_ids = "";
				
				foreach($my_departments AS $my_department)
					$department_ids .= "'".$my_department."',";
				
				$department_ids = "(".substr($department_ids,0,strlen($department_ids) - 1).")";
									 
				$check_departments = "
				SELECT count(template_id)
				FROM workflow_tasks_departments
				WHERE template_id = '$id' AND department_id IN $department_ids
				";
				$num_departments = $database->getOne($check_departments);		
				
				if($num_departments > 0)
					return true;
				else
					return false;
			}			
			else 
				return false;*/
		}

		/**
		 * Kai 22/11/2012
		 * 
		 * get project workflow for work awaiting approval
		 * 
		 * used in: views_company_summary
		 * 			list_quote
		 * */
		public function get_work_awaiting_approval($start_date,$end_date){
			global $database;
			/** foreign_id: quote id */
			$get_decision 	= "
			SELECT *
			FROM project_workflow
			WHERE direction = ''
			AND project_workflow.table ='quotes'
			AND foreign_id IN (SELECT id
								FROM quotes
								WHERE status = '1'
								AND date_submitted >= '$start_date 00:00:00'
								AND date_submitted  <= '$end_date 23:59:59')
								AND workflow_id IN (6,360,406,433,435)
			";
			$job_wait_approval	= $database->getAll($get_decision);
			return $job_wait_approval;
		}
		
		/**
		 * Kai 22/11/2012
		 *
		 * get project workflow for work in production
		 *
		 * used in: views_company_summary
		 * 			list_jobs
		 * */
		public function get_work_in_production($start_date,$end_date){
			global $database;
			/** foreign_id: job id */
// 			$get_job_production	= "
// 			SELECT foreign_id 
// 			FROM project_workflow
// 			WHERE project_workflow.table = 'jobs'
// 			AND direction = 'yes'
// 			AND foreign_id IN (SELECT id FROM jobs
// 								WHERE status = '1'
// 								AND date_submitted >= '$start_date 00:00:00'
// 								AND date_submitted  <= '$end_date 23:59:59')
// 								AND workflow_id IN (35,373,419,433)
// 			";

			$get_job_production	= "
				SELECT a.foreign_id
				FROM project_workflow a, project_tasks b
				WHERE a.table = 'jobs'
				AND a.direction = 'yes'
				AND a.id = b.project_workflow_id
				AND a.foreign_id IN (SELECT id FROM jobs
										WHERE status = '1'
										AND date_submitted >= '$start_date 00:00:00'
										AND date_submitted  <= '$end_date 23:59:59')
				AND b.workflow_task_id IN (36,594,699,762)
			";
			
			$jobs_in_production	= $database->getAll($get_job_production);
			return $jobs_in_production;
		}
		
		/**
		 * Kai 22/11/2012
		 *
		 * get project workflow for work not accepted
		 *
		 * used in: views_company_summary
		 * 			list_quote
		 * */
		public function get_work_not_accepted($start_date,$end_date){
			global $database;
			/** foreign_id: quote id */
			$get_job_closed	= "
			SELECT foreign_id 
			FROM project_workflow
			WHERE project_workflow.table = 'quotes'
			AND direction = 'no'
			AND foreign_id IN (SELECT id FROM quotes
								WHERE date_submitted >= '$start_date 00:00:00'
								AND date_submitted  <= '$end_date 23:59:59')
								AND workflow_id IN (6,360,406,433)
			";
			$jobs_not_accepted = $database->getAll($get_job_closed);
			return $jobs_not_accepted;
		}
		
		public function get_pending_quotes(){
			global $database;
			/** foreign_id: quote id */
			$sql = "
			SELECT foreign_id
			FROM project_workflow
			WHERE project_workflow.table = 'quotes'
			AND !(direction != '' AND workflow_id IN (6,360,406,433))
			AND foreign_id IN (SELECT id FROM quotes
			WHERE status = 1) group by foreign_id
			";
			$get_pending_quote = $database->getAll($sql);
			return $get_pending_quote;
		}
		
		public function get_production_jobs(){
			global $database;
			
			//AND (a.direction = 'yes' AND a.workflow_id IN (419,437,438,436))
			$get_job_to_right	= "
			SELECT a.foreign_id
			FROM project_workflow a,project_tasks b
			WHERE a.table = 'jobs'
			AND a.id=b.project_workflow_id
			AND (b.workflow_task_id IN (35,373,419,436) OR (a.direction = 'yes' AND a.workflow_id IN (419,437,438,436)))
			AND a.foreign_id IN (SELECT id FROM jobs WHERE status = '1')
			group by a.foreign_id
			";
			$jobs_right	= $database->getAll($get_job_to_right);
			
			$common_jobs = array();
			foreach($jobs_right as $job){
				$get_job_to_left ="
					SELECT a.foreign_id
					FROM project_workflow a
					WHERE a.table = 'jobs' AND a.workflow_id IN (39,377,423,434)
					AND a.foreign_id=".$job['foreign_id']."
					";
				$the_job	= $database->getAll($get_job_to_left);
					
				if(!$the_job){
					$common_jobs[]=$job['foreign_id'];
				}
			}
			return $common_jobs;
		}
	}

?>