<?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 as customer_code, b.customer_name as customer, n.brand_name brand, a.ref_no, 
                    to_char(a.date_received,'DD/MM/YYYY') date_received, l.employee_name received_by, attachment1, attachment2,
                    a.sample_data, a.total_ctn, a.application_status, a.irrd, a.serial_no, a.cust_order_no, a.carton_w, a.carton_l, 
                    a.carton_h, a.gamma_batch_no, a.blank_batch_no, a.certificate_no, a.product_valid_code, a.loading_pattern, 
                    a.dose_required, a.maximum_acc_dose, a.minimum_acc_dose, a.carton_w1, a.carton_w2, a.carton_w3, a.carton_w4, 
                    a.carton_w5, a.total_weight, a.avg_weight, rtrim(to_char(a.carton_density, 'FM990D000'), '.') carton_density, 
                    rtrim(to_char(a.tote_pack_density, 'FM990D000'), '.') tote_pack_density, remarks,
                    CASE WHEN a.dose_mapping = 'Y'      THEN 'DOSE_MAPPING'
                         WHEN a.routine_irrd = 'Y'      THEN 'ROUTINE_IRRD'
                         WHEN a.prod_pack_eval = 'Y'    THEN 'PROD_PACK_EVAL'
                         WHEN a.dose_verification = 'Y' THEN 'DOSE_VERIFICATION'
                    END verification_type, a.min_dose_read, a.min_dose_status, a.max_dose_read, a.max_dose_status, 
                    c.employee_name jobc_prepared_by, to_char(a.jobc_prepared_date,'DD/MM/YYYY') jobc_prepared_date, 
                    d.employee_name jobc_checked_by, to_char(a.jobc_checked_date, 'DD/MM/YYYY') jobc_checked_date, 
                    e.employee_name jobc_insp_by, to_char(a.jobc_insp_date,'DD/MM/YYYY') jobc_insp_date, 
                    f.employee_name jobc_approved_by, to_char(a.jobc_approved_date, 'DD/MM/YYYY') jobc_approved_date,
                    m.employee_name jobc_verified_by, to_char(a.jobc_verified_date, 'DD/MM/YYYY') jobc_verified_date,
                    a.dosimetry_batch_no, a.cycle_time, a.dosimeter_type, to_char(a.first_tote_in,'DD/MM/YYYY') first_tote_datein,
                    to_char(a.first_tote_in,'HH24:MI') first_tote_timein, to_char(a.last_tote_out,'DD/MM/YYYY') last_tote_dateout,
                    to_char(a.last_tote_out,'HH24:MI') last_tote_timeout, a.spectrophotometer_used,
                    to_char(a.expiry_date,'DD/MM/YYYY') expiry_date, 
                    rtrim(to_char(a.min_correction_factor, 'FM90D000'), '.') min_correction_factor,
                    a.min_corrected_dose,
                    rtrim(to_char(a.max_correction_factor, 'FM90D000'), '.') max_correction_factor,
                    a.max_corrected_dose, a.dosimetry_status, 
                    g.employee_name dose_inspected_by, to_char(a.dose_inspected_date,'DD/MM/YYYY') dose_inspected_date, 
                    h.employee_name dose_verified_by, to_char(a.dose_verified_date,'DD/MM/YYYY') dose_verified_date, 
                    i.employee_name log_prepared_by, to_char(a.log_prepared_date,'DD/MM/YYYY') log_prepared_date,
                    j.employee_name log_checked_by, to_char(a.log_checked_date,'DD/MM/YYYY') log_checked_date,
                    k.employee_name log_verified_by, to_char(a.log_verified_date,'DD/MM/YYYY') log_verified_date
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl a,
                    apps.xxyty_cust_gth_v b,
                    production.employee_master c,
                    production.employee_master d,
                    production.employee_master e,
                    production.employee_master f,
                    production.employee_master g,
                    production.employee_master h,
                    production.employee_master i,
                    production.employee_master j,
                    production.employee_master k,
                    production.employee_master l,
                    production.employee_master m,
                    ytycustom.gth_customer_brand_master n
              WHERE a.customer = b.customer_id
                AND a.jobc_prepared_by = c.emp_id(+)
                AND a.jobc_checked_by = d.emp_id(+)
                AND a.jobc_insp_by = e.emp_id(+)
                AND a.jobc_approved_by = f.emp_id(+)
                AND a.dose_inspected_by = g.emp_id(+)
                AND a.dose_verified_by = h.emp_id(+)
                AND a.log_prepared_by = i.emp_id(+)
                AND a.log_checked_by = j.emp_id(+)
                AND a.log_verified_by = k.emp_id(+)
                AND a.received_by = l.emp_id(+)
                AND a.jobc_verified_by = m.emp_id(+)
                AND a.brand = n.brand_id
                AND a.id||'|'|| a.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);

    //Detail
    $sql = " SELECT a.pallet_no, a.quantity_of_cartons, a.size_code, a.lot_no,
                    a.damage_data, decode(status, 'P', 'Pass', 'F', 'Fail', null) status, 
                    b.employee_name as inspected_by, 
                    to_char(a.inspected_date,'DD/MM/YYYY') inspected_date, 
                    decode(lock_rel,'1','Y','') lock_rel, a.racking_no, 
                    c.employee_name as picked_by, 
                    to_char(a.picked_by_date,'DD/MM/YYYY') picked_by_date,
                    a.remarks, a.remarks_recv
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_tbl a,
                    production.employee_master b,
                    production.employee_master c
              WHERE a.inspected_by = b.emp_id(+)
                AND a.picked_by = c.emp_id(+)
                AND a.inspection_id = :insp_id
           ORDER BY a.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);

    //Dosemetry 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 = :insp_id
           ORDER BY 1";
    
    $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_dose, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Operator Log
    $sql = " SELECT tote_no, quantity, loading_loc, unloading_loc, 
                    to_char(loading_date, 'DD/MM/YYYY HH24:MI') loading_date, 
                    to_char(unloading_date, 'DD/MM/YYYY HH24:MI') unloading_date, 
                    remarks
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_op
              WHERE inspection_id = :insp_id
           ORDER BY tote_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_op, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //S Racking
    $sql = " SELECT a.pallet_no, a.quantity_of_cartons, nvl(a.outgoing_qty,0) outgoing_qty, 
                    a.quantity_of_cartons - nvl(a.outgoing_qty,0) quantity_left, a.size_code, 
                    a.racking_no, b.employee_name picked_by, 
                    to_char(a.picked_by_date, 'DD/MM/YYYY') picked_by_date
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl a, 
                    production.employee_master b
              WHERE a.picked_by = b.emp_id(+)
                AND a.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_stl, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Stage 1
    $sql = " SELECT min(nvl(lock_rel,'0')) lock_rel, max(racking_no) racking_no
               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"]);
    $r = oci_execute($sqlarr);
    if (!$r) {
         $e = oci_error($sqlarr);
         print json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_stage1, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Stage 2
    $sql = " SELECT max(racking_no) racking_no
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl
              WHERE inspection_id = :insp_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_stage2, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $result[] = [
                    "main" => $rows_main,
                    "details" => $rows_detail,
                    "dosimetry" => $rows_dose,
                    "oplog" => $rows_op,
                    "sracking" => $rows_stl,
                    "stage1" => $rows_stage1,
                    "stage2" => $rows_stage2,
                ];    
 
    print json_encode($result);
?>