From fe8f34f7a42146ed02161bd0c4ab6d45e7c234ef Mon Sep 17 00:00:00 2001 From: "kilian (ksmachome)" Date: Mon, 16 Dec 2019 06:31:35 +0100 Subject: [PATCH] v20191215 --- backoffice/api/index.cgi | 27 +- backoffice/api/lib/dksconfig.pm | 4 +- backoffice/api/lib/dksdb.pm | 30 + backoffice/data/.htaccess | 2 +- backoffice/data/company/.htaccess | 5 + backoffice/data/schemata/.htaccess | 5 + .../data/schemata/defaultcompany.schema.sql | 1193 +++++++++++++++ backoffice/tmpl/module/companies/index.js | 280 ++-- backoffice/tmpl/module/companies/index.tt | 83 +- .../companies/widgets/companies/companies.js | 104 ++ .../widgets/companies/frm_companies.tt | 35 + .../widgets/companies/tbar_companies.tt | 7 + .../widgets/companies/tbl_companies.tt | 3 + dev/checkvat.pl | 15 + dev/db/gendefaultschema.sh | 20 + .../lib/perl5/Business/Tax/VAT/Validation.pm | 485 ++++++ .../MYMETA.json | 52 + .../install.json | 1 + .../Business/Tax/VAT/Validation/.packlist | 2 + .../l/lib/perl5/darwin-2level/perllocal.pod | 22 + .../man/man3/Business::Tax::VAT::Validation.3 | 261 ++++ .../potlu_db.newcompanyschema.pg.schema.sql | 1193 +++++++++++++++ .../potlu_db.newcompanyschema.pg.schema.sql-e | 1349 +++++++++++++++++ 23 files changed, 4955 insertions(+), 223 deletions(-) create mode 100644 backoffice/data/company/.htaccess create mode 100644 backoffice/data/schemata/.htaccess create mode 100644 backoffice/data/schemata/defaultcompany.schema.sql create mode 100644 backoffice/tmpl/module/companies/widgets/companies/companies.js create mode 100644 backoffice/tmpl/module/companies/widgets/companies/frm_companies.tt create mode 100644 backoffice/tmpl/module/companies/widgets/companies/tbar_companies.tt create mode 100644 backoffice/tmpl/module/companies/widgets/companies/tbl_companies.tt create mode 100644 dev/checkvat.pl create mode 100755 dev/db/gendefaultschema.sh create mode 100755 dev/db/l/lib/perl5/Business/Tax/VAT/Validation.pm create mode 100644 dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/MYMETA.json create mode 100644 dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/install.json create mode 100644 dev/db/l/lib/perl5/darwin-2level/auto/Business/Tax/VAT/Validation/.packlist create mode 100644 dev/db/l/lib/perl5/darwin-2level/perllocal.pod create mode 100644 dev/db/l/man/man3/Business::Tax::VAT::Validation.3 create mode 100644 dev/db/potlu_db.newcompanyschema.pg.schema.sql create mode 100644 dev/db/potlu_db.newcompanyschema.pg.schema.sql-e diff --git a/backoffice/api/index.cgi b/backoffice/api/index.cgi index 95d87da5..56cf9686 100755 --- a/backoffice/api/index.cgi +++ b/backoffice/api/index.cgi @@ -6,6 +6,7 @@ use CGI; use CGI::Cookie; #use CGI::Carp qw/fatalsToBrowser/; use File::Basename; +use Business::Tax::VAT::Validation; use JSON::PP; use dksconfig qw/$sitecfg/; @@ -34,7 +35,6 @@ $html->{p} = $p; # $html->{sess} =$sess; #my $datapath = $ENV{"DOCUMENT_ROOT"}.dirname(dirname($scriptpath)).'/data/'; if (($cgi->request_method() eq "GET") || ($cgi->request_method() eq "POST")){ - my @params = $cgi->param(); foreach my $pp (@params){ $p->{$pp} = $cgi->param($pp); @@ -146,6 +146,31 @@ if (($cgi->request_method() eq "GET") || ($cgi->request_method() eq "POST")){ if($p->{fn} eq "getsitemedia"){ my $path = dirname(dirname($0)).'/img'; } + if ($p->{fn} eq "checkvat"){ + my $hvatn=Business::Tax::VAT::Validation->new(); + my $VAT=$p->{vatnumber}; + $VAT =~ s/\s+//g; + if ($hvatn->check($VAT)){ + $html->{result} = $hvatn->informations(); + } else { + $html->{result} = $hvatn->get_last_error; + } + } + if ($p->{fn} eq "getfreeschema"){ + my $cmpy = lc($p->{companyname}); + $cmpy =~ s///; + $cmpy =~ s/[^a-z0-9\-]/\_/g; + $cmpy =~ s/^[\_]+//; + $cmpy =~ s/[\_]+$//; + $cmpy =~ s/^[\_]+/_/; + my $schemasql = "select count(*) as cnt from information_schema.schemata where schema_name='".$db->securetext($p->{schemaname})."';"; + my $dbschema = $db->dbquery($schemasql); + my $dbsnum = ""; + if ($dbschema->{cnt} > 1){ + $dbsnum = int($dbschema->{cnt}) + 1; + } + $html->{result}->{newschema} = $cmpy.$dnsnum; + } } } diff --git a/backoffice/api/lib/dksconfig.pm b/backoffice/api/lib/dksconfig.pm index 270fff14..17c9ea80 100644 --- a/backoffice/api/lib/dksconfig.pm +++ b/backoffice/api/lib/dksconfig.pm @@ -9,7 +9,7 @@ use Exporter 'import'; our @EXPORT_OK = qw($sitecfg); our $sitecfg ={ - cookiename => 'hourtrax', + cookiename => 'potlu', dbtype => 'PgPP', dsn => 'DBI:PgPP:dbname=potlu_db;host=DKS-LAPTOP.fritz.box', #dsn => 'DBI:PgPP:dbname=solarch_db;host=sql629.your-server.de', @@ -18,7 +18,7 @@ our $sitecfg ={ page => 'index.tt', pagename => 'index', basepath => substr((exists($ENV{"SCRIPT_FILENAME"})?dirname($ENV{"SCRIPT_FILENAME"}):dirname($0)),length($ENV{"DOCUMENT_ROOT"})), - #datapath => substr((exists($ENV{"SCRIPT_FILENAME"})?dirname($ENV{"SCRIPT_FILENAME"}):dirname($0)),length($ENV{"DOCUMENT_ROOT"})).'/data/', + datapath => substr((exists($ENV{"SCRIPT_FILENAME"})?dirname($ENV{"SCRIPT_FILENAME"}):dirname($0)),length($ENV{"DOCUMENT_ROOT"})).'/data/', docroot => $ENV{"DOCUMENT_ROOT"}, registration_enabled => '0', default_group => 'users', diff --git a/backoffice/api/lib/dksdb.pm b/backoffice/api/lib/dksdb.pm index ba7e9bd8..13c1d5a7 100644 --- a/backoffice/api/lib/dksdb.pm +++ b/backoffice/api/lib/dksdb.pm @@ -357,4 +357,34 @@ sub textunidecode(){ return $text; } +sub createdefinedschema(){ + my $self = shift; + my $schematype = shift; + my $schemaname = shift; + my $ddlstr = ""; + my $sc = $self->dbquerysorted("select * from information_schema.schemata where schema_name='".$schemaname."';"); + if (keys(%{$sc}) > 0){ + return; + } + my @schemaddl =(); + open(SCA,$sitecfg->{datapath}.'/schemata/'.$schematype.'.schema.sql'); + while (my $l = ){ + $l =~ s/%%NEWSCHEMA%%/$schemaname/g; + if ($l eq "\n"){ + if ($ddlstr ne ""){ + push(@schemaddl,$ddlstr); + } + $ddlstr = ""; + next; + } + $ddlstr .= $l; + } + close(SCA); + if (scalar(@schemaddl) > 0){ + for(my $d;$ddbexec($schemaddl[$d]); + } + } +} + 1; diff --git a/backoffice/data/.htaccess b/backoffice/data/.htaccess index fd8b0c39..b4b826d0 100644 --- a/backoffice/data/.htaccess +++ b/backoffice/data/.htaccess @@ -1,5 +1,5 @@ RewriteEngine On RewriteBase / -RewriteCond %{HTTP_COOKIE} !solana= [NC] +RewriteCond %{HTTP_COOKIE} !potlu= [NC] RewriteRule .* "%{REQUEST_SCHEME}://%{HTTP_HOST}/backoffice/login.html" [L] # Require all denied \ No newline at end of file diff --git a/backoffice/data/company/.htaccess b/backoffice/data/company/.htaccess new file mode 100644 index 00000000..b4b826d0 --- /dev/null +++ b/backoffice/data/company/.htaccess @@ -0,0 +1,5 @@ +RewriteEngine On +RewriteBase / +RewriteCond %{HTTP_COOKIE} !potlu= [NC] +RewriteRule .* "%{REQUEST_SCHEME}://%{HTTP_HOST}/backoffice/login.html" [L] +# Require all denied \ No newline at end of file diff --git a/backoffice/data/schemata/.htaccess b/backoffice/data/schemata/.htaccess new file mode 100644 index 00000000..b4b826d0 --- /dev/null +++ b/backoffice/data/schemata/.htaccess @@ -0,0 +1,5 @@ +RewriteEngine On +RewriteBase / +RewriteCond %{HTTP_COOKIE} !potlu= [NC] +RewriteRule .* "%{REQUEST_SCHEME}://%{HTTP_HOST}/backoffice/login.html" [L] +# Require all denied \ No newline at end of file diff --git a/backoffice/data/schemata/defaultcompany.schema.sql b/backoffice/data/schemata/defaultcompany.schema.sql new file mode 100644 index 00000000..f7db8e9a --- /dev/null +++ b/backoffice/data/schemata/defaultcompany.schema.sql @@ -0,0 +1,1193 @@ + +CREATE SCHEMA %%NEWSCHEMA%%; + +CREATE TABLE %%NEWSCHEMA%%.defaultweekworkplan ( + id integer NOT NULL, + id_staff integer, + startdate date, + mon json, + tue json, + wed json, + thu json, + fri json, + sat json, + sun json +); + +CREATE SEQUENCE %%NEWSCHEMA%%.defaultweekworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.defaultweekworkplan_id_seq OWNED BY %%NEWSCHEMA%%.defaultweekworkplan.id; + +CREATE TABLE %%NEWSCHEMA%%.defaultworkplan ( + id bigint NOT NULL, + id_staff integer, + daydate date, + plannedtimes json, + vacancytimes json, + presencetimes json +); + +CREATE SEQUENCE %%NEWSCHEMA%%.defaultworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.defaultworkplan_id_seq OWNED BY %%NEWSCHEMA%%.defaultworkplan.id; + +CREATE TABLE %%NEWSCHEMA%%.reportperiod ( + id integer NOT NULL, + periodname text, + startdate date, + enddate date +); + +CREATE SEQUENCE %%NEWSCHEMA%%.reportperiod_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.reportperiod_id_seq OWNED BY %%NEWSCHEMA%%.reportperiod.id; + +CREATE TABLE %%NEWSCHEMA%%.sites ( + id integer NOT NULL, + sitename text, + address text, + zip text, + city text, + country text, + id_timetracker integer, + created timestamp without time zone DEFAULT now(), + modified timestamp without time zone DEFAULT now(), + timeclockhost text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.sites_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.sites_id_seq OWNED BY %%NEWSCHEMA%%.sites.id; + +CREATE TABLE %%NEWSCHEMA%%.staff ( + id integer NOT NULL, + staffnumber text, + surname text, + prename text, + job text, + birthdate date, + entrydate date, + leavedate date +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staff_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staff_id_seq OWNED BY %%NEWSCHEMA%%.staff.id; + +CREATE TABLE %%NEWSCHEMA%%.staffgroups ( + id integer NOT NULL, + groupname text, + groupcolor text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffgroups_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffgroups_id_seq OWNED BY %%NEWSCHEMA%%.staffgroups.id; + +CREATE TABLE %%NEWSCHEMA%%.staffperiodbase ( + id integer NOT NULL, + id_staff integer, + startdate date, + monthhours numeric, + weekhours numeric +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffperiodbase_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffperiodbase_id_seq OWNED BY %%NEWSCHEMA%%.staffperiodbase.id; + +CREATE TABLE %%NEWSCHEMA%%.stafftimetracks ( + id bigint NOT NULL, + id_staff integer, + stamp_in timestamp without time zone, + stamp_out timestamp without time zone, + tracktype text, + created timestamp without time zone DEFAULT now(), + modified timestamp without time zone DEFAULT now() +); + +CREATE SEQUENCE %%NEWSCHEMA%%.stafftimetracks_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.stafftimetracks_id_seq OWNED BY %%NEWSCHEMA%%.stafftimetracks.id; + +CREATE TABLE %%NEWSCHEMA%%.staffvacancy ( + id integer NOT NULL, + id_staff integer, + startdate date, + enddate date, + vacancytype text, + dayhours time without time zone, + note text, + validated boolean +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffvacancy_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffvacancy_id_seq OWNED BY %%NEWSCHEMA%%.staffvacancy.id; + +CREATE TABLE %%NEWSCHEMA%%.staffvacancyyear ( + id integer NOT NULL, + id_staff integer, + vyear integer, + hours numeric, + days numeric +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffvacancyyear_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffvacancyyear_id_seq OWNED BY %%NEWSCHEMA%%.staffvacancyyear.id; + +CREATE TABLE %%NEWSCHEMA%%.staffworkplan ( + id bigint NOT NULL, + id_staff integer, + daydate date, + timestart1 time without time zone, + timeend1 time without time zone, + timestart2 time without time zone, + timeend2 time without time zone, + timepause time without time zone, + vacancyhours time without time zone, + vacancytype text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffworkplan_id_seq OWNED BY %%NEWSCHEMA%%.staffworkplan.id; + +CREATE VIEW %%NEWSCHEMA%%.vw_reportperiodlist AS + SELECT reportperiod.id, + reportperiod.periodname, + reportperiod.startdate, + reportperiod.enddate + FROM %%NEWSCHEMA%%.reportperiod; + +CREATE VIEW %%NEWSCHEMA%%.vw_stafflist AS + SELECT staff.id, + staff.staffnumber, + staff.surname, + staff.prename, + staff.job, + staff.birthdate, + staff.entrydate, + staff.leavedate, + ((staff.surname || ' '::text) || staff.prename) AS dspname + FROM %%NEWSCHEMA%%.staff; + +CREATE VIEW %%NEWSCHEMA%%.vw_staffplanned_dayweektotals AS + SELECT stw2.calweek, + stw2.caldate AS dates, + stw2.id_staff, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS mon_id, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS mon_timetotal, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS tue_id, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS tue_timetotal, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS wed_id, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS wed_timetotal, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS thu_id, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS thu_timetotal, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS fri_id, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS fri_timetotal, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sat_id, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sat_timetotal, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sun_id, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sun_timetotal, + to_char(sum(((stw2.time1 + stw2.time2) - (stw2.timepause)::interval)), 'HH24:MI'::text) AS week_timetotal + FROM ( SELECT staffworkplan.daydate, + date_part('week'::text, staffworkplan.daydate) AS calweek, + (date_trunc('week'::text, (staffworkplan.daydate)::timestamp with time zone))::date AS caldate, + date_part('isodow'::text, staffworkplan.daydate) AS isodow, + staffworkplan.id, + staffworkplan.id_staff, + staffworkplan.timestart1, + staffworkplan.timeend1, + CASE + WHEN (staffworkplan.timestart1 > staffworkplan.timeend1) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart1 - staffworkplan.timeend1))::time without time zone) + ELSE (staffworkplan.timeend1 - staffworkplan.timestart1) + END AS time1, + staffworkplan.timestart2, + staffworkplan.timeend2, + CASE + WHEN (staffworkplan.timestart2 > staffworkplan.timeend2) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart2 - staffworkplan.timeend2))::time without time zone) + ELSE (staffworkplan.timeend2 - staffworkplan.timestart2) + END AS time2, + staffworkplan.timepause + FROM %%NEWSCHEMA%%.staffworkplan) stw2 + GROUP BY stw2.calweek, stw2.caldate, stw2.id_staff; + +CREATE VIEW %%NEWSCHEMA%%.vw_staffworkplan_weekly AS + SELECT stw2.calweek, + to_char((stw2.caldate)::timestamp with time zone, 'YYYY'::text) AS calyear, + stw2.caldate AS weekbegin, + public.getdateslist(stw2.caldate, 7) AS dates, + stw2.id_staff, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS mon_id, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_pause, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS mon_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS mon_timetotal, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS tue_id, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_pause, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS tue_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS tue_timetotal, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS wed_id, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_pause, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS wed_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS wed_timetotal, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS thu_id, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_pause, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS thu_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS thu_timetotal, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS fri_id, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_pause, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS fri_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS fri_timetotal, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sat_id, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_pause, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS sat_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sat_timetotal, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sun_id, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_pause, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS sun_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sun_timetotal, + to_char(sum(((stw2.time1 + stw2.time2) - (stw2.timepause)::interval)), 'HH24:MI'::text) AS week_timetotal + FROM ( SELECT staffworkplan.daydate, + date_part('week'::text, staffworkplan.daydate) AS calweek, + (date_trunc('week'::text, (staffworkplan.daydate)::timestamp with time zone))::date AS caldate, + date_part('isodow'::text, staffworkplan.daydate) AS isodow, + staffworkplan.id, + staffworkplan.id_staff, + staffworkplan.timestart1, + staffworkplan.timeend1, + staffworkplan.vacancyhours, + staffworkplan.vacancytype, + CASE + WHEN (staffworkplan.timestart1 > staffworkplan.timeend1) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart1 - staffworkplan.timeend1))::time without time zone) + ELSE (staffworkplan.timeend1 - staffworkplan.timestart1) + END AS time1, + staffworkplan.timestart2, + staffworkplan.timeend2, + CASE + WHEN (staffworkplan.timestart2 > staffworkplan.timeend2) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart2 - staffworkplan.timeend2))::time without time zone) + ELSE (staffworkplan.timeend2 - staffworkplan.timestart2) + END AS time2, + staffworkplan.timepause + FROM %%NEWSCHEMA%%.staffworkplan) stw2 + GROUP BY stw2.calweek, stw2.caldate, stw2.id_staff; + +CREATE VIEW %%NEWSCHEMA%%.vw_staffworkplanlist AS + SELECT st.id AS id_staff, + ((st.surname || ' '::text) || st.prename) AS staffname, + (((((('Semaine '::text || sp_dwt.calweek) || '
('::text) || to_char((sp_dwt.weekbegin)::timestamp with time zone, 'DD.MM.YYYY'::text)) || ' - '::text) || to_char((date((sp_dwt.weekbegin + '7 days'::interval)))::timestamp with time zone, 'DD.MM.YYYY'::text)) || ')'::text) AS calweek, + sp_dwt.week_timetotal, + sp_dwt.weekbegin AS weekstart, + date((sp_dwt.weekbegin + '7 days'::interval)) AS weekend, + to_char((((((( + CASE + WHEN (sp_dwt.mon_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.mon_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END + + CASE + WHEN (sp_dwt.tue_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.tue_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.wed_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.wed_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.thu_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.thu_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.fri_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.fri_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sat_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.sat_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sun_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.sun_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END), 'HH24:MI'::text) AS weekvacancy_times_ill, + to_char((((((( + CASE + WHEN (sp_dwt.mon_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.mon_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END + + CASE + WHEN (sp_dwt.tue_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.tue_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.wed_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.wed_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.thu_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.thu_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.fri_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.fri_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sat_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.sat_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sun_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.sun_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END), 'HH24:MI'::text) AS weekvacancy_times, + (((((( + CASE + WHEN (sp_dwt.mon_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.mon_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.mon_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END + + CASE + WHEN (sp_dwt.tue_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.tue_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.tue_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.wed_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.wed_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.wed_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.thu_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.thu_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.thu_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.fri_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.fri_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.fri_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sat_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sat_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sat_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sun_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sun_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sun_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) AS weekvacancy_minutes_ill, + (((((( + CASE + WHEN (sp_dwt.mon_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.mon_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.mon_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END + + CASE + WHEN (sp_dwt.tue_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.tue_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.tue_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.wed_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.wed_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.wed_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.thu_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.thu_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.thu_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.fri_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.fri_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.fri_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sat_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sat_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sat_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sun_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sun_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sun_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) AS weekvacancy_minutes, + ((to_number("left"(sp_dwt.week_timetotal, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.week_timetotal, 2), '99'::text)) AS weekminutes, + sp_dwt.mon_id, + sp_dwt.weekbegin AS mon_date, + ((COALESCE(((to_char((sp_dwt.mon_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.mon_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.mon_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.mon_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.mon_vacancytype) || ': '::text) || to_char((sp_dwt.mon_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspmontimes, + sp_dwt.mon_timetotal, + sp_dwt.tue_id, + date((sp_dwt.weekbegin + '1 day'::interval)) AS tue_date, + ((COALESCE(((to_char((sp_dwt.tue_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.tue_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.tue_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.tue_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.tue_vacancytype) || ': '::text) || to_char((sp_dwt.tue_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dsptuetimes, + sp_dwt.tue_timetotal, + sp_dwt.wed_id, + date((sp_dwt.weekbegin + '2 days'::interval)) AS wed_date, + ((COALESCE(((to_char((sp_dwt.wed_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.wed_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.wed_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.wed_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.wed_vacancytype) || ': '::text) || to_char((sp_dwt.wed_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspwedtimes, + sp_dwt.wed_timetotal, + sp_dwt.thu_id, + date((sp_dwt.weekbegin + '3 days'::interval)) AS thu_date, + ((COALESCE(((to_char((sp_dwt.thu_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.thu_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.thu_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.thu_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.thu_vacancytype) || ': '::text) || to_char((sp_dwt.thu_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspthutimes, + sp_dwt.thu_timetotal, + sp_dwt.fri_id, + date((sp_dwt.weekbegin + '4 days'::interval)) AS fri_date, + ((COALESCE(((to_char((sp_dwt.fri_start1)::interval, 'HH24:MI'::text) || ' -'::text) || to_char((sp_dwt.fri_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.fri_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.fri_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.fri_vacancytype) || ': '::text) || to_char((sp_dwt.fri_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspfritimes, + sp_dwt.fri_timetotal, + sp_dwt.sat_id, + date((sp_dwt.weekbegin + '5 days'::interval)) AS sat_date, + ((COALESCE(((to_char((sp_dwt.sat_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.sat_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.sat_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.sat_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.sat_vacancytype) || ': '::text) || to_char((sp_dwt.sat_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspsattimes, + sp_dwt.sat_timetotal, + sp_dwt.sun_id, + date((sp_dwt.weekbegin + '6 days'::interval)) AS sun_date, + ((COALESCE(((to_char((sp_dwt.sun_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.sun_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.sun_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.sun_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.sun_vacancytype) || ': '::text) || to_char((sp_dwt.sun_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspsuntimes, + sp_dwt.sun_timetotal + FROM (%%NEWSCHEMA%%.vw_staffworkplan_weekly sp_dwt + LEFT JOIN %%NEWSCHEMA%%.staff st ON ((sp_dwt.id_staff = st.id))) + ORDER BY sp_dwt.calweek; + +CREATE TABLE %%NEWSCHEMA%%.workplans ( + id integer NOT NULL, + workplan text, + mon_timestart1 time without time zone, + mon_timeend1 time without time zone, + mon_timestart2 time without time zone, + mon_timeend2 time without time zone, + mon_timepause time without time zone, + tue_timestart1 time without time zone, + tue_timeend1 time without time zone, + tue_timestart2 time without time zone, + tue_timeend2 time without time zone, + tue_timepause time without time zone, + wed_timestart1 time without time zone, + wed_timeend1 time without time zone, + wed_timestart2 time without time zone, + wed_timeend2 time without time zone, + wed_timepause time without time zone, + thu_timestart1 time without time zone, + thu_timeend1 time without time zone, + thu_timestart2 time without time zone, + thu_timeend2 time without time zone, + thu_timepause time without time zone, + fri_timestart1 time without time zone, + fri_timeend1 time without time zone, + fri_timestart2 time without time zone, + fri_timeend2 time without time zone, + fri_timepause time without time zone, + sat_timestart1 time without time zone, + sat_timeend1 time without time zone, + sat_timestart2 time without time zone, + sat_timeend2 time without time zone, + sat_timepause time without time zone, + sun_timestart1 time without time zone, + sun_timeend1 time without time zone, + sun_timestart2 time without time zone, + sun_timeend2 time without time zone, + sun_timepause time without time zone +); + +CREATE VIEW %%NEWSCHEMA%%.vw_workplanlist AS + SELECT workplans.id, + workplans.workplan + FROM %%NEWSCHEMA%%.workplans; + +CREATE VIEW %%NEWSCHEMA%%.vw_workplans AS + SELECT workplans.id, + workplans.workplan, + ((COALESCE(((to_char((workplans.mon_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.mon_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.mon_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.mon_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.mon_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspmontimes, + ((COALESCE(((to_char((workplans.tue_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.tue_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.tue_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.tue_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.tue_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dsptuetimes, + ((COALESCE(((to_char((workplans.wed_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.wed_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.wed_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.wed_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.wed_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspwedtimes, + ((COALESCE(((to_char((workplans.thu_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.thu_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.thu_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.thu_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.thu_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspthutimes, + ((COALESCE(((to_char((workplans.fri_timestart1)::interval, 'HH24:MI'::text) || ' -'::text) || to_char((workplans.fri_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.fri_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.fri_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.fri_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspfritimes, + ((COALESCE(((to_char((workplans.sat_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.sat_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.sat_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.sat_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.sat_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspsattimes, + ((COALESCE(((to_char((workplans.sun_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.sun_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.sun_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.sun_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.sun_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspsuntimes + FROM %%NEWSCHEMA%%.workplans; + +CREATE VIEW %%NEWSCHEMA%%.vw_workplansdata AS + SELECT workplans.id, + workplans.workplan, + to_char((workplans.mon_timestart1)::interval, 'HH24:MI'::text) AS mon_timestart1, + to_char((workplans.mon_timeend1)::interval, 'HH24:MI'::text) AS mon_timeend1, + to_char((workplans.mon_timestart2)::interval, 'HH24:MI'::text) AS mon_timestart2, + to_char((workplans.mon_timeend2)::interval, 'HH24:MI'::text) AS mon_timeend2, + to_char((workplans.mon_timepause)::interval, 'HH24:MI'::text) AS mon_timepause, + to_char((workplans.tue_timestart1)::interval, 'HH24:MI'::text) AS tue_timestart1, + to_char((workplans.tue_timeend1)::interval, 'HH24:MI'::text) AS tue_timeend1, + to_char((workplans.tue_timestart2)::interval, 'HH24:MI'::text) AS tue_timestart2, + to_char((workplans.tue_timeend2)::interval, 'HH24:MI'::text) AS tue_timeend2, + to_char((workplans.tue_timepause)::interval, 'HH24:MI'::text) AS tue_timepause, + to_char((workplans.wed_timestart1)::interval, 'HH24:MI'::text) AS wed_timestart1, + to_char((workplans.wed_timeend1)::interval, 'HH24:MI'::text) AS wed_timeend1, + to_char((workplans.wed_timestart2)::interval, 'HH24:MI'::text) AS wed_timestart2, + to_char((workplans.wed_timeend2)::interval, 'HH24:MI'::text) AS wed_timeend2, + to_char((workplans.wed_timepause)::interval, 'HH24:MI'::text) AS wed_timepause, + to_char((workplans.thu_timestart1)::interval, 'HH24:MI'::text) AS thu_timestart1, + to_char((workplans.thu_timeend1)::interval, 'HH24:MI'::text) AS thu_timeend1, + to_char((workplans.thu_timestart2)::interval, 'HH24:MI'::text) AS thu_timestart2, + to_char((workplans.thu_timeend2)::interval, 'HH24:MI'::text) AS thu_timeend2, + to_char((workplans.thu_timepause)::interval, 'HH24:MI'::text) AS thu_timepause, + to_char((workplans.fri_timestart1)::interval, 'HH24:MI'::text) AS fri_timestart1, + to_char((workplans.fri_timeend1)::interval, 'HH24:MI'::text) AS fri_timeend1, + to_char((workplans.fri_timestart2)::interval, 'HH24:MI'::text) AS fri_timestart2, + to_char((workplans.fri_timeend2)::interval, 'HH24:MI'::text) AS fri_timeend2, + to_char((workplans.fri_timepause)::interval, 'HH24:MI'::text) AS fri_timepause, + to_char((workplans.sat_timestart1)::interval, 'HH24:MI'::text) AS sat_timestart1, + to_char((workplans.sat_timeend1)::interval, 'HH24:MI'::text) AS sat_timeend1, + to_char((workplans.sat_timestart2)::interval, 'HH24:MI'::text) AS sat_timestart2, + to_char((workplans.sat_timeend2)::interval, 'HH24:MI'::text) AS sat_timeend2, + to_char((workplans.sat_timepause)::interval, 'HH24:MI'::text) AS sat_timepause, + to_char((workplans.sun_timestart1)::interval, 'HH24:MI'::text) AS sun_timestart1, + to_char((workplans.sun_timeend1)::interval, 'HH24:MI'::text) AS sun_timeend1, + to_char((workplans.sun_timestart2)::interval, 'HH24:MI'::text) AS sun_timestart2, + to_char((workplans.sun_timeend2)::interval, 'HH24:MI'::text) AS sun_timeend2, + to_char((workplans.sun_timepause)::interval, 'HH24:MI'::text) AS sun_timepause + FROM %%NEWSCHEMA%%.workplans; + +CREATE SEQUENCE %%NEWSCHEMA%%.workplans_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.workplans_id_seq OWNED BY %%NEWSCHEMA%%.workplans.id; + +CREATE TABLE %%NEWSCHEMA%%.worktypes ( + id integer NOT NULL, + worktype text, + isworktime boolean, + isfreetime boolean, + typecolor text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.worktypes_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.worktypes_id_seq OWNED BY %%NEWSCHEMA%%.worktypes.id; + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultweekworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.defaultweekworkplan_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.defaultworkplan_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.reportperiod ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.reportperiod_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.sites ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.sites_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staff ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staff_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffgroups ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffgroups_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffperiodbase ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffperiodbase_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.stafftimetracks ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.stafftimetracks_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancy ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffvacancy_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancyyear ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffvacancyyear_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffworkplan_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.workplans ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.workplans_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.worktypes ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.worktypes_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultweekworkplan + ADD CONSTRAINT defaultweekworkplan_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultworkplan + ADD CONSTRAINT defaultworkplan_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.reportperiod + ADD CONSTRAINT reportperiod_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.sites + ADD CONSTRAINT sites_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staff + ADD CONSTRAINT staff_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffgroups + ADD CONSTRAINT staffgroups_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffperiodbase + ADD CONSTRAINT staffperiodbase_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.stafftimetracks + ADD CONSTRAINT stafftimetracks_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancy + ADD CONSTRAINT staffvacancy_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancyyear + ADD CONSTRAINT staffvacancyyear_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffworkplan + ADD CONSTRAINT staffworkplan_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.workplans + ADD CONSTRAINT workplans_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.worktypes + ADD CONSTRAINT worktypes_pkey PRIMARY KEY (id); + +CREATE TRIGGER trg_upd_%%NEWSCHEMA%%_weekhours BEFORE UPDATE OF weekhours ON %%NEWSCHEMA%%.staffperiodbase FOR EACH ROW EXECUTE PROCEDURE public.trg_update_monthhours(); diff --git a/backoffice/tmpl/module/companies/index.js b/backoffice/tmpl/module/companies/index.js index 52ae0dac..dc07ef2e 100644 --- a/backoffice/tmpl/module/companies/index.js +++ b/backoffice/tmpl/module/companies/index.js @@ -1,170 +1,154 @@ -var tbl = null; -[% sid = session.id %] -[% IF(session.usergroups.search('admin') != 1) %] -[% qcompany = dksdb.query("select id_company from vw_userlist where id=$sid;") %] -[% company = qcompany.get_all() %] -var idcompany = "[% company.0.id_company %]"; -[% ELSE %] -var idcompany = null; -[% END %] -//var choice = {"submissionaccess":{"projects":null,"cdm":null,"cdm_requests":null}}; -var tab_id_projects = null; -var tab_id_cdms = null; -var tab_id_cdm_requests = null; - -function initpage() { - [% IF(session.usergroups.search('admin') == 1) %] - // choice["submissionaccess"]["projects"] = new Choices('#projects',{ - // searchEnabled: false, - // itemSelectText: 'sélectionner...', - // removeItemButton: true, - // choices : [] - // }); - - // choice["submissionaccess"]["cdm"] = new Choices('#cdm',{ - // searchEnabled: false, - // itemSelectText: 'sélectionner...', - // removeItemButton: true, - // choices : [] - // }); - // [% END %] - // choice["submissionaccess"]["cdm_requests"] = new Choices('#cdm_requests',{ - // searchEnabled: false, - // itemSelectText: 'sélectionner...', - // removeItemButton: true, - // choices : [] - // }); - //getprojects(); - //getcdms(); - [% IF(session.usergroups.search('admin') == 1) %] - tbl = new Tabulator("#tbl_companies", { - headerFilterPlaceholder: "filter...", - height: "95vh", - layout: "fitDataFill", - selectable: 1, - responsiveLayout: "collapse", - columns: [{ - title: "entreprise", - field: "company", - headerFilter: "input" - }, { - title: "address", - field: "address", - headerFilter: "input" - }, { - title: "cp", - field: "zip", - headerFilter: "input" - }, { - title: "ville", - field: "city", - headerFilter: "input" - }, { - title: "pays", - field: "country", - headerFilter: "input" - }] - }); - gettbldata(); - - [% ELSE %] - getcompanydata(idcompany); - [% END %] +var schemata = "public"; +var currentview = null; +function initpage(){ + //schemata = parent.admin.getcurrentSchemata(); + console.log("Current Schema:" + schemata); + companies.inittable(); + module.viewpanel("tbl_companies"); + //call("reportperiod","inittable"); +// flatpickr(".timefield",{ +// //altInput: true, +// //altFormat: "H:i", +// dateFormat: "H:i", +// //allowInput: true, +// defaultHour:'', +// defaultMinute:'', +// enableTime: true, +// noCalendar: true, +// time_24hr: true, +// "locale": "fr", +// }); +} +// var tbl = null; +// [% sid = session.id %] +// [% IF(session.usergroups.search('admin') != 1) %] +// [% qcompany = dksdb.query("select id_company from vw_userlist where id=$sid;") %] +// [% company = qcompany.get_all() %] +// var idcompany = "[% company.0.id_company %]"; +// [% ELSE %] +// var idcompany = null; +// [% END %] +// //var choice = {"submissionaccess":{"projects":null,"cdm":null,"cdm_requests":null}}; + +// function initpage() { +// [% IF(session.usergroups.search('admin') == 1) %] + +// tbl = new Tabulator("#tbl_companies", { +// headerFilterPlaceholder: "filter...", +// height: "95vh", +// layout: "fitDataFill", +// selectable: 1, +// responsiveLayout: "collapse", +// columns: [{ +// title: "entreprise", +// field: "company", +// headerFilter: "input" +// }, { +// title: "address", +// field: "address", +// headerFilter: "input" +// }, { +// title: "cp", +// field: "zip", +// headerFilter: "input" +// }, { +// title: "ville", +// field: "city", +// headerFilter: "input" +// }, { +// title: "pays", +// field: "country", +// headerFilter: "input" +// }] +// }); +// gettbldata(); + +// [% ELSE %] +// getcompanydata(idcompany); +// [% END %] -} -[% IF(session.usergroups.search('admin') == 1) %] -function gettbldata() { - req.reqdata("POST", "db.cgi", { - "get": "companylist" - }, loadtbldata); +// } +// [% IF(session.usergroups.search('admin') == 1) %] -} -function loadtbldata(data) { - if (data && data.sqldata) { - tbl.setData(data.sqldata); - } -} -function edit() { - var udata = tbl.getSelectedData(); - if (udata[0]) { - var uid = udata[0].id; - getcompanydata(uid); - - } +// function gettbldata() { +// req.reqdata("POST", "db.cgi", { +// "get": "companylist" +// }, loadtbldata); -} +// } -function add() { - cleanform('companies'); - viewform(); -} +// function loadtbldata(data) { +// if (data && data.sqldata) { +// tbl.setData(data.sqldata); +// } +// } -function remove() { - var udata = tbl.getSelectedData(); - if (udata[0]) { - if (confirm("Êtes vous sûre de supprimer l'entreprise selectionné?")) { - req.reqdata("POST", "db.cgi", { - "del": "1", - "ident_companies_id": udata[0].id - }, gettbldata); - } - } +// function edit() { + +// var udata = tbl.getSelectedData(); +// console.log(udata[0]); +// if (udata[0]) { +// var uid = udata[0].id; +// getcompanydata(uid); + +// } -} -[% END %] +// } +// function add() { +// cleanform('companies'); +// viewform(); +// } -function getcompanydata(id) { - req.reqdata("POST", "db.cgi", { - "get": "companydata", - "filter": "id=" + id - }, fillformcompany); - //req.reqdata("POST","db.cgi",{"get":"submissionaccess","filter":"id_company=" + id},fillsubmissionaccess); -} +// function remove() { +// var udata = tbl.getSelectedData(); +// if (udata[0]) { +// if (confirm("Êtes vous sûre de supprimer l'entreprise selectionné?")) { +// req.reqdata("POST", "db.cgi", { +// "del": "1", +// "ident_companies_id": udata[0].id +// }, gettbldata); +// } +// } +// } +// [% END %] -// function fillsubmissionaccess(data){ -// console.log("FILL Submission Access!"); -// console.log(data); -// cleanform('submissionaccess'); -// if (data && data.sqldata){ -// //var frm = document.querySelectorAll('.data_submissionaccess'); -// fillformbydataclass('submissionaccess',data.sqldata[0],false); -// } +// function getcompanydata(id) { +// req.reqdata("POST", "db.cgi", {"get": "companydata","filter": "id=" + id}, fillformcompany); // } -function fillformcompany(data) { - // console.log(data); - cleanform('companies'); - var isreadonly = false; - [% IF(session.usergroups.search('admin') != 1) %] - if (data.sqldata[0]["validated"] == 1) { - isreadonly = true; - } - [% END %] - if (data && data.sqldata) { - fillformbydataclass('companies', data.sqldata[0], isreadonly); - } - if (isreadonly == true) { - document.getElementById("btnsave_companies").style.display = 'none'; - } -} +// function fillformcompany(data) { +// cleanform('companies'); +// var isreadonly = false; +// [% IF(session.usergroups.search('admin') != 1) %] +// if (data.sqldata[0]["validated"] == 1) { +// isreadonly = true; +// } +// [% END %] +// if (data && data.sqldata) { +// fillformbydataclass('companies', data.sqldata[0], isreadonly); +// } +// if (isreadonly == true) { +// document.getElementById("btnsave_companies").style.display = 'none'; +// } +// } -function viewtable() { - gettbldata(); - document.getElementById("pnl_table").style.display = 'block'; - document.getElementById("pnl_company").style.display = 'none'; -} +// function viewtable() { +// gettbldata(); +// document.getElementById("pnl_table").style.display = 'block'; +// document.getElementById("pnl_company").style.display = 'none'; +// } -function viewform(){ - document.getElementById("pnl_table").style.display = 'none'; - document.getElementById("pnl_company").style.display = 'block'; -} +// function viewform(){ +// document.getElementById("pnl_table").style.display = 'none'; +// document.getElementById("pnl_company").style.display = 'block'; +// } //[% IF(session.usergroups.search('admin') == 1) %] // function getprojects(){ diff --git a/backoffice/tmpl/module/companies/index.tt b/backoffice/tmpl/module/companies/index.tt index 484ee63f..f59f8b08 100644 --- a/backoffice/tmpl/module/companies/index.tt +++ b/backoffice/tmpl/module/companies/index.tt @@ -1,72 +1,13 @@ -[% PROCESS macro/fields.tt %] -[% IF (session.usergroups.search('admin') == 1) %] -
-
- - - - - - - -
-
-
-
-
-[% END %] -
- -
-
-
-

