<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    //Main
    $sql = " SELECT a.id, a.customer, a.irrd, a.serial_no, a.total_ctn, a.loading_pattern, 
                    a.dosimetry_batch_no, a.log_prepared_by, 
                    to_char(a.log_prepared_date, 'YYYY-MM-DD') log_prepared_date, 
                    a.log_checked_by, to_char(a.log_checked_date, 'YYYY-MM-DD') log_checked_date, 
                    a.log_verified_by, to_char(a.log_verified_date, 'YYYY-MM-DD') log_verified_date,
                    b.dose_loc_min, b.dose_loc_max
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl a,
                    ytycustom.gth_customer_brand_master b
              WHERE a.customer = b.customer_id
                AND a.brand = b.brand_id
                AND a.id || '|' || a.customer = :qr
                AND nvl(a.application_status,'1') IN ('1','2')
                AND NVL(a.cancel_flag,'N') = 'N'";
    
    $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);

    //Dosimetry Batch No
    $sql = "SELECT category_type, category_value
              FROM ytycustom.gth_fixed_reference
             WHERE trunc(sysdate) BETWEEN nvl(effective_from, trunc(sysdate)) AND nvl(effective_to, trunc(sysdate))
               AND category_type IN ('DOSIMETRY_BATCH_NO')";
    $sqlarr = oci_parse($dbcon , $sql);
    $r = oci_execute($sqlarr);
    if (!$r) {
         $e = oci_error($sqlarr);
         print json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_fixed, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if (empty($rows_main[0]["DOSIMETRY_BATCH_NO"])) {
        $idx = array_search("DOSIMETRY_BATCH_NO", array_column($rows_fixed, 'CATEGORY_TYPE'));
        $dosimetrybatch = $rows_fixed[$idx]["CATEGORY_VALUE"];
    }
    $rows_main[0]["DOSIMETRY_BATCH_NO"] = $dosimetrybatch;

    //Prepared By, Checked By, Verified By
    $sql = "SELECT emp_id id, employee_name text
              FROM production.employee_master
             WHERE emp_id in (SELECT employee
                                FROM ( SELECT log_prepared_by, log_checked_by, log_verified_by
                                         FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl
                                        WHERE id = :insp_id) 
                             UNPIVOT ( employee FOR col IN ( log_prepared_by, log_checked_by, log_verified_by )))";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':insp_id', $rows_main[0]["ID"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $hod, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Prepared By
    if (!empty($rows_main[0]["LOG_PREPARED_BY"])) {
        $prep = array_search($rows_main[0]["LOG_PREPARED_BY"], array_column($hod, 'ID'));
        $prep_by = $hod[$prep];
    } else {
        $prep_by = [ "ID" => "", "TEXT" => "" ]; 
    }

    //Checked By
    if (!empty($rows_main[0]["LOG_CHECKED_BY"])) {
        $check = array_search($rows_main[0]["LOG_CHECKED_BY"], array_column($hod, 'ID'));
        $check_by = $hod[$check];
    } else {
        $check_by = [ "ID" => "", "TEXT" => "" ]; 
    }

    //Verified By
    if (!empty($rows_main[0]["LOG_VERIFIED_BY"])) {
        $verified = array_search($rows_main[0]["LOG_VERIFIED_BY"], array_column($hod, 'ID'));
        $verified_by = $hod[$verified];
    } else {
        $verified_by = [ "ID" => "", "TEXT" => "" ]; 
    }

    $rows_main[0]["LOG_PREPARED_BY"] = $prep_by;
    $rows_main[0]["LOG_CHECKED_BY"] = $check_by;
    $rows_main[0]["LOG_VERIFIED_BY"] = $verified_by;

    //Detail
    $sql = "SELECT tote_no, quantity, loading_loc, unloading_loc, 
                   to_char(loading_date, 'YYYY-MM-DD') loading_dt, 
                   to_char(loading_date, 'HH24:MI') loading_tm,
                   loading_by, unloading_by,
                   to_char(unloading_date, 'YYYY-MM-DD') unloading_dt, 
                   to_char(unloading_date, 'HH24:MI') unloading_tm, remarks, irrd
              FROM ytycustom.xx_yty_incoming_outgoing_inspection_op
             WHERE inspection_id = :insp_id
          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);

    //Loading / Unloading By
    $sql = "SELECT emp_id id, employee_name text
              FROM production.employee_master
             WHERE emp_id in (SELECT employee
                                FROM ( SELECT DISTINCT loading_by, unloading_by
                                         FROM ytycustom.xx_yty_incoming_outgoing_inspection_op
                                        WHERE inspection_id = :insp_id) 
                             UNPIVOT ( employee FOR col IN ( loading_by, unloading_by )))";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':insp_id', $rows_main[0]["ID"]);
    oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $operator, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $i = 0;
    foreach ($rows_detail as $det) {
        if (!empty($rows_detail[$i]["LOADING_BY"])) {
            $idx = array_search($rows_detail[$i]["LOADING_BY"], array_column($operator, 'ID'));
            $loading_by = $operator[$idx];
        } else {
            $loading_by = [ "ID" => "", "TEXT" => "" ]; 
        }

        if (!empty($rows_detail[$i]["UNLOADING_BY"])) {
            $idx = array_search($rows_detail[$i]["UNLOADING_BY"], array_column($operator, 'ID'));
            $unloading_by = $operator[$idx];
        } else {
            $unloading_by = [ "ID" => "", "TEXT" => "" ]; 
        }

        $rows_detail[$i]["LOADING_BY"] = $loading_by;
        $rows_detail[$i]["UNLOADING_BY"] = $unloading_by;
        $i++;
    }

    $result[] = [
                    "main" => $rows_main,
                    "details" => $rows_detail,
                ];    
 
    print json_encode($result);
?>