From e96f8f9daa1db38bb03e70542490beeb1d010116 Mon Sep 17 00:00:00 2001
From: kilian <ksaffran@dks.lu>
Date: Mon, 31 Aug 2020 18:58:04 +0200
Subject: [PATCH] v20200831

---
 website/app/lib/POT/Period.pm                 |  67 ++++---
 website/app/lib/POT/Report.pm                 | 189 ++++++++++++++----
 website/app/lib/POT/Staff.pm                  |   2 +-
 website/app/lib/dksdb.pm                      |   8 +-
 .../tmpl/module/periods/staffperiodweeks.js   |   2 +-
 website/app/tmpl/module/staff/staffperiods.js |   7 +-
 6 files changed, 194 insertions(+), 81 deletions(-)

diff --git a/website/app/lib/POT/Period.pm b/website/app/lib/POT/Period.pm
index 9abb9fdb..70e9c107 100644
--- a/website/app/lib/POT/Period.pm
+++ b/website/app/lib/POT/Period.pm
@@ -78,8 +78,8 @@ sub getStaffPeriods(){
     to_char(srp.payedhours, 'HH24:MI'::text) AS payedhours,
     to_char(srp.transferedhourscalc, 'HH24:MI'::text) AS transferedhourscalc,
     to_char(srp.transferedhours, 'HH24:MI'::text) AS transferedhours,
-    '+' || suppvacancysunwork as suppvacancysunwork,
-    '+' || suppvacancy44hours as suppvacancy44hours
+    case when suppvacancysunwork is not null and suppvacancysunwork > 0 then '+' || suppvacancysunwork  else  null end as suppvacancysunwork,
+    case when suppvacancy44hours is not null and suppvacancy44hours > 0 then '+' || suppvacancy44hours  else null end as suppvacancy44hours
    FROM ".$schema.".staffreportperiod srp
      LEFT JOIN ".$schema.".staff st ON srp.id_staff = st.id
      LEFT JOIN ".$schema.".reportperiod rp ON srp.id_reportperiod = rp.id
@@ -186,7 +186,7 @@ sub getPeriodDays(){
             to_char(pd.pauseend2::interval, 'HH24:MI') AS pauseend2,
             COALESCE((to_char(pd.pausestart1::interval, 'HH24:MI') || '-') || to_char(pd.pauseend1::interval, 'HH24:MI'), '') || COALESCE(((CASE WHEN pd.pausestart1 IS NOT NULL THEN '<br/>' ELSE '' END || to_char(pd.pausestart2::interval, 'HH24:MI')) || '-') || to_char(pd.pauseend2::interval, 'HH24:MI'), '') AS dsppausedata,
             COALESCE((to_char(pd.timestart1::interval, 'HH24:MI') || '-') || to_char(pd.timeend1::interval, 'HH24:MI'), '') || COALESCE(((CASE WHEN pd.timestart1 IS NOT NULL THEN '<br/>' ELSE '' END || to_char(pd.timestart2::interval, 'HH24:MI')) || '-') || to_char(pd.timeend2::interval, 'HH24:MI'), '') AS dspworkdata,
-            CASE WHEN freehours.freehours < '00:00:00'::interval THEN NULL ELSE to_char(freehours.freehours, 'HH24:MI') END AS freehoursafter 
+            CASE WHEN max(freehours.freehours) < '00:00:00'::interval THEN NULL ELSE to_char(max(freehours.freehours), 'HH24:MI') END AS freehoursafter 
             FROM ".$schema.".staffreportperioddays pd
              JOIN ".$schema.".staff st ON pd.id_staff = st.id and st.id='".$id_staff."' and st.isdeleted is null
              LEFT JOIN ( SELECT yy.id_staff, yy.ndaydate AS daydate,yy.ndaydate + yy.ntimeend AS tmfrom, yy.daydate + yy.timestart AS tmto,
@@ -202,7 +202,9 @@ sub getPeriodDays(){
                                   WHERE sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone
                                   ORDER BY sr.id_staff, sr.daydate) xx_1) yy) freehours ON pd.id_staff = freehours.id_staff AND freehours.daydate = pd.daydate
           WHERE pd.daydate between date('".$date_start."') and date('".$date_end."')
+          group by pd.id
           ORDER BY pd.id_staff, pd.daydate;";
+  #print STDERR "PDAys:".$sql."\n===========\n";
   my $ret = $self->{db}->querysorted($sql);
   return $ret;
 }
@@ -278,7 +280,7 @@ sub updatePeriodDaySums(){
   }
   #case when payedpause is null then  (preds.dt1+preds.dt2) else  (preds.dt1+preds.dt2)-(preds.pdt1+preds.pdt2) end as worktime,
   my $sql = "update ".$schema.".staffreportperioddays spds set
