<?
class list_report_quality_time extends listing
{
	public $status;
	public $order;
	public $method;
	
	public $group_id;
	public $department_id;
	public $table;

	public $avail_status;
	public $avail_edit;

	public $link_view;
	public $link_edit;
		
	public $db_name;
	public $account_id;
	 
			
	public function __construct($context)
	{					
		parent::set_data($context);							
				
		$this->status = $context['status'];
		$this->start_date = $context['start_date'];
		$this->end_date = $context['end_date'];

		$this->method = (empty($context['method'])) ? "id" : $context['method'];													
	}
	
	public function get_data()
	{
		global $database;

		$status = $this->status;			
				
		if($status == 1)		
			$results = common_jobs::get_jobs($status);		
		else
		{
			$start_date = $this->start_date;		
			$end_date = $this->end_date;	

			$status_sql = (empty($this->status)) ? "" : "AND jobs.status = '".$this->status."'";
		
			$date_coltype = (empty($status_sql)) ? "jobs.date_created" : "jobs.date_submitted";
					
			if(empty($start_date) && empty($end_date))
				$date_sql = "";
			else if(!empty($start_date) && empty($end_date))
				$date_sql = "AND $date_coltype >= '$start_date 00:00:00'";
			else if(empty($start_date) && !empty($end_date))
				$date_sql = "AND $date_coltype <= '$end_date 23:59:59'";
			else if(!empty($start_date) && !empty($end_date))
				$date_sql = "AND $date_coltype >= '$start_date 00:00:00' AND $date_coltype <= '$end_date 23:59:59'";
							
			$get_jobs = "
			SELECT *
			FROM jobs
			WHERE 1 = 1 $date_sql $status_sql
			";			
			$results = $database->getAll($get_jobs);				
		}
		
		$users = common_users::get_all_names_link();
		$locations = common_location::get_locations();
		$quote_types = common_quotes::get_all_quotes();

		$job_type[0] = "Single Site";
		$job_type[1] = "Single Site";
		$job_type[2] = "Multi Site";
		$job_type[3] = "LED Media";		
		$job_type[4] = "Cost Plus";		
		$job_type[5] = "Quick Job";
		
		$return_results = array();
				
		if(count($results) > 0)										
		{
			foreach($results AS $result)					
			{															 						 			 																																						
				$quote_details = common_quotes::get_details($result['quote_id']);
				
				$result['job_type'] = $job_type[$quote_details['job_type']];
				
				if(($quote_details['job_type'] == 1) || ($quote_details['job_type'] == 3))
				{
					$visual_art_start = 2;
					$visual_art_end	 = 24;//28;
					$quote_start = 24;
					$quote_end = 7;/*task id*/
					$quote_acc_start = 7;/*task id*/
					$quote_acc_end = 6;
					$council_art_start = 6;
					$coucil_art_end = 35;/*task id*/
					
					$prod_start = 35;/*task id*/
					$prod_end = 39;
					
					$install_start = 39;
					$install_end = "48,50";/*task id*/
					
					$invoice_start = "48,50";/*task id*/
					$invoice_end = 54;/*task id*/
					
					$pre_start_id = 8;
                    $pre_end_id = 35;                   
                    $post_end_id = 38;                  
                    $install_end_id = 41;       															
				}
				else if($quote_details['job_type'] == 2)
				{
					$visual_art_start = 359;
					$visual_art_end	 = 363;//366;
					$quote_start = 363;
					$quote_end = 574;
					$quote_acc_start = 574;
					$quote_acc_end = 360;
					$council_art_start = 360;
					$council_art_end = 593;
					
					$prod_start = 593;/*task id*/
					$prod_end = 377;
					
					$install_start = 377;
					$install_end = "608,610";/*task id*/
					
					$invoice_start = "608,610";/*task id*/
					$invoice_end = 614;/*task id*/
					
					$pre_start_id = 361;
                    $pre_end_id = 373;                  
                    $post_end_id = 376;                 
                    $install_end_id = 379; 																			
				}
				else if($quote_details['job_type'] == 4)
				{
					$visual_art_start = 405;
					$visual_art_end	 = 405;//412;
					$quote_start = 405;
					$quote_end = 674;
					$quote_acc_start = 674;
					$quote_acc_end = 406;
					$council_art_start = 406;
					$council_art_end = 698;
					
					$prod_start = 698;/*task id*/
					$prod_end = 423;
					
					$install_start = 423;
					$install_end = "713,715";/*task id*/
					
					$invoice_start = "713,715";/*task id*/
					$invoice_end = 719;/*task id*/
					
					$pre_start_id = 407;
                    $pre_end_id = 419;                  
                    $post_end_id = 422;                 
                    $install_end_id = 425;  
				}
				else if($quote_details['job_type'] == 5)
				{
					$visual_art_start = 432;
					$visual_art_end	 = 432;//433;
					$quote_start = 432;
					$quote_end = 760;
					$quote_acc_start = 760;
					$quote_acc_end = 433;
					$council_art_start = 433;
					$council_art_end = 767;
					
					$prod_start = 767;/*task id*/
					$prod_end = 767;
					
					$install_start = 767;
					$install_end = 764;/*task id*/
					
					$invoice_start = 764;/*task id*/
					$invoice_end = 748;/*task id*/
					
					$pre_start_id = 433;
					$pre_end_id = 419;
					$post_end_id = 422;
					$install_end_id = 425;
				}				
				
				/********** visual art ***********/
				$get_art_start = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$visual_art_start' AND foreign_id = '".$result['quote_id']."'
				ORDER BY date_submitted DESC
				";
				$visual_art_start_time = $database->getOne($get_art_start);
				
				$get_art_end = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$visual_art_end' AND foreign_id = '".$result['quote_id']."'
				ORDER BY date_submitted DESC
				";
				$visual_art_end_time = $database->getOne($get_art_end);

				/********** quote ***********/
				$get_quote_start = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$quote_start' AND foreign_id = '".$result['quote_id']."'
				ORDER BY date_submitted DESC
				";
				$quote_start_time = $database->getOne($get_quote_start);
				
				$get_quote_end = "
				SELECT project_tasks.date_submitted
				FROM project_workflow, project_tasks
				WHERE project_tasks.workflow_task_id = '$quote_end' AND project_workflow.foreign_id = '".$result['quote_id']."' AND project_workflow.id = project_tasks.project_workflow_id
				ORDER BY project_tasks.date_submitted DESC
				";
				$quote_end_time = $database->getOne($get_quote_end);
				
				/********** quote acceptance***********/
				$get_quote_acc_start = "
				SELECT project_tasks.date_submitted
				FROM project_workflow, project_tasks
				WHERE project_tasks.workflow_task_id = '$quote_acc_start' AND project_workflow.foreign_id = '".$result['quote_id']."' AND project_workflow.id = project_tasks.project_workflow_id
				ORDER BY project_tasks.date_submitted DESC
				";
				$quote_acc_start_time = $database->getOne($get_quote_acc_start);
				
				$get_quote_acc_end = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$quote_acc_end' AND foreign_id = '".$result['quote_id']."'
				ORDER BY date_submitted DESC
				";
				$quote_acc_end_time = $database->getOne($get_quote_acc_end);
				
				/********** council art ***********/
				$get_council_start = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$council_art_start' AND foreign_id = '".$result['quote_id']."'
				ORDER BY date_submitted DESC
				";
				$council_start_time = $database->getOne($get_council_start);
				
				$get_council_end = "
				SELECT project_tasks.date_submitted
				FROM project_workflow, project_tasks
				WHERE project_tasks.workflow_task_id = '$council_art_end' AND project_workflow.foreign_id = '".$result['id']."' AND project_workflow.id = project_tasks.project_workflow_id
				ORDER BY project_tasks.date_submitted DESC
				";
				$council_end_time = $database->getOne($get_council_end);
				
				/********** production***********/
				$get_production_start = "
				SELECT project_tasks.date_submitted
				FROM project_workflow, project_tasks
				WHERE project_tasks.workflow_task_id = '$prod_start' AND project_workflow.foreign_id = '".$result['id']."' AND project_workflow.id = project_tasks.project_workflow_id
				ORDER BY project_tasks.date_submitted DESC
				";
				$prod_start_time = $database->getOne($get_production_start);
				
				$get_production_end = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$prod_end' AND foreign_id = '".$result['id']."'
				ORDER BY date_submitted DESC
				";
				$prod_end_time = $database->getOne($get_production_end);
				
				/********** installation ***********/
				$get_install_start = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$install_start' AND foreign_id = '".$result['id']."'
				ORDER BY date_submitted DESC
				";
				$install_start_time = $database->getOne($get_install_start);
				
				$get_install_end = "
				SELECT project_tasks.date_submitted
				FROM project_workflow, project_tasks
				WHERE project_tasks.workflow_task_id IN ($install_end) AND project_workflow.foreign_id = '".$result['id']."' AND project_workflow.id = project_tasks.project_workflow_id
				ORDER BY project_tasks.date_submitted DESC
				";
				$install_end_time = $database->getOne($get_install_end);
				
				/********** invoice ***********/
				$get_invoice_start = "
				SELECT project_tasks.date_submitted
				FROM project_workflow, project_tasks
				WHERE project_tasks.workflow_task_id IN ($invoice_start) AND project_workflow.foreign_id = '".$result['id']."' AND project_workflow.id = project_tasks.project_workflow_id
				ORDER BY project_tasks.date_submitted DESC
				";
				$invoice_start_time = $database->getOne($get_invoice_start);
				
				$get_invoice_end = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$install_end' AND foreign_id = '".$result['id']."'
				ORDER BY date_submitted DESC
				";
				$invoice_end_time = $database->getOne($get_invoice_end);
				

				
				$result['visual_art'] = aging::get_time($visual_art_start_time,$visual_art_end_time);
				$result['quote'] = aging::get_time($quote_start_time,$quote_end_time);
				$result['quote_acceptance'] = aging::get_time($quote_acc_start_time,$quote_acc_end_time);
				//$result['council_art'] = aging::get_time($council_start_time,$council_end_time);
				$result['production'] = aging::get_time($prod_start_time,$prod_end_time);
				$result['installation'] = aging::get_time($install_start_time,$install_end_time);
				$result['invoicing'] = aging::get_time($invoice_start_time,$invoice_end_time);
				$result['total'] = decimal_fix::fix($result['visual_art'] + $result['quote'] + $result['quote_acceptance']+$result['production']+$result['installation']+$result['invoicing']);
				
				/********** old one***********/
				$get_pre_start = "
				 SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$pre_start_id' AND foreign_id = '".$result['quote_id']."' AND `table` = 'quotes' AND direction = 'yes'
				ORDER BY date_submitted DESC
				";
				$pre_start_time = $database->getOne($get_pre_start);
				
				$get_pre_start = "
				SELECT date_submitted
				FROM project_workflow
				WHERE workflow_id = '$pre_end_id' AND foreign_id = '".$result['id']."' AND `table` = 'jobs' AND direction = 'yes'
				ORDER BY date_submitted DESC
				";
				$pre_end_time = $database->getOne($get_pre_start);
				
// 				$get_post_end = "
// 				SELECT date_submitted
// 				FROM project_workflow
// 				WHERE workflow_id = '$post_end_id' AND foreign_id = '".$result['id']."' AND `table` = 'jobs' AND direction = 'yes'
// 				ORDER BY date_submitted DESC
// 				";
// 				$prod_end_time = $database->getOne($get_post_end);
				
// 				$get_install_end = "
// 				SELECT date_submitted
// 				FROM project_workflow
// 				WHERE workflow_id = '$install_end_id' AND foreign_id = '".$result['id']."' AND `table` = 'jobs' AND direction = 'yes'
// 				ORDER BY date_submitted DESC
// 				";
// 				$install_end_time = $database->getOne($get_install_end);
// 				/********** old one ends***********/
 				$result['pre_production'] = aging::get_time($pre_start_time,$pre_end_time);
// 				$result['production'] = aging::get_time($pre_end_time,$prod_end_time);
// 				$result['installation'] = aging::get_time($prod_end_time,$install_end_time);
				
// 				$result['total'] = decimal_fix::fix($result['apre_production']+$result['aproduction']+$result['ainstallation']);
				
				
				$return_results[] = $result;
			}	
		}

		$this->final_results = $return_results;

		self::prepare();
		               		
		return $this;				
	}	

