"""SAP blueprint — HANA SQL generation, analytics fast-path.""" import calendar, json, logging, os, re, uuid from datetime import datetime, timedelta from zoneinfo import ZoneInfo _IST = ZoneInfo("Asia/Kolkata") import numpy as np from flask import Blueprint, jsonify, request, session import extensions as ext from services.user_service import get_user_where_clause from services.formatter import format_rows from services.rbac_decorators import admin_required, login_required logger = logging.getLogger("apex") sap_bp = Blueprint("sap", __name__) _HANA_HOST = os.getenv("HANA_HOST", "") _HANA_PORT = int(os.getenv("HANA_PORT", "30015")) _HANA_USER = os.getenv("HANA_USER", "") _HANA_PASSWORD = os.getenv("HANA_PASSWORD", "") _REDIS_EXPIRY = int(os.getenv("REDIS_EXPIRY", "86400")) # ── HANA ────────────────────────────────────────────────────────────────────── def get_hana_connection(): from hdbcli import dbapi return dbapi.connect(address=_HANA_HOST, port=_HANA_PORT, user=_HANA_USER, password=_HANA_PASSWORD) def run_hana_query(query: str): conn = None try: conn = get_hana_connection() cur = conn.cursor() cur.execute(query) if cur.description: cols = [c[0] for c in cur.description] return [dict(zip(cols, row)) for row in cur.fetchall()] return {"affected_rows": cur.rowcount} except Exception as exc: return {"error": str(exc)} finally: if conn: conn.close() # ── helpers ──────────────────────────────────────────────────────────────────── def _amt(): return "CASE WHEN VBTYP IN ('N','O') THEN NETVALUE * -1 ELSE NETVALUE END * CASE WHEN CURRENCY='USD' THEN KURRF/100 ELSE 1 END" def _shift_months(base, delta): idx = base.month - 1 + delta return datetime(base.year + idx // 12, idx % 12 + 1, 1).date() def _date_window(ui): lo = (ui or "").lower() today = datetime.now(tz=_IST).date() if "yesterday" in lo: c = (today - timedelta(days=1)).strftime("%Y%m%d"); return c, c, "yesterday" if "today" in lo: c = today.strftime("%Y%m%d"); return c, c, "today" if "last month" in lo or "previous month" in lo: ms = _shift_months(today, -1) me = datetime(ms.year, ms.month, calendar.monthrange(ms.year, ms.month)[1]).date() return ms.strftime("%Y%m%d"), me.strftime("%Y%m%d"), "last month" m = re.search(r"last\s+(\d+)\s+months?", lo) if m: n = max(1, min(int(m.group(1)), 12)) ms = _shift_months(today.replace(day=1), -(n-1)) return ms.strftime("%Y%m%d"), today.strftime("%Y%m%d"), f"last {n} months" if "this month" in lo or "current month" in lo: ms = today.replace(day=1) return ms.strftime("%Y%m%d"), today.strftime("%Y%m%d"), "this month" return None, None, "selected period" def _extract_limit(ui, default=5, maximum=25): lo = (ui or "").lower() if any(p in lo for p in ["highest one","top one","best one","single best","top product","best product"]): return 1 m = re.search(r"\btop\s+(\d+)\b", lo) return max(1, min(int(m.group(1)), maximum)) if m else default def _safe_sess(key, default=None): try: return session.get(key, default) except RuntimeError: return default def _ext_where(email): if _safe_sess("user_type") != "authenticated" or not email: return "" return get_user_where_clause(email) def _where_sql(ds, de, ew=""): if ds and de: return f" WHERE BILLINGDATE BETWEEN '{ds}' AND '{de}'{ew}" if ew: return f" WHERE 1=1{ew}" return "" def _norm(result): if isinstance(result, dict): if result.get("error"): return result if "affected_rows" in result: return [] return result if isinstance(result, list) else [] def _safe(value): if value is None or isinstance(value, (str, int, float, bool)): return value if isinstance(value, np.generic): return value.item() if isinstance(value, dict): return {str(k): _safe(v) for k, v in value.items()} if isinstance(value, (list, tuple, set)): return [_safe(i) for i in value] iso = getattr(value, "isoformat", None) if callable(iso): try: return iso() except: pass return str(value) def _ctx_key(): token = _safe_sess("apex_hana_context_token") if not token: token = uuid.uuid4().hex try: session["apex_hana_context_token"] = token except RuntimeError: pass return f"apex:last_hana:{token}" def _store_last(ui, payload): safe = {"user_input": ui, "answer": payload.get("answer"), "result": _safe(payload.get("result") or []), "sql": payload.get("sql"), "title": payload.get("title"), "data_source": payload.get("data_source") or "sap_hana"} try: ext.redis_client.set(_ctx_key(), json.dumps(safe), ex=_REDIS_EXPIRY) except Exception as e: logger.warning("[SAP] cache error: %s", e) def _get_last(): try: raw = ext.redis_client.get(_ctx_key()) if not raw: return None if isinstance(raw, bytes): raw = raw.decode("utf-8", errors="ignore") return json.loads(raw) except: return None def _get_email_where(): email = _safe_sess("user") return email, _ext_where(email) # ── payloads ─────────────────────────────────────────────────────────────────── def _summary_payload(email, ew): from cells import fetch_cells_data cache_key = f"kpi:{email}" if email else None summary = None if cache_key: cached = ext.redis_client.get(cache_key) if cached: try: _c = json.loads(cached) if any(isinstance(v, (int, float)) and v != 0 for v in _c.values()): summary = _c except: pass if not summary: summary = fetch_cells_data(email=email, external_where=ew) if ew and summary and all(v == 0 for v in summary.values() if isinstance(v, (int, float))): summary = fetch_cells_data(email=email, external_where="") if cache_key and summary: try: ext.redis_client.set(cache_key, json.dumps(summary), ex=_REDIS_EXPIRY) except: pass rows = [{"Metric": k, "Value (Cr)": v} for k, v in summary.items()] def _fmt(v): try: return f"₹{float(v):,.2f} Cr" except: return str(v) parts = [f"{k}: {_fmt(summary[k])}" for k in ["Today's Sales","Yesterday's Sales","QTD","YTD"] if k in summary] return {"sql":"-- KPI summary","result":rows, "answer":"Sales snapshot — " + " · ".join(parts) if parts else "Sales data is ready.", "data_source":"sap_hana","title":"Sales Summary"} def _period_payload(ui, ew): ds, de, label = _date_window(ui) w = _where_sql(ds, de, ew); a = _amt() _reg = "CASE WHEN STATENAME IS NULL OR TRIM(STATENAME)='' THEN 'Unknown' ELSE STATENAME END" rows_total = _norm(run_hana_query(f"SELECT ROUND(SUM({a})/10000000,2) AS REVENUE_CR FROM sapabap1.ztab_salesv1{w}")) if isinstance(rows_total, dict) and rows_total.get("error"): return rows_total, 500 rev = float((rows_total[0].get("REVENUE_CR", 0) if rows_total else 0) or 0) sql_r = f"SELECT TOP 15 {_reg} AS REGION, ROUND(SUM({a})/10000000,2) AS REVENUE_CR FROM sapabap1.ztab_salesv1{w} GROUP BY {_reg} ORDER BY SUM({a}) DESC" rows_r = _norm(run_hana_query(sql_r)) or [] answer = f"{label.title()} total sales: ₹{rev:,.2f} Cr" if rows_r: answer += f" | Top: {rows_r[0].get('REGION')} ₹{rows_r[0].get('REVENUE_CR',0):,.2f} Cr" answer += f" | {len(rows_r)} regions." return {"sql":sql_r,"result":rows_r,"answer":answer,"data_source":"sap_hana","title":f"{label.title()} Sales"}, 200 def _ranked_payload(ui, ew, dim_label, dim_expr, limit_override=None): ds, de, label = _date_window(ui) w = _where_sql(ds, de, ew); a = _amt() limit = limit_override or _extract_limit(ui) sql = (f"SELECT TOP {limit} {dim_expr} AS {dim_label}, " f"ROUND(SUM({a})/10000000,2) AS REVENUE_CR " f"FROM sapabap1.ztab_salesv1{w} GROUP BY {dim_expr} ORDER BY SUM({a}) DESC") rows = _norm(run_hana_query(sql)) if isinstance(rows, dict) and rows.get("error"): return rows, 500 readable = dim_label.lower().replace("_"," ") if rows: top = rows[0]; tv = top.get("REVENUE_CR", 0) answer = f"Top {readable} for {label if ds else 'all time'}" total = sum(float(r.get("REVENUE_CR",0) or 0) for r in rows) answer += f" | Total: ₹{total:,.2f} Cr | #1: {top.get(dim_label)} ₹{float(tv):,.2f} Cr." else: answer = f"No {readable} records found" + (f" for {label}." if ds else ".") return {"sql":sql,"result":rows,"answer":answer,"data_source":"sap_hana","title":dim_label.replace("_"," ").title()}, 200 def _txn_payload(ui, ew, limit=100): ds, de, label = _date_window(ui) w = _where_sql(ds, de, ew); a = _amt() sql = (f"SELECT TOP {limit} BILLINGDATE, PLANTNAME, STATENAME, " "CASE WHEN MAT_GRP_NAME IS NULL OR TRIM(MAT_GRP_NAME)='' THEN MATERIALGROUP ELSE MAT_GRP_NAME END AS PRODUCT_GROUP, " f"PARTNERNAME, ROUND(({a})/10000000,2) AS REVENUE_CR " f"FROM sapabap1.ztab_salesv1{w} ORDER BY {a} DESC") rows = _norm(run_hana_query(sql)) if isinstance(rows, dict) and rows.get("error"): return rows, 500 return {"sql":sql,"result":rows,"answer":f"Transactions for {label if ds else 'requested scope'}.","data_source":"sap_hana","title":"Transactions"}, 200 # ── fast-path & follow-up ────────────────────────────────────────────────────── _PROD = "CASE WHEN MAT_GRP_NAME IS NULL OR TRIM(MAT_GRP_NAME)='' THEN MATERIALGROUP ELSE MAT_GRP_NAME END" _REG = "CASE WHEN STATENAME IS NULL OR TRIM(STATENAME)='' THEN 'Unknown' ELSE STATENAME END" def _is_show(ui): n = re.sub(r"\s+"," ",(ui or "").strip().lower()) return n.startswith("show") or "display" in n or "table" in n or n in {"show","show more","show all","show data"} def _is_summary_req(ui): n = re.sub(r"\s+"," ",(ui or "").strip().lower()) return any(m in n for m in ["summary of this","summarize this","summarise this","give insight of this","explain this table","summary of above"]) def _is_analytics(ui): lo = (ui or "").lower() if any(p in lo for p in ["sales summary","sales overview","today sales","yesterday sales","ytd","qtd"]): return True if any(s in lo for s in ["sales","revenue","turnover"]) and any(t in lo for t in ["today","yesterday","month","quarter","ytd","qtd","last","this","trend","summary","performance"]): return True if "product" in lo and any(m in lo for m in ["top","best","highest","leading"]): return True if any(m in lo for m in ["region","state","zone"]) and any(m in lo for m in ["top","best","compare","performance"]): return True return False def _follow_up(ui): last = _get_last() if not last: return None email, ew = _get_email_where() rows = last.get("result") or [] lo_last = (last.get("user_input") or "").lower() if _is_show(ui): if len(rows) > 1: r = dict(last); r["answer"] = f"Latest result — {len(rows)} row(s)."; return r, 200 if any(k in lo_last for k in ["product","material"]): return _ranked_payload(lo_last, ew, "PRODUCT_GROUP", _PROD, 10) if any(k in lo_last for k in ["region","state","zone"]): return _ranked_payload(lo_last, ew, "REGION", _REG, 10) if any(k in lo_last for k in ["sales","revenue","month","today","yesterday"]): return _txn_payload(lo_last, ew) r = dict(last); r["answer"] = "Latest SAP HANA result."; return r, 200 if _is_summary_req(ui): r = dict(last); r["answer"] = _summarize(rows, last.get("title")) r["title"] = f"Summary — {last.get('title') or 'Latest'}"; return r, 200 return None def _fast_path(ui): lo = (ui or "").lower() email, ew = _get_email_where() if any(k in lo for k in ["sales summary","sales overview","snapshot","kpi","today sales","today's sales","yesterday sales","yesterday's sales","ytd","qtd"]): return _summary_payload(email, ew), 200 if "overall" in lo and any(k in lo for k in ["sales","revenue"]): return _period_payload(ui, ew) if any(k in lo for k in ["sales","revenue","turnover"]) and any(k in lo for k in ["last month","this month","current month","today","yesterday"]): return _period_payload(ui, ew) m_months = re.search(r"last\s+(\d+)\s+months?", lo) if m_months: if any(k in lo for k in ["product","material","brand"]): return _ranked_payload(ui, ew, "PRODUCT_GROUP", _PROD, 10) if any(k in lo for k in ["region","state","zone","branch"]): return _ranked_payload(ui, ew, "REGION", _REG, 10) if any(k in lo for k in ["partner","vendor","customer"]): return _ranked_payload(ui, ew, "PARTNERNAME", "PARTNERNAME", 10) return _period_payload(ui, ew) if any(k in lo for k in ["top","best"]) and any(k in lo for k in ["product","products","material","materials"]): return _ranked_payload(ui, ew, "PRODUCT_GROUP", _PROD) if "product" in lo and any(k in lo for k in ["highest","leading","insight"]): return _ranked_payload(ui, ew, "PRODUCT_GROUP", _PROD) if any(k in lo for k in ["region","regions","state","states","zone","zones","branch performance","branch"]): return _ranked_payload(ui, ew, "REGION", _REG) return None def _summarize(rows, title=None): if not isinstance(rows, list) or not rows: return "No previous result available." first = rows[0] if isinstance(rows[0], dict) else None if not first: return f"Result has {len(rows)} row(s)." cols = list(first.keys()) def is_num(v): try: float(v); return True except: return False num_cols = [c for c in cols if any(is_num(r.get(c)) for r in rows)] txt_cols = [c for c in cols if c not in num_cols] parts = [f"{len(rows)} rows"] if num_cols: pc = num_cols[0]; nums = [float(r.get(pc)) for r in rows if is_num(r.get(pc))] if nums: parts.append(f"{pc}: {min(nums):,.2f}–{max(nums):,.2f}") if txt_cols: best = max([r for r in rows if is_num(r.get(pc))], key=lambda r: float(r.get(pc, 0))) parts.append(f"top: {best.get(txt_cols[0])} @ {float(best.get(pc,0)):,.2f}") if title: parts.append(title) return " | ".join(parts) + "." # ── main flow ────────────────────────────────────────────────────────────────── def execute_hana_query_flow(ui: str): if not ui: return {"error": "Missing input"}, 400 greetings = {"hi","hello","hey","hola","namaste","good morning","good afternoon","good evening"} if ui.lower() in greetings or any(g in ui.lower() for g in ["who are you","what can you do"]): return {"answer":"Hello! I can help with SAP HANA data, sales analysis, and general AI chat.","result":[]}, 200 fu = _follow_up(ui) if fu: payload, code = fu if code < 400: _store_last(ui, payload) return payload, code fast = _fast_path(ui) if fast: payload, code = fast if code < 400: _store_last(ui, payload) return fast return {"answer":"I couldn't find matching data. Try asking about today's sales, top products, or region performance.","result":[]}, 200 # ── report helpers ───────────────────────────────────────────────────────────── def _report_dates(): today = datetime.now(tz=_IST).date() ms = today.replace(day=1); pe = ms - timedelta(days=1); ps = pe.replace(day=1) return today.strftime("%Y%m%d"), ms.strftime("%Y%m%d"), ps.strftime("%Y%m%d"), pe.strftime("%Y%m%d") def _fy_start(): today = datetime.now(tz=_IST).date() yr = today.year if today.month >= 4 else today.year - 1 return datetime(yr, 4, 1).strftime("%Y%m%d") def _rep(sql, title): return jsonify({"result": run_hana_query(sql), "title": title}) # ── routes ───────────────────────────────────────────────────────────────────── @sap_bp.route("/query", methods=["POST"]) @login_required def query(): body = request.get_json() or {} payload, code = execute_hana_query_flow(body.get("input","").strip()) return jsonify(payload), code @sap_bp.route("/sap/sales-kpis", methods=["GET"]) @login_required def sales_kpis(): from cells import fetch_cells_data email = session.get("user"); ew = _ext_where(email) try: kpis = fetch_cells_data(email=email, external_where=ew) if ew and kpis and all(v==0 for v in kpis.values() if isinstance(v,(int,float))): kpis = fetch_cells_data(email=email, external_where="") return jsonify({"kpis": kpis}) except Exception as e: return jsonify({"kpis":{}, "error": str(e)}), 200 @sap_bp.route("/sap/kpi-debug", methods=["GET"]) @admin_required def kpi_debug(): import datetime as dt email = session.get("user"); ew = _ext_where(email) today = dt.datetime.now(tz=ZoneInfo("Asia/Kolkata")).date() yd = today - dt.timedelta(days=1) fy = dt.date(today.year if today.month>=4 else today.year-1, 4, 1) qm = (today.month-1)//3*3+1; qtd = dt.date(today.year, qm, 1); mtd = dt.date(today.year, today.month, 1) td, yd_s, fy_s, qtd_s, mtd_s = today.strftime("%Y%m%d"), yd.strftime("%Y%m%d"), fy.strftime("%Y%m%d"), qtd.strftime("%Y%m%d"), mtd.strftime("%Y%m%d") a = _amt() queries = { "Today": f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE='{td}'{ew}", "Yesterday": f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE='{yd_s}'{ew}", "MTD": f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE>='{mtd_s}' AND BILLINGDATE<='{td}'{ew}", "YTD": f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE>='{fy_s}' AND BILLINGDATE<='{td}'{ew}", "QTD": f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE>='{qtd_s}' AND BILLINGDATE<='{td}'{ew}", } raw = {}; hana_error = None try: conn = get_hana_connection(); cur = conn.cursor() for label, sql in queries.items(): try: cur.execute(sql); raw[label] = {"value": cur.fetchone()[0], "sql": sql} except Exception as qe: raw[label] = {"error": str(qe), "sql": sql} cur.close(); conn.close() except Exception as ce: hana_error = str(ce) return jsonify({"session_email":email,"external_where":ew,"today":str(today),"hana_connection_error":hana_error,"raw_hana_results":raw}) @sap_bp.route("/report/sales", methods=["GET"]) @login_required def report_sales(): email = session.get("user"); ew = _ext_where(email) td, ms, _, _ = _report_dates(); a = _amt() return _rep(f"SELECT TOP 50 BRANDCODE, MAT_GRP_NAME, ROUND(SUM({a})/10000000,2) AS REVENUE_CR, COUNT(*) AS LINES FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{ms}' AND '{td}'{ew} GROUP BY BRANDCODE, MAT_GRP_NAME ORDER BY SUM({a}) DESC", "Sales by Brand - MTD") @sap_bp.route("/report/stock", methods=["GET"]) @login_required def report_stock(): email = session.get("user"); ew = _ext_where(email) td, ms, _, _ = _report_dates(); a = _amt() return _rep(f"SELECT TOP 100 MATERIALGROUP, MAT_GRP_NAME, BRANDCODE, ROUND(SUM({a})/10000000,2) AS REVENUE_CR, COUNT(*) AS LINES FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{ms}' AND '{td}'{ew} GROUP BY MATERIALGROUP, MAT_GRP_NAME, BRANDCODE ORDER BY SUM({a}) DESC", "Stock/Product Mix - MTD") @sap_bp.route("/report/transit", methods=["GET"]) @login_required def report_transit(): email = session.get("user"); ew = _ext_where(email) today = datetime.now(tz=_IST).date(); s7 = (today-timedelta(days=7)).strftime("%Y%m%d"); td = today.strftime("%Y%m%d"); a = _amt() return _rep(f"SELECT BILLINGDATE, PLANTNAME, STATENAME, ROUND(SUM({a})/10000000,2) AS REVENUE_CR, COUNT(*) AS LINES FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{s7}' AND '{td}'{ew} GROUP BY BILLINGDATE, PLANTNAME, STATENAME ORDER BY BILLINGDATE DESC, REVENUE_CR DESC", "Daily Transit - Last 7 Days") @sap_bp.route("/report/vendor-ageing", methods=["GET"]) @login_required def report_vendor_ageing(): email = session.get("user"); ew = _ext_where(email) td, ms, ps, pe = _report_dates(); a = _amt() return _rep(f"SELECT TOP 50 PARTNERCODE, PARTNERNAME, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ms}' AND '{td}' THEN {a} ELSE 0 END)/10000000,2) AS MTD_CR, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ps}' AND '{pe}' THEN {a} ELSE 0 END)/10000000,2) AS PREV_MONTH_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{ps}' AND '{td}'{ew} GROUP BY PARTNERCODE, PARTNERNAME ORDER BY MTD_CR DESC", "Vendor Ageing") @sap_bp.route("/report/branch-ageing", methods=["GET"]) @login_required def report_branch_ageing(): email = session.get("user"); ew = _ext_where(email) td, ms, ps, pe = _report_dates(); a = _amt() return _rep(f"SELECT PLANTNAME, STATENAME, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ms}' AND '{td}' THEN {a} ELSE 0 END)/10000000,2) AS MTD_CR, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ps}' AND '{pe}' THEN {a} ELSE 0 END)/10000000,2) AS PREV_MONTH_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{ps}' AND '{td}'{ew} GROUP BY PLANTNAME, STATENAME ORDER BY MTD_CR DESC", "Branch Ageing") @sap_bp.route("/report/vendor-backlog", methods=["GET"]) @login_required def report_vendor_backlog(): email = session.get("user"); ew = _ext_where(email) td, ms, ps, pe = _report_dates(); a = _amt() return _rep(f"SELECT TOP 50 PARTNERCODE, PARTNERNAME, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ps}' AND '{pe}' THEN {a} ELSE 0 END)/10000000,2) AS PREV_MONTH_CR, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ms}' AND '{td}' THEN {a} ELSE 0 END)/10000000,2) AS MTD_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{ps}' AND '{td}'{ew} GROUP BY PARTNERCODE, PARTNERNAME HAVING SUM(CASE WHEN BILLINGDATE BETWEEN '{ms}' AND '{td}' THEN {a} ELSE 0 END) < SUM(CASE WHEN BILLINGDATE BETWEEN '{ps}' AND '{pe}' THEN {a} ELSE 0 END) ORDER BY PREV_MONTH_CR DESC", "Vendor Backlog") @sap_bp.route("/report/vendor-transit", methods=["GET"]) @login_required def report_vendor_transit(): email = session.get("user"); ew = _ext_where(email) today = datetime.now(tz=_IST).date(); s7 = (today-timedelta(days=7)).strftime("%Y%m%d"); td = today.strftime("%Y%m%d"); a = _amt() return _rep(f"SELECT TOP 50 PARTNERCODE, PARTNERNAME, BILLINGDATE, PLANTNAME, ROUND(SUM({a})/10000000,2) AS REVENUE_CR, COUNT(*) AS LINES FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{s7}' AND '{td}'{ew} GROUP BY PARTNERCODE, PARTNERNAME, BILLINGDATE, PLANTNAME ORDER BY BILLINGDATE DESC, REVENUE_CR DESC", "Vendor Transit") @sap_bp.route("/report/zsales-order", methods=["GET"]) @login_required def report_zsales_order(): email = session.get("user"); ew = _ext_where(email) rtype = request.args.get("type","brand"); td, ms, _, _ = _report_dates(); a = _amt() type_map = {"state":("STATENAME","STATENAME AS DIMENSION"),"company":("COMPANY_CODE","COMPANY_CODE AS DIMENSION"),"brand":("BRANDCODE","BRANDCODE AS DIMENSION")} gc, lc = type_map.get(rtype, type_map["brand"]) return _rep(f"SELECT TOP 50 {lc}, ROUND(SUM({a})/10000000,2) AS REVENUE_CR, COUNT(*) AS LINES FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{ms}' AND '{td}'{ew} GROUP BY {gc} ORDER BY SUM({a}) DESC", f"Z-Sales by {rtype.title()}") @sap_bp.route("/report/opportunity-analysis", methods=["GET"]) @login_required def report_opportunity_analysis(): email = session.get("user"); ew = _ext_where(email) td = datetime.now(tz=_IST).strftime("%Y%m%d"); a = _amt() return _rep(f"SELECT TOP 30 PARTNERCODE, PARTNERNAME, BRANDCODE, ROUND(SUM({a})/10000000,2) AS YTD_REVENUE_CR, COUNT(*) AS LINES FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_fy_start()}' AND '{td}'{ew} GROUP BY PARTNERCODE, PARTNERNAME, BRANDCODE ORDER BY SUM({a}) DESC", "Opportunity Analysis - YTD") @sap_bp.route("/report/opportunity-vs-quotation", methods=["GET"]) @login_required def report_opportunity_vs_quotation(): email = session.get("user"); ew = _ext_where(email) td, ms, ps, pe = _report_dates(); a = _amt() return _rep(f"SELECT TOP 50 PARTNERCODE, PARTNERNAME, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ms}' AND '{td}' THEN {a} ELSE 0 END)/10000000,2) AS THIS_MONTH_CR, ROUND(SUM(CASE WHEN BILLINGDATE BETWEEN '{ps}' AND '{pe}' THEN {a} ELSE 0 END)/10000000,2) AS LAST_MONTH_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{ps}' AND '{td}'{ew} GROUP BY PARTNERCODE, PARTNERNAME ORDER BY THIS_MONTH_CR DESC", "MTD vs Prior Month") @sap_bp.route("/sales/cells", methods=["GET"]) def sales_cells(): try: from cells import fetch_cells_data return jsonify(fetch_cells_data(email=session.get("user",""), external_where="")) except Exception as e: logger.warning("[cells] HANA unavailable: %s", e) return jsonify({"Today's Sales":None,"Yesterday's Sales":None,"YTD":None,"QTD":None}), 200