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

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.
Data Fetching: Calls API endpoints via requests to retrieve JSON payloads for raw physical clock events and official absences.
Processing: Maps clock events to academic phases, applying strict contractual targets and dynamic break deductions.
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+

# 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.

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).

4.1 KiB Raw Permalink Blame History Unescape Escape