Battery PDF Report — 2026-07-14

Generates a PDF report of iPhone battery usage over the last 7 days and publishes it to the web server.

1 — Fetch data

import psycopg2, datetime
BST = datetime.timezone(datetime.timedelta(hours=1))
conn = psycopg2.connect(host='/home/john/tmp', port=5432, dbname='owntracks', user='owntracks_ro')
cur = conn.cursor()
cur.execute("""
    WITH day_batt AS (
        SELECT (to_timestamp(tst) AT TIME ZONE 'Europe/London')::date AS day,
               batt, tst, bs
        FROM locations
        WHERE tst >= extract(epoch FROM now() - interval '7 days')
          AND batt IS NOT NULL
    )
    SELECT day,
           (array_agg(batt ORDER BY tst ASC))[1]  AS first_batt,
           (array_agg(batt ORDER BY tst DESC))[1] AS last_batt,
           MIN(batt), MAX(batt),
           COUNT(*) FILTER (WHERE bs IN (2,3)),
           COUNT(*),
           array_agg(batt ORDER BY tst),
           array_agg(tst  ORDER BY tst)
    FROM day_batt GROUP BY day ORDER BY day
""")
rows = cur.fetchall()
conn.close()
print(f"Fetched {len(rows)} days")
for r in rows:
    day, first_b, last_b = r[0], r[1], r[2]
    dt = datetime.datetime(day.year, day.month, day.day, tzinfo=BST)
    print(f"  {dt.strftime('%a %d %b')}  {first_b}% → {last_b}%  drop={first_b-last_b:+d}%")

2 — Generate PDF

import datetime, matplotlib
matplotlib.use('pdf')
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from matplotlib.backends.backend_pdf import PdfPages
import seaborn as sns

BST = datetime.timezone(datetime.timedelta(hours=1))
pdf_path = '/home/john/tmp/battery-report-2026-07-14.pdf'

def plot_day(ax, row):
    day, first_b, last_b, min_b, max_b, chrg_fixes, total_fixes, batt_series, tst_series = row
    times = [datetime.datetime.fromtimestamp(t, tz=BST) for t in tst_series]
    charging = chrg_fixes > total_fixes * 0.1
    drop = first_b - last_b
    colour = '#e05050' if drop > 30 else '#e09030' if drop > 10 else '#50a050'
    ax.plot(times, batt_series, color=colour, linewidth=1.5)
    ax.fill_between(times, batt_series, alpha=0.2, color=colour)
    ax.set_ylim(0, 105)
    ax.set_ylabel('%', fontsize=9)
    dt = datetime.datetime(day.year, day.month, day.day, tzinfo=BST)
    ax.set_title(
        f"{dt.strftime('%A %d %b')} — iPhone battery   "
        f"start={first_b}%  end={last_b}%  min={min_b}%  drop={drop:+d}%"
        + ('  [charging]' if charging else ''),
        fontsize=10, loc='left'
    )
    ax.xaxis.set_major_formatter(mdates.DateFormatter('%H:%M'))
    ax.xaxis.set_major_locator(mdates.AutoDateLocator())
    ax.tick_params(labelsize=8)
    ax.axhline(20, color='#bbb', linewidth=0.6, linestyle='--')
    ax.axhline(80, color='#bbb', linewidth=0.6, linestyle='--')

sns.set_theme(style='whitegrid')
with PdfPages(pdf_path) as pdf:
    fig, axes = plt.subplots(len(rows), 1, figsize=(14, 3 * len(rows)))
    if len(rows) == 1:
        axes = [axes]
    for ax, row in zip(axes, rows):
        plot_day(ax, row)
    fig.suptitle(f'iPhone battery — last 7 days (generated 2026-07-14)', fontsize=13)
    fig.tight_layout()
    pdf.savefig(fig)
    plt.close(fig)

    # PDF metadata
    d = pdf.infodict()
    d['Title'] = 'iPhone Battery Report'
    d['Author'] = 'John Critchley'
    d['Subject'] = f'Battery usage last 7 days ending 2026-07-14'

import os
size_kb = os.path.getsize(pdf_path) // 1024
print(f"PDF saved: {pdf_path}  ({size_kb} KB)")

3 — Publish to web server

import subprocess, datetime
BST = datetime.timezone(datetime.timedelta(hours=1))
pdf_path = '/home/john/tmp/battery-report-2026-07-14.pdf'
remote = 'root@gravlax.critchley.biz:/var/www/www.critchley.biz/tmp/battery-report-2026-07-14.pdf'
result = subprocess.run(['scp', pdf_path, remote], capture_output=True, text=True)
if result.returncode == 0:
    subprocess.run(['ssh', 'root@gravlax.critchley.biz',
                    f'chmod 644 /var/www/www.critchley.biz/tmp/battery-report-2026-07-14.pdf'])
    print(f"Published: https://www.critchley.biz/tmp/battery-report-2026-07-14.pdf")
else:
    print(f"scp failed: {result.stderr}")
version 1