Content
# Report Generator Rewrite — Hourly Data (2026-04-02)
## Problem
The VTScada daily snapshot report had a month-boundary bug. The report delivered on 4/2 showed a single row for 4/1 with **March's accumulated totals** instead of fresh April data. Root cause: VTScada's totalizer resets between 00:00-01:00 on the 1st, but the daily snapshot captures values at exactly 00:00 — creating a race condition where the midnight row still has the previous month's values.
## Root Cause Analysis
### VTScada Totalizer Reset Timing
- Monthly reset is **hardcoded to midnight** on the 1st (per VTScada docs — non-configurable)
- Actual reset completes between **00:00 and 01:00** (observed in hourly data)
- The 00:00 row on the 1st carries the **previous month's final accumulated values**
- Production between 23:00 and midnight on the last day is captured in this pre-reset row
### Daily Snapshot Limitations
- Daily snapshot rows are all timestamped at 00:00 (midnight snapshots from VTScada historian)
- The Report Tag's Trigger and Period tabs control *when* the report runs and *what date range*, but NOT the snapshot sample time
- The snapshot time is determined by the Totalizer's historian log interval, which is hardcoded to midnight
- **Neither the Trigger tab nor the Period tab can fix this**
### VTScada Report Tag Config (before change)
- **Trigger:** Daily at 11:55 PM
- **Period:** Preset = "Current month", Period Type = "Previous Time Period"
- **Report Offset:** 0 months (offset is in months, not hours — no fine-grained control)
## Solution
Switched from the **daily snapshot** XLS to the **hourly snapshot** XLS as the data source. The hourly data has 24 readings per day, allowing the report generator to:
1. Use the **23:00 reading** as end-of-day value (captures ~96% of daily production)
2. **Detect the monthly reset** by finding the value drop between 00:00 and 01:00 on the 1st
3. Use the **pre-reset 00:00 row** as the authoritative previous month total (captures 100% of production)
4. Use only **post-reset readings** (01:00+) for the new month — no stale data contamination
### Trade-off
- Daily breakdown values miss ~1 hour per day (23:00 to midnight), roughly 3-5 kgal/day (~5%)
- **Monthly totals are exact** — the pre-reset row captures every gallon
- This is acceptable for an operational daily report
## Code Changes
### report_generator.py — Complete rewrite of parser
- **New function:** `parse_hourly_xls(filepath, wells, report_month=None)`
- Groups hourly readings by date
- Gets last reading per day as end-of-day accumulated value
- `_detect_reset()` finds monthly resets (>50% value drop between consecutive hours)
- On 1st of month: skips pre-reset readings, uses only post-reset data
- `report_type` field: `"end_of_month"` (authoritative totals from pre-reset row) or `"month_to_date"` (running report from latest readings)
- Handles data gaps: negative daily increments shown as dashes, next day absorbs the span
- **Auto-detect logic:** Always reports on the month of the latest data date
- Mar 31 trigger → March MTD (31 days, full month)
- Apr 1 trigger → April MTD (1 day, post-reset)
- **PDF generation unchanged** — same branded layout (summary cards, well table, daily table)
### service.py — Smart dual-report generation
- On the **1st of the month:** generates TWO reports:
1. End-of-month for previous month (authoritative totals from pre-reset row)
2. Month-to-date for the new month (just day 1, fresh data)
- On **every other day:** generates a single MTD report
- Both queued for 6:00 AM email delivery
### email_sender.py — Dynamic email subjects
- End-of-month: "Rancho Ynecita Water Production Monthly Report - March 2026"
- Daily: "Rancho Ynecita Water Production Daily Report - Apr 01, 2026"
## VTScada Configuration Required
1. **Create/enable** an hourly snapshot report:
- Trigger: Daily at 23:55 PM
- Period: Current month
- Output: `C:\Reports` (same watch folder)
2. **Disable** the old daily snapshot report (or leave it — service processes any XLS)
3. The hourly XLS contains one row per hour for the entire current month
## Data Flow
```
VTScada (23:55 PM)
→ Generates hourly XLS to C:\Reports
→ Service detects new file (watchdog)
→ parse_hourly_xls() processes data
→ Detects reset if 1st of month → generates end-of-month + MTD
→ Otherwise → generates MTD only
→ PDF queued → emailed at 6:00 AM
```
## Well Configuration
| Well | Total Column | Resets Monthly | Notes |
|----------|---------------|----------------|--------------------------|
| Bonita | Monthly Total | Yes | Heaviest runner (~1126 kgal/mo) |
| Caballo | Grand Total | Yes | Light use (~454 kgal/mo), mostly 22:00-23:00 |
| Estes | Grand Total | Yes | Heavy runner (~2731 kgal/mo) |
All three reset despite "Grand Total" naming — the column names are misleading.
## Verified Results (from test data: Feb 26 - Apr 1, 2026)
### March End-of-Month Report
- **Bonita:** 1,126.6 kgal, 143.2 hrs, 642 starts
- **Caballo:** 454.1 kgal, 40.7 hrs
- **Estes:** 2,731.1 kgal, 400.5 hrs
- **Total:** 4,311.8 kgal
- Totals sourced from pre-reset Apr 1 00:00 row (authoritative)
### April 1 Daily Report
- **Bonita:** 69.3 kgal, 9.0 hrs
- **Caballo:** 12.7 kgal, 1.2 hrs
- **Estes:** 71.9 kgal, 9.4 hrs
- Clean post-reset data, no March contamination
## Deployment
- **Source:** ~/Documents/claude/vtscada-reporter/
- **Target:** user@100.108.21.38 (Tailscale), C:\VTScadaReporter\
- **Deploy:** `./deploy.sh` (SCP + NSSM restart)
- **Deployed:** 2026-04-02 11:02 AM
- **Service status:** Running
## Files on Panel PC
- `C:\Reports\` — watch folder for incoming XLS
- `C:\Reports\archive\` — processed XLS files moved here
- `C:\VTScadaReporter\vtscada_reporter.log` — service log (rotating, 5MB, 3 backups)