#!/usr/bin/perl -w
use DBI;
use strict;
use POSIX;
use FindBin;            	# locate this script
use lib "$FindBin::Bin";    # include script directory
use Config::Simple;
use strict;

## Dependencies
#
# apt-get install libconfig-simple-perl libdbi-perl libdbd-mysql-perl gnuplot


my $DBHOST = "";
my $DBNAME = "";
my $DBUSER = "";
my $DBPASS = "";

my $config_file_base = "power_db";
my $fileprefix = "power";
my $plot_max_power = 13000;
my $plot_max_power_detail = 750;

my $gnuplot = "/usr/bin/gnuplot";
my $debug = 0;
my $font = "font '/usr/share/fonts/dejavu/DejaVuSans.ttf' 8";

sub read_config_file;
read_config_file;
my $db = DBI->connect("DBI:mysql:$DBNAME:$DBHOST", $DBUSER, $DBPASS);

if (!exists($ARGV[1]) ) {
	print "Error - no parameters! Usage:\n\n";
	print "powerplot.pl DB_table plot_directory [DAY_TO_PLOT] [TIMEZONE]\n";
	print "\nDAY_TO_PLOT could be -n or YYYY-MM-DD\n";
	print "TIMEZONE like \"Europe/Berlin\"\n";
	print "\n";
	exit;
}


# Table, parameter 0
#
my $TABLE = $ARGV[0];

# Directory, parameter 1
#
my $plotdir = $ARGV[1];

# Timezone, parameter 3
#
my $tz = "system";
my $time = "time";
if (exists($ARGV[3]) ) { $tz= $ARGV[3]; }

# Date, parameter 2
#
my $day_to_plot = "now()";
if (exists($ARGV[2]) ) {
    if ( ! ($ARGV[2] eq  "now()")) {
		# "-n" to plat today - n days
		if ($ARGV[2] =~ /^[+-]?\d+\z/) {
			$DBI::result = $db->prepare("SELECT DATE(now() + INTERVAL " . int($ARGV[2]) . " DAY)");
			$DBI::result->execute();
			my ($d) = $DBI::result->fetchrow_array;
			$day_to_plot = "'$d'";
		} else {
	    	$day_to_plot = "'$ARGV[2]'";
		}
    }
	$debug = 1;
}
#print "date: $day_to_plot\n";


my $data_day = "$plotdir/$fileprefix" . "_day_$TABLE.dat";
my $data_day2 = "$plotdir/$fileprefix" . "_day2_$TABLE.dat";
my $data_day3 = "$plotdir/$fileprefix" . "_day3_$TABLE.dat";
my $data_week = "$plotdir/$fileprefix" . "_week_$TABLE.dat";
my $data_week2 = "$plotdir/$fileprefix" . "_week2_$TABLE.dat";
my $data_month = "$plotdir/$fileprefix" . "_month_$TABLE.dat";
my $data_month2 = "$plotdir/$fileprefix" . "_month2_$TABLE.dat";
my $data_month3 = "$plotdir/$fileprefix" . "_month3_$TABLE.dat";
my $data_year = "$plotdir/$fileprefix" . "_year_$TABLE.dat";
#my $data_year2 = "$plotdir/$fileprefix" . "_year2_$TABLE.dat";

my $query = "SELECT ROUND(power) AS t, HOUR($time), MINUTE($time), SECOND($time), date_format($time,'%d'), date_format($time,'%m'), date_format($time,'%Y'), date_format($time,'%W'), date_format($time,'%M'), DAYOFWEEK($time)-1, YEARWEEK($time,3), DAYOFYEAR($time), DAYOFMONTH(LAST_DAY($time)), DAYNAME($time), round(counter/1000,2), DAY($time)";
my ($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, $time_index, $energy);

# min/max query
#my $range_query  = "SELECT FLOOR(MAX($t_corr/10)-MIN($t_corr/10))*10 AS range1, CEIL(MAX($t_corr/10)-MIN($t_corr/10))*10 AS range2, FLOOR(MIN($t_corr/5))*5 AS min, CEIL(MAX($t_corr/5))*5 AS max FROM $TABLE ";
my $range_query = "select ceil(max(power)/100)*100 as pmax, floor(min(power)/100)*100 as pmin, ceil(max(counter)/1000) as cmax, floor(min(counter)/1000) as cmin ";


