#!/usr/bin/perl -w
#2345678911234567892123456789312345678941234567895123456789612345678971234567898

# License: GPL v3
# by Marc MERLIN <marc_soft at merlins.org>, 2009/11/09 
# $Id: parsebrandpower 356 2011-09-24 23:25:47Z svnuser $

use strict;
use Date::Manip;
use Getopt::Long;
use DB_File;

# this script works based on the timezone it's run in. If the data is
# captured in PST8PDT but the script is run in MET, 9 timezones away,
# the data will be wrong since the hours and TOU calculations are based
# on the computer's local time since this script has no way to know if
# there is a mismatch between the timezone where the data was captured
# and the timezone the script is run in.
# Or to parse PDT data while in PST time: export TZ=America/Denver'

my $VERBOSE = 0;

# fix counters from the powermeter not being 0 when it started
my $PGE_OFFSET = 124;
my $AC_OFFSET = 0;
my $PV_OFFSET = 230.8;

# notes from my setup:
# Data before monitoring was started:
# 20090512 mid: 1803 Kwh/ 805h |PMeter:  230 (diff 1573) <-- log start

# Sync dumps from external meter with inside meter
# 20090713 mor: 3915 Kwh/1680h |PMeter: 2206 (diff 1709/+136 62days 2.2Kwh/day)
# offset diff:117 Pmeter diff:1583Kwh days diff:51|0.07 diff/kwh 2.29 diff/day

# 20090727 mor: 4386 Kwh/1871h |PMeter: 2651 (diff 1735/+162 76days 2.1Kwh/day)
# offset diff: 26 Pmeter diff: 445Kwh days diff:14|0.05 diff/kwh 1.85 diff/day

# 20090920 mid: 6065 Kwh/2579h |PMeter: 4230 (diff 1835/+262 131days 2.0Kwh/day)
#   04:49654 | 05:49311 | 09:49614 | 13:50728 | Total: -693 |Powermeter PGE:   16
# offset diff:100 Pmeter diff:1579Kwh days diff:55|0.06 diff/kwh 1.81 diff/day

# 20091017 mid: 6685 Kwh/2879h |PMeter: 4824 (diff 1861/+288 158days 1.8Kwh/day)
#   04:49776 | 05:49255 | 09:49538 | 13:50983 | Total: -448 |Powermeter PGE:  165
# offset diff: 26 Pmeter diff: 594Kwh days diff:27|0.04 diff/kwh 0.96 diff/day

# 20091220 ???: 7678 Kwh/3504h |PMeter: 5841 (diff 1837/+264 222 days 0.8Kwh/day)
#   04:50634 | 05:49275 | 09:49626 | 13:51732 | Total:  1267|Powermeter PGE: 1086
# offset diff:-24 Pmeter diff:1017Kwh days diff:64|-.02 diff/kwh -.38 diff/day

# 1 Year Panels
# 20100301 mor: 8503 Kwh/4179h |PMeter: 6680 (diff 1823/+250 293 days 0.8Kwh/day)
#   04:51722 | 05:49275 | 09:49790 | 13:52656 | Total:  3443|Powermeter PGE: 2245
# offset diff:-14 Pmeter diff: 839Kwh days diff:71|-.02 diff/kwh -.19 diff/day

# 1 Year Powermeter
# 20100511 mor:10369 Kwh/5052h |PMeter: 8490 (diff 1879/+306 355 days 0.8Kwh/day)
#   04:51704 | 05:49227 | 09:49887 | 13:52595 | Total:  3413|Powermeter PGE: 2335
# offset diff: 56 Pmeter diff:1810Kwh days diff:71|0.03 diff/kwh 0.78 diff/day
# Total solar production = 8566 (8490-230 = 8260  vs 8566)

# lifetime: diff is 0.06 Kwh per Kwh read (288 / 4824-230)
# http://www.wolframalpha.com/input/?i=2009%2F10%2F17+-+2009%2F05%2F12


# 20090722 20090820 PG&E: -151 -112 + 230 = -33
# Powermeter says: -11 to 0 = - 11 Kwh or real is +22/300% bigger?
#PPLOGFILE=~/power/power parsepower --parse-month 20090722 20090820
#Summer Off Peak tier 1: PG&E has  193.4Kwh at $.08/Kwh or a total of $15.47
#Total Summer Off Peak: PG&E had  193.4Kwh for total of $15.47
#Summer Partial Peak tier 1: PG&E has -80.2Kwh at $.14/Kwh or a total of $-11.22
#Total Summer Partial Peak: PG&E had -80.2Kwh for total of $-11.22
#Summer Peak tier 1: PG&E has -112.5Kwh at $.29/Kwh or a total of $-32.64
#Total Summer Peak: PG&E had -112.5Kwh for total of $-32.64
#Total: PG&E had   0.7Kwh for total of $-28.38
#
#001,004-000010.682,20090722000033
#001,004,000000.610,20090820000033
#
#230/193
#1.19170984455958549222
#112/80
#1.40000000000000000000
#151/112
#1.34821428571428571428
#
#
#
# 20090620 20090721 PG&E: -172 - 119 + 257 = -34
# Powermeter says: -5 to -13 = - 8 Kwh or real is +26/400% bigger?
#PPLOGFILE=~/power/power parsepower --parse-month 20090620 20090721
#Summer Off Peak tier 1: PG&E has  201.8Kwh at $.08/Kwh or a total of $16.15
#Total Summer Off Peak: PG&E had  201.8Kwh for total of $16.15
#Summer Partial Peak tier 1: PG&E has -84.9Kwh at $.14/Kwh or a total of $-11.89
#Total Summer Partial Peak: PG&E had -84.9Kwh for total of $-11.89
#Summer Peak tier 1: PG&E has -116.5Kwh at $.29/Kwh or a total of $-33.80
#Total Summer Peak: PG&E had -116.5Kwh for total of $-33.80
#Total: PG&E had   0.4Kwh for total of $-29.54
#
# 001,004-000004.956,20090620000033
# 001,004-000013.821,20090721000033
#
#257/202
#1.27227722772277227722
#119/85
#1.40000000000000000000
#172/116
#1.48275862068965517241
#
#
#
# 20090521 20090619 PGE: -173 - 113 + 138 = -148 Kwh
# Powermeter says: 106.145 to 0 = - 106 Kwh or real is +42/36% bigger?
#PPLOGFILE=~/power/power parsepower --parse-month 20090521 20090619
#Use of uninitialized value in addition (+) at /var/local/scr/parsepower line 479.
#Summer Off Peak tier 1: PG&E has  131.2Kwh at $.08/Kwh or a total of $10.50
#Total Summer Off Peak: PG&E had  131.2Kwh for total of $10.50
#Summer Partial Peak tier 1: PG&E has -114.1Kwh at $.14/Kwh or a total of $-15.97
#Total Summer Partial Peak: PG&E had -114.1Kwh for total of $-15.97
#Summer Peak tier 1: PG&E has -147.7Kwh at $.29/Kwh or a total of $-42.83
#Total Summer Peak: PG&E had -147.7Kwh for total of $-42.83
#Total: PG&E had -130.6Kwh for total of $-48.31
#
#001,004,000106.145,20090521000033
#001,004-000000.232,20090619000033
#
#
#138/131
#1.05343511450381679389
#113/114
#.99122807017543859649
#173/147
#1.17687074829931972789



