<?php
//include('../include/session.php');

date_default_timezone_set('Africa/Gaborone');
$entry_date = date("Y-m-d H:i:s"); 


$comp_date = date("Y-m-d");
////$comp_date = '2014-08-11';
//if (date("l", strtotime($comp_date))=='Monday') {
//  $select_date1 = strtotime(date("Y-m-d", strtotime($comp_date)) . "-3 day");
//  $select_date2 = strtotime(date("Y-m-d", strtotime($comp_date)) . "-1 day");
//  $select_date1 = date("Y-m-d", $select_date1);
//  $select_date2 = date("Y-m-d", $select_date2);
//}
//else {
//   $select_date = strtotime(date("Y-m-d", strtotime($comp_date)) . "-1 day");
//   $select_date1 = date("Y-m-d", $select_date);
//   $select_date2 = date("Y-m-d", $select_date);
//}


$table_name = $_SESSION['db_prefix']."_time_sheet";
$entry_total_query = "SELECT time_sheet_id FROM $table_name WHERE attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$entry_total_result = mySQL_query($entry_total_query);
$entry_total_num = mysql_num_rows($entry_total_result);

$table_name = $_SESSION['db_prefix']."_time_sheet";
$entry_late_query = "SELECT time_sheet_id FROM $table_name WHERE attendance_status = 'Late' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$entry_late_result = mySQL_query($entry_late_query);
$entry_late_num = mysql_num_rows($entry_late_result);

$table_name = $_SESSION['db_prefix']."_time_sheet";
$entry_sick_query = "SELECT time_sheet_id FROM $table_name WHERE attendance_status = 'Sick' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$entry_sick_result = mySQL_query($entry_sick_query);
$entry_sick_num = mysql_num_rows($entry_sick_result);

$table_name = $_SESSION['db_prefix']."_time_sheet";
$entry_leave_query = "SELECT time_sheet_id FROM $table_name WHERE attendance_status = 'Leave' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$entry_leave_result = mySQL_query($entry_leave_query);
$entry_leave_num = mysql_num_rows($entry_leave_result);

$table_name = $_SESSION['db_prefix']."_time_sheet";
$entry_absent_query = "SELECT time_sheet_id FROM $table_name WHERE attendance_status = 'Absent' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$entry_absent_result = mySQL_query($entry_absent_query);
$entry_absent_num = mysql_num_rows($entry_absent_result);

$recordings_content =  '<table>
						<tr><td>Total recordings:<td><td align=right>'.$entry_total_num.'</td></tr>
						<tr><td>Late recordings:<td><td align=right>'.$entry_late_num.'</td></tr>
						<tr><td>Sick recordings:<td><td align=right>'.$entry_sick_num.'</td></tr>
						<tr><td>Leave recordings:<td><td align=right>'.$entry_leave_num.'</td></tr>
						<tr><td>Absent recordings:<td><td align=right>'.$entry_absent_num.'</td></tr>
						</table>';
						

$table_name = $_SESSION['db_prefix']."_time_sheet";
$late_query = "SELECT emp_id, check_in FROM $table_name WHERE attendance_status = 'Late' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$late_result = mySQL_query($late_query);
$late_num = mysql_num_rows($late_result);

$late_content_line = '';
$i=0;
while ($i<$late_num) {
	$emp_id = mysql_result($late_result, $i, "emp_id");
	$check_in = mysql_result($late_result, $i, "check_in");
	
	$table_name2 = $_SESSION['db_prefix']."_employee";
	$employee_query = "SELECT emp_first_name, emp_last_name FROM $table_name2 WHERE emp_id ='".$emp_id."' ";
	$employee_result = mySQL_query($employee_query);
	$employee_num = mysql_num_rows($employee_result);	
	$emp_name = mysql_result($employee_result, 0, "emp_first_name").' '.mysql_result($employee_result, 0, "emp_last_name"); 
	
	$late_content_line = $late_content_line.'<tr><td>&nbsp;&nbsp;&nbsp;</td><td>'.$emp_name.'</td><td>'.$check_in.'</td></tr>';
		
	$i++;
}
	$late_content = '<table>'.$late_content_line.'</table>';
	

$table_name = $_SESSION['db_prefix']."_time_sheet";
$sick_query = "SELECT emp_id FROM $table_name WHERE attendance_status = 'Sick' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$sick_result = mySQL_query($sick_query);
$sick_num = mysql_num_rows($sick_result);

$sick_content_line = '';
$i=0;
while ($i<$sick_num) {
	$emp_id = mysql_result($sick_result, $i, "emp_id");
		
	$table_name2 = $_SESSION['db_prefix']."_employee";
	$employee_query = "SELECT emp_first_name, emp_last_name FROM $table_name2 WHERE emp_id ='".$emp_id."' ";
	$employee_result = mySQL_query($employee_query);
	$employee_num = mysql_num_rows($employee_result);	
	$emp_name = mysql_result($employee_result, 0, "emp_first_name").' '.mysql_result($employee_result, 0, "emp_last_name"); 
	
	$sick_content_line = $sick_content_line.'<tr><td>&nbsp;&nbsp;&nbsp;</td><td>'.$emp_name.'</td></tr>';
		
	$i++;
}
	$sick_content = '<table>'.$sick_content_line.'</table>';
	
$table_name = $_SESSION['db_prefix']."_time_sheet";
$leave_query = "SELECT emp_id FROM $table_name WHERE attendance_status = 'Leave' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$leave_result = mySQL_query($leave_query);
$leave_num = mysql_num_rows($leave_result);

$leave_content_line = '';
$i=0;
while ($i<$leave_num) {
	$emp_id = mysql_result($leave_result, $i, "emp_id");
		
	$table_name2 = $_SESSION['db_prefix']."_employee";
	$employee_query = "SELECT emp_first_name, emp_last_name FROM $table_name2 WHERE emp_id ='".$emp_id."' ";
	$employee_result = mySQL_query($employee_query);
	$employee_num = mysql_num_rows($employee_result);	
	$emp_name = mysql_result($employee_result, 0, "emp_first_name").' '.mysql_result($employee_result, 0, "emp_last_name"); 
	
	$leave_content_line = $leave_content_line.'<tr><td>&nbsp;&nbsp;&nbsp;</td><td>'.$emp_name.'</td></tr>';
		
	$i++;
}
	$leave_content = '<table>'.$leave_content_line.'</table>';		
	
$table_name = $_SESSION['db_prefix']."_time_sheet";
$absent_query = "SELECT emp_id FROM $table_name WHERE attendance_status = 'Absent' AND attendance_date BETWEEN '".$select_date1."' AND '".$select_date2."'";
$absent_result = mySQL_query($absent_query);
$absent_num = mysql_num_rows($absent_result);

$absent_content_line = '';
$i=0;
while ($i<$absent_num) {
	$emp_id = mysql_result($absent_result, $i, "emp_id");
		
	$table_name2 = $_SESSION['db_prefix']."_employee";
	$employee_query = "SELECT emp_first_name, emp_last_name FROM $table_name2 WHERE emp_id ='".$emp_id."' ";
	$employee_result = mySQL_query($employee_query);
	$employee_num = mysql_num_rows($employee_result);	
	$emp_name = mysql_result($employee_result, 0, "emp_first_name").' '.mysql_result($employee_result, 0, "emp_last_name"); 
	
	$absent_content_line = $absent_content_line.'<tr><td>&nbsp;&nbsp;&nbsp;</td><td>'.$emp_name.'</td></tr>';
		
	$i++;
}
	$absent_content = '<table>'.$absent_content_line.'</table>';	
	

$report_text = 'Daily attendance report. (Compiled on '.date("d-m-Y", strtotime($comp_date)).')<br><br>Attendance for '.date("l, d-m-Y", strtotime($select_date1)).'<br>'.$recordings_content.'<br>'.'Employees late today:<br>'.$late_content.'<br>'.'Employees sick today:<br>'.$sick_content.'<br>'.'Employees on leave today:<br>'.$leave_content.'<br>'.'Employees absent today:<br>'.$absent_content;



$table_name = $_SESSION['db_prefix']."_reports";
	$report_query = "SELECT user_name, send_report, send_email FROM $table_name WHERE report_name = 'daily attendance' ";
	$report_result = mySQL_query($report_query);
	$report_num = mysql_num_rows($report_result);
			
	$i=0;
	$email_address = '';
	while ($i<$report_num) {
		$user_name = mysql_result($report_result, $i, "user_name");
		$send_alert = mysql_result($report_result, $i, "send_report");
		$send_email = mysql_result($report_result, $i, "send_email");
		
		$table_name = $_SESSION['db_prefix']."_saved_hr_reports";
$report_query = "INSERT INTO $table_name (department, saved_report_name, saved_report_date, report_date, saved_report_text, user_name ) VALUES ('HR', 'Daily attendance report', '".$entry_date."', '".$recording_date."', '".$report_text."', '".$_SESSION['username']."' ) ";
execsql($report_query);	
		
		if ($send_email=='yes') {
			$table_name = $_SESSION['db_prefix']."_users";
			$address_query = "SELECT email FROM $table_name WHERE username = '".$user_name."' ";
			$address_result = mySQL_query($address_query);
			$email_address = mysql_result($address_result, 0, "email").','.$email_address;
		}
		$i++;
	}


if ($email_address!='') {	
$subject = 'Daily attendance';
			$message = '
<body style=font-face=Calibri; font-size=10px;><font size=14px;><b>Daily attendance report</b></font><br><br>
'.$report_text.'
<br><br>
For more info, login at <a href=crmcustomized.manase.co.bw/login.php?e= >Manase Management Program</a>.<br><br><font color=red><b>This message comes from a non-responsive address, please do not reply</b></font><body>';


$to = $email_address;
$subject = $subject;
$message = $message;
$from = 'manase-management@bwcustomized.com';
$headers = "From:" . $from. "\r\n";
$headers  .= 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
mail($to,$subject,$message,$headers);

}

?>