-  workhours=ds.worktime, timepause=case when ds.timepause::interval > '00:00:00' then ds.timepause else null end, dayhours=ds.worktime + ds.vachours, interruptionhours = ds.interruption , contracthours=case when ds.worktime + ds.vachours > '08:00:00'::time then '08:00:00'::time else ds.worktime + ds.vachours end 
+  workhours=ds.worktime, timepause=case when ds.timepause::interval > '00:00:00' then ds.timepause else null end, dayhours=(ds.worktime + ds.vachours)::interval, interruptionhours = ds.interruption , contracthours=case when ds.worktime + ds.vachours > '08:00:00'::time then '08:00:00'::time else ds.worktime + ds.vachours end 
   from (
     select preds.id,case when preds.recuperationhours is null then '00:00:00'::time else preds.recuperationhours end as recup,
     case when preds.vacancyhours is null then '00:00:00'::time else preds.vacancyhours end as vachours,
@@ -305,33 +307,36 @@ sub updateStaffPeriod(){
   my $id_staff = shift;
   my $dayselect = "";
   my $prd = $self->{db}->query("select * from ".$schema.".reportperiod where id='".$id_period."';");
-  my $staffprdprev = $self->{db}->query("select lag(id_reportperiod,1) over (order by startdate,enddate) as prevperiod,id from ( 
+  my $staffprdprev = $self->{db}->query("select prevperiod from (select lag(id_reportperiod,1) over (order by startdate,enddate) as prevperiod,id from ( 
 select srp.id_reportperiod ,startdate,enddate,rp.id from ".$schema.".staffreportperiod srp
 join ".$schema.".reportperiod rp on (rp.id=srp.id_reportperiod)
 where srp.id_staff='".$id_staff."' order by rp.startdate,rp.enddate)
-sper where id='".$id_period."'; ");
-  my $prevprddata={statussuncount => 0, status44hcount =>0};
-  
+sper ) prp where id='".$id_period."'; ");
+  my $prevprddata={};
+  #print STDERR Dumper($staffprdprev);
   if ($staffprdprev->{prevperiod} ne ''){
-    $prevprddata = $self->{db}->query("select statussuncount,status44hcount from ".$schema.".staffreportperiod where id_reportperiod='".$staffprdprev->{prevperiod}."' and id_staff='".$id_staff."';");
+    my $prevsql = "select * from ".$schema.".staffreportperiod where id_reportperiod='".$staffprdprev->{prevperiod}."' and id_staff='".$id_staff."';";
+    #print STDERR "PrevSQL:".$prevsql."\n==========\n";
+    $prevprddata = $self->{db}->query($prevsql);
   }
+  #print STDERR 
   #every 20 sundays in a year worked  => +2 vacancy days => calculated by period; statuscount => start/stop value by period to count
-  my $statussuncount = $self->{db}->query("select max(rowx) as statussuncount, sum(modx) as suppvacancysunwork from (
+  my $sqlsun = "select max(rowx) as statussuncount, sum(modx) as suppvacancysunwork from (
 select daydate,rowx, case when mod(rowx,20) = 0 then 2 else null end as modx from (
 select daydate,".$prevprddata->{statussuncount}."+row_number() over (order by daydate) as rowx  from ".$schema.".staffreportperioddays 
 where daydate between date('".$prd->{startdate}."') and date('".$prd->{enddate}."') 
 and date_part('isodow',daydate)::int4 = 7 and workhours > '00:00:00'::time
 and id_staff='".$id_staff."' order by daydate
-) xsunday) sunresult;");
+) xsunday) sunresult;";
+
+  my $statussuncount = $self->{db}->query($sqlsun);
 
   #every 8 times not 44hours between workstart/workstop => +1 vacancy day => calculated by period; statuscount => start/stop value by period to count
-  my $status44count = $self->{db}->query("select sum(sum44) as suppvacancy44hours, max(rowx)-max(sumx) as status44count from ( 
-select *,case when mod(sumx,8) = 0 then 1 else 0 end as sum44 from ( 
-select *,sum(case when maxweek < '44:00:00'::interval then 1 else 0 end) over (order by rowx) as sumx from (
-  SELECT ".$prevprddata->{status44hcount}."+row_number() over (order by date_trunc('week',yy.daydate)) as rowx,date_trunc('week',yy.daydate) as weekstart,
-  --yy.ndaydate + yy.ntimeend AS tmfrom, yy.daydate + yy.timestart AS tmto,
-                    --yy.daydate + yy.timestart - (yy.ndaydate + yy.ntimeend) AS realinterval,
-                    max( date_part('epoch', yy.daydate + yy.timestart - (yy.ndaydate + yy.ntimeend)) * '00:00:01'::interval ) AS maxweek
+  my $sql44 = "select *,case when week44s >= 8 then (week44s/8)::int else null end as plusdays from (
+select date(date_trunc('week',max(maxdate)) + interval '7 days') as maxdate,max(sumx) as week44s,mod(max(sumx),8) as weeksrest from (
+select weekstart,".((exists($prevprddata->{status44hcount}) && $prevprddata->{status44hcount} != "")?$prevprddata->{status44hcount}."+":"")."sum(case when sum(cnt44) > 0 then 0 else 1 end) over (order by weekstart) as sumx, max(daydate) as maxdate from (
+  SELECT date(date_trunc('week',yy.daydate)) as weekstart,daydate,
+                   case when (date_part('epoch', yy.daydate + yy.timestart - (yy.ndaydate + yy.ntimeend)) * '00:00:01'::interval) >= '44:00:00'::interval then 1 else 0 end as cnt44
                    FROM ( SELECT lag(xx_1.daydate, 1) OVER (ORDER BY xx_1.id_staff, xx_1.daydate) AS ndaydate,
                             lag(xx_1.timeend, 1) OVER (ORDER BY xx_1.id_staff, xx_1.daydate) AS ntimeend,
                             xx_1.id_staff, xx_1.daydate, xx_1.timestart, xx_1.timeend
@@ -339,10 +344,15 @@ select *,sum(case when maxweek < '44:00:00'::interval then 1 else 0 end) over (o
             CASE WHEN sr.timestart1 IS NOT NULL THEN sr.timestart1 ELSE sr.timestart2 END AS timestart,
             CASE WHEN sr.timestart2 IS NOT NULL THEN sr.timeend2 ELSE sr.timeend1 END AS timeend
                                    FROM ".$schema.".staffreportperioddays sr
-                                  WHERE sr.id_staff='".$id_staff."' and sr.daydate between date('".$prd->{startdate}."') and date('".$prd->{enddate}."') 
+                                  WHERE sr.id_staff='".$id_staff."' and sr.daydate between  date('".((exists($prevprddata->{last44date}) && $prevprddata->{last44date} != "")?$prevprddata->{last44date}:$prd->{startdate})."')  and date(date_trunc('week',date('".$prd->{enddate}."')) - interval '1 day')
                                   and sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone
-                                  ORDER BY sr.id_staff, sr.daydate) xx_1) yy group by weekstart order by weekstart) aa  order by weekstart) bb ) cc ;");
+                                  ORDER BY sr.id_staff, sr.daydate) xx_1) yy order by yy.daydate,weekstart
+                                 ) aa group by aa.weekstart order by weekstart
+                                 ) bb)cc;";
+
 
