<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->period->ID)) {
          $wherePeriod = " AND a.PERIOD = '".$data->period->ID."' ";
    }

    if (!empty($data->currency->ID)) {
          $whereCurrency = " AND a.CURRENCY_CODE = '".$data->currency->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, c.EMPLOYEE_NAME CREATED_BY,
                   a.CREATED_BY CREATED_BY_ID,
                   a.COMMENTS, CASE a.APPROVAL_FLAG 
                                    WHEN 'A' THEN 'Approved'
                                    WHEN 'R' THEN 'Rejected'
                                    WHEN 'P' THEN 'In Progress'
                               ELSE 'Pending' END Status,
                   a.approver_comments
              FROM apps.yty_g_prc_modifier_list_tl a,
                   production.size_master b,
                   production.employee_master c
             WHERE a.SKU_SIZE = b.siz_main(+)
               AND a.CREATED_BY = c.EMP_ID
               AND CASE a.APPROVAL_FLAG 
                        WHEN 'A' THEN 'Approved'
                        WHEN 'R' THEN 'Rejected'
                        WHEN 'P' THEN 'In Progress'
                   ELSE 'Pending' END = 'In Progress' 
               ".$wherePeriod.$whereCurrency."
          ORDER BY a.customer_name, a.product_attr_value";

    $sqlarr = oci_parse($dbcon , $sql);
    $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);

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_main
    ];

    print json_encode($result);
?>