app.py

import gradio as gr
import pandas as pd
from pulp import *
from io import StringIO
import numpy as np
import xlsxwriter
from datetime import datetime

# Function to process the inputs and execute the optimization
def process_data(stock_data, order_data, cost_data, priority_data):
    try:
        # Convert inputs to DataFrames
        stock_data = pd.DataFrame(stock_data, columns=["Warehouse", "Product#1", "Product#2", "Product#3", "Product#4"])
        order_data = pd.DataFrame(order_data, columns=["Order", "Product#1", "Product#2", "Product#3", "Product#4"])
        cost_data = pd.DataFrame(cost_data, columns=["Warehouse", "Order", "Product", "Cost"])
        priority_data = pd.DataFrame(priority_data, columns=["Warehouse", "Priority"])

        # Convert numeric columns to appropriate types
        for col in stock_data.columns[1:]:
            stock_data[col] = pd.to_numeric(stock_data[col], errors='coerce')

        for col in order_data.columns[1:]:
            order_data[col] = pd.to_numeric(order_data[col], errors='coerce')

        cost_data["Priority"] = pd.to_numeric(cost_data["Cost"], errors='coerce')

        priority_data["Priority"] = pd.to_numeric(priority_data["Priority"], errors='coerce')

        # Drop rows with invalid data
        #stock_data.dropna(inplace=True)
        #order_data.dropna(inplace=True)
        #priority_data.dropna(inplace=True)
        ##############################################################################################################

        # Creates a dictionary of the available stock of each product in each warehouse

        Warehouses = stock_data['Warehouse'].to_list()
        Products = stock_data.columns[1:]

        stock = makeDict([Warehouses, Products], stock_data.drop('Warehouse', axis=1).values, default=0)

        # Creates a dictionary of the number of each product in each deamnd

        Orders = order_data['Order'].to_list()

        quantity = makeDict([Orders, Products], order_data.drop('Order', axis=1).values, default=0)

        # Calculate the minimum and maximum cost
        min_cost = cost_data['Cost'].min()
        max_cost = cost_data['Cost'].max()

        # Normalize the 'Cost' column
        cost_data['Cost'] = (cost_data['Cost'] - min_cost) / (max_cost - min_cost)

        # Creates a dictionary of the cost for each Warehouse, Order and Product Combination

        # Reshape cost_data["Cost"].values to match the dimensions of Warehouses, Orders, Products
        cost_values = cost_data["Cost"].values.reshape(len(Warehouses), len(Orders), len(Products))
        cost_values.shape

        cost = makeDict([Warehouses, Orders, Products], cost_values, default=0)

        # Calculate the minimum and maximum cost
        min_cost = priority_data['Priority'].min()
        max_cost = priority_data['Priority'].max()

        # Normalize the 'Cost' column
        priority_data['Priority'] = (priority_data['Priority'] - min_cost) / (max_cost - min_cost)

        # Creates a dictionary of the priority for warehouse

        priority = makeDict([Warehouses], priority_data.drop('Warehouse', axis=1).values.reshape(len(Warehouses)), default=0)
        priority

        # Creates a list of tuples containing all the possible routes for transport
        routes = [(w, o, s) for w in Warehouses for o in Orders for s in Products]

        # A dictionary called 'Vars' is created to contain the referenced variables(the routes)
        variable = LpVariable.dicts("Route", (Warehouses, Orders, Products), 0, None, LpInteger)

        for w in Warehouses:
          for o in Orders:
            for p in Products:
              print(f'The variable for transporting {p} from {w} to {o} is {variable[w][o][p]}')

        # Creates the 'prob' variable to contain the problem data
        prob = LpProblem("Distribution Problem", LpMinimize)

        # The objective function is added to 'prob' first
        prob += (
            lpSum([cost[w][o][p] * variable[w][o][p] * -priority[w] for (w,o,p) in routes]),
            "Sum_of_Costs"
        )

        # The stock constraints are added to prob for each Warehouse

        for w in Warehouses:
          for p in Products:
            prob += (
                lpSum([variable[w][o][p] for o in Orders ]) <= stock[w][p],
                f"Sum_of_{p}_out_of_{w}"
            )

        # Quantity constrainsts are added for each Order

        for o in Orders:
          for p in Products:
            prob += (
                lpSum([variable[w][o][p] for w in Warehouses ]) == quantity[o][p],
                f"Sum_of_{p}_sent_to_{o}"
            )

        # The problem is solved using PuLP's choice of Solver
        prob.solve()

        # Capture output
        output = StringIO()
        print("Status:", LpStatus[prob.status], file=output)
        if LpStatus[prob.status] != "Optimal":
            print("Optimization was not successful!!", file=output)
            print("Optimization was not successful!!", file=output)
            print("Optimization was not successful!!", file=output)
            print("Optimization was not successful!!", file=output)
            print("Optimization was not successful!!", file=output)
            print("Optimization was not successful!!", file=output)
            print("Optimization was not successful!!", file=output)
            raise ValueError(f"ERR!! No solution found!! - Status is {LpStatus[prob.status]}")
            return output.getvalue()

        gr.Info(f"Solution found!! - Status is {LpStatus[prob.status]}")


        print("\n   Fulfillment Report:", file=output)
        print("========================", file=output)

        print("\nSupply from each Warehouse to each Order:", file=output)
        for w in Warehouses:
            print(f"Warehouse {w}:", file=output)
            for p in Products:
                for o in Orders:
                    route_var = variable[w][o][p]
                    if route_var.varValue > 0:
                        print(f"  supplies {route_var.varValue} units of {p}", file=output)
                        print(f"    to Order {o}", file=output)

        print("\nUnfulfilled Demand:", file=output)
        unfulfilled_flag = False
        for o in Orders:
            for p in Products:
                total_received = sum(variable[w][o][p].varValue for w in Warehouses)
                unfulfilled = quantity[o][p] - total_received
                if unfulfilled > 0:
                    print(f"Order {o}: Unfulfilled demand for {p} : {unfulfilled}", file=output)
        if unfulfilled_flag is False:
            print("All orders are fulfilled", file=output)


        print("\n Validation Report:", file=output)
        print("========================", file=output)

        print("\nValidation - Warehouse level:", file=output)
        for w in Warehouses:
            print(f"Warehouse {w}:", file=output)
            for p in Products:
                total_supplied = sum(variable[w][o][p].varValue for o in Orders)

                if total_supplied > stock[w][p]:
                    print(f"ERROR!!! - Warehouse {w}:", file=output)
                    print(f"  Product {p}: Shipped ({total_supplied} units) is > than Stock ({stock[w][p]} units)", file=output)

                print(f"  Product {p}: Shipped ({total_supplied} units) is <= than Stock ({stock[w][p]} units). Remaining is {stock[w][p]} - {total_supplied}", file=output)

        print("\nValidation - Order level:", file=output)
        for o in Orders:
            print(f"Order {w}:", file=output)
            for p in Products:
                total_received = sum(variable[w][o][p].varValue for w in Warehouses)

                if total_received != quantity[o][p]:
                    print(f"ERROR!!! - Order {o}:", file=output)
                    print(f"  Product {p}: Received ({total_received} units) is != than Quantity ({quantity[o][p]} units)", file=output)

                print(f"  Product {p}: Received ({total_received} units) is = than Quantity ({quantity[o][p]} units)", file=output)


        # Generate a timestamped file name
        timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
        file_name = f"/tmp/Intelligent_Sourcing_{timestamp}.xlsx"
        
        # Create a Pandas Excel writer using XlsxWriter as the engine
        writer = pd.ExcelWriter(file_name, engine='xlsxwriter')

        # Write each DataFrame to a different worksheet.
        stock_data.to_excel(writer, sheet_name='Stock Data', index=False)
        order_data.to_excel(writer, sheet_name='Order Data', index=False)
        cost_data.to_excel(writer, sheet_name='Cost Data', index=False)
        priority_data.to_excel(writer, sheet_name='Priority Data', index=False)

        fulfillment_solution = []
        for w in Warehouses:
            for p in Products:
                for o in Orders:
                    fulfillment_solution.append({
                        "Warehouse": w,
                        "Product": p,
                        "Order": o,
                        "Supply Quantity": variable[w][o][p].varValue
                    })

        fulfillment_solution = pd.DataFrame(fulfillment_solution)
        fulfillment_solution.to_excel(writer, sheet_name='Fulfillment Solution', index=False)

        # Close the Pandas Excel writer and output the Excel file.
        writer.close()


        return output.getvalue(), file_name

    except Exception as e:
        return f"Error: {str(e)}"