+  my $status44count = $self->{db}->query($sql44);
+  #print STDERR "SQLSun:".$sqlsun."\n";
   my $sql = "select id from ".$schema.".staffreportperioddays where id_staff='".$id_staff."' and daydate between date(date_trunc('week',date('".$prd->{startdate}."'))) and date(date_trunc('week',date('".$prd->{enddate}."')) + interval '6 days')";
   $self->updatePeriodDaySums($schema,$sql);
   $sql = "update ".$schema.".staffreportperiod rpd set
@@ -351,13 +361,12 @@ select *,sum(case when maxweek < '44:00:00'::interval then 1 else 0 end) over (o
   vacancyhours=csp.vacancyhours,
   totalhours=csp.totalhours,
   recuperationhours=csp.recuperationhours,";
-  if ((defined($statussuncount) && $staffprdprev->{prevperiod} ne '')){
-    $sql .= $statussuncount->{statussuncount}." as statussuncount,".(($statussuncount->{suppvacancysunwork} eq "")?'null':$statussuncount->{suppvacancysunwork})." as suppvacancysunwork,";
-  }
-  if ((defined($status44count) && $staffprdprev->{prevperiod} ne '')){
-    $sql .= $status44count->{status44hcount}." as status44hcount,".(($status44count->{suppvacancy44hours} eq "")?'null':$status44count->{suppvacancy44hours})." as suppvacancy44hours,";
-  }
-  
+  #if ((defined($statussuncount) && $staffprdprev->{prevperiod} ne '')){
+    $sql .= "statussuncount=".(($statussuncount->{statussuncount} eq "")?'null':$statussuncount->{statussuncount}).",suppvacancysunwork=".(($statussuncount->{suppvacancysunwork} eq "")?'null':$statussuncount->{suppvacancysunwork}).",";
+  #}
+  $sql .= "suppvacancy44hours=".(($status44count->{plusdays} gt "0")?$status44count->{plusdays}:'null').",";
+  $sql .= "status44hcount=".(($status44count->{weeksrest} eq "")?'null':$status44count->{weeksrest}).",";
+  $sql .= "last44date=".(($status44count->{maxdate} eq "")?'null':"date('".$status44count->{maxdate}."')").",";
   $sql .= "hoursdiff=csp.diffhours
   FROM(
   select contracthours,workhours,vacancyhours,totalhours,recuperationhours, totalhours-contracthours-recuperationhours-payedhours+transferedhourscalc as diffhours,id from (
@@ -374,7 +383,7 @@ select *,sum(case when maxweek < '44:00:00'::interval then 1 else 0 end) over (o
   where pd.id_staff='".$id_staff."' and  pd.daydate between date('".$prd->{startdate}."') and date('".$prd->{enddate}."')
   group by dp.id,dp.payedhours,dp.transferedhourscalc
   ) rp) csp where csp.id=rpd.id;";
-#  print STDERR "\n==STAFF CONTRACT===\n".$sql."\n=======\n"; 
+  #print STDERR "\n==STAFF CONTRACT===\n".$sql."\n=======\n"; 
   $self->{db}->exec($sql);
   return 1;
 }
@@ -447,7 +456,7 @@ SELECT date_trunc('day', dd)::date as daydate,'".$id_staff."' as id_staff, null
     
   }
   my $delstaffreportssql = "delete from ".$schema.".staffreportperiod sp where id_staff='".$id_staff."' and id_reportperiod not in (
-select rp.id from portanova.reportperiod rp left join portanova.staffreportperioddays pd on (pd.daydate between rp.startdate and rp.enddate)
+select rp.id from ".$schema.".reportperiod rp left join ".$schema.".staffreportperioddays pd on (pd.daydate between rp.startdate and rp.enddate)
 where pd.id_staff='".$id_staff."' group by rp.id);";
 #  print STDERR $delstaffreportssql."\n======\n";
   $self->{db}->exec($delstaffreportssql);
