#!/usr/bin/perl -w use strict; use DBI; use Data::Dumper; use Date::Manip; use List::Util qw(max min); use File::Basename; use Benchmark; use File::Find; my $DBNAME = "/home/jarv/db/power.db"; my $JSON_PATH = "/home/jarv/jarv.org/power/gen-files"; # dir for gen files my $HTML_PATH_REL = "power"; my $JSON_PATH_REL = "power/gen-files"; # relative path for json files my $HTML_FILE = "/home/jarv/jarv.org/power/pwr_script.shtml"; my $STAT_FILE = "/home/jarv/jarv.org/power/pwr_stat.shtml"; Date_Init("TZ=EDT"); # i live on the east coast, server is on the west coast { #main my $t0 = new Benchmark; my $date = get_max_from_db(); `mkdir -p $JSON_PATH` unless (-d $JSON_PATH); print "Generating 72Hour line chart\n"; my $html = (); my $date_str = UnixDate($date,'%Y-%m-%d-%H-%M-%S'); $html .= gen_chart( { title => "hour3", date => $date, offset => "-72hours", filename => "$JSON_PATH/72hour-current-" . $date_str . ".json", frequency => "0:0:0:0:0:5:0", # every 5 minute end => $date, xlabelstep => 240, xstep => 15, ytitle => "Power (watts)", labeltotal => "Approximate power usage", labelphasea => "single phase", labelphaseb => "single phase", height => 300, width => 750, ydate => '%a %i:%M%p', type => 'line', }); print "Generating 24Hour line chart\n"; $html .= gen_chart( { title => "hour2", date => $date, offset => "-24hours", filename => "$JSON_PATH/24hour-current-" . $date_str . ".json", frequency => "0:0:0:0:0:1:0", # every minute end => $date, xlabelstep => 120, xstep => 15, ytitle => "Power (watts)", labeltotal => "Approximate power usage", labelphasea => "single phase", labelphaseb => "single phase", height => 300, width => 750, ydate => '%i:%M%p', type => 'line', }); print "Generating 2hour line chart\n"; $html .= gen_chart( { title => "hour1", date=>$date, offset => "-2hours", filename => "$JSON_PATH/2hour-current-" . $date_str . ".json", frequency => "0:0:0:0:0:1:0", # every minute end => $date, xlabelstep => 20, xstep => 2, ytitle => "Power (watts)", labeltotal => "Approximage power usage", labelphasea => "single phase", labelphaseb => "single phase", height => 300, width => 750, ydate => '%i:%M%p', type => 'line', }); print "Generating 1week summary report by hour\n"; $html .= gen_chart( { title => "hoursummary1", date=>$date, offset => "-7days", filename => "$JSON_PATH/7day-hour-summary-" . $date_str . ".json", frequency => "0:0:0:0:1:0:0", # every hour Y:M:W:D:H:MN:S filter => ['12AM',' 1AM',' 2AM', ' 3AM', ' 4AM',' 5AM',' 6AM',' 7AM', ' 9AM','10AM','12PM',' 1PM', ' 2PM',' 3PM',' 4PM',' 5PM', ' 6PM',' 7PM',' 8PM',' 9PM', '10PM','11PM'], end => $date, xlabelstep => 1, xstep => 1, ytitle => "Power (watts)", labeltotal => "Approximage power usage", labelphasea => "single phase", labelphaseb => "single phase", height => 400, width => 750, ydate => '%i%p', type => 'summary_bar', }); print "Generating 1week weekday report\n"; $html .= gen_chart( { title => "weeksummary1", date=>$date, offset => "-7days", filename => "$JSON_PATH/7day-weekday-summary-" . $date_str . ".json", frequency => "0:0:0:1:0:0:0", # every day filter => ['Sun','Mon','Tue','Wed', 'Thu','Fri','Sat'], end => $date, xlabelstep => 1, xstep => 1, ytitle => "Power (watts)", labeltotal => "Approximage power usage", labelphasea => "single phase", labelphaseb => "single phase", height => 400, width => 750, ydate => '%a', type => 'summary_bar', }); print "Generating 30day summary report by hour\n"; $html .= gen_chart( { title => "hoursummary2", date=>$date, offset => "-30days", filename => "$JSON_PATH/30day-hour-summary-" . $date_str . ".json", frequency => "0:0:0:0:1:0:0", # every hour Y:M:W:D:H:MN:S filter => ['12AM',' 1AM',' 2AM', ' 3AM', ' 4AM',' 5AM',' 6AM',' 7AM', ' 9AM','10AM','12PM',' 1PM', ' 2PM',' 3PM',' 4PM',' 5PM', ' 6PM',' 7PM',' 8PM',' 9PM', '10PM','11PM'], end => $date, xlabelstep => 1, xstep => 1, ytitle => "Power (watts)", labeltotal => "Approximage power usage", labelphasea => "single phase", labelphaseb => "single phase", height => 400, width => 750, ydate => '%i%p', type => 'summary_bar', }); print "Generating 30day weekday report\n"; $html .= gen_chart( { title => "weeksummary2", date=>$date, offset => "-30days", filename => "$JSON_PATH/30day-weekday-summary-" . $date_str . ".json", frequency => "0:0:0:1:0:0:0", # every day filter => ['Sun','Mon','Tue','Wed', 'Thu','Fri','Sat'], end => $date, xlabelstep => 1, xstep => 1, ytitle => "Power (watts)", labeltotal => "Approximage power usage", labelphasea => "single phase", labelphaseb => "single phase", height => 400, width => 750, ydate => '%a', type => 'summary_bar', }); print "Writing out html\n"; open (FD,">$HTML_FILE") or die "Can't open $HTML_FILE : $!"; print FD $html; close(FD); print "Cleaning up old json files\n"; # any file that doesn't match the current date string will be blown away, # hopefully we've successfully written out something by now find( sub{ unlink($File::Find::name) unless ($File::Find::name =~ /$date_str/); }, $JSON_PATH); my $t1 = new Benchmark; my $td = timediff($t1, $t0); print "Last data collection on " . UnixDate($date,'%a, %b %d %Y %i:%M %p EDT') . " completed in " . timestr($td) . "\n"; print "Writing out benchmark info\n"; open (FD, ">$STAT_FILE") or die "Can't open $STAT_FILE : $!"; print FD "Last data collection on " . UnixDate($date,'%a, %b %d %Y %i:%M %p EDT') . " completed in " . timestr($td) . "\n"; close(FD); } sub filter_data { # filters data on a filter array my @filter = @{ (shift) }; my @time_vals = @{ (shift) }; my @values = @{ (shift) }; my @phasea = @{ (shift) }; my @phaseb = @{ (shift) }; my %new_data = (); foreach (@filter) { my $f_str = $_; $new_data{$f_str}{values} = 0; $new_data{$f_str}{phasea} = 0; $new_data{$f_str}{phaseb} = 0; $new_data{$f_str}{cnt} = 0; for (my $i=0; $i 0) { $new_data{$_}{values} = rnd($new_data{$_}{values} / $new_data{$_}{cnt}); $new_data{$_}{phasea} = rnd($new_data{$_}{phasea} / $new_data{$_}{cnt}); $new_data{$_}{phaseb} = rnd($new_data{$_}{phaseb} / $new_data{$_}{cnt}); } } return %new_data; } sub gen_chart { my ($args) = @_; my $start = DateCalc($args->{date}, "$args->{offset}"); $start = Date_GetPrev($start,undef,1,undef,0,undef); my @dates = ParseRecur($args->{frequency}, $start,$start, $args->{end}); my @data = process_for_range(\@dates); my (@time_vals, @values, @phasea, @phaseb) = (); foreach my $element (@data) { push @time_vals, UnixDate($element->[0], $args->{ydate}); push @phasea, $element->[1]; push @phaseb, $element->[2]; push @values, $element->[3]; } # get the first value that isn't a null and record it as the first real date we processed my $first_date; foreach my $element (@data) { if ($element->[3] ne 'null') { $first_date = $element->[0]; last; } } my $title_string = "Processed from " . UnixDate($first_date, '%a %f/%e %i:%M %p') . " to " . UnixDate($args->{date}, '%a %f/%e %i:%M %p'); open (FD, ">$args->{filename}") or die "Unable to open $args->{filename} : $!"; if ($args->{type} eq 'line') { my $value_str = join(',', @values); my $phasea_str = join(',', @phasea); my $phaseb_str = join(',', @phaseb); my $ymax = max(grep{/^\d+/} @values, @phasea, @phaseb); my $ymin = min(grep{/^\d+/} @values, @phasea, @phaseb); my $diff = $ymax - $ymin; my $ystep; if ($diff > scale(200)) { $ystep = scale(20); } elsif ( $diff > scale(60) ) { $ystep = scale(10); } elsif ( $diff > scale(20) ) { $ystep = scale(5); } else { $ystep = scale(1); } my $time_str = '"' . join ('","',@time_vals) . '"'; print FD<{labeltotal}", "font-size": 12, "width": 2, "halo-size": 1, "values" : [$value_str], "dot-style" : { "tip": "~#val#W @ #x_label#", "type": "dot", "dot-size": 5, "halo-size": 1 } }, { "type": "line", "colour": "#66FF00", "text": "$args->{labelphasea}", "font-size": 12, "width": 1, "values" : [$phasea_str], "dot-style" : { "tip": "~#val#W @ #x_label#", "type": "dot", "dot-size": 2, "halo-size": 1, "colour": "#FF0000" } }, { "type": "line", "colour": "#00FFFF", "text": "$args->{labelphaseb}", "font-size": 12, "width": 1, "values" : [$phaseb_str], "dot-style" : { "tip": "~#val#W @ #x_label#", "type": "dot", "dot-size": 2, "halo-size": 1, "colour": "#FF0000" } } ], "title": { "text": "$title_string", "style": "{ font-size: 10px; font-weight: bold; text-align: center; }" }, "x_axis":{ "steps" : $args->{xstep}, "labels": { "steps": $args->{xlabelstep}, "rotate": 20, "labels":[$time_str] } }, "y_axis": { "steps": $ystep, "min" : $ymin, "max" : $ymax } } LINEEND close(FD); } elsif ($args->{type} eq 'summary_bar') { my %data = filter_data($args->{filter}, \@time_vals, \@values, \@phasea, \@phaseb); my (@yvals,@values,@labels); for my $val (@{$args->{filter}}) { push @values, "[ $data{$val}{phasea} , $data{$val}{phaseb} ]"; push @yvals, $data{$val}{values}; push @labels, "\"$val\""; # "\"$val ($data{$val}{cnt})\""; } my $value_str = join(',', @values); my $label_str = join(',', @labels); my $max = max(@yvals); my $ystep; if ($max > scale(500)) { $ystep = scale(50); } elsif ($max > scale(200)) { $ystep = scale(20); } elsif ( $max > scale(60) ) { $ystep = scale(10); } elsif ( $max > scale(20) ) { $ystep = scale(5); } else { $ystep = scale(1); } print FD<~#val#W for this phase", "on-show": { "type": "pop", "cascade": 1, "delay": 0.5 } } ], "title": { "text": "$title_string", "style": "{font-size: 10px; text-align: center;}" }, "x_axis": { "labels": { "rotate": 20, "labels": [$label_str] } }, "y_axis": { "min": 0, "max": $max, "steps": $ystep }, "tooltip": { "mouse": 2 } } SUMMARYBAREND close(FD); } my $data_file = $JSON_PATH_REL . "/" . basename($args->{filename}); return qq# #; } sub get_max_from_db { my $dbargs = {AutoCommit =>1, PrintError =>1}; my $dbh = DBI->connect("dbi:SQLite:dbname=" . $DBNAME, "", "", $dbargs ); my $max = $dbh->selectrow_array( "select max(timestamp) from data"); $dbh->disconnect; my $date = ParseDateString("epoch $max"); die "unable to determine max timestamp from db" unless ($date); return $date; } sub process_for_range { my @dates=@{ (shift) }; my @data = (); my $dbargs = {AutoCommit =>1, PrintError =>1}; my $dbh = DBI->connect("dbi:SQLite:dbname=" . $DBNAME, "", "", $dbargs ); for (my $i=0; $i < scalar @dates; $i++) { my $start = UnixDate($dates[$i],'%s'); if ($dates[$i+1]) { my $end = UnixDate($dates[$i+1], '%s'); # query average for timespan my ($v1,$v2) = $dbh->selectrow_array( "select avg(value1),avg(value2) from data where timestamp >= $start AND timestamp < $end"); if ($v1 && $v2) { push @data, [ $dates[$i], scale( rnd($v1) ), scale( rnd($v2) ), scale( rnd($v1+$v2) )]; } else { # no data for the timespan push @data, [ $dates[$i], 'null','null','null' ]; #push @data, [ $dates[$i], 0,0,0 ]; } } else { # query average for all values less than current time my ($v1,$v2) = $dbh->selectrow_array( "select avg(value1),avg(value2) from data where timestamp >= $start"); if ($v1 && $v2) { push @data, [ $dates[$i], scale( rnd($v1) ), scale( rnd($v2) ), scale( rnd($v1+$v2) ) ]; } else { # no data available print "select avg(value1),avg(value2) from data where timestamp >= $start\n"; push @data, [ $dates[$i], 'null', 'null', 'null' ]; #push @data, [ $dates[$i], 0, 0, 0 ]; } } } $dbh->disconnect; return @data; } sub rnd { my($number) = shift; return int($number + .5); } sub scale { # convert raw data to watts my $val = shift; # conversion is # volts read by arduino (val * .0032) # scaled to current (volts) * 200 / 3 # multiplied by 120V # multiplied by the power factor .60 # this scaling factor ends up being 15.36 times the value read return rnd( ($val * 15.36) ); }