<?php 
error_reporting(E_ALL);
//include "dbconfig.php";
    
	include 'phpmailer/PHPMailerAutoload.php';		
    date_default_timezone_set ("Europe/London");
	
 $mot_reminder_date=date('Y-m-d',strtotime("+5 MONTHS")); 
//echo  $mot_reminder_date;
//exit;
 $sql="SELECT employees.id,employees.first_name as driver,employees.email as driver_email,employees.tfl_expiry,employees.pco_expiry,employees.email_status,employees.email_status1,employees.email_status2,employees.email_status3,vehicles.mot_date,vehicles.insurance_date  
FROM employees 
LEFT JOIN vehicles on employees.id=vehicles.driver_id";// WHERE mot_date='".$mot_reminder_date."'";

         function send_driver_email($to,$message,$driver_name)
		 {
		   $driver_email=$to;
		   $driver_message=$message;
		  $driver_name=$driver_name;
		$mail2 = new PHPMailer;
		$mail2->setFrom('info@wingschauffeurs.com', 'Wings Chauffeurs Ltd.');
		$mail2->IsHTML(true);
		$mail2->addAddress($driver_email, $driver_name);
		$mail2->AddCC('info@wingschauffeurs.com', 'Wings');
		$mail2->Subject  = 'Vehicle Expiry Reminder';
		$mail2->Body     = $driver_message;
		if(!$mail2->send()){
			echo '<h3>Email was not sent</h3><br><br><br>';
			}else{
				echo 'Mail was sent<br><br><br>';
		    }
		 } 
		 
		$result=mysqli_query($con,$sql);
		$mot_db_date='';
		$driver_email='';
		$driver_name='';
		$driver_message='';
		$driver_id='';
	    while($db=mysqli_fetch_assoc($result))
		{
		
		  $mot_db_date=$db['mot_date'];
		   $insurance_db_date=$db['insurance_date'];
           $tfl_expiry_db_date=$db['tfl_expiry'];
           $pco_expiry_db_date=$db['pco_expiry'];
         $email_status=$db['email_status'];
		 $email_status1=$db['email_status1'];
		 $email_status2=$db['email_status2'];
		 $email_status3=$db['email_status3'];
		  $driver_email=$db['driver_email'];
		 
		 $driver_id=$db['id'];
		  //if driver does not hhave email then send reminder to manager
		  if($driver_email==''){$driver_email='info@wingschauffeurs.com';}
		 
		  $driver_name=$db['driver'];
		  
		 $expiry_date = new DateTime($db['mot_date']); 
         $today = new DateTime();
         $no_of_days = $today->diff($expiry_date)->format("%a"); 
			
		 if($no_of_days<8 && $email_status=="0")
		   {
			   $driver_message= '    
		 <p> Dear '.$driver_name.' </p><h3  style="color:#E3060A"> This is your MOT Reminder</h3>
		 <h4>Your MOT will expire on '.date("d/m/Y",strtotime($mot_db_date)).'</h4>
		 <p>Please renew your MOT</p>
		 <p>Wings Chauffeurs Ltd.</p>
		 ';
		 echo $driver_message."<br>";
			 $send_mail=send_driver_email($driver_email,$driver_message,$driver_name);
			 
			  $sql_up="UPDATE employees SET email_status='1' WHERE id=".$driver_id;	
		 mysqli_query($con,$sql_up);
			}		
			
			 $expiry_date1 = new DateTime($db['insurance_date']); 
         $today = new DateTime();
         $no_of_days1 = $today->diff($expiry_date1)->format("%a"); 
			 if($no_of_days1<8 && $email_status1=="0")
		   {
			   $driver_message1= '    
		 <p> Dear '.$driver_name.' </p><h3  style="color:#E3060A"> This is your Insurance Reminder</h3>
		 <h4>Your Insurance will expire on '.date("d/m/Y",strtotime($insurance_db_date)).'</h4>
		 <p>Please renew your Insurance</p>
		 <p>Wings Chauffeurs Ltd.</p>
		 ';
		 echo $driver_message1."<br>";
			 $send_mail=send_driver_email($driver_email,$driver_message1,$driver_name);
			 
			  $sql_up="UPDATE employees SET email_status1='1' WHERE id=".$driver_id;	
		 mysqli_query($con,$sql_up);
			}	
			
			
			 $expiry_date2 = new DateTime($db['tfl_expiry']); 
         $today = new DateTime();
         $no_of_days2 = $today->diff($expiry_date2)->format("%a"); 
			 if($no_of_days2<8 && $email_status2=="0")
		   {
			   $driver_message2= '    
		 <p> Dear '.$driver_name.' </p><h3  style="color:#E3060A"> This is your PCO Plate Expiry Date Reminder</h3>
		 <h4>Your PCO Plate will expire on '.date("d/m/Y",strtotime($tfl_expiry_db_date)).'</h4>
		 <p>Please renew your PCO Plate</p>
		 <p>Wings Chauffeurs Ltd.</p>
		 ';
		 echo $driver_message2."<br>";
			 $send_mail=send_driver_email($driver_email,$driver_message2,$driver_name);
			 
			  $sql_up="UPDATE employees SET email_status2='1' WHERE id=".$driver_id;	
		 mysqli_query($con,$sql_up);
			}	
			
			 $expiry_date3 = new DateTime($db['pco_expiry']); 
         $today = new DateTime();
         $no_of_days3 = $today->diff($expiry_date3)->format("%a"); 
			 if($no_of_days3<8 && $email_status3=="0")
		   {
			   $driver_message3= '    
		 <p> Dear '.$driver_name.' </p><h3  style="color:#E3060A"> This is your PCO Expiry Date Reminder</h3>
		 <h4>Your PCO will expire on '.date("d/m/Y",strtotime($pco_expiry_db_date)).'</h4>
		 <p>Please renew your PCO</p>
		 <p>Wings Chauffeurs Ltd.</p>
		 ';
		 echo $driver_message3."<br>";
			 $send_mail=send_driver_email($driver_email,$driver_message3,$driver_name);
			 
			  $sql_up="UPDATE employees SET email_status3='1' WHERE id=".$driver_id;	
		 mysqli_query($con,$sql_up);
			}	
		
		  
		  
		  	
		
			
			
		}

 
	

	
			