BP-732 changed query for better performance
diff --git a/oKBereitschaftsplanungBackend/src/main/asciidoc/arch/src/09_design_decisions.adoc b/oKBereitschaftsplanungBackend/src/main/asciidoc/arch/src/09_design_decisions.adoc
index b6d0734..68d9961 100644
--- a/oKBereitschaftsplanungBackend/src/main/asciidoc/arch/src/09_design_decisions.adoc
+++ b/oKBereitschaftsplanungBackend/src/main/asciidoc/arch/src/09_design_decisions.adoc
@@ -68,7 +68,7 @@
|2019-01-17|Integrity of planning data |The product owner and the team of developers decided to use database shadow tables. Theses tables saves copies of manipulated data rows. This mechanism works outside of the application directly in the database. There are no interfaces implemented between the shadow tables and the application. That design decision make sure that no-one from outside of the database, from the StandbyPlanning backend to the frontend, can change existing data in the history tables.
-|2019-01-29|BIRT as reporting framework | We decided to use BIRT as reporting framework, while the IP-Check for that library works on Eclipse. The alternative JASPER REPORTS has no valid IP-Check on Eclipse.
+|2019-01-29|BIRT as reporting framework | We decided to use BIRT as reporting framework, while the IP-Check for that library works on Eclipse. The alternative JASPER REPORTS has no valid IP-Check on Eclipse. There were requirements that were covered by a report engine. BIRT offered these functionalities. With this it is possible to make adaptations on the customer side.
|2019-01-29|Camunda BPM not working| Unfortunately we can not use the Camunda engine. While the IP-Check on Eclipse dose not work for that engine, we decided to dismount the Camunda engine on this project.
diff --git a/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/controller/planning/PlannedDataController.java b/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/controller/planning/PlannedDataController.java
index cf72b80..3c4ee5e 100644
--- a/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/controller/planning/PlannedDataController.java
+++ b/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/controller/planning/PlannedDataController.java
@@ -15,6 +15,7 @@
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
+import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;
@@ -118,18 +119,36 @@
List<List<StandbyScheduleBodySelectionDto>> listGroupBodies = new ArrayList<>();
dto.setListGroupBodys(listGroupBodies);
- for (int j = 0; j < planHeader.getListGroups().size(); j++) {
+ HashMap<Long, List<StandbyScheduleBodySelectionDto>> dayGroupMap = new HashMap<>();
- StandbyGroupSelectionDto standbyGroup = planHeader.getListGroups().get(j);
- List<StandbyScheduleBody> listBody = getPlannedBodysForGroupAndDayAndStatus(standbyGroup, dateIndex,
- statusId);
+ // init ArrayLists for this day row
+ for (int i = 0; i < listStandbyGroupDto.size(); i++) {
+ StandbyGroupSelectionDto standbyGroupSelectionDto = listStandbyGroupDto.get(i);
List<StandbyScheduleBodySelectionDto> listBodyDto = new ArrayList<>();
-
- listBodyDto = entityConverter.convertEntityToDtoList(listBody, listBodyDto,
- StandbyScheduleBodySelectionDto.class);
-
- Collections.sort(listBodyDto, (o1, o2) -> o1.getValidFrom().compareTo(o2.getValidFrom()));
listGroupBodies.add(listBodyDto);
+ dayGroupMap.put(standbyGroupSelectionDto.getId(), listBodyDto);
+ }
+
+ // query day row
+ List<StandbyScheduleBody> listBodyRow = rowOfPlannedBodysByDateAndStatus(dateIndex, dateIndex,
+ statusId);
+
+ for (StandbyScheduleBody standbyScheduleBody : listBodyRow) {
+
+ Long sbgId = standbyScheduleBody.getStandbyGroup().getId();
+
+ if (dayGroupMap.containsKey(sbgId)) {
+ List<StandbyScheduleBodySelectionDto> groupDayListOfStandbyBodys = dayGroupMap.get(sbgId);
+
+ StandbyScheduleBodySelectionDto standbyScheduleBodySelectionDto = (StandbyScheduleBodySelectionDto) entityConverter
+ .convertEntityToDto(standbyScheduleBody, new StandbyScheduleBodySelectionDto());
+ groupDayListOfStandbyBodys.add(standbyScheduleBodySelectionDto);
+
+ // sort
+ Collections.sort(groupDayListOfStandbyBodys,
+ (o1, o2) -> o1.getValidFrom().compareTo(o2.getValidFrom()));
+
+ }
}
dateIndex = DateHelper.addDaysToDate(dateIndex, 1);
@@ -397,4 +416,15 @@
}
+ public List<StandbyScheduleBody> rowOfPlannedBodysByDateAndStatus(Date validFrom, Date validTo, Long statusId) {
+
+ validFrom = DateHelper.getStartOfDay(validFrom);
+ validTo = DateHelper.getEndOfDay(validTo);
+ List<StandbyScheduleBody> listBodies = standbyScheduleBodyRepository.findRowByDateAndStatus(validFrom, validTo,
+ statusId);
+
+ return listBodies;
+
+ }
+
}
diff --git a/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/db/dao/StandbyScheduleBodyRepository.java b/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/db/dao/StandbyScheduleBodyRepository.java
index c71367c..82c204d 100644
--- a/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/db/dao/StandbyScheduleBodyRepository.java
+++ b/oKBereitschaftsplanungBackend/src/main/java/org/eclipse/openk/sp/db/dao/StandbyScheduleBodyRepository.java
@@ -34,18 +34,10 @@
List<StandbyScheduleBody> findByGroupAndDateAndStatus(@Param("groupId") Long groupId,
@Param("validFrom") Date validFrom, @Param("validTo") Date validTo, @Param("status") Long status);
- @Query(value = "SELECT l.id, l.valid_from, l.valid_to, l.standby_group_id, l.user_id, l.status_id FROM public.standby_schedule_body l "
- + "WHERE (l.standby_group_id = ?groupId "
- + "AND l.valid_from >= ?validFrom AND l.valid_to <= ?validTo AND l.status_id = ?status) "
- + "ORDER BY l.valid_from ASC;", nativeQuery = true)
- List<Object[]> findFastByGroupAndDateAndStatus(Long groupId, Date validFrom, Date validTo, Long status);
-
- @Query(value = "SELECT sbsb.id, sbsb.validFrom, sbsb.validTo FROM StandbyScheduleBody sbsb "
- + "WHERE (sbsb.standbyGroup.id = :groupId "
- + "AND sbsb.validFrom >= :validFrom AND sbsb.validTo <= :validTo AND sbsb.status.id = :status) "
- + "ORDER BY sbsb.validFrom ASC")
- List<StandbyScheduleBody> findFlatByGroupAndDateAndStatus(@Param("groupId") Long groupId,
- @Param("validFrom") Date validFrom, @Param("validTo") Date validTo, @Param("status") Long status);
+ @Query(value = "SELECT sbsb FROM StandbyScheduleBody sbsb "
+ + "WHERE (sbsb.validFrom >= :validFrom AND sbsb.validTo <= :validTo AND sbsb.status.id = :status) ")
+ List<StandbyScheduleBody> findRowByDateAndStatus(@Param("validFrom") Date validFrom, @Param("validTo") Date validTo,
+ @Param("status") Long status);
@Query(value = "SELECT sbsb FROM StandbyScheduleBody sbsb "
+ "WHERE (sbsb.user.id = :userId AND sbsb.standbyGroup.id = :groupId "
