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="languageId" column="language_id" />
234 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
236 <key column="user_id" />
237 <one-to-many class="org.onap.portalapp.portal.domain.EPUserApp" />
242 <class name="MicroserviceData" table="ep_microservice">
243 <id name="id" column="id">
244 <generator class="native">
245 <param name="sequence">seq_ep_microservice</param>
249 <property name="name" type="string">
250 <column name="name" not-null="true" default="?"></column>
253 <property name="desc" type="string">
254 <column name="description" not-null="true" default="?"></column>
257 <property name="appId" type="long">
258 <column name="appId" not-null="true"></column>
261 <property name="url" type="string">
262 <column name="endpoint_url" not-null="true" default="?"></column>
265 <property name="securityType" type="string">
266 <column name="security_type" not-null="true" default="?"></column>
270 <property name="username" type="string">
271 <column name="username" not-null="true" default="?"></column>
274 <property name="password" type="string">
275 <column name="password" not-null="true" default="?"></column>
278 <property name="active" type="string">
279 <column name="active" not-null="true" default="Y"></column>
283 <class name="MicroserviceParameter" table="ep_microservice_parameter">
284 <id name="id" column="id">
285 <generator class="native">
286 <param name="sequence">seq_ep_microservice_parameter</param>
290 <property name="serviceId" type="long">
291 <column name="service_id" not-null="true"></column>
294 <property name="para_key" type="string">
295 <column name="para_key" not-null="true" default="?"></column>
298 <property name="para_value" type="string">
299 <column name="para_value" not-null="true" default="?"></column>
303 <!-- UserApp class mapping details -->
304 <class name="EPUserApp" table="fn_user_role">
306 <key-property name="userId" type="long">
307 <column name="user_id" precision="11" scale="0" />
309 <key-many-to-one name="app"
310 class="org.onap.portalapp.portal.domain.EPApp" column="app_id" />
311 <key-many-to-one name="role"
312 class="org.onap.portalapp.portal.domain.EPRole" column="role_id" />
314 <property name="priority">
315 <column name="priority" default="1" precision="4" scale="0" />
319 <!-- User App class mapping details -->
320 <class name="EPApp" table="fn_app">
321 <id name="id" column="app_id">
322 <generator class="native">
323 <param name="sequence">seq_fn_app</param>
326 <property name="name" type="string">
327 <column name="app_name" not-null="true" default="?"></column>
329 <property name="imageUrl" column="app_image_url" />
330 <property name="description" column="app_description" />
331 <property name="notes" column="app_notes" />
332 <property name="url" column="app_url" />
333 <property name="alternateUrl" column="app_alternate_url" />
334 <property name="appRestEndpoint" column="app_rest_endpoint" />
335 <property name="mlAppName" type="string">
336 <column name="ml_app_name" not-null="true" default="?"></column>
338 <property name="mlAppAdminId" type="string">
339 <column name="ml_app_admin_id" not-null="true" default="?"></column>
341 <property name="motsId" column="mots_id" />
342 <property name="appPassword" type="string">
343 <column name="app_password" not-null="true" default="?"></column>
345 <property name="thumbnail" column="thumbnail" />
346 <property name="username" type="string">
347 <column name="app_username" not-null="true" default="?"></column>
349 <property name="open" type="yes_no">
350 <column name="open" not-null="true" default="Y"></column>
352 <property name="enabled" type="yes_no">
353 <column name="enabled" not-null="true" default="N"></column>
355 <property name="uebTopicName" type="string">
356 <column name="ueb_topic_name"></column>
358 <property name="uebKey" type="string">
359 <column name="ueb_key"></column>
361 <property name="uebSecret" type="string">
362 <column name="ueb_secret"></column>
364 <property name="appType" type="integer">
365 <column name="app_type"></column>
367 <property name="centralAuth" type="yes_no">
368 <column name="auth_central"></column>
370 <property name="nameSpace" type="string">
371 <column name="auth_namespace"></column>
373 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
377 <!-- EPUserAppsSortPreference class mapping details -->
378 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
379 <id name="id" column="ID">
380 <generator class="native">
381 <param name="sequence">seq_ep_usrappsrtprf</param>
384 <property name="userId" column="USER_ID" />
385 <property name="sortPref" column="SORT_PREF" />
388 <!-- EPUserAppsManualSortPreference class mapping details -->
389 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
390 <id name="id" column="ID">
391 <generator class="native">
392 <param name="sequence">seq_ep_usrmanappsrtprf</param>
395 <property name="userId" column="user_id"
396 unique-key="uk_1_ep_pers_user_app_man_sort" />
397 <property name="appId" column="app_id"
398 unique-key="uk_1_ep_pers_user_app_man_sort" />
399 <property name="appManualSortOrder" column="sort_order" type="int" />
402 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
403 <id name="id" column="ID">
404 <generator class="native">
405 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
408 <property name="userId" column="user_id" />
409 <property name="widgetId" column="widget_id" />
410 <property name="widgetRow" column="x" />
411 <property name="widgetCol" column="y" />
412 <property name="widgetHeight" column="height" />
413 <property name="widgetWidth" column="width" />
416 <!-- User Role class mapping details -->
417 <class name="EPRole" table="FN_ROLE" >
418 <id name="id" column="role_id">
419 <generator class="native">
420 <param name="sequence">seq_fn_role</param>
424 <property name="name" column="role_name" />
425 <property name="priority" column="priority" />
426 <property name="active" column="active_yn" type="yes_no" />
427 <!-- 2 lines below may be removed! -->
428 <property name="appId" column="APP_ID" />
429 <property name="appRoleId" column="APP_ROLE_ID" />
430 <!-- 2 lines above may be removed -->
432 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
434 <key column="role_id" />
435 <many-to-many column="function_cd"
436 class="org.onap.portalsdk.core.domain.RoleFunction" />
439 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
441 <key column="parent_role_id" />
442 <many-to-many column="child_role_id"
443 class="org.onap.portalapp.portal.domain.EPRole" />
446 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
448 <key column="child_role_id" />
449 <many-to-many column="parent_role_id"
450 class="org.onap.portalapp.portal.domain.EPRole" />
455 <!-- User App class mapping details -->
456 <class name="AppContactUs" table="fn_app_contact_us">
457 <id name="id" column="app_id">
458 <generator class="foreign">
459 <param name="property">app</param>
462 <property name="url" column="url" />
463 <property name="description" column="description" />
464 <property name="contactName" column="contact_name" />
465 <property name="contactEmail" column="contact_email" />
466 <property name="activeYN" column="active_yn" />
468 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
472 <!-- Personalization of user app selections -->
473 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
474 <id name="id" column="id">
475 <generator class="native">
476 <param name="property">seq_fn_pers_user_app_sel</param>
479 <property name="userId" column="user_id" />
480 <property name="appId" column="app_id" />
481 <property name="statusCode" column="status_cd" />
484 <!-- EPAppRoleFunction class mapping details -->
485 <class name="EPAppRoleFunction" table="ep_app_role_function">
486 <id name="id" column="id">
487 <generator class="native">
488 <param name="property">seq_epp_app_role_func</param>
491 <property name="roleId" column="role_id"></property>
492 <property name="appId" column="app_id"></property>
493 <property name="code" column="function_cd"></property>
494 <property name="roleAppId" column="role_app_id"></property>
497 <!-- CentralRoleFunction class mapping details -->
498 <class name="CentralV2RoleFunction" table="ep_app_function">
500 <key-property name="appId"
502 <key-property name="code"
503 column="function_cd" />
505 <property name="name" column="function_name" />
508 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
509 <id name="id" column="id">
510 <generator class="native">
511 <param name="property">seq_ep_pers_user_widget_sel</param>
514 <property name="userId" column="user_id" />
515 <property name="widgetId" column="widget_id" />
516 <property name="statusCode" column="status_cd" />
519 <!-- EPUserAppRolesRequest class mapping details -->
520 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
521 <id name="id" column="req_id">
522 <generator class="native">
523 <param name="sequence">seq_ep_user_role_request</param>
526 <property name="userId" column="user_id" />
527 <property name="appId" column="app_id" />
528 <property name="createdDate" column="created_date" type="timestamp" />
529 <property name="updatedDate" column="updated_date" type="timestamp" />
530 <property name="requestStatus" column="request_status" />
532 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
533 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
534 <key column="req_id" />
536 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
540 <!-- EPUserAppRolesRequestDetail class mapping details -->
541 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
542 <id name="id" column="id">
543 <generator class="native">
544 <param name="sequence">seq_ep_user_role_request_det</param>
547 <property name="reqRoleId" column="requested_role_id" />
548 <property name="reqType" column="request_type" />
549 <many-to-one name="epRequestIdData" fetch="select"
550 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest">
551 <column name="req_id" not-null="true" />
555 <!-- multilanguage -->
556 <query name="queryLanguage">
562 <query name="displayText">
564 from DisplayText where languageId = :language_id
568 <query name="queryLanguageByLanguageId">
570 from Language where languageId = :language_id
574 <!-- show the current user plus related users -->
575 <sql-query name="relatedUsers">
576 <return-scalar column="org_user_id" type="java.lang.String" />
578 select t.org_user_id from (
579 select distinct c.org_user_id org_user_id, c.last_login_date from
580 (select c.org_user_id, c.last_login_date from
582 (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,
584 where a.ROLE_ID = b.role_id
585 and a.APP_ID = b.app_id
586 and a.USER_ID = c.user_id
588 select org_user_id , last_login_date from fn_user where org_user_id=:userId
590 order by c.last_login_date desc limit 10
595 <!-- requires values for named parameters :searchQuery and :userId -->
596 <sql-query name="searchPortal">
597 <return alias="searchResult"
598 class="org.onap.portalapp.portal.ecomp.model.SearchResultItem" />
600 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
602 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
604 upper( :searchQuery ) != ''
605 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
606 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
607 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%'))
609 ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
611 last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
613 (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
615 first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
616 and upper(active_yn) = 'Y'
620 select distinct 'Application' CATEGORY, b1.app_name NAME,
621 if (b1.app_type = 2,'false','true') UUID,
622 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
626 from fn_user_role a, fn_user b, fn_app c
627 where a.USER_ID = b.user_id
628 and upper(b.org_user_id) = upper( :userId )
629 and a.app_id = c.app_id
630 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
631 and upper(c.ENABLED) = 'Y'
632 ) a1 right outer join
633 (select * from fn_app where
634 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
635 and upper(ENABLED) = 'Y') b1
636 on a1.APP_ID = b1.app_id
640 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
641 if (b1.app_type = 2,'false','true') UUID,
642 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
644 (select distinct d.*, e.app_type from fn_user a,
645 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
646 where a.USER_ID = b.user_id
647 and b.role_id = c.role_id
648 and c.menu_id = d.menu_id
649 and upper(a.org_user_id) = upper( :userId )
650 and c.APP_ID = e.app_id
651 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
652 and upper(d.active_yn) = 'Y'
653 and upper(e.enabled) = 'Y'
654 ) a1 right outer join
656 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
657 where active_yn = 'Y'
658 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
659 and a.menu_id = b.menu_id
660 and b.app_id = c.app_id
663 on a1.menu_id = b1.menu_id
666 select distinct CATEGORY, NAME, UUID, TARGET
670 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
673 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
674 where upper(a.org_user_id) = upper( :userId )
675 and a.user_id = b.user_id
676 and b.role_id = c.role_id
677 and c.widget_id = d.widget_id
678 and d.all_user_flag<>'Y'
679 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
683 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
686 from ep_widget_catalog d
687 where d.all_user_flag='Y'
688 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
692 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
695 from fn_user a, fn_user_role b, ep_widget_catalog d
696 where upper(a.org_user_id) = upper( :userId )
697 and a.user_id = b.user_id
699 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
703 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
709 <!-- requires values for named parameters :searchQuery and :userId -->
710 <sql-query name="getNotifications">
711 <return alias="notificationResult"
712 class="org.onap.portalapp.portal.transport.EpNotificationItem" />
714 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
717 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
720 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
723 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
724 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
725 from ep_notification a, ep_role_notification b
726 where a.notification_id = b.notification_id
727 and (end_time is null || SYSDATE() <= end_time )
728 and (start_time is null || SYSDATE() >= start_time)
729 and a.is_for_all_roles = 'N'
732 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
733 from fn_user a, fn_user_role b, fn_role c, fn_app d
734 where COALESCE(c.app_id,1) = d.app_id
735 and a.user_id = b.user_id
736 and a.user_id = :user_id
737 and b.role_id = c.role_id
738 and (d.enabled='Y' or d.app_id=1)
742 a.role_id = b.role_id
745 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
748 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
749 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
750 from ep_notification a, ep_role_notification b
751 where a.notification_id = b.notification_id
752 and (end_time is null || SYSDATE() <= end_time )
753 and (start_time is null || SYSDATE() >= start_time)
754 and a.is_for_all_roles = 'N'
758 a.recv_user_id=:user_id
762 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
763 from ep_notification a
764 where a.notification_id
765 and (end_time is null || SYSDATE() <= end_time )
766 and (start_time is null || SYSDATE() >= start_time)
767 and a.is_for_all_roles = 'Y'
775 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'
777 order by priority desc, created_date desc,start_Time desc
781 (SELECT @rn /*'*/:=/*'*/ 0) t2
786 <sql-query name="getUsersByOrgIdsNotifications">
787 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
789 SELECT * from fn_user where org_user_id in (:OrgIds)
795 <!-- Gets all Admin notification history; accepts no parameters -->
796 <sql-query name="getAdminNotificationHistoryVO">
797 <return alias="adminNotificationHistoryVOResult"
798 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
802 n.notification_ID AS notificationId,
803 n.is_for_online_users AS isForOnlineUsers,
804 n.is_for_all_roles AS isForAllRoles,
805 n.active_YN AS activeYn,
806 n.msg_header AS msgHeader,
807 n.msg_description AS msgDescription,
808 n.msg_source AS msgSource,
809 n.start_Time AS startTime,
810 n.end_time AS endTime,
812 n.creator_ID AS creatorId,
813 n.created_date AS createdDate,
814 n.notification_hyperlink AS notificationHyperlink,
815 u.org_user_id AS loginId
819 fn_user u on u.user_id = n.creator_id
821 n.active_YN='Y' and msg_source='EP'
826 n.notification_ID AS notificationId,
827 n.is_for_online_users AS isForOnlineUsers,
828 n.is_for_all_roles AS isForAllRoles,
829 n.active_YN AS activeYn,
830 n.msg_header AS msgHeader,
831 n.msg_description AS msgDescription,
832 n.msg_source AS msgSource,
833 n.start_Time AS startTime,
834 n.end_time AS endTime,
836 n.creator_ID AS creatorId,
837 n.created_date AS createdDate,
838 n.notification_hyperlink AS notificationHyperlink,
839 u.org_user_id AS loginId
843 fn_user u on u.user_id = n.creator_id
845 ep_role_notification r on r.notification_ID=n.notification_ID
847 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
848 )n order by n.startTime desc
852 <sql-query name="getEpNotificationAppRoles">
853 <return alias="notificationAppRoles"
854 class="org.onap.portalapp.portal.domain.EcompAppRole" />
855 <!-- This query requires no parameters. -->
858 select a.app_id, a.app_name, b.role_id, b.role_name from
859 (select * from fn_app where app_id = 1) a,
860 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
862 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
864 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
871 <!-- Gets all notification history -->
872 <sql-query name="getNotificationHistoryVO">
873 <return alias="notificationHistoryVOResult"
874 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
877 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
878 startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
882 a.notification_ID AS notificationId,
883 is_for_online_users AS isForOnlineUsers,
884 is_for_all_roles AS isForAllRoles,
885 msg_header AS msgHeader,
886 msg_description AS msgDescription,
887 msg_source AS msgSource,
888 start_Time AS startTime,
891 created_date AS createdDate,
892 creator_ID AS creatorId,
893 notification_hyperlink AS notificationHyperlink,
895 active_YN AS activeYn,
896 if (is_viewed is null, 'N', is_viewed)
900 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
901 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
902 creator_ID,notification_hyperlink,active_YN
905 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
906 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,
907 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
910 END AS login_id,b.recv_user_id
911 from ep_notification a, ep_role_notification b,fn_user u
912 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
913 and a.is_for_all_roles = 'N'
915 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
917 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
919 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
921 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
925 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
926 from fn_user a, fn_user_role b, fn_role c, fn_app d
927 where COALESCE(c.app_id,1) = d.app_id
928 and a.user_id = b.user_id
929 and a.user_id = :user_id
930 and b.role_id = c.role_id
931 and (d.enabled='Y' or d.app_id=1)
935 a.role_id = b.role_id
939 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
940 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
941 creator_ID,notification_hyperlink,active_YN
944 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
945 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,
946 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
949 END AS login_id,b.recv_user_id
950 from ep_notification a, ep_role_notification b,fn_user u
951 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
952 and a.is_for_all_roles = 'N'
954 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
956 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
958 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
960 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
965 a.recv_user_id=:user_id
970 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
971 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
972 creator_ID, a.notification_hyperlink,a.active_YN
973 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
974 where a.notification_id
975 and a.is_for_all_roles = 'Y'
977 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
979 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
981 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
983 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
986 ) a left outer join (
987 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
989 on a.notification_id = m.notification_ID
993 order by start_Time desc,end_time desc
995 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
1000 <!-- shows the received recipient to whom the notification is delivered from external system -->
1001 <sql-query name="messageRecipients">
1002 <return-scalar column="org_user_id" type="java.lang.String" />
1004 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
1009 <!-- Gets role details for a specified notification -->
1010 <sql-query name="getNotificationRoles">
1011 <return alias="notificationRolesResult"
1012 class="org.onap.portalapp.portal.transport.EpRoleNotificationItem" />
1016 ep_role_notification
1018 notification_Id = :notificationId
1022 <!-- Gets all applications, possibly with contact information -->
1023 <sql-query name="getAppsAndContacts">
1024 <return alias="appContactUs"
1025 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1026 <!-- This query requires no parameters. -->
1029 a.app_id as appId, a.app_name as appName,
1030 c.contact_name as contactName,
1031 c.contact_email as contactEmail, c.url, c.description,
1032 c.active_yn as activeYN
1037 on a.app_id = c.app_id
1039 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1044 <!-- Gets all rows from the app-contact-us table, extended with app information -->
1045 <sql-query name="getAppContactUsItems">
1046 <return alias="appContactUs"
1047 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1048 <!-- This query requires no parameters. -->
1051 c.app_id as appId, c.contact_name as contactName,
1052 c.contact_email as contactEmail, c.url, c.description,
1053 c.active_yn as activeYN, a.app_name as appName
1058 on a.app_id = c.app_id
1060 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1065 <!-- Gets one row for each app with the category and catenated functions. -->
1066 <sql-query name="getAppCategoryFunctions">
1067 <return alias="appCategoryFunctions"
1068 class="org.onap.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1069 <!-- This query requires no parameters. -->
1071 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1072 r.category as category, r.app_id as appId, r.app_name as application,
1073 group_concat(function_text separator ', ') as functions
1076 app_id, function_text, app_name,
1077 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1080 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1081 k.text parent_menu, k.parent_menu_id parent_menu_id
1084 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1086 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1088 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1090 fn_menu_functional k
1091 WHERE j.parent_menu_id = k.menu_id
1093 fn_menu_functional a
1094 WHERE fn.parent_menu_id = a.menu_id
1096 (SELECT @rn /*'*/:=/*'*/ 0) t2
1097 group by r.category, r.app_id, r.app_name
1098 order by category, app_name
1103 <!-- Gets one row for each function-application-role combination. -->
1104 <sql-query name="getAppAccessFunctionRole">
1105 <return alias="appAccessFunctionRole"
1106 class="org.onap.portalapp.portal.domain.GetAccessResult" />
1107 <!-- This query requires one parameters. -->
1109 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1110 (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,
1111 o.requested_role_id,o.request_type
1113 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1116 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1119 where COALESCE(c.app_id,1) = d.app_id
1120 and (d.enabled='Y' or d.app_id=1)
1121 and c.active_yn = 'Y'
1124 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1126 A.MENU_ID = B.MENU_ID
1129 on a.role_id = b.role_id) t left outer join
1130 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1131 where er.req_id=ed.req_id
1132 and upper(ed.request_type)='P'
1133 and er.user_id =:userId) o
1134 on t.app_id=o.app_id
1135 and t.role_id = o.requested_role_id
1136 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1141 <query name="getCommonWidgetItem">
1142 from CommonWidget where category = :cat order by
1146 <sql-query name="getGuestLastLogin">
1147 <return-scalar column="audit_date" type="java.util.Date" />
1149 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1153 <sql-query name="getActiveUsers">
1154 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1156 SELECT * FROM FN_USER WHERE active_yn='Y'
1160 <sql-query name="getAppsAdmins">
1161 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1163 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)
1167 <sql-query name="getPortalAdmins">
1168 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1170 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
1174 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1175 <sql-query name="getPersUserApps">
1176 <return alias="persUserApps" class="org.onap.portalapp.portal.domain.EPApp" />
1177 <!-- This query requires one parameter: userId (number) -->
1180 distinct -- multiple roles yield multiple rows
1181 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1182 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1183 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1184 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1185 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
1187 -- Portal assigns role 999 to app administrator
1188 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1189 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1190 where a.ENABLED = 'Y'
1192 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1194 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1201 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1203 <sql-query name="getPersAdminApps">
1204 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1205 <!-- This query requires one parameter: userId (number) -->
1208 distinct -- multiple roles yield multiple rows
1209 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1210 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1211 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1212 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1213 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1216 -- Portal assigns role 999 to app administrator
1217 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1218 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1219 where a.ENABLED = 'Y'
1221 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1223 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1225 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1232 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1233 <sql-query name="getPersAdminAppsOrderByName">
1234 <return alias="AdminAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1235 <!-- This query requires one parameter: userId (number) -->
1238 distinct -- multiple roles yield multiple rows
1239 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1240 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1241 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1242 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1243 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1246 -- Portal assigns role 999 to app administrator
1247 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1248 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1249 where a.ENABLED = 'Y'
1251 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1253 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1255 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1258 select distinct -- multiple roles yield multiple rows
1259 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1260 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1261 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1262 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1263 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1265 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1266 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
1273 <!--Gets personalized list of enabled and accessible User apps by name -->
1274 <sql-query name="getPersUserAppsOrderByName">
1275 <return alias="UserAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1276 <!-- This query requires one parameter: userId (number) -->
1279 distinct -- multiple roles yield multiple rows
1280 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1281 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1282 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1283 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1284 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1287 -- Portal assigns role 999 to app administrator
1288 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1289 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1290 where a.ENABLED = 'Y'
1292 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1294 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1297 select distinct -- multiple roles yield multiple rows
1298 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1299 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1300 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1301 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1302 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1304 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1305 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
1313 <!--Gets personalized list of enabled and accessible Super Admin apps by
1315 <sql-query name="getAdminAppsOrderByLastUsed">
1316 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1321 distinct -- multiple roles yield multiple rows
1322 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1323 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1324 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1325 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1326 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1329 -- Portal assigns role 999 to app administrator
1330 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1331 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1332 where a.ENABLED = 'Y'
1334 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1336 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1338 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1341 select distinct -- multiple roles yield multiple rows
1342 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1343 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1344 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1345 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1346 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1348 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1349 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
1352 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1353 from FN_APP LEFT JOIN FN_AUDIT_LOG
1354 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1355 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1356 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1357 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1359 on A.app_id = B.Affected_record_id
1360 order by AUDIT_DATE DESC;
1364 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1365 <sql-query name="getUserAppsOrderByLastUsed">
1366 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1368 select * from (select
1369 distinct -- multiple roles yield multiple rows
1370 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1371 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1372 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1373 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1374 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1377 -- Portal assigns role 999 to app administrator
1378 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1379 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1380 where a.ENABLED = 'Y'
1382 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1384 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1386 select distinct -- multiple roles yield multiple rows
1387 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1388 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1389 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1390 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1391 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1393 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1394 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
1398 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1399 from FN_APP LEFT JOIN FN_AUDIT_LOG
1400 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1401 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1402 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1403 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1405 on A.app_id = B.Affected_record_id
1406 order by AUDIT_DATE DESC;
1413 <!--Gets personalized list of enabled and accessible Super Admin apps by
1415 <sql-query name="getAdminAppsOrderByMostUsed">
1416 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1421 distinct -- multiple roles yield multiple rows
1422 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1423 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1424 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1425 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1426 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1429 -- Portal assigns role 999 to app administrator
1430 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1431 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1432 where a.ENABLED = 'Y'
1434 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1436 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1438 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1440 select distinct -- multiple roles yield multiple rows
1441 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1442 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1443 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1444 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1445 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1447 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1448 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
1451 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1452 from FN_APP LEFT JOIN FN_AUDIT_LOG
1453 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1454 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1455 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1456 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1458 on A.app_id = B.Affected_record_id
1459 order by total_visits DESC;
1464 <!--Gets personalized list of enabled and accessible NON Super Admin User
1465 apps by Most Used -->
1466 <sql-query name="getUserAppsOrderByMostUsed">
1467 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1471 distinct -- multiple roles yield multiple rows
1472 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1473 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1474 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1475 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1476 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1479 -- Portal assigns role 999 to app administrator
1480 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1481 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1482 where a.ENABLED = 'Y'
1484 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1486 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1488 select distinct -- multiple roles yield multiple rows
1489 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1490 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1491 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1492 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1493 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1495 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1496 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1499 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1500 from FN_APP LEFT JOIN FN_AUDIT_LOG
1501 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1502 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1503 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1504 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1506 on A.app_id = B.Affected_record_id
1507 order by total_visits DESC;
1511 <!--Gets personalized list of enabled and accessible Super Admin apps by
1513 <sql-query name="getAdminAppsOrderByManual">
1514 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1518 distinct -- multiple roles yield multiple rows
1519 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1520 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1521 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1522 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1523 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1526 -- Portal assigns role 999 to app administrator
1527 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1528 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1529 where a.ENABLED = 'Y'
1531 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1533 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1535 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1537 select distinct -- multiple roles yield multiple rows
1538 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1539 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1540 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1541 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1542 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1544 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1545 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
1548 (select m.app_id, m.sort_order
1549 from ep_pers_user_app_man_sort m
1550 where USER_ID = :userId
1552 on A.APP_ID = B.app_id
1553 order by sort_order ASC
1559 <!--Gets personalized list of enabled and accessible NON Super admin User
1561 <sql-query name="getUserAppsOrderByManual">
1562 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1564 select * from (select
1565 distinct -- multiple roles yield multiple rows
1566 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1567 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1568 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1569 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1570 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1573 -- Portal assigns role 999 to app administrator
1574 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1575 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1576 where a.ENABLED = 'Y'
1578 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1580 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1582 select distinct -- multiple roles yield multiple rows
1583 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1584 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1585 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1586 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1587 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1589 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1590 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
1593 (select m.app_id, m.sort_order
1594 from ep_pers_user_app_man_sort m
1595 where USER_ID = :userId
1597 on A.APP_ID = B.app_id
1598 order by sort_order ASC
1603 <!-- Gets regular user's list of enabled apps including accessible and select
1605 <sql-query name="getUserAppCatalog">
1606 <return alias="userAppCatalog"
1607 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1608 <!-- This query requires one parameter: userId (number) -->
1611 distinct -- multiple roles yield multiple rows
1612 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1613 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1614 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1615 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1616 IF(a.open = 'Y', TRUE, FALSE) as open,
1617 -- ACCESS(-ible) means user has a defined role OR the application is open
1619 -- regular app and user has a role
1620 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1622 when a.OPEN = 'Y' then TRUE
1625 -- SELECT(-ed) indicates user personalization
1627 -- regular app, user has a role, no personalization
1628 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1629 -- open app and has personalization
1630 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1633 -- PENDING indicates user checked a box
1635 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1639 -- Portal assigns role 999 to app administrator
1640 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1641 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1642 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'
1643 where a.ENABLED = 'Y'
1645 -- Show accessible apps first, then the rest; sort by name within each set.
1646 order by access desc, app_name asc
1651 <sql-query name="getMyloginAppDetails">
1652 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1654 SELECT * FROM fn_app where ml_app_name =:appName
1659 <sql-query name="getAppDetailsByAppName">
1660 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1662 SELECT * FROM fn_app where app_name =:appName
1667 <sql-query name="getMyAppDetailsByUebKey">
1668 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1670 SELECT * FROM fn_app where ueb_key =:appKey
1676 <sql-query name="deleteAccountEndpoint">
1678 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1683 <sql-query name="deleteAccountEndpointRecord">
1685 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1690 <sql-query name="deleteEPEndpoint">
1692 DELETE FROM ep_endpoints WHERE id =:epId
1697 <sql-query name="deleteBasicAuthAccount">
1699 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1704 <sql-query name="getAppRoles">
1705 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1707 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1712 <sql-query name="getPartnerAppRolesList">
1713 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1715 SELECT * FROM fn_role where app_id =:appId
1720 <sql-query name="getPortalAppRolesList">
1721 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1723 SELECT * FROM fn_role where app_id is null
1728 <sql-query name="getPortalAppRoleByRoleId">
1729 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1731 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1736 <sql-query name="getPartnerAppRoleByRoleId">
1737 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1739 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1744 <sql-query name="getPartnerAppRoleById">
1745 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1747 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1752 <sql-query name="getPortalAppRoles">
1753 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1755 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1760 <sql-query name="deleteMicroserviceParameter">
1762 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1767 <sql-query name="deleteMicroserviceParameterById">
1769 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1774 <sql-query name="deleteWidgetCatalogParameter">
1776 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1781 <sql-query name="deleteMicroservice">
1783 DELETE FROM ep_microservice WHERE id =:serviceId
1788 <sql-query name="epUserAppId">
1789 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1791 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1796 <sql-query name="userAppsSortPreferenceQuery">
1797 <return alias="appsSortPreferenceQuery"
1798 class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1800 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1805 <sql-query name="userAppsManualSortPrfQuery">
1806 <return alias="AppsManualSortPrfQuery"
1807 class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1809 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1814 <sql-query name="userWidgetManualSortPrfQuery">
1815 <return alias="widgetManualSortPrfQuery"
1816 class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1818 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1822 <sql-query name="appRoles">
1823 <return alias="rolesForApp"
1824 class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1826 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1831 <sql-query name="userAppRolesRequestList">
1832 <return alias="appRolesRequestList"
1833 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1835 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'
1840 <sql-query name="userAppRolesRequestDetailList">
1841 <return alias="appRolesRequestDetailList"
1842 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1845 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1850 <!-- Gets list of enabled apps including accessible and select statuses -->
1851 <sql-query name="getAdminAppCatalog">
1852 <return alias="adminAppCatalog"
1853 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1854 <!-- This query requires one parameter: userId (number) -->
1857 distinct -- multiple roles yield multiple rows
1858 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1859 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1860 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1861 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1862 IF(a.open = 'Y', TRUE, FALSE) as open,
1863 -- ACCESS(-ible) means user has a defined role OR the application is open
1865 -- regular app and user has a role
1866 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1868 when a.OPEN = 'Y' then TRUE
1871 -- SELECT(-ed) indicates user personalization
1873 -- regular app, user has a role, no personalization
1874 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1875 -- regular app, user has role, admin forced a personalization
1876 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1877 -- regular app, user has no role, admin forced a personalization
1878 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1879 -- open app and has personalization
1880 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1883 -- PENDING indicates user checked a box
1885 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1889 -- Portal assigns role 999 to app administrator
1890 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1891 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1892 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'
1893 where a.ENABLED = 'Y'
1894 -- Show accessible apps first, then the rest; sort by name within each set.
1895 order by access desc, app_name asc
1900 <sql-query name="userAppCatalogRoles">
1901 <return alias="epUserAppCatalogRoles"
1902 class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1904 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
1905 from ep_user_roles_request A left join ep_user_roles_request_det B
1906 on a.req_id = b.req_id
1907 where A.user_id=:userid
1908 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1909 and A.request_status ='P'
1914 <sql-query name="getUserApproles">
1915 <return alias="businessCardUserApplicationRoles"
1916 class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1919 select fr.role_name, fa.app_name
1921 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1922 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)
1928 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1929 <sql-query name="deleteNotificationsFromEpNotificationTable">
1931 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1936 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1937 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1939 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1943 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1944 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1946 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1950 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1951 <sql-query name="getAppRoleFunctionList">
1952 <return alias="appRoleFunctionList"
1953 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1956 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1957 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1963 <!-- Gets list of all role functions -->
1964 <sql-query name="getAllRoleFunctions">
1965 <return alias="allRoleFunctions"
1966 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1969 SELECT * from ep_app_function where app_id =:appId
1975 <!-- Get ep_app_function records provided by single parameter -->
1976 <sql-query name="getRoleFunction">
1977 <return alias="RoleFunction"
1978 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1981 SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1987 <!-- Gets the current user app roles records and requires two parameters -->
1988 <sql-query name="getUserAppCurrentRoles">
1989 <return alias="userAppCurrentRoles"
1990 class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
1993 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'
1998 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
1999 <sql-query name="getUserAppExistingRoles">
2000 <return alias="userAppExistingRoles"
2001 class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
2003 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
2004 left outer join fn_role fr on fu.role_id = fr.role_id
2005 where fu.user_id =:userId and fu.app_id =:appId
2010 <!-- Gets all functions of portal app -->
2011 <sql-query name="getAllFunctions">
2012 <return alias="allFunctions"
2013 class="org.onap.portalsdk.core.domain.RoleFunction" />
2015 select * from fn_function
2020 <!-- Gets the current user app roles records and requires two parameters -->
2021 <sql-query name="getPartnerAppFunctions">
2022 <return alias="partnerAppFunctions"
2023 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2025 select * from ep_app_function where app_id =:appId
2030 <!-- Gets the all role functions and requires one parameters -->
2031 <sql-query name="uploadAllRoleFunctions">
2032 <return alias="allRoleFunctions"
2033 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2035 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
2040 <!-- Gets the all application role functions from ep_app_function table and requires one parameters -->
2041 <sql-query name="uploadPartnerRoleFunctions">
2042 <return alias="partnerRoleFunctions"
2043 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2045 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
2050 <sql-query name="getRoletoUpdateInExternalAuthSystem">
2051 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2053 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2058 <!-- Gets all active records from fn_role that is associated with this app-->
2059 <sql-query name="getActiveRolesOfApplication">
2060 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2062 select * from fn_role where active_yn = 'Y' and app_id=:appId
2067 <sql-query name="getBulkUserRoles">
2068 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2070 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2071 left outer join fn_role fr on fr.role_id = fur.role_id
2072 left outer join fn_app fa on fa.app_id = fur.app_id
2073 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2078 <sql-query name="getBulkUsersForSingleRole">
2079 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2081 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2082 left outer join fn_role fr on fr.role_id = fur.role_id
2083 left outer join fn_app fa on fa.app_id = fur.app_id
2084 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2089 <sql-query name="getCentralizedApps">
2090 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2092 select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2097 <sql-query name="getUserRoles">
2098 <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2101 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
2105 <sql-query name="getRoleFunctionsOfUser">
2107 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2108 where fu.role_id = ep.role_id
2109 and fu.app_id = ep.app_id
2110 and fu.user_id =:userId and ep.app_id = 1
2111 and ea.function_cd = ep.function_cd
2113 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2115 and app_r_f.function_cd = a_f.function_cd
2118 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
2119 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2124 <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
2126 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2127 where fu.role_id = ep.role_id
2128 and fu.app_id = ep.app_id
2129 and fu.user_id =:userId
2130 and ea.function_cd = ep.function_cd
2133 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
2134 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2139 <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
2140 <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2142 SELECT app.APP_ID, app.APP_NAME, app.APP_TYPE FROM FN_APP app
2143 inner join FN_USER_ROLE userrole ON userrole.APP_ID=app.APP_ID
2144 where userrole.USER_ID = :userId AND userrole.ROLE_ID=999 AND (app.ENABLED = 'Y' OR app.APP_ID=1)
2146 SELECT DISTINCT app.APP_ID, app.app_name, app.APP_TYPE FROM fn_user user
2147 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2148 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2149 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2150 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%')
2151 WHERE user.user_id = :userId ;
2155 <sql-query name="getApplicationsofTheUserContainsApprover">
2156 <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2158 SELECT DISTINCT app.APP_ID, app.APP_TYPE, app.app_name FROM fn_user user
2159 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2160 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2161 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2162 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%')
2163 WHERE user.user_id = :userId ;
2169 <sql-query name="getUserRolesForLeftMenu">
2170 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2173 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
2174 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2175 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2176 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2177 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)
2182 <sql-query name="getRolesForLeftMenu">
2183 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2186 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
2187 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2188 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2189 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2190 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)
2195 <!-- Gets all functions for an application along with global functions and requires single parameter -->
2196 <sql-query name="getMenuFunctions">
2198 select f.function_cd from ep_app_function f
2199 where f.app_id =:appId
2201 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2202 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2207 <sql-query name="getRequestIdsForApp">
2208 <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2210 select * from ep_user_roles_request where app_id =:app_id
2215 <sql-query name="ApplicationUserRoles">
2216 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2218 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,
2219 fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2220 from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2221 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
2223 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,
2224 fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2225 from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2226 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'
2227 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';
2231 <sql-query name="getCurrentAppRoleFunctions">
2232 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2234 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
2235 and ef.function_cd =:functionCd and epr.app_id =:appId
2240 <sql-query name="deleteUserWidgetPlacement">
2242 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2247 <sql-query name="getCentralizedAppsOfUser">
2248 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2251 select distinct fa.app_id, fa.app_name
2252 from fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2253 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2254 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)
2260 <query name="getEPUserByOrgUserId">
2262 FROM EPUser WHERE orgUserId = :org_user_id
2266 <query name="getEPUserByLoginId">
2268 FROM EPUser WHERE loginId = :login_id
2272 <query name="getEPUserByLoginIdLoginPwd">
2274 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2278 <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2279 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2281 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
2282 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2283 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
2288 <sql-query name="getGlobalRolesOfPortal">
2289 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2291 select * from fn_role where role_name like 'global_%' and app_id is null and active_yn='Y'
2296 <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2297 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2299 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2304 <sql-query name="getAppFunctionOnCodeAndAppId">
2305 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2307 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2312 <sql-query name="getGlobalRoleForRequestedApp">
2313 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2315 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
2316 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2317 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
2322 <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2323 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2325 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
2326 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2327 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
2332 <sql-query name="updateMenuFunctionalAndRoles">
2334 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
2339 <!-- Remove any favorites associated with a menu item that is associated with this app -->
2340 <sql-query name="removeAppFromMenuFavorites">
2342 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
2347 <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2348 <sql-query name="removeAppFromMenuFunctionalRoles">
2350 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2355 <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2356 <sql-query name="removeAppFromEpAppRoleFunction">
2358 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2363 <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2364 <sql-query name="removeAppFromEpAppFunction">
2366 DELETE FROM ep_app_function WHERE app_id=:app_id
2371 <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2372 <sql-query name="removeAppFromFnUserRole">
2374 DELETE FROM fn_user_role WHERE app_id=:app_id
2379 <!-- Remove any widgets that is associated with this app-->
2380 <sql-query name="removeAppFromEpWidgetCatalogRole">
2382 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2387 <!-- Remove any notifications that is associated with this app-->
2388 <sql-query name="removeAppFromEpRoleNotification">
2390 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2391 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2396 <!-- Remove all records from fn_role that is associated with this app-->
2397 <sql-query name="removeAppFromFnRole">
2399 DELETE FROM fn_role where app_id=:app_id
2404 <!-- Remove app contact us entries that is associated with this app-->
2405 <sql-query name="removeAppFromAppContactUs">
2407 DELETE FROM fn_app_contact_us where app_id=:app_id
2412 <!-- Remove app personalization entries that is associated with this app-->
2413 <sql-query name="removeAppFromEpPersUserAppSel">
2415 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2420 <!-- Remove app personalization sorting entries that is associated with this app-->
2421 <sql-query name="removeAppFromEpPersUserAppManSort">
2423 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2428 <!-- Remove rows from user role request table entries that is associated with this app-->
2429 <sql-query name="removeAppFromEpUserRolesRequest">
2431 DELETE FROM ep_user_roles_request where app_id=:app_id
2436 <!-- Remove rows from web analytics table entries that is associated with this app-->
2437 <sql-query name="removeAppFromEpWebAnalytics">
2439 DELETE FROM ep_web_analytics_source where app_id=:app_id
2444 <!-- Remove row from fn app table -->
2445 <sql-query name="removeAppFromFnApp">
2447 DELETE FROM fn_app where app_id=:app_id
2453 <query name="getBasicauthAccount">
2454 select id,password from BasicAuthCredentials
2457 <query name="getMicroserviceInfo">
2458 select id,password from MicroserviceData
2461 <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2462 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2464 select * from fn_user_role where user_id= :userId
2465 and role_id= :roleId
2471 <sql-query name="userAppGlobalRoles">
2472 <return alias="userAppGlobalRoles"
2473 class="org.onap.portalapp.portal.domain.EPRole" />
2475 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2476 from fn_user_role a, fn_role fr, fn_user fu
2477 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
2482 <sql-query name="getAllCentralizedAppsRoles">
2483 <return alias="allCentralizedAppsRoles"
2484 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2486 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
2487 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 ;
2491 <sql-query name="getUserCentralizedAppRoles">
2492 <return alias="userCentralizedAppRoles"
2493 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2495 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
2496 where fa.app_id = fur.app_id
2497 and fr.role_id = fur.role_id
2498 and fu.user_id = fur.user_id
2499 and fu.ORG_USER_ID = :orgUserId
2500 and fa.auth_namespace is not null
2501 and fr.active_yn = 'Y'
2505 <sql-query name="getAprroverRoleFunctionsOfUser">
2507 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2508 where fu.role_id = ep.role_id
2509 and fu.app_id = ep.app_id
2510 and fu.user_id =:userId
2511 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2514 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
2515 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2519 <sql-query name="getUserApproverRoles">
2521 select distinct fr.role_name from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_role fr
2522 where fu.role_id = ep.role_id
2523 and fu.app_id = ep.app_id
2524 and fu.user_id = :userId
2525 and fu.role_id = fr.role_id and fr.active_yn='Y'
2526 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2529 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
2530 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2534 <sql-query name="getAdminAppsForTheUser">
2536 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)
2543 <sql-query name="getUserRoleOnUserIdAndAppId">
2544 <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
2547 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'
2552 <sql-query name="updateFnUser">
2554 UPDATE fn_user fu SET fu.language_id=:language_id WHERE fu.login_id=:login_id
2557 </hibernate-mapping>