<?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);
    $errorMsg = "";

    //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, 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);

    if (count($rows_main) > 0) {

        //OBN Submit
        if ($rows_main[0]["REQUEST_TYPE"] == "OBN" && $rows_main[0]["STATUS"] == "0") { 

            //Update Request Status
            $sql = "UPDATE itinv_request
                       SET status = 1, current_approval = 1
                     WHERE 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;
            }

            // //Check If Approval Exist Before
            // $sql = "SELECT COUNT(1) CTR 
            //           FROM YTYCUSTOM.USER_REG_APP_TRANS
            //          WHERE DOC_NO = :DOC_NO
            //            AND doc_type_code = '2009'";

            // $sqlarr = oci_parse($dbcon, $sql);
            // oci_bind_by_name($sqlarr, ':DOC_NO', $rows_main[0]["REQ_ID"]);
            // $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            // if (!$r) {
            //     $e = oci_error($sqlarr);
            //     $errorMsg = json_encode($e['message']);
            //     goto finalStep;
            // }

            // $apprv_exists = 0;
            // while ($rw = oci_fetch_array($sqlarr)) {
            //     $apprv_exists = $rw['CTR'];
            // }

            // if ($apprv_exists > 0) {
            //     $sql = "DELETE FROM YTYCUSTOM.USER_REG_APP_TRANS 
            //                   WHERE doc_no = :DOC_NO
            //                     AND doc_type_code = '2009'";
            //     $sqlarr = oci_parse($dbcon, $sql);

            //     oci_bind_by_name($sqlarr, ':DOC_NO', $rows_main[0]["REQ_ID"]);
            //     $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            // }

            $sql = "INSERT INTO YTYCUSTOM.USER_REG_APP_TRANS (emp_id, approver_emp_id, appr_level, doc_type_code, doc_no, action, enter_date, enter_by)
                         SELECT '" . $data->userid . "', '" . $data->userid . "', 0, '2009', '" . $rows_main[0]["REQ_ID"] . "', 'Submit', sysdate, '" . $data->userid . "'
                           FROM dual
                          UNION
                         SELECT '" . $data->userid . "', approver_emp_id, appr_level, doc_type_code, '" . $rows_main[0]["REQ_ID"] . "', NULL, NULL, NULL
                           FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a
                          WHERE a.portal_name = 'ITINV_REQ_OBN'
                            AND NVL(a.enabled, 'Y') = 'Y'
                            AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to
                       ORDER BY 3";
            $sqlarr = oci_parse($dbcon, $sql);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }

            //First Approver
            $sql = " SELECT b.emp_id, b.employee_name, b.email_id
                       FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a, PRODUCTION.EMPLOYEE_MASTER b
                      WHERE a.APPROVER_EMP_ID = b.EMP_ID
                        AND a.portal_name = 'ITINV_REQ_OBN'
                        AND NVL(a.enabled, 'Y') = 'Y'
                        AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to
                        AND appr_level = 1";

            $sqlarr = oci_parse($dbcon, $sql);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            $rows_approver = [];
            oci_fetch_all($sqlarr, $rows_approver, null, null, OCI_FETCHSTATEMENT_BY_ROW);

            //Email Notification for OBN
            $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');

                //Approver OBN
                $mail->addAddress($rows_approver[0]["EMAIL_ID"]);        
                $mail->addBCC('reyndo.frenliedo@ytygroup.com.my');     

                //Content
                $mail->Subject = 'A new Onboarding Notification has been submitted (REQ_ID:'.$rows_main[0]["REQ_ID"].')';
                $mail->Body    = '<p>Dear Approver,</p>
                                  <p>This is a notification to inform you that the Onboarding Notification (OBN) hardware request for the upcoming new hire has been successfully submitted. The details are as follows:</p>
                                  <table border="0">
                                  <tr>
                                    <td><strong>Position Title:</strong> '.$rows_main[0]["POS_TITLE"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Work Location:</strong> '.$rows_main[0]["WORK_LOCATION"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Join Date:</strong> '.$rows_main[0]["JOIN_DATE"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Hardware Requested:</strong> '.$rows_main[0]["HW_SELECTED"].'</td>
                                  <tr>
                                  </table>

                                  <p>Please click this <a href="https://ytygroup.app/itinv-app/approvalreqpage/'.$rows_main[0]["ID"].'">link</a> to view the details.</p><br />
                                  <p>Thanks.</p>
                                  <p style="font-size: 12px; color: #666;">*This is an automated email. Please do not reply.</p>';
                
                $sendStatus = $mail->send();
            
            } catch (Exception $e) {
                $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
            }

        } else if ($rows_main[0]["REQUEST_TYPE"] == "HRF" && $rows_main[0]["STATUS"] == "2") { 
            
            //HRF Submit
            //Update Request Status
            $sql = "UPDATE itinv_request
                       SET status = 3, current_approval = 3
                     WHERE 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;
            }

            //Add to Reg Trans 
            $sql = "INSERT INTO YTYCUSTOM.USER_REG_APP_TRANS (emp_id, approver_emp_id, appr_level, doc_type_code, doc_no, action, enter_date, enter_by)
                         SELECT '" . $data->userid . "', '" . $data->userid . "', 2, '2009', '" . $rows_main[0]["REQ_ID"] . "', 'Submit HRF', sysdate, '" . $data->userid . "'
                           FROM dual
                          UNION
                         SELECT '" . $data->userid . "', approver_emp_id, 3, doc_type_code, '" . $rows_main[0]["REQ_ID"] . "', NULL, NULL, NULL
                           FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a
                          WHERE a.portal_name = 'ITINV_REQ_HRF'
                            AND NVL(a.enabled, 'Y') = 'Y'
                            AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to
                       ORDER BY 3";
            $sqlarr = oci_parse($dbcon, $sql);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }

            //First Approver
            $sql = " SELECT b.emp_id, b.employee_name, b.email_id
                       FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a, PRODUCTION.EMPLOYEE_MASTER b
                      WHERE a.APPROVER_EMP_ID = b.EMP_ID
                        AND a.portal_name = 'ITINV_REQ_HRF'
                        AND NVL(a.enabled, 'Y') = 'Y'
                        AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to
                        AND appr_level = 1";

            $sqlarr = oci_parse($dbcon, $sql);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            $rows_approver = [];
            oci_fetch_all($sqlarr, $rows_approver, null, null, OCI_FETCHSTATEMENT_BY_ROW);
            
            //Email Notification for HRF
            $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');

                //Approver HRF
                $mail->addAddress($rows_approver[0]["EMAIL_ID"]);         
                $mail->addBCC('reyndo.frenliedo@ytygroup.com.my');     

                //Content
                $mail->Subject = 'A new Hardware Requisition Form has been submitted (REQ_ID:'.$rows_main[0]["REQ_ID"].')';
                $mail->Body    = '<p>Dear Approver,</p>
                                  <p>This is a notification to inform you that a Hardware Requisition Form (HRF) has been submitted and requires your approval. The details are as follows:</p>
                                  <table border="0">
                                  <tr>
                                    <td><strong>Full Name:</strong> '.$rows_main[0]["REQ_FOR"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Position Title:</strong> '.$rows_main[0]["POS_TITLE"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Work Location:</strong> '.$rows_main[0]["WORK_LOCATION"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Join Date:</strong> '.$rows_main[0]["JOIN_DATE"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Hardware Requested:</strong> '.$rows_main[0]["HW_SELECTED"].'</td>
                                  <tr>
                                  </table>

                                  <p>Please click this <a href="https://ytygroup.app/itinv-app/approvalreqpage/'.$rows_main[0]["ID"].'">link</a> to view the details.</p><br />
                                  <p>Thanks.</p>
                                  <p style="font-size: 12px; color: #666;">*This is an automated email. Please do not reply.</p>';
                
                $sendStatus = $mail->send();
            
            } catch (Exception $e) {
                $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
            }

        } else if ($rows_main[0]["REQUEST_TYPE"] == "TRR") {

            //Update Request Status
            $sql = "UPDATE itinv_request
                       SET status = 1, current_approval = 1
                     WHERE 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;
            }

            // //Check If Approval Exist Before
            // $sql = "SELECT COUNT(1) CTR 
            //           FROM YTYCUSTOM.USER_REG_APP_TRANS
            //          WHERE DOC_NO = :DOC_NO
            //            AND doc_type_code = '2009'";

            // $sqlarr = oci_parse($dbcon, $sql);
            // oci_bind_by_name($sqlarr, ':DOC_NO', $rows_main[0]["REQ_ID"]);
            // $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            // if (!$r) {
            //     $e = oci_error($sqlarr);
            //     $errorMsg = json_encode($e['message']);
            //     goto finalStep;
            // }

            // $apprv_exists = 0;
            // while ($rw = oci_fetch_array($sqlarr)) {
            //     $apprv_exists = $rw['CTR'];
            // }

            // if ($apprv_exists > 0) {
            //     $sql = "DELETE FROM YTYCUSTOM.USER_REG_APP_TRANS 
            //                   WHERE doc_no = :DOC_NO
            //                     AND doc_type_code = '2009'";
            //     $sqlarr = oci_parse($dbcon, $sql);

            //     oci_bind_by_name($sqlarr, ':DOC_NO', $rows_main[0]["REQ_ID"]);
            //     $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            // }

            //Reg Trans
            $sql = "INSERT INTO YTYCUSTOM.USER_REG_APP_TRANS (emp_id, approver_emp_id, appr_level, doc_type_code, doc_no, action, enter_date, enter_by)
                         SELECT '" . $data->userid . "', '" . $data->userid . "', 0, '2009', '" . $rows_main[0]["REQ_ID"] . "', 'Submit TRR', sysdate, '" . $data->userid . "'
                           FROM dual
                          UNION
                         SELECT '" . $data->userid . "', approver_emp_id, appr_level, doc_type_code, '" . $rows_main[0]["REQ_ID"] . "', NULL, NULL, NULL
                           FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a
                          WHERE a.portal_name = 'ITINV_REQ_TRR'
                            AND NVL(a.enabled, 'Y') = 'Y'
                            AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to
                       ORDER BY 3";
            $sqlarr = oci_parse($dbcon, $sql);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }

            //First Approver
            $sql = " SELECT b.emp_id, b.employee_name, b.email_id
                       FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a, PRODUCTION.EMPLOYEE_MASTER b
                      WHERE a.APPROVER_EMP_ID = b.EMP_ID
                        AND a.portal_name = 'ITINV_REQ_TRR'
                        AND NVL(a.enabled, 'Y') = 'Y'
                        AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to
                        AND appr_level = 1";

            $sqlarr = oci_parse($dbcon, $sql);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            $rows_approver = [];
            oci_fetch_all($sqlarr, $rows_approver, null, null, OCI_FETCHSTATEMENT_BY_ROW);

            //Email Notification for TRR
            $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');

                //First Approver
                $mail->addAddress($rows_approver[0]["EMAIL_ID"]);     
                $mail->addBCC('reyndo.frenliedo@ytygroup.com.my');        

                //Content
                $mail->Subject = 'A new Technology Reassignment Request (TRR) has been submitted (REQ_ID:'.$rows_main[0]["REQ_ID"].')';
                $mail->Body    = '<p>Dear Approver,</p>
                                  <p>A new Technology Reassignment Request (TRR) has been submitted and requires your approval. The details are as follows:</p>
                                  <table border="0">
                                  <tr>
                                    <td><strong>Full Name:</strong> '.$rows_main[0]["REQ_FOR"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Position Title:</strong> '.$rows_main[0]["POS_TITLE"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Work Location:</strong> '.$rows_main[0]["WORK_LOCATION"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Join Date:</strong> '.$rows_main[0]["JOIN_DATE"].'</td>
                                  <tr>
                                  <tr>
                                    <td><strong>Hardware Requested:</strong> '.$rows_main[0]["HW_SELECTED"].'</td>
                                  <tr>
                                  </table>

                                  <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>
                                  <p style="font-size: 12px; color: #666;">*This is an automated email. Please do not reply.</p>';
                
                $sendStatus = $mail->send();
            
            } catch (Exception $e) {
                $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
            }
        }
    
    } else {
        $errorMsg = "Failed to submit request.";
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $data->ID
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => ""
        ];
    }
    print json_encode($result);
?>  