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"