my $clause_daily = ", (unix_timestamp($time)-unix_timestamp(date($day_to_plot)))/3600 AS timeindex FROM $TABLE";
my $clause_daily1 = " $clause_daily WHERE TIMESTAMP($time) > TIMESTAMPADD(MINUTE, -60*2, date($day_to_plot)) AND TIMESTAMP($time) <= TIMESTAMPADD(MINUTE, 60*26, date($day_to_plot)) ";
my $clause_daily2 = " $clause_daily WHERE date(time) = date($day_to_plot)";


my $clause_weekly = ", ( unix_timestamp($time)-UNIX_TIMESTAMP(DATE(DATE_ADD($day_to_plot, INTERVAL(0-WEEKDAY($day_to_plot)) DAY) ) ) ) /(3600*24) AS timeindex FROM $TABLE WHERE YEARWEEK($time,3) = YEARWEEK($day_to_plot,3) ";

my $clause_monthly = ", (unix_timestamp($time)-unix_timestamp(DATE(DATE_SUB($day_to_plot, INTERVAL DAYOFMONTH($day_to_plot)-1 DAY))))/ (3600*24) AS timeindex 
FROM $TABLE WHERE YEAR($time) = YEAR($day_to_plot) and MONTH($time) = MONTH($day_to_plot)";

my $clause_yearly = ", ( unix_timestamp($time)-unix_timestamp(DATE(DATE_SUB(now(), INTERVAL DAYOFYEAR(now())-1 DAY)))
 )/(3600*24) AS timeindex FROM $TABLE WHERE YEAR($time) = YEAR($day_to_plot) ";


my $end_clause = " and power > 0 ORDER BY $time";

#############################################################################
sub read_config_file;
sub plot_bias;
my @temp_row;
my $line;

read_config_file;


$DBI::result = $db->prepare("SET time_zone = '$tz'");
$DBI::result->execute();
$DBI::result = $db->prepare("SELECT hour(now()), minute(now()), year(now()) ");
$DBI::result->execute();
my ($hour_now, $minute_now) = $DBI::result->fetchrow_array;



### Create day plot
#
open (DAYPLOT, ">", $data_day) or die "Can't open $data_day";
open (DAYPLOT2, ">", $data_day2) or die "Can't open $data_day2";
$DBI::result = $db->prepare($query . $clause_daily1 . $end_clause);
#print "$query $clause_daily1 $end_clause \n";
$DBI::result->execute();
while(@temp_row = $DBI::result->fetchrow_array) {
($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, $time_index)  = @temp_row;
	$line = $time_index . " $t\n";
	print DAYPLOT $line;
	print DAYPLOT2 $time_index . " $counter\n";
}
if (not defined $t) { exit; }
close DAYPLOT;
close DAYPLOT2;
$DBI::result->finish();

if (!($year)) { exit; } # Quit if no data

# Power bars
open (DAYPLOT, ">", $data_day3) or die "Can't open $data_day3";
my $sql = $query . ", round(max(counter/1000)-min(counter /1000),1) AS energy " . $clause_daily2 . " AND power > 0 group by hour(time) ORDER BY time";
$DBI::result = $db->prepare($sql);
#	print "\n$sql\n";
$DBI::result->execute();
while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, 	$energy, $time_index) = @temp_row;
    print DAYPLOT "$hour $energy \n";
}
close DAYPLOT;
$DBI::result->finish();


# Get date info for plot, last data point is next day
$DBI::result = $db->prepare($query . $clause_daily2 . $end_clause);
$DBI::result->execute();
@temp_row = $DBI::result->fetchrow_array;
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, $time_index) = @temp_row;

# Ordinary daily plot
#
my ($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_daily2, $plot_max_power);
open (GPLOT, ">$plotdir/day.gplot") or die "Can't open $plotdir/day.gplot";
print GPLOT "set terminal png $font size 768, 500 \n";
print GPLOT "set xtics 1, 1, 23\n";
print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
#print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
#print GPLOT "set y2range[$cmin:$cmax]\n"; 
print GPLOT "set y2range[0:*]\n";
 print GPLOT "set y2tics\n";