	 public function list_body()
	 {
		$results = $this->final_results;
		
		$avail_status = $this->avail_status;
		$avail_edit = $this->avail_edit;

		$link_view = $this->link_view;							
		$link_edit = $this->link_edit;			

		$columns = $this->columns;			
		$sortable = $this->sortable;			

		$pagination = $this->pagination;	
		$start = $this->start;	
		$size = $this->size;	
		$total_results = $this->total_results;	
		$max_size = $this->max_size;	

		$normalise_get = $this->normalise_get;
				
		$new_normalised_results = $this->normalised_results;	
			 	
		$count = 0;		 
		 								
		if(count($results) > 0)
		{	
			?><tbody><?
										
			for($i = $start; $i < $max_size; $i++)
			{	
				$key = $new_normalised_results[$i];
								
				$mod = $count%2;
				$class = ($mod == 0) ? "odd" : "even";
				$count++;						
				
				$results[$key]['date_submitted'] = dates::change_date_time($results[$key]['date_submitted']);
				$results[$key]['date_created'] = dates::change_date_time($results[$key]['date_created']);
								
				?><tr id="row_" class="" ><?

				if($avail_status)
				{
					?><td><input id="checkbox_" class="input_clickable" type="checkbox" name="chosen[]" value="" /></td><?				
				}
								
				if($avail_edit)
				{					
					?><td><a href="?page=&id="><img src="icons/edit_small.png" border="0" alt="edit" /></a></td><?                		
				}

				foreach($columns AS $key_col => $value)
				{									
					if($value['view'] == true)
					{
						?><td><a href="?page=&id="></a></td><?
					}
					else if($key_col == "quote_id")
					{
						?><td><a href="?page=quote_details&id="></a></td><?					
					}
					else
					{
						?><td></td><?					
					}
				}	
					
				?>		
				</tr>
				<?	
			}
			
			?></tbody><?
			
			$this->list_footer();			
		}			
			 
		return $this;	 
	 }		

		
	public function get_data_all()
	{		
		global $database;
		
		$results =common_jobs::get_all_active_jobs_for_tablet();
	
		$count = 0;

		$return_results = array();

		foreach($results AS $result)
		{
			$id = $result['id'];
			$job_id = $result['job_id'];
			$client_id = $result['client_id'];
			$client_name = common_clients::get_client_name($client_id);
			$job_name = $result['job_name'];
			$job_number = $result['job_number'];
			$description = $result['description'];
			$location_id = $result['location_id'];
			$stage_id = $result['stage_id'];
			$depts = explode(':',$result['department_id']);
			$dept_id = $result['department_id'];
			  
			$status = explode(':',$result['status']);
			$cnt=  count($status);
			$dept_array=array_map(null,$depts,$status);
			 
			$submitter = $result['submitter'];
			$date_submitted = $result['date_submitted'];

			$return_results[] = array(
				"id" => $id,
				"client_id" => $client_id,
				"client_name" => $client_name, 
				"department_id" => $department_id,
				"dept_id" => $dept_id, 
				"job_id" => $job_id ,
				"job_name" => $job_name, 
				"job_number" => $job_number, 
				"description" => $description, 
				"location_id" =>  $location_id, 
				"company_name" => $company_name, 
				"department_name" => $department_name, 
				"stage_name" => $stage_name, 
				"status" => $status,
				"status1" => $status1, 
				"status_name" => $status_name,  
				"status_name1" => $status_name1, 
				"submitter" => $submitter,
				"date_submitted" => $date_submitted
				);
		}

		$this->final_results = $return_results;
		        		
		return $this;
	}
	
				
	public function show()
    {		
		global $database;
	
		$order = $this->order;
		$method = $this->method;	
		
		$results = $this->final_results;

		$avail_status = $this->avail_status;
		$avail_edit = $this->avail_edit;

		$link_view = $this->link_view;							
		$link_edit = $this->link_edit;	
		
		$normalised_results = filter::sort_results($method,$order,$results);						
		$normalise_get = filter::normalise_get($order);								
							
		$pagination_results = filter::pagination($normalised_results,$normalise_get);

		$pagination_results['show_pagination'] = $this->show_pagination;
		$pagination_results['show_search'] = $this->show_search;
				
		filter::pagination_html($pagination_results);
		

		
		$pagination = $pagination_results['pagination'];
		$start = $pagination_results['start'];
		$size = $pagination_results['size'];
		$total_results = $pagination_results['total_results'];
		$max_size = $pagination_results['max_size'];
								
		$order = ($order == "ASC") ? "DESC" : "ASC";					
		$normalise_get = filter::normalise_get($order);		
		
		$new_normalised_results = array();		
		
		foreach($normalised_results AS $key => $value)					
			$new_normalised_results[] = $key;					
												
        ?>
		<script>
		 $(document).ready(function() {		 
			 $("#department").change(function(){		 
				 var dept_id = $(this).val();
				 window.location = "?page=jobs&department_id="+dept_id;
			 });		 
		 });		 				
		</script>
        
		<table id="the-table" class="table_list" cellpadding="0" cellspacing="0">
       
		<thead>
		
        <?		
		if($avail_status)
		{
			?>
			<th class="icon"><input type="checkbox" name="all_check_box" id="all_check_box" /></th>            	        
			<?            		
		}
		
		if($avail_edit)
		{
			?>	
	        <th class="icon">Edit</th>                           
			<?
		}
		?>
        <th><a href="&method=job_number">Job #</a></th>        
        <th><a href="&method=company_name">Client</a></th>        
        <th class="description"><a href="&method=job_name">Job Name</a></th>        
        <th><a href="&method=stage_id">Stage</a></th>        
        <th><a href="&method=department_id">Department</a></th>        
        <th><a href="&method=status_name">Status</a></th>                 
        <th class="submitter"><a href="&method=submitter">Submitter</a></th>
        <th class="date_submitted"><a href="&method=date_submitted">Last Modified</a></th>                                                				
        </thead>
		<tbody>
		<?
		
		$count = 0;
												
		if(count($results) > 0)
		{	
			?><tbody><?
										
			for($i = $start; $i < $max_size; $i++)
			{	
				$key = $new_normalised_results[$i];
				
				$mod = $count%2;
				$class = ($mod == 0) ? "odd" : "even";
				$count++;
						
				$id = $results[$key]['id'];
				$job_id = $results[$key]['job_id'];
				$job_name = $results[$key]['job_name'];
				$job_number = $results[$key]['job_number'];
				$department_name = $results[$key]['department_name'];
				$company_name = $results[$key]['company_name'];
				$stage_name = $results[$key]['stage_name'];
				$status_name = $results[$key]['status_name'];
				$status_name1 = $results[$key]['status_name1'];
				$status  = $results[$key]['status'];
				$status1  = $results[$key]['status1'];
				$parent_id = $results[$key]['parent_id'];
				$department_id = $results[$key]['department_id'];
				$dept_id = $results[$key]['dept_id'];
				 
				$submitter = $results[$key]['submitter'];
				$date_submitted = $results[$key]['date_submitted'];
				
				if($status_name1 != ""){
				
				$status_name .= ','.$status_name1;
				$status  .= ','.$status1;
				
				}
				?><tr id="row_" class=""><?

				if($avail_status)
				{
					?><td><input id="checkbox_" class="input_clickable" type="checkbox" name="chosen[]" value="" /></td><?				
				}
								
				if($avail_edit)
				{					
					?>
					<td><a href="?page=&id="><img src="icons/edit_small.png" border="0" alt="edit" /></a></td>
					<?                		
				}
	
				?>
                <td><a href="?page=&id="></a></td>
                <td></td>
                <td><div class="description_list"></div></td>
                <td></td>
                <td></td>
                <td></td>
				 
				 
				<td></td>
				<td></td>					
				</tr>
				<?	
			}
			
			?>
			</tbody>
			<tfoot>
			<tr>
            <?
			if($avail_status)
			{
				?><th>&nbsp;</th><?
			}
							
			if($avail_edit)
			{					
				?><th>&nbsp;</th><?
			}			
			?>
			<th>&nbsp;</th>
			<th>&nbsp;</th>
			<th>&nbsp;</th>
			<th>&nbsp;</th>
            <th>&nbsp;</th>
			<th>&nbsp;</th>
			<th>&nbsp;</th> 
            <th>&nbsp;</th>       
			</tr>
			</tfoot>         
			<?
		}
		?>
		</table>
		<?			
	 }
	 
