125 lines
3.6 KiB
Python
125 lines
3.6 KiB
Python
"""Shared attendance/fee logic for FUJ Tuesday practices."""
|
|
|
|
import csv
|
|
import io
|
|
import urllib.request
|
|
from datetime import datetime
|
|
|
|
SHEET_ID = "1E2e_gT_K5AwSRCDLDTa2UetZTkHmBOcz0kFbBUNUNBA"
|
|
EXPORT_URL = f"https://docs.google.com/spreadsheets/d/{SHEET_ID}/export?format=csv"
|
|
|
|
FEE_FULL = 750 # CZK, for 2+ practices in a month
|
|
FEE_SINGLE = 200 # CZK, for exactly 1 practice in a month
|
|
|
|
COL_NAME = 0
|
|
COL_TIER = 1
|
|
FIRST_DATE_COL = 3
|
|
|
|
|
|
def fetch_csv() -> list[list[str]]:
|
|
"""Fetch the attendance Google Sheet as parsed CSV rows."""
|
|
req = urllib.request.Request(EXPORT_URL)
|
|
with urllib.request.urlopen(req) as resp:
|
|
text = resp.read().decode("utf-8")
|
|
reader = csv.reader(io.StringIO(text))
|
|
return list(reader)
|
|
|
|
|
|
def parse_dates(header_row: list[str]) -> list[tuple[int, datetime]]:
|
|
"""Return (column_index, date) pairs for all date columns."""
|
|
dates = []
|
|
for i in range(FIRST_DATE_COL, len(header_row)):
|
|
raw = header_row[i].strip()
|
|
if not raw:
|
|
continue
|
|
try:
|
|
dates.append((i, datetime.strptime(raw, "%m/%d/%Y")))
|
|
except ValueError:
|
|
continue
|
|
return dates
|
|
|
|
|
|
def group_by_month(dates: list[tuple[int, datetime]]) -> dict[str, list[int]]:
|
|
"""Group column indices by YYYY-MM."""
|
|
months: dict[str, list[int]] = {}
|
|
for col, dt in dates:
|
|
key = dt.strftime("%Y-%m")
|
|
months.setdefault(key, []).append(col)
|
|
return months
|
|
|
|
|
|
def calculate_fee(attendance_count: int) -> int:
|
|
"""Apply fee rules: 0 → 0, 1 → 200, 2+ → 750."""
|
|
if attendance_count == 0:
|
|
return 0
|
|
if attendance_count == 1:
|
|
return FEE_SINGLE
|
|
return FEE_FULL
|
|
|
|
|
|
def get_members(rows: list[list[str]]) -> list[tuple[str, str, list[str]]]:
|
|
"""Parse member rows. Returns list of (name, tier, row).
|
|
|
|
Stopped at row where first column contains '# last line'.
|
|
Skips rows starting with '#'.
|
|
"""
|
|
members = []
|
|
for row in rows[1:]:
|
|
if not row or len(row) <= COL_NAME:
|
|
continue
|
|
|
|
first_col = row[COL_NAME].strip()
|
|
|
|
# Terminator for rows to process
|
|
if "# last line" in first_col.lower():
|
|
break
|
|
|
|
# Ignore comments
|
|
if first_col.startswith("#"):
|
|
continue
|
|
|
|
if not first_col or first_col.lower() in ("jméno", "name", "jmeno"):
|
|
continue
|
|
|
|
tier = row[COL_TIER].strip().upper() if len(row) > COL_TIER else ""
|
|
members.append((first_col, tier, row))
|
|
return members
|
|
|
|
|
|
def get_members_with_fees() -> tuple[list[tuple[str, str, dict[str, int]]], list[str]]:
|
|
"""Fetch attendance data and compute fees.
|
|
|
|
Returns:
|
|
(members, sorted_months) where members is a list of
|
|
(name, tier, {month_key: (fee, count)}) for ALL members (all tiers).
|
|
sorted_months is the list of YYYY-MM keys in order.
|
|
"""
|
|
rows = fetch_csv()
|
|
if len(rows) < 2:
|
|
return [], []
|
|
|
|
header_row = rows[0]
|
|
dates = parse_dates(header_row)
|
|
if not dates:
|
|
return [], []
|
|
|
|
months = group_by_month(dates)
|
|
sorted_months = sorted(months.keys())
|
|
members_raw = get_members(rows)
|
|
|
|
members = []
|
|
for name, tier, row in members_raw:
|
|
month_fees = {}
|
|
for month_key in sorted_months:
|
|
cols = months[month_key]
|
|
count = sum(
|
|
1
|
|
for c in cols
|
|
if c < len(row) and row[c].strip().upper() == "TRUE"
|
|
)
|
|
fee = calculate_fee(count) if tier == "A" else 0
|
|
month_fees[month_key] = (fee, count)
|
|
members.append((name, tier, month_fees))
|
|
|
|
return members, sorted_months
|