X-Git-Url: https://gerrit.onap.org/r/gitweb?a=blobdiff_plain;f=portal-BE%2Fsrc%2Fmain%2Fjava%2Forg%2Fonap%2Fportal%2Fdomain%2Fdb%2Fep%2FEpNotification.java;fp=portal-BE%2Fsrc%2Fmain%2Fjava%2Forg%2Fonap%2Fportal%2Fdomain%2Fdb%2Fep%2FEpNotification.java;h=4da1bd991d099bef348799b8536c570412cf3394;hb=1d76d572d920e9c77ca1fb0db35169a90a5ecf3a;hp=a10cc3e321b89e6fe279099ad3b17f2cb0d28be8;hpb=f7966b9ae53834f9eb664b81ab2d0a3da3f7a9b5;p=portal.git diff --git a/portal-BE/src/main/java/org/onap/portal/domain/db/ep/EpNotification.java b/portal-BE/src/main/java/org/onap/portal/domain/db/ep/EpNotification.java index a10cc3e3..4da1bd99 100644 --- a/portal-BE/src/main/java/org/onap/portal/domain/db/ep/EpNotification.java +++ b/portal-BE/src/main/java/org/onap/portal/domain/db/ep/EpNotification.java @@ -42,17 +42,26 @@ package org.onap.portal.domain.db.ep; import java.io.Serializable; import java.time.LocalDateTime; +import java.util.List; import java.util.Set; import javax.persistence.CascadeType; import javax.persistence.Column; +import javax.persistence.ColumnResult; +import javax.persistence.ConstructorResult; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; +import javax.persistence.NamedNativeQueries; +import javax.persistence.NamedNativeQuery; +import javax.persistence.NamedQueries; +import javax.persistence.NamedQuery; import javax.persistence.OneToMany; import javax.persistence.SequenceGenerator; +import javax.persistence.SqlResultSetMapping; import javax.persistence.Table; +import javax.persistence.Transient; import javax.validation.constraints.Digits; import javax.validation.constraints.FutureOrPresent; import javax.validation.constraints.NotNull; @@ -64,6 +73,8 @@ import lombok.Getter; import lombok.NoArgsConstructor; import lombok.Setter; import org.hibernate.validator.constraints.SafeHtml; +import org.onap.portal.domain.db.fn.FnUser; +import org.onap.portal.domain.dto.transport.EpNotificationItemVO; /* CREATE TABLE `ep_notification` ( @@ -84,6 +95,77 @@ CREATE TABLE `ep_notification` ( ) */ +@NamedNativeQueries({ + @NamedNativeQuery( + name = "EpNotification.getNotifications", + query = "select rowId, notification_ID, is_for_online_users,is_for_all_roles, msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date, creator_ID,notification_hyperlink, active_YN from\n" + + "( \n" + + "select notification_ID, is_for_online_users, is_for_all_roles, msg_header, msg_description, msg_source,start_Time, end_time, priority,created_date, creator_ID,notification_hyperlink,active_YN\n" + + "from\n" + + " (\n" + + " select user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN\n" + + " from\n" + + " (\n" + + " select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,\n" + + " a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.notification_hyperlink,a.created_date,b.role_id,b.recv_user_id \n" + + " from ep_notification a, ep_role_notification b\n" + + " where a.notification_id = b.notification_id\n" + + " and (end_time is null || SYSDATE() <= end_time )\n" + + " and (start_time is null || SYSDATE() >= start_time)\n" + + " and a.is_for_all_roles = 'N'\n" + + " ) a,\n" + + " (\n" + + " select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME\n" + + " from fn_user a, fn_user_role b, fn_role c, fn_app d\n" + + " where COALESCE(c.app_id,1) = d.app_id\n" + + " and a.user_id = b.user_id\n" + + " and a.user_id = :user_id\n" + + " and b.role_id = c.role_id\n" + + " and (d.enabled='Y' or d.app_id=1)\n" + + " )b\n" + + " where\n" + + " (\n" + + " a.role_id = b.role_id\n" + + " )\n" + + " union\n" + + " select :user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN\n" + + " from\n" + + " (\n" + + " select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,\n" + + " a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date, a.notification_hyperlink,b.role_id,b.recv_user_id \n" + + " from ep_notification a, ep_role_notification b\n" + + " where a.notification_id = b.notification_id\n" + + " and (end_time is null || SYSDATE() <= end_time )\n" + + " and (start_time is null || SYSDATE() >= start_time)\n" + + " and a.is_for_all_roles = 'N'\n" + + " ) a\n" + + " where\n" + + " (\n" + + " a.recv_user_id=:user_id\n" + + " )\n" + + " union\n" + + " (\n" + + " select :user_id user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description, msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN\n" + + " from ep_notification a\n" + + " where a.notification_id\n" + + " and (end_time is null || SYSDATE() <= end_time )\n" + + " and (start_time is null || SYSDATE() >= start_time)\n" + + " and a.is_for_all_roles = 'Y'\n" + + " )\n" + + " ) a\n" + + " where\n" + + " active_YN = 'Y'\n" + + " and\n" + + " not exists\n" + + " (\n" + + " select ID,User_ID,notification_ID,is_viewed,updated_time from ep_user_notification m where user_id = :user_id and m.notification_id = a.notification_id and is_viewed = 'Y'\n" + + " )\n" + + " order by priority desc, created_date desc,start_Time desc\n" + + "\n" + + "\n" + + " ) t,\n")} +) + @Table(name = "ep_notification") @NoArgsConstructor @AllArgsConstructor @@ -92,11 +174,10 @@ CREATE TABLE `ep_notification` ( @Entity public class EpNotification implements Serializable { @Id - - @GeneratedValue(strategy = GenerationType.AUTO) + @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "notification_ID", length = 11, nullable = false) @Digits(integer = 11, fraction = 0) - private Long notificationID; + private Long notificationId; @Column(name = "is_for_online_users", length = 1, columnDefinition = "char(1) default 'N'") @Pattern(regexp = "[YNyn]") @Size(max = 1) @@ -130,13 +211,13 @@ public class EpNotification implements Serializable { private LocalDateTime startTime; @Column(name = "end_time") @FutureOrPresent - private LocalDateTime end_time; + private LocalDateTime endTime; @Column(name = "priority", length = 11) @Digits(integer = 11, fraction = 0) private Long priority; - @Column(name = "creator_ID", length = 11) + @Column(name = "creator_Id", length = 11) @Digits(integer = 11, fraction = 0) - private Long creatorID; + private FnUser creatorId; @Column(name = "created_date") @FutureOrPresent private LocalDateTime createdDate; @@ -144,9 +225,11 @@ public class EpNotification implements Serializable { @Size(max = 512) @SafeHtml private String notificationHyperlink; + @Transient + private List roleIds; @OneToMany( targetEntity = EpRoleNotification.class, - mappedBy = "notificationID", + mappedBy = "notificationId", cascade = CascadeType.MERGE, fetch = FetchType.LAZY )