From 22f2c3e86aeea63b9e25b6ba249ca9062ae337f8 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Wed, 28 Apr 2021 12:35:17 +0200 Subject: [PATCH] Zeiterfassung: Erweiterung f. Datum und Dauer: DB-Upgrade/Rose; Berichtsanzeige --- SL/Controller/TimeRecording.pm | 9 +++-- SL/DB/Manager/TimeRecording.pm | 11 +++++- SL/DB/MetaSetup/TimeRecording.pm | 4 +- SL/DB/TimeRecording.pm | 10 ----- .../time_recordings_date_duration.sql | 38 +++++++++++++++++++ .../webpages/time_recording/_filter.html | 8 ++-- 6 files changed, 60 insertions(+), 20 deletions(-) create mode 100644 sql/Pg-upgrade2/time_recordings_date_duration.sql diff --git a/SL/Controller/TimeRecording.pm b/SL/Controller/TimeRecording.pm index e852ba1a9..821512252 100644 --- a/SL/Controller/TimeRecording.pm +++ b/SL/Controller/TimeRecording.pm @@ -29,6 +29,7 @@ __PACKAGE__->run_before('check_auth'); __PACKAGE__->run_before('check_auth_edit', only => [ qw(edit save delete) ]); my %sort_columns = ( + date => t8('Date'), start_time => t8('Start'), end_time => t8('End'), customer => t8('Customer'), @@ -48,8 +49,8 @@ sub action_list { my ($self, %params) = @_; $::form->{filter} //= { - staff_member_id => SL::DB::Manager::Employee->current->id, - "start_time:date::ge" => DateTime->now_local->add(weeks => -2)->to_kivitendo, + staff_member_id => SL::DB::Manager::Employee->current->id, + "date:date::ge" => DateTime->today_local->add(weeks => -2)->to_kivitendo, }; $self->setup_list_action_bar; @@ -195,9 +196,11 @@ sub prepare_report { my $report = SL::ReportGenerator->new(\%::myconfig, $::form); $self->{report} = $report; - my @columns = qw(start_time end_time customer part project description staff_member duration booked); + my @columns = qw(date start_time end_time customer part project description staff_member duration booked); my %column_defs = ( + date => { text => t8('Date'), sub => sub { $_[0]->date_as_date }, + obj_link => sub { $self->url_for(action => 'edit', 'id' => $_[0]->id, callback => $self->models->get_callback) } }, start_time => { text => t8('Start'), sub => sub { $_[0]->start_time_as_timestamp }, obj_link => sub { $self->url_for(action => 'edit', 'id' => $_[0]->id, callback => $self->models->get_callback) } }, end_time => { text => t8('End'), sub => sub { $_[0]->end_time_as_timestamp }, diff --git a/SL/DB/Manager/TimeRecording.pm b/SL/DB/Manager/TimeRecording.pm index e1e9b84e1..6992aa10d 100644 --- a/SL/DB/Manager/TimeRecording.pm +++ b/SL/DB/Manager/TimeRecording.pm @@ -16,8 +16,15 @@ __PACKAGE__->make_manager_methods; sub _sort_spec { return ( default => [ 'start_time', 1 ], - columns => { SIMPLE => 'ALL' , - customer => [ 'lower(customer.name)', ], + nulls => { + date => 'FIRST', + start_time => 'FIRST', + end_time => 'FIRST', + }, + columns => { SIMPLE => 'ALL' , + start_time => [ 'date', 'start_time' ], + end_time => [ 'date', 'end_time' ], + customer => [ 'lower(customer.name)', 'date','start_time'], } ); } diff --git a/SL/DB/MetaSetup/TimeRecording.pm b/SL/DB/MetaSetup/TimeRecording.pm index 4fca65738..0ab79a551 100644 --- a/SL/DB/MetaSetup/TimeRecording.pm +++ b/SL/DB/MetaSetup/TimeRecording.pm @@ -11,7 +11,9 @@ __PACKAGE__->meta->table('time_recordings'); __PACKAGE__->meta->columns( booked => { type => 'boolean', default => 'false' }, customer_id => { type => 'integer', not_null => 1 }, + date => { type => 'date', not_null => 1 }, description => { type => 'text', not_null => 1 }, + duration => { type => 'integer' }, employee_id => { type => 'integer', not_null => 1 }, end_time => { type => 'timestamp' }, id => { type => 'serial', not_null => 1 }, @@ -21,7 +23,7 @@ __PACKAGE__->meta->columns( payroll => { type => 'boolean', default => 'false' }, project_id => { type => 'integer' }, staff_member_id => { type => 'integer', not_null => 1 }, - start_time => { type => 'timestamp', not_null => 1 }, + start_time => { type => 'timestamp' }, ); __PACKAGE__->meta->primary_key_columns([ 'id' ]); diff --git a/SL/DB/TimeRecording.pm b/SL/DB/TimeRecording.pm index 970fa8738..62143b309 100644 --- a/SL/DB/TimeRecording.pm +++ b/SL/DB/TimeRecording.pm @@ -119,14 +119,4 @@ sub displayable_times { return ($self->start_time_as_timestamp||$ph) . ' - ' . ($self->end_time_as_timestamp||$ph); } -sub duration { - my ($self) = @_; - - if ($self->start_time && $self->end_time) { - return ($self->end_time->subtract_datetime_absolute($self->start_time))->seconds/60.0; - } else { - return; - } -} - 1; diff --git a/sql/Pg-upgrade2/time_recordings_date_duration.sql b/sql/Pg-upgrade2/time_recordings_date_duration.sql new file mode 100644 index 000000000..98404e162 --- /dev/null +++ b/sql/Pg-upgrade2/time_recordings_date_duration.sql @@ -0,0 +1,38 @@ +-- @tag: time_recordings_date_duration +-- @description: Erweiterung Zeiterfassung um Datum und Dauer +-- @depends: time_recordings2 + +ALTER TABLE time_recordings ADD COLUMN date DATE; +ALTER TABLE time_recordings ADD COLUMN duration INTEGER; + +UPDATE time_recordings SET date = start_time::DATE; +ALTER TABLE time_recordings ALTER COLUMN start_time DROP NOT NULL; +ALTER TABLE time_recordings ALTER COLUMN date SET NOT NULL; + +UPDATE time_recordings SET duration = EXTRACT(EPOCH FROM (end_time - start_time))/60; + +-- trigger to set date from start_time +CREATE OR REPLACE FUNCTION time_recordings_set_date_trigger() +RETURNS TRIGGER AS $$ + BEGIN + IF NEW.start_time IS NOT NULL THEN + NEW.date = NEW.start_time::DATE; + END IF; + RETURN NEW; + END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER time_recordings_set_date BEFORE INSERT OR UPDATE ON time_recordings FOR EACH ROW EXECUTE PROCEDURE time_recordings_set_date_trigger(); + +-- trigger to set duration from start_time and end_time +CREATE OR REPLACE FUNCTION time_recordings_set_duration_trigger() +RETURNS TRIGGER AS $$ + BEGIN + IF NEW.start_time IS NOT NULL AND NEW.end_time IS NOT NULL THEN + NEW.duration = EXTRACT(EPOCH FROM (NEW.end_time - NEW.start_time))/60; + END IF; + RETURN NEW; + END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER time_recordings_set_duration BEFORE INSERT OR UPDATE ON time_recordings FOR EACH ROW EXECUTE PROCEDURE time_recordings_set_duration_trigger(); diff --git a/templates/webpages/time_recording/_filter.html b/templates/webpages/time_recording/_filter.html index f63d370e9..482599987 100644 --- a/templates/webpages/time_recording/_filter.html +++ b/templates/webpages/time_recording/_filter.html @@ -11,12 +11,12 @@ [% 'Hide Filter' | $T8 %] - - + + - - + + -- 2.20.1
[% 'Start' | $T8 %] [% 'From Date' | $T8 %][% L.date_tag('filter.start_time:date::ge', filter.start_time_date__ge) %][% 'Date' | $T8 %] [% 'From Date' | $T8 %][% L.date_tag('filter.date:date::ge', filter.date_date__ge) %]
[% 'Start' | $T8 %] [% 'To Date' | $T8 %][% L.date_tag('filter.start_time:date::le', filter.start_time_date__le) %][% 'Date' | $T8 %] [% 'To Date' | $T8 %][% L.date_tag('filter.date:date::le', filter.date_date__le) %]
[% 'Customer' | $T8 %]