#! /usr/bin/perl

#  dataservice.cgi table=tp5min&vars=t,p&q=200905182000,200905182000/1,1

use CGI;
use DBI;

$table  = Scrub( CGI::param('table') );

$vars = CGI::param('vars');
@vars = split(',', $vars);
for ($i=0; $i<=$#vars; ++$i) {
    $vars[$i] = Scrub($vars[$i]);
}
$vars = join(",", @vars);

$q = CGI::param('q');
$q =~ s|^/||;
$q =~ s|/$||;

($daterange,$buffer) = split('/', $q);

($first,$last) = split(',', $daterange);
($bmin,$bmax)  = split(',', $buffer);

$first = ScrubDate($first);
$last  = ScrubDate($last);
$bmin  = Scrub($bmin);
$bmax  = Scrub($bmax);

Connect();

$sth = DoQuery("select   date_format(datetime,'%Y%m%d%H%i') as datetime,"
	       .       " $vars"
	       .  " from $table"
	       . " where datetime_index >= "
	       . "  ( select datetime_index-$bmin from $table "
	       . "           where datetime<='${first}' order by datetime_index desc limit 1 )"
	       . "   and datetime_index <= "
	       . "  ( select datetime_index+$bmax from $table "
	       . "           where datetime>='${last}' order by datetime_index asc limit 1 )"
    );

print("Content-type: text/xml\n\n");
print("<mugl><data><values>\n");
while ($hr=$sth->fetchrow_hashref()) {
    @values = ( $hr->{datetime} );
    foreach $var (@vars) {
	push(@values, $hr->{$var});
    }
    print join(",", @values), "\n";
}
print("</values></data></mugl>\n");

########################################################################

sub Connect {
    $dbh = DBI->connect($dbaddress,$dbuser,$dbpassword);
}

sub DoQuery {
    my $query = shift;
    my $sth = $dbh->prepare($query);
    if (!$sth) {
        my $errmsg = "SQL Error: " . $dbh->errstr . "\n" .
            " on query: \"$query\"\n";
        die $errmsg;
    }
    $sth->execute();
    return $sth;
}

sub ScrubDate {
    my $date = shift;
    my $year = substr($date, 0,  4);
    my $mon  = substr($date, 4,  2);
    my $day  = substr($date, 6,  2);
    my $hour = substr($date, 8,  2);
    my $min  = substr($date, 10, 2);
    return sprintf("%04d%02d%02d%02d%02d00",
		   $year, $mon, $day, $hour, $min);
}

sub Scrub {
    my $x = shift;
    $x =~ s/[^A-Za-z0-9_]//g;
    return $x;
}

BEGIN {
    my $dbh;
    $dbname     = "workshop";
    $dbaddress  = "DBI:mysql:workshop:localhost";
    $dbuser     = "workshop";
    $dbpassword = "workshop";
}