4 # Copyright (c) 2007,8,13,14,15 Martin Schulze <joey@infodrom.org>
6 # This program is free software; you can redistribute it and/or modify
7 # it under the terms of the GNU General Public License as published by
8 # the Free Software Foundation; either version 2 of the License, or
9 # (at your option) any later version.
11 # This program is distributed in the hope that it will be useful,
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 # GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License
17 # along with this program; if not, write to the Free Software
18 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
27 my $table = "stempel";
28 my $engine = "dbi:Pg:dbname=infocon";
30 my $dbh = DBI->connect($engine);
31 die "Access to database denied!\n" unless $dbh;
32 $dbh->do("SET DateStyle = 'ISO'");
38 return sprintf('%02d:%02d', $minutes/60, $minutes%60);
43 my $query = q{SELECT count(*) AS count FROM stempel WHERE stop IS NULL};
44 my $sth = $dbh->prepare ($query);
45 if ($sth && $sth->execute > 0) {
46 my $row = $sth->fetchrow_hashref;
47 return $row->{count} > 0 ? 1 : 0;
56 my $query = q{SELECT DISTINCT customer FROM stempel WHERE start > now() - interval '1 year' ORDER BY customer};
57 my $sth = $dbh->prepare ($query);
58 if ($sth && $sth->execute > 0) {
59 while ((my $row = $sth->fetchrow_hashref)) {
60 push @res, $row->{customer};
71 my $query = q{SELECT DISTINCT task FROM stempel WHERE start > now() - interval '1 month' AND customer = ? ORDER BY task};
72 my $sth = $dbh->prepare($query);
73 if ($sth && $sth->execute($customer) > 0) {
74 while ((my $row = $sth->fetchrow_hashref)) {
75 push @res, $row->{task};
83 my ($customer, $text, $line, $start) = @_;
85 # return () unless exists $answers->{category} && length $answers->{category};
87 my $sql = sprintf("SELECT DISTINCT task FROM stempel WHERE start > now() - interval '1 month' AND customer = '%s' AND task LIKE '%s%%' ORDER BY task",
91 my $sth = $dbh->prepare($sql);
94 while (my $row = $sth->fetchrow_hashref) {
95 $row->{task} = substr $row->{task}, $start if $start;
96 push @complete, $row->{task};
104 my $term = new Term::ReadLine '';
105 my $customers = customerlist;
107 my $attribs = $term->Attribs;
108 $attribs->{completion_entry_function} = $attribs->{list_completion_function};
109 $attribs->{completion_word} = $customers;
111 printf "[%s]\n", join (', ', @$customers);
113 my $customer = $term->readline ('Kunde: ');
115 if (defined $customer && length($customer) > 0) {
116 $customer =~ s/\s*$//;
118 my $tasks = tasklist $customer;
120 $term->addhistory($_) foreach (@$tasks);
121 $attribs->{completion_word} = $tasks;
122 $attribs->{completion_entry_function} = undef;
123 $term->{completion_function} = sub {return complete_customer $customer, @_};
125 my $task = $term->readline ('Aufgabe: ');
126 return unless length $task;
127 return if $task eq 'q';
130 printf " %s\n", $_ foreach (@$tasks);
135 my $query = q{INSERT INTO stempel (start,customer,task) VALUES('now()',?,?)};
136 my $sth = $dbh->prepare($query);
137 $sth->execute($customer, $task);
147 return 15 if $min > 0 && $min < 15;
149 return $min - $min%15 if ($min%15 < 3);
151 return $min + 15-$min%15;
156 my ($sh, $sm, $eh, $em) = @_;
159 return ($eh-$sh)*60 + $em-$sm;
161 return ($eh-$sh)*60 - ($sm-$em);
167 my ($d_sec,$d_min,$d_hour,$d_mday,$d_mon,$d_year,$d_wday,$d_isdst) = localtime();
169 my $query = q{SELECT oid,start FROM stempel WHERE stop IS NULL};
170 my $sth = $dbh->prepare ($query);
171 if ($sth && $sth->execute > 0) {
172 while ((my $row = $sth->fetchrow_hashref)) {
173 my @arr = split(/ /, $row->{start});
174 my @d = split(/-/, $arr[0]);
176 if ($d[0] != $d_year+1900 || $d[1] != $d_mon+1 || $d[2] != $d_mday) {
177 printf "Task not started today, aborting.\n";
181 my @t = split(/:/, $arr[1]);
182 my $int = quarter(hdiff($t[0], $t[1], $d_hour, $d_min));
184 $query = sprintf('UPDATE stempel SET stop=now(),time=%d WHERE oid = %d',
187 printf "Wrote %s.\n", min2hour($int);
195 my $query = q{DELETE FROM stempel WHERE stop IS NULL};
203 my $sql = q{SELECT max(id) AS max_id FROM stempel};
204 my $sth = $dbh->prepare($sql);
205 if ($sth && $sth->execute > 0) {
206 my $row = $sth->fetchrow_hashref;
207 $sql = sprintf("UPDATE stempel SET stop = NULL, time = NULL WHERE id = %d", $row->{max_id});
217 my ($d_sec,$d_min,$d_hour,$d_mday,$d_mon,$d_year,$d_wday,$d_isdst) = localtime();
218 my $query = q{SELECT customer,start,task FROM stempel WHERE time IS NULL ORDER BY start,customer};
220 my $sth = $dbh->prepare ($query);
221 if ($sth && $sth->execute > 0) {
222 while ((my $row = $sth->fetchrow_hashref)) {
223 my @arr = split(/ /, $row->{start});
225 my @d = split(/-/, $day);
227 if ($d[0] != $d_year+1900 || $d[1] != $d_mon+1 || $d[2] != $d_mday) {
228 printf "Task not started today.\n";
232 my @t = split(/:/, $arr[1]);
233 my $time = sprintf('%02d:%02d', $t[0], $t[1]);
234 my $int = quarter(hdiff($t[0], $t[1], $d_hour, $d_min));
236 printf "%-15s %s %s (%s) %s\n", $row->{customer}, $day, $time, min2hour($int), $row->{task};
245 my ($d_sec,$d_min,$d_hour,$d_mday,$d_mon,$d_year,$d_wday,$d_isdst) = localtime();
246 my $query = q{SELECT start,customer,time,task FROM stempel WHERE time IS NOT NULL };
248 if ($month =~ /^(\d{4})-?(\d\d?)$/) {
249 my $pivot = $1 . '-' . $2 . '-01';
250 $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 month' ";
251 } elsif ($month =~ /^(\d{4})$/) {
252 my $pivot = $1 . '-01-01';
253 $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 year' ";
254 } elsif ($month =~ /^(\d\d?)$/) {
255 my $pivot = $d_year+1900 . '-' . $1 . '-01';
256 $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 month' ";
257 } elsif ($month !~ /^all$/) {
258 my $pivot = sprintf('%04d-%02d-01', $d_year+1900, $d_mon+1);
259 $query .= "AND start >= '$pivot' AND start <= '$pivot'::date + interval '1 month' ";
261 $query .= "ORDER BY customer,start";
263 my $sth = $dbh->prepare ($query);
265 if ($sth && $sth->execute > 0) {
266 while ((my $row = $sth->fetchrow_hashref)) {
267 if (defined $row->{time}) {
268 my $day = (split(/ /, $row->{start}))[0];
269 my $time = min2hour $row->{time};
270 printf "%-15s %s %s %s\n", $row->{customer}, $day, $time, $row->{task};
289 my ($d_sec,$d_min,$d_hour,$d_mday,$d_mon,$d_year,$d_wday,$d_isdst) = localtime();
291 return unless is_open;
293 my $query = sprintf("UPDATE stempel SET stop=start + interval '%d minutes',time=%d WHERE stop IS NULL",
294 $delta, quarter($delta));
302 return unless is_open;
304 my $query = sprintf("UPDATE stempel SET start = start - interval '%d minutes' WHERE stop IS NULL", $minutes);
310 return unless is_open;
314 my $term = new Term::ReadLine '';
316 my $sql = q{SELECT customer,task FROM stempel WHERE stop IS NULL};
317 my $sth = $dbh->prepare($sql);
319 my $row = $sth->fetchrow_hashref;
321 my $tasks = tasklist $row->{'customer'};
322 $term->addhistory($_) foreach (@$tasks);
324 my $attribs = $term->Attribs;
325 $attribs->{completion_word} = $tasks;
326 $term->{completion_function} = sub {return complete_customer $row->{'customer'}, @_};
328 my $task = $term->readline ('Aufgabe: ');
330 if (length($task) > 0) {
331 my $query = q{UPDATE stempel SET task=? WHERE stop IS NULL};
332 my $sth = $dbh->prepare($query);
333 $sth->execute($task);
341 stempel Copyright (c) 2007,8 Martin Schulze <joey\@infodrom.org>
342 --list [month] list month [default=current|all]
343 --back n move start of current task back by n minutes
345 --close time close open task
347 --end|-d terminate task
348 --delete delete open task
349 --reopen re-open last task
350 --task alter task content
355 my $opt_close = undef;
356 my $opt_list = undef;
357 my $opt_back = undef;
358 my %options = ('list:s' => \$opt_list,
359 'back=i' => \$opt_back,
360 'open' => \&list_open,
362 'close=i' => \$opt_close,
363 'delete' => \&delete_task,
364 'reopen' => \&reopen_task,
365 'task|t' => \&alter_task,
366 'terminate|end|d' => \&close_task,
371 late_close_task $opt_close;
372 } elsif (defined $opt_list) {
374 } elsif (defined $opt_back) {