From bf62161acc788625a4d6ae9b270b637cfc4331d4 Mon Sep 17 00:00:00 2001 From: Kilian Date: Wed, 31 Jan 2024 09:20:09 +0100 Subject: [PATCH] v240131 repair +44hours --- app_plandutravail_lu/lib/POT/Period.php | 5 +- app_plandutravail_lu/lib/config.php | 4 +- app_plandutravail_lu/lib/version.php | 2 +- app_plandutravail_lu/log/sql.log | 1207 ++++++++++++++--- .../tmpl/module/js/pot/request.min.js | 2 +- .../tmpl/module/js/request.js | 2 +- 6 files changed, 1059 insertions(+), 163 deletions(-) diff --git a/app_plandutravail_lu/lib/POT/Period.php b/app_plandutravail_lu/lib/POT/Period.php index 20eafd0b..e5726a41 100644 --- a/app_plandutravail_lu/lib/POT/Period.php +++ b/app_plandutravail_lu/lib/POT/Period.php @@ -641,9 +641,9 @@ public function updateStaffPeriod($schema,$id_period,$id_staff){ $statussuncount = $this->dbh->query($sqlsun); // if reached 6 times +1 day vacancy for not paused 44h then do not add 1 day for the rest of the year! we check all the periods with the same years startdate! - $sql44 = "select id_period, case when sum(plusdays) over (order by startdate) >= 6 then null else plusdays end as plusdays from ( + $sql44 = "select id_period, case when sum(plusdays) over (order by startdate) >= 6 then 6 else case when plusdays = 0 then null else plusdays end end as plusdays from ( select id_period,sum(week44s) as plusdays,startdate from ( - select weekstart,case when (sum(sumx) over (order by weekstart)%8)::integer = 0 then 1 else null end as week44s,r.id as id_period,r.startdate,r.enddate, 1 as cnt from ( + select weekstart,case when (sum(sumx) over (order by weekstart)%8)::integer = 0 then 0 else 1 end as week44s,r.id as id_period,r.startdate,r.enddate from ( select weekstart,case when sum(cnt44) > 0 then 0 else 1 end as sumx, max(daydate) as maxdate from ( SELECT date(date_trunc('week',yy.daydate)) as weekstart,daydate, dayhours, @@ -656,7 +656,6 @@ public function updateStaffPeriod($schema,$id_period,$id_staff){ 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["yearstart"]."') and date(date_trunc('week',date('".$prd["yearstart"]."') + interval '1 year') - interval '1 day') and sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone and (sr.timestart1 IS NOT null or sr.timestart2 IS NOT null) diff --git a/app_plandutravail_lu/lib/config.php b/app_plandutravail_lu/lib/config.php index 54f70df3..d2f585a4 100644 --- a/app_plandutravail_lu/lib/config.php +++ b/app_plandutravail_lu/lib/config.php @@ -10,8 +10,8 @@ "type" => "app", "db"=> array( "type" => "pgsql", - "host" => "localhost", - //"host" => "sql12.your-server.de", + //"host" => "localhost", + "host" => "sql12.your-server.de", "dbname" => "potlu2_db", "user" => 'potlu2_user', "password" => 'dMY8xGB6cBLzrDGE', diff --git a/app_plandutravail_lu/lib/version.php b/app_plandutravail_lu/lib/version.php index 846e5914..29d724f6 100644 --- a/app_plandutravail_lu/lib/version.php +++ b/app_plandutravail_lu/lib/version.php @@ -2,7 +2,7 @@ // $cfg["appversion"] = "2.9.18";//;date('YmdHi'); #$cfg["toolsversion"] ="2.9.18"; $cfg["env"] = "prod"; -$cfg["version"] ="2.9.20"; +$cfg["version"] ="2.9.22"; if ($cfg["debug"] == "1"){ $cfg["version"] =date('YmdHis');; $cfg["appversion"] = date('YmdHis'); diff --git a/app_plandutravail_lu/log/sql.log b/app_plandutravail_lu/log/sql.log index 9586428b..0dd48927 100644 --- a/app_plandutravail_lu/log/sql.log +++ b/app_plandutravail_lu/log/sql.log @@ -1,159 +1,1056 @@ QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se join users us on (us.id=se.id_user) left join usergroups ugrp on (ugrp.id=us.id_usergroup) - where se.id= '23ae2c33-dce6-b98e-16bc-fb22f75af533' and se.remote_addr= '2001:7e8:c427:9501:5d62:2340:f73e:5b6c' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36' and - us.isblocked is null group by se.id,us.id,ugrp.id; -QUERYARRAY: select yy.id_staff,uu.id_reportperiod,yy.staffnumber,yy.staffname,yy.groupname,xx.cw, - xx.montimeall,xx.montime,xx.monentry,xx.monexit,xx.monstamps,xx.monpauses,xx.monpausedurations,xx.monminpauses,vac.monvacancyhours,vac.monvacextrahours,vac.monvacillhours, - xx.tuetimeall,xx.tuetime,xx.tueentry,xx.tueexit,xx.tuestamps,xx.tuepauses,xx.tuepausedurations,xx.tueminpauses,vac.tuevacancyhours,vac.tuevacextrahours,vac.tuevacillhours, - xx.wedtimeall,xx.wedtime,xx.wedentry,xx.wedexit,xx.wedstamps,xx.wedpauses,xx.wedpausedurations,xx.wedminpauses,vac.wedvacancyhours,vac.wedvacextrahours,vac.wedvacillhours, - xx.thutimeall,xx.thutime,xx.thuentry,xx.thuexit,xx.thustamps,xx.thupauses,xx.thupausedurations,xx.thuminpauses,vac.thuvacancyhours,vac.thuvacextrahours,vac.thuvacillhours, - xx.fritimeall,xx.fritime,xx.frientry,xx.friexit,xx.fristamps,xx.fripauses,xx.fripausedurations,xx.friminpauses,vac.frivacancyhours,vac.frivacextrahours,vac.frivacillhours, - xx.sattimeall,xx.sattime,xx.satentry,xx.satexit,xx.satstamps,xx.satpauses,xx.satpausedurations,xx.satminpauses,vac.satvacancyhours,vac.satvacextrahours,vac.satvacillhours, - xx.suntimeall,xx.suntime,xx.sunentry,xx.sunexit,xx.sunstamps,xx.sunpauses,xx.sunpausedurations,xx.sunminpauses,vac.sunvacancyhours,vac.sunvacextrahours,vac.sunvacillhours, - zz.monthstamptime as amonthsum, - uu.workhours as amonthwork,uu.hoursdiff as amonthdiff, - xx.weeksum from ( - select st.id as id_staff,st.staffnumber,coalesce(st.surname || ' ','') || coalesce(st.prename,'') as staffname,gg.groupname from dolcevita.staffcontract sc join dolcevita.staff st on (st.id=sc.id_staff) left join dolcevita.staffgroups gg on (gg.id=st.id_staffgroup) - where (sc.enddate >= date(date_trunc('week',date('2023-12-28'))) or enddate is null) and sc.startdate <= date(date_trunc('week',date('2023-12-28')) + interval '6 days') - and st.isdeleted is null - group by st.id,st.surname,st.prename,gg.groupname) yy left join ( - select id_staff,cw, - to_char(sum(case when dow = 1 then daystamptime else null end),'HH24:MI') as montime, - to_char(sum(case when dow = 1 then sumdayall else null end),'HH24:MI') as montimeall, - min(case when dow = 1 then timeentry else null end) as monentry, - max(case when dow = 1 then timeexit else null end) as monexit, - max(case when dow = 1 then stamptimes else null end) as monstamps, - max(case when dow = 1 then pausetimes else null end) as monpauses, - max(case when dow = 1 then pausedurations else null end) as monpausedurations, - max(case when dow = 1 then minpauses else null end) as monminpauses, - to_char(sum(case when dow = 2 then daystamptime else null end),'HH24:MI') as tuetime, - to_char(sum(case when dow = 2 then sumdayall else null end),'HH24:MI') as tuetimeall, - min(case when dow = 2 then timeentry else null end) as tueentry, - max(case when dow = 2 then timeexit else null end) as tueexit, - max(case when dow = 2 then stamptimes else null end) as tuestamps, - max(case when dow = 2 then pausetimes else null end) as tuepauses, - max(case when dow = 2 then pausedurations else null end) as tuepausedurations, - max(case when dow = 2 then minpauses else null end) as tueminpauses, - to_char(sum(case when dow = 3 then daystamptime else null end),'HH24:MI') as wedtime, - to_char(sum(case when dow = 3 then sumdayall else null end),'HH24:MI') as wedtimeall, - min(case when dow = 3 then timeentry else null end) as wedentry, - max(case when dow = 3 then timeexit else null end) as wedexit, - max(case when dow = 3 then stamptimes else null end) as wedstamps, - max(case when dow = 3 then pausetimes else null end) as wedpauses, - max(case when dow = 3 then pausedurations else null end) as wedpausedurations, - max(case when dow = 3 then minpauses else null end) as wedminpauses, - to_char(sum(case when dow = 4 then daystamptime else null end),'HH24:MI') as thutime, - to_char(sum(case when dow = 4 then sumdayall else null end),'HH24:MI') as thutimeall, - min(case when dow = 4 then timeentry else null end) as thuentry, - max(case when dow = 4 then timeexit else null end) as thuexit, - max(case when dow = 4 then stamptimes else null end) as thustamps, - max(case when dow = 4 then pausetimes else null end) as thupauses, - max(case when dow = 4 then pausedurations else null end) as thupausedurations, - max(case when dow = 4 then minpauses else null end) as thuminpauses, - to_char(sum(case when dow = 5 then daystamptime else null end),'HH24:MI') as fritime, - to_char(sum(case when dow = 5 then sumdayall else null end),'HH24:MI') as fritimeall, - min(case when dow = 5 then timeentry else null end) as frientry, - max(case when dow = 5 then timeexit else null end) as friexit, - max(case when dow = 5 then stamptimes else null end) as fristamps, - max(case when dow = 5 then pausetimes else null end) as fripauses, - max(case when dow = 5 then pausedurations else null end) as fripausedurations, - max(case when dow = 5 then minpauses else null end) as friminpauses, - to_char(sum(case when dow = 6 then daystamptime else null end),'HH24:MI') as sattime, - to_char(sum(case when dow = 6 then sumdayall else null end),'HH24:MI') as sattimeall, - min(case when dow = 6 then timeentry else null end) as satentry, - max(case when dow = 6 then timeexit else null end) as satexit, - max(case when dow = 6 then stamptimes else null end) as satstamps, - max(case when dow = 6 then pausetimes else null end) as satpauses, - max(case when dow = 6 then pausedurations else null end) as satpausedurations, - max(case when dow = 6 then minpauses else null end) as satminpauses, - to_char(sum(case when dow = 7 then daystamptime else null end),'HH24:MI') as suntime, - to_char(sum(case when dow = 7 then sumdayall else null end),'HH24:MI') as suntimeall, - min(case when dow = 7 then timeentry else null end) as sunentry, - max(case when dow = 7 then timeexit else null end) as sunexit, - max(case when dow = 7 then stamptimes else null end) as sunstamps, - max(case when dow = 7 then pausetimes else null end) as sunpauses, - max(case when dow = 7 then pausedurations else null end) as sunpausedurations, - max(case when dow = 7 then minpauses else null end) as sunminpauses, - to_char(sum(daystamptime),'HH24:MI') as weeksum - from ( - select id_staff, - date_part('week',daydate) as cw, - string_agg(to_char(stamp_in,'HH24:MI') || '-' || coalesce(to_char(stamp_out,'HH24:MI'),'?'),',' order by stamp_in) as stamptimes, - to_char(min(stamp_in),'HH24:MI') as timeentry, - to_char(max(stamp_out),'HH24:MI') as timeexit, - string_agg(to_char(pauseduration,'HH24:MI'),',') as pausedurations, - string_agg(pausetime,',') as pausetimes, - sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) as sumdayall, - string_agg(minpauseok::text,'') as minpauses, - case when sum(minpauseok) = 0 and sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) >= '06:00:00'::interval then sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) - '00:30:00'::interval else sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) end as daystamptime, - date_part('isodow',daydate) as dow,daydate - from - ( select *, tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) as pauseduration, - to_char(lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in),'HH24:MI') || '-' || to_char(tx.stamp_in,'HH24:MI') as pausetime, - case when tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) > '00:30:00'::interval then 1 else 0 end as minpauseok - from dolcevita.timetracker tx where tx.daydate between date_trunc('week',date('2023-12-28')) and date(date_trunc('week',date('2023-12-28')) + interval '6 days') order by tx.id_staff,tx.stamp_in) tt - group by tt.id_staff,tt.daydate - ) ttd group by id_staff,cw) xx on yy.id_staff=xx.id_staff left join ( - select id_staff,to_char(sum(daystamptime),'HH24:MI') as monthstamptime,to_char(sum(sumdayall),'HH24:MI') as monthstampalltime from ( - select id_staff, - sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) as sumdayall, - case when sum(minpauseok) = 0 and sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) >= '06:00:00'::interval then sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) - '00:30:00'::interval else sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) end as daystamptime,daydate - from - ( select *, tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) as pauseduration, - case when tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) > '00:30:00'::interval then 1 else 0 end as minpauseok - from dolcevita.timetracker tx where tx.daydate between date_trunc('month',date('2023-12-28')) and date(date_trunc('month',date('2023-12-28')) + interval '1 month' - interval '1 day') order by tx.id_staff,tx.stamp_in) tt - group by tt.id_staff,tt.daydate - ) uu group by uu.id_staff - ) zz on yy.id_staff=zz.id_staff - left join ( - select - pdu.id_staff,psplit.id_reportperiod, - coalesce(case when position('-' in to_char(sum(pdu.dayhours)-sum(pdu.contracthours), 'HH24:MI'::text)) > 0 then '-' || replace(to_char(sum(pdu.dayhours)-sum(pdu.contracthours), 'HH24:MI'::text),'-','') else to_char(sum(pdu.dayhours)-sum(pdu.contracthours), 'HH24:MI'::text) end,'00:00') as hoursdiff, - coalesce(to_char(sum(pdu.contracthours), 'HH24:MI'::text),'00:00') as contracthours, - coalesce(to_char(sum(pdu.workhours), 'HH24:MI'::text),'00:00') as workhours, - coalesce(to_char(sum(pdu.dayhours), 'HH24:MI'::text),'00:00') as dayhours - from dolcevita.staffreportperioddays pdu - join ( - select id as id_reportperiod,date(date_trunc('month',date(substartdate))) as splitter,date(substartdate) as startdate, date(date(substartdate) + interval '1 month' - interval '1 day') as enddate from ( - select rpx.id, date(generate_series(rpx.startdate::timestamp,rpx.enddate::timestamp,'1 month'::interval)) as substartdate from dolcevita.reportperiod rpx - ) gp - ) psplit on (pdu.daydate between psplit.startdate and psplit.enddate) - join dolcevita.staffreportperiod srp on (pdu.id_staff=srp.id_staff and srp.id_reportperiod=psplit.id_reportperiod) - join dolcevita.reportperiod rp on (rp.id=srp.id_reportperiod) - join dolcevita.staff st on (pdu.id_staff=st.id) - LEFT JOIN dolcevita.staffgroups sgr ON st.id_staffgroup = sgr.id - WHERE st.isdeleted IS null and daydate between date_trunc('month',date('2023-12-28')) and date(date_trunc('month',date('2023-12-28')) + interval '1 Month' - interval '1 day') - group by srp.id,pdu.id_staff,psplit.id_reportperiod,psplit.splitter,sgr.groupname - ) uu on yy.id_staff=uu.id_staff - left join ( - select xdu.id_staff,max(case when date_part('isodow',xdu.daydate) =1 then to_char(xdu.vacancyhours, 'HH24:MI'::text) end) as monvacancyhours, - max(case when date_part('isodow',xdu.daydate) =1 then to_char(xdu.vacextrahours, 'HH24:MI'::text) end) as monvacextrahours, - max(case when date_part('isodow',xdu.daydate) =1 then to_char(xdu.vacillhours, 'HH24:MI'::text) end) as monvacillhours, - max(case when date_part('isodow',xdu.daydate) =2 then to_char(xdu.vacancyhours, 'HH24:MI'::text) end) as tuevacancyhours, - max(case when date_part('isodow',xdu.daydate) =2 then to_char(xdu.vacextrahours, 'HH24:MI'::text) end) as tuevacextrahours, - max(case when date_part('isodow',xdu.daydate) =2 then to_char(xdu.vacillhours, 'HH24:MI'::text) end) as tuevacillhours, - max(case when date_part('isodow',xdu.daydate) =3 then to_char(xdu.vacancyhours, 'HH24:MI'::text) end) as wedvacancyhours, - max(case when date_part('isodow',xdu.daydate) =3 then to_char(xdu.vacextrahours, 'HH24:MI'::text) end) as wedvacextrahours, - max(case when date_part('isodow',xdu.daydate) =3 then to_char(xdu.vacillhours, 'HH24:MI'::text) end) as wedvacillhours, - max(case when date_part('isodow',xdu.daydate) =4 then to_char(xdu.vacancyhours, 'HH24:MI'::text) end) as thuvacancyhours, - max(case when date_part('isodow',xdu.daydate) =4 then to_char(xdu.vacextrahours, 'HH24:MI'::text) end) as thuvacextrahours, - max(case when date_part('isodow',xdu.daydate) =4 then to_char(xdu.vacillhours, 'HH24:MI'::text) end) as thuvacillhours, - max(case when date_part('isodow',xdu.daydate) =5 then to_char(xdu.vacancyhours, 'HH24:MI'::text) end) as frivacancyhours, - max(case when date_part('isodow',xdu.daydate) =5 then to_char(xdu.vacextrahours, 'HH24:MI'::text) end) as frivacextrahours, - max(case when date_part('isodow',xdu.daydate) =5 then to_char(xdu.vacillhours, 'HH24:MI'::text) end) as frivacillhours, - max(case when date_part('isodow',xdu.daydate) =6 then to_char(xdu.vacancyhours, 'HH24:MI'::text) end) as satvacancyhours, - max(case when date_part('isodow',xdu.daydate) =6 then to_char(xdu.vacextrahours, 'HH24:MI'::text) end) as satvacextrahours, - max(case when date_part('isodow',xdu.daydate) =6 then to_char(xdu.vacillhours, 'HH24:MI'::text) end) as satvacillhours, - max(case when date_part('isodow',xdu.daydate) =7 then to_char(xdu.vacancyhours, 'HH24:MI'::text) end) as sunvacancyhours, - max(case when date_part('isodow',xdu.daydate) =7 then to_char(xdu.vacextrahours, 'HH24:MI'::text) end) as sunvacextrahours, - max(case when date_part('isodow',xdu.daydate) =7 then to_char(xdu.vacillhours, 'HH24:MI'::text) end) as sunvacillhours - from demo2m17.staffreportperioddays xdu - where xdu.daydate between date_trunc('week',date('2023-12-28')) and date(date_trunc('week',date('2023-12-28')) + interval '6 days') - group by xdu.id_staff) vac on (yy.id_staff=vac.id_staff) - order by staffnumber,staffname; -QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se - join users us on (us.id=se.id_user) - left join usergroups ugrp on (ugrp.id=us.id_usergroup) - where se.id= '23ae2c33-dce6-b98e-16bc-fb22f75af533' and se.remote_addr= '2001:7e8:c427:9501:5d62:2340:f73e:5b6c' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36' and + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and us.isblocked is null group by se.id,us.id,ugrp.id; +EXEC: UPDATE portanova.staffreportperioddays SET timestart1='09:30',timeend1='18:30',timestart2=null,timeend2=null,pausestart1='14:30',pauseend1='15:30',pausestart2=null,pauseend2=null,timepause='01:00',timepausetracked='00:00',workhourstracked='00:00',daycomment=null,vacancycomment=null,vacancyhours=null,vacillcomment=null,vacillhours=null,vacextracomment=null,vacextrahours=null,vacschoolcomment=null,vacschoolhours=null,recupcomment=null,recuperationhours=null,partunempcomment=null,partunemplhours=null,otherpaidhourscomment=null,otherpaidhours=null,dayhourstracked='00:00' WHERE id='c233cc20-66d2-5bb0-bfb8-111ef06270f1'; +QUERYARRAY: select daydate from portanova.vacancydays v where daydate between date(date_trunc('week',date('2024-01-31'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days'); +QUERY: select id from portanova.reportperiod where date('2024-01-31') between startdate and enddate; +QUERY: select date(date_trunc('year',startdate)) as yearstart ,* from portanova.reportperiod where id='83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e'; +EXEC: update portanova.staffreportperioddays spds set + workhours=ds.worktime, timepause=case when ds.timepause::interval > '00:00:00' then ds.timepause else null end, + dayhours=(ds.worktime::interval + ds.vachours::interval + ds.vacillhours::interval + ds.vacextrahours::interval + ds.otherpaidhours::interval + ds.recup::interval + ds.partunemplhours )::interval, interruptionhours = ds.interruption , + contracthours=case when ds.worktime::interval + ds.vachours::interval + ds.vacillhours::interval + ds.vacextrahours::interval + ds.otherpaidhours::interval + ds.recup::interval + ds.partunemplhours::interval > '00:00:00'::interval then ds.wdcontracthours else null end + from ( + select preds.id,case when preds.recuperationhours is null then '00:00:00'::interval else preds.recuperationhours::interval end as recup, + case when preds.vacancyhours is null then '00:00:00'::time else preds.vacancyhours end as vachours, + case when preds.vacillhours is null then '00:00:00'::time else preds.vacillhours end as vacillhours, + case when preds.vacextrahours is null then '00:00:00'::time else preds.vacextrahours end as vacextrahours, + case when preds.otherpaidhours is null then '00:00:00'::time else preds.otherpaidhours end as otherpaidhours, + case when preds.partunemplhours is null then '00:00:00'::time else preds.partunemplhours end as partunemplhours, + preds.interruption, + (preds.dt1+preds.dt2)-(preds.pdt1+preds.pdt2) as worktime, + (preds.pdt1+preds.pdt2) as timepause, + wdcontracthours + from ( select id, recuperationhours,vacancyhours,vacillhours,vacextrahours,otherpaidhours,payedpause,partunemplhours,nighthours, + case when timestart1 is not null and timeend1 is not null then case when timeend1 < timestart1 then '24:00:00'::interval + timeend1::interval - timestart1::interval else timeend1::interval - timestart1::interval end else '00:00:00'::interval end as dt1, + case when timestart2 is not null and timeend2 is not null then case when timeend2 < timestart2 then '24:00:00'::interval + timeend2::interval - timestart2::interval else timeend2::interval - timestart2::interval end else '00:00:00'::interval end as dt2, + case when pausestart1 between timestart1 and case when timeend1 < timestart1 then timeend1::interval + '24:00:00'::interval else timeend1::interval end and pauseend1 between timestart1 and case when timeend1 < timestart1 then timeend1::interval + '24:00:00'::interval else timeend1::interval end then case when pauseend1 < pausestart1 then '24:00:00'::interval + pauseend1::interval - pausestart1::interval else pauseend1::interval - pausestart1::interval end else '00:00:00'::interval end as pdt1, + case when pausestart1 between timestart2 and case when timeend2 < timestart2 then timeend2::interval + '24:00:00'::interval else timeend2::interval end and pauseend2 between timestart2 and case when timeend2 < timestart2 then timeend2::interval + '24:00:00'::interval else timeend2::interval end then case when pauseend2 < pausestart2 then '24:00:00'::interval + pauseend2::interval - pausestart2::interval else pauseend2::interval - pausestart2::interval end else '00:00:00'::interval end as pdt2, + case when timestart2 is not null and timeend1 is not null and timestart1 is not null and timeend2 is not null then + timestart2::interval - timeend1::interval + else null end as interruption, + wdcontracthours + from portanova.staffreportperioddays where id in (select id from portanova.staffreportperioddays where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days')) + ) preds) ds where spds.id=ds.id; +EXEC: update portanova.staffreportperioddays SET nighthours = null where id in (select id from portanova.staffreportperioddays where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days')); +EXEC: update portanova.staffreportperioddays spds set + nighthours=nh from ( + select id,daydate,timestart1,timeend1, timestart2,timeend2, + coalesce((case when timeend1 not between '01:00:01'::interval and '05:59:59'::interval then '06:00:00'::interval else timeend1::interval end) - + (case when timestart1 not between '01:00:01'::interval and '05:59:59'::interval then '01:00:00'::interval else timestart1::interval end),'00:00:00'::interval) + + coalesce((case when timeend2 not between '01:00:01'::interval and '05:59:59'::interval then '06:00:00'::interval else timeend2::interval end) - + (case when timestart2 not between '01:00:01'::interval and '05:59:59'::interval then '01:00:00'::interval else timestart2::interval end),'00:00:00'::interval) as nh + from portanova.staffreportperioddays s where + (timeend1 between '01:00:01' and '05:59:59' or timeend2 between '01:00:01' and '05:59:59' + or timestart1 between '01:00:01' and '05:59:59' or timestart2 between '01:00:01' and '05:59:59') + and id in (select id from portanova.staffreportperioddays where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days')) + ) ds + where spds.id=ds.id +QUERY: 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,row_number() over (order by daydate) as rowx from portanova.staffreportperioddays + where daydate between date('2023-01-01') and date('2023-12-31') + and date_part('isodow',daydate)::int4 = 7 and workhours > '00:00:00'::time + and id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' order by daydate + ) xsunday where daydate between date('2023-12-01') and date('2024-01-31') + ) sunresult; +QUERY: select id_period, case when sum(plusdays) over (order by startdate) >= 6 then null else plusdays end as plusdays from ( + select id_period,sum(week44s) as plusdays,startdate from ( + select weekstart,case when (sum(sumx) over (order by weekstart)%8)::integer = 0 then 1 else null end as week44s,r.id as id_period,r.startdate,r.enddate, 1 as cnt from ( + select weekstart,case when sum(cnt44) > 0 then 0 else 1 end as sumx, + max(daydate) as maxdate from ( + SELECT date(date_trunc('week',yy.daydate)) as weekstart,daydate, dayhours, + (date_part('epoch', yy.daydate + yy.timestart - (coalesce(yy.ndaydate,date('2023-01-01')) + coalesce(yy.ntimeend,'00:00:00'::time without time zone))) * '00:00:01'::interval) as xx,yy.timestart,yy.ntimeend, + case when (date_part('epoch', yy.daydate + yy.timestart - (coalesce(yy.ndaydate,date('2023-01-01')) + coalesce(yy.ntimeend,'00:00:00'::time without time zone))) * '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,xx_1.dayhours + FROM ( SELECT sr.id_staff, sr.daydate,sr.dayhours, + 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 portanova.staffreportperioddays sr + + WHERE sr.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and sr.daydate between date('2023-01-01') and date(date_trunc('week',date('2023-01-01') + interval '1 year') - interval '1 day') + and sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone + and (sr.timestart1 IS NOT null or sr.timestart2 IS NOT null) + ORDER BY sr.id_staff, sr.daydate) xx_1) yy order by yy.daydate,weekstart + ) aa group by aa.weekstart order by weekstart + + ) bb + join portanova.reportperiod r on (bb.weekstart between r.startdate and r.enddate) + group by weekstart,r.id,bb.sumx order by weekstart + + ) ff group by id_period,startdate) ss + where id_period='83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e' + group by id_period,startdate,ss.plusdays; +QUERY: select weekstart,id_staff,avgtotalhours from ( + select ws.weekstart, ws.id_staff, + AVG(ws.totalhours) over (order by ws.weekstart) as avgtotalhours + from ( + SELECT weekstart, id_staff, +sum(CASE WHEN srpd.dayhours IS NULL THEN '00:00:00'::interval ELSE srpd.dayhours::interval END) AS totalhours + FROM (select *,date(date_trunc('week', daydate)) AS weekstart, + date(date_trunc('week', daydate) + interval '6 days') AS weekend, + date_part('week', daydate) AS calweek, + date_part('isoyear', daydate) AS calyear from portanova.staffreportperioddays + where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days') + --where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date('2023-12-01') and date('2024-01-31') + ) srpd GROUP BY srpd.weekstart,srpd.id_staff + ORDER BY weekstart) ws) wsavg order by weekstart DESC LIMIT 1; +EXEC: update portanova.staffreportperiod rpd set + --contracthours=csp.contracthours, + workhours=csp.workhours, + vacancyill=csp.vacillhours, + vacancynormal=csp.vacancynormal, + vacancyextra=csp.vacextrahours, + otherpaidhours=csp.otherpaidhours, + partunemplhours=csp.partunemplhours, + totalhours=csp.totalhours, + nighthours=csp.nighthours, + recuperationhours=csp.recuperationhours,statussuncount=3,suppvacancysunwork=null,suppvacancy44hours=1,status44hcount=null,last44date=null,avgtotalweekhours=null,hoursdiff=csp.diffhours + FROM ( + select contracthours,workhours,totalhours,recuperationhours, totalhours-contracthours as diffhours,id,vacillhours,vacancynormal,vacextrahours,otherpaidhours,partunemplhours,nighthours from ( + select dp.id, + sum(pd.workhours) as workhours, + sum(pd.dayhours) as totalhours, + sum(coalesce(pd.nighthours,'00:00:00'::interval)) as nighthours, + sum(coalesce(pd.vacancyhours,'00:00:00'::time)) as vacancynormal, + sum(coalesce(pd.vacillhours,'00:00:00'::time)) as vacillhours, + sum(coalesce(pd.vacextrahours,'00:00:00'::time)) as vacextrahours, + sum(coalesce(pd.otherpaidhours,'00:00:00'::time)) as otherpaidhours, + sum(coalesce(pd.partunemplhours,'00:00:00'::time)) as partunemplhours, + sum(coalesce(pd.recuperationhours,'00:00:00'::time)) as recuperationhours, + coalesce(dp.contracthours,'00:00:00'::interval) as contracthours, + coalesce(dp.payedhours0,'00:00:00'::interval) as payedhours0, + coalesce(dp.payedhours40,'00:00:00'::interval) as payedhours40, + coalesce(dp.transferedhours,'00:00:00'::interval) as transferedhours + from portanova.staffreportperioddays pd + join portanova.staffreportperiod dp on (pd.id_staff=dp.id_staff and dp.id_reportperiod = '83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e') + where pd.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and pd.daydate between date('2023-12-01') and date('2024-01-31') + group by dp.id,dp.payedhours,dp.transferedhourscalc + ) rp) csp where csp.id=rpd.id; +QUERYARRAY: SELECT st.prename, st.surname, coalesce(st.surname || ' ','') || coalesce(st.prename,'') as name, + to_char(srp.contracthours, 'HH24:MI'::text) AS contracthours, + to_char(srp.workhours, 'HH24:MI'::text) AS workhours, + --to_char(srp.vacancyhours, 'HH24:MI'::text) AS vacancyhours, + to_char(srp.vacancyill, 'HH24:MI'::text) AS vacancyill, + to_char(srp.vacancynormal, 'HH24:MI'::text) AS vacancynormal, + to_char(srp.vacancyextra, 'HH24:MI'::text) AS vacancyextra, + to_char(srp.otherpaidhours, 'HH24:MI'::text) AS otherpaidhours, + to_char(srp.partunemplhours, 'HH24:MI'::text) AS partunemplhours, + to_char(srp.recuperationhours, 'HH24:MI'::text) AS recuperationhours, + to_char(srp.nighthours, 'HH24:MI'::text) AS nighthours, + case when position('-' in to_char(srp.hoursdiff, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.hoursdiff, 'HH24:MI'),'-','') else to_char(srp.hoursdiff, 'HH24:MI') end as hoursdiff, + to_char(srp.totalhours, 'HH24:MI'::text) AS totalhours, + srp.id as id_staffreportperiod,srp.id_reportperiod, srp.id_staff, srp.id, rp.startdate, rp.enddate, + COALESCE(st.surname || ' '::text, ''::text) || COALESCE(st.prename, ''::text) AS staffname, + st.id_staffgroup, sgr.groupname, + case when position('-' in to_char(srp.transferedhours, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.transferedhours, 'HH24:MI'),'-','') else to_char(srp.transferedhours, 'HH24:MI') end as transferedhours, + 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 + ,itmcontracthours,maxdays,srp.isvalidated, + to_char(srp.payedhours40calc,'HH24:MI'::text) AS payedhours, + to_char(payedhours0, 'HH24:MI'::text) as payedhours0, + to_char(payedhours40, 'HH24:MI'::text) as payedhours40, + to_char(payedhours40calc, 'HH24:MI'::text) as payedhours40calc, + to_char(hourstotransfer, 'HH24:MI'::text) as hourstotransfer, + to_char(hourstotransfercalc, 'HH24:MI'::text) as hourstotransfercalc, + to_char(nighthourstotransfer, 'HH24:MI'::text) as nighthourstotransfer, + to_char(nighthourstotransfercalc, 'HH24:MI'::text) as nighthourstotransfercalc, + to_char(srp.avgtotalweekhours, 'HH24:MI'::text) AS avgtotalweekhours + FROM portanova.staffreportperiod srp + LEFT JOIN portanova.staff st ON srp.id_staff = st.id + LEFT JOIN portanova.reportperiod rp ON srp.id_reportperiod = rp.id + LEFT JOIN portanova.staffgroups sgr ON st.id_staffgroup = sgr.id + WHERE st.isdeleted IS NULL AND srp.id_staff = 'eda4fd22-9dcf-4d61-327b-e039b9f48107' + ORDER BY st.surname, st.prename, srp.id_staff, rp.startdate DESC, rp.enddate DESC; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select date(date_trunc('week',startdate)) as startdate,startdate as periodstart, enddate as periodend,date(date_trunc('week',enddate) + interval '6 days') as enddate from portanova.reportperiod where id='83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e' +QUERYBYKEY: select ws.weekstart,ws.weekend,ws.calweek,ws.calyear, ws.id_staff + ,to_char(ws.workhours::interval,'HH24:MI') as workhours + ,to_char(ws.vacancyhours,'HH24:MI') as vacancynormal + ,to_char(ws.vacillhours,'HH24:MI') as vacancyill + ,to_char(ws.vacextrahours,'HH24:MI') as vacancyextra + ,to_char(ws.vacschoolhours,'HH24:MI') as vacancyschool + ,to_char(ws.otherpaidhours,'HH24:MI') as otherpaidhours + ,to_char(ws.partunemplhours,'HH24:MI') as partunemplhours + ,to_char(ws.recuperationhours,'HH24:MI') as recuperationhours + ,to_char(ws.totalhours,'HH24:MI') as totalhours + ,cd.contractworkdays + ,cd.cmpcontractdays + ,cd.cmpweekhours + ,to_char(coalesce(ttr.weektrackedhours,'00:00:00'::interval),'HH24:MI') as weektrackedhours + ,ws.periodworkhours + ,interval_to_char(sum(coalesce(ttr.weektrackedhours,'00:00:00'::interval)) over (order by ws.weekstart) - sum(ws.periodworkhours::interval) over (order by ws.weekstart)) as periodtrackdiff + ,sum(ws.workhours::interval) over (order by ws.weekstart) as runningworkhours + ,interval_to_char(coalesce(ttr.weektrackedhours,'00:00:00'::interval) - ws.periodworkhours::interval) as trackeddiff + ,case when cd.contractworkdays < cd.cmpcontractdays then to_char(cd.contractworkdays*'08:00:00'::interval,'HH24:MI') else to_char(cd.contracthours,'HH24:MI') end as contracthours + ,to_char(AVG(ws.totalhours) over (order by ws.weekstart),'HH24:MI') as avgtotalhours + ,case when cd.contractworkdays < cd.cmpcontractdays then '00:00' else interval_to_char(ws.totalhours-cd.contracthours) end as diffhours + ,case when cd.contractworkdays < cd.cmpcontractdays then + to_char(case when cd.cmpweekhours < '40:00:00'::interval then cd.contracthours * 1.15 * cd.contractworkdays else (cd.contractworkdays*'08:00:00'::interval) + (cd.contractworkdays* case when maxdata.maxweekhours > '48:00:00' then '01:16:50'::interval else '00:50:00'::interval end) end,'HH24:MI') + else + to_char(case when cd.contracthours < '40:00:00'::interval then cd.contracthours * 1.15 else maxdata.maxweekhours end,'HH24:MI') + end as maxweekhours + ,case when cd.contractworkdays < cd.cmpcontractdays then to_char(cd.contractworkdays*'08:00:00'::interval,'HH24:MI') else + to_char(case when cd.contracthours < '40:00:00'::interval then cd.contracthours else maxdata.defaultweekhours end,'HH24:MI') + end as defaultweekhours + from ( + SELECT weekstart,weekend,calweek,calyear, id_staff, + sum(CASE WHEN srpd.workhours IS NULL THEN '00:00:00'::interval ELSE srpd.workhours::interval END) AS workhours, + sum(case when srpd.daydate between date('2023-12-01') and date('2024-01-31') then coalesce(srpd.workhours::interval, '00:00:00'::interval) else '00:00:00'::interval end) as periodworkhours, + --sum(case when srpd.daydate between date('2023-12-01') and date('2024-01-31') then coalesce(srpd.workhours::interval, '00:00:00'::interval) else '00:00:00'::interval end) as periodworkhoursnopause, + sum(CASE WHEN srpd.vacancyhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacancyhours::interval END) AS vacancyhours, + sum(CASE WHEN srpd.vacillhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacillhours::interval END) AS vacillhours, + sum(CASE WHEN srpd.vacextrahours IS NULL THEN '00:00:00'::interval ELSE srpd.vacextrahours::interval END) AS vacextrahours, + sum(CASE WHEN srpd.vacschoolhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacschoolhours::interval END) AS vacschoolhours, + sum(CASE WHEN srpd.recuperationhours IS NULL THEN '00:00:00'::interval ELSE srpd.recuperationhours::interval END) AS recuperationhours, + sum(CASE WHEN srpd.otherpaidhours IS NULL THEN '00:00:00'::interval ELSE srpd.otherpaidhours::interval END) AS otherpaidhours, + sum(CASE WHEN srpd.partunemplhours IS NULL THEN '00:00:00'::interval ELSE srpd.partunemplhours::interval END) AS partunemplhours, + sum(CASE WHEN srpd.dayhours IS NULL THEN '00:00:00'::interval ELSE srpd.dayhours::interval END) AS totalhours, + sum(CASE WHEN srpd.workhours > '00:00:00'::time without time zone OR srpd.recuperationhours > '00:00:00'::time without time zone OR srpd.vacancyhours > '00:00:00'::time without time zone OR srpd.vacillhours > '00:00:00'::time without time zone OR srpd.vacextrahours > '00:00:00'::time without time zone OR srpd.vacschoolhours > '00:00:00'::time without time zone OR srpd.otherpaidhours > '00:00:00'::time without time zone or srpd.partunemplhours > '00:00:00'::time without time zone THEN 1 ELSE 0 END) AS workdays, + sum( CASE WHEN srpd.contracthours IS NULL THEN '00:00:00'::interval ELSE srpd.contracthours::interval END) AS contracthours + FROM (select *,date(date_trunc('week', daydate)) AS weekstart, + date(date_trunc('week', daydate) + interval '6 days') AS weekend, + date_part('week', daydate) AS calweek, + date_part('isoyear', daydate) AS calyear from portanova.staffreportperioddays + where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date('2023-11-27') and date('2024-02-04') + ) srpd GROUP BY srpd.weekstart,srpd.weekend,srpd.calweek,srpd.calyear,srpd.id_staff + ORDER BY weekstart) ws left join ( + select date(date_trunc('week',pd.daydate)) as weekstart,pd.id_staff,max(sc.weekdays) as cmpcontractdays,max(sc.weekhours) as cmpweekhours, + case when count(pd.daydate) < avg(sc.weekdays) then count(pd.daydate) else avg(sc.weekdays) end as contractworkdays, + case when count(pd.daydate) < avg(sc.weekdays) then avg(sc.weekhours/ sc.weekdays) * count(pd.daydate) else avg(sc.weekhours/ sc.weekdays) * avg(sc.weekdays) end as contracthours ,sum(case when coalesce(pd.workhours,'00:00:00'::time) > '00:00:00'::time then 1 else 0 end) as realworkdays + from ( select id_staff,weekdays ,weekhours, + case when startdate> date('2023-11-27') then startdate else date('2023-11-27') end as staffperiodstart, + case when enddate is null or enddate > date('2024-02-04') then date('2024-02-04') + else enddate end as staffperiodend from portanova.staffcontract where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + and startdate < date('2024-02-04') + ) sc + join portanova.staffreportperioddays pd on (pd.id_staff=sc.id_staff and pd.daydate between sc.staffperiodstart and sc.staffperiodend) + group by date(date_trunc('week',pd.daydate)),pd.id_staff + ) cd on ws.id_staff = cd.id_staff and ws.weekstart=cd.weekstart + left join ( + select id_staff,weekstart,max(defaultweekhours) as defaultweekhours,avg(maxweekhours) as maxweekhours from ( + select lpd.id_staff,date(date_trunc('week',lpd.daydate)) as weekstart,lpd.daydate, + case when wk.defaultweekhours is null then wkdef.defaultweekhours else wk.defaultweekhours end as defaultweekhours , + case when wk.maxweekhours is null then wkdef.maxweekhours else wk.maxweekhours end as maxweekhours + from portanova.staffreportperioddays lpd + left join portanova.worktimes wk on (lpd.daydate between wk.startdate and wk.enddate) + cross join (select * from portanova.worktimes where isdefault=true) wkdef + where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and lpd.daydate between date('2023-11-27') and date('2024-02-04') + order by lpd.daydate ) mh group by id_staff,weekstart + ) maxdata on ws.id_staff = maxdata.id_staff and ws.weekstart=maxdata.weekstart + left join ( + select weekstart,id_staff,coalesce(sum(daytracked),'00:00:00'::interval) as weektrackedhours from ( + select daydate,id_staff,weekstart,case when sum(trackedhours) > '06:00:00'::interval then sum(trackedhours)- '00:30:00'::interval else coalesce(sum(trackedhours),'00:00:00'::interval) end as daytracked from ( +select daydate,date(date_trunc('week',daydate)) as weekstart,id_staff,(stamp_out-stamp_in)::interval as trackedhours from portanova.timetracker +where daydate between date('2023-12-01') and date('2024-01-31') and id_staff ='eda4fd22-9dcf-4d61-327b-e039b9f48107') tto group by daydate,id_staff,weekstart,trackedhours) ttx group by weekstart,id_staff + ) ttr on ws.weekstart=ttr.weekstart and ws.id_staff=ttr.id_staff; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: SELECT pd.id,pd.id_staff,pd.id_reportperiod,pd.daydate,pd.payedpause,pd.daytype,pd.daycomment, + vacancycomment,vacillcomment,vacextracomment,recupcomment,otherpaidhourscomment,partunempcomment, + pd.daytemplate,pd.vacschoolcomment,rp.viewpartunempl,rp.viewvacschool, + date(date_trunc('week',pd.daydate)) as weekstart, + to_char(pd.timestart1::interval, 'HH24:MI') AS timestart1, + to_char(pd.timeend1::interval, 'HH24:MI') AS timeend1, + to_char(pd.timestart2::interval, 'HH24:MI') AS timestart2, + to_char(pd.timeend2::interval, 'HH24:MI') AS timeend2, + to_char(pd.timepause::interval, 'HH24:MI') AS timepause, + -- coalesce('
' || to_char(CASE WHEN pd.vacancyhours::interval = '00:00:00'::interval THEN null ELSE pd.vacancyhours END::interval, 'HH24:MI') || '
','') || + -- coalesce('
' || to_char(CASE WHEN pd.vacillhours::interval = '00:00:00'::interval THEN null else pd.vacillhours END::interval, 'HH24:MI') || '
','') || + -- coalesce('
' || to_char(CASE WHEN pd.vacextrahours::interval = '00:00:00'::interval THEN null ELSE pd.vacextrahours END::interval, 'HH24:MI') || '
','') AS dspvacancyhours, + to_char(CASE WHEN pd.vacancyhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacancyhours END::interval, 'HH24:MI') AS vacancyhours, + to_char(CASE WHEN pd.vacillhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacillhours END::interval, 'HH24:MI') AS vacillhours, + to_char(CASE WHEN pd.vacextrahours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacextrahours END::interval, 'HH24:MI') AS vacextrahours, + to_char(CASE WHEN pd.vacschoolhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacschoolhours END::interval, 'HH24:MI') AS vacschoolhours, + to_char(CASE WHEN pd.otherpaidhours::interval = '00:00:00'::interval THEN NULL ELSE pd.otherpaidhours END::interval, 'HH24:MI') AS otherpaidhours, + to_char(CASE WHEN pd.partunemplhours::interval = '00:00:00'::interval THEN NULL ELSE pd.partunemplhours END::interval, 'HH24:MI') AS partunemplhours, + to_char(pd.contracthours::interval, 'HH24:MI') AS contracthours, + to_char(CASE WHEN pd.workhours::interval = '00:00:00'::interval THEN NULL ELSE pd.workhours END::interval, 'HH24:MI') AS workhours, + to_char(CASE WHEN pd.dayhours::interval = '00:00:00'::interval THEN NULL ELSE pd.dayhours END::interval, 'HH24:MI') AS dayhours, + to_char(case WHEN pd.recuperationhours::interval = '00:00:00'::interval THEN NULL ELSE pd.recuperationhours END::interval, 'HH24:MI') AS recuperationhours, + to_char(CASE WHEN pd.interruptionhours::interval = '00:00:00'::interval THEN NULL ELSE pd.interruptionhours END::interval, 'HH24:MI') AS interruptionhours, + to_char(CASE WHEN pd.nighthours::interval = '00:00:00'::interval THEN NULL ELSE pd.nighthours END::interval, 'HH24:MI') AS nighthours, + to_char(pd.pausestart1::interval, 'HH24:MI') AS pausestart1, + to_char(pd.pauseend1::interval, 'HH24:MI') AS pauseend1, + to_char(pd.pausestart2::interval, 'HH24:MI') AS pausestart2, + 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 '
' 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 '
' ELSE '' END || to_char(pd.timestart2::interval, 'HH24:MI')) || '-') || to_char(pd.timeend2::interval, 'HH24:MI'), '') AS dspworkdata, + CASE WHEN max(freehours.freehours) < '00:00:00'::interval THEN NULL ELSE to_char(max(freehours.freehours), 'HH24:MI') END AS freehoursafter, + to_char(coalesce(max(wk.defaultdayhours),max(wkstd.defaultdayhours)),'HH24:MI') as defaultdayhours, + to_char(coalesce(max(wk.maxdayhours),max(wkstd.maxdayhours)),'HH24:MI') as maxdayhours, + to_char(coalesce(max(wk.mindailyrecuperation),max(wkstd.mindailyrecuperation)),'HH24:MI') as mindailyrecuperation, + to_char(coalesce(max(wk.maxinterruptionhours),max(wkstd.maxinterruptionhours)),'HH24:MI') as maxinterruptionhours, + max(vd.vacancyname) as vacancyname, + to_char(max(tt.trackedtime),'HH24:MI') as trackedtime, + max(case when tt.trackedtime is null or pd.workhours is null then null else interval_to_char(coalesce(tt.trackedtime::interval,'00:00:00'::interval) - coalesce(pd.workhours::interval,'00:00:00'::interval)) end) as trackeddiff, + null as incompletetrack + FROM portanova.staffreportperioddays pd + JOIN portanova.staff st ON pd.id_staff = st.id and st.id='eda4fd22-9dcf-4d61-327b-e039b9f48107' and st.isdeleted is null + LEFT JOIN ( SELECT yy.id_staff, yy.ndaydate AS daydate,case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend AS tmfrom, yy.daydate + yy.timestart AS tmto, + yy.daydate + yy.timestart - (case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend) AS realinterval, + date_part('epoch', yy.daydate + yy.timestart - (case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend)) * '00:00:01'::interval AS freehours + 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 + FROM ( SELECT sr.id_staff, sr.daydate, + 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 portanova.staffreportperioddays sr + WHERE sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone and sr.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + ORDER BY sr.id_staff, sr.daydate) xx_1) yy) freehours ON pd.id_staff = freehours.id_staff AND freehours.daydate = pd.daydate + left join portanova.worktimes wk on pd.daydate between wk.startdate and wk.enddate + cross join (select defaultdayhours,maxdayhours,mindailyrecuperation,maxinterruptionhours from portanova.worktimes where isdefault =true) wkstd + left join portanova.vacancydays vd on pd.daydate=vd.daydate + left join portanova.reportperiod rp on (pd.id_reportperiod=rp.id) + left join ( + select + sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) as sumdayall, + string_agg(minpauseok::text,'') as minpauses, null as incompletetrack, + case when sum(minpauseok) = 0 and sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) >= '06:00:00'::interval then sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) - '00:30:00'::interval else sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) end as trackedtime,daydate from + ( select *, tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) as pauseduration, + to_char(lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in),'HH24:MI') || '-' || to_char(tx.stamp_in,'HH24:MI') as pausetime, + case when tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) > '00:30:00'::interval then 1 else 0 end as minpauseok + from portanova.timetracker tx where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and tx.daydate between date('2023-11-27') and date('2024-02-04') + order by tx.stamp_in) tt + group by tt.daydate + ) tt on pd.daydate=tt.daydate + WHERE pd.daydate between date('2023-11-27') and date('2024-02-04') + group by pd.id,rp.viewpartunempl,rp.viewvacschool + ORDER BY pd.id_staff, pd.daydate; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select timetrackertype,sector,portal,module_vacancy,datasetname from public.companies where schemata='portanova'; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select timetrackertype,sector,portal,module_vacancy,datasetname from public.companies where schemata='portanova'; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: select sg.id,sg.groupname,sg.groupcolor,sg.isdefault,COALESCE(count(st.id), 0) AS staffmembers FROM portanova.staffgroups sg LEFT JOIN portanova.staff st ON (st.id_staffgroup = sg.id) WHERE st.isdeleted IS NULL GROUP BY sg.id ORDER BY sg.groupname; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: select id,trackername from portanova.timetrackerconfig order by trackername +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: select ttr.*, st.* ,stg.groupname,'' || COALESCE(coalesce(st.surname,'') || ' ', '') || COALESCE(st.prename, '') || '
' || ''|| coalesce(st.job,'') || '' AS staffname,sc.staffstatus from portanova.staff st + LEFT JOIN portanova.staffgroups stg ON st.id_staffgroup = stg.id + left join (select id_staff, case when min(startdate) <= current_date and (max(enddate) is null or max(enddate) >= current_date) then 'current' + when min(startdate) > current_date then 'futur' when max(enddate) < current_date then 'past' else null end as staffstatus,min(startdate)as startdate,max(enddate) as enddate from (select id_staff,startdate,case when enddate is null then current_date else enddate end as enddate from portanova.staffcontract) scc group by id_staff) sc on (st.id=sc.id_staff) + cross join (select timetrackertype,case when timetrackertype is not null then true else null end as istimetrackerenabled,periodtypes from public.companies where schemata='portanova') ttr + where st.isdeleted is null order by groupname,st.staffnumber,surname,prename; +QUERYARRAY: select id,tmplname,tmplcolor as bgcolor FROM portanova.daytemplate order by tmplname; +QUERYARRAY: SELECT wp.id,wp.workplan,wp.isdefault,wp.weekhours,wp.weekdays, + coalesce((((((wp.workplan || ' / ') ||substr(wp.weekhours::text, 1, 5)) || 'h / ') || wp.weekdays) || 'j / libre: ') || (( SELECT string_agg(ww.wday, ',') AS string_agg FROM ( SELECT public.day_name(json_array_elements_text(wp.freeweekdayslist)) AS wday) ww)),wp.workplan ) AS dspworkplandetail + FROM portanova.workplans wp + ORDER BY wp.isdefault, wp.workplan; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select * from portanova.staff where id='eda4fd22-9dcf-4d61-327b-e039b9f48107' and isdeleted is null; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: SELECT id,id as id_staffcontract,id_staff,startdate,enddate,weekdays,to_char(weekhours, 'HH24:MI'::text) AS weekhours,defaultfreedays from portanova.staffcontract where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' order by startdate,enddate; +QUERYARRAY: select vac.id_staff || '-' || vac.vacyear as id,vac.id_staff,vac.vacyear, + to_char(vac.vacnormal,'HH24:MI') as vachours, + to_char(vac.vacnormal,'HH24:MI') as vacnormal, + to_char(vac.vacrestbefore,'HH24:MI') as vacrestbefore, + to_char(vac.vacextra,'HH24:MI') as vacextra, + coalesce(to_char(plan.vacnormalplanned,'HH24:MI'),'00:00') as vacnormalplanned, + coalesce(to_char(plan.vacextraplanned,'HH24:MI'),'00:00') as vacextraplanned, + coalesce(to_char(plan.vacnormalplanned + plan.vacextraplanned,'HH24:MI'),'00:00') as vacplanned, + coalesce(to_char(vac.vacnormal + vac.vacrestbefore + vac.vacextra,'HH24:MI'),'00:00') as vactotal + from ( + select id_staff,vacyear, + sum(case when vactype is null or vactype ='normal' then coalesce(vachours,'00:00:00'::interval) else '00:00:00'::interval end) as vacnormal, + sum(case when vactype ='rest' then coalesce(vachours,'00:00:00'::interval) else '00:00:00'::interval end) as vacrestbefore, + sum(case when vactype ='extra' then coalesce(vachours,'00:00:00'::interval) else '00:00:00'::interval end) as vacextra + from portanova.staffvacancyyear group by id_staff,vacyear ) vac + left join ( + select id_staff,date_part('year',daydate) as vyear, + coalesce(sum(vacancyhours),'00:00:00'::interval) as vacnormalplanned, + coalesce(sum(vacextrahours),'00:00:00'::interval) as vacextraplanned + from portanova.staffreportperioddays + where (vacancyhours is not null or vacextrahours is not null) + group by vyear,id_staff) plan on (vac.id_staff=plan.id_staff and vac.vacyear=plan.vyear) + where vac.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + order by vacyear DESC; +QUERYARRAY: select id_staff || '_' || mmonth || '_' || myear as id,surname || ' ' || prename as staffname ,id_staff,groupname, + -- vacancyhours,vacillhours,vacextrahours,vacancycomment,vacillcomment,vacextracomment,mday,daydate,vactotal, + mmonth,myear, case when regexp_replace(string_agg(case when mday='01' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='01' then vcomment else null end,','),',,',',','g') end as c01,string_agg(case when mday='01' then vtypes else null end,'') as t01,to_char(sum(case when mday='01' then vactotal else null end),'HH24:MI') as d01,case when regexp_replace(string_agg(case when mday='02' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='02' then vcomment else null end,','),',,',',','g') end as c02,string_agg(case when mday='02' then vtypes else null end,'') as t02,to_char(sum(case when mday='02' then vactotal else null end),'HH24:MI') as d02,case when regexp_replace(string_agg(case when mday='03' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='03' then vcomment else null end,','),',,',',','g') end as c03,string_agg(case when mday='03' then vtypes else null end,'') as t03,to_char(sum(case when mday='03' then vactotal else null end),'HH24:MI') as d03,case when regexp_replace(string_agg(case when mday='04' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='04' then vcomment else null end,','),',,',',','g') end as c04,string_agg(case when mday='04' then vtypes else null end,'') as t04,to_char(sum(case when mday='04' then vactotal else null end),'HH24:MI') as d04,case when regexp_replace(string_agg(case when mday='05' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='05' then vcomment else null end,','),',,',',','g') end as c05,string_agg(case when mday='05' then vtypes else null end,'') as t05,to_char(sum(case when mday='05' then vactotal else null end),'HH24:MI') as d05,case when regexp_replace(string_agg(case when mday='06' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='06' then vcomment else null end,','),',,',',','g') end as c06,string_agg(case when mday='06' then vtypes else null end,'') as t06,to_char(sum(case when mday='06' then vactotal else null end),'HH24:MI') as d06,case when regexp_replace(string_agg(case when mday='07' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='07' then vcomment else null end,','),',,',',','g') end as c07,string_agg(case when mday='07' then vtypes else null end,'') as t07,to_char(sum(case when mday='07' then vactotal else null end),'HH24:MI') as d07,case when regexp_replace(string_agg(case when mday='08' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='08' then vcomment else null end,','),',,',',','g') end as c08,string_agg(case when mday='08' then vtypes else null end,'') as t08,to_char(sum(case when mday='08' then vactotal else null end),'HH24:MI') as d08,case when regexp_replace(string_agg(case when mday='09' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='09' then vcomment else null end,','),',,',',','g') end as c09,string_agg(case when mday='09' then vtypes else null end,'') as t09,to_char(sum(case when mday='09' then vactotal else null end),'HH24:MI') as d09,case when regexp_replace(string_agg(case when mday='10' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='10' then vcomment else null end,','),',,',',','g') end as c10,string_agg(case when mday='10' then vtypes else null end,'') as t10,to_char(sum(case when mday='10' then vactotal else null end),'HH24:MI') as d10,case when regexp_replace(string_agg(case when mday='11' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='11' then vcomment else null end,','),',,',',','g') end as c11,string_agg(case when mday='11' then vtypes else null end,'') as t11,to_char(sum(case when mday='11' then vactotal else null end),'HH24:MI') as d11,case when regexp_replace(string_agg(case when mday='12' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='12' then vcomment else null end,','),',,',',','g') end as c12,string_agg(case when mday='12' then vtypes else null end,'') as t12,to_char(sum(case when mday='12' then vactotal else null end),'HH24:MI') as d12,case when regexp_replace(string_agg(case when mday='13' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='13' then vcomment else null end,','),',,',',','g') end as c13,string_agg(case when mday='13' then vtypes else null end,'') as t13,to_char(sum(case when mday='13' then vactotal else null end),'HH24:MI') as d13,case when regexp_replace(string_agg(case when mday='14' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='14' then vcomment else null end,','),',,',',','g') end as c14,string_agg(case when mday='14' then vtypes else null end,'') as t14,to_char(sum(case when mday='14' then vactotal else null end),'HH24:MI') as d14,case when regexp_replace(string_agg(case when mday='15' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='15' then vcomment else null end,','),',,',',','g') end as c15,string_agg(case when mday='15' then vtypes else null end,'') as t15,to_char(sum(case when mday='15' then vactotal else null end),'HH24:MI') as d15,case when regexp_replace(string_agg(case when mday='16' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='16' then vcomment else null end,','),',,',',','g') end as c16,string_agg(case when mday='16' then vtypes else null end,'') as t16,to_char(sum(case when mday='16' then vactotal else null end),'HH24:MI') as d16,case when regexp_replace(string_agg(case when mday='17' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='17' then vcomment else null end,','),',,',',','g') end as c17,string_agg(case when mday='17' then vtypes else null end,'') as t17,to_char(sum(case when mday='17' then vactotal else null end),'HH24:MI') as d17,case when regexp_replace(string_agg(case when mday='18' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='18' then vcomment else null end,','),',,',',','g') end as c18,string_agg(case when mday='18' then vtypes else null end,'') as t18,to_char(sum(case when mday='18' then vactotal else null end),'HH24:MI') as d18,case when regexp_replace(string_agg(case when mday='19' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='19' then vcomment else null end,','),',,',',','g') end as c19,string_agg(case when mday='19' then vtypes else null end,'') as t19,to_char(sum(case when mday='19' then vactotal else null end),'HH24:MI') as d19,case when regexp_replace(string_agg(case when mday='20' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='20' then vcomment else null end,','),',,',',','g') end as c20,string_agg(case when mday='20' then vtypes else null end,'') as t20,to_char(sum(case when mday='20' then vactotal else null end),'HH24:MI') as d20,case when regexp_replace(string_agg(case when mday='21' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='21' then vcomment else null end,','),',,',',','g') end as c21,string_agg(case when mday='21' then vtypes else null end,'') as t21,to_char(sum(case when mday='21' then vactotal else null end),'HH24:MI') as d21,case when regexp_replace(string_agg(case when mday='22' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='22' then vcomment else null end,','),',,',',','g') end as c22,string_agg(case when mday='22' then vtypes else null end,'') as t22,to_char(sum(case when mday='22' then vactotal else null end),'HH24:MI') as d22,case when regexp_replace(string_agg(case when mday='23' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='23' then vcomment else null end,','),',,',',','g') end as c23,string_agg(case when mday='23' then vtypes else null end,'') as t23,to_char(sum(case when mday='23' then vactotal else null end),'HH24:MI') as d23,case when regexp_replace(string_agg(case when mday='24' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='24' then vcomment else null end,','),',,',',','g') end as c24,string_agg(case when mday='24' then vtypes else null end,'') as t24,to_char(sum(case when mday='24' then vactotal else null end),'HH24:MI') as d24,case when regexp_replace(string_agg(case when mday='25' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='25' then vcomment else null end,','),',,',',','g') end as c25,string_agg(case when mday='25' then vtypes else null end,'') as t25,to_char(sum(case when mday='25' then vactotal else null end),'HH24:MI') as d25,case when regexp_replace(string_agg(case when mday='26' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='26' then vcomment else null end,','),',,',',','g') end as c26,string_agg(case when mday='26' then vtypes else null end,'') as t26,to_char(sum(case when mday='26' then vactotal else null end),'HH24:MI') as d26,case when regexp_replace(string_agg(case when mday='27' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='27' then vcomment else null end,','),',,',',','g') end as c27,string_agg(case when mday='27' then vtypes else null end,'') as t27,to_char(sum(case when mday='27' then vactotal else null end),'HH24:MI') as d27,case when regexp_replace(string_agg(case when mday='28' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='28' then vcomment else null end,','),',,',',','g') end as c28,string_agg(case when mday='28' then vtypes else null end,'') as t28,to_char(sum(case when mday='28' then vactotal else null end),'HH24:MI') as d28,case when regexp_replace(string_agg(case when mday='29' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='29' then vcomment else null end,','),',,',',','g') end as c29,string_agg(case when mday='29' then vtypes else null end,'') as t29,to_char(sum(case when mday='29' then vactotal else null end),'HH24:MI') as d29,case when regexp_replace(string_agg(case when mday='30' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='30' then vcomment else null end,','),',,',',','g') end as c30,string_agg(case when mday='30' then vtypes else null end,'') as t30,to_char(sum(case when mday='30' then vactotal else null end),'HH24:MI') as d30,case when regexp_replace(string_agg(case when mday='31' then vcomment else null end,','),',,',',','g') = ',' then null else regexp_replace(string_agg(case when mday='31' then vcomment else null end,','),',,',',','g') end as c31,string_agg(case when mday='31' then vtypes else null end,'') as t31,to_char(sum(case when mday='31' then vactotal else null end),'HH24:MI') as d31, to_char(sum(vacillhours),'HH24:MI') as totalill, + to_char(sum(vacextrahours),'HH24:MI') as totalextra, + to_char(sum(vacancyhours),'HH24:MI') as totalvac from ( + select st.prename,st.surname,spd.id_staff,to_char(spd.daydate,'YYYY') as vacyear,spd.daydate,sg.id as id_staffgroup,sg.groupname, + coalesce(spd.vacancyhours::interval,'00:00:00'::interval) + coalesce(spd.vacillhours::interval,'00:00:00'::interval) + coalesce(spd.vacextrahours::interval,'00:00:00'::interval) as vactotal, + spd.vacancyhours,spd.vacillhours,spd.vacextrahours, + spd.vacancycomment, spd.vacillcomment, spd.vacextracomment, + case when spd.vacancyhours is not null then 'n' else '' end || case when spd.vacillhours is not null then 'i' else '' end || case when spd.vacextrahours is not null then 'e' else '' end as vtypes, + coalesce(spd.vacancycomment,'') || ',' || coalesce(spd.vacillcomment,'') || ',' || coalesce(spd.vacextracomment,'') as vcomment, + to_char(spd.daydate,'DD') as mday, + to_char(spd.daydate,'MM') as mmonth, + to_char(spd.daydate,'YYYY') as myear + from portanova.staffreportperioddays spd + join portanova.staff st on (st.id=spd.id_staff) + join portanova.staffgroups sg on (st.id_staffgroup=sg.id) + where (spd.vacancyhours is not null or spd.vacillhours is not null or spd.vacextrahours is not null) and id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + ) vd group by staffname,groupname,id_staff,myear,mmonth order by myear,staffname,mmonth +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: SELECT st.prename, st.surname, coalesce(st.surname || ' ','') || coalesce(st.prename,'') as name, + to_char(srp.contracthours, 'HH24:MI'::text) AS contracthours, + to_char(srp.workhours, 'HH24:MI'::text) AS workhours, + --to_char(srp.vacancyhours, 'HH24:MI'::text) AS vacancyhours, + to_char(srp.vacancyill, 'HH24:MI'::text) AS vacancyill, + to_char(srp.vacancynormal, 'HH24:MI'::text) AS vacancynormal, + to_char(srp.vacancyextra, 'HH24:MI'::text) AS vacancyextra, + to_char(srp.otherpaidhours, 'HH24:MI'::text) AS otherpaidhours, + to_char(srp.partunemplhours, 'HH24:MI'::text) AS partunemplhours, + to_char(srp.recuperationhours, 'HH24:MI'::text) AS recuperationhours, + to_char(srp.nighthours, 'HH24:MI'::text) AS nighthours, + case when position('-' in to_char(srp.hoursdiff, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.hoursdiff, 'HH24:MI'),'-','') else to_char(srp.hoursdiff, 'HH24:MI') end as hoursdiff, + to_char(srp.totalhours, 'HH24:MI'::text) AS totalhours, + srp.id as id_staffreportperiod,srp.id_reportperiod, srp.id_staff, srp.id, rp.startdate, rp.enddate, + COALESCE(st.surname || ' '::text, ''::text) || COALESCE(st.prename, ''::text) AS staffname, + st.id_staffgroup, sgr.groupname, + case when position('-' in to_char(srp.transferedhours, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.transferedhours, 'HH24:MI'),'-','') else to_char(srp.transferedhours, 'HH24:MI') end as transferedhours, + 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 + ,itmcontracthours,maxdays,srp.isvalidated, + to_char(srp.payedhours40calc,'HH24:MI'::text) AS payedhours, + to_char(payedhours0, 'HH24:MI'::text) as payedhours0, + to_char(payedhours40, 'HH24:MI'::text) as payedhours40, + to_char(payedhours40calc, 'HH24:MI'::text) as payedhours40calc, + to_char(hourstotransfer, 'HH24:MI'::text) as hourstotransfer, + to_char(hourstotransfercalc, 'HH24:MI'::text) as hourstotransfercalc, + to_char(nighthourstotransfer, 'HH24:MI'::text) as nighthourstotransfer, + to_char(nighthourstotransfercalc, 'HH24:MI'::text) as nighthourstotransfercalc, + to_char(srp.avgtotalweekhours, 'HH24:MI'::text) AS avgtotalweekhours + FROM portanova.staffreportperiod srp + LEFT JOIN portanova.staff st ON srp.id_staff = st.id + LEFT JOIN portanova.reportperiod rp ON srp.id_reportperiod = rp.id + LEFT JOIN portanova.staffgroups sgr ON st.id_staffgroup = sgr.id + WHERE st.isdeleted IS NULL AND srp.id_staff = 'eda4fd22-9dcf-4d61-327b-e039b9f48107' + ORDER BY st.surname, st.prename, srp.id_staff, rp.startdate DESC, rp.enddate DESC; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select date(date_trunc('week',startdate)) as startdate,startdate as periodstart, enddate as periodend,date(date_trunc('week',enddate) + interval '6 days') as enddate from portanova.reportperiod where id='83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e' +QUERYBYKEY: select ws.weekstart,ws.weekend,ws.calweek,ws.calyear, ws.id_staff + ,to_char(ws.workhours::interval,'HH24:MI') as workhours + ,to_char(ws.vacancyhours,'HH24:MI') as vacancynormal + ,to_char(ws.vacillhours,'HH24:MI') as vacancyill + ,to_char(ws.vacextrahours,'HH24:MI') as vacancyextra + ,to_char(ws.vacschoolhours,'HH24:MI') as vacancyschool + ,to_char(ws.otherpaidhours,'HH24:MI') as otherpaidhours + ,to_char(ws.partunemplhours,'HH24:MI') as partunemplhours + ,to_char(ws.recuperationhours,'HH24:MI') as recuperationhours + ,to_char(ws.totalhours,'HH24:MI') as totalhours + ,cd.contractworkdays + ,cd.cmpcontractdays + ,cd.cmpweekhours + ,to_char(coalesce(ttr.weektrackedhours,'00:00:00'::interval),'HH24:MI') as weektrackedhours + ,ws.periodworkhours + ,interval_to_char(sum(coalesce(ttr.weektrackedhours,'00:00:00'::interval)) over (order by ws.weekstart) - sum(ws.periodworkhours::interval) over (order by ws.weekstart)) as periodtrackdiff + ,sum(ws.workhours::interval) over (order by ws.weekstart) as runningworkhours + ,interval_to_char(coalesce(ttr.weektrackedhours,'00:00:00'::interval) - ws.periodworkhours::interval) as trackeddiff + ,case when cd.contractworkdays < cd.cmpcontractdays then to_char(cd.contractworkdays*'08:00:00'::interval,'HH24:MI') else to_char(cd.contracthours,'HH24:MI') end as contracthours + ,to_char(AVG(ws.totalhours) over (order by ws.weekstart),'HH24:MI') as avgtotalhours + ,case when cd.contractworkdays < cd.cmpcontractdays then '00:00' else interval_to_char(ws.totalhours-cd.contracthours) end as diffhours + ,case when cd.contractworkdays < cd.cmpcontractdays then + to_char(case when cd.cmpweekhours < '40:00:00'::interval then cd.contracthours * 1.15 * cd.contractworkdays else (cd.contractworkdays*'08:00:00'::interval) + (cd.contractworkdays* case when maxdata.maxweekhours > '48:00:00' then '01:16:50'::interval else '00:50:00'::interval end) end,'HH24:MI') + else + to_char(case when cd.contracthours < '40:00:00'::interval then cd.contracthours * 1.15 else maxdata.maxweekhours end,'HH24:MI') + end as maxweekhours + ,case when cd.contractworkdays < cd.cmpcontractdays then to_char(cd.contractworkdays*'08:00:00'::interval,'HH24:MI') else + to_char(case when cd.contracthours < '40:00:00'::interval then cd.contracthours else maxdata.defaultweekhours end,'HH24:MI') + end as defaultweekhours + from ( + SELECT weekstart,weekend,calweek,calyear, id_staff, + sum(CASE WHEN srpd.workhours IS NULL THEN '00:00:00'::interval ELSE srpd.workhours::interval END) AS workhours, + sum(case when srpd.daydate between date('2023-12-01') and date('2024-01-31') then coalesce(srpd.workhours::interval, '00:00:00'::interval) else '00:00:00'::interval end) as periodworkhours, + --sum(case when srpd.daydate between date('2023-12-01') and date('2024-01-31') then coalesce(srpd.workhours::interval, '00:00:00'::interval) else '00:00:00'::interval end) as periodworkhoursnopause, + sum(CASE WHEN srpd.vacancyhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacancyhours::interval END) AS vacancyhours, + sum(CASE WHEN srpd.vacillhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacillhours::interval END) AS vacillhours, + sum(CASE WHEN srpd.vacextrahours IS NULL THEN '00:00:00'::interval ELSE srpd.vacextrahours::interval END) AS vacextrahours, + sum(CASE WHEN srpd.vacschoolhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacschoolhours::interval END) AS vacschoolhours, + sum(CASE WHEN srpd.recuperationhours IS NULL THEN '00:00:00'::interval ELSE srpd.recuperationhours::interval END) AS recuperationhours, + sum(CASE WHEN srpd.otherpaidhours IS NULL THEN '00:00:00'::interval ELSE srpd.otherpaidhours::interval END) AS otherpaidhours, + sum(CASE WHEN srpd.partunemplhours IS NULL THEN '00:00:00'::interval ELSE srpd.partunemplhours::interval END) AS partunemplhours, + sum(CASE WHEN srpd.dayhours IS NULL THEN '00:00:00'::interval ELSE srpd.dayhours::interval END) AS totalhours, + sum(CASE WHEN srpd.workhours > '00:00:00'::time without time zone OR srpd.recuperationhours > '00:00:00'::time without time zone OR srpd.vacancyhours > '00:00:00'::time without time zone OR srpd.vacillhours > '00:00:00'::time without time zone OR srpd.vacextrahours > '00:00:00'::time without time zone OR srpd.vacschoolhours > '00:00:00'::time without time zone OR srpd.otherpaidhours > '00:00:00'::time without time zone or srpd.partunemplhours > '00:00:00'::time without time zone THEN 1 ELSE 0 END) AS workdays, + sum( CASE WHEN srpd.contracthours IS NULL THEN '00:00:00'::interval ELSE srpd.contracthours::interval END) AS contracthours + FROM (select *,date(date_trunc('week', daydate)) AS weekstart, + date(date_trunc('week', daydate) + interval '6 days') AS weekend, + date_part('week', daydate) AS calweek, + date_part('isoyear', daydate) AS calyear from portanova.staffreportperioddays + where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date('2023-11-27') and date('2024-02-04') + ) srpd GROUP BY srpd.weekstart,srpd.weekend,srpd.calweek,srpd.calyear,srpd.id_staff + ORDER BY weekstart) ws left join ( + select date(date_trunc('week',pd.daydate)) as weekstart,pd.id_staff,max(sc.weekdays) as cmpcontractdays,max(sc.weekhours) as cmpweekhours, + case when count(pd.daydate) < avg(sc.weekdays) then count(pd.daydate) else avg(sc.weekdays) end as contractworkdays, + case when count(pd.daydate) < avg(sc.weekdays) then avg(sc.weekhours/ sc.weekdays) * count(pd.daydate) else avg(sc.weekhours/ sc.weekdays) * avg(sc.weekdays) end as contracthours ,sum(case when coalesce(pd.workhours,'00:00:00'::time) > '00:00:00'::time then 1 else 0 end) as realworkdays + from ( select id_staff,weekdays ,weekhours, + case when startdate> date('2023-11-27') then startdate else date('2023-11-27') end as staffperiodstart, + case when enddate is null or enddate > date('2024-02-04') then date('2024-02-04') + else enddate end as staffperiodend from portanova.staffcontract where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + and startdate < date('2024-02-04') + ) sc + join portanova.staffreportperioddays pd on (pd.id_staff=sc.id_staff and pd.daydate between sc.staffperiodstart and sc.staffperiodend) + group by date(date_trunc('week',pd.daydate)),pd.id_staff + ) cd on ws.id_staff = cd.id_staff and ws.weekstart=cd.weekstart + left join ( + select id_staff,weekstart,max(defaultweekhours) as defaultweekhours,avg(maxweekhours) as maxweekhours from ( + select lpd.id_staff,date(date_trunc('week',lpd.daydate)) as weekstart,lpd.daydate, + case when wk.defaultweekhours is null then wkdef.defaultweekhours else wk.defaultweekhours end as defaultweekhours , + case when wk.maxweekhours is null then wkdef.maxweekhours else wk.maxweekhours end as maxweekhours + from portanova.staffreportperioddays lpd + left join portanova.worktimes wk on (lpd.daydate between wk.startdate and wk.enddate) + cross join (select * from portanova.worktimes where isdefault=true) wkdef + where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and lpd.daydate between date('2023-11-27') and date('2024-02-04') + order by lpd.daydate ) mh group by id_staff,weekstart + ) maxdata on ws.id_staff = maxdata.id_staff and ws.weekstart=maxdata.weekstart + left join ( + select weekstart,id_staff,coalesce(sum(daytracked),'00:00:00'::interval) as weektrackedhours from ( + select daydate,id_staff,weekstart,case when sum(trackedhours) > '06:00:00'::interval then sum(trackedhours)- '00:30:00'::interval else coalesce(sum(trackedhours),'00:00:00'::interval) end as daytracked from ( +select daydate,date(date_trunc('week',daydate)) as weekstart,id_staff,(stamp_out-stamp_in)::interval as trackedhours from portanova.timetracker +where daydate between date('2023-12-01') and date('2024-01-31') and id_staff ='eda4fd22-9dcf-4d61-327b-e039b9f48107') tto group by daydate,id_staff,weekstart,trackedhours) ttx group by weekstart,id_staff + ) ttr on ws.weekstart=ttr.weekstart and ws.id_staff=ttr.id_staff; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: SELECT pd.id,pd.id_staff,pd.id_reportperiod,pd.daydate,pd.payedpause,pd.daytype,pd.daycomment, + vacancycomment,vacillcomment,vacextracomment,recupcomment,otherpaidhourscomment,partunempcomment, + pd.daytemplate,pd.vacschoolcomment,rp.viewpartunempl,rp.viewvacschool, + date(date_trunc('week',pd.daydate)) as weekstart, + to_char(pd.timestart1::interval, 'HH24:MI') AS timestart1, + to_char(pd.timeend1::interval, 'HH24:MI') AS timeend1, + to_char(pd.timestart2::interval, 'HH24:MI') AS timestart2, + to_char(pd.timeend2::interval, 'HH24:MI') AS timeend2, + to_char(pd.timepause::interval, 'HH24:MI') AS timepause, + -- coalesce('
' || to_char(CASE WHEN pd.vacancyhours::interval = '00:00:00'::interval THEN null ELSE pd.vacancyhours END::interval, 'HH24:MI') || '
','') || + -- coalesce('
' || to_char(CASE WHEN pd.vacillhours::interval = '00:00:00'::interval THEN null else pd.vacillhours END::interval, 'HH24:MI') || '
','') || + -- coalesce('
' || to_char(CASE WHEN pd.vacextrahours::interval = '00:00:00'::interval THEN null ELSE pd.vacextrahours END::interval, 'HH24:MI') || '
','') AS dspvacancyhours, + to_char(CASE WHEN pd.vacancyhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacancyhours END::interval, 'HH24:MI') AS vacancyhours, + to_char(CASE WHEN pd.vacillhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacillhours END::interval, 'HH24:MI') AS vacillhours, + to_char(CASE WHEN pd.vacextrahours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacextrahours END::interval, 'HH24:MI') AS vacextrahours, + to_char(CASE WHEN pd.vacschoolhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacschoolhours END::interval, 'HH24:MI') AS vacschoolhours, + to_char(CASE WHEN pd.otherpaidhours::interval = '00:00:00'::interval THEN NULL ELSE pd.otherpaidhours END::interval, 'HH24:MI') AS otherpaidhours, + to_char(CASE WHEN pd.partunemplhours::interval = '00:00:00'::interval THEN NULL ELSE pd.partunemplhours END::interval, 'HH24:MI') AS partunemplhours, + to_char(pd.contracthours::interval, 'HH24:MI') AS contracthours, + to_char(CASE WHEN pd.workhours::interval = '00:00:00'::interval THEN NULL ELSE pd.workhours END::interval, 'HH24:MI') AS workhours, + to_char(CASE WHEN pd.dayhours::interval = '00:00:00'::interval THEN NULL ELSE pd.dayhours END::interval, 'HH24:MI') AS dayhours, + to_char(case WHEN pd.recuperationhours::interval = '00:00:00'::interval THEN NULL ELSE pd.recuperationhours END::interval, 'HH24:MI') AS recuperationhours, + to_char(CASE WHEN pd.interruptionhours::interval = '00:00:00'::interval THEN NULL ELSE pd.interruptionhours END::interval, 'HH24:MI') AS interruptionhours, + to_char(CASE WHEN pd.nighthours::interval = '00:00:00'::interval THEN NULL ELSE pd.nighthours END::interval, 'HH24:MI') AS nighthours, + to_char(pd.pausestart1::interval, 'HH24:MI') AS pausestart1, + to_char(pd.pauseend1::interval, 'HH24:MI') AS pauseend1, + to_char(pd.pausestart2::interval, 'HH24:MI') AS pausestart2, + 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 '
' 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 '
' ELSE '' END || to_char(pd.timestart2::interval, 'HH24:MI')) || '-') || to_char(pd.timeend2::interval, 'HH24:MI'), '') AS dspworkdata, + CASE WHEN max(freehours.freehours) < '00:00:00'::interval THEN NULL ELSE to_char(max(freehours.freehours), 'HH24:MI') END AS freehoursafter, + to_char(coalesce(max(wk.defaultdayhours),max(wkstd.defaultdayhours)),'HH24:MI') as defaultdayhours, + to_char(coalesce(max(wk.maxdayhours),max(wkstd.maxdayhours)),'HH24:MI') as maxdayhours, + to_char(coalesce(max(wk.mindailyrecuperation),max(wkstd.mindailyrecuperation)),'HH24:MI') as mindailyrecuperation, + to_char(coalesce(max(wk.maxinterruptionhours),max(wkstd.maxinterruptionhours)),'HH24:MI') as maxinterruptionhours, + max(vd.vacancyname) as vacancyname, + to_char(max(tt.trackedtime),'HH24:MI') as trackedtime, + max(case when tt.trackedtime is null or pd.workhours is null then null else interval_to_char(coalesce(tt.trackedtime::interval,'00:00:00'::interval) - coalesce(pd.workhours::interval,'00:00:00'::interval)) end) as trackeddiff, + null as incompletetrack + FROM portanova.staffreportperioddays pd + JOIN portanova.staff st ON pd.id_staff = st.id and st.id='eda4fd22-9dcf-4d61-327b-e039b9f48107' and st.isdeleted is null + LEFT JOIN ( SELECT yy.id_staff, yy.ndaydate AS daydate,case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend AS tmfrom, yy.daydate + yy.timestart AS tmto, + yy.daydate + yy.timestart - (case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend) AS realinterval, + date_part('epoch', yy.daydate + yy.timestart - (case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend)) * '00:00:01'::interval AS freehours + 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 + FROM ( SELECT sr.id_staff, sr.daydate, + 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 portanova.staffreportperioddays sr + WHERE sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone and sr.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + ORDER BY sr.id_staff, sr.daydate) xx_1) yy) freehours ON pd.id_staff = freehours.id_staff AND freehours.daydate = pd.daydate + left join portanova.worktimes wk on pd.daydate between wk.startdate and wk.enddate + cross join (select defaultdayhours,maxdayhours,mindailyrecuperation,maxinterruptionhours from portanova.worktimes where isdefault =true) wkstd + left join portanova.vacancydays vd on pd.daydate=vd.daydate + left join portanova.reportperiod rp on (pd.id_reportperiod=rp.id) + left join ( + select + sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) as sumdayall, + string_agg(minpauseok::text,'') as minpauses, null as incompletetrack, + case when sum(minpauseok) = 0 and sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) >= '06:00:00'::interval then sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) - '00:30:00'::interval else sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) end as trackedtime,daydate from + ( select *, tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) as pauseduration, + to_char(lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in),'HH24:MI') || '-' || to_char(tx.stamp_in,'HH24:MI') as pausetime, + case when tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) > '00:30:00'::interval then 1 else 0 end as minpauseok + from portanova.timetracker tx where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and tx.daydate between date('2023-11-27') and date('2024-02-04') + order by tx.stamp_in) tt + group by tt.daydate + ) tt on pd.daydate=tt.daydate + WHERE pd.daydate between date('2023-11-27') and date('2024-02-04') + group by pd.id,rp.viewpartunempl,rp.viewvacschool + ORDER BY pd.id_staff, pd.daydate; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select daydate as datebefore,to_char(case when timestart2 is null then timeend1 else timeend2 end,'HH24:MI') as timeendbefore from portanova.staffreportperioddays where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate < date('2023-12-06') and (timestart1 is not null or timestart2 is not null) order by daydate desc limit 1; +QUERY: select daydate as dateafter ,to_char(case when timestart1 is null then timestart2 else timestart1 end,'HH24:MI') as timestartafter from portanova.staffreportperioddays where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate > date('2023-12-06') and (timestart1 is not null or timestart2 is not null) order by daydate asc limit 1; +QUERY: select weekhours from portanova.staffcontract where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and ((date('2023-12-06') between startdate and enddate) or (startdate <= date('2023-12-06') and enddate is null)) order by startdate,enddate nulls first limit 1; +QUERY: select to_char(wk.maxdayhours,'HH24:MI') as maxdayhours, + to_char(wk.maxweekhours,'HH24:MI') as maxweekhours, + to_char(wk.maxinterruptionhours,'HH24:MI') as maxinterruptionhours, + to_char(wk.mindailyrecuperation,'HH24:MI') as mindailyrecuperation, + to_char(wk.defaultweekhours,'HH24:MI') as defaultweekhours, + '2023-12-05' as datebefore, + '18:30' as timebefore, + '2023-12-07' as dateafter, + '09:30' as timeafter + from portanova.worktimes wk where (date('2023-12-06') between wk.startdate and wk.enddate) or (wk.startdate is null and wk.enddate is null) and maxhourspercents is null + order by wk.startdate,wk.enddate nulls last limit 1; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: select id,id_staff,daydate,to_char(stamp_in,'HH24:MI') as stamp_in,to_char(stamp_out,'HH24:MI') as stamp_out, + case when stamp_in is null or stamp_out is null then null else to_char(stamp_out-stamp_in,'HH24:MI') end as total,tracktype, to_char(stamp_in - lag(stamp_out) over (partition by id_staff,daydate order by stamp_in),'HH24:MI') as pauseduration, + to_char(lag(stamp_out) over (partition by id_staff,daydate order by stamp_in),'HH24:MI') || '-' || to_char(stamp_in,'HH24:MI') as pausetime, + case when stamp_in - lag(stamp_out) over (partition by id_staff,daydate order by stamp_in) > '00:30:00'::interval then 1 else 0 end as minpauseok + from portanova.timetracker where daydate=date('2023-12-06') and id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' order by stamp_in,stamp_out; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: select id,id_staff,daydate,to_char(stamp_in,'HH24:MI') as stamp_in,to_char(stamp_out,'HH24:MI') as stamp_out, + case when stamp_in is null or stamp_out is null then null else to_char(stamp_out-stamp_in,'HH24:MI') end as total,tracktype, to_char(stamp_in - lag(stamp_out) over (partition by id_staff,daydate order by stamp_in),'HH24:MI') as pauseduration, + to_char(lag(stamp_out) over (partition by id_staff,daydate order by stamp_in),'HH24:MI') || '-' || to_char(stamp_in,'HH24:MI') as pausetime, + case when stamp_in - lag(stamp_out) over (partition by id_staff,daydate order by stamp_in) > '00:30:00'::interval then 1 else 0 end as minpauseok + from portanova.timetracker where daydate=date('2023-12-06') and id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' order by stamp_in,stamp_out; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +EXEC: UPDATE portanova.staffreportperioddays SET timestart1='09:30',timeend1='18:30',timestart2=null,timeend2=null,pausestart1='14:30',pauseend1='15:30',pausestart2=null,pauseend2=null,timepause='01:00',timepausetracked='00:00',workhourstracked='00:00',daycomment=null,vacancycomment=null,vacancyhours=null,vacillcomment=null,vacillhours=null,vacextracomment=null,vacextrahours=null,vacschoolcomment=null,vacschoolhours=null,recupcomment=null,recuperationhours=null,partunempcomment=null,partunemplhours=null,otherpaidhourscomment=null,otherpaidhours=null,dayhourstracked='00:00' WHERE id='4e853d22-d4b6-bce5-5e98-51a64b5f350e'; +QUERYARRAY: select daydate from portanova.vacancydays v where daydate between date(date_trunc('week',date('2023-12-06'))) and date(date_trunc('week',date('2023-12-06')) + interval '6 days'); +QUERY: select id from portanova.reportperiod where date('2023-12-06') between startdate and enddate; +QUERY: select date(date_trunc('year',startdate)) as yearstart ,* from portanova.reportperiod where id='83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e'; +EXEC: update portanova.staffreportperioddays spds set + workhours=ds.worktime, timepause=case when ds.timepause::interval > '00:00:00' then ds.timepause else null end, + dayhours=(ds.worktime::interval + ds.vachours::interval + ds.vacillhours::interval + ds.vacextrahours::interval + ds.otherpaidhours::interval + ds.recup::interval + ds.partunemplhours )::interval, interruptionhours = ds.interruption , + contracthours=case when ds.worktime::interval + ds.vachours::interval + ds.vacillhours::interval + ds.vacextrahours::interval + ds.otherpaidhours::interval + ds.recup::interval + ds.partunemplhours::interval > '00:00:00'::interval then ds.wdcontracthours else null end + from ( + select preds.id,case when preds.recuperationhours is null then '00:00:00'::interval else preds.recuperationhours::interval end as recup, + case when preds.vacancyhours is null then '00:00:00'::time else preds.vacancyhours end as vachours, + case when preds.vacillhours is null then '00:00:00'::time else preds.vacillhours end as vacillhours, + case when preds.vacextrahours is null then '00:00:00'::time else preds.vacextrahours end as vacextrahours, + case when preds.otherpaidhours is null then '00:00:00'::time else preds.otherpaidhours end as otherpaidhours, + case when preds.partunemplhours is null then '00:00:00'::time else preds.partunemplhours end as partunemplhours, + preds.interruption, + (preds.dt1+preds.dt2)-(preds.pdt1+preds.pdt2) as worktime, + (preds.pdt1+preds.pdt2) as timepause, + wdcontracthours + from ( select id, recuperationhours,vacancyhours,vacillhours,vacextrahours,otherpaidhours,payedpause,partunemplhours,nighthours, + case when timestart1 is not null and timeend1 is not null then case when timeend1 < timestart1 then '24:00:00'::interval + timeend1::interval - timestart1::interval else timeend1::interval - timestart1::interval end else '00:00:00'::interval end as dt1, + case when timestart2 is not null and timeend2 is not null then case when timeend2 < timestart2 then '24:00:00'::interval + timeend2::interval - timestart2::interval else timeend2::interval - timestart2::interval end else '00:00:00'::interval end as dt2, + case when pausestart1 between timestart1 and case when timeend1 < timestart1 then timeend1::interval + '24:00:00'::interval else timeend1::interval end and pauseend1 between timestart1 and case when timeend1 < timestart1 then timeend1::interval + '24:00:00'::interval else timeend1::interval end then case when pauseend1 < pausestart1 then '24:00:00'::interval + pauseend1::interval - pausestart1::interval else pauseend1::interval - pausestart1::interval end else '00:00:00'::interval end as pdt1, + case when pausestart1 between timestart2 and case when timeend2 < timestart2 then timeend2::interval + '24:00:00'::interval else timeend2::interval end and pauseend2 between timestart2 and case when timeend2 < timestart2 then timeend2::interval + '24:00:00'::interval else timeend2::interval end then case when pauseend2 < pausestart2 then '24:00:00'::interval + pauseend2::interval - pausestart2::interval else pauseend2::interval - pausestart2::interval end else '00:00:00'::interval end as pdt2, + case when timestart2 is not null and timeend1 is not null and timestart1 is not null and timeend2 is not null then + timestart2::interval - timeend1::interval + else null end as interruption, + wdcontracthours + from portanova.staffreportperioddays where id in (select id from portanova.staffreportperioddays where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days')) + ) preds) ds where spds.id=ds.id; +EXEC: update portanova.staffreportperioddays SET nighthours = null where id in (select id from portanova.staffreportperioddays where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days')); +EXEC: update portanova.staffreportperioddays spds set + nighthours=nh from ( + select id,daydate,timestart1,timeend1, timestart2,timeend2, + coalesce((case when timeend1 not between '01:00:01'::interval and '05:59:59'::interval then '06:00:00'::interval else timeend1::interval end) - + (case when timestart1 not between '01:00:01'::interval and '05:59:59'::interval then '01:00:00'::interval else timestart1::interval end),'00:00:00'::interval) + + coalesce((case when timeend2 not between '01:00:01'::interval and '05:59:59'::interval then '06:00:00'::interval else timeend2::interval end) - + (case when timestart2 not between '01:00:01'::interval and '05:59:59'::interval then '01:00:00'::interval else timestart2::interval end),'00:00:00'::interval) as nh + from portanova.staffreportperioddays s where + (timeend1 between '01:00:01' and '05:59:59' or timeend2 between '01:00:01' and '05:59:59' + or timestart1 between '01:00:01' and '05:59:59' or timestart2 between '01:00:01' and '05:59:59') + and id in (select id from portanova.staffreportperioddays where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days')) + ) ds + where spds.id=ds.id +QUERY: 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,row_number() over (order by daydate) as rowx from portanova.staffreportperioddays + where daydate between date('2023-01-01') and date('2023-12-31') + and date_part('isodow',daydate)::int4 = 7 and workhours > '00:00:00'::time + and id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' order by daydate + ) xsunday where daydate between date('2023-12-01') and date('2024-01-31') + ) sunresult; +QUERY: select id_period, case when sum(plusdays) over (order by startdate) >= 6 then 6 else case when plusdays = 0 then null else plusdays end end as plusdays from ( + select id_period,sum(week44s) as plusdays,startdate from ( + select weekstart,case when (sum(sumx) over (order by weekstart)%8)::integer = 0 then 0 else 1 end as week44s,r.id as id_period,r.startdate,r.enddate from ( + select weekstart,case when sum(cnt44) > 0 then 0 else 1 end as sumx, + max(daydate) as maxdate from ( + SELECT date(date_trunc('week',yy.daydate)) as weekstart,daydate, dayhours, + (date_part('epoch', yy.daydate + yy.timestart - (coalesce(yy.ndaydate,date('2023-01-01')) + coalesce(yy.ntimeend,'00:00:00'::time without time zone))) * '00:00:01'::interval) as xx,yy.timestart,yy.ntimeend, + case when (date_part('epoch', yy.daydate + yy.timestart - (coalesce(yy.ndaydate,date('2023-01-01')) + coalesce(yy.ntimeend,'00:00:00'::time without time zone))) * '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,xx_1.dayhours + FROM ( SELECT sr.id_staff, sr.daydate,sr.dayhours, + 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 portanova.staffreportperioddays sr + WHERE sr.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and sr.daydate between date('2023-01-01') and date(date_trunc('week',date('2023-01-01') + interval '1 year') - interval '1 day') + and sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone + and (sr.timestart1 IS NOT null or sr.timestart2 IS NOT null) + ORDER BY sr.id_staff, sr.daydate) xx_1) yy order by yy.daydate,weekstart + ) aa group by aa.weekstart order by weekstart + + ) bb + join portanova.reportperiod r on (bb.weekstart between r.startdate and r.enddate) + group by weekstart,r.id,bb.sumx order by weekstart + + ) ff group by id_period,startdate) ss + where id_period='83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e' + group by id_period,startdate,ss.plusdays; +QUERY: select weekstart,id_staff,avgtotalhours from ( + select ws.weekstart, ws.id_staff, + AVG(ws.totalhours) over (order by ws.weekstart) as avgtotalhours + from ( + SELECT weekstart, id_staff, +sum(CASE WHEN srpd.dayhours IS NULL THEN '00:00:00'::interval ELSE srpd.dayhours::interval END) AS totalhours + FROM (select *,date(date_trunc('week', daydate)) AS weekstart, + date(date_trunc('week', daydate) + interval '6 days') AS weekend, + date_part('week', daydate) AS calweek, + date_part('isoyear', daydate) AS calyear from portanova.staffreportperioddays + where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date(date_trunc('week',date('2023-12-01'))) and date(date_trunc('week',date('2024-01-31')) + interval '6 days') + --where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date('2023-12-01') and date('2024-01-31') + ) srpd GROUP BY srpd.weekstart,srpd.id_staff + ORDER BY weekstart) ws) wsavg order by weekstart DESC LIMIT 1; +EXEC: update portanova.staffreportperiod rpd set + --contracthours=csp.contracthours, + workhours=csp.workhours, + vacancyill=csp.vacillhours, + vacancynormal=csp.vacancynormal, + vacancyextra=csp.vacextrahours, + otherpaidhours=csp.otherpaidhours, + partunemplhours=csp.partunemplhours, + totalhours=csp.totalhours, + nighthours=csp.nighthours, + recuperationhours=csp.recuperationhours,statussuncount=3,suppvacancysunwork=null,suppvacancy44hours=null,status44hcount=null,last44date=null,avgtotalweekhours=null,hoursdiff=csp.diffhours + FROM ( + select contracthours,workhours,totalhours,recuperationhours, totalhours-contracthours as diffhours,id,vacillhours,vacancynormal,vacextrahours,otherpaidhours,partunemplhours,nighthours from ( + select dp.id, + sum(pd.workhours) as workhours, + sum(pd.dayhours) as totalhours, + sum(coalesce(pd.nighthours,'00:00:00'::interval)) as nighthours, + sum(coalesce(pd.vacancyhours,'00:00:00'::time)) as vacancynormal, + sum(coalesce(pd.vacillhours,'00:00:00'::time)) as vacillhours, + sum(coalesce(pd.vacextrahours,'00:00:00'::time)) as vacextrahours, + sum(coalesce(pd.otherpaidhours,'00:00:00'::time)) as otherpaidhours, + sum(coalesce(pd.partunemplhours,'00:00:00'::time)) as partunemplhours, + sum(coalesce(pd.recuperationhours,'00:00:00'::time)) as recuperationhours, + coalesce(dp.contracthours,'00:00:00'::interval) as contracthours, + coalesce(dp.payedhours0,'00:00:00'::interval) as payedhours0, + coalesce(dp.payedhours40,'00:00:00'::interval) as payedhours40, + coalesce(dp.transferedhours,'00:00:00'::interval) as transferedhours + from portanova.staffreportperioddays pd + join portanova.staffreportperiod dp on (pd.id_staff=dp.id_staff and dp.id_reportperiod = '83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e') + where pd.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and pd.daydate between date('2023-12-01') and date('2024-01-31') + group by dp.id,dp.payedhours,dp.transferedhourscalc + ) rp) csp where csp.id=rpd.id; +QUERYARRAY: SELECT st.prename, st.surname, coalesce(st.surname || ' ','') || coalesce(st.prename,'') as name, + to_char(srp.contracthours, 'HH24:MI'::text) AS contracthours, + to_char(srp.workhours, 'HH24:MI'::text) AS workhours, + --to_char(srp.vacancyhours, 'HH24:MI'::text) AS vacancyhours, + to_char(srp.vacancyill, 'HH24:MI'::text) AS vacancyill, + to_char(srp.vacancynormal, 'HH24:MI'::text) AS vacancynormal, + to_char(srp.vacancyextra, 'HH24:MI'::text) AS vacancyextra, + to_char(srp.otherpaidhours, 'HH24:MI'::text) AS otherpaidhours, + to_char(srp.partunemplhours, 'HH24:MI'::text) AS partunemplhours, + to_char(srp.recuperationhours, 'HH24:MI'::text) AS recuperationhours, + to_char(srp.nighthours, 'HH24:MI'::text) AS nighthours, + case when position('-' in to_char(srp.hoursdiff, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.hoursdiff, 'HH24:MI'),'-','') else to_char(srp.hoursdiff, 'HH24:MI') end as hoursdiff, + to_char(srp.totalhours, 'HH24:MI'::text) AS totalhours, + srp.id as id_staffreportperiod,srp.id_reportperiod, srp.id_staff, srp.id, rp.startdate, rp.enddate, + COALESCE(st.surname || ' '::text, ''::text) || COALESCE(st.prename, ''::text) AS staffname, + st.id_staffgroup, sgr.groupname, + case when position('-' in to_char(srp.transferedhours, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.transferedhours, 'HH24:MI'),'-','') else to_char(srp.transferedhours, 'HH24:MI') end as transferedhours, + 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 + ,itmcontracthours,maxdays,srp.isvalidated, + to_char(srp.payedhours40calc,'HH24:MI'::text) AS payedhours, + to_char(payedhours0, 'HH24:MI'::text) as payedhours0, + to_char(payedhours40, 'HH24:MI'::text) as payedhours40, + to_char(payedhours40calc, 'HH24:MI'::text) as payedhours40calc, + to_char(hourstotransfer, 'HH24:MI'::text) as hourstotransfer, + to_char(hourstotransfercalc, 'HH24:MI'::text) as hourstotransfercalc, + to_char(nighthourstotransfer, 'HH24:MI'::text) as nighthourstotransfer, + to_char(nighthourstotransfercalc, 'HH24:MI'::text) as nighthourstotransfercalc, + to_char(srp.avgtotalweekhours, 'HH24:MI'::text) AS avgtotalweekhours + FROM portanova.staffreportperiod srp + LEFT JOIN portanova.staff st ON srp.id_staff = st.id + LEFT JOIN portanova.reportperiod rp ON srp.id_reportperiod = rp.id + LEFT JOIN portanova.staffgroups sgr ON st.id_staffgroup = sgr.id + WHERE st.isdeleted IS NULL AND srp.id_staff = 'eda4fd22-9dcf-4d61-327b-e039b9f48107' + ORDER BY st.surname, st.prename, srp.id_staff, rp.startdate DESC, rp.enddate DESC; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERY: select date(date_trunc('week',startdate)) as startdate,startdate as periodstart, enddate as periodend,date(date_trunc('week',enddate) + interval '6 days') as enddate from portanova.reportperiod where id='83cf3c4f-62f1-b8a8-c377-d5ae8445dc1e' +QUERYBYKEY: select ws.weekstart,ws.weekend,ws.calweek,ws.calyear, ws.id_staff + ,to_char(ws.workhours::interval,'HH24:MI') as workhours + ,to_char(ws.vacancyhours,'HH24:MI') as vacancynormal + ,to_char(ws.vacillhours,'HH24:MI') as vacancyill + ,to_char(ws.vacextrahours,'HH24:MI') as vacancyextra + ,to_char(ws.vacschoolhours,'HH24:MI') as vacancyschool + ,to_char(ws.otherpaidhours,'HH24:MI') as otherpaidhours + ,to_char(ws.partunemplhours,'HH24:MI') as partunemplhours + ,to_char(ws.recuperationhours,'HH24:MI') as recuperationhours + ,to_char(ws.totalhours,'HH24:MI') as totalhours + ,cd.contractworkdays + ,cd.cmpcontractdays + ,cd.cmpweekhours + ,to_char(coalesce(ttr.weektrackedhours,'00:00:00'::interval),'HH24:MI') as weektrackedhours + ,ws.periodworkhours + ,interval_to_char(sum(coalesce(ttr.weektrackedhours,'00:00:00'::interval)) over (order by ws.weekstart) - sum(ws.periodworkhours::interval) over (order by ws.weekstart)) as periodtrackdiff + ,sum(ws.workhours::interval) over (order by ws.weekstart) as runningworkhours + ,interval_to_char(coalesce(ttr.weektrackedhours,'00:00:00'::interval) - ws.periodworkhours::interval) as trackeddiff + ,case when cd.contractworkdays < cd.cmpcontractdays then to_char(cd.contractworkdays*'08:00:00'::interval,'HH24:MI') else to_char(cd.contracthours,'HH24:MI') end as contracthours + ,to_char(AVG(ws.totalhours) over (order by ws.weekstart),'HH24:MI') as avgtotalhours + ,case when cd.contractworkdays < cd.cmpcontractdays then '00:00' else interval_to_char(ws.totalhours-cd.contracthours) end as diffhours + ,case when cd.contractworkdays < cd.cmpcontractdays then + to_char(case when cd.cmpweekhours < '40:00:00'::interval then cd.contracthours * 1.15 * cd.contractworkdays else (cd.contractworkdays*'08:00:00'::interval) + (cd.contractworkdays* case when maxdata.maxweekhours > '48:00:00' then '01:16:50'::interval else '00:50:00'::interval end) end,'HH24:MI') + else + to_char(case when cd.contracthours < '40:00:00'::interval then cd.contracthours * 1.15 else maxdata.maxweekhours end,'HH24:MI') + end as maxweekhours + ,case when cd.contractworkdays < cd.cmpcontractdays then to_char(cd.contractworkdays*'08:00:00'::interval,'HH24:MI') else + to_char(case when cd.contracthours < '40:00:00'::interval then cd.contracthours else maxdata.defaultweekhours end,'HH24:MI') + end as defaultweekhours + from ( + SELECT weekstart,weekend,calweek,calyear, id_staff, + sum(CASE WHEN srpd.workhours IS NULL THEN '00:00:00'::interval ELSE srpd.workhours::interval END) AS workhours, + sum(case when srpd.daydate between date('2023-12-01') and date('2024-01-31') then coalesce(srpd.workhours::interval, '00:00:00'::interval) else '00:00:00'::interval end) as periodworkhours, + --sum(case when srpd.daydate between date('2023-12-01') and date('2024-01-31') then coalesce(srpd.workhours::interval, '00:00:00'::interval) else '00:00:00'::interval end) as periodworkhoursnopause, + sum(CASE WHEN srpd.vacancyhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacancyhours::interval END) AS vacancyhours, + sum(CASE WHEN srpd.vacillhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacillhours::interval END) AS vacillhours, + sum(CASE WHEN srpd.vacextrahours IS NULL THEN '00:00:00'::interval ELSE srpd.vacextrahours::interval END) AS vacextrahours, + sum(CASE WHEN srpd.vacschoolhours IS NULL THEN '00:00:00'::interval ELSE srpd.vacschoolhours::interval END) AS vacschoolhours, + sum(CASE WHEN srpd.recuperationhours IS NULL THEN '00:00:00'::interval ELSE srpd.recuperationhours::interval END) AS recuperationhours, + sum(CASE WHEN srpd.otherpaidhours IS NULL THEN '00:00:00'::interval ELSE srpd.otherpaidhours::interval END) AS otherpaidhours, + sum(CASE WHEN srpd.partunemplhours IS NULL THEN '00:00:00'::interval ELSE srpd.partunemplhours::interval END) AS partunemplhours, + sum(CASE WHEN srpd.dayhours IS NULL THEN '00:00:00'::interval ELSE srpd.dayhours::interval END) AS totalhours, + sum(CASE WHEN srpd.workhours > '00:00:00'::time without time zone OR srpd.recuperationhours > '00:00:00'::time without time zone OR srpd.vacancyhours > '00:00:00'::time without time zone OR srpd.vacillhours > '00:00:00'::time without time zone OR srpd.vacextrahours > '00:00:00'::time without time zone OR srpd.vacschoolhours > '00:00:00'::time without time zone OR srpd.otherpaidhours > '00:00:00'::time without time zone or srpd.partunemplhours > '00:00:00'::time without time zone THEN 1 ELSE 0 END) AS workdays, + sum( CASE WHEN srpd.contracthours IS NULL THEN '00:00:00'::interval ELSE srpd.contracthours::interval END) AS contracthours + FROM (select *,date(date_trunc('week', daydate)) AS weekstart, + date(date_trunc('week', daydate) + interval '6 days') AS weekend, + date_part('week', daydate) AS calweek, + date_part('isoyear', daydate) AS calyear from portanova.staffreportperioddays + where id_staff= 'eda4fd22-9dcf-4d61-327b-e039b9f48107' and daydate between date('2023-11-27') and date('2024-02-04') + ) srpd GROUP BY srpd.weekstart,srpd.weekend,srpd.calweek,srpd.calyear,srpd.id_staff + ORDER BY weekstart) ws left join ( + select date(date_trunc('week',pd.daydate)) as weekstart,pd.id_staff,max(sc.weekdays) as cmpcontractdays,max(sc.weekhours) as cmpweekhours, + case when count(pd.daydate) < avg(sc.weekdays) then count(pd.daydate) else avg(sc.weekdays) end as contractworkdays, + case when count(pd.daydate) < avg(sc.weekdays) then avg(sc.weekhours/ sc.weekdays) * count(pd.daydate) else avg(sc.weekhours/ sc.weekdays) * avg(sc.weekdays) end as contracthours ,sum(case when coalesce(pd.workhours,'00:00:00'::time) > '00:00:00'::time then 1 else 0 end) as realworkdays + from ( select id_staff,weekdays ,weekhours, + case when startdate> date('2023-11-27') then startdate else date('2023-11-27') end as staffperiodstart, + case when enddate is null or enddate > date('2024-02-04') then date('2024-02-04') + else enddate end as staffperiodend from portanova.staffcontract where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + and startdate < date('2024-02-04') + ) sc + join portanova.staffreportperioddays pd on (pd.id_staff=sc.id_staff and pd.daydate between sc.staffperiodstart and sc.staffperiodend) + group by date(date_trunc('week',pd.daydate)),pd.id_staff + ) cd on ws.id_staff = cd.id_staff and ws.weekstart=cd.weekstart + left join ( + select id_staff,weekstart,max(defaultweekhours) as defaultweekhours,avg(maxweekhours) as maxweekhours from ( + select lpd.id_staff,date(date_trunc('week',lpd.daydate)) as weekstart,lpd.daydate, + case when wk.defaultweekhours is null then wkdef.defaultweekhours else wk.defaultweekhours end as defaultweekhours , + case when wk.maxweekhours is null then wkdef.maxweekhours else wk.maxweekhours end as maxweekhours + from portanova.staffreportperioddays lpd + left join portanova.worktimes wk on (lpd.daydate between wk.startdate and wk.enddate) + cross join (select * from portanova.worktimes where isdefault=true) wkdef + where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and lpd.daydate between date('2023-11-27') and date('2024-02-04') + order by lpd.daydate ) mh group by id_staff,weekstart + ) maxdata on ws.id_staff = maxdata.id_staff and ws.weekstart=maxdata.weekstart + left join ( + select weekstart,id_staff,coalesce(sum(daytracked),'00:00:00'::interval) as weektrackedhours from ( + select daydate,id_staff,weekstart,case when sum(trackedhours) > '06:00:00'::interval then sum(trackedhours)- '00:30:00'::interval else coalesce(sum(trackedhours),'00:00:00'::interval) end as daytracked from ( +select daydate,date(date_trunc('week',daydate)) as weekstart,id_staff,(stamp_out-stamp_in)::interval as trackedhours from portanova.timetracker +where daydate between date('2023-12-01') and date('2024-01-31') and id_staff ='eda4fd22-9dcf-4d61-327b-e039b9f48107') tto group by daydate,id_staff,weekstart,trackedhours) ttx group by weekstart,id_staff + ) ttr on ws.weekstart=ttr.weekstart and ws.id_staff=ttr.id_staff; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: SELECT pd.id,pd.id_staff,pd.id_reportperiod,pd.daydate,pd.payedpause,pd.daytype,pd.daycomment, + vacancycomment,vacillcomment,vacextracomment,recupcomment,otherpaidhourscomment,partunempcomment, + pd.daytemplate,pd.vacschoolcomment,rp.viewpartunempl,rp.viewvacschool, + date(date_trunc('week',pd.daydate)) as weekstart, + to_char(pd.timestart1::interval, 'HH24:MI') AS timestart1, + to_char(pd.timeend1::interval, 'HH24:MI') AS timeend1, + to_char(pd.timestart2::interval, 'HH24:MI') AS timestart2, + to_char(pd.timeend2::interval, 'HH24:MI') AS timeend2, + to_char(pd.timepause::interval, 'HH24:MI') AS timepause, + -- coalesce('
' || to_char(CASE WHEN pd.vacancyhours::interval = '00:00:00'::interval THEN null ELSE pd.vacancyhours END::interval, 'HH24:MI') || '
','') || + -- coalesce('
' || to_char(CASE WHEN pd.vacillhours::interval = '00:00:00'::interval THEN null else pd.vacillhours END::interval, 'HH24:MI') || '
','') || + -- coalesce('
' || to_char(CASE WHEN pd.vacextrahours::interval = '00:00:00'::interval THEN null ELSE pd.vacextrahours END::interval, 'HH24:MI') || '
','') AS dspvacancyhours, + to_char(CASE WHEN pd.vacancyhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacancyhours END::interval, 'HH24:MI') AS vacancyhours, + to_char(CASE WHEN pd.vacillhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacillhours END::interval, 'HH24:MI') AS vacillhours, + to_char(CASE WHEN pd.vacextrahours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacextrahours END::interval, 'HH24:MI') AS vacextrahours, + to_char(CASE WHEN pd.vacschoolhours::interval = '00:00:00'::interval THEN NULL ELSE pd.vacschoolhours END::interval, 'HH24:MI') AS vacschoolhours, + to_char(CASE WHEN pd.otherpaidhours::interval = '00:00:00'::interval THEN NULL ELSE pd.otherpaidhours END::interval, 'HH24:MI') AS otherpaidhours, + to_char(CASE WHEN pd.partunemplhours::interval = '00:00:00'::interval THEN NULL ELSE pd.partunemplhours END::interval, 'HH24:MI') AS partunemplhours, + to_char(pd.contracthours::interval, 'HH24:MI') AS contracthours, + to_char(CASE WHEN pd.workhours::interval = '00:00:00'::interval THEN NULL ELSE pd.workhours END::interval, 'HH24:MI') AS workhours, + to_char(CASE WHEN pd.dayhours::interval = '00:00:00'::interval THEN NULL ELSE pd.dayhours END::interval, 'HH24:MI') AS dayhours, + to_char(case WHEN pd.recuperationhours::interval = '00:00:00'::interval THEN NULL ELSE pd.recuperationhours END::interval, 'HH24:MI') AS recuperationhours, + to_char(CASE WHEN pd.interruptionhours::interval = '00:00:00'::interval THEN NULL ELSE pd.interruptionhours END::interval, 'HH24:MI') AS interruptionhours, + to_char(CASE WHEN pd.nighthours::interval = '00:00:00'::interval THEN NULL ELSE pd.nighthours END::interval, 'HH24:MI') AS nighthours, + to_char(pd.pausestart1::interval, 'HH24:MI') AS pausestart1, + to_char(pd.pauseend1::interval, 'HH24:MI') AS pauseend1, + to_char(pd.pausestart2::interval, 'HH24:MI') AS pausestart2, + 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 '
' 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 '
' ELSE '' END || to_char(pd.timestart2::interval, 'HH24:MI')) || '-') || to_char(pd.timeend2::interval, 'HH24:MI'), '') AS dspworkdata, + CASE WHEN max(freehours.freehours) < '00:00:00'::interval THEN NULL ELSE to_char(max(freehours.freehours), 'HH24:MI') END AS freehoursafter, + to_char(coalesce(max(wk.defaultdayhours),max(wkstd.defaultdayhours)),'HH24:MI') as defaultdayhours, + to_char(coalesce(max(wk.maxdayhours),max(wkstd.maxdayhours)),'HH24:MI') as maxdayhours, + to_char(coalesce(max(wk.mindailyrecuperation),max(wkstd.mindailyrecuperation)),'HH24:MI') as mindailyrecuperation, + to_char(coalesce(max(wk.maxinterruptionhours),max(wkstd.maxinterruptionhours)),'HH24:MI') as maxinterruptionhours, + max(vd.vacancyname) as vacancyname, + to_char(max(tt.trackedtime),'HH24:MI') as trackedtime, + max(case when tt.trackedtime is null or pd.workhours is null then null else interval_to_char(coalesce(tt.trackedtime::interval,'00:00:00'::interval) - coalesce(pd.workhours::interval,'00:00:00'::interval)) end) as trackeddiff, + null as incompletetrack + FROM portanova.staffreportperioddays pd + JOIN portanova.staff st ON pd.id_staff = st.id and st.id='eda4fd22-9dcf-4d61-327b-e039b9f48107' and st.isdeleted is null + LEFT JOIN ( SELECT yy.id_staff, yy.ndaydate AS daydate,case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend AS tmfrom, yy.daydate + yy.timestart AS tmto, + yy.daydate + yy.timestart - (case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend) AS realinterval, + date_part('epoch', yy.daydate + yy.timestart - (case when yy.ntimeend < yy.timestart then yy.daydate else yy.ndaydate end + yy.ntimeend)) * '00:00:01'::interval AS freehours + 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 + FROM ( SELECT sr.id_staff, sr.daydate, + 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 portanova.staffreportperioddays sr + WHERE sr.dayhours IS NOT NULL AND sr.dayhours <> '00:00:00'::time without time zone and sr.id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' + ORDER BY sr.id_staff, sr.daydate) xx_1) yy) freehours ON pd.id_staff = freehours.id_staff AND freehours.daydate = pd.daydate + left join portanova.worktimes wk on pd.daydate between wk.startdate and wk.enddate + cross join (select defaultdayhours,maxdayhours,mindailyrecuperation,maxinterruptionhours from portanova.worktimes where isdefault =true) wkstd + left join portanova.vacancydays vd on pd.daydate=vd.daydate + left join portanova.reportperiod rp on (pd.id_reportperiod=rp.id) + left join ( + select + sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) as sumdayall, + string_agg(minpauseok::text,'') as minpauses, null as incompletetrack, + case when sum(minpauseok) = 0 and sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) >= '06:00:00'::interval then sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) - '00:30:00'::interval else sum(coalesce((stamp_out-stamp_in)::interval,'00:00:00')) end as trackedtime,daydate from + ( select *, tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) as pauseduration, + to_char(lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in),'HH24:MI') || '-' || to_char(tx.stamp_in,'HH24:MI') as pausetime, + case when tx.stamp_in - lag(tx.stamp_out) over (partition by tx.id_staff,tx.daydate order by tx.stamp_in) > '00:30:00'::interval then 1 else 0 end as minpauseok + from portanova.timetracker tx where id_staff='eda4fd22-9dcf-4d61-327b-e039b9f48107' and tx.daydate between date('2023-11-27') and date('2024-02-04') + order by tx.stamp_in) tt + group by tt.daydate + ) tt on pd.daydate=tt.daydate + WHERE pd.daydate between date('2023-11-27') and date('2024-02-04') + group by pd.id,rp.viewpartunempl,rp.viewvacschool + ORDER BY pd.id_staff, pd.daydate; +QUERY: select se.id as idsession,us.lang,us.id,us.username,ugrp.id as usergroup,replace(replace(replace(us.id_staffgroups::text,'"',''),'[',''),']','') as staffgroups,se.sessiondata from sessions se + join users us on (us.id=se.id_user) + left join usergroups ugrp on (ugrp.id=us.id_usergroup) + where se.id= 'd3012122-762e-cc6c-0e78-7fb6787225bd' and se.remote_addr= '::1' and se.user_agent ='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36' and + us.isblocked is null group by se.id,us.id,ugrp.id; +QUERYARRAY: SELECT st.prename, st.surname, coalesce(st.surname || ' ','') || coalesce(st.prename,'') as name, + to_char(srp.contracthours, 'HH24:MI'::text) AS contracthours, + to_char(srp.workhours, 'HH24:MI'::text) AS workhours, + --to_char(srp.vacancyhours, 'HH24:MI'::text) AS vacancyhours, + to_char(srp.vacancyill, 'HH24:MI'::text) AS vacancyill, + to_char(srp.vacancynormal, 'HH24:MI'::text) AS vacancynormal, + to_char(srp.vacancyextra, 'HH24:MI'::text) AS vacancyextra, + to_char(srp.otherpaidhours, 'HH24:MI'::text) AS otherpaidhours, + to_char(srp.partunemplhours, 'HH24:MI'::text) AS partunemplhours, + to_char(srp.recuperationhours, 'HH24:MI'::text) AS recuperationhours, + to_char(srp.nighthours, 'HH24:MI'::text) AS nighthours, + case when position('-' in to_char(srp.hoursdiff, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.hoursdiff, 'HH24:MI'),'-','') else to_char(srp.hoursdiff, 'HH24:MI') end as hoursdiff, + to_char(srp.totalhours, 'HH24:MI'::text) AS totalhours, + srp.id as id_staffreportperiod,srp.id_reportperiod, srp.id_staff, srp.id, rp.startdate, rp.enddate, + COALESCE(st.surname || ' '::text, ''::text) || COALESCE(st.prename, ''::text) AS staffname, + st.id_staffgroup, sgr.groupname, + case when position('-' in to_char(srp.transferedhours, 'HH24:MI')) > 0 then '-' || replace(to_char(srp.transferedhours, 'HH24:MI'),'-','') else to_char(srp.transferedhours, 'HH24:MI') end as transferedhours, + 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 + ,itmcontracthours,maxdays,srp.isvalidated, + to_char(srp.payedhours40calc,'HH24:MI'::text) AS payedhours, + to_char(payedhours0, 'HH24:MI'::text) as payedhours0, + to_char(payedhours40, 'HH24:MI'::text) as payedhours40, + to_char(payedhours40calc, 'HH24:MI'::text) as payedhours40calc, + to_char(hourstotransfer, 'HH24:MI'::text) as hourstotransfer, + to_char(hourstotransfercalc, 'HH24:MI'::text) as hourstotransfercalc, + to_char(nighthourstotransfer, 'HH24:MI'::text) as nighthourstotransfer, + to_char(nighthourstotransfercalc, 'HH24:MI'::text) as nighthourstotransfercalc, + to_char(srp.avgtotalweekhours, 'HH24:MI'::text) AS avgtotalweekhours + FROM portanova.staffreportperiod srp + LEFT JOIN portanova.staff st ON srp.id_staff = st.id + LEFT JOIN portanova.reportperiod rp ON srp.id_reportperiod = rp.id + LEFT JOIN portanova.staffgroups sgr ON st.id_staffgroup = sgr.id + WHERE st.isdeleted IS NULL AND srp.id_staff = 'eda4fd22-9dcf-4d61-327b-e039b9f48107' + ORDER BY st.surname, st.prename, srp.id_staff, rp.startdate DESC, rp.enddate DESC; diff --git a/app_plandutravail_lu/tmpl/module/js/pot/request.min.js b/app_plandutravail_lu/tmpl/module/js/pot/request.min.js index 3f8a9aed..d072dbf1 100644 --- a/app_plandutravail_lu/tmpl/module/js/pot/request.min.js +++ b/app_plandutravail_lu/tmpl/module/js/pot/request.min.js @@ -1 +1 @@ -var api=location.origin+"/";if("https://app.plandutravail.lu"!=location.origin&&"https://demo.plandutravail.lu"!=location.origin)var api=location.origin+"/app_plandutravail_lu/";var Base64={_keyStr:"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",encode:function(e){var t="",n,r,i,s,o,u,a,f=0;for(e=Base64._utf8_encode(e);f>2,o=(3&n)<<4|(r=e.charCodeAt(f++))>>4,u=(15&r)<<2|(i=e.charCodeAt(f++))>>6,a=63&i,isNaN(r)?u=a=64:isNaN(i)&&(a=64),t=t+this._keyStr.charAt(s)+this._keyStr.charAt(o)+this._keyStr.charAt(u)+this._keyStr.charAt(a);return t},decode:function(e){var t="",n,r,i,s,o,u,a,f=0;for(e=e.replace(/[^A-Za-z0-9\+\/\=]/g,"");f>4,r=(15&o)<<4|(u=this._keyStr.indexOf(e.charAt(f++)))>>2,i=(3&u)<<6|(a=this._keyStr.indexOf(e.charAt(f++))),t+=String.fromCharCode(n),64!=u&&(t+=String.fromCharCode(r)),64!=a&&(t+=String.fromCharCode(i));return t=Base64._utf8_decode(t)},_utf8_encode:function(e){e=e.replace(/\r\n/g,"\n");for(var t="",n=0;n127&&r<2048?(t+=String.fromCharCode(r>>6|192),t+=String.fromCharCode(63&r|128)):(t+=String.fromCharCode(r>>12|224),t+=String.fromCharCode(r>>6&63|128),t+=String.fromCharCode(63&r|128))}return t},_utf8_decode:function(e){for(var t="",n=0,r=c1=c2=0;n191&&r<224?(c2=e.charCodeAt(n+1),t+=String.fromCharCode((31&r)<<6|63&c2),n+=2):(c2=e.charCodeAt(n+1),c3=e.charCodeAt(n+2),t+=String.fromCharCode((15&r)<<12|(63&c2)<<6|63&c3),n+=3);return t}};async function postData(data={}){const response=await fetch(api+"db.php",{method:"POST",mode:"same-origin",cache:"no-cache",credentials:"same-origin",headers:{"Content-Type":"application/json"},redirect:"follow",referrerPolicy:"strict-origin",body:JSON.stringify(data)});var x=response.json();return x||{}}async function postReportData(data={}){const response=await fetch(api+"report.php",{method:"POST",mode:"same-origin",cache:"no-cache",credentials:"same-origin",headers:{"Content-Type":"application/json"},redirect:"follow",referrerPolicy:"strict-origin",body:JSON.stringify(data)});var x=response.json();return x||{}}let report={generate:function(type,repname,data){showdataloaddlg("{{lbl.createpdf}}","{{lbl.waitmsg}}");var windowReference=window.open();return console.log("senddata",{type:type,name:repname,data:data}),postReportData({type:type,name:repname,data:data}).then(data=>{console.log("data returned",data),windowReference.location=api+"file.php?open="+encodeURIComponent(data.result.file),closedataloaddlg()}).catch(e=>{closedataloaddlg()}),!1},exportXLSX:function(data){showdataloaddlg("{{lbl.createpdf}}","{{lbl.waitmsg}}");var windowReference=window.open();return data.cl="XlsxExport",postData(data).then(data=>{windowReference.location=api+"file.php?open="+encodeURIComponent(data.result.data.file),closedataloaddlg()}).catch(e=>{closedataloaddlg()}),!1}};async function syncData(url,username,password,data={}){let hh={"Content-Type":"application/json"};null!=username&&""!=username&&(hh.Authorization=Base64.encode(username+":"+password));const response=await fetch(url,{method:"POST",mode:"cors",cache:"no-cache",credentials:"omit",headers:hh,redirect:"follow",referrerPolicy:"strict-origin",body:JSON.stringify(data)});return response.json()} \ No newline at end of file +var api=location.origin+"/";if("https://app.plandutravail.lu"!=location.origin&&"https://demo.plandutravail.lu"!=location.origin)var api=location.origin+"/plandutravail_lu/";var Base64={_keyStr:"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",encode:function(e){var t="",n,r,i,s,o,u,a,f=0;for(e=Base64._utf8_encode(e);f>2,o=(3&n)<<4|(r=e.charCodeAt(f++))>>4,u=(15&r)<<2|(i=e.charCodeAt(f++))>>6,a=63&i,isNaN(r)?u=a=64:isNaN(i)&&(a=64),t=t+this._keyStr.charAt(s)+this._keyStr.charAt(o)+this._keyStr.charAt(u)+this._keyStr.charAt(a);return t},decode:function(e){var t="",n,r,i,s,o,u,a,f=0;for(e=e.replace(/[^A-Za-z0-9\+\/\=]/g,"");f>4,r=(15&o)<<4|(u=this._keyStr.indexOf(e.charAt(f++)))>>2,i=(3&u)<<6|(a=this._keyStr.indexOf(e.charAt(f++))),t+=String.fromCharCode(n),64!=u&&(t+=String.fromCharCode(r)),64!=a&&(t+=String.fromCharCode(i));return t=Base64._utf8_decode(t)},_utf8_encode:function(e){e=e.replace(/\r\n/g,"\n");for(var t="",n=0;n127&&r<2048?(t+=String.fromCharCode(r>>6|192),t+=String.fromCharCode(63&r|128)):(t+=String.fromCharCode(r>>12|224),t+=String.fromCharCode(r>>6&63|128),t+=String.fromCharCode(63&r|128))}return t},_utf8_decode:function(e){for(var t="",n=0,r=c1=c2=0;n191&&r<224?(c2=e.charCodeAt(n+1),t+=String.fromCharCode((31&r)<<6|63&c2),n+=2):(c2=e.charCodeAt(n+1),c3=e.charCodeAt(n+2),t+=String.fromCharCode((15&r)<<12|(63&c2)<<6|63&c3),n+=3);return t}};async function postData(data={}){const response=await fetch(api+"db.php",{method:"POST",mode:"same-origin",cache:"no-cache",credentials:"same-origin",headers:{"Content-Type":"application/json"},redirect:"follow",referrerPolicy:"strict-origin",body:JSON.stringify(data)});var x=response.json();return x||{}}async function postReportData(data={}){const response=await fetch(api+"report.php",{method:"POST",mode:"same-origin",cache:"no-cache",credentials:"same-origin",headers:{"Content-Type":"application/json"},redirect:"follow",referrerPolicy:"strict-origin",body:JSON.stringify(data)});var x=response.json();return x||{}}let report={generate:function(type,repname,data){showdataloaddlg("{{lbl.createpdf}}","{{lbl.waitmsg}}");var windowReference=window.open();return console.log("senddata",{type:type,name:repname,data:data}),postReportData({type:type,name:repname,data:data}).then(data=>{console.log("data returned",data),windowReference.location=api+"file.php?open="+encodeURIComponent(data.result.file),closedataloaddlg()}).catch(e=>{closedataloaddlg()}),!1},exportXLSX:function(data){showdataloaddlg("{{lbl.createpdf}}","{{lbl.waitmsg}}");var windowReference=window.open();return data.cl="XlsxExport",postData(data).then(data=>{windowReference.location=api+"file.php?open="+encodeURIComponent(data.result.data.file),closedataloaddlg()}).catch(e=>{closedataloaddlg()}),!1}};async function syncData(url,username,password,data={}){let hh={"Content-Type":"application/json"};null!=username&&""!=username&&(hh.Authorization=Base64.encode(username+":"+password));const response=await fetch(url,{method:"POST",mode:"cors",cache:"no-cache",credentials:"omit",headers:hh,redirect:"follow",referrerPolicy:"strict-origin",body:JSON.stringify(data)});return response.json()} \ No newline at end of file diff --git a/app_plandutravail_lu/tmpl/module/js/request.js b/app_plandutravail_lu/tmpl/module/js/request.js index 7df9c17d..a0081aca 100644 --- a/app_plandutravail_lu/tmpl/module/js/request.js +++ b/app_plandutravail_lu/tmpl/module/js/request.js @@ -1,6 +1,6 @@ var api = location.origin + '/'; if ((location.origin != 'https://app.plandutravail.lu') && (location.origin != 'https://demo.plandutravail.lu')){ - var api = location.origin + '/app_plandutravail_lu/'; + var api = location.origin + '/plandutravail_lu/'; } var Base64={_keyStr:"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",encode:function(e){var t="";var n,r,i,s,o,u,a;var f=0;e=Base64._utf8_encode(e);while(f>2;o=(n&3)<<4|r>>4;u=(r&15)<<2|i>>6;a=i&63;if(isNaN(r)){u=a=64}else if(isNaN(i)){a=64}t=t+this._keyStr.charAt(s)+this._keyStr.charAt(o)+this._keyStr.charAt(u)+this._keyStr.charAt(a)}return t},decode:function(e){var t="";var n,r,i;var s,o,u,a;var f=0;e=e.replace(/[^A-Za-z0-9\+\/\=]/g,"");while(f>4;r=(o&15)<<4|u>>2;i=(u&3)<<6|a;t=t+String.fromCharCode(n);if(u!=64){t=t+String.fromCharCode(r)}if(a!=64){t=t+String.fromCharCode(i)}}t=Base64._utf8_decode(t);return t},_utf8_encode:function(e){e=e.replace(/\r\n/g,"\n");var t="";for(var n=0;n127&&r<2048){t+=String.fromCharCode(r>>6|192);t+=String.fromCharCode(r&63|128)}else{t+=String.fromCharCode(r>>12|224);t+=String.fromCharCode(r>>6&63|128);t+=String.fromCharCode(r&63|128)}}return t},_utf8_decode:function(e){var t="";var n=0;var r=c1=c2=0;while(n191&&r<224){c2=e.charCodeAt(n+1);t+=String.fromCharCode((r&31)<<6|c2&63);n+=2}else{c2=e.charCodeAt(n+1);c3=e.charCodeAt(n+2);t+=String.fromCharCode((r&15)<<12|(c2&63)<<6|c3&63);n+=3}}return t}} -- 2.39.5