# Data expected by this script
# Note that times are expected to be in UTC
# (i.e. you want to set the powermeter to UTC time because it is incapable of
# handling DST, so you want to leave that job to your computer)

# PG&E (goes negative when reselling)
# 001,001,005719.076,20090518013228  Watts
# 001,002,000234.338,20090518013230  Volts
# 001,003,000026.448,20090518013231  Amps
# 001,004,000112.033,20090518013233  Kwh

# AC / 2 (negative when in use)
# 002,001-001840.226,20090518013235  Watts
# 002,002,000119.244,20090518013237  Volts
# 002,003,000016.438,20090518013238  Amps
# 002,004-000017.225,20090518013240  Kwh

# Panels (positive when producing)
# 003,001,000129.365,20090518013242  Watts
# 003,002,000231.143,20090518013244  Volts
# 003,003,000001.267,20090518013246  Amps
# 003,004,000418.650,20090518013247  Kwh


# Great, the power device will also randomly have burbs in the data, look at the AC kWh:
# 001,001,001197.511,20091018133228
# 001,002,000239.255,20091018133229
# 001,003,000005.695,20091018133231
# 001,004,000182.637,20091018133233
# 002,001,000001.592,20091018133235
# 002,002,000122.769,20091018133237
# 002,003,000000.059,20091018133238
# 002,004-000223.867,20091018133240  < -223 the proper value
# 003,001-000009.220,20091018133242
# 003,002,000236.001,20091018133244
# 003,003,000000.358,20091018133246
# 003,004,004833.315,20091018133247
# ^D
# logout
# exit
# ÿý
# password
# powermeter
# 001,001,001053.361,20091018133428
# 001,002,000239.361,20091018133429
# 001,003,000005.034,20091018133431
# 001,004,000182.674,20091018133433
# 002,001,000000.446,20091018133435
# 002,002,000122.980,20091018133437
# 002,003,000000.024,20091018133438
# 002,004-000095.943,20091018133440 <- random jump to -95 (and then goes back to -223)
# 003,001-000009.341,20091018133442
# 003,002,000236.091,20091018133444
# 003,003,000000.358,20091018133445
# 003,004,004833.315,20091018133447
#
# or better, it goes all to 000 (see end of sample 2):
# 001,001-002080.565,20090627161628
# 001,002,000238.256,20090627161629
# 001,003,000009.049,20090627161631
# 001,004-000057.594,20090627161633
# 002,001-000004.700,20090627161635
# 002,002,000122.230,20090627161636
# 002,003,000000.072,20090627161638
# 002,004-000030.218,20090627161640
# 003,001,003114.796,20090627161642
# 003,002,000235.118,20090627161644
# 003,003,000013.411,20090627161645
# 003,004,001665.934,20090627161647
# 
# logout
# exit
# ÿý
# password
# powermeter
# 001,001-002125.447,20090627161828
# 001,002,000237.720,20090627161830
# 001,003,000009.248,20090627161831
# 001,004-000057.664,20090627161833
# 002,001-000005.098,20090627161835
# 002,002,000121.866,20090627161837
# 002,003,000000.072,20090627161839
# 002,004-000030.218,20090627161840
# 003,001,000000.000,20090627161842 <
# 003,002,000000.000,20090627161844 <
# 003,003,000000.000,20090627161846 < WTF?
# 003,004,000000.000,20090627161847 < 



################################################################################
# Code starts here
################################################################################

# http://graphcomp.com/info/specs/ansi_col.html
my $OUTPUTTYPE = "none";
$_ = `tty`;
$OUTPUTTYPE = "tty" if m#/dev#;

my $LOGFILE = "/var/log/power/power";
$LOGFILE=$ENV{'PPLOGFILE'} if ($ENV{'PPLOGFILE'});
# how many lines is a single set of records (you can overshoot by a bit)
my $REC_LINES = 20;
# how many minutes between each data sample (to back-calculate average
# watts per time slice)
my $SAMPLE_TIME = 2;

# How many watts we want to see to decide that the PV system is active
my $PVMinWatts = 100;

# How many Kwh we want to see in an hour before we'll consider the value
# (remove noise)
my $MinHourKwh = 0.03;

# Filter out huge counter jumps due to an error in the protocol or powermeter
# 30Kwh per hour max (increase this for bigger panels or big electricity
# use) which gives 1Kwh per sample. 
my $MaxKwhPerSample = 1;

use constant PGE =>	    1;
use constant AC =>	    2;
use constant PV =>	    3;
use constant House =>	    4;
use constant HouseNoAC =>   5;

use constant Watts =>	    1;
use constant Volts =>	    2;
use constant Amps =>	    3;
use constant Kwh =>	    4;
# AvgWatts is based on Kwh between 2 samples
use constant AvgWatts =>    5;

