yearly meter summary for volkszaehler.org

meter.pl

#!/usr/bin/perl
# vim: ts=4
use strict;
use warnings;
use DBI;
use Text::Table;

my $chid = shift;

my $dbh = DBI->connect("DBI:mysql:database=vz;host=localhost", "vz", "Geheim", { RaiseError => 1, AutoCommit => 1 });

if (not defined $chid) {
    my $channels = $dbh->selectall_arrayref(q(
        select e.id, p1.value AS title, p2.value as 'description', e.uuid
        from entities e
        left join properties p1 on e.id=p1.entity_id and p1.pkey='title'
        left join properties p2 on e.id=p2.entity_id and p2.pkey='description'
        where type='electric meter'
        order by e.id;
    ));
    my $tb = Text::Table->new(qw(id title desc uuid));
    $tb->load(@{$channels});
    print $tb;

} else {
    my $ints = $dbh->selectall_arrayref(q(select from_unixtime(timestamp / 1000, '%Y'), timestamp from data where channel_id=? group by 1 order by 2), undef, $chid);
    push @$ints, $dbh->selectrow_arrayref(q(select 'dummy', timestamp from data where channel_id=? order by timestamp desc limit 1), undef, $chid);
    my ($val0) = $dbh->selectrow_array(q(select value from data where channel_id=? order by timestamp limit 1), undef, $chid);
    my $per0 = (shift @$ints)->[0];
    my $tb = Text::Table->new(qw(year reading kWh));
    foreach my $int (@$ints) {
        my $per = $int->[0];
        my $tsms = $int->[1];
        my ($val) = $dbh->selectrow_array(q(select value from data where channel_id=? and timestamp=?), undef, $chid, $tsms);
        $tb->add($per0, $val, sprintf('%.1f', $val - $val0));
        $per0 = $per;
        $val0 = $val;
    }
    print $tb;
}