<?php
    global $dbcon;
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $sql = " SELECT a.id, b.employee_name requester, a.req_for, a.r_level,
                    a.hw_selected, a.hw_avail, a.supporting_note,
                    a.updates_note,
                    CASE request_type||nvl(a.status, 0)
                       WHEN 'OBN0' THEN 'Onboarding - New'
                       WHEN 'OBN1' THEN 'Onboarding - In Progress'
                       WHEN 'OBN-1' THEN 'Onboarding - Rejected'
                       WHEN 'HRF2' THEN 'Hardware Requisition - New'
                       WHEN 'HRF3' THEN 'Hardware Requisition - In Progress'
                       WHEN 'HRF4' THEN 'Hardware Requisition - Approved'
                       WHEN 'HRF5' THEN 'Hardware Requisition - Completed'
                       WHEN 'HRF-1' THEN 'Hardware Requisition - Rejected'
                       WHEN 'HRF-2' THEN 'Hardware Requisition - Closed'
                       WHEN 'TRR0' THEN 'Reassignment Request - New'
                       WHEN 'TRR1' THEN 'Reassignment Request - In Progress'
                       WHEN 'TRR2' THEN 'Reassignment Request - Approved'
                       WHEN 'TRR3' THEN 'Reassignment Request - Completed'
                       WHEN 'TRR-1' THEN 'Reassignment Request - Rejected'
                       WHEN 'TRR-2' THEN 'Reassignment Request - Closed'
                     END status,
                    to_char(a.date_request, 'DD-MON-YYYY') date_request,
                    request_type, req_id
               FROM ITINV_REQUEST a, production.employee_master b, 
                    (SELECT *
                       FROM YTYCUSTOM.USER_REG_APP_TRANS
                      WHERE doc_type_code = '2009'
                        AND approver_emp_id = :emp_id) c
              WHERE a.requester = b.emp_id
                 AND a.req_id = c.doc_no
               AND c.action IS NULL
               AND a.current_approval = c.appr_level
              ORDER BY id DESC";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':emp_id', $data->USERID);
    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    $rows_main = [];
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    print json_encode($rows_main);
?>
