<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->customer)) {
       $customer = $data->customer->ID;
    } else {
       $customer = "";
    }

    if (!empty($data->brand)) {
       $brand = $data->brand->ID;
    } else {
       $brand = "";
    }

    if (!empty($data->refno)) {
       $refno = $data->refno;
    } else {
       $refno = "";
    }

    if (!empty($data->irrd)) {
       $irrd = $data->irrd;
    } else {
       $irrd = "";
    }

    $sql = "SELECT a.rack_code, a.rack_desc, a.rack_type, a.occupied, a.ref_no, 
                   b.customer_name, c.brand_name, a.quantity, a.size_code, a.irrd, 
                   case when NVL(a.occupied,'N') = 'Y' then
                     case when :customer is null and :brand is null and :refno is null and :irrd is null then
                       'bgc-occupied-unmatch'
                     else  
                       case when nvl(:customer, customer) = customer then
                         case when nvl(:brand, brand) = brand then
                           case when nvl(:refno, ref_no) = ref_no then
                             case when nvl(:irrd, nvl(irrd,'-')) = nvl(irrd,'-') then
                               'bgc-occupied-match'
                             else 
                               'bgc-occupied-unmatch'
                             end
                           else
                             'bgc-occupied-unmatch'
                           end
                         else
                           'bgc-occupied-unmatch'
                         end
                       else
                         'bgc-occupied-unmatch'
                       end
                     end
                   end occupied_color
              FROM ytycustom.gth_rack_master a,
                   apps.xxyty_cust_gth_v b,
                   ytycustom.gth_customer_brand_master c
             WHERE a.customer = b.customer_id(+)
               AND a.brand = c.brand_id(+)
               AND nvl(a.active_flag,'Y') = 'Y'
               AND a.rack_type = :area
               AND substr(a.rack_code,1,1) = :block
          ORDER BY substr(a.rack_code,1,1), substr(a.rack_code,3,1) DESC, substr(a.rack_code,5)";
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':area', $data->area);
    oci_bind_by_name($sqlarr, ':block', $data->block);
    oci_bind_by_name($sqlarr, ':customer', $customer);
    oci_bind_by_name($sqlarr, ':brand', $brand);
    oci_bind_by_name($sqlarr, ':refno', $refno);
    oci_bind_by_name($sqlarr, ':irrd', $irrd);
    $r = oci_execute($sqlarr);

    if (!$r) {
         $e = oci_error($sqlarr);
         print json_encode($e['message']);
    }

    oci_fetch_all($sqlarr, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    print json_encode($rows);
?>