<?php
//file_get_c('application/config/email.php'); 
require("vendor/phpmailer/phpmailer/class.phpmailer.php");
require("vendor/phpmailer/phpmailer/class.smtp.php"); 
require('config.php');

$linkID = new PDO('mysql:host='.Config::DB_HOST.';dbname='.Config::DB_NAME, Config::DB_USERNAME, Config::DB_PASSWORD);
$linkID->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting ERRMODE

$company_name = $linkID->query("SELECT * FROM ea_settings WHERE ea_settings.id=14")->fetchAll(PDO::FETCH_ASSOC);
$company_email = $linkID->query("SELECT * FROM ea_settings WHERE ea_settings.id=15")->fetchAll(PDO::FETCH_ASSOC);
$company_link = $linkID->query("SELECT * FROM ea_settings WHERE ea_settings.id=16")->fetchAll(PDO::FETCH_ASSOC);
$services = $linkID->query("SELECT * FROM millie_service WHERE millie_service.send_reminder='ON'")->fetchAll(PDO::FETCH_ASSOC);

//echo 'bro';
foreach($services as $s){
    //echo 'here';
date_default_timezone_set('America/Toronto');
    $tomorrow = date("Y-m-d", strtotime('tomorrow'));
    $result = $linkID->query("SELECT CONCAT(DAYNAME(start_datetime),' ',MONTHNAME(start_datetime),' ', DAY(start_datetime)) as concatday,
    TIME(start_datetime) as time,
    CONCAT(DAYNAME(end_datetime),' ',MONTHNAME(end_datetime),' ', DAY(end_datetime)) as concatendday,
    TIME(end_datetime) as endtime,
    CONCAT(DAYNAME(book_datetime),' ',MONTHNAME(book_datetime),' ', DAY(book_datetime)) as concatbookday,
    TIME(end_datetime) as booktime,
    YEAR(start_datetime) as year, 
    MONTH(start_datetime) as monthnumber, 
    MONTHNAME(start_datetime) as monthday, 
    DAY(start_datetime) as daynumber, 
    DAYNAME(start_datetime) as dayname, 
    HOUR(start_datetime) as hour, 
    MINUTE( start_datetime) as minute, 
    COUNT(start_datetime) as countbookdate, 
    sum(ea_services.price) as totalprice, 
    ea_appointments.book_datetime, 
    ea_appointments.start_datetime, ea_appointments.end_datetime, 
    ea_users.notes as usernotes, 
    ea_appointments.notes as apptnotes, 
    ea_appointments.id as apptid,
    ea_services.name as servicename, 
    ea_users.first_name, 
    CONCAT(ea_users.first_name, ' ', ea_users.last_name) as apptperson,
    ea_users.last_name,
    ea_users.email as emailperson, 
    ea_users.phone_number as phoneperson FROM `ea_appointments`
    inner JOIN 
        ea_services 
        on 
        ea_appointments.id_services=ea_services.id 
    inner join 
        ea_users 
        on 
        ea_appointments.id_users_customer=ea_users.id 
    
    WHERE ea_appointments.start_datetime BETWEEN ".'"'.$tomorrow.' '.'00:00:00'.'"'." AND ".'"'.$tomorrow.' '.'23:59:59'.'"'." AND ea_services.id=".$s['service'].
    " GROUP BY YEAR(start_datetime), MONTH(start_datetime), MONTHNAME(start_datetime), DAY(start_datetime), DAYNAME(start_datetime), concatday, time, HOUR(start_datetime), MINUTE(book_datetime), ea_services.name, ea_appointments.book_datetime,ea_appointments.start_datetime,ea_appointments.end_datetime, ea_users.first_name, ea_users.last_name, ea_appointments.id, ea_users.email, ea_users.phone_number, ea_users.notes, ea_appointments.notes
     ORDER BY YEAR(start_datetime), MONTH(start_datetime), MONTHNAME(start_datetime), DAY(start_datetime), DAYNAME(start_datetime), HOUR(start_datetime), MINUTE(book_datetime), ea_users.last_name, ea_users.first_name 
    ")->fetchAll(PDO::FETCH_ASSOC);


//echo 'bro1';
    foreach($result as $r){

        $mail = new \PHPMailer;
        $mail->IsSMTP(); // enable SMTP
        $mail->SMTPOptions = array(
            'ssl' => array(
            'verify_peer' => false,
            'verify_peer_name' => false,
            'allow_self_signed' => true
                            )
                                    );
		$mail->SMTPDebug = 0;
		$mail->SMTPAuth = TRUE;
		$mail->SMTPSecure = 'tls'; // tls or ssl
		#$mail->Port     = "SMTP port";
		$mail->Username = 'optimumpharmacy@myappts.ca';
		$mail->Password = 'tiNN5c;5b?u_';
		#$mail->Host     = "SMTP HOST";
		#$mail->Mailer   = "smtp";
		$mail->SetFrom('optimumpharmacy@myappts.ca', $company_name[0]['value']);
		$mail->AddAddress($r['emailperson']);
		$mail->Subject = $s['email_header']; 
		$html = file_get_contents('email_template.php');
		$tobereplaced = array("companylinkbro", "companynamebro", "servicenamebro", "emaildescriptionbro", "concatdaybro", "startbrotime", "endbrotime", "apptpersonbro", "emailpersonbro", "phonepersonbro", "email_formbro", "email_form_descriptionbro", "email_form_namebro", "email_form_twobro", "email_form_two_descriptionbro", "email_form_two_namebro", "email_form_threebro", "email_form_three_descriptionbro", "email_form_three_namebro");
		$replacements = array($company_link[0]['value'], $company_name[0]['value'], $r['servicename'], $s['email_description'], $r['concatday'], $r['time'], $r['endtime'], $r['apptperson'], $r['emailperson'], $r['phoneperson'], $s['email_form'], $s['email_form_description'], $s['email_form_name'], $s['email_form_two'], $s['email_form_two_description'], $s['email_form_two_name'], $s['email_form_three'], $s['email_form_three_description'], $s['email_form_three_name']); 
		$htmlone = str_replace($tobereplaced, $replacements, $html); 
		$mail->MsgHTML($htmlone);
		$mail->IsHTML(true);	
	    $mail->Send();

//echo 'bro3';
        $today = date("Y/m/d"); 

        $data = [
                'id' => $r['apptid'],
                'datesent' => $today, 
                'messageheader' => $s['email_header'],
                'messagedescription' => $s['email_description'],
                ];

        $sql = "INSERT INTO appointment_reminders_one (appointment_id, date_sent, email_header, email_description, status) VALUES (:id, :datesent, :messageheader, :messagedescription, 'sent')";
        $stmt= $linkID->prepare($sql);
        $stmt->execute($data);
        //echo 'done';


                            }
                    }


