<?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.brand, a.ref_no, a.irrd, a.total_ctn, 
                    a.cust_order_no, a.carton_w, a.carton_l, a.carton_h, 
                    a.product_valid_code, a.tote_pack_density, a.cycle_time, 
                    a.maximum_acc_dose, a.minimum_acc_dose, a.dosimeter_type, 
                    a.dosimetry_batch_no, 
                    nvl(to_char(a.first_tote_in, 'YYYY-MM-DD'), c.first_tote_datein) first_tote_datein, 
                    nvl(to_char(a.first_tote_in, 'HH24:MI'), c.first_tote_timein) first_tote_timein, 
                    nvl(to_char(a.last_tote_out, 'YYYY-MM-DD'), c.last_tote_dateout) last_tote_dateout, 
                    nvl(to_char(a.last_tote_out, 'HH24:MI'), c.last_tote_timeout) last_tote_timeout, 
                    a.spectrophotometer_used, to_char(a.expiry_date, 'YYYY-MM-DD') expiry_date, 
                    a.min_dose_read, nvl(a.min_correction_factor, b.correction_fac_min) min_correction_factor, 
                    case when a.min_corrected_dose is null then
                        a.min_dose_read / case when nvl(a.min_correction_factor, b.correction_fac_min) is null or nvl(a.min_correction_factor, b.correction_fac_min) = 0 
                                                then 1 
                                          else nvl(a.min_correction_factor, b.correction_fac_min) end 
                    else a.min_corrected_dose end min_corrected_dose,
                    a.max_dose_read, nvl(a.max_correction_factor,b.correction_fac_max) max_correction_factor,
                    case when a.max_corrected_dose is null then
                        a.max_dose_read / case when nvl(a.max_correction_factor,b.correction_fac_max) is null or nvl(a.max_correction_factor,b.correction_fac_max) = 0 
                                                then 1 
                                          else nvl(a.max_correction_factor,b.correction_fac_max) end 
                    else a.max_corrected_dose end max_corrected_dose,
                    a.dosimetry_status, 
                    a.dose_inspected_by, to_char(a.dose_inspected_date, 'YYYY-MM-DD') dose_inspected_date,
                    a.dose_verified_by, to_char(a.dose_verified_date, 'YYYY-MM-DD') dose_verified_date,
                    a.log_prepared_by, to_char(a.log_prepared_date, 'YYYY-MM-DD') log_prepared_date,
                    a.certificate_no, a.type_of_product, a.jobc_insp_by, a.jobc_approved_by, a.jobc_verified_by
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl a,
                    ytycustom.gth_customer_brand_master b,
                    (SELECT inspection_id,
                            to_char(min(loading_date),'YYYY-MM-DD') first_tote_datein,
                            to_char(min(loading_date),'HH24:MI') first_tote_timein,
                            to_char(max(unloading_date),'YYYY-MM-DD') last_tote_dateout,
                            to_char(max(unloading_date),'HH24:MI') last_tote_timeout
                       FROM ytycustom.xx_yty_incoming_outgoing_inspection_op
                   GROUP BY inspection_id) c
              WHERE a.customer = b.customer_id
                AND a.brand = b.brand_id
                AND a.id = c.inspection_id
                AND a.id || '|' || a.customer = :qr
                AND nvl(a.application_status,'1') IN ('1','2')
                AND a.irrd is not null ";
    
    $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);

    //Fixed Reference
    $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 ('DOSIMETER_TYPE','DOSIMETRY_BATCH_NO','SPECTROPHOTOMETER_USED','CALIBRATION_EXPIRY')";
    
    $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);

    //Dosimeter Type
    if (empty($rows_main[0]["DOSIMETER_TYPE"])) {
        $idx = array_search("DOSIMETER_TYPE", array_column($rows_fixed, 'CATEGORY_TYPE'));
        $dosimeter_type = $rows_fixed[$idx]["CATEGORY_VALUE"];
        $rows_main[0]["DOSIMETER_TYPE"] = $dosimeter_type;
    }

    //Dosimetry Batch No
    if (empty($rows_main[0]["DOSIMETRY_BATCH_NO"])) {
        $idx = array_search("DOSIMETRY_BATCH_NO", array_column($rows_fixed, 'CATEGORY_TYPE'));
        $dosimetry_batch = $rows_fixed[$idx]["CATEGORY_VALUE"];
        $rows_main[0]["DOSIMETRY_BATCH_NO"] = $dosimetry_batch;
    }

    //Spectrophotometer Used
    if (empty($rows_main[0]["SPECTROPHOTOMETER_USED"])) {
        $idx = array_search("SPECTROPHOTOMETER_USED", array_column($rows_fixed, 'CATEGORY_TYPE'));
        $spec_used = $rows_fixed[$idx]["CATEGORY_VALUE"];
        $rows_main[0]["SPECTROPHOTOMETER_USED"] = $spec_used;
    }

    //Expiry Date
    if (empty($rows_main[0]["EXPIRY_DATE"])) {
        $idx = array_search("CALIBRATION_EXPIRY", array_column($rows_fixed, 'CATEGORY_TYPE'));
        $expiry = $rows_fixed[$idx]["CATEGORY_VALUE"];
        $rows_main[0]["EXPIRY_DATE"] = $expiry;
    }

    //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];

    //Inspected By, Verified By
    $sql = "SELECT emp_id id, employee_name text
              FROM production.employee_master
             WHERE emp_id in (SELECT employee
                                FROM ( SELECT dose_inspected_by, dose_verified_by
                                         FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl
                                        WHERE id = :insp_id) 
                             UNPIVOT ( employee FOR col IN ( dose_inspected_by, dose_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);

    //Inspected By
    if (!empty($rows_main[0]["DOSE_INSPECTED_BY"])) {
        $insp = array_search($rows_main[0]["DOSE_INSPECTED_BY"], array_column($hod, 'ID'));
        $insp_by = $hod[$insp];
    } else {
        $insp_by = [ "ID" => "", "TEXT" => "" ]; 
    }

    //Verified By
    if (!empty($rows_main[0]["DOSE_VERIFIED_BY"])) {
        $verified = array_search($rows_main[0]["DOSE_VERIFIED_BY"], array_column($hod, 'ID'));
        $verified_by = $hod[$verified];
    } else {
        $verified_by = [ "ID" => "", "TEXT" => "" ]; 
    }

    $rows_main[0]["DOSE_INSPECTED_BY"] = $insp_by;
    $rows_main[0]["DOSE_VERIFIED_BY"] = $verified_by;

    //Detail
    $sql = "SELECT tote_no, dose_max_location, dose_min_location, dose_read_max_location, 
                   dose_read_min_location, remarks
              FROM ytycustom.xx_yty_incoming_outgoing_inspection_dose_det
             WHERE inspection_id = :inspection
          ORDER BY id";
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':inspection', $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);

    //If new load from OP Log
    if (empty($rows_detail)) {
        $sql = " SELECT a.tote_no, c.dose_loc_max dose_max_location, c.dose_loc_min dose_min_location, 
                        null dose_read_max_location, null dose_read_min_location, a.remarks
                   FROM ytycustom.xx_yty_incoming_outgoing_inspection_op a,
                        ytycustom.xx_yty_incoming_outgoing_inspection_tbl b,
                        ytycustom.gth_customer_brand_master c
                  WHERE a.inspection_id = b.id
                    AND b.customer = c.customer_id
                    AND b.brand = c.brand_id
                    AND a.inspection_id = :inspection
                    AND a.remarks is not null
               ORDER BY a.tote_no";
        
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':inspection', $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);
    }

    $result[] = [
                    "main" => $rows_main,
                    "details" => $rows_detail,
                ];    
 
    print json_encode($result);
?>