<?php
    global $dbcon;
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);
    $adminAccess = "U"; 

    //Check Admin Access
    $sql = "SELECT CASE portal WHEN 'SUPERADMIN_ITINV' THEN 'S' WHEN 'ADMIN_ITINV' THEN 'A' WHEN 'HR_ITINV' THEN 'H' END portal
              FROM apps.xxyty_sso_reports_access
             WHERE portal IN ('HR_ITINV', 'ADMIN_ITINV', 'SUPERADMIN_ITINV')
               AND NVL(attribute1, 'N') = 'Y'
               AND emp_id = :emp_id";
                
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':emp_id', $data->USERID);
    $r = oci_execute($sqlarr);
    while ($row = oci_fetch_array($sqlarr, OCI_ASSOC+OCI_RETURN_NULLS)) {
        $adminAccess = $row['PORTAL'];
    }

    $whereRequesterClause = "";
    if ($adminAccess == "U") {
        $whereRequesterClause = " AND a.REQUESTER = '".$data->USERID."' ";
    } else if ($adminAccess == "H") {
        $whereRequesterClause = " AND (a.REQUEST_TYPE != 'OBN' OR a.REQUESTER = '".$data->USERID."') AND a.REQUESTER = '".$data->USERID."'";
    } else if ($adminAccess == "A") {
        $whereRequesterClause = " AND (a.REQUEST_TYPE != 'OBN' OR a.REQUESTER = '".$data->USERID."')";
    }

    $whereStatusClause = "";
    if ($data->sts != '-') {
        $whereStatusClause = "AND a.REQUEST_TYPE||NVL(a.STATUS, 0) = '".$data->sts."'";
    }

    $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
              WHERE a.requester = b.emp_id
                ".$whereRequesterClause."
                ".$whereStatusClause."
                AND to_char(a.date_request, 'YYYY') = :yr
              ORDER BY id DESC";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':yr', $data->yr);
    $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);
?>
