3 ============LICENSE_START==========================================
5 ===================================================================
6 Copyright (C) 2017-2018 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============================================
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 ONAP 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">
291 <column name="priority" default="1" 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
1309 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1310 from FN_APP LEFT JOIN FN_AUDIT_LOG
1311 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1312 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1313 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1314 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1316 on A.app_id = B.Affected_record_id
1317 order by AUDIT_DATE DESC;
1321 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1322 <sql-query name="getUserAppsOrderByLastUsed">
1323 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1325 select * from (select
1326 distinct -- multiple roles yield multiple rows
1327 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1328 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1329 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1330 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1331 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1334 -- Portal assigns role 999 to app administrator
1335 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1336 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1337 where a.ENABLED = 'Y'
1339 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1341 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1343 select distinct -- multiple roles yield multiple rows
1344 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1345 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1346 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1347 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1348 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1350 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1351 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
1355 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1356 from FN_APP LEFT JOIN FN_AUDIT_LOG
1357 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1358 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1359 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1360 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1362 on A.app_id = B.Affected_record_id
1363 order by AUDIT_DATE DESC;
1370 <!--Gets personalized list of enabled and accessible Super Admin apps by
1372 <sql-query name="getAdminAppsOrderByMostUsed">
1373 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1378 distinct -- multiple roles yield multiple rows
1379 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1380 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1381 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1382 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1383 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
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.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1395 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1397 select distinct -- multiple roles yield multiple rows
1398 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1399 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1400 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1401 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1402 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1404 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1405 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
1408 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1409 from FN_APP LEFT JOIN FN_AUDIT_LOG
1410 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1411 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1412 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1413 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1415 on A.app_id = B.Affected_record_id
1416 order by total_visits DESC;
1421 <!--Gets personalized list of enabled and accessible NON Super Admin User
1422 apps by Most Used -->
1423 <sql-query name="getUserAppsOrderByMostUsed">
1424 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1428 distinct -- multiple roles yield multiple rows
1429 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1430 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1431 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1432 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1433 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1436 -- Portal assigns role 999 to app administrator
1437 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1438 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1439 where a.ENABLED = 'Y'
1441 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1443 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1445 select distinct -- multiple roles yield multiple rows
1446 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1447 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1448 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1449 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1450 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1452 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1453 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
1456 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1457 from FN_APP LEFT JOIN FN_AUDIT_LOG
1458 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1459 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1460 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1461 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1463 on A.app_id = B.Affected_record_id
1464 order by total_visits DESC;
1468 <!--Gets personalized list of enabled and accessible Super Admin apps by
1470 <sql-query name="getAdminAppsOrderByManual">
1471 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1475 distinct -- multiple roles yield multiple rows
1476 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1477 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1478 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1479 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1480 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1483 -- Portal assigns role 999 to app administrator
1484 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1485 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1486 where a.ENABLED = 'Y'
1488 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1490 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1492 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1494 select distinct -- multiple roles yield multiple rows
1495 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1496 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1497 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1498 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1499 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1501 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1502 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
1505 (select m.app_id, m.sort_order
1506 from ep_pers_user_app_man_sort m
1507 where USER_ID = :userId
1509 on A.APP_ID = B.app_id
1510 order by sort_order ASC
1516 <!--Gets personalized list of enabled and accessible NON Super admin User
1518 <sql-query name="getUserAppsOrderByManual">
1519 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1521 select * from (select
1522 distinct -- multiple roles yield multiple rows
1523 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1524 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1525 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1526 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1527 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1530 -- Portal assigns role 999 to app administrator
1531 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1532 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1533 where a.ENABLED = 'Y'
1535 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1537 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1539 select distinct -- multiple roles yield multiple rows
1540 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1541 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1542 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1543 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1544 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1546 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1547 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
1550 (select m.app_id, m.sort_order
1551 from ep_pers_user_app_man_sort m
1552 where USER_ID = :userId
1554 on A.APP_ID = B.app_id
1555 order by sort_order ASC
1560 <!-- Gets regular user's list of enabled apps including accessible and select
1562 <sql-query name="getUserAppCatalog">
1563 <return alias="userAppCatalog"
1564 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1565 <!-- This query requires one parameter: userId (number) -->
1568 distinct -- multiple roles yield multiple rows
1569 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1570 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1571 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1572 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1573 IF(a.open = 'Y', TRUE, FALSE) as open,
1574 -- ACCESS(-ible) means user has a defined role OR the application is open
1576 -- regular app and user has a role
1577 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1579 when a.OPEN = 'Y' then TRUE
1582 -- SELECT(-ed) indicates user personalization
1584 -- regular app, user has a role, no personalization
1585 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1586 -- open app and has personalization
1587 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1590 -- PENDING indicates user checked a box
1592 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1596 -- Portal assigns role 999 to app administrator
1597 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1598 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1599 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'
1600 where a.ENABLED = 'Y'
1602 -- Show accessible apps first, then the rest; sort by name within each set.
1603 order by access desc, app_name asc
1608 <sql-query name="getMyloginAppDetails">
1609 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1611 SELECT * FROM fn_app where ml_app_name =:appName
1616 <sql-query name="getAppDetailsByAppName">
1617 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1619 SELECT * FROM fn_app where app_name =:appName
1624 <sql-query name="getMyAppDetailsByUebKey">
1625 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1627 SELECT * FROM fn_app where ueb_key =:appKey
1633 <sql-query name="deleteAccountEndpoint">
1635 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1640 <sql-query name="deleteAccountEndpointRecord">
1642 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1647 <sql-query name="deleteEPEndpoint">
1649 DELETE FROM ep_endpoints WHERE id =:epId
1654 <sql-query name="deleteBasicAuthAccount">
1656 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1661 <sql-query name="getAppRoles">
1662 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1664 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1669 <sql-query name="getPartnerAppRolesList">
1670 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1672 SELECT * FROM fn_role where app_id =:appId
1677 <sql-query name="getPortalAppRolesList">
1678 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1680 SELECT * FROM fn_role where app_id is null
1685 <sql-query name="getPortalAppRoleByRoleId">
1686 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1688 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1693 <sql-query name="getPartnerAppRoleByRoleId">
1694 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1696 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1701 <sql-query name="getPartnerAppRoleById">
1702 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1704 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1709 <sql-query name="getPortalAppRoles">
1710 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1712 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1717 <sql-query name="deleteMicroserviceParameter">
1719 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1724 <sql-query name="deleteMicroserviceParameterById">
1726 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1731 <sql-query name="deleteWidgetCatalogParameter">
1733 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1738 <sql-query name="deleteMicroservice">
1740 DELETE FROM ep_microservice WHERE id =:serviceId
1745 <sql-query name="epUserAppId">
1746 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1748 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1753 <sql-query name="userAppsSortPreferenceQuery">
1754 <return alias="appsSortPreferenceQuery"
1755 class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1757 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1762 <sql-query name="userAppsManualSortPrfQuery">
1763 <return alias="AppsManualSortPrfQuery"
1764 class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1766 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1771 <sql-query name="userWidgetManualSortPrfQuery">
1772 <return alias="widgetManualSortPrfQuery"
1773 class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1775 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1779 <sql-query name="appRoles">
1780 <return alias="rolesForApp"
1781 class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1783 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1788 <sql-query name="userAppRolesRequestList">
1789 <return alias="appRolesRequestList"
1790 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1792 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'
1797 <sql-query name="userAppRolesRequestDetailList">
1798 <return alias="appRolesRequestDetailList"
1799 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1802 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1807 <!-- Gets list of enabled apps including accessible and select statuses -->
1808 <sql-query name="getAdminAppCatalog">
1809 <return alias="adminAppCatalog"
1810 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1811 <!-- This query requires one parameter: userId (number) -->
1814 distinct -- multiple roles yield multiple rows
1815 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1816 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1817 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1818 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1819 IF(a.open = 'Y', TRUE, FALSE) as open,
1820 -- ACCESS(-ible) means user has a defined role OR the application is open
1822 -- regular app and user has a role
1823 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1825 when a.OPEN = 'Y' then TRUE
1828 -- SELECT(-ed) indicates user personalization
1830 -- regular app, user has a role, no personalization
1831 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1832 -- regular app, user has role, admin forced a personalization
1833 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1834 -- regular app, user has no role, admin forced a personalization
1835 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1836 -- open app and has personalization
1837 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1840 -- PENDING indicates user checked a box
1842 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1846 -- Portal assigns role 999 to app administrator
1847 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1848 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1849 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'
1850 where a.ENABLED = 'Y'
1851 -- Show accessible apps first, then the rest; sort by name within each set.
1852 order by access desc, app_name asc
1857 <sql-query name="userAppCatalogRoles">
1858 <return alias="epUserAppCatalogRoles"
1859 class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1861 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
1862 from ep_user_roles_request A left join ep_user_roles_request_det B
1863 on a.req_id = b.req_id
1864 where A.user_id=:userid
1865 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1866 and A.request_status ='P'
1871 <sql-query name="getUserApproles">
1872 <return alias="businessCardUserApplicationRoles"
1873 class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1876 select fr.role_name, fa.app_name
1878 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1879 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)
1885 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1886 <sql-query name="deleteNotificationsFromEpNotificationTable">
1888 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1893 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1894 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1896 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1900 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1901 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1903 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1907 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1908 <sql-query name="getAppRoleFunctionList">
1909 <return alias="appRoleFunctionList"
1910 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1913 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1914 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1920 <!-- Gets list of all role functions -->
1921 <sql-query name="getAllRoleFunctions">
1922 <return alias="allRoleFunctions"
1923 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1926 SELECT * from ep_app_function where app_id =:appId
1932 <!-- Get ep_app_function records provided by single parameter -->
1933 <sql-query name="getRoleFunction">
1934 <return alias="RoleFunction"
1935 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1938 SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1944 <!-- Gets the current user app roles records and requires two parameters -->
1945 <sql-query name="getUserAppCurrentRoles">
1946 <return alias="userAppCurrentRoles"
1947 class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
1950 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 and fu.active_yn='Y'
1955 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
1956 <sql-query name="getUserAppExistingRoles">
1957 <return alias="userAppExistingRoles"
1958 class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
1960 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1961 left outer join fn_role fr on fu.role_id = fr.role_id
1962 where fu.user_id =:userId and fu.app_id =:appId
1967 <!-- Gets all functions of portal app -->
1968 <sql-query name="getAllFunctions">
1969 <return alias="allFunctions"
1970 class="org.onap.portalsdk.core.domain.RoleFunction" />
1972 select * from fn_function
1977 <!-- Gets the current user app roles records and requires two parameters -->
1978 <sql-query name="getPartnerAppFunctions">
1979 <return alias="partnerAppFunctions"
1980 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1982 select * from ep_app_function where app_id =:appId
1987 <!-- Gets the all role functions and requires one parameters -->
1988 <sql-query name="uploadAllRoleFunctions">
1989 <return alias="allRoleFunctions"
1990 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
1992 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
1997 <!-- Gets the all application role functions from ep_app_function table and requires one parameters -->
1998 <sql-query name="uploadPartnerRoleFunctions">
1999 <return alias="partnerRoleFunctions"
2000 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2002 select distinct eprf.function_cd, epfn.function_name from ep_app_role_function eprf left outer join ep_app_function epfn on eprf.function_cd = epfn.function_cd where eprf.role_id =:roleId
2007 <sql-query name="getRoletoUpdateInExternalAuthSystem">
2008 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2010 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2015 <!-- Gets all active records from fn_role that is associated with this app-->
2016 <sql-query name="getActiveRolesOfApplication">
2017 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2019 select * from fn_role where active_yn = 'Y' and app_id=:appId
2024 <sql-query name="getBulkUserRoles">
2025 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2027 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2028 left outer join fn_role fr on fr.role_id = fur.role_id
2029 left outer join fn_app fa on fa.app_id = fur.app_id
2030 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2035 <sql-query name="getBulkUsersForSingleRole">
2036 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2038 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2039 left outer join fn_role fr on fr.role_id = fur.role_id
2040 left outer join fn_app fa on fa.app_id = fur.app_id
2041 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2046 <sql-query name="getCentralizedApps">
2047 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2049 select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2054 <sql-query name="getUserRoles">
2055 <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2058 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
2062 <sql-query name="getRoleFunctionsOfUser">
2064 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2065 where fu.role_id = ep.role_id
2066 and fu.app_id = ep.app_id
2067 and fu.user_id =:userId and ep.app_id = 1
2068 and ea.function_cd = ep.function_cd
2070 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2072 and app_r_f.function_cd = a_f.function_cd
2075 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
2076 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2081 <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
2083 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2084 where fu.role_id = ep.role_id
2085 and fu.app_id = ep.app_id
2086 and fu.user_id =:userId
2087 and ea.function_cd = ep.function_cd
2090 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
2091 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2096 <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
2097 <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2099 SELECT app.APP_ID, app.APP_NAME, app.APP_TYPE FROM FN_APP app
2100 inner join FN_USER_ROLE userrole ON userrole.APP_ID=app.APP_ID
2101 where userrole.USER_ID = :userId AND userrole.ROLE_ID=999 AND (app.ENABLED = 'Y' OR app.APP_ID=1)
2103 SELECT DISTINCT app.APP_ID, app.app_name, app.APP_TYPE FROM fn_user user
2104 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2105 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2106 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2107 INNER JOIN ep_app_role_function appfunction ON appfunction.app_id = app.app_id and appfunction.role_id=role.ROLE_ID and (appfunction.function_cd like '%APPROVER%')
2108 WHERE user.user_id = :userId ;
2112 <sql-query name="getApplicationsofTheUserContainsApprover">
2113 <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2115 SELECT DISTINCT app.APP_ID, app.APP_TYPE, app.app_name FROM fn_user user
2116 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2117 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2118 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2119 INNER JOIN ep_app_role_function appfunction ON appfunction.app_id = app.app_id and appfunction.role_id=role.ROLE_ID and (appfunction.function_cd like '%APPROVER%')
2120 WHERE user.user_id = :userId ;
2126 <sql-query name="getUserRolesForLeftMenu">
2127 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2130 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
2131 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2132 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2133 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2134 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)
2139 <sql-query name="getRolesForLeftMenu">
2140 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2143 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
2144 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2145 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2146 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2147 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)
2152 <!-- Gets all functions for an application along with global functions and requires single parameter -->
2153 <sql-query name="getMenuFunctions">
2155 select f.function_cd from ep_app_function f
2156 where f.app_id =:appId
2158 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2159 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2164 <sql-query name="getRequestIdsForApp">
2165 <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2167 select * from ep_user_roles_request where app_id =:app_id
2172 <sql-query name="ApplicationUserRoles">
2173 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2175 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,
2176 fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2177 from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2178 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
2180 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,
2181 fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2182 from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2183 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'
2184 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';
2188 <sql-query name="getCurrentAppRoleFunctions">
2189 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2191 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
2192 and ef.function_cd =:functionCd and epr.app_id =:appId
2197 <sql-query name="deleteUserWidgetPlacement">
2199 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2204 <sql-query name="getCentralizedAppsOfUser">
2205 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2208 select distinct fa.app_id, fa.app_name
2209 from fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2210 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2211 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)
2217 <query name="getEPUserByOrgUserId">
2219 FROM EPUser WHERE orgUserId = :org_user_id
2223 <query name="getEPUserByLoginId">
2225 FROM EPUser WHERE loginId = :login_id
2229 <query name="getEPUserByLoginIdLoginPwd">
2231 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2235 <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2236 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2238 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
2239 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2240 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
2245 <sql-query name="getGlobalRolesOfPortal">
2246 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2248 select * from fn_role where role_name like 'global_%' and app_id is null and active_yn='Y'
2253 <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2254 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2256 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2261 <sql-query name="getAppFunctionOnCodeAndAppId">
2262 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2264 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2269 <sql-query name="getGlobalRoleForRequestedApp">
2270 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2272 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
2273 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2274 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
2279 <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2280 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2282 select distinct fr.role_id, fr.role_name, fr.active_yn, fr.priority, epr.function_cd, ep.function_name, ep.app_id, epr.role_app_id
2283 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2284 where fr.role_id = epr.role_id and ep.function_cd = epr.function_cd and ep.app_id = epr.app_id and epr.app_id = :appId and epr.role_app_id = 1
2289 <sql-query name="updateMenuFunctionalAndRoles">
2291 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
2296 <!-- Remove any favorites associated with a menu item that is associated with this app -->
2297 <sql-query name="removeAppFromMenuFavorites">
2299 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
2304 <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2305 <sql-query name="removeAppFromMenuFunctionalRoles">
2307 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2312 <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2313 <sql-query name="removeAppFromEpAppRoleFunction">
2315 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2320 <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2321 <sql-query name="removeAppFromEpAppFunction">
2323 DELETE FROM ep_app_function WHERE app_id=:app_id
2328 <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2329 <sql-query name="removeAppFromFnUserRole">
2331 DELETE FROM fn_user_role WHERE app_id=:app_id
2336 <!-- Remove any widgets that is associated with this app-->
2337 <sql-query name="removeAppFromEpWidgetCatalogRole">
2339 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2344 <!-- Remove any notifications that is associated with this app-->
2345 <sql-query name="removeAppFromEpRoleNotification">
2347 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2348 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2353 <!-- Remove all records from fn_role that is associated with this app-->
2354 <sql-query name="removeAppFromFnRole">
2356 DELETE FROM fn_role where app_id=:app_id
2361 <!-- Remove app contact us entries that is associated with this app-->
2362 <sql-query name="removeAppFromAppContactUs">
2364 DELETE FROM fn_app_contact_us where app_id=:app_id
2369 <!-- Remove app personalization entries that is associated with this app-->
2370 <sql-query name="removeAppFromEpPersUserAppSel">
2372 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2377 <!-- Remove app personalization sorting entries that is associated with this app-->
2378 <sql-query name="removeAppFromEpPersUserAppManSort">
2380 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2385 <!-- Remove rows from user role request table entries that is associated with this app-->
2386 <sql-query name="removeAppFromEpUserRolesRequest">
2388 DELETE FROM ep_user_roles_request where app_id=:app_id
2393 <!-- Remove rows from web analytics table entries that is associated with this app-->
2394 <sql-query name="removeAppFromEpWebAnalytics">
2396 DELETE FROM ep_web_analytics_source where app_id=:app_id
2401 <!-- Remove row from fn app table -->
2402 <sql-query name="removeAppFromFnApp">
2404 DELETE FROM fn_app where app_id=:app_id
2410 <query name="getBasicauthAccount">
2411 select id,password from BasicAuthCredentials
2414 <query name="getMicroserviceInfo">
2415 select id,password from MicroserviceData
2418 <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2419 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2421 select * from fn_user_role where user_id= :userId
2422 and role_id= :roleId
2428 <sql-query name="userAppGlobalRoles">
2429 <return alias="userAppGlobalRoles"
2430 class="org.onap.portalapp.portal.domain.EPRole" />
2432 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2433 from fn_user_role a, fn_role fr, fn_user fu
2434 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
2439 <sql-query name="getAllCentralizedAppsRoles">
2440 <return alias="allCentralizedAppsRoles"
2441 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2443 select distinct fa.app_id, fa.auth_namespace, fr.role_name, fr.role_id from fn_app fa, fn_role fr where fa.auth_central = 'Y' and fa.auth_namespace is not null
2444 and (fa.app_id = fr.app_id or COALESCE(fr.app_id,1) = fa.app_id) and fr.active_yn = 'Y' order by fa.app_id ;
2448 <sql-query name="getUserCentralizedAppRoles">
2449 <return alias="userCentralizedAppRoles"
2450 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2452 select distinct fur.app_id, fa.auth_namespace, fr.role_name, fur.role_id from fn_user_role fur, fn_app fa, fn_role fr, fn_user fu
2453 where fa.app_id = fur.app_id
2454 and fr.role_id = fur.role_id
2455 and fu.user_id = fur.user_id
2456 and fu.ORG_USER_ID = :orgUserId
2457 and fa.auth_namespace is not null
2458 and fr.active_yn = 'Y'
2462 <sql-query name="getAprroverRoleFunctionsOfUser">
2464 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2465 where fu.role_id = ep.role_id
2466 and fu.app_id = ep.app_id
2467 and fu.user_id =:userId
2468 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2471 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
2472 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2476 <sql-query name="getUserApproverRoles">
2478 select distinct fr.role_name from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_role fr
2479 where fu.role_id = ep.role_id
2480 and fu.app_id = ep.app_id
2481 and fu.user_id = :userId
2482 and fu.role_id = fr.role_id and fr.active_yn='Y'
2483 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2486 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
2487 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2491 <sql-query name="getAdminAppsForTheUser">
2493 select fa.app_id from fn_user_role ur,fn_app fa where ur.user_id =:userId and ur.app_id=fa.app_id and ur.role_id= 999 and (fa.enabled = 'Y' || fa.app_id=1)
2500 <sql-query name="getUserRoleOnUserIdAndAppId">
2501 <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
2504 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id FROM fn_role fr, fn_user_role fur WHERE fr.role_id = fur.role_id AND fur.user_id= :userId AND fur.app_id = :appId AND fr.active_yn='Y'
2508 </hibernate-mapping>