# STT report generation from SmartTicket XLSX

Use when the user sends daily `Zvit kontrol KP CHETS.xlsx` files and asks to place reports in the site’s `Звіт СТТ` section.

## Source file structure

SmartTicket XLSX exports may look empty to `openpyxl` (`max_row=1`) even though the data exists in `xl/worksheets/sheet1.xml` as `inlineStr`. If that happens, parse the worksheet XML directly with `zipfile` + `xml.etree.ElementTree`.

Relevant columns:

- `B`: №
- `C`: Валідатор
- `D`: Контролер
- `E`: Дата перевірки та час (`DD.MM.YYYY HH:MM:SS`)
- `F`: Кількість перевірок успішних
- `G`: Кількість перевірок не успішних

## Aggregation rules

For each daily file:

1. Read rows with a valid controller name in `D` and datetime in `E`.
2. Group by exact controller string.
3. For each controller:
   - first check = minimum datetime
   - last check = maximum datetime
   - duration = last - first, rounded up to the next full minute when seconds remain
   - successful = sum column `F`
   - unsuccessful = sum column `G`
4. Sort controllers by first check time.
5. Daily totals:
   - controllers = number of grouped controllers
   - total checks = successful + unsuccessful
   - records = number of parsed data rows, often equal to total checks

Duration helper:

```python
def dur_hm(first, last):
    secs = int((last - first).total_seconds())
    mins = (secs + 59) // 60
    return f'{mins//60:02d}:{mins%60:02d}'
```

## Site outputs

- Write a page per date: `/opt/data/vercel-project/stt/DD.MM.26.html` and mirror it to `/tmp/schedule_site/vercel-project/stt/DD.MM.26.html`.
- Update `/opt/data/vercel-project/stt.html` (and mirror) so that each available day is a `.stt-row.has-report` link with a short summary.
- Keep navigation consistent with other pages and include `save-page.js`.
- Avoid raw emoji in generated Python strings when possible; use HTML entities for ✅/❌ (`&#9989;`, `&#10060;`).

## Verification

After generation and deploy, verify live URLs:

```bash
python3 - <<'PY'
import urllib.request, re
base='https://vercel-project-xi-five.vercel.app'
idx=urllib.request.urlopen(base+'/stt.html', timeout=30).read().decode('utf-8')
for d in [15,16,17,18]:
    print('index', d, bool(re.search(r'<div class="stt-row has-report"><div class="num">%d</div>' % d, idx)))
for key in ['15.05.26','16.05.26','17.05.26','18.05.26']:
    s=urllib.request.urlopen(base+f'/stt/{key}.html', timeout=30).read().decode('utf-8')
    rows=len(re.findall(r'<td class="num">\d+</td>', s))
    stats=re.findall(r'<div class="stat"><b>(\d+)</b><span>([^<]+)</span></div>', s)[:4]
    print(key, 'rows', rows, stats)
PY
```
