Fixed Bug: Instructions not shown in Historical Resp Notification tables
diff --git a/db/oracle/02_create_DB_1.0.0.sql b/db/oracle/02_create_DB_1.0.0.sql
index 2c6445c..da0a764 100644
--- a/db/oracle/02_create_DB_1.0.0.sql
+++ b/db/oracle/02_create_DB_1.0.0.sql
@@ -315,30 +315,8 @@
WHERE S.RANK = 1;
-INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 1, 'Offen' );
-INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 2, 'In arbeit' );
-INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 3, 'Erledigt' );
-INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 4, 'Geschlossen' );
-
-INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 1, 'S', 'Strom' );
-INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 2, 'G', 'Gas' );
-INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 3, 'F', 'Fernwärme' );
-INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 4, 'W', 'Wasser' );
-
-INSERT INTO "REF_GRID_TERRITORY" ("ID", "NAME", "DESCRIPTION", "FK_REF_MASTER") VALUES ( 1, 'MA', 'Mannheim', 1);
-INSERT INTO "REF_GRID_TERRITORY" ("ID", "NAME", "DESCRIPTION", "FK_REF_MASTER") VALUES ( 2, 'OF', 'Offenbach', 2);
-
INSERT INTO REF_VERSION VALUES (1, '1.0.0_ORA');
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 1, 'Admin','Admin', CURRENT_TIMESTAMP);
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 2, 'Admin','Admin', CURRENT_TIMESTAMP);
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 3, 'Admin','Admin', CURRENT_TIMESTAMP);
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 4, 'Admin','Admin', CURRENT_TIMESTAMP);
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 1, 'Admin','Admin', CURRENT_TIMESTAMP);
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 2, 'Admin','Admin', CURRENT_TIMESTAMP);
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 3, 'Admin','Admin', CURRENT_TIMESTAMP);
-INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 4, 'Admin','Admin', CURRENT_TIMESTAMP);
-
diff --git a/db/oracle/03_config.DB_1.0.0.sql b/db/oracle/03_config.DB_1.0.0.sql
index 88ccbe1..525f9b5 100644
--- a/db/oracle/03_config.DB_1.0.0.sql
+++ b/db/oracle/03_config.DB_1.0.0.sql
@@ -1,4 +1,3 @@
-
INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 1, 'Offen' );
INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 2, 'In arbeit' );
INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 3, 'Erledigt' );
@@ -12,8 +11,6 @@
INSERT INTO "REF_GRID_TERRITORY" ("ID", "NAME", "DESCRIPTION", "FK_REF_MASTER") VALUES ( 1, 'MA', 'Mannheim', 1);
INSERT INTO "REF_GRID_TERRITORY" ("ID", "NAME", "DESCRIPTION", "FK_REF_MASTER") VALUES ( 2, 'OF', 'Offenbach', 2);
-INSERT INTO REF_VERSION VALUES (1, '1.0.0_ORA');
-
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 1, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 2, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 3, 'Admin','Admin', CURRENT_TIMESTAMP);
diff --git a/pom.xml b/pom.xml
index e3e4c3f..8e3aea7 100644
--- a/pom.xml
+++ b/pom.xml
@@ -89,6 +89,11 @@
<version>${commons-io.version}</version>
</dependency>
<dependency>
+ <groupId>com.google.collections</groupId>
+ <artifactId>google-collections</artifactId>
+ <version>1.0</version>
+ </dependency>
+ <dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>eclipselink</artifactId>
<version>2.6.4</version>
diff --git a/src/main/java/org/eclipse/openk/elogbook/common/Pair.java b/src/main/java/org/eclipse/openk/elogbook/common/Pair.java
new file mode 100644
index 0000000..e78b31f
--- /dev/null
+++ b/src/main/java/org/eclipse/openk/elogbook/common/Pair.java
@@ -0,0 +1,27 @@
+package org.eclipse.openk.elogbook.common;
+
+public class Pair <T1, T2> {
+ private T1 t1;
+ private T2 t2;
+
+ public Pair (T1 t1, T2 t2) {
+ this.t1 = t1;
+ this.t2 = t2;
+ }
+
+ public T1 getT1() {
+ return t1;
+ }
+
+ public void setT1(T1 t1) {
+ this.t1 = t1;
+ }
+
+ public T2 getT2() {
+ return t2;
+ }
+
+ public void setT2(T2 t2) {
+ this.t2 = t2;
+ }
+}
diff --git a/src/main/java/org/eclipse/openk/elogbook/persistence/util/NotificationQueryCreator.java b/src/main/java/org/eclipse/openk/elogbook/persistence/util/NotificationQueryCreator.java
index c3947f6..b98ab41 100644
--- a/src/main/java/org/eclipse/openk/elogbook/persistence/util/NotificationQueryCreator.java
+++ b/src/main/java/org/eclipse/openk/elogbook/persistence/util/NotificationQueryCreator.java
@@ -1,8 +1,10 @@
package org.eclipse.openk.elogbook.persistence.util;
+import com.google.common.collect.Lists;
import org.apache.log4j.Logger;
import org.eclipse.openk.elogbook.common.Globals;
import org.eclipse.openk.elogbook.common.NotificationStatus;
+import org.eclipse.openk.elogbook.common.Pair;
import org.eclipse.openk.elogbook.persistence.dao.EntityHelper;
import org.eclipse.openk.elogbook.persistence.model.HTblResponsibility;
import org.eclipse.openk.elogbook.persistence.model.TblNotification;
@@ -29,7 +31,7 @@
private static final String FK_BRANCH_NULL = FK_REF_BRANCH + IS_NULL;
private static final String FK_GRID_TERRITORY_NULL = FK_REF_GRID_TERRITORY+IS_NULL;
private static final String FK_REF_NOTIFICATION_STATUS = EntityHelper.instance().makeIdentifier("fk_ref_notification_status");
- private static final String DUMMY_FALSE_VALUE_ORCLAUSE = " (1=0) ";
+ private static final String DUMMY_TRUE_VALUE_ANDCLAUSE = " (1=1) ";
private static final String TBL_NOTIFICATION = EntityHelper.instance().makeIdentifier("TBL_NOTIFICATION");
private static final String VIEW_ACTIVE_NOTIFICATION = EntityHelper.instance().makeIdentifier("VIEW_ACTIVE_NOTIFICATION");
private static final String INCIDENT_ID = EntityHelper.instance().makeIdentifier("incident_id");
@@ -131,12 +133,11 @@
paramList.add(hTblResponsibilities.get(0).getTransferDate());
paramList.add(hTblResponsibilities.get(0).getTransferDate());
- sqlSB.append("(").append(DUMMY_FALSE_VALUE_ORCLAUSE);
- for (HTblResponsibility hTblResponsibility : hTblResponsibilities) {
- sqlSB.append(OR_LIT);
- sqlSB.append(FK_BRANCH_EQUAL).append(hTblResponsibility.getRefBranch().getId()).append(AND_LIT)
- .append(FK_GRID_TERRITORY_EQUAL).append(hTblResponsibility.getRefGridTerritory().getId());
- }
+ sqlSB.append("(");
+ sqlSB.append(DUMMY_TRUE_VALUE_ANDCLAUSE);
+ sqlSB.append( extendWhereClauseByResponsibility(
+ Lists.transform(hTblResponsibilities, this::convertHRespToPair)));
+
sqlSB.append(")");
extendFindHistoricalNotificationsByResponsibilityQueryListTypeSpecific(sqlSB, listType,
hTblResponsibilities.get(0).getTransferDate(), paramList);
@@ -324,7 +325,7 @@
ret.append(extendWhereClauseByDates(nsf));
- ret.append(extendWhereClauseByResponsibility(tblResponsibilities));
+ ret.append(extendWhereClauseByResponsibility(Lists.transform(tblResponsibilities, this::convertRespToPair)));
return ret.toString();
}
@@ -334,10 +335,20 @@
ret.append(extendWhereClauseByReminderDate(rsf));
- ret.append(extendWhereClauseByResponsibility(tblResponsibilities));
+ ret.append(extendWhereClauseByResponsibility(Lists.transform(tblResponsibilities, this::convertRespToPair)));
return ret.toString();
}
+ private Pair<Integer, Integer> convertRespToPair( TblResponsibility tblResp ) {
+ return new Pair<> (tblResp.getRefBranch().getId(),
+ tblResp.getRefGridTerritory().getFkRefMaster());
+ }
+
+ private Pair<Integer, Integer> convertHRespToPair( HTblResponsibility tblResp ) {
+ return new Pair<> (tblResp.getRefBranch().getId(),
+ tblResp.getRefGridTerritory().getFkRefMaster());
+ }
+
/**
* Use DateFrom and DateTo in where clause.
*
@@ -375,25 +386,26 @@
* for matching. Null value matches all branches/grid territories. In case of empty responsibilities entity only the
* notifications for all branches and grid territories are returned.
*
- * @param tblResponsibilities
- * the list with the {@link TblResponsibility} objects
+ * @param branchGridMasterList
+ * the pair-list: first item=branchID, 2nd item=gridTerritoryMasterId
* @return the appropriate String to extend the where-clause
*/
- private String extendWhereClauseByResponsibility(List<TblResponsibility> tblResponsibilities) {
+ private String extendWhereClauseByResponsibility(List<Pair<Integer, Integer>> branchGridMasterList) {
StringBuilder stringBuilder = new StringBuilder(AND_LIT).append("(").append(FK_BRANCH_NULL).append(AND_LIT)
.append(FK_GRID_TERRITORY_NULL);
- if (tblResponsibilities.isEmpty()) {
+ if (branchGridMasterList.isEmpty()) {
stringBuilder.append(")");
return stringBuilder.toString();
}
stringBuilder.append(OR_LIT).append("(");
int actualListPos = 0;
- for (TblResponsibility tblResponsibility : tblResponsibilities) {
- stringBuilder.append(appendForeignKeyNullOrMatching(tblResponsibility));
+ for (Pair<Integer, Integer> pair : branchGridMasterList) {
+ stringBuilder.append(appendForeignKeyNullOrMatching(pair.getT1(),
+ pair.getT2()));
actualListPos++;
- if (actualListPos < tblResponsibilities.size()) {
+ if (actualListPos < branchGridMasterList.size()) {
stringBuilder.append(OR_LIT);
}
}
@@ -406,17 +418,19 @@
*
* Append to where clause: (fk_ref_branch IS NULL OR fk_ref_branch = x) AND (fk_grid_territory IS NULL OR
* fk_ref_grid_territory = y)
- *
- * @param tblResponsibility
- * the responsibility with the foreign keys to check matching
+ *
+ * @param branchId
+ * the branchId for matching
+ * @param refGridTerritoryMasterId
+ * the gridTerritoryId (Master) to check matching
* @return the extended where clause
*/
- private String appendForeignKeyNullOrMatching(TblResponsibility tblResponsibility) {
+ private String appendForeignKeyNullOrMatching(Integer branchId, Integer refGridTerritoryMasterId) {
StringBuilder stringBuilder = new StringBuilder("(");
stringBuilder.append(FK_BRANCH_NULL).append(OR_LIT).append(FK_BRANCH_EQUAL)
- .append(tblResponsibility.getRefBranch().getId()).append(")").append(AND_LIT).append("(")
+ .append(branchId).append(")").append(AND_LIT).append("(")
.append(FK_GRID_TERRITORY_NULL).append(OR_LIT).append(FK_GRID_TERRITORY_EQUAL)
- .append(tblResponsibility.getRefGridTerritory().getFkRefMaster()).append(")");
+ .append(refGridTerritoryMasterId).append(")");
return stringBuilder.toString();
}
