<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data)) {
        // if ($data->ROUTENAME == "stageedit") {
        //     $stageQuery = " AND :STAGE = :STAGE ";
        
        // } else if ($data->ROUTENAME == "approvalpage") {
        //     $stageQuery = " AND APPROVAL_POS = :STAGE ";
        // }

        $sql = "SELECT a.ID, SKU, SKU_REQUIREMENT, VOLUME_EXP1, VOLUME_EXP2, VOLUME_EXP3, PRICE_EXP, CUSTOMER, 
                       CUSTOMER_CODE, SEGMENT_TERRITORY, MEDICAL, FOOD_GRADE, PPE, INDUSTRIAL, SEGMENT_OTH, 
                       PROTOTYPE_REQUIRED, FEASIBLE_WO_DEV, EXPECT_CHANGES_FORM,
                       COST_CHANGE, TRIAL_REQ, QUALIFICATION_REQ, OQ, PQ, TIMING_PROTOTYPE, RESOURCES_REQUIRED, 
                       TECHNICAL_RECOMMEND, DISCUSS_WITH_OPS, COST_OF_CERT, TIMING_CERT, TIMING_TRIAL, FINAL_SIGNOFF, 
                       FINAL_SIGNOFF_DATE, CERT_REQUIRED, AQL_REQUIREMENT, MANUFACTURE_REVIEW, SALES_NAME, REQUEST_DATE, 
                       CUSTOMER_ADDRESS, GLOVE_COLOUR, GLOVE_TYPE, TARGET_WEIGHT, GLOVE_LENGTH, GLOVE_SIZE, GLOVE_SURFACE, 
                       GLOVE_DONNING, SPECIAL_FEATURES, SPECIAL_REQUEST, ATTENTION_TO, DELV_ADDRESS, CONTACT_NUMBER, 
                       TO_CHAR(SEND_OUT_BY_DATE, 'YYYY-MM-DD') SEND_OUT_BY_DATE, GEOGRAPHICAL, COUNTRY, PARTY_SITE_ID, STAGE1_REMARKS, 
                       EXPECT_CHANGES_PROC, FORMULATION_REMARKS, PROD_DEV_REMARKS, STAGE2_REMARKS, STAGE3_REMARKS, STAGE4_REMARKS,
                       STAGE5_REMARKS, TIMING_OQ, TIMING_PQ, VOLUME_REMARKS, TO_CHAR(CLOSE_ON, 'YYYY-MM-DD') CLOSE_ON, TRIAL_RESULT,
                       APPROVAL_POS, STAGE_POS, null STATUS, null MODIFIED_BY, SALES_ID, EMPLOYEE_NAME SALES_NAME
                  FROM ytycustom.ple_header a, production.employee_master
                 WHERE a.ID = :ID
                   AND LPAD(SALES_ID,9,'0') = EMP_ID(+)
                   AND (STAGE_POS >= :STAGE OR STAGE_POS = -1)";
                 
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':ID', $data->ID);
        oci_bind_by_name($sqlarr, ':STAGE', $data->STAGE);
        $r = oci_execute($sqlarr);
        oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        if (!empty($rows_main)) {
            // if (!empty($rows_main[0]["GLOVE_SIZE"])) {
            //     $size_val = [ "ID" => $rows_main[0]["GLOVE_SIZE"], "TEXT" => $rows_main[0]["GLOVE_SIZE"] ]; 
            //     $rows_main[0]["GLOVE_SIZE"] = $size_val;
            // }

            if (!empty($rows_main[0]["SALES_ID"])) {
                $sales_val = [ "ID" => $rows_main[0]["SALES_ID"], "TEXT" => $rows_main[0]["SALES_NAME"] ]; 
                $rows_main[0]["SALES_ID"] = $sales_val;
            }

            if (!empty($rows_main[0]["GLOVE_SIZE"])) {
                $sizee = explode(', ', $rows_main[0]["GLOVE_SIZE"]);

                $gsizee = array();
                foreach ($sizee as $txt) {
                    array_push($gsizee, [ "ID" => $txt, "TEXT" => $txt ]);
                }
                $rows_main[0]["GLOVE_SIZE"] = $gsizee;
            }

            if (!empty($rows_main[0]["MEDICAL"])) {
                if ($rows_main[0]["MEDICAL"] == 1) {
                    $medical_val = true;
                } else {
                    $medical_val = false;
                }
                $rows_main[0]["MEDICAL"] = $medical_val;
            }

            if (!empty($rows_main[0]["FOOD_GRADE"])) {
                if ($rows_main[0]["FOOD_GRADE"] == 1) {
                    $foodgrade_val = true;
                } else {
                    $foodgrade_val = false;
                }
                $rows_main[0]["FOOD_GRADE"] = $foodgrade_val;
            }

            if (!empty($rows_main[0]["PPE"])) {
                if ($rows_main[0]["PPE"] == 1) {
                    $ppe_val = true;
                } else {
                    $ppe_val = false;
                }
                $rows_main[0]["PPE"] = $ppe_val;
            }

            if (!empty($rows_main[0]["INDUSTRIAL"])) {
                if ($rows_main[0]["INDUSTRIAL"] == 1) {
                    $industrial_val = true;
                } else {
                    $industrial_val = false;
                }
                $rows_main[0]["INDUSTRIAL"] = $industrial_val;
            }

            if (!empty($rows_main[0]["OQ"])) {
                if ($rows_main[0]["OQ"] == 1) {
                    $oq_val = true;
                } else {
                    $oq_val = false;
                }
                $rows_main[0]["OQ"] = $oq_val;
            }

            if (!empty($rows_main[0]["PQ"])) {
                if ($rows_main[0]["PQ"] == 1) {
                    $pq_val = true;
                } else {
                    $pq_val = false;
                }
                $rows_main[0]["PQ"] = $pq_val;
            }

            if (!empty($rows_main[0]["SEGMENT_TERRITORY"])) {
                $segg = explode(', ', $rows_main[0]["SEGMENT_TERRITORY"]);

                $segments = array();
                foreach ($segg as $txt) {
                    array_push($segments, [ "ID" => $txt, "TEXT" => $txt ]);
                }
                $rows_main[0]["SEGMENT_TERRITORY"] = $segments;
            }

            if (!empty($rows_main[0]["SPECIAL_FEATURES"])) {
                $spec_feat = explode(', ', $rows_main[0]["SPECIAL_FEATURES"]);
                $rows_main[0]["SPECIAL_FEATURES"] = $spec_feat;
            }

            $sql = "SELECT ID, HEADER_ID, C_510K, ASTM_D6319, EN_455, ASTM_EN_455, ISO_11193, AS_NZS_4011, 
                           EN_16523_1, PPE_EU, ASTM_D6978, ISO_10993_5, ELUTION_TEST, HEAVY_METAL, JAPAN, 
                           CHINA, EU, US, OTHERS_REQ, ASTM_F1671, ENDOTOXIN_LAL, PYROGEN, BIOBURDEN, SILICONE_OIL, 
                           DRAIZE_95, AS_NZS_4011_1, COEFICIENT_FRICTION, BIO_COMPATIBILITY, SEM, VOC, GRIP_TEST, 
                           NFPA_1999, ISO_10993_11, OTHERS_CC
                      FROM ytycustom.ple_regulatory
                     WHERE HEADER_ID = :ID";
    
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':ID', $data->ID);
            $r = oci_execute($sqlarr);
            oci_fetch_all($sqlarr, $rows_reg, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    
            $sql = "SELECT t.action
                      FROM ytycustom.ple_approval_stages t
                     WHERE NOT EXISTS
                            (SELECT 1
                               FROM ytycustom.ple_approval_stages s
                              WHERE t.header_id = s.header_id
                                AND s.action_date > t.action_date)
                       AND t.stage_pos = 1
                       AND t.action IS NOT NULL
                       AND t.header_id = :header_id
                     UNION
                    SELECT 'REQ. INFO' action 
                      FROM ytycustom.ple_information x
                     WHERE x.info_desc IS NULL
                       AND x.header_id = :header_id";
    
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':header_id', $data->ID);
            $r = oci_execute($sqlarr);
            oci_fetch_all($sqlarr, $rows_sts, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    
            $rows_main[0]["STATUS"] = $rows_sts[0]["ACTION"];
            $resMsg = "OK";
    
            if (!$r) {
                $e = oci_error($sqlarr);
                $resMsg = json_encode($e['message']);
            } 
        } else {
            $resMsg = "No data found.";    
        }
    
    } else {
        $resMsg = "Wrong parameters.";
    }

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_main,
        "regulatory" => $rows_reg
    ];

    print json_encode($result, JSON_NUMERIC_CHECK);
?>