print GPLOT "set xrange [0:24]\n";
print GPLOT "set yrange [" , $pmin , ":" , $pmax , "]\n";
print GPLOT "set grid xtics ytics\n";
print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month$day.png\"\n";
print GPLOT "set title \"Power consumption $dayname $monthname $day $year\"\n";
	print GPLOT "set boxwidth 0.5\n";
#	print GPLOT "set style fill pattern 2 border -1\n";
print GPLOT "plot \"$data_day\" axes x1y1 ti \"Power W\" with steps, ";
#print GPLOT " \"$data_day2\" axes x1y2 ti \"Energy KWh\" with lines\n";
print GPLOT " \"$data_day3\" axes x1y2 ti \"Energy KWh\" with boxes\n";
close (GPLOT);
`$gnuplot $plotdir/day.gplot &>/dev/null`;

# Mini daily plot
#
($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_daily2, $plot_max_power_detail);
open (GPLOT, ">$plotdir/day.gplot") or die "Can't open $plotdir/day.gplot";
print GPLOT "set terminal png $font size 768, 500 \n";
print GPLOT "set xtics 1, 1, 23\n";
print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
print GPLOT "set y2range[$cmin:$cmax]\n"; 
print GPLOT "set xrange [0:24]\n";
print GPLOT "set yrange [" , $pmin , ":" , $pmax , "]\n";
print GPLOT "set grid xtics ytics\n";
print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month$day" . "_m.png\"\n";
print GPLOT "set title \"Power consumption $dayname $monthname $day $year\"\n";
print GPLOT "plot \"$data_day\" axes x1y1 ti \"Power W\" with steps, ";
print GPLOT " \"$data_day2\" axes x1y2 ti \"Energy KWh\" with lines\n";
close (GPLOT);
`$gnuplot $plotdir/day.gplot &>/dev/null`;

# Log daily plot
#
open (GPLOT, ">$plotdir/day.gplot") or die "Can't open $plotdir/day.gplot";
print GPLOT "set terminal png $font size 768, 500 \n";
print GPLOT "set xtics 1, 1, 23\n";
print GPLOT "set yrange [100:20000]\n";
print GPLOT "set logscale y 10\n";
print GPLOT "set ytics (100, 200, 300, 400, 500, 600, 700, 800, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 10000, 20000)\n";
print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
print GPLOT "set y2range[$cmin:$cmax]\n"; 
print GPLOT "set xrange [0:24]\n";
print GPLOT "set grid xtics ytics\n";
print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month$day" . "_l.png\"\n";
print GPLOT "set title \"Power consumption $dayname $monthname $day $year\"\n";
print GPLOT "plot \"$data_day\" axes x1y1 ti \"Power W\" with steps, ";
print GPLOT " \"$data_day2\" axes x1y2 ti \"Energy KWh\" with lines\n";
close (GPLOT);
`$gnuplot $plotdir/day.gplot &>/dev/null`;




