Skip to content

Instantly share code, notes, and snippets.

@jahir
Created December 17, 2020 20:39
Show Gist options
  • Save jahir/46539b7c8988b451c2ebeead24eb7da5 to your computer and use it in GitHub Desktop.
Save jahir/46539b7c8988b451c2ebeead24eb7da5 to your computer and use it in GitHub Desktop.
yearly meter summary for volkszaehler.org
#!/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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment