<?php
    session_start();
    include "../db/headerApi.php";
    include "../db/connection_main.php";

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

    if (empty($data->ID)) {
        //Check if order already exist
        $sql = "SELECT XXYTYCPORT.FETCH_ORDER_LIST(TO_DATE(:p_period,'MON-RR'),
                                               :p_customer,
                                               null,
                                               null,
                                               substr(:p_sku,1,7)) order_no
                  FROM dual";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':p_period', $data->PERIOD->ID);
        oci_bind_by_name($sqlarr, ':p_customer', $data->CUSTOMER_ID->ID);
        oci_bind_by_name($sqlarr, ':p_sku', $data->PRODUCT_ATTR_VALUE->ID);
        $r = oci_execute($sqlarr);
        $order_number = '';
        while ($row = oci_fetch_array($sqlarr)) {
            $order_number = $row['ORDER_NO'];
        }

        if (empty($order_number)) {
            //$sizeWhere = '';
            //Check if exists
            if (!empty($data->SKU_SIZE->ID)) {
                $sizeWhere = " AND sku_size = '".$data->SKU_SIZE->ID."' ";
            } else {
                $sizeWhere = " AND sku_size IS NULL ";
            }
           // $brandWhere = '';
            if (!empty($data->BRAND->ID)) {
                $brandWhere = " AND brand = '".$data->BRAND->ID."' ";
            } else {
                $brandWhere = " AND brand IS NULL ";
            }
        
            $sql = "SELECT COUNT(1) ctr
                      FROM apps.yty_g_prc_modifier_list_tl
                     WHERE period = :period
                       AND customer_id = :customerid
                       AND product_attr_value = :sku
                       ".$sizeWhere."
                       ".$brandWhere;
            
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':period', $data->PERIOD->ID);
            oci_bind_by_name($sqlarr, ':customerid', $data->CUSTOMER_ID->ID);
            oci_bind_by_name($sqlarr, ':sku', $data->PRODUCT_ATTR_VALUE->ID);
            $r = oci_execute($sqlarr);
           // $exists = '';
            while ($row = oci_fetch_array($sqlarr)) {
                $exists = $row['CTR'];
            }
            
            if ($exists == "0") {
                $sql = "SELECT to_char(to_date(:period, 'MON-RR'), 'YYYY-MM-DD') start_date,
                               to_char(last_day(to_date(:period, 'MON-RR')), 'YYYY-MM-DD') end_date
                          FROM dual";
            
                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':period', $data->PERIOD->ID);
                $r = oci_execute($sqlarr);
                // oci_fetch_all($sqlarr, $row_period, null, null, OCI_FETCHSTATEMENT_BY_ROW);
                oci_fetch_all($sqlarr, $row_period, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);

            
                $name="YTY-QP-".$_SESSION['P_UNAME']."-".$data->CURRENCY_CODE->ID."-".$data->PERIOD->ID;
                $start_date = $row_period[0]["START_DATE"];
                $end_date = $row_period[0]["END_DATE"];
            
                $sql = "INSERT INTO apps.yty_g_prc_modifier_list_tl
                                    (name, description, currency_code, period, start_date, end_date, 
                                     product_uom_code, product_attr_value, category_id, new_price, customer_name, customer_id, created_by, 
                                     creation_date, comments, volume, bill_to, ship_to, sku_size, incoterm, brand, size_nm, packing_type)
                             VALUES (:name, :description, :currency_code, :period, to_date(:start_date,'YYYY-MM-DD'), to_date(:end_date,'YYYY-MM-DD'), 
                                     :product_uom_code, :product_attr_value, :category_id, :new_price, :customer_name, :customer_id, :created_by, 
                                     SYSDATE, :comments, :volume, :bill_to, :ship_to, :sku_size, :incoterm, :brand, :size_nm, :packing_type)";
                
                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':name', $name);
                oci_bind_by_name($sqlarr, ':description', $data->ITEM_DESCRIPTION);
                oci_bind_by_name($sqlarr, ':currency_code', $data->CURRENCY_CODE->ID);
                oci_bind_by_name($sqlarr, ':period', $data->PERIOD->ID);
                oci_bind_by_name($sqlarr, ':start_date', $start_date);
                oci_bind_by_name($sqlarr, ':end_date', $end_date);
                oci_bind_by_name($sqlarr, ':product_uom_code', $data->PRODUCT_UOM_CODE->ID);
                oci_bind_by_name($sqlarr, ':product_attr_value', $data->PRODUCT_ATTR_VALUE->ID);
                oci_bind_by_name($sqlarr, ':category_id', $data->CATEGORY_ID);
                oci_bind_by_name($sqlarr, ':new_price', $data->NEW_PRICE);
                oci_bind_by_name($sqlarr, ':customer_name', $data->CUSTOMER_NAME);
                oci_bind_by_name($sqlarr, ':customer_id', $data->CUSTOMER_ID->ID);
                oci_bind_by_name($sqlarr, ':created_by', $data->CREATED_BY);
                oci_bind_by_name($sqlarr, ':comments', $data->COMMENTS);
                oci_bind_by_name($sqlarr, ':volume', $data->VOLUME);
                oci_bind_by_name($sqlarr, ':bill_to', $data->BILL_TO->ID);

                if (!empty($data->SHIP_TO->ID)) {
                    oci_bind_by_name($sqlarr, ':ship_to', $data->SHIP_TO->ID);
                } else {
                    $shipto = null;
                    oci_bind_by_name($sqlarr, ':ship_to', $shipto);
                }
                
                if (!empty($data->SKU_SIZE->ID)) {
                    oci_bind_by_name($sqlarr, ':sku_size', $data->SKU_SIZE->ID);
                    oci_bind_by_name($sqlarr, ':size_nm', $data->SKU_SIZE->TEXT);
                } else {
                    $sku_size = null;
                    $size_nm  = null;
                    oci_bind_by_name($sqlarr, ':sku_size', $sku_size);
                    oci_bind_by_name($sqlarr, ':size_nm', $size_nm);
                }
                
               
                oci_bind_by_name($sqlarr, ':incoterm', $data->INCOTERM);
                if(!empty($data->BRAND->ID))
                {
                    oci_bind_by_name($sqlarr, ':brand', $data->BRAND->ID);
                }
                else{
                    $tmp_brand_id = null;
                    oci_bind_by_name($sqlarr, ':brand', $tmp_brand_id);
                }

                if(!empty($data->PACKING_TYPE->ID))
                {
                    oci_bind_by_name($sqlarr, ':packing_type', $data->PACKING_TYPE->ID);
                }
                else{
                    $tmp_price_id = null;
                    oci_bind_by_name($sqlarr, ':packing_type', $data->tmp_price_id);
                }
                $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            
                if (!$r) {
                    $e = oci_error($sqlarr);
                    $errorMsg = json_encode($e['message']);
                    goto finalStep;
                }
        
            } else {
                $errorMsg = "The price with this combination already exists.";
            }
        
        } else {
            $errorMsg = "Order Exists";
            $order = explode(", ",$order_number);

            $liststr = "<ul>";
            for ($i = 0; $i < count($order); $i++) {
                $liststr .= "<li>".$order[$i]."</li>";
            }
            $liststr .= "</ul>";
        }
    
    } else {

        //Check if order already exist
        $sql = "SELECT XXYTYCPORT.FETCH_ORDER_LIST(TO_DATE(:p_period,'MON-RR'),
                                               :p_customer,
                                               null,
                                               null,
                                               substr(:p_sku,1,7)) order_no
                  FROM dual";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':p_period', $data->PERIOD->ID);
        oci_bind_by_name($sqlarr, ':p_customer', $data->CUSTOMER_ID->ID);
        oci_bind_by_name($sqlarr, ':p_sku', $data->PRODUCT_ATTR_VALUE->ID);
        $r = oci_execute($sqlarr);
        while ($row = oci_fetch_array($sqlarr)) {
            $order_number = $row['ORDER_NO'];
        }

        if (empty($order_number)) {
            if (!empty($data->SKU_SIZE->ID)) {
                $sizeWhere = " AND sku_size = '".$data->SKU_SIZE->ID."' ";
            }
            if (!empty($data->BRAND->ID)) {
                $brandWhere = " AND brand = '".$data->BRAND->ID."' ";
            }
        
            $sql = "SELECT COUNT(1) ctr
                      FROM apps.yty_g_prc_modifier_list_tl
                     WHERE period = :period
                       AND customer_id = :customerid
                       AND product_attr_value = :sku
                       AND id != :id
                       ".$sizeWhere."
                       ".$brandWhere;
            
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':period', $data->PERIOD->ID);
            oci_bind_by_name($sqlarr, ':customerid', $data->CUSTOMER_ID->ID);
            oci_bind_by_name($sqlarr, ':sku', $data->PRODUCT_ATTR_VALUE->ID);
            oci_bind_by_name($sqlarr, ':id', $data->ID);
            $r = oci_execute($sqlarr);
            while ($row = oci_fetch_array($sqlarr)) {
                $exists = $row['CTR'];
            }
        
            if ($exists == "0") {
    
                $sql = "UPDATE apps.yty_g_prc_modifier_list_tl
                           SET description = :description, 
                               product_uom_code = :product_uom_code, 
                               product_attr_value = :product_attr_value, 
                               category_id = :category_id, 
                               new_price = :new_price, 
                               customer_name = :customer_name, 
                               customer_id = :customer_id, 
                               last_updated_by = :created_by, 
                               last_updated_date = sysdate, 
                               comments = :comments, 
                               volume = :volume, 
                               bill_to = :bill_to, 
                               ship_to = :ship_to, 
                               sku_size = :sku_size, 
                               size_nm = :size_nm, 
                               incoterm = :incoterm, 
                               brand = :brand,
                               packing_type = :packing_type,
                               approval_flag = null
                         WHERE id = :id";
        
                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':description', $data->ITEM_DESCRIPTION);
                oci_bind_by_name($sqlarr, ':product_uom_code', $data->PRODUCT_UOM_CODE->ID);
                oci_bind_by_name($sqlarr, ':product_attr_value', $data->PRODUCT_ATTR_VALUE->ID);
                oci_bind_by_name($sqlarr, ':category_id', $data->CATEGORY_ID);
                oci_bind_by_name($sqlarr, ':new_price', $data->NEW_PRICE);
                oci_bind_by_name($sqlarr, ':customer_name', $data->CUSTOMER_NAME);
                oci_bind_by_name($sqlarr, ':customer_id', $data->CUSTOMER_ID->ID);
                oci_bind_by_name($sqlarr, ':created_by', $data->CREATED_BY);
                oci_bind_by_name($sqlarr, ':comments', $data->COMMENTS);
                oci_bind_by_name($sqlarr, ':volume', $data->VOLUME);
                oci_bind_by_name($sqlarr, ':bill_to', $data->BILL_TO->ID);

                if (!empty($data->SHIP_TO->ID)) {
                    oci_bind_by_name($sqlarr, ':ship_to', $data->SHIP_TO->ID);
                } else {
                    $shipto = null;
                    oci_bind_by_name($sqlarr, ':ship_to', $shipto);
                }
                
                if (!empty($data->SKU_SIZE->ID)) {
                    oci_bind_by_name($sqlarr, ':sku_size', $data->SKU_SIZE->ID);
                    oci_bind_by_name($sqlarr, ':size_nm', $data->SKU_SIZE->TEXT);
                } else {
                    $sku_size = null;
                    $size_nm  = null;
                    oci_bind_by_name($sqlarr, ':sku_size', $sku_size);
                    oci_bind_by_name($sqlarr, ':size_nm', $size_nm);
                }
                
               
                oci_bind_by_name($sqlarr, ':incoterm', $data->INCOTERM);
                if(!empty($data->BRAND->ID))
                {
                    oci_bind_by_name($sqlarr, ':brand', $data->BRAND->ID);
                }
                else{
                    $tmp_brand_id = null;
                    oci_bind_by_name($sqlarr, ':brand', $tmp_brand_id);
                }

                if(!empty($data->PACKING_TYPE->ID))
                {
                    oci_bind_by_name($sqlarr, ':packing_type', $data->PACKING_TYPE->ID);
                }
                else{
                    $tmp_price_id = null;
                    oci_bind_by_name($sqlarr, ':packing_type', $data->tmp_price_id);
                }

                oci_bind_by_name($sqlarr, ':id', $data->ID);
                $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            
                if (!$r) {
                    $e = oci_error($sqlarr);
                    $errorMsg = json_encode($e['message']);
                    goto finalStep;
                }
            
            } else {
                $errorMsg = "The price with this combination already exists.";
            }
            
        } else {
            $errorMsg = "Order Exists";
            $order = explode(", ",$order_number);

            $liststr = "<ul>";
            for ($i = 0; $i < count($order); $i++) {
                $liststr .= "<li>".$order[$i]."</li>";
            }
            $liststr .= "</ul>";
        }
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $newid,
            "order" => ""
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => "",
            "order" => "Failed to save the price because there is already order raised with this price combination:<p>".$liststr."</p>"
        ];
    }
    print json_encode($result);
?>