# Weekly plot
#
if ( ($minute_now < 5 or $minute_now > 55) or $debug) {
# {
	open (WEEKPLOT, ">", $data_week) or die "Can't open $data_week";
	open (WEEKPLOT2, ">", $data_week2) or die "Can't open $data_week2";
	$DBI::result = $db->prepare($query . $clause_weekly . $end_clause);
#	print $query . $clause_weekly . $end_clause . "\n";
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, $time_index) = @temp_row;
		$time_index++;
	    print WEEKPLOT "$time_index $t \n";
		print WEEKPLOT2 "$time_index $counter\n";
	}
	close WEEKPLOT;
	close WEEKPLOT2;
	$DBI::result->finish();

	my $xlegend = "";
	$DBI::result = $db->prepare($query . $clause_weekly . $end_clause);
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
		my ($dn, $d, $wd) = @temp_row;
		$wd++;
		my $wd2 = $wd + 0.5;
		$xlegend = $xlegend . "'$dn $d' $wd, '' $wd2" ;
		if ($wd < 7) { $xlegend .= ", "; }		
	}
	my ($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_weekly, $plot_max_power);

	# Weekly normal plot
	#
	open (GPLOT, ">$plotdir/week.gplot") or die "Can't open $plotdir/month.gplot";
	print GPLOT "set terminal png $font size 768, 500 \n";
	print GPLOT "set xtics ('Mon' 1, '' 1.5, 'Tue' 2, '' 2.5, 'Wed' 3, '' 3.5, 'Thu' 4, '' 4.5, 'Fri' 5, '' 5.5, 'Sat' 6, '' 6.5, 'Sun' 7, '' 7.5)\n";
	print GPLOT "set xrange [1:7+1]\n";
	print GPLOT "set yrange[$pmin:$pmax]\n";
	print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set grid xtics ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_" . $week . "w.png\"\n";
	print GPLOT "set title \"Power consumption Week " . substr($week,0,4) . " ". substr($week,4,2) . "\"\n";
	print GPLOT "plot \"$data_week\" axes x1y1 ti \"Power W\" with steps, ";
	print GPLOT " \"$data_week2\" axes x1y2 ti \"Energy KWh\" with lines\n";

	close (GPLOT);
	`$gnuplot $plotdir/week.gplot &>/dev/null`;
#	`rm -f $plotdir/week.gplot $data_week`;

	# Weekly log plot
	#
	open (GPLOT, ">$plotdir/week.gplot") or die "Can't open $plotdir/month.gplot";
	print GPLOT "set terminal png $font size 768, 500 \n";
	print GPLOT "set xtics ('Mon' 1, '' 1.5, 'Tue' 2, '' 2.5, 'Wed' 3, '' 3.5, 'Thu' 4, '' 4.5, 'Fri' 5, '' 5.5, 'Sat' 6, '' 6.5, 'Sun' 7, '' 7.5)\n";
	print GPLOT "set xrange [1:7+1]\n";
	print GPLOT "set yrange [100:20000]\n";
	print GPLOT "set logscale y 10\n";
	print GPLOT "set ytics (100, 200, 300, 400, 500, 600, 700, 800, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 10000, 20000)\n";
	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set grid xtics ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_" . $week . "w_l.png\"\n";
	print GPLOT "set title \"Power consumption Week " . substr($week,0,4) . " ". substr($week,4,2) . "\"\n";
	print GPLOT "plot \"$data_week\" axes x1y1 ti \"Power W\" with steps, ";
	print GPLOT " \"$data_week2\" axes x1y2 ti \"Energy KWh\" with lines\n";

	close (GPLOT);
	`$gnuplot $plotdir/week.gplot &>/dev/null`;



# Monthly plot
#
	open (MONTHPLOT, ">", $data_month) or die "Can't open $data_month";
	open (MONTHPLOT2, ">", $data_month2) or die "Can't open $data_month2";
	$DBI::result = $db->prepare($query . $clause_monthly . $end_clause);
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, $time_index) = @temp_row;
	    print MONTHPLOT $time_index+1 . " $t \n";
		print MONTHPLOT2 $time_index+1 . " $counter\n";
	}
	close MONTHPLOT;
	close MONTHPLOT2;
	$DBI::result->finish();
	($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_monthly, $plot_max_power);

	open (GPLOT, ">$plotdir/month.gplot") or die "Can't open $plotdir/month.gplot";
	print GPLOT "set terminal png $font size 768, 500\n";
	print GPLOT "set xtics 0, 1, $daysinmonth\n";
	print GPLOT "set xrange [1:$daysinmonth+1]\n";
	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set yrange[$pmin:$pmax]\n";
	print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
	print GPLOT "set grid xtics ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month.png\"\n";
	print GPLOT "set title \"Power consumption $monthname $year\"\n";
	print GPLOT "set pointsize 0.3\n";
	print GPLOT "plot \"$data_month\" axes x1y1 ti \"Power W\" with steps, ";
	print GPLOT " \"$data_month2\" axes x1y2 ti \"Energy KWh\" with lines\n";
	close (GPLOT);
	`$gnuplot $plotdir/month.gplot &>/dev/null`;

	open (GPLOT, ">$plotdir/month.gplot") or die "Can't open $plotdir/month.gplot";
	print GPLOT "set terminal png $font size 768, 500\n";
	print GPLOT "set xtics 0, 1, $daysinmonth\n";
	print GPLOT "set xrange [1:$daysinmonth+1]\n";
	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set yrange [100:20000]\n";
	print GPLOT "set logscale y 10\n";
	print GPLOT "set ytics (100, 200, 300, 400, 500, 600, 700, 800, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 10000, 20000)\n";
	print GPLOT "set grid xtics ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month" . "_l.png\"\n";
	print GPLOT "set title \"Power consumption $monthname $year\"\n";
	print GPLOT "set pointsize 0.3\n";
	print GPLOT "plot \"$data_month\" axes x1y1 ti \"Power W\" with steps, ";
	print GPLOT " \"$data_month2\" axes x1y2 ti \"Energy KWh\" with lines\n";
	close (GPLOT);
	`$gnuplot $plotdir/month.gplot &>/dev/null`;


# Monthly plot boxes
#
	open (MONTHPLOT, ">", $data_month3) or die "Can't open $data_month";
	$sql = $query . ", round(max(counter/1000)-min(counter /1000),1) AS energy " . $clause_monthly . " AND power > 0 group by date(time) ORDER BY time";
	$DBI::result = $db->prepare($sql);

#	print "\n$sql\n";
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, $energy, $time_index) = @temp_row;
	    print MONTHPLOT "$dom $energy \n";
	}
	close MONTHPLOT;
	$DBI::result->finish();
	
	open (GPLOT, ">$plotdir/month.gplot") or die "Can't open $plotdir/month.gplot";
	print GPLOT "set terminal png $font size 768, 500\n";
	print GPLOT "set xtics 1, 1, $daysinmonth\n";
	print GPLOT "set xrange [0:$daysinmonth+1]\n";
	print GPLOT "set yrange[0:*]\n";
	print GPLOT "set grid ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month.png\"\n";
	print GPLOT "set title \"Power consumption $monthname $year\"\n";
	print GPLOT "set boxwidth 0.5 \n";
	print GPLOT "set style fill solid 1.0 border -1\n";
	print GPLOT "red = \"#080000\"; green = \"#000800\"; blue = \"#000008\"\n";
	print GPLOT "plot \"$data_month3\" with boxes ti \"kWh\" linecolor rgb \"#00FF00\" \n";
	close (GPLOT);
	`$gnuplot $plotdir/month.gplot &>/dev/null`;

}



# Create year plot
#
if ( ($hour_now == 0 and $minute_now < 15) or $debug) {
	open (YEARPLOT, ">", $data_year) or die "Can't open $data_year";
	my $sql = $query . ", round(max(counter/1000)-min(counter /1000),1) AS energy " . $clause_yearly . " AND power > 0 group by MONTH(time) ORDER BY time";
	$DBI::result = $db->prepare($sql);
#	print "\n$sql\n";
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $counter, $dom, $energy, $time_index) = @temp_row;
	    print YEARPLOT "$month $energy \n";
	}
	close YEARPLOT;
#	($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_monthly, $plot_max_power);

	open (GPLOT, ">$plotdir/year.gplot") or
		die "Can't open $plotdir/year.gplot";
	print GPLOT "set terminal png $font size 768, 500\n";
#	print GPLOT "set xtics ('Jan' 1, 'Feb' 32, 'Mar' 60, 'Apr' 91, 'May' 121, 'Jun' 152, 'jul' 182, 'Aug' 213, 'Sep' 244, 'Okt' 274, 'Nov' 305, 'Dec' 335, '' 366)\n";
	print GPLOT "set xtics ('Jan' 1, 'Feb' 2, 'Mar' 3, 'Apr' 4, 'May' 5, 'Jun' 6, 'jul' 7, 'Aug' 8, 'Sep' 9, 'Okt' 10, 'Nov' 11, 'Dec' 12)\n";

#    print GPLOT "set xrange [-15:365+1]\n";
    print GPLOT "set xrange [0.5:12.5]\n";
#	print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
#	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
#	print GPLOT "set yrange[$pmin:$pmax]\n";
	print GPLOT "set yrange[0:*]\n";
#	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set grid ytics \n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_$year.png\"\n";
	print GPLOT "set title \"Power consumption $year\"\n";
#	print GPLOT "set pointsize 0.3\n";
#	print GPLOT "plot \"$data_year\" axes x1y1 ti \"Power W\" with steps, ";
#	print GPLOT " \"$data_year2\" axes x1y2 ti \"Energy KWh\" with lines\n";

	print GPLOT "set boxwidth 0.5\n";
	print GPLOT "set style fill solid 1.0 border -1\n";
#	print GPLOT "set style fill solid \n";

#	print GPLOT "plot \"$data_month3\" using 2:xticlabels(1) ti \"kWh\" \n";
	print GPLOT "red = \"#080000\"; green = \"#000800\"; blue = \"#000008\"\n";
	print GPLOT "plot \"$data_year\" with boxes ti \"kWh\" linecolor rgb \"#00FF00\" \n";


	close (GPLOT);
	`$gnuplot $plotdir/year.gplot &>/dev/null`;
#	`rm -f $plotdir/year.gplot $plotdir/temp_year.dat`;
}

$DBI::result->finish();
$db->disconnect;
undef $db; 



sub plot_bias {
	my ($sql, $plot_pmax)  = @_;
    $DBI::result = $db->prepare($sql);
    $DBI::result->execute();
	my ($pmax, $pmin, $cmax, $cmin) = $DBI::result->fetchrow_array;
	$DBI::result->finish();
	my $y1tic = 50;
    my $y2tic = 1;
	$pmin = 0; 
    if ( ($pmax>$plot_pmax) && $plot_pmax ) { $pmax = $plot_pmax; }
	if ($pmax>1000)  { $y1tic = 100;  }
	if ($pmax>1500)  { $y1tic = 250;  }
    if ($pmax>4000)  { $y1tic = 500;  }
    if ($pmax>8000)  { $y1tic = 1000; }
    if ($pmax>15000) { $y1tic = 2000; }
	$pmax = ceil($pmax/$y1tic)*$y1tic;
	my $y1steps = ($pmax - $pmin) / $y1tic;

    $y2tic = ceil(($cmax-$cmin) / $y1steps);
	$cmax = $cmin + ($y2tic * $y1steps);
#	print " ($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic)\n";
	return ($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic);
}

# Read config file
#
sub read_config_file {
	my $configfile;
	if (-e "$FindBin::Bin/$config_file_base.conf") {
		$configfile = "$FindBin::Bin/$config_file_base.conf";
	} else {
		if (-e "~/.$config_file_base") {
			$configfile = "~/.$config_file_base";
		} else {
			if (-e "/etc/$config_file_base.conf") {
				$configfile = "/etc/$config_file_base.conf";
			}
		}
	}

	if (!$configfile) { 
		print "Missing config file, the program will look for a file in the following order:\n\n";
		print " 1: Same directory as the program, $FindBin::Bin/$config_file_base.conf\n";
		print " 2: ~/.$config_file_base\n";
		print " 3: /etc/$config_file_base.conf\n\n";
		print "A sample config file would look like this:\n\n";
		print "DBHOST\tlocalhost\nDBNAME\tpower\nDBUSER\tpower_user\nDBPASS\tpower_pass\n\n";
		exit 1;
	}
	my $cfg = new Config::Simple();
	$cfg->read($configfile);
	if ($cfg->param('DBHOST'))  { $DBHOST  = $cfg->param('DBHOST'); }
	if ($cfg->param('DBNAME'))  { $DBNAME  = $cfg->param('DBNAME'); }
	if ($cfg->param('DBUSER'))  { $DBUSER  = $cfg->param('DBUSER'); }
	if ($cfg->param('DBPASS'))  { $DBPASS  = $cfg->param('DBPASS'); }
#	if ($cfg->param('DBTABLE')) { $DBTABLE = $cfg->param('DBTABLE'); }
    undef $cfg;
}