From 2317b5c8979ab890cfa9459519cfe1d5b3453201 Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Mon, 22 Mar 2021 10:26:41 +0100 Subject: [PATCH] v20210322 --- app/lib/POT/Period.php | 14 +++--- app/lib/POT/Report.php | 4 +- app/lib/POT/Staff.php | 39 +++++++++-------- app/lib/config.php | 4 +- app/tmpl/module/periods.html | 6 ++- app/tmpl/module/periods/reportperiod.js | 22 +++++----- app/tmpl/module/periods/staffperiodweeks.js | 16 ++++--- dev/db/update.live.sql | 47 +++++++-------------- 8 files changed, 71 insertions(+), 81 deletions(-) diff --git a/app/lib/POT/Period.php b/app/lib/POT/Period.php index 3c8e4011..8dccb35b 100644 --- a/app/lib/POT/Period.php +++ b/app/lib/POT/Period.php @@ -91,7 +91,7 @@ function getStaffPeriodSplitted($schema,$id_staff,$id_period,$id_groups){ WHERE st.isdeleted IS NULL ".$where." ".$wgroups." group by srp.id,pd.id_staff,st.prename,st.surname,psplit.id_reportperiod,psplit.splitter,sgr.groupname ORDER BY sgr.groupname,staffname, srp.id_staff,startdate,enddate;"; - error_log("SPLITTED SQL".$sql); + //error_log("SPLITTED SQL".$sql); return $this->dbh->queryarray($sql); } @@ -227,10 +227,10 @@ function getPeriodWeekSums($schema,$id_staff, $date_start,$date_end,$id_period){ 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,sum(trackedhours) as weektrackedhours from ( - select date(date_trunc('week',daydate)) as weekstart,id_staff,(stamp_out-stamp_in)::interval as trackedhours from alicehartmann.timetracker + select date(date_trunc('week',daydate)) as weekstart,id_staff,(stamp_out-stamp_in)::interval as trackedhours from ".$schema.".timetracker where daydate between date('".$pstart."') and date('".$pend."') and id_staff ='".$id_staff."') ttx group by weekstart,id_staff ) ttr on ws.weekstart=ttr.weekstart and ws.id_staff=ttr.id_staff"; - error_log("WS".$sql); + //error_log("WS".$sql); $ret = $this->dbh->querybykey('weekstart',$sql); @@ -552,8 +552,6 @@ sum(CASE WHEN srpd.dayhours IS NULL THEN '00:00:00'::interval ELSE srpd.dayhours // } function setStaffPeriodDays($schema,$id_period,$id_staff){ - - //print STDERR "START Period::setStaffPeriodDays()\n"; $maxprdsql = "select date(date_trunc('week',min(startdate))) as startdatealt,min(startdate) as startdate,max(enddate) as enddate from ".$schema.".reportperiod;"; $prddata = $this->dbh->query($maxprdsql); @@ -595,14 +593,14 @@ do update set wdcontracthours = '".$cth["wdcontracthours"]."';"; if (count($delete) > 0){ $delsql = "DELETE FROM ".$schema.".staffreportperioddays where id_staff='".$id_staff."' and (daydate > date('".$dlim["lastday"]."') or daydate < date('".$dlim["firstday"]."')) OR (id_staff='".$id_staff."' AND ".join(" AND ",$delete).");"; - // print STDERR "DEL1:".$delsql."\n======\n"; + error_log("DELETE ".$delsql); $this->dbh->exec($delsql); } $delstaffreportssql = "delete from ".$schema.".staffreportperiod sp where id_staff='".$id_staff."' and id_reportperiod not in ( select rp.id from ".$schema.".reportperiod rp left join ".$schema.".staffreportperioddays pd on (pd.daydate between rp.startdate and rp.enddate) where pd.id_staff='".$id_staff."' group by rp.id);"; - // print STDERR "DEL2:".$delstaffreportssql."\n======\n"; + error_log("DELETE ".$delstaffreportssql); $this->dbh->exec($delstaffreportssql); //print STDERR "END Period::setStaffPeriodDays()\n"; return 1; @@ -635,7 +633,7 @@ function clonePeriodDay($schema,$id_staff,$id_period,$copyid,$pasteids){ partunempcomment=".$this->dbh->value($copy["partunempcomment"])." where id in ('".join("','",$pasteids)."');"; $sql = preg_replace('/\s+/',' ',$sql); - error_log("CLONE: ".$sql); + //error_log("CLONE: ".$sql); $this->dbh->exec($sql); //$prd = $this->getPeriodByDayId($schema,@{$pasteids}[0]); //$this->setStaffPeriodDays($schema,$id_period,$id_staff); diff --git a/app/lib/POT/Report.php b/app/lib/POT/Report.php index 4c0e59ad..85b0618a 100644 --- a/app/lib/POT/Report.php +++ b/app/lib/POT/Report.php @@ -65,7 +65,7 @@ public function createPDF($schema,$template,$tmpfile,$pdfname){ } $cmd .= ' "'.$this->cfg["tmppath"].'/'.$tmpfile.'.html"'; $cmd .= ' "'.$pdfname.'"'; - error_log($cmd); + //error_log($cmd); //print STDERR $cmd."\n"; $r = system($cmd); } @@ -242,7 +242,7 @@ public function printPOTFooter($schema,$outfile){ $dateseries = $this->dbh->queryarray("select date_part('isodow',generate_series)::int as weekday, date(generate_series) as daydate, to_char(generate_series,'DD.MM.YYYY') as dspdate from pg_catalog.generate_series(date('".$data["startdate"]."'),date('".$data["enddate"]."'),'1 day');"); $pdata = $this->getPeriodDays($schema,$data["id_staff"],$data["startdate"],$data["enddate"]); $wsums = $this->getPeriodWeekSums($schema,$data["id_staff"],$data["startdate"],$data["enddate"]); - error_log(print_r($wsums,true)); + //error_log(print_r($wsums,true)); $pagehead = $data["surname"]." ".$data["prename"]." ".(($data["matricule"] != "")?"(".$data["matricule"].")":""); $tblhead = " diff --git a/app/lib/POT/Staff.php b/app/lib/POT/Staff.php index e0e3fb62..8d15549a 100644 --- a/app/lib/POT/Staff.php +++ b/app/lib/POT/Staff.php @@ -60,22 +60,21 @@ or date((select min(startdate) from ".$schema.".staffcontract where id_staff='". $period = new Period($this->dbh,$this->cfg); foreach ($perioddata as $pp){ - - $this->dbh->exec("INSERT INTO ".$schema.".staffreportperiod (id_reportperiod, id_staff) - VALUES ('".$pp["id"]."','".$id_staff."') on conflict on constraint uniq_staffreportperiod_cal do nothing;"); $period->setStaffPeriodDays($schema,$pp["id"],$id_staff); + $this->dbh->exec("INSERT INTO ".$schema.".staffreportperiod (id_reportperiod, id_staff) + VALUES ('".$pp["id"]."','".$id_staff."') on conflict on constraint uniq_staffreportperiod_cal do nothing;"); } $cpsql = "select reportperiodunit,reportperiodlength from public.companies where schemata='".$schema."'"; $rprol = $this->dbh->query($cpsql); foreach ($perioddata as $pp){ - + $ctsql = "select id,weekhours,weekdays from ( -select rp.id,case when sc.startdate < rp.startdate then rp.startdate else sc.startdate end as startdate , -case when sc.enddate is null or sc.enddate > rp.enddate then rp.enddate else sc.enddate end as enddate, -sc.weekhours,sc.weekdays from ".$schema.".staffcontract sc -cross join ".$schema.".reportperiod rp -where sc.id_staff='".$id_staff."' -) scx where startdate < enddate and id='".$pp["id"]."' order by startdate,enddate;"; + select rp.id,case when sc.startdate < rp.startdate then rp.startdate else sc.startdate end as startdate , + case when sc.enddate is null or sc.enddate > rp.enddate then rp.enddate else sc.enddate end as enddate, + sc.weekhours,sc.weekdays from ".$schema.".staffcontract sc + cross join ".$schema.".reportperiod rp + where sc.id_staff='".$id_staff."' + ) scx where startdate < enddate and id='".$pp["id"]."' order by startdate,enddate;"; $ctdata = $this->dbh->queryarray($ctsql); foreach ($ctdata as $c){ @@ -93,17 +92,17 @@ where sc.id_staff='".$id_staff."' } $mdays = $this->dbh->query( "select sum(dow) as maxdays from ( select case when date_part('isodow',generate_series) in ".$inday." then 0 else 1 end as dow from pg_catalog.generate_series(date('".$pp["startdate"]."'),date('".$pp["enddate"]."'),'1 day')) mxdays"); $chsql = "select sum(maxdays) as maxdays,sum(contracthours - ('00:00:'|| extract(second from contracthours))::interval ) as itmcontracthours from ( -select ".$mdays["maxdays"]." as maxdays,".$mdays["maxdays"]." * (round(cast(((extract(epoch from (current_date + weekhours::interval)::timestamp) - extract(epoch from current_date::timestamp))/3600.0) /weekdays as numeric) ,2)+0.00) * '01:00:00'::interval as contracthours from ( -select case when count(daydate) >= weekdays then weekdays else weekdays-count(daydate)-1 end as maxdays, calweek,count(daydate) as cntdays,count(sundays) as sundays,weekdays,weekhours from ( + select ".$mdays["maxdays"]." as maxdays,".$mdays["maxdays"]." * (round(cast(((extract(epoch from (current_date + weekhours::interval)::timestamp) - extract(epoch from current_date::timestamp))/3600.0) /weekdays as numeric) ,2)+0.00) * '01:00:00'::interval as contracthours from ( + select case when count(daydate) >= weekdays then weekdays else weekdays-count(daydate)-1 end as maxdays, calweek,count(daydate) as cntdays,count(sundays) as sundays,weekdays,weekhours from ( - select id_staff,date(date_trunc('week',daydate)) as calweek,daydate,case when date_part('isodow',daydate)::int4=7 then 1 else null end as sundays - from ".$schema.".staffreportperioddays - where id_staff='".$id_staff."' and daydate between date('".$pp["startdate"]."') and date('".$pp["enddate"]."') -) wgr -left join ( - select date(date_trunc('week',startdate)) as weekstartdate,startdate,case when enddate is null then date('".$pp["enddate"]."') else enddate end as enddate,case when enddate is null then date(date_trunc('week',date('".$pp["enddate"]."')) + interval '6 days') else enddate end as weekenddate,weekdays,weekhours from ".$schema.".staffcontract where id_staff='".$id_staff."' -) sc on (wgr.calweek between sc.weekstartdate and sc.weekenddate ) where wgr.daydate between sc.startdate and sc.enddate group by sc.weekhours,sc.weekdays,calweek -) rnddays group by weekhours,weekdays) xx"; + select id_staff,date(date_trunc('week',daydate)) as calweek,daydate,case when date_part('isodow',daydate)::int4=7 then 1 else null end as sundays + from ".$schema.".staffreportperioddays + where id_staff='".$id_staff."' and daydate between date('".$pp["startdate"]."') and date('".$pp["enddate"]."') + ) wgr + left join ( + select date(date_trunc('week',startdate)) as weekstartdate,startdate,case when enddate is null then date('".$pp["enddate"]."') else enddate end as enddate,case when enddate is null then date(date_trunc('week',date('".$pp["enddate"]."')) + interval '6 days') else enddate end as weekenddate,weekdays,weekhours from ".$schema.".staffcontract where id_staff='".$id_staff."' + ) sc on (wgr.calweek between sc.weekstartdate and sc.weekenddate ) where wgr.daydate between sc.startdate and sc.enddate group by sc.weekhours,sc.weekdays,calweek + ) rnddays group by weekhours,weekdays) xx"; //error_log("set contract:".$chsql); $checkhours = $this->dbh->query($chsql); diff --git a/app/lib/config.php b/app/lib/config.php index 25a70f55..d2aa2f74 100644 --- a/app/lib/config.php +++ b/app/lib/config.php @@ -14,8 +14,8 @@ "db"=> array( "type" => "pgsql", #"dbfile" => "/home/dks/mirror/invoicejournal/dks/dks.sqlite" - #"host" => "dks-pc1", - "host" => "sql12.your-server.de", + "host" => "dks-pc1", + #"host" => "sql12.your-server.de", "dbname" => "potlu2_db", "user" => 'potlu2_user', "password" => 'dMY8xGB6cBLzrDGE', diff --git a/app/tmpl/module/periods.html b/app/tmpl/module/periods.html index e481c09b..fc536b02 100644 --- a/app/tmpl/module/periods.html +++ b/app/tmpl/module/periods.html @@ -5,8 +5,10 @@ {{ lbl.back }}
{{ lbl.refperiods }}
- - + {% if session.usergroup == 'admin' %} + + + {% endif %} diff --git a/app/tmpl/module/periods/reportperiod.js b/app/tmpl/module/periods/reportperiod.js index efa84595..eab300b9 100644 --- a/app/tmpl/module/periods/reportperiod.js +++ b/app/tmpl/module/periods/reportperiod.js @@ -65,7 +65,7 @@ let reportperiod ={ {label:" {{ lbl.add }}", action:function(e, row){reportperiod.add();return false;}}, {label:"{{ lbl.edit }}", action:function(e, row){reportperiod.editplan();return false;}}, {label:"{{ lbl.pdf }}", action:function(e, row){reportperiod.generatereport();return false;}}, - /*{% if session.usergroup == 'admin' %}*/ + /*{% if session.usergroup != 'teamleader' %}*/ {label:"{{ lbl.finalstatement }}", action:function(e, row){periodvalidation.showdlg();return false;}} /*{% endif %}*/ @@ -124,15 +124,17 @@ let reportperiod ={ console.log(reportperiod.tbl); if (fsel[0]){ console.log("RPER",fsel[0]); - // if (fsel[0].viewpartumempl == null ){ - // // staffperiodweeks.tbl.hideColumn("partunemplhours"); - // // staffperiodweeks.tblfooter.hideColumn("partunemplhours"); - // reportperiod.tbl.hideColumn("partunemplhours"); - // } else { - // // staffperiodweeks.tbl.showColumn("partunemplhours"); - // // staffperiodweeks.tblfooter.showColumn("partunemplhours"); - // reportperiod.tbl.showColumn("partunemplhours"); - // } + if (fsel[0].viewpartunempl == null ){ + var col1 = reportperiod.tbl.columnManager.findColumn("partunemplhours"); + var col2 = staffperiodweeks.tbl.columnManager.findColumn("partunemplhours"); + var col3 = staffperiodweeks.tblfooter.columnManager.findColumn("partunemplhours"); + var col4 = reportperiod.tblsplitted.columnManager.findColumn("partunemplhours"); + col1.visible = false;col2.visible =false;col3.visible=false;col4.visible=false; + var xx = document.querySelectorAll('[tabulator-field="partunemplhours"]'); + for (var c= 0;c< xx.length; c++){ + xx[c].style.display = 'none'; + } + } postData("db.php",{ "get": "staffperiods","schemata":schemata, "id_period":fsel[0].id,"id_staff":null}).then(data => { reportperiod.tbl.setData(data.result.data).then(upd => { //console.log("Select length",rpsel.length); diff --git a/app/tmpl/module/periods/staffperiodweeks.js b/app/tmpl/module/periods/staffperiodweeks.js index a3e50897..417352ee 100644 --- a/app/tmpl/module/periods/staffperiodweeks.js +++ b/app/tmpl/module/periods/staffperiodweeks.js @@ -160,10 +160,10 @@ let staffperiodweeks = { // // // } staffperiodweeks.tbl.setData(data2.result.data).then(dd => { - // if (staffperiodweeks.selectedid != null){ - // staffperiodweeks.tbl.selectRow(staffperiodweeks.selectedid); - // staffperiodweeks.tbl.scrollToRow(staffperiodweeks.selectedid, "center", false); - // } + if (staffperiodweeks.selectedid != null){ + staffperiodweeks.tbl.selectRow(staffperiodweeks.selectedid); + staffperiodweeks.tbl.scrollToRow(staffperiodweeks.selectedid, "center", false); + } }); } @@ -344,7 +344,11 @@ let staffperiodweeks = { for (var i=0;i timetrackdata[i].stamp_out){ stoutday = moment(stpoutday).add(1, 'd').format("YYYY-MM-DD"); } + //console.log(timetrackdata[i].stamp_in + " > " + timetrackdata[i].stamp_out); + if (timetrackdata[i].stamp_in > timetrackdata[i].stamp_out){ + stpoutday = moment(stpoutday).add(1, 'd').format("YYYY-MM-DD"); + //console.log(stpoutday); + } trdata.push({"id":timetrackdata[i].id,"stamp_in":sel[0].daydate + " " +timetrackdata[i].stamp_in + ":00","stamp_out":stpoutday + " " +timetrackdata[i].stamp_out + ":00"}); } } @@ -693,7 +697,7 @@ let staffperiodweeks = { let stpin = timecalc.TimeToMinutes(timecalc.StringToTime(data[0].stamp_in)); let stpout = timecalc.TimeToMinutes(timecalc.StringToTime(data[0].stamp_out)); stpoutcalc = stpout; - if (stpin > stpoutcalc){ stpoutcalc = stpoutcalc + 1500; } + if (stpin > stpoutcalc){ stpoutcalc = stpoutcalc + 1440; } let total = stpoutcalc-stpin; staffperiodweeks.tbltimetracker.updateData([{"id":data[0].id,"stamp_in":timecalc.MinutesToTime(stpin),"stamp_out":timecalc.MinutesToTime(stpout),"total":timecalc.MinutesToTime(total)}]); }, diff --git a/dev/db/update.live.sql b/dev/db/update.live.sql index 70443445..f4958c99 100644 --- a/dev/db/update.live.sql +++ b/dev/db/update.live.sql @@ -1,54 +1,39 @@ -schema_name -public -alicehartmann -domain_hartmann_xx -kraeltgen -demo50 -etude_dbe -demo15 -brasserie_du_theatre -portanova -elch + + +ALTER TABLE public.users ADD COLUMN displaymonthview boolean; ALTER TABLE alicehartmann.reportperiod ADD COLUMN viewpartunempl boolean; ALTER TABLE alicehartmann.staffreportperiod ADD COLUMN otherpaidhours interval; -ALTER TABLE alicehartmann.staffreportperioddays ADD COLUMN otherpaidhours timewithouttimezone; +ALTER TABLE alicehartmann.staffreportperioddays ADD COLUMN otherpaidhours time without time zone; ALTER TABLE alicehartmann.staffreportperioddays ADD COLUMN otherpaidhourscomment text; - ALTER TABLE brasserie_du_theatre.reportperiod ADD COLUMN viewpartunempl boolean; ALTER TABLE brasserie_du_theatre.staffreportperiod ADD COLUMN otherpaidhours interval; -ALTER TABLE brasserie_du_theatre.staffreportperioddays ADD COLUMN otherpaidhours timewithouttimezone; +ALTER TABLE brasserie_du_theatre.staffreportperioddays ADD COLUMN otherpaidhours time without time zone; ALTER TABLE brasserie_du_theatre.staffreportperioddays ADD COLUMN otherpaidhourscomment text; - -ALTER TABLE demo50.periodconfig ADD COLUMN id text; -ALTER TABLE demo50.periodconfig ADD COLUMN payedpauses boolean; -ALTER TABLE demo50.periodconfig ADD COLUMN periodlength integer; -ALTER TABLE demo50.periodconfig ADD COLUMN periodstart date; -ALTER TABLE demo50.periodconfig ADD COLUMN periodtypes text; -ALTER TABLE demo50.periodconfig ADD COLUMN periodunit text; -ALTER TABLE demo50.periodconfig ADD COLUMN sector text; -ALTER TABLE demo50.periodconfig ADD COLUMN subinterval text; +ALTER TABLE demo50.reportperiod ADD COLUMN viewpartunempl boolean; ALTER TABLE demo50.staffreportperiod ADD COLUMN otherpaidhours interval; -ALTER TABLE demo50.staffreportperioddays ADD COLUMN otherpaidhours timewithouttimezone; +ALTER TABLE demo50.staffreportperioddays ADD COLUMN otherpaidhours time without time zone; ALTER TABLE demo50.staffreportperioddays ADD COLUMN otherpaidhourscomment text; - +ALTER TABLE elch.reportperiod ADD COLUMN viewpartunempl boolean; ALTER TABLE elch.staffreportperiod ADD COLUMN otherpaidhours interval; -ALTER TABLE elch.staffreportperioddays ADD COLUMN otherpaidhours timewithouttimezone; +ALTER TABLE elch.staffreportperioddays ADD COLUMN otherpaidhours time without time zone; ALTER TABLE elch.staffreportperioddays ADD COLUMN otherpaidhourscomment text; +ALTER TABLE etude_dbe.reportperiod ADD COLUMN viewpartunempl boolean; ALTER TABLE etude_dbe.staffreportperiod ADD COLUMN otherpaidhours interval; -ALTER TABLE etude_dbe.staffreportperioddays ADD COLUMN otherpaidhours timewithouttimezone; +ALTER TABLE etude_dbe.staffreportperioddays ADD COLUMN otherpaidhours time without time zone; +ALTER TABLE kraeltgen.reportperiod ADD COLUMN viewpartunempl boolean; ALTER TABLE kraeltgen.staffreportperiod ADD COLUMN otherpaidhours interval; -ALTER TABLE kraeltgen.staffreportperioddays ADD COLUMN otherpaidhours timewithouttimezone; +ALTER TABLE kraeltgen.staffreportperioddays ADD COLUMN otherpaidhours time without time zone; ALTER TABLE kraeltgen.staffreportperioddays ADD COLUMN otherpaidhourscomment text; - +ALTER TABLE portanova.reportperiod ADD COLUMN viewpartunempl boolean; ALTER TABLE portanova.staffreportperiod ADD COLUMN otherpaidhours interval; -ALTER TABLE portanova.staffreportperioddays ADD COLUMN otherpaidhours timewithouttimezone; +ALTER TABLE portanova.staffreportperioddays ADD COLUMN otherpaidhours time without time zone; ALTER TABLE portanova.staffreportperioddays ADD COLUMN otherpaidhourscomment text; -ALTER TABLE public.users ADD COLUMN displaymonthview boolean; + -- 2.39.5
Date