<!--Notice: (c)2013 This page is part of the management program for Tlhomeso Timbers, Gaborone, 
and developed by Customized Software (Pty) Ltd, Gaborone, Botswana. 
Customized Software holds the copyright on the source code on this and all other pages in the 
management program. For more info contact Customized Software on email: info@bwcustomized.com-->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Tlhomeso Timbers</title>
<link rel="shortcut icon" type="image/x-icon" href="../images/favicon.ico">
<script language="javascript" src="../jscript/calendar.js"></script>
<script type="text/javascript" src="../jscript/tablesort.js"></script>
<?php 
include("../include/session.php");

$start=$_GET['start'];
$end=$_GET['end'];
	
$table_name = $_SESSION['db_prefix']."_time_record";
$days_query = "SELECT DISTINCT date  FROM $table_name WHERE date>='".$start."' AND date<'".$end."' AND type='N' AND status<>'N'  ";
$days_result = mySQL_query($days_query);
$days_num = mysql_num_rows($days_result);

$table_name = $_SESSION['db_prefix']."_time_record";
$not_query = "SELECT DISTINCT date  FROM $table_name WHERE date>='".$start."' AND date<'".$end."' AND type='N' AND status_2=''  ";
$not_result = mySQL_query($not_query);
$not_num = mysql_num_rows($not_result);
if ($not_num!=0) {
	$attMessage='<img src=../images/exclamation-mark_small.png width=24 height=24 />There are days not all employees were clocked out.';
}
else {
	$attMessage='';
}


	
$table_name2 = $_SESSION['db_prefix']."_time_record";
$worked_query = "SELECT emp_id,  SUM(w_min) AS worked_min, SUM(l_min) AS late_min, SUM(o1_min) AS over1_min, SUM(o2_min) AS over2_min FROM $table_name2 WHERE date >= '".$start."' AND date < '".$end."' GROUP BY emp_id ";
$worked_result = mySQL_query($worked_query);
$worked_num = mysql_num_rows($worked_result);



$table_name2 = $_SESSION['db_prefix']."_time_record";
$hours_query = "SELECT DISTINCT date FROM $table_name2 where `date` >= '".$start."' AND  `date` <='".$end."' AND `type` = 'N' ";

$hours_result = mySQL_query($hours_query);
$hours_num = mysql_num_rows($hours_result);



?>
<script type="text/javascript" src="../jscript/tablesort.js"></script>
<script language="javascript" src="../jscript/calendar.js"></script>
<link href="../css/main.css" rel="stylesheet" type="text/css" />
</head>

<body>



  <table id="tableMain" > <!--begin of tableMain-->
    <tr>
      <td width="881" height="84" valign="top" ><div class="page_head_frame" style="width:900px;">
        
        <table width="884" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td width="420"><label class="label_page_head">Attendance register</label></td>
            <td width="331">&nbsp;</td>
            <td width="133" rowspan="2" align="right"><img src="../images/logo_tlhomeso.png" width="111" height="54" /></td>
          </tr>
          <tr>
            <td><label class="label_std">Worked hours in the period from <?php echo date("d-m-Y", strtotime($start)) ?> to <?php echo date("d-m-Y", strtotime($end)) ?>.</label></td>
            <td>&nbsp;</td>
          </tr>
        </table>
      </div>
        <br />
<br />
<br /></td> <!--center of tableMain-->
</tr>
<tr>
<td> <!--center of tableMain-->

<table width="912" id="tableprint" >
  <tr>
    <th class="sortable-text">No</th>
    <th class="sortable-text">Name</th>
    <th class="sortable-text">Department</th>
    <th >&nbsp;</th>
    <th >Worked </th>
    <th >Absent </th>
    <th align="center" >Overtime<br />
150%</th>
    <th align="center" >Overtime<br />
200%</th>
    <th align="center" ><span class="sortable-numeric">Attendance<br />
(%)</span></th>
    </tr>
<?php 
$i=0;
$a2tot='';
$a3tot='';
$a4tot='';
$a5tot='';
$a6tot='';
$a8tot='';
$a9tot='';
$a10tot='';



