<?php
    global $dbcon;
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($_POST["data"]);
    $hardware = json_decode($_POST["hw_selected"]);
    $hardware_all = json_decode($_POST["hw_avail"]);
    $hwassign = json_decode($_POST["hw_assign"]);
    
    $userid = $_POST["userid"];
    $filename1 = null;
    $filename2 = null;
    $errorMsg = "";

    $target_dir = "uploads/request/";
    if (!empty($_FILES["upload1"]["name"])) {
        $target_file = $target_dir . basename($_FILES["upload1"]["name"]);
        if (move_uploaded_file($_FILES["upload1"]["tmp_name"], $target_file)) {
            $filename1 = basename($_FILES["upload1"]["name"]);
        }
    }

    if (!empty($_FILES["upload2"]["name"])) {
        $target_file = $target_dir . basename($_FILES["upload2"]["name"]);
        if (move_uploaded_file($_FILES["upload2"]["tmp_name"], $target_file)) {
            $filename2 = basename($_FILES["upload2"]["name"]);
        }
    }

    if (empty($data->ID)) {
        //Generate Req ID
        $sql = "SELECT :REQ_TYPE||TO_CHAR(SYSDATE, 'YYYYMM') ||
                       LPAD(NVL(MAX(SUBSTR(req_id, 10)), 0) + 1, 4, 0) req_id
                  FROM itinv_request
                 WHERE substr(req_id, 4, 6) = TO_CHAR(SYSDATE, 'YYYYMM')";
        
        $sqlarr = oci_parse($dbcon, $sql);
        oci_bind_by_name($sqlarr, ':REQ_TYPE', $data->REQUEST_TYPE);
        oci_execute($sqlarr);
        while ($row = oci_fetch_array($sqlarr)) {
            $req_id = $row['REQ_ID'];
        }

        //New data
        $sql = "INSERT INTO ITINV_REQUEST 
                            (REQUESTER,REQ_FOR,R_LEVEL,SUPPORTING_NOTE,UPDATES_NOTE,ATTACHMENT_FILE1,ATTACHMENT_FILE2,DATE_REQUEST,HW_SELECTED,HW_AVAIL,CREATED_DATE,CREATED_BY,STATUS,
                            POS_TITLE, DEPARTMENT, HOD, JOIN_DATE, WORK_LOCATION, WORK_ROLE, CONTACT_NO, EMAIL_ADDRESS, POS_GROUP, REQUEST_TYPE, REQ_FOR_EMP, REQ_ID,
                            COUNTRY_CODE, OFC_COUNTRY_CODE, PREFERRED_NAME)
                     VALUES (:REQUESTER,:REQ_FOR,:R_LEVEL,:SUPPORTING_NOTE,:UPDATES_NOTE,:ATTACHMENT_FILE1,:ATTACHMENT_FILE2,SYSDATE,:HW_SELECTED,:HW_AVAIL,SYSDATE,:CREATED_BY,:STATUS,
                            :POS_TITLE, :DEPARTMENT, :HOD, TO_DATE(:JOIN_DATE, 'YYYY-MM-DD'), :WORK_LOCATION, :WORK_ROLE, :CONTACT_NO, :EMAIL_ADDRESS, :POS_GROUP, :REQUEST_TYPE, :REQ_FOR_EMP, :REQ_ID,
                            :COUNTRY_CODE, :OFC_COUNTRY_CODE, :PREFERRED_NAME)
          RETURNING ID INTO :NEWID";

        $hardware_array = array();
        foreach ($hardware as $txt) {
            array_push($hardware_array, $txt);
        }
        $hardware_text = implode(', ', $hardware_array);
        
        $hardware_list = array_column($hardware_all, 'TEXT');
        $hardware_avail_text = implode(', ', $hardware_list);

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':REQUESTER', $data->REQUESTER);
        oci_bind_by_name($sqlarr, ':REQ_FOR', $data->REQ_FOR);
        oci_bind_by_name($sqlarr, ':PREFERRED_NAME', $data->PREFERRED_NAME);
        oci_bind_by_name($sqlarr, ':R_LEVEL', $data->R_LEVEL);
        oci_bind_by_name($sqlarr, ':SUPPORTING_NOTE', $data->SUPPORTING_NOTE);
        oci_bind_by_name($sqlarr, ':UPDATES_NOTE', $data->UPDATES_NOTE);
        oci_bind_by_name($sqlarr, ':ATTACHMENT_FILE1', $filename1);
        oci_bind_by_name($sqlarr, ':ATTACHMENT_FILE2', $filename2);
        oci_bind_by_name($sqlarr, ':HW_SELECTED', $hardware_text);
        oci_bind_by_name($sqlarr, ':HW_AVAIL', $hardware_avail_text);
        oci_bind_by_name($sqlarr, ':CREATED_BY', $userid);
        oci_bind_by_name($sqlarr, ':STATUS', $data->STATUS);
        oci_bind_by_name($sqlarr, ':POS_TITLE', $data->POS_TITLE);
        oci_bind_by_name($sqlarr, ':DEPARTMENT', $data->DEPARTMENT);
        oci_bind_by_name($sqlarr, ':HOD', $data->HOD);
        oci_bind_by_name($sqlarr, ':JOIN_DATE', $data->JOIN_DATE);
        oci_bind_by_name($sqlarr, ':WORK_LOCATION', $data->WORK_LOCATION);
        oci_bind_by_name($sqlarr, ':WORK_ROLE', $data->WORK_ROLE);
        oci_bind_by_name($sqlarr, ':CONTACT_NO', $data->CONTACT_NO);
        oci_bind_by_name($sqlarr, ':EMAIL_ADDRESS', $data->EMAIL_ADDRESS);
        oci_bind_by_name($sqlarr, ':POS_GROUP', $data->POS_GROUP);
        oci_bind_by_name($sqlarr, ':REQUEST_TYPE', $data->REQUEST_TYPE);
        oci_bind_by_name($sqlarr, ':REQ_FOR_EMP', $data->REQ_FOR_EMP);
        oci_bind_by_name($sqlarr, ':COUNTRY_CODE', $data->COUNTRY_CODE);
        oci_bind_by_name($sqlarr, ':OFC_COUNTRY_CODE', $data->OFC_COUNTRY_CODE);
        oci_bind_by_name($sqlarr, ':REQ_ID', $req_id);
        oci_bind_by_name($sqlarr, ':NEWID', $newid, 20);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

    } else {

        $newid = $data->ID;

        // $attach_set1 = "";
        // if (!empty($filename1)) {
        //     $attach_set1 = " ATTACHMENT_FILE1 = '".$filename1."', ";
        // }

        // $attach_set2 = "";
        // if (!empty($filename2)) {
        //     $attach_set2 = " ATTACHMENT_FILE2 = '".$filename2."', ";
        // }

        //Update data
        $sql = "UPDATE ITINV_REQUEST 
                   SET REQUESTER = :REQUESTER,
                       REQ_FOR = :REQ_FOR,
                       PREFERRED_NAME = :PREFERRED_NAME,
                       R_LEVEL = :R_LEVEL,
                       SUPPORTING_NOTE = :SUPPORTING_NOTE,
                       UPDATES_NOTE = :UPDATES_NOTE,
                       ATTACHMENT_FILE1 = COALESCE(:ATTACHMENT_FILE1, ATTACHMENT_FILE1),
                       ATTACHMENT_FILE2 = COALESCE(:ATTACHMENT_FILE2, ATTACHMENT_FILE2),
                       HW_SELECTED = :HW_SELECTED,
                       HW_AVAIL = :HW_AVAIL,
                       MODIFIED_DATE = SYSDATE,
                       MODIFIED_BY = :MODIFIED_BY,
                       COUNTRY_CODE = :COUNTRY_CODE,
                       OFC_COUNTRY_CODE = :OFC_COUNTRY_CODE,
                       POS_TITLE = :POS_TITLE,
                       DEPARTMENT = :DEPARTMENT,
                       HOD = :HOD,
                       JOIN_DATE = TO_DATE(:JOIN_DATE, 'YYYY-MM-DD'),
                       WORK_LOCATION = :WORK_LOCATION,
                       WORK_ROLE = :WORK_ROLE,
                       CONTACT_NO = :CONTACT_NO,
                       EMAIL_ADDRESS = :EMAIL_ADDRESS,
                       POS_GROUP = :POS_GROUP,
                       REQUEST_TYPE = :REQUEST_TYPE,
                       REQ_FOR_EMP = :REQ_FOR_EMP
                 WHERE ID = :ID";

        $hardware_array = array();
        foreach ($hardware as $txt) {
            array_push($hardware_array, $txt);
        }
        $hardware_text = implode(', ', $hardware_array);

        $hardware_list = array_column($hardware_all, 'TEXT');
        $hardware_avail_text = implode(', ', $hardware_list);

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':REQUESTER', $data->REQUESTER);
        oci_bind_by_name($sqlarr, ':REQ_FOR', $data->REQ_FOR);
        oci_bind_by_name($sqlarr, ':PREFERRED_NAME', $data->PREFERRED_NAME);
        oci_bind_by_name($sqlarr, ':R_LEVEL', $data->R_LEVEL);
        oci_bind_by_name($sqlarr, ':SUPPORTING_NOTE', $data->SUPPORTING_NOTE);
        oci_bind_by_name($sqlarr, ':UPDATES_NOTE', $data->UPDATES_NOTE);
        oci_bind_by_name($sqlarr, ':ATTACHMENT_FILE1', $filename1);
        oci_bind_by_name($sqlarr, ':ATTACHMENT_FILE2', $filename2);
        oci_bind_by_name($sqlarr, ':HW_SELECTED', $hardware_text);
        oci_bind_by_name($sqlarr, ':HW_AVAIL', $hardware_avail_text);
        oci_bind_by_name($sqlarr, ':MODIFIED_BY', $userid);
        oci_bind_by_name($sqlarr, ':POS_TITLE', $data->POS_TITLE);
        oci_bind_by_name($sqlarr, ':DEPARTMENT', $data->DEPARTMENT);
        oci_bind_by_name($sqlarr, ':HOD', $data->HOD);
        oci_bind_by_name($sqlarr, ':JOIN_DATE', $data->JOIN_DATE);
        oci_bind_by_name($sqlarr, ':WORK_LOCATION', $data->WORK_LOCATION);
        oci_bind_by_name($sqlarr, ':WORK_ROLE', $data->WORK_ROLE);
        oci_bind_by_name($sqlarr, ':CONTACT_NO', $data->CONTACT_NO);
        oci_bind_by_name($sqlarr, ':EMAIL_ADDRESS', $data->EMAIL_ADDRESS);
        oci_bind_by_name($sqlarr, ':POS_GROUP', $data->POS_GROUP);
        oci_bind_by_name($sqlarr, ':REQUEST_TYPE', $data->REQUEST_TYPE);
        oci_bind_by_name($sqlarr, ':REQ_FOR_EMP', $data->REQ_FOR_EMP);  
        oci_bind_by_name($sqlarr, ':COUNTRY_CODE', $data->COUNTRY_CODE);
        oci_bind_by_name($sqlarr, ':OFC_COUNTRY_CODE', $data->OFC_COUNTRY_CODE);
        oci_bind_by_name($sqlarr, ':ID', $newid);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }
    }

    if ($data->REQUEST_TYPE == "TRR") {
        //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', $newid);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        foreach ($hwassign as $assign) {    
            $newinvid = $assign->ID;

            //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', $newid);
            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;
            } 
        }
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $newid
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => ""
        ];
    }
    print json_encode($result);
?>