<?php
$hostname = "localhost";
$username = "manase";
$password = "2014manase";

$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");

$selected = mysql_select_db("man2014",$dbhandle)
  or die("Could not select man2014");

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


$m = date("m");	
$check_date = date("Y-m-31");

if ($m=='02') {
$end = date("Y-m-28");
}

if ( ($m=='04') OR ($m=='06')  OR ($m=='09')  OR ($m=='11') ) {
$check_date = date("Y-m-30");
}
$check_date_display = date("M-Y", strtotime($check_date));

$table_name2 = "man_vehicle";
$road_license_query = "SELECT registration_no, make, model, department, road_license_date, clock_reading FROM $table_name2 WHERE road_license_date < '".$check_date."' AND road_license_date <> '0000-00-00' AND disposal_date = '2030-01-01'" ;
$road_license_result = mySQL_query($road_license_query);
$road_license_num = mysql_num_rows($road_license_result);

$table_name2 = "man_vehicle";
$ba_license_query = "SELECT registration_no, make, model, department, ba_license_date, clock_reading FROM $table_name2 WHERE ba_license_date < '".$check_date."' AND ba_license_date <> '0000-00-00' AND disposal_date = '2030-01-01'" ;
$ba_license_result = mySQL_query($ba_license_query);
$ba_license_num = mysql_num_rows($ba_license_result);

$table_name2 = "man_vehicle";
$insurance_query = "SELECT registration_no, make, model, department, renewal_date, insurance, clock_reading FROM $table_name2 WHERE renewal_date < '".$check_date."' AND renewal_date <> '0000-00-00' AND disposal_date = '2030-01-01'" ; 
$insurance_result = mySQL_query($insurance_query);
$insurance_num = mysql_num_rows($insurance_result);


$road_license_content='';
$i=0;
while ($i<$road_license_num) {
	$a0 = mysql_result($road_license_result, $i, "registration_no");
	$a1 = mysql_result($road_license_result, $i, "make")." ".mysql_result($road_license_result, $i, "model");
	$a2 = mysql_result($road_license_result, $i, "department");
	$a3 = date("M-Y", strtotime(mysql_result($road_license_result, $i, "road_license_date")));
	

$road_license_content = $road_license_content.'<tr><td>'.$a0.'</td><td>'.$a1.'</td><td>'.$a2.'</td><td align=center>'.$a3.'</td></tr>';
$i++;
}

$ba_license_content=''; 
$i=0;
while ($i<$ba_license_num) {

	$b0 = mysql_result($ba_license_result, $i, "registration_no");
	$b1 = mysql_result($ba_license_result, $i, "make")." ".mysql_result($ba_license_result, $i, "model");
	$b2 = mysql_result($ba_license_result, $i, "department");
	$b3 = date("M-Y", strtotime(mysql_result($ba_license_result, $i, "ba_license_date")));
	
	
$ba_license_content = $ba_license_content.'<tr><td>'.$b0.'</td><td>'.$b1.'</td><td>'.$b2.'</td><td>'.$b3.'</td></tr>';

  $i++;
}
 
$insurance_content = '';
$i=0;
while ($i<$insurance_num) {

	$c0 = mysql_result($insurance_result, $i, "registration_no");
	$c1 = mysql_result($insurance_result, $i, "make")." ".mysql_result($insurance_result, $i, "model");
	$c2 = mysql_result($insurance_result, $i, "department");
	$c3 = date("d-m-Y", strtotime(mysql_result($insurance_result, $i, "renewal_date")));
	$c5 = mysql_result($insurance_result, $i, "insurance");
	
	if ($c5=='') {$c5='--';}

$insurance_content = $insurance_content.'<tr><td>'.$c0.'</td><td>'.$c1.'</td><td>'.$c2.'</td><td align=center>'.$c3.'</td><td>'.$c5.'</td></tr>';

  $i++;
}

$table_name = "man_reports";
	$report_query = "SELECT user_name, send_report, send_email FROM $table_name WHERE report_name = 'license expiry' ";
	$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");
		
		
		$report_text = 'Road licenses for the following vehicles need to be renewed before the end of: '.$check_date_display.'.<br><br><table border=1; style=border-collapse:collapse; font-size:10px;><tr><td width=125>Registration no</td><td width=150>Make</td><td width=125>Department</td><td width=125>Renew before</td></tr>'.$road_license_content.'</table><br><br>BA licenses for the following vehicles need to be renewed before the end of: '.$check_date_display.'.<br><br><table border=1; style=border-collapse:collapse; font-size:10px;><tr><td width=125>Registration no</td><td width=150>Make</td><td width=125>Department</td><td width=125>Renew before</td></tr>'.$ba_license_content.'</table><br><br>Vehicle insurances for the following vehicles need to be renewed before the end of: '.$check_date_display.'.<br><br><table border=1; style=border-collapse:collapse; font-size:10px;><tr><td width=125>Registration no</td><td width=150>Make</td><td width=125>Department</td><td width=125>Renew before</td><td width=125>Insured with</td></tr>'.$insurance_content.'</table>';	


$table_name = "man_saved_transport_reports";
$result = mysql_query("INSERT INTO $table_name (department, saved_report_name, saved_report_date, saved_report_text, user_name ) VALUES ('Transport', 'License & Insurance expiry', '".$entry_date."', '".$report_text."', '".$user_name."' ") ;

		
				
		if ($send_email=='yes') {
			$table_name = "man_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 = 'Vehicle license & insurance renewal';
			$message = '
<body style=font-face=Calibri; font-size=10px;>Road licenses for the following vehicles need to be renewed before the end of: '.$check_date_display.'.<br><br>
<table border=1 style=border-collapse:collapse; font-size:10px;>
<tr>
<td width=125>Registration no</td>
<td width=150>Make</td><td width=125>Department</td>
<td width=125>Renew before</td>
</tr>'
.$road_license_content.
'</table>
<br><br>
BA licenses for the following vehicles need to be renewed before the end of: '.$check_date_display.'.<br><br>
<table border=1 style=border-collapse:collapse; font-size:10px;>
<tr>
<td width=125>Registration no</td>
<td width=150>Make</td>
<td width=125>Department</td>
<td width=125>Renew before</td>
</tr>'
.$ba_license_content.
'</table>
<br><br>
Vehicle insurances for the following vehicles need to be renewed before the end of: '.$check_date_display.'.<br><br>
<table border=1 style=border-collapse:collapse; font-size:10px;>
<tr>
<td width=125>Registration no</td>
<td width=150>Make</td>
<td width=125>Department</td>
<td width=125>Renew before</td>
<td width=125>Insured with</td></tr>'
.$insurance_content.
'</table>
<br><br>
For more info, login at <a href=man.bwcustomized.com/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);
}

			
?>