# first 3 probes are real. The last 2 are calculated from the first 2
# you may only have one probe or two
my @probes = ( 0, "PG&E", "AC", "PV", "House", "HouseNoAC" );
my @subprobes = ( 0, "Watts", "Volts", "Amps", "Kwh", "AvgWatts" );


# Globals that are used in load_file_data
my @timeslot;   # array of increasing timeslots that can be fed as keys to %data
my %data;	# $data{$timeslot[idx]}
		#   -> [probe: PGE .. HouseNoAC][subprobe: Watts .. Kwh] : value
my @hourrate;	# array of first timeslot for each new hour (used for getting
		# the billing rate for each hour block later on)

	
my $PRINT_TIME = 0;
my $PRINT_WATTS = 0;
my $RRDTOOL = 0;
my $CACTI = 0;
my $CACTI_DUMP = 0;
my $GOOGLE_POWERMETER_DUMP = 0;
my $GOOGLE_POWERMETER_TAIL = 0;
my $PARSE_MONTH = 0;

use constant PEAK =>	    1;
use constant PARPK =>	    2;
use constant OFFPK =>	    3;
my %levels = (
   -2 => "Winter Partial Peak",
   -3 => "Winter Off Peak",
    1 => "Summer Peak",
    2 => "Summer Partial Peak",
    3 => "Summer Off Peak"
);

# compute PG&E date to peak / partial peak / off peak
# http://www.pge.com/tariffs/doc/E-6.doc
# http://www.pge.com/tariffs/electric.shtml
# E6 times at the bottom of:
# http://www.pge.com/includes/docs/pdfs/b2b/newgenerator/solarwindgenerators/standardenet/howto_readnemmeter_e6.pdf
# Find the baseline quantity here:
# http://www.pge.com/myhome/customerservice/financialassistance/medicalbaseline/understand/
# but knowing what your baseline quantity is for each month is not fun,
# they can change it at any time. It's about 13Kwh/day in zone X
use constant BASELINEPERDAY => 13;
# for tier debugging
#use constant BASELINEPERDAY => 4;

# M-F 10:00-13:00 PP
# M-F 13:00-19:00 P
# M-F 19:00-21:00 PP
# M-F 21:00-10:00 OP
# SS  17:00-20:00 PP
# SS  20:00-17:00 OP
#				    PEAK     PART-PK  OFF-PEAK
my @Summer = (
["Baseline Usage",		    0.29320, 0.14456, 0.08458],
["101% - 130% of Baseline",	    0.30900, 0.16036, 0.10038],
["131% - 200% of Baseline",	    0.43690, 0.28857, 0.22872],
["201% - 300% of Baseline",	    0.55568, 0.40735, 0.34750],
["Over 300% of Baseline",	    0.61792, 0.46960, 0.40974] );

# Winter
# M-F 17:00-20:00 PP
# rest: OP
my @Winter = (
["Baseline Usage",		    0,       0.10033, 0.08848],
["101% - 130% of Baseline",	    0,       0.11612, 0.10424],
["131% - 200% of Baseline",	    0,       0.24443, 0.24443],
["201% - 300% of Baseline",	    0,       0.36321, 0.35151],
["Over 300% of Baseline",	    0,       0.42546, 0.41375] );

# what percentage of baseline is allowed in each subsequent tier
my @tier_breakpoints = ( 0, 100, 30, 70, 100, 99999999 );

my $dbfilename = "$LOGFILE.badsamples";
my %bad_samples;

tie (%bad_samples, 'DB_File', $dbfilename, O_RDWR, 0, $DB_HASH) or die "Can't tie $dbfilename: $!";

sub verbose
{
    my ($mesg, $level) = @_;
    $level = 1 if (not $level);

    warn("$mesg\n") if ($VERBOSE >= $level);
}

sub color
{
    # http://graphcomp.com/info/specs/ansi_col.html
    # http://www.utexas.edu/learn/html/colors.html
    my ($color) = @_;

    return if ($OUTPUTTYPE eq "none");
    return if (not $color);

    print $OUTPUTTYPE eq "tty" ? "" : "" if ($color eq "init");
    print $OUTPUTTYPE eq "tty" ? "" : "" if ($color eq "end");
    print $OUTPUTTYPE eq "tty" ? "" : "</FONT>" if ($color eq "endcolor");

    print $OUTPUTTYPE eq "tty" ? "" : "<FONT COLOR=#FF0000>" if ($color eq "red");
    print $OUTPUTTYPE eq "tty" ? "" : "<FONT COLOR=#FF6600>" if ($color eq "yellow");
    print $OUTPUTTYPE eq "tty" ? "" : "<FONT COLOR=#0000FF>" if ($color eq "blue");
    print $OUTPUTTYPE eq "tty" ? "" : "<FONT COLOR=#000000>" if ($color eq "white");

}

# returns what peak tier the date is in (1, 2, 3) and negative for winter tiers
sub date_to_peak_level
{
    my ($date, $tier) = @_;
    my $wday = UnixDate($_[0], "%w");
    my $hour = UnixDate($_[0], "%H");
    my $month = UnixDate($_[0], "%m");

    # winter is Nov 1st to Apr 30th
    if ($month <= 4 or $month >= 11)
    {
	if (Date_IsWorkDay($date))
	{
	    if ($hour <= 17 or $hour >= 20) { return OFFPK * -1 } else { return PARPK * -1 }
	}
	return OFFPK * -1;
    }
    # summer
    else
    {
	verbose("Checking for summer holiday on $date ($wday)", 3);
	return OFFPK if (Date_IsHoliday($date));
	# sat and sun
	if ($wday >= 6)
	{
	    verbose("Checking for summer partial peak or offpeak on weekend ($wday)", 3);
	    return ($hour >= 17 and $hour <= 20) ? PARPK : OFFPK;
	}
	# we're left with weekdays
	verbose("Checking for summer peak on hour $hour / $wday", 3);
	return PEAK if ($hour >= 13 and $hour <= 19);
	verbose("Checking for summer partial peak on hour $hour / $wday", 3);
	return PARPK if (($hour >= 10 and $hour <= 13) or ($hour >= 19 and $hour <= 21));
	verbose("left with summer off peak on hour $hour / $wday", 3);
	return OFFPK;
    }
}

