<?php

include("config.inc.php");
require 'PHPMailer-master/class.phpmailer.php';

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

 mysql_query("SET NAMES 'utf8'"); 
 mysql_query('SET CHARACTER SET utf8'); 
 mysql_set_charset('utf8', $connection);

$dbname = "hobeika_acc2018";

$query = "select reminderdt_id, reminder_sendEmail, user_account, reminderdt_userCode, concat('Reminder ',reminder_name), reminder_desc, reminder_Date
			from $dbname.users_reminderdt
			inner join $dbname.users_reminder on users_reminder.reminder_id = users_reminderdt.reminder_id
			where reminder_period = 'o'  and ifnull(notification_sent,0) = 0 and ifnull(reminderdt_userChecked,0) = 1
			and DATE_SUB(reminder_Date, interval reminder_days Day) <= now() 
		union select reminderdt_id, reminder_sendEmail, user_account, reminderdt_userCode, concat('Reminder ',reminder_name), reminder_desc, reminder_Date
			from $dbname.users_reminderdt
			inner join $dbname.users_reminder on users_reminder.reminder_id = users_reminderdt.reminder_id
			where reminder_period = 'd' and ifnull(notification_sent,0) = 0 and ifnull(reminderdt_userChecked,0) = 1
			and date_format(reminder_Date,'%H:%i') <= date_format(now(),'%H:%i')	
		union
			select reminderdt_id, reminder_sendEmail, user_account, reminderdt_userCode, concat('Reminder ',reminder_name), reminder_desc, reminder_Date
			from $dbname.users_reminderdt
			inner join $dbname.users_reminder on users_reminder.reminder_id = users_reminderdt.reminder_id
			where reminder_period = 'w' and ifnull(notification_sent,0) = 0 and ifnull(reminderdt_userChecked,0) = 1
			and DAYOFWEEK(reminder_Date) = DAYOFWEEK(now()) and date_format(reminder_Date,'%H:%i') <= date_format(now(),'%H:%i')	
		union
			select reminderdt_id, reminder_sendEmail, user_account, reminderdt_userCode, concat('Reminder ',reminder_name), reminder_desc, reminder_Date
			from $dbname.users_reminderdt
			inner join $dbname.users_reminder on users_reminder.reminder_id = users_reminderdt.reminder_id
			where reminder_period = 'm' and ifnull(notification_sent,0) = 0 and ifnull(reminderdt_userChecked,0) = 1
			and concat(date_format(CURDATE(),'%Y-%m-'),date_format(DATE_SUB(reminder_Date, interval reminder_days Day),'%d')) = CURDATE()
			and date_format(reminder_Date,'%H:%i') <= date_format(now(),'%H:%i')
		union
			select reminderdt_id, reminder_sendEmail, user_account, reminderdt_userCode, concat('Reminder ',reminder_name), reminder_desc, reminder_Date
			from $dbname.users_reminderdt
			inner join $dbname.users_reminder on users_reminder.reminder_id = users_reminderdt.reminder_id
			where reminder_period = 'y' and ifnull(notification_sent,0) = 0 and ifnull(reminderdt_userChecked,0) = 1
			and concat(year(CURDATE()),'-',date_format(DATE_SUB(reminder_Date, interval reminder_days Day),'%m-%d')) = CURDATE()
			and date_format(reminder_Date,'%H:%i') <= date_format(now(),'%H:%i')		
		order by reminder_Date ";  
$result = mysql_query($query,$connection);
while ($row = mysql_fetch_array($result)){

	$queryNotification = "call $dbname.sendNotification('User_Reminder','$row[2]',0, 0, '$row[0]', '')";//echo $queryNotification;exit;
	$resNotification = mysql_query($queryNotification,$connection);
	if ($row[1] == 1){
		$emailQuery = "select group_concat(email_address SEPARATOR ';' ) from ".$config['dbName_log'].".users_emails where user_account='$row[3]' ";
		$emailRes	= mysql_query($emailQuery, $connection);
		$emailRow   = mysql_fetch_row($emailRes);
		$emails = $emailRow[0];
		$Subject = "Reminder";
		$Body = "<table><tr><td>$row[2]</td></tr></table>";
		sendmail($Subject,$Body,$emails,$dbname,$connection);
	}
	
	$queryup = "update $dbname.users_reminderdt set notification_sent = 1 where reminderdt_id = '$row[0]' ";
	$resultup = mysql_query($queryup, $connection);
}


function sendmail($Subject,$Body,$emails,$dbname,$connection){
	$recipients="";
	   error_reporting(E_ALL);
	   error_reporting(E_ALL);
   
	   //// Configuration
	   $query_mailsetup="select hostname,mailagent,port,SenderEmail,ReceiverEmail,password from $dbname.configurationemail";
	   $result_mailsetup = mysql_query($query_mailsetup, $connection);
	   $row_setup=mysql_fetch_row($result_mailsetup); 
	   $hostname=$row_setup[0];
	   $port=$row_setup[2];
	   $sender=$row_setup[3];
	   $pswd = $row_setup[5];
	   
	   $mail = new PHPMailer;
	   $mail->IsSMTP();                                      // Set mailer to use SMTP
	   $mail->Host = $hostname; 							// Specify main and backup server
	   $mail->SMTPAuth = true;                               // Enable SMTP authentication
	   $mail->Username = $sender;                            // SMTP username
	   $mail->Password = $pswd;                           // SMTP password
	   $mail->SMTPSecure = 'tls';                            // Enable encryption, 'ssl' also accepted
	   $mail->From = $sender;
	   $mail->setFrom($sender, $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]; 
	   //exit;
	   
	   
		   
		   $emailtechs = explode(';',$emails);
		   foreach($emailtechs as $emailtech){
		   $mail->AddAddress($emailtech, '');  // Add a recipient
		   $recipients = $recipients." $emailtech,";
			   
		   }
	   
	   if(!$mail->Send()) { 
		 $error = $mail->ErrorInfo;
		   return false;	
			  
	   }
   
	   
	   return true;
   }
?>