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 <!-- multilanguage -->
47 <class name="Language" table="fn_language">
48 <id name="languageId" column="language_id">
49 <generator class="native">
50 <param name="sequence"></param>
53 <property name="languageName" column="language_name" />
54 <property name="languageAlias" column="language_alias" />
57 <class name="DisplayText" table="fn_display_text">
58 <id name="id" column="id">
59 <generator class="native">
60 <param name="sequence"></param>
64 <property name="languageId" column="language_id" />
65 <property name="textId" column="text_id" />
66 <property name="label" column="text_label" />
69 <!-- Widget class mapping details -->
70 <class name="Widget" table="FN_WIDGET">
71 <id name="id" column="WIDGET_ID">
72 <generator class="native">
73 <param name="sequence">seq_fn_widget</param>
76 <property name="width" type="integer">
77 <column name="WDG_WIDTH" not-null="true" default="0"></column>
79 <property name="height" type="integer">
80 <column name="WDG_HEIGHT" not-null="true" default="0"></column>
82 <property name="url" type="string">
83 <column name="WDG_URL" not-null="true" default="?"></column>
85 <property name="name" type="string">
86 <column name="WDG_NAME" not-null="true" default="?"></column>
89 <property name="appId" type="long">
90 <column name="APP_ID" not-null="true"></column>
95 <class name="EPEndpoint" table="EP_ENDPOINTS">
96 <id name="id" column="id">
97 <generator class="native">
98 <param name="sequence">seq_ep_endpoint</param>
101 <property name="name" type="string">
102 <column name="url" not-null="true"></column>
107 <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
108 <id name="id" column="id">
109 <generator class="native">
110 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
113 <property name="ep_id" type="long">
114 <column name="ep_id"></column>
116 <property name="account_id" type="long">
117 <column name="account_id"></column>
121 <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
122 <id name="id" column="id">
123 <generator class="native">
124 <param name="sequence">seq_ep_widget_catalog_parameter</param>
127 <property name="widgetId" type="long">
128 <column name="widget_id"></column>
130 <property name="userId" type="long">
131 <column name="user_id" not-null="true"></column>
133 <property name="paramId" type="long">
134 <column name="param_id" not-null="true"></column>
137 <property name="user_value" type="string">
138 <column name="user_value" not-null="true"></column>
143 <class name="BasicAuthCredentials" table="ep_basic_auth_account">
144 <id name="id" column="id">
145 <generator class="native">
146 <param name="sequence">seq_ep_basic_auth_account</param>
149 <property name="applicationName" type="string">
150 <column name="ext_app_name" not-null="true" default="?"></column>
152 <property name="username" type="string">
153 <column name="username"></column>
155 <property name="password" type="string">
156 <column name="password"></column>
158 <property name="isActive" type="string">
159 <column name="active_yn" not-null="true" default="Y"></column>
163 <!-- EPUserNotification class mapping details -->
164 <class name="EPUserNotification" table="ep_user_notification">
165 <id name="id" column="id">
166 <generator class="native">
167 <param name="sequence">seq_ep_user_notification</param>
170 <property name="userId" column="User_ID" />
171 <property name="notificationId" column="notification_ID" />
172 <property name="viewed" column="is_viewed" />
173 <property name="updateTime" column="updated_time" />
177 <!-- User class mapping details -->
178 <class name="EPUser" table="FN_USER">
179 <id name="id" column="user_id">
180 <generator class="native">
181 <param name="sequence">seq_fn_user</param>
185 <property name="orgId" column="org_id" />
186 <property name="managerId" column="manager_id" />
187 <property name="firstName" column="first_name" />
188 <property name="middleInitial" column="middle_name" />
189 <property name="lastName" column="last_name" />
190 <property name="phone" column="phone" />
191 <property name="fax" column="fax" />
192 <property name="cellular" column="cellular" />
193 <property name="email" column="email" />
194 <property name="addressId" column="address_id" />
195 <property name="alertMethodCd" column="alert_method_cd" />
197 <property name="address1" column="address_line_1" />
198 <property name="address2" column="address_line_2" />
199 <property name="city" column="city" />
200 <property name="state" column="state_cd" />
201 <property name="zipCode" column="zip_code" />
202 <property name="country" column="country_cd" />
204 <property name="hrid" column="hrid" />
205 <property name="orgUserId" column="org_user_id" />
206 <property name="orgCode" column="org_code" />
207 <property name="loginId" column="login_id" />
208 <property name="loginPwd" column="login_pwd" />
209 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
211 <property name="locationClli" column="location_clli" />
212 <property name="orgManagerUserId" column="org_manager_userid" />
213 <property name="company" column="company" />
214 <property name="department" column="department" />
215 <property name="departmentName" column="department_name" />
216 <property name="businessUnit" column="business_unit" />
217 <property name="businessUnitName" column="business_unit_name" />
218 <property name="jobTitle" column="job_title" />
219 <property name="siloStatus" column="silo_status" />
220 <property name="financialLocCode" column="fin_loc_code" />
221 <property name="costCenter" column="cost_center" />
223 <property name="active" column="active_yn" type="yes_no" />
224 <property name="internal" column="is_internal_yn" type="yes_no" />
226 <property name="created" type="timestamp" column="created_date" />
227 <property name="modified" type="timestamp" column="modified_date" />
229 <property name="createdId" column="created_id" />
230 <property name="modifiedId" column="modified_id" />
231 <property name="timeZoneId" column="timezone" />
232 <property name="systemUser" column="is_system_user" type="yes_no"/>
233 <property name="languageId" column="language_id" />
235 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
237 <key column="user_id" />
238 <one-to-many class="org.onap.portalapp.portal.domain.EPUserApp" />
243 <class name="MicroserviceData" table="ep_microservice">
244 <id name="id" column="id">
245 <generator class="native">
246 <param name="sequence">seq_ep_microservice</param>
250 <property name="name" type="string">
251 <column name="name" not-null="true" default="?"></column>
254 <property name="desc" type="string">
255 <column name="description" not-null="true" default="?"></column>
258 <property name="appId" type="long">
259 <column name="appId" not-null="true"></column>
262 <property name="url" type="string">
263 <column name="endpoint_url" not-null="true" default="?"></column>
266 <property name="securityType" type="string">
267 <column name="security_type" not-null="true" default="?"></column>
271 <property name="username" type="string">
272 <column name="username" not-null="true" default="?"></column>
275 <property name="password" type="string">
276 <column name="password" not-null="true" default="?"></column>
279 <property name="active" type="string">
280 <column name="active" not-null="true" default="Y"></column>
284 <class name="MicroserviceParameter" table="ep_microservice_parameter">
285 <id name="id" column="id">
286 <generator class="native">
287 <param name="sequence">seq_ep_microservice_parameter</param>
291 <property name="serviceId" type="long">
292 <column name="service_id" not-null="true"></column>
295 <property name="para_key" type="string">
296 <column name="para_key" not-null="true" default="?"></column>
299 <property name="para_value" type="string">
300 <column name="para_value" not-null="true" default="?"></column>
304 <!-- UserApp class mapping details -->
305 <class name="EPUserApp" table="fn_user_role">
307 <key-property name="userId" type="long">
308 <column name="user_id" precision="11" scale="0" />
310 <key-many-to-one name="app"
311 class="org.onap.portalapp.portal.domain.EPApp" column="app_id" />
312 <key-many-to-one name="role"
313 class="org.onap.portalapp.portal.domain.EPRole" column="role_id" />
315 <property name="priority">
316 <column name="priority" default="1" precision="4" scale="0" />
320 <!-- User App class mapping details -->
321 <class name="EPApp" table="fn_app">
322 <id name="id" column="app_id">
323 <generator class="native">
324 <param name="sequence">seq_fn_app</param>
327 <property name="name" type="string">
328 <column name="app_name" not-null="true" default="?"></column>
330 <property name="imageUrl" column="app_image_url" />
331 <property name="description" column="app_description" />
332 <property name="notes" column="app_notes" />
333 <property name="url" column="app_url" />
334 <property name="alternateUrl" column="app_alternate_url" />
335 <property name="appRestEndpoint" column="app_rest_endpoint" />
336 <property name="mlAppName" type="string">
337 <column name="ml_app_name" not-null="true" default="?"></column>
339 <property name="mlAppAdminId" type="string">
340 <column name="ml_app_admin_id" not-null="true" default="?"></column>
342 <property name="motsId" column="mots_id" />
343 <property name="appPassword" type="string">
344 <column name="app_password" not-null="true" default="?"></column>
346 <property name="thumbnail" column="thumbnail" />
347 <property name="username" type="string">
348 <column name="app_username" not-null="true" default="?"></column>
350 <property name="open" type="yes_no">
351 <column name="open" not-null="true" default="Y"></column>
353 <property name="enabled" type="yes_no">
354 <column name="enabled" not-null="true" default="N"></column>
356 <property name="uebTopicName" type="string">
357 <column name="ueb_topic_name"></column>
359 <property name="uebKey" type="string">
360 <column name="ueb_key"></column>
362 <property name="uebSecret" type="string">
363 <column name="ueb_secret"></column>
365 <property name="appType" type="integer">
366 <column name="app_type"></column>
368 <property name="centralAuth" type="yes_no">
369 <column name="auth_central"></column>
371 <property name="nameSpace" type="string">
372 <column name="auth_namespace"></column>
374 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
378 <!-- EPUserAppsSortPreference class mapping details -->
379 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
380 <id name="id" column="ID">
381 <generator class="native">
382 <param name="sequence">seq_ep_usrappsrtprf</param>
385 <property name="userId" column="USER_ID" />
386 <property name="sortPref" column="SORT_PREF" />
389 <!-- EPUserAppsManualSortPreference class mapping details -->
390 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
391 <id name="id" column="ID">
392 <generator class="native">
393 <param name="sequence">seq_ep_usrmanappsrtprf</param>
396 <property name="userId" column="user_id"
397 unique-key="uk_1_ep_pers_user_app_man_sort" />
398 <property name="appId" column="app_id"
399 unique-key="uk_1_ep_pers_user_app_man_sort" />
400 <property name="appManualSortOrder" column="sort_order" type="int" />
403 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
404 <id name="id" column="ID">
405 <generator class="native">
406 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
409 <property name="userId" column="user_id" />
410 <property name="widgetId" column="widget_id" />
411 <property name="widgetRow" column="x" />
412 <property name="widgetCol" column="y" />
413 <property name="widgetHeight" column="height" />
414 <property name="widgetWidth" column="width" />
417 <!-- User Role class mapping details -->
418 <class name="EPRole" table="FN_ROLE" >
419 <id name="id" column="role_id">
420 <generator class="native">
421 <param name="sequence">seq_fn_role</param>
425 <property name="name" column="role_name" />
426 <property name="priority" column="priority" />
427 <property name="active" column="active_yn" type="yes_no" />
428 <!-- 2 lines below may be removed! -->
429 <property name="appId" column="APP_ID" />
430 <property name="appRoleId" column="APP_ROLE_ID" />
431 <!-- 2 lines above may be removed -->
433 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
435 <key column="role_id" />
436 <many-to-many column="function_cd"
437 class="org.onap.portalsdk.core.domain.RoleFunction" />
440 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
442 <key column="parent_role_id" />
443 <many-to-many column="child_role_id"
444 class="org.onap.portalapp.portal.domain.EPRole" />
447 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
449 <key column="child_role_id" />
450 <many-to-many column="parent_role_id"
451 class="org.onap.portalapp.portal.domain.EPRole" />
456 <!-- User App class mapping details -->
457 <class name="AppContactUs" table="fn_app_contact_us">
458 <id name="id" column="app_id">
459 <generator class="foreign">
460 <param name="property">app</param>
463 <property name="url" column="url" />
464 <property name="description" column="description" />
465 <property name="contactName" column="contact_name" />
466 <property name="contactEmail" column="contact_email" />
467 <property name="activeYN" column="active_yn" />
469 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
473 <!-- Personalization of user app selections -->
474 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
475 <id name="id" column="id">
476 <generator class="native">
477 <param name="property">seq_fn_pers_user_app_sel</param>
480 <property name="userId" column="user_id" />
481 <property name="appId" column="app_id" />
482 <property name="statusCode" column="status_cd" />
485 <!-- EPAppRoleFunction class mapping details -->
486 <class name="EPAppRoleFunction" table="ep_app_role_function">
487 <id name="id" column="id">
488 <generator class="native">
489 <param name="property">seq_epp_app_role_func</param>
492 <property name="roleId" column="role_id"></property>
493 <property name="appId" column="app_id"></property>
494 <property name="code" column="function_cd"></property>
495 <property name="roleAppId" column="role_app_id"></property>
498 <!-- CentralRoleFunction class mapping details -->
499 <class name="CentralV2RoleFunction" table="ep_app_function">
501 <key-property name="appId"
503 <key-property name="code"
504 column="function_cd" />
506 <property name="name" column="function_name" />
509 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
510 <id name="id" column="id">
511 <generator class="native">
512 <param name="property">seq_ep_pers_user_widget_sel</param>
515 <property name="userId" column="user_id" />
516 <property name="widgetId" column="widget_id" />
517 <property name="statusCode" column="status_cd" />
520 <!-- EPUserAppRolesRequest class mapping details -->
521 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
522 <id name="id" column="req_id">
523 <generator class="native">
524 <param name="sequence">seq_ep_user_role_request</param>
527 <property name="userId" column="user_id" />
528 <property name="appId" column="app_id" />
529 <property name="createdDate" column="created_date" type="timestamp" />
530 <property name="updatedDate" column="updated_date" type="timestamp" />
531 <property name="requestStatus" column="request_status" />
533 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
534 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
535 <key column="req_id" />
537 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
541 <!-- EPUserAppRolesRequestDetail class mapping details -->
542 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
543 <id name="id" column="id">
544 <generator class="native">
545 <param name="sequence">seq_ep_user_role_request_det</param>
548 <property name="reqRoleId" column="requested_role_id" />
549 <property name="reqType" column="request_type" />
550 <many-to-one name="epRequestIdData" fetch="select"
551 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest">
552 <column name="req_id" not-null="true" />
556 <!-- multilanguage -->
557 <query name="queryLanguage">
563 <query name="displayText">
565 from DisplayText where languageId = :language_id
569 <query name="queryLanguageByLanguageId">
571 from Language where languageId = :language_id
575 <!-- show the current user plus related users -->
576 <sql-query name="relatedUsers">
577 <return-scalar column="org_user_id" type="java.lang.String" />
579 select t.org_user_id from (
580 select distinct c.org_user_id org_user_id, c.last_login_date from
581 (select c.org_user_id, c.last_login_date from
583 (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,
585 where a.ROLE_ID = b.role_id
586 and a.APP_ID = b.app_id
587 and a.USER_ID = c.user_id
589 select org_user_id , last_login_date from fn_user where org_user_id=:userId
591 order by c.last_login_date desc limit 10
596 <!-- requires values for named parameters :searchQuery and :userId -->
597 <sql-query name="searchPortal">
598 <return alias="searchResult"
599 class="org.onap.portalapp.portal.ecomp.model.SearchResultItem" />
601 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
603 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
605 upper( :searchQuery ) != ''
606 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
607 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
608 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%'))
610 ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
612 last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
614 (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
616 first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
617 and upper(active_yn) = 'Y'
621 select distinct 'Application' CATEGORY, b1.app_name NAME,
622 if (b1.app_type = 2,'false','true') UUID,
623 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
627 from fn_user_role a, fn_user b, fn_app c
628 where a.USER_ID = b.user_id
629 and upper(b.org_user_id) = upper( :userId )
630 and a.app_id = c.app_id
631 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
632 and upper(c.ENABLED) = 'Y'
633 ) a1 right outer join
634 (select * from fn_app where
635 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
636 and upper(ENABLED) = 'Y') b1
637 on a1.APP_ID = b1.app_id
641 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
642 if (b1.app_type = 2,'false','true') UUID,
643 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
645 (select distinct d.*, e.app_type from fn_user a,
646 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
647 where a.USER_ID = b.user_id
648 and b.role_id = c.role_id
649 and c.menu_id = d.menu_id
650 and upper(a.org_user_id) = upper( :userId )
651 and c.APP_ID = e.app_id
652 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
653 and upper(d.active_yn) = 'Y'
654 and upper(e.enabled) = 'Y'
655 ) a1 right outer join
657 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
658 where active_yn = 'Y'
659 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
660 and a.menu_id = b.menu_id
661 and b.app_id = c.app_id
664 on a1.menu_id = b1.menu_id
667 select distinct CATEGORY, NAME, UUID, TARGET
671 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
674 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
675 where upper(a.org_user_id) = upper( :userId )
676 and a.user_id = b.user_id
677 and b.role_id = c.role_id
678 and c.widget_id = d.widget_id
679 and d.all_user_flag<>'Y'
680 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
684 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
687 from ep_widget_catalog d
688 where d.all_user_flag='Y'
689 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
693 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
696 from fn_user a, fn_user_role b, ep_widget_catalog d
697 where upper(a.org_user_id) = upper( :userId )
698 and a.user_id = b.user_id
700 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
704 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
710 <!-- requires values for named parameters :searchQuery and :userId -->
711 <sql-query name="getNotifications">
712 <return alias="notificationResult"
713 class="org.onap.portalapp.portal.transport.EpNotificationItem" />
715 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
718 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
721 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
724 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
725 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
726 from ep_notification a, ep_role_notification b
727 where a.notification_id = b.notification_id
728 and (end_time is null || SYSDATE() <= end_time )
729 and (start_time is null || SYSDATE() >= start_time)
730 and a.is_for_all_roles = 'N'
733 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
734 from fn_user a, fn_user_role b, fn_role c, fn_app d
735 where COALESCE(c.app_id,1) = d.app_id
736 and a.user_id = b.user_id
737 and a.user_id = :user_id
738 and b.role_id = c.role_id
739 and (d.enabled='Y' or d.app_id=1)
743 a.role_id = b.role_id
746 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
749 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
750 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
751 from ep_notification a, ep_role_notification b
752 where a.notification_id = b.notification_id
753 and (end_time is null || SYSDATE() <= end_time )
754 and (start_time is null || SYSDATE() >= start_time)
755 and a.is_for_all_roles = 'N'
759 a.recv_user_id=:user_id
763 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
764 from ep_notification a
765 where a.notification_id
766 and (end_time is null || SYSDATE() <= end_time )
767 and (start_time is null || SYSDATE() >= start_time)
768 and a.is_for_all_roles = 'Y'
776 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'
778 order by priority desc, created_date desc,start_Time desc
782 (SELECT @rn /*'*/:=/*'*/ 0) t2
787 <sql-query name="getUsersByOrgIdsNotifications">
788 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
790 SELECT * from fn_user where org_user_id in (:OrgIds)
796 <!-- Gets all Admin notification history; accepts no parameters -->
797 <sql-query name="getAdminNotificationHistoryVO">
798 <return alias="adminNotificationHistoryVOResult"
799 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
803 n.notification_ID AS notificationId,
804 n.is_for_online_users AS isForOnlineUsers,
805 n.is_for_all_roles AS isForAllRoles,
806 n.active_YN AS activeYn,
807 n.msg_header AS msgHeader,
808 n.msg_description AS msgDescription,
809 n.msg_source AS msgSource,
810 n.start_Time AS startTime,
811 n.end_time AS endTime,
813 n.creator_ID AS creatorId,
814 n.created_date AS createdDate,
815 n.notification_hyperlink AS notificationHyperlink,
816 u.org_user_id AS loginId
820 fn_user u on u.user_id = n.creator_id
822 n.active_YN='Y' and msg_source='EP'
827 n.notification_ID AS notificationId,
828 n.is_for_online_users AS isForOnlineUsers,
829 n.is_for_all_roles AS isForAllRoles,
830 n.active_YN AS activeYn,
831 n.msg_header AS msgHeader,
832 n.msg_description AS msgDescription,
833 n.msg_source AS msgSource,
834 n.start_Time AS startTime,
835 n.end_time AS endTime,
837 n.creator_ID AS creatorId,
838 n.created_date AS createdDate,
839 n.notification_hyperlink AS notificationHyperlink,
840 u.org_user_id AS loginId
844 fn_user u on u.user_id = n.creator_id
846 ep_role_notification r on r.notification_ID=n.notification_ID
848 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
849 )n order by n.startTime desc
853 <sql-query name="getEpNotificationAppRoles">
854 <return alias="notificationAppRoles"
855 class="org.onap.portalapp.portal.domain.EcompAppRole" />
856 <!-- This query requires no parameters. -->
859 select a.app_id, a.app_name, b.role_id, b.role_name from
860 (select * from fn_app where app_id = 1) a,
861 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
863 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
865 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
872 <!-- Gets all notification history -->
873 <sql-query name="getNotificationHistoryVO">
874 <return alias="notificationHistoryVOResult"
875 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
878 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
879 startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
883 a.notification_ID AS notificationId,
884 is_for_online_users AS isForOnlineUsers,
885 is_for_all_roles AS isForAllRoles,
886 msg_header AS msgHeader,
887 msg_description AS msgDescription,
888 msg_source AS msgSource,
889 start_Time AS startTime,
892 created_date AS createdDate,
893 creator_ID AS creatorId,
894 notification_hyperlink AS notificationHyperlink,
896 active_YN AS activeYn,
897 if (is_viewed is null, 'N', is_viewed)
901 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
902 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
903 creator_ID,notification_hyperlink,active_YN
906 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
907 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,
908 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
911 END AS login_id,b.recv_user_id
912 from ep_notification a, ep_role_notification b,fn_user u
913 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
914 and a.is_for_all_roles = 'N'
916 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
918 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
920 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
922 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
926 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
927 from fn_user a, fn_user_role b, fn_role c, fn_app d
928 where COALESCE(c.app_id,1) = d.app_id
929 and a.user_id = b.user_id
930 and a.user_id = :user_id
931 and b.role_id = c.role_id
932 and (d.enabled='Y' or d.app_id=1)
936 a.role_id = b.role_id
940 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
941 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
942 creator_ID,notification_hyperlink,active_YN
945 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
946 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,
947 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
950 END AS login_id,b.recv_user_id
951 from ep_notification a, ep_role_notification b,fn_user u
952 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
953 and a.is_for_all_roles = 'N'
955 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
957 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
959 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
961 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
966 a.recv_user_id=:user_id
971 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
972 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
973 creator_ID, a.notification_hyperlink,a.active_YN
974 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
975 where a.notification_id
976 and a.is_for_all_roles = 'Y'
978 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
980 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
982 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
984 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
987 ) a left outer join (
988 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
990 on a.notification_id = m.notification_ID
994 order by start_Time desc,end_time desc
996 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
1001 <!-- shows the received recipient to whom the notification is delivered from external system -->
1002 <sql-query name="messageRecipients">
1003 <return-scalar column="org_user_id" type="java.lang.String" />
1005 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
1010 <!-- Gets role details for a specified notification -->
1011 <sql-query name="getNotificationRoles">
1012 <return alias="notificationRolesResult"
1013 class="org.onap.portalapp.portal.transport.EpRoleNotificationItem" />
1017 ep_role_notification
1019 notification_Id = :notificationId
1023 <!-- Gets all applications, possibly with contact information -->
1024 <sql-query name="getAppsAndContacts">
1025 <return alias="appContactUs"
1026 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1027 <!-- This query requires no parameters. -->
1030 a.app_id as appId, a.app_name as appName,
1031 c.contact_name as contactName,
1032 c.contact_email as contactEmail, c.url, c.description,
1033 c.active_yn as activeYN
1038 on a.app_id = c.app_id
1040 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1045 <!-- Gets all rows from the app-contact-us table, extended with app information -->
1046 <sql-query name="getAppContactUsItems">
1047 <return alias="appContactUs"
1048 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1049 <!-- This query requires no parameters. -->
1052 c.app_id as appId, c.contact_name as contactName,
1053 c.contact_email as contactEmail, c.url, c.description,
1054 c.active_yn as activeYN, a.app_name as appName
1059 on a.app_id = c.app_id
1061 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1066 <!-- Gets one row for each app with the category and catenated functions. -->
1067 <sql-query name="getAppCategoryFunctions">
1068 <return alias="appCategoryFunctions"
1069 class="org.onap.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1070 <!-- This query requires no parameters. -->
1072 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1073 r.category as category, r.app_id as appId, r.app_name as application,
1074 group_concat(function_text separator ', ') as functions
1077 app_id, function_text, app_name,
1078 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1081 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1082 k.text parent_menu, k.parent_menu_id parent_menu_id
1085 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1087 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1089 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1091 fn_menu_functional k
1092 WHERE j.parent_menu_id = k.menu_id
1094 fn_menu_functional a
1095 WHERE fn.parent_menu_id = a.menu_id
1097 (SELECT @rn /*'*/:=/*'*/ 0) t2
1098 group by r.category, r.app_id, r.app_name
1099 order by category, app_name
1104 <!-- Gets one row for each function-application-role combination. -->
1105 <sql-query name="getAppAccessFunctionRole">
1106 <return alias="appAccessFunctionRole"
1107 class="org.onap.portalapp.portal.domain.GetAccessResult" />
1108 <!-- This query requires one parameters. -->
1110 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1111 (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,
1112 o.requested_role_id,o.request_type
1114 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1117 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1120 where COALESCE(c.app_id,1) = d.app_id
1121 and (d.enabled='Y' or d.app_id=1)
1122 and c.active_yn = 'Y'
1125 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1127 A.MENU_ID = B.MENU_ID
1130 on a.role_id = b.role_id) t left outer join
1131 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1132 where er.req_id=ed.req_id
1133 and upper(ed.request_type)='P'
1134 and er.user_id =:userId) o
1135 on t.app_id=o.app_id
1136 and t.role_id = o.requested_role_id
1137 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1142 <query name="getCommonWidgetItem">
1143 from CommonWidget where category = :cat order by
1147 <sql-query name="getGuestLastLogin">
1148 <return-scalar column="audit_date" type="java.util.Date" />
1150 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1154 <sql-query name="getActiveUsers">
1155 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1157 SELECT * FROM FN_USER WHERE active_yn='Y'
1161 <sql-query name="getAppsAdmins">
1162 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1164 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
1168 <sql-query name="getPortalAdmins">
1169 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1171 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
1175 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1176 <sql-query name="getPersUserApps">
1177 <return alias="persUserApps" class="org.onap.portalapp.portal.domain.EPApp" />
1178 <!-- This query requires one parameter: userId (number) -->
1181 distinct -- multiple roles yield multiple rows
1182 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1183 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1184 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1185 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1186 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
1188 -- Portal assigns role 999 to app administrator
1189 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1190 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1191 where a.ENABLED = 'Y'
1193 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1195 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1202 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1204 <sql-query name="getPersAdminApps">
1205 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1206 <!-- This query requires one parameter: userId (number) -->
1209 distinct -- multiple roles yield multiple rows
1210 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1211 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1212 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1213 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1214 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1217 -- Portal assigns role 999 to app administrator
1218 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1219 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1220 where a.ENABLED = 'Y'
1222 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1224 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1226 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1233 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1234 <sql-query name="getPersAdminAppsOrderByName">
1235 <return alias="AdminAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1236 <!-- This query requires one parameter: userId (number) -->
1239 distinct -- multiple roles yield multiple rows
1240 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1241 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1242 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1243 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1244 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1247 -- Portal assigns role 999 to app administrator
1248 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1249 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1250 where a.ENABLED = 'Y'
1252 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1254 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1256 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1259 select distinct -- multiple roles yield multiple rows
1260 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1261 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1262 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1263 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1264 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1266 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1267 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
1274 <!--Gets personalized list of enabled and accessible User apps by name -->
1275 <sql-query name="getPersUserAppsOrderByName">
1276 <return alias="UserAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1277 <!-- This query requires one parameter: userId (number) -->
1280 distinct -- multiple roles yield multiple rows
1281 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1282 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1283 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1284 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1285 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1288 -- Portal assigns role 999 to app administrator
1289 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1290 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1291 where a.ENABLED = 'Y'
1293 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1295 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
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
1314 <!--Gets personalized list of enabled and accessible Super Admin apps by
1316 <sql-query name="getAdminAppsOrderByLastUsed">
1317 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1322 distinct -- multiple roles yield multiple rows
1323 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1324 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1325 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1326 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1327 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1330 -- Portal assigns role 999 to app administrator
1331 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1332 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1333 where a.ENABLED = 'Y'
1335 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1337 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1339 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1342 select distinct -- multiple roles yield multiple rows
1343 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1344 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1345 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1346 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1347 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1349 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1350 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 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1354 from FN_APP LEFT JOIN FN_AUDIT_LOG
1355 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1356 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1357 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1358 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1360 on A.app_id = B.Affected_record_id
1361 order by AUDIT_DATE DESC;
1365 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1366 <sql-query name="getUserAppsOrderByLastUsed">
1367 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1369 select * from (select
1370 distinct -- multiple roles yield multiple rows
1371 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1372 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1373 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1374 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1375 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1378 -- Portal assigns role 999 to app administrator
1379 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1380 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1381 where a.ENABLED = 'Y'
1383 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1385 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1387 select distinct -- multiple roles yield multiple rows
1388 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1389 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1390 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1391 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1392 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1394 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1395 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
1399 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1400 from FN_APP LEFT JOIN FN_AUDIT_LOG
1401 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1402 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1403 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1404 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1406 on A.app_id = B.Affected_record_id
1407 order by AUDIT_DATE DESC;
1414 <!--Gets personalized list of enabled and accessible Super Admin apps by
1416 <sql-query name="getAdminAppsOrderByMostUsed">
1417 <return alias="AdminAppsOrderByMostUsed" 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.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1439 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1441 select distinct -- multiple roles yield multiple rows
1442 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1443 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1444 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1445 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1446 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1448 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1449 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
1452 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1453 from FN_APP LEFT JOIN FN_AUDIT_LOG
1454 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1455 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1456 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1457 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1459 on A.app_id = B.Affected_record_id
1460 order by total_visits DESC;
1465 <!--Gets personalized list of enabled and accessible NON Super Admin User
1466 apps by Most Used -->
1467 <sql-query name="getUserAppsOrderByMostUsed">
1468 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1472 distinct -- multiple roles yield multiple rows
1473 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1474 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1475 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1476 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1477 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1480 -- Portal assigns role 999 to app administrator
1481 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1482 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1483 where a.ENABLED = 'Y'
1485 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1487 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1489 select distinct -- multiple roles yield multiple rows
1490 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1491 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1492 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1493 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1494 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1496 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1497 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
1500 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1501 from FN_APP LEFT JOIN FN_AUDIT_LOG
1502 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1503 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1504 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1505 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1507 on A.app_id = B.Affected_record_id
1508 order by total_visits DESC;
1512 <!--Gets personalized list of enabled and accessible Super Admin apps by
1514 <sql-query name="getAdminAppsOrderByManual">
1515 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1519 distinct -- multiple roles yield multiple rows
1520 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1521 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1522 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1523 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1524 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1527 -- Portal assigns role 999 to app administrator
1528 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1529 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1530 where a.ENABLED = 'Y'
1532 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1534 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1536 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1538 select distinct -- multiple roles yield multiple rows
1539 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1540 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1541 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1542 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1543 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1545 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1546 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
1549 (select m.app_id, m.sort_order
1550 from ep_pers_user_app_man_sort m
1551 where USER_ID = :userId
1553 on A.APP_ID = B.app_id
1554 order by sort_order ASC
1560 <!--Gets personalized list of enabled and accessible NON Super admin User
1562 <sql-query name="getUserAppsOrderByManual">
1563 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1565 select * from (select
1566 distinct -- multiple roles yield multiple rows
1567 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1568 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1569 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1570 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1571 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1574 -- Portal assigns role 999 to app administrator
1575 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1576 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1577 where a.ENABLED = 'Y'
1579 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1581 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1583 select distinct -- multiple roles yield multiple rows
1584 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1585 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1586 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1587 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1588 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1590 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1591 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
1594 (select m.app_id, m.sort_order
1595 from ep_pers_user_app_man_sort m
1596 where USER_ID = :userId
1598 on A.APP_ID = B.app_id
1599 order by sort_order ASC
1604 <!-- Gets regular user's list of enabled apps including accessible and select
1606 <sql-query name="getUserAppCatalog">
1607 <return alias="userAppCatalog"
1608 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1609 <!-- This query requires one parameter: userId (number) -->
1612 distinct -- multiple roles yield multiple rows
1613 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1614 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1615 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1616 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1617 IF(a.open = 'Y', TRUE, FALSE) as open,
1618 -- ACCESS(-ible) means user has a defined role OR the application is open
1620 -- regular app and user has a role
1621 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1623 when a.OPEN = 'Y' then TRUE
1626 -- SELECT(-ed) indicates user personalization
1628 -- regular app, user has a role, no personalization
1629 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1630 -- open app and has personalization
1631 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1634 -- PENDING indicates user checked a box
1636 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1640 -- Portal assigns role 999 to app administrator
1641 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1642 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1643 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'
1644 where a.ENABLED = 'Y'
1646 -- Show accessible apps first, then the rest; sort by name within each set.
1647 order by access desc, app_name asc
1652 <sql-query name="getMyloginAppDetails">
1653 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1655 SELECT * FROM fn_app where ml_app_name =:appName
1660 <sql-query name="getAppDetailsByAppName">
1661 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1663 SELECT * FROM fn_app where app_name =:appName
1668 <sql-query name="getMyAppDetailsByUebKey">
1669 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1671 SELECT * FROM fn_app where ueb_key =:appKey
1677 <sql-query name="deleteAccountEndpoint">
1679 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1684 <sql-query name="deleteAccountEndpointRecord">
1686 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1691 <sql-query name="deleteEPEndpoint">
1693 DELETE FROM ep_endpoints WHERE id =:epId
1698 <sql-query name="deleteBasicAuthAccount">
1700 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1705 <sql-query name="getAppRoles">
1706 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1708 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1713 <sql-query name="getPartnerAppRolesList">
1714 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1716 SELECT * FROM fn_role where app_id =:appId
1721 <sql-query name="getPortalAppRolesList">
1722 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1724 SELECT * FROM fn_role where app_id is null
1729 <sql-query name="getPortalAppRoleByRoleId">
1730 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1732 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1737 <sql-query name="getPartnerAppRoleByRoleId">
1738 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1740 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1745 <sql-query name="getPartnerAppRoleById">
1746 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1748 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1753 <sql-query name="getPortalAppRoles">
1754 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1756 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1761 <sql-query name="deleteMicroserviceParameter">
1763 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1768 <sql-query name="deleteMicroserviceParameterById">
1770 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1775 <sql-query name="deleteWidgetCatalogParameter">
1777 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1782 <sql-query name="deleteMicroservice">
1784 DELETE FROM ep_microservice WHERE id =:serviceId
1789 <sql-query name="epUserAppId">
1790 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1792 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1797 <sql-query name="userAppsSortPreferenceQuery">
1798 <return alias="appsSortPreferenceQuery"
1799 class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1801 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1806 <sql-query name="userAppsManualSortPrfQuery">
1807 <return alias="AppsManualSortPrfQuery"
1808 class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1810 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1815 <sql-query name="userWidgetManualSortPrfQuery">
1816 <return alias="widgetManualSortPrfQuery"
1817 class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1819 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1823 <sql-query name="appRoles">
1824 <return alias="rolesForApp"
1825 class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1827 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1832 <sql-query name="userAppRolesRequestList">
1833 <return alias="appRolesRequestList"
1834 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1836 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'
1841 <sql-query name="userAppRolesRequestDetailList">
1842 <return alias="appRolesRequestDetailList"
1843 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1846 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1851 <!-- Gets list of enabled apps including accessible and select statuses -->
1852 <sql-query name="getAdminAppCatalog">
1853 <return alias="adminAppCatalog"
1854 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1855 <!-- This query requires one parameter: userId (number) -->
1858 distinct -- multiple roles yield multiple rows
1859 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1860 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1861 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1862 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1863 IF(a.open = 'Y', TRUE, FALSE) as open,
1864 -- ACCESS(-ible) means user has a defined role OR the application is open
1866 -- regular app and user has a role
1867 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1869 when a.OPEN = 'Y' then TRUE
1872 -- SELECT(-ed) indicates user personalization
1874 -- regular app, user has a role, no personalization
1875 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1876 -- regular app, user has role, admin forced a personalization
1877 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1878 -- regular app, user has no role, admin forced a personalization
1879 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1880 -- open app and has personalization
1881 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1884 -- PENDING indicates user checked a box
1886 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1890 -- Portal assigns role 999 to app administrator
1891 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1892 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1893 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'
1894 where a.ENABLED = 'Y'
1895 -- Show accessible apps first, then the rest; sort by name within each set.
1896 order by access desc, app_name asc
1901 <sql-query name="userAppCatalogRoles">
1902 <return alias="epUserAppCatalogRoles"
1903 class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1905 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
1906 from ep_user_roles_request A left join ep_user_roles_request_det B
1907 on a.req_id = b.req_id
1908 where A.user_id=:userid
1909 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1910 and A.request_status ='P'
1915 <sql-query name="getUserApproles">
1916 <return alias="businessCardUserApplicationRoles"
1917 class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1920 select fr.role_name, fa.app_name
1922 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1923 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)
1929 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1930 <sql-query name="deleteNotificationsFromEpNotificationTable">
1932 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1937 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1938 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1940 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1944 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1945 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1947 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1951 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1952 <sql-query name="getAppRoleFunctionList">
1953 <return alias="appRoleFunctionList"
1954 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1957 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1958 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1964 <!-- Gets list of all role functions -->
1965 <sql-query name="getAllRoleFunctions">
1966 <return alias="allRoleFunctions"
1967 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1970 SELECT * from ep_app_function where app_id =:appId
1976 <!-- Get ep_app_function records provided by single parameter -->
1977 <sql-query name="getRoleFunction">
1978 <return alias="RoleFunction"
1979 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1982 SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1988 <!-- Gets the current user app roles records and requires two parameters -->
1989 <sql-query name="getUserAppCurrentRoles">
1990 <return alias="userAppCurrentRoles"
1991 class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
1994 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'
1999 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
2000 <sql-query name="getUserAppExistingRoles">
2001 <return alias="userAppExistingRoles"
2002 class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
2004 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
2005 left outer join fn_role fr on fu.role_id = fr.role_id
2006 where fu.user_id =:userId and fu.app_id =:appId
2011 <!-- Gets all functions of portal app -->
2012 <sql-query name="getAllFunctions">
2013 <return alias="allFunctions"
2014 class="org.onap.portalsdk.core.domain.RoleFunction" />
2016 select * from fn_function
2021 <!-- Gets the current user app roles records and requires two parameters -->
2022 <sql-query name="getPartnerAppFunctions">
2023 <return alias="partnerAppFunctions"
2024 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2026 select * from ep_app_function where app_id =:appId
2031 <!-- Gets the all role functions and requires one parameters -->
2032 <sql-query name="uploadAllRoleFunctions">
2033 <return alias="allRoleFunctions"
2034 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2036 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
2041 <!-- Gets the all application role functions from ep_app_function table and requires one parameters -->
2042 <sql-query name="uploadPartnerRoleFunctions">
2043 <return alias="partnerRoleFunctions"
2044 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2046 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
2051 <sql-query name="getRoletoUpdateInExternalAuthSystem">
2052 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2054 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2059 <!-- Gets all active records from fn_role that is associated with this app-->
2060 <sql-query name="getActiveRolesOfApplication">
2061 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2063 select * from fn_role where active_yn = 'Y' and app_id=:appId
2068 <sql-query name="getBulkUserRoles">
2069 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2071 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2072 left outer join fn_role fr on fr.role_id = fur.role_id
2073 left outer join fn_app fa on fa.app_id = fur.app_id
2074 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2079 <sql-query name="getBulkUsersForSingleRole">
2080 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2082 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2083 left outer join fn_role fr on fr.role_id = fur.role_id
2084 left outer join fn_app fa on fa.app_id = fur.app_id
2085 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2090 <sql-query name="getCentralizedApps">
2091 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2093 select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2098 <sql-query name="getUserRoles">
2099 <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2102 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
2106 <sql-query name="getRoleFunctionsOfUser">
2108 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2109 where fu.role_id = ep.role_id
2110 and fu.app_id = ep.app_id
2111 and fu.user_id =:userId and ep.app_id = 1
2112 and ea.function_cd = ep.function_cd
2114 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2116 and app_r_f.function_cd = a_f.function_cd
2119 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
2120 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2125 <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
2127 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2128 where fu.role_id = ep.role_id
2129 and fu.app_id = ep.app_id
2130 and fu.user_id =:userId
2131 and ea.function_cd = ep.function_cd
2134 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
2135 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2140 <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
2141 <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2143 SELECT app.APP_ID, app.APP_NAME, app.APP_TYPE FROM FN_APP app
2144 inner join FN_USER_ROLE userrole ON userrole.APP_ID=app.APP_ID
2145 where userrole.USER_ID = :userId AND userrole.ROLE_ID=999 AND (app.ENABLED = 'Y' OR app.APP_ID=1)
2147 SELECT DISTINCT app.APP_ID, app.app_name, app.APP_TYPE FROM fn_user user
2148 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2149 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2150 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2151 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%')
2152 WHERE user.user_id = :userId ;
2156 <sql-query name="getApplicationsofTheUserContainsApprover">
2157 <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2159 SELECT DISTINCT app.APP_ID, app.APP_TYPE, app.app_name FROM fn_user user
2160 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2161 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2162 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2163 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%')
2164 WHERE user.user_id = :userId ;
2170 <sql-query name="getUserRolesForLeftMenu">
2171 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2174 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
2175 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2176 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2177 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2178 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)
2183 <sql-query name="getRolesForLeftMenu">
2184 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2187 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
2188 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2189 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2190 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2191 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)
2196 <!-- Gets all functions for an application along with global functions and requires single parameter -->
2197 <sql-query name="getMenuFunctions">
2199 select f.function_cd from ep_app_function f
2200 where f.app_id =:appId
2202 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2203 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2208 <sql-query name="getRequestIdsForApp">
2209 <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2211 select * from ep_user_roles_request where app_id =:app_id
2216 <sql-query name="ApplicationUserRoles">
2217 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2219 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,
2220 fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2221 from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2222 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
2224 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,
2225 fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2226 from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2227 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'
2228 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';
2232 <sql-query name="getCurrentAppRoleFunctions">
2233 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2235 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
2236 and ef.function_cd =:functionCd and epr.app_id =:appId
2241 <sql-query name="deleteUserWidgetPlacement">
2243 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2248 <sql-query name="getCentralizedAppsOfUser">
2249 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2252 select distinct fa.app_id, fa.app_name
2253 from fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2254 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2255 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)
2261 <query name="getEPUserByOrgUserId">
2263 FROM EPUser WHERE orgUserId = :org_user_id
2267 <query name="getEPUserByLoginId">
2269 FROM EPUser WHERE loginId = :login_id
2273 <query name="getEPUserByLoginIdLoginPwd">
2275 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2279 <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2280 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2282 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
2283 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2284 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
2289 <sql-query name="getGlobalRolesOfPortal">
2290 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2292 select * from fn_role where role_name like 'global_%' and app_id is null and active_yn='Y'
2297 <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2298 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2300 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2305 <sql-query name="getAppFunctionOnCodeAndAppId">
2306 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2308 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2313 <sql-query name="getGlobalRoleForRequestedApp">
2314 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2316 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
2317 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2318 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
2323 <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2324 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2326 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
2327 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2328 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
2333 <sql-query name="updateMenuFunctionalAndRoles">
2335 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
2340 <!-- Remove any favorites associated with a menu item that is associated with this app -->
2341 <sql-query name="removeAppFromMenuFavorites">
2343 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
2348 <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2349 <sql-query name="removeAppFromMenuFunctionalRoles">
2351 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2356 <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2357 <sql-query name="removeAppFromEpAppRoleFunction">
2359 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2364 <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2365 <sql-query name="removeAppFromEpAppFunction">
2367 DELETE FROM ep_app_function WHERE app_id=:app_id
2372 <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2373 <sql-query name="removeAppFromFnUserRole">
2375 DELETE FROM fn_user_role WHERE app_id=:app_id
2380 <!-- Remove any widgets that is associated with this app-->
2381 <sql-query name="removeAppFromEpWidgetCatalogRole">
2383 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2388 <!-- Remove any notifications that is associated with this app-->
2389 <sql-query name="removeAppFromEpRoleNotification">
2391 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2392 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2397 <!-- Remove all records from fn_role that is associated with this app-->
2398 <sql-query name="removeAppFromFnRole">
2400 DELETE FROM fn_role where app_id=:app_id
2405 <!-- Remove app contact us entries that is associated with this app-->
2406 <sql-query name="removeAppFromAppContactUs">
2408 DELETE FROM fn_app_contact_us where app_id=:app_id
2413 <!-- Remove app personalization entries that is associated with this app-->
2414 <sql-query name="removeAppFromEpPersUserAppSel">
2416 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2421 <!-- Remove app personalization sorting entries that is associated with this app-->
2422 <sql-query name="removeAppFromEpPersUserAppManSort">
2424 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2429 <!-- Remove rows from user role request table entries that is associated with this app-->
2430 <sql-query name="removeAppFromEpUserRolesRequest">
2432 DELETE FROM ep_user_roles_request where app_id=:app_id
2437 <!-- Remove rows from web analytics table entries that is associated with this app-->
2438 <sql-query name="removeAppFromEpWebAnalytics">
2440 DELETE FROM ep_web_analytics_source where app_id=:app_id
2445 <!-- Remove row from fn app table -->
2446 <sql-query name="removeAppFromFnApp">
2448 DELETE FROM fn_app where app_id=:app_id
2454 <query name="getBasicauthAccount">
2455 select id,password from BasicAuthCredentials
2458 <query name="getMicroserviceInfo">
2459 select id,password from MicroserviceData
2462 <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2463 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2465 select * from fn_user_role where user_id= :userId
2466 and role_id= :roleId
2472 <sql-query name="userAppGlobalRoles">
2473 <return alias="userAppGlobalRoles"
2474 class="org.onap.portalapp.portal.domain.EPRole" />
2476 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2477 from fn_user_role a, fn_role fr, fn_user fu
2478 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
2483 <sql-query name="getAllCentralizedAppsRoles">
2484 <return alias="allCentralizedAppsRoles"
2485 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2487 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
2488 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 ;
2492 <sql-query name="getUserCentralizedAppRoles">
2493 <return alias="userCentralizedAppRoles"
2494 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2496 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
2497 where fa.app_id = fur.app_id
2498 and fr.role_id = fur.role_id
2499 and fu.user_id = fur.user_id
2500 and fu.ORG_USER_ID = :orgUserId
2501 and fa.auth_namespace is not null
2502 and fr.active_yn = 'Y'
2506 <sql-query name="getAprroverRoleFunctionsOfUser">
2508 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2509 where fu.role_id = ep.role_id
2510 and fu.app_id = ep.app_id
2511 and fu.user_id =:userId
2512 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2515 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
2516 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2520 <sql-query name="getUserApproverRoles">
2522 select distinct fr.role_name from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_role fr
2523 where fu.role_id = ep.role_id
2524 and fu.app_id = ep.app_id
2525 and fu.user_id = :userId
2526 and fu.role_id = fr.role_id and fr.active_yn='Y'
2527 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2530 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
2531 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2535 <sql-query name="getAdminAppsForTheUser">
2537 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)
2544 <sql-query name="getUserRoleOnUserIdAndAppId">
2545 <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
2548 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'
2553 <sql-query name="getAllAdminAppsofTheUser">
2555 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
2559 <sql-query name="getAllAppsFunctionsOfUser">
2561 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_app fa , fn_role fr
2562 where fu.role_id = ep.role_id
2563 and fu.app_id = ep.app_id
2564 and fu.user_id =:userId
2565 and ea.function_cd = ep.function_cd
2566 and ((fu.app_id = fa.app_id and fa.enabled = 'Y' ) or (fa.app_id = 1))
2567 and fr.role_id = fu.role_id and fr.active_yn='Y'
2569 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2571 and app_r_f.function_cd = a_f.function_cd
2574 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
2575 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2579 <sql-query name="updateFnUser">
2581 UPDATE fn_user fu SET fu.language_id=:language_id WHERE fu.login_id=:login_id
2584 </hibernate-mapping>