#!/usr/bin/perl

use LWP::UserAgent;
use HTTP::Request::Common qw(POST);
use DBI;
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 libwww-perl

## Database setup
#
# CREATE TABLE IF NOT EXISTS nordpool_hour (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, time TIMESTAMP NULL, SE1 MEDIUMINT  UNSIGNED, SE2 MEDIUMINT UNSIGNED, SE3 MEDIUMINT UNSIGNED, SE4 MEDIUMINT UNSIGNED, PRIMARY KEY(id));


my $currency = "SEK";
my $base_uri = "http://www.nordpoolspot.com/Market-data1/Elspot/Area-Prices/SE/Hourly/";

# Database config, 
my $DBHOST  = "";
my $DBNAME  = "";
my $DBUSER  = "";
my $DBPASS  = "";
my $DBTABLE = "nordpool_hour";
my $config_file_base = "power_db";

my $ua = LWP::UserAgent->new;
my $eventvalidation;
my $viewstate;
my $date;
my $hour = 0;
my @se1;
my @se2;
my @se3;
my @se4;


sub read_config_file;
read_config_file;

my @lines = split /\n/, $ua->get($base_uri)->as_string;
foreach my $line (@lines) {
	if ( $line =~ "EVENTVALIDATION") {
		chomp $line;
		my @w=split(" ", $line);
		my @u = split("\"", @w[4]);
		$eventvalidation = @u[1];
	}
	if ( ($line =~ "__VIEWSTATE") && ($line =~ "<input")) {
		chomp $line;
		my @w=split(" ", $line);
		my @u = split("\"", @w[4]);
		$viewstate = @u[1];
	}
}



my $req = POST $base_uri,
["__EVENTTARGET" => "&#39;ctl00\$FullRegion\$npsGridView\$lnkExport&#39;" ,
"__EVENTARGUMENT.value" => "&#39;&#39;",
"ctl00\$FullRegion\$npsActionPanelView\$rptFilters\$ctl00\$ddlFilters" => $currency,  
"__EVENTVALIDATION" => $eventvalidation,
"__VIEWSTATE" => $viewstate
];


my @lines = split /\n/, $ua->request($req)->as_string;;
foreach my $line (@lines) {
	if ( $line =~ "rgGroupCol") {
		if ( $line =~ "colspan=\"6\"") {
			chomp $line;
			my @w=split(">", $line);
			my @u = split("<", @w[4]);
			my ($d, $m, $y) = split("-", @u[0]);
			$date = "$y-$m-$d";
		}
		if ( $line =~ "82px") {
			if ($hour < 25) {
				chomp $line;
				my @w=split(">", $line);
				my @u = split("<", @w[5]);
				push(@se1, @u[0]); 
				@u = split("<", @w[7]);
				push(@se2, @u[0]); 
				@u = split("<", @w[9]);
				push(@se3, @u[0]); 
				@u = split("<", @w[11]);
				push(@se4, @u[0]); 
				$hour++;
			}			
		}
	}
}



my $db = DBI->connect("DBI:mysql:$DBNAME:$DBHOST", $DBUSER, $DBPASS, { PrintError => 0, RaiseError => 0 });
if (!$db) { 
	undef $db; 
	die "Database error\n";
}


for (my $x=0;$x<24;$x++) {
	@se1[$x] =~ s/\,/./g;
 	@se2[$x] =~ s/\,/./g;
 	@se3[$x] =~ s/\,/./g;
 	@se4[$x] =~ s/\,/./g;
	my $sql = sprintf("INSERT INTO %s VALUES (null, TIMESTAMPADD(HOUR, %d, '%s'), %d, %d, %d, %d);", $DBTABLE, $x, $date, @se1[$x]*100, @se2[$x]*100, @se3[$x]*100, @se4[$x]*100); 
	$DBI::result = $db->prepare($sql);
    $DBI::result->execute();
    $DBI::result->finish();
}

$db->disconnect;
undef $db; 


# 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;
}