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()