# Define example data
stock_example = [["Warehouse#1", 5, 1, 5, 1],
                 ["Warehouse#2", 5, 5, 1, 15],
                 ["Warehouse#3", 2, 5, 10, 0],
                 ["Warehouse#4", 4, 5, 0, 2]]
order_example = [["Order#1", 2, 3, 2, 3],
                 ["Order#2", 4, 1, 4, 1],
                 ["Order#3", 5, 3, 5, 3],
                 ["Order#4", 2, 2, 2, 2]]
cost_example = [
    ["Warehouse#1", "Order#1", "Product#1", 103],
    ["Warehouse#1", "Order#1", "Product#2", 436],
    ["Warehouse#1", "Order#1", "Product#3", 861],
    ["Warehouse#1", "Order#1", "Product#4", 271],
    ["Warehouse#1", "Order#2", "Product#1", 107],
    ["Warehouse#1", "Order#2", "Product#2", 72],
    ["Warehouse#1", "Order#2", "Product#3", 701],
    ["Warehouse#1", "Order#2", "Product#4", 21],
    ["Warehouse#1", "Order#3", "Product#1", 615],
    ["Warehouse#1", "Order#3", "Product#2", 122],
    ["Warehouse#1", "Order#3", "Product#3", 467],
    ["Warehouse#1", "Order#3", "Product#4", 215],
    ["Warehouse#1", "Order#4", "Product#1", 331],
    ["Warehouse#1", "Order#4", "Product#2", 459],
    ["Warehouse#1", "Order#4", "Product#3", 88],
    ["Warehouse#1", "Order#4", "Product#4", 373],
    ["Warehouse#2", "Order#1", "Product#1", 100],
    ["Warehouse#2", "Order#1", "Product#2", 872],
    ["Warehouse#2", "Order#1", "Product#3", 664],
    ["Warehouse#2", "Order#1", "Product#4", 131],
    ["Warehouse#2", "Order#2", "Product#1", 662],
    ["Warehouse#2", "Order#2", "Product#2", 309],
    ["Warehouse#2", "Order#2", "Product#3", 770],
    ["Warehouse#2", "Order#2", "Product#4", 344],
    ["Warehouse#2", "Order#3", "Product#1", 492],
    ["Warehouse#2", "Order#3", "Product#2", 414],
    ["Warehouse#2", "Order#3", "Product#3", 806],
    ["Warehouse#2", "Order#3", "Product#4", 386],
    ["Warehouse#2", "Order#4", "Product#1", 192],
    ["Warehouse#2", "Order#4", "Product#2", 956],
    ["Warehouse#2", "Order#4", "Product#3", 277],
    ["Warehouse#2", "Order#4", "Product#4", 161],
    ["Warehouse#3", "Order#1", "Product#1", 460],
    ["Warehouse#3", "Order#1", "Product#2", 314],
    ["Warehouse#3", "Order#1", "Product#3", 22],
    ["Warehouse#3", "Order#1", "Product#4", 253],
    ["Warehouse#3", "Order#2", "Product#1", 748],
    ["Warehouse#3", "Order#2", "Product#2", 857],
    ["Warehouse#3", "Order#2", "Product#3", 561],
    ["Warehouse#3", "Order#2", "Product#4", 475],
    ["Warehouse#3", "Order#3", "Product#1", 59],
    ["Warehouse#3", "Order#3", "Product#2", 511],
    ["Warehouse#3", "Order#3", "Product#3", 682],
    ["Warehouse#3", "Order#3", "Product#4", 476],
    ["Warehouse#3", "Order#4", "Product#1", 700],
    ["Warehouse#3", "Order#4", "Product#2", 976],
    ["Warehouse#3", "Order#4", "Product#3", 783],
    ["Warehouse#3", "Order#4", "Product#4", 190],
    ["Warehouse#4", "Order#1", "Product#1", 958],
    ["Warehouse#4", "Order#1", "Product#2", 687],
    ["Warehouse#4", "Order#1", "Product#3", 958],
    ["Warehouse#4", "Order#1", "Product#4", 563],
    ["Warehouse#4", "Order#2", "Product#1", 876],
    ["Warehouse#4", "Order#2", "Product#2", 567],
    ["Warehouse#4", "Order#2", "Product#3", 244],
    ["Warehouse#4", "Order#2", "Product#4", 832],
    ["Warehouse#4", "Order#3", "Product#1", 505],
    ["Warehouse#4", "Order#3", "Product#2", 131],
    ["Warehouse#4", "Order#3", "Product#3", 485],
    ["Warehouse#4", "Order#3", "Product#4", 819],
    ["Warehouse#4", "Order#4", "Product#1", 647],
    ["Warehouse#4", "Order#4", "Product#2", 21],
    ["Warehouse#4", "Order#4", "Product#3", 841],
    ["Warehouse#4", "Order#4", "Product#4", 167],
]
priority_example = [["Warehouse#1", 100],
                    ["Warehouse#2", 200],
                    ["Warehouse#3", 300],
                    ["Warehouse#4", 1000]]

