70 lines
4.1 KiB
Markdown
70 lines
4.1 KiB
Markdown
# 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).
|