src/main/java/org/eclipse/openk/elogbook/persistence/util/NotificationQueryCreator.java - openk-usermodules/org.eclipse.openk-usermodules.elogbook - Git at Google

 package org.eclipse.openk.elogbook.persistence.util;

 import org.apache.log4j.Logger;
 import org.eclipse.openk.elogbook.common.Globals;
 import org.eclipse.openk.elogbook.common.NotificationStatus;
 import org.eclipse.openk.elogbook.persistence.dao.EntityHelper;
 import org.eclipse.openk.elogbook.persistence.model.HTblResponsibility;
 import org.eclipse.openk.elogbook.persistence.model.TblNotification;
 import org.eclipse.openk.elogbook.persistence.model.TblResponsibility;
 import org.eclipse.openk.elogbook.viewmodel.GlobalSearchFilter;
 import org.eclipse.openk.elogbook.viewmodel.Notification.ListType;
 import org.eclipse.openk.elogbook.viewmodel.NotificationSearchFilter;
 import org.eclipse.openk.elogbook.viewmodel.ReminderSearchFilter;

 import javax.persistence.EntityManager;
 import javax.persistence.Query;
 import java.sql.Timestamp;
 import java.util.*;

 public class NotificationQueryCreator {
 	private static final String AND_LIT = " AND ";
 	private static final String OR_LIT = " OR ";
 	private static final String ORDER_LIT = " ORDER BY ";
 	private static final String IS_NULL = " IS NULL";
 	private static final String FK_REF_BRANCH = EntityHelper.instance().makeIdentifier("fk_ref_branch");
 	private static final String FK_REF_GRID_TERRITORY = EntityHelper.instance().makeIdentifier("fk_ref_grid_territory");
 	private static final String FK_BRANCH_EQUAL = FK_REF_BRANCH + " = ";
 	private static final String FK_GRID_TERRITORY_EQUAL = FK_REF_GRID_TERRITORY+" = ";
 	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 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");
 	private static final String VERSION = EntityHelper.instance().makeIdentifier("version");
 	private static final String BEGIN_DATE = EntityHelper.instance().makeIdentifier("begin_date");
 	private static final String MOD_DATE = EntityHelper.instance().makeIdentifier("mod_date");
 	private static final String CREATE_DATE = EntityHelper.instance().makeIdentifier("create_date");
 	private static final String REMINDER_DATE = EntityHelper.instance().makeIdentifier("reminder_date");


 	private static final Logger LOGGER = Logger.getLogger(NotificationQueryCreator.class.getName());

 	private EntityManager em;
 	private String tablePrefix = "";
 	private Map<Integer, Object> latebindParamMap = new HashMap<>();
 	private int iCounter = 1;

 	public NotificationQueryCreator(EntityManager em, String tablePrefix ) {
 		this.em = em;
 		if (tablePrefix != null && !tablePrefix.isEmpty()) {
 			this.tablePrefix = tablePrefix + ".";
 		}
 	}

 	/**
 	 * Generate the Query by processing the informations from the notification search-filter.
 	 *
 	 * @param notificationSearchFilter
 	 *            The Object obtained from view model
 	 * @param baseWhereClause
 	 *            the base where clause to be completed using the notification search filter
 	 * @param listType
 	 *            the listType (where clause creation depends from it)
 	 * @param tblResponsibilities
 	 *            the list with the {@link TblResponsibility} objects
 	 * @return the generated query
 	 */
 	public Query generateNotificationQuery(NotificationSearchFilter notificationSearchFilter, String baseWhereClause,
 			List<TblResponsibility> tblResponsibilities) {

 		StringBuilder sql = new StringBuilder("select * from "+VIEW_ACTIVE_NOTIFICATION+" v where 1=1 "); // NOSONAR
 		if (baseWhereClause != null && !baseWhereClause.isEmpty()) {
 			sql.append(AND_LIT).append(baseWhereClause);
 		}
 		if (notificationSearchFilter != null) {
 			sql.append(extendWhereClauseApplyingSearchFilter(notificationSearchFilter, tblResponsibilities));
 		}
 		sql.append(getNotificationOrder());
 		// _fd: No String comes unescaped from outside the program -> So SONAR
 		// is wrong with it error
 		Query q = em.createNativeQuery(sql.toString(), TblNotification.class); // NOSONAR
 		latebindParamMap.forEach(q::setParameter);
 		return q;
 	}

 	public Query generateNotificationQueryWithReminder(ReminderSearchFilter rsf, String baseWhereClause,
 			List<TblResponsibility> tblResponsibilities) {

 		StringBuilder sql = new StringBuilder("select * from "+VIEW_ACTIVE_NOTIFICATION+" v where 1=1 "); // NOSONAR
 		if (baseWhereClause != null && !baseWhereClause.isEmpty()) {
 			sql.append(AND_LIT).append(baseWhereClause);
 		}
 		if (rsf != null) {
 			sql.append(extendWhereClauseApplyingSearchFilterWithReminder(rsf, tblResponsibilities));
 		}
 		sql.append(getNotificationOrder());

 		// _fd: No String comes unescaped from outside the program -> So SONAR
 		// is wrong with it error
 		Query q = em.createNativeQuery(sql.toString(), TblNotification.class); // NOSONAR
 		latebindParamMap.forEach(q::setParameter);
 		return q;
 	}

 	/**
 	 * Create a query to get historical notifications relevant for the responsibility belonging to a specific
 	 * transaction id .
 	 *
 	 * @param hTblResponsibilities
 	 *            the list of historical responsibilities at shift change date
 	 * @param listType
 	 *            the {@link ListType}
 	 * @return the generated query.
 	 */
 	public Query generateFindHistoricalNotificationsByResponsibilityQuery(List<HTblResponsibility> hTblResponsibilities,
 			ListType listType) {
 		List<Object> paramList = new LinkedList<>();
 		StringBuilder sqlSB = new StringBuilder("select * from " + TBL_NOTIFICATION + " t1 join ( "
 				+ " select "+INCIDENT_ID+ ", "
 				+ " max("+VERSION+") as VERSION "
 				+ " FROM "+TBL_NOTIFICATION+" t " + " WHERE ");
 		sqlSB.append("( "+MOD_DATE+" < ? ").append(OR_LIT)
 				.append(" "+MOD_DATE+" IS NULL").append(AND_LIT)
 				.append(" "+CREATE_DATE + " < ? ");
 		sqlSB.append(")");
 		sqlSB.append(" GROUP BY "+INCIDENT_ID);
 		sqlSB.append(" ) t2 ON ( t1."+INCIDENT_ID+" = t2."+INCIDENT_ID);
 		sqlSB.append(" AND t1."+VERSION+" = t2.VERSION) where ");

 		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(")");
 		extendFindHistoricalNotificationsByResponsibilityQueryListTypeSpecific(sqlSB, listType,
 				hTblResponsibilities.get(0).getTransferDate(), paramList);
 		sqlSB.append(getNotificationOrder());

 		Query query = em.createNativeQuery(sqlSB.toString(), TblNotification.class);
 		int i = 1;
 		for( Object o: paramList) {
 			query.setParameter(i++, o);
 		}
 		return query;
 	}

 	/**
 	 * Create a query by evaluating the search filter criteria.
 	 *
 	 * @param gsf
 	 *            the search filter containing the search criteria.
 	 * @return the generated query.
 	 */
 	public Query generateFindNotificationsMatchingSearchCriteriaQuery(GlobalSearchFilter gsf) {
 		String sql = createSearchCriteriaSelectString(gsf);
 		Query query = em.createQuery(sql, TblNotification.class);
 		substituteSearchCriteriaParameters(gsf, query);
 		return query;
 	}

 	/**
 	 * Helper method generating SQL SELECT for search.
 	 * @param gsf the search filter containing search criteria
 	 * @return The generated query string
 	 */
 	private String createSearchCriteriaSelectString(GlobalSearchFilter gsf) {
 		StringBuilder sqlSB = new StringBuilder("SELECT t FROM");
 		if (gsf.isFastSearchSelected()) {
 			sqlSB.append(" ViewNotification t WHERE 1 = 1 AND ");
 			sqlSB.append("(t.modDate > :timestamp or t.modDate IS NULL AND t.createDate > :timestamp) AND ");
 		}
 		else {
 			sqlSB.append(" TblNotification t WHERE 1 = 1 AND ");
 		}

 		if (gsf.getSearchString() != null) {
 			sqlSB.append("( UPPER(t.notificationText) LIKE :notificationText OR")
 				 .append(" UPPER(t.freeText) LIKE :freeText OR")
 				 .append(" UPPER(t.freeTextExtended) LIKE :freeTextExtended)");
 		}
 		if (gsf.getResponsibilityForwarding() != null && !gsf.getResponsibilityForwarding().isEmpty()) {
 			sqlSB.append(" AND t.responsibilityForwarding LIKE :responsibilityForwarding ");
 		}
 		if (gsf.isBranchSelected()) {
 			sqlSB.append(" AND t.refBranch.id = :refBranch ");
 		}
 		if (gsf.isGridTerritorySelected()) {
 			sqlSB.append(" AND t.refGridTerritory.id = :refGridTerritory ");
 		}
 		List<Integer> statusIds = gsf.getSelectedStatusMappedToIds();
 		if (!statusIds.isEmpty()) {
 			sqlSB.append(" AND t.refNotificationStatus.id IN ( ");
 			for (Integer status : statusIds) {
 				sqlSB.append(status.toString()).append(", ");
 			}
 			replaceLastCommaWithParanthese(sqlSB);
 		}
 		sqlSB.append(" ORDER BY t.refBranch, t.incidentId DESC, t.version DESC, t.refGridTerritory, t.beginDate ");
 		return sqlSB.toString();
 	}


 	/**
 	 * Helper method setting parameters to the generated search query.
 	 * @param gsf the search filter where the parameter values are taken from
 	 * @param query the generated query.
 	 */
 	private void substituteSearchCriteriaParameters(GlobalSearchFilter gsf, Query query) {
 		if (gsf.isFastSearchSelected()) {
 			query.setParameter("timestamp", createSearchIntervalParameter());
 		}
 		if (gsf.getSearchString() != null) {
 			query.setParameter("notificationText", "%" + gsf.getSearchString().toUpperCase() + "%");
 			query.setParameter("freeText", "%" + gsf.getSearchString().toUpperCase() + "%");
 			query.setParameter("freeTextExtended", "%" + gsf.getSearchString().toUpperCase() + "%");
 		}
 		if (gsf.getResponsibilityForwarding() != null && !gsf.getResponsibilityForwarding().isEmpty()) {
 			query.setParameter("responsibilityForwarding", "%" + gsf.getResponsibilityForwarding() + "%");
 		}
 		if (gsf.isBranchSelected()) {
 			query.setParameter("refBranch", gsf.getFkRefBranch());
 		}
 		if (gsf.isGridTerritorySelected()) {
 			query.setParameter("refGridTerritory", gsf.getFkRefGridTerritory());
 		}
 	}

 	/**
 	 * Helper method computing the search interval. The (negative) number of days actually is taken from Globals.
 	 *
 	 * @return the search interval.
 	 */
 	private Timestamp createSearchIntervalParameter() {
 		Calendar calendar = Calendar.getInstance();
 		calendar.set(Calendar.HOUR_OF_DAY, 0);
 		calendar.set(Calendar.MINUTE, 0);
 		calendar.set(Calendar.SECOND, 0);
 		calendar.set(Calendar.MILLISECOND, 0);
 		calendar.add(Calendar.DAY_OF_YEAR, Globals.FAST_SEARCH_NUMBER_OF_DAYS_BACK);
 		long searchDateFromInms = calendar.getTime().getTime();
 		Timestamp searchDateFrom = new Timestamp(searchDateFromInms);
 		if (LOGGER.isDebugEnabled()) {
 			LOGGER.debug("Folgenden Timestamp für das Schnellsucheintervall ermittelt: " + searchDateFrom.toString());
 		}
 		return searchDateFrom;
 	}

 	/**
 	 * Helper method removing last (= wrong) comma by inserting paranthese instead.
 	 *
 	 * @param sqlSB
 	 *            the String Builderwhere the comma is to replace.
 	 */
 	private void replaceLastCommaWithParanthese(StringBuilder sqlSB) {
 		int posFinished = sqlSB.length() - 2;
 		sqlSB.replace(posFinished, posFinished + 1, ")");
 	}

 	/**
 	 * Helper method to extend the Where-Clause of generateFindHistoricalNotificationsByResponsibilityQuery method list
 	 * type specific
 	 *
 	 * Note: Past notifications are picked only if their begin date is between transferDate and one week before transfer
 	 * date.
 	 *
 	 * @param sqlSB
 	 *            the query string builder to be extended
 	 * @param listType
 	 *            the list type
 	 */
 	private void extendFindHistoricalNotificationsByResponsibilityQueryListTypeSpecific(StringBuilder sqlSB,
 			ListType listType, Timestamp transferDate, List<Object> paramList) {
 		sqlSB.append(AND_LIT).append( FK_REF_NOTIFICATION_STATUS + " ");
 		switch (listType) {
 		case PAST:
 			sqlSB.append("IN ").append("(").append(NotificationStatus.CLOSED.id).append(", ")
 					.append(NotificationStatus.FINISHED.id).append(")")
 					.append(AND_LIT).append( BEGIN_DATE ).append(" <= ?") // NOSONAR
 					.append(AND_LIT).append( BEGIN_DATE ).append(" > ?"); // NOSONAR
 			paramList.add(transferDate);
 			paramList.add(TimestampConverter.getTimestampWithTime(
 					new Timestamp(transferDate.getTime()-1*(1000*60*60*24)),
 					0, 0, 0));
 			break;
 		case OPEN:
 			sqlSB.append("IN ").append("(").append(NotificationStatus.OPEN.id).append(", ")
 					.append(NotificationStatus.INPROGRESS.id).append(", ").append(NotificationStatus.FINISHED.id)
 					.append(")")
 					.append(AND_LIT).append(BEGIN_DATE).append(" < ?");
 			paramList.add(TimestampConverter.getTimestampWithTime(
 					new Timestamp(transferDate.getTime()+1*(1000*60*60*24)),
 					0, 0, 0));
 			break;
 		case FUTURE:
 			sqlSB.append("NOT IN ").append("(").append(NotificationStatus.CLOSED.id).append(", ")
 					.append(NotificationStatus.FINISHED.id).append(")")
 					.append(AND_LIT).append(BEGIN_DATE).append(" >= ?");
 					paramList.add(TimestampConverter.getTimestampWithTime(
 							new Timestamp(transferDate.getTime()+1*(1000*60*60*24)),
 							0, 0, 0 ));
 			break;
 		default:
 			break;
 		}
 	}

 	/**
 	 * Create the where clause.
 	 *
 	 * @param nsf

 	 * @param tblResponsibilities
 	 *            the list with the {@link TblResponsibility} objects
 	 * @return he appropriate String to extend the where-clause
 	 */
 	private String extendWhereClauseApplyingSearchFilter(NotificationSearchFilter nsf, List<TblResponsibility> tblResponsibilities) {
 		StringBuilder ret = new StringBuilder();

 		ret.append(extendWhereClauseByDates(nsf));

 		ret.append(extendWhereClauseByResponsibility(tblResponsibilities));
 		return ret.toString();
 	}

 	private String extendWhereClauseApplyingSearchFilterWithReminder(ReminderSearchFilter rsf,
 			List<TblResponsibility> tblResponsibilities) {
 		StringBuilder ret = new StringBuilder();

 		ret.append(extendWhereClauseByReminderDate(rsf));

 		ret.append(extendWhereClauseByResponsibility(tblResponsibilities));
 		return ret.toString();
 	}

 	/**
 	 * Use DateFrom and DateTo in where clause.
 	 *
 	 * @param nsf
 	 *            the notification search filter
 	 * @return the appropriate String to extend the where-clause
 	 */
 	private String extendWhereClauseByDates(NotificationSearchFilter nsf) {
 		StringBuilder ret = new StringBuilder();
 		if (nsf.getDateFrom() != null) {
 			ret.append(AND_LIT).append(tablePrefix).append(BEGIN_DATE).append(" >= ?");
 			latebindParamMap.put(iCounter, nsf.getDateFrom());
 			iCounter++;
 		}

 		if (nsf.getDateTo() != null) {
 			ret.append(AND_LIT).append(tablePrefix).append(BEGIN_DATE).append(" <= ?");
 			latebindParamMap.put(iCounter, nsf.getDateTo());
 		}
 		return ret.toString();
 	}

 	private String extendWhereClauseByReminderDate(ReminderSearchFilter rsf) {
 		StringBuilder ret = new StringBuilder();
 		if (rsf.getReminderDate() != null) {
 			ret.append(AND_LIT).append(tablePrefix).append(REMINDER_DATE).append(" <= ?");
 			latebindParamMap.put(iCounter, rsf.getReminderDate());
 		}

 		return ret.toString();
 	}

 	/**
 	 * Use TblResponsibilities in where clause. The foreign key constraints for branches and grid territories are used
 	 * 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
 	 * @return the appropriate String to extend the where-clause
 	 */
 	private String extendWhereClauseByResponsibility(List<TblResponsibility> tblResponsibilities) {
 		StringBuilder stringBuilder = new StringBuilder(AND_LIT).append("(").append(FK_BRANCH_NULL).append(AND_LIT)
 				.append(FK_GRID_TERRITORY_NULL);

 		if (tblResponsibilities.isEmpty()) {
 			stringBuilder.append(")");
 			return stringBuilder.toString();
 		}
 		stringBuilder.append(OR_LIT).append("(");

 		int actualListPos = 0;
 		for (TblResponsibility tblResponsibility : tblResponsibilities) {
 			stringBuilder.append(appendForeignKeyNullOrMatching(tblResponsibility));
 			actualListPos++;
 			if (actualListPos < tblResponsibilities.size()) {
 				stringBuilder.append(OR_LIT);
 			}
 		}
 		stringBuilder.append(")").append(")");
 		return stringBuilder.toString();
 	}

 	/**
 	 * Helper Method: Extend the where clause by foreign keys null or matching.
 	 *
 	 * 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
 	 * @return the extended where clause
 	 */
 	private String appendForeignKeyNullOrMatching(TblResponsibility tblResponsibility) {
 		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(FK_GRID_TERRITORY_NULL).append(OR_LIT).append(FK_GRID_TERRITORY_EQUAL)
 				.append(tblResponsibility.getRefGridTerritory().getFkRefMaster()).append(")");
 		return stringBuilder.toString();
 	}

 	private String getNotificationOrder() {
 		return ORDER_LIT + tablePrefix + FK_REF_BRANCH+" ASC, "+FK_REF_GRID_TERRITORY+" ASC, " + tablePrefix + BEGIN_DATE;
 	}


 }
	package org.eclipse.openk.elogbook.persistence.util;

	import org.apache.log4j.Logger;
	import org.eclipse.openk.elogbook.common.Globals;
	import org.eclipse.openk.elogbook.common.NotificationStatus;
	import org.eclipse.openk.elogbook.persistence.dao.EntityHelper;
	import org.eclipse.openk.elogbook.persistence.model.HTblResponsibility;
	import org.eclipse.openk.elogbook.persistence.model.TblNotification;
	import org.eclipse.openk.elogbook.persistence.model.TblResponsibility;
	import org.eclipse.openk.elogbook.viewmodel.GlobalSearchFilter;
	import org.eclipse.openk.elogbook.viewmodel.Notification.ListType;
	import org.eclipse.openk.elogbook.viewmodel.NotificationSearchFilter;
	import org.eclipse.openk.elogbook.viewmodel.ReminderSearchFilter;

	import javax.persistence.EntityManager;
	import javax.persistence.Query;
	import java.sql.Timestamp;
	import java.util.*;

	public class NotificationQueryCreator {
	private static final String AND_LIT = " AND ";
	private static final String OR_LIT = " OR ";
	private static final String ORDER_LIT = " ORDER BY ";
	private static final String IS_NULL = " IS NULL";
	private static final String FK_REF_BRANCH = EntityHelper.instance().makeIdentifier("fk_ref_branch");
	private static final String FK_REF_GRID_TERRITORY = EntityHelper.instance().makeIdentifier("fk_ref_grid_territory");
	private static final String FK_BRANCH_EQUAL = FK_REF_BRANCH + " = ";
	private static final String FK_GRID_TERRITORY_EQUAL = FK_REF_GRID_TERRITORY+" = ";
	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 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");
	private static final String VERSION = EntityHelper.instance().makeIdentifier("version");
	private static final String BEGIN_DATE = EntityHelper.instance().makeIdentifier("begin_date");
	private static final String MOD_DATE = EntityHelper.instance().makeIdentifier("mod_date");
	private static final String CREATE_DATE = EntityHelper.instance().makeIdentifier("create_date");
	private static final String REMINDER_DATE = EntityHelper.instance().makeIdentifier("reminder_date");


	private static final Logger LOGGER = Logger.getLogger(NotificationQueryCreator.class.getName());

	private EntityManager em;
	private String tablePrefix = "";
	private Map<Integer, Object> latebindParamMap = new HashMap<>();
	private int iCounter = 1;

	public NotificationQueryCreator(EntityManager em, String tablePrefix ) {
	this.em = em;
	if (tablePrefix != null && !tablePrefix.isEmpty()) {
	this.tablePrefix = tablePrefix + ".";
	}
	}

	/**
	* Generate the Query by processing the informations from the notification search-filter.
	*
	* @param notificationSearchFilter
	* The Object obtained from view model
	* @param baseWhereClause
	* the base where clause to be completed using the notification search filter
	* @param listType
	* the listType (where clause creation depends from it)
	* @param tblResponsibilities
	* the list with the {@link TblResponsibility} objects
	* @return the generated query
	*/
	public Query generateNotificationQuery(NotificationSearchFilter notificationSearchFilter, String baseWhereClause,
	List<TblResponsibility> tblResponsibilities) {

	StringBuilder sql = new StringBuilder("select * from "+VIEW_ACTIVE_NOTIFICATION+" v where 1=1 "); // NOSONAR
	if (baseWhereClause != null && !baseWhereClause.isEmpty()) {
	sql.append(AND_LIT).append(baseWhereClause);
	}
	if (notificationSearchFilter != null) {
	sql.append(extendWhereClauseApplyingSearchFilter(notificationSearchFilter, tblResponsibilities));
	}
	sql.append(getNotificationOrder());
	// _fd: No String comes unescaped from outside the program -> So SONAR
	// is wrong with it error
	Query q = em.createNativeQuery(sql.toString(), TblNotification.class); // NOSONAR
	latebindParamMap.forEach(q::setParameter);
	return q;
	}

	public Query generateNotificationQueryWithReminder(ReminderSearchFilter rsf, String baseWhereClause,
	List<TblResponsibility> tblResponsibilities) {

	StringBuilder sql = new StringBuilder("select * from "+VIEW_ACTIVE_NOTIFICATION+" v where 1=1 "); // NOSONAR
	if (baseWhereClause != null && !baseWhereClause.isEmpty()) {
	sql.append(AND_LIT).append(baseWhereClause);
	}
	if (rsf != null) {
	sql.append(extendWhereClauseApplyingSearchFilterWithReminder(rsf, tblResponsibilities));
	}
	sql.append(getNotificationOrder());

	// _fd: No String comes unescaped from outside the program -> So SONAR
	// is wrong with it error
	Query q = em.createNativeQuery(sql.toString(), TblNotification.class); // NOSONAR
	latebindParamMap.forEach(q::setParameter);
	return q;
	}

	/**
	* Create a query to get historical notifications relevant for the responsibility belonging to a specific
	* transaction id .
	*
	* @param hTblResponsibilities
	* the list of historical responsibilities at shift change date
	* @param listType
	* the {@link ListType}
	* @return the generated query.
	*/
	public Query generateFindHistoricalNotificationsByResponsibilityQuery(List<HTblResponsibility> hTblResponsibilities,
	ListType listType) {
	List<Object> paramList = new LinkedList<>();
	StringBuilder sqlSB = new StringBuilder("select * from " + TBL_NOTIFICATION + " t1 join ( "
	+ " select "+INCIDENT_ID+ ", "
	+ " max("+VERSION+") as VERSION "
	+ " FROM "+TBL_NOTIFICATION+" t " + " WHERE ");
	sqlSB.append("( "+MOD_DATE+" < ? ").append(OR_LIT)
	.append(" "+MOD_DATE+" IS NULL").append(AND_LIT)
	.append(" "+CREATE_DATE + " < ? ");
	sqlSB.append(")");
	sqlSB.append(" GROUP BY "+INCIDENT_ID);
	sqlSB.append(" ) t2 ON ( t1."+INCIDENT_ID+" = t2."+INCIDENT_ID);
	sqlSB.append(" AND t1."+VERSION+" = t2.VERSION) where ");

	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(")");
	extendFindHistoricalNotificationsByResponsibilityQueryListTypeSpecific(sqlSB, listType,
	hTblResponsibilities.get(0).getTransferDate(), paramList);
	sqlSB.append(getNotificationOrder());

	Query query = em.createNativeQuery(sqlSB.toString(), TblNotification.class);
	int i = 1;
	for( Object o: paramList) {
	query.setParameter(i++, o);
	}
	return query;
	}

	/**
	* Create a query by evaluating the search filter criteria.
	*
	* @param gsf
	* the search filter containing the search criteria.
	* @return the generated query.
	*/
	public Query generateFindNotificationsMatchingSearchCriteriaQuery(GlobalSearchFilter gsf) {
	String sql = createSearchCriteriaSelectString(gsf);
	Query query = em.createQuery(sql, TblNotification.class);
	substituteSearchCriteriaParameters(gsf, query);
	return query;
	}

	/**
	* Helper method generating SQL SELECT for search.
	* @param gsf the search filter containing search criteria
	* @return The generated query string
	*/
	private String createSearchCriteriaSelectString(GlobalSearchFilter gsf) {
	StringBuilder sqlSB = new StringBuilder("SELECT t FROM");
	if (gsf.isFastSearchSelected()) {
	sqlSB.append(" ViewNotification t WHERE 1 = 1 AND ");
	sqlSB.append("(t.modDate > :timestamp or t.modDate IS NULL AND t.createDate > :timestamp) AND ");
	}
	else {
	sqlSB.append(" TblNotification t WHERE 1 = 1 AND ");
	}

	if (gsf.getSearchString() != null) {
	sqlSB.append("( UPPER(t.notificationText) LIKE :notificationText OR")
	.append(" UPPER(t.freeText) LIKE :freeText OR")
	.append(" UPPER(t.freeTextExtended) LIKE :freeTextExtended)");
	}
	if (gsf.getResponsibilityForwarding() != null && !gsf.getResponsibilityForwarding().isEmpty()) {
	sqlSB.append(" AND t.responsibilityForwarding LIKE :responsibilityForwarding ");
	}
	if (gsf.isBranchSelected()) {
	sqlSB.append(" AND t.refBranch.id = :refBranch ");
	}
	if (gsf.isGridTerritorySelected()) {
	sqlSB.append(" AND t.refGridTerritory.id = :refGridTerritory ");
	}
	List<Integer> statusIds = gsf.getSelectedStatusMappedToIds();
	if (!statusIds.isEmpty()) {
	sqlSB.append(" AND t.refNotificationStatus.id IN ( ");
	for (Integer status : statusIds) {
	sqlSB.append(status.toString()).append(", ");
	}
	replaceLastCommaWithParanthese(sqlSB);
	}
	sqlSB.append(" ORDER BY t.refBranch, t.incidentId DESC, t.version DESC, t.refGridTerritory, t.beginDate ");
	return sqlSB.toString();
	}


	/**
	* Helper method setting parameters to the generated search query.
	* @param gsf the search filter where the parameter values are taken from
	* @param query the generated query.
	*/
	private void substituteSearchCriteriaParameters(GlobalSearchFilter gsf, Query query) {
	if (gsf.isFastSearchSelected()) {
	query.setParameter("timestamp", createSearchIntervalParameter());
	}
	if (gsf.getSearchString() != null) {
	query.setParameter("notificationText", "%" + gsf.getSearchString().toUpperCase() + "%");
	query.setParameter("freeText", "%" + gsf.getSearchString().toUpperCase() + "%");
	query.setParameter("freeTextExtended", "%" + gsf.getSearchString().toUpperCase() + "%");
	}
	if (gsf.getResponsibilityForwarding() != null && !gsf.getResponsibilityForwarding().isEmpty()) {
	query.setParameter("responsibilityForwarding", "%" + gsf.getResponsibilityForwarding() + "%");
	}
	if (gsf.isBranchSelected()) {
	query.setParameter("refBranch", gsf.getFkRefBranch());
	}
	if (gsf.isGridTerritorySelected()) {
	query.setParameter("refGridTerritory", gsf.getFkRefGridTerritory());
	}
	}

	/**
	* Helper method computing the search interval. The (negative) number of days actually is taken from Globals.
	*
	* @return the search interval.
	*/
	private Timestamp createSearchIntervalParameter() {
	Calendar calendar = Calendar.getInstance();
	calendar.set(Calendar.HOUR_OF_DAY, 0);
	calendar.set(Calendar.MINUTE, 0);
	calendar.set(Calendar.SECOND, 0);
	calendar.set(Calendar.MILLISECOND, 0);
	calendar.add(Calendar.DAY_OF_YEAR, Globals.FAST_SEARCH_NUMBER_OF_DAYS_BACK);
	long searchDateFromInms = calendar.getTime().getTime();
	Timestamp searchDateFrom = new Timestamp(searchDateFromInms);
	if (LOGGER.isDebugEnabled()) {
	LOGGER.debug("Folgenden Timestamp für das Schnellsucheintervall ermittelt: " + searchDateFrom.toString());
	}
	return searchDateFrom;
	}

	/**
	* Helper method removing last (= wrong) comma by inserting paranthese instead.
	*
	* @param sqlSB
	* the String Builderwhere the comma is to replace.
	*/
	private void replaceLastCommaWithParanthese(StringBuilder sqlSB) {
	int posFinished = sqlSB.length() - 2;
	sqlSB.replace(posFinished, posFinished + 1, ")");
	}

	/**
	* Helper method to extend the Where-Clause of generateFindHistoricalNotificationsByResponsibilityQuery method list
	* type specific
	*
	* Note: Past notifications are picked only if their begin date is between transferDate and one week before transfer
	* date.
	*
	* @param sqlSB
	* the query string builder to be extended
	* @param listType
	* the list type
	*/
	private void extendFindHistoricalNotificationsByResponsibilityQueryListTypeSpecific(StringBuilder sqlSB,
	ListType listType, Timestamp transferDate, List<Object> paramList) {
	sqlSB.append(AND_LIT).append( FK_REF_NOTIFICATION_STATUS + " ");
	switch (listType) {
	case PAST:
	sqlSB.append("IN ").append("(").append(NotificationStatus.CLOSED.id).append(", ")
	.append(NotificationStatus.FINISHED.id).append(")")
	.append(AND_LIT).append( BEGIN_DATE ).append(" <= ?") // NOSONAR
	.append(AND_LIT).append( BEGIN_DATE ).append(" > ?"); // NOSONAR
	paramList.add(transferDate);
	paramList.add(TimestampConverter.getTimestampWithTime(
	new Timestamp(transferDate.getTime()-1(1000606024)),
	0, 0, 0));
	break;
	case OPEN:
	sqlSB.append("IN ").append("(").append(NotificationStatus.OPEN.id).append(", ")
	.append(NotificationStatus.INPROGRESS.id).append(", ").append(NotificationStatus.FINISHED.id)
	.append(")")
	.append(AND_LIT).append(BEGIN_DATE).append(" < ?");
	paramList.add(TimestampConverter.getTimestampWithTime(
	new Timestamp(transferDate.getTime()+1(1000606024)),
	0, 0, 0));
	break;
	case FUTURE:
	sqlSB.append("NOT IN ").append("(").append(NotificationStatus.CLOSED.id).append(", ")
	.append(NotificationStatus.FINISHED.id).append(")")
	.append(AND_LIT).append(BEGIN_DATE).append(" >= ?");
	paramList.add(TimestampConverter.getTimestampWithTime(
	new Timestamp(transferDate.getTime()+1(1000606024)),
	0, 0, 0 ));
	break;
	default:
	break;
	}
	}

	/**
	* Create the where clause.
	*
	* @param nsf

	* @param tblResponsibilities
	* the list with the {@link TblResponsibility} objects
	* @return he appropriate String to extend the where-clause
	*/
	private String extendWhereClauseApplyingSearchFilter(NotificationSearchFilter nsf, List<TblResponsibility> tblResponsibilities) {
	StringBuilder ret = new StringBuilder();

	ret.append(extendWhereClauseByDates(nsf));

	ret.append(extendWhereClauseByResponsibility(tblResponsibilities));
	return ret.toString();
	}

	private String extendWhereClauseApplyingSearchFilterWithReminder(ReminderSearchFilter rsf,
	List<TblResponsibility> tblResponsibilities) {
	StringBuilder ret = new StringBuilder();

	ret.append(extendWhereClauseByReminderDate(rsf));

	ret.append(extendWhereClauseByResponsibility(tblResponsibilities));
	return ret.toString();
	}

	/**
	* Use DateFrom and DateTo in where clause.
	*
	* @param nsf
	* the notification search filter
	* @return the appropriate String to extend the where-clause
	*/
	private String extendWhereClauseByDates(NotificationSearchFilter nsf) {
	StringBuilder ret = new StringBuilder();
	if (nsf.getDateFrom() != null) {
	ret.append(AND_LIT).append(tablePrefix).append(BEGIN_DATE).append(" >= ?");
	latebindParamMap.put(iCounter, nsf.getDateFrom());
	iCounter++;
	}

	if (nsf.getDateTo() != null) {
	ret.append(AND_LIT).append(tablePrefix).append(BEGIN_DATE).append(" <= ?");
	latebindParamMap.put(iCounter, nsf.getDateTo());
	}
	return ret.toString();
	}

	private String extendWhereClauseByReminderDate(ReminderSearchFilter rsf) {
	StringBuilder ret = new StringBuilder();
	if (rsf.getReminderDate() != null) {
	ret.append(AND_LIT).append(tablePrefix).append(REMINDER_DATE).append(" <= ?");
	latebindParamMap.put(iCounter, rsf.getReminderDate());
	}

	return ret.toString();
	}

	/**
	* Use TblResponsibilities in where clause. The foreign key constraints for branches and grid territories are used
	* 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
	* @return the appropriate String to extend the where-clause
	*/
	private String extendWhereClauseByResponsibility(List<TblResponsibility> tblResponsibilities) {
	StringBuilder stringBuilder = new StringBuilder(AND_LIT).append("(").append(FK_BRANCH_NULL).append(AND_LIT)
	.append(FK_GRID_TERRITORY_NULL);

	if (tblResponsibilities.isEmpty()) {
	stringBuilder.append(")");
	return stringBuilder.toString();
	}
	stringBuilder.append(OR_LIT).append("(");

	int actualListPos = 0;
	for (TblResponsibility tblResponsibility : tblResponsibilities) {
	stringBuilder.append(appendForeignKeyNullOrMatching(tblResponsibility));
	actualListPos++;
	if (actualListPos < tblResponsibilities.size()) {
	stringBuilder.append(OR_LIT);
	}
	}
	stringBuilder.append(")").append(")");
	return stringBuilder.toString();
	}

	/**
	* Helper Method: Extend the where clause by foreign keys null or matching.
	*
	* 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
	* @return the extended where clause
	*/
	private String appendForeignKeyNullOrMatching(TblResponsibility tblResponsibility) {
	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(FK_GRID_TERRITORY_NULL).append(OR_LIT).append(FK_GRID_TERRITORY_EQUAL)
	.append(tblResponsibility.getRefGridTerritory().getFkRefMaster()).append(")");
	return stringBuilder.toString();
	}

	private String getNotificationOrder() {
	return ORDER_LIT + tablePrefix + FK_REF_BRANCH+" ASC, "+FK_REF_GRID_TERRITORY+" ASC, " + tablePrefix + BEGIN_DATE;
	}


	}