first commit

.gitignore

@@ -0,0 +1,30 @@
+# Virtual environment
+venv/
+.venv/
+env/
+
+# Environment variables and secrets
+.env
+.env.*
+!.env.example
+
+# Python byte-compiled / optimized / DLL files and caches
+__pycache__/
+*.pyc
+*.pyo
+*.pyd
+.pytest_cache/
+.mypy_cache/
+
+# Office temporary files (created automatically when opening Excel files)
+~$*.xlsx
+~$*.xls
+
+# --- Data files ---
+# If your Excel sheet contains dynamic local data that you do not want in git, 
+# uncomment the line below. Otherwise, keep it commented to track the spreadsheet.
+# work_times.xlsx
+
+# OS-specific files
+.DS_Store
+Thumbs.db

README.md

@@ -0,0 +1,70 @@
+# TimeRecording Sync & Balance Tracker
+
+Automates the extraction, processing, and formatting of work hours from the IDENTsmart TimeRecording API into a local Excel spreadsheet (`work_times.xlsx`). Designed for Verbundstudium (dual-study) contracts.
+
+## 1. Architecture & Execution Flow
+1. **Authentication:** Spawns headless Playwright (Chromium) to execute Auth0 PKCE login and intercepts the `Bearer` token from network headers. Closes the browser immediately to prevent memory leaks.
+2. **Data Fetching:** Calls API endpoints via `requests` to retrieve JSON payloads for raw physical clock events and official absences.
+3. **Processing:** Maps clock events to academic phases, applying strict contractual targets and dynamic break deductions.
+4. **Excel Generation:** Compiles data natively into `work_times.xlsx` via `openpyxl`, writing cross-platform Excel formulas for local calculation.
+
+## 2. Installation & Configuration
+
+**Prerequisites:** Python 3.9+
+
+```bash
+# 1. Initialize environment & dependencies
+python -m venv venv
+source venv/bin/activate  # Windows: venv\Scripts\activate
+pip install openpyxl requests playwright python-dotenv
+
+# 2. Install Playwright browser binary
+playwright install chromium
+```
+
+**Environment Variables (`.env`)**
+Create a `.env` file in the project root. All contractual parameters and credentials must reside here.
+
+```ini
+TR_EMAIL=your_email@domain.com
+TR_PASSWORD=your_secure_password
+TR_START_DATE=2024-10-01
+SEMESTER_TARGET_TOTAL=240.0
+SEMESTER_WEEKS=39.0
+```
+
+**Execution:** `python sync_hours.py` (overwrites `work_times.xlsx` on run).
+
+## 3. Domain Logic & Contract Rules
+
+The script strictly maps all days into two phases. Academic year resets annually on October 1st.
+
+| Phase | Dates | Daily Target | Absence Handling |
+| :--- | :--- | :--- | :--- |
+| **Fulltime** *(Vorlesungsfreie Zeit)* | Feb 15–Mar 14, Aug 1–Sep 30 | `08:00:00` | Sickness/Vacation overrides target to `00:00:00` (neutralizes deficit). |
+| **Semester** *(Vorlesungszeit)* | All other dates | `00:00:00` | Ignored (target is already zero). |
+
+**Special Overrides:**
+* **Berufsschule:** If logged during the **Semester** phase, script hardcodes clock-in to `08:00:00` and clock-out to `16:00:00` (8h net) to credit the rolling semester balance.
+
+## 4. Excel Mathematical Model
+
+Calculations are pushed as raw formulas so the resulting spreadsheet is fully dynamic. 
+
+* **Gross Time:** `(Out1 - In1) + (Out2 - In2)`. Supports maximum 2 work blocks per day.
+* **Pause (ArbZG Compliance):** Enforces German labor law (30m for $\ge$6h, 45m for $\ge$9h). To prevent double penalization, the script subtracts any *actual unlogged time* (gap between `Out1` and `In2`) from the statutory deduction.
+* **Negative Time Workaround:** Standard Excel cannot display negative times without enabling the legacy 1904 Date System, which breaks cross-platform compatibility. The script uses a `TEXT()` formula evaluation to prefix a `"-"` string if `Worked < Target` (e.g., `"-0:35:00"`).
+* **Overview Formatting:** Excel evaluates time internally as fractions of 1 day. `SEMESTER_TARGET_TOTAL` is divided by `24.0` during Python injection so Excel natively renders it as `240:00:00`.
+
+## 5. Maintenance & Troubleshooting
+
+| Component | Location / File | Action |
+| :--- | :--- | :--- |
+| **Contract Hours / Pace** | `.env` | Update `SEMESTER_TARGET_TOTAL` or `SEMESTER_WEEKS`. |
+| **Academic Break Dates** | `Config.__init__` | Update `self.winter_break` and `self.summer_break` tuples. |
+| **Query Range Start** | `.env` | Update `TR_START_DATE`. *Note: Older dates significantly increase API latency.* |
+| **Excel Column Layout** | `ExcelWriter` | Added columns require manual shifting of formula column references (e.g., `G`, `H`). |
+
+**Known Issues & Fixes:**
+* **Auth Timeouts / Failure:** TimeRecording uses Auth0. If their DOM layout changes, Playwright `.fill()` / `.click()` selectors will time out. Update CSS selectors in `TimeRecordingAPI.authenticate()`.
+* **Zombie Chromium Processes:** If the script is force-killed (`SIGKILL`) before the `try...finally` block closes the browser, invisible Chromium instances may persist. Kill manually via `pkill Chrome` (Unix) or Task Manager (Windows).

