from flask import Flask, render_template_string, jsonify, Response import pandas as pd import os from collections import Counter from io import BytesIO app = Flask(__name__) # ----------------------------------------------------- # Load and preprocess data # ----------------------------------------------------- if os.path.exists("BACKLOG.xlsx"): df = pd.read_excel("BACKLOG.xlsx") else: df = pd.DataFrame({ "SALES_ORDER_NO": [], "CUSTOMER NAME": [], "CREDIT BLOCK": [], "DELIVERY BLOCK": [], "PRICING BLOCK": [], "SHIP DEBIT BLOCK": [], "EXTENDED_RESALE": [], "ATP_DATE_RAW": [] }) df.columns = df.columns.str.strip().str.upper() SO_COL = "SALES_ORDER_NO" CUSTOMER_COL = "CUSTOMER NAME" WORTH_COL = "EXTENDED_RESALE" BLOCK_COLUMNS = ["CREDIT BLOCK", "DELIVERY BLOCK", "PRICING BLOCK", "SHIP DEBIT BLOCK"] DATE_COL = "ATP_DATE_RAW" if "ATP_DATE_RAW" in df.columns else None BLOCK_MAP = { "CREDIT BLOCK": "Credit", "DELIVERY BLOCK": "Delivery", "PRICING BLOCK": "Pricing", "SHIP DEBIT BLOCK": "Ship Debit" } # ----------------------------------------------------- # Helper functions # ----------------------------------------------------- def block_summary_df(): tmp = df.copy() exploded = tmp.assign(BLOCK_TYPE=tmp[BLOCK_COLUMNS].apply( lambda row: " + ".join([BLOCK_MAP[c] for c in BLOCK_COLUMNS if str(row[c]).upper() == "X"]) or "No Block", axis=1)) exploded = exploded[exploded["BLOCK_TYPE"] != "No Block"] agg = exploded.groupby("BLOCK_TYPE", as_index=False)[WORTH_COL].sum().sort_values(WORTH_COL, ascending=False) return agg def actionable_focus_df(): bs = block_summary_df() if bs.empty: return pd.DataFrame(), "No actionable focus area data." total_val = bs[WORTH_COL].sum() bs["SHARE_%"] = (bs[WORTH_COL] / total_val) * 100 top2 = bs.head(2) blocks = ", ".join(top2["BLOCK_TYPE"]) insight = f"{blocks} contribute {top2['SHARE_%'].sum():.1f}% of total blocked value." return top2, insight def multiple_block_df(): tmp = df.copy() tmp["BLOCK_COUNT"] = tmp[BLOCK_COLUMNS].apply( lambda row: sum(str(x).upper() == "X" for x in row), axis=1) multiblocks = tmp[tmp["BLOCK_COUNT"] > 1][[SO_COL, CUSTOMER_COL, WORTH_COL, "BLOCK_COUNT"]] return multiblocks def quarter_trends_df(): if DATE_COL not in df.columns: return pd.DataFrame() temp = df.copy() temp[DATE_COL] = pd.to_datetime(temp[DATE_COL], errors="coerce") temp["QUARTER"] = temp[DATE_COL].dt.to_period("Q").astype(str) recs = [] for b in BLOCK_COLUMNS: subset = temp[temp[b].astype(str).eq("X")] grouped = subset.groupby("QUARTER")[WORTH_COL].sum().reset_index() grouped["BLOCK_TYPE"] = BLOCK_MAP[b] recs.append(grouped) qdf = pd.concat(recs, ignore_index=True) # Correct chronological sorting qdf = qdf.dropna(subset=["QUARTER"]) qdf["YEAR"] = qdf["QUARTER"].str[:4].astype(int) qdf["QNUM"] = qdf["QUARTER"].str[-1].astype(int) qdf = qdf.sort_values(["YEAR", "QNUM"]).drop(columns=["YEAR", "QNUM"]) return qdf def get_top_faqs(limit=6): """Read logged queries and return top N most common questions.""" if not os.path.exists("faq_log.csv"): return [ "Which block type had the highest backlog last quarter?", "Which customers have the most open blocked orders?", "How many orders have multiple active blocks?", "What is the trend of delivery blocks across quarters?", "Which blocks contribute the most to total backlog value?" ] with open("faq_log.csv", "r", encoding="utf-8") as f: lines = [l.strip() for l in f if l.strip()] # keep last 500 if len(lines) > 500: with open("faq_log.csv", "w", encoding="utf-8") as f: f.write("\n".join(lines[-500:])) common = [q for q, _ in Counter(lines).most_common(limit)] return common if common else ["Ask me about blocked orders or quarterly trends!"] # ----------------------------------------------------- # Chatbot Endpoint # ----------------------------------------------------- @app.route("/chat_ollama/") def chat_ollama(query): q = query.lower().strip() # Log the user query to file try: with open("faq_log.csv", "a", encoding="utf-8") as f: f.write(q + "\n") except Exception as e: print("FAQ log write error:", e) reply = "No relevant insight found. Try asking about customers, block types, or quarterly trends." try: # --- CUSTOMER-LEVEL LOGIC --- if "customer" in q: cust_summary = ( df.groupby(CUSTOMER_COL)[WORTH_COL] .sum() .sort_values(ascending=False) .reset_index() ) top = cust_summary.iloc[0] cust_name, value = top[CUSTOMER_COL], top[WORTH_COL] block_counts = ( df[df[CUSTOMER_COL] == cust_name][BLOCK_COLUMNS] .apply(lambda row: [BLOCK_MAP[c] for c in BLOCK_COLUMNS if str(row[c]).upper() == "X"], axis=1) .explode() .value_counts() ) main_block = block_counts.index[0] if not block_counts.empty else "general" reply = ( f"{cust_name} currently holds the highest backlog value (${value:,.0f}) " f"with {main_block} as the main issue. Coordinate cross-functionally to resolve {main_block.lower()} constraints." ) # --- MULTIPLE BLOCK ORDERS --- elif "multiple" in q or "two blocks" in q or "more than one" in q: mb = multiple_block_df() if not mb.empty: count = mb[SO_COL].nunique() reply = ( f"{count} orders have multiple active blocks. " f"Prioritize clearance for these to restore smoother flow." ) else: reply = "No orders found with multiple active blocks." # --- BLOCK SUMMARY LOGIC --- elif "block" in q and "quarter" not in q: bs = block_summary_df() if not bs.empty: top = bs.iloc[0] reply = ( f"{top['BLOCK_TYPE']} block represents the largest backlog exposure " f"(${top[WORTH_COL]:,.0f}). Engage respective teams to address {top['BLOCK_TYPE'].lower()} issues." ) # --- QUARTERLY TREND LOGIC --- elif "quarter" in q: qdf = quarter_trends_df() if not qdf.empty: quarters = sorted( qdf["QUARTER"].unique(), key=lambda q: (int(q[:4]), int(q[-1])) ) if quarters: next_quarter = quarters[-1] next_q_df = qdf[qdf["QUARTER"] == next_quarter] if not next_q_df.empty: top_block = next_q_df.sort_values(WORTH_COL, ascending=False).iloc[0] reply = ( f"In {next_quarter}, {top_block['BLOCK_TYPE']} block shows the highest exposure " f"(${top_block[WORTH_COL]:,.0f}). Prepare mitigation plans with owners." ) else: reply = f"No data found for {next_quarter}." except Exception as e: reply = f"(Error: {e})" return jsonify({"response": reply}) # ----------------------------------------------------- # Data APIs # ----------------------------------------------------- @app.route("/data/summary") def data_summary(): bs = block_summary_df() if bs.empty: return jsonify({"columns": [], "rows": [], "insight": "No data available."}) insight = f"{bs.iloc[0]['BLOCK_TYPE']} block has the highest backlog exposure (${bs.iloc[0][WORTH_COL]:,.0f})." return jsonify({ "columns": list(bs.columns), "rows": bs.to_dict("records"), "insight": insight, "download": "/download/summary" }) @app.route("/data/focus_area") def data_focus_area(): top2, insight = actionable_focus_df() return jsonify({ "columns": list(top2.columns), "rows": top2.to_dict("records"), "insight": insight, "download": "/download/focus" }) @app.route("/data/multiple_blocks") def data_multiple_blocks(): mb = multiple_block_df() insight = ( f"{mb[SO_COL].nunique()} orders have multiple active blocks. " "Prioritize clearance for these to restore smoother flow." if not mb.empty else "No multiple block orders found." ) return jsonify({ "columns": list(mb.columns), "rows": mb.to_dict("records"), "insight": insight, "download": "/download/multiple" }) @app.route("/data/quarter_trends") def data_quarter_trends(): qdf = quarter_trends_df() if qdf.empty: return jsonify({"columns": [], "rows": [], "insight": "No quarterly data found."}) next_quarter = qdf["QUARTER"].max() next_q_df = qdf[qdf["QUARTER"] == next_quarter] if not next_q_df.empty: top_block = next_q_df.sort_values(WORTH_COL, ascending=False).iloc[0] insight = ( f"In {next_quarter}, {top_block['BLOCK_TYPE']} Block held the highest backlog exposure " f"(${top_block[WORTH_COL]:,.0f}). Coordinate with teams to expedite clearance." ) else: insight = "No valid quarter trend data found." return jsonify({ "columns": list(qdf.columns), "rows": qdf.to_dict("records"), "insight": insight, "download": "/download/quarter" }) # ----------------------------------------------------- # HTML Template with adaptive FAQ placeholder # ----------------------------------------------------- HTML = """ Blocks Assistant
Blocks Assistant
Overall Block Summary
Actionable Focus Area
Multiple Block Orders
Quarter-wise Trends
💡 FAQ:
Hello!
I'm your Blocks Assistant. How can I help you today?
""" # ----------------------------------------------------- # Dynamic injection of top FAQs # ----------------------------------------------------- @app.route("/") def home(): faqs = get_top_faqs() faq_js_array = "[" + ",".join([f'"{f}"' for f in faqs]) + "]" html_with_faqs = HTML.replace("const faqs=[PLACEHOLDER_FAQS];", f"const faqs={faq_js_array};") return render_template_string(html_with_faqs) # ----------------------------------------------------- # CSV Download Endpoints # ----------------------------------------------------- def df_to_csv_response(dataframe, filename): csv_data = dataframe.to_csv(index=False) return Response(csv_data, mimetype="text/csv", headers={"Content-Disposition": f"attachment;filename={filename}.csv"}) @app.route("/download/summary") def download_summary(): bs = block_summary_df() if bs.empty: return jsonify({"error": "No data"}), 404 return df_to_csv_response(bs, "block_summary") @app.route("/download/focus") def download_focus(): top2, _ = actionable_focus_df() if top2.empty: return jsonify({"error": "No data"}), 404 return df_to_csv_response(top2, "actionable_focus") @app.route("/download/multiple") def download_multiple(): mb = multiple_block_df() if mb.empty: return jsonify({"error": "No data"}), 404 return df_to_csv_response(mb, "multiple_blocks") @app.route("/download/quarter") def download_quarter(): qdf = quarter_trends_df() if qdf.empty: return jsonify({"error": "No data"}), 404 return df_to_csv_response(qdf, "quarter_trends") if __name__ == "__main__": app.run(host="0.0.0.0", port=7860)