Spaces:

samridh12
/

EY_test

Sleeping

Update app.py

251c645 verified over 2 years ago

8.65 kB

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)

	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)