# Create Gradio interface
with gr.Blocks(gr.themes.Soft()) as app:
    gr.Markdown("# Intelligent Sourcing Optimization App")
    gr.Markdown("## Created by Krishna Kumar.S")
    gr.Markdown("### PoC - Work in Progress!!")

    with gr.Tab("Stock Data"):
        stock_data_input = gr.Dataframe(label="Stock Data", headers=["Warehouse", "Product#1", "Product#2", "Product#3", "Product#4"],
                                        value=stock_example, datatype="str")
    with gr.Tab("Order Data"):
        order_data_input = gr.Dataframe(label="Order Data", headers=["Order", "Product#1", "Product#2", "Product#3", "Product#4"],
                                        value=order_example, datatype="str")
    with gr.Tab("Cost Data"):
        cost_data_input = gr.Dataframe(label="Cost Data", headers=["Warehouse", "Order", "Product", "Cost"],
                                        value=cost_example, datatype="str")
    with gr.Tab("Priority Data"):
        priority_data_input = gr.Dataframe(label="Priority Data", headers=["Warehouse", "Priority"],
                                           value=priority_example, datatype="str")
    
    file_path = gr.State()  # State to hold the file path dynamically
    
    submit_button = gr.Button("Run Optimization")
    download_button = gr.File(label="Download Excel", file_types=[".xlsx"])
    output_text = gr.Textbox(label="Output", lines=110, interactive=False)
    
    submit_button.click(process_data, inputs=[stock_data_input, order_data_input, cost_data_input, priority_data_input], outputs=[output_text, file_path])

    # Update the download button dynamically with the generated file path
    file_path.change(
        fn=lambda path, : path,
        inputs=file_path,
        outputs=download_button
    )

app.launch()