<?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
              FROM apps.YTY_G_PRC_MODIFIER_LIST_TL
             WHERE period = :period
               AND currency_code = :currency
               AND created_by = :userid
               AND id = :id
          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);
    $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
    $uom = [ "ID" => $rows_main[0]["PRODUCT_UOM_CODE"], "TEXT" => $rows_main[0]["PRODUCT_UOM_CODE"] ]; 
    $rows_main[0]["PRODUCT_UOM_CODE"] = $uom;

    //SKU    
    // $sql = "SELECT DISTINCT b.inventory_item_id, a.sku id, b.description text
    //           FROM apps.YTY_ITEM_MAPPING_SKUSIZEBRAND_V a, 
    //                apps.MTL_SYSTEM_ITEMS_B b
    //          WHERE a.sku = SUBSTR(b.SEGMENT1,1,INSTR(b.SEGMENT1,'-',2,2)-1)
    //            AND b.ITEM_TYPE = 'SFGL'
    //            AND a.sku = :sku ";

    $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);
    $rows_main[0]["PRODUCT_ATTR_VALUE"] = $rows_sku[0];

    //Size
    $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
        $sql = "SELECT DISTINCT a.brand id, a.brand text
                  FROM apps.YTY_ITEM_MAPPING_SKUSIZEBRAND_V a
                 WHERE a.sku = :sku
                   AND a.p_size = :siz
              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
        $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];
    }

    //Bill To
    $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
    $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_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;

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

    if (!empty($rows_main[0]["SHIP_TO"])) {
        //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"])) {
        
        $sql = "SELECT packing_type_code id,
                       '[ ' || packing_type || ' ] ' || description text
                  FROM apps.XXYTY_PACKING_TYPE_V
                 WHERE packing_type_code = :packing_type";
    
        $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);
?>