<?php
use Twilio\Rest\Client;
//file_get_c('application/config/email.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 ea_services WHERE reminder_on_off_two='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, 
    company.name as companyname, 
    company.hash_id as companyhash,
    ea_appointments.hash as hashid,
    ea_users.first_name, 
    CONCAT(ea_users.first_name, ' ', ea_users.last_name) as apptperson,
        CONCAT(ea_users.address, ' ', ea_users.city, ' ', ea_users.state, ' ', ea_users.zip_code) as locationaddress,

    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 
        company 
        on 
        ea_appointments.id_company=company.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['id'].
        " 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';

    $location =  $linkID->query("select ea_users.first_name as locationname, CONCAT(ea_users.address, ' ', ea_users.city, ' ', ea_users.state, ' ', ea_users.zip_code) as locationaddress from ea_services_providers inner join ea_users on ea_services_providers.id_users=ea_users.id where ea_services_providers.id_services=".$s['id'])->fetchAll(PDO::FETCH_ASSOC);

    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'] == ''){
try{
            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_reminder_subject_two']);
                $html = file_get_contents('email_template.php');
                $tobereplaced = array("companylinkbro", "companynamebro", "servicenamebro", "emaildescriptionbro", "concatdaybro", "startbrotime", "endbrotime", "apptpersonbro", "emailpersonbro", "phonepersonbro", "locationnamebro", "companynameappt", "frontendcolor", "locationaddress", "appointmenteditdeletelink");
                $replacements = array($company_link[0]['value'], $company_name[0]['value'], $r['servicename'], stripslashes($s['email_reminder_description_two']), $r['concatday'], $r['time'], $r['endtime'], $r['apptperson'], $r['emailperson'], $r['phoneperson'], $location[0]['locationname'], $r['companyname'], Config::FRONTEND_COLOR, $location[0]['locationaddress'], CONFIG::BASE_URL.'/index.php/appointments/index/' . $r['hashid'].'?company='.$r['companyhash']);
                $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();
            }
        }catch(Exception $e) {
  echo 'Message: ' .$e->getMessage();
}
            if(Config::SMS_ON_OFF == 'ON'){
                if($s['smsonoff'] === 'ON'){
try{
// Use the REST API Client to make requests to the Twilio REST API
                    if($r['phoneperson'] !== ''){
                        $textmessage="You booked an appointment yesterday at"." ".$r['companyname']." at ".$location[0]['locationname']." at ".$r['servicename'] ." on ".$r['concatday']." "."at"." ".$r['time']. " "."for"." ". $r['apptperson']. " ". stripslashes($s['text_reminder_description_two']);
// 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
                            ]
                        );

                        $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);
                    }
                }catch(Exception $e) {
  echo 'Message: ' .$e->getMessage();
}
                }
            }
//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';




        }
    }
}