diff --git a/website/app/lib/POT/Report.pm b/website/app/lib/POT/Report.pm
index f3c954a2..40ffe6f8 100644
--- a/website/app/lib/POT/Report.pm
+++ b/website/app/lib/POT/Report.pm
@@ -156,25 +156,25 @@ sub printContent(){
 div { font-size: 12pt; }
 table {  font-size: 8pt; border-spacing: 0; border-collapse: collapse; border: none; width: 100%; }
 th { border: 1px solid black; }
-td { text-align: center; vertical-align: top; border: 1px solid black; }
+td { text-align: center; border: 1px solid black; }
 .right{ text-align: right; }
 table.tbinline { border-collapse: collapse; }
-table.tbinline td {  border: 0.5px solid #969696;  }
+/*table.tbinline td {  border: 0.5px solid #969696;  }*/
 table.tbinline tr:first-child td { border-top: 0; }
 table.tbinline tr td:first-child { border-left: 0; }
 table.tbinline tr:last-child td { border-bottom: 0; }
 table.tbinline tr td:last-child { border-right: 0; }
 td.datavalues div { font-size: 8pt; }
 
-table { page-break-inside:auto }
-tr    { page-break-inside:avoid; page-break-after:auto }
+table { page-break-inside:avoid!important; }
+tr    { page-break-inside:avoid!important; page-break-after:avoid!important; }
 thead { display:table-header-group }
 tfoot { display:table-footer-group }
-.page-break  { display: block; page-break-before: always; }
+.page-break  { display: inline-block; page-break-before: always!important; }
   </style>
 </head>
 <body>";
-           #print CT Dumper($data);
+
 if (exists($data->{staff_exclude})){
   my $stexcl = "";
   if (scalar(@{$data->{staff_exclude}}) > 0){
@@ -191,9 +191,9 @@ if (exists($data->{staff_exclude})){
     to_char(srp.transferedhourscalc, 'HH24:MI'::text) AS transferedhourscalc,
     '+' || suppvacancysunwork as suppvacancysunwork,
     '+' || suppvacancy44hours as suppvacancy44hours
-   from portanova.staffreportperiod srp 
-join portanova.reportperiod rp on (srp.id_reportperiod =rp.id)
-join portanova.staff st on (srp.id_staff=st.id)
+   from ".$schema.".staffreportperiod srp 
+join ".$schema.".reportperiod rp on (srp.id_reportperiod =rp.id)
+join ".$schema.".staff st on (srp.id_staff=st.id)
 where st.isdeleted is null and srp.id_reportperiod ='".$self->{db}->securetext($data->{id_reportperiod})."' ".$stexcl."
 order by rp.startdate,rp.enddate,st.surname,st.prename;";
   
@@ -209,7 +209,8 @@ order by rp.startdate,rp.enddate,st.surname,st.prename;";
   print CT "</body></html>";
 } elsif (exists($data->{id_staff})){
   my $rpincl = "";
-  if ($data->{id_periods} ne ""){
+  #print STDERR "prds:".Dumper($data->{id_periods});
+  if (scalar(@{$data->{id_periods}}) > 0){
     $rpincl = " and srp.id_reportperiod in ('".join("','",@{$data->{id_periods}})."')";
   }
   my $sql = "select rp.id,rp.startdate,rp.enddate,st.prename,st.surname,to_char(rp.startdate,'DD.MM.YYYY') as dspstartdate,to_char(rp.enddate,'DD.MM.YYYY') as dspenddate, srp.id_staff, 
@@ -223,10 +224,10 @@ order by rp.startdate,rp.enddate,st.surname,st.prename;";
     to_char(srp.transferedhourscalc, 'HH24:MI'::text) AS transferedhourscalc,
     '+' || suppvacancysunwork as suppvacancysunwork,
     '+' || suppvacancy44hours as suppvacancy44hours
-   from portanova.staffreportperiod srp 
-join portanova.reportperiod rp on (srp.id_reportperiod =rp.id)
-join portanova.staff st on (srp.id_staff=st.id)
-where st.isdeleted is null and id_staff='".$data->{id_staff}."' and srp.id_reportperiod ='".$self->{db}->securetext($data->{id_reportperiod})."' ".$rpincl."
+   from ".$schema.".staffreportperiod srp 
+join ".$schema.".reportperiod rp on (srp.id_reportperiod =rp.id)
+join ".$schema.".staff st on (srp.id_staff=st.id)
+where st.isdeleted is null and id_staff='".$data->{id_staff}."' ".$rpincl."
 order by rp.startdate,rp.enddate,st.surname,st.prename;";
   my $rpdata = $self->{db}->querysorted($sql);
   my $rpmax = keys(%{$rpdata})-1;
@@ -246,7 +247,9 @@ sub ReportPage(){
   my $self = shift;
   my $schema = shift;
   my $data = shift;
-  my $txtpage = "<table>
+  my $perioddata = $self->getPeriodDays($schema,$data->{id_staff},$data->{startdate},$data->{enddate});
+  my $weeksums = $self->getPeriodWeekSums($schema,$data->{id_staff},$data->{startdate},$data->{enddate});
+  my $tblheader = "<table>
     <thead>
       <tr ><th colspan=\"5\" style=\"border-right: 0px;font-size: 10pt;\">".$data->{dspstartdate}." - ".$data->{dspenddate}."</th><th colspan=\"4\" style=\"border-left: 0px;text-align: right;font-size: 10pt;\">".$data->{surname}." ".$data->{prename}."</th></tr>
       <tr><th>Semaine</th>
@@ -260,56 +263,113 @@ sub ReportPage(){
       <th>Totaux</th></tr>
     </thead>
     <tbody>";
-    my $perioddata = $self->getPeriodDays($schema,$data->{id_staff},$data->{startdate},$data->{enddate});
-    my $weeksums = $self->getPeriodWeekSums($schema,$data->{id_staff},$data->{startdate},$data->{enddate});
+    my $txtpage = $tblheader;
+    #my $pdays = $self->getPeriodDays($schema,$data->{startdate},$data->{enddate});
      my $wdk = 0;
+     my $lastpw = -1;
+     my $numweeks = keys(%{$weeksums});
+     my $cntr = 0;
+    
      foreach my $pw  (sort {$a <=> $b} keys(%{$perioddata})){
-        my @wd = split(',',$perioddata->{$pw}->{weekdates});
+        my @wd = split(',',','.$perioddata->{$pw}->{weekdates});
+        #print STDERR "CWEEKDAY:".$perioddata->{$pw}->{daydate}."=>".$wd[$wdk]."\n";
         if ($wdk == 0 ){
+          if ($cntr > 7){
+            $txtpage .= "<div class=\"page-break\"></div>";
+            $txtpage .= $tblheader;
+            $cntr = 0;
+          }   
+          $cntr++;
           my @dspwd = split(',',$perioddata->{$pw}->{dspweekdates});
     $txtpage .="<tr><th rowspan=\"2\">".$perioddata->{$pw}->{dspweekshort}."</th>
-          <th>".$dspwd[0]."</th>
-          <th>".$dspwd[1]."</th>
-          <th>".$dspwd[2]."</th>
-          <th>".$dspwd[3]."</th>
-          <th>".$dspwd[4]."</th>
-          <th>".$dspwd[5]."</th>
-          <th>".$dspwd[6]."</th>
+          <th style=\"width: 44mm;\">".$dspwd[0]."</th>
+          <th style=\"width: 44mm;\">".$dspwd[1]."</th>
+          <th style=\"width: 44mm;\">".$dspwd[2]."</th>
+          <th style=\"width: 44mm;\">".$dspwd[3]."</th>
+          <th style=\"width: 44mm;\">".$dspwd[4]."</th>
+          <th style=\"width: 44mm;\">".$dspwd[5]."</th>
+          <th style=\"width: 44mm;\">".$dspwd[6]."</th>
           <th></th></tr><tr>
         ";
+        $wdk = 1; 
         }
-          while (($perioddata->{$pw}->{daydate} ne $wd[$wdk]) && $wdk lt "7"){
-            $txtpage .="<td>jour de repos</td>";
+          #print STDERR "CWEEKDAY:".$perioddata->{$pw}->{daydate}."=>".$wd[$wdk]."\n";
+          while (($perioddata->{$pw}->{daydate} ne $wd[$wdk]) && $wdk le "7"){
+            if ($wdk == 0){$wdk++; next; }
+            if (($perioddata->{$pw}->{daydate} gt $data->{startdate}) || ($perioddata->{$pw}->{daydate} lt $data->{enddate})){
+              $txtpage .="<td style=\"max-height: 15px!important;height: 15mm;\">&nbsp;</td>";
+            } 
+            
             $wdk++; 
           }
-        if ($wdk lt "7"){
-          if ($perioddata->{$pw}->{dayhours} ne "00:00" || $perioddata->{$pw}->{vacancyhours} ne "00:00" || $perioddata->{$pw}->{recuperationhours} ne "00:00"){
-          $txtpage .="<td>
+          #print STDERR "CWEEKDAY2:".$perioddata->{$pw}->{daydate}."=>".$wd[$wdk]."\n";
+        if ($wdk le "7"){
+          
+          if ($perioddata->{$pw}->{dayhours} ne ""){
+          $txtpage .= "<td style=\"max-height: 15px!important; height: 15mm;\">
+          <table class=\"tbinline\" style=\"height: 15mm;\" style=\"border: 0px;\"><tr style=\"border: 0px;\"><td style=\"border: 0px;\">
           <table class=\"tbinline\">
+            <tr style=\" border-bottom: 0.5px solid #969696;\" >
+              <td class=\"datavalues\" style=\"height: 8mm;border-right: 0.5px solid #969696;\"><div>".$perioddata->{$pw}->{dspworkdata}."</div></td>
+            </tr>
+            <tr style=\"border-right: 0.5px solid #969696;\">
+              <td class=\"datavalues\" style=\"height: 7mm;border-right: 0.5px solid #969696;\">
+                <div style=\"font-size: 6pt;\">".(($perioddata->{$pw}->{dsppausedata})?$perioddata->{$pw}->{dsppausedata}:"&nbsp;")."</div>
+              </td>
+            </tr>
+          </table>
+          </td><td style=\"width: 15mm;border: 0px; \">
+          <table class=\"tbinline\" style=\"border: 0px;\">
             <tr>
-              <td class=\"datavalues\"><div>".$perioddata->{$pw}->{dspworkdata}."</div><div>".$perioddata->{$pw}->{dspworkdata}."</div></td>
-              <td class=\"datavalues\" style=\"width: 15mm;\">
-                <div style=\"font-weight: bold;\">".$perioddata->{$pw}->{dayhours}."</div>
-                <div style=\"font-style: italic;\">".$perioddata->{$pw}->{vacancyhours}."</div>
-                <div style=\"text-decoration: underline;\">".$perioddata->{$pw}->{recuperationhours}."</div>
+              <td class=\"datavalues\"  style=\"height: 5mm;border-bottom: 0.5px solid #969696;border-right: 0;\"\">
+                ".(($perioddata->{$pw}->{dayhours})?"<div style=\"font-weight: bold;\">".$perioddata->{$pw}->{dayhours}."</div>":"&nbsp;")."
+              </td>
+            </tr>
+            <tr>  
+              <td class=\"datavalues\"  style=\"height: 5mm;border-bottom: 0.5px solid #969696;border-right: 0;\">
+                ".(($perioddata->{$pw}->{vacancyhours})?"<div style=\"font-style: italic;\">".$perioddata->{$pw}->{vacancyhours}."</div>":"&nbsp;")."
+              </td>
+            </tr>
+            <tr>  
+              <td class=\"datavalues\"  style=\"height: 5mm;border-right: 0;\">
+                ".(($perioddata->{$pw}->{recuperationhours})?"<div style=\"text-decoration: underline;\">".$perioddata->{$pw}->{recuperationhours}."</div>":"&nbsp;")."
               </td>
             </tr>
             </table>
-            </td>"
+            </td></tr></table>
+            </td>";
          } else {
-          $txtpage .= "<td>jour de repos</td>"
-          }
+           #print STDERR "ELSE:".$perioddata->{$pw}->{dayhours}."\n"; 
+          $txtpage .= "<td style=\"max-height: 15px!important; height: 15mm;\">jour de repos</td>";
+        }
           } 
         $wdk++;
+        $lastpw = $pw;
+        #print STDERR "Weekday:".$wdk."<=".$perioddata->{$pw}->{daydate}."\n";
         if ($wdk gt "7"){
          $txtpage .=  "<td class=\"datavalues\">
           <table  class=\"tbinline\"><tr><td class=\"datavalues\">";
           my $ws = $perioddata->{$pw}->{weekstart};
           if (exists($weeksums->{$ws})){
-            $txtpage .= "<div style=\"font-weight: bold;\">".(($weeksums->{$ws}->{totalhours} eq "00:00")?"":$weeksums->{$ws}->{totalhours})."</div>
-            <div style=\"font-weight: bold;text-decoration: underline;\">".(($weeksums->{$ws}->{recperationhours} eq "00:00")?"":$weeksums->{$ws}->{recperationhours})."</div>
-            <div style=\"font-weight: bold;font-style: italic;\">".(($weeksums->{$ws}->{vacancyhours} eq "00:00")?"":$weeksums->{$ws}->{vacancyhours})."</div>
-            <div style=\"font-weight: bold;text-decoration: underline;text-decoration-style: double;\">".(($weeksums->{$ws}->{diffhours} eq "00:00")?"":$weeksums->{$ws}->{diffhours})."</div>";
+            $txtpage .= "
+             <table class=\"tbinline\">
+            <tr>
+              <td class=\"datavalues\"  style=\"height: 5mm;\">
+              <div style=\"font-weight: bold;border-bottom: 0.5px solid #969696;\">".$weeksums->{$ws}->{totalhours}."</div>
+              </td>
+            </tr>
+            <tr>
+              <td class=\"datavalues\"  style=\"height: 5mm;\">
+              <div style=\"font-weight: bold;text-decoration: underline;border-bottom: 0.5px solid #969696;\">".(($weeksums->{$ws}->{recperationhours} eq "00:00")?"":$weeksums->{$ws}->{recperationhours})."</div>
+              </td>
+            </tr>
+            <tr>
+              <td class=\"datavalues\"  style=\"height: 5mm;\">
+            <div style=\"font-weight: bold;font-style: italic;border-bottom: 0.5px solid #969696;\">".(($weeksums->{$ws}->{vacancyhours} eq "00:00")?"":$weeksums->{$ws}->{vacancyhours})."</div>
+            </td>
+            </tr></table>
+            ";
+            #<div style=\"font-weight: bold;text-decoration: underline;text-decoration-style: double;border-bottom: 0.5px solid #969696;\">".(($weeksums->{$ws}->{diffhours} eq "00:00")?"":$weeksums->{$ws}->{diffhours})."</div>
           }
           $txtpage .= "</td></tr></table>
           </td></tr>
@@ -317,7 +377,42 @@ sub ReportPage(){
           $wdk=0;
         }
       }
+      while ($wdk le "7"){
+         $txtpage .="<td style=\"height: 15mm;\">&nbsp;</td>";
+        $wdk++;
+      }
+      if ($wdk gt "7"){
+         $txtpage .=  "<td class=\"datavalues\">
+          <table  class=\"tbinline\"><tr><td class=\"datavalues\">";
+          my $ws = $perioddata->{$lastpw}->{weekstart};
+          if (exists($weeksums->{$ws})){
+            $txtpage .= "
+             <table class=\"tbinline\">
+            <tr>
+              <td class=\"datavalues\"  style=\"height: 5mm;\">
+              <div style=\"font-weight: bold;border-bottom: 0.5px solid #969696;\">".$weeksums->{$ws}->{totalhours}."</div>
+              </td>
+            </tr>
+            <tr>
+              <td class=\"datavalues\"  style=\"height: 5mm;\">
+              <div style=\"font-weight: bold;text-decoration: underline;border-bottom: 0.5px solid #969696;\">".(($weeksums->{$ws}->{recperationhours} eq "00:00")?"":$weeksums->{$ws}->{recperationhours})."</div>
+              </td>
+            </tr>
+            <tr>
+              <td class=\"datavalues\"  style=\"height: 5mm;\">
+            <div style=\"font-weight: bold;font-style: italic;border-bottom: 0.5px solid #969696;\">".(($weeksums->{$ws}->{vacancyhours} eq "00:00")?"":$weeksums->{$ws}->{vacancyhours})."</div>
+            </td>
+            </tr></table>
+            ";
+            #<div style=\"font-weight: bold;text-decoration: underline;text-decoration-style: double;border-bottom: 0.5px solid #969696;\">".(($weeksums->{$ws}->{diffhours} eq "00:00")?"":$weeksums->{$ws}->{diffhours})."</div>
+          }
+          $txtpage .= "</td></tr></table>
+          </td></tr>
+          ";
+         
+        }
    $txtpage .= "</tbody> </table>";
+
   # my $spt = $data->{sums};
   $txtpage .= "<table>
     <thead>
@@ -366,7 +461,7 @@ sub getPeriodDays(){
             COALESCE((to_char(pd.pausestart1::interval, 'HH24:MI') || '-') || to_char(pd.pauseend1::interval, 'HH24:MI'), '') || COALESCE(((CASE WHEN pd.pausestart1 IS NOT NULL THEN '<br/>' ELSE '' END || to_char(pd.pausestart2::interval, 'HH24:MI')) || '-') || to_char(pd.pauseend2::interval, 'HH24:MI'), '') AS dsppausedata,
             COALESCE((to_char(pd.timestart1::interval, 'HH24:MI') || '-') || to_char(pd.timeend1::interval, 'HH24:MI'), '') || COALESCE(((CASE WHEN pd.timestart1 IS NOT NULL THEN '<br/>' ELSE '' END || to_char(pd.timestart2::interval, 'HH24:MI')) || '-') || to_char(pd.timeend2::interval, 'HH24:MI'), '') AS dspworkdata,
             (((((((((((to_char(date_trunc('week',pd.daydate), 'DD.MM.YYYY') || ',') || to_char(date(date_trunc('week',pd.daydate) + '1 day'::interval), 'DD.MM.YYYY')) || ',') || to_char(date(date_trunc('week',pd.daydate) + '2 days'::interval), 'DD.MM.YYYY')) || ',') || to_char(date(date_trunc('week',pd.daydate)) + '3 days'::interval, 'DD.MM.YYYY')) || ',') || to_char(date(date_trunc('week',pd.daydate) + '4 days'::interval), 'DD.MM.YYYY')) || ',') || to_char(date(date_trunc('week',pd.daydate) + '5 days'::interval), 'DD.MM.YYYY')) || ',') || to_char(date(date_trunc('week',pd.daydate) + '6 days'::interval), 'DD.MM.YYYY') AS dspweekdates,
-    (((((((((((date_trunc('week',pd.daydate) || ',') || date(date_trunc('week',pd.daydate) + '1 day'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '2 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '3 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '4 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '5 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '6 days'::interval) AS weekdates
+    (((((((((((date(date_trunc('week',pd.daydate)) || ',') || date(date_trunc('week',pd.daydate) + '1 day'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '2 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '3 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '4 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '5 days'::interval)) || ',') || date(date_trunc('week',pd.daydate) + '6 days'::interval) AS weekdates
           FROM ".$schema.".staffreportperioddays pd
              JOIN ".$schema.".staff st ON pd.id_staff = st.id and st.id='".$id_staff."' and st.isdeleted is null
           WHERE pd.daydate between date('".$date_start."') and date('".$date_end."')
@@ -376,6 +471,14 @@ sub getPeriodDays(){
   return $ret;
 }
 
+sub getPeriodDates(){
+  my $self = shift;
+  my $date_start = shift;
+  my $date_end = shift;
+  my $sql = "SELECT date_trunc('day', dd)::date as daydate  FROM generate_series( '2020-07-01'::timestamp,'2020-08-31'::timestamp,'1 day'::interval) dd";
+  my $ret = $self->{db}->querybykey('daydate',$sql);
+}
+
 sub getPeriodWeekSums(){
   my $self = shift;
   my $schema = shift;
diff --git a/website/app/lib/POT/Staff.pm b/website/app/lib/POT/Staff.pm
index e6f2cd22..c68818fa 100644
--- a/website/app/lib/POT/Staff.pm
+++ b/website/app/lib/POT/Staff.pm
@@ -102,7 +102,7 @@ sub getStaffList(){
   my $self = shift;
   my $schema = shift;
   my $id_groups = shift;
-  my $sql = "select st.* ,stg.groupname from ".$schema.".staff st LEFT JOIN ".$schema.".staffgroups stg ON st.id_staffgroup = stg.id where st.isdeleted is null;";
+  my $sql = "select st.* ,stg.groupname,COALESCE(st.surname || ' ', '') || COALESCE(st.prename, '') AS staffname from ".$schema.".staff st LEFT JOIN ".$schema.".staffgroups stg ON st.id_staffgroup = stg.id where st.isdeleted is null;";
   if (defined($id_groups)){
     $sql .= " stg.id in ('".join("','",@{$id_groups})."')";
   }
diff --git a/website/app/lib/dksdb.pm b/website/app/lib/dksdb.pm
index 6957708a..3412b67c 100644
--- a/website/app/lib/dksdb.pm
+++ b/website/app/lib/dksdb.pm
@@ -59,7 +59,7 @@ sub query_unenc(){
     my $sth = $self->{dbh}->prepare($stat) or return $retdata->{error} = "query".$self->{dbh}->errstr. "- SQL: ".$stat;;
 
 		
-   $sth->execute() or return $retdata->{error} = "query: ".$sth->errstr;
+   $sth->execute() or return $retdata->{error} = "query: ".$sth->errstr. "- SQL: ".$stat;;
    
    my $data = $sth->fetchrow_hashref();
    foreach my $k (keys %{$data}){
@@ -86,7 +86,7 @@ sub query(){
     my $sth = $self->{dbh}->prepare($stat) or return $retdata->{error} = "query".$self->{dbh}->errstr. "- SQL: ".$stat;;
 
 		
-   $sth->execute() or return $retdata->{error} = "query: ".$sth->errstr;
+   $sth->execute() or return $retdata->{error} = "query: ".$sth->errstr. "- SQL: ".$stat;
    
    my $data = $sth->fetchrow_hashref();
    foreach my $k (keys %{$data}){
@@ -149,7 +149,7 @@ sub querysorted(){
     my $sth = $self->{dbh}->prepare($stat) or return $retdata->{error} = "querysorted ".$self->{dbh}->errstr. "- SQL: ".$stat;;
 
 		
-   $sth->execute() or return $retdata->{error} = "querysorted: ".$sth->errstr;
+   $sth->execute() or return $retdata->{error} = "querysorted: ".$sth->errstr. "- SQL: ".$stat;;
    my $count = 0;
 
    while(my $data = $sth->fetchrow_hashref())
@@ -181,7 +181,7 @@ sub exec(){
     my $retdata;
     #my $self->{dbh} = DBI->connect($sitecfg->{dsn},$sitecfg->{dbuser},$sitecfg->{dbpassword},{AutoCommit=>1})  or return $retdata->{error} =  "query Connection Error!".$!;
     $stat = encode("UTF-8", $stat);
-    # open FILE,">>tmp/sql.log";
+    # open FILE,">>sql.log";
     # print FILE "\n==\n$stat\n==\n";
     # close FILE;
     # print STDERR "EXEC:\n".$stat."\n===\n";
diff --git a/website/app/tmpl/module/periods/staffperiodweeks.js b/website/app/tmpl/module/periods/staffperiodweeks.js
index 01ebeb9a..7ddf0647 100644
--- a/website/app/tmpl/module/periods/staffperiodweeks.js
+++ b/website/app/tmpl/module/periods/staffperiodweeks.js
@@ -364,7 +364,7 @@ let staffperiodweeks = {
     if (orech.value){
       reptime = timecalc.TimeToMinutes(orech.value);
     }
-    let fulldaytime =  wktime+vactime;
+    let fulldaytime =  wktime+vactime;//+reptime;
     odyh.value= timecalc.MinutesToTime(fulldaytime);
     //console.log("cmp",odyh.value,staffperiodweeks.dayrefdata.maxdayhours);
     if (fulldaytime > timecalc.TimeToMinutes(staffperiodweeks.dayrefdata.maxdayhours)){
diff --git a/website/app/tmpl/module/staff/staffperiods.js b/website/app/tmpl/module/staff/staffperiods.js
index 7d14f299..6c1c4629 100644
--- a/website/app/tmpl/module/staff/staffperiods.js
+++ b/website/app/tmpl/module/staff/staffperiods.js
@@ -53,15 +53,16 @@ let staffperiods = {
   },
   generatereport: function(){
     let udata = staffperiods.tbl.getSelectedData();
+    let sttbl = staff.tbl.getSelectedData();
     let rpids = [];
     for (var i in udata){
-      rpids.push(udata[i].id_reportperiod);
+        rpids.push(udata[i].id_reportperiod);
     }
     let repdata = {"schemata":schemata,"id_staff":staff.current_id,"id_periods":rpids};
     //console.log(udata);
     //console.log(repdata);
-    if (udata[0]) {
-      report.generate('pot',udata[0].staffname.replace(/\s+/g,"_")+"_" + moment(udata[0].startdate).format("YYYYMMDD") + "-" + moment(udata[0].enddate).format("YYYYMMDD"),repdata);
+    if (staff.current_id) {
+      report.generate('pot',sttbl[0].staffname.replace(/\s+/g,"_"),repdata);
     }
   },
   showdlgpayedhours: function(){
-- 
2.39.5