sync_hours.py

@@ -0,0 +1,312 @@
+import os
+import requests
+import zoneinfo
+from dataclasses import dataclass
+from datetime import datetime, date, timedelta
+from dotenv import load_dotenv
+from openpyxl import Workbook
+from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
+from playwright.sync_api import sync_playwright
+
+@dataclass
+class WorkDay:
+    date_obj: date
+    phase: str
+    in1: str = ""
+    out1: str = ""
+    in2: str = ""
+    out2: str = ""
+    note: str = ""
+    is_absence: bool = False
+    
+    @property
+    def target_time_formula(self) -> str:
+        # Zero target hours during semester or on pre-approved absence days.
+        if self.phase == "Semester":
+            return '=TIME(0,0,0)'
+        return '=TIME(0,0,0)' if self.is_absence else '=TIME(8,0,0)'
+
+class Config:
+    def __init__(self):
+        load_dotenv()
+        
+        self.email = os.environ.get("TR_EMAIL")
+        self.password = os.environ.get("TR_PASSWORD")
+        if not self.email or not self.password:
+            raise ValueError("Missing credentials in environment.")
+
+        # Contractual constants
+        self.semester_target_total = float(os.environ.get("SEMESTER_TARGET_TOTAL", "240.0"))
+        self.semester_weeks = float(os.environ.get("SEMESTER_WEEKS", "39.0"))
+
+        self.start_date = datetime.strptime(os.environ.get("TR_START_DATE", "2024-10-01"), "%Y-%m-%d").date()
+        self.local_tz = zoneinfo.ZoneInfo("Europe/Berlin")
+        
+        today = datetime.now().date()
+        self.end_date_str = today.strftime("%Y-%m-%d")
+        
+        # Academic year logic (resets Oct 1st annually)
+        if today.month >= 10:
+            self.acad_year_start = date(today.year, 10, 1)
+            self.current_term_end = date(today.year + 1, 7, 31)
+        else:
+            self.acad_year_start = date(today.year - 1, 10, 1)
+            self.current_term_end = date(today.year, 7, 31)
+            
+        days_left = (self.current_term_end - today).days
+        self.remaining_weeks = max(0.0, round(days_left / 7.0, 1))
+
+        # Semester breaks (Vorlesungsfreie Zeit)
+        self.winter_break = ((2, 15), (3, 14))
+        self.summer_break = ((8, 1), (9, 30))
+
+class TimeRecordingAPI:
+    """Handles Auth0 PKCE session hijacking and HTTP interactions with Time Recording API."""
+    
+    def __init__(self, config: Config):
+        self.config = config
+        self.token = None
+        self.employee_id = None
+        self.base_url = "https://api.timerecording.com"
+
+    def authenticate(self):
+        with sync_playwright() as p:
+            browser = p.chromium.launch(headless=True)
+            try:
+                context = browser.new_context()
+                page = context.new_page()
+
+                # Event listener to extract OAuth Bearer token from network headers
+                def capture_token(request):
+                    if self.base_url in request.url:
+                        auth = request.headers.get("authorization")
+                        if auth and auth.startswith("Bearer "):
+                            self.token = auth
+
+                page.on("request", capture_token)
+                page.goto("https://app.timerecording.com/de/main/dashboard")
+                page.fill('input[name="username"], input[type="email"]', self.config.email)
+                page.fill('input[name="password"], input[type="password"]', self.config.password)
+                page.click('button[type="submit"], button[name="action"]')
+
+                # Block until login request is executed
+                try:
+                    with page.expect_request(lambda r: f"{self.base_url}/employees/me" in r.url, timeout=10000):
+                        pass
+                except Exception:
+                    pass
+
+                if not self.token:
+                    raise RuntimeError("Authentication failed: Could not capture Bearer token.")
+
+                resp = context.request.get(f"{self.base_url}/employees/me", headers={"Authorization": self.token})
+                if not resp.ok:
+                    raise RuntimeError(f"Failed to fetch employee ID. HTTP {resp.status}")
+                
+                self.employee_id = resp.json().get("id")
+            finally:
+                browser.close()
+
+    def fetch_data(self) -> tuple[dict, list]:
+        headers = {"authorization": self.token, "referer": "https://app.timerecording.com/"}
+        
+        r_url = f"{self.base_url}/employees/{self.employee_id}/record-buckets/{self.config.start_date}/{self.config.end_date_str}"
+        a_url = f"{self.base_url}/employees/{self.employee_id}/absences/{self.config.start_date}/{self.config.end_date_str}"
+        
+        r_resp = requests.get(r_url, headers=headers)
+        a_resp = requests.get(a_url, headers=headers)
+        
+        r_resp.raise_for_status()
+        a_resp.raise_for_status()
+        
+        return r_resp.json(), a_resp.json()
+
+class TimesheetProcessor:
+    """Merges physical clock events and official absences into chronologically mapped workdays."""
+
+    def __init__(self, config: Config):
+        self.config = config
+
+    def get_phase(self, d: date) -> str:
+        w_start, w_end = date(d.year, *self.config.winter_break[0]), date(d.year, *self.config.winter_break[1])
+        s_start, s_end = date(d.year, *self.config.summer_break[0]), date(d.year, *self.config.summer_break[1])
+        
+        if (w_start <= d <= w_end) or (s_start <= d <= s_end):
+            return "Fulltime"
+        return "Semester"
+
+    def process(self, records_data: dict, absences_data: list) -> list[WorkDay]:
+        days_map = {}
+        
+        # 1. Parse official absences (sick leave, public holidays, vocational school)
+        for item in absences_data:
+            name = item.get("absenceType", {}).get("name", item.get("name", "Absence"))
+            start_d = datetime.strptime(item["start"], "%Y-%m-%d").date()
+            end_d = datetime.strptime(item["end"], "%Y-%m-%d").date()
+            
+            curr = start_d
+            while curr <= end_d:
+                if curr >= self.config.start_date:
+                    phase = self.get_phase(curr)
+                    d_str = curr.strftime("%Y-%m-%d")
+                    
+                    # Hardcode vocational school times (Berufsschule) to match local regional guidelines
+                    if name == "Berufsschule" and phase == "Semester":
+                        days_map[d_str] = WorkDay(curr, phase, in1="08:00:00", out1="16:00:00", note="Berufsschule")
+                    elif phase == "Fulltime" and name != "Berufsschule":
+                        days_map[d_str] = WorkDay(curr, phase, note=name, is_absence=True)
+                curr += timedelta(days=1)
+
+        # 2. Parse physical clock-in/out stamps
+        for d_str, records in records_data.items():
+            curr_date = datetime.strptime(d_str, "%Y-%m-%d").date()
+            if curr_date < self.config.start_date:
+                continue
+                
+            if d_str not in days_map:
+                days_map[d_str] = WorkDay(curr_date, self.get_phase(curr_date))
+                
+            # Skip merging logged times if day is overwritten by vocational school
+            if days_map[d_str].note == "Berufsschule":
+                continue 
+                
+            records.sort(key=lambda x: x['time'])
+            ins = [r for r in records if r['type'] == 'work_start']
+            outs = [r for r in records if r['type'] == 'work_stop']
+            
+            def format_time(record):
+                dt = datetime.fromisoformat(record['time'].replace('Z', '+00:00')).astimezone(self.config.local_tz)
+                return dt.strftime('%H:%M:%S')
+
+            # Map up to 2 shifts per calendar day
+            if len(ins) > 0: days_map[d_str].in1 = format_time(ins[0])
+            if len(outs) > 0: days_map[d_str].out1 = format_time(outs[0])
+            if len(ins) > 1: days_map[d_str].in2 = format_time(ins[1])
+            if len(outs) > 1: days_map[d_str].out2 = format_time(outs[1])
+
+        return [days_map[k] for k in sorted(days_map.keys())]
+
+class ExcelWriter:
+    """Generates styled worksheet with dynamic formulas for balances and German break laws."""
+
+    def __init__(self, config: Config):
+        self.config = config
+        self.wb = Workbook()
+
+    def save(self, workdays: list[WorkDay], filename="work_times.xlsx"):
+        ws = self.wb.active
+        ws.title = "Work Hours"
+        
+        headers = ["Date", "Phase", "In1", "Out1", "In2", "Out2", "Gross Time", 
+                   "Pause", "Net Time", "Target Time", "Semester Balance", 
+                   "Fulltime Balance", "Notes"]
+        ws.append(headers)
+
+        thin_border = Border(left=Side(style='thin', color='D9D9D9'), right=Side(style='thin', color='D9D9D9'),
+                             top=Side(style='thin', color='D9D9D9'), bottom=Side(style='thin', color='D9D9D9'))
+        
+        styles = {
+            "Semester": {"fill": PatternFill("solid", fgColor="E2EFDA"), "font": Font(name="Calibri", size=11, color="375623")},
+            "Fulltime": {"fill": PatternFill("solid", fgColor="FFF2CC"), "font": Font(name="Calibri", size=11, color="7F6000")}
+        }
+
+        for i, wd in enumerate(workdays, start=2):
+            gross_f = f"=(IF(D{i}>C{i},D{i}-C{i},0))+(IF(F{i}>E{i},F{i}-E{i},0))"
+            
+            # German statutory breaks (ArbZG): 30m after 6h total, 45m after 9h. Deduct manual gap if exists.
+            pause_f = (f'=MAX(0, IF(M{i}="Berufsschule", TIME(0,0,0), IF(G{i}>=TIME(9,0,0), TIME(0,45,0), '
+                       f'IF(G{i}>=TIME(6,0,0), TIME(0,30,0), TIME(0,0,0)))) - IF(AND(E{i}>0, D{i}>0), E{i}-D{i}, TIME(0,0,0)))')
+            
+            net_f = f"=IF(G{i}>0, G{i}-H{i}, 0)"
+            sem_bal_f = f'=SUMIF($B$2:B{i}, "Semester", $I$2:I{i})'
+            
+            # Workaround for Excel's 1900 date system which errors out on negative times
+            val_expr = f'ROUND(SUMIF($B$2:B{i}, "Fulltime", $I$2:I{i}) - SUMIF($B$2:B{i}, "Fulltime", $J$2:J{i}), 10)'
+            full_bal_f = f'=IF({val_expr}<0, "-" & TEXT(ABS({val_expr}), "[h]:mm:ss"), TEXT({val_expr}, "[h]:mm:ss"))'
+
+            row_data = [wd.date_obj, wd.phase, wd.in1 or None, wd.out1 or None, wd.in2 or None, wd.out2 or None,
+                        gross_f, pause_f, net_f, wd.target_time_formula, sem_bal_f, full_bal_f, wd.note or None]
+            
+            for col_idx, value in enumerate(row_data, start=1):
+                c = ws.cell(row=i, column=col_idx, value=value)
+                c.border = thin_border
+                c.alignment = Alignment(horizontal='left' if col_idx == 13 else 'center', vertical='center')
+
+            ws.cell(row=i, column=1).number_format = 'yyyy-mm-dd'
+            for col in range(3, 12):
+                ws.cell(row=i, column=col).number_format = '[h]:mm:ss'
+            ws.cell(row=i, column=12).number_format = '@'
+
+            p_cell = ws.cell(row=i, column=2)
+            p_cell.fill, p_cell.font = styles[wd.phase]["fill"], styles[wd.phase]["font"]
+
+        # Style headers
+        for col_idx in range(1, 14):
+            c = ws.cell(row=1, column=col_idx)
+            c.fill = PatternFill("solid", fgColor="1F497D")
+            c.font = Font(name="Calibri", size=11, color="FFFFFF", bold=True)
+            c.alignment, c.border = Alignment(horizontal='center', vertical='center'), thin_border
+
+        col_widths = {"A": 14, "B": 14, "C": 11, "D": 11, "E": 11, "F": 11, "G": 14, "H": 11, "I": 12, "J": 14, "K": 18, "L": 18, "M": 20}
+        for col_letter, width in col_widths.items():
+            ws.column_dimensions[col_letter].width = width
+
+        # Overview Sheet Calculations
+        ov = self.wb.create_sheet(title="Overview")
+        last_r = max(2, len(workdays) + 1)
+        
+        ov.merge_cells("A1:B1")
+        ov["A1"] = "Semester Target & Pace"
+        ov["A1"].fill = PatternFill("solid", fgColor="1F497D")
+        ov["A1"].font = Font(name="Calibri", size=11, color="FFFFFF", bold=True)
+        ov["A1"].alignment = Alignment(horizontal='center')
+        
+        wks = self.config.semester_weeks
+        ay_start = self.config.acad_year_start
+        
+        metrics = [
+            ("Target Hours (Total)", self.config.semester_target_total / 24.0, '[h]:mm:ss'),
+            ("Worked Hours", f'=SUMIFS(\'Work Hours\'!I2:I{last_r}, \'Work Hours\'!B2:B{last_r}, "Semester", \'Work Hours\'!A2:A{last_r}, ">="&DATE({ay_start.year},{ay_start.month},{ay_start.day}))', '[h]:mm:ss'),
+            ("Remaining Hours", "=B2-B3", '[h]:mm:ss'),
+            ("Remaining Weeks", self.config.remaining_weeks, '0.0'),
+            ("Required Pace / Wk", "=IF(B5>0, B4/B5, 0)", '[h]:mm:ss'),
+            ("Current Deficit", f'=IF(B5>0, ((({wks}-B5)*(B2/{wks})) - B3), 0)', '[h]:mm:ss')
+        ]
+
+        for idx, (label, val, fmt) in enumerate(metrics, start=2):
+            ov[f"A{idx}"] = label
+            ov[f"B{idx}"] = val
+            ov[f"A{idx}"].font = Font(name="Calibri", size=10, bold=True)
+            ov[f"B{idx}"].font = Font(name="Calibri", size=10)
+            ov[f"B{idx}"].number_format = fmt
+            
+            for c in "AB":
+                cell = ov[f"{c}{idx}"]
+                cell.border = thin_border
+                cell.alignment = Alignment(horizontal='left' if c == "A" else 'center')
+
+        ov.column_dimensions["A"].width, ov.column_dimensions["B"].width = 24, 14
+        
+        self.wb.save(filename)
+        print(f"Data saved to {filename}")
+
+def main():
+    try:
+        config = Config()
+        
+        api = TimeRecordingAPI(config)
+        api.authenticate()
+        records, absences = api.fetch_data()
+        
+        processor = TimesheetProcessor(config)
+        workdays = processor.process(records, absences)
+        
+        writer = ExcelWriter(config)
+        writer.save(workdays)
+        
+    except Exception as e:
+        print(f"Process failed: {e}")
+        exit(1)
+
+if __name__ == "__main__":
+    main()