<?php
  include('../include/session.php');
  
 $today = date("Y-m-d"); 
  
  	date_default_timezone_set('Africa/Gaborone');
	$entry_date = date("Y-m-d H:i:s", time()); 
  
  $table_name = $_SESSION['db_prefix']."_time_sheet_upload";
  $delete_query = "DELETE FROM $table_name "; 
  execsql($delete_query);
  
  $select_file= "../upload/".$_SESSION['file_name'];
  $file_handle = fopen($select_file, "r");

  while (!feof($file_handle) ) {
    
  $line_of_text = fgetcsv($file_handle, 1024);	  
  $b2 = $line_of_text[2] ; //Attendance time

$date = date("Y-m-d", strtotime($b2));
 
$table_name = $_SESSION['db_prefix']."_time_sheet";
$check_date_query = "SELECT attendance_date FROM $table_name WHERE attendance_date = '".$date."' ";
$check_date_result = mySQL_query($check_date_query);
$check_date_num = mysql_num_rows($check_date_result);

} 
fclose($file_handle);

if ( $check_date_num > 0) {

?>

<script language="javascript" type="text/javascript">
        
	 window.location="recordTimesWiz0.php?error=1";
	 
</script>

<?php
}

else {
  $select_file= "../upload/".$_SESSION['file_name'];
  $file_handle = fopen($select_file, "r");
 
  while (!feof($file_handle) ) {
    
  $line_of_text = fgetcsv($file_handle, 1024);	  
  $b0 = $line_of_text[0] ; //Personnel no
  $b1 = $line_of_text[1] ; //First name
  $b2 = $line_of_text[2] ; //Attendance time
  $b3 = $line_of_text[3] ; //Attendance status
  $b4 = $line_of_text[4] ; //Serial number
  

   
if ($b0!='') {  
if ($b0!='Personnel no. ') {

$check_date = date("Y-m-d", strtotime($b2));
if ($b3=='Check-in') { $check_in = date("H:i", strtotime($b2));} else { $check_in = '00:00'; }
if ($b3=='Out') {$check_lunch_out = date("H:i", strtotime($b2));} else { $check_lunch_out = '00:00'; }
if ($b3=='Return from outside') {$check_lunch_in = date("H:i", strtotime($b2));} else { $check_lunch_in = '00:00'; }
if ($b3=='Check-out') {$check_out = date("H:i", strtotime($b2));} else { $check_out = '00:00'; }
	
$table_name = $_SESSION['db_prefix']."_time_sheet_upload";
$time_query = "INSERT INTO $table_name (personnel_no, attendance_date, attendance_status, check_in, check_lunch_out, check_lunch_in, check_out, user_name ) VALUES ('".$b0."', '".$check_date."', '".$b3."', '".$check_in."', '".$check_lunch_out."', '".$check_lunch_in."', '".$check_out."', '".$_SESSION['username']."') ";
execsql($time_query);
}
}
}

fclose($file_handle);

$table_name = $_SESSION['db_prefix']."_tracking_hr";
   $trackingQuery = "INSERT INTO $table_name (entry_date, user_name, action, details_1, details_2, details_3, details_4) VALUES ('".$entry_date."' ,'".$_SESSION['username']."', '29', '".$select_file."' ,'','','' )";
   $trackingResult = mySQL_query($trackingQuery);
   
$table_name = $_SESSION['db_prefix']."_employee";
$employee_query = "SELECT emp_no, time_keeping, time_zone FROM $table_name WHERE time_keeping = 'yes' AND end_employ_date > '".$today."' AND premature_end_date > '".$today."' ";
$employee_result = mySQL_query($employee_query);
$employee_num = mysql_num_rows($employee_result);

$table_name = $_SESSION['db_prefix']."_presets";
$time_query = "SELECT preset_value FROM $table_name WHERE preset_name = 'clockin1'  ";
$time_result = mySQL_query($time_query);
$intime1 = mysql_result($time_result, 0, "preset_value");

$table_name = $_SESSION['db_prefix']."_presets";
$time_query = "SELECT preset_value FROM $table_name WHERE preset_name = 'clockout1'  ";
$time_result = mySQL_query($time_query);
$outtime1 = mysql_result($time_result, 0, "preset_value");

$table_name = $_SESSION['db_prefix']."_presets";
$time_query = "SELECT preset_value FROM $table_name WHERE preset_name = 'clockin2'  ";
$time_result = mySQL_query($time_query);
$intime2 = mysql_result($time_result, 0, "preset_value");

$table_name = $_SESSION['db_prefix']."_presets";
$time_query = "SELECT preset_value FROM $table_name WHERE preset_name = 'clockout2'  ";
$time_result = mySQL_query($time_query);
$outtime2 = mysql_result($time_result, 0, "preset_value");


$table_name = $_SESSION['db_prefix']."_time_sheet_upload";
$days_query = "SELECT attendance_date FROM $table_name GROUP BY attendance_date  ";
$days_result = mySQL_query($days_query);
$select_day = mysql_result($days_result, 0, "attendance_date");	

$i=0;
while ($i<$employee_num) { 
$personnel_no = mysql_result($employee_result, $i, "emp_no");
$time_zone = mysql_result($employee_result, $i, "time_zone");
$time_keeping = mysql_result($employee_result, $i, "time_keeping");

if ($time_zone==1) {
			  $intime = $intime1;
			  $outtime = $outtime1;
		}
		
		if ($time_zone==2) {
			  $intime = $intime2;
			  $outtime = $outtime2;
		}
		
		if ($time_zone==3) {
			  $intime = '';
			  $outtime = '';
		}

 
	        $table_name = $_SESSION['db_prefix']."_time_sheet_upload";
			$emp_time_query = "SELECT personnel_no, attendance_date, attendance_status, check_in, check_lunch_out, check_lunch_in, check_out FROM $table_name WHERE personnel_no = '".$personnel_no."' AND attendance_date = '".$select_day."' ORDER BY attendance_date DESC ";
			$emp_time_result = mySQL_query($emp_time_query);
			$emp_time_num = mysql_num_rows($emp_time_result);
			
			if ( ($time_keeping=='yes') && ($time_zone<3) ) {
			$table_name = $_SESSION['db_prefix']."_time_sheet_temp";
			$time_sheet_query = "INSERT INTO $table_name (personnel_no, attendance_date, time_zone, time_zone_in, time_zone_out) VALUES ('".$personnel_no."', '".$select_day."', '".$time_zone."', '".$intime."', '".$outtime."') ";
			execsql($time_sheet_query);
			
			$j=0;
			while ($j<$emp_time_num) {
						
			//$temp_date = '2000-01-01';
//			$time_in = '0';
//			$time_out = '0';
//			$lunch_out = '0';
//			$lunch_in = '0';
//			$check_in_min = 0;
//			$check_out_min = 0;
//			$lunch_out_min = 0;
//			$lunch_in_min = 0;
//			$late=0;
//			$early=0;
			
			//$table_name = $_SESSION['db_prefix']."_time_sheet_temp";
//			$time_sheet_query = "INSERT INTO $table_name (personnel_no, attendance_date, check_in, check_in_min, check_lunch_out, check_lunch_out_min, check_lunch_in, check_lunch_in_min, check_out, check_out_min, time_zone, time_zone_in, time_zone_out  ) VALUES ('".$personnel_no."', '".$select_day."', '".$time_in."', '".$check_in_min."', '".$lunch_out."', '".$lunch_out_min."', '".$lunch_in."', '".$lunch_in_min."', '".$time_out."', '".$check_out_min."', '".$time_zone."', '".$intime."', '".$outtime."'  ) ";
//			execsql($time_sheet_query);
//			echo '<br>1'.$time_keeping;
			
	
			
//if ($emp_time_num!=0) {//
//			
//			$j=0;
			$temp_date = '2000-01-01';
			$time_in = '0';
//			$time_out = '0';
			$lunch_out = '0';
			$lunch_in = '0';
			$check_in_min = 0;
			$check_out_min = 0;
			$lunch_out_min = 0;
			$lunch_in_min = 0;
			
			
			$attendance_date = mysql_result($emp_time_result, $j, "attendance_date");  
			$attendance_status = mysql_result($emp_time_result, $j, "attendance_status");
			
				
			if ($attendance_status=='Check-in') { 
			    $time_in = mysql_result($emp_time_result, $j, "check_in"); 
			    $b2a = substr($time_in, 0, 2);
    			$b2b = substr($time_in, 3, 2);
    			$check_in_min = ($b2a*60)+$b2b;
				} 
				
				
			  if ($attendance_status=='Check-out') { 
			    $time_out = mysql_result($emp_time_result, $j, "check_out");
			  	$c2a = substr($time_out, 0, 2);
    			$c2b = substr($time_out, 3, 2);
    			$check_out_min = ($c2a*60)+$c2b;
				} 
				   
			   
			  if ($attendance_status=='Out') { 
			    $lunch_out = mysql_result($emp_time_result, $j, "check_lunch_out");
				$d2a = substr($lunch_out, 0, 2);
    			$d2b = substr($lunch_out, 3, 2);
    			$lunch_out_min = ($d2a*60)+$d2b;
				} 
				
				
				 
			  if ($attendance_status=='Return from outside') {
				$lunch_in = mysql_result($emp_time_result, $j, "check_lunch_in");
				$e2a = substr($lunch_in, 0, 2);
    			$e2b = substr($lunch_in, 3, 2);
    			$lunch_in_min = ($e2a*60)+$e2b;
				} 
				
			 
			 		   
			if ($attendance_status=='Check-in') { 	 
			 $table_name = $_SESSION['db_prefix']."_time_sheet_temp";
			 $time_record_query = "UPDATE $table_name SET check_in = '".$time_in."', check_in_min = '".$check_in_min."', attendance_status = '".$attendance_status."'  WHERE personnel_no = '".$personnel_no."' ";
			execsql($time_record_query);
			}
			
			if ($attendance_status=='Out') { 	 
			 $table_name = $_SESSION['db_prefix']."_time_sheet_temp";
			 $time_record_query = "UPDATE $table_name SET check_lunch_out = '".$lunch_out."', check_lunch_out_min = '".$lunch_out_min."', attendance_status = '".$attendance_status."'  WHERE personnel_no = '".$personnel_no."' ";
			execsql($time_record_query);
			}
			
			if ($attendance_status=='Return from outside') { 	 
			 $table_name = $_SESSION['db_prefix']."_time_sheet_temp";
			 $time_record_query = "UPDATE $table_name SET check_lunch_in = '".$lunch_in."', check_lunch_in_min = '".$lunch_in_min."', attendance_status = '".$attendance_status."'  WHERE personnel_no = '".$personnel_no."' ";
			execsql($time_record_query);
			}
			
			if ($attendance_status=='Check-out') { 	 
			 $table_name = $_SESSION['db_prefix']."_time_sheet_temp";
			 $time_record_query = "UPDATE $table_name SET check_out = '".$time_out."', check_out_min = '".$check_out_min."',  attendance_status = '".$attendance_status."'  WHERE personnel_no = '".$personnel_no."' ";
			execsql($time_record_query);
			}
						
			$j++;
			}
			}
//			
//			$temp_date=$attendance_date;	  
//			   
//			   $j++;
			
$i++;
	
//}

}   
   
$table_name = $_SESSION['db_prefix']."_time_sheet_upload";
$delete_query = "DELETE FROM $table_name "; 
execsql($delete_query);   
   
   

?>

<script language="javascript" type="text/javascript">
        
	 window.location="../hr_module/recordTimesWizbase.php?p=3&s=&r=";
        
       </script>
       
<?php
}
?>