<?php 

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
require 'phpmailer/src/Exception.php';
require 'phpmailer/src/PHPMailer.php';
require 'phpmailer/src/SMTP.php';

include('dbconnect.php');
//date_default_timezone_set('Asia/Manila');
/* $date_now = date('m/d/Y'); */
/* $day_date = date('d');

$date = date('m').'/'.$day_date.'/'.date('y'); */

function psttomnl($date, $time){
    
    $convert = new DateTime($date.' '.$time, new DateTimeZone('America/Los_Angeles'));
    
    
    $convert->setTimezone(new DateTimeZone('Asia/Manila'));
    
    $datetime = $convert->format('m/d/Y h:i A');
    
    
    return ($datetime);
    
    
    
    
}


$date = date('m/d/Y');
$sched_date = date('Y-m-d');
$query = "SELECT firstname,surname,work_from_home, empinfo.empid as emp_id FROM empinfo JOIN empdetails ON empinfo.empid = empdetails.detailid WHERE team IN ('IT/Data Analytics','Dev Ops') AND empdetails.status= 'Active'   ";

$sched_sql = $conn->query($query);
$fp = fopen('lilo.csv', 'w');
fputcsv($fp,['Date','Employee ID','Name','WFH/Office','Work Status','Time In', 'Time Out']);
while($row = $sched_sql->fetch_assoc()){


    $lilo =$conn->query( "SELECT * FROM tbl_login WHERE date='".$date."' and empid='".$row['emp_id']."' limit 1 ");
    

    $sched = $conn->query("SELECT * FROM schedule WHERE date='".$sched_date."' AND empid='".$row['emp_id']."' limit 1 ");

    //echo "SELECT * FROM schedule WHERE date='".$sched_date."' AND empid='".$sched_row['emp_id']."' limit 1 ";

    
    $lilo_row = $lilo->fetch_assoc();

    if($lilo->num_rows > 0){
        $work_status = 'Present';
        $in = date('h:i a', strtotime($lilo_row['timein']));
        $out = $lilo_row['timeout'] == '' ? '' : date('h:i a', strtotime($lilo_row['timeout']));
    }else{
    

        $sched_row = $sched->fetch_assoc();
        

        if($sched_row['pststarttime'] != 'OFF'){
            $date_mnl = psttomnl($sched_date, $sched_row['pststarttime']);
            $mnl_date = date('Y-m-d', strtotime($date_mnl));
            $leave = $conn->query("SELECT * FROM schedule WHERE date='".$mnl_date."' AND empid='".$row['emp_id']."' limit 1 ");
            $leave_row = $leave->fetch_assoc();

            if($leave_row['idl'] == null || $leave_row['idl'] == ''){
                $work_status= 'Absent';

            }else{
                $work_status = 'Leave';
            }
            
            

        }else{

            $work_status='OFF';
        }
      

        $in = '-';
        $out ='-';
    }


    

    $wfh = $row['work_from_home'] == 0 ? 'Office' :  'Work From Home';

    
    fputcsv($fp, [$date,$row['emp_id'], $row['firstname'].' '.$row['surname'],$wfh,$work_status,$in, $out ]);




    
};

$conn->close();

fclose($fp);







$mail = new PHPMailer(true);

$mail->SMTPDebug = 0;                      // Enable verbose debug output
$mail->isSMTP();                                            // Send using SMTP
$mail->Host       = 'smtp-mail.outlook.com';                    // Set the SMTP server to send through
$mail->SMTPAuth   = true;                                   // Enable SMTP authentication
$mail->Username   = 'eprisno@atvtireinc.com';                     // SMTP username
$mail->Password   = 'Put21729';                               // SMTP password
$mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;         // Enable TLS encryption; `PHPMailer::ENCRYPTION_SMTPS` also accepted
$mail->Port       = 587;                                    // TCP port to connect to

$mail->setFrom('eprisno@atvtireinc.com', 'Mailer');

//Recipients

// $mail->addAddress($row["email_add"]); //testing
//$mail->addAddress(['ejay@atvtireinc.com','marvin@atvtireinc.com','YMarcelo@atvtireinc.com']); //testing
/* $mail->addAddress(['eprisno27@gmail.com','eprisno@atvtireinc.com']);  */
$email = ['ejay@atvtireinc.com','marvin@atvtireinc.com','YMarcelo@atvtireinc.com'];
foreach ($email as $m) {

    $mail->addAddress($m); 


    
}



// Attachments
$mail->addAttachment('lilo.csv');         // Add attachments


// Content
$mail->isHTML(true);                                  // Set email format to HTML
$mail->Subject = 'Daily Time In and Time Out';
$mail->Body = 'Employee daily time in and out';

$mail->send();



?>