<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $newid = $data->ID;
    $sql = "UPDATE YTYCUSTOM.XX_YTY_INCOMING_OUTGOING_INSPECTION_SHIP_HD
               SET STATUS = 'R',
                   MODIFIED_BY = :MODIFIED_BY,
                   MODIFIED_DATE = SYSDATE
             WHERE ID = :SHIP_ID";
        
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':MODIFIED_BY', $data->USERID);
    oci_bind_by_name($sqlarr, ':SHIP_ID', $newid);
    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    //Deduct Qty from rack
    // $sql_det = "MERGE INTO ytycustom.gth_rack_master c
    //             USING (
    //                 SELECT 
    //                     a.racking_no, 
    //                     (a.quantity_of_cartons - NVL(a.outgoing_qty, 0)) AS new_quantity
    //                 FROM 
    //                     ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl a
    //                 INNER JOIN 
    //                     ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl_part b
    //                     ON a.inspection_id = b.inspection_id
    //                     AND a.id = b.stl_id
    //                 WHERE 
    //                     b.ship_id = :ship_id
    //             ) src
    //             ON (c.rack_code = src.racking_no)
    //             WHEN MATCHED THEN
    //                 UPDATE SET c.quantity = src.new_quantity";

    $sql_det = "MERGE INTO ytycustom.gth_rack_master c
                USING (
                    SELECT 
                        a.racking_no, 
                        SUM(a.quantity_of_cartons - NVL(a.outgoing_qty, 0)) AS new_quantity
                    FROM 
                        ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl a
                    INNER JOIN 
                        ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl_part b
                        ON a.inspection_id = b.inspection_id
                        AND a.id = b.stl_id
                    WHERE 
                        b.ship_id = :ship_id
                    GROUP BY
                        a.racking_no
                ) src
                ON (c.rack_code = src.racking_no)
                WHEN MATCHED THEN
                    UPDATE SET c.quantity = src.new_quantity";

    $sqlarr_det = oci_parse($dbcon , $sql_det);
    oci_bind_by_name($sqlarr_det, ':ship_id', $newid);
    $r = oci_execute($sqlarr_det, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr_det);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    //Empty the rack
    $sql_det = " UPDATE ytycustom.gth_rack_master
                    SET occupied = NULL, 
                        ref_no = NULL, 
                        customer = NULL,
                        brand = NULL, 
                        size_code = NULL,
                        quantity = NULL,
                        irrd = NULL,
                        serial_no = NULL,
                        inspection_id = NULL,
                        line_id = NULL
                    WHERE rack_code IN (SELECT b.racking_no 
                                        FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl_part a,
                                                ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl b
                                        WHERE a.stl_id = b.id
                                            AND a.ship_id = :ship_id
                                            AND b.outgoing_qty = b.quantity_of_cartons)";
    $sqlarr_det = oci_parse($dbcon , $sql_det);
    oci_bind_by_name($sqlarr_det, ':ship_id', $newid);
    $r = oci_execute($sqlarr_det, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr_det);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $newid
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => ""
        ];
    }
    print json_encode($result);
?>