données de l'entreprise

-
-
- - [% fieldhidden('id','companies','ident') %] -
- [% fieldeditbox('company','companies','Entreprise','w3-half','','','') %] - - [% fieldeditbox('address','companies','Adresse','','','','') %] - [% fieldeditbox('zip','companies','CP','w3-fifth','','','') %] - [% fieldeditbox('city','companies','Ville','w3-half','','','') %] - [% fieldeditbox('country','companies','Pays','w3-quarter','','','') %] -
- [% fieldeditbox('comregister','companies','No. Registre de Commerce','w3-quarter','','','') %] - [% fieldeditbox('vatnumber','companies','No TVA Internationale','w3-quarter','','','') %] -
- [% fieldeditbox('email','companies','E-Mail (pour envoie de factures)','w3-quarter','','','') %] -
- -
+
+
+
+ - [% formsavebutton('companies','sauvegarder') %] -
- -
- - -
-
-
\ No newline at end of file + [% INCLUDE "module/$module/widgets/companies/tbar_companies.tt" %] +
+
+ [% INCLUDE "module/$module/widgets/companies/tbl_companies.tt" %] + [% INCLUDE "module/$module/widgets/companies/frm_companies.tt" %] + +[% INCLUDE block/dlgdeleterow.tt %] + diff --git a/backoffice/tmpl/module/companies/widgets/companies/companies.js b/backoffice/tmpl/module/companies/widgets/companies/companies.js new file mode 100644 index 00000000..d3eb7510 --- /dev/null +++ b/backoffice/tmpl/module/companies/widgets/companies/companies.js @@ -0,0 +1,104 @@ +//var tbl_companies = null; +//var weekdays= ["mon","tue","wed","thu","fri","sat","sun"]; +var companies ={ + tbl: null, + current_company: null, + name: "companies", + choices:{}, + initform: function(){ + + }, + inittable: function(){ + companies.initform(); + companies.tbl = new Tabulator("#tbl_" + companies.name, { + headerFilterPlaceholder: "filter...", + height: "94vh", + layout: "fitData", + selectable: 1, + rowContext:function(e, row){ + //e - the click event object + //row - row component + e.preventDefault(); // prevent the browsers default context menu form appearing. + }, + columns: [{ + title: "entreprise", + field: "company", + headerFilter: "input" + }, { + title: "address", + field: "address", + headerFilter: "input" + }, { + title: "cp", + field: "zip", + headerFilter: "input" + }, { + title: "ville", + field: "city", + headerFilter: "input" + }, { + title: "pays", + field: "country", + headerFilter: "input" + }] + }); + companies.gettbldata(); + }, + gettbldata: function(){ + req.reqdata("POST", "db.cgi", { "get": companies.name + "list","schemata":schemata}, companies.loadtbldata); + }, + loadtbldata: function(data){ + if (data && data.sqldata) { companies.tbl.setData(data.sqldata);} + }, + add: function(){ + cleanform2(companies.name,companies.choices); + module.viewpanel('frm_' + companies.name); + }, + edit: function(){ + var udata = companies.tbl.getSelectedData(); + if (udata[0]) { + cleanform2(companies.name,companies.choices); + req.reqdata("POST", "db.cgi", { "get": companies.name + "data","schemata":schemata, "filter":"id='" + udata[0].id + "'"}, companies.fillform); + module.viewpanel('frm_' +companies.name); + } + }, + fillform: function(data){ + console.log("Fill Form 1"); + if (data && data.sqldata){ + console.log("Fill Form 2"); + fillformbydataclass2(companies.name,companies.choices,data.sqldata[0]); + } + }, + remove: function(){ + var udata = companies.tbl.getSelectedData(); + if (udata[0]) { + var uid = udata[0].id; + //TODO: delete Data + module.viewpanel('tbl_' + companies.name); + } + }, + saveform: function(){ + var wpdata = getformcontent(companies.name); + //TODO: save form + console.log(wpdata); + }, + // getcompany: function(ev,id){ + // console.log(id + "=>" + ev.detail.value); + // req.reqdata("POST","db.cgi",{"get":companies.name + "data","schemata":schemata,"filter":"id='" + ev.detail.value + "'"},companies.setcompany); + // }, + // setcompany: function(data){ + // if (data && data.sqldata){ + // console.log(data.sqldata[0]); + // fillformbydataclass("companies",data.sqldata[0],false); + // } + // }, + +} + + + + + + + + diff --git a/backoffice/tmpl/module/companies/widgets/companies/frm_companies.tt b/backoffice/tmpl/module/companies/widgets/companies/frm_companies.tt new file mode 100644 index 00000000..3638f51d --- /dev/null +++ b/backoffice/tmpl/module/companies/widgets/companies/frm_companies.tt @@ -0,0 +1,35 @@ +[% PROCESS macro/fields.tt %] + + diff --git a/backoffice/tmpl/module/companies/widgets/companies/tbar_companies.tt b/backoffice/tmpl/module/companies/widgets/companies/tbar_companies.tt new file mode 100644 index 00000000..038663ce --- /dev/null +++ b/backoffice/tmpl/module/companies/widgets/companies/tbar_companies.tt @@ -0,0 +1,7 @@ + + \ No newline at end of file diff --git a/backoffice/tmpl/module/companies/widgets/companies/tbl_companies.tt b/backoffice/tmpl/module/companies/widgets/companies/tbl_companies.tt new file mode 100644 index 00000000..26b61d12 --- /dev/null +++ b/backoffice/tmpl/module/companies/widgets/companies/tbl_companies.tt @@ -0,0 +1,3 @@ +
+
+
\ No newline at end of file diff --git a/dev/checkvat.pl b/dev/checkvat.pl new file mode 100644 index 00000000..87d2408a --- /dev/null +++ b/dev/checkvat.pl @@ -0,0 +1,15 @@ +#!/Users/kilian/perl5/perlbrew/perls/perl-5.24.1/bin/perl + +use strict; +use Business::Tax::VAT::Validation; +use Data::Dumper; + +my $hvatn=Business::Tax::VAT::Validation->new(); +my $VAT="LU25375617"; +if ($hvatn->check($VAT)){ + print "OK\n"; + my $infos=$hvatn->informations(); + print Dumper($infos); +} else { + print $hvatn->get_last_error; +} \ No newline at end of file diff --git a/dev/db/gendefaultschema.sh b/dev/db/gendefaultschema.sh new file mode 100755 index 00000000..feaf8f71 --- /dev/null +++ b/dev/db/gendefaultschema.sh @@ -0,0 +1,20 @@ +#!/bin/bash + +CALLDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )" +COMPANYSCHEMA='%%NEWSCHEMA%%' + +if [[ $# -eq 0 ]] +then + echo "No args" + exit 0 +fi +DBSCHEMA=$1 +cat ${CALLDIR}"/potlu_db."${DBSCHEMA}".pg.schema.sql" | \ +grep -v -e "^--" | \ +grep -v -e "^SET" | \ +grep -v "AS integer" | \ +grep -v "SELECT pg_catalog" \ +> ${CALLDIR}"/potlu_db.newcompanyschema.pg.schema.sql" +sed -i -e 's/'${DBSCHEMA}'/'${COMPANYSCHEMA}'/g' ${CALLDIR}"/potlu_db.newcompanyschema.pg.schema.sql" +sed -i -e '/^$/N;/^\n$/D' ${CALLDIR}"/potlu_db.newcompanyschema.pg.schema.sql" +cp ${CALLDIR}"/potlu_db.newcompanyschema.pg.schema.sql" ${CALLDIR}"/../../backoffice/data/schemata/defaultcompany.schema.sql" diff --git a/dev/db/l/lib/perl5/Business/Tax/VAT/Validation.pm b/dev/db/l/lib/perl5/Business/Tax/VAT/Validation.pm new file mode 100755 index 00000000..2b859cbc --- /dev/null +++ b/dev/db/l/lib/perl5/Business/Tax/VAT/Validation.pm @@ -0,0 +1,485 @@ +package Business::Tax::VAT::Validation; +=pod + +=encoding UTF-8 + +=cut + + ############################################################################ +# Original author: # +# IT Development software # +# European VAT number validator Version 1.0.2 # +# Created 06/08/2003 Last Modified 30/11/2012 # +# # +# Maintainership kindly handed over to David Precious (BIGPRESH) in 2015 # + ############################################################################ +# COPYRIGHT NOTICE # +# Copyright 2003 Bernard Nauwelaerts All Rights Reserved. # +# Copyright 2015 David Precious All Rights Reserved. # +# # +# THIS SOFTWARE IS RELEASED UNDER THE GNU Public Licence # +# Please see COPYING for details # +# # +# DISCLAIMER # +# As usual with GNU software, this one is provided as is, # +# WITHOUT ANY WARRANTY, without even the implied warranty of # +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. # +# # +############################################################################ +use strict; + +BEGIN { + $Business::Tax::VAT::Validation::VERSION = '1.11'; + use HTTP::Request::Common qw(POST); + use LWP::UserAgent; +} + +=head1 NAME + +Business::Tax::VAT::Validation - Validate EU VAT numbers against VIES + +=head1 SYNOPSIS + + use Business::Tax::VAT::Validation; + + my $hvatn=Business::Tax::VAT::Validation->new(); + + # Check number + if ($hvatn->check($VAT, [$member_state])){ + print "OK\n"; + } else { + print $hvatn->get_last_error; + } + +=head1 DESCRIPTION + +This class provides an easy API to check European VAT numbers' syntax, +and if they has been registered by the competent authorities. + +It asks the EU database (VIES) for this, using its SOAP API. Basic checks that +the supplied VAT number fit the expected format for the specified EU member +state are performed first, to avoid unnecessarily sending queries to VIES for +input that could never be valid. + + +=head1 CONSTRUCTOR + +=over 4 + +=item B Class constructor. + + $hvatn=Business::Tax::VAT::Validation->new(); + + + If your system is located behind a proxy : + + $hvatn=Business::Tax::VAT::Validation->new(-proxy => ['http', 'http://example.com:8001/']); + + Note : See LWP::UserAgent for proxy options. + +=cut + +sub new { + my ( $class, %arg ) = @_; + my $self = { + baseurl => $arg{baseurl} || 'http://ec.europa.eu/taxation_customs/vies/services/checkVatService', + error => '', + error_code => 0, + response => '', + re => { + ### t/01_localcheck.t tests if these regexps accepts all regular VAT numbers, according to VIES FAQ + AT => 'U[0-9]{8}', + BE => '0[0-9]{9}', + BG => '[0-9]{9,10}', + CY => '[0-9]{8}[A-Za-z]', + CZ => '[0-9]{8,10}', + DE => '[0-9]{9}', + DK => '[0-9]{2} ?[0-9]{2} ?[0-9]{2} ?[0-9]{2}', + EE => '[0-9]{9}', + EL => '[0-9]{9}', + ES => '([A-Za-z0-9][0-9]{7}[A-Za-z0-9])', + FI => '[0-9]{8}', + FR => '[A-Za-z0-9]{2} ?[0-9]{9}', + GB => '([0-9]{3} ?[0-9]{4} ?[0-9]{2}|[0-9]{3} ?[0-9]{4} ?[0-9]{2} ?[0-9]{3}|GD[0-9]{3}|HA[0-9]{3})', + HR => '[0-9]{11}', + HU => '[0-9]{8}', + IE => '[0-9][A-Za-z0-9\+\*][0-9]{5}[A-Za-z]{1,2}', + IT => '[0-9]{11}', + LT => '([0-9]{9}|[0-9]{12})', + LU => '[0-9]{8}', + LV => '[0-9]{11}', + MT => '[0-9]{8}', + NL => '[0-9]{9}B[0-9]{2}', + PL => '[0-9]{10}', + PT => '[0-9]{9}', + RO => '[0-9]{2,10}', + SE => '[0-9]{12}', + SI => '[0-9]{8}', + SK => '[0-9]{10}', + }, + proxy => $arg{-proxy}, + informations => {} + }; + $self = bless $self, $class; + $self->{members} = join( '|', keys %{ $self->{re} } ); + $self; +} + +=back + +=head1 PROPERTIES + +=over 4 + +=item B Returns all member states 2-digit codes as array + + @ms=$hvatn->member_states; + +=cut + +sub member_states { + ( keys %{ $_[0]->{re} } ); +} + +=item B - Returns a hash list containing one regular expression for each country + +If you want to test a VAT number format ouside this module, e.g. embedded as javascript in a web form. + + %re=$hvatn->regular_expressions; + +returns + + ( + AT => 'U[0-9]{8}', + ... + SK => '[0-9]{10}', + ); + +=cut + +sub regular_expressions { + ( %{ $_[0]->{re} } ); +} + +=back + +=head1 METHODS + +=cut + +=over 4 + +=item B - Checks if a VAT number exists in the VIES database + + $ok=$hvatn->check($vatNumber, [$countryCode]); + +You may either provide the VAT number under its complete form (e.g. BE-123456789, BE123456789) +or specify the VAT and MSC (vatNumber and countryCode) individually. + +Valid MS values are : + + AT, BE, BG, CY, CZ, DE, DK, EE, EL, ES, + FI, FR, GB, HU, IE, IT, LU, LT, LV, MT, + NL, PL, PT, RO, SE, SI, SK + +=cut + +sub check { + my ($self, $vatNumber, $countryCode, @other) = @_; # @other is here for backward compatibility purposes + return $self->_set_error('You must provide a VAT number') unless $vatNumber; + $countryCode ||= ''; + ( $vatNumber, $countryCode ) = $self->_format_vatn( $vatNumber, $countryCode ); + if ($vatNumber) { + my $ua = LWP::UserAgent->new; + if ( ref $self->{proxy} eq 'ARRAY' ) { + $ua->proxy( @{ $self->{proxy} } ); + } else { + $ua->env_proxy; + } + $ua->agent( 'Business::Tax::VAT::Validation/'. $Business::Tax::VAT::Validation::VERSION ); + + my $request = HTTP::Request->new(POST => $self->{baseurl}); + $request->header(SOAPAction => 'http://www.w3.org/2003/05/soap-envelope'); + $request->content(_in_soap_envelope($vatNumber, $countryCode)); + $request->content_type("Content-Type: application/soap+xml; charset=utf-8"); + + my $response = $ua->request($request); + + return $countryCode . '-' . $vatNumber if $self->_is_res_ok( $response->code, $response->decoded_content ); + } + 0; +} + +=item B - Checks if a VAT number format is valid + This method is based on regexps only and DOES NOT ask the VIES database + + $ok=$hvatn->local_check($VAT, [$member_state]); + + +=cut + +sub local_check { + my ( $self, $vatn, $mscc, @other ) = @_; # @other is here for backward compatibility purposes + return $self->_set_error('You must provide a VAT number') unless $vatn; + $mscc ||= ''; + ( $vatn, $mscc ) = $self->_format_vatn( $vatn, $mscc ); + if ($vatn) { + return 1; + } + else { + return 0; + } +} + +=item B - Returns informations related to the last validated VAT number + + %infos=$hvatn->informations(); + + +=cut + +sub informations { + my ( $self, $key, @other ) = @_; + if ($key) { + return $self->{informations}{$key} + } else { + return ($self->{informations}) + } +} + +=item B - Returns the last recorded error code + +=item B - Returns the last recorded error + + my $err = $hvatn->get_last_error_code(); + my $txt = $hvatn->get_last_error(); + +Possible errors are : + +=over 4 + +=item * + -1 The provided VAT number is valid. + +=item * + 0 Unknown MS code : Internal checkup failed (Specified Member State does not exist) + +=item * + 1 Invalid VAT number format : Internal checkup failed (bad syntax) + +=item * + 2 This VAT number doesn't exist in EU database : distant checkup + +=item * + 3 This VAT number contains errors : distant checkup + +=item * + 17 Time out connecting to the database : Temporary error when the connection to the database times out + +=item * + 18 Member Sevice Unavailable: The EU database is unable to reach the requested member's database. + +=item * + 19 The EU database is too busy. + +=item * + 20 Connexion to the VIES database failed. + +=item * + 21 The VIES interface failed to parse a stream. This error occurs unpredictabely, so you should retry your validation request. + +=item * +257 Invalid response, please contact the author of this module. : This normally only happens if this software doesn't recognize any valid pattern into the response document: this generally means that the database interface has been modified, and you'll make the author happy by submitting the returned response !!! + +=item * +500 The VIES server encountered an internal server error. +Error 500 : soap:Server TIMEOUT +Error 500 : soap:Server MS_UNAVAILABLE + +=back + +If error_code > 16, you should temporarily accept the provided number, and periodically perform new checks until response is OK or error < 17 +If error_code > 256, you should temporarily accept the provided number, contact the author, and perform a new check when the software is updated. + +=cut + +sub get_last_error { + $_[0]->{error}; +} + +sub get_last_error_code { + $_[0]->{error_code}; +} + +=item B - Returns the full last response + +=cut + +sub get_last_response { + $_[0]->{response}; +} + +### PRIVATE FUNCTIONS ========================================================== +sub _format_vatn { + my ( $self, $vatn, $mscc ) = @_; + my $null = ''; + $vatn =~ s/\-/ /g; + $vatn =~ s/\./ /g; + $vatn =~ s/\s+/ /g; + if ( !$mscc && $vatn =~ s/^($self->{members}) ?/$null/e ) { + $mscc = $1; + } + return $self->_set_error( 0, "Unknown MS code" ) + if $mscc !~ m/^($self->{members})$/; + my $re = $self->{re}{$mscc}; + return $self->_set_error( 1, "Invalid VAT number format" ) + if $vatn !~ m/^$re$/; + ( $vatn, $mscc ); +} + +sub _in_soap_envelope { + my ($vatNumber, $countryCode)=@_; + ' + + + + '.$countryCode.' + '.$vatNumber.' + + + ' +} + +sub _is_res_ok { + my ( $self, $code, $res ) = @_; + $self->{informations}={}; + $res=~s/[\r\n]/ /g; + $self->{response} = $res; + if ($code == 200) { + if ($res=~m/ *(.*?) *<\/valid>/) { + my $v = $1; + if ($v eq 'true' || $v eq '1') { + if ($res=~m/ *(.*?) *<\/name>/) { + $self->{informations}{name} = $1 + } + if ($res=~m/
*(.*?) *<\/address>/) { + $self->{informations}{address} = $1 + } + $self->_set_error( -1, 'Valid VAT Number'); + return 1; + } else { + return $self->_set_error( 2, 'Invalid VAT Number ('.$v.')'); + } + } else { + return $self->_set_error( 257, "Invalid response, please contact the author of this module. " . $res ); + } + } else { + if ($res=~m/ *(.*?) *<\/faultcode> * *(.*?) *<\/faultstring>/) { + my $faultcode = $1; + my $faultstring = $2; + if ($faultcode eq 'soap:Server' && $faultstring eq 'TIMEOUT') { + return $self->_set_error(17, "The VIES server timed out. Please re-submit your request later.") + } elsif ($faultcode eq 'soap:Server' && $faultstring eq 'MS_UNAVAILABLE') { + return $self->_set_error(18, "Member State service unavailable. Please re-submit your request later.") + } elsif ($faultstring=~m/^Couldn't parse stream/) { + return $self->_set_error( 21, "The VIES database failed to parse a stream. Please re-submit your request later." ); + } else { + return $self->_set_error( $code, $1.' '.$2 ) + } + } elsif ($res=~m/^Can't connect to/) { + return $self->_set_error( 20, "Connexion to the VIES database failed. " . $res ); + } else { + return $self->_set_error( 257, "Invalid response [".$code."], please contact the author of this module. " . $res ); + } + } +} + +sub _set_error { + my ( $self, $code, $txt ) = @_; + $self->{error_code} = $code; + $self->{error} = $txt; + undef; +} + +=back + +=head1 SEE ALSO + +LWP::UserAgent + +I for the FAQs related to the VIES service. + + +=head1 FEEDBACK + +If you find this module useful, or have any comments, suggestions or improvements, feel free to let me know. + + +=head1 AUTHOR + +Original author: Bernard Nauwelaerts + +Maintainership since 2015: David Precious (BIGPRESH) + + +=head1 CREDITS + +Many thanks to the following people, actively involved in the development of this software by submitting patches, bug reports, new members regexps, VIES interface changes,... (sorted by last intervention) : + +=over 4 + +=item * +Gregor Herrmann, Debian. + +=item * +Graham Knop. + +=item * +Bart Heupers, Netherlands. + +=item * +Martin H. Sluka, noris network AG, Germany. + +=item * +Simon Williams, UK2 Limited, United Kingdom & Benoît Galy, Greenacres, France & Raluca Boboia, Evozon, Romania + +=item * +Dave O., POBox, U.S.A. + +=item * +Kaloyan Iliev, Digital Systems, Bulgaria. + +=item * +Tom Kirkpatrick, Virus Bulletin, United Kingdom. + +=item * +Andy Wardley, individual, United Kingdom. + +=item * +Robert Alloway, Service Centre, United Kingdom. + +=item * +Torsten Mueller, Archesoft, Germany + +=back + +=head1 LICENSE + +GPL. Enjoy! See COPYING for further information on the GPL. + + +=head1 DISCLAIMER + +See I to known the limitations of the EU validation service. + + This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; + without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. + +=cut + +1; diff --git a/dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/MYMETA.json b/dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/MYMETA.json new file mode 100644 index 00000000..7b58ac83 --- /dev/null +++ b/dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/MYMETA.json @@ -0,0 +1,52 @@ +{ + "abstract" : "Validate EU VAT numbers against VIES", + "author" : [ + "David Precious " + ], + "dynamic_config" : 0, + "generated_by" : "ExtUtils::MakeMaker version 7.18, CPAN::Meta::Converter version 2.143240, CPAN::Meta::Converter version 2.150005", + "license" : [ + "gpl_3" + ], + "meta-spec" : { + "url" : "http://search.cpan.org/perldoc?CPAN::Meta::Spec", + "version" : 2 + }, + "name" : "Business-Tax-VAT-Validation", + "no_index" : { + "directory" : [ + "t", + "inc" + ] + }, + "prereqs" : { + "build" : { + "requires" : { + "ExtUtils::MakeMaker" : "0" + } + }, + "configure" : { + "requires" : { + "ExtUtils::MakeMaker" : "0" + } + }, + "runtime" : { + "requires" : { + "HTTP::Request::Common" : "1", + "LWP::UserAgent" : "1" + } + } + }, + "release_status" : "stable", + "resources" : { + "bugtracker" : { + "web" : "https://github.com/bigpresh/Business-Tax-VAT-Validation/issues" + }, + "homepage" : "https://github.com/bigpresh/Business-Tax-VAT-Validation/", + "repository" : { + "url" : "https://github.com/bigpresh/Business-Tax-VAT-Validation" + } + }, + "version" : "1.11", + "x_serialization_backend" : "JSON::PP version 4.02" +} diff --git a/dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/install.json b/dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/install.json new file mode 100644 index 00000000..63f40622 --- /dev/null +++ b/dev/db/l/lib/perl5/darwin-2level/.meta/Business-Tax-VAT-Validation-1.11/install.json @@ -0,0 +1 @@ +{"pathname":"B/BI/BIGPRESH/Business-Tax-VAT-Validation-1.11.tar.gz","dist":"Business-Tax-VAT-Validation-1.11","provides":{"Business::Tax::VAT::Validation":{"file":"lib/Business/Tax/VAT/Validation.pm","version":"1.11"}},"target":"Business::Tax::VAT::Validation","name":"Business::Tax::VAT::Validation","version":"1.11"} \ No newline at end of file diff --git a/dev/db/l/lib/perl5/darwin-2level/auto/Business/Tax/VAT/Validation/.packlist b/dev/db/l/lib/perl5/darwin-2level/auto/Business/Tax/VAT/Validation/.packlist new file mode 100644 index 00000000..7f0d2df6 --- /dev/null +++ b/dev/db/l/lib/perl5/darwin-2level/auto/Business/Tax/VAT/Validation/.packlist @@ -0,0 +1,2 @@ +/Users/kilian/Workspace/Websites/pot_lu/dev/db/l/lib/perl5/Business/Tax/VAT/Validation.pm +/Users/kilian/Workspace/Websites/pot_lu/dev/db/l/man/man3/Business::Tax::VAT::Validation.3 diff --git a/dev/db/l/lib/perl5/darwin-2level/perllocal.pod b/dev/db/l/lib/perl5/darwin-2level/perllocal.pod new file mode 100644 index 00000000..49c04a37 --- /dev/null +++ b/dev/db/l/lib/perl5/darwin-2level/perllocal.pod @@ -0,0 +1,22 @@ +=head2 Sun Dec 15 11:53:47 2019: C L + +=over 4 + +=item * + +C + +=item * + +C + +=item * + +C + +=item * + +C + +=back + diff --git a/dev/db/l/man/man3/Business::Tax::VAT::Validation.3 b/dev/db/l/man/man3/Business::Tax::VAT::Validation.3 new file mode 100644 index 00000000..0fca637e --- /dev/null +++ b/dev/db/l/man/man3/Business::Tax::VAT::Validation.3 @@ -0,0 +1,261 @@ +.\" Automatically generated by Pod::Man 4.07 (Pod::Simple 3.32) +.\" +.\" Standard preamble: +.\" ======================================================================== +.de Sp \" Vertical space (when we can't use .PP) +.if t .sp .5v +.if n .sp +.. +.de Vb \" Begin verbatim text +.ft CW +.nf +.ne \\$1 +.. +.de Ve \" End verbatim text +.ft R +.fi +.. +.\" Set up some character translations and predefined strings. \*(-- will +.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left +.\" double quote, and \*(R" will give a right double quote. \*(C+ will +.\" give a nicer C++. Capital omega is used to do unbreakable dashes and +.\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, +.\" nothing in troff, for use with C<>. +.tr \(*W- +.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' +.ie n \{\ +. ds -- \(*W- +. ds PI pi +. if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch +. if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch +. ds L" "" +. ds R" "" +. ds C` "" +. ds C' "" +'br\} +.el\{\ +. ds -- \|\(em\| +. ds PI \(*p +. ds L" `` +. ds R" '' +. ds C` +. ds C' +'br\} +.\" +.\" Escape single quotes in literal strings from groff's Unicode transform. +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" +.\" If the F register is >0, we'll generate index entries on stderr for +.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index +.\" entries marked with X<> in POD. Of course, you'll have to process the +.\" output yourself in some meaningful fashion. +.\" +.\" Avoid warning from groff about undefined register 'F'. +.de IX +.. +.if !\nF .nr F 0 +.if \nF>0 \{\ +. de IX +. tm Index:\\$1\t\\n%\t"\\$2" +.. +. if !\nF==2 \{\ +. nr % 0 +. nr F 2 +. \} +.\} +.\" ======================================================================== +.\" +.IX Title "Business::Tax::VAT::Validation 3" +.TH Business::Tax::VAT::Validation 3 "2017-03-13" "perl v5.24.1" "User Contributed Perl Documentation" +.\" For nroff, turn off justification. Always turn off hyphenation; it makes +.\" way too many mistakes in technical documents. +.if n .ad l +.nh +.SH "NAME" +Business::Tax::VAT::Validation \- Validate EU VAT numbers against VIES +.SH "SYNOPSIS" +.IX Header "SYNOPSIS" +.Vb 1 +\& use Business::Tax::VAT::Validation; +\& +\& my $hvatn=Business::Tax::VAT::Validation\->new(); +\& +\& # Check number +\& if ($hvatn\->check($VAT, [$member_state])){ +\& print "OK\en"; +\& } else { +\& print $hvatn\->get_last_error; +\& } +.Ve +.SH "DESCRIPTION" +.IX Header "DESCRIPTION" +This class provides an easy \s-1API\s0 to check European \s-1VAT\s0 numbers' syntax, +and if they has been registered by the competent authorities. +.PP +It asks the \s-1EU\s0 database (\s-1VIES\s0) for this, using its \s-1SOAP API. \s0 Basic checks that +the supplied \s-1VAT\s0 number fit the expected format for the specified \s-1EU\s0 member +state are performed first, to avoid unnecessarily sending queries to \s-1VIES\s0 for +input that could never be valid. +.SH "CONSTRUCTOR" +.IX Header "CONSTRUCTOR" +.IP "\fBnew\fR Class constructor." 4 +.IX Item "new Class constructor." +.Vb 1 +\& $hvatn=Business::Tax::VAT::Validation\->new(); +\& +\& +\& If your system is located behind a proxy : +\& +\& $hvatn=Business::Tax::VAT::Validation\->new(\-proxy => [\*(Aqhttp\*(Aq, \*(Aqhttp://example.com:8001/\*(Aq]); +\& +\& Note : See LWP::UserAgent for proxy options. +.Ve +.SH "PROPERTIES" +.IX Header "PROPERTIES" +.IP "\fBmember_states\fR Returns all member states 2\-digit codes as array" 4 +.IX Item "member_states Returns all member states 2-digit codes as array" +.Vb 1 +\& @ms=$hvatn\->member_states; +.Ve +.IP "\fBregular_expressions\fR \- Returns a hash list containing one regular expression for each country" 4 +.IX Item "regular_expressions - Returns a hash list containing one regular expression for each country" +If you want to test a \s-1VAT\s0 number format ouside this module, e.g. embedded as javascript in a web form. +.Sp +.Vb 1 +\& %re=$hvatn\->regular_expressions; +.Ve +.Sp +returns +.Sp +.Vb 5 +\& ( +\& AT => \*(AqU[0\-9]{8}\*(Aq, +\& ... +\& SK => \*(Aq[0\-9]{10}\*(Aq, +\& ); +.Ve +.SH "METHODS" +.IX Header "METHODS" +.IP "\fBcheck\fR \- Checks if a \s-1VAT\s0 number exists in the \s-1VIES\s0 database" 4 +.IX Item "check - Checks if a VAT number exists in the VIES database" +.Vb 1 +\& $ok=$hvatn\->check($vatNumber, [$countryCode]); +.Ve +.Sp +You may either provide the \s-1VAT\s0 number under its complete form (e.g. \s-1BE\-123456789, BE123456789\s0) +or specify the \s-1VAT\s0 and \s-1MSC \s0(vatNumber and countryCode) individually. +.Sp +Valid \s-1MS\s0 values are : +.Sp +.Vb 3 +\& AT, BE, BG, CY, CZ, DE, DK, EE, EL, ES, +\& FI, FR, GB, HU, IE, IT, LU, LT, LV, MT, +\& NL, PL, PT, RO, SE, SI, SK +.Ve +.IP "\fBlocal_check\fR \- Checks if a \s-1VAT\s0 number format is valid This method is based on regexps only and \s-1DOES NOT\s0 ask the \s-1VIES\s0 database" 4 +.IX Item "local_check - Checks if a VAT number format is valid This method is based on regexps only and DOES NOT ask the VIES database" +.Vb 1 +\& $ok=$hvatn\->local_check($VAT, [$member_state]); +.Ve +.IP "\fBinformations\fR \- Returns informations related to the last validated \s-1VAT\s0 number" 4 +.IX Item "informations - Returns informations related to the last validated VAT number" +.Vb 1 +\& %infos=$hvatn\->informations(); +.Ve +.IP "\fBget_last_error_code\fR \- Returns the last recorded error code" 4 +.IX Item "get_last_error_code - Returns the last recorded error code" +.PD 0 +.IP "\fBget_last_error\fR \- Returns the last recorded error" 4 +.IX Item "get_last_error - Returns the last recorded error" +.PD +.Vb 2 +\& my $err = $hvatn\->get_last_error_code(); +\& my $txt = $hvatn\->get_last_error(); +.Ve +.Sp +Possible errors are : +.RS 4 +.IP "\(bu" 4 +\&\-1 The provided \s-1VAT\s0 number is valid. +.IP "\(bu" 4 +0 Unknown \s-1MS\s0 code : Internal checkup failed (Specified Member State does not exist) +.IP "\(bu" 4 +1 Invalid \s-1VAT\s0 number format : Internal checkup failed (bad syntax) +.IP "\(bu" 4 +2 This \s-1VAT\s0 number doesn't exist in \s-1EU\s0 database : distant checkup +.IP "\(bu" 4 +3 This \s-1VAT\s0 number contains errors : distant checkup +.IP "\(bu" 4 +17 Time out connecting to the database : Temporary error when the connection to the database times out +.IP "\(bu" 4 +18 Member Sevice Unavailable: The \s-1EU\s0 database is unable to reach the requested member's database. +.IP "\(bu" 4 +19 The \s-1EU\s0 database is too busy. +.IP "\(bu" 4 +20 Connexion to the \s-1VIES\s0 database failed. +.IP "\(bu" 4 +21 The \s-1VIES\s0 interface failed to parse a stream. This error occurs unpredictabely, so you should retry your validation request. +.IP "\(bu" 4 +257 Invalid response, please contact the author of this module. : This normally only happens if this software doesn't recognize any valid pattern into the response document: this generally means that the database interface has been modified, and you'll make the author happy by submitting the returned response !!! +.IP "\(bu" 4 +500 The \s-1VIES\s0 server encountered an internal server error. +Error 500 : soap:Server \s-1TIMEOUT\s0 +Error 500 : soap:Server \s-1MS_UNAVAILABLE\s0 +.RE +.RS 4 +.Sp +If error_code > 16, you should temporarily accept the provided number, and periodically perform new checks until response is \s-1OK\s0 or error < 17 +If error_code > 256, you should temporarily accept the provided number, contact the author, and perform a new check when the software is updated. +.RE +.IP "\fBget_last_response\fR \- Returns the full last response" 4 +.IX Item "get_last_response - Returns the full last response" +.SH "SEE ALSO" +.IX Header "SEE ALSO" +LWP::UserAgent +.PP +\&\fIhttp://ec.europa.eu/taxation_customs/vies/faqvies.do\fR for the FAQs related to the \s-1VIES\s0 service. +.SH "FEEDBACK" +.IX Header "FEEDBACK" +If you find this module useful, or have any comments, suggestions or improvements, feel free to let me know. +.SH "AUTHOR" +.IX Header "AUTHOR" +Original author: Bernard Nauwelaerts +.PP +Maintainership since 2015: David Precious (\s-1BIGPRESH\s0) +.SH "CREDITS" +.IX Header "CREDITS" +Many thanks to the following people, actively involved in the development of this software by submitting patches, bug reports, new members regexps, \s-1VIES\s0 interface changes,... (sorted by last intervention) : +.IP "\(bu" 4 +Gregor Herrmann, Debian. +.IP "\(bu" 4 +Graham Knop. +.IP "\(bu" 4 +Bart Heupers, Netherlands. +.IP "\(bu" 4 +Martin H. Sluka, noris network \s-1AG,\s0 Germany. +.IP "\(bu" 4 +Simon Williams, \s-1UK2\s0 Limited, United Kingdom & Benoît Galy, Greenacres, France & Raluca Boboia, Evozon, Romania +.IP "\(bu" 4 +Dave O., POBox, U.S.A. +.IP "\(bu" 4 +Kaloyan Iliev, Digital Systems, Bulgaria. +.IP "\(bu" 4 +Tom Kirkpatrick, Virus Bulletin, United Kingdom. +.IP "\(bu" 4 +Andy Wardley, individual, United Kingdom. +.IP "\(bu" 4 +Robert Alloway, Service Centre, United Kingdom. +.IP "\(bu" 4 +Torsten Mueller, Archesoft, Germany +.SH "LICENSE" +.IX Header "LICENSE" +\&\s-1GPL.\s0 Enjoy! See \s-1COPYING\s0 for further information on the \s-1GPL.\s0 +.SH "DISCLAIMER" +.IX Header "DISCLAIMER" +See \fIhttp://ec.europa.eu/taxation_customs/vies/viesdisc.do\fR to known the limitations of the \s-1EU\s0 validation service. +.PP +.Vb 2 +\& This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; +\& without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. +.Ve diff --git a/dev/db/potlu_db.newcompanyschema.pg.schema.sql b/dev/db/potlu_db.newcompanyschema.pg.schema.sql new file mode 100644 index 00000000..f7db8e9a --- /dev/null +++ b/dev/db/potlu_db.newcompanyschema.pg.schema.sql @@ -0,0 +1,1193 @@ + +CREATE SCHEMA %%NEWSCHEMA%%; + +CREATE TABLE %%NEWSCHEMA%%.defaultweekworkplan ( + id integer NOT NULL, + id_staff integer, + startdate date, + mon json, + tue json, + wed json, + thu json, + fri json, + sat json, + sun json +); + +CREATE SEQUENCE %%NEWSCHEMA%%.defaultweekworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.defaultweekworkplan_id_seq OWNED BY %%NEWSCHEMA%%.defaultweekworkplan.id; + +CREATE TABLE %%NEWSCHEMA%%.defaultworkplan ( + id bigint NOT NULL, + id_staff integer, + daydate date, + plannedtimes json, + vacancytimes json, + presencetimes json +); + +CREATE SEQUENCE %%NEWSCHEMA%%.defaultworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.defaultworkplan_id_seq OWNED BY %%NEWSCHEMA%%.defaultworkplan.id; + +CREATE TABLE %%NEWSCHEMA%%.reportperiod ( + id integer NOT NULL, + periodname text, + startdate date, + enddate date +); + +CREATE SEQUENCE %%NEWSCHEMA%%.reportperiod_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.reportperiod_id_seq OWNED BY %%NEWSCHEMA%%.reportperiod.id; + +CREATE TABLE %%NEWSCHEMA%%.sites ( + id integer NOT NULL, + sitename text, + address text, + zip text, + city text, + country text, + id_timetracker integer, + created timestamp without time zone DEFAULT now(), + modified timestamp without time zone DEFAULT now(), + timeclockhost text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.sites_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.sites_id_seq OWNED BY %%NEWSCHEMA%%.sites.id; + +CREATE TABLE %%NEWSCHEMA%%.staff ( + id integer NOT NULL, + staffnumber text, + surname text, + prename text, + job text, + birthdate date, + entrydate date, + leavedate date +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staff_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staff_id_seq OWNED BY %%NEWSCHEMA%%.staff.id; + +CREATE TABLE %%NEWSCHEMA%%.staffgroups ( + id integer NOT NULL, + groupname text, + groupcolor text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffgroups_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffgroups_id_seq OWNED BY %%NEWSCHEMA%%.staffgroups.id; + +CREATE TABLE %%NEWSCHEMA%%.staffperiodbase ( + id integer NOT NULL, + id_staff integer, + startdate date, + monthhours numeric, + weekhours numeric +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffperiodbase_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffperiodbase_id_seq OWNED BY %%NEWSCHEMA%%.staffperiodbase.id; + +CREATE TABLE %%NEWSCHEMA%%.stafftimetracks ( + id bigint NOT NULL, + id_staff integer, + stamp_in timestamp without time zone, + stamp_out timestamp without time zone, + tracktype text, + created timestamp without time zone DEFAULT now(), + modified timestamp without time zone DEFAULT now() +); + +CREATE SEQUENCE %%NEWSCHEMA%%.stafftimetracks_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.stafftimetracks_id_seq OWNED BY %%NEWSCHEMA%%.stafftimetracks.id; + +CREATE TABLE %%NEWSCHEMA%%.staffvacancy ( + id integer NOT NULL, + id_staff integer, + startdate date, + enddate date, + vacancytype text, + dayhours time without time zone, + note text, + validated boolean +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffvacancy_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffvacancy_id_seq OWNED BY %%NEWSCHEMA%%.staffvacancy.id; + +CREATE TABLE %%NEWSCHEMA%%.staffvacancyyear ( + id integer NOT NULL, + id_staff integer, + vyear integer, + hours numeric, + days numeric +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffvacancyyear_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffvacancyyear_id_seq OWNED BY %%NEWSCHEMA%%.staffvacancyyear.id; + +CREATE TABLE %%NEWSCHEMA%%.staffworkplan ( + id bigint NOT NULL, + id_staff integer, + daydate date, + timestart1 time without time zone, + timeend1 time without time zone, + timestart2 time without time zone, + timeend2 time without time zone, + timepause time without time zone, + vacancyhours time without time zone, + vacancytype text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.staffworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.staffworkplan_id_seq OWNED BY %%NEWSCHEMA%%.staffworkplan.id; + +CREATE VIEW %%NEWSCHEMA%%.vw_reportperiodlist AS + SELECT reportperiod.id, + reportperiod.periodname, + reportperiod.startdate, + reportperiod.enddate + FROM %%NEWSCHEMA%%.reportperiod; + +CREATE VIEW %%NEWSCHEMA%%.vw_stafflist AS + SELECT staff.id, + staff.staffnumber, + staff.surname, + staff.prename, + staff.job, + staff.birthdate, + staff.entrydate, + staff.leavedate, + ((staff.surname || ' '::text) || staff.prename) AS dspname + FROM %%NEWSCHEMA%%.staff; + +CREATE VIEW %%NEWSCHEMA%%.vw_staffplanned_dayweektotals AS + SELECT stw2.calweek, + stw2.caldate AS dates, + stw2.id_staff, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS mon_id, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS mon_timetotal, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS tue_id, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS tue_timetotal, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS wed_id, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS wed_timetotal, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS thu_id, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS thu_timetotal, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS fri_id, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS fri_timetotal, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sat_id, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sat_timetotal, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sun_id, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sun_timetotal, + to_char(sum(((stw2.time1 + stw2.time2) - (stw2.timepause)::interval)), 'HH24:MI'::text) AS week_timetotal + FROM ( SELECT staffworkplan.daydate, + date_part('week'::text, staffworkplan.daydate) AS calweek, + (date_trunc('week'::text, (staffworkplan.daydate)::timestamp with time zone))::date AS caldate, + date_part('isodow'::text, staffworkplan.daydate) AS isodow, + staffworkplan.id, + staffworkplan.id_staff, + staffworkplan.timestart1, + staffworkplan.timeend1, + CASE + WHEN (staffworkplan.timestart1 > staffworkplan.timeend1) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart1 - staffworkplan.timeend1))::time without time zone) + ELSE (staffworkplan.timeend1 - staffworkplan.timestart1) + END AS time1, + staffworkplan.timestart2, + staffworkplan.timeend2, + CASE + WHEN (staffworkplan.timestart2 > staffworkplan.timeend2) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart2 - staffworkplan.timeend2))::time without time zone) + ELSE (staffworkplan.timeend2 - staffworkplan.timestart2) + END AS time2, + staffworkplan.timepause + FROM %%NEWSCHEMA%%.staffworkplan) stw2 + GROUP BY stw2.calweek, stw2.caldate, stw2.id_staff; + +CREATE VIEW %%NEWSCHEMA%%.vw_staffworkplan_weekly AS + SELECT stw2.calweek, + to_char((stw2.caldate)::timestamp with time zone, 'YYYY'::text) AS calyear, + stw2.caldate AS weekbegin, + public.getdateslist(stw2.caldate, 7) AS dates, + stw2.id_staff, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS mon_id, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_pause, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS mon_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS mon_timetotal, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS tue_id, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_pause, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS tue_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS tue_timetotal, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS wed_id, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_pause, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS wed_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS wed_timetotal, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS thu_id, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_pause, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS thu_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS thu_timetotal, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS fri_id, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_pause, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS fri_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS fri_timetotal, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sat_id, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_pause, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS sat_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sat_timetotal, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sun_id, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_pause, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS sun_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sun_timetotal, + to_char(sum(((stw2.time1 + stw2.time2) - (stw2.timepause)::interval)), 'HH24:MI'::text) AS week_timetotal + FROM ( SELECT staffworkplan.daydate, + date_part('week'::text, staffworkplan.daydate) AS calweek, + (date_trunc('week'::text, (staffworkplan.daydate)::timestamp with time zone))::date AS caldate, + date_part('isodow'::text, staffworkplan.daydate) AS isodow, + staffworkplan.id, + staffworkplan.id_staff, + staffworkplan.timestart1, + staffworkplan.timeend1, + staffworkplan.vacancyhours, + staffworkplan.vacancytype, + CASE + WHEN (staffworkplan.timestart1 > staffworkplan.timeend1) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart1 - staffworkplan.timeend1))::time without time zone) + ELSE (staffworkplan.timeend1 - staffworkplan.timestart1) + END AS time1, + staffworkplan.timestart2, + staffworkplan.timeend2, + CASE + WHEN (staffworkplan.timestart2 > staffworkplan.timeend2) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart2 - staffworkplan.timeend2))::time without time zone) + ELSE (staffworkplan.timeend2 - staffworkplan.timestart2) + END AS time2, + staffworkplan.timepause + FROM %%NEWSCHEMA%%.staffworkplan) stw2 + GROUP BY stw2.calweek, stw2.caldate, stw2.id_staff; + +CREATE VIEW %%NEWSCHEMA%%.vw_staffworkplanlist AS + SELECT st.id AS id_staff, + ((st.surname || ' '::text) || st.prename) AS staffname, + (((((('Semaine '::text || sp_dwt.calweek) || '
('::text) || to_char((sp_dwt.weekbegin)::timestamp with time zone, 'DD.MM.YYYY'::text)) || ' - '::text) || to_char((date((sp_dwt.weekbegin + '7 days'::interval)))::timestamp with time zone, 'DD.MM.YYYY'::text)) || ')'::text) AS calweek, + sp_dwt.week_timetotal, + sp_dwt.weekbegin AS weekstart, + date((sp_dwt.weekbegin + '7 days'::interval)) AS weekend, + to_char((((((( + CASE + WHEN (sp_dwt.mon_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.mon_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END + + CASE + WHEN (sp_dwt.tue_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.tue_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.wed_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.wed_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.thu_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.thu_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.fri_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.fri_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sat_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.sat_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sun_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.sun_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END), 'HH24:MI'::text) AS weekvacancy_times_ill, + to_char((((((( + CASE + WHEN (sp_dwt.mon_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.mon_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END + + CASE + WHEN (sp_dwt.tue_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.tue_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.wed_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.wed_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.thu_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.thu_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.fri_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.fri_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sat_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.sat_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sun_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.sun_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END), 'HH24:MI'::text) AS weekvacancy_times, + (((((( + CASE + WHEN (sp_dwt.mon_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.mon_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.mon_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END + + CASE + WHEN (sp_dwt.tue_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.tue_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.tue_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.wed_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.wed_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.wed_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.thu_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.thu_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.thu_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.fri_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.fri_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.fri_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sat_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sat_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sat_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sun_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sun_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sun_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) AS weekvacancy_minutes_ill, + (((((( + CASE + WHEN (sp_dwt.mon_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.mon_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.mon_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END + + CASE + WHEN (sp_dwt.tue_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.tue_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.tue_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.wed_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.wed_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.wed_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.thu_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.thu_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.thu_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.fri_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.fri_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.fri_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sat_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sat_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sat_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sun_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sun_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sun_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) AS weekvacancy_minutes, + ((to_number("left"(sp_dwt.week_timetotal, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.week_timetotal, 2), '99'::text)) AS weekminutes, + sp_dwt.mon_id, + sp_dwt.weekbegin AS mon_date, + ((COALESCE(((to_char((sp_dwt.mon_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.mon_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.mon_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.mon_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.mon_vacancytype) || ': '::text) || to_char((sp_dwt.mon_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspmontimes, + sp_dwt.mon_timetotal, + sp_dwt.tue_id, + date((sp_dwt.weekbegin + '1 day'::interval)) AS tue_date, + ((COALESCE(((to_char((sp_dwt.tue_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.tue_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.tue_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.tue_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.tue_vacancytype) || ': '::text) || to_char((sp_dwt.tue_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dsptuetimes, + sp_dwt.tue_timetotal, + sp_dwt.wed_id, + date((sp_dwt.weekbegin + '2 days'::interval)) AS wed_date, + ((COALESCE(((to_char((sp_dwt.wed_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.wed_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.wed_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.wed_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.wed_vacancytype) || ': '::text) || to_char((sp_dwt.wed_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspwedtimes, + sp_dwt.wed_timetotal, + sp_dwt.thu_id, + date((sp_dwt.weekbegin + '3 days'::interval)) AS thu_date, + ((COALESCE(((to_char((sp_dwt.thu_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.thu_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.thu_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.thu_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.thu_vacancytype) || ': '::text) || to_char((sp_dwt.thu_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspthutimes, + sp_dwt.thu_timetotal, + sp_dwt.fri_id, + date((sp_dwt.weekbegin + '4 days'::interval)) AS fri_date, + ((COALESCE(((to_char((sp_dwt.fri_start1)::interval, 'HH24:MI'::text) || ' -'::text) || to_char((sp_dwt.fri_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.fri_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.fri_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.fri_vacancytype) || ': '::text) || to_char((sp_dwt.fri_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspfritimes, + sp_dwt.fri_timetotal, + sp_dwt.sat_id, + date((sp_dwt.weekbegin + '5 days'::interval)) AS sat_date, + ((COALESCE(((to_char((sp_dwt.sat_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.sat_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.sat_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.sat_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.sat_vacancytype) || ': '::text) || to_char((sp_dwt.sat_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspsattimes, + sp_dwt.sat_timetotal, + sp_dwt.sun_id, + date((sp_dwt.weekbegin + '6 days'::interval)) AS sun_date, + ((COALESCE(((to_char((sp_dwt.sun_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.sun_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.sun_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.sun_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.sun_vacancytype) || ': '::text) || to_char((sp_dwt.sun_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspsuntimes, + sp_dwt.sun_timetotal + FROM (%%NEWSCHEMA%%.vw_staffworkplan_weekly sp_dwt + LEFT JOIN %%NEWSCHEMA%%.staff st ON ((sp_dwt.id_staff = st.id))) + ORDER BY sp_dwt.calweek; + +CREATE TABLE %%NEWSCHEMA%%.workplans ( + id integer NOT NULL, + workplan text, + mon_timestart1 time without time zone, + mon_timeend1 time without time zone, + mon_timestart2 time without time zone, + mon_timeend2 time without time zone, + mon_timepause time without time zone, + tue_timestart1 time without time zone, + tue_timeend1 time without time zone, + tue_timestart2 time without time zone, + tue_timeend2 time without time zone, + tue_timepause time without time zone, + wed_timestart1 time without time zone, + wed_timeend1 time without time zone, + wed_timestart2 time without time zone, + wed_timeend2 time without time zone, + wed_timepause time without time zone, + thu_timestart1 time without time zone, + thu_timeend1 time without time zone, + thu_timestart2 time without time zone, + thu_timeend2 time without time zone, + thu_timepause time without time zone, + fri_timestart1 time without time zone, + fri_timeend1 time without time zone, + fri_timestart2 time without time zone, + fri_timeend2 time without time zone, + fri_timepause time without time zone, + sat_timestart1 time without time zone, + sat_timeend1 time without time zone, + sat_timestart2 time without time zone, + sat_timeend2 time without time zone, + sat_timepause time without time zone, + sun_timestart1 time without time zone, + sun_timeend1 time without time zone, + sun_timestart2 time without time zone, + sun_timeend2 time without time zone, + sun_timepause time without time zone +); + +CREATE VIEW %%NEWSCHEMA%%.vw_workplanlist AS + SELECT workplans.id, + workplans.workplan + FROM %%NEWSCHEMA%%.workplans; + +CREATE VIEW %%NEWSCHEMA%%.vw_workplans AS + SELECT workplans.id, + workplans.workplan, + ((COALESCE(((to_char((workplans.mon_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.mon_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.mon_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.mon_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.mon_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspmontimes, + ((COALESCE(((to_char((workplans.tue_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.tue_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.tue_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.tue_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.tue_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dsptuetimes, + ((COALESCE(((to_char((workplans.wed_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.wed_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.wed_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.wed_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.wed_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspwedtimes, + ((COALESCE(((to_char((workplans.thu_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.thu_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.thu_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.thu_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.thu_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspthutimes, + ((COALESCE(((to_char((workplans.fri_timestart1)::interval, 'HH24:MI'::text) || ' -'::text) || to_char((workplans.fri_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.fri_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.fri_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.fri_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspfritimes, + ((COALESCE(((to_char((workplans.sat_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.sat_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.sat_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.sat_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.sat_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspsattimes, + ((COALESCE(((to_char((workplans.sun_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.sun_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.sun_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.sun_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.sun_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspsuntimes + FROM %%NEWSCHEMA%%.workplans; + +CREATE VIEW %%NEWSCHEMA%%.vw_workplansdata AS + SELECT workplans.id, + workplans.workplan, + to_char((workplans.mon_timestart1)::interval, 'HH24:MI'::text) AS mon_timestart1, + to_char((workplans.mon_timeend1)::interval, 'HH24:MI'::text) AS mon_timeend1, + to_char((workplans.mon_timestart2)::interval, 'HH24:MI'::text) AS mon_timestart2, + to_char((workplans.mon_timeend2)::interval, 'HH24:MI'::text) AS mon_timeend2, + to_char((workplans.mon_timepause)::interval, 'HH24:MI'::text) AS mon_timepause, + to_char((workplans.tue_timestart1)::interval, 'HH24:MI'::text) AS tue_timestart1, + to_char((workplans.tue_timeend1)::interval, 'HH24:MI'::text) AS tue_timeend1, + to_char((workplans.tue_timestart2)::interval, 'HH24:MI'::text) AS tue_timestart2, + to_char((workplans.tue_timeend2)::interval, 'HH24:MI'::text) AS tue_timeend2, + to_char((workplans.tue_timepause)::interval, 'HH24:MI'::text) AS tue_timepause, + to_char((workplans.wed_timestart1)::interval, 'HH24:MI'::text) AS wed_timestart1, + to_char((workplans.wed_timeend1)::interval, 'HH24:MI'::text) AS wed_timeend1, + to_char((workplans.wed_timestart2)::interval, 'HH24:MI'::text) AS wed_timestart2, + to_char((workplans.wed_timeend2)::interval, 'HH24:MI'::text) AS wed_timeend2, + to_char((workplans.wed_timepause)::interval, 'HH24:MI'::text) AS wed_timepause, + to_char((workplans.thu_timestart1)::interval, 'HH24:MI'::text) AS thu_timestart1, + to_char((workplans.thu_timeend1)::interval, 'HH24:MI'::text) AS thu_timeend1, + to_char((workplans.thu_timestart2)::interval, 'HH24:MI'::text) AS thu_timestart2, + to_char((workplans.thu_timeend2)::interval, 'HH24:MI'::text) AS thu_timeend2, + to_char((workplans.thu_timepause)::interval, 'HH24:MI'::text) AS thu_timepause, + to_char((workplans.fri_timestart1)::interval, 'HH24:MI'::text) AS fri_timestart1, + to_char((workplans.fri_timeend1)::interval, 'HH24:MI'::text) AS fri_timeend1, + to_char((workplans.fri_timestart2)::interval, 'HH24:MI'::text) AS fri_timestart2, + to_char((workplans.fri_timeend2)::interval, 'HH24:MI'::text) AS fri_timeend2, + to_char((workplans.fri_timepause)::interval, 'HH24:MI'::text) AS fri_timepause, + to_char((workplans.sat_timestart1)::interval, 'HH24:MI'::text) AS sat_timestart1, + to_char((workplans.sat_timeend1)::interval, 'HH24:MI'::text) AS sat_timeend1, + to_char((workplans.sat_timestart2)::interval, 'HH24:MI'::text) AS sat_timestart2, + to_char((workplans.sat_timeend2)::interval, 'HH24:MI'::text) AS sat_timeend2, + to_char((workplans.sat_timepause)::interval, 'HH24:MI'::text) AS sat_timepause, + to_char((workplans.sun_timestart1)::interval, 'HH24:MI'::text) AS sun_timestart1, + to_char((workplans.sun_timeend1)::interval, 'HH24:MI'::text) AS sun_timeend1, + to_char((workplans.sun_timestart2)::interval, 'HH24:MI'::text) AS sun_timestart2, + to_char((workplans.sun_timeend2)::interval, 'HH24:MI'::text) AS sun_timeend2, + to_char((workplans.sun_timepause)::interval, 'HH24:MI'::text) AS sun_timepause + FROM %%NEWSCHEMA%%.workplans; + +CREATE SEQUENCE %%NEWSCHEMA%%.workplans_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.workplans_id_seq OWNED BY %%NEWSCHEMA%%.workplans.id; + +CREATE TABLE %%NEWSCHEMA%%.worktypes ( + id integer NOT NULL, + worktype text, + isworktime boolean, + isfreetime boolean, + typecolor text +); + +CREATE SEQUENCE %%NEWSCHEMA%%.worktypes_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE %%NEWSCHEMA%%.worktypes_id_seq OWNED BY %%NEWSCHEMA%%.worktypes.id; + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultweekworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.defaultweekworkplan_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.defaultworkplan_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.reportperiod ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.reportperiod_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.sites ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.sites_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staff ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staff_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffgroups ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffgroups_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffperiodbase ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffperiodbase_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.stafftimetracks ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.stafftimetracks_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancy ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffvacancy_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancyyear ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffvacancyyear_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffworkplan_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.workplans ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.workplans_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.worktypes ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.worktypes_id_seq'::regclass); + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultweekworkplan + ADD CONSTRAINT defaultweekworkplan_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultworkplan + ADD CONSTRAINT defaultworkplan_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.reportperiod + ADD CONSTRAINT reportperiod_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.sites + ADD CONSTRAINT sites_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staff + ADD CONSTRAINT staff_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffgroups + ADD CONSTRAINT staffgroups_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffperiodbase + ADD CONSTRAINT staffperiodbase_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.stafftimetracks + ADD CONSTRAINT stafftimetracks_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancy + ADD CONSTRAINT staffvacancy_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancyyear + ADD CONSTRAINT staffvacancyyear_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffworkplan + ADD CONSTRAINT staffworkplan_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.workplans + ADD CONSTRAINT workplans_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY %%NEWSCHEMA%%.worktypes + ADD CONSTRAINT worktypes_pkey PRIMARY KEY (id); + +CREATE TRIGGER trg_upd_%%NEWSCHEMA%%_weekhours BEFORE UPDATE OF weekhours ON %%NEWSCHEMA%%.staffperiodbase FOR EACH ROW EXECUTE PROCEDURE public.trg_update_monthhours(); diff --git a/dev/db/potlu_db.newcompanyschema.pg.schema.sql-e b/dev/db/potlu_db.newcompanyschema.pg.schema.sql-e new file mode 100644 index 00000000..28b37cf1 --- /dev/null +++ b/dev/db/potlu_db.newcompanyschema.pg.schema.sql-e @@ -0,0 +1,1349 @@ + + + + +CREATE SCHEMA %%NEWSCHEMA%%; + + + + + +CREATE TABLE %%NEWSCHEMA%%.defaultweekworkplan ( + id integer NOT NULL, + id_staff integer, + startdate date, + mon json, + tue json, + wed json, + thu json, + fri json, + sat json, + sun json +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.defaultweekworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.defaultweekworkplan_id_seq OWNED BY %%NEWSCHEMA%%.defaultweekworkplan.id; + + + +CREATE TABLE %%NEWSCHEMA%%.defaultworkplan ( + id bigint NOT NULL, + id_staff integer, + daydate date, + plannedtimes json, + vacancytimes json, + presencetimes json +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.defaultworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.defaultworkplan_id_seq OWNED BY %%NEWSCHEMA%%.defaultworkplan.id; + + + +CREATE TABLE %%NEWSCHEMA%%.reportperiod ( + id integer NOT NULL, + periodname text, + startdate date, + enddate date +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.reportperiod_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.reportperiod_id_seq OWNED BY %%NEWSCHEMA%%.reportperiod.id; + + + +CREATE TABLE %%NEWSCHEMA%%.sites ( + id integer NOT NULL, + sitename text, + address text, + zip text, + city text, + country text, + id_timetracker integer, + created timestamp without time zone DEFAULT now(), + modified timestamp without time zone DEFAULT now(), + timeclockhost text +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.sites_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.sites_id_seq OWNED BY %%NEWSCHEMA%%.sites.id; + + + +CREATE TABLE %%NEWSCHEMA%%.staff ( + id integer NOT NULL, + staffnumber text, + surname text, + prename text, + job text, + birthdate date, + entrydate date, + leavedate date +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.staff_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.staff_id_seq OWNED BY %%NEWSCHEMA%%.staff.id; + + + +CREATE TABLE %%NEWSCHEMA%%.staffgroups ( + id integer NOT NULL, + groupname text, + groupcolor text +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.staffgroups_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.staffgroups_id_seq OWNED BY %%NEWSCHEMA%%.staffgroups.id; + + + +CREATE TABLE %%NEWSCHEMA%%.staffperiodbase ( + id integer NOT NULL, + id_staff integer, + startdate date, + monthhours numeric, + weekhours numeric +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.staffperiodbase_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.staffperiodbase_id_seq OWNED BY %%NEWSCHEMA%%.staffperiodbase.id; + + + +CREATE TABLE %%NEWSCHEMA%%.stafftimetracks ( + id bigint NOT NULL, + id_staff integer, + stamp_in timestamp without time zone, + stamp_out timestamp without time zone, + tracktype text, + created timestamp without time zone DEFAULT now(), + modified timestamp without time zone DEFAULT now() +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.stafftimetracks_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.stafftimetracks_id_seq OWNED BY %%NEWSCHEMA%%.stafftimetracks.id; + + + +CREATE TABLE %%NEWSCHEMA%%.staffvacancy ( + id integer NOT NULL, + id_staff integer, + startdate date, + enddate date, + vacancytype text, + dayhours time without time zone, + note text, + validated boolean +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.staffvacancy_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.staffvacancy_id_seq OWNED BY %%NEWSCHEMA%%.staffvacancy.id; + + + +CREATE TABLE %%NEWSCHEMA%%.staffvacancyyear ( + id integer NOT NULL, + id_staff integer, + vyear integer, + hours numeric, + days numeric +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.staffvacancyyear_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.staffvacancyyear_id_seq OWNED BY %%NEWSCHEMA%%.staffvacancyyear.id; + + + +CREATE TABLE %%NEWSCHEMA%%.staffworkplan ( + id bigint NOT NULL, + id_staff integer, + daydate date, + timestart1 time without time zone, + timeend1 time without time zone, + timestart2 time without time zone, + timeend2 time without time zone, + timepause time without time zone, + vacancyhours time without time zone, + vacancytype text +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.staffworkplan_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.staffworkplan_id_seq OWNED BY %%NEWSCHEMA%%.staffworkplan.id; + + + +CREATE VIEW %%NEWSCHEMA%%.vw_reportperiodlist AS + SELECT reportperiod.id, + reportperiod.periodname, + reportperiod.startdate, + reportperiod.enddate + FROM %%NEWSCHEMA%%.reportperiod; + + + +CREATE VIEW %%NEWSCHEMA%%.vw_stafflist AS + SELECT staff.id, + staff.staffnumber, + staff.surname, + staff.prename, + staff.job, + staff.birthdate, + staff.entrydate, + staff.leavedate, + ((staff.surname || ' '::text) || staff.prename) AS dspname + FROM %%NEWSCHEMA%%.staff; + + + +CREATE VIEW %%NEWSCHEMA%%.vw_staffplanned_dayweektotals AS + SELECT stw2.calweek, + stw2.caldate AS dates, + stw2.id_staff, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS mon_id, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS mon_timetotal, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS tue_id, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS tue_timetotal, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS wed_id, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS wed_timetotal, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS thu_id, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS thu_timetotal, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS fri_id, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS fri_timetotal, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sat_id, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sat_timetotal, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sun_id, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_pause, + to_char(max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sun_timetotal, + to_char(sum(((stw2.time1 + stw2.time2) - (stw2.timepause)::interval)), 'HH24:MI'::text) AS week_timetotal + FROM ( SELECT staffworkplan.daydate, + date_part('week'::text, staffworkplan.daydate) AS calweek, + (date_trunc('week'::text, (staffworkplan.daydate)::timestamp with time zone))::date AS caldate, + date_part('isodow'::text, staffworkplan.daydate) AS isodow, + staffworkplan.id, + staffworkplan.id_staff, + staffworkplan.timestart1, + staffworkplan.timeend1, + CASE + WHEN (staffworkplan.timestart1 > staffworkplan.timeend1) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart1 - staffworkplan.timeend1))::time without time zone) + ELSE (staffworkplan.timeend1 - staffworkplan.timestart1) + END AS time1, + staffworkplan.timestart2, + staffworkplan.timeend2, + CASE + WHEN (staffworkplan.timestart2 > staffworkplan.timeend2) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart2 - staffworkplan.timeend2))::time without time zone) + ELSE (staffworkplan.timeend2 - staffworkplan.timestart2) + END AS time2, + staffworkplan.timepause + FROM %%NEWSCHEMA%%.staffworkplan) stw2 + GROUP BY stw2.calweek, stw2.caldate, stw2.id_staff; + + + +CREATE VIEW %%NEWSCHEMA%%.vw_staffworkplan_weekly AS + SELECT stw2.calweek, + to_char((stw2.caldate)::timestamp with time zone, 'YYYY'::text) AS calyear, + stw2.caldate AS weekbegin, + public.getdateslist(stw2.caldate, 7) AS dates, + stw2.id_staff, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS mon_id, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end1, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_start2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_end2, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_pause, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS mon_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS mon_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (1)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS mon_timetotal, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS tue_id, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end1, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_start2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_end2, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_pause, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS tue_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS tue_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (2)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS tue_timetotal, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS wed_id, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end1, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_start2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_end2, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_pause, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS wed_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS wed_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (3)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS wed_timetotal, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS thu_id, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end1, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_start2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_end2, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_pause, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS thu_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS thu_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (4)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS thu_timetotal, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS fri_id, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end1, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_start2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_end2, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_pause, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS fri_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS fri_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (5)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS fri_timetotal, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sat_id, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end1, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_start2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_end2, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_pause, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sat_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS sat_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (6)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sat_timetotal, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.id + ELSE NULL::bigint + END) AS sun_id, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend1)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end1, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timestart2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_start2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timeend2)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_end2, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.timepause)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_pause, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN to_char((stw2.vacancyhours)::interval, 'HH24:MI'::text) + ELSE NULL::text + END) AS sun_vacancyhours, + max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN stw2.vacancytype + ELSE NULL::text + END) AS sun_vacancytype, + to_char(max( + CASE + WHEN (stw2.isodow = (7)::double precision) THEN ((stw2.time1 + stw2.time2) - (stw2.timepause)::interval) + ELSE NULL::interval + END), 'HH24:MI'::text) AS sun_timetotal, + to_char(sum(((stw2.time1 + stw2.time2) - (stw2.timepause)::interval)), 'HH24:MI'::text) AS week_timetotal + FROM ( SELECT staffworkplan.daydate, + date_part('week'::text, staffworkplan.daydate) AS calweek, + (date_trunc('week'::text, (staffworkplan.daydate)::timestamp with time zone))::date AS caldate, + date_part('isodow'::text, staffworkplan.daydate) AS isodow, + staffworkplan.id, + staffworkplan.id_staff, + staffworkplan.timestart1, + staffworkplan.timeend1, + staffworkplan.vacancyhours, + staffworkplan.vacancytype, + CASE + WHEN (staffworkplan.timestart1 > staffworkplan.timeend1) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart1 - staffworkplan.timeend1))::time without time zone) + ELSE (staffworkplan.timeend1 - staffworkplan.timestart1) + END AS time1, + staffworkplan.timestart2, + staffworkplan.timeend2, + CASE + WHEN (staffworkplan.timestart2 > staffworkplan.timeend2) THEN ('24:00:00'::time without time zone - ((staffworkplan.timestart2 - staffworkplan.timeend2))::time without time zone) + ELSE (staffworkplan.timeend2 - staffworkplan.timestart2) + END AS time2, + staffworkplan.timepause + FROM %%NEWSCHEMA%%.staffworkplan) stw2 + GROUP BY stw2.calweek, stw2.caldate, stw2.id_staff; + + + +CREATE VIEW %%NEWSCHEMA%%.vw_staffworkplanlist AS + SELECT st.id AS id_staff, + ((st.surname || ' '::text) || st.prename) AS staffname, + (((((('Semaine '::text || sp_dwt.calweek) || '
('::text) || to_char((sp_dwt.weekbegin)::timestamp with time zone, 'DD.MM.YYYY'::text)) || ' - '::text) || to_char((date((sp_dwt.weekbegin + '7 days'::interval)))::timestamp with time zone, 'DD.MM.YYYY'::text)) || ')'::text) AS calweek, + sp_dwt.week_timetotal, + sp_dwt.weekbegin AS weekstart, + date((sp_dwt.weekbegin + '7 days'::interval)) AS weekend, + to_char((((((( + CASE + WHEN (sp_dwt.mon_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.mon_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END + + CASE + WHEN (sp_dwt.tue_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.tue_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.wed_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.wed_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.thu_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.thu_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.fri_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.fri_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sat_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.sat_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sun_vacancytype = 'ill'::text) THEN COALESCE((sp_dwt.sun_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END), 'HH24:MI'::text) AS weekvacancy_times_ill, + to_char((((((( + CASE + WHEN (sp_dwt.mon_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.mon_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END + + CASE + WHEN (sp_dwt.tue_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.tue_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.wed_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.wed_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.thu_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.thu_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.fri_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.fri_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sat_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.sat_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END) + + CASE + WHEN (sp_dwt.sun_vacancytype <> 'ill'::text) THEN COALESCE((sp_dwt.sun_vacancyhours)::interval, '00:00:00'::interval) + ELSE '00:00:00'::interval + END), 'HH24:MI'::text) AS weekvacancy_times, + (((((( + CASE + WHEN (sp_dwt.mon_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.mon_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.mon_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END + + CASE + WHEN (sp_dwt.tue_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.tue_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.tue_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.wed_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.wed_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.wed_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.thu_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.thu_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.thu_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.fri_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.fri_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.fri_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sat_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sat_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sat_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sun_vacancytype = 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sun_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sun_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) AS weekvacancy_minutes_ill, + (((((( + CASE + WHEN (sp_dwt.mon_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.mon_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.mon_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END + + CASE + WHEN (sp_dwt.tue_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.tue_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.tue_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.wed_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.wed_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.wed_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.thu_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.thu_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.thu_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.fri_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.fri_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.fri_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sat_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sat_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sat_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) + + CASE + WHEN (sp_dwt.sun_vacancytype <> 'ill'::text) THEN COALESCE(((to_number("left"(sp_dwt.sun_vacancyhours, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.sun_vacancyhours, 2), '99'::text)), (0)::numeric) + ELSE (0)::numeric + END) AS weekvacancy_minutes, + ((to_number("left"(sp_dwt.week_timetotal, 2), '99'::text) * (60)::numeric) + to_number("right"(sp_dwt.week_timetotal, 2), '99'::text)) AS weekminutes, + sp_dwt.mon_id, + sp_dwt.weekbegin AS mon_date, + ((COALESCE(((to_char((sp_dwt.mon_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.mon_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.mon_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.mon_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.mon_vacancytype) || ': '::text) || to_char((sp_dwt.mon_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspmontimes, + sp_dwt.mon_timetotal, + sp_dwt.tue_id, + date((sp_dwt.weekbegin + '1 day'::interval)) AS tue_date, + ((COALESCE(((to_char((sp_dwt.tue_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.tue_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.tue_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.tue_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.tue_vacancytype) || ': '::text) || to_char((sp_dwt.tue_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dsptuetimes, + sp_dwt.tue_timetotal, + sp_dwt.wed_id, + date((sp_dwt.weekbegin + '2 days'::interval)) AS wed_date, + ((COALESCE(((to_char((sp_dwt.wed_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.wed_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.wed_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.wed_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.wed_vacancytype) || ': '::text) || to_char((sp_dwt.wed_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspwedtimes, + sp_dwt.wed_timetotal, + sp_dwt.thu_id, + date((sp_dwt.weekbegin + '3 days'::interval)) AS thu_date, + ((COALESCE(((to_char((sp_dwt.thu_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.thu_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.thu_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.thu_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.thu_vacancytype) || ': '::text) || to_char((sp_dwt.thu_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspthutimes, + sp_dwt.thu_timetotal, + sp_dwt.fri_id, + date((sp_dwt.weekbegin + '4 days'::interval)) AS fri_date, + ((COALESCE(((to_char((sp_dwt.fri_start1)::interval, 'HH24:MI'::text) || ' -'::text) || to_char((sp_dwt.fri_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.fri_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.fri_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.fri_vacancytype) || ': '::text) || to_char((sp_dwt.fri_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspfritimes, + sp_dwt.fri_timetotal, + sp_dwt.sat_id, + date((sp_dwt.weekbegin + '5 days'::interval)) AS sat_date, + ((COALESCE(((to_char((sp_dwt.sat_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.sat_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.sat_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.sat_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.sat_vacancytype) || ': '::text) || to_char((sp_dwt.sat_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspsattimes, + sp_dwt.sat_timetotal, + sp_dwt.sun_id, + date((sp_dwt.weekbegin + '6 days'::interval)) AS sun_date, + ((COALESCE(((to_char((sp_dwt.sun_start1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((sp_dwt.sun_end1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((sp_dwt.sun_start2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((sp_dwt.sun_end2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE(((('
Congé '::text || sp_dwt.sun_vacancytype) || ': '::text) || to_char((sp_dwt.sun_vacancyhours)::interval, 'HH24:MI'::text)), ''::text)) AS dspsuntimes, + sp_dwt.sun_timetotal + FROM (%%NEWSCHEMA%%.vw_staffworkplan_weekly sp_dwt + LEFT JOIN %%NEWSCHEMA%%.staff st ON ((sp_dwt.id_staff = st.id))) + ORDER BY sp_dwt.calweek; + + + +CREATE TABLE %%NEWSCHEMA%%.workplans ( + id integer NOT NULL, + workplan text, + mon_timestart1 time without time zone, + mon_timeend1 time without time zone, + mon_timestart2 time without time zone, + mon_timeend2 time without time zone, + mon_timepause time without time zone, + tue_timestart1 time without time zone, + tue_timeend1 time without time zone, + tue_timestart2 time without time zone, + tue_timeend2 time without time zone, + tue_timepause time without time zone, + wed_timestart1 time without time zone, + wed_timeend1 time without time zone, + wed_timestart2 time without time zone, + wed_timeend2 time without time zone, + wed_timepause time without time zone, + thu_timestart1 time without time zone, + thu_timeend1 time without time zone, + thu_timestart2 time without time zone, + thu_timeend2 time without time zone, + thu_timepause time without time zone, + fri_timestart1 time without time zone, + fri_timeend1 time without time zone, + fri_timestart2 time without time zone, + fri_timeend2 time without time zone, + fri_timepause time without time zone, + sat_timestart1 time without time zone, + sat_timeend1 time without time zone, + sat_timestart2 time without time zone, + sat_timeend2 time without time zone, + sat_timepause time without time zone, + sun_timestart1 time without time zone, + sun_timeend1 time without time zone, + sun_timestart2 time without time zone, + sun_timeend2 time without time zone, + sun_timepause time without time zone +); + + + +CREATE VIEW %%NEWSCHEMA%%.vw_workplanlist AS + SELECT workplans.id, + workplans.workplan + FROM %%NEWSCHEMA%%.workplans; + + + +CREATE VIEW %%NEWSCHEMA%%.vw_workplans AS + SELECT workplans.id, + workplans.workplan, + ((COALESCE(((to_char((workplans.mon_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.mon_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.mon_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.mon_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.mon_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspmontimes, + ((COALESCE(((to_char((workplans.tue_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.tue_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.tue_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.tue_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.tue_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dsptuetimes, + ((COALESCE(((to_char((workplans.wed_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.wed_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.wed_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.wed_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.wed_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspwedtimes, + ((COALESCE(((to_char((workplans.thu_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.thu_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.thu_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.thu_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.thu_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspthutimes, + ((COALESCE(((to_char((workplans.fri_timestart1)::interval, 'HH24:MI'::text) || ' -'::text) || to_char((workplans.fri_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.fri_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.fri_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.fri_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspfritimes, + ((COALESCE(((to_char((workplans.sat_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.sat_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.sat_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.sat_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.sat_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspsattimes, + ((COALESCE(((to_char((workplans.sun_timestart1)::interval, 'HH24:MI'::text) || ' - '::text) || to_char((workplans.sun_timeend1)::interval, 'HH24:MI'::text)), ''::text) || COALESCE(((('
'::text || to_char((workplans.sun_timestart2)::interval, 'HH24:MI'::text)) || ' - '::text) || to_char((workplans.sun_timeend2)::interval, 'HH24:MI'::text)), ''::text)) || COALESCE((('
'::text || 'Pause: '::text) || to_char((workplans.sun_timepause)::interval, 'HH24:MI'::text)), ''::text)) AS dspsuntimes + FROM %%NEWSCHEMA%%.workplans; + + + +CREATE VIEW %%NEWSCHEMA%%.vw_workplansdata AS + SELECT workplans.id, + workplans.workplan, + to_char((workplans.mon_timestart1)::interval, 'HH24:MI'::text) AS mon_timestart1, + to_char((workplans.mon_timeend1)::interval, 'HH24:MI'::text) AS mon_timeend1, + to_char((workplans.mon_timestart2)::interval, 'HH24:MI'::text) AS mon_timestart2, + to_char((workplans.mon_timeend2)::interval, 'HH24:MI'::text) AS mon_timeend2, + to_char((workplans.mon_timepause)::interval, 'HH24:MI'::text) AS mon_timepause, + to_char((workplans.tue_timestart1)::interval, 'HH24:MI'::text) AS tue_timestart1, + to_char((workplans.tue_timeend1)::interval, 'HH24:MI'::text) AS tue_timeend1, + to_char((workplans.tue_timestart2)::interval, 'HH24:MI'::text) AS tue_timestart2, + to_char((workplans.tue_timeend2)::interval, 'HH24:MI'::text) AS tue_timeend2, + to_char((workplans.tue_timepause)::interval, 'HH24:MI'::text) AS tue_timepause, + to_char((workplans.wed_timestart1)::interval, 'HH24:MI'::text) AS wed_timestart1, + to_char((workplans.wed_timeend1)::interval, 'HH24:MI'::text) AS wed_timeend1, + to_char((workplans.wed_timestart2)::interval, 'HH24:MI'::text) AS wed_timestart2, + to_char((workplans.wed_timeend2)::interval, 'HH24:MI'::text) AS wed_timeend2, + to_char((workplans.wed_timepause)::interval, 'HH24:MI'::text) AS wed_timepause, + to_char((workplans.thu_timestart1)::interval, 'HH24:MI'::text) AS thu_timestart1, + to_char((workplans.thu_timeend1)::interval, 'HH24:MI'::text) AS thu_timeend1, + to_char((workplans.thu_timestart2)::interval, 'HH24:MI'::text) AS thu_timestart2, + to_char((workplans.thu_timeend2)::interval, 'HH24:MI'::text) AS thu_timeend2, + to_char((workplans.thu_timepause)::interval, 'HH24:MI'::text) AS thu_timepause, + to_char((workplans.fri_timestart1)::interval, 'HH24:MI'::text) AS fri_timestart1, + to_char((workplans.fri_timeend1)::interval, 'HH24:MI'::text) AS fri_timeend1, + to_char((workplans.fri_timestart2)::interval, 'HH24:MI'::text) AS fri_timestart2, + to_char((workplans.fri_timeend2)::interval, 'HH24:MI'::text) AS fri_timeend2, + to_char((workplans.fri_timepause)::interval, 'HH24:MI'::text) AS fri_timepause, + to_char((workplans.sat_timestart1)::interval, 'HH24:MI'::text) AS sat_timestart1, + to_char((workplans.sat_timeend1)::interval, 'HH24:MI'::text) AS sat_timeend1, + to_char((workplans.sat_timestart2)::interval, 'HH24:MI'::text) AS sat_timestart2, + to_char((workplans.sat_timeend2)::interval, 'HH24:MI'::text) AS sat_timeend2, + to_char((workplans.sat_timepause)::interval, 'HH24:MI'::text) AS sat_timepause, + to_char((workplans.sun_timestart1)::interval, 'HH24:MI'::text) AS sun_timestart1, + to_char((workplans.sun_timeend1)::interval, 'HH24:MI'::text) AS sun_timeend1, + to_char((workplans.sun_timestart2)::interval, 'HH24:MI'::text) AS sun_timestart2, + to_char((workplans.sun_timeend2)::interval, 'HH24:MI'::text) AS sun_timeend2, + to_char((workplans.sun_timepause)::interval, 'HH24:MI'::text) AS sun_timepause + FROM %%NEWSCHEMA%%.workplans; + + + +CREATE SEQUENCE %%NEWSCHEMA%%.workplans_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.workplans_id_seq OWNED BY %%NEWSCHEMA%%.workplans.id; + + + +CREATE TABLE %%NEWSCHEMA%%.worktypes ( + id integer NOT NULL, + worktype text, + isworktime boolean, + isfreetime boolean, + typecolor text +); + + + +CREATE SEQUENCE %%NEWSCHEMA%%.worktypes_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + + +ALTER SEQUENCE %%NEWSCHEMA%%.worktypes_id_seq OWNED BY %%NEWSCHEMA%%.worktypes.id; + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultweekworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.defaultweekworkplan_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.defaultworkplan_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.reportperiod ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.reportperiod_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.sites ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.sites_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staff ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staff_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffgroups ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffgroups_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffperiodbase ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffperiodbase_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.stafftimetracks ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.stafftimetracks_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancy ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffvacancy_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancyyear ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffvacancyyear_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffworkplan ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.staffworkplan_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.workplans ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.workplans_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.worktypes ALTER COLUMN id SET DEFAULT nextval('%%NEWSCHEMA%%.worktypes_id_seq'::regclass); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultweekworkplan + ADD CONSTRAINT defaultweekworkplan_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.defaultworkplan + ADD CONSTRAINT defaultworkplan_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.reportperiod + ADD CONSTRAINT reportperiod_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.sites + ADD CONSTRAINT sites_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staff + ADD CONSTRAINT staff_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffgroups + ADD CONSTRAINT staffgroups_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffperiodbase + ADD CONSTRAINT staffperiodbase_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.stafftimetracks + ADD CONSTRAINT stafftimetracks_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancy + ADD CONSTRAINT staffvacancy_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffvacancyyear + ADD CONSTRAINT staffvacancyyear_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.staffworkplan + ADD CONSTRAINT staffworkplan_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.workplans + ADD CONSTRAINT workplans_pkey PRIMARY KEY (id); + + + +ALTER TABLE ONLY %%NEWSCHEMA%%.worktypes + ADD CONSTRAINT worktypes_pkey PRIMARY KEY (id); + + + +CREATE TRIGGER trg_upd_%%NEWSCHEMA%%_weekhours BEFORE UPDATE OF weekhours ON %%NEWSCHEMA%%.staffperiodbase FOR EACH ROW EXECUTE PROCEDURE public.trg_update_monthhours(); + + + -- 2.39.5