<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->ship_id)) {
          $sql = "SELECT a.id, a.ref_no, a.irrd, a.serial_no, a.brand, b.size_code, 
                         (SUM(b.quantity_of_cartons) - SUM(nvl(b.outgoing_qty, 0)) + SUM(nvl(c.quantity_out,0))) available_qty
                    FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl a, 
                         ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl b,
                         ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl_part c
                   WHERE a.id = b.inspection_id
                     AND NVL(a.dosimetry_status,'R') = 'A'
                     AND b.id = c.stl_id(+)
                     AND a.customer = :customer
                     AND c.ship_id(+) = :ship_id
                     AND (b.quantity_of_cartons - nvl(b.outgoing_qty, 0) + nvl(c.quantity_out,0)) > 0
                GROUP BY a.id, a.ref_no, a.irrd, a.serial_no, a.brand, b.size_code
                ORDER BY a.irrd, a.brand, b.size_code";

          $sqlarr = oci_parse($dbcon , $sql);
          oci_bind_by_name($sqlarr, ':customer', $data->customer);
          oci_bind_by_name($sqlarr, ':ship_id', $data->ship_id);
          $r = oci_execute($sqlarr);

          if (!$r) {
               $e = oci_error($sqlarr);
               print json_encode($e['message']);
          }
          oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    } else {
          $sql = "  SELECT a.id, a.ref_no, a.irrd, a.serial_no, a.brand, b.size_code, 
                           (SUM(quantity_of_cartons) - SUM(nvl(outgoing_qty, 0))) available_qty
                      FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl a, 
                           ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl b
                     WHERE a.id = b.inspection_id
                       AND (b.quantity_of_cartons - nvl(b.outgoing_qty, 0)) > 0
                       AND NVL(a.dosimetry_status,'R') = 'A'
                       AND customer = :customer
                  GROUP BY a.id, a.ref_no, a.irrd, a.serial_no, a.brand, b.size_code
                  ORDER BY a.irrd, a.brand, b.size_code";

          $sqlarr = oci_parse($dbcon , $sql);
          oci_bind_by_name($sqlarr, ':customer', $data->customer);
          $r = oci_execute($sqlarr);

          if (!$r) {
               $e = oci_error($sqlarr);
               print json_encode($e['message']);
          }
          oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    }
    
    //Brand List
    $sql = "SELECT brand_id id, brand_name text
              FROM ytycustom.gth_customer_brand_master
             WHERE customer_id = :customerid";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':customerid', $data->customer);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $listbrand, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $i = 0;
    foreach ($rows_main as $row) {
      //Brand  
      if (!empty($rows_main[$i]["BRAND"])) {
         $idx = array_search($rows_main[$i]["BRAND"], array_column($listbrand, 'ID'));
         $brand = $listbrand[$idx];
      } else {
         $brand = [ "ID" => "", "TEXT" => "" ]; 
      }
      $rows_main[$i]["BRAND"] = $brand;

      //Size
      $sz = [ "ID" => $rows_main[$i]["SIZE_CODE"], "TEXT" => $rows_main[$i]["SIZE_CODE"] ]; 
      $rows_main[$i]["SIZE_CODE"] = $sz;

      //Selected
      $rows_main[$i]["SELECTED"] = false;
      $i++;
    }
    
    print json_encode($rows_main);
?>