<?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);

    //Check Request Type
    $sql = "SELECT REQUEST_TYPE FROM ITINV_REQUEST WHERE ID = :ID";
    $sqlarr = oci_parse($dbcon , $sql);

    oci_bind_by_name($sqlarr, ':ID', $data->ID);
    $r = oci_execute($sqlarr);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }
    $requestType = "";
    while ($row = oci_fetch_array($sqlarr, OCI_ASSOC+OCI_RETURN_NULLS)) {
        $requestType = $row['REQUEST_TYPE'];
    }

    if ($requestType == "TRR") {
        $statusCompleted = 3;
        $actionCompleted = "Completed TRR";
    } else {
        $statusCompleted = 5;
        $actionCompleted = "Completed HRF";
    }

    $sql = "UPDATE ITINV_REQUEST 
               SET STATUS = :STATUS, 
                   OFC_CONTACT_NO = :OFC_CONTACT_NO,
                   OFC_EMAIL_ADDRESS = :OFC_EMAIL_ADDRESS,
                   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, ':OFC_CONTACT_NO', $data->OFC_CONTACT_NO);
    oci_bind_by_name($sqlarr, ':OFC_EMAIL_ADDRESS', $data->OFC_EMAIL_ADDRESS);
    oci_bind_by_name($sqlarr, ':UPDATES_NOTE', $data->UPDATES_NOTE);
    oci_bind_by_name($sqlarr, ':STATUS', $statusCompleted);
    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    //Hardware assignment
    $sql = "DELETE FROM ITINV_REQUEST_ASSIGN 
                  WHERE REQUEST_ID = :REQUEST_ID";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':REQUEST_ID', $data->ID);
    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    foreach ($data->hw_assign as $assign) {    
        $newinvid = $assign->ID;

        $sql = "UPDATE ITINV_DATA 
                    SET IV_HOST = :IV_HOST,
                        IV_USER = :IV_USER,
                        IV_DEPT = :IV_DEPT,
                        IV_LEVEL = :IV_LEVEL,
                        IV_STATUS = 'Assigned',
                        IV_USER_EMP = :IV_USER_EMP, 
                        IV_USER_JOIN_DATE = TO_DATE(:IV_USER_JOIN_DATE, 'YYYY-MM-DD'),
                        REQUEST_ID = :REQUEST_ID,
                        MODIFIED_DATE = SYSDATE,
                        MODIFIED_BY = :MODIFIED_BY
                    WHERE ID = :ID";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':IV_HOST', $assign->IV_HOST);
        oci_bind_by_name($sqlarr, ':IV_USER', $assign->IV_USER);
        oci_bind_by_name($sqlarr, ':IV_DEPT', $assign->IV_DEPT);
        oci_bind_by_name($sqlarr, ':IV_LEVEL', $assign->IV_LEVEL);
        oci_bind_by_name($sqlarr, ':IV_USER_EMP', $assign->IV_USER_EMP);
        oci_bind_by_name($sqlarr, ':IV_USER_JOIN_DATE', $assign->IV_USER_JOIN_DATE);
        oci_bind_by_name($sqlarr, ':REQUEST_ID', $data->ID);
        oci_bind_by_name($sqlarr, ':MODIFIED_BY', $userid);
        oci_bind_by_name($sqlarr, ':ID', $newinvid);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        //Assign
        $sql = "INSERT INTO ITINV_REQUEST_ASSIGN 
                            (REQUEST_ID, INVENTORY_ID)
                     VALUES (:REQUEST_ID, :INVENTORY_ID)";  

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':REQUEST_ID', $data->ID);
        oci_bind_by_name($sqlarr, ':INVENTORY_ID', $newinvid);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        } 

        //Log the User Assignment
        $sqlLog = "INSERT INTO ITINV_DATA_HISTORY (DATA_ID, DATE_LOG, IV_HOST, IV_USER, IV_DEPT, IV_LEVEL, IV_BLOCK, IV_STATUS, IV_USER_JOIN_DATE, IV_USER_EMP)
                        VALUES (:DATA_ID, SYSDATE, :IV_HOST, :IV_USER, :IV_DEPT, :IV_LEVEL, :IV_BLOCK, 'Assigned', TO_DATE(:IV_USER_JOIN_DATE, 'YYYY-MM-DD'), :IV_USER_EMP)";

        $sqlarrLog = oci_parse($dbcon , $sqlLog);
        oci_bind_by_name($sqlarrLog, ':DATA_ID', $newinvid);
        oci_bind_by_name($sqlarrLog, ':IV_HOST', $assign->IV_HOST);
        oci_bind_by_name($sqlarrLog, ':IV_USER', $assign->IV_USER);
        oci_bind_by_name($sqlarrLog, ':IV_DEPT', $assign->IV_DEPT);
        oci_bind_by_name($sqlarrLog, ':IV_LEVEL', $assign->IV_LEVEL);
        oci_bind_by_name($sqlarrLog, ':IV_BLOCK', $assign->IV_BLOCK);
        oci_bind_by_name($sqlarrLog, ':IV_USER_JOIN_DATE', $assign->IV_USER_JOIN_DATE);
        oci_bind_by_name($sqlarrLog, ':IV_USER_EMP', $assign->IV_USER_EMP);
        $rLog = oci_execute($sqlarrLog, OCI_NO_AUTO_COMMIT);

        if (!$rLog) {
            $e = oci_error($sqlarrLog);
            $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, NVL(A.UPDATES_NOTE,'-') 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, :appr_level, '2009', :doc_no, :action, :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, ':appr_level', $statusCompleted);
    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, ':enter_by', $data->userid);
    oci_bind_by_name($sqlarr, ':action', $actionCompleted);
    $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"]);
        $mail->addCC('pawnesh.kumar@ytygroup.com.my');
        $mail->addBCC('reyndo.frenliedo@ytygroup.com.my');

        //CC IT Team
        $sql = "SELECT b.email_id
                  FROM APPS.XXYTY_SSO_REPORTS_ACCESS a, employee_master b
                 WHERE a.emp_id = b.emp_id
                   AND a.portal = 'ADMIN_ITINV'";

        $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;
        }

        $rows_ccemail = [];
        oci_fetch_all($sqlarr, $rows_ccemail, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        foreach ($rows_ccemail as $email) {
            $mail->addCC($email["EMAIL_ID"]);             
        }

        //Content
        $mail->Subject = $rows_main[0]["REQUEST_TYPE_DESC"].' has been completed (REQ_ID: '.$rows_main[0]["REQ_ID"].')';
        $mail->Body    = '<p>Dear All,</p>
                          <p>The '.$rows_main[0]["REQUEST_TYPE_DESC"].' has been completed by the Admin. Please find the notes/details 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" => $errorMsg,
            "id" => ""
        ];
    }
    print json_encode($result);
?>  