From d0442bba47f8e41da7dc38e0f92e83515c62d2da Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Mon, 2 Nov 2020 09:00:48 +0100 Subject: [PATCH] v20201102tmp --- app/lib/POT/Period.pm | 73 +++++++++++++++++++++++++++++++++++++++-- dev/tmpsql.sql | 76 +++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 146 insertions(+), 3 deletions(-) create mode 100644 dev/tmpsql.sql diff --git a/app/lib/POT/Period.pm b/app/lib/POT/Period.pm index ddc55bce..80283458 100644 --- a/app/lib/POT/Period.pm +++ b/app/lib/POT/Period.pm @@ -30,11 +30,11 @@ sub refreshPeriods(){ sub addPeriod(){ my $self = shift; my $schema = shift; - my $prdd1= $self->{db}->query("select reportperiodunit,reportperiodlength,reportperiodstart from public.companies where schemata='".$schema."';"); + my $prdd1= $self->{db}->query("select reportperiodunit,reportperiodlength,reportperiodstart,periodtypes from public.companies where schemata='".$schema."';"); my $prdstart= $self->{db}->query("select case when max(enddate) is null then date('".$prdd1->{reportperiodstart}."') else date(max(enddate) + interval '1 day') end as rpstart from ".$schema.".reportperiod;"); my $prdend= $self->{db}->query("select date(date('".$prdstart->{rpstart}."') + interval '".$prdd1->{reportperiodlength}." ".$prdd1->{reportperiodunit}."s' - interval '1 day' ) as rpend;"); my $prdprt =$self->{db}->query("select id from ".$schema.".reportperiod order by startdate DESC,enddate LIMIT 1;"); - my $prd =$self->{db}->query("INSERT INTO ".$schema.".reportperiod (startdate, enddate, id_parentreportperiod) VALUES('".$prdstart->{rpstart}."','".$prdend->{rpend}."','".$prdprt->{id}."') returning id;"); + my $prd =$self->{db}->query("INSERT INTO ".$schema.".reportperiod (startdate, enddate, id_parentreportperiod,periodtype) VALUES('".$prdstart->{rpstart}."','".$prdend->{rpend}."','".$prdprt->{id}."','".$prdd1->{periodtypes}."') returning id;"); #my $staff = $self->{db}->querysorted("select id from ".$schema.".staff where isdeleted is null;"); my $staff = $self->{db}->querysorted("INSERT INTO ".$schema.".staffreportperiod (id_reportperiod, id_staff) select '".$prd->{id}."' as id_reportperiod,id_staff from ".$schema.".staffcontract sc @@ -102,10 +102,77 @@ sub getStaffPeriods(){ LEFT JOIN ".$schema.".staffgroups sgr ON st.id_staffgroup = sgr.id WHERE st.isdeleted IS NULL ".$where." ".$wgroups." ORDER BY st.surname, st.prename, srp.id_staff, rp.startdate, rp.enddate;"; + my $res = $self->{db}->querysorted($sql); + my $monthlysql = "select + pd.id_staff, + dp.id_reportperiod as id_reportperiod, + min(daydate) || '-' || max(daydate) as subperiods, + sum(pd.workhours) as workhours, + sum(pd.dayhours) as totalhours, + sum(coalesce(pd.vacancyhours,'00:00:00'::time)) as vacancyhours, + sum(case when pd.id_vacancytype = 'ill' and pd.vacancyhours > '00:00:00'::time then coalesce(pd.vacancyhours,'00:00:00'::time) else '00:00:00'::time end) as vacancyill, + sum(case when pd.id_vacancytype != 'ill' and pd.vacancyhours > '00:00:00'::time then coalesce(pd.vacancyhours,'00:00:00'::time) else '00:00:00'::time end) as vacancynormal, + sum(coalesce(pd.recuperationhours,'00:00:00'::time)) as recuperationhours + from demo.staffreportperioddays pd + join demo.staffreportperiod dp on (pd.id_staff=dp.id_staff and dp.id_reportperiod = '2583c916-2d07-f42d-9c85-18ee42959317') + left join (select date(date_trunc('month',date(\"generate_series\"))) as splitter,date(\"generate_series\") as startdate, date(date(\"generate_series\") + interval '4 weeks' - interval '1 day') as enddate from ( + select * from generate_series('2020-08-10 00:00:00'::timestamp,'2020-10-04 00:00:00'::timestamp,'4 weeks') + ) gp) psplit on (pd.daydate between psplit.startdate and psplit.enddate) + where pd.daydate between date('2020-08-10') and date('2020-10-04') + group by pd.id_staff,dp.id,psplit.splitter + order by id_staff + ;" + #my $resmonthly = return $self->{db}->querysorted($sql); } - +sub getStaffPeriodsByMonth(){ + my $self = shift; + my $schema = shift; + my $id_staff = shift; + my $id_period = shift; + my $id_groups = shift; + my $where = ""; + my $wgroups = ""; + if (defined($id_staff)){ + $where = " AND id_staff = '".$id_staff."' "; + } elsif (defined($id_period)){ + $where = " AND id_reportperiod = '".$id_period."' "; + } + if (defined($id_groups)){ + $wgroups .= " and sgr.id in ('".join("','",@{$id_groups})."') "; + } + my $sql = "SELECT st.prename, st.surname, + 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.recuperationhours, 'HH24:MI'::text) AS recuperationhours, + 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_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(srp.avgtotalweekhours, 'HH24:MI'::text) AS avgtotalweekhours + FROM ".$schema.".staffreportperiod srp + LEFT JOIN ".$schema.".staff st ON srp.id_staff = st.id + LEFT JOIN ".$schema.".reportperiod rp ON srp.id_reportperiod = rp.id + LEFT JOIN ".$schema.".staffgroups sgr ON st.id_staffgroup = sgr.id + WHERE st.isdeleted IS NULL ".$where." ".$wgroups." + ORDER BY st.surname, st.prename, srp.id_staff, rp.startdate, rp.enddate;"; + return $self->{db}->querysorted($sql); +} sub getPeriodWeekSums(){ my $self = shift; diff --git a/dev/tmpsql.sql b/dev/tmpsql.sql new file mode 100644 index 00000000..d847035a --- /dev/null +++ b/dev/tmpsql.sql @@ -0,0 +1,76 @@ + select row_to_json(xx) from + (select + pd.id_staff, + dp.id_reportperiod as id_reportperiod, + --min(daydate) || '-' || max(daydate) as subperiods, + min(daydate) as startdate, + max(daydate) as enddate, + sum(pd.workhours) as workhours, + sum(pd.dayhours) as totalhours, + sum(coalesce(pd.vacancyhours,'00:00:00'::time)) as vacancyhours, + sum(case when pd.id_vacancytype = 'ill' and pd.vacancyhours > '00:00:00'::time then coalesce(pd.vacancyhours,'00:00:00'::time) else '00:00:00'::time end) as vacancyill, + sum(case when pd.id_vacancytype != 'ill' and pd.vacancyhours > '00:00:00'::time then coalesce(pd.vacancyhours,'00:00:00'::time) else '00:00:00'::time end) as vacancynormal, + sum(coalesce(pd.recuperationhours,'00:00:00'::time)) as recuperationhours + from demo.staffreportperioddays pd + join demo.reportperiod rp on (pd.id_reportperiod=rp.id) + join demo.staffreportperiod dp on (pd.id_staff=dp.id_staff and dp.id_reportperiod = rp.id) + left join (select date(date_trunc('month',date(substartdate))) as splitter,date(substartdate) as startdate, date(date(substartdate) + interval '4 weeks' - interval '1 day') as enddate from ( + select rpx.id,date(generate_series(rpx.startdate::timestamp,rpx.enddate::timestamp,rpx.subinterval::interval)) as substartdate from demo.reportperiod rpx + ) gp) psplit on (pd.daydate between psplit.startdate and psplit.enddate) + group by pd.id_staff,dp.id,psplit.splitter + order by id_staff) xx + ; + + select startdate,enddate from demo.reportperiod r where id='2583c916-2d07-f42d-9c85-18ee42959317'; + + select date(date_trunc('month',date("generate_series"))) as splitter,date("generate_series") as startdate, date(date("generate_series") + interval '4 weeks' - interval '1 day') as enddate from ( + select * from generate_series('2020-08-10 00:00:00'::timestamp,'2020-10-04 00:00:00'::timestamp,'4 weeks') + ) gp; + +select rp.id,date(generate_series(rp.startdate,rp.enddate,rp.subinterval)) as startdate from demo.reportperiod rp; + +-- demo.staffreportperiod definition + +-- Drop table + +-- DROP TABLE staffreportperiod; +alter table demo.reportperiod add column subinterval text; +CREATE TABLE demo.staffreportperiodmonth ( + id text NOT NULL DEFAULT getuuid(), + id_reportperiod text NULL, + id_staff text NULL, + startdate date null, + enddate date null, + workhours interval(6) NULL, + contracthours interval(6) NULL, + totalhours interval(6) NULL, + vacancyhours interval(6) NULL, + recuperationhours interval(6) NULL, + hoursdiff interval(6) NULL, + payedhours interval(6) NULL, + diffhourspayed interval(6) NULL, + modified timestamp NULL DEFAULT now(), + created timestamp NULL DEFAULT now(), + itmcontracthours interval(6) NULL DEFAULT '00:00:00'::interval, + maxdays int4 NULL DEFAULT 0, + avgtotalweekhours interval(6) NULL, + vacancyill interval(6) NULL, + vacancynormal interval(6) NULL, + PRIMARY KEY (id), + CONSTRAINT uniq_staffreportperiodmonth UNIQUE (id_reportperiod, id_staff,startdate,enddate) +); + +-- Table Triggers + +-- DROP TRIGGER trg_demo_before_upd_staffreportperiod ON demo.staffreportperiod; + +create trigger trg_demo_before_upd_staffreportperiodmonth before +update + on + demo.staffreportperiodmonth for each row execute procedure demo.trg_before_upd_schematable(); + + +-- demo.staffreportperiod foreign keys + +ALTER TABLE demo.staffreportperiodmonth ADD CONSTRAINT staffreportperiodmonth_fk FOREIGN KEY (id_staff) REFERENCES demo.staff(id); +ALTER TABLE demo.staffreportperiodmonth ADD CONSTRAINT staffreportperiodmonth_fk_1 FOREIGN KEY (id_reportperiod) REFERENCES demo.reportperiod(id); \ No newline at end of file -- 2.39.5