sub peak_level_to_price
{
    my ($peaklevel, $tier) = @_;
    $tier = 0 if (not defined $tier);

    die "Can't be called with peaklevel 0" if (not $peaklevel);

    if ($peaklevel < 0)
    {
	$peaklevel *= -1;
	return $Winter[$tier][$peaklevel];
    }
    else { return $Summer[$tier][$peaklevel]; }
}

sub date_to_hour
{
    return UnixDate($_[0], "%H");
}

sub date_to_wday
{
    return UnixDate($_[0], "%a");
}

sub date_to_epoch
{
    return UnixDate($_[0], "%s");
}


sub delta_hms
{
    $_ = DateCalc($_[0], $_[1]);
    #print "Got delta $_ from ".join("|", @_)."\n";

    return sprintf("%5.2f", Delta_Format($_, 1, "%hd"));
}

sub delta_mins
{
    $_ = DateCalc($_[0], $_[1]);
    #print "Got delta $_ from ".join("|", @_)."\n";

    return sprintf("%5.2f", Delta_Format($_, 1, "%mh"));
}

sub printable_date
{
    return UnixDate($_[0], "%Y/%m/%d %T");
}

sub printable_time
{
    return UnixDate($_[0], "%T");
}

sub powermeter_date
{
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime(UnixDate($_[0], "%s"));

    $year += 1900;
    $mon += 1;

    return sprintf("%d-%02d-%02dT%02d:%02d:%02dZ", $year, $mon, $mday, $hour, $min, $sec);
}



