<?php
    global $dbcon;
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\SMTP;
    use PHPMailer\PHPMailer\Exception;

    include "../db/headerApi.php";
    include "../db/connection_main.php";
    require_once('vendor/autoload.php'); 

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $sql = "UPDATE ITINV_REQUEST 
               SET STATUS = -2,
                   UPDATES_NOTE = :UPDATES_NOTE
             WHERE ID = :ID";
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':ID', $data->ID);
    oci_bind_by_name($sqlarr, ':UPDATES_NOTE', $data->UPDATES_NOTE);
    $r = oci_execute($sqlarr);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    //Request Detail
    $sql = "SELECT A.ID, A.REQUESTER, B.EMPLOYEE_NAME REQUESTER_NAME, A.REQ_FOR, R_LEVEL,
                   TO_CHAR(A.DATE_REQUEST, 'DD/MM/YYYY') as DATE_REQUEST, A.HW_SELECTED,
                   NVL(A.SUPPORTING_NOTE, '-') as SUPPORTING_NOTE, A.UPDATES_NOTE, 
                   A.ATTACHMENT_FILE1, A.ATTACHMENT_FILE2, A.ATTACHMENT_FILE3, NVL(A.STATUS, 0) STATUS,
                   A.HW_AVAIL, A.HW_SELECTED, A.POS_TITLE, A.DEPARTMENT, A.HOD, TO_CHAR(A.JOIN_DATE,'DD/MM/YYYY') JOIN_DATE, 
                   A.WORK_LOCATION, A.WORK_ROLE, A.CONTACT_NO, A.EMAIL_ADDRESS, A.POS_GROUP, A.REQUEST_TYPE, 
                   CASE WHEN A.REQUEST_TYPE = 'HRF' THEN 'Hardware Requisition Form'
                        WHEN A.REQUEST_TYPE = 'OBN' THEN 'Onboarding Notification'
                        WHEN A.REQUEST_TYPE = 'TRR' THEN 'Technology Reassignment Request'
                        ELSE '-' END REQUEST_TYPE_DESC,
                   A.REQ_FOR_EMP, A.REQ_ID
              FROM ITINV_REQUEST A, EMPLOYEE_MASTER B
             WHERE A.REQUESTER = B.EMP_ID
               AND A.ID = :ID";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':ID', $data->ID);
    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    $rows_main = [];
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Add history to Req Trans
    $sql = "INSERT INTO YTYCUSTOM.USER_REG_APP_TRANS 
                        (emp_id, approver_emp_id, appr_level, doc_type_code, doc_no, action, reason, enter_date, enter_by)
                 VALUES (:emp_id, :approver_emp_id, '-2', '2009', :doc_no, 'Closed '||:req_type, :reason, sysdate, :enter_by)";
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':emp_id', $rows_main[0]["REQUESTER"]);
    oci_bind_by_name($sqlarr, ':approver_emp_id', $data->userid);
    oci_bind_by_name($sqlarr, ':doc_no', $rows_main[0]["REQ_ID"]);
    oci_bind_by_name($sqlarr, ':reason', $rows_main[0]["UPDATES_NOTE"]);
    oci_bind_by_name($sqlarr, ':req_type', $rows_main[0]["REQUEST_TYPE"]);
    oci_bind_by_name($sqlarr, ':enter_by', $data->userid);
    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    //Submitter
    $sql = "SELECT email_id 
              FROM production.employee_master
             WHERE emp_id = :emp_id";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':emp_id', $rows_main[0]["REQUESTER"]);
    $r = oci_execute($sqlarr);
    $rows_submitter = [];
    oci_fetch_all($sqlarr, $rows_submitter, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Email
    $mail = new PHPMailer(true);
    try {
        //Server settings                 
        $mail->isSMTP();
        $mail->Host       = 'email-smtp.us-east-1.amazonaws.com';
        $mail->SMTPAuth   = true;
        $mail->Username   = 'AKIA4TM64HI4LPHXMK6L';
        $mail->Password   = 'BFAufGeMYsb8sgH5Ta5VaTR/0omvCn6Ev3qFp+5wG83+';
        $mail->SMTPSecure = 'ssl';
        $mail->Port       = 465;
        $mail->isHTML(true); 
        $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow');

        //Submitter
        $mail->addAddress($rows_submitter[0]["EMAIL_ID"]);

        //Admin
        $mail->addAddress('reyndo.frenliedo@ytygroup.com.my');      
        $mail->addBCC('reyndo.frenliedo@ytygroup.com.my');       

        //Content
        $mail->Subject = $rows_main[0]["REQUEST_TYPE_DESC"].' has been closed without hardware assigned (REQ_ID: '.$rows_main[0]["REQ_ID"].')';
        $mail->Body    = '<p>Dear All,</p>
                          <p>This '.$rows_main[0]["REQUEST_TYPE_DESC"].' has been marked as closed without hardware assigned by the Admin with notes below:</p>
                          <p><i>'.$rows_main[0]["UPDATES_NOTE"].'</i></p><br />
                          <p>Please click this <a href="https://ytygroup.app/itinv-app/request/'.$rows_main[0]["ID"].'">link</a> to view the details.</p><br />
                          <p>Thanks.</p>';
        
        $sendStatus = $mail->send();
    
    } catch (Exception $e) {
        $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        $result[] = [
            "message" => "Success",
            "id" => $data->ID
        ];
    } else {
        $result[] = [
            "message" => error_get_last(),
            "id" => ""
        ];
    }
    print json_encode($result);
?>  