<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->ID)) {
        //Just for return value
        $newid = $data->ID;
        $emailStatus = "1";

        //Reset Verification Type
        $sql = "UPDATE YTYCUSTOM.XX_YTY_INCOMING_OUTGOING_INSPECTION_TBL
                   SET DOSE_MAPPING = null,
                       ROUTINE_IRRD = null,
                       PROD_PACK_EVAL = null,
                       DOSE_VERIFICATION = null
                 WHERE ID = :INSPECTION_ID"; 

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':INSPECTION_ID', $data->ID);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        if ($data->VERIFICATION_TYPE) {
            $verification_str = ", ".$data->VERIFICATION_TYPE." = 'Y'";
        }

        //Existing Data
        $sql = "UPDATE YTYCUSTOM.XX_YTY_INCOMING_OUTGOING_INSPECTION_TBL
                   SET IRRD = :IRRD,
                       SERIAL_NO = :SERIAL_NO,
                       CUST_ORDER_NO = :CUST_ORDER_NO,
                       CARTON_W = :CARTON_W,
                       CARTON_L = :CARTON_L,
                       CARTON_H = :CARTON_H,
                       GAMMA_BATCH_NO = :GAMMA_BATCH_NO,
                       BLANK_BATCH_NO = :BLANK_BATCH_NO,
                       CERTIFICATE_NO = :CERTIFICATE_NO,
                       PRODUCT_VALID_CODE = :PRODUCT_VALID_CODE,
                       LOADING_PATTERN = :LOADING_PATTERN,
                       DOSE_REQUIRED = :DOSE_REQUIRED,
                       MAXIMUM_ACC_DOSE = :MAXIMUM_ACC_DOSE,
                       MINIMUM_ACC_DOSE = :MINIMUM_ACC_DOSE,
                       CARTON_W1 = :CARTON_W1,
                       CARTON_W2 = :CARTON_W2,
                       CARTON_W3 = :CARTON_W3,
                       CARTON_W4 = :CARTON_W4,
                       CARTON_W5 = :CARTON_W5,
                       TOTAL_WEIGHT = :TOTAL_WEIGHT,
                       AVG_WEIGHT = :AVG_WEIGHT,
                       CARTON_DENSITY = :CARTON_DENSITY,
                       WIDTH_IN_TOTE = :WIDTH_IN_TOTE,
                       TOTE_PACK_DENSITY = :TOTE_PACK_DENSITY,
                       JOBC_PREPARED_BY = :JOBC_PREPARED_BY,
                       JOBC_PREPARED_DATE = TO_DATE(:JOBC_PREPARED_DATE,'YYYY-MM-DD'),
                       JOBC_CHECKED_BY = :JOBC_CHECKED_BY,
                       JOBC_CHECKED_DATE = TO_DATE(:JOBC_CHECKED_DATE,'YYYY-MM-DD'),
                       JOBC_VERIFIED_BY = :JOBC_VERIFIED_BY,
                       JOBC_VERIFIED_DATE = TO_DATE(:JOBC_VERIFIED_DATE,'YYYY-MM-DD'),
                       LOG_VERIFIED_BY = :JOBC_VERIFIED_BY,
                       LOG_VERIFIED_DATE = TO_DATE(:JOBC_VERIFIED_DATE,'YYYY-MM-DD'),
                       --MIN_DOSE_STATUS = :MIN_DOSE_STATUS,
                       --MAX_DOSE_STATUS = :MAX_DOSE_STATUS,
                       CYCLE_TIME = :CYCLE_TIME,
                       --JOBC_INSP_BY = :JOBC_INSP_BY,
                       --JOBC_INSP_DATE = TO_DATE(:JOBC_INSP_DATE,'YYYY-MM-DD'),
                       --JOBC_APPROVED_BY = :JOBC_APPROVED_BY,
                       --JOBC_APPROVED_DATE = TO_DATE(:JOBC_APPROVED_DATE,'YYYY-MM-DD'),
                       APPLICATION_STATUS = '2',
                       MODIFIED_BY = :MODIFIED_BY,
                       MODIFIED_AT = SYSDATE
                       ".$verification_str."
                 WHERE ID = :INSP_ID";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':IRRD', $data->IRRD);
        oci_bind_by_name($sqlarr, ':SERIAL_NO', $data->SERIAL_NO);
        oci_bind_by_name($sqlarr, ':CUST_ORDER_NO', $data->CUST_ORDER_NO);
        oci_bind_by_name($sqlarr, ':CARTON_W', $data->CARTON_W);
        oci_bind_by_name($sqlarr, ':CARTON_L', $data->CARTON_L);
        oci_bind_by_name($sqlarr, ':CARTON_H', $data->CARTON_H);
        oci_bind_by_name($sqlarr, ':GAMMA_BATCH_NO', $data->GAMMA_BATCH_NO);
        oci_bind_by_name($sqlarr, ':BLANK_BATCH_NO', $data->BLANK_BATCH_NO);
        oci_bind_by_name($sqlarr, ':CERTIFICATE_NO', $data->CERTIFICATE_NO);
        oci_bind_by_name($sqlarr, ':PRODUCT_VALID_CODE', $data->PRODUCT_VALID_CODE);
        oci_bind_by_name($sqlarr, ':LOADING_PATTERN', $data->LOADING_PATTERN);
        oci_bind_by_name($sqlarr, ':DOSE_REQUIRED', $data->DOSE_REQUIRED);
        oci_bind_by_name($sqlarr, ':MAXIMUM_ACC_DOSE', $data->MAXIMUM_ACC_DOSE);
        oci_bind_by_name($sqlarr, ':MINIMUM_ACC_DOSE', $data->MINIMUM_ACC_DOSE);
        oci_bind_by_name($sqlarr, ':CARTON_W1', $data->CARTON_W1);
        oci_bind_by_name($sqlarr, ':CARTON_W2', $data->CARTON_W2);
        oci_bind_by_name($sqlarr, ':CARTON_W3', $data->CARTON_W3);
        oci_bind_by_name($sqlarr, ':CARTON_W4', $data->CARTON_W4);
        oci_bind_by_name($sqlarr, ':CARTON_W5', $data->CARTON_W5);
        oci_bind_by_name($sqlarr, ':TOTAL_WEIGHT', $data->TOTAL_WEIGHT);
        oci_bind_by_name($sqlarr, ':AVG_WEIGHT', $data->AVG_WEIGHT);
        oci_bind_by_name($sqlarr, ':CARTON_DENSITY', $data->CARTON_DENSITY);
        oci_bind_by_name($sqlarr, ':WIDTH_IN_TOTE', $data->WIDTH_IN_TOTE);
        oci_bind_by_name($sqlarr, ':TOTE_PACK_DENSITY', $data->TOTE_PACK_DENSITY);
        oci_bind_by_name($sqlarr, ':JOBC_PREPARED_BY', $data->JOBC_PREPARED_BY->ID);
        oci_bind_by_name($sqlarr, ':JOBC_PREPARED_DATE', $data->JOBC_PREPARED_DATE);
        oci_bind_by_name($sqlarr, ':JOBC_CHECKED_BY', $data->JOBC_CHECKED_BY->ID);
        oci_bind_by_name($sqlarr, ':JOBC_CHECKED_DATE', $data->JOBC_CHECKED_DATE);
        oci_bind_by_name($sqlarr, ':JOBC_VERIFIED_BY', $data->JOBC_VERIFIED_BY->ID);
        oci_bind_by_name($sqlarr, ':JOBC_VERIFIED_DATE', $data->JOBC_VERIFIED_DATE);
        //oci_bind_by_name($sqlarr, ':MIN_DOSE_STATUS', $data->MIN_DOSE_STATUS);
        //oci_bind_by_name($sqlarr, ':MAX_DOSE_STATUS', $data->MAX_DOSE_STATUS);
        oci_bind_by_name($sqlarr, ':CYCLE_TIME', $data->CYCLE_TIME);
        //oci_bind_by_name($sqlarr, ':JOBC_INSP_BY', $data->JOBC_INSP_BY->ID);
        //oci_bind_by_name($sqlarr, ':JOBC_INSP_DATE', $data->JOBC_INSP_DATE);
        //oci_bind_by_name($sqlarr, ':JOBC_APPROVED_BY', $data->JOBC_APPROVED_BY->ID);
        //oci_bind_by_name($sqlarr, ':JOBC_APPROVED_DATE', $data->JOBC_APPROVED_DATE);
        oci_bind_by_name($sqlarr, ':MODIFIED_BY', $data->USERID);
        oci_bind_by_name($sqlarr, ':INSP_ID', $data->ID);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }  

        if (empty($data->JOBC_INSP_BY->ID) && empty($data->JOBC_APPROVED_BY->ID)) {

            //Check If Email Notification has been sent before
            $sql = "SELECT nvl(jobc_insp_email, 0) jobc_insp_email,
                           nvl(jobc_approve_email, 0) jobc_approve_email
                      FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl
                     WHERE id = :inspection_id";
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':inspection_id', $data->ID);
            oci_execute($sqlarr);
            while ($row = oci_fetch_array($sqlarr)) {
                $emailStatus = $row['JOBC_APPROVE_EMAIL'];
            }
        }
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $newid,
            "email" => $emailStatus
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => "",
            "email" => $emailStatus
        ];
    }
    print json_encode($result);
?>