<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    //Header
    $sql = "SELECT id, customer, ref_no, to_char(date_received, 'DD/MM/YYYY') date_received, 
                   brand, total_ctn, sample_data, application_status
              FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl
             WHERE id||'|'|| customer = :qr
               AND nvl(application_status,'1') = '1'";
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':qr', $data->qr);
    $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);

    //Customer
    $sql = "SELECT customer_id id, customer_name text
              FROM apps.xxyty_cust_gth_v
             WHERE customer_id = :customerid";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':customerid', $rows_main[0]["CUSTOMER"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $customer, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Brand
    $sql = "SELECT brand_id id, brand_name text
              FROM ytycustom.gth_customer_brand_master
             WHERE customer_id = :customerid
               AND brand_id = :brandid";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':customerid', $rows_main[0]["CUSTOMER"]);
    oci_bind_by_name($sqlarr, ':brandid', $rows_main[0]["BRAND"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $brand, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $rows_main[0]["CUSTOMER"] = $customer[0];
    $rows_main[0]["BRAND"] = $brand[0];

    //Detail
    if (!empty($rows_main)) {
      $sql = "SELECT id, inspection_id, pallet_no, quantity_of_cartons, size_code, 
                    racking_no, picked_by, to_char(picked_by_date,'YYYY-MM-DD') picked_by_date
                FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_tbl
              WHERE inspection_id = :insp_id
                AND NVL(LOCK_REL,'0') = '1'
            ORDER BY id";
      
      $sqlarr = oci_parse($dbcon , $sql);
      oci_bind_by_name($sqlarr, ':insp_id', $rows_main[0]["ID"]);
      $r = oci_execute($sqlarr);
      if (!$r) {
          $e = oci_error($sqlarr);
          print json_encode($e['message']);
      }
      oci_fetch_all($sqlarr, $rows_detail, null, null, OCI_FETCHSTATEMENT_BY_ROW);

      //Picked By
      $sql = "SELECT emp_id id, employee_name text
                FROM production.employee_master
              WHERE emp_id in (SELECT DISTINCT picked_by 
                                  FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_tbl
                                WHERE inspection_id = :insp_id)";
      $sqlarr = oci_parse($dbcon , $sql);
      oci_bind_by_name($sqlarr, ':insp_id', $rows_main[0]["ID"]);
      oci_execute($sqlarr);
      oci_fetch_all($sqlarr, $pickedby, null, null, OCI_FETCHSTATEMENT_BY_ROW);

      //Racking
      $sql = "SELECT rack_code id, rack_desc text
                FROM ytycustom.gth_rack_master
              WHERE rack_type = 'NS'";
      $sqlarr = oci_parse($dbcon , $sql);
      oci_bind_by_name($sqlarr, ':insp_id', $rows_main[0]["ID"]);
      oci_execute($sqlarr);
      oci_fetch_all($sqlarr, $nsrack, null, null, OCI_FETCHSTATEMENT_BY_ROW);

      $i = 0;
      foreach ($rows_detail as $det) {
        //Size
        $sz = [ "ID" => $rows_detail[$i]["SIZE_CODE"], "TEXT" => $rows_detail[$i]["SIZE_CODE"] ];
        $rows_detail[$i]["SIZE_CODE"] = $sz;

        //Racking
        if (!empty($rows_detail[$i]["RACKING_NO"])) {
          $rack = array_search($rows_detail[$i]["RACKING_NO"], array_column($nsrack, 'ID'));
          $rackno = $nsrack[$rack];
        } else {
          $rackno = [ "ID" => "", "TEXT" => "" ]; 
        }
        $rows_detail[$i]["RACKING_NO"] = $rackno;
        
        //Picked By
        if (!empty($rows_detail[$i]["PICKED_BY"])) {
          $pick = array_search($rows_detail[$i]["PICKED_BY"], array_column($pickedby, 'ID'));
          $pick_by = $pickedby[$pick];
        } else {
          $pick_by = [ "ID" => "", "TEXT" => "" ]; 
        }
        $rows_detail[$i]["PICKED_BY"] = $pick_by;

        $i++;
      }
    }

    $result[] = [
                    "main" => $rows_main,
                    "details" => $rows_detail,
                ];    
 
    print json_encode($result);
?>