<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $sql = " SELECT a.id, a.customer, a.brand, a.ref_no, a.irrd, a.serial_no, a.total_ctn, a.cust_order_no,
                    nvl(a.carton_w, b.carton_w) carton_w, nvl(a.carton_l, b.carton_l) carton_l, 
                    nvl(a.carton_h, b.carton_h) carton_h, a.gamma_batch_no, a.blank_batch_no, 
                    nvl(a.product_valid_code, b.validation_code) product_valid_code, 
                    nvl(a.loading_pattern, b.loading_pattern) 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, 
                    nvl(a.carton_density, b.carton_density) carton_density, a.width_in_tote,
                    nvl(a.tote_pack_density, b.packing_density) tote_pack_density, a.cycle_time,
                    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, 
                    a.jobc_prepared_by, to_char(a.jobc_prepared_date,'YYYY-MM-DD') jobc_prepared_date, 
                    a.jobc_checked_by, to_char(a.jobc_checked_date, 'YYYY-MM-DD') jobc_checked_date, 
                    a.jobc_verified_by, to_char(a.jobc_verified_date, 'YYYY-MM-DD') jobc_verified_date,
                    a.jobc_insp_by, to_char(a.jobc_insp_date,'YYYY-MM-DD') jobc_insp_date, 
                    a.jobc_approved_by, to_char(a.jobc_approved_date, 'YYYY-MM-DD') jobc_approved_date,
                    a.application_status, a.log_prepared_by, a.log_prepared_date, a.irradiation_lock
               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')";
    
    $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 ('GAMMA_INDICATOR_BATCH_NO','BLANK_LABEL_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);

    //Gamma Indicator Batch No
    if (empty($rows_main[0]["GAMMA_BATCH_NO"])) {
        $idx = array_search("GAMMA_INDICATOR_BATCH_NO", array_column($rows_fixed, 'CATEGORY_TYPE'));
        $gamma_batch_no = $rows_fixed[$idx]["CATEGORY_VALUE"];
        $rows_main[0]["GAMMA_BATCH_NO"] = $gamma_batch_no;
    }

    //Blank Label Batch No
    if (empty($rows_main[0]["BLANK_BATCH_NO"])) {
        $idx = array_search("BLANK_LABEL_BATCH_NO", array_column($rows_fixed, 'CATEGORY_TYPE'));
        $blanklbl_batch_no = $rows_fixed[$idx]["CATEGORY_VALUE"];
        $rows_main[0]["BLANK_BATCH_NO"] = $blanklbl_batch_no;
    }

    //Check if all rows already inspected
    $sql = " SELECT COUNT(id) ctr
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_tbl
              WHERE inspection_id = :insp_id
                AND nvl(lock_rel, '0')= '0'";

    $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_valid, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    
    if ($rows_valid[0]["CTR"] == "0") {
        //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];

        //Prepared By, Checked By, Verified By, Inspected By, Approved By
        $sql = "SELECT emp_id id, employee_name text
                FROM production.employee_master
                WHERE emp_id in (SELECT employee
                                    FROM ( SELECT jobc_prepared_by, jobc_checked_by, jobc_verified_by, jobc_insp_by, jobc_approved_by
                                            FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl
                                            WHERE id = :insp_id) 
                                UNPIVOT ( employee FOR col IN ( jobc_prepared_by, jobc_checked_by, jobc_verified_by, jobc_insp_by, jobc_approved_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]["JOBC_PREPARED_BY"])) {
            $prepared = array_search($rows_main[0]["JOBC_PREPARED_BY"], array_column($hod, 'ID'));
            $prepared_by = $hod[$prepared];
        } else {
            $prepared_by = [ "ID" => "", "TEXT" => "" ]; 
        }

        //Checked By
        if (!empty($rows_main[0]["JOBC_CHECKED_BY"])) {
            $checked = array_search($rows_main[0]["JOBC_CHECKED_BY"], array_column($hod, 'ID'));
            $checked_by = $hod[$checked];
        } else {
            $checked_by = [ "ID" => "", "TEXT" => "" ]; 
        }

        //Verified By
        if (!empty($rows_main[0]["JOBC_VERIFIED_BY"])) {
            $verified = array_search($rows_main[0]["JOBC_VERIFIED_BY"], array_column($hod, 'ID'));
            $verified_by = $hod[$verified];
        } else {
            $verified_by = [ "ID" => "", "TEXT" => "" ]; 
        }

        //Inspected By
        if (!empty($rows_main[0]["JOBC_INSP_BY"])) {
            $insp = array_search($rows_main[0]["JOBC_INSP_BY"], array_column($hod, 'ID'));
            $insp_by = $hod[$insp];
        } else {
            $insp_by = [ "ID" => "", "TEXT" => "" ]; 
        }

        //Approved By
        if (!empty($rows_main[0]["JOBC_APPROVED_BY"])) {
            $approved = array_search($rows_main[0]["JOBC_APPROVED_BY"], array_column($hod, 'ID'));
            $approved_by = $hod[$approved];
        } else {
            $approved_by = [ "ID" => "", "TEXT" => "" ]; 
        }

        $rows_main[0]["JOBC_PREPARED_BY"] = $prepared_by;
        $rows_main[0]["JOBC_CHECKED_BY"] = $checked_by;
        $rows_main[0]["JOBC_VERIFIED_BY"] = $verified_by;
        $rows_main[0]["JOBC_INSP_BY"] = $insp_by;
        $rows_main[0]["JOBC_APPROVED_BY"] = $approved_by;

    } else {
        $rows_main[0]["ID"] = null;
        $err_msg = "Data still pending inspection process";
    }

    $result[] = [
                    "main" => $rows_main,
                    "message" => $err_msg
                ];    
 
    print json_encode($result);
?>