annotate_grafana

#!/usr/bin/env php
<?php

$conn = pg_connect("dbname=hacklabjkl_sensor");

pg_prepare($conn, "tag_open", 'INSERT INTO annotation (source, ts_start, tag) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING');
pg_prepare($conn, "tag_close", 'UPDATE annotation SET ts_end=$2 WHERE source=$1 AND tag=$3 AND ts_end IS NULL');

$result = pg_query($conn, 'SELECT ts, not battery_ok_in as "battery_low_in", not battery_ok_basement as "battery_low_basement", not internet_ok as "internet_down", not ac as "blackout", pk, not battery_ok_out as "battery_low_out" from ahma order by ts desc limit 1');
//$result = pg_query($conn, 'SELECT ts, not battery_ok_in as "battery_low_in", not battery_ok_basement as "battery_low_basement", not internet_ok as "internet_down", not ac as "blackout", pk, not battery_ok_out as "battery_low_out" from ahma order by ts asc');

// Having extra loop in here (in vain normally) to allow doing queries
// to populate the old data with little changes to this script
while ($row = pg_fetch_assoc($result)) {
    $ts = $row['ts'];
    unset($row['ts']);
    foreach ($row as $k => $v) {
        $a = ["ahma", $ts, $k];
        pg_execute($conn, $v === 'f' ? "tag_close" : "tag_open", $a);
    }
}