- # my $query = q{SELECT customer,sum(time) FROM stempel GROUP BY customer ORDER BY customer};
- my $query = q{SELECT start,customer,time,task FROM stempel WHERE time IS NOT NULL ORDER BY customer,start};
+ my $month = shift;
+ my ($d_sec,$d_min,$d_hour,$d_mday,$d_mon,$d_year,$d_wday,$d_isdst) = localtime();
+ my $query = q{SELECT start,customer,time,task FROM stempel WHERE time IS NOT NULL };
+
+ if ($month =~ /^(\d{4})-?(\d\d?)$/) {
+ my $pivot = $1 . '-' . $2 . '-01';
+ $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 month' ";
+ } elsif ($month =~ /^(\d{4})$/) {
+ my $pivot = $1 . '-01-01';
+ $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 year' ";
+ } elsif ($month =~ /^(\d\d?)$/) {
+ my $pivot = $d_year+1900 . '-' . $1 . '-01';
+ $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 month' ";
+ } elsif ($month !~ /^all$/) {
+ my $pivot = sprintf('%04d-%02d-01', $d_year+1900, $d_mon+1);
+ $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 month' ";
+ }
+ $query .= "ORDER BY customer,start";