<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $sql = "SELECT ID, NAME, PERIOD, CURRENCY_CODE, PRODUCT_ATTR_VALUE, CATEGORY_ID,
                   DESCRIPTION ITEM_DESCRIPTION, SKU_SIZE, INCOTERM, BRAND, PRODUCT_UOM_CODE,
                   CUSTOMER_ID, CUSTOMER_NAME, VOLUME, NEW_PRICE, BILL_TO, SHIP_TO,
                   COMMENTS, CASE APPROVAL_FLAG 
                                WHEN 'A' THEN 'Approved'
                                WHEN 'R' THEN 'Rejected'
                                WHEN 'P' THEN 'In Progress'
                             ELSE null END Status, PACKING_TYPE,
                   SPR_FLAG, NULL SPR_SIZE, NULL SPR_PRICE
              FROM apps.YTY_G_PRC_MODIFIER_LIST_TL
             WHERE period = :period
               AND currency_code = :currency
               AND created_by = :userid
               AND id = :id
               AND NVL(company,'YTY') = NVL(:company,'YTY')
          ORDER BY customer_name, 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);
    oci_bind_by_name($sqlarr, ':id', $data->id);
    oci_bind_by_name($sqlarr, ':company', $data->comp);
    $r = oci_execute($sqlarr);

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //UOM
    if ($data->comp == 'MST') {
        $sql = "SELECT uom_code id, uom_desc text 
                  FROM APPS.XXYTY_PRICING_UOM_VW
              ORDER BY 1";
        
    } else if ($data->comp == 'YTY') {

        $sql = "SELECT uom_code id, unit_of_measure text 
                  FROM APPS.yty_g_prc_uom_code
              ORDER BY 1";
    }
    
    $sqlarr = oci_parse($dbcon , $sql);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows_uom, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    
    $idx = array_search($rows_main[0]["PRODUCT_UOM_CODE"], array_column($rows_uom, 'ID')); 
    $rows_main[0]["PRODUCT_UOM_CODE"] = $rows_uom[$idx];

    //SKU    
    if ($data->comp == 'MST') {
      $sql = " SELECT DISTINCT inventory_item_id, a.sku id, a.description text
                 FROM apps.MST_item_mapping_skusizebrand_v a
                WHERE 1=1
                  AND a.sku = :sku 
             ORDER BY 1";
        
    } else if ($data->comp == 'YTY') {

      $sql = " SELECT DISTINCT b.inventory_item_id, a.sku id, SUBSTR(b.description,1,INSTR(b.description,':',1,1)-1) text
                 FROM apps.YTY_ITEM_MAPPING_SKUSIZEBRAND_V a, 
                      apps.MTL_SYSTEM_ITEMS_B b
                WHERE a.sku = b.segment1
                  AND a.sku = :sku 
             ORDER BY 1";
    }

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':sku', $rows_main[0]["PRODUCT_ATTR_VALUE"]);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows_sku, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Select SKU
    $rows_main[0]["PRODUCT_ATTR_VALUE"] = $rows_sku[0];

    //Size
    if ($data->comp == 'MST') {
        $sql = "SELECT DISTINCT a.p_size id, a.p_size text
                  FROM apps.MST_item_mapping_skusizebrand_v a
                 WHERE a.sku = :p_sku
              ORDER BY a.p_size";
        
    } else if ($data->comp == 'YTY') {

          $sql = "SELECT DISTINCT a.p_size id, b.siz_nm text
                    FROM apps.YTY_ITEM_MAPPING_SKUSIZEBRAND_V a, 
                         production.size_master b
                   WHERE a.p_size = b.siz_main
                     AND a.sku = :p_sku
                ORDER BY b.siz_nm";
    }
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':p_sku', $rows_sku[0]["ID"]);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows_size, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //if (!empty($rows_main[0]["SKU_SIZE"])) {
        
    //Brand
    $whereSize = "";
    if (!empty($rows_main[0]["SKU_SIZE"])) {
        $whereSize = "AND a.p_size = '".$rows_main[0]["SKU_SIZE"]."'";
    }
    
    if ($data->comp == 'MST') {
        $sql = "SELECT DISTINCT a.brand_id id, a.brand_desc text
                    FROM apps.MST_item_mapping_skusizebrand_v a
                    WHERE a.sku = :sku
                    ".$whereSize."
                ORDER BY brand_desc";
        
    } else if ($data->comp == 'YTY') {

        $sql = "SELECT DISTINCT a.brand id, a.brand_desc text
                    FROM apps.YTY_ITEM_MAPPING_SKUSIZEBRAND_V a
                    WHERE a.sku = :sku
                    ".$whereSize."
                ORDER BY brand";
    }

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':sku', $rows_sku[0]["ID"]);
    oci_bind_by_name($sqlarr, ':siz', $rows_main[0]["SKU_SIZE"]);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows_brand, null, null, OCI_FETCHSTATEMENT_BY_ROW);
        
    //}

    //Select Size
    if (!empty($rows_main[0]["SKU_SIZE"])) {
        $idx = array_search($rows_main[0]["SKU_SIZE"], array_column($rows_size, 'ID')); 
        $rows_main[0]["SKU_SIZE"] = $rows_size[$idx];
    }

    if (!empty($rows_main[0]["BRAND"])) {
        
        //Select Brand
        $idx = array_search($rows_main[0]["BRAND"], array_column($rows_brand, 'ID')); 
        $rows_main[0]["BRAND"] = $rows_brand[$idx];
    }

    //SPR Size
    $sql = "SELECT spr_sku_size id, spr_size_nm text, spr_price
              FROM apps.yty_g_prc_modifier_list_spr
             WHERE header_id = :header_id";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':header_id', $rows_main[0]["ID"]);
    $r = oci_execute($sqlarr);

    $spr_size = array();
    $spr_price = '';
    while ($rw = oci_fetch_array($sqlarr)) {
        array_push($spr_size, [ "ID" => $rw['ID'], "TEXT" => $rw['TEXT'] ]);
        $spr_price = $rw['SPR_PRICE'];
    }
    $rows_main[0]["SPR_SIZE"] = $spr_size;
    $rows_main[0]["SPR_PRICE"] = $spr_price;

    //Bill To
    if ($data->comp == 'MST') {
        $sql = "SELECT BL.LOCATION ID, BL.LOCATION TEXT 
                  FROM APPS.MST_G_PRC_BILL_TO_LOV BL
                 WHERE BL.CUST_ACCOUNT_ID = :customerid";
        
    } else if ($data->comp == 'YTY') {

        $sql = "SELECT BL.LOCATION ID, BL.LOCATION TEXT 
                  FROM APPS.YTY_G_PRC_BILL_TO_LOV BL
                 WHERE BL.CUST_ACCOUNT_ID = :customerid";
    }

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':customerid', $rows_main[0]["CUSTOMER_ID"]);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows_billto, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Ship To
    if ($data->comp == 'MST') {
        $sql = "SELECT SL.LOCATION ID, SL.LOCATION TEXT 
                  FROM apps.mst_g_prc_ship_to_lov SL
                 WHERE SL.CUST_ACCOUNT_ID = :customerid";
        
    } else if ($data->comp == 'YTY') {

        $sql = "SELECT SL.LOCATION ID, SL.LOCATION TEXT 
                  FROM APPS.YTY_G_PRC_SHIP_TO_LOV SL
                 WHERE SL.CUST_ACCOUNT_ID = :customerid";
    }

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':customerid', $rows_main[0]["CUSTOMER_ID"]);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows_shipto, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Customer
    $customer = [ "ID" => $rows_main[0]["CUSTOMER_ID"], "TEXT" => $rows_main[0]["CUSTOMER_NAME"] ]; 
    $rows_main[0]["CUSTOMER_ID"] = $customer;

    //Bill To
    if (!empty($rows_main[0]["BILL_TO"])) {
        $idx = array_search($rows_main[0]["BILL_TO"], array_column($rows_billto, 'ID')); 
        $rows_main[0]["BILL_TO"] = $rows_billto[$idx];
    }

    //Ship To
    if (!empty($rows_main[0]["SHIP_TO"])) {
        $idx = array_search($rows_main[0]["SHIP_TO"], array_column($rows_shipto, 'ID')); 
        $rows_main[0]["SHIP_TO"] = $rows_shipto[$idx];
    }

    //Packing Type
    if (!empty($rows_main[0]["PACKING_TYPE"])) {
        if ($data->comp == 'MST') {
            $sql = "SELECT packing_scheme id, packing_scheme text
                      FROM apps.mst_packing_type_v
                     WHERE packing_scheme = :packing_type
                  ORDER BY 1";

        } else if ($data->comp == 'YTY') {

            $sql = "SELECT packing_type_code id,
                           trim('[ ' || packing_type || ' ] ' || description) text
                      FROM apps.XXYTY_PACKING_TYPE_V
                     WHERE packing_type_code = :packing_type
                  ORDER BY 1";
        }
        
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':packing_type', $rows_main[0]["PACKING_TYPE"]);
        $r = oci_execute($sqlarr);
        oci_fetch_all($sqlarr, $rows_packtype, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        //Select Packing Type
        $packtype = [ "ID" => $rows_packtype[0]["ID"], "TEXT" => $rows_packtype[0]["TEXT"] ]; 
        $rows_main[0]["PACKING_TYPE"] = $packtype;
    }

    //Number
    $rows_main[0]["VOLUME"] = (float)$rows_main[0]["VOLUME"]; 
    $rows_main[0]["NEW_PRICE"] = (float)$rows_main[0]["NEW_PRICE"];

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_main,
        "size_lst" => $rows_size,
        "brand_lst" => $rows_brand,
        "billto" => $rows_billto,
        "shipto" => $rows_shipto
    ];

    print json_encode($result);
?>