<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<?php
include ("../navigation/header.php");
$page_name = "HR-main";
include ("../navigation/access.php");

if ($permission_num==1) {
	include ("../navigation/hr_sidebar.php");


$current_month = $_GET['current_month'];
$current_year = $_GET['current_year'];
$firstday = date($current_year).'-'.date($current_month).'-01';
$lastday = date("Y-m-d", mktime(0,0,0,date($current_month)+1,0,date($current_year)));

$table_name = $_SESSION['db_prefix']."_time_sheet";


$table_name = $_SESSION['db_prefix']."_time_sheet";
$time_query = "SELECT SUM(check_out_min - check_in_min - lunch_min) AS recorded_time, SUM(time_zone_out - time_zone_in - lunch_period) AS required_time FROM $table_name WHERE attendance_date >= '".$firstday."' AND attendance_date <= '".$lastday."' ";
$time_result = $con->query($time_query);
$time_num = mysqli_num_rows($time_result);
$row = mysqli_fetch_assoc($time_result);
$recorded_time = $row["recorded_time"];
$required_time = $row["required_time"];
if ($required_time!=0) {
$attendance = ($recorded_time / $required_time) * 100;
} else {
	$attendance = 0;
}


$display_query = "SELECT COUNT(*), emp_no, emp_id, SUM(check_out_min - check_in_min - lunch_min) AS recorded_time, SUM(time_zone_out - time_zone_in - lunch_period) AS required_time FROM $table_name WHERE attendance_date >= '".$firstday."' AND attendance_date <= '".$lastday."'  GROUP BY emp_id";
$display_result = $con->query($display_query);


	
?>
<!--style sheets-->
<link href="../css/main.css" rel="stylesheet" type="text/css" />
<!--jscript-->
<script language="JavaScript" src="../jscript/go_to_url.js"  type="text/javascript"></script> 

<!--style sheets-->
<link href="../css/main.css" rel="stylesheet" type="text/css">  
<link rel="stylesheet" type="text/css" href="../css/scroll_table.css" />
<link rel="stylesheet" type="text/css" href="../css/tooltip.css" />

<!--jscript-->
<script language="JavaScript" src="../jscript/tooltip.js"  type="text/javascript"></script> 

</head>


<body onLoad="document.getElementById('current_month').selectedIndex=<?php echo $current_month-1 ?>; document.getElementById('current_year').value=<?php echo $current_year ?> ">


<div class="main_frame">
   <div class="page_head_frame">
      <table width="650" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td width="385"><label class="label_page_head">Attendance per period</label></td>
            <td width="253">&nbsp;</td>
            <td width="171" align="right">&nbsp;</td>
          </tr>
        </table>
   </div>
      
<div class="page_middle_frame">
<form action="month_attendance_list.php" method="get" target="_self">
<div class="page_inside_frame">
<table width="676" border="0px">
<tr>
<td width="433">
Select month: 
  <select name="current_month" class="select100" id="current_month">
    <option value="01">January</option>
    <option value="02">February</option>
    <option value="03">March</option>
    <option value="04">April</option>
    <option value="05">May</option>
    <option value="06">June</option>
    <option value="07">July</option>
    <option value="08">August</option>
    <option value="09">September</option>
    <option value="10">October</option>
    <option value="11">November</option>
    <option value="12">December</option>
  </select>
  <select name="current_year" class="select80" id="current_year">
    <option value="2017">2017</option>
    <option value="2018">2018</option>
    <option value="2019">2019</option>
    <option value="2020">2020</option>
  </select>
  <input name="select_button" type="submit" class="imagebutton" id="select_button" value="Select">
  <br>
</td>
<td width="233" align="center">
<?php $attendance_label = "label_attendance_green" ;
		if ($attendance<96)  { $attendance_label = "label_attendance_orange" ; }
        if ($attendance<92)  { $attendance_label = "label_attendance_red" ; }?>
        attendance this month, <b><?php echo $current_month.' '.$current_year ?></b> :<br />              <label class="<?php echo $attendance_label ?>"><?php echo number_format($attendance, 2, '.', ',')?> %</label>
</td>
</tr>
</table>
</div>  
</form>
<div class="page_inside_frame">
<script type="text/javascript" language="javascript" src="../jscript/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf-8">
			$(document).ready(function() {
				$('#scroll_table').dataTable( {
					"sScrollY": "500px",
					"bPaginate": false,
					"bScrollCollapse": true,
					"aaSorting": [[ 2, "asc" ]],
					"aoColumns": [
								
                              	{"bSortable": true},
                             	{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true},
								
								 ]
				} );
			} );
		</script>
<table width="611" class="data_display" id="scroll_table" >
  <thead>
    <tr>
      <td width="48">Emp no</td>
      <td width="108">Name</td>
      <td width="45">Zone</td>
      <td width="52" align="center">Days</td>
      <td width="52" align="center">Hours</td>
      <td width="92" align="center">Attendance</td>
      <td width="39" align="center">&nbsp;</td>
      <td width="66" align="center">Overtime<br>
        150%</td>
      <td width="69" align="center">Overtime<br>
        200%</td>
    </tr>
  </thead>
    <?php 
	
	while($row = $display_result->fetch_assoc()) {
		$emp_no = $row["emp_no"];
		$recorded_time = $row["recorded_time"];
		$required_time = $row["required_time"];
		$attendance = ($recorded_time / $required_time) * 100;
		$no_of_days = mysqli_num_rows($display_result);
		$worked_time= $recorded_time;
		
		
		
			$table_name2 = $_SESSION['db_prefix']."_employee";
			$employee_query = "SELECT emp_id, emp_first_name, emp_last_name, time_zone FROM $table_name2 WHERE emp_no ='".$emp_no."' ";
			$employee_result = $con->query($employee_query);
			$row = mysqli_fetch_assoc($employee_result);
			$employee_name = $row["emp_first_name"].' '.$row["emp_last_name"];
			$emp_id = $row["emp_id"];
			$time_zone = $row["time_zone"];	
			
		 
		 
		 $hours =  floor($worked_time/60); 
		 $mins =   $worked_time % 60; 
		   if ($hours<10) { $hours = 0 . $hours; }
		   if ($mins<10) { $mins = 0 . $mins; }
		 $worked_display_time = $hours . ":" . $mins ;
		 
		$employee_TT = $employee_name.'<br>Click for details';
		
		$table_name = $_SESSION['db_prefix']."_overtime_records";
		$overtime_query = "SELECT SUM(overtime_min_150) AS summed150, SUM(overtime_min_200) AS summed200 FROM $table_name WHERE overtime_date >= '".$firstday."' AND overtime_date <= '".$lastday."' AND emp_id = '".$emp_id."' ";
		$overtime_result = $con->query($overtime_query);
		$row = mysqli_fetch_assoc($overtime_result);
		$overtime_min_150 = $row["summed150"];
		$overtime_min_200 = $row["summed200"];
		
		
		$hours =  floor($overtime_min_150/60); 
		 $mins =   $overtime_min_150 % 60; 
		   if ($hours<10) { $hours = 0 . $hours; }
		   if ($mins<10) { $mins = 0 . $mins; }
		 $overtime_min_150_display = $hours . ":" . $mins ;
		 
		 $hours =  floor($overtime_min_200/60); 
		 $mins =   $overtime_min_200 % 60; 
		   if ($hours<10) { $hours = 0 . $hours; }
		   if ($mins<10) { $mins = 0 . $mins; }
		 $overtime_min_200_display = $hours . ":" . $mins ;
		
				
					
	?>	
		
	<tr>
      <td width="48"><?php echo $emp_no ?></td>
      <td width="108"><a href="employee_details.php?emp_id=<?php echo $emp_id?> " onMouseover="ddrivetip('<?php echo $employee_TT?>', 150)"; onMouseout="hideddrivetip()"><?php echo $employee_name ?></a></td>
      <td width="45" align="center"><?php echo $time_zone ?></td>
      <td width="52" align="center"><?php echo $no_of_days ?></td>
      <td width="52" align="center"><?php echo $worked_display_time ?></td>
      <td width="92" align="center"><?php echo number_format($attendance, 2, '.', ',') ?>%</td>
      <td width="39" align="center">&nbsp;</td>
      <td width="66" align="center"><?php if ($overtime_min_150_display!=0) {echo $overtime_min_150_display;} ?></td>
      <td width="69" align="center"><?php if($overtime_min_200_display!=0) {echo $overtime_min_200_display;} ?></td>
    </tr>	
		
		
	<?php	
	}
	?>
  </table>
</div>
</div>
</div>

</body>
<?php } else {
$_SESSION['denied_return'] = "hr_module/hr_main.php?date=";	
include ("../navigation/hr_sidebar.php");?>
	<body onload="accessdenied(); return false">
	</body>
<?php }
?>
<?php include ('../navigation/side_bar.php'); ?>
</html>