<?
class list_report_nonbillable 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->order = (empty($context['list_config']['order'])) ? "ASC" : $context['list_config']['order'];																			
		$this->method = (empty($context['list_config']['method'])) ? "name" : $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);
		$end_date= dates::change_to_sql($end_date);
		
		$start_date_unix = dates::to_unix($start_date." 00:00:00");
		$end_date_unix = dates::to_unix($end_date." 23:59:59");

		$user_names = common_users::get_all_names_link();
				//DB_CLIENT_SETTINGS				
		$get_users = "
		SELECT a.id,concat(a.first_name,' ',a.last_name),c.department_name
		FROM ".DB_CLIENT_ACCOUNTS."users a
		INNER JOIN metro_client.departments_users b ON b.user_id = a.id
		INNER JOIN metro_client.departments c ON c.id = b.department_id
		INNER JOIN ".DB_CLIENT_SETTINGS."groups d ON d.id = a.group_id
		WHERE a.status ='1'
		AND c.has_productivity = 1 AND c.status = 1
		AND (d.id = '35' OR d.id = '39')
		GROUP BY a.id
		";
		$users = $database->getAll($get_users);
		
		$this->total_timesheets = 0;
		$this->total_jobsheets = 0;
				
		if(count($users) > 0)
		{
			foreach($users AS $user)
			{							
				$user_id = $user['id'];
				$name = $user_names[$user['id']];
				
				$get_timesheets = "
				SELECT id,start_time,end_time 
				FROM timesheets
				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 `user_id` = '$user_id' AND status = '1'
				";
				$results_timesheets = $database->getAll($get_timesheets);
										
				$accum_timesheets = array();
				
				if(count($results_timesheets) > 0)
				{
					foreach($results_timesheets AS $results_timesheet)
					{
						$accum_timesheets[$results_timesheet['id']] = $results_timesheet;
						$accum_timesheets[$results_timesheet['id']]['end_time'] = date('Y-m-d G:i:s');
					}
				}
					
				$get_timesheets = "
				SELECT id,start_time,end_time 
				FROM timesheets
				WHERE start_time >= '$start_date 00:00:00' AND start_time <= '$end_date 23:59:59' AND `user_id` = '$user_id' AND status = '1'
				";
				$results_timesheets = $database->getAll($get_timesheets);
											
				if(count($results_timesheets) > 0)
				{
					foreach($results_timesheets AS $results_timesheet)
					{
						$accum_timesheets[$results_timesheet['id']] = $results_timesheet;
					}
				}
			
				$get_timesheets = "
				SELECT id,start_time,end_time 
				FROM timesheets
				WHERE end_time >= '$start_date 00:00:00' AND end_time <= '$end_date 23:59:59' AND `user_id` = '$user_id' AND status = '1'
				";
				$results_timesheets = $database->getAll($get_timesheets);
											
				if(count($results_timesheets) > 0)
				{
					foreach($results_timesheets AS $results_timesheet)
					{
						$accum_timesheets[$results_timesheet['id']] = $results_timesheet;
					}
				}
				
				$total_ts = 0;
				
				$date_arr = array();
				
				if(count($accum_timesheets))
				{
					foreach($accum_timesheets AS $key => $accum_timesheet)
					{
						$start_time = $accum_timesheet['start_time'];
						$end_time = $accum_timesheet['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))
						{
							$total_ts += aging::get_time_by_hour($start_time,$end_time);	
						}
						else
						{
							if(($this_start_time_unix < $start_date_unix) && ($this_end_time_unix > $start_date_unix))			
							{
								$total_ts += aging::get_time_by_hour($start_date." 00:00:00",$end_time);											
							}
							else if(($this_start_time_unix >= $start_date_unix) && ($this_end_time_unix > $end_date_unix))			
							{
								$total_ts += aging::get_time_by_hour($start_time,$end_date." 23:59:59");															
							}
						}			
					}
				}
						
				$get_timesheets = "
				SELECT job_trackings.id,job_trackings.start_time,job_trackings.end_time 
				FROM job_trackings
				INNER JOIN jobs ON job_trackings.job_id = jobs.id
				INNER JOIN quotes ON quotes.id = jobs.quote_id
				WHERE job_trackings.start_time >= '$start_date 00:00:00' 
				AND job_trackings.start_time <= '$end_date 23:59:59'
				AND job_trackings.end_time = '0000-00-00 00:00:00' 
				AND job_trackings.`user_id` = '$user_id' 
				AND job_trackings.status = '1'
				AND quotes.nonbill_hour = '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 = "
				SELECT job_trackings.id,job_trackings.start_time,job_trackings.end_time 
				FROM job_trackings
				INNER JOIN jobs ON job_trackings.job_id = jobs.id
				INNER JOIN quotes ON quotes.id = jobs.quote_id
				WHERE job_trackings.start_time >= '$start_date 00:00:00' 
				AND job_trackings.start_time <= '$end_date 23:59:59' 
				AND job_trackings.`user_id` = '$user_id' 
				AND job_trackings.status = '1'
				AND quotes.nonbill_hour = '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 = "
				SELECT job_trackings.id,job_trackings.start_time,job_trackings.end_time 
				FROM job_trackings
				INNER JOIN jobs ON job_trackings.job_id = jobs.id
				INNER JOIN quotes ON quotes.id = jobs.quote_id
				WHERE job_trackings.end_time >= '$start_date 00:00:00' 
				AND job_trackings.end_time <= '$end_date 23:59:59' 
				AND job_trackings.`user_id` = '$user_id' 
				AND job_trackings.status = '1'
				AND quotes.nonbill_hour = '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;
					}
				}
											
				$total_jt = 0;
				
				$date_arr = array();
				
				if(count($accum_jt))
				{
					foreach($accum_jt AS $key => $jt)
					{
						$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))
						{
							$total_jt += aging::get_time_by_hour($start_time,$end_time);	
						}
						else
						{
							if(($this_start_time_unix < $start_date_unix) && ($this_end_time_unix > $start_date_unix))			
							{
								$total_jt += aging::get_time_by_hour($start_date." 00:00:00",$end_time);											
							}
							else if(($this_start_time_unix >= $start_date_unix) && ($this_end_time_unix > $end_date_unix))			
							{														
								$total_jt += aging::get_time_by_hour($start_time,$end_date." 23:59:59");															
							}
						}			
					}
				}
													
				$productivity = 0;
				
				if($total_ts > 0)
				{
					$productivity = ($total_jt / $total_ts) * 100;
				}
				
				$productivity = decimal_fix::fix($productivity);					
				
				$return_results[] = array(
					"id" => $user_id,
					"name" => $name,
					"timesheets" => $total_ts,
					"job_sheets" => $total_jt,										
					"productivity" => $productivity										
					
				);

				$this->total_timesheets	+= $total_ts;
				$this->total_jobsheets	+= $total_jt;				
			}
		}
				
		$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 == "timesheets")
					{
						?><td><a class="timesheets_list" ></a></td><?					
					}
					else if($key_col == "job_sheets")
					{
						?><td><a class="job_sheets_list" ></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;			 
		
		$productivity = 0;
		
		if($this->total_timesheets > 0)
		{
			$productivity = ($this->total_jobsheets / $this->total_timesheets) * 100;
		}

		$productivity = decimal_fix::fix($productivity);				
		?>
		<tfoot>
		<tr>
		<?
		if($avail_status)
		{
			?><th>&nbsp;</th><?
		}
						
		if($avail_edit)
		{					
			?><th>&nbsp;</th><?
		}
		
		
		foreach($columns AS $key => $value)
		{		
			if($key == "timesheets")
			{
				?><th>