# load all the data samples in %data and the timeslots keys in @timeslot
# %date time indexes are converted from UTC to local time at read time
# and are stored in Date::Manip format (YYYMMDDHH:MM:SS)
sub load_file_data
{
    # fromdate and todate are given in localtime as per locale
    my ($FH, $fromdate, $todate, $print_first_parsed_date) = @_;
    # convert them once to parsed format so that comparisons can be done as strings
    my ($parsed_fromdate, $parsed_todate) = (ParseDate($fromdate), ParseDate($todate));

    my $line = 0;
    my $counter = 0;
    my $needsync = 1;
    my $time = 0;
    my $datatime = 0;
    my $dataline;
    # previous data record is used to compute average watts per timeslice
    my $prev_dataline;
    my $prev_datatime;

    $print_first_parsed_date = 1 if (not defined $print_first_parsed_date);
    verbose("load_file_data: between $parsed_fromdate and $parsed_todate");

    while(<$FH>)
    {
	$line++;
	chomp;

	if (/^powermeter$/)
	{
	    $counter = 0;
	    $needsync = 0;
	    undef $dataline;
	    next;
	}
	elsif ($needsync)
	{
	    next;
	}
	$counter++;

	if (/^00[123],/)
	{
	    die "Too many data lines read at line $line on $_\n (after $time/$datatime)\n" if ($counter > 12);
	}
	elsif ($counter < 13)
	{
	    warn "Data line missing at line $line on $_\n (after $time/$datatime)\n";
	    $needsync = 1;
	    next;
	}
	else
	{
	    $needsync = 1;
	    next;
	}

	# fix bug in input where negative numbers are missing the , separator
	s/-/,-/;
	#print "working on line $_";
	if ($counter < 13)
	{
	    my ($probe, $subprobe, $value);
	    ($probe, $subprobe, $value, $time) = split(/,/, $_);

	    chomp($time);
	    $datatime = ParseDate("$time UTC") if ($counter == 1);
	    print STDERR "Parsing $time -> $datatime\n" if ($PRINT_TIME);
	    if ($print_first_parsed_date)
	    {
		verbose("First log entry starts from ".printable_date($datatime));
		$print_first_parsed_date = 0;
	    }

	    if ($datatime lt $parsed_fromdate)
	    {
		# speed up parsing
		verbose("datatime $datatime is too early, skipping record", 5);
		$needsync = 1;
		next;
	    }
	    elsif ($parsed_todate lt $datatime)
	    {
		# got the last sample we want, stop parsing
		close($FH);
		verbose("datatime $datatime is now past $parsed_todate, stopping read");
		last;
	    }
	    else
	    {
		$prev_datatime = $datatime if (not defined $prev_datatime);
		$dataline->[$probe][$subprobe] = $value;

		# if this was the last probe/subprobe, fix data entries
		if ($counter == 12)
		{
		    # Fix AC since we only probe one leg and the
		    # sign is wrong
		    $dataline->[AC][Watts] *= -2;
		    $dataline->[AC][Kwh] *= -2;
		    $dataline->[AC][Volts] *= 2;
		    $dataline->[AC][Amps] *= 2;

		    # Fix PV probe to show negative
		    # no more, it's easier to deal with positive data for cacti and google powermeter
		    #$dataline->[PV][Watts] *= -1;
		    #$dataline->[PV][Kwh] *= -1;

		    # Fix offsets that weren't at 0 when logs started
		    $dataline->[PGE][Kwh] -= $PGE_OFFSET;
		    $dataline->[AC][Kwh] -= $AC_OFFSET;
		    $dataline->[PV][Kwh] -= $PV_OFFSET;


		    # House = PG&E + PV
		    $dataline->[House][Watts] = $dataline->[PGE][Watts] + $dataline->[PV][Watts];
		    $dataline->[House][Kwh] = $dataline->[PGE][Kwh] + $dataline->[PV][Kwh];

		    # HouseNoAC = House - AC
		    $dataline->[HouseNoAC][Watts] = $dataline->[House][Watts] - $dataline->[AC][Watts];
		    $dataline->[HouseNoAC][Kwh] = $dataline->[House][Kwh] - $dataline->[AC][Kwh];

		    my $sample_delta = delta_mins($prev_datatime, $datatime);
		    if ($prev_datatime and $sample_delta > $SAMPLE_TIME * 2)
		    {
			warn "Warning data log time jumped by $sample_delta mn at $datatime\n";
		    }
		    foreach my $i (1 .. $#probes)
		    {
			if ($prev_dataline)
			{
			    my $timediff= delta_mins($prev_datatime, $datatime);
			    my $allowed_kwh_diff = $timediff * $MaxKwhPerSample;

			    if (abs($prev_dataline->[$i][Kwh] - $dataline->[$i][Kwh]) > $allowed_kwh_diff)
			    {
				warn("Data sample error at $datatime ($time): ".$probes[$i]." went from ".$prev_dataline->[$i][Kwh]." to ".$dataline->[$i][Kwh].", in $timediff mn which is bigger than the allowed $allowed_kwh_diff. Fixing...\n");
				$dataline->[$i][Kwh] = $prev_dataline->[$i][Kwh];
				$bad_samples{$datatime} = '1';
			    }
			    else
			    {
				verbose("Data at $datatime ($time): ".$probes[$i]." is ".$dataline->[$i][Kwh], 2);
			    }
			    # AvgWatts is computed from Kwh diff in our time
			    # sample
			    $dataline->[$i][AvgWatts] = sprintf("%d",(($dataline->[$i][Kwh] - $prev_dataline->[$i][Kwh]) * 1000 * 60 / $SAMPLE_TIME));

			}
			else
			{
			    $dataline->[$i][AvgWatts] = $dataline->[$i][Watts]
			}
		    }

		    if (not $bad_samples{$datatime})
		    {
			$data{$datatime} = $dataline;
			push (@timeslot, $datatime);
			$prev_dataline = $dataline;
			$prev_datatime = $datatime;
		    }
		    else
		    {
			warn("Ignoring bad data sample at $datatime\n");
		    }
		}
	    }
	}
    }
}


sub parse_and_print_day
{
    # all work is done on local timezone dates
    my ($fromdate, $todate, $print_watts, $print) = @_;
    my ($pfromdate, $ptodate) = (printable_date($fromdate), printable_date($todate));
    my ($unix_fromdate, $unix_todate) = (date_to_epoch($fromdate), date_to_epoch($todate));
    my $parsehour = 0;
    my $hour_first_slot;
    my @hour_kwh_sum;
    my @hour_dollar_sum;
    my (%rate_kwh_sum, %rate_dollar_sum);
    my ($pv_start, $pv_stop, $pv_hours) = (0, 0, 0);
    my $cur_hour;
    my ($parsed_fromdate, $parsed_todate) = (ParseDate($fromdate), ParseDate($todate));
    # the loop needs to go into the next hour before it can close the 23rd hour
    my $parsed_aftertodate = DateCalc($todate, "+10 minutes");

    $print_watts = 0 if (not defined $print_watts);
    $print = 1 if (not defined $print);

    die "No data read for $pfromdate -> $ptodate\n" if ($#timeslot == -1);
    die "Earliest data record (".printable_date($timeslot[0]).") is newer $pfromdate, try a bigger tail value \n" if (DateCalc($timeslot[0], "-10 minutes") gt $parsed_fromdate); # -10mn is because the first timeslot is slightly bigger than our start date
    foreach my $timeslot (0 .. $#timeslot)
    {
	next if ($timeslot[$timeslot] lt $parsed_fromdate);
	last if ($timeslot[$timeslot] gt $parsed_aftertodate);
	$hour_first_slot = $timeslot if (not $hour_first_slot);
	verbose("Will parse timeslot $timeslot, date $timeslot[$timeslot] between $parsed_fromdate and $parsed_todate", 4);
	$cur_hour = date_to_hour($timeslot[$timeslot]);

	# find the first time slot where we have non leakage current
	# detected on the PV system (more than $PVMinWatts watts)
	if ($data{$timeslot[$timeslot]}->[PV][Watts] > $PVMinWatts )
	{
	    $pv_start = $timeslot if (not $pv_start);
	    $pv_stop = $timeslot;
	    #print "PV watts is ".$data{$timeslot[$timeslot]}->[PV][Watts]." for $cur_hour/$timeslot and now have start: $pv_start and stop: $pv_stop\n";
	}
#	else
#	{
#	    print "Rejecting watts: ".$data{$timeslot[$timeslot]}->[AC][Watts]."\n";
#	}
	print "House watts: ".$data{$timeslot[$timeslot]}->[House][Watts]."(inst) | ".$data{$timeslot[$timeslot]}->[House][AvgWatts]." (avg)\n" if ($PRINT_WATTS);

	verbose("Now cur $cur_hour parse $parsehour | $timeslot out of $#timeslot", 4);
	# loop ends when curhour loops back to 0 (and parsehour is 23) or timeslot is the last one
	if ($cur_hour > $parsehour or $cur_hour < $parsehour or $timeslot == $#timeslot)
	{
	    my $level = date_to_peak_level($timeslot[$timeslot-1]);
	    my $rate = peak_level_to_price($level);
	    $hourrate[$parsehour] = $level;
	    verbose("$parsehour is rate $rate (level $level)");
	    if ($timeslot == 0)
	    {
		warn ("Data only starts a $cur_hour:00 (earlier hours missing)\n");
		$parsehour = $cur_hour;
		next;
	    }
	    #print "Entered cur $cur_hour parse $parsehour | $timeslot out of $#timeslot\n";
	    foreach my $i (1 .. $#probes)
	    {
		my $cost;
		my $kwh = $data{$timeslot[$timeslot-1]}->[$i][Kwh] - $data{$timeslot[$hour_first_slot]}->[$i][Kwh];
		$kwh *= -1 if ($i eq PV);
		# remove noise
		$kwh = 0 if (abs($kwh) <= $MinHourKwh);
		$cost = $kwh * $rate;
		$hour_kwh_sum[$parsehour][$i] = $kwh;
		$hour_dollar_sum[$parsehour][$i] += $cost;
		verbose("for hour $parsehour, $kwh Kwh for probe ".$probes[$i]." at rate $rate costs $cost (between timeslot ".$timeslot[$hour_first_slot]." and ".$timeslot[$timeslot-1]." ($hour_first_slot to ".($timeslot-1).")", 3);

		$rate_kwh_sum{$level}[$i] += $kwh;
		$rate_dollar_sum{$level}[$i] += $cost;
		verbose("after hour $parsehour, day has seen ".$rate_kwh_sum{$level}[$i]."kWh at level $level for a total dollar value ".$rate_dollar_sum{$level}[$i]." on ".$probes[$i], 2);
	    }

	    last if ($parsehour > $cur_hour);
	    $parsehour = $cur_hour;
	    $hour_first_slot = $timeslot
	}
    }
    foreach my $i (1 .. $#probes)
    {
	$hour_kwh_sum[99][$i] = $data{$timeslot[$#timeslot]}->[$i][Kwh] - $data{$timeslot[0]}->[$i][Kwh];
	$hour_dollar_sum[99][$i] = 0;
	foreach my $hour (0 .. $parsehour)
	{
	    verbose("Adding sum for $i on hour $hour", 4);
	    $hour_dollar_sum[99][$i] += $hour_dollar_sum[$hour][$i] if ($hour_dollar_sum[$hour][$i]);
	}
    }

    if ($pv_start > 0)
    {
	$pv_hours = delta_hms($timeslot[$pv_start], $timeslot[$pv_stop]);
	$pv_start = printable_time($timeslot[$pv_start]);
	$pv_stop = printable_time($timeslot[$pv_stop]);
    }


    if ($print)
    {
	if ($OUTPUTTYPE eq "html")
	{
	    ($_ = $ptodate) =~ s/.* //;
	    my $title = "Power details from ".date_to_wday($fromdate).": $pfromdate to $_";
	    print "<HTML><HEAD><TITLE>$title</TITLE></HEAD>\n";
	    print "<BODY>\n";
	    print "<H1>$title</H1>\n";
	    print "<PRE>\n";
	}
	print "\nHourly Differences\n";
	foreach my $hour (0 .. $cur_hour, 99)
	{
	    if (not $hour_kwh_sum[$hour])
	    {
		warn("No data gathered for $hour:00\n");
		next;
	    }
	    if ($hour < 99)
	    {
		printf("%02d", $hour);
		if ($hourrate[$hour] < 0)
		{
		    # blue is a fake color that should not happen, left for debugging
		    color( ["", "blue", "yellow", "white"]->[-$hourrate[$hour]] );
		    #          N/A  PP   OFFP (Winter)
		    print ["", "H", "^", "v"]->[-$hourrate[$hour]];
		}
		else
		{
		    color( ["", "red", "yellow", "white"]->[$hourrate[$hour]] );
		    #          PEAK PP   OFFP (Summer)
		    print ["", "~", "-", "_"]->[$hourrate[$hour]];
		}
		print ":";
	    }
	    else
	    {
		print "-"x100,"\n";
		print date_to_wday($todate).":";
	    }
	    # nicely ordered fields
	    foreach my $i (House, AC, HouseNoAC, PV, PGE)
	    {
		printf("% 5.1fKwh/", $hour_kwh_sum[$hour][$i]);

		$_ = sprintf("\$% 3.1f ", $hour_dollar_sum[$hour][$i]);
		if ($hour eq 99)
		{
		    # this would show the sign
		    s/\$(.)/$1\$/;
		    # remove trailing space for column alignment
		    s/ $//;
		}
		else
		{
		    # but for normal colums, we remove it
		    s/\$(.)/\$/;
		}
		print $_;
		print $probes[$i]."|";
	    }
	    color("endcolor");
	    if ($hour == 99)
	    {
		my $i = 1;
		print "\n\nSplit per rate:";
		foreach my $level (reverse sort {abs($a) <=> abs($b)} keys %rate_kwh_sum)
		{
		    my $rate = peak_level_to_price($level);
		    $_ = sprintf("%3.2f", $rate);
		    s/^0/\$/;
		    printf "\n";
		    color( ["", "white", "yellow", "red"]->[$i++] );
		    print "$_";
		    foreach my $i (House, AC, HouseNoAC, PV, PGE)
		    {
			printf("% 5.1fKwh/", $rate_kwh_sum{$level}[$i]);
			$_ = sprintf("\$% 4.2f", $rate_dollar_sum{$level}[$i]);
			# this would show the sign
			#s/\$(.)/$1\$/;
			# but we'll remove it since it's redundant
			s/\$(.)/\$/;
			print $_;
			print $probes[$i]."|";
		    }
		    color("endcolor");
		}
	    }
	    print "\n";
	}

	my $pv_total = sprintf("% 5.1fKwh", $hour_kwh_sum[99][PV]);
	print "\nSolar panels produced $pv_total during ${pv_hours}h, between $pv_start and $pv_stop\n" if ($pv_start);

	if ($OUTPUTTYPE eq "html")
	{
	# Yeah, all hardcoded, change/remove as required for you
	print  <<EOF;
	    </PRE>
<BR>
Cacti graph: <a href="http://graphs.merlins.org/graphs/g.php?action=zoom&local_graph_id=19&rra_id=0&view_type=&graph_start=$unix_fromdate&graph_end=$unix_todate&graph_height=320&graph_width=800&title_font_size=10">http://graphs.merlins.org/graphs/g.php?action=zoom&local_graph_id=19&rra_id=0&view_type=&graph_start=$unix_fromdate&graph_end=$unix_todate&graph_height=320&graph_width=800&title_font_size=10</a><BR>
<iframe src="http://graphs.merlins.org/graphs/g.php?action=zoom&local_graph_id=19&rra_id=0&view_type=&graph_start=$unix_fromdate&graph_end=$unix_todate&graph_height=320&graph_width=800&title_font_size=10"
scrolling="auto" frameborder="no" align="center" height="660px" width="980px"></iframe>
	    </BODY></HTML>
EOF

	}
    }

    # This can be used by the caller to get a sum of kwh per tier
    return (\%rate_kwh_sum, $pv_start, $pv_stop);
}


# expects first and optional last day as 20091213 (just the day)
sub parse_month
{
    my ($first_day, $last_day, $tail) = @_;
    my @month_data;
    my %level_sums;
    my $numdays = 0;
    my $baseline;

    # Load data
    my $fromdate = $first_day."000000";

    if (not $last_day)
    {
	$last_day = DateCalc($first_day, "+1 month");
	# DateCalc("20091213", "+1 month") gives 2010011300:00:00
	$last_day =~ s/00:00:00//;
    }
    my $todate = $last_day."23:59:59";

    if ($tail)
    {
	verbose("Will gather stats for $fromdate to $todate working on $tail lines");
	open(POWER, "tail -n $tail $LOGFILE |");
    }
    else
    {
	verbose("Will gather stats for $fromdate to $todate (reading the whole file)");
	open(POWER, $LOGFILE);
    }
    load_file_data(*POWER, $fromdate, $todate);
    close(POWER);

    my $day = $first_day;
    while ($day le $last_day)
    {
	$numdays++;
	verbose("Analysing day $day (between $first_day and $last_day)");
        my ($level_kwh_sums, $pv_start, $pv_stop) = parse_and_print_day($day."00:00:00", $day."23:59:59", 0, 0);

	foreach my $level (keys %{$level_kwh_sums})
	{
	    foreach my $i (House, AC, HouseNoAC, PV, PGE)
	    {
		$level_sums{$level}[$i] += $level_kwh_sums->{$level}[$i];
		verbose("level $level ".$probes[$i]." is now ".$level_sums{$level}[$i]."Kwh after adding ".$level_kwh_sums->{$level}[$i]." on day $day ($numdays)", 1);
	    }
	}
	$day = DateCalc($day, "+1 day");
	$day =~ s/00:00:00//;
    }
    $baseline = $numdays * BASELINEPERDAY;

    print "Baseline for $numdays days will be estimated at $baseline kWh\n\n";

    my $levelidx = 0;
    foreach my $probe (House, AC, HouseNoAC, PV, PGE)
    {
	my $probe_billed = 0;
	my $probe_kwh = 0;

	foreach my $level (reverse sort {abs($a) <=> abs($b)} keys %level_sums)
	{
	    my $kwh = $level_sums{$level}[$probe];
	    my $log_kwh = $kwh;
	    my $sign = 1;
	    my $level_billed = 0;

	    if ($kwh < 0)
	    {
		$sign = -1;
		$kwh *= -1;
	    }

	    foreach my $tier (1..5)
	    {
		my $tier_allowed = $baseline * $tier_breakpoints[$tier] / 100;
		my $tier_billed;
		my $rate = peak_level_to_price($level, $tier-1);
		$_ = sprintf("%3.2f", $rate);
		s/^0//;
		$rate = $_;

		print $levels{$level}." tier $tier: ";
		if ($kwh > $tier_allowed)
		{
		    $kwh -= $tier_allowed;
		    $tier_billed = $tier_allowed * $rate * $sign;
		    $level_billed += $tier_billed;
		    printf $probes[$probe]." has % 5.1fKwh at \$$rate/Kwh or a total of \$%4.2f (% 5.1fKwh left)\n", $tier_allowed * $sign, $tier_billed, $kwh * $sign;
		}
		else
		{
		    $tier_billed = $kwh * $rate * $sign;
		    $level_billed += $tier_billed;
		    printf $probes[$probe]." has % 5.1fKwh at \$$rate/Kwh or a total of \$%4.2f\n", $kwh * $sign, $tier_billed;
		    last;
		}
	    }
	    printf "Total ".$levels{$level}.": ".$probes[$probe]." had % 5.1fKwh for total of \$%4.2f\n", $log_kwh, $level_billed;
	    $probe_billed += $level_billed;
	    $probe_kwh += $log_kwh;
	}
	printf "Total: ".$probes[$probe]." had % 5.1fKwh for total of \$%4.2f\n", $probe_kwh, $probe_billed;
	print "\n";
    }
}



sub day_run
{
    my ($today, $tail) = @_;

    color("init");
    if (not $today)
    {
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
	$year += 1900;
	$mon = sprintf("%02d", $mon + 1);
	$mday = sprintf("%02d", $mday);
	$today = "$year$mon$mday";
    }

    # Load data
    my $fromdate = $today."000000";
    my $todate = $today."235959";
    # get last 20000 lines or whatever requested
    $tail=20000 if (not defined $tail);

    verbose("Will gather stats for $fromdate to $todate working on $tail lines");

    if ($tail eq "all")
    {
	open(POWER, "$LOGFILE");
    }
    else
    {
	open(POWER, "tail -$tail $LOGFILE |");
    }
    load_file_data(*POWER, $fromdate, $todate);
    close(POWER);
    parse_and_print_day($fromdate, $todate);
    color("end");
}

sub data_dump
{
    open(POWER, $LOGFILE);
    load_file_data(*POWER, 0, "now");
    close(POWER);

    # nicely ordered fields
    foreach my $i (House, AC, HouseNoAC, PV, PGE)
    {
	print "Dumping data to ".$probes[$i]."\n";
	open(FH, ">$LOGFILE.".$probes[$i]);

	foreach my $timeslot (0 .. $#timeslot)
	{
	    print FH powermeter_date($timeslot[$timeslot]);
	    print FH " ".$data{$timeslot[$timeslot]}->[$i][Kwh];
	    print FH "\n";
	}
    }
}

sub cacti_dump
{
    my $from = $_[0] ? $_[0] : 0;
    my $tail = $_[1] ? $_[1] : 0;

    if ($tail)
    {
	verbose("Will gather stats from $from working on $tail lines");
	open(POWER, "tail -n $tail $LOGFILE |");
    }
    else
    {
	verbose("Will gather stats for $from (reading the whole file)");
	open(POWER, $LOGFILE);
    }
    load_file_data(*POWER, $from, "now");
    close(POWER);

    foreach my $timeslot (0 .. $#timeslot)
    {
	# ordering has to match rrd file, change to match yours
	print date_to_epoch($timeslot[$timeslot]);
	foreach my $i (HouseNoAC, House, PGE, AC, PV)
	{
	    print ":".cacti_var_munge($data{$timeslot[$timeslot]}->[$i][Kwh]);
	}
	print "\n";
    }
}

sub cacti_var_munge
{
    my ($_) = @_;

    # cacti doesn't like negative numbers, let's init at 500,000 Kwh
    $_ += 500000;
    # Show Wh without commas to be a rrdtool DERIVE compatible
    $_ *= 1000;

    return $_;
}

sub data_tail
{
    my ($mode, $var) = @_;
    my @vars;
    my $data_old;

    # we try to get the last 5 samples' worth of data so that if the last
    # sample is unusually big, this gets caught and fixed
    open(POWER, "tail -n ".($REC_LINES*5)." $LOGFILE |");
    load_file_data(*POWER, 0, "now");
    close(POWER);
    
    my $lastdate = $timeslot[$#timeslot];
    # Find out if the last sample is recent enough (powermeter seems to output stuff
    # SAMPLE_TIME late, so we double and add one minute just to be sure
    my $acceptable_time = DateCalc("now", "-".($SAMPLE_TIME*2 + 1)." minutes");
    if ($mode > 0 and $acceptable_time gt $lastdate)
    {
	warn("FAIL: acceptable time $acceptable_time is still bigger than read $lastdate\n");
	$data_old = "U";
    }
    
    # match cacti order, also change in caller (see usage)
    @vars = (PGE .. HouseNoAC);
    @vars = ($var) if ($var);
    print date_to_epoch($timeslot[$#timeslot]).":" if ($mode == 2);
    foreach my $i (@vars)
    {
	# Note that this is pretty custom at this point obviously.
	# For one, if you are low on space, you can graph the 2 remaining
	# vars by calculating them from the first 3 which are the only ones
	# you need to store in an RRD
	if ($mode > 0)
	{
	    if ($i > 1)
	    {
		print " " if ($mode == 1);
		print ":" if ($mode == 2);
	    }
	    if ($mode == 1)
	    {
		$_ = $probes[$i];
		s/PG&E/PGE/;
		print "$_:";
	    }
	    if ($data_old)
	    {
		print "U";
	    }
	    else
	    {
		print cacti_var_munge($data{$timeslot[$#timeslot]}->[$i][Kwh]);
	    }
	}
	else
	{
	    print powermeter_date($timeslot[$#timeslot])." ";
	    print $data{$timeslot[$#timeslot]}->[$i][Kwh];
	}
    }
    print "\n";
}


sub usage
{
    print STDERR "$_[0]\n\n" if ($_[0]);
    print STDERR <<EOF;
    $0 [--google-powermeter var | --google-powermeter-dump]
Output one variable in google powermeter format, or dump everything for
batch feeding.

    $0 [--cacti | --cacti-dump]
Output one variable in cacti format (to be used as a cacti query script), or
dump everything for batch feeding.
Note that --cacti is not recommended, see --rrdtool below.

    $0 --parse-month 20090702 [20090802] (defaults to 30-ish days) 
			      [tail lines, like 100000]
Attempt at doing month summaries for bills with time of use
calculation.

    $0 [--rrdtool]
Output last sample in rrdtool format. Suitable for use like this:
rrdtool update /var/lib/cacti/rra/housepower_21.rrd --template PGE:AC:PV:House:HouseNoAC `$0 --rrdtool`.
The reason for using this is that if the data in your file updates as
often as cacti queries it, they could get slightly out of sync once in a
while and cacti would get 2 cycle's worth of updates, showing a jump and
then a dip in your graph (not pretty).
Instead, you turn off cacti rrd updating and update the RRD yourself each time
you know you just got an update. This solves the race condition.

    $0 20090801 [tail lines, like 10000]
Output daily summary for today or given date.

    optional opts:
    $0 [--print-watts] [--print-time] [--color]
EOF
    exit;
}

GetOptions(
    "verbose" => \$VERBOSE,
    "output:s" => \$OUTPUTTYPE,
    "parse-month" => \$PARSE_MONTH,
    "print-watts" => \$PRINT_WATTS,
    "print-time" => \$PRINT_TIME,
    "rrdtool" => \$RRDTOOL,
    "cacti" => \$CACTI,
    "cacti-dump" => \$CACTI_DUMP,
    "google-powermeter:i" => \$GOOGLE_POWERMETER_TAIL,
    "google-powermeter-dump" => \$GOOGLE_POWERMETER_DUMP
) or usage;

usage("bad --output $OUTPUTTYPE") unless grep(/^$OUTPUTTYPE$/, ("none", "tty", "html"));

if ($RRDTOOL)
{
    data_tail(2);
}
elsif ($CACTI)
{
    data_tail(1);
}
elsif ($GOOGLE_POWERMETER_TAIL)
{
    data_tail(0, $GOOGLE_POWERMETER_TAIL);
}
elsif ($CACTI_DUMP)
{
    cacti_dump(@ARGV);
}
elsif ($GOOGLE_POWERMETER_DUMP)
{
    data_dump();
}
elsif ($PARSE_MONTH)
{
    usage if ($#ARGV < 0 or $#ARGV > 2);
    parse_month(@ARGV);
}
else
{
    day_run(@ARGV);
}

__END__
This is what my RRD looks like if you're curious:
/usr/bin/rrdtool create --start 1242148820 /var/lib/cacti/rra/housepower_21.rrd --step 120  DS:HouseNoAC:DERIVE:600:-1000000000:100000
0000 DS:House:DERIVE:600:-1000000000:1000000000 DS:PGE:DERIVE:600:-1000000000:1000000000 DS:AC:DERIVE:600:-1000000000:1000000000 DS:PV:
DERIVE:600:-1000000000:1000000000 RRA:AVERAGE:0.5:1:2628000 RRA:AVERAGE:0.5:30:525600 RRA:AVERAGE:0.5:120:131400 RRA:AVERAGE:0.5:1440:1
0950 RRA:AVERAGE:0.5:5:3153600 RRA:MAX:0.5:30:525600 RRA:MAX:0.5:120:131400 RRA:MAX:0.5:1440:10950

# vim:sts=4:sw=4