import gradio as gr import pandas as pd import numpy as np import os from datetime import datetime import tempfile from collections import defaultdict # Required columns for dyeing priority calculation REQUIRED_COLS = [ "Account", "Order #", "DESIGN", "Labels", "Colours", "Kgs", "Pending" ] # Additional columns that might be present OPTIONAL_COLS = ["Sqm", "Unnamed: 0"] def _normalize_columns(df: pd.DataFrame) -> pd.DataFrame: """Normalize column names by stripping whitespace""" df = df.copy() df.columns = [str(c).strip() for c in df.columns] return df def _parse_colours(colour_str): """Parse colour string into list of individual colours""" if pd.isna(colour_str): return [] # Handle various separators (comma, semicolon, pipe, etc.) colour_str = str(colour_str).strip() # Try different separators for sep in [',', ';', '|', '/', '+', '&']: if sep in colour_str: colours = [c.strip().upper() for c in colour_str.split(sep) if c.strip()] return colours # If no separators found, treat as single colour return [colour_str.upper()] if colour_str else [] def calculate_colour_totals(df: pd.DataFrame) -> pd.DataFrame: """Calculate total quantity required for each colour across all designs""" colour_totals = defaultdict(float) colour_details = defaultdict(list) # Track which designs use each colour for _, row in df.iterrows(): colours = _parse_colours(row['Colours']) kgs = pd.to_numeric(row['Kgs'], errors='coerce') or 0 design = str(row.get('DESIGN', 'Unknown')) order_num = str(row.get('Order #', 'Unknown')) if colours and kgs > 0: # Distribute weight equally among colours if multiple colours kgs_per_colour = kgs / len(colours) for colour in colours: colour_totals[colour] += kgs_per_colour colour_details[colour].append({ 'Design': design, 'Order': order_num, 'Kgs_Contribution': kgs_per_colour, 'Total_Order_Kgs': kgs }) # Convert to DataFrame with detailed breakdown colour_rows = [] for colour, total_kgs in sorted(colour_totals.items(), key=lambda x: x[1], reverse=True): designs_using = list(set([detail['Design'] for detail in colour_details[colour]])) orders_count = len(colour_details[colour]) colour_rows.append({ 'Colour': colour, 'Total_Kgs_Required': round(total_kgs, 2), 'Designs_Using_This_Colour': ', '.join(sorted(designs_using)), 'Number_of_Orders': orders_count, 'Priority_Rank': len(colour_rows) + 1 }) colour_df = pd.DataFrame(colour_rows) return colour_df, colour_details def create_detailed_colour_breakdown(colour_details: dict) -> pd.DataFrame: """Create detailed breakdown showing which orders contribute to each colour""" breakdown_rows = [] for colour, details in colour_details.items(): for detail in details: breakdown_rows.append({ 'Colour': colour, 'Design': detail['Design'], 'Order_Number': detail['Order'], 'Kgs_for_This_Colour': round(detail['Kgs_Contribution'], 2), 'Total_Order_Kgs': detail['Total_Order_Kgs'] }) breakdown_df = pd.DataFrame(breakdown_rows) # Sort by colour, then by kgs contribution (descending) breakdown_df = breakdown_df.sort_values(['Colour', 'Kgs_for_This_Colour'], ascending=[True, False]) return breakdown_df def detect_date_columns(df: pd.DataFrame) -> list: """Detect date columns in the dataframe""" date_columns = [] for col in df.columns: col_str = str(col).strip() # Try to parse as datetime try: pd.to_datetime(col_str) date_columns.append(col) except: # Check for date patterns like "13/8", "14/8" if '/' in col_str and len(col_str.split('/')) == 2: try: parts = col_str.split('/') if all(part.isdigit() for part in parts): date_columns.append(col) except: pass return date_columns def find_earliest_order_date(df: pd.DataFrame) -> pd.Series: """Find the earliest date for each order from date columns""" date_columns = detect_date_columns(df) if not date_columns: # No date columns found, assign all orders as very old (high priority) return pd.Series([365] * len(df), index=df.index) # 365 days old earliest_dates = [] for idx, row in df.iterrows(): order_dates = [] for date_col in date_columns: cell_value = row[date_col] # Skip if cell is empty or contains non-date data if pd.isna(cell_value) or cell_value == 0 or cell_value == "": continue # Try to parse date from column name try: if '/' in str(date_col): # Handle formats like "13/8" (day/month) day, month = str(date_col).split('/') # Assume current year date_obj = pd.to_datetime(f"2025-{month.zfill(2)}-{day.zfill(2)}") else: # Handle datetime column names date_obj = pd.to_datetime(str(date_col)) # If there's actual data in this cell (not empty/zero), consider this date if not pd.isna(cell_value) and str(cell_value).strip() != "" and str(cell_value) != "0": order_dates.append(date_obj) except: continue # Find earliest date for this order if order_dates: earliest_date = min(order_dates) else: # No valid dates found, assign a default old date earliest_date = pd.to_datetime("2024-01-01") earliest_dates.append(earliest_date) return pd.Series(earliest_dates, index=df.index) def compute_dyeing_priority(df: pd.DataFrame, min_kgs: int = 100, weights: dict = None) -> tuple: """ Compute dyeing priority based on: 1. Oldest orders with minimum kgs per design 2. Designs with fewest colours 3. Order age """ # Default weights if not provided if weights is None: weights = {"AGE_WEIGHT": 50, "COLOUR_SIMPLICITY_WEIGHT": 30, "DESIGN_WEIGHT": 20} df = _normalize_columns(df) # Check for required columns (excluding Date which is now optional) missing = [c for c in REQUIRED_COLS if c not in df.columns] if missing: raise ValueError(f"Missing required columns: {missing}. Found columns: {list(df.columns)}") # Create working copy out = df.copy() # Find earliest order dates from date columns out["OrderDate"] = find_earliest_order_date(out) # Calculate age in days today = pd.Timestamp.now().normalize() out["OrderAgeDays"] = (today - out["OrderDate"]).dt.days out["OrderAgeDays"] = out["OrderAgeDays"].fillna(0).clip(lower=0) # Convert Kgs to numeric out["Kgs"] = pd.to_numeric(out["Kgs"], errors="coerce").fillna(0) # Parse colours and count them out["ColourList"] = out["Colours"].apply(_parse_colours) out["ColourCount"] = out["ColourList"].apply(len) # Group by design to calculate design-level metrics design_groups = out.groupby("DESIGN").agg({ "Kgs": "sum", "OrderDate": "min", # Oldest date for this design "OrderAgeDays": "max", # Maximum age for this design "ColourCount": "first", # Colour count should be same for same design "Order #": "count" # Number of orders for this design }).reset_index() design_groups.columns = ["DESIGN", "Total_Kgs", "Oldest_Date", "Max_Age_Days", "ColourCount", "Order_Count"] # Filter designs that meet minimum kg requirement design_groups["MeetsMinKgs"] = design_groups["Total_Kgs"] >= min_kgs # Calculate scores for designs that meet criteria eligible_designs = design_groups[design_groups["MeetsMinKgs"]].copy() if len(eligible_designs) == 0: # If no designs meet criteria, include all for ranking eligible_designs = design_groups.copy() eligible_designs["MeetsMinKgs"] = False # Age Score (0-1, older = higher) if eligible_designs["Max_Age_Days"].max() > 0: eligible_designs["AgeScore_01"] = eligible_designs["Max_Age_Days"] / eligible_designs["Max_Age_Days"].max() else: eligible_designs["AgeScore_01"] = 0 # Colour Simplicity Score (0-1, fewer colours = higher) if eligible_designs["ColourCount"].max() > 0: eligible_designs["ColourSimplicityScore_01"] = 1 - (eligible_designs["ColourCount"] / eligible_designs["ColourCount"].max()) else: eligible_designs["ColourSimplicityScore_01"] = 0 # Design Volume Score (0-1, more kgs = higher priority for production efficiency) if eligible_designs["Total_Kgs"].max() > 0: eligible_designs["VolumeScore_01"] = eligible_designs["Total_Kgs"] / eligible_designs["Total_Kgs"].max() else: eligible_designs["VolumeScore_01"] = 0 # Calculate weighted priority scores w_age = weights["AGE_WEIGHT"] / 100.0 w_colour = weights["COLOUR_SIMPLICITY_WEIGHT"] / 100.0 w_design = weights["DESIGN_WEIGHT"] / 100.0 eligible_designs["AgeScore"] = eligible_designs["AgeScore_01"] * w_age eligible_designs["ColourSimplicityScore"] = eligible_designs["ColourSimplicityScore_01"] * w_colour eligible_designs["VolumeScore"] = eligible_designs["VolumeScore_01"] * w_design eligible_designs["PriorityScore"] = ( eligible_designs["AgeScore"] + eligible_designs["ColourSimplicityScore"] + eligible_designs["VolumeScore"] ) # Sort by priority eligible_designs = eligible_designs.sort_values( ["MeetsMinKgs", "PriorityScore", "Max_Age_Days"], ascending=[False, False, False] ) # Join back to original data to get detailed view detailed_results = out.merge( eligible_designs[["DESIGN", "Total_Kgs", "Max_Age_Days", "MeetsMinKgs", "AgeScore", "ColourSimplicityScore", "VolumeScore", "PriorityScore"]], on="DESIGN", how="left" ) # Sort detailed results by priority detailed_results = detailed_results.sort_values( ["MeetsMinKgs", "PriorityScore", "OrderAgeDays"], ascending=[False, False, False] ) # Calculate colour totals with detailed breakdown colour_totals, colour_details = calculate_colour_totals(out) colour_breakdown = create_detailed_colour_breakdown(colour_details) return detailed_results, eligible_designs, colour_totals, colour_breakdown def save_dyeing_results(detailed_df, design_summary, colour_totals, colour_breakdown, output_path, min_kgs, weights): """Save all results with multiple sheets""" with pd.ExcelWriter(output_path, engine='openpyxl') as writer: # Sheet 1: Colour Requirements Summary (MAIN PRIORITY - what you need most!) colour_totals.to_excel(writer, sheet_name='COLOUR_REQUIREMENTS', index=False) # Sheet 2: Detailed Colour Breakdown (which orders contribute to each colour) colour_breakdown.to_excel(writer, sheet_name='Colour_Order_Breakdown', index=False) # Sheet 3: Design Summary (design-level priority ranking) design_summary.to_excel(writer, sheet_name='Design_Priority_Summary', index=False) # Sheet 4: Detailed Order Priority detailed_df.to_excel(writer, sheet_name='Order_Priority_Detail', index=False) # Sheet 5: Instructions instructions_data = [ ['🎨 DYEING PRIORITY & COLOUR REQUIREMENTS ANALYSIS'], [''], ['📋 SHEET EXPLANATIONS:'], [''], ['1. COLOUR_REQUIREMENTS - 🎯 MAIN OUTPUT YOU NEED'], [' • Total kgs needed for each colour (consolidated across all designs)'], [' • No colour repetition - each colour listed once with total quantity'], [' • Sorted by quantity (highest first) for production planning'], [' • Shows which designs use each colour and order count'], [''], ['2. Colour_Order_Breakdown - Detailed breakdown'], [' • Shows exactly which orders contribute to each colour total'], [' • Useful for tracking and verification'], [''], ['3. Design_Priority_Summary - Design-level priorities'], [' • Ranked by priority score for production sequence'], [''], ['4. Order_Priority_Detail - Individual order details'], [' • All orders with calculated priority scores'], [''], ['🎯 PRIORITY METHODOLOGY:'], [f'• Age Weight: {weights["AGE_WEIGHT"]}% - Prioritizes older orders'], [f'• Colour Simplicity Weight: {weights["COLOUR_SIMPLICITY_WEIGHT"]}% - Fewer colours = higher priority'], [f'• Design Volume Weight: {weights["DESIGN_WEIGHT"]}% - Larger quantities get priority'], [f'• Minimum Kgs Threshold: {min_kgs} - Only designs with total kgs >= this value are prioritized'], [''], ['🎨 COLOUR CONSOLIDATION LOGIC:'], ['• If RED is used in Design-A (100kg) and Design-B (50kg)'], ['• Output shows: RED = 150kg total (no repetition)'], ['• Helps plan exact dye batch quantities needed'], ['• Multi-colour orders split proportionally (e.g., "Red,Blue" 100kg = 50kg each)'], [''], ['📊 USAGE RECOMMENDATIONS:'], ['• Use COLOUR_REQUIREMENTS sheet for dye purchasing/batching'], ['• Use Design_Priority_Summary for production sequence planning'], ['• Check Colour_Order_Breakdown for detailed verification'], [''], [f'Generated on: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}'] ] instructions_df = pd.DataFrame(instructions_data, columns=['Instructions']) instructions_df.to_excel(writer, sheet_name='Instructions', index=False) # Gradio Interface Functions def load_excel(file): """Load Excel file and return available sheet names""" if file is None: return gr.Dropdown(choices=[]), "Please upload a file first." try: xls = pd.ExcelFile(file.name) return gr.Dropdown(choices=xls.sheet_names, value=xls.sheet_names[0]), "✅ File loaded successfully!" except Exception as e: return gr.Dropdown(choices=[]), f"❌ Error loading file: {str(e)}" def validate_weights(age_weight, colour_weight, design_weight): """Validate that weights sum to 100%""" total = age_weight + colour_weight + design_weight if total == 100: return "✅ Weights are valid (sum = 100%)" else: return f"⚠️ Weights sum to {total}%. Please adjust to equal 100%." def preview_dyeing_data(file, sheet_name): """Preview the selected sheet data for dyeing analysis""" if file is None or not sheet_name: return "Please upload a file and select a sheet first.", pd.DataFrame() try: df = pd.read_excel(file.name, sheet_name=sheet_name) # Show basic info preview_info = f"📊 **Sheet: {sheet_name}**\n" preview_info += f"- Rows: {len(df)}\n" preview_info += f"- Columns: {len(df.columns)}\n\n" # Check for required columns df_norm = df.copy() df_norm.columns = [str(c).strip() for c in df_norm.columns] missing = [c for c in REQUIRED_COLS if c not in df_norm.columns] if missing: preview_info += f"❌ **Missing required columns:** {missing}\n\n" else: preview_info += "✅ **All required columns found!**\n\n" # Detect date columns date_columns = detect_date_columns(df_norm) if date_columns: preview_info += f"📅 **Date columns detected:** {len(date_columns)} columns\n" preview_info += f" Sample dates: {date_columns[:5]}\n\n" else: preview_info += "⚠️ **No date columns detected** - will use default prioritization\n\n" # Show some statistics if 'Kgs' in df_norm.columns: total_kgs = pd.to_numeric(df_norm['Kgs'], errors='coerce').sum() preview_info += f"**Total Kgs:** {total_kgs:,.1f}\n" if 'DESIGN' in df_norm.columns: unique_designs = df_norm['DESIGN'].nunique() preview_info += f"**Unique Designs:** {unique_designs}\n" preview_info += f"\n**Available columns:**\n" for i, col in enumerate(df.columns, 1): marker = "📅" if col in date_columns else "" preview_info += f"{i}. {col} {marker}\n" # Show first few rows preview_df = df.head(5) return preview_info, preview_df except Exception as e: return f"❌ Error previewing data: {str(e)}", pd.DataFrame() def process_dyeing_priority(file, sheet_name, age_weight, colour_weight, design_weight, min_kgs): """Main processing function for dyeing priorities""" if file is None: return None, None, None, "❌ Please upload a file first." if not sheet_name: return None, None, None, "❌ Please select a sheet." # Validate weights total_weight = age_weight + colour_weight + design_weight if total_weight != 100: return None, None, None, f"❌ Error: Total weight must equal 100% (currently {total_weight}%)" try: # Load data df = pd.read_excel(file.name, sheet_name=sheet_name) if df.empty: return None, None, None, "❌ The selected sheet is empty." # Prepare weights weights = { "AGE_WEIGHT": age_weight, "COLOUR_SIMPLICITY_WEIGHT": colour_weight, "DESIGN_WEIGHT": design_weight } # Compute priorities detailed_results, design_summary, colour_totals, colour_breakdown = compute_dyeing_priority( df, min_kgs=min_kgs, weights=weights ) # Create temporary output file output_path = tempfile.NamedTemporaryFile(delete=False, suffix='.xlsx').name save_dyeing_results(detailed_results, design_summary, colour_totals, colour_breakdown, output_path, min_kgs, weights) # Create success message total_designs = len(design_summary) eligible_designs = sum(design_summary['MeetsMinKgs']) total_colours = len(colour_totals) top_colours = colour_totals.head(3)['Colour'].tolist() if len(colour_totals) > 0 else [] success_msg = f"✅ Dyeing Priority Analysis Complete!\n" success_msg += f"📊 SUMMARY:\n" success_msg += f"- Total Designs Analyzed: {total_designs}\n" success_msg += f"- Designs Meeting {min_kgs}kg Threshold: {eligible_designs}\n" success_msg += f"- Unique Colours Required: {total_colours}\n" if top_colours: success_msg += f"- Top 3 Colours by Volume: {', '.join(top_colours)}\n" success_msg += f"- Highest Priority Score: {design_summary['PriorityScore'].max():.3f}\n\n" success_msg += f"🎨 COLOUR REQUIREMENTS sheet contains consolidated totals!\n" success_msg += f"📥 Download complete analysis below" return output_path, design_summary.head(10), colour_totals.head(15), success_msg except Exception as e: return None, None, None, f"❌ Error processing data: {str(e)}" # Create Gradio Interface def create_dyeing_interface(): with gr.Blocks(title="Dyeing Urgency Priority Calculator", theme=gr.themes.Soft()) as demo: gr.Markdown(""" # 🎨 Dyeing Urgency Priority Calculator Upload your Excel file with dyeing/textile manufacturing data to calculate production priorities based on: - **Order Age**: Prioritize older orders first (detects dates from column headers) - **Colour Simplicity**: Fewer colours = easier production - **Design Volume**: Larger quantities for efficiency **Expected Columns**: Account, Order #, DESIGN, Labels, Colours, Kgs, Pending **Date Detection**: Automatically detects date columns (like 2025-01-08, 13/8, etc.) """) with gr.Row(): with gr.Column(scale=1): gr.Markdown("## 📁 File Upload & Selection") file_input = gr.File( label="Upload Excel File", file_types=[".xlsx", ".xls"], type="filepath" ) sheet_dropdown = gr.Dropdown( label="Select Sheet", choices=[], interactive=True ) file_status = gr.Textbox(label="File Status", interactive=False) with gr.Column(scale=1): gr.Markdown("## ⚖️ Priority Weights (must sum to 100%)") age_weight = gr.Slider( minimum=0, maximum=100, value=50, step=1, label="Age Weight (%)", info="Higher = prioritize older orders more" ) colour_weight = gr.Slider( minimum=0, maximum=100, value=30, step=1, label="Colour Simplicity Weight (%)", info="Higher = prioritize designs with fewer colours" ) design_weight = gr.Slider( minimum=0, maximum=100, value=20, step=1, label="Design Volume Weight (%)", info="Higher = prioritize larger quantity designs" ) weight_status = gr.Textbox(label="Weight Validation", interactive=False) min_kgs = gr.Number( label="Minimum Kgs Threshold per Design", value=100, info="Only designs with total kgs >= this value get priority" ) with gr.Row(): preview_btn = gr.Button("👁️ Preview Data", variant="secondary") process_btn = gr.Button("🎨 Calculate Dyeing Priorities", variant="primary", size="lg") with gr.Row(): with gr.Column(): gr.Markdown("## 📊 Data Preview") preview_info = gr.Textbox(label="Data Information", lines=10, interactive=False) preview_table = gr.Dataframe(label="Sample Data") with gr.Row(): with gr.Column(): gr.Markdown("## 🏆 Priority Results") results_info = gr.Textbox(label="Processing Status", interactive=False) with gr.Column(): download_file = gr.File(label="📥 Download Complete Analysis") with gr.Row(): with gr.Column(): gr.Markdown("## 📋 Top Design Priorities") design_results = gr.Dataframe(label="Design Priority Summary") with gr.Column(): gr.Markdown("## 🎨 Colour Requirements (Consolidated)") colour_results = gr.Dataframe( label="Total Kgs Required Per Colour", headers=["Colour", "Total Kgs", "Used in Designs", "Orders Count"], interactive=False ) # Event handlers file_input.change( fn=load_excel, inputs=[file_input], outputs=[sheet_dropdown, file_status] ) for weight_input in [age_weight, colour_weight, design_weight]: weight_input.change( fn=validate_weights, inputs=[age_weight, colour_weight, design_weight], outputs=[weight_status] ) preview_btn.click( fn=preview_dyeing_data, inputs=[file_input, sheet_dropdown], outputs=[preview_info, preview_table] ) process_btn.click( fn=process_dyeing_priority, inputs=[file_input, sheet_dropdown, age_weight, colour_weight, design_weight, min_kgs], outputs=[download_file, design_results, colour_results, results_info] ) # Initialize weight validation demo.load( fn=validate_weights, inputs=[age_weight, colour_weight, design_weight], outputs=[weight_status] ) return demo # Launch the app if __name__ == "__main__": demo = create_dyeing_interface() demo.launch( #server_name="0.0.0.0", #server_port=7860, share=True, debug=True )