<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->status)) {
        $whereStatus = " AND CASE APPROVAL_FLAG 
                                WHEN 'A' THEN 'Approved'
                                WHEN 'R' THEN 'Rejected'
                                WHEN 'P' THEN 'In Progress'
                             ELSE 'New' END = '".$data->status."' ";
    }

    if (!empty($data->customer)) {
        $whereCustomer = " AND customer_id = '".$data->customer->ID."' ";
    }

    $sql = "SELECT a.ID, a.NAME, a.PERIOD, a.CURRENCY_CODE, a.PRODUCT_ATTR_VALUE, a.CATEGORY_ID,
                   a.DESCRIPTION ITEM_DESCRIPTION, a.SKU_SIZE, b.SIZ_NM, a.INCOTERM, a.BRAND, a.PRODUCT_UOM_CODE,
                   a.CUSTOMER_ID, a.CUSTOMER_NAME, a.VOLUME, a.NEW_PRICE, a.BILL_TO, a.SHIP_TO,
                   a.COMMENTS, a.APPROVER_COMMENTS, a.PACKING_TYPE, c.EMPLOYEE_NAME CREATED_BY,
                   CASE a.APPROVAL_FLAG 
                      WHEN 'A' THEN 'Approved'
                      WHEN 'R' THEN 'Rejected'
                      WHEN 'P' THEN 'In Progress'
                   ELSE 'New' END Status,
                   XXYTYCPORT.FETCH_ORDER_LIST(TO_DATE(a.PERIOD,'MON-RR'),
                                               a.CUSTOMER_ID,
                                               b.SIZ_NM,
                                               a.BRAND,
                                               a.PRODUCT_ATTR_VALUE) order_number,
                   CASE WHEN a.packing_type IS NOT NULL THEN
                      '[ ' || d.packing_type || ' ] ' || d.description 
                   END packing_type
              FROM apps.yty_g_prc_modifier_list_tl a,
                   production.size_master b,
                   production.employee_master c,
                   apps.xxyty_packing_type_v d
             WHERE a.SKU_SIZE = b.siz_main(+)
               AND a.CREATED_BY = c.EMP_ID 
               AND a.PACKING_TYPE = d.PACKING_TYPE_CODE(+)
               AND a.period = :period
               AND a.currency_code = :currency
               AND a.created_by = :userid
               ".$whereStatus.$whereCustomer."
          ORDER BY a.customer_name, a.product_attr_value";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':period', $data->period->ID);
    oci_bind_by_name($sqlarr, ':currency', $data->currency->ID);
    oci_bind_by_name($sqlarr, ':userid', $data->userid);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if (in_array('New', array_column($rows_main, 'STATUS'))) {
        $submit = "Y";
    } else {
        if (in_array('Rejected', array_column($rows_main, 'STATUS'))) {
            $submit = "Y";
        } else {
            $submit = "N";
        }
    }

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_main,
        "submit" => $submit 
    ];

    print json_encode($result);
?>