import streamlit as st import pandas as pd import pickle import os from database import create_connection, insert_data, fetch_data # Database setup conn = create_connection('example_db') # Adjust the name 'example_db' as necessary # Load the trained models current_dir = os.path.dirname(os.path.abspath(__file__)) with open(os.path.join(current_dir, 'best_model.pkl'), 'rb') as model_file: input_weight_model = pickle.load(model_file) with open(os.path.join(current_dir, 'machining_model.pkl'), 'rb') as model_file: machining_model = pickle.load(model_file) with open(os.path.join(current_dir, 'inspection_model.pkl'), 'rb') as model_file: inspection_model = pickle.load(model_file) # Final landed cost based on grade type final_landed_cost = { '1 MT XX (25-95 dia)': 103, '1 MT XX (100-210 dia)': 113, '1 MT YY (25-95 dia)': 160, '1 MT YY (100-125 dia)': 173, '1 MT XY (25-95 dia)': 106, '1 MT 8319 (100-210 dia)': 116, '1 MT 8319': 104 } # Function to calculate raw material cost def calculate_raw_material_cost(process_type, input_weight, grade_type): if process_type == 0: # 0 represents casting return 0 elif process_type == 1: # 1 represents forging return input_weight * final_landed_cost[grade_type] # Function to calculate process cost def calculate_process_cost(process_type, input_weight): if process_type == 0: # 0 represents casting return input_weight * (120.57788 / 1000) * 1000 elif process_type == 1: # 1 represents forging return input_weight * 30 # Streamlit interface st.title("EX-Works Calculator") # Tabs for page navigation tabs = st.tabs(["Home", "Vendor Data", "Material Data", "RM Cost Data", "Supplier Data","Vendor Data and RM cost Data Databases"]) with tabs[0]: st.write("Welcome to the EX-Works Calculator application. Click on the relevant tabs to enter the information ") with tabs[1]: st.header("Vendor Data") vendor_name = st.text_input("Vendor Name") vendor_type = st.text_input("Vendor Type") gst_no = st.number_input("GST NO") contact_person_name = st.text_input("Contact Person/Name") address = st.text_input("Address") city = st.text_input("City") panno = st.text_input("PAN NO") if st.button("Add Vendor"): vendor_data = pd.DataFrame({'vendor_name': [vendor_name], 'vendor_type': [vendor_type], 'GST_NO': [gst_no], 'Contact_person_name': [contact_person_name], 'address': [address], 'city': [city], 'pan_no': [panno]}) insert_data(conn, 'vendor_data', vendor_data) st.success("Vendor data added successfully") with tabs[2]: st.header("Material Data") part_id = st.number_input("Part ID") part_no = st.number_input("Part Number") scf = st.selectbox("SCF", options=[0, 1], key="material_scf") process_type = st.selectbox("Process Type", options=[0, 1], key="material_process_type") part_od = st.number_input("Part Outer Dimension") part_width = st.number_input("Part Width") part_inner_dimension = st.number_input("Part Inner Dimension") material_spec = st.selectbox("Material Specification", options=[0, 1], key="material_spec") finish_wt = st.number_input("Finish Weight") green_drg_no = st.selectbox("Green DRG Number", options=[0, 1], key="material_green_drg_no") if st.button("Add Material"): material_data = pd.DataFrame({'Part_id': [part_id], 'part_no': [part_no], 'scf': [scf], 'process_type': [process_type], 'part_od': [part_od], 'part_width': [part_width], 'part_inner_dimension': [part_inner_dimension], 'material_specification': [material_spec], 'finish_wt': [finish_wt], 'green_drg_no': [green_drg_no]}) insert_data(conn, 'material_data', material_data) st.success("Material data added successfully") with tabs[3]: st.header("RM Cost Data") rm_type = st.text_input("RM Type") rm_cost = st.number_input("RM Cost", min_value=0.0, step=0.01) vendor_id = st.number_input("Vendor ID", min_value=1, step=1) if st.button("Add RM Cost Data"): rm_cost_data = pd.DataFrame({'rm_type': [rm_type], 'rm_cost': [rm_cost], 'vendor_id': [vendor_id]}) insert_data(conn, 'rm_cost_data', rm_cost_data) st.success("RM cost data added successfully") with tabs[4]: st.header("Supplier Data") part_no = st.number_input("Part No", min_value=1, step=1) process_type = st.selectbox("Process Type", options=[0, 1], key="supplier_process_type") part_od = st.number_input("Part OD", min_value=0.0, step=0.1) part_id = st.number_input("Part ID", min_value=0.0, step=0.1) part_width = st.number_input("Part Width", min_value=0, step=1) finish_wt = st.number_input("Finish Wt", min_value=0.0, step=0.1) grade_type = st.selectbox("Grade Type", options=list(final_landed_cost.keys()), key="supplier_grade_type") material_id = st.number_input("Material ID", min_value=1, step=1) if st.button("Calculate and Add Supplier Data"): # Prepare the input data for prediction input_data = pd.DataFrame({ 'Process type': [process_type], 'Part Od': [part_od], 'Part ID': [part_id], 'Part Width': [part_width], 'Finish Wt': [finish_wt] }) # Predict the input weight predicted_input_weight = input_weight_model.predict(input_data)[0] # Calculate raw material cost raw_material_cost = calculate_raw_material_cost(process_type, predicted_input_weight, grade_type) # Calculate process cost process_cost = calculate_process_cost(process_type, predicted_input_weight) # Prepare the data for machining time prediction machining_data = pd.DataFrame({ 'Process type': [process_type], 'Part Od': [part_od], 'Part ID': [part_id], 'Part Width': [part_width], 'Finish Wt': [finish_wt], 'Input Weight': [predicted_input_weight], 'Raw material cost': [raw_material_cost], 'Process cost': [process_cost] }) # Predict the machining time predicted_machining_time = machining_model.predict(machining_data)[0] # Calculate machining cost machining_cost = predicted_machining_time * 375.71 # Calculate scrap recovery scrap_recovery = (predicted_input_weight - finish_wt) * 11.5 # Prepare the data for inspection time prediction inspection_data = pd.DataFrame({ 'Process type': [process_type], 'Part Od': [part_od], 'Part ID': [part_id], 'Part Width': [part_width], 'Finish Wt': [finish_wt], 'Input Weight': [predicted_input_weight], 'Raw material cost': [raw_material_cost], 'Process cost': [process_cost], 'Machining Time': [predicted_machining_time], 'Machining cost': [machining_cost], 'Scrap recovery': [scrap_recovery] }) # Predict the inspection time predicted_inspection_time = inspection_model.predict(inspection_data)[0] # Calculate inspection cost inspection_cost = predicted_inspection_time * 375.71 # Calculate total manufacturing cost total_mg_cost = raw_material_cost + process_cost + machining_cost - scrap_recovery + inspection_cost # Insert supplier data supplier_data = pd.DataFrame({ 'part_no': [part_no], 'process_type': [process_type], 'part_od': [part_od], 'part_id': [part_id], 'part_width': [part_width], 'finish_wt': [finish_wt], 'grade_type': [grade_type], 'material_id': [material_id], 'input_weight': [predicted_input_weight], 'raw_material_cost': [raw_material_cost], 'process_cost': [process_cost], 'machining_time': [predicted_machining_time], 'machining_cost': [machining_cost], 'scrap_recovery': [scrap_recovery], 'inspection_time': [predicted_inspection_time], 'inspection_cost': [inspection_cost], 'total_mg_cost': [total_mg_cost] }) insert_data(conn, 'supplier_data', supplier_data) st.success("Supplier data added successfully") with tabs[5]: # Assuming this is an additional tab st.header("Vendor Data and RM cost Data Databases") query = "SELECT * FROM vendor_data" query = "SELECT * FROM rm_cost_data" df = fetch_data(conn, query) st.dataframe(df)