<?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, cust_order_no, '' ref_no_new, to_char(date_received, 'YYYY-MM-DD') date_received, 
                   received_by, brand, total_ctn, sample_data, application_status
              FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl
             WHERE id||'|'|| customer = :qr";
    
    $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);

    //Received By
    $sql = "SELECT emp_id id, employee_name text
              FROM production.employee_master
             WHERE emp_id in (SELECT employee
                                FROM ( SELECT received_by
                                         FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl
                                        WHERE id||'|'|| customer = :qr) 
                             UNPIVOT ( employee FOR col IN ( received_by )))";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':qr', $data->qr);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $hod, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if (!empty($rows_main[0]["RECEIVED_BY"])) {
        $recv = array_search($rows_main[0]["RECEIVED_BY"], array_column($hod, 'ID'));
        $recv_by = $hod[$recv];
    } else {
        $recv_by = [ "ID" => "", "TEXT" => "" ]; 
    }
    $rows_main[0]["RECEIVED_BY"] = $recv_by;

    //Ref No
    $ref = [ "ID" => $rows_main[0]["REF_NO"], "TEXT" => $rows_main[0]["REF_NO"] ]; 
    $rows_main[0]["REF_NO"] = $ref;

    //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
    $sql = "SELECT pallet_no, quantity_of_cartons, quantity_pcs, size_code, lock_rel, lot_no
              FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_tbl
             WHERE inspection_id = :insp_id
          ORDER BY pallet_no";
    
    $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);

    $i = 0;
    foreach ($rows_detail as $det) {
        $sz = [ "ID" => $rows_detail[$i]["SIZE_CODE"], "TEXT" => $rows_detail[$i]["SIZE_CODE"] ]; 
        $rows_detail[$i]["SIZE_CODE"] = $sz;
        $i++;
    }

    $result[] = [
                    "main" => $rows_main,
                    "details" => $rows_detail,
                ];    
 
    print json_encode($result);
?>