Inventory / app.py
ovrathi1's picture
Update app.py
b6daa0c verified
Raw History Blame Contribute Delete
28 kB
import requests
import pandas as pd
import gradio as gr
from fractions import Fraction
from datetime import datetime
# Authentication and initialization code
client_id = 'b15f2681-4da6-4f72-9310-4e9d1796d7d4'
client_secret = 'VFu8Q~8Wk8rhSBpYuKqaTxd3-PQMs2CLVJCZ4akw'
tenant_id = 'a4721b64-14c3-48c1-8a71-333abb37024d'
authority_url = f'https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token'
inventory_file_id = '0135JPEOSWHMR6I6YEKBFYLMRRCFGDCZQS' # Replace with actual file ID for inventory
reservation_file_id = '0135JPEOS7ITA3N4M3VFBLPCN66ERDJ6JV' # Replace with actual file ID for reservation
activity_log_file_id = '0135JPEOUTR3KMR55CNREZOZI7UWRSKAJN' # Replace with actual file ID for activity log
user_id = 'b9f6184b-6bb9-47e4-835b-ab7134897319' # Replace with your actual user ID
scope = ['https://graph.microsoft.com/.default']
def fraction_to_decimal(fraction_str):
try:
if isinstance(fraction_str, str) and ' ' in fraction_str:
whole_number, fraction_part = fraction_str.split(' ')
whole_number = float(whole_number)
fraction_part = float(Fraction(fraction_part))
return whole_number + fraction_part
elif isinstance(fraction_str, str):
return float(Fraction(fraction_str))
else:
return fraction_str # Return the input if it's already a float
except ValueError:
return None
def decimal_to_fraction(decimal, denominators=[2, 4, 8, 16]):
fraction = Fraction(decimal).limit_denominator(16)
if fraction.denominator == 1:
return str(fraction.numerator)
elif fraction.numerator > fraction.denominator:
whole_part = fraction.numerator // fraction.denominator
fractional_part = Fraction(fraction.numerator % fraction.denominator, fraction.denominator)
return f"{whole_part} {fractional_part}"
else:
return str(fraction)
def get_access_token():
data = {
'grant_type': 'client_credentials',
'client_id': client_id,
'client_secret': client_secret,
'scope': ' '.join(scope)
}
response = requests.post(authority_url, data=data)
return response.json().get('access_token')
access_token = get_access_token()
headers = {
'Authorization': f'Bearer {access_token}'
}
# Function to download and load inventory and reservation sheets from OneDrive
def download_and_load(file_id):
download_url = f'https://graph.microsoft.com/v1.0/users/{user_id}/drive/items/{file_id}/content'
response = requests.get(download_url, headers=headers)
local_file_path = f'{file_id}.xlsx'
with open(local_file_path, 'wb') as file:
file.write(response.content)
return pd.read_excel(local_file_path, engine='openpyxl', header=0)
inventory = download_and_load(inventory_file_id)
fixed_columns = ['Part Number', 'Size', 'Description']
qty_columns = [col for col in inventory.columns if col.startswith('QTY')]
other_columns = [col for col in inventory.columns if col not in fixed_columns + qty_columns]
inventory.columns = fixed_columns + qty_columns + other_columns
reservation = download_and_load(reservation_file_id)
reservation.columns = [
'Part Number', 'Size', 'Description', 'Quantity', 'Job Number', 'Reserved', 'Available'
]
activity_log = download_and_load(activity_log_file_id)
activity_log.columns = [
'Date', 'Part Number', 'Job Number', 'Action', 'Quantity', 'Used Length', 'Count'
]
# Function to get options for part numbers or size and description
def get_options(preferred, search_by):
if preferred:
filtered_inventory = inventory[inventory['Preferred'] == "Yes"]
else:
filtered_inventory = inventory
if search_by == "Part Number":
options = filtered_inventory['Part Number'].unique().tolist()
else:
sizes = filtered_inventory['Size'].unique().tolist()
descriptions = filtered_inventory['Description'].unique().tolist()
options = (sizes, descriptions)
return options
# Function to dynamically add quantity columns if needed
def add_quantity_columns(df):
current_qty_columns = [col for col in df.columns if 'QTY ' in col]
last_qty_column = max([int(col.split(' ')[1]) for col in current_qty_columns])
new_column = f'QTY {last_qty_column + 1}'
df.insert(df.columns.get_loc('Total'), new_column, None)
def update_inventory_and_reservation(preferred, search_by, action, part_number=None, size=None, description=None, from_length=None, used_length=None, new_length=None, count=None, job_number=None, unit=None):
global reservation # Ensure reservation is accessible
global activity_log
from_length = fraction_to_decimal(from_length) if from_length else None
used_length = fraction_to_decimal(used_length) if used_length else None
new_length_decimal = fraction_to_decimal(new_length) if new_length else None
# Convert to inches if unit is feet
if unit == "feet":
new_length_decimal *= 12
new_length_formatted = decimal_to_fraction(new_length_decimal) if new_length_decimal else None
print(f"Converted values in update - from_length: {from_length}, used_length: {used_length}, new_length: {new_length_formatted}")
# ... rest of the logic remains unchanged ...
if preferred:
inventory_to_use = inventory[inventory['Preferred'] == "Yes"]
else:
inventory_to_use = inventory
if search_by == "Part Number":
part_data = inventory_to_use[inventory_to_use["Part Number"] == part_number]
else:
part_data = inventory_to_use[(inventory_to_use["Size"] == size) & (inventory_to_use["Description"] == description)]
if part_data.empty:
return "Part not found."
inventory_index = part_data.index[0]
available_lengths = part_data[[col for col in inventory.columns if 'QTY ' in col]].values[0].tolist()
available_lengths = [length for length in available_lengths if not pd.isna(length)]
action_description = ""
if action == 'Consume':
if job_number:
job_number = str(job_number) # Ensure job number is a string
reservation['Job Number'] = reservation['Job Number'].astype(str) # Convert job number column to string
if search_by == "Part Number":
reservation_data = reservation[(reservation['Job Number'] == job_number) & (reservation['Part Number'] == part_number) & (reservation['Quantity'] == from_length)]
else:
reservation_data = reservation[(reservation['Job Number'] == job_number) & (reservation['Size'] == size) & (reservation['Description'] == description) & (reservation['Quantity'] == from_length)]
if not reservation_data.empty:
reserved_length = reservation_data['Reserved'].values[0]
if used_length > reserved_length:
return f"Error: Used length exceeds reserved length of {reserved_length} inches for job {job_number}."
reservation.loc[reservation_data.index[0], 'Reserved'] -= used_length
if reservation.loc[reservation_data.index[0], 'Reserved'] <= 0:
reservation = reservation.drop(reservation_data.index[0])
# Update quantity for all matching rows
if search_by == "Part Number":
reservation.loc[(reservation['Part Number'] == part_number) & (reservation['Quantity'] == from_length), 'Quantity'] -= used_length
else:
reservation.loc[(reservation['Size'] == size) & (reservation['Description'] == description) & (reservation['Quantity'] == from_length), 'Quantity'] -= used_length
else:
if search_by == "Part Number":
available_data = reservation[(reservation['Part Number'] == part_number) & (reservation['Quantity'] == from_length)]
else:
available_data = reservation[(reservation['Size'] == size) & (reservation['Description'] == description) & (reservation['Quantity'] == from_length)]
if not available_data.empty:
available_length = available_data['Available'].values[0]
if used_length > available_length:
return f"Error: Used length exceeds available length of {available_length} inches for the part."
reservation.loc[available_data.index, 'Available'] -= used_length
reservation.loc[available_data.index, 'Quantity'] -= used_length
action_description = f"Consumed {used_length} inches from {from_length} inch sheet"
else:
if search_by == "Part Number":
available_data = reservation[(reservation['Part Number'] == part_number) & (reservation['Quantity'] == from_length)]
else:
available_data = reservation[(reservation['Size'] == size) & (reservation['Description'] == description) & (reservation['Quantity'] == from_length)]
if not available_data.empty:
available_length = available_data['Available'].values[0]
if used_length > available_length:
return f"Error: Used length exceeds available length of {available_length} inches for the part."
reservation.loc[available_data.index, 'Available'] -= used_length
reservation.loc[available_data.index, 'Quantity'] -= used_length
action_description = f"Consumed {used_length} inches from {from_length} inch sheet"
column_to_update = None
for column in [col for col in inventory.columns if 'QTY ' in col]:
if inventory.at[inventory_index, column] == from_length:
column_to_update = column
break
if column_to_update:
inventory.at[inventory_index, column_to_update] -= used_length
# Check if the updated length is 0, and shift left if it is
if inventory.at[inventory_index, column_to_update] == 0:
qty_columns = [col for col in inventory.columns if 'QTY ' in col]
col_index = qty_columns.index(column_to_update)
for i in range(col_index, len(qty_columns) - 1):
inventory.at[inventory_index, qty_columns[i]] = inventory.at[inventory_index, qty_columns[i + 1]]
inventory.at[inventory_index, qty_columns[-1]] = None # Clear the last quantity column
inventory.at[inventory_index, "Total"] = inventory.loc[inventory_index, [col for col in inventory.columns if 'QTY ' in col]].sum()
else:
return "Length not found."
elif action == 'Add':
if new_length_decimal is None:
return "Error: New length is not provided or invalid."
added = False
for _ in range(count):
column_to_update = None
for column in [col for col in inventory.columns if 'QTY ' in col]:
if pd.isna(inventory.at[inventory_index, column]):
column_to_update = column
break
if column_to_update is None:
add_quantity_columns(inventory)
column_to_update = f'QTY {len([col for col in inventory.columns if "QTY " in col]) + 1}'
print(f"Adding new_length {new_length_formatted} to column {column_to_update}")
inventory.at[inventory_index, column_to_update] = new_length_decimal
added = True
if added:
inventory.at[inventory_index, "Total"] = inventory.loc[inventory_index, [col for col in inventory.columns if 'QTY ' in col]].sum()
action_description = f"Added {count} {new_length_formatted} {unit} sheets"
elif action == 'Scrap':
column_to_update = None
for column in [col for col in inventory.columns if 'QTY ' in col]:
if inventory.at[inventory_index, column] == from_length:
column_to_update = column
break
if column_to_update:
inventory.at[inventory_index, column_to_update] = None
# Shift left the quantities
qty_columns = [col for col in inventory.columns if 'QTY ' in col]
col_index = qty_columns.index(column_to_update)
for i in range(col_index, len(qty_columns) - 1):
inventory.at[inventory_index, qty_columns[i]] = inventory.at[inventory_index, qty_columns[i + 1]]
inventory.at[inventory_index, qty_columns[-1]] = None # Clear the last quantity column
inventory.at[inventory_index, "Total"] = inventory.loc[inventory_index, [col for col in inventory.columns if 'QTY ' in col]].sum()
action_description = f"Scrapped {from_length} inch sheet"
else:
return "Length not found."
# Save updated inventory and reservation to local files
updated_inventory_path = "updated_inventory.xlsx"
updated_reservation_path = "updated_reservation.xlsx"
updated_activity_log_path = "updated_activity_log.xlsx"
inventory.to_excel(updated_inventory_path, index=False)
reservation.to_excel(updated_reservation_path, index=False)
# Function to upload a file to OneDrive
def upload_file(local_path, file_id):
upload_url = f'https://graph.microsoft.com/v1.0/users/{user_id}/drive/items/{file_id}/content'
with open(local_path, 'rb') as file:
response = requests.put(upload_url, headers=headers, data=file)
return response.status_code, response.text
# Upload updated files back to OneDrive
inventory_status_code, inventory_response_text = upload_file(updated_inventory_path, inventory_file_id)
if inventory_status_code == 200:
reservation_status_code, reservation_response_text = upload_file(updated_reservation_path, reservation_file_id)
# Update activity log if inventory update was successful
new_log_entry = {
'Date': datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
'Part Number': part_number,
'Job Number': job_number if job_number else '',
'Action': action,
'Quantity': from_length if action == 'Consume' or action == 'Scrap' else new_length_formatted,
'Used Length': used_length if action == 'Consume' else '',
'Count': count if action == 'Add' else ''
}
activity_log = activity_log._append(new_log_entry, ignore_index=True)
activity_log.to_excel(updated_activity_log_path, index=False)
activity_log_status_code, activity_log_response_text = upload_file(updated_activity_log_path, activity_log_file_id)
if reservation_status_code == 200 and activity_log_status_code == 200:
return "Update successful and files uploaded."
else:
return (f"Update successful, but failed to upload reservation or activity log. Reservation upload status: {reservation_status_code}, "
f"response: {reservation_response_text}. Activity log upload status: {activity_log_status_code}, "
f"response: {activity_log_response_text}.")
else:
return (f"Failed to upload inventory file. Inventory upload status: {inventory_status_code}, response: {inventory_response_text}.")
def main(preferred, search_by, action, part_number, size, description, from_length, job_number, used_length, new_length, count, unit):
from_length = fraction_to_decimal(from_length) if from_length else None
used_length = fraction_to_decimal(used_length) if used_length else None
new_length_decimal = fraction_to_decimal(new_length) if new_length else None
# Convert to inches if unit is feet
if unit == "feet":
new_length_decimal *= 12
new_length_formatted = decimal_to_fraction(new_length_decimal) if new_length_decimal else None
print(f"Converted values - from_length: {from_length}, used_length: {used_length}, new_length: {new_length_decimal}")
if search_by == "Size and Description" and size and description:
part_data = inventory[(inventory["Size"] == size) & (inventory["Description"] == description)]
if not part_data.empty:
part_number = part_data["Part Number"].values[0]
elif search_by == "Part Number" and part_number:
part_data = inventory[inventory["Part Number"] == part_number]
if not part_data.empty:
size = part_data["Size"].values[0]
description = part_data["Description"].values[0]
if new_length_decimal is None and action == 'Add':
return "Error: New length is not provided or invalid."
message = update_inventory_and_reservation(preferred, search_by, action, part_number, size, description, from_length, used_length, new_length_decimal, count, job_number)
print(f"Update message: {message}")
return message
def reload_sheets():
global inventory, reservation, activity_log, access_token, headers
# Refresh the access token
access_token = get_access_token()
headers = {
'Authorization': f'Bearer {access_token}'
}
# Reload the sheets
inventory = download_and_load(inventory_file_id)
reservation = download_and_load(reservation_file_id)
activity_log = download_and_load(activity_log_file_id)
with gr.Blocks() as demo:
preferred = gr.Checkbox(label="Show Preferred Materials Only")
search_by = gr.Radio(choices=["Part Number", "Size and Description"], label="Search By")
part_number = gr.Dropdown(label="Part Number", interactive=True, visible=False)
size = gr.Dropdown(label="Size", interactive=True, visible=False)
description = gr.Dropdown(label="Description", interactive=True, visible=False)
action = gr.Radio(choices=["Add", "Consume", "Scrap"], label="Action")
from_length = gr.Dropdown(label="Quantity (inches)", interactive=True, visible=False)
job_number = gr.Textbox(label="Job Number", interactive=True, visible=False)
used_length = gr.Textbox(label="Used Length (inches)", interactive=True, visible=False)
new_length = gr.Textbox(label="New Length", interactive=True, visible=True) # Set as Textbox to accept strings like "25 3/8"
count = gr.Number(label="Count", interactive=True, visible=False)
unit = gr.Radio(choices=["inches", "feet"], label="Unit", visible=True) # Add unit selector
output_message = gr.Textbox(label="Output Message")
confirmation_html = gr.HTML("")
confirm_button = gr.Button("Confirm", visible=False)
def update_visibility(search_by):
return gr.update(visible=search_by == "Part Number"), gr.update(visible=search_by == "Size and Description"), gr.update(visible=search_by == "Size and Description")
def update_options(preferred, search_by):
reload_sheets() # Reload sheets when selecting by part number or size and description
options = get_options(preferred, search_by)
if search_by == "Part Number":
return gr.update(choices=options, visible=True), gr.update(visible=False), gr.update(visible=False)
else:
sizes, descriptions = options
return gr.update(visible=False), gr.update(choices=sizes, visible=True), gr.update(choices=descriptions, visible=True)
# Function to reload the sheets and refresh the access token
def reload_sheets():
global inventory, reservation, activity_log, access_token, headers
# Refresh the access token
access_token = get_access_token()
headers = {
'Authorization': f'Bearer {access_token}'
}
# Reload the sheets
inventory = download_and_load(inventory_file_id)
reservation = download_and_load(reservation_file_id)
activity_log = download_and_load(activity_log_file_id)
# Modify update_lengths to reload sheets before updating lengths
def update_lengths(preferred, search_by, part_number=None, size=None, description=None):
if search_by == "Part Number":
part_data = inventory[(inventory['Part Number'] == part_number) & (inventory['Preferred'] == ("Yes" if preferred else "No"))]
else:
part_data = inventory[(inventory['Size'] == size) & (inventory['Description'] == description) & (inventory['Preferred'] == ("Yes" if preferred else "No"))]
if part_data.empty:
return gr.update(choices=[], visible=False) # No data found
lengths = part_data[[col for col in inventory.columns if 'QTY ' in col]].values[0].tolist()
lengths = [length for length in lengths if not pd.isna(length)]
lengths = [decimal_to_fraction(length) for length in lengths] # Convert to fractions
return gr.update(choices=lengths, visible=bool(lengths))
# Modify update_reserved_lengths to reload sheets before updating reserved lengths
def update_reserved_lengths(job_number, part_number=None, size=None, description=None, from_length=None):
reservation['Job Number'] = reservation['Job Number'].astype(str) # Convert job number column to string
if part_number:
reservation_data = reservation[(reservation['Job Number'] == job_number) & (reservation['Part Number'] == part_number) & (reservation['Quantity'] == from_length)]
else:
reservation_data = reservation[(reservation['Job Number'] == job_number) & (reservation['Size'] == size) & (reservation['Description'] == description) & (reservation['Quantity'] == from_length)]
if not reservation_data.empty:
reserved_lengths = reservation_data['Reserved'].tolist() # Get all reserved lengths as a list
reserved_lengths_str = ", ".join(map(str, reserved_lengths)) # Convert list to comma-separated string
return gr.update(visible=True), f"Reserved lengths = {reserved_lengths_str}"
else:
if part_number:
available_data = reservation[(reservation['Part Number'] == part_number) & (reservation['Quantity'] == from_length)]
else:
available_data = reservation[(reservation['Size'] == size) & (reservation['Description'] == description) & (reservation['Quantity'] == from_length)]
if not available_data.empty:
available_length = available_data['Available'].values[0] # Show maximum available length from a single entry
return gr.update(visible=True), f"Maximum available length = {available_length}"
else:
return gr.update(visible=True), "No matching reservation or available length found."
search_by.change(update_visibility, inputs=[search_by], outputs=[part_number, size, description])
preferred.change(update_options, inputs=[preferred, search_by], outputs=[part_number, size, description])
search_by.change(update_options, inputs=[preferred, search_by], outputs=[part_number, size, description])
part_number.change(update_lengths, inputs=[preferred, search_by, part_number, size, description], outputs=from_length)
size.change(update_lengths, inputs=[preferred, search_by, part_number, size, description], outputs=from_length)
description.change(update_lengths, inputs=[preferred, search_by, part_number, size, description], outputs=from_length)
def handle_from_length_change(action_value):
if action_value == "Consume":
return gr.update(visible=True), gr.update(visible=True)
else:
return gr.update(visible=False), gr.update(visible=False)
from_length.change(handle_from_length_change, inputs=[action], outputs=[job_number, used_length])
job_number.change(update_reserved_lengths, inputs=[job_number, part_number, size, description, from_length], outputs=[used_length, output_message])
def show_length_inputs(action):
if action == "Add":
return (
gr.update(visible=False), # from_length
gr.update(visible=False), # used_length
gr.update(visible=True), # new_length
gr.update(visible=True), # count
gr.update(visible=False), # job_number
gr.update(visible=True) # unit
)
elif action == "Consume":
return (
gr.update(visible=True), # from_length
gr.update(visible=True), # used_length
gr.update(visible=False), # new_length
gr.update(visible=False), # count
gr.update(visible=True), # job_number
gr.update(visible=False) # unit
)
elif action == "Scrap":
return (
gr.update(visible=True), # from_length
gr.update(visible=False), # used_length
gr.update(visible=False), # new_length
gr.update(visible=False), # count
gr.update(visible=False), # job_number
gr.update(visible=False) # unit
)
else:
return (
gr.update(visible=False), # from_length
gr.update(visible=False), # used_length
gr.update(visible=False), # new_length
gr.update(visible=False), # count
gr.update(visible=False), # job_number
gr.update(visible=False) # unit
)
action.change(show_length_inputs, inputs=[action], outputs=[from_length, used_length, new_length, count, job_number, unit]) # Add unit to outputs
submit = gr.Button("Submit")
def show_confirmation(preferred, search_by, action, part_number, size, description, from_length, job_number, used_length, new_length, count, unit):
new_length_decimal = fraction_to_decimal(new_length) if new_length else None
if unit == "feet" and new_length_decimal:
new_length_decimal *= 12
new_length_formatted = decimal_to_fraction(new_length_decimal) if new_length_decimal else None
if search_by == "Size and Description" and size and description:
part_data = inventory[(inventory["Size"] == size) & (inventory["Description"] == description)]
if not part_data.empty:
part_number = part_data["Part Number"].values[0]
elif search_by == "Part Number" and part_number:
part_data = inventory[inventory["Part Number"] == part_number]
if not part_data.empty:
size = part_data["Size"].values[0]
description = part_data["Description"].values[0]
if action == "Consume":
message = f"Are you sure you want to consume {used_length} inches of {part_number} ({size} {description}) from a {from_length} inch piece for job number {job_number}?"
elif action == "Add":
message = f"Are you sure you want to add {count} pieces of {new_length_formatted} inches to {part_number} ({size} {description})?"
elif action == "Scrap":
message = f"Are you sure you want to scrap {from_length} inch pieces of {part_number} ({size} {description})?"
html_message = f"<div style='font-size: 30px;'>{message}</div>"
return html_message, gr.update(visible=True), part_number
submit.click(
fn=show_confirmation,
inputs=[preferred, search_by, action, part_number, size, description, from_length, job_number, used_length, new_length, count, unit], # Add unit to inputs
outputs=[confirmation_html, confirm_button]
)
confirm_button.click(
fn=main,
inputs=[preferred, search_by, action, part_number, size, description, from_length, job_number, used_length, new_length, count, unit], # Add unit to inputs
outputs=[output_message]
)
demo.launch()