blob: c8d685d79fbe2ec223ed49d064465c5a1a765842 [file] [log] [blame]
-----------------------------------------------------------------------------------
-- *******************************************************************************
-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
-- *
-- * See the NOTICE file(s) distributed with this work for additional
-- * information regarding copyright ownership.
-- *
-- * This program and the accompanying materials are made available under the
-- * terms of the Eclipse Public License v. 2.0 which is available at
-- * http://www.eclipse.org/legal/epl-2.0.
-- *
-- * SPDX-License-Identifier: EPL-2.0
-- *******************************************************************************
-----------------------------------------------------------------------------------
-- CREATE ROLE GFI_SERVICE LOGIN
-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
-- ALTER ROLE GFI_SERVICE with password 'gfi_service';
DROP TABLE IF EXISTS public.VERSION CASCADE;
DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_ID_SEQ;
DROP TABLE IF EXISTS public.REF_STATUS CASCADE;
DROP SEQUENCE IF EXISTS public.REF_STATUS_ID_SEQ;
DROP TABLE IF EXISTS public.REF_BRANCH CASCADE;
DROP SEQUENCE IF EXISTS public.REF_BRANCH_ID_SEQ;
DROP TABLE IF EXISTS public.REF_FAILURE_CLASSIFICATION CASCADE;
DROP SEQUENCE IF EXISTS public.REF_FAILURE_CLASSIFICATION_ID_SEQ;
DROP TABLE IF EXISTS public.REF_FAILURE_TYPE CASCADE;
DROP SEQUENCE IF EXISTS public.REF_FAILURE_TYPE_ID_SEQ;
DROP TABLE IF EXISTS public.REF_RADIUS CASCADE;
DROP SEQUENCE IF EXISTS public.REF_RADIUS_ID_SEQ;
DROP TABLE IF EXISTS public.REF_EXPECTED_REASON CASCADE;
DROP SEQUENCE IF EXISTS public.REF_EXPECTED_REASON_ID_SEQ;
DROP TABLE IF EXISTS public.TBL_IMPORT_DATA CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_IMPORT_DATA_ID_SEQ;
DROP TABLE IF EXISTS public.TBL_DISTRIBUTION_GROUP_MEMBER CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ;
DROP TABLE IF EXISTS public.TBL_DISTRIBUTION_GROUP CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_DISTRIBUTION_GROUP_ID_SEQ;
DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_STATION CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_STATION_ID_SEQ;
DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP_ID_SEQ;
DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL;
DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL_SEQ;
DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT_ID_SEQ;
-- ---------------------------------------------
-- TABLE VERSION
-- ---------------------------------------------
CREATE TABLE public.VERSION
(
ID integer NOT NULL,
VERSION character varying(50) NOT NULL,
CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id)
);
ALTER TABLE public.VERSION
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.VERSION TO GFI_SERVICE;
INSERT INTO public.VERSION (ID, VERSION) VALUES ( 1, '00-DEV' );
-- ---------------------------------------------
-- TABLE REF_STATUS
-- ---------------------------------------------
CREATE SEQUENCE public.ref_status_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.ref_status_id_seq
OWNER TO GFI_SERVICE;
CREATE TABLE public.REF_STATUS
(
ID integer NOT NULL DEFAULT nextval('REF_STATUS_ID_SEQ'::regclass),
UUID uuid NOT NULL,
STATUS character varying(50) NOT NULL,
CONSTRAINT REF_STATUS_PKEY PRIMARY KEY (id)
);
ALTER TABLE public.REF_STATUS
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.REF_STATUS TO GFI_SERVICE;
INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (1, 'acabc8f6-2cf3-485a-a4f8-68d178c7df45', 'neu');
INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (2, 'f2e44dc5-d30c-4128-86bb-d3d5fc766b61', 'geplant');
INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (3, '23fc0254-cc3d-4371-97ad-54ef733008ae', 'angelegt');
INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (4, '74a4ca78-7268-11ea-bc55-0242ac130003', 'storniert');
INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (5, '7264e572-eae9-4cca-be05-af6b0d081247', 'qualifiziert');
INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (6, '9374219a-7419-4b72-899d-cd0576d85cdb', 'aktualisiert');
INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (7, '8c333345-5c3c-41ed-9de4-323045f64259', 'abgeschlossen');
-- ---------------------------------------------
-- TABLE REF_FAILURE_CLASSIFICATION
-- ---------------------------------------------
CREATE SEQUENCE public.ref_failure_classification_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.ref_failure_classification_id_seq
OWNER TO GFI_SERVICE;
CREATE TABLE public.REF_FAILURE_CLASSIFICATION
(
ID integer NOT NULL DEFAULT nextval('REF_FAILURE_CLASSIFICATION_ID_SEQ'::regclass),
UUID uuid NOT NULL,
CLASSIFICATION character varying(50) NOT NULL,
DESCRIPTION character varying(255) NULL,
CONSTRAINT REF_FAILURE_CLASSIFICATION_PKEY PRIMARY KEY (id)
);
ALTER TABLE public.REF_FAILURE_CLASSIFICATION
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.REF_FAILURE_CLASSIFICATION TO GFI_SERVICE;
INSERT INTO public.REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( '9255fb79-c57a-4448-a69c-5d57994f0c91', 'Störung', NULL );
INSERT INTO public.REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( '8ec1e144-5230-4d43-a3df-f62dd64bb855', 'geplante Maßnahme', NULL );
-- ---------------------------------------------
-- TABLE REF_BRANCH
-- ---------------------------------------------
CREATE SEQUENCE public.ref_branch_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.ref_branch_id_seq
OWNER TO GFI_SERVICE;
CREATE TABLE public.REF_BRANCH
(
ID integer NOT NULL DEFAULT nextval('REF_BRANCH_ID_SEQ'::regclass),
UUID uuid NOT NULL,
NAME character varying(50) NOT NULL,
DESCRIPTION character varying(255),
COLOR_CODE character varying(20),
CONSTRAINT REF_BRANCH_PKEY PRIMARY KEY (id)
);
ALTER TABLE public.REF_BRANCH
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.REF_BRANCH TO GFI_SERVICE;
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('535b4beb-9b17-4247-bb8b-26bd01b48f9a', 'S', 'Strom', '#fc6042');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('d41f54e5-c4df-440e-b334-40e8f3a6854a', 'G', 'Gas', '#fdea64');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('62c6d361-96a0-41cc-bda1-4e58ad16f21a', 'F', 'Fernwärme', '#2cc990');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('d8d93e0e-5c8c-4ab8-9625-f820de55ee7c', 'W', 'Wasser', '#2c82c9');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('7d4907fb-cb3f-4a4f-93e9-839052e76894', 'TK', 'Telekommunikation', '#ff33cc');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('014c4a2a-3cf1-4d28-af70-4573722bceb0', 'ST', 'Sekundärtechnik', '#9933ff');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('8fb3c764-8fb5-11ea-bc55-0242ac130003', 'OS', 'ohne Sparte', '#ffffff');
-- ---------------------------------------------
-- TABLE REF_EXPECTED_REASON
-- ---------------------------------------------
CREATE SEQUENCE public.ref_expected_reason_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.ref_expected_reason_id_seq
OWNER TO GFI_SERVICE;
CREATE TABLE public.REF_EXPECTED_REASON
(
ID integer NOT NULL DEFAULT nextval('REF_EXPECTED_REASON_ID_SEQ'::regclass),
UUID uuid NOT NULL,
TEXT character varying(100) NOT NULL,
DESCRIPTION character varying(255) NULL,
BRANCHES character varying(100) NOT NULL,
CONSTRAINT REF_EXPECTED_REASON_PKEY PRIMARY KEY (id)
);
ALTER TABLE public.REF_EXPECTED_REASON
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.REF_EXPECTED_REASON TO GFI_SERVICE;
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07918-6aa0-11ea-bc55-0242ac130003', 'Defekt technische Anlage', 'Dies ist die Beschreibung für: Defekt technische Anlage', 'S,G,W,F,TK,ST,OS');
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07c42-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Mittelspannung', 'Dies ist die Beschreibung für: Kabelfehler Mittelspannung', 'S');
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07d50-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Niederspannung', 'Dies ist die Beschreibung für: Kabelfehler Niederspannung', 'S');
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07e22-6aa0-11ea-bc55-0242ac130003', 'Leitung beschädigt', 'Dies ist die Beschreibung für: Leitung beschädigt', 'S,G,W,F,TK,ST');
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07eea-6aa0-11ea-bc55-0242ac130003', 'noch nicht bekannt', 'Dies ist die Beschreibung für: noch nicht bekannt', 'S,G,W,F,TK,ST,OS');
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07fbc-6aa0-11ea-bc55-0242ac130003', 'Wasserrohrbruch', 'Dies ist die Beschreibung für: Wasserrohrbruch', 'W');
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a08160-6aa0-11ea-bc55-0242ac130003', 'Überregionale Störung', 'Dies ist die Beschreibung für: Überregionale Störung', 'S,G,W,F,TK,ST');
INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('343bd0e9-b958-4a94-950f-52223b58f62d', 'Sonstiges', 'Dies ist die Beschreibung für: sonstiges', 'S,G,W,F,TK,ST,OS');
-- ---------------------------------------------
-- TABLE REF_RADIUS
-- ---------------------------------------------
CREATE SEQUENCE public.ref_radius_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.ref_radius_id_seq
OWNER TO GFI_SERVICE;
CREATE TABLE public.REF_RADIUS
(
ID integer NOT NULL DEFAULT nextval('REF_RADIUS_ID_SEQ'::regclass),
UUID uuid NOT NULL,
RADIUS integer NOT NULL,
CONSTRAINT REF_RADIUS_PKEY PRIMARY KEY (id)
);
ALTER TABLE public.REF_RADIUS
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.REF_RADIUS TO GFI_SERVICE;
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('e6439b49-ce5a-486e-a437-b362ec73dc44', 0);
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671000-6aa6-11ea-bc55-0242ac130003', 50);
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('366712a8-6aa6-11ea-bc55-0242ac130003', 100);
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('366713c0-6aa6-11ea-bc55-0242ac130003', 250);
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('3667150a-6aa6-11ea-bc55-0242ac130003', 500);
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671780-6aa6-11ea-bc55-0242ac130003', 1000);
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('3667187a-6aa6-11ea-bc55-0242ac130003', 1500);
INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671ad2-6aa6-11ea-bc55-0242ac130003', 2000);
-- ---------------------------------------------
-- TABLE TBL_FAILURE_INFORMATION
-- ---------------------------------------------
CREATE SEQUENCE public.tbl_failure_information_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.tbl_failure_information_id_seq
OWNER TO GFI_SERVICE;
CREATE TABLE public.TBL_FAILURE_INFORMATION
(
ID integer NOT NULL DEFAULT nextval('TBL_FAILURE_INFORMATION_ID_SEQ'::regclass),
UUID uuid NOT NULL,
VERSION_NUMBER integer NOT NULL,
FK_REF_FAILURE_CLASSIFICATION integer,
TITLE character varying(255) NULL,
DESCRIPTION character varying(255) NULL,
RESPONSIBILITY character varying(255),
FK_REF_STATUS_INTERN integer NOT NULL,
FK_REF_BRANCH integer NOT NULL,
VOLTAGE_LEVEL character varying(2),
PRESSURE_LEVEL character varying(2),
FAILURE_BEGIN timestamp NOT NULL,
FAILURE_END_PLANNED timestamp,
FAILURE_END_RESUPPLIED timestamp,
INTERNAL_REMARK character varying(1024),
POSTCODE character varying(30),
CITY character varying(255),
DISTRICT character varying(255),
STREET character varying(255),
HOUSENUMBER character varying(30),
STATION_ID character varying (30),
STATION_DESCRIPTION character varying (255),
STATION_COORDS character varying (255),
FK_REF_RADIUS integer,
LONGITUDE numeric(9,6),
LATITUDE numeric(9,6),
FK_TBL_FAILURE_INFORMATION_CONDENSED integer,
CONDENSED boolean,
CONDENSED_COUNT integer,
OBJECT_REFERENCE_EXTERNAL_SYSTEM character varying (255),
PUBLICATION_STATUS character varying (40),
PUBLICATION_FREETEXT character varying (1024),
FK_REF_EXPECTED_REASON integer,
ADDRESS_TYPE varchar(10),
fault_location_area varchar(20),
CREATE_DATE timestamp without time zone NOT NULL,
CREATE_USER character varying(100) NOT NULL,
MOD_DATE timestamp without time zone NOT NULL,
MOD_USER character varying(100),
CONSTRAINT TBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (id),
CONSTRAINT TBL_FAIL_INF__FCLASS_FKEY FOREIGN KEY (FK_REF_FAILURE_CLASSIFICATION)
REFERENCES public.REF_FAILURE_CLASSIFICATION (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT TBL_FAIL_INF_STATUSINT_FKEY FOREIGN KEY (FK_REF_STATUS_INTERN)
REFERENCES public.REF_STATUS (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT TBL_FAIL_INF__BRANCH_ID_FKEY FOREIGN KEY (FK_REF_BRANCH)
REFERENCES public.REF_BRANCH (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT TBL_FAIL_INF__EXPREASON_FKEY FOREIGN KEY (FK_REF_EXPECTED_REASON)
REFERENCES public.REF_EXPECTED_REASON (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
ALTER TABLE public.TBL_FAILURE_INFORMATION
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.TBL_FAILURE_INFORMATION TO GFI_SERVICE;
INSERT INTO public.tbl_failure_information(UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, TITLE, DESCRIPTION, RESPONSIBILITY, FK_REF_STATUS_INTERN, FK_REF_BRANCH, VOLTAGE_LEVEL, PRESSURE_LEVEL, FAILURE_BEGIN, FAILURE_END_PLANNED, FAILURE_END_RESUPPLIED, INTERNAL_REMARK, POSTCODE, CITY, DISTRICT, STREET, HOUSENUMBER, STATION_ID, STATION_DESCRIPTION, STATION_COORDS, FK_REF_RADIUS, LONGITUDE, LATITUDE, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, CONDENSED, CONDENSED_COUNT, FK_TBL_FAILURE_INFORMATION_CONDENSED, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER)
VALUES ('37aef635-d0d4-4c47-ac25-c0d16c29e35c', 1, 2, 'Gasleck Oldenburg', 'Es gibt ein Gasleck in Oldenburg', 'Bernd Britzel', 2, 3, 'NS', 'HD', '2021-05-19 00:00:00', '2021-05-22 00:00:00', '2021-05-22 12:00:00', 'HD betroffen', '26133', 'Oldenburg', null, 'Cloppenburger Str.', '302', '1234863-b234', 'ertf', '124,2323', 4, 8.210150, 53.111820, 'Link - extenes System', 'nicht veröffentlicht', 'Freitext: Eine Gasleitung wurde beschädigt', 4, false, 0, null, '2020-02-03 15:15:15', 'SCRIPT', '2020-02-24 20:08:41', 'SCRIPT');
-- ---------------------------------------------
-- TABLE TBL_DISTRIBUTION_GROUP
-- ---------------------------------------------
CREATE SEQUENCE public.TBL_DISTRIBUTION_GROUP_ID_SEQ
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.TBL_DISTRIBUTION_GROUP_ID_SEQ
OWNER TO GFI_SERVICE;
CREATE TABLE public.TBL_DISTRIBUTION_GROUP
(
ID integer NOT NULL DEFAULT nextval('TBL_DISTRIBUTION_GROUP_ID_SEQ'::regclass),
UUID uuid NOT NULL,
NAME character varying(255),
DISTRIBUTION_TEXT_PUBLISH character varying(2048),
EMAIL_SUBJECT_PUBLISH varchar(255),
DISTRIBUTION_TEXT_COMPLETE character varying(2048),
EMAIL_SUBJECT_COMPLETE varchar(255),
DISTRIBUTION_TEXT_UPDATE character varying(2048),
EMAIL_SUBJECT_UPDATE varchar(255),
CONSTRAINT TBL_DISTRIBUTION_GROUP_PKEY PRIMARY KEY (id)
);
ALTER TABLE public.TBL_DISTRIBUTION_GROUP
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.TBL_DISTRIBUTION_GROUP TO GFI_SERVICE;
INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT_PUBLISH)
VALUES('5f5017fe-887a-11ea-bc55-0242ac130003', 'Verteiler technisch - intern', 'Subject: Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status veröffentlicht geändert.
Body:
Verteiler fachlich TEST
Sehr geehrte Damen und Herren,
die im Betreff genannte Meldung ist über folgenden Link erreichbar:
$Direkter_Link_zur_Störung$
Mit freundlichen Grüßen
Ihre Admin-Meister-Team der PTA GmbH');
INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT_PUBLISH)
VALUES('5f502046-887a-11ea-bc55-0242ac130003', 'Verteiler fachlich - intern', 'Subject: Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status veröffentlicht geändert.
Body:
Verteiler fachlich TEST
Sehr geehrte Damen und Herren,
die im Betreff genannte Meldung ist über folgenden Link erreichbar:
$Direkter_Link_zur_Störung$
Mit freundlichen Grüßen
Ihre Admin-Meister-Team der PTA GmbH');
INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT_PUBLISH)
VALUES('f71660e2-aee1-11ea-b3de-0242ac130004', 'Veröffentlicher', 'Subject: Bitte Anpassen - Das geplannte Ende der Störung (Sparte: $Sparte$) wird demnächst erreicht. Der Statuswechsel erfolgt automatisch.
Body:
Veröffentlicher
Sehr geehrte Damen und Herren,
die im Betreff genannte Meldung ist über folgenden Link erreichbar:
$Direkter_Link_zur_Störung$
Mit freundlichen Grüßen');
-- Verteiler technisch - intern ###############################################################################################
DO $$
DECLARE DISTRIBUTION_TEXT TEXT;
BEGIN
DISTRIBUTION_TEXT := 'Verteiler technisch [TEST]
Sehr geehrte Damen und Herren,
die im Betreff genannte Meldung ist über folgenden Link erreichbar:
$Direkter_Link_zur_Störung$
Mit freundlichen Grüßen
Ihre Admin-Meister-Team der PTA GmbH' ;
UPDATE public.tbl_distribution_group
SET EMAIL_SUBJECT_PUBLISH = 'Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status veröffentlicht geändert.',
EMAIL_SUBJECT_UPDATE = 'Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status aktualisiert geändert.',
EMAIL_SUBJECT_COMPLETE = 'Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status beendet geändert.',
DISTRIBUTION_TEXT_PUBLISH = DISTRIBUTION_TEXT,
DISTRIBUTION_TEXT_UPDATE = DISTRIBUTION_TEXT,
DISTRIBUTION_TEXT_COMPLETE = DISTRIBUTION_TEXT
WHERE UUID = '5f5017fe-887a-11ea-bc55-0242ac130003';
END $$;
-- Verteiler fachlich - intern ###############################################################################################
DO $$
DECLARE DISTRIBUTION_TEXT TEXT;
BEGIN
DISTRIBUTION_TEXT := 'Verteiler fachlich [TEST]
Sehr geehrte Damen und Herren,
die im Betreff genannte Meldung ist über folgenden Link erreichbar:
$Direkter_Link_zur_Störung$
Mit freundlichen Grüßen
Ihre Admin-Meister-Team der PTA GmbH' ;
UPDATE public.tbl_distribution_group
SET EMAIL_SUBJECT_PUBLISH = 'Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status veröffentlicht geändert.',
EMAIL_SUBJECT_UPDATE = 'Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status aktualisiert geändert.',
EMAIL_SUBJECT_COMPLETE = 'Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status beendet geändert.',
DISTRIBUTION_TEXT_PUBLISH = DISTRIBUTION_TEXT,
DISTRIBUTION_TEXT_UPDATE = DISTRIBUTION_TEXT,
DISTRIBUTION_TEXT_COMPLETE = DISTRIBUTION_TEXT
WHERE UUID = '5f502046-887a-11ea-bc55-0242ac130003';
END $$;
-- Veröffentlicher ###############################################################################################
UPDATE public.tbl_distribution_group
SET EMAIL_SUBJECT_PUBLISH = 'Bitte Anpassen - Das geplannte Ende der Störung (Sparte: $Sparte$) wird demnächst erreicht. Der Statuswechsel erfolgt automatisch.',
DISTRIBUTION_TEXT_PUBLISH =
'Veröffentlicher [TEST]
Sehr geehrte Damen und Herren,
die im Betreff genannte Meldung ist über folgenden Link erreichbar:
$Direkter_Link_zur_Störung$
Mit freundlichen Grüßen
Ihre Admin-Meister-Team der PTA GmbH'
WHERE UUID = 'f71660e2-aee1-11ea-b3de-0242ac130004';
-- Test für alle Textbausteine ###############################################################################################
DO $$
DECLARE DISTRIBUTION_TEXT TEXT;
BEGIN
DISTRIBUTION_TEXT := 'Beschreibung $Beschreibung$
Direkter_Link_zur_Störung $Direkter_Link_zur_Störung$
Druckstufe $Druckstufe$
Hausnummer $Hausnummer$
Interne_Bemerkungen $Interne_Bemerkungen$
Klassifikation $Klassifikation$
Ort $Ort$
Ortsteil $Ortsteil$
Postleitzahl $Postleitzahl$
Radius $Radius$
Spannungsebene $Spannungsebene$
Sparte $Sparte$
Station_Bezeichnung $Station_Bezeichnung$
Status_extern $Status_extern$
Status_intern $Status_intern$
Störungsbeginn_gemeldet $Störungsbeginn_gemeldet$
Störungsende_geplant $Störungsende_geplant$
Störungsende_wiederversorgt $Störungsende_wiederversorgt$
Straße $Straße$
Veröffentlichungsstatus $Veröffentlichungsstatus$
Voraussichtlicher_Grund $Voraussichtlicher_Grund$
Zuständigkeit $Zuständigkeit$' ;
INSERT INTO public.tbl_distribution_group(UUID, NAME, EMAIL_SUBJECT_PUBLISH, DISTRIBUTION_TEXT_PUBLISH, EMAIL_SUBJECT_UPDATE, DISTRIBUTION_TEXT_UPDATE, EMAIL_SUBJECT_COMPLETE, DISTRIBUTION_TEXT_COMPLETE)
VALUES('a09140ea-ef06-4773-bb1d-dd7725b89570', 'Test für alle Textbausteine', 'Betreff: Test für alle Textbausteine Veröffentlicht', DISTRIBUTION_TEXT, 'Betreff: Test für alle Textbausteine Aktualisiert', DISTRIBUTION_TEXT,
'Betreff: Test für alle Textbausteine Beendet', DISTRIBUTION_TEXT);
END $$;
-- ---------------------------------------------
-- TABLE TBL_DISTRIBUTION_GROUP_MEMBER
-- ---------------------------------------------
CREATE SEQUENCE public.TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ
OWNER TO GFI_SERVICE;
CREATE TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER
(
ID integer NOT NULL DEFAULT nextval('TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ'::regclass),
UUID uuid NOT NULL,
FK_TBL_DISTRIBUTION_GROUP integer NOT NULL,
CONTACT_ID uuid NOT NULL,
POSTCODES varchar(1024),
CONSTRAINT TBL_DISTRIBUTION_GROUP_MEMBER_PKEY PRIMARY KEY (id),
CONSTRAINT TBL_DIS_GRP_MEM__DIS_GRP_FKEY FOREIGN KEY (FK_TBL_DISTRIBUTION_GROUP)
REFERENCES public.TBL_DISTRIBUTION_GROUP (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
ALTER TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER
OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER TO GFI_SERVICE;
INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID)
VALUES('5f5021a4-887a-11ea-bc55-0242ac130003', 1, 'ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e' );
INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID)
VALUES('5f50228a-887a-11ea-bc55-0242ac130003', 1, '7782179b-fb79-4370-8f71-f4c71470d006' );
INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID)
VALUES('14dcac8c-887f-11ea-bc55-0242ac130003', 1, 'fa3d981b-a7d6-4965-a623-cdbc69404153' );
INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID)
VALUES('14dcaef8-887f-11ea-bc55-0242ac130003', 2, 'c862d604-5766-43d6-a7e8-a4bac2bd01e1');
INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID)
VALUES('14dcaffc-887f-11ea-bc55-0242ac130003', 2, '8fe41b90-d10c-4a70-8fde-0990286ad3c6');
-- ---------------------------------------------
-- TABLE TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP
-- ---------------------------------------------
CREATE SEQUENCE public.tbl_failure_information_distribution_group_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.tbl_failure_information_distribution_group_id_seq
OWNER TO gfi_service;
CREATE TABLE public.tbl_failure_information_distribution_group
(
id integer NOT NULL DEFAULT nextval('tbl_failure_information_distribution_group_id_seq'::regclass),
fk_tbl_failure_information integer NOT NULL,
fk_tbl_distribution_group integer NOT NULL,
CONSTRAINT tbl_failure_information_distribution_group_pkey PRIMARY KEY (id),
CONSTRAINT tbl_fail_inf_dist_grp__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT tbl_fail_inf_dist_grp__distribution_group_fkey FOREIGN KEY (fk_tbl_distribution_group)
REFERENCES public.tbl_distribution_group (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.tbl_failure_information_distribution_group
OWNER TO gfi_service;
GRANT ALL ON TABLE public.tbl_failure_information_distribution_group TO gfi_service;
INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP)
VALUES(1, 1);
INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP)
VALUES(1, 2);
CREATE UNIQUE INDEX idx_tbl_fi_distribution_group_uq ON public.tbl_failure_information_distribution_group ( fk_tbl_failure_information, fk_tbl_distribution_group );
-- ---------------------------------------------
-- TABLE TBL_FAILURE_INFORMATION_STATION
-- ---------------------------------------------
CREATE SEQUENCE public.tbl_failure_information_station_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.tbl_failure_information_station_id_seq
OWNER TO gfi_service;
CREATE TABLE public.tbl_failure_information_station
(
id integer NOT NULL DEFAULT nextval('tbl_failure_information_station_id_seq'::regclass),
fk_tbl_failure_information integer NOT NULL,
station_station_id character varying(30) NOT NULL,
CONSTRAINT tbl_failure_information_station_pkey PRIMARY KEY (id),
CONSTRAINT tbl_fail_inf_station__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.tbl_failure_information_station
OWNER TO gfi_service;
GRANT ALL ON TABLE public.tbl_failure_information_station TO gfi_service;
-- ---------------------------------------------
-- TABLE TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL
-- ---------------------------------------------
CREATE SEQUENCE public.tbl_failure_information_publication_channel_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.tbl_failure_information_publication_channel_seq
OWNER TO gfi_service;
CREATE TABLE public.tbl_failure_information_publication_channel
(
id integer NOT NULL DEFAULT nextval('tbl_failure_information_publication_channel_seq'::regclass),
fk_tbl_failure_information integer NOT NULL,
publication_channel character varying(50) NOT NULL,
published boolean NOT NULL,
CONSTRAINT tbl_failure_information_publication_channel_pkey PRIMARY KEY (id),
CONSTRAINT tbl_failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
ALTER TABLE public.tbl_failure_information_publication_channel
OWNER TO gfi_service;
GRANT ALL ON TABLE public.tbl_failure_information_publication_channel TO gfi_service;
INSERT INTO public.tbl_failure_information_publication_channel(FK_TBL_FAILURE_INFORMATION, PUBLICATION_CHANNEL, PUBLISHED)
VALUES(1, 'Mail', false);
-- ---------------------------------------------
-- TABLE TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT
-- ---------------------------------------------
CREATE SEQUENCE public.tbl_failure_information_reminder_mail_sent_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE public.tbl_failure_information_reminder_mail_sent_id_seq
OWNER TO gfi_service;
CREATE TABLE public.tbl_failure_information_reminder_mail_sent
(
id integer NOT NULL DEFAULT nextval('tbl_failure_information_reminder_mail_sent_id_seq'::regclass),
fk_tbl_failure_information integer NOT NULL,
mail_sent boolean NOT NULL,
date_mail_sent timestamp,
CONSTRAINT tbl_failure_information_reminder_mail_sent_pkey PRIMARY KEY (id),
CONSTRAINT tbl_fail_inf_rem_mail_sent__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.tbl_failure_information_reminder_mail_sent
OWNER TO gfi_service;
GRANT ALL ON TABLE public.tbl_failure_information_reminder_mail_sent TO gfi_service;
create unique index idx_ri_reminder_sent_uq on public.tbl_failure_information_reminder_mail_sent ( fk_tbl_failure_information );
INSERT INTO public.tbl_failure_information_reminder_mail_sent(FK_TBL_FAILURE_INFORMATION, mail_sent, date_mail_sent)
VALUES(1, true, '2021-01-21 00:01:00');
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- HISTORY-TABLES
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- PUBLIC.HTBL_FAILURE_INFORMATION Automatic generanted History Table DDL --
-- <GENERATED CODE!>
DROP TABLE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION;
DROP SEQUENCE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ;
CREATE SEQUENCE PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ
OWNER TO gfi_service;
CREATE TABLE PUBLIC.HTBL_FAILURE_INFORMATION
(
HID integer NOT NULL DEFAULT nextval('HTBL_FAILURE_INFORMATION_ID_SEQ'::regclass),
HACTION integer NOT NULL,
HDATE timestamp without time zone NOT NULL,
HUSER character varying(100),
ID integer,
UUID uuid,
VERSION_NUMBER integer,
FK_REF_FAILURE_CLASSIFICATION integer,
TITLE character varying(255) NULL,
DESCRIPTION character varying(255) NULL,
RESPONSIBILITY character varying (255),
FK_REF_STATUS_INTERN integer,
FK_REF_BRANCH integer,
VOLTAGE_LEVEL character varying (2),
PRESSURE_LEVEL character varying (2),
FAILURE_BEGIN timestamp without time zone,
FAILURE_END_PLANNED timestamp without time zone,
FAILURE_END_RESUPPLIED timestamp without time zone,
INTERNAL_REMARK character varying(1024),
POSTCODE character varying (30),
CITY character varying (255),
DISTRICT character varying (255),
STREET character varying (255),
HOUSENUMBER character varying (30),
STATION_ID character varying (30),
STATION_DESCRIPTION character varying (255),
STATION_COORDS character varying (255),
FK_REF_RADIUS integer,
LONGITUDE numeric(9,6),
LATITUDE numeric(9,6),
FK_TBL_FAILURE_INFORMATION_CONDENSED integer,
CONDENSED boolean,
CONDENSED_COUNT integer,
OBJECT_REFERENCE_EXTERNAL_SYSTEM character varying (255),
PUBLICATION_STATUS character varying (40),
PUBLICATION_FREETEXT character varying (1024),
FK_REF_EXPECTED_REASON integer,
ADDRESS_TYPE varchar(10),
fault_location_area varchar(20),
CREATE_USER character varying(100),
CREATE_DATE timestamp without time zone,
MOD_USER character varying(100),
MOD_DATE timestamp without time zone,
CONSTRAINT HTBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (HID)
)
WITH (
OIDS=FALSE
);
ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION
OWNER TO gfi_service;
GRANT ALL ON TABLE PUBLIC.HTBL_FAILURE_INFORMATION TO gfi_service;
INSERT INTO public.htbl_failure_information(HACTION, HDATE, HUSER, ID, UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, TITLE, DESCRIPTION, RESPONSIBILITY, FK_REF_STATUS_INTERN, FK_REF_BRANCH, VOLTAGE_LEVEL, PRESSURE_LEVEL, FAILURE_BEGIN, FAILURE_END_PLANNED, FAILURE_END_RESUPPLIED, INTERNAL_REMARK, POSTCODE, CITY, DISTRICT, STREET, HOUSENUMBER, STATION_ID, STATION_DESCRIPTION, STATION_COORDS, FK_REF_RADIUS, LONGITUDE, LATITUDE, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, FK_TBL_FAILURE_INFORMATION_CONDENSED, CONDENSED, CONDENSED_COUNT, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER)
VALUES (1, '2020-03-13 00:00:00', 'SCRIPT', 1, '37aef635-d0d4-4c47-ac25-c0d16c29e35c', 1, 2, 'Gasleck Oldenburg', 'Es gibt ein Gasleck in Oldenburg', 'Bernd Britzel', 2, 3, 'NS', 'HD', '2021-05-19 00:00:00', '2021-05-22 00:00:00', '2021-05-22 12:00:00', 'HD betroffen', '26133', 'Oldenburg', null, 'Cloppenburger Str.', '302', '1234863-b234', 'ertf', '124,2323', 4, 8.210150, 53.111820, 'Link - extenes System', 'nicht veröffentlicht', 'Freitext: Eine Gasleitung wurde beschädigt', 4, null, false, 0, '2020-02-03 15:15:15', 'SCRIPT', '2020-02-24 20:08:41', 'SCRIPT');
-- ----------------------------------------------------------------------------
-- HTBL_FAILURE_INFORMATION_STATION
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION_STATION;
DROP SEQUENCE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION_STATION_ID_SEQ;
CREATE SEQUENCE PUBLIC.HTBL_FAILURE_INFORMATION_STATION_ID_SEQ
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION_STATION_ID_SEQ
OWNER TO gfi_service;
CREATE TABLE PUBLIC.HTBL_FAILURE_INFORMATION_STATION
(
HID integer NOT NULL DEFAULT nextval('HTBL_FAILURE_INFORMATION_STATION_ID_SEQ'::regclass),
HDATE timestamp without time zone NOT NULL,
FK_TBL_FAILURE_INFORMATION integer,
VERSION_NUMBER integer,
STATION_STATION_ID character varying (30),
CONSTRAINT HTBL_FAILURE_INFORMATION_STATION_PKEY PRIMARY KEY (HID)
)
WITH (
OIDS=FALSE
);
ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION_STATION
OWNER TO gfi_service;
GRANT ALL ON TABLE PUBLIC.HTBL_FAILURE_INFORMATION_STATION TO gfi_service;
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- Tables ADDRESSIMPORT
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- ---------------------------------------------
-- TABLE TBL_ADDRESS
-- ---------------------------------------------
-- Das SQL Skript für den Addressimport wird im Modul "addressImport" verwaltet
-- ---------------------------------------------
-- TABLE TBL_STATION
-- ---------------------------------------------
-- Das SQL Skript für den Stationsimport wird im Modul "addressImport" verwaltet
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- TRIGGER
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- PUBLIC.TBL_FAILURE_INFORMATION INSERT TRIGGER --
-- <GENERATED CODE!>
CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO HTBL_FAILURE_INFORMATION (
ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN, FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON, ADDRESS_TYPE, FAULT_LOCATION_AREA, CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER,
HACTION,
HDATE,
HUSER
)
VALUES (
NEW.ID,NEW.UUID,NEW.VERSION_NUMBER,NEW.FK_REF_FAILURE_CLASSIFICATION,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.FK_REF_STATUS_INTERN, NEW.FK_REF_BRANCH,NEW.VOLTAGE_LEVEL,NEW.PRESSURE_LEVEL,NEW.FAILURE_BEGIN,NEW.FAILURE_END_PLANNED,NEW.FAILURE_END_RESUPPLIED,NEW.INTERNAL_REMARK,NEW.POSTCODE,NEW.CITY,NEW.DISTRICT,NEW.STREET,NEW.HOUSENUMBER,NEW.STATION_ID,NEW.STATION_DESCRIPTION,NEW.STATION_COORDS,NEW.FK_REF_RADIUS,NEW.LONGITUDE,NEW.LATITUDE,NEW.FK_TBL_FAILURE_INFORMATION_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REFERENCE_EXTERNAL_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON, NEW.ADDRESS_TYPE, NEW.FAULT_LOCATION_AREA, NEW.CREATE_DATE,NEW.CREATE_USER,NEW.MOD_DATE,NEW.MOD_USER,
1,
current_timestamp,
NEW.CREATE_USER );
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG()
OWNER TO gfi_service;
DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_INSERT_TRG ON PUBLIC.TBL_FAILURE_INFORMATION;
CREATE TRIGGER TBL_FAILURE_INFORMATION_INSERT_TRG
BEFORE INSERT
ON PUBLIC.TBL_FAILURE_INFORMATION
FOR EACH ROW
EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG();
-- PUBLIC.TBL_FAILURE_INFORMATION UPDATE TRIGGER --
-- <GENERATED CODE!>
CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO HTBL_FAILURE_INFORMATION (
ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN,FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,ADDRESS_TYPE,FAULT_LOCATION_AREA,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER,
HACTION,
HDATE,
HUSER
)
VALUES (
NEW.ID,NEW.UUID,NEW.VERSION_NUMBER,NEW.FK_REF_FAILURE_CLASSIFICATION,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.FK_REF_STATUS_INTERN,NEW.FK_REF_BRANCH,NEW.VOLTAGE_LEVEL,NEW.PRESSURE_LEVEL,NEW.FAILURE_BEGIN,NEW.FAILURE_END_PLANNED,NEW.FAILURE_END_RESUPPLIED,NEW.INTERNAL_REMARK,NEW.POSTCODE,NEW.CITY,NEW.DISTRICT,NEW.STREET,NEW.HOUSENUMBER,NEW.STATION_ID,NEW.STATION_DESCRIPTION,NEW.STATION_COORDS,NEW.FK_REF_RADIUS,NEW.LONGITUDE,NEW.LATITUDE,NEW.FK_TBL_FAILURE_INFORMATION_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REFERENCE_EXTERNAL_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON,NEW.ADDRESS_TYPE,NEW.FAULT_LOCATION_AREA, NEW.CREATE_DATE,NEW.CREATE_USER,NEW.MOD_DATE,NEW.MOD_USER,
2,
current_timestamp,
NEW.MOD_USER
);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG()
OWNER TO gfi_service;
DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_UPDATE_TRG ON PUBLIC.TBL_FAILURE_INFORMATION;
CREATE TRIGGER TBL_FAILURE_INFORMATION_UPDATE_TRG
BEFORE UPDATE
ON PUBLIC.TBL_FAILURE_INFORMATION
FOR EACH ROW
EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG();
-- PUBLIC.TBL_FAILURE_INFORMATION DELETE TRIGGER --
-- <GENERATED CODE!>
CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG()
RETURNS trigger AS
$BODY$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO HTBL_FAILURE_INFORMATION (
ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN,FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,ADDRESS_TYPE,FAULT_LOCATION_AREA,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER,
HACTION,
HDATE,
HUSER
)
VALUES (
OLD.ID,OLD.UUID,OLD.VERSION_NUMBER,OLD.FK_REF_FAILURE_CLASSIFICATION,OLD.TITLE,OLD.DESCRIPTION,OLD.RESPONSIBILITY,OLD.FK_REF_STATUS_INTERN,OLD.FK_REF_BRANCH,OLD.VOLTAGE_LEVEL,OLD.PRESSURE_LEVEL,OLD.FAILURE_BEGIN,OLD.FAILURE_END_PLANNED,OLD.FAILURE_END_RESUPPLIED,OLD.INTERNAL_REMARK,OLD.POSTCODE,OLD.CITY,OLD.DISTRICT,OLD.STREET,OLD.HOUSENUMBER,OLD.STATION_ID,OLD.STATION_DESCRIPTION,OLD.STATION_COORDS,OLD.FK_REF_RADIUS,OLD.LONGITUDE,OLD.LATITUDE,OLD.FK_TBL_FAILURE_INFORMATION_CONDENSED,OLD.CONDENSED,OLD.CONDENSED_COUNT,OLD.OBJECT_REFERENCE_EXTERNAL_SYSTEM,OLD.PUBLICATION_STATUS,OLD.PUBLICATION_FREETEXT,OLD.FK_REF_EXPECTED_REASON,OLD.ADDRESS_TYPE,OLD.FAULT_LOCATION_AREA, OLD.CREATE_DATE,OLD.CREATE_USER,OLD.MOD_DATE,OLD.MOD_USER,
3,
current_timestamp,
OLD.MOD_USER );
END IF;
RETURN OLD;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG()
OWNER TO gfi_service;
DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_DELETE_TRG ON PUBLIC.TBL_FAILURE_INFORMATION;
CREATE TRIGGER TBL_FAILURE_INFORMATION_DELETE_TRG
BEFORE DELETE
ON PUBLIC.TBL_FAILURE_INFORMATION
FOR EACH ROW
EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG();
-- ---------------------------------------------
-- INSERT TEST DATA
-- ---------------------------------------------
DELETE FROM public.tbl_failure_information_distribution_group WHERE fk_tbl_failure_information = (select id from public.tbl_failure_information where uuid = '37aef635-d0d4-4c47-ac25-c0d16c29e35c');
DELETE FROM public.tbl_failure_information_publication_channel WHERE fk_tbl_failure_information = (select id from public.tbl_failure_information where uuid = '37aef635-d0d4-4c47-ac25-c0d16c29e35c');
DELETE FROM public.tbl_failure_information_reminder_mail_sent WHERE fk_tbl_failure_information = (select id from public.tbl_failure_information where uuid = '37aef635-d0d4-4c47-ac25-c0d16c29e35c');
DELETE FROM public.tbl_failure_information WHERE uuid = '37aef635-d0d4-4c47-ac25-c0d16c29e35c';
update tbl_failure_information set version_number = version_number+1, fault_location_area = 'address';
update tbl_failure_information set version_number = version_number+1, fault_location_area = 'map' where address_type = 'freetext';
update tbl_failure_information set version_number = version_number+1, fault_location_area = 'station' where station_id IS NOT NULL;
update tbl_failure_information set version_number = version_number+1, fault_location_area = 'station' where id in (SELECT DISTINCT fk_tbl_failure_information FROM tbl_failure_information_station);
UPDATE public.VERSION SET VERSION = '1.0.0' WHERE ID = 1;