import csv, io, logging from datetime import datetime, timezone, timedelta from urllib.request import urlopen log = logging.getLogger("GEBot") BASE_PUB = "https://docs.google.com/spreadsheets/d/e/2PACX-1vTKwg06Ld5j5gT66m7iVU7pHm3KTM0HShnORH_pIfrbaBecXZe_LiXSOcoVHcToPMqxlC4FLtHlgBma/pub?gid={gid}&single=true&output=csv" MONTHLY_SHEETS = { "2026-02": 408974653, "2026-01": 518246342, "2025-12": 357479, "2025-11": 1905113797, "2025-10": 971455792, "2025-09": 959867871, } DAILY_GID = 520165065 def parse_money(val): if not val: return 0 val = val.replace("$", "").replace(",", "").replace('"', '').strip() if not val or val in ("#DIV/0!", "#REF!", "#N/A"): return 0 try: return round(float(val), 2) except: return 0 def parse_int(val): if not val: return 0 val = val.replace(",", "").replace('"', '').strip() try: return int(float(val)) except: return 0 def parse_pct(val): if not val: return "0%" val = val.replace('"', '').strip() if "#DIV" in val or "#REF" in val: return "N/A" return val def fetch_csv(gid): try: resp = urlopen(BASE_PUB.format(gid=gid), timeout=15) return list(csv.reader(io.StringIO(resp.read().decode("utf-8")))) except Exception as e: log.error(f"Sheet fetch error gid={gid}: {e}") return None def parse_monthly_sheet(rows): """Parse monthly D/W sheet. Structure varies by month: Feb 2026: Row1=Deposit Gross, Row7=Deposit Net Jan 2026: Row1=Deposit Net(actually gross), Row7=Deposit Net(real) Dec/Nov 2025: Row1=Total Gross Deposit, Row3=Deposit Net Strategy: find ALL rows with key fields, use LAST Deposit Net as real net. Row 1 col B is always Gross (regardless of label). """ if not rows: return None data = {} # Row 1 is ALWAYS gross (even if labeled "Deposit Net" like Jan 2026) if len(rows) > 0 and len(rows[0]) > 1: data["deposit_gross"] = parse_money(rows[0][1]) # Scan all rows for Deposit Net - take the LAST one found as real net last_dep_net = None for i, r in enumerate(rows): if len(r) < 2: continue key = r[0].strip() val = r[1].strip() if key == "Deposit Net": last_dep_net = parse_money(val) # If no Deposit Net found, net = gross - refunded if last_dep_net is not None: data["deposit_net"] = last_dep_net else: data["deposit_net"] = data.get("deposit_gross", 0) # Parse remaining fields for r in rows: if len(r) < 2: continue key = r[0].strip() val = r[1].strip() if key == "US gross": data["us_gross"] = parse_money(val) elif key == "UK gross": data["uk_gross"] = parse_money(val) elif key == "Amount Refunded": data["refunded"] = parse_money(val) elif key == "US Net" and "us_net" not in data: data["us_net"] = parse_money(val) elif key == "UK Net": data["uk_net"] = parse_money(val) elif key == "US money out": data["us_money_out"] = parse_money(val) elif key == "UK money out": data["uk_money_out"] = parse_money(val) elif key == "RET Deposit Count": data["ret_deposits"] = parse_int(val) elif key == "CONV Deposit Count": data["conv_deposits"] = parse_int(val) elif key == "FTDs received": data["ftds"] = parse_int(val) elif key == "US leads": data["us_leads"] = parse_int(val) elif key == "UK leads": data["uk_leads"] = parse_int(val) elif key == "PLV" and "plv" not in data: data["plv"] = parse_money(val) elif key == "Runrate": data["runrate"] = parse_money(val) elif key == "TARGET": data["target"] = parse_money(val) elif key == "Upsell Count": data["upsell_count"] = parse_int(val) elif key == "Upsell From CONV": data["upsell_pct"] = parse_pct(val) elif key == "Conv rate %": data["conv_rate"] = parse_pct(val) elif key in ("% REFUND NET", "% WD/refunds NET", "% WD NET"): data["refund_pct"] = parse_pct(val) # Daily totals from column D # Find "Total daily" or similar in column C/D for i, r in enumerate(rows): if len(r) < 4: continue c_val = r[2].strip() if r[2] else "" d_label = r[0].strip() if r[0] else "" # Row with "Total daily" label in col C or D if "Total daily" in (r[3] if len(r) > 3 else ""): pass # label row if "Daily Net" in (r[3] if len(r) > 3 else "") or "Daily Gross" in (r[3] if len(r) > 3 else ""): pass # Safer: parse daily from known structure # In Feb 2026: row index 3 col D = Total daily amount # In Jan 2026: same structure for i, r in enumerate(rows[:15]): if len(r) < 4: continue d_val = r[3].strip() if r[3] else "" if i >= 2 and i <= 4: if "Daily Net" not in d_val and "Daily Gross" not in d_val: if d_val.startswith("$") or d_val.startswith("-$"): if "daily_total" not in data: data["daily_total"] = parse_money(d_val) if "US Daily Net" in d_val: # Next row has the value if i+1 < len(rows) and len(rows[i+1]) > 3: data["us_daily_net"] = parse_money(rows[i+1][3]) elif "UK Daily Net" in d_val: if i+1 < len(rows) and len(rows[i+1]) > 3: data["uk_daily_net"] = parse_money(rows[i+1][3]) # Fallback: try column D rows 3-8 for daily values if "daily_total" not in data: for i in range(2, 8): if i < len(rows) and len(rows[i]) > 3: val = rows[i][3].strip() if val and (val.startswith("$") or val.startswith("-$")) and parse_money(val) != 0: data["daily_total"] = parse_money(val) break if "us_daily_net" not in data: data["us_daily_net"] = 0 if "uk_daily_net" not in data: data["uk_daily_net"] = 0 if "daily_total" not in data: data["daily_total"] = 0 # Agent Retention (columns F=5, G=6, H=7, I=8, J=9, K=10) agents = [] for r in rows[1:20]: if len(r) < 11: continue us_name = r[6].strip() if r[6] else "" if not us_name or "Agent" in us_name or "Totals" in us_name: continue agents.append({ "us": us_name, "count": parse_int(r[7]), "deposit": parse_money(r[8]), "refund": parse_money(r[9]), "net": parse_money(r[10]) }) data["agents"] = sorted(agents, key=lambda x: x["net"], reverse=True) data["date"] = rows[0][3].strip() if len(rows[0]) > 3 else "" return data def fetch_sheet_data(period="month"): eet = timezone(timedelta(hours=2)) now = datetime.now(eet) key = now.strftime("%Y-%m") gid = MONTHLY_SHEETS.get(key, 408974653) rows = fetch_csv(gid) return parse_monthly_sheet(rows)