Merge branch 'DEVELOP' of ssh://git.eclipse.org:29418/openk-usermodules/org.eclipse.openk-usermodules.gridFailureInformation.backend into DEVELOP
diff --git a/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/jobs/JobManagerTest.java b/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/jobs/JobManagerTest.java
index 4f198e9..1b682a5 100644
--- a/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/jobs/JobManagerTest.java
+++ b/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/jobs/JobManagerTest.java
@@ -17,11 +17,16 @@
import org.eclipse.openk.gridfailureinformation.importadresses.config.TestConfiguration;
import org.eclipse.openk.gridfailureinformation.importadresses.service.AddressImportService;
import org.junit.jupiter.api.Test;
+import org.mockito.internal.stubbing.answers.DoesNothing;
import org.powermock.reflect.Whitebox;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.mock.mockito.MockBean;
+import org.springframework.http.HttpStatus;
import org.springframework.test.context.ContextConfiguration;
+
+import javax.xml.ws.http.HTTPException;
+
import static java.lang.Boolean.TRUE;
import static java.lang.Boolean.FALSE;
import static org.junit.jupiter.api.Assertions.*;
@@ -41,7 +46,7 @@
public void shouldTriggerStartImport() {
jobManager.triggerStartImport();
Boolean startImport = Whitebox.getInternalState(jobManager, "startImport");
- assertEquals(startImport, TRUE);
+ assertEquals(TRUE, startImport);
}
@Test
@@ -55,7 +60,7 @@
}
Boolean startImport = Whitebox.getInternalState(jobManager, "startImport");
- assertEquals(startImport, FALSE);
+ assertEquals(FALSE, startImport);
}
@Test
@@ -69,7 +74,7 @@
}
Boolean startImport = Whitebox.getInternalState(jobManager, "startImport");
- assertEquals(startImport, FALSE);
+ assertEquals(FALSE, startImport);
}
@Test
@@ -82,4 +87,29 @@
}
verify(addressImportService, times(1)).importAddresses(false);
}
+
+
+ @Test
+ public void shouldNotImportAddressdataDueToAnException() {
+ jobManager.cleanUp = false;
+
+ doThrow(new HTTPException(HttpStatus.PROCESSING.value())).when(addressImportService).importAddresses(anyBoolean());
+ try {
+ Whitebox.invokeMethod(jobManager, "importAddressdata");
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ verify(addressImportService, times(1)).importAddresses(false);
+ }
+
+
+ @Test
+ public void shouldNotImportAddressdataDueToAnUnknownException() throws Exception {
+ jobManager.cleanUp = false;
+
+ doThrow(new HTTPException(HttpStatus.INTERNAL_SERVER_ERROR.value())).when(addressImportService).importAddresses(anyBoolean());
+
+ assertThrows(HTTPException.class, () -> Whitebox.invokeMethod(jobManager, "importAddressdata") );
+
+ }
}
diff --git a/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/service/AdressImportServiceTest.java b/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/service/AdressImportServiceTest.java
index 6f6f6ab..7affe3e 100644
--- a/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/service/AdressImportServiceTest.java
+++ b/addressImport/src/test/java/org/eclipse/openk/gridfailureinformation/importadresses/service/AdressImportServiceTest.java
@@ -18,6 +18,8 @@
import org.eclipse.openk.gridfailureinformation.importadresses.viewmodel.AddressDto;
import org.eclipse.openk.gridfailureinformation.importadresses.viewmodel.StationDto;
import org.junit.jupiter.api.Test;
+import static org.junit.jupiter.api.Assertions.*;
+import org.powermock.reflect.Whitebox;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
@@ -25,6 +27,8 @@
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.ContextConfiguration;
+import javax.xml.ws.http.HTTPException;
+
import static org.mockito.ArgumentMatchers.any;
import static org.mockito.Mockito.times;
import static org.mockito.Mockito.verify;
@@ -45,6 +49,18 @@
@Autowired
private AddressImportService addressImportService;
+
+ @Test
+ public void shouldNotImportIfAlreadyProcessing() {
+ boolean b = Whitebox.getInternalState(addressImportService, "importInWork");
+ Whitebox.setInternalState(addressImportService, "importInWork", true);
+
+ assertThrows(HTTPException.class, () -> addressImportService.importAddresses(false) );
+
+ Whitebox.setInternalState(addressImportService, "importInWork", b);
+ }
+
+
@Test
public void shouldImportAndInsertProperly() {
addressImportService.importAddresses(true);
@@ -64,4 +80,26 @@
verify(addressService, times(12)).updateOrInsertAddressByG3efid(any(AddressDto.class));
}
+
+ @Test
+ public void shouldNotImportDueToMissingFiles() {
+ String addressFile = Whitebox.getInternalState(addressImportService, "fileAddresses");
+ String filePowerConnections = Whitebox.getInternalState(addressImportService, "filePowerConnections");
+ String fileWaterConnections = Whitebox.getInternalState(addressImportService, "fileWaterConnections");
+ String fileGasConnections = Whitebox.getInternalState(addressImportService, "fileGasConnections");
+ Whitebox.setInternalState(addressImportService, "fileAddresses", "Not here");
+ Whitebox.setInternalState(addressImportService, "filePowerConnections", "Not here");
+ Whitebox.setInternalState(addressImportService, "fileWaterConnections", "Not here");
+ Whitebox.setInternalState(addressImportService, "fileGasConnections", "Not here");
+
+
+ addressImportService.importAddresses(false);
+ verify(addressService, times(0)).deleteAllAddresses();
+ verify(stationService, times(0)).deleteAllStations();
+
+ Whitebox.setInternalState(addressImportService, "fileAddresses", addressFile);
+ Whitebox.setInternalState(addressImportService, "filePowerConnections", filePowerConnections);
+ Whitebox.setInternalState(addressImportService, "fileWaterConnections", fileWaterConnections);
+ Whitebox.setInternalState(addressImportService, "fileGasConnections", fileGasConnections);
+ }
}
diff --git a/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/bpmn/impl/GfiGrid.java b/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/bpmn/impl/GfiGrid.java
index 3fb2a3d..17096d8 100644
--- a/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/bpmn/impl/GfiGrid.java
+++ b/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/bpmn/impl/GfiGrid.java
@@ -61,6 +61,7 @@
ProcessTask publishMessageToChannels = new PublishFailureToChannelsTask();
ProcessTask storePublicationStatusPublished = new StorePublishStatusTask(Constants.PUB_STATUS_VEROEFFENTLICHT);
ProcessTask storePublicationStatusWithdrawn = new StorePublishStatusTask(Constants.PUB_STATUS_ZURUECKGEZOGEN);
+ DecisionTask decidePublishedOnComplete = new DecideFailureInfoPublished();
ProcessTask updateMessage = new UIStoreFailureInformationTask( "State Updated UI Task", true);
DecisionTask decideUpdated = new DecideFailureInfoUpdated();
@@ -92,7 +93,9 @@
decideUpdated.connectOutputTo(PORT1, storeEditStatusUpdated);
decideUpdated.connectOutputTo(PORT2, setEndDate);
decideUpdated.connectOutputTo(PORT3, storeEditStatusCanceled);
- setEndDate.connectOutputTo(storePublicationStatusWithdrawn);
+ setEndDate.connectOutputTo(decidePublishedOnComplete);
+ decidePublishedOnComplete.connectOutputTo(YES, storePublicationStatusWithdrawn);
+ decidePublishedOnComplete.connectOutputTo(NO, completedEndpoint);
storePublicationStatusWithdrawn.connectOutputTo(completedEndpoint);
storeEditStatusCanceled.connectOutputTo(decidePublished);
diff --git a/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationService.java b/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationService.java
index 39800ff..8a54c8a 100644
--- a/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationService.java
+++ b/gfsBackendService/src/main/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationService.java
@@ -348,36 +348,6 @@
}
}
-// protected void updatePublicationChannels(FailureInformationDto sourceDto, TblFailureInformation destTblFailureInformation ){
-//
-// // Löschen zugeordneter Kanäle mit published = false
-// TblFailureInformation existingTblFailureInformation = failureInformationRepository.findByUuid(sourceDto.getUuid())
-// .orElseThrow(() -> new NotFoundException(Constants.FAILURE_INFO_UUID_NOT_EXISTING));
-//
-// Set<TblFailureInformationPublicationChannel> existingChannels =
-// existingTblFailureInformation.getPublicationChannelList();
-//
-// List<TblFailureInformationPublicationChannel>filteredList = existingChannels.stream().filter(x -> !x.isPublished()).collect(Collectors.toList());
-//
-// for(TblFailureInformationPublicationChannel channel: filteredList){
-// existingChannels.remove(channel);
-// }
-//
-// //Hinzufügen zugeordneter Kanäle in der Zuordnungstabelle mit published= false (check, damit kein Kanal doppelt)
-// List<String> newChannelList = sourceDto.getPublicationChannels();
-// for(String newChannel: newChannelList){
-// if(existingChannels.stream().filter(x->x.getPublicationChannel().equals(newChannel)).count() == 0){
-// TblFailureInformationPublicationChannel tblFailureInformationPublicationChannel = new TblFailureInformationPublicationChannel();
-// tblFailureInformationPublicationChannel.setTblFailureInformation(existingTblFailureInformation);
-// tblFailureInformationPublicationChannel.setPublicationChannel(newChannel);
-// tblFailureInformationPublicationChannel.setPublished(false);
-//
-// existingChannels.add(tblFailureInformationPublicationChannel);
-// }
-// }
-// destTblFailureInformation.setPublicationChannelList(existingChannels);
-// }
-
private void setVersionNumber(TblFailureInformation tblFailureInformation){
List<HtblFailureInformation> hfailureList = histFailureInformationRepository.findByUuid(tblFailureInformation.getUuid());
Long lastVersion = hfailureList.stream().map(HtblFailureInformation::getVersionNumber).max(Comparator.naturalOrder()).orElse(0L) ;
@@ -439,7 +409,7 @@
return enrichFailureInfo(condenseFailureInfos(subordinatedUuidList, Optional.of(condensedUuid)));
}
- private boolean containsAlienSubordinatedFailureInfos(UUID condensedUuid, List<UUID> subordinatedUuidList){
+ protected boolean containsAlienSubordinatedFailureInfos(UUID condensedUuid, List<UUID> subordinatedUuidList){
List<TblFailureInformation> futureSubordinateFailureInfoList = failureInformationRepository
.findByUuidIn(subordinatedUuidList);
diff --git a/gfsBackendService/src/main/resources/db/migration/V0_43__CREATE_GFI_DB.sql b/gfsBackendService/src/main/resources/db/migration/V0_43__CREATE_GFI_DB.sql
new file mode 100644
index 0000000..9046706
--- /dev/null
+++ b/gfsBackendService/src/main/resources/db/migration/V0_43__CREATE_GFI_DB.sql
@@ -0,0 +1,912 @@
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * 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');
+
+-- ---------------------------------------------
+-- 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,
+ 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 ('6432a9c9-0384-44af-9bb8-34f2878d7b49',3, 1, 'Stromausfall Murr', 'Es gibt einen Stromausfall im Bereich Murr/Westbezirk', 'Rolf Rudis', 1, 1,'NS', null, '2021-01-19 00:00:00', '2021-01-22 00:00:00', '2021-01-22 12:00:00', 'Der Bagger grub zu tief', '71711', 'Murr', 'Westbezirk', 'Ferdinand-Porsche-Straße', '2', '52863a', 'Trafo 1', '124,2323', 2, 9.247952, 48.955700, 'Link - extenes System', 'veröffentlicht', 'Freitext für die Veröffentlichung: Defekt an Trafostation', 1, false, 0, null, '2020-01-22 15:32:15', 'SCRIPT', '2020-01-24 08:02:44', 'SCRIPT');
+
+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 character varying(2048),
+ 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)
+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:
+
+$directFailureLink$
+
+Mit freundlichen Grüßen
+Ihre Admin-Meister-Team der PTA GmbH');
+
+INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT)
+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:
+
+$directFailureLink$
+
+Mit freundlichen Grüßen
+Ihre Admin-Meister-Team der PTA GmbH');
+
+INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT)
+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:
+
+$directFailureLink$
+
+Mit freundlichen Grüßen');
+
+-- ---------------------------------------------
+-- 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,
+ 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);
+INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP)
+VALUES(2, 1);
+INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP)
+VALUES(2, 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;
+
+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,
+ 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, '6432a9c9-0384-44af-9bb8-34f2878d7b49', 1, 1, 'Stromausfall Murr', 'Es gibt einen Stromausfall im Bereich Murr/Westbezirk', 'Rolf Rudis', 1, 1, 'NS', null, '2021-01-19 00:00:00', '2021-01-22 00:00:00', '2021-01-22 12:00:00', 'Der Bagger grub zu tief', '71711', 'Murr', 'Westbezirk', 'Ferdinand-Porsche-Straße', '2', '52863a', 'Trafo 1', '124,2323', 2, 9.247952, 48.955700, 'Link - extenes System', 'veröffentlicht', 'Freitext für die Veröffentlichung: Defekt an Trafostation', 1, null, false, 0, '2020-01-22 15:32:15', 'SCRIPT', '2020-01-24 08:02:44', 'SCRIPT');
+
+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 (2, '2020-03-13 00:00:00', 'SCRIPT', 2, '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');
+
+
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- Tables ADDRESSIMPORT
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+
+/*DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;*/
+
+-- ---------------------------------------------
+-- TABLE TBL_ADDRESS
+-- ---------------------------------------------
+/*CREATE SEQUENCE public.TBL_ADDRESS_ID_SEQ
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.TBL_ADDRESS_ID_SEQ
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.TBL_ADDRESS
+(
+ ID integer NOT NULL DEFAULT nextval('TBL_ADDRESS_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ SDOX1 numeric(9,2) NOT NULL,
+ SDOY1 numeric(10,2) NOT NULL,
+ G3EFID numeric,
+ POSTCODE character varying(30),
+ COMMUNITY character varying(255),
+ DISTRICT character varying(255),
+ STREET character varying(255),
+ HOUSENUMBER character varying(30),
+ WATER_CONNECTION boolean,
+ WATER_GROUP character varying(255),
+ GAS_CONNECTION boolean,
+ GAS_GROUP character varying(255),
+ POWER_CONNECTION boolean,
+ STATION_ID character varying(30),
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.TBL_ADDRESS
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.TBL_ADDRESS TO GFI_SERVICE;
+
+CREATE INDEX idx_tbl_address_g3efid ON public.TBL_ADDRESS ( G3EFID );
+CREATE INDEX idx_tbl_address_postcode ON public.TBL_ADDRESS ( POSTCODE );
+CREATE INDEX idx_tbl_address_community ON public.TBL_ADDRESS ( COMMUNITY );
+CREATE INDEX idx_tbl_address_district ON public.TBL_ADDRESS ( DISTRICT );
+CREATE INDEX idx_tbl_address_street ON public.TBL_ADDRESS ( STREET );
+CREATE INDEX idx_tbl_address_station_id ON public.TBL_ADDRESS ( STATION_ID );*/
+
+
+-- ---------------------------------------------
+-- TABLE TBL_STATION
+-- ---------------------------------------------
+
+/*DROP TABLE IF EXISTS public.TBL_STATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_STATION_ID_SEQ;
+
+CREATE SEQUENCE public.TBL_STATION_ID_SEQ
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.TBL_STATION_ID_SEQ
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.TBL_STATION
+(
+ ID integer NOT NULL DEFAULT nextval('TBL_STATION_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ SDOX1 numeric(9,2) NOT NULL,
+ SDOY1 numeric(10,2) NOT NULL,
+ G3EFID numeric,
+ STATION_ID character varying(30),
+ STATION_NAME character varying(255),
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ CONSTRAINT TBL_STATION_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.TBL_STATION
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.TBL_STATION TO GFI_SERVICE;
+
+CREATE INDEX idx_tbl_station_g3efid ON public.TBL_STATION ( G3EFID );
+CREATE INDEX idx_tbl_station_station_id ON public.TBL_STATION ( STATION_ID );
+CREATE INDEX idx_tbl_station_uuid ON public.TBL_STATION ( UUID );
+*/
+
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- 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,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.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,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.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,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.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
+-- ---------------------------------------------
+
+-- tbl_address
+
+INSERT INTO public.tbl_address
+(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES
+('0bb024ea-8974-4c39-a167-8c430cd09db9', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.351669311523438, 49.63756523757894);
+
+INSERT INTO public.tbl_address
+(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES
+('6669f4b1-1731-4b9e-8584-38fea6bd9b8a', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.336992263793945, 49.62066424962382);
+
+
+INSERT INTO public.tbl_address
+(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES
+('c0a7f3c5-9b47-4214-b212-c76c4543314e', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.379220962524414, 49.62233237106394);
+
+INSERT INTO public.tbl_address
+(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES
+('93bb8dcb-a72c-472f-a570-09a4be00c2af', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.372011184692383, 49.64173398743793);
+
+
+INSERT INTO public.tbl_address
+(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES
+('dd9f4ce3-4caa-4a7a-ab96-76475ba3cbc9', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.351669311523438, 49.63756523757894);
+
+
+-- tbl_station
+
+INSERT INTO public.tbl_station
+(uuid, sdox1, sdoy1, g3efid, station_id, station_name, longitude, latitude) VALUES
+('c155a74e-1882-4fa9-a96b-76e313671618', 487484.68, 5527339.19, 5711305, '111001', 'Test Polygon Worms', 8.351669311523438, 49.63756523758894);
+
diff --git a/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/ExportServiceTest.java b/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/ExportServiceTest.java
index 884d241..2a739df 100644
--- a/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/ExportServiceTest.java
+++ b/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/ExportServiceTest.java
@@ -18,6 +18,7 @@
import org.eclipse.openk.gridfailureinformation.config.TestConfiguration;
import org.eclipse.openk.gridfailureinformation.config.rabbitMq.RabbitMqChannel;
import org.eclipse.openk.gridfailureinformation.config.rabbitMq.RabbitMqProperties;
+import org.eclipse.openk.gridfailureinformation.constants.Constants;
import org.eclipse.openk.gridfailureinformation.exceptions.NotFoundException;
import org.eclipse.openk.gridfailureinformation.config.rabbitMq.RabbitMqConfig;
import org.eclipse.openk.gridfailureinformation.mapper.FailureInformationMapper;
@@ -47,10 +48,14 @@
import org.springframework.messaging.MessageChannel;
import org.springframework.test.context.ContextConfiguration;
+import java.math.BigDecimal;
+import java.sql.Date;
+import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.UUID;
+import java.util.stream.Collectors;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertThrows;
@@ -133,6 +138,43 @@
}
@Test
+ public void shouldExportFailureInfoAsMail_emptyFieldsForContentReplace() throws Exception {
+
+ ExportService exportServiceLocal = spy(exportService);
+
+ RabbitMqChannel rabbitMqChannel = MockDataHelper.mockRabbitMqChannelMail();
+ TblFailureInformation tblFailureInformation = MockDataHelper.mockTblFailureInformation();
+ tblFailureInformation.setResponsibility(null);
+ tblFailureInformation.setVoltageLevel(null);
+ tblFailureInformation.setPressureLevel(null);
+ tblFailureInformation.setFailureBegin(null);
+ tblFailureInformation.setFailureEndPlanned(null);
+ tblFailureInformation.setFailureEndResupplied(null);
+ tblFailureInformation.setStreet(null);
+ tblFailureInformation.setDistrict(null);
+ tblFailureInformation.setCity(null);
+ tblFailureInformation.setRefExpectedReason(null);
+
+ List<TblDistributionGroup> listTblDistributionGroups= MockDataHelper.mockDistributionGroupList();
+ tblFailureInformation.setDistributionGroups(listTblDistributionGroups);
+ List<DistributionGroupMemberDto> listTblDistributionGroupMemberDtos = MockDataHelper.mockDistributionGroupMemberDtoList();
+
+ when (failureInformationRepository.findByUuid( any(UUID.class))).thenReturn(Optional.of(tblFailureInformation));
+ when(distributionGroupMemberService.getMembersByGroupId(any(UUID.class))).thenReturn(listTblDistributionGroupMemberDtos);
+ when(publicationChannelRepository.save(any(TblFailureInformationPublicationChannel.class))).thenReturn(new TblFailureInformationPublicationChannel());
+ doNothing().when(rabbitMqConfig).checkExchangeAndQueueOnRabbitMq(anyString(), anyString());
+ doReturn(rabbitMqChannel).when(exportServiceLocal).getAvailableRabbitMqChannel(anyString());
+
+ String[] channels = {"Mail"};
+
+ boolean isMailPushed = exportServiceLocal.exportFailureInformation(UUID.randomUUID(), channels);
+
+ Mockito.verify(rabbitTemplate, times(2)).convertAndSend(any(), any(), any(Object.class));
+ assertTrue(isMailPushed);
+
+ }
+
+ @Test
public void shouldExportFailureInfoToStoerungsauskunft() throws Exception {
ExportService exportServiceLocal = spy(exportService);
diff --git a/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationServiceTest.java b/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationServiceTest.java
index ac8bb02..fd1368e 100644
--- a/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationServiceTest.java
+++ b/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/service/FailureInformationServiceTest.java
@@ -31,6 +31,7 @@
import org.junit.jupiter.api.Test;
import org.mockito.Mockito;
import org.mockito.stubbing.Answer;
+import org.mockito.stubbing.OngoingStubbing;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
@@ -43,6 +44,7 @@
import java.util.*;
import static org.junit.jupiter.api.Assertions.*;
+import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.mockito.ArgumentMatchers.*;
import static org.mockito.Mockito.times;
import static org.mockito.Mockito.when;
@@ -111,6 +113,27 @@
}
@Test
+ public void shouldFindASingleFailureInformationWithoutExternalStatus_NoStartdateNoEnddate() {
+ TblFailureInformation mockFailure = MockDataHelper.mockTblFailureInformation();
+ mockFailure.setFailureBegin(null);
+ mockFailure.setFailureEndPlanned(null);
+ TblStation tblStation = MockDataHelper.mockTblStation();
+ List<TblAddress> addressList = MockDataHelper.mockTblAddressList();
+ RefStatus refStatus = MockDataHelper.mockRefStatusCreated();
+
+ when(statusRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(refStatus));
+ when(failureInformationRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(mockFailure));
+ when(stationRepository.findByStationId(anyString())).thenReturn(Optional.of(tblStation));
+ when(addressRepository.findByStationId(anyString())).thenReturn(addressList);
+
+ FailureInformationDto retDto = failureInformationService.findFailureInformation(UUID.randomUUID());
+
+ assertEquals( mockFailure.getResponsibility(), retDto.getResponsibility() );
+ assertEquals( mockFailure.getUuid(), retDto.getUuid());
+ assertEquals("", retDto.getStatusExtern());
+ }
+
+ @Test
public void shouldThrowExceptionWhenFailureNotFound() {
when(failureInformationRepository.findByUuid(any(UUID.class))).thenReturn(Optional.empty());
assertThrows(NotFoundException.class,
@@ -192,7 +215,6 @@
fiDto.setBranchId(null);
fiDto.setFailureClassificationId(null);
fiDto.setFailureTypeId(null);
-// fiDto.setStatusExternId(null);
fiDto.setStatusInternId(UUID.randomUUID());
fiDto.setRadiusId(null);
fiDto.setExpectedReasonId(null);
@@ -203,6 +225,8 @@
fiTbl.setRefFailureClassification(null);
fiTbl.setRefRadius(null);
fiTbl.setRefExpectedReason(null);
+ fiTbl.setDistributionGroups(null);
+ fiTbl.setStationId(null);
when(statusRepository.findByUuid(any( UUID.class ))).thenReturn(Optional.of(refStatus));
when(failureInformationRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(fiTbl));
@@ -424,7 +448,7 @@
}
@Test
- public void shouldInsertFailureInformation() {
+ public void shouldStoreNewFailureInformation() {
FailureInformationDto fiDto = MockDataHelper.mockFailureInformationDto();
TblFailureInformation fiTbl = MockDataHelper.mockTblFailureInformation();
RefFailureClassification refFailureClassification = MockDataHelper.mockRefFailureClassification();
@@ -488,6 +512,70 @@
assertEquals(refExpectedReason.getText(), savedDto.getExpectedReasonText());
}
+ @Test
+ public void shouldInsertNewFailureInformation() {
+ FailureInformationDto fiDto = MockDataHelper.mockFailureInformationDto();
+ TblFailureInformation fiTbl = MockDataHelper.mockTblFailureInformation();
+ RefFailureClassification refFailureClassification = MockDataHelper.mockRefFailureClassification();
+ RefStatus refStatus = MockDataHelper.mockRefStatusCreated();
+ RefBranch refBranch = MockDataHelper.mockRefBranch();
+ RefRadius refRadius = MockDataHelper.mockRefRadius();
+ RefExpectedReason refExpectedReason = MockDataHelper.mockRefExpectedReason();
+ TblStation tblStation = MockDataHelper.mockTblStation();
+ TblDistributionGroup tblDistributionGroup = MockDataHelper.mockTblDistributionGroup();
+ List<TblFailureInformationPublicationChannel> publicationChannelList = MockDataHelper.mockTblFailureInformationPublicationChannelList();
+
+ when(statusRepository.findById(anyLong())).thenReturn(Optional.of(refStatus));
+ when(failureInformationRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(fiTbl), Optional.of(fiTbl));
+ when(branchRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(refBranch));
+ when(failureClassificationRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(refFailureClassification));
+ when(statusRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(refStatus));
+ when(radiusRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(refRadius));
+ when(expectedReasonRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(refExpectedReason));
+ when(stationRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(tblStation));
+ when(distributionGroupRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(tblDistributionGroup));
+ when(failureInformationPublicationChannelRepository.findByTblFailureInformation(any(TblFailureInformation.class))).thenReturn(publicationChannelList);
+
+ fiDto.setUuid(null);
+
+ when(failureInformationRepository.save(any(TblFailureInformation.class)))
+ .then((Answer<TblFailureInformation>) invocation -> {
+ Object[] args = invocation.getArguments();
+ return (TblFailureInformation) args[0];
+ });
+
+ FailureInformationDto savedDto = failureInformationService.insertFailureInfo(fiDto, GfiProcessState.NEW);
+
+ assertNotNull(savedDto.getUuid());
+ assertEquals(1L, savedDto.getVersionNumber());
+ assertEquals(fiDto.getResponsibility(), savedDto.getResponsibility());
+ assertEquals(fiDto.getVoltageLevel(), savedDto.getVoltageLevel());
+ assertEquals(fiDto.getPressureLevel(), savedDto.getPressureLevel());
+ assertEquals(fiDto.getFailureBegin(), savedDto.getFailureBegin());
+ assertEquals(fiDto.getFailureEndPlanned(), savedDto.getFailureEndPlanned());
+ assertEquals(fiDto.getFailureEndResupplied(), savedDto.getFailureEndResupplied());
+ assertEquals(fiDto.getDescription(), savedDto.getDescription());
+ assertEquals(fiDto.getPostcode(), savedDto.getPostcode());
+ assertEquals(fiDto.getCity(), savedDto.getCity());
+ assertEquals(fiDto.getDistrict(), savedDto.getDistrict());
+ assertEquals(fiDto.getStreet(), savedDto.getStreet());
+ assertEquals(fiDto.getHousenumber(), savedDto.getHousenumber());
+ assertEquals(fiDto.getStationDescription(), savedDto.getStationDescription());
+ assertEquals(fiDto.getStationCoords(), savedDto.getStationCoords());
+ assertEquals(fiTbl.getLongitude(), savedDto.getLongitude());
+ assertEquals(fiTbl.getLatitude(), savedDto.getLatitude());
+ assertEquals(refFailureClassification.getUuid(), savedDto.getFailureClassificationId());
+ assertEquals(refFailureClassification.getClassification(), savedDto.getFailureClassification());
+ assertEquals(refStatus.getStatus(), savedDto.getStatusIntern());
+ assertEquals(refBranch.getUuid(), savedDto.getBranchId());
+ assertEquals(refBranch.getName(), savedDto.getBranch());
+ assertEquals(refBranch.getColorCode(), savedDto.getBranchColorCode());
+ assertEquals(refRadius.getUuid(), savedDto.getRadiusId());
+ assertEquals(refRadius.getRadius(), savedDto.getRadius());
+ assertEquals(refExpectedReason.getUuid(), savedDto.getExpectedReasonId());
+ assertEquals(refExpectedReason.getText(), savedDto.getExpectedReasonText());
+ }
+
@Test
public void shouldNotCondenseFailureInformations_DifferentBranches() {
@@ -718,6 +806,26 @@
}
@Test
+ public void shouldNotUpdateSubordinateFailureInfos_containsAlienSubordinatedFis(){
+
+ UUID uuid = UUID.randomUUID();
+ TblFailureInformation fi = MockDataHelper.mockTblFailureInformation();
+ when(failureInformationRepository.findByUuid(any(UUID.class))).thenReturn(Optional.of(fi));
+
+ List<TblFailureInformation> fiMockList = MockDataHelper.mockTblFailureInformationList();
+ when(failureInformationRepository.findByFailureInformationCondensedUuid(any(UUID.class))).thenReturn(fiMockList);
+
+ List<TblFailureInformation> subordinatedFiList = MockDataHelper.mockTblFailureInformationList();
+ TblFailureInformation tblFailureInformation = MockDataHelper.mockTblFailureInformation();
+ subordinatedFiList.get(0).setTblFailureInformationCondensed(tblFailureInformation);
+
+ when(failureInformationRepository.findByUuidIn(anyList())).thenReturn(subordinatedFiList);
+
+ List<UUID> subordinatedFiUuidList = MockDataHelper.mockUuidList();
+ assertThrows(BadRequestException.class, () -> failureInformationService.updateSubordinatedFailureInfos(tblFailureInformation.getUuid(), subordinatedFiUuidList));
+ }
+
+ @Test
public void shouldInsertPublicationChannelForFailureInfo(){
TblFailureInformation tblFailureInformation = MockDataHelper.mockTblFailureInformation();
@@ -850,4 +958,26 @@
}
+ @Test
+ public void shouldFindByObjectReferenceExternalSystem(){
+ TblFailureInformation tblFailureInformation = MockDataHelper.mockTblFailureInformation();
+ when( failureInformationRepository.findByObjectReferenceExternalSystem( anyString() )).thenReturn(Optional.of(tblFailureInformation));
+
+ FailureInformationDto existingFi = failureInformationService.findByObjectReferenceExternalSystem("test");
+
+ assertEquals(existingFi.getUuid(), tblFailureInformation.getUuid());
+ }
+
+ @Test
+ public void shouldNotFindByObjectReferenceExternalSystem(){
+ TblFailureInformation tblFailureInformation = MockDataHelper.mockTblFailureInformation();
+ when( failureInformationRepository.findByObjectReferenceExternalSystem( anyString() )).thenReturn(Optional.empty());
+
+ FailureInformationDto existingFi = failureInformationService.findByObjectReferenceExternalSystem("test");
+
+ assertEquals(null, existingFi);
+ }
+
+
+
}
diff --git a/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/util/ExternalStatusCalculatorTest.java b/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/util/ExternalStatusCalculatorTest.java
index 0e35370..e73806a 100644
--- a/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/util/ExternalStatusCalculatorTest.java
+++ b/gfsBackendService/src/test/java/org/eclipse/openk/gridfailureinformation/util/ExternalStatusCalculatorTest.java
@@ -49,6 +49,8 @@
assertEquals( "", ExternalStatusCalculator.calculate(NEW.getStatusValue(), "any", inPast, null ));
+ assertEquals( "", ExternalStatusCalculator.calculate(QUALIFIED.getStatusValue(), "any", null, null ));
+
}
}
