<?
class list_jobs_updates 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->start_date = $context['start_date'];
		$this->end_date = $context['end_date'];

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

		$start_date = $this->start_date;			
		$end_date = $this->end_date;			

		$start_date = dates::change_to_sql($start_date)." 00:00:00";
		$end_date= dates::change_to_sql($end_date)." 23:59:59";						
		
		$start_date_unix = dates::to_unix($start_date." 00:00:00");
		$end_date_unix = dates::to_unix($end_date." 23:59:59");

		$name = common_users::get_full_name($user_id);
							
		 $get_timesheets = "
		SELECT id,start_time,end_time,job_id
		FROM job_trackings
		WHERE start_time >= '$start_date 00:00:00' AND start_time <= '$end_date 23:59:59' AND end_time = '0000-00-00 00:00:00' AND status = '1'
		";
		$results_timesheets = $database->getAll($get_timesheets);
								
		$accum_jt = array();
		
		if(count($results_timesheets) > 0)
		{
			foreach($results_timesheets AS $results_timesheet)
			{
				$accum_jt[$results_timesheet['id']] = $results_timesheet;
				$accum_jt[$results_timesheet['id']]['end_time'] = date('Y-m-d G:i:s');
			}
		}
			
		/*get timesheets with start time*/
		 $get_timesheets = "
		SELECT id,start_time,end_time,job_id
		FROM job_trackings
		WHERE start_time >= '$start_date 00:00:00' AND start_time <= '$end_date 23:59:59' AND status = '1'
		";
		$results_timesheets = $database->getAll($get_timesheets);
									
		if(count($results_timesheets) > 0)
		{
			foreach($results_timesheets AS $results_timesheet)
			{
				$accum_jt[$results_timesheet['id']] = $results_timesheet;
			}
		}

		/*get timesheets with end time*/
		$get_timesheets = "
		SELECT id,start_time,end_time,job_id
		FROM job_trackings
		WHERE end_time >= '$start_date 00:00:00' AND end_time <= '$end_date 23:59:59' AND status = '1'
		";
		$results_timesheets = $database->getAll($get_timesheets);
									
		if(count($results_timesheets) > 0)
		{
			foreach($results_timesheets AS $results_timesheet)
			{
				$accum_jt[$results_timesheet['id']] = $results_timesheet;
			}
		}
				 				
		$this->total_duration = 0;
		//bug::bug_array('', $accum_jt);
		$date_arr = array();
		if(count($accum_jt))
		{
			foreach($accum_jt AS $key => $jt)
			{
				$id = $jt['id'];
				$job_id = $jt['job_id'];

				$start_time = $jt['start_time'];
				$end_time = $jt['end_time'];	
				
				$split_start_time = explode(" ",$start_time);
				$split_end_time = explode(" ",$end_time);
				
				$this_start_date = $split_start_time[0];
				$this_end_date = $split_end_time[0];			
									
				$this_start_time_unix = dates::to_unix($start_time);
				$this_end_time_unix = dates::to_unix($end_time);
				 
				if(($this_start_time_unix >= $start_date_unix) && ($this_end_time_unix <= $end_date_unix))
				{
					$duration = aging::get_time_by_hour($start_time,$end_time);						
					
					$this->total_duration += $duration;
						
					$return_results[$id] = array(
						"id" => $id,
						"job_id" => $job_id,
						"duration" => $duration							
					);		
					
					
				}
				else
				{
					if(($this_start_time_unix < $start_date_unix) && ($this_end_time_unix > $start_date_unix))			
					{
						$duration = aging::get_time_by_hour($start_date." 00:00:00",$end_time);											

						$this->total_duration += $duration;

						$return_results[$id] = array(
							"id" => $id,
							"job_id" => $job_id,
							"duration" => $duration							
						);											
					}
					else if(($this_start_time_unix >= $start_date_unix) && ($this_end_time_unix > $end_date_unix))			
					{														
						$duration = aging::get_time_by_hour($start_time,$end_date." 23:59:59");	

						$this->total_duration += $duration;

						$return_results[$id] = array(
							"id" => $id,
							"job_id" => $job_id,
							"duration" => $duration							
						);																										
					}
				}			
			}
		}				
		//bug::bug_array('',$return_results);
		$jobs = array();
		
		if(count($return_results) > 0)
			foreach($return_results AS $return_result)
				$jobs[$return_result['job_id']]['labours'] = 0;
				
		if(count($return_results) > 0)
			foreach($return_results AS $return_result)
				$jobs[$return_result['job_id']]['labours'] += $return_result['duration'];								

			//bug::bug_array('',$jobs);
		
		$get_materials = "
		SELECT quotes_materials.quote_id, quotes_materials.materials, quotes_materials.actual_used,jobs.id AS job_id
		FROM quotes_materials,jobs
		WHERE 
			quotes_materials.quote_id = jobs.quote_id AND
			quotes_materials.date_submitted >= '$start_date 00:00:00' AND 
			quotes_materials.date_submitted <= '$end_date 23:59:59' AND 			
			quotes_materials.actual_used != ''
		";	

		/*Kai 23-10-2012 modified*/
		 $get_materials = "
		SELECT 	logs_notes.foreign_id,logs_notes.description AS quantity,
				quotes_materials.item,quotes_materials.materials AS material_name,
				quotes_materials.price AS cost, logs_notes.date_submitted,
				quotes_materials.quote_id,quotes_materials.price*quotes_materials.actual_used AS total
		FROM 	logs_notes,quotes_materials
		WHERE
			logs_notes.foreign_id = quotes_materials.id AND
			logs_notes.date_submitted >= '$start_date 00:00:00' AND 
			logs_notes.date_submitted <= '$end_date 23:59:59' AND 	
			logs_notes.table_name = 'quotes_materials' AND 
			logs_notes.description REGEXP '^-?[.0-9]+$'
		";	
		$results_materials = $database->getAll($get_materials);		
				
		$materials = array();
		
		if(count($results_materials) > 0)
			foreach($results_materials AS $results_material)	
				$materials[$results_material['quote_id']][] = $results_material;
		
		$ids_sql = "";
		
		$job_materials = array();
		
		if(count($materials) > 0)
		{
			foreach($materials AS $key => $material)	
				$ids_sql .= "'".$key."',";							

			$ids_sql = "(".substr($ids_sql,0, strlen($ids_sql) - 1).")";				
	
			$get_request = "
			SELECT id,quote_id,job_name
			FROM jobs
			WHERE quote_id IN $ids_sql
			";
			$results = $database->getAll($get_request);				
			
			foreach($results AS $result)
			{
				$job_materials[$result['id']] = $materials[$result['quote_id']];				
			}					
		}
						
		if(count($job_materials) > 0)
		{
			foreach($job_materials AS $key => $job_material)
			{						
				if(count($job_material) > 0)
				{
					foreach($job_material AS $value)
					{
						$jobs[$key]['materials'][$value['foreign_id']]['material_name'] = $value['material_name'];
						$jobs[$key]['materials'][$value['foreign_id']]['item'] = $value['item'];
						$jobs[$key]['materials'][$value['foreign_id']]['cost'] = $value['cost'];
						$jobs[$key]['materials'][$value['foreign_id']]['date'] = date("d/m/Y",strtotime($value['date_submitted']));
						
						if(isset($jobs[$key]['materials'][$value['foreign_id']]['quantity']))
							$jobs[$key]['materials'][$value['foreign_id']]['quantity'] += $value['quantity'];						
						else
							$jobs[$key]['materials'][$value['foreign_id']]['quantity'] = $value['quantity'];

						/** Kai 07-11-2012 added*/
						if(isset($jobs[$key]['materials'][$value['foreign_id']]['total']))
							$jobs[$key]['materials'][$value['foreign_id']]['total'] = $value['total'];
						else
							$jobs[$key]['materials'][$value['foreign_id']]['total'] = $value['total'];
					}
				}
			}
		}

		$return_results = array();
		
		$ids_sql = "";
		
		$this->total_labours = 0;
		
		if(count($jobs) > 0)
		{
			foreach($jobs AS $key => $value)
				$ids_sql .= "'".$key."',";
				
			$ids_sql = "(".substr($ids_sql,0, strlen($ids_sql) - 1).")";
			
			  $get_request = "
			SELECT id,quote_id,job_name
			FROM $db_name"."jobs
			WHERE id IN $ids_sql
			";
			$results = $database->getAll($get_request);				
			$k= 0;
			$test_flag= false;
			
			//replace this with the one below to show child
			
			/* foreach($results AS $result)
			{
				$flag_child[$k]=$result['quote_id'];
				 
				if($k > 0 ){
				
					if( $flag_child[$k] !=  $flag_child[$k-1]){
						$jobs[$result['id']]['id'] = $result['id'];
						$jobs[$result['id']]['quote_id'] = $result['quote_id'];
						$jobs[$result['id']]['job_name'] = $result['job_name'];			
					
				
				
				if(!isset($jobs[$result['id']]['labours']))	
					$jobs[$result['id']]['labours'] = "-";
					
				if(!isset($jobs[$result['id']]['materials']))	
					$jobs[$result['id']]['current_materials'] = "-";
				else
				{
					 // 30-04-2012
					if($k >0 )
					{
						if($flag_child[$k] == $flag_child[$k-1])
						{
							$test_flag = true;
						}
						else
						{
							$test_flag = false;
						}
					}
					
					if($test_flag) 
					{
						$jobs[$result['id']]['current_materials'] = "-";
						
					}
					else
					{ // 30-04-2012 
						$count_material = 1;
						$num_materials = count($result['materials']);
					
						foreach($jobs[$result['id']]['materials'] AS $job_material)
						{
							if($num_materials == $count_material)
								$jobs[$result['id']]['current_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity'];
							else
								$jobs[$result['id']]['current_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity']."<br />";					

							//Kai 23-10-2012 added
							if($num_materials == $count_material)
								$jobs[$result['id']]['total'] .= " $".$job_material['total'];
							else
								$jobs[$result['id']]['total'] .= " $".$job_material['total']."<br />";
							
							$count_material++;
						}
					 }		// 30-04-2012		
				}
					
					}else{
						continue;
					}
				}else{
				
				$jobs[$result['id']]['id'] = $result['id'];
				$jobs[$result['id']]['quote_id'] = $result['quote_id'];
				$jobs[$result['id']]['job_name'] = $result['job_name'];			
				
				
				
				if(!isset($jobs[$result['id']]['labours']))	
					$jobs[$result['id']]['labours'] = "-";
					
				if(!isset($jobs[$result['id']]['materials']))	
					$jobs[$result['id']]['current_materials'] = "-";
				else
				{
					 // 30-04-2012
					if($k >0 )
					{
						if($flag_child[$k] == $flag_child[$k-1])
						{
							$test_flag = true;
						}
						else
						{
							$test_flag = false;
						}
					}
					
					if($test_flag) 
					{
						$jobs[$result['id']]['current_materials'] = "-";
						
					}
					else
					{ // 30-04-2012 
						$count_material = 1;
						$num_materials = count($result['materials']);
					
						foreach($jobs[$result['id']]['materials'] AS $job_material)
						{
							//Kai 23-10-2012 modified
							if($num_materials == $count_material)
								$jobs[$result['id']]['current_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity'];
							else
								$jobs[$result['id']]['current_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity']."<br />";

							//Kai 23-10-2012 added
							if($num_materials == $count_material)
								$jobs[$result['id']]['total'] .= " $".$job_material['total'];
							else
								$jobs[$result['id']]['total'] .= " $".$job_material['total']."<br />";
														
							$count_material++;
						}
					 }		// 30-04-2012		
				}
				
				 }
				  $k++;	// 30-04-2012
			}*/			
 
			
			
			//RONALD 21-08-2012
			 
			/** Child Job Material Part*/
			foreach($results AS $result)
			{
				//check if parent id
				$id = $result['id'];
				$sql = "SELECT parent_job_id FROM jobs WHERE id='$id'";
				 
				$isparent = $database->getOne($sql);
				
				if($jobs[$result['id']]['labours']!="" || ($jobs[$result['id']]['labours']=="" && $isparent==0) )
				{
					$jobs[$result['id']]['id'] = $result['id'];
					$jobs[$result['id']]['quote_id'] = $result['quote_id'];
					$jobs[$result['id']]['job_name'] = $result['job_name'];	
					if(!isset($jobs[$result['id']]['labours']))	
						$jobs[$result['id']]['labours'] = "-";
				
					if(!isset($jobs[$result['id']]['materials'])){	
						$jobs[$result['id']]['current_materials'] = "-";
						$jobs[$result['id']]['csv_materials'] = "-";
					}
				
					$count_material = 1;
					$num_materials = count($result['materials']);
					if($isparent ==0){
						if(!empty($jobs[$result['id']]['materials'])){
							foreach($jobs[$result['id']]['materials'] AS $job_material)
							{
								if($num_materials == $count_material){
									$jobs[$result['id']]['current_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity'];
									$jobs[$result['id']]['csv_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity'];
								}else{
									$jobs[$result['id']]['current_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity']."<br />";					
									$jobs[$result['id']]['csv_materials'] .= $job_material['item'].' - '.$job_material['material_name']." x ".$job_material['quantity']."\n";					
								}							
								$count_material++;
							}
						}
					}
				 }
			}
			
			
			
			// --end RONALD
			foreach($jobs AS $job){
				$return_results[] = $job;	 	
			}
		}				
		$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;		 

		?><h2>Number of Jobs : 0</h2><?
		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($results[$key]['quote_id']==''){
						continue;
						}else{
					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 list_footer()
	 {
	 	$avail_status = $this->avail_status;
	 	$avail_edit = $this->avail_edit;
	 	$columns = $this->columns;			 
		
		$num_colums = count($columns) - 1;
		$num_colums_total = count($columns) - 2;
		
		?>
		<tfoot>
		<tr>
		<?
		if($avail_status)
		{
			?><th>&nbsp;</th><?
		}
						
		if($avail_edit)
		{					
			?><th>&nbsp;</th><?
		}
		
		
		foreach($columns AS $key => $value)
		{		
			if($key == "labours")
			{
				?><th>