while ($i<$worked_num) {
	
	$a1 = mysql_result($worked_result, $i, "emp_id");
	   	$table_name = $_SESSION['db_prefix']."_employee";
    	$employee_query = "SELECT emp_no,emp_first_name, emp_last_name, department, time_keeping  FROM $table_name WHERE emp_id = $a1 ";
		$employee_result = mySQL_query($employee_query);
        $a1a = mysql_result($employee_result, 0, "emp_first_name")." ".mysql_result($employee_result, 0, "emp_last_name");
		$a7 = mysql_result($employee_result, 0, "department");
		$a1b = mysql_result($employee_result, 0, "emp_no");
		
	$table_name = $_SESSION['db_prefix']."_time_record";
	$not_query = "SELECT  date  FROM $table_name WHERE emp_id='".$a1."' AND date>='".$start."' AND date<'".$end."' AND type='N' AND status_2=''  ";
	$not_result = mySQL_query($not_query);
	$not_num = mysql_num_rows($not_result);
	
	$table_name = $_SESSION['db_prefix']."_time_record";
	$count_query = "SELECT  date  FROM $table_name WHERE emp_id='".$a1."' AND date>='".$start."' AND date<'".$end."' AND type='N' ";
	$count_result = mySQL_query($count_query);
	$count_num = mysql_num_rows($count_result);
	
	
	if ($not_num!=0) {
	    $a12='not';
	}
	else {
		if ($count_num!=$days_num) {
			$a12="part";
		}
		else {
		   $a12='ok';
		}
	}
	$dateTT='This employee was not clocked out on:<br>';
	$n=0;
	while ($n<$not_num) {	
		$d1 = mysql_result($not_result, $n, "date");
		$dateTT=$dateTT.date("d-m-Y", strtotime($d1)).'<br>';
		$n++;
	}
	
	$empTT="<label class=tip_text_label_bold>".$a1."<br>".$a1a."<br>Click for details</label>";
		
	$a10 = 540*$count_num;
		   $hours =  floor($a10/60); 
		   $mins =   $a10 % 60; 
		   $a10a = $hours . " hrs " . $mins . " min ";
		   if ($a10!=0) {
		         $a10a = $hours . " hrs " . $mins . " min ";
		   }
		   else
		   {
			   $a10a = "";
		   }
	
		
	$a2 = mysql_result($worked_result, $i, "worked_min");
		   $hours =  floor($a2/60); 
		   $mins =   $a2 % 60; 
		   $a2a = $hours . " hrs " . $mins . " min ";
		   if ($a2!=0) {
		         $a2a = $hours . " hrs " . $mins . " min ";
		   }
		   else
		   {
			   $a2a = "";
		   }
	$a3 = mysql_result($worked_result, $i, "late_min");
		   $hours =  floor($a3/60); 
		   $mins =   $a3 % 60; 
		   $a3a = $hours . " hrs " . $mins . " min ";
		   if ($a3!=0) {
		         $a3a = $hours . " hrs " . $mins . " min ";
		   }
		   else
		   {
			   $a3a = "";
		   }
		   
	$a4 = $a10-$a2;
	       $hours =  floor($a4/60); 
		   $mins =   $a4 % 60; 
		   $a4a = $hours . " hrs " . $mins . " min ";
		   if ($a4!=0) {
		         $a4a = $hours . " hrs " . $mins . " min ";
		   }
		   else
		   {
			   $a4a = "";
		   }
		   
    $a8 = mysql_result($worked_result, $i, "over1_min");
	       $hours =  floor($a8/60); 
		   $mins =   $a8 % 60; 
		   if ($a8!=0) {
		         $a8a = $hours . " hrs " . $mins . " min ";
		   }
		   else
		   {
			   $a8a = "";
		   }
    $a9 = mysql_result($worked_result, $i, "over2_min");
	       $hours =  floor($a9/60); 
		   $mins =   $a9 % 60; 
		   $a9a = $hours . " hrs " . $mins . " min ";
		   if ($a9!=0) {
		         $a9a = $hours . " hrs " . $mins . " min ";
		   }
		   else
		   {
			   $a9a = "";
		   }
		
	$a5 = $a2 + $a3 + $a4;	
	       $hours =  floor($a5/60); 
		   $mins =   $a5 % 60; 
		   $a5a = $hours . " hrs " . $mins . " min ";
		   
     if ($a5!=0) {
	 $a6a = (( $a2/$a5 ) * 100);
		 }
		 else {
			 $a6a = 0;
		 }
		 
	 
	 $a2tot = $a2tot + $a2;
	 $a3tot = $a3tot + $a3;
	 $a4tot = $a4tot + $a4;
	 $a5tot = $a5tot + $a5;
	 $a6tot = $a6tot + $a6a;
	 $a8tot = $a8tot + $a8;
	 $a9tot = $a9tot + $a9;
	 $a10tot = $a10tot + $a10;
	
	?>
  
  <tr>
    <td width="37" align="left"><?php echo $a1b ?>&nbsp;</td>
    <td width="94" align="left"><a href="employee-details.php?emp_id=<?php echo $a1?>" onMouseover="ddrivetip(' <?php echo $empTT ?>', 150)"; onMouseout="hideddrivetip()"><?php echo $a1a ?> </a></td>
    <td width="85" align="left"><?php echo $a7  ?></td>
    <td width="29" align="left"><?php if ($a12=='ok') {?><a href=" " onMouseover="ddrivetip('This employee was registered the full period.', 150)"; onMouseout="hideddrivetip()">   <img src="../images/check_mark_green.png" width="16" height="16" /></a><?php } ?>
    <?php if ($a12=='part') {?><a href=" " onMouseover="ddrivetip('This employee was not registered all days during this period.', 150)"; onMouseout="hideddrivetip()"><img src="../images/check_mark_orange.png" width="16" height="16" /></a><?php } ?>
     <?php if ($a12=='not') {?><a href="" onMouseover="ddrivetip('<?php echo $dateTT ?> ', 200)"; onMouseout="hideddrivetip()"><img src="../images/exclamation-mark_small.png" width="16" height="16" /></a><?php } ?></td>
    <td width="89" align="left"><?php echo $a2a ?></td>
    <td width="92" align="left"><?php echo $a4a ?></td>
    <td width="76" align="right"><?php echo $a8a ?></td>
    <td width="78" align="right"><?php echo $a9a ?></td>
    <td width="62" align="right"><?php echo number_format($a6a , 2, '.', ',') ?></td>
    </tr>
  <?php
  $i++;
}
?>
<tfoot>
  <tr>
    <td align="left" valign="bottom">&nbsp;</td>
    <td height="42" align="left" valign="bottom">&nbsp;</td>
    <td align="left" valign="bottom">totals</td>
    <td align="left" valign="bottom">&nbsp;</td>
    <td align="left" valign="bottom"><?php $hours =  floor($a2tot/60); 
		                   $mins =   $a2tot % 60; 
		                   $a2tot = $hours . " hrs " . $mins . " Min ";
						   echo $a2tot ;
						   ?></td>
    <td align="left" valign="bottom"><?php $hours =  floor($a4tot/60); 
		                   $mins =   $a4tot % 60; 
		                   $a4tot = $hours . " hrs " . $mins . " Min ";
						   echo $a4tot; 
						   ?></td>
    <td align="right" valign="bottom"><?php $hours =  floor($a8tot/60); 
		                   $mins =   $a8tot % 60; 
		                   $a8tot = $hours . " hrs " . $mins . " Min ";
						   echo $a8tot; 
						   ?></td>
    <td align="right" valign="bottom"><?php $hours =  floor($a9tot/60); 
		                   $mins =   $a9tot % 60; 
		                   $a9tot = $hours . " hrs " . $mins . " Min ";
						   echo $a9tot; 
						   ?></td>
    <td align="right" valign="bottom"><?php $average = $a6tot/$worked_num; echo number_format($average, 2, '.', ','); ?></td>
    </tr>
  </tfoot>
</table>
<div align="left"><label class="label_end">end of list</label></div>
</td>
</tr>
</table>  <!--end of tableMain-->
  

</body>

</html>