<?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, destination, total_ctn, status,
                   to_char(date_out, 'DD/MM/YYYY') date_out,
                   container_no, driver_name, nric, remarks,
                   authorized_by, to_char(authorized_date, 'YYYY-MM-DD') authorized_date,
                   verified_by, to_char(verified_date, 'YYYY-MM-DD') verified_date,
                   to_char(gateout_date, 'DD/MM/YYYY') gateout_date  
              FROM ytycustom.xx_yty_incoming_outgoing_inspection_ship_hd
             WHERE id||'!'|| customer = :qr
               AND NVL(status,'-') = 'R'";
    
    $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);

    //Destination
    $sql = "SELECT customer_id id, customer_name text
              FROM apps.xxyty_cust_gth_v
             WHERE customer_id = :destinationid";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':destinationid', $rows_main[0]["DESTINATION"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $destination, 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";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':customerid', $rows_main[0]["CUSTOMER"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $listbrand, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $rows_main[0]["CUSTOMER"] = $customer[0];
    $rows_main[0]["DESTINATION"] = $destination[0];

    //Authorized By
    $sql = "SELECT emp_id id, employee_name text
              FROM production.employee_master
             WHERE emp_id in (SELECT employee
                                FROM ( SELECT authorized_by
                                         FROM ytycustom.xx_yty_incoming_outgoing_inspection_ship_hd
                                        WHERE id = :ship_id) 
                             UNPIVOT ( employee FOR col IN ( authorized_by )))";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':ship_id', $rows_main[0]["ID"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $hod, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if (!empty($rows_main[0]["AUTHORIZED_BY"])) {
        $auth = array_search($rows_main[0]["AUTHORIZED_BY"], array_column($hod, 'ID'));
        $auth_by = $hod[$auth];
    } else {
        $auth_by = [ "ID" => "", "TEXT" => "" ]; 
    }
    $rows_main[0]["AUTHORIZED_BY"] = $auth_by;

    //Verified By
    $sql = "SELECT emp_id id, employee_name text
              FROM production.employee_master
             WHERE emp_id in (SELECT employee
                                FROM ( SELECT verified_by
                                         FROM ytycustom.xx_yty_incoming_outgoing_inspection_ship_hd
                                        WHERE id = :ship_id) 
                             UNPIVOT ( employee FOR col IN ( verified_by )))";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':ship_id', $rows_main[0]["ID"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $hod, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if (!empty($rows_main[0]["VERIFIED_BY"])) {
        $verify = array_search($rows_main[0]["VERIFIED_BY"], array_column($hod, 'ID'));
        $verify_by = $hod[$verify];
    } else {
        $verify_by = [ "ID" => "", "TEXT" => "" ]; 
    }
    $rows_main[0]["VERIFIED_BY"] = $verify_by;

    //Detail
    $sql = "  SELECT a.ref_no, a.brand, a.irrd, a.serial_no, a.size_code, 
                     SUM(a.quantity_out) quantity_out, SUM(a.damage_data) damage_data, 
                     a.status, a.sticker_gng,
                     (SELECT SUM(quantity_of_cartons)
                        FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_tbl
                       WHERE inspection_id = a.inspection_id
                         AND size_code = a.size_code) quantity_receive,
                     (SELECT SUM(quantity_of_cartons - nvl(outgoing_qty, 0)) balance
                        FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl
                       WHERE inspection_id = a.inspection_id
                         AND size_code = a.size_code) balance
                FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl_part a
               WHERE a.ship_id = :ship_id
                 AND nvl(a.lock_rel,'0') = '1'
            GROUP BY a.ref_no, a.brand, a.irrd, a.serial_no, a.size_code, a.status, a.sticker_gng,
                     a.inspection_id
            ORDER BY a.brand, a.irrd, a.size_code";
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':ship_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) {
        //Brand  
        if (!empty($rows_detail[$i]["BRAND"])) {
            $idx = array_search($rows_detail[$i]["BRAND"], array_column($listbrand, 'ID'));
            $brand = $listbrand[$idx];
        } else {
            $brand = [ "ID" => "", "TEXT" => "" ]; 
        }
        $rows_detail[$i]["BRAND"] = $brand;

        //Size
        $sz = [ "ID" => $rows_detail[$i]["SIZE_CODE"], "TEXT" => $rows_detail[$i]["SIZE_CODE"] ]; 
        $rows_detail[$i]["SIZE_CODE"] = $sz;

        //Status
        if ($rows_detail[$i]["STATUS"] == "P") {
            $status = [ "ID" => "P", "TEXT" => "Pass" ];
        } else if ($rows_detail[$i]["STATUS"] == "F") {
            $status = [ "ID" => "F", "TEXT" => "Fail" ];
        } else {
            $status = [ "ID" => "", "TEXT" => "" ];
        }
        $rows_detail[$i]["STATUS"] = $status;

        //Sticker
        if ($rows_detail[$i]["STICKER_GNG"] == "P") {
            $sticker = [ "ID" => "P", "TEXT" => "Pass" ];
        } else if ($rows_detail[$i]["STICKER_GNG"] == "F") {
            $sticker = [ "ID" => "F", "TEXT" => "Fail" ];
        } else {
            $sticker = [ "ID" => "", "TEXT" => "" ];
        }
        $rows_detail[$i]["STICKER_GNG"] = $sticker;
        $i++;
    }

    $result[] = [
                    "main" => $rows_main,
                    "details" => $rows_detail,
                ];    
 
    print json_encode($result);
?>