<?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.PACKING_TYPE,
                   a.COMMENTS, CASE a.APPROVAL_FLAG 
                                    WHEN 'A' THEN 'Approved'
                                    WHEN 'R' THEN 'Rejected'
                                    WHEN 'P' THEN 'In Progress'
                               ELSE 'New' END Status,
                   a.approver_comments,
                   to_char(to_date(a.period,'MON-RR')-1,'MON-RR') prev_period,
                   null prev_price,
                   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 CASE a.APPROVAL_FLAG 
                        WHEN 'A' THEN 'Approved'
                        WHEN 'R' THEN 'Rejected'
                        WHEN 'P' THEN 'In Progress'
                   ELSE 'New' END = 'In Progress' 
               ".$wherePeriod.$whereCurrency."
          ORDER BY TO_DATE(a.PERIOD,'MON-RR') DESC, 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);

    //Prev Price
    $i = 0;
    foreach($rows_main as $main) {
          //Clearing Phase
          $exact_prev = [];
          $size_prev = [];
          $brand_prev = [];

          $sql = "SELECT to_char(new_price) prev_price, (to_number(:p_price) - new_price) diff_price,
                         CASE
                           WHEN (to_number(:p_price) - new_price) > 0 THEN
                            '<i class=\"pi pi-arrow-up text-success\">+' || rtrim(to_char((to_number(:p_price) - new_price), 'FM90.99'), '.') || '</i>'
                           WHEN (to_number(:p_price) - new_price) < 0 THEN
                            '<i class=\"pi pi-arrow-down text-danger\">' || rtrim(to_char((to_number(:p_price) - new_price), 'FM90.99'), '.') || '</i>'
                         END html_diff_price,
                         product_attr_value, NVL(incoterm,'-') incoterm, NVL(brand,'-') brand, currency_code, period
                    FROM apps.YTY_G_PRC_MODIFIER_LIST_TL
                   WHERE period = :p_period
                     AND currency_code = :p_currency
                     AND customer_id = :p_custid
                     AND product_attr_value = :p_sku
                     AND NVL(approval_flag, 'N') = 'A'
                   UNION
                  SELECT '-', NULL, NULL, '-', '-', '-', NULL, '-' 
                    FROM dual
                   WHERE NOT EXISTS (SELECT new_price prev_price, (to_number(:p_price) - new_price) diff_price,
                                            CASE
                                              WHEN (to_number(:p_price) - new_price) > 0 THEN
                                               '<i class=\"pi pi-arrow-up text-success\">+' || (to_number(:p_price) - new_price) || '</i>'
                                              WHEN (to_number(:p_price) - new_price) < 0 THEN
                                               '<i class=\"pi pi-arrow-down text-danger\">' || (to_number(:p_price) - new_price) || '</i>'
                                              ELSE
                                               to_char((to_number(:p_price) - new_price))
                                            END html_diff_price,
                                            product_attr_value, incoterm, brand, currency_code
                                       FROM apps.YTY_G_PRC_MODIFIER_LIST_TL
                                      WHERE period = :p_period
                                        AND currency_code = :p_currency
                                        AND customer_id = :p_custid
                                        AND product_attr_value = :p_sku
                                        AND NVL(approval_flag, 'N') = 'A')";

          $sqlarr = oci_parse($dbcon , $sql);
          oci_bind_by_name($sqlarr, ':p_period', $main["PREV_PERIOD"]);
          oci_bind_by_name($sqlarr, ':p_currency', $main["CURRENCY_CODE"]);
          oci_bind_by_name($sqlarr, ':p_custid', $main["CUSTOMER_ID"]);
          oci_bind_by_name($sqlarr, ':p_sku', $main["PRODUCT_ATTR_VALUE"]);
          oci_bind_by_name($sqlarr, ':p_price', $main["NEW_PRICE"]);
          $r = oci_execute($sqlarr);

          if (!$r) {
               $e = oci_error($sqlarr);
               $resMsg = json_encode($e['message']);
          }
          oci_fetch_all($sqlarr, $rows_prev, null, null, OCI_FETCHSTATEMENT_BY_ROW);
          
          if (count($rows_prev) > 1) {
       
              //Filter Exactly
              foreach($rows_prev as $sub) {
                  if($sub["INCOTERM"] == $main["INCOTERM"] && $sub["BRAND"] == $main["BRAND"]) {
                      $exact_prev[] = $sub;
                  }
              }
       
              if (count($exact_prev) == 0) {
                  
                  //Filter with matching size only
                  foreach($rows_prev as $sub) {
                      if($sub["INCOTERM"] == $main["INCOTERM"] || $sub["INCOTERM"] == "-") {
                          $size_prev[] = $sub;
                      }
                  }
       
                  if (count($size_prev) > 0) {
                      $rows_prev = $size_prev;
                  }
       
                  //From the result filter again by brand
                  if (count($rows_prev) > 1) {
                      foreach($rows_prev as $sub) {
                          if($sub["BRAND"] == $main["BRAND"] || $sub["BRAND"] == "-") {
                              $brand_prev[] = $sub;
                          }
                      }
       
                      if (count($brand_prev) > 0) {
                          $rows_prev = $brand_prev;
                      }
                  }
       
              } else {
                  $rows_prev = $exact_prev;
              }
          }

          $rows_main[$i]["PREV_PRICE"] = $rows_prev[0]["PREV_PRICE"];
          $i++;
    } 

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_main
    ];

    print json_encode($result);
?>