<?php
//use Twilio\Rest\Client;
//file_get_c('application/config/email.php');
require('twiliocallsms.php');

require("vendor/phpmailer/phpmailer/class.phpmailer.php");
require("vendor/phpmailer/phpmailer/class.smtp.php"); 
require('config.php');
require('application/language/english/translations_lang.php');
//require 'twilio-php-main/src/Twilio/autoload.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_reminder2='ON'")->fetchAll(PDO::FETCH_ASSOC);

//echo 'bro';
foreach($services as $s){
    //echo 'here';
date_default_timezone_set('America/Toronto');
    $yesterday = date("Y-m-d", strtotime('yesterday'));
    $result = $linkID->query("SELECT CONCAT(DAYNAME(start_datetime),' ',MONTHNAME(start_datetime),' ', DAY(start_datetime)) as concatday,
    TIME_FORMAT(start_datetime, '%h:%i%p') as time,
    CONCAT(DAYNAME(end_datetime),' ',MONTHNAME(end_datetime),' ', DAY(end_datetime)) as concatendday,
   TIME_FORMAT(end_datetime, '%h:%i%p') 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.is_unavailable=0 AND ea_appointments.book_datetime BETWEEN ".'"'.$yesterday.' '.'00:00:00'.'"'." AND ".'"'.$yesterday.' '.'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);
//print_r($result);
//echo 'bro1';
    foreach($result as $r){
        $checkalreadysent = $linkID->query("SELECT * FROM appointment_reminders_two WHERE appointment_id=".$r['apptid'])->fetchAll(PDO::FETCH_ASSOC);

        if($checkalreadysent[0]['appointment_id'] == ''){

if($r['emailperson'] !== ''){

        $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 = Config::EMAIL;
		$mail->Password = Config::EMAIL_PASSWORD;
		#$mail->Host     = "SMTP HOST";
		#$mail->Mailer   = "smtp";
		$mail->SetFrom(Config::EMAIL, $company_name[0]['value']);
		$mail->AddAddress($r['emailperson']);
		$mail->Subject = stripslashes($s['email_header2']); 
		$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", "frontendcolor");
		$replacements = array($company_link[0]['value'], $company_name[0]['value'], $r['servicename'], stripslashes($s['email_description2']), $r['concatday'], $r['time'], $r['endtime'], $r['apptperson'], $r['emailperson'], $r['phoneperson'], stripslashes($s['email_form']), stripslashes($s['email_form_description']), stripslashes($s['email_form_name']), stripslashes($s['email_form_two']), stripslashes($s['email_form_two_description']), stripslashes($s['email_form_two_name']), stripslashes($s['email_form_three']), stripslashes($s['email_form_three_description']), stripslashes($s['email_form_three_name']), Config::FRONTEND_COLOR); 
		$htmlone = str_replace($tobereplaced, $replacements, $html); 
		$mail->MsgHTML($htmlone);
		$mail->IsHTML(true);	
if(!$mail->Send()) {
        echo "Mailer Error: " . $mail->ErrorInfo;
     } else {	  	     
//	echo 'dude';
$today = date("Y-m-d h:i:sa"); 
$dataone = [
                'datesent' => $today, 
                'pharmacy' => Config::COMPANY_FIRST_NAME.' '.Config::COMPANY_LAST_NAME,
                'apptid' => $r['apptid'],
                ];
        $sqlone = "INSERT INTO `emails_sent`(`date_sent`, `pharmacy`, `apptid`, `reason`) VALUES (:datesent, :pharmacy, :apptid, 'Email 2 Reminder')";
        $stmtone= $linkID->prepare($sqlone);
        $stmtone->execute($dataone);
//echo 'bro';
}
$mail->ClearAddresses();
		    $mail->ClearAllRecipients();
}
if(Config::SMS_ON_OFF == 'ON'){
if($lang['smsonoff'] === 'ON'){

// Use the REST API Client to make requests to the Twilio REST API
if($r['phoneperson'] !== ''){
$textmessage="You booked an appointment yesterday at"." ".$company_name[0]['value']." ". "on". " ".$r['concatday']." "."at"." ".$r['time']. " "."for"." ". $r['apptperson']. " ". stripslashes($s['text_message_one']);
// Your Account SID and Auth Token from twilio.com/console
$sid = Config::SID;
$token = Config::AUTH;
/*$client = new Client($sid, $token);

// Use the client to do fun stuff like send text messages!
$client->messages->create(
    // the number you'd like to send the message to
    $r['phoneperson'],
    [
        // A Twilio phone number you purchased at twilio.com/console
        'from' => Config::TWILNUM,
        // the body of the text message you'd like to send
        'body' => $textmessage
    ]
);*/
    sendsms1($sid, $token, Config::TWILNUM, $r['phoneperson'], $textmessage);


    $today = date("Y-m-d h:i:sa");
$datatwo = [
                'datesent' => $today, 
                'pharmacy' => Config::COMPANY_FIRST_NAME.' '.Config::COMPANY_LAST_NAME,
                'apptid' => $r['apptid'],
                'lengthoft' => strlen($textmessage),

                ];
        $sqltwo = "INSERT INTO `texts_sent`(`date_sent`, `pharmacy`, `apptid`, `reason`, `lengthoftext`) VALUES (:datesent, :pharmacy, :apptid, 'Text 2 Reminder', :lengthoft)";
        $stmttwo= $linkID->prepare($sqltwo);
        $stmttwo->execute($datatwo);
}
}
}
//echo 'bro3';
        $today = date("Y/m/d"); 

        $data = [
                'id' => $r['apptid'],
                'datesent' => $today, 
                'messageheader' => stripslashes($s['email_header2']),
                'messagedescription' => stripslashes($s['email_description2']),
                ];

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


                            }
                    }
}