	 public function show_job_tracking_tablet()
	 {
		global $database;
	
		$order = $this->order;
		$method = $this->method;	
		
		$results = $this->final_results;

		$avail_status = $this->avail_status;
		$avail_edit = $this->avail_edit;

		$link_view = $this->link_view;							
		$link_edit = $this->link_edit;	

		$normalised_results = filter::sort_results($method,$order,$results);						
		$normalise_get = filter::normalise_get($order);								
					
		$pagination_results = filter::pagination($normalised_results,$normalise_get);
		//filter::pagination_html($pagination_results);
		
		//$pagination = $pagination_results['pagination'];
		$start = $pagination_results['start'];
		$size = $pagination_results['size'];
		$total_results = $pagination_results['total_results'];
		$max_size = $pagination_results['max_size'];
								
		$order = ($order == "ASC") ? "DESC" : "ASC";					
		$normalise_get = filter::normalise_get($order);		
		
		$new_normalised_results = array();		
		
		foreach($normalised_results AS $key => $value)					
			$new_normalised_results[] = $key;									
        ?>
		<div style="margin-right:10px;">
		<table class="table_list" cellpadding="0" cellspacing="0">
       
		<thead>
        <!--<th class="date"><a href="&method=job_number">Job ID</a></th>-->
        <th class="date">Job ID</th>
        <th class="name">Client</th>
        <th style="width:65%;">Description</th>
<!--        <th><a href="&method=stage_id">Stage</a></th>
        <th><a href="&method=department_id">Department</a></th>
        <th><a href="&method=status_name">Status</a></th>  -->
         <!--
        <th class="submitter"><a href="&method=submitter">Submitter</a></th>
        <th class="date_submitted"><a href="&method=date_submitted">Last Modified</a></th>  -->
        </thead>
		<tbody>
		<?
		
		$count = 0;
												
		if(count($results) > 0)
		{
			?><tbody><?
										
			for($i = $start; $i < $max_size; $i++)
			{	
				$key = $new_normalised_results[$i];
				
				$mod = $count%2;
				$class = ($mod == 0) ? "odd" : "even";
				$count++;
						
				$id = $results[$key]['id'];
				$client_id = $results[$key]['client_id'];
				$client_name = $results[$key]['client_name'];
				$job_id = $results[$key]['job_id'];
				$job_name = $results[$key]['job_name'];
				$job_number = $results[$key]['job_number'];
				$department_name = $results[$key]['department_name'];
				$company_name = $results[$key]['company_name'];
				$stage_name = $results[$key]['stage_name'];
				$status_name = $results[$key]['status_name'];
				$status  = $results[$key]['status'];
				$parent_id = $results[$key]['parent_id'];
				$department_id = $results[$key]['department_id'];
				$description = $results[$key]['description'];
				 
				$submitter = $results[$key]['submitter'];
				$date_submitted = $results[$key]['date_submitted'];
	
				?>
                <tr id="row_ " class=" click_tracking">
                    <!--<td class="time"></td>-->
                    <td ><!--<a href="?page=&id=">--><!--</a>--></td>
                    <td ></td>
                    <td><div class="description_list_tablet"></div></td>		
				</tr>
				<?	
			}
			
			?>
			</tbody>
			<tfoot>
			<tr>
            <!--th>&nbsp;</th>-->
			<th>&nbsp;</th>
			<th>&nbsp;</th>
			<th>&nbsp;</th>
           
			</tr>
			</tfoot>         
			<?
		}
		?>
		</table>
        </div>
		<?			
	 }

}