src/main/java/org/eclipse/openk/elogbook/persistence/dao/TblNotificationDao.java - openk-usermodules/org.eclipse.openk-usermodules.elogbook - Git at Google

 /**
 ******************************************************************************
 * Copyright © 2017-2018 PTA GmbH.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 *
 *     http://www.eclipse.org/legal/epl-v10.html
 *
 ******************************************************************************
 */
 package org.eclipse.openk.elogbook.persistence.dao;

 import org.eclipse.openk.elogbook.common.NotificationStatus;
 import org.eclipse.openk.elogbook.exceptions.BtbException;
 import org.eclipse.openk.elogbook.exceptions.BtbInternalServerError;
 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.persistence.util.NotificationQueryCreator;
 import org.eclipse.openk.elogbook.persistence.util.TimestampConverter;
 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.ArrayList;
 import java.util.List;
 import java.util.UUID;

 public class TblNotificationDao extends GenericDaoJpa<TblNotification, Integer> {
 	private static final String VIEW_ACTIVE_NOTIFICATION = EntityHelper.instance().makeIdentifier("VIEW_ACTIVE_NOTIFICATION");
 	private static final String FK_REF_NOTIFICATION_STATUS = EntityHelper.instance().makeIdentifier("fk_ref_notification_status");
 	private static final String TBL_NOTIFICATION = EntityHelper.instance().makeIdentifier("TBL_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 RESPONSIBILITY_FORWARDING = EntityHelper.instance().makeIdentifier("responsibility_forwarding");
 	private static final String RESPONSIBILITY_FORW_UUID = EntityHelper.instance().makeIdentifier("responsibility_forwarding_uuid");


 	public TblNotificationDao() {
 		super();
 	}

 	public TblNotificationDao(EntityManager em) {
 		super(em);
 	}

 	/*-
 	 * Mögliche Status:
 	 * 1	offen
 	 * 2	in Arbeit
 	 * 3	erledigt
 	 * 4	geschlossen
 	 */

 	/**
 	 * Returns the latest version of all notifications.
 	 *
 	 * @return List of notifications
 	 */
 	public List<TblNotification> getActiveNotifications() throws BtbInternalServerError {
 		return refreshModelCollection(
 				getActiveNotifications(null, null, null, new ArrayList<>()));
 	}

 	/**
 	 * Get the List of Active Notifications. The active notifications view is used.
 	 *
 	 * @param nsf
 	 *            search filter to narrow the navigations to be returned
 	 * @param whereClause
 	 *            the where clause to be completed
 	 * @param tablePrefix
 	 *            the table prefix
 	 * @param tblResponsibilities
 	 *            the list of responsibilities
 	 * @return the list of active notifications
 	 * @throws BtbInternalServerError
 	 * 						internal Server Error
 	 */
 	@SuppressWarnings("unchecked")
 	private List<TblNotification> getActiveNotifications(NotificationSearchFilter nsf, String whereClause,
 														 String tablePrefix, List<TblResponsibility> tblResponsibilities)
 			throws BtbInternalServerError {

 		try {
 			NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), tablePrefix);
 			Query q = queryCreator.generateNotificationQuery(nsf, whereClause, tblResponsibilities);
 			return refreshModelCollection((List<TblNotification>) q.getResultList());
 		} catch (Exception e) {
 			LOGGER.error("Error while retrieving notifications", e);
 			throw new BtbInternalServerError("Error retrieving notifications");
 		}
 	}

 	@SuppressWarnings("unchecked")
 	private List<TblNotification> getActiveNotificationsWithReminder(ReminderSearchFilter rsf, String whereClause,
 														 String tablePrefix, List<TblResponsibility> tblResponsibilities)
 														throws BtbInternalServerError {

 		try {
 			NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), tablePrefix);
 			Query q = queryCreator.generateNotificationQueryWithReminder(rsf, whereClause, tblResponsibilities);
 			return refreshModelCollection((List<TblNotification>) q.getResultList());
 		} catch (Exception e) {
 			LOGGER.error("Error while retrieving notifications with reminder", e);
 			throw new BtbInternalServerError("Error retrieving notifications with reminder");
 		}
 	}

 	public List<TblNotification> getNotificationsWithReminder(ReminderSearchFilter rsf, List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {

 		try {
 			String where = "v."+FK_REF_NOTIFICATION_STATUS+" IN ( " + NotificationStatus.OPEN.id + "," + NotificationStatus.INPROGRESS.id + " ) ";
 			return refreshModelCollection(getActiveNotificationsWithReminder(rsf, where, "v", tblResponsibilities));
 		} catch (Exception t) {
 			LOGGER.error(t);
 			throw new BtbInternalServerError("Error loading notifications");
 		}
 	}


 	/**
 	 * Returns all Notifications with status "closed" that are not older than x
 	 * hours.
 	 *
 	 * @return List of notifications
 	 */
 	public List<TblNotification> getPastNotifications(NotificationSearchFilter nsf,
 			List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {
 		String where = "v."+FK_REF_NOTIFICATION_STATUS+" IN ( " + NotificationStatus.CLOSED.id + " ) ";
 		return refreshModelCollection(getActiveNotifications(nsf, where, "v", tblResponsibilities));
 	}

 	/**
 	 * Returns the Notifications with Status "open", "in progess" or "finished"
 	 * that have a begin date before tomorrow.
 	 *
 	 * @param notificationSearchFilter
 	 *            The filter to determine the the search criteria.
 	 * @return List of notifications
 	 */
 	public List<TblNotification> getOpenNotifications(NotificationSearchFilter notificationSearchFilter,
 			List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {
 		String where = "v."+FK_REF_NOTIFICATION_STATUS+" IN (" + NotificationStatus.OPEN.id + ","
 				+ NotificationStatus.INPROGRESS.id + ","
                 + NotificationStatus.FINISHED.id+ ")";
 		notificationSearchFilter.setDateTo(
 				TimestampConverter.getTimestampWithTime(
 						new Timestamp(System.currentTimeMillis() + 1000 * 60 * 60 * 24), 0, 0, 0 ));
 		return refreshModelCollection(
 				getActiveNotifications(notificationSearchFilter, where, "v", tblResponsibilities));
 	}

 	public List<TblNotification> getFutureNotifications(NotificationSearchFilter nsf,
 			List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {
 		String where = "v."+FK_REF_NOTIFICATION_STATUS+" NOT IN (" + NotificationStatus.CLOSED.id + ","
 				+ NotificationStatus.FINISHED.id + ")";
 		return refreshModelCollection(getActiveNotifications(nsf, where, "v", tblResponsibilities));
 	}


 	@SuppressWarnings("unchecked")
 	public List<TblNotification> getByIncidentId(int incidentId) throws BtbInternalServerError {
 		try {
 			String selectString = "select * from "+TBL_NOTIFICATION+" n where n."+INCIDENT_ID+"=? order by n."+VERSION+" desc";
 			Query q = getEM().createNativeQuery(selectString, TblNotification.class);
 			q.setParameter(1, incidentId);
 			return refreshModelCollection(q.getResultList());
 		} catch (Exception t) {
 			LOGGER.error(t);
 			throw new BtbInternalServerError("Error loading notifications by incidentId");
 		}
 	}

 	@SuppressWarnings("unchecked")
 	public List<String> getAssignedUserSuggestions() throws BtbInternalServerError {

 		try {
 			String selectString = "select distinct "+RESPONSIBILITY_FORWARDING+" from "+
 			VIEW_ACTIVE_NOTIFICATION+" n where n."+RESPONSIBILITY_FORWARDING+" is not null";
 			Query q = getEM().createNativeQuery(selectString);
 			return q.getResultList();
 		} catch (Exception t) {
 			LOGGER.error(t);
 			throw new BtbInternalServerError("Error loading notifications");
 		}
 	}

 	public List<UUID> getDistinctResponsibilityForwardingUuid() throws BtbInternalServerError {
 		try {
 			String selectString = "select distinct "+RESPONSIBILITY_FORW_UUID+" from "+TBL_NOTIFICATION+" n where n.responsibility_forwarding_uuid is not null";
 			Query q = getEM().createNativeQuery(selectString);
 			return q.getResultList();

 		} catch (Exception t) {
 			LOGGER.error(t);
 			throw new BtbInternalServerError("Error loading distict responsibility forwarding uuid");
 		}
 	}

 	public void updateResponsibilityForwardingByContactUuid( UUID contactUuid, String contactName ) {
 		try {
 			String selectString = "update "+TBL_NOTIFICATION+" set "+RESPONSIBILITY_FORWARDING+" = ? where "+RESPONSIBILITY_FORW_UUID+" = ?";
 			Query q = getEM().createNativeQuery(selectString);
 			q.setParameter(2, contactUuid);
 			q.setParameter(1, contactName);
 			q.executeUpdate();

 		} catch (Exception t) {
 			LOGGER.error(t);
 		}
 	}


 	/**
 	 * Create and execute the query to get a collection of notifications.
 	 *
 	 * @param hTblResponsibilities
 	 *            the responsibilities containing branch and grid territory references
 	 * @param listType
 	 *            the {@link ListType}
 	 * @return a List of historical notifications relevant for the responsibility given by parameter.
 	 * @throws BtbException if an error occurs.
 	 */
 	@SuppressWarnings("unchecked")
 	public List<TblNotification> findHistoricalNotificationsByResponsibility(
 			List<HTblResponsibility> hTblResponsibilities, ListType listType) throws BtbInternalServerError {
 		try {
 			NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), "");
 			Query query = queryCreator.generateFindHistoricalNotificationsByResponsibilityQuery(hTblResponsibilities,
 					listType);
 			List<TblNotification> tblNotifications = refreshModelCollection(
 					(List<TblNotification>) query.getResultList());

 			if (LOGGER.isDebugEnabled()) {
 				for (TblNotification tblNotification : tblNotifications) {
 					LOGGER.debug("Notification found: " + tblNotification.getId() + "/"
 							+ tblNotification.getIncidentId() + tblNotification.getVersion());
 				}
 			}
 			return tblNotifications;
 		} catch (Exception e) {
 			LOGGER.error(e);
 			throw new BtbInternalServerError("Error retrieving historical notifications by responsibility");
 		}
 	}


 	/**
 	 * Find notifications matching the search criteria in the global search filter.
 	 *
 	 * @param globalSearchFilter
 	 *            contains the search criteria.
 	 * @return the notifications found, matching the given criteria.
 	 * @throws BtbInternalServerError
 	 *             if an error occurs.
 	 */
 	@SuppressWarnings("unchecked")
 	public List<TblNotification> findNotificationsMatchingSearchCriteria(GlobalSearchFilter globalSearchFilter)
 			throws BtbInternalServerError {
 		try {
 			NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), "");
 			Query query = queryCreator.generateFindNotificationsMatchingSearchCriteriaQuery(globalSearchFilter);
 			List<TblNotification> results;
 			results = query.getResultList();
 			return refreshModelCollection(results);
 		} catch (Exception e) {
 			LOGGER.error(e);
 			throw new BtbInternalServerError("Error retrieving notifications applying global search filter.");
 		}
 	}
 }
	/**
	******************************************************************************
	* Copyright © 2017-2018 PTA GmbH.
	* All rights reserved. This program and the accompanying materials
	* are made available under the terms of the Eclipse Public License v1.0
	* which accompanies this distribution, and is available at
	*
	* http://www.eclipse.org/legal/epl-v10.html
	*
	******************************************************************************
	*/
	package org.eclipse.openk.elogbook.persistence.dao;

	import org.eclipse.openk.elogbook.common.NotificationStatus;
	import org.eclipse.openk.elogbook.exceptions.BtbException;
	import org.eclipse.openk.elogbook.exceptions.BtbInternalServerError;
	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.persistence.util.NotificationQueryCreator;
	import org.eclipse.openk.elogbook.persistence.util.TimestampConverter;
	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.ArrayList;
	import java.util.List;
	import java.util.UUID;

	public class TblNotificationDao extends GenericDaoJpa<TblNotification, Integer> {
	private static final String VIEW_ACTIVE_NOTIFICATION = EntityHelper.instance().makeIdentifier("VIEW_ACTIVE_NOTIFICATION");
	private static final String FK_REF_NOTIFICATION_STATUS = EntityHelper.instance().makeIdentifier("fk_ref_notification_status");
	private static final String TBL_NOTIFICATION = EntityHelper.instance().makeIdentifier("TBL_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 RESPONSIBILITY_FORWARDING = EntityHelper.instance().makeIdentifier("responsibility_forwarding");
	private static final String RESPONSIBILITY_FORW_UUID = EntityHelper.instance().makeIdentifier("responsibility_forwarding_uuid");



	public TblNotificationDao() {
	super();
	}

	public TblNotificationDao(EntityManager em) {
	super(em);
	}

	/*-
	* Mögliche Status:
	* 1 offen
	* 2 in Arbeit
	* 3 erledigt
	* 4 geschlossen
	*/

	/**
	* Returns the latest version of all notifications.
	*
	* @return List of notifications
	*/
	public List<TblNotification> getActiveNotifications() throws BtbInternalServerError {
	return refreshModelCollection(
	getActiveNotifications(null, null, null, new ArrayList<>()));
	}

	/**
	* Get the List of Active Notifications. The active notifications view is used.
	*
	* @param nsf
	* search filter to narrow the navigations to be returned
	* @param whereClause
	* the where clause to be completed
	* @param tablePrefix
	* the table prefix
	* @param tblResponsibilities
	* the list of responsibilities
	* @return the list of active notifications
	* @throws BtbInternalServerError
	* internal Server Error
	*/
	@SuppressWarnings("unchecked")
	private List<TblNotification> getActiveNotifications(NotificationSearchFilter nsf, String whereClause,
	String tablePrefix, List<TblResponsibility> tblResponsibilities)
	throws BtbInternalServerError {

	try {
	NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), tablePrefix);
	Query q = queryCreator.generateNotificationQuery(nsf, whereClause, tblResponsibilities);
	return refreshModelCollection((List<TblNotification>) q.getResultList());
	} catch (Exception e) {
	LOGGER.error("Error while retrieving notifications", e);
	throw new BtbInternalServerError("Error retrieving notifications");
	}
	}

	@SuppressWarnings("unchecked")
	private List<TblNotification> getActiveNotificationsWithReminder(ReminderSearchFilter rsf, String whereClause,
	String tablePrefix, List<TblResponsibility> tblResponsibilities)
	throws BtbInternalServerError {

	try {
	NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), tablePrefix);
	Query q = queryCreator.generateNotificationQueryWithReminder(rsf, whereClause, tblResponsibilities);
	return refreshModelCollection((List<TblNotification>) q.getResultList());
	} catch (Exception e) {
	LOGGER.error("Error while retrieving notifications with reminder", e);
	throw new BtbInternalServerError("Error retrieving notifications with reminder");
	}
	}

	public List<TblNotification> getNotificationsWithReminder(ReminderSearchFilter rsf, List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {

	try {
	String where = "v."+FK_REF_NOTIFICATION_STATUS+" IN ( " + NotificationStatus.OPEN.id + "," + NotificationStatus.INPROGRESS.id + " ) ";
	return refreshModelCollection(getActiveNotificationsWithReminder(rsf, where, "v", tblResponsibilities));
	} catch (Exception t) {
	LOGGER.error(t);
	throw new BtbInternalServerError("Error loading notifications");
	}
	}


	/**
	* Returns all Notifications with status "closed" that are not older than x
	* hours.
	*
	* @return List of notifications
	*/
	public List<TblNotification> getPastNotifications(NotificationSearchFilter nsf,
	List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {
	String where = "v."+FK_REF_NOTIFICATION_STATUS+" IN ( " + NotificationStatus.CLOSED.id + " ) ";
	return refreshModelCollection(getActiveNotifications(nsf, where, "v", tblResponsibilities));
	}

	/**
	* Returns the Notifications with Status "open", "in progess" or "finished"
	* that have a begin date before tomorrow.
	*
	* @param notificationSearchFilter
	* The filter to determine the the search criteria.
	* @return List of notifications
	*/
	public List<TblNotification> getOpenNotifications(NotificationSearchFilter notificationSearchFilter,
	List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {
	String where = "v."+FK_REF_NOTIFICATION_STATUS+" IN (" + NotificationStatus.OPEN.id + ","
	+ NotificationStatus.INPROGRESS.id + ","
	+ NotificationStatus.FINISHED.id+ ")";
	notificationSearchFilter.setDateTo(
	TimestampConverter.getTimestampWithTime(
	new Timestamp(System.currentTimeMillis() + 1000 * 60 * 60 * 24), 0, 0, 0 ));
	return refreshModelCollection(
	getActiveNotifications(notificationSearchFilter, where, "v", tblResponsibilities));
	}

	public List<TblNotification> getFutureNotifications(NotificationSearchFilter nsf,
	List<TblResponsibility> tblResponsibilities) throws BtbInternalServerError {
	String where = "v."+FK_REF_NOTIFICATION_STATUS+" NOT IN (" + NotificationStatus.CLOSED.id + ","
	+ NotificationStatus.FINISHED.id + ")";
	return refreshModelCollection(getActiveNotifications(nsf, where, "v", tblResponsibilities));
	}


	@SuppressWarnings("unchecked")
	public List<TblNotification> getByIncidentId(int incidentId) throws BtbInternalServerError {
	try {
	String selectString = "select * from "+TBL_NOTIFICATION+" n where n."+INCIDENT_ID+"=? order by n."+VERSION+" desc";
	Query q = getEM().createNativeQuery(selectString, TblNotification.class);
	q.setParameter(1, incidentId);
	return refreshModelCollection(q.getResultList());
	} catch (Exception t) {
	LOGGER.error(t);
	throw new BtbInternalServerError("Error loading notifications by incidentId");
	}
	}

	@SuppressWarnings("unchecked")
	public List<String> getAssignedUserSuggestions() throws BtbInternalServerError {

	try {
	String selectString = "select distinct "+RESPONSIBILITY_FORWARDING+" from "+
	VIEW_ACTIVE_NOTIFICATION+" n where n."+RESPONSIBILITY_FORWARDING+" is not null";
	Query q = getEM().createNativeQuery(selectString);
	return q.getResultList();
	} catch (Exception t) {
	LOGGER.error(t);
	throw new BtbInternalServerError("Error loading notifications");
	}
	}

	public List<UUID> getDistinctResponsibilityForwardingUuid() throws BtbInternalServerError {
	try {
	String selectString = "select distinct "+RESPONSIBILITY_FORW_UUID+" from "+TBL_NOTIFICATION+" n where n.responsibility_forwarding_uuid is not null";
	Query q = getEM().createNativeQuery(selectString);
	return q.getResultList();

	} catch (Exception t) {
	LOGGER.error(t);
	throw new BtbInternalServerError("Error loading distict responsibility forwarding uuid");
	}
	}

	public void updateResponsibilityForwardingByContactUuid( UUID contactUuid, String contactName ) {
	try {
	String selectString = "update "+TBL_NOTIFICATION+" set "+RESPONSIBILITY_FORWARDING+" = ? where "+RESPONSIBILITY_FORW_UUID+" = ?";
	Query q = getEM().createNativeQuery(selectString);
	q.setParameter(2, contactUuid);
	q.setParameter(1, contactName);
	q.executeUpdate();

	} catch (Exception t) {
	LOGGER.error(t);
	}
	}


	/**
	* Create and execute the query to get a collection of notifications.
	*
	* @param hTblResponsibilities
	* the responsibilities containing branch and grid territory references
	* @param listType
	* the {@link ListType}
	* @return a List of historical notifications relevant for the responsibility given by parameter.
	* @throws BtbException if an error occurs.
	*/
	@SuppressWarnings("unchecked")
	public List<TblNotification> findHistoricalNotificationsByResponsibility(
	List<HTblResponsibility> hTblResponsibilities, ListType listType) throws BtbInternalServerError {
	try {
	NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), "");
	Query query = queryCreator.generateFindHistoricalNotificationsByResponsibilityQuery(hTblResponsibilities,
	listType);
	List<TblNotification> tblNotifications = refreshModelCollection(
	(List<TblNotification>) query.getResultList());

	if (LOGGER.isDebugEnabled()) {
	for (TblNotification tblNotification : tblNotifications) {
	LOGGER.debug("Notification found: " + tblNotification.getId() + "/"
	+ tblNotification.getIncidentId() + tblNotification.getVersion());
	}
	}
	return tblNotifications;
	} catch (Exception e) {
	LOGGER.error(e);
	throw new BtbInternalServerError("Error retrieving historical notifications by responsibility");
	}
	}


	/**
	* Find notifications matching the search criteria in the global search filter.
	*
	* @param globalSearchFilter
	* contains the search criteria.
	* @return the notifications found, matching the given criteria.
	* @throws BtbInternalServerError
	* if an error occurs.
	*/
	@SuppressWarnings("unchecked")
	public List<TblNotification> findNotificationsMatchingSearchCriteria(GlobalSearchFilter globalSearchFilter)
	throws BtbInternalServerError {
	try {
	NotificationQueryCreator queryCreator = new NotificationQueryCreator(getEM(), "");
	Query query = queryCreator.generateFindNotificationsMatchingSearchCriteriaQuery(globalSearchFilter);
	List<TblNotification> results;
	results = query.getResultList();
	return refreshModelCollection(results);
	} catch (Exception e) {
	LOGGER.error(e);
	throw new BtbInternalServerError("Error retrieving notifications applying global search filter.");
	}
	}
	}