<?php

session_start();
if($_SESSION['dbName']==""){
header("location:../index.php");
}


include("config.inc.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");



print <<<HERE
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" media="all" href="css-v2/style.css">

<link rel="stylesheet" type="text/css" media="all" href="js/grid/UI_DataGrid_V1.css">
<script type="text/javascript" src="js/grid/UI_DataGrid.js"></script>
<script type="text/javascript" src="js/grid/jquery-1.9.1.js"></script>
<link type="text/css" href="js/grid/scripts/jquery-ui.css" rel="stylesheet">
<script type="text/javascript" src="js/grid/scripts/jquery-ui.min.js"></script>
<title>Visa Expire Date</title>

HERE;


print <<<HERE
</script>
</head>
<body >
HERE;


$query = "select ifnull(max(nbrOfDays),0)
		from notification_type
		left join ".$_SESSION['dbLabel'].".Notification_Access as Notification_Access on Notification_Access.Notification_Name= notification_type.Notification_Name
		where ifnull(menubased,0)=1 and notification_type.notification_name='Staff_VisaExpireDate' ;"; 
$result = mysql_query($query,$connection);
$rw=mysql_fetch_row($result);

$nbrOfDays = $rw[0];
if($nbrOfDays == '') $nbrOfDays = 0;

$query = "select Staff_code, date_format(Staff_visaExpDate,'%d-%m-%Y')
		from staff 
		where date_format(Staff_visaExpDate,'%Y-%m-%d') = DATE_ADD(date_format(now(),'%Y-%m-%d'),INTERVAL $nbrOfDays DAY) ; ";
//echo $query;
$res = mysql_query($query,$connection);
$i=0;
$numrow = mysql_num_rows($res);
if($numrow>0){
$Body = 'Visa Expires For: <br> ';
while($row = mysql_fetch_row($res)){
	if($i==0) $Body = $Body." ".$row[0];
	else $Body = $Body.", ".$row[0];
	$expdate = $row[1];
	$i++;
}	
$Body = "<table><tr><td>".$Body." <br> on ".$expdate."</td></tr></table>";

$Subject = "Expired Visa List";
	error_reporting(E_ALL);
	

	require 'PHPMailer-master/class.phpmailer.php';
	
	//// Configuration
	$query_mailsetup="select hostname,mailagent,port,SenderEmail,ReceiverEmail,password from configurationemail";
	$result_mailsetup = mysql_query($query_mailsetup, $connection);
	$row_setup=mysql_fetch_row($result_mailsetup); 
	$hostname=$row_setup[0];
	
	
	$sender=$row_setup[3];
	$pswd = $row_setup[5];
	$recipients = $row_setup[4]; 
	
	$mail = new PHPMailer;
	$mail->IsSMTP();                                      // Set mailer to use SMTP
	$mail->Host = $hostname; //'smtp.gmail.com';  // Specify main and backup server
	$mail->SMTPAuth = true;                               // Enable SMTP authentication
	$mail->Username = $sender;                            // SMTP username
	$mail->Password = $pswd;                           // SMTP password
	//$mail->SMTPSecure = 'ssl';                            // Enable encryption, 'ssl' also accepted
	$mail->From = $sender;
	$mail->WordWrap = 50;                                 // Set word wrap to 50 characters
	$mail->Body    =  $Body;
	$mail->IsHTML(true);                                  // Set email format to HTML
	$mail->Subject = $Subject;
	$mail->SMTPDebug = 1;
	$mail->Port = $row_setup[2]; 
	
	$mail->AddAddress($recipients, ''); 	
		
	if(!$mail->Send()) { 
	  $error = $mail->ErrorInfo;
	
      $query="insert into mailogs(ticket_date,errormsg,sender,recipient,mailbody) values (now(),'$error','$sender','$recipients','$Body')";
	 $result = mysql_query($query, $connection); 
	 
	}
}


print <<<HERE

</body>

HERE;

?>