<?php
$today = date("Y-m-d");

$late_query = "SELECT status FROM $table_name WHERE emp_id = '".$emp_id."' AND status='L' AND date >= DATE_SUB('".$today."', INTERVAL 31 DAY);";
$late_result = mySQL_query($late_query);
$late_num = mysql_num_rows($late_result);

if ($late_num>1) {
date_default_timezone_set('Africa/Khartoum');
$entry_date = date("Y-m-d H:i:s",time()-3600); 

$table_name = $_SESSION['db_prefix']."_employee";
$emp_name_query = "SELECT emp_first_name, emp_last_name FROM $table_name WHERE emp_id = '".$emp_id."' ";
$emp_name_result = mySQL_query($emp_name_query);
$emp_name = mysql_result($emp_name_result, 0, "emp_first_name").' '.mysql_result($emp_name_result, 0, "emp_last_name");

	$table_name = $_SESSION['db_prefix']."_alerts";
	
	$alert_query = "SELECT user_name, send_alert, send_email FROM $table_name WHERE alert_name = 'employee late' AND condition_1 <= ".$late_num." AND condition_2 = 'month' ";
	$alert_result = mySQL_query($alert_query);
	$alert_num = mysql_num_rows($alert_result);
	
	$table_name = $_SESSION['db_prefix']."_time_record";
	$late_date_query = "SELECT date, clock_in FROM $table_name WHERE emp_id = '".$emp_id."' AND status = 'L' AND date >= DATE_SUB('".$today."', INTERVAL 31 DAY);";
	$late_date_result = mySQL_query($late_date_query);
	$late_date_num = mysql_num_rows($late_date_result);
	$d=0;
	$late_note='';
	while ($d<$late_date_num) {
	$late_date = mysql_result($late_date_result, $d, "date");
	$late_min = mysql_result($late_date_result, $d, "clock_in")-420;
	$late_note = $late_note.'<br>'.date("d-m-Y", strtotime($late_date)).': '.$late_min.' minutes late.';
	$d++;
	}
				
	$i=0;
	while ($i<$alert_num) {
		$user_name = mysql_result($alert_result, $i, "user_name");
		$send_alert = mysql_result($alert_result, $i, "send_alert");
		$send_email = mysql_result($alert_result, $i, "send_email");
		
		if ($send_alert=='yes') {
			$table_name = $_SESSION['db_prefix']."_alert_message";
			$message_query = "INSERT INTO $table_name (user_name, generated, alert, detail_1, detail_2, detail_3 ) VALUES ('".$user_name."', '".$entry_date."', '20', '".$emp_id."', '30', '".$late_date_num."' ) ";
			execsql($message_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");
			$subject = 'Late employee alert from Tlhomeso Management';
			$message = '<body><font face=Calibri size=3>Alert from Tlhomeso Management:<br><br>'.$emp_name.' has now been '.$late_num.' times late in the last 30 days.</font><br><br>He/she was late on:<br>'.$late_note.'<br><br>For more info, login at <a href=costing.tocotex.com/mainmenu.php>Tlhomeso Management Program</a>.<br><br><font face=Calibri size=3 color=red><b>This message comes from a non-responsive address, please do not reply</b></font><body>';
			//include ('alert_to_email.php');
		}
			
		$i++;
	}
	
	
	$table_name = $_SESSION['db_prefix']."_alerts";
	$alert_query = "SELECT user_name, send_alert, send_email FROM $table_name WHERE alert_name = 'employee late' AND condition_1 <= ".$late_num." AND condition_2 = 'week' ";
	//echo '<br>'.$alert_query;
	$alert_result = mySQL_query($alert_query);
	$alert_num = mysql_num_rows($alert_result);
	//echo '<br><br><br>'.'no week: '.$alert_num;
	
	$table_name = $_SESSION['db_prefix']."_time_record";
	$late_date_query = "SELECT date, clock_in FROM $table_name WHERE emp_id = '".$emp_id."' AND status = 'L' AND date >= DATE_SUB('".$today."', INTERVAL 7 DAY);";
	$late_date_result = mySQL_query($late_date_query);
	$late_date_num = mysql_num_rows($late_date_result);
	$d=0;
	$late_note='';
	while ($d<$late_date_num) {
	$late_date = mysql_result($late_date_result, $d, "date");
	$late_min = mysql_result($late_date_result, $d, "clock_in")-420;
	$late_note = $late_note.'<br>'.date("d-m-Y", strtotime($late_date)).': '.$late_min.' minutes late.';
	$d++;
	}
			
	$i=0;
	while ($i<$alert_num) {
		$user_name = mysql_result($alert_result, $i, "user_name");
		$send_alert = mysql_result($alert_result, $i, "send_alert");
		$send_email = mysql_result($alert_result, $i, "send_email");
		
		if ($send_alert=='yes') {
			$table_name = $_SESSION['db_prefix']."_alert_message";
			$message_query = "INSERT INTO $table_name (user_name, generated, alert, detail_1, detail_2, detail_3 ) VALUES ('".$user_name."', '".$entry_date."', '20', '".$emp_id."', '7', '".$late_date_num."' ) ";
			execsql($message_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");
			$subject = 'Late employee alert from Tlhomeso Management';
			$message = '<body><font face=Calibri size=3>Alert from Tlhomeso Management:<br><br>'.$emp_name.' has now been '.$late_date_num.' times late in the last 7 days.</font><br><br>He/she was late on:<br>'.$late_note.'<br><br>For more info, login at <a href=costing.tocotex.com/mainmenu.php>Tlhomeso Management Program</a>.<br><br><font face=Calibri size=3 color=red><b>This message comes from a non-responsive address, please do not reply</b></font><body>';
			//include ('alert_to_email.php');
		}
			
		$i++;
	}
}
	
?>