3 ============LICENSE_START==========================================
5 ===================================================================
6 Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
7 ===================================================================
9 Unless otherwise specified, all software contained herein is licensed
10 under the Apache License, Version 2.0 (the "License");
11 you may not use this software except in compliance with the License.
12 You may obtain a copy of the License at
14 http://www.apache.org/licenses/LICENSE-2.0
16 Unless required by applicable law or agreed to in writing, software
17 distributed under the License is distributed on an "AS IS" BASIS,
18 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
19 See the License for the specific language governing permissions and
20 limitations under the License.
22 Unless otherwise specified, all documentation contained herein is licensed
23 under the Creative Commons License, Attribution 4.0 Intl. (the "License");
24 you may not use this documentation except in compliance with the License.
25 You may obtain a copy of the License at
27 https://creativecommons.org/licenses/by/4.0/
29 Unless required by applicable law or agreed to in writing, documentation
30 distributed under the License is distributed on an "AS IS" BASIS,
31 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
32 See the License for the specific language governing permissions and
33 limitations under the License.
35 ============LICENSE_END============================================
37 ECOMP is a trademark and service mark of AT&T Intellectual Property.
39 <!DOCTYPE hibernate-mapping PUBLIC
40 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
41 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
43 <!-- Publishes mappings and queries specific to the ECOMP Portal application. -->
44 <hibernate-mapping package="org.onap.portalapp.portal.domain">
46 <!-- Widget class mapping details -->
47 <class name="Widget" table="FN_WIDGET">
48 <id name="id" column="WIDGET_ID">
49 <generator class="native">
50 <param name="sequence">seq_fn_widget</param>
53 <property name="width" type="integer">
54 <column name="WDG_WIDTH" not-null="true" default="0"></column>
56 <property name="height" type="integer">
57 <column name="WDG_HEIGHT" not-null="true" default="0"></column>
59 <property name="url" type="string">
60 <column name="WDG_URL" not-null="true" default="?"></column>
62 <property name="name" type="string">
63 <column name="WDG_NAME" not-null="true" default="?"></column>
66 <property name="appId" type="long">
67 <column name="APP_ID" not-null="true"></column>
72 <class name="EPEndpoint" table="EP_ENDPOINTS">
73 <id name="id" column="id">
74 <generator class="native">
75 <param name="sequence">seq_ep_endpoint</param>
78 <property name="name" type="string">
79 <column name="url" not-null="true"></column>
84 <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
85 <id name="id" column="id">
86 <generator class="native">
87 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
90 <property name="ep_id" type="long">
91 <column name="ep_id"></column>
93 <property name="account_id" type="long">
94 <column name="account_id"></column>
98 <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
99 <id name="id" column="id">
100 <generator class="native">
101 <param name="sequence">seq_ep_widget_catalog_parameter</param>
104 <property name="widgetId" type="long">
105 <column name="widget_id"></column>
107 <property name="userId" type="long">
108 <column name="user_id" not-null="true"></column>
110 <property name="paramId" type="long">
111 <column name="param_id" not-null="true"></column>
114 <property name="user_value" type="string">
115 <column name="user_value" not-null="true"></column>
120 <class name="BasicAuthCredentials" table="ep_basic_auth_account">
121 <id name="id" column="id">
122 <generator class="native">
123 <param name="sequence">seq_ep_basic_auth_account</param>
126 <property name="applicationName" type="string">
127 <column name="ext_app_name" not-null="true" default="?"></column>
129 <property name="username" type="string">
130 <column name="username"></column>
132 <property name="password" type="string">
133 <column name="password"></column>
135 <property name="isActive" type="string">
136 <column name="active_yn" not-null="true" default="Y"></column>
140 <!-- EPUserNotification class mapping details -->
141 <class name="EPUserNotification" table="ep_user_notification">
142 <id name="id" column="id">
143 <generator class="native">
144 <param name="sequence">seq_ep_user_notification</param>
147 <property name="userId" column="User_ID" />
148 <property name="notificationId" column="notification_ID" />
149 <property name="viewed" column="is_viewed" />
150 <property name="updateTime" column="updated_time" />
154 <!-- User class mapping details -->
155 <class name="EPUser" table="FN_USER">
156 <id name="id" column="user_id">
157 <generator class="native">
158 <param name="sequence">seq_fn_user</param>
162 <property name="orgId" column="org_id" />
163 <property name="managerId" column="manager_id" />
164 <property name="firstName" column="first_name" />
165 <property name="middleInitial" column="middle_name" />
166 <property name="lastName" column="last_name" />
167 <property name="phone" column="phone" />
168 <property name="fax" column="fax" />
169 <property name="cellular" column="cellular" />
170 <property name="email" column="email" />
171 <property name="addressId" column="address_id" />
172 <property name="alertMethodCd" column="alert_method_cd" />
174 <property name="address1" column="address_line_1" />
175 <property name="address2" column="address_line_2" />
176 <property name="city" column="city" />
177 <property name="state" column="state_cd" />
178 <property name="zipCode" column="zip_code" />
179 <property name="country" column="country_cd" />
181 <property name="hrid" column="hrid" />
182 <property name="orgUserId" column="org_user_id" />
183 <property name="orgCode" column="org_code" />
184 <property name="loginId" column="login_id" />
185 <property name="loginPwd" column="login_pwd" />
186 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
188 <property name="locationClli" column="location_clli" />
189 <property name="orgManagerUserId" column="org_manager_userid" />
190 <property name="company" column="company" />
191 <property name="department" column="department" />
192 <property name="departmentName" column="department_name" />
193 <property name="businessUnit" column="business_unit" />
194 <property name="businessUnitName" column="business_unit_name" />
195 <property name="jobTitle" column="job_title" />
196 <property name="siloStatus" column="silo_status" />
197 <property name="financialLocCode" column="fin_loc_code" />
198 <property name="costCenter" column="cost_center" />
200 <property name="active" column="active_yn" type="yes_no" />
201 <property name="internal" column="is_internal_yn" type="yes_no" />
203 <property name="created" type="timestamp" column="created_date" />
204 <property name="modified" type="timestamp" column="modified_date" />
206 <property name="createdId" column="created_id" />
207 <property name="modifiedId" column="modified_id" />
208 <property name="timeZoneId" column="timezone" />
210 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
212 <key column="user_id" />
213 <one-to-many class="org.onap.portalapp.portal.domain.EPUserApp" />
218 <class name="MicroserviceData" table="ep_microservice">
219 <id name="id" column="id">
220 <generator class="native">
221 <param name="sequence">seq_ep_microservice</param>
225 <property name="name" type="string">
226 <column name="name" not-null="true" default="?"></column>
229 <property name="desc" type="string">
230 <column name="description" not-null="true" default="?"></column>
233 <property name="appId" type="long">
234 <column name="appId" not-null="true"></column>
237 <property name="url" type="string">
238 <column name="endpoint_url" not-null="true" default="?"></column>
241 <property name="securityType" type="string">
242 <column name="security_type" not-null="true" default="?"></column>
246 <property name="username" type="string">
247 <column name="username" not-null="true" default="?"></column>
250 <property name="password" type="string">
251 <column name="password" not-null="true" default="?"></column>
254 <property name="active" type="string">
255 <column name="active" not-null="true" default="Y"></column>
259 <class name="MicroserviceParameter" table="ep_microservice_parameter">
260 <id name="id" column="id">
261 <generator class="native">
262 <param name="sequence">seq_ep_microservice_parameter</param>
266 <property name="serviceId" type="long">
267 <column name="service_id" not-null="true"></column>
270 <property name="para_key" type="string">
271 <column name="para_key" not-null="true" default="?"></column>
274 <property name="para_value" type="string">
275 <column name="para_value" not-null="true" default="?"></column>
279 <!-- UserApp class mapping details -->
280 <class name="EPUserApp" table="fn_user_role">
282 <key-property name="userId" type="long">
283 <column name="user_id" precision="11" scale="0" />
285 <key-many-to-one name="app"
286 class="org.onap.portalapp.portal.domain.EPApp" column="app_id" />
287 <key-many-to-one name="role"
288 class="org.onap.portalapp.portal.domain.EPRole" column="role_id" />
290 <property name="priority" type="java.lang.Short">
291 <column name="priority" precision="4" scale="0" />
295 <!-- User App class mapping details -->
296 <class name="EPApp" table="fn_app">
297 <id name="id" column="app_id">
298 <generator class="native">
299 <param name="sequence">seq_fn_app</param>
302 <property name="name" type="string">
303 <column name="app_name" not-null="true" default="?"></column>
305 <property name="imageUrl" column="app_image_url" />
306 <property name="description" column="app_description" />
307 <property name="notes" column="app_notes" />
308 <property name="url" column="app_url" />
309 <property name="alternateUrl" column="app_alternate_url" />
310 <property name="appRestEndpoint" column="app_rest_endpoint" />
311 <property name="mlAppName" type="string">
312 <column name="ml_app_name" not-null="true" default="?"></column>
314 <property name="mlAppAdminId" type="string">
315 <column name="ml_app_admin_id" not-null="true" default="?"></column>
317 <property name="motsId" column="mots_id" />
318 <property name="appPassword" type="string">
319 <column name="app_password" not-null="true" default="?"></column>
321 <property name="thumbnail" column="thumbnail" />
322 <property name="username" type="string">
323 <column name="app_username" not-null="true" default="?"></column>
325 <property name="open" type="yes_no">
326 <column name="open" not-null="true" default="Y"></column>
328 <property name="enabled" type="yes_no">
329 <column name="enabled" not-null="true" default="N"></column>
331 <property name="uebTopicName" type="string">
332 <column name="ueb_topic_name"></column>
334 <property name="uebKey" type="string">
335 <column name="ueb_key"></column>
337 <property name="uebSecret" type="string">
338 <column name="ueb_secret"></column>
340 <property name="appType" type="integer">
341 <column name="app_type"></column>
343 <property name="centralAuth" type="yes_no">
344 <column name="auth_central"></column>
346 <property name="nameSpace" type="string">
347 <column name="auth_namespace"></column>
349 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
353 <!-- EPUserAppsSortPreference class mapping details -->
354 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
355 <id name="id" column="ID">
356 <generator class="native">
357 <param name="sequence">seq_ep_usrappsrtprf</param>
360 <property name="userId" column="USER_ID" />
361 <property name="sortPref" column="SORT_PREF" />
364 <!-- EPUserAppsManualSortPreference class mapping details -->
365 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
366 <id name="id" column="ID">
367 <generator class="native">
368 <param name="sequence">seq_ep_usrmanappsrtprf</param>
371 <property name="userId" column="user_id"
372 unique-key="uk_1_ep_pers_user_app_man_sort" />
373 <property name="appId" column="app_id"
374 unique-key="uk_1_ep_pers_user_app_man_sort" />
375 <property name="appManualSortOrder" column="sort_order" type="int" />
378 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
379 <id name="id" column="ID">
380 <generator class="native">
381 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
384 <property name="userId" column="user_id" />
385 <property name="widgetId" column="widget_id" />
386 <property name="widgetRow" column="x" />
387 <property name="widgetCol" column="y" />
388 <property name="widgetHeight" column="height" />
389 <property name="widgetWidth" column="width" />
392 <!-- User Role class mapping details -->
393 <class name="EPRole" table="FN_ROLE" >
394 <id name="id" column="role_id">
395 <generator class="native">
396 <param name="sequence">seq_fn_role</param>
400 <property name="name" column="role_name" />
401 <property name="priority" column="priority" />
402 <property name="active" column="active_yn" type="yes_no" />
403 <!-- 2 lines below may be removed! -->
404 <property name="appId" column="APP_ID" />
405 <property name="appRoleId" column="APP_ROLE_ID" />
406 <!-- 2 lines above may be removed -->
408 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
410 <key column="role_id" />
411 <many-to-many column="function_cd"
412 class="org.onap.portalsdk.core.domain.RoleFunction" />
415 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
417 <key column="parent_role_id" />
418 <many-to-many column="child_role_id"
419 class="org.onap.portalapp.portal.domain.EPRole" />
422 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
424 <key column="child_role_id" />
425 <many-to-many column="parent_role_id"
426 class="org.onap.portalapp.portal.domain.EPRole" />
431 <!-- User App class mapping details -->
432 <class name="AppContactUs" table="fn_app_contact_us">
433 <id name="id" column="app_id">
434 <generator class="foreign">
435 <param name="property">app</param>
438 <property name="url" column="url" />
439 <property name="description" column="description" />
440 <property name="contactName" column="contact_name" />
441 <property name="contactEmail" column="contact_email" />
442 <property name="activeYN" column="active_yn" />
444 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
448 <!-- Personalization of user app selections -->
449 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
450 <id name="id" column="id">
451 <generator class="native">
452 <param name="property">seq_fn_pers_user_app_sel</param>
455 <property name="userId" column="user_id" />
456 <property name="appId" column="app_id" />
457 <property name="statusCode" column="status_cd" />
460 <!-- EPAppRoleFunction class mapping details -->
461 <class name="EPAppRoleFunction" table="ep_app_role_function">
462 <id name="id" column="id">
463 <generator class="native">
464 <param name="property">seq_epp_app_role_func</param>
467 <property name="roleId" column="role_id"></property>
468 <property name="appId" column="app_id"></property>
469 <property name="code" column="function_cd"></property>
470 <property name="roleAppId" column="role_app_id"></property>
473 <!-- CentralRoleFunction class mapping details -->
474 <class name="CentralV2RoleFunction" table="ep_app_function">
476 <key-property name="appId"
478 <key-property name="code"
479 column="function_cd" />
481 <property name="name" column="function_name" />
484 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
485 <id name="id" column="id">
486 <generator class="native">
487 <param name="property">seq_ep_pers_user_widget_sel</param>
490 <property name="userId" column="user_id" />
491 <property name="widgetId" column="widget_id" />
492 <property name="statusCode" column="status_cd" />
495 <!-- EPUserAppRolesRequest class mapping details -->
496 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
497 <id name="id" column="req_id">
498 <generator class="native">
499 <param name="sequence">seq_ep_user_role_request</param>
502 <property name="userId" column="user_id" />
503 <property name="appId" column="app_id" />
504 <property name="createdDate" column="created_date" type="timestamp" />
505 <property name="updatedDate" column="updated_date" type="timestamp" />
506 <property name="requestStatus" column="request_status" />
508 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
509 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
510 <key column="req_id" />
512 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
516 <!-- EPUserAppRolesRequestDetail class mapping details -->
517 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
518 <id name="id" column="id">
519 <generator class="native">
520 <param name="sequence">seq_ep_user_role_request_det</param>
523 <property name="reqRoleId" column="requested_role_id" />
524 <property name="reqType" column="request_type" />
525 <many-to-one name="epRequestIdData" fetch="select"
526 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest">
527 <column name="req_id" not-null="true" />
531 <!-- show the current user plus related users -->
532 <sql-query name="relatedUsers">
533 <return-scalar column="org_user_id" type="java.lang.String" />
535 select t.org_user_id from (
536 select distinct c.org_user_id org_user_id, c.last_login_date from
537 (select c.org_user_id, c.last_login_date from
539 (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,
541 where a.ROLE_ID = b.role_id
542 and a.APP_ID = b.app_id
543 and a.USER_ID = c.user_id
545 select org_user_id , last_login_date from fn_user where org_user_id=:userId
547 order by c.last_login_date desc limit 10
552 <!-- requires values for named parameters :searchQuery and :userId -->
553 <sql-query name="searchPortal">
554 <return alias="searchResult"
555 class="org.onap.portalapp.portal.ecomp.model.SearchResultItem" />
557 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
559 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
561 upper( :searchQuery ) != ''
562 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
563 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
564 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%'))
566 ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
568 last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
570 (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
572 first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
573 and upper(active_yn) = 'Y'
577 select distinct 'Application' CATEGORY, b1.app_name NAME,
578 if (b1.app_type = 2,'false','true') UUID,
579 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
583 from fn_user_role a, fn_user b, fn_app c
584 where a.USER_ID = b.user_id
585 and upper(b.org_user_id) = upper( :userId )
586 and a.app_id = c.app_id
587 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
588 and upper(c.ENABLED) = 'Y'
589 ) a1 right outer join
590 (select * from fn_app where
591 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
592 and upper(ENABLED) = 'Y') b1
593 on a1.APP_ID = b1.app_id
597 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
598 if (b1.app_type = 2,'false','true') UUID,
599 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
601 (select distinct d.*, e.app_type from fn_user a,
602 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
603 where a.USER_ID = b.user_id
604 and b.role_id = c.role_id
605 and c.menu_id = d.menu_id
606 and upper(a.org_user_id) = upper( :userId )
607 and c.APP_ID = e.app_id
608 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
609 and upper(d.active_yn) = 'Y'
610 and upper(e.enabled) = 'Y'
611 ) a1 right outer join
613 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
614 where active_yn = 'Y'
615 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
616 and a.menu_id = b.menu_id
617 and b.app_id = c.app_id
620 on a1.menu_id = b1.menu_id
623 select distinct CATEGORY, NAME, UUID, TARGET
627 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
630 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
631 where upper(a.org_user_id) = upper( :userId )
632 and a.user_id = b.user_id
633 and b.role_id = c.role_id
634 and c.widget_id = d.widget_id
635 and d.all_user_flag<>'Y'
636 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
640 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
643 from ep_widget_catalog d
644 where d.all_user_flag='Y'
645 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
649 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
652 from fn_user a, fn_user_role b, ep_widget_catalog d
653 where upper(a.org_user_id) = upper( :userId )
654 and a.user_id = b.user_id
656 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
660 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
666 <!-- requires values for named parameters :searchQuery and :userId -->
667 <sql-query name="getNotifications">
668 <return alias="notificationResult"
669 class="org.onap.portalapp.portal.transport.EpNotificationItem" />
671 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,notification_hyperlink, active_YN from
674 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
677 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
680 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
681 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
682 from ep_notification a, ep_role_notification b
683 where a.notification_id = b.notification_id
684 and (end_time is null || SYSDATE() <= end_time )
685 and (start_time is null || SYSDATE() >= start_time)
686 and a.is_for_all_roles = 'N'
689 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
690 from fn_user a, fn_user_role b, fn_role c, fn_app d
691 where COALESCE(c.app_id,1) = d.app_id
692 and a.user_id = b.user_id
693 and a.user_id = :user_id
694 and b.role_id = c.role_id
695 and (d.enabled='Y' or d.app_id=1)
699 a.role_id = b.role_id
702 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
705 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
706 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
707 from ep_notification a, ep_role_notification b
708 where a.notification_id = b.notification_id
709 and (end_time is null || SYSDATE() <= end_time )
710 and (start_time is null || SYSDATE() >= start_time)
711 and a.is_for_all_roles = 'N'
715 a.recv_user_id=:user_id
719 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
720 from ep_notification a
721 where a.notification_id
722 and (end_time is null || SYSDATE() <= end_time )
723 and (start_time is null || SYSDATE() >= start_time)
724 and a.is_for_all_roles = 'Y'
732 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'
734 order by priority desc, created_date desc,start_Time desc
738 (SELECT @rn /*'*/:=/*'*/ 0) t2
743 <sql-query name="getUsersByOrgIdsNotifications">
744 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
746 SELECT * from fn_user where org_user_id in (:OrgIds)
752 <!-- Gets all Admin notification history; accepts no parameters -->
753 <sql-query name="getAdminNotificationHistoryVO">
754 <return alias="adminNotificationHistoryVOResult"
755 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
759 n.notification_ID AS notificationId,
760 n.is_for_online_users AS isForOnlineUsers,
761 n.is_for_all_roles AS isForAllRoles,
762 n.active_YN AS activeYn,
763 n.msg_header AS msgHeader,
764 n.msg_description AS msgDescription,
765 n.msg_source AS msgSource,
766 n.start_Time AS startTime,
767 n.end_time AS endTime,
769 n.creator_ID AS creatorId,
770 n.created_date AS createdDate,
771 n.notification_hyperlink AS notificationHyperlink,
772 u.org_user_id AS loginId
776 fn_user u on u.user_id = n.creator_id
778 n.active_YN='Y' and msg_source='EP'
783 n.notification_ID AS notificationId,
784 n.is_for_online_users AS isForOnlineUsers,
785 n.is_for_all_roles AS isForAllRoles,
786 n.active_YN AS activeYn,
787 n.msg_header AS msgHeader,
788 n.msg_description AS msgDescription,
789 n.msg_source AS msgSource,
790 n.start_Time AS startTime,
791 n.end_time AS endTime,
793 n.creator_ID AS creatorId,
794 n.created_date AS createdDate,
795 n.notification_hyperlink AS notificationHyperlink,
796 u.org_user_id AS loginId
800 fn_user u on u.user_id = n.creator_id
802 ep_role_notification r on r.notification_ID=n.notification_ID
804 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
805 )n order by n.startTime desc
809 <sql-query name="getEpNotificationAppRoles">
810 <return alias="notificationAppRoles"
811 class="org.onap.portalapp.portal.domain.EcompAppRole" />
812 <!-- This query requires no parameters. -->
815 select a.app_id, a.app_name, b.role_id, b.role_name from
816 (select * from fn_app where app_id = 1) a,
817 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
819 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
821 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
828 <!-- Gets all notification history -->
829 <sql-query name="getNotificationHistoryVO">
830 <return alias="notificationHistoryVOResult"
831 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
834 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
835 startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
839 a.notification_ID AS notificationId,
840 is_for_online_users AS isForOnlineUsers,
841 is_for_all_roles AS isForAllRoles,
842 msg_header AS msgHeader,
843 msg_description AS msgDescription,
844 msg_source AS msgSource,
845 start_Time AS startTime,
848 created_date AS createdDate,
849 creator_ID AS creatorId,
850 notification_hyperlink AS notificationHyperlink,
852 active_YN AS activeYn,
853 if (is_viewed is null, 'N', is_viewed)
857 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
858 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
859 creator_ID,notification_hyperlink,active_YN
862 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
863 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,
864 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
867 END AS login_id,b.recv_user_id
868 from ep_notification a, ep_role_notification b,fn_user u
869 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
870 and a.is_for_all_roles = 'N'
872 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
874 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
876 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
878 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
882 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
883 from fn_user a, fn_user_role b, fn_role c, fn_app d
884 where COALESCE(c.app_id,1) = d.app_id
885 and a.user_id = b.user_id
886 and a.user_id = :user_id
887 and b.role_id = c.role_id
888 and (d.enabled='Y' or d.app_id=1)
892 a.role_id = b.role_id
896 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
897 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
898 creator_ID,notification_hyperlink,active_YN
901 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
902 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,
903 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
906 END AS login_id,b.recv_user_id
907 from ep_notification a, ep_role_notification b,fn_user u
908 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
909 and a.is_for_all_roles = 'N'
911 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
913 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
915 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
917 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
922 a.recv_user_id=:user_id
927 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
928 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
929 creator_ID, a.notification_hyperlink,a.active_YN
930 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
931 where a.notification_id
932 and a.is_for_all_roles = 'Y'
934 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
936 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
938 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
940 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
943 ) a left outer join (
944 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
946 on a.notification_id = m.notification_ID
950 order by start_Time desc,end_time desc
952 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
957 <!-- shows the received recipient to whom the notification is delivered from external system -->
958 <sql-query name="messageRecipients">
959 <return-scalar column="org_user_id" type="java.lang.String" />
961 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
966 <!-- Gets role details for a specified notification -->
967 <sql-query name="getNotificationRoles">
968 <return alias="notificationRolesResult"
969 class="org.onap.portalapp.portal.transport.EpRoleNotificationItem" />
975 notification_Id = :notificationId
979 <!-- Gets all applications, possibly with contact information -->
980 <sql-query name="getAppsAndContacts">
981 <return alias="appContactUs"
982 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
983 <!-- This query requires no parameters. -->
986 a.app_id as appId, a.app_name as appName,
987 c.contact_name as contactName,
988 c.contact_email as contactEmail, c.url, c.description,
989 c.active_yn as activeYN
994 on a.app_id = c.app_id
996 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1001 <!-- Gets all rows from the app-contact-us table, extended with app information -->
1002 <sql-query name="getAppContactUsItems">
1003 <return alias="appContactUs"
1004 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1005 <!-- This query requires no parameters. -->
1008 c.app_id as appId, c.contact_name as contactName,
1009 c.contact_email as contactEmail, c.url, c.description,
1010 c.active_yn as activeYN, a.app_name as appName
1015 on a.app_id = c.app_id
1017 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1022 <!-- Gets one row for each app with the category and catenated functions. -->
1023 <sql-query name="getAppCategoryFunctions">
1024 <return alias="appCategoryFunctions"
1025 class="org.onap.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1026 <!-- This query requires no parameters. -->
1028 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1029 r.category as category, r.app_id as appId, r.app_name as application,
1030 group_concat(function_text separator ', ') as functions
1033 app_id, function_text, app_name,
1034 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1037 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1038 k.text parent_menu, k.parent_menu_id parent_menu_id
1041 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1043 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1045 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1047 fn_menu_functional k
1048 WHERE j.parent_menu_id = k.menu_id
1050 fn_menu_functional a
1051 WHERE fn.parent_menu_id = a.menu_id
1053 (SELECT @rn /*'*/:=/*'*/ 0) t2
1054 group by r.category, r.app_id, r.app_name
1055 order by category, app_name
1060 <!-- Gets one row for each function-application-role combination. -->
1061 <sql-query name="getAppAccessFunctionRole">
1062 <return alias="appAccessFunctionRole"
1063 class="org.onap.portalapp.portal.domain.GetAccessResult" />
1064 <!-- This query requires one parameters. -->
1066 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1067 (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,
1068 o.requested_role_id,o.request_type
1070 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1073 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1076 where COALESCE(c.app_id,1) = d.app_id
1077 and (d.enabled='Y' or d.app_id=1)
1078 and c.active_yn = 'Y'
1081 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1083 A.MENU_ID = B.MENU_ID
1086 on a.role_id = b.role_id) t left outer join
1087 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1088 where er.req_id=ed.req_id
1089 and upper(ed.request_type)='P'
1090 and er.user_id =:userId) o
1091 on t.app_id=o.app_id
1092 and t.role_id = o.requested_role_id
1093 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1098 <query name="getCommonWidgetItem">
1099 from CommonWidget where category = :cat order by
1103 <sql-query name="getGuestLastLogin">
1104 <return-scalar column="audit_date" type="java.util.Date" />
1106 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1110 <sql-query name="getActiveUsers">
1111 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1113 SELECT * FROM FN_USER WHERE active_yn='Y'
1117 <sql-query name="getAppsAdmins">
1118 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1120 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)
1124 <sql-query name="getPortalAdmins">
1125 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1127 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
1131 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1132 <sql-query name="getPersUserApps">
1133 <return alias="persUserApps" class="org.onap.portalapp.portal.domain.EPApp" />
1134 <!-- This query requires one parameter: userId (number) -->
1137 distinct -- multiple roles yield multiple rows
1138 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1139 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1140 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1141 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1142 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
1144 -- Portal assigns role 999 to app administrator
1145 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1146 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1147 where a.ENABLED = 'Y'
1149 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1151 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1158 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1160 <sql-query name="getPersAdminApps">
1161 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1162 <!-- This query requires one parameter: userId (number) -->
1165 distinct -- multiple roles yield multiple rows
1166 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1167 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1168 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1169 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1170 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1173 -- Portal assigns role 999 to app administrator
1174 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1175 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1176 where a.ENABLED = 'Y'
1178 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1180 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1182 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1189 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1190 <sql-query name="getPersAdminAppsOrderByName">
1191 <return alias="AdminAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1192 <!-- This query requires one parameter: userId (number) -->
1195 distinct -- multiple roles yield multiple rows
1196 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1197 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1198 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1199 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1200 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1203 -- Portal assigns role 999 to app administrator
1204 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1205 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1206 where a.ENABLED = 'Y'
1208 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1210 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1212 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1215 select distinct -- multiple roles yield multiple rows
1216 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1217 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1218 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1219 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1220 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1222 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1223 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1230 <!--Gets personalized list of enabled and accessible User apps by name -->
1231 <sql-query name="getPersUserAppsOrderByName">
1232 <return alias="UserAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1233 <!-- This query requires one parameter: userId (number) -->
1236 distinct -- multiple roles yield multiple rows
1237 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1238 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1239 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1240 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1241 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1244 -- Portal assigns role 999 to app administrator
1245 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1246 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1247 where a.ENABLED = 'Y'
1249 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1251 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1254 select distinct -- multiple roles yield multiple rows
1255 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1256 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1257 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1258 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1259 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1261 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1262 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1270 <!--Gets personalized list of enabled and accessible Super Admin apps by
1272 <sql-query name="getAdminAppsOrderByLastUsed">
1273 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1278 distinct -- multiple roles yield multiple rows
1279 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1280 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1281 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1282 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1283 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1286 -- Portal assigns role 999 to app administrator
1287 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1288 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1289 where a.ENABLED = 'Y'
1291 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1293 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1295 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1298 select distinct -- multiple roles yield multiple rows
1299 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1300 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1301 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1302 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1303 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1305 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1306 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1310 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1313 on A.app_id = B.Affected_record_id
1314 order by AUDIT_DATE DESC;
1318 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1319 <sql-query name="getUserAppsOrderByLastUsed">
1320 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1322 select * from (select
1323 distinct -- multiple roles yield multiple rows
1324 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1325 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1326 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1327 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1328 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1331 -- Portal assigns role 999 to app administrator
1332 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1333 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1334 where a.ENABLED = 'Y'
1336 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1338 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1340 select distinct -- multiple roles yield multiple rows
1341 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1342 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1343 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1344 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1345 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1347 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1348 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1353 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1356 on A.app_id = B.Affected_record_id
1357 order by AUDIT_DATE DESC;
1364 <!--Gets personalized list of enabled and accessible Super Admin apps by
1366 <sql-query name="getAdminAppsOrderByMostUsed">
1367 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1372 distinct -- multiple roles yield multiple rows
1373 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1374 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1375 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1376 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1377 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1380 -- Portal assigns role 999 to app administrator
1381 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1382 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1383 where a.ENABLED = 'Y'
1385 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1387 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1389 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1391 select distinct -- multiple roles yield multiple rows
1392 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1393 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1394 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1395 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1396 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1398 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1399 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1402 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1403 from FN_APP LEFT JOIN FN_AUDIT_LOG
1404 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1405 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1406 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1407 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1409 on A.app_id = B.Affected_record_id
1410 order by total_visits DESC;
1415 <!--Gets personalized list of enabled and accessible NON Super Admin User
1416 apps by Most Used -->
1417 <sql-query name="getUserAppsOrderByMostUsed">
1418 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1422 distinct -- multiple roles yield multiple rows
1423 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1424 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1425 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1426 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1427 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1430 -- Portal assigns role 999 to app administrator
1431 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1432 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1433 where a.ENABLED = 'Y'
1435 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1437 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1439 select distinct -- multiple roles yield multiple rows
1440 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1441 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1442 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1443 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1444 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1446 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1447 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1450 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1451 from FN_APP LEFT JOIN FN_AUDIT_LOG
1452 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1453 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1454 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1455 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1457 on A.app_id = B.Affected_record_id
1458 order by total_visits DESC;
1462 <!--Gets personalized list of enabled and accessible Super Admin apps by
1464 <sql-query name="getAdminAppsOrderByManual">
1465 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1469 distinct -- multiple roles yield multiple rows
1470 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1471 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1472 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1473 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1474 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1477 -- Portal assigns role 999 to app administrator
1478 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1479 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1480 where a.ENABLED = 'Y'
1482 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1484 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1486 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1488 select distinct -- multiple roles yield multiple rows
1489 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1490 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1491 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1492 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1493 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1495 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1496 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1499 (select m.app_id, m.sort_order
1500 from ep_pers_user_app_man_sort m
1501 where USER_ID = :userId
1503 on A.APP_ID = B.app_id
1504 order by sort_order ASC
1510 <!--Gets personalized list of enabled and accessible NON Super admin User
1512 <sql-query name="getUserAppsOrderByManual">
1513 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1515 select * from (select
1516 distinct -- multiple roles yield multiple rows
1517 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1518 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1519 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1520 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1521 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1524 -- Portal assigns role 999 to app administrator
1525 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1526 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1527 where a.ENABLED = 'Y'
1529 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1531 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1533 select distinct -- multiple roles yield multiple rows
1534 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1535 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1536 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1537 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1538 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1540 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1541 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1544 (select m.app_id, m.sort_order
1545 from ep_pers_user_app_man_sort m
1546 where USER_ID = :userId
1548 on A.APP_ID = B.app_id
1549 order by sort_order ASC
1554 <!-- Gets regular user's list of enabled apps including accessible and select
1556 <sql-query name="getUserAppCatalog">
1557 <return alias="userAppCatalog"
1558 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1559 <!-- This query requires one parameter: userId (number) -->
1562 distinct -- multiple roles yield multiple rows
1563 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1564 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1565 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1566 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1567 IF(a.open = 'Y', TRUE, FALSE) as open,
1568 -- ACCESS(-ible) means user has a defined role OR the application is open
1570 -- regular app and user has a role
1571 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1573 when a.OPEN = 'Y' then TRUE
1576 -- SELECT(-ed) indicates user personalization
1578 -- regular app, user has a role, no personalization
1579 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1580 -- open app and has personalization
1581 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1584 -- PENDING indicates user checked a box
1586 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1590 -- Portal assigns role 999 to app administrator
1591 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1592 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1593 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'
1594 where a.ENABLED = 'Y'
1596 -- Show accessible apps first, then the rest; sort by name within each set.
1597 order by access desc, app_name asc
1602 <sql-query name="getMyloginAppDetails">
1603 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1605 SELECT * FROM fn_app where ml_app_name =:appName
1610 <sql-query name="getAppDetailsByAppName">
1611 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1613 SELECT * FROM fn_app where app_name =:appName
1618 <sql-query name="getMyAppDetailsByUebKey">
1619 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1621 SELECT * FROM fn_app where ueb_key =:appKey
1627 <sql-query name="deleteAccountEndpoint">
1629 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1634 <sql-query name="deleteAccountEndpointRecord">
1636 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1641 <sql-query name="deleteEPEndpoint">
1643 DELETE FROM ep_endpoints WHERE id =:epId
1648 <sql-query name="deleteBasicAuthAccount">
1650 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1655 <sql-query name="getAppRoles">
1656 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1658 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1663 <sql-query name="getPartnerAppRolesList">
1664 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1666 SELECT * FROM fn_role where app_id =:appId
1671 <sql-query name="getPortalAppRolesList">
1672 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1674 SELECT * FROM fn_role where app_id is null
1679 <sql-query name="getPortalAppRoleByRoleId">
1680 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1682 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1687 <sql-query name="getPartnerAppRoleByRoleId">
1688 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1690 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1695 <sql-query name="getPartnerAppRoleById">
1696 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1698 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1703 <sql-query name="getPortalAppRoles">
1704 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1706 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1711 <sql-query name="deleteMicroserviceParameter">
1713 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1718 <sql-query name="deleteMicroserviceParameterById">
1720 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1725 <sql-query name="deleteWidgetCatalogParameter">
1727 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1732 <sql-query name="deleteMicroservice">
1734 DELETE FROM ep_microservice WHERE id =:serviceId
1739 <sql-query name="epUserAppId">
1740 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1742 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1747 <sql-query name="userAppsSortPreferenceQuery">
1748 <return alias="appsSortPreferenceQuery"
1749 class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1751 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1756 <sql-query name="userAppsManualSortPrfQuery">
1757 <return alias="AppsManualSortPrfQuery"
1758 class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1760 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1765 <sql-query name="userWidgetManualSortPrfQuery">
1766 <return alias="widgetManualSortPrfQuery"
1767 class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1769 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1773 <sql-query name="appRoles">
1774 <return alias="rolesForApp"
1775 class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1777 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1782 <sql-query name="userAppRolesRequestList">
1783 <return alias="appRolesRequestList"
1784 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1786 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'
1791 <sql-query name="userAppRolesRequestDetailList">
1792 <return alias="appRolesRequestDetailList"
1793 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1796 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1801 <!-- Gets list of enabled apps including accessible and select statuses -->
1802 <sql-query name="getAdminAppCatalog">
1803 <return alias="adminAppCatalog"
1804 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1805 <!-- This query requires one parameter: userId (number) -->
1808 distinct -- multiple roles yield multiple rows
1809 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1810 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1811 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1812 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1813 IF(a.open = 'Y', TRUE, FALSE) as open,
1814 -- ACCESS(-ible) means user has a defined role OR the application is open
1816 -- regular app and user has a role
1817 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1819 when a.OPEN = 'Y' then TRUE
1822 -- SELECT(-ed) indicates user personalization
1824 -- regular app, user has a role, no personalization
1825 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1826 -- regular app, user has role, admin forced a personalization
1827 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1828 -- regular app, user has no role, admin forced a personalization
1829 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1830 -- open app and has personalization
1831 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1834 -- PENDING indicates user checked a box
1836 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1840 -- Portal assigns role 999 to app administrator
1841 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1842 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1843 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'
1844 where a.ENABLED = 'Y'
1845 -- Show accessible apps first, then the rest; sort by name within each set.
1846 order by access desc, app_name asc
1851 <sql-query name="userAppCatalogRoles">
1852 <return alias="epUserAppCatalogRoles"
1853 class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1855 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
1856 from ep_user_roles_request A left join ep_user_roles_request_det B
1857 on a.req_id = b.req_id
1858 where A.user_id=:userid
1859 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1860 and A.request_status ='P'
1865 <sql-query name="getUserApproles">
1866 <return alias="businessCardUserApplicationRoles"
1867 class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1870 select fr.role_name, fa.app_name
1872 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1873 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 and fr.active_yn='Y' and (fa.enabled = 'Y' or fa.app_id=1)
1879 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1880 <sql-query name="deleteNotificationsFromEpNotificationTable">
1882 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1887 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1888 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1890 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1894 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1895 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1897 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1901 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1902 <sql-query name="getAppRoleFunctionList">
1903 <return alias="appRoleFunctionList"
1904 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1907 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1908 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1914 <!-- Gets list of all role functions -->
1915 <sql-query name="getAllRoleFunctions">
1916 <return alias="allRoleFunctions"
1917 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1920 SELECT * from ep_app_function where app_id =:appId
1926 <!-- Get ep_app_function records provided by single parameter -->
1927 <sql-query name="getRoleFunction">
1928 <return alias="RoleFunction"
1929 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1932 SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1938 <!-- Gets the current user app roles records and requires two parameters -->
1939 <sql-query name="getUserAppCurrentRoles">
1940 <return alias="userAppCurrentRoles"
1941 class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
1944 select distinct fu.role_id, fr.user_id, fu.role_name, fu.priority from fn_role fu left outer join fn_user_role fr ON fu.role_id = fr.role_id and fu.app_id = fr.app_id and fr.role_id != 999 where fu.app_id =:appId and fr.user_id =:userId
1949 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
1950 <sql-query name="getUserAppExistingRoles">
1951 <return alias="userAppExistingRoles"
1952 class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
1954 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1955 left outer join fn_role fr on fu.role_id = fr.role_id
1956 where fu.user_id =:userId and fu.app_id =:appId
1961 <!-- Gets the current user app roles records and requires two parameters -->
1962 <sql-query name="getAllFunctions">
1963 <return alias="allFunctions"
1964 class="org.onap.portalsdk.core.domain.RoleFunction" />
1966 select * from fn_function
1971 <!-- Gets the all role functions and requires one parameters -->
1972 <sql-query name="uploadAllRoleFunctions">
1973 <return alias="allRoleFunctions"
1974 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
1976 select fr.function_cd, fn.function_name from fn_role_function fr left outer join fn_function fn on fr.function_cd = fn.function_cd where role_id =:roleId
1981 <sql-query name="getRoletoUpdateInExternalAuthSystem">
1982 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
1984 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
1989 <!-- Gets all active records from fn_role that is associated with this app-->
1990 <sql-query name="getActiveRolesOfApplication">
1991 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
1993 select * from fn_role where active_yn = 'Y' and app_id=:appId
1998 <sql-query name="getBulkUserRoles">
1999 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2001 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2002 left outer join fn_role fr on fr.role_id = fur.role_id
2003 left outer join fn_app fa on fa.app_id = fur.app_id
2004 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2009 <sql-query name="getBulkUsersForSingleRole">
2010 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2012 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2013 left outer join fn_role fr on fr.role_id = fur.role_id
2014 left outer join fn_app fa on fa.app_id = fur.app_id
2015 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2020 <sql-query name="getCentralizedApps">
2021 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2023 select * from fn_app where auth_central = 'Y' and open = 'N';
2028 <sql-query name="getUserRoles">
2029 <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2032 SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID WHERE user.org_user_id =:org_user_id and userrole.app_id = 1
2036 <sql-query name="getRoleFunctionsOfUser">
2038 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2039 where fu.role_id = ep.role_id
2040 and fu.app_id = ep.app_id
2041 and fu.user_id =:userId and ep.app_id = 1
2042 and ea.function_cd = ep.function_cd
2044 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2046 and app_r_f.function_cd = a_f.function_cd
2049 select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
2050 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2055 <sql-query name="getUserRolesForLeftMenu">
2056 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2059 SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole
2060 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2061 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2062 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2063 WHERE user.org_user_id =:org_user_id and (userrole.app_id = 1 or role.role_id = 999) and (app.enabled='Y' or app.app_id=1)
2068 <sql-query name="getRolesForLeftMenu">
2069 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2072 SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole
2073 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2074 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2075 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2076 WHERE user.org_user_id =:org_user_id and (userrole.app_id = 1 or role.role_id = 999) and ((app.enabled='Y' and auth_central='Y' ) or app.app_id=1)
2081 <!-- Gets all functions for an application along with global functions and requires single parameter -->
2082 <sql-query name="getMenuFunctions">
2084 select f.function_cd from ep_app_function f
2085 where f.app_id =:appId
2087 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2088 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2093 <sql-query name="getRequestIdsForApp">
2094 <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2096 select * from ep_user_roles_request where app_id =:app_id
2101 <sql-query name="ApplicationUserRoles">
2102 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2104 select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title, fu.login_id,
2105 fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2106 from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2107 where fu.user_id = fur.user_id and fu.active_yn='Y' and fur.role_id = fr.role_id and fr.app_id =:appId and fr.active_yn='Y' and epr.function_cd= epf.function_cd and epf.app_id=epr.app_id and fur.role_id=epr.role_id
2109 select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title,
2110 fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2111 from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2112 where a.role_id in (select b.role_id from ep_app_role_function b where b.role_app_id = 1 and b.app_id =:appId) and a.user_id =fu.user_id and a.role_id = fr.role_id and fr.active_yn='Y' and fu.active_yn='Y'
2113 and earf.role_id = a.role_id and earf.function_cd = eaf.function_cd and earf.app_id = eaf.app_id and earf.role_app_id = 1 and fr.active_yn='Y' and fu.active_yn='Y';
2117 <sql-query name="getCurrentAppRoleFunctions">
2118 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2120 select distinct fr.role_id, fr.role_name from fn_role fr, ep_app_function ef, ep_app_role_function epr where fr.role_id = epr.role_id and epr.function_cd = ef.function_cd
2121 and ef.function_cd =:functionCd and epr.app_id =:appId
2126 <sql-query name="deleteUserWidgetPlacement">
2128 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2133 <sql-query name="getCentralizedAppsOfUser">
2134 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2137 select distinct fa.app_id, fa.app_name
2138 from fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2139 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2140 and fu.org_user_id = :userId and (fur.role_id = 999 or fur.role_id = 1) and fr.active_yn='Y' and ((fa.enabled = 'Y' and fa.auth_central='Y') or fa.app_id =1)
2146 <query name="getEPUserByOrgUserId">
2148 FROM EPUser WHERE orgUserId = :org_user_id
2152 <query name="getEPUserByLoginId">
2154 FROM EPUser WHERE loginId = :login_id
2158 <query name="getEPUserByLoginIdLoginPwd">
2160 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2164 <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2165 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2168 select distinct d.role_id, d.role_name , c.function_cd , e.function_name , d.active_yn , d.priority , c.app_id, c.role_app_id
2169 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2170 where b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and e.function_cd = c.function_cd and c.app_id= :appId and e.app_id=c.app_id
2176 <sql-query name="getGlobalRolesOfPortal">
2177 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2179 select * from fn_role where role_name like 'global_%' and app_id is null and active_yn='Y'
2184 <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2185 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2187 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2192 <sql-query name="getAppFunctionOnCodeAndAppId">
2193 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2195 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2200 <sql-query name="getGlobalRoleForRequestedApp">
2201 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2204 select distinct d.role_id, d.role_name , c.function_cd , e.function_name , d.active_yn , d.priority , c.app_id, c.role_app_id
2205 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2206 where b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and e.function_cd = c.function_cd and c.app_id=:requestedAppId and c.role_id =:roleId and e.app_id = c.app_id
2212 <sql-query name="updateMenuFunctionalAndRoles">
2214 UPDATE fn_menu_functional m, fn_menu_functional_roles mr SET m.url='' WHERE m.menu_id=mr.menu_id AND mr.app_id=:app_id
2219 <!-- Remove any favorites associated with a menu item that is associated with this app -->
2220 <sql-query name="removeAppFromMenuFavorites">
2222 DELETE FROM fn_menu_favorites using fn_menu_favorites inner join fn_menu_functional_roles where fn_menu_functional_roles.app_id=:app_id AND fn_menu_functional_roles.menu_id=fn_menu_favorites.menu_id
2227 <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2228 <sql-query name="removeAppFromMenuFunctionalRoles">
2230 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2235 <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2236 <sql-query name="removeAppFromEpAppRoleFunction">
2238 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2243 <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2244 <sql-query name="removeAppFromEpAppFunction">
2246 DELETE FROM ep_app_function WHERE app_id=:app_id
2251 <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2252 <sql-query name="removeAppFromFnUserRole">
2254 DELETE FROM fn_user_role WHERE app_id=:app_id
2259 <!-- Remove any widgets that is associated with this app-->
2260 <sql-query name="removeAppFromEpWidgetCatalogRole">
2262 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2267 <!-- Remove any notifications that is associated with this app-->
2268 <sql-query name="removeAppFromEpRoleNotification">
2270 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2271 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2276 <!-- Remove all records from fn_role that is associated with this app-->
2277 <sql-query name="removeAppFromFnRole">
2279 DELETE FROM fn_role where app_id=:app_id
2284 <!-- Remove app contact us entries that is associated with this app-->
2285 <sql-query name="removeAppFromAppContactUs">
2287 DELETE FROM fn_app_contact_us where app_id=:app_id
2292 <!-- Remove app personalization entries that is associated with this app-->
2293 <sql-query name="removeAppFromEpPersUserAppSel">
2295 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2300 <!-- Remove app personalization sorting entries that is associated with this app-->
2301 <sql-query name="removeAppFromEpPersUserAppManSort">
2303 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2308 <!-- Remove rows from user role request table entries that is associated with this app-->
2309 <sql-query name="removeAppFromEpUserRolesRequest">
2311 DELETE FROM ep_user_roles_request where app_id=:app_id
2316 <!-- Remove rows from web analytics table entries that is associated with this app-->
2317 <sql-query name="removeAppFromEpWebAnalytics">
2319 DELETE FROM ep_web_analytics_source where app_id=:app_id
2324 <!-- Remove row from fn app table -->
2325 <sql-query name="removeAppFromFnApp">
2327 DELETE FROM fn_app where app_id=:app_id
2333 <query name="getBasicauthAccount">
2334 select id,password from BasicAuthCredentials
2337 <query name="getMicroserviceInfo">
2338 select id,password from MicroserviceData
2341 <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2342 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2345 select * from fn_user_role where user_id= :userId
2346 and role_id= :roleId
2353 <sql-query name="userAppGlobalRoles">
2354 <return alias="userAppGlobalRoles" class="org.onap.portalapp.portal.domain.EPRole" />
2356 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2357 from fn_user_role a, fn_role fr, fn_user fu
2358 where a.role_id in (select b.role_id from ep_app_role_function b where b.role_app_id = 1 and b.app_id =:appId) and a.user_id =fu.user_id and a.role_id = fr.role_id and fr.active_yn='Y' and fu.active_yn='Y' and fu.user_id =:userId
2363 </hibernate-mapping>