src/main/java/org/eclipse/openk/elogbook/persistence/util/NotificationQueryCreator.java

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.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.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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 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 DUMMY_FALSE_VALUE_ORCLAUSE = " false ";
	
	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 viewmodel
	 * @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,
			ListType listType, List<TblResponsibility> tblResponsibilities) {

		StringBuilder sql = new StringBuilder("select * from view_active_notification v where 1=1 ");
		if (baseWhereClause != null && !baseWhereClause.isEmpty()) {
			sql.append(AND_LIT).append(baseWhereClause);
		}
		if (notificationSearchFilter != null) {
			sql.append(extendWhereClauseApplyingSearchFilter(notificationSearchFilter, listType, 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 ");
		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) {
		StringBuilder sqlSB = new StringBuilder("select * from " + " tbl_notification t1 join ( "
				+ " select incident_id, max(version) as version FROM tbl_notification t " + " WHERE ");
		sqlSB.append("(");
		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 AND t1.version = t2.version) where ");

		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());
		}

		extendFindHistoricalNotificationsByResponsibilityQueryListTypeSpecific(sqlSB, listType,
				hTblResponsibilities.get(0).getTransferDate());
		sqlSB.append(")").append(getNotificationOrder());
		Query query = em.createNativeQuery(sqlSB.toString(), TblNotification.class);
		query.setParameter(1, hTblResponsibilities.get(0).getTransferDate());
		query.setParameter(2, hTblResponsibilities.get(0).getTransferDate());
		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) {
		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(transferDate).append("'").append(AND_LIT).append("begin_date > TIMESTAMP '")
					.append(transferDate).append("' - INTERVAL '7 days'");
			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 < TIMESTAMP '").append(transferDate)
					.append("'  + INTERVAL '1 day '");
			break;
		case FUTURE:
			sqlSB.append("NOT IN ").append("(").append(NotificationStatus.CLOSED.id).append(", ")
					.append(NotificationStatus.FINISHED.id).append(")");
			break;
		default:
			break;
		}
	}

	/**
	 * Create the where clause.
	 *
	 * @param nsf
	 *            The Object obtained from view viewmodel
	 * @param listType
	 *            the listType (where clause creation depends from it)
	 * @param tblResponsibilities
	 *            the list with the {@link TblResponsibility} objects
	 * @return he appropriate String to extend the where-clause
	 */
	private String extendWhereClauseApplyingSearchFilter(NotificationSearchFilter nsf, ListType listType,
			List<TblResponsibility> tblResponsibilities) {
		StringBuilder ret = new StringBuilder();

		if (listType == ListType.PAST || listType == ListType.FUTURE) {
			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 >= ?");
			latebindParamMap.put(iCounter, nsf.getDateFrom());
			iCounter++;
		}

		if (nsf.getDateTo() != null) {
			ret.append(AND_LIT).append(tablePrefix).append("begin_date <= ?");
			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 <= ?");
			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";
	}
}