3 ================================================================================
5 ================================================================================
6 Copyright (C) 2017 AT&T Intellectual Property
7 ================================================================================
8 Licensed under the Apache License, Version 2.0 (the "License");
9 you may not use this file except in compliance with the License.
10 You may obtain a copy of the License at
12 http://www.apache.org/licenses/LICENSE-2.0
14 Unless required by applicable law or agreed to in writing, software
15 distributed under the License is distributed on an "AS IS" BASIS,
16 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 See the License for the specific language governing permissions and
18 limitations under the License.
19 ================================================================================
21 <!DOCTYPE hibernate-mapping PUBLIC
22 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
23 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
25 <!-- Publishes mappings and queries specific to the ECOMP Portal application. -->
26 <hibernate-mapping package="org.openecomp.portalapp.portal.domain">
28 <!-- Widget class mapping details -->
29 <class name="Widget" table="FN_WIDGET">
30 <id name="id" column="WIDGET_ID">
31 <generator class="native">
32 <param name="sequence">seq_fn_widget</param>
35 <property name="width" type="integer">
36 <column name="WDG_WIDTH" not-null="true" default="0"></column>
38 <property name="height" type="integer">
39 <column name="WDG_HEIGHT" not-null="true" default="0"></column>
41 <property name="url" type="string">
42 <column name="WDG_URL" not-null="true" default="?"></column>
44 <property name="name" type="string">
45 <column name="WDG_NAME" not-null="true" default="?"></column>
48 <property name="appId" type="long">
49 <column name="APP_ID" not-null="true"></column>
54 <class name="EPEndpoint" table="EP_ENDPOINTS">
55 <id name="id" column="id">
56 <generator class="native">
57 <param name="sequence">seq_ep_endpoint</param>
60 <property name="name" type="string">
61 <column name="url" not-null="true"></column>
66 <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
67 <id name="id" column="id">
68 <generator class="native">
69 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
72 <property name="ep_id" type="long">
73 <column name="ep_id"></column>
75 <property name="account_id" type="long">
76 <column name="account_id"></column>
80 <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
81 <id name="id" column="id">
82 <generator class="native">
83 <param name="sequence">seq_ep_widget_catalog_parameter</param>
86 <property name="widgetId" type="long">
87 <column name="widget_id"></column>
89 <property name="userId" type="long">
90 <column name="user_id" not-null="true"></column>
92 <property name="paramId" type="long">
93 <column name="param_id" not-null="true"></column>
96 <property name="user_value" type="string">
97 <column name="user_value" not-null="true"></column>
102 <class name="BasicAuthCredentials" table="ep_basic_auth_account">
103 <id name="id" column="id">
104 <generator class="native">
105 <param name="sequence">seq_ep_basic_auth_account</param>
108 <property name="applicationName" type="string">
109 <column name="ext_app_name" not-null="true" default="?"></column>
111 <property name="username" type="string">
112 <column name="username"></column>
114 <property name="password" type="string">
115 <column name="password"></column>
117 <property name="isActive" type="string">
118 <column name="active_yn" not-null="true" default="Y"></column>
122 <!-- EPUserNotification class mapping details -->
123 <class name="EPUserNotification" table="ep_user_notification">
124 <id name="id" column="id">
125 <generator class="native">
126 <param name="sequence">seq_ep_user_notification</param>
129 <property name="userId" column="User_ID" />
130 <property name="notificationId" column="notification_ID" />
131 <property name="viewed" column="is_viewed" />
132 <property name="updateTime" column="updated_time" />
136 <!-- User class mapping details -->
137 <class name="EPUser" table="FN_USER">
138 <id name="id" column="user_id">
139 <generator class="native">
140 <param name="sequence">seq_fn_user</param>
144 <property name="orgId" column="org_id" />
145 <property name="managerId" column="manager_id" />
146 <property name="firstName" column="first_name" />
147 <property name="middleInitial" column="middle_name" />
148 <property name="lastName" column="last_name" />
149 <property name="phone" column="phone" />
150 <property name="fax" column="fax" />
151 <property name="cellular" column="cellular" />
152 <property name="email" column="email" />
153 <property name="addressId" column="address_id" />
154 <property name="alertMethodCd" column="alert_method_cd" />
156 <property name="address1" column="address_line_1" />
157 <property name="address2" column="address_line_2" />
158 <property name="city" column="city" />
159 <property name="state" column="state_cd" />
160 <property name="zipCode" column="zip_code" />
161 <property name="country" column="country_cd" />
163 <property name="hrid" column="hrid" />
164 <property name="orgUserId" column="org_user_id" />
165 <property name="orgCode" column="org_code" />
166 <property name="loginId" column="login_id" />
167 <property name="loginPwd" column="login_pwd" />
168 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
170 <property name="locationClli" column="location_clli" />
171 <property name="orgManagerUserId" column="org_manager_userid" />
172 <property name="company" column="company" />
173 <property name="department" column="department" />
174 <property name="departmentName" column="department_name" />
175 <property name="businessUnit" column="business_unit" />
176 <property name="businessUnitName" column="business_unit_name" />
177 <property name="jobTitle" column="job_title" />
178 <property name="siloStatus" column="silo_status" />
179 <property name="financialLocCode" column="fin_loc_code" />
181 <property name="active" column="active_yn" type="yes_no" />
182 <property name="internal" column="is_internal_yn" type="yes_no" />
184 <property name="created" type="timestamp" column="created_date" />
185 <property name="modified" type="timestamp" column="modified_date" />
187 <property name="createdId" column="created_id" />
188 <property name="modifiedId" column="modified_id" />
189 <property name="timeZoneId" column="timezone" />
191 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
193 <key column="user_id" />
194 <one-to-many class="org.openecomp.portalapp.portal.domain.EPUserApp" />
199 <class name="MicroserviceData" table="ep_microservice">
200 <id name="id" column="id">
201 <generator class="native">
202 <param name="sequence">seq_ep_microservice</param>
206 <property name="name" type="string">
207 <column name="name" not-null="true" default="?"></column>
210 <property name="desc" type="string">
211 <column name="description" not-null="true" default="?"></column>
214 <property name="appId" type="long">
215 <column name="appId" not-null="true"></column>
218 <property name="url" type="string">
219 <column name="endpoint_url" not-null="true" default="?"></column>
222 <property name="securityType" type="string">
223 <column name="security_type" not-null="true" default="?"></column>
227 <property name="username" type="string">
228 <column name="username" not-null="true" default="?"></column>
231 <property name="password" type="string">
232 <column name="password" not-null="true" default="?"></column>
235 <property name="active" type="string">
236 <column name="active" not-null="true" default="Y"></column>
240 <class name="MicroserviceParameter" table="ep_microservice_parameter">
241 <id name="id" column="id">
242 <generator class="native">
243 <param name="sequence">seq_ep_microservice_parameter</param>
247 <property name="serviceId" type="long">
248 <column name="service_id" not-null="true"></column>
251 <property name="para_key" type="string">
252 <column name="para_key" not-null="true" default="?"></column>
255 <property name="para_value" type="string">
256 <column name="para_value" not-null="true" default="?"></column>
260 <!-- UserApp class mapping details -->
261 <class name="EPUserApp" table="fn_user_role">
263 <key-property name="userId" type="long">
264 <column name="user_id" precision="11" scale="0" />
266 <key-many-to-one name="app"
267 class="org.openecomp.portalapp.portal.domain.EPApp" column="app_id" />
268 <key-many-to-one name="role"
269 class="org.openecomp.portalapp.portal.domain.EPRole" column="role_id" />
271 <property name="priority" type="java.lang.Short">
272 <column name="priority" precision="4" scale="0" />
276 <!-- User App class mapping details -->
277 <class name="EPApp" table="fn_app">
278 <id name="id" column="app_id">
279 <generator class="native">
280 <param name="sequence">seq_fn_app</param>
283 <property name="name" type="string">
284 <column name="app_name" not-null="true" default="?"></column>
286 <property name="imageUrl" column="app_image_url" />
287 <property name="description" column="app_description" />
288 <property name="notes" column="app_notes" />
289 <property name="url" column="app_url" />
290 <property name="alternateUrl" column="app_alternate_url" />
291 <property name="appRestEndpoint" column="app_rest_endpoint" />
292 <property name="mlAppName" type="string">
293 <column name="ml_app_name" not-null="true" default="?"></column>
295 <property name="mlAppAdminId" type="string">
296 <column name="ml_app_admin_id" not-null="true" default="?"></column>
298 <property name="motsId" column="mots_id" />
299 <property name="appPassword" type="string">
300 <column name="app_password" not-null="true" default="?"></column>
302 <property name="thumbnail" column="thumbnail" />
303 <property name="username" type="string">
304 <column name="app_username" not-null="true" default="?"></column>
306 <property name="open" type="yes_no">
307 <column name="open" not-null="true" default="Y"></column>
309 <property name="enabled" type="yes_no">
310 <column name="enabled" not-null="true" default="N"></column>
312 <property name="uebTopicName" type="string">
313 <column name="ueb_topic_name"></column>
315 <property name="uebKey" type="string">
316 <column name="ueb_key"></column>
318 <property name="uebSecret" type="string">
319 <column name="ueb_secret"></column>
321 <property name="appType" type="integer">
322 <column name="app_type"></column>
325 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
329 <!-- EPUserAppsSortPreference class mapping details -->
330 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
331 <id name="id" column="ID">
332 <generator class="native">
333 <param name="sequence">seq_ep_usrappsrtprf</param>
336 <property name="userId" column="USER_ID" />
337 <property name="sortPref" column="SORT_PREF" />
340 <!-- EPUserAppsManualSortPreference class mapping details -->
341 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
342 <id name="id" column="ID">
343 <generator class="native">
344 <param name="sequence">seq_ep_usrmanappsrtprf</param>
347 <property name="userId" column="user_id"
348 unique-key="uk_1_ep_pers_user_app_man_sort" />
349 <property name="appId" column="app_id"
350 unique-key="uk_1_ep_pers_user_app_man_sort" />
351 <property name="appManualSortOrder" column="sort_order" type="int" />
354 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
355 <id name="id" column="ID">
356 <generator class="native">
357 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
360 <property name="userId" column="user_id" />
361 <property name="widgetId" column="widget_id" />
362 <property name="widgetRow" column="x" />
363 <property name="widgetCol" column="y" />
364 <property name="widgetHeight" column="height" />
365 <property name="widgetWidth" column="width" />
368 <!-- User Role class mapping details -->
369 <class name="EPRole" table="FN_ROLE">
370 <id name="id" column="role_id">
371 <generator class="native">
372 <param name="sequence">seq_fn_role</param>
376 <property name="name" column="role_name" />
377 <property name="priority" column="priority" />
378 <property name="active" column="active_yn" type="yes_no" />
379 <!-- 2 lines below may be removed! -->
380 <property name="appId" column="APP_ID" />
381 <property name="appRoleId" column="APP_ROLE_ID" />
382 <!-- 2 lines above may be removed -->
384 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
386 <key column="role_id" />
387 <many-to-many column="function_cd"
388 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
391 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
393 <key column="parent_role_id" />
394 <many-to-many column="child_role_id"
395 class="org.openecomp.portalapp.portal.domain.EPRole" />
398 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
400 <key column="child_role_id" />
401 <many-to-many column="parent_role_id"
402 class="org.openecomp.portalapp.portal.domain.EPRole" />
407 <!-- User App class mapping details -->
408 <class name="AppContactUs" table="fn_app_contact_us">
409 <id name="id" column="app_id">
410 <generator class="foreign">
411 <param name="property">app</param>
414 <property name="url" column="url" />
415 <property name="description" column="description" />
416 <property name="contactName" column="contact_name" />
417 <property name="contactEmail" column="contact_email" />
418 <property name="activeYN" column="active_yn" />
420 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
424 <!-- Personalization of user app selections -->
425 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
426 <id name="id" column="id">
427 <generator class="native">
428 <param name="property">seq_fn_pers_user_app_sel</param>
431 <property name="userId" column="user_id" />
432 <property name="appId" column="app_id" />
433 <property name="statusCode" column="status_cd" />
436 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
437 <id name="id" column="id">
438 <generator class="native">
439 <param name="property">seq_ep_pers_user_widget_sel</param>
442 <property name="userId" column="user_id" />
443 <property name="widgetId" column="widget_id" />
444 <property name="statusCode" column="status_cd" />
447 <!-- EPUserAppRolesRequest class mapping details -->
448 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
449 <id name="id" column="req_id">
450 <generator class="native">
451 <param name="sequence">seq_ep_user_role_request</param>
454 <property name="userId" column="user_id" />
455 <property name="appId" column="app_id" />
456 <property name="createdDate" column="created_date" type="timestamp" />
457 <property name="updatedDate" column="updated_date" type="timestamp" />
458 <property name="requestStatus" column="request_status" />
460 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
461 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
462 <key column="req_id" />
464 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
468 <!-- EPUserAppRolesRequestDetail class mapping details -->
469 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
470 <id name="id" column="id">
471 <generator class="native">
472 <param name="sequence">seq_ep_user_role_request_det</param>
475 <property name="reqRoleId" column="requested_role_id" />
476 <property name="reqType" column="request_type" />
477 <many-to-one name="epRequestIdData" fetch="select"
478 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
479 <column name="req_id" not-null="true" />
483 <!-- show the current user plus related users -->
484 <sql-query name="relatedUsers">
485 <return-scalar column="org_user_id" type="java.lang.String" />
487 select t.org_user_id from (
488 select distinct c.org_user_id org_user_id, c.last_login_date from
489 (select c.org_user_id, c.last_login_date from
491 (select distinct role_id, app_id from fn_user_role a1, fn_user a2 where a1.user_id = a2.user_id and a2.org_user_id=:userId ) b,
493 where a.ROLE_ID = b.role_id
494 and a.APP_ID = b.app_id
495 and a.USER_ID = c.user_id
497 select org_user_id , last_login_date from fn_user where org_user_id=:userId
499 order by c.last_login_date desc limit 10
504 <!-- requires values for named parameters :searchQuery and :userId -->
505 <sql-query name="searchPortal">
506 <return alias="searchResult"
507 class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
509 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
511 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
513 upper( :searchQuery ) != ''
514 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
515 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
516 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
517 and upper(active_yn) = 'Y'
521 select distinct 'Application' CATEGORY, b1.app_name NAME,
522 if (b1.app_type = 2,'false','true') UUID,
523 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
527 from fn_user_role a, fn_user b, fn_app c
528 where a.USER_ID = b.user_id
529 and upper(b.org_user_id) = upper( :userId )
530 and a.app_id = c.app_id
531 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
532 and upper(c.ENABLED) = 'Y'
533 ) a1 right outer join
534 (select * from fn_app where
535 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
536 and upper(ENABLED) = 'Y') b1
537 on a1.APP_ID = b1.app_id
541 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
542 if (b1.app_type = 2,'false','true') UUID,
543 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
545 (select distinct d.*, e.app_type from fn_user a,
546 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
547 where a.USER_ID = b.user_id
548 and b.role_id = c.role_id
549 and c.menu_id = d.menu_id
550 and upper(a.org_user_id) = upper( :userId )
551 and c.APP_ID = e.app_id
552 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
553 and upper(d.active_yn) = 'Y'
554 and upper(e.enabled) = 'Y'
555 ) a1 right outer join
557 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
558 where active_yn = 'Y'
559 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
560 and a.menu_id = b.menu_id
561 and b.app_id = c.app_id
564 on a1.menu_id = b1.menu_id
567 select distinct CATEGORY, NAME, UUID, TARGET
571 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
574 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
575 where upper(a.org_user_id) = upper( :userId )
576 and a.user_id = b.user_id
577 and b.role_id = c.role_id
578 and c.widget_id = d.widget_id
579 and d.all_user_flag<>'Y'
580 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
584 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
587 from ep_widget_catalog d
588 where d.all_user_flag='Y'
589 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
593 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
596 from fn_user a, fn_user_role b, ep_widget_catalog d
597 where upper(a.org_user_id) = upper( :userId )
598 and a.user_id = b.user_id
600 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
604 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
610 <!-- requires values for named parameters :searchQuery and :userId -->
611 <sql-query name="getNotifications">
612 <return alias="notificationResult"
613 class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
615 select @rn /*'*/:=/*'*/ @rn+1 AS 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, active_YN from
618 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,active_YN
621 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, creator_ID,active_YN
624 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
625 a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,b.role_id,b.recv_user_id
626 from ep_notification a, ep_role_notification b
627 where a.notification_id = b.notification_id
628 and (end_time is null || SYSDATE() <= end_time )
629 and (start_time is null || SYSDATE() >= start_time)
630 and a.is_for_all_roles = 'N'
633 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
634 from fn_user a, fn_user_role b, fn_role c, fn_app d
635 where COALESCE(c.app_id,1) = d.app_id
636 and a.user_id = b.user_id
637 and a.user_id = :user_id
638 and b.role_id = c.role_id
639 and (d.enabled='Y' or d.app_id=1)
643 a.role_id = b.role_id
646 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, creator_ID,active_YN
649 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
650 a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date, b.role_id,b.recv_user_id
651 from ep_notification a, ep_role_notification b
652 where a.notification_id = b.notification_id
653 and (end_time is null || SYSDATE() <= end_time )
654 and (start_time is null || SYSDATE() >= start_time)
655 and a.is_for_all_roles = 'N'
659 a.recv_user_id=:user_id
663 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, creator_ID,active_YN
664 from ep_notification a
665 where a.notification_id
666 and (end_time is null || SYSDATE() <= end_time )
667 and (start_time is null || SYSDATE() >= start_time)
668 and a.is_for_all_roles = 'Y'
676 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'
678 order by priority desc, created_date desc,start_Time desc
682 (SELECT @rn /*'*/:=/*'*/ 0) t2
688 <sql-query name="getUsersByOrgIdsNotifications">
689 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
691 SELECT * from fn_user where org_user_id in (:OrgIds)
697 <!-- Gets all Admin notification history; accepts no parameters -->
698 <sql-query name="getAdminNotificationHistoryVO">
699 <return alias="adminNotificationHistoryVOResult"
700 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
704 n.notification_ID AS notificationId,
705 n.is_for_online_users AS isForOnlineUsers,
706 n.is_for_all_roles AS isForAllRoles,
707 n.active_YN AS activeYn,
708 n.msg_header AS msgHeader,
709 n.msg_description AS msgDescription,
710 n.msg_source AS msgSource,
711 n.start_Time AS startTime,
712 n.end_time AS endTime,
714 n.creator_ID AS creatorId,
715 n.created_date AS createdDate,
716 u.org_user_id AS loginId
720 fn_user u on u.user_id = n.creator_id
722 n.active_YN='Y' and msg_source='EP'
727 n.notification_ID AS notificationId,
728 n.is_for_online_users AS isForOnlineUsers,
729 n.is_for_all_roles AS isForAllRoles,
730 n.active_YN AS activeYn,
731 n.msg_header AS msgHeader,
732 n.msg_description AS msgDescription,
733 n.msg_source AS msgSource,
734 n.start_Time AS startTime,
735 n.end_time AS endTime,
737 n.creator_ID AS creatorId,
738 n.created_date AS createdDate,
739 u.org_user_id AS loginId
743 fn_user u on u.user_id = n.creator_id
745 ep_role_notification r on r.notification_ID=n.notification_ID
747 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
748 )n order by n.startTime desc
752 <sql-query name="getEpNotificationAppRoles">
753 <return alias="notificationAppRoles"
754 class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
755 <!-- This query requires no parameters. -->
758 select a.app_id, a.app_name, b.role_id, b.role_name from
759 (select * from fn_app where app_id = 1) a,
760 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
762 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
764 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' order by app_name
771 <!-- Gets all notification history -->
772 <sql-query name="getNotificationHistoryVO">
773 <return alias="notificationHistoryVOResult"
774 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
777 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
778 startTime, endTime, priority, createdDate, creatorId, loginId, activeYn
782 a.notification_ID AS notificationId,
783 is_for_online_users AS isForOnlineUsers,
784 is_for_all_roles AS isForAllRoles,
785 msg_header AS msgHeader,
786 msg_description AS msgDescription,
787 msg_source AS msgSource,
788 start_Time AS startTime,
791 created_date AS createdDate,
792 creator_ID AS creatorId,
794 active_YN AS activeYn,
795 if (is_viewed is null, 'N', is_viewed)
799 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
800 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
804 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
805 a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,
806 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
809 END AS login_id,b.recv_user_id
810 from ep_notification a, ep_role_notification b,fn_user u
811 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
812 and a.is_for_all_roles = 'N'
814 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
816 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
818 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
820 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
824 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
825 from fn_user a, fn_user_role b, fn_role c, fn_app d
826 where COALESCE(c.app_id,1) = d.app_id
827 and a.user_id = b.user_id
828 and a.user_id = :user_id
829 and b.role_id = c.role_id
830 and (d.enabled='Y' or d.app_id=1)
834 a.role_id = b.role_id
838 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
839 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
843 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
844 a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,
845 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
848 END AS login_id,b.recv_user_id
849 from ep_notification a, ep_role_notification b,fn_user u
850 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
851 and a.is_for_all_roles = 'N'
853 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
855 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
857 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
859 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
864 a.recv_user_id=:user_id
869 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
870 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
871 creator_ID, a.active_YN
872 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
873 where a.notification_id
874 and a.is_for_all_roles = 'Y'
876 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
878 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
880 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
882 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
885 ) a left outer join (
886 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
888 on a.notification_id = m.notification_ID
892 order by start_Time desc,end_time desc
894 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
899 <!-- shows the received recipient to whom the notification is delivered from external system -->
900 <sql-query name="messageRecipients">
901 <return-scalar column="org_user_id" type="java.lang.String" />
903 select u.org_user_id from ep_notification n join ep_role_notification r on r.notification_ID=n.notification_ID join fn_user u on u.user_id=r.recv_user_id where n.notification_id=:notificationId
908 <!-- Gets role details for a specified notification -->
909 <sql-query name="getNotificationRoles">
910 <return alias="notificationRolesResult"
911 class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
917 notification_Id = :notificationId
921 <!-- Gets all applications, possibly with contact information -->
922 <sql-query name="getAppsAndContacts">
923 <return alias="appContactUs"
924 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
925 <!-- This query requires no parameters. -->
928 a.app_id as appId, a.app_name as appName,
929 c.contact_name as contactName,
930 c.contact_email as contactEmail, c.url, c.description,
931 c.active_yn as activeYN
936 on a.app_id = c.app_id
938 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
943 <!-- Gets all rows from the app-contact-us table, extended with app information -->
944 <sql-query name="getAppContactUsItems">
945 <return alias="appContactUs"
946 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
947 <!-- This query requires no parameters. -->
950 c.app_id as appId, c.contact_name as contactName,
951 c.contact_email as contactEmail, c.url, c.description,
952 c.active_yn as activeYN, a.app_name as appName
957 on a.app_id = c.app_id
959 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
964 <!-- Gets one row for each app with the category and catenated functions. -->
965 <sql-query name="getAppCategoryFunctions">
966 <return alias="appCategoryFunctions"
967 class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
968 <!-- This query requires no parameters. -->
970 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
971 r.category as category, r.app_id as appId, r.app_name as application,
972 group_concat(function_text separator ', ') as functions
975 app_id, function_text, app_name,
976 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
979 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
980 k.text parent_menu, k.parent_menu_id parent_menu_id
983 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
985 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
987 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
990 WHERE j.parent_menu_id = k.menu_id
993 WHERE fn.parent_menu_id = a.menu_id
995 (SELECT @rn /*'*/:=/*'*/ 0) t2
996 group by r.category, r.app_id, r.app_name
997 order by category, app_name
1002 <!-- Gets one row for each function-application-role combination. -->
1003 <sql-query name="getAppAccessFunctionRole">
1004 <return alias="appAccessFunctionRole"
1005 class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
1006 <!-- This query requires one parameters. -->
1008 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1009 (select (case when fur.role_id is not null then 'Y' end) from fn_user_role fur where fur.user_id =:userId and fur.role_id = t.role_id and fur.app_id = t.app_id) as role_actv,
1010 o.requested_role_id,o.request_type
1012 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1015 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1018 where COALESCE(c.app_id,1) = d.app_id
1019 and (d.enabled='Y' or d.app_id=1)
1020 and c.active_yn = 'Y'
1023 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1025 A.MENU_ID = B.MENU_ID
1028 on a.role_id = b.role_id) t left outer join
1029 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1030 where er.req_id=ed.req_id
1031 and upper(ed.request_type)='P'
1032 and er.user_id =:userId) o
1033 on t.app_id=o.app_id
1034 and t.role_id = o.requested_role_id
1035 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1040 <query name="getCommonWidgetItem">
1041 from CommonWidget where category = :cat order by
1045 <sql-query name="getGuestLastLogin">
1046 <return-scalar column="audit_date" type="java.util.Date" />
1048 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1052 <sql-query name="getAppsAdmins">
1053 <return alias="adminUserApp" class="org.openecomp.portalapp.portal.domain.AdminUserApp" />
1055 SELECT apps.APP_NAME, apps.APP_ID, user.USER_ID, user.FIRST_NAME, user.LAST_NAME, user.org_user_id FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_app apps ON apps.APP_ID = userrole.APP_ID WHERE user.active_yn='Y' AND userrole.ROLE_ID = :accountAdminRoleId AND (apps.ENABLED = 'Y' OR apps.APP_ID=1)
1059 <sql-query name="getPortalAdmins">
1060 <return alias="portalAdmin" class="org.openecomp.portalapp.portal.transport.PortalAdmin" />
1062 SELECT u.user_id, u.first_name, u.last_name, u.login_id FROM fn_user u, fn_user_role ur WHERE u.active_yn = 'Y' AND u.user_id = ur.user_id AND ur.role_id= :adminRoleId
1066 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1067 <sql-query name="getPersUserApps">
1068 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1069 <!-- This query requires one parameter: userId (number) -->
1072 distinct -- multiple roles yield multiple rows
1073 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1074 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1075 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1076 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1077 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1079 -- Portal assigns role 999 to app administrator
1080 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1081 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1082 where a.ENABLED = 'Y'
1084 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1086 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1093 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1095 <sql-query name="getPersAdminApps">
1096 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1097 <!-- This query requires one parameter: userId (number) -->
1100 distinct -- multiple roles yield multiple rows
1101 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1102 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1103 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1104 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1105 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1107 -- Portal assigns role 999 to app administrator
1108 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1109 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1110 where a.ENABLED = 'Y'
1112 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1114 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1116 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1123 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1124 <sql-query name="getPersAdminAppsOrderByName">
1125 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1126 <!-- This query requires one parameter: userId (number) -->
1129 distinct -- multiple roles yield multiple rows
1130 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1131 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1132 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1133 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1134 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1136 -- Portal assigns role 999 to app administrator
1137 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1138 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1139 where a.ENABLED = 'Y'
1141 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1143 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1145 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1153 <!--Gets personalized list of enabled and accessible User apps by name -->
1154 <sql-query name="getPersUserAppsOrderByName">
1155 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1156 <!-- This query requires one parameter: userId (number) -->
1159 distinct -- multiple roles yield multiple rows
1160 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1161 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1162 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1163 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1164 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1166 -- Portal assigns role 999 to app administrator
1167 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1168 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1169 where a.ENABLED = 'Y'
1171 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1173 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1182 <!--Gets personalized list of enabled and accessible Super Admin apps by
1184 <sql-query name="getAdminAppsOrderByLastUsed">
1185 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1191 distinct -- multiple roles yield multiple rows
1192 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1193 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1194 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1195 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1196 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1198 -- Portal assigns role 999 to app administrator
1199 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1200 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1201 where a.ENABLED = 'Y'
1203 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1205 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1207 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1212 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1215 on A.app_id = B.Affected_record_id
1216 order by AUDIT_DATE DESC;
1220 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1221 <sql-query name="getUserAppsOrderByLastUsed">
1222 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1225 select * from (select
1226 distinct -- multiple roles yield multiple rows
1227 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1228 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1229 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1230 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1231 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1233 -- Portal assigns role 999 to app administrator
1234 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1235 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1236 where a.ENABLED = 'Y'
1238 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1240 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1244 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1247 on A.app_id = B.Affected_record_id
1248 order by AUDIT_DATE DESC;
1255 <!--Gets personalized list of enabled and accessible Super Admin apps by
1257 <sql-query name="getAdminAppsOrderByMostUsed">
1258 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1264 distinct -- multiple roles yield multiple rows
1265 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1266 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1267 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1268 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1269 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1271 -- Portal assigns role 999 to app administrator
1272 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1273 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1274 where a.ENABLED = 'Y'
1276 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1278 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1280 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1284 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1285 from FN_APP LEFT JOIN FN_AUDIT_LOG
1286 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1287 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1288 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1289 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1291 on A.app_id = B.Affected_record_id
1292 order by total_visits DESC;
1297 <!--Gets personalized list of enabled and accessible NON Super Admin User
1298 apps by Most Used -->
1299 <sql-query name="getUserAppsOrderByMostUsed">
1300 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1305 distinct -- multiple roles yield multiple rows
1306 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1307 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1308 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1309 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1310 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1312 -- Portal assigns role 999 to app administrator
1313 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1314 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1315 where a.ENABLED = 'Y'
1317 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1319 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1322 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1323 from FN_APP LEFT JOIN FN_AUDIT_LOG
1324 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1325 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1326 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1327 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1329 on A.app_id = B.Affected_record_id
1330 order by total_visits DESC;
1334 <!--Gets personalized list of enabled and accessible Super Admin apps by
1336 <sql-query name="getAdminAppsOrderByManual">
1337 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1342 distinct -- multiple roles yield multiple rows
1343 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1344 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1345 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1346 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1347 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1349 -- Portal assigns role 999 to app administrator
1350 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1351 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1352 where a.ENABLED = 'Y'
1354 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1356 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1358 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1361 (select m.app_id, m.sort_order
1362 from ep_pers_user_app_man_sort m
1363 where USER_ID = :userId
1365 on A.APP_ID = B.app_id
1366 order by sort_order ASC
1372 <!--Gets personalized list of enabled and accessible NON Super admin User
1374 <sql-query name="getUserAppsOrderByManual">
1375 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1378 select * from (select
1379 distinct -- multiple roles yield multiple rows
1380 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1381 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1382 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1383 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1384 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1386 -- Portal assigns role 999 to app administrator
1387 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1388 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1389 where a.ENABLED = 'Y'
1391 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1393 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1396 (select m.app_id, m.sort_order
1397 from ep_pers_user_app_man_sort m
1398 where USER_ID = :userId
1400 on A.APP_ID = B.app_id
1401 order by sort_order ASC
1406 <!-- Gets regular user's list of enabled apps including accessible and select
1408 <sql-query name="getUserAppCatalog">
1409 <return alias="userAppCatalog"
1410 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1411 <!-- This query requires one parameter: userId (number) -->
1414 distinct -- multiple roles yield multiple rows
1415 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1416 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1417 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1418 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1419 IF(a.open = 'Y', TRUE, FALSE) as open,
1420 -- ACCESS(-ible) means user has a defined role OR the application is open
1422 -- regular app and user has a role
1423 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1425 when a.OPEN = 'Y' then TRUE
1428 -- SELECT(-ed) indicates user personalization
1430 -- regular app, user has a role, no personalization
1431 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1432 -- open app and has personalization
1433 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1436 -- PENDING indicates user checked a box
1438 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1442 -- Portal assigns role 999 to app administrator
1443 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1444 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1445 left outer join EP_USER_ROLES_REQUEST q ON a.APP_ID = q.APP_ID and q.USER_ID = :userId and q.request_status = 'P'
1446 where a.ENABLED = 'Y'
1448 -- Show accessible apps first, then the rest; sort by name within each set.
1449 order by access desc, app_name asc
1454 <sql-query name="getMyloginAppDetails">
1455 <return alias="myloginAppDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1458 SELECT * FROM fn_app where ml_app_name =:appName
1464 <sql-query name="deleteAccountEndpoint">
1466 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1471 <sql-query name="deleteAccountEndpointRecord">
1473 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1478 <sql-query name="deleteEPEndpoint">
1480 DELETE FROM ep_endpoints WHERE id =:epId
1485 <sql-query name="deleteBasicAuthAccount">
1487 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1492 <sql-query name="getAppRoles">
1493 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1496 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1501 <sql-query name="getPortalAppRoles">
1502 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1506 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1511 <sql-query name="deleteMicroserviceParameter">
1514 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1519 <sql-query name="deleteMicroserviceParameterById">
1522 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1527 <sql-query name="deleteWidgetCatalogParameter">
1530 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1535 <sql-query name="deleteMicroservice">
1538 DELETE FROM ep_microservice WHERE id =:serviceId
1543 <sql-query name="epUserAppId">
1544 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1548 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1553 <sql-query name="userAppsSortPreferenceQuery">
1554 <return alias="appsSortPreferenceQuery"
1555 class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1559 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1564 <sql-query name="userAppsManualSortPrfQuery">
1565 <return alias="AppsManualSortPrfQuery"
1566 class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1570 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1575 <sql-query name="userWidgetManualSortPrfQuery">
1576 <return alias="widgetManualSortPrfQuery"
1577 class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1579 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1583 <sql-query name="appRoles">
1584 <return alias="rolesForApp"
1585 class="org.openecomp.portalapp.portal.domain.EpUserAppRoles" />
1587 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID =:appRoleId and app_id =:appId
1592 <sql-query name="userAppRolesRequestList">
1593 <return alias="appRolesRequestList"
1594 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1596 SELECT req_id, user_id, app_id, created_date, updated_date, request_status from EP_USER_ROLES_REQUEST where user_id =:userId and app_id=:appId and request_status = 'P'
1601 <sql-query name="userAppRolesRequestDetailList">
1602 <return alias="appRolesRequestDetailList"
1603 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1606 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1611 <!-- Gets list of enabled apps including accessible and select statuses -->
1612 <sql-query name="getAdminAppCatalog">
1613 <return alias="adminAppCatalog"
1614 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1615 <!-- This query requires one parameter: userId (number) -->
1618 distinct -- multiple roles yield multiple rows
1619 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1620 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1621 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1622 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1623 IF(a.open = 'Y', TRUE, FALSE) as open,
1624 -- ACCESS(-ible) means user has a defined role OR the application is open
1626 -- regular app and user has a role
1627 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1629 when a.OPEN = 'Y' then TRUE
1632 -- SELECT(-ed) indicates user personalization
1634 -- regular app, user has a role, no personalization
1635 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1636 -- regular app, user has role, admin forced a personalization
1637 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1638 -- regular app, user has no role, admin forced a personalization
1639 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1640 -- open app and has personalization
1641 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1644 -- PENDING indicates user checked a box
1646 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1650 -- Portal assigns role 999 to app administrator
1651 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1652 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1653 left outer join EP_USER_ROLES_REQUEST q ON a.APP_ID = q.APP_ID and q.USER_ID = :userId and q.request_status = 'P'
1654 where a.ENABLED = 'Y'
1655 -- Show accessible apps first, then the rest; sort by name within each set.
1656 order by access desc, app_name asc
1661 <sql-query name="userAppCatalogRoles">
1662 <return alias="epUserAppCatalogRoles"
1663 class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1665 select a.req_id, B.requested_role_id , A.request_status , A.app_id , (select role_name from fn_role where role_id = B.requested_role_id) as role_name
1666 from ep_user_roles_request A left join ep_user_roles_request_det B
1667 on a.req_id = b.req_id
1668 where A.user_id=:userid
1669 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1670 and A.request_status ='P'
1675 <sql-query name="getUserApproles">
1676 <return alias="businessCardUserApplicationRoles"
1677 class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1681 select fr.role_name, fa.app_name
1683 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1684 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id and fu.org_user_id = :userId
1690 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1691 <sql-query name="deleteNotificationsFromEpNotificationTable">
1694 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1699 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1700 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1703 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1707 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1708 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1711 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1715 </hibernate-mapping>