Zeiterfassung: Erweiterung f. Datum und Dauer: DB-Upgrade/Rose; Berichtsanzeige

diff --git a/SL/Controller/TimeRecording.pm b/SL/Controller/TimeRecording.pm
index e852ba1..8215122 100644 (file)
--- 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 e1e9b84..6992aa1 100644 (file)
--- 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 4fca657..0ab79a5 100644 (file)
--- 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 970fa87..62143b3 100644 (file)
--- 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 (file)
index 0000000..98404e1
--- /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 f63d370..4825999 100644 (file)
--- a/templates/webpages/time_recording/_filter.html
+++ b/templates/webpages/time_recording/_filter.html
@@ -11,12 +11,12 @@
 <a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Hide Filter' | $T8 %]</a>
  <table id='filter_table'>
   <tr>
-   <th align="right">[% 'Start' | $T8 %] [% 'From Date' | $T8 %]</th>
-   <td>[% L.date_tag('filter.start_time:date::ge', filter.start_time_date__ge) %]</td>
+   <th align="right">[% 'Date' | $T8 %] [% 'From Date' | $T8 %]</th>
+   <td>[% L.date_tag('filter.date:date::ge', filter.date_date__ge) %]</td>
   </tr>
   <tr>
-   <th align="right">[% 'Start' | $T8 %] [% 'To Date' | $T8 %]</th>
-   <td>[% L.date_tag('filter.start_time:date::le', filter.start_time_date__le) %]</td>
+   <th align="right">[% 'Date' | $T8 %] [% 'To Date' | $T8 %]</th>
+   <td>[% L.date_tag('filter.date:date::le', filter.date_date__le) %]</td>
   </tr>
   <tr>
     <th align="right">[% 'Customer' | $T8 %]</th>