<?
	class common_timesheets
	{						
		public function get_all_timesheets()
		{			
			global $database;
			
			$current_date = date('Y-m-d');
			
			//$database->debug = true;
			
			$get_all_timesheets = "
			SELECT *
			FROM timesheets
			";
			$all_timesheets = $database->getAll($get_all_timesheets);
			
			return $all_timesheets;			
		}
		
		public function get_all_timesheets_by_status($status)
		{			
			global $database;
			
			$current_date = date('Y-m-d');
			
			//$database->debug = true;
			
			$get_all_timesheets = "
			SELECT *
			FROM timesheets
			WHERE status = '$status'
			";
			$all_timesheets = $database->getAll($get_all_timesheets);
			
			return $all_timesheets;			
		}
		
		public function get_all_timesheets_by_status_and_user($status,$user_id)
		{			
			global $database;
			
			$current_date = date('Y-m-d');
			
			//$database->debug = true;
			
			$get_all_timesheets = "
			SELECT *
			FROM timesheets
			WHERE status = '$status' AND user_id = '$user_id'
			";
			$all_timesheets = $database->getAll($get_all_timesheets);
			
			return $all_timesheets;			
		}	
		
		public function get_all_timesheets_duration_by_status_list_user($status)
		{			
			global $database;
			
			$db_name = DB_CLIENT_ACCOUNTS;															

			$current_date = date('Y-m-d');
			$current_month = date('m');
			
			//$database->debug = true;
			/** Kai 04/04/2013*/
			$get_all_timesheets = "
			SELECT *
			FROM timesheets
			WHERE status = '$status' AND (user_id IN (
												SELECT id
												FROM $db_name"."users
												WHERE status = '1'
											))

			";
			
			
// 			$get_all_timesheets = "
// 			SELECT *
// 			FROM timesheets
// 			WHERE status = '$status' AND (user_id IN (
// 			SELECT id
// 			FROM $db_name"."users
// 			WHERE status = '1' and group_id IN (35,39)
// 			))
			
// 			";
			
			$all_timesheets = $database->getAll($get_all_timesheets);
			
			$users_timesheet = array();
			foreach($all_timesheets AS $timesheet)
			{
				$split_start = explode("-",$timesheet['start_time']);
				$month = $split_start[1];
				$split_start_date = explode(" ",$timesheet['start_time']);
				$date = $split_start_date[0];

				$users_timesheet[$timesheet['user_id']]['id'] = $timesheet['user_id'];
				
				if($month == $current_month)
				{
					if($timesheet['end_time'] == "0000-00-00 00:00:00")
					{
						$timesheet['end_time'] = date('Y-m-d G:i:s');		
					}
					$users_timesheet[$timesheet['user_id']]['mtd_duration'] += common_job_trackings::get_duration_minute($timesheet['start_time'],$timesheet['end_time']);
				}

				if($date == $current_date)
				{
					$users_timesheet[$timesheet['user_id']]['today_duration'] += common_job_trackings::get_duration_minute($timesheet['start_time'],$timesheet['end_time']);
				}
			}
			
			return $users_timesheet;			
		}		
			
		public function get_timesheet($id)
		{			
			global $database;
			
			//$database->debug = true;
			
			$get_timesheet = "
			SELECT *
			FROM timesheets
			WHERE id='$id'
			";
			$timesheet = $database->getRow($get_timesheet);
			
			$timesheet['start_time_normal'] = $timesheet['start_time'];
			$timesheet['end_time_normal'] = $timesheet['end_time'];
			$timesheet['start_time'] = dates::change_date_time($timesheet['start_time']);
			if($timesheet['end_time'] == "0000-00-00 00:00:00")
			{
				$timesheet['end_time'] = "I am Still working !";
				$timesheet['duration'] = self::get_duration_not_end($timesheet['start_time']);
			}
			else
			{
				$timesheet['end_time'] = dates::change_date_time($timesheet['end_time']);
				$timesheet['duration'] = self::get_duration($timesheet['start_time'],$timesheet['end_time']);
			}
			
			return $timesheet;			
		}	
		
		public function get_duration($start_time, $end_time)
		{			

			$to_time=strtotime($end_time);
			$from_time=strtotime($start_time);
			$shift_minutes =round(abs($to_time - $from_time) / 60);
			
			$duration = floor($shift_minutes/60)."h ".($shift_minutes%60)."m";

			return $duration;	
		}	
		
		public function get_duration_not_end($start_time)
		{	
			$to_time= strtotime(date('Y-m-d G:i:s'));
			$from_time=strtotime($start_time);
			$shift_minutes =round(abs($to_time - $from_time) / 60);
			
			$duration = floor($shift_minutes/60)."h ".($shift_minutes%60)."m";

			return $duration;	
		}	
		
		public function get_start_end_shift($id)
		{
			global $database;
			$db_name = DB_CLIENT;
			$get_timesheet = "
				SELECT * 
				FROM $db_name"."timesheets 
				WHERE user_id = '$id'
				ORDER BY id DESC LIMIT 1
			";
			$result=$database->getRow($get_timesheet);
			
			$start_end = "start";
		
			if(($result['end_time'] != "0000-00-00 00:00:00") || ($result['start_time'] == ""))
			{ 
				$start_end = 0; 
			} 
			else
			{ 
				$start_end = 1; 
			} 
			
			return $start_end;
		}
		
		public function get_last_timesheet_by_user($user_id)
		{
			global $database;
			$db_name = DB_CLIENT;
			$get_timesheet = "
				SELECT * 
				FROM $db_name"."timesheets 
				WHERE user_id = '$user_id'
				ORDER BY id DESC LIMIT 1
			";
			$result=$database->getRow($get_timesheet);
			
			return $result;
		}
		
		public function get_dates_of_week($start_date,$end_date)
		{
			$date_start = date('Y-m-d' , strtotime($start_date)) ;
			$date_end = date('Y-m-d' , strtotime($end_date));
			
			$week['0'] = date('Y-m-d' , strtotime($start_date));
			$week['1'] = date('Y-m-d' , strtotime($start_date."+1days"));
			$week['2'] = date('Y-m-d' , strtotime($start_date."+2days"));
			$week['3'] = date('Y-m-d' , strtotime($start_date."+3days"));
			$week['4'] = date('Y-m-d' , strtotime($start_date."+4days"));
			$week['5'] = date('Y-m-d' , strtotime($start_date."+5days"));
			$week['6'] = date('Y-m-d' , strtotime($start_date."+6days")); 
			
			return $week;
		}
		
		public function get_workhour_weekly_report($user_id,$start_date,$end_date)
		{			
			global $database;
			
			//$database->debug = true;
			
			$date_start = date('Y-m-d' , strtotime($start_date)) ;
			$date_end = date('Y-m-d' , strtotime($end_date));
			
			$week = self::get_dates_of_week($start_date,$end_date);
			
			$date_start_sql = date('Y-m-d H:i:s' , strtotime($start_date)) ;
			$date_end_sql = date('Y-m-d H:i:s' , strtotime($end_date."23hours 59minutes 59second")) ;
			
			$get_timesheet = "
			SELECT *
			FROM timesheets
			WHERE user_id='$user_id' AND status='1' AND start_time BETWEEN '$date_start_sql' AND '$date_end_sql'
			";
			$timesheets = $database->getAll($get_timesheet);
			
			$works_hours = array();
			for($i = 0; $i<=6 ; $i++)
			{
				foreach($timesheets AS $timesheet)
				{	
					$split_start = explode(" ",$timesheet['start_time']);
					if($split_start[0] == $week[$i])
					{
						$duration = common_job_trackings::get_duration_minute($timesheet['start_time'], $timesheet['end_time']);
						$works_hours[$i] = intval($works_hours[$i]) + intval($duration);
					}
				}
			}
			
			return $works_hours;
		}
		
		public function get_leave_weekly_report($user_id,$start_date,$end_date)
		{			
			global $database;
			
			//$database->debug = true;
			
			$date_start = date('Y-m-d' , strtotime($start_date)) ;
			$date_end = date('Y-m-d' , strtotime($end_date));
			
			$week = self::get_dates_of_week($start_date,$end_date);
			$shift_time = common_users::get_user_shift($user_id);
			
			$date_start_sql = date('Y-m-d' , strtotime($start_date)) ;
			$date_end_sql = date('Y-m-d' , strtotime($end_date)) ;
			
			$get_leaves = "
			SELECT *
			FROM leaves
			WHERE user_id='$user_id' AND status='4' AND
				((start_date BETWEEN '$date_start' AND '$date_end') OR (end_date BETWEEN '$date_start' AND '$date_end'))
			";
			$leaves = $database->getAll($get_leaves);
			
			$leaves_array = array();
			for($i = 0; $i<=4 ; $i++)
			{
				foreach($leaves AS $leave)
				{
					//print " date: ".$week[$i]." start date: ".$leave['start_date']." end date: ".$leave['end_date']."<br/>";
					if($leave['start_date'] == $week[$i])	
					{	
						if($leave['type'] == "1" && $leave['certificate'] == "1")
						{
							if($leave['certificate'] == "1")
							{
								$type = "11";
							}
							else
							{
								$type = "10";
							}
						}
						else
						{
							$type = $leave['type'];
						}
						
						$leaves_array[$type][$i] = 0;
						if($leave['start_date'] == $leave['end_date'])
						{
							$leaves_array[$type][$i] += ($leave['end_time'] - $leave['start_time']);
						}
						else
						{
							$end = strtotime($leave['end_date']);
							$start = strtotime($leave['start_date']);
							$datediff = (($end - $start)/3600)/24;
	
							for($j=0; ($j<=$datediff && $i+$j<=4) ; $j++)
							{
								$leaves_array[$type][$i+$j] += 8*60;
							}
						}
					}
				}
			}
			
			return $leaves_array;	
		}	
										
	}
?>