#!/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" => "'ctl00\$FullRegion\$npsGridView\$lnkExport'" , "__EVENTARGUMENT.value" => "''", "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; }