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="appDescription" column="app_description" />
332 <property name="appNotes" column="app_notes" />
333 <property name="landingPage" column="app_url" />
334 <property name="alternateLandingPage" 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="appBasicAuthPassword" type="string">
344 <column name="app_password" not-null="true" default="?"></column>
346 <property name="thumbnail" column="thumbnail" />
347 <property name="appBasicAuthUsername" 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="rolesInAAF" type="yes_no">
369 <column name="auth_central"></column>
371 <property name="nameSpace" type="string">
372 <column name="auth_namespace"></column>
374 <property name="modeOfIntegration" type="string">
375 <column name="mode_of_integration"></column>
377 <property name="appAck" type="yes_no">
378 <column name="ack_app"></column>
380 <property name="usesCadi" type="yes_no">
381 <column name="uses_cadi"></column>
384 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
388 <!-- EPUserAppsSortPreference class mapping details -->
389 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
390 <id name="id" column="ID">
391 <generator class="native">
392 <param name="sequence">seq_ep_usrappsrtprf</param>
395 <property name="userId" column="USER_ID" />
396 <property name="sortPref" column="SORT_PREF" />
399 <!-- EPUserAppsManualSortPreference class mapping details -->
400 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
401 <id name="id" column="ID">
402 <generator class="native">
403 <param name="sequence">seq_ep_usrmanappsrtprf</param>
406 <property name="userId" column="user_id"
407 unique-key="uk_1_ep_pers_user_app_man_sort" />
408 <property name="appId" column="app_id"
409 unique-key="uk_1_ep_pers_user_app_man_sort" />
410 <property name="appManualSortOrder" column="sort_order" type="int" />
413 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
414 <id name="id" column="ID">
415 <generator class="native">
416 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
419 <property name="userId" column="user_id" />
420 <property name="widgetId" column="widget_id" />
421 <property name="widgetRow" column="x" />
422 <property name="widgetCol" column="y" />
423 <property name="widgetHeight" column="height" />
424 <property name="widgetWidth" column="width" />
427 <!-- User Role class mapping details -->
428 <class name="EPRole" table="FN_ROLE" >
429 <id name="id" column="role_id">
430 <generator class="native">
431 <param name="sequence">seq_fn_role</param>
435 <property name="name" column="role_name" />
436 <property name="priority" column="priority" />
437 <property name="active" column="active_yn" type="yes_no" />
438 <!-- 2 lines below may be removed! -->
439 <property name="appId" column="APP_ID" />
440 <property name="appRoleId" column="APP_ROLE_ID" />
441 <!-- 2 lines above may be removed -->
443 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
445 <key column="role_id" />
446 <many-to-many column="function_cd"
447 class="org.onap.portalsdk.core.domain.RoleFunction" />
450 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
452 <key column="parent_role_id" />
453 <many-to-many column="child_role_id"
454 class="org.onap.portalapp.portal.domain.EPRole" />
457 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
459 <key column="child_role_id" />
460 <many-to-many column="parent_role_id"
461 class="org.onap.portalapp.portal.domain.EPRole" />
466 <!-- User App class mapping details -->
467 <class name="AppContactUs" table="fn_app_contact_us">
468 <id name="id" column="app_id">
469 <generator class="foreign">
470 <param name="property">app</param>
473 <property name="url" column="url" />
474 <property name="description" column="description" />
475 <property name="contactName" column="contact_name" />
476 <property name="contactEmail" column="contact_email" />
477 <property name="activeYN" column="active_yn" />
479 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
483 <!-- Personalization of user app selections -->
484 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
485 <id name="id" column="id">
486 <generator class="native">
487 <param name="property">seq_fn_pers_user_app_sel</param>
490 <property name="userId" column="user_id" />
491 <property name="appId" column="app_id" />
492 <property name="statusCode" column="status_cd" />
495 <!-- EPAppRoleFunction class mapping details -->
496 <class name="EPAppRoleFunction" table="ep_app_role_function">
497 <id name="id" column="id">
498 <generator class="native">
499 <param name="property">seq_epp_app_role_func</param>
502 <property name="roleId" column="role_id"></property>
503 <property name="appId" column="app_id"></property>
504 <property name="code" column="function_cd"></property>
505 <property name="roleAppId" column="role_app_id"></property>
508 <!-- CentralRoleFunction class mapping details -->
509 <class name="CentralV2RoleFunction" table="ep_app_function">
511 <key-property name="appId"
513 <key-property name="code"
514 column="function_cd" />
516 <property name="name" column="function_name" />
519 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
520 <id name="id" column="id">
521 <generator class="native">
522 <param name="property">seq_ep_pers_user_widget_sel</param>
525 <property name="userId" column="user_id" />
526 <property name="widgetId" column="widget_id" />
527 <property name="statusCode" column="status_cd" />
530 <!-- EPUserAppRolesRequest class mapping details -->
531 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
532 <id name="id" column="req_id">
533 <generator class="native">
534 <param name="sequence">seq_ep_user_role_request</param>
537 <property name="userId" column="user_id" />
538 <property name="appId" column="app_id" />
539 <property name="createdDate" column="created_date" type="timestamp" />
540 <property name="updatedDate" column="updated_date" type="timestamp" />
541 <property name="requestStatus" column="request_status" />
543 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
544 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
545 <key column="req_id" />
547 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
551 <!-- EPUserAppRolesRequestDetail class mapping details -->
552 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
553 <id name="id" column="id">
554 <generator class="native">
555 <param name="sequence">seq_ep_user_role_request_det</param>
558 <property name="reqRoleId" column="requested_role_id" />
559 <property name="reqType" column="request_type" />
560 <many-to-one name="epRequestIdData" fetch="select"
561 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest">
562 <column name="req_id" not-null="true" />
566 <!-- multilanguage -->
567 <query name="queryLanguage">
573 <query name="displayText">
575 from DisplayText where languageId = :language_id
579 <query name="queryLanguageByLanguageId">
581 from Language where languageId = :language_id
585 <query name="getActiveUsersForApp">
587 select distinct(u) from EPUser as u inner join fetch u.EPUserApps as apps inner join fetch apps.app as app inner join fetch apps.role as role where app.id = :id and u.active = :active and role.id is not null
591 <!-- show the current user plus related users -->
592 <sql-query name="relatedUsers">
593 <return-scalar column="org_user_id" type="java.lang.String" />
595 select t.org_user_id from (
596 select distinct c.org_user_id org_user_id, c.last_login_date from
597 (select c.org_user_id, c.last_login_date from
599 (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,
601 where a.ROLE_ID = b.role_id
602 and a.APP_ID = b.app_id
603 and a.USER_ID = c.user_id
605 select org_user_id , last_login_date from fn_user where org_user_id=:userId
607 order by c.last_login_date desc limit 10
612 <!-- requires values for named parameters :searchQuery and :userId -->
613 <sql-query name="searchPortal">
614 <return alias="searchResult"
615 class="org.onap.portalapp.portal.ecomp.model.SearchResultItem" />
617 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
619 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
621 upper( :searchQuery ) != ''
622 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
623 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
624 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%'))
626 ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
628 last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
630 (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
632 first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
633 and upper(active_yn) = 'Y'
637 select distinct 'Application' CATEGORY, b1.app_name NAME,
638 if (b1.app_type = 2,'false','true') UUID,
639 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
643 from fn_user_role a, fn_user b, fn_app c
644 where a.USER_ID = b.user_id
645 and upper(b.org_user_id) = upper( :userId )
646 and a.app_id = c.app_id
647 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
648 and upper(c.ENABLED) = 'Y'
649 ) a1 right outer join
650 (select * from fn_app where
651 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
652 and upper(ENABLED) = 'Y') b1
653 on a1.APP_ID = b1.app_id
657 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
658 if (b1.app_type = 2,'false','true') UUID,
659 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
661 (select distinct d.*, e.app_type from fn_user a,
662 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
663 where a.USER_ID = b.user_id
664 and b.role_id = c.role_id
665 and c.menu_id = d.menu_id
666 and upper(a.org_user_id) = upper( :userId )
667 and c.APP_ID = e.app_id
668 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
669 and upper(d.active_yn) = 'Y'
670 and upper(e.enabled) = 'Y'
671 ) a1 right outer join
673 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
674 where active_yn = 'Y'
675 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
676 and a.menu_id = b.menu_id
677 and b.app_id = c.app_id
680 on a1.menu_id = b1.menu_id
683 select distinct CATEGORY, NAME, UUID, TARGET
687 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
690 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
691 where upper(a.org_user_id) = upper( :userId )
692 and a.user_id = b.user_id
693 and b.role_id = c.role_id
694 and c.widget_id = d.widget_id
695 and d.all_user_flag<>'Y'
696 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
700 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
703 from ep_widget_catalog d
704 where d.all_user_flag='Y'
705 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
709 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
712 from fn_user a, fn_user_role b, ep_widget_catalog d
713 where upper(a.org_user_id) = upper( :userId )
714 and a.user_id = b.user_id
716 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
720 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
726 <!-- requires values for named parameters :searchQuery and :userId -->
727 <sql-query name="getNotifications">
728 <return alias="notificationResult"
729 class="org.onap.portalapp.portal.transport.EpNotificationItem" />
731 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
734 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
737 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
740 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
741 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
742 from ep_notification a, ep_role_notification b
743 where a.notification_id = b.notification_id
744 and (end_time is null || SYSDATE() <= end_time )
745 and (start_time is null || SYSDATE() >= start_time)
746 and a.is_for_all_roles = 'N'
749 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
750 from fn_user a, fn_user_role b, fn_role c, fn_app d
751 where COALESCE(c.app_id,1) = d.app_id
752 and a.user_id = b.user_id
753 and a.user_id = :user_id
754 and b.role_id = c.role_id
755 and (d.enabled='Y' or d.app_id=1)
759 a.role_id = b.role_id
762 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
765 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
766 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
767 from ep_notification a, ep_role_notification b
768 where a.notification_id = b.notification_id
769 and (end_time is null || SYSDATE() <= end_time )
770 and (start_time is null || SYSDATE() >= start_time)
771 and a.is_for_all_roles = 'N'
775 a.recv_user_id=:user_id
779 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
780 from ep_notification a
781 where a.notification_id
782 and (end_time is null || SYSDATE() <= end_time )
783 and (start_time is null || SYSDATE() >= start_time)
784 and a.is_for_all_roles = 'Y'
792 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'
794 order by priority desc, created_date desc,start_Time desc
798 (SELECT @rn /*'*/:=/*'*/ 0) t2
803 <sql-query name="getUsersByOrgIdsNotifications">
804 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
806 SELECT * from fn_user where org_user_id in (:OrgIds)
812 <!-- Gets all Admin notification history; accepts no parameters -->
813 <sql-query name="getAdminNotificationHistoryVO">
814 <return alias="adminNotificationHistoryVOResult"
815 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
819 n.notification_ID AS notificationId,
820 n.is_for_online_users AS isForOnlineUsers,
821 n.is_for_all_roles AS isForAllRoles,
822 n.active_YN AS activeYn,
823 n.msg_header AS msgHeader,
824 n.msg_description AS msgDescription,
825 n.msg_source AS msgSource,
826 n.start_Time AS startTime,
827 n.end_time AS endTime,
829 n.creator_ID AS creatorId,
830 n.created_date AS createdDate,
831 n.notification_hyperlink AS notificationHyperlink,
832 u.org_user_id AS loginId
836 fn_user u on u.user_id = n.creator_id
838 n.active_YN='Y' and msg_source='EP'
843 n.notification_ID AS notificationId,
844 n.is_for_online_users AS isForOnlineUsers,
845 n.is_for_all_roles AS isForAllRoles,
846 n.active_YN AS activeYn,
847 n.msg_header AS msgHeader,
848 n.msg_description AS msgDescription,
849 n.msg_source AS msgSource,
850 n.start_Time AS startTime,
851 n.end_time AS endTime,
853 n.creator_ID AS creatorId,
854 n.created_date AS createdDate,
855 n.notification_hyperlink AS notificationHyperlink,
856 u.org_user_id AS loginId
860 fn_user u on u.user_id = n.creator_id
862 ep_role_notification r on r.notification_ID=n.notification_ID
864 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
865 )n order by n.startTime desc
869 <sql-query name="getEpNotificationAppRoles">
870 <return alias="notificationAppRoles"
871 class="org.onap.portalapp.portal.domain.EcompAppRole" />
872 <!-- This query requires no parameters. -->
875 select a.app_id, a.app_name, b.role_id, b.role_name from
876 (select * from fn_app where app_id = 1) a,
877 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
879 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
881 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
888 <!-- Gets all notification history -->
889 <sql-query name="getNotificationHistoryVO">
890 <return alias="notificationHistoryVOResult"
891 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
894 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
895 startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
899 a.notification_ID AS notificationId,
900 is_for_online_users AS isForOnlineUsers,
901 is_for_all_roles AS isForAllRoles,
902 msg_header AS msgHeader,
903 msg_description AS msgDescription,
904 msg_source AS msgSource,
905 start_Time AS startTime,
908 created_date AS createdDate,
909 creator_ID AS creatorId,
910 notification_hyperlink AS notificationHyperlink,
912 active_YN AS activeYn,
913 if (is_viewed is null, 'N', is_viewed)
917 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
918 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
919 creator_ID,notification_hyperlink,active_YN
922 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
923 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,
924 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
927 END AS login_id,b.recv_user_id
928 from ep_notification a, ep_role_notification b,fn_user u
929 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
930 and a.is_for_all_roles = 'N'
932 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
934 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
936 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
938 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
942 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
943 from fn_user a, fn_user_role b, fn_role c, fn_app d
944 where COALESCE(c.app_id,1) = d.app_id
945 and a.user_id = b.user_id
946 and a.user_id = :user_id
947 and b.role_id = c.role_id
948 and (d.enabled='Y' or d.app_id=1)
952 a.role_id = b.role_id
956 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
957 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
958 creator_ID,notification_hyperlink,active_YN
961 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
962 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,
963 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
966 END AS login_id,b.recv_user_id
967 from ep_notification a, ep_role_notification b,fn_user u
968 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
969 and a.is_for_all_roles = 'N'
971 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
973 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
975 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
977 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
982 a.recv_user_id=:user_id
987 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
988 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
989 creator_ID, a.notification_hyperlink,a.active_YN
990 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
991 where a.notification_id
992 and a.is_for_all_roles = 'Y'
994 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
996 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
998 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
1000 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
1003 ) a left outer join (
1004 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
1006 on a.notification_id = m.notification_ID
1010 order by start_Time desc,end_time desc
1012 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
1017 <!-- shows the received recipient to whom the notification is delivered from external system -->
1018 <sql-query name="messageRecipients">
1019 <return-scalar column="org_user_id" type="java.lang.String" />
1021 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
1026 <!-- Gets role details for a specified notification -->
1027 <sql-query name="getNotificationRoles">
1028 <return alias="notificationRolesResult"
1029 class="org.onap.portalapp.portal.transport.EpRoleNotificationItem" />
1033 ep_role_notification
1035 notification_Id = :notificationId
1039 <!-- Gets all applications, possibly with contact information -->
1040 <sql-query name="getAppsAndContacts">
1041 <return alias="appContactUs"
1042 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1043 <!-- This query requires no parameters. -->
1046 a.app_id as appId, a.app_name as appName,
1047 c.contact_name as contactName,
1048 c.contact_email as contactEmail, c.url, c.description,
1049 c.active_yn as activeYN
1054 on a.app_id = c.app_id
1056 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1061 <!-- Gets all rows from the app-contact-us table, extended with app information -->
1062 <sql-query name="getAppContactUsItems">
1063 <return alias="appContactUs"
1064 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1065 <!-- This query requires no parameters. -->
1068 c.app_id as appId, c.contact_name as contactName,
1069 c.contact_email as contactEmail, c.url, c.description,
1070 c.active_yn as activeYN, a.app_name as appName
1075 on a.app_id = c.app_id
1077 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1082 <!-- Gets one row for each app with the category and catenated functions. -->
1083 <sql-query name="getAppCategoryFunctions">
1084 <return alias="appCategoryFunctions"
1085 class="org.onap.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1086 <!-- This query requires no parameters. -->
1088 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1089 r.category as category, r.app_id as appId, r.app_name as application,
1090 group_concat(function_text separator ', ') as functions
1093 app_id, function_text, app_name,
1094 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1097 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1098 k.text parent_menu, k.parent_menu_id parent_menu_id
1101 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1103 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1105 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1107 fn_menu_functional k
1108 WHERE j.parent_menu_id = k.menu_id
1110 fn_menu_functional a
1111 WHERE fn.parent_menu_id = a.menu_id
1113 (SELECT @rn /*'*/:=/*'*/ 0) t2
1114 group by r.category, r.app_id, r.app_name
1115 order by category, app_name
1120 <!-- Gets one row for each function-application-role combination. -->
1121 <sql-query name="getAppAccessFunctionRole">
1122 <return alias="appAccessFunctionRole"
1123 class="org.onap.portalapp.portal.domain.GetAccessResult" />
1124 <!-- This query requires one parameters. -->
1126 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1127 (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,
1128 o.requested_role_id,o.request_type
1130 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1133 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1136 where COALESCE(c.app_id,1) = d.app_id
1137 and (d.enabled='Y' or d.app_id=1)
1138 and c.active_yn = 'Y'
1141 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1143 A.MENU_ID = B.MENU_ID
1146 on a.role_id = b.role_id) t left outer join
1147 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1148 where er.req_id=ed.req_id
1149 and upper(ed.request_type)='P'
1150 and er.user_id =:userId) o
1151 on t.app_id=o.app_id
1152 and t.role_id = o.requested_role_id
1153 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1158 <query name="getCommonWidgetItem">
1159 from CommonWidget where category = :cat order by
1163 <sql-query name="getGuestLastLogin">
1164 <return-scalar column="audit_date" type="java.util.Date" />
1166 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1170 <sql-query name="getActiveUsers">
1171 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1173 SELECT * FROM FN_USER WHERE active_yn='Y'
1177 <sql-query name="getAppsAdmins">
1178 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1180 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
1184 <sql-query name="getPortalAdmins">
1185 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1187 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
1191 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1192 <sql-query name="getPersUserApps">
1193 <return alias="persUserApps" class="org.onap.portalapp.portal.domain.EPApp" />
1194 <!-- This query requires one parameter: userId (number) -->
1197 distinct -- multiple roles yield multiple rows
1198 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1199 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1200 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1201 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1202 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
1204 -- Portal assigns role 999 to app administrator
1205 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1206 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1207 where a.ENABLED = 'Y'
1209 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1211 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1218 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1220 <sql-query name="getPersAdminApps">
1221 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1222 <!-- This query requires one parameter: userId (number) -->
1225 distinct -- multiple roles yield multiple rows
1226 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1227 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1228 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1229 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1230 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1233 -- Portal assigns role 999 to app administrator
1234 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1235 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1236 where a.ENABLED = 'Y'
1238 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1240 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1242 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1249 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1250 <sql-query name="getPersAdminAppsOrderByName">
1251 <return alias="AdminAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1252 <!-- This query requires one parameter: userId (number) -->
1255 distinct -- multiple roles yield multiple rows
1256 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1257 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1258 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1259 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1260 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1261 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1263 -- Portal assigns role 999 to app administrator
1264 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1265 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1266 where a.ENABLED = 'Y'
1268 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1270 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1272 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1275 select distinct -- multiple roles yield multiple rows
1276 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1277 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1278 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1279 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1280 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1281 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION , b.ACK_APP , b.USES_CADI
1282 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1283 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
1290 <!--Gets personalized list of enabled and accessible User apps by name -->
1291 <sql-query name="getPersUserAppsOrderByName">
1292 <return alias="UserAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1293 <!-- This query requires one parameter: userId (number) -->
1296 distinct -- multiple roles yield multiple rows
1297 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1298 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1299 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1300 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1301 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1302 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1304 -- Portal assigns role 999 to app administrator
1305 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1306 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1307 where a.ENABLED = 'Y'
1309 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1311 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1314 select distinct -- multiple roles yield multiple rows
1315 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1316 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1317 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1318 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1319 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1320 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1321 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1322 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
1330 <!--Gets personalized list of enabled and accessible Super Admin apps by
1332 <sql-query name="getAdminAppsOrderByLastUsed">
1333 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1338 distinct -- multiple roles yield multiple rows
1339 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1340 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1341 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1342 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1343 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1346 -- Portal assigns role 999 to app administrator
1347 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1348 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1349 where a.ENABLED = 'Y'
1351 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1353 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1355 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1358 select distinct -- multiple roles yield multiple rows
1359 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1360 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1361 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1362 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1363 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1365 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1366 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
1369 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1370 from FN_APP LEFT JOIN FN_AUDIT_LOG
1371 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1372 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1373 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1374 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1376 on A.app_id = B.Affected_record_id
1377 order by AUDIT_DATE DESC;
1381 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1382 <sql-query name="getUserAppsOrderByLastUsed">
1383 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1385 select * from (select
1386 distinct -- multiple roles yield multiple rows
1387 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1388 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1389 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1390 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1391 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1394 -- Portal assigns role 999 to app administrator
1395 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1396 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1397 where a.ENABLED = 'Y'
1399 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1401 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1403 select distinct -- multiple roles yield multiple rows
1404 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1405 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1406 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1407 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1408 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1410 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1411 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
1415 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1416 from FN_APP LEFT JOIN FN_AUDIT_LOG
1417 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1418 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1419 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1420 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1422 on A.app_id = B.Affected_record_id
1423 order by AUDIT_DATE DESC;
1430 <!--Gets personalized list of enabled and accessible Super Admin apps by
1432 <sql-query name="getAdminAppsOrderByMostUsed">
1433 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1438 distinct -- multiple roles yield multiple rows
1439 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1440 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1441 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1442 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1443 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1446 -- Portal assigns role 999 to app administrator
1447 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1448 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1449 where a.ENABLED = 'Y'
1451 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1453 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1455 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1457 select distinct -- multiple roles yield multiple rows
1458 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1459 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1460 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1461 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1462 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1464 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1465 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
1468 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1469 from FN_APP LEFT JOIN FN_AUDIT_LOG
1470 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1471 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1472 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1473 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1475 on A.app_id = B.Affected_record_id
1476 order by total_visits DESC;
1481 <!--Gets personalized list of enabled and accessible NON Super Admin User
1482 apps by Most Used -->
1483 <sql-query name="getUserAppsOrderByMostUsed">
1484 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1488 distinct -- multiple roles yield multiple rows
1489 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1490 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1491 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1492 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1493 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1496 -- Portal assigns role 999 to app administrator
1497 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1498 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1499 where a.ENABLED = 'Y'
1501 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1503 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1505 select distinct -- multiple roles yield multiple rows
1506 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1507 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1508 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1509 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1510 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1512 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1513 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
1516 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1517 from FN_APP LEFT JOIN FN_AUDIT_LOG
1518 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1519 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1520 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1521 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1523 on A.app_id = B.Affected_record_id
1524 order by total_visits DESC;
1528 <!--Gets personalized list of enabled and accessible Super Admin apps by
1530 <sql-query name="getAdminAppsOrderByManual">
1531 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1535 distinct -- multiple roles yield multiple rows
1536 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1537 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1538 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1539 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1540 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1543 -- Portal assigns role 999 to app administrator
1544 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1545 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1546 where a.ENABLED = 'Y'
1548 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1550 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1552 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1554 select distinct -- multiple roles yield multiple rows
1555 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1556 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1557 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1558 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1559 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1561 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1562 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
1565 (select m.app_id, m.sort_order
1566 from ep_pers_user_app_man_sort m
1567 where USER_ID = :userId
1569 on A.APP_ID = B.app_id
1570 order by sort_order ASC
1576 <!--Gets personalized list of enabled and accessible NON Super admin User
1578 <sql-query name="getUserAppsOrderByManual">
1579 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1581 select * from (select
1582 distinct -- multiple roles yield multiple rows
1583 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1584 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1585 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1586 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1587 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1590 -- Portal assigns role 999 to app administrator
1591 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1592 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1593 where a.ENABLED = 'Y'
1595 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1597 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1599 select distinct -- multiple roles yield multiple rows
1600 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1601 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1602 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1603 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1604 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1606 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1607 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
1610 (select m.app_id, m.sort_order
1611 from ep_pers_user_app_man_sort m
1612 where USER_ID = :userId
1614 on A.APP_ID = B.app_id
1615 order by sort_order ASC
1620 <!-- Gets regular user's list of enabled apps including accessible and select
1622 <sql-query name="getUserAppCatalog">
1623 <return alias="userAppCatalog"
1624 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1625 <!-- This query requires one parameter: userId (number) -->
1628 distinct -- multiple roles yield multiple rows
1629 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1630 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1631 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1632 -- IF(a.app_type = '2', TRUE, FALSE) as restricted,
1634 when a.app_type = '2' and a.app_type = '3' then TRUE
1637 IF(a.open = 'Y', TRUE, FALSE) as open,
1638 -- ACCESS(-ible) means user has a defined role OR the application is open
1640 -- regular app and user has a role
1641 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1643 when a.OPEN = 'Y' then TRUE
1646 -- SELECT(-ed) indicates user personalization
1648 -- regular app, user has a role, no personalization
1649 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1650 -- open app and has personalization
1651 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1654 -- PENDING indicates user checked a box
1656 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1660 -- Portal assigns role 999 to app administrator
1661 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1662 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1663 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'
1664 where a.ENABLED = 'Y'
1666 -- Show accessible apps first, then the rest; sort by name within each set.
1667 order by access desc, app_name asc
1672 <sql-query name="getMyloginAppDetails">
1673 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1675 SELECT * FROM fn_app where ml_app_name =:appName
1680 <sql-query name="getAppDetailsByAppName">
1681 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1683 SELECT * FROM fn_app where app_name =:appName
1688 <sql-query name="getMyAppDetailsByUebKey">
1689 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1691 SELECT * FROM fn_app where ueb_key =:appKey
1697 <sql-query name="deleteAccountEndpoint">
1699 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1704 <sql-query name="deleteAccountEndpointRecord">
1706 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1711 <sql-query name="deleteEPEndpoint">
1713 DELETE FROM ep_endpoints WHERE id =:epId
1718 <sql-query name="deleteBasicAuthAccount">
1720 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1725 <sql-query name="getAppRoles">
1726 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1728 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1733 <sql-query name="getPartnerAppRolesList">
1734 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1736 SELECT * FROM fn_role where app_id =:appId
1741 <sql-query name="getPortalAppRolesList">
1742 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1744 SELECT * FROM fn_role where app_id is null
1749 <sql-query name="getPortalAppRoleByRoleId">
1750 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1752 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1757 <sql-query name="getPartnerAppRoleByRoleId">
1758 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1760 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1765 <sql-query name="getPartnerAppRoleById">
1766 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1768 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1773 <sql-query name="getPortalAppRoles">
1774 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1776 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1781 <sql-query name="deleteMicroserviceParameter">
1783 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1788 <sql-query name="deleteMicroserviceParameterById">
1790 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1795 <sql-query name="deleteWidgetCatalogParameter">
1797 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1802 <sql-query name="deleteMicroservice">
1804 DELETE FROM ep_microservice WHERE id =:serviceId
1809 <sql-query name="epUserAppId">
1810 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1812 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1817 <sql-query name="userAppsSortPreferenceQuery">
1818 <return alias="appsSortPreferenceQuery"
1819 class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1821 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1826 <sql-query name="userAppsManualSortPrfQuery">
1827 <return alias="AppsManualSortPrfQuery"
1828 class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1830 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1835 <sql-query name="userWidgetManualSortPrfQuery">
1836 <return alias="widgetManualSortPrfQuery"
1837 class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1839 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1843 <sql-query name="appRoles">
1844 <return alias="rolesForApp"
1845 class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1847 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1852 <sql-query name="userAppRolesRequestList">
1853 <return alias="appRolesRequestList"
1854 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1856 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'
1861 <sql-query name="userAppRolesRequestDetailList">
1862 <return alias="appRolesRequestDetailList"
1863 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1866 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1871 <!-- Gets list of enabled apps including accessible and select statuses -->
1872 <sql-query name="getAdminAppCatalog">
1873 <return alias="adminAppCatalog"
1874 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1875 <!-- This query requires one parameter: userId (number) -->
1878 distinct -- multiple roles yield multiple rows
1879 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1880 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1881 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1882 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1883 IF(a.open = 'Y', TRUE, FALSE) as open,
1884 -- ACCESS(-ible) means user has a defined role OR the application is open
1886 -- regular app and user has a role
1887 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1889 when a.OPEN = 'Y' then TRUE
1892 -- SELECT(-ed) indicates user personalization
1894 -- regular app, user has a role, no personalization
1895 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1896 -- regular app, user has role, admin forced a personalization
1897 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1898 -- regular app, user has no role, admin forced a personalization
1899 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1900 -- open app and has personalization
1901 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1904 -- PENDING indicates user checked a box
1906 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1910 -- Portal assigns role 999 to app administrator
1911 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1912 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1913 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'
1914 where a.ENABLED = 'Y'
1915 -- Show accessible apps first, then the rest; sort by name within each set.
1916 order by access desc, app_name asc
1921 <sql-query name="userAppCatalogRoles">
1922 <return alias="epUserAppCatalogRoles"
1923 class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1925 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
1926 from ep_user_roles_request A left join ep_user_roles_request_det B
1927 on a.req_id = b.req_id
1928 where A.user_id=:userid
1929 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1930 and A.request_status ='P'
1935 <sql-query name="getUserApproles">
1936 <return alias="businessCardUserApplicationRoles"
1937 class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1940 select fr.role_name, fa.app_name
1942 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1943 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)
1949 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1950 <sql-query name="deleteNotificationsFromEpNotificationTable">
1952 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1957 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1958 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1960 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1964 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1965 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1967 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1971 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1972 <sql-query name="getAppRoleFunctionList">
1973 <return alias="appRoleFunctionList"
1974 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1977 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1978 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1984 <!-- Gets list of all role functions -->
1985 <sql-query name="getAllRoleFunctions">
1986 <return alias="allRoleFunctions"
1987 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1990 SELECT * from ep_app_function where app_id =:appId
1996 <!-- Get ep_app_function records provided by single parameter -->
1997 <sql-query name="getRoleFunction">
1998 <return alias="RoleFunction"
1999 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2002 SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
2008 <!-- Gets the current user app roles records and requires two parameters -->
2009 <sql-query name="getUserAppCurrentRoles">
2010 <return alias="userAppCurrentRoles"
2011 class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
2014 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'
2019 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
2020 <sql-query name="getUserAppExistingRoles">
2021 <return alias="userAppExistingRoles"
2022 class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
2024 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
2025 left outer join fn_role fr on fu.role_id = fr.role_id
2026 where fu.user_id =:userId and fu.app_id =:appId
2031 <!-- Gets all functions of portal app -->
2032 <sql-query name="getAllFunctions">
2033 <return alias="allFunctions"
2034 class="org.onap.portalsdk.core.domain.RoleFunction" />
2036 select * from fn_function
2041 <!-- Gets the current user app roles records and requires two parameters -->
2042 <sql-query name="getPartnerAppFunctions">
2043 <return alias="partnerAppFunctions"
2044 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2046 select * from ep_app_function where app_id =:appId
2051 <!-- Gets the all role functions and requires one parameters -->
2052 <sql-query name="uploadAllRoleFunctions">
2053 <return alias="allRoleFunctions"
2054 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2056 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
2061 <!-- Gets the all application role functions from ep_app_function table and requires one parameters -->
2062 <sql-query name="uploadPartnerRoleFunctions">
2063 <return alias="partnerRoleFunctions"
2064 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2066 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
2071 <sql-query name="getRoletoUpdateInExternalAuthSystem">
2072 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2074 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2079 <!-- Gets all active records from fn_role that is associated with this app-->
2080 <sql-query name="getActiveRolesOfApplication">
2081 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2083 select * from fn_role where active_yn = 'Y' and app_id=:appId
2088 <sql-query name="getBulkUserRoles">
2089 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2091 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2092 left outer join fn_role fr on fr.role_id = fur.role_id
2093 left outer join fn_app fa on fa.app_id = fur.app_id
2094 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2099 <sql-query name="getBulkUsersForSingleRole">
2100 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2102 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2103 left outer join fn_role fr on fr.role_id = fur.role_id
2104 left outer join fn_app fa on fa.app_id = fur.app_id
2105 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2110 <sql-query name="getCentralizedApps">
2111 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2113 select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2118 <sql-query name="getUserRoles">
2119 <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2122 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
2126 <sql-query name="getRoleFunctionsOfUser">
2128 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2129 where fu.role_id = ep.role_id
2130 and fu.app_id = ep.app_id
2131 and fu.user_id =:userId and ep.app_id = 1
2132 and ea.function_cd = ep.function_cd
2134 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2136 and app_r_f.function_cd = a_f.function_cd
2139 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
2140 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2145 <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
2147 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2148 where fu.role_id = ep.role_id
2149 and fu.app_id = ep.app_id
2150 and fu.user_id =:userId
2151 and ea.function_cd = ep.function_cd
2154 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
2155 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2160 <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
2161 <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2163 SELECT app.APP_ID, app.APP_NAME, app.APP_TYPE FROM FN_APP app
2164 inner join FN_USER_ROLE userrole ON userrole.APP_ID=app.APP_ID
2165 where userrole.USER_ID = :userId AND userrole.ROLE_ID=999 AND (app.ENABLED = 'Y' OR app.APP_ID=1)
2167 SELECT DISTINCT app.APP_ID, app.app_name, app.APP_TYPE FROM fn_user user
2168 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2169 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2170 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2171 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%')
2172 WHERE user.user_id = :userId ;
2176 <sql-query name="getApplicationsofTheUserContainsApprover">
2177 <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2179 SELECT DISTINCT app.APP_ID, app.APP_TYPE, app.app_name FROM fn_user user
2180 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2181 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2182 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2183 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%')
2184 WHERE user.user_id = :userId ;
2190 <sql-query name="getUserRolesForLeftMenu">
2191 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2194 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
2195 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2196 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2197 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2198 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)
2203 <sql-query name="getRolesForLeftMenu">
2204 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2207 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
2208 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2209 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2210 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2211 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)
2216 <!-- Gets all functions for an application along with global functions and requires single parameter -->
2217 <sql-query name="getMenuFunctions">
2219 select f.function_cd from ep_app_function f
2220 where f.app_id =:appId
2222 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2223 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2228 <sql-query name="getRequestIdsForApp">
2229 <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2231 select * from ep_user_roles_request where app_id =:app_id
2236 <sql-query name="ApplicationUserRoles">
2237 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2239 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,
2240 fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2241 from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2242 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
2244 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,
2245 fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2246 from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2247 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'
2248 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';
2252 <sql-query name="getCurrentAppRoleFunctions">
2253 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2255 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
2256 and ef.function_cd =:functionCd and epr.app_id =:appId
2261 <sql-query name="deleteUserWidgetPlacement">
2263 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2268 <sql-query name="getCentralizedAppsOfUser">
2269 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2272 select distinct fa.app_id, fa.app_name
2273 from fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2274 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2275 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)
2281 <query name="getEPUserByOrgUserId">
2283 FROM EPUser WHERE orgUserId = :org_user_id
2287 <query name="getEPUserByLoginId">
2289 FROM EPUser WHERE loginId = :login_id
2293 <query name="getEPUserByLoginIdLoginPwd">
2295 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2299 <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2300 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2302 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
2303 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2304 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
2309 <sql-query name="getGlobalRolesOfPortal">
2310 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2312 select * from fn_role where role_name like 'global_%' and app_id is null and active_yn='Y'
2317 <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2318 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2320 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2325 <sql-query name="getAppFunctionOnCodeAndAppId">
2326 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2328 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2333 <sql-query name="getGlobalRoleForRequestedApp">
2334 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2336 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
2337 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2338 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
2343 <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2344 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2346 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
2347 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2348 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
2353 <sql-query name="updateMenuFunctionalAndRoles">
2355 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
2360 <!-- Remove any favorites associated with a menu item that is associated with this app -->
2361 <sql-query name="removeAppFromMenuFavorites">
2363 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
2368 <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2369 <sql-query name="removeAppFromMenuFunctionalRoles">
2371 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2376 <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2377 <sql-query name="removeAppFromEpAppRoleFunction">
2379 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2384 <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2385 <sql-query name="removeAppFromEpAppFunction">
2387 DELETE FROM ep_app_function WHERE app_id=:app_id
2392 <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2393 <sql-query name="removeAppFromFnUserRole">
2395 DELETE FROM fn_user_role WHERE app_id=:app_id
2400 <!-- Remove any widgets that is associated with this app-->
2401 <sql-query name="removeAppFromEpWidgetCatalogRole">
2403 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2408 <!-- Remove any notifications that is associated with this app-->
2409 <sql-query name="removeAppFromEpRoleNotification">
2411 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2412 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2417 <!-- Remove all records from fn_role that is associated with this app-->
2418 <sql-query name="removeAppFromFnRole">
2420 DELETE FROM fn_role where app_id=:app_id
2425 <!-- Remove app contact us entries that is associated with this app-->
2426 <sql-query name="removeAppFromAppContactUs">
2428 DELETE FROM fn_app_contact_us where app_id=:app_id
2433 <!-- Remove app personalization entries that is associated with this app-->
2434 <sql-query name="removeAppFromEpPersUserAppSel">
2436 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2441 <!-- Remove app personalization sorting entries that is associated with this app-->
2442 <sql-query name="removeAppFromEpPersUserAppManSort">
2444 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2449 <!-- Remove rows from user role request table entries that is associated with this app-->
2450 <sql-query name="removeAppFromEpUserRolesRequest">
2452 DELETE FROM ep_user_roles_request where app_id=:app_id
2457 <!-- Remove rows from web analytics table entries that is associated with this app-->
2458 <sql-query name="removeAppFromEpWebAnalytics">
2460 DELETE FROM ep_web_analytics_source where app_id=:app_id
2465 <!-- Remove row from fn app table -->
2466 <sql-query name="removeAppFromFnApp">
2468 DELETE FROM fn_app where app_id=:app_id
2474 <query name="getBasicauthAccount">
2475 select id,password from BasicAuthCredentials
2478 <query name="getMicroserviceInfo">
2479 select id,password from MicroserviceData
2482 <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2483 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2485 select * from fn_user_role where user_id= :userId
2486 and role_id= :roleId
2492 <sql-query name="userAppGlobalRoles">
2493 <return alias="userAppGlobalRoles"
2494 class="org.onap.portalapp.portal.domain.EPRole" />
2496 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2497 from fn_user_role a, fn_role fr, fn_user fu
2498 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
2503 <sql-query name="getAllCentralizedAppsRoles">
2504 <return alias="allCentralizedAppsRoles"
2505 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2507 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
2508 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 ;
2512 <sql-query name="getUserCentralizedAppRoles">
2513 <return alias="userCentralizedAppRoles"
2514 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2516 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
2517 where fa.app_id = fur.app_id
2518 and fr.role_id = fur.role_id
2519 and fu.user_id = fur.user_id
2520 and fu.ORG_USER_ID = :orgUserId
2521 and fa.auth_namespace is not null
2522 and fr.active_yn = 'Y'
2526 <sql-query name="getAprroverRoleFunctionsOfUser">
2528 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2529 where fu.role_id = ep.role_id
2530 and fu.app_id = ep.app_id
2531 and fu.user_id =:userId
2532 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2535 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
2536 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2540 <sql-query name="getUserApproverRoles">
2542 select distinct fr.role_name from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_role fr
2543 where fu.role_id = ep.role_id
2544 and fu.app_id = ep.app_id
2545 and fu.user_id = :userId
2546 and fu.role_id = fr.role_id and fr.active_yn='Y'
2547 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2550 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
2551 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2555 <sql-query name="getAdminAppsForTheUser">
2557 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)
2564 <sql-query name="getUserRoleOnUserIdAndAppId">
2565 <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
2568 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'
2573 <sql-query name="getAllAdminAppsofTheUser">
2575 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
2579 <sql-query name="getAllAppsFunctionsOfUser">
2581 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
2582 where fu.role_id = ep.role_id
2583 and fu.app_id = ep.app_id
2584 and fu.user_id =:userId
2585 and ea.function_cd = ep.function_cd
2586 and ((fu.app_id = fa.app_id and fa.enabled = 'Y' ) or (fa.app_id = 1))
2587 and fr.role_id = fu.role_id and fr.active_yn='Y'
2589 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2591 and app_r_f.function_cd = a_f.function_cd
2594 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
2595 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2599 <sql-query name="updateFnUser">
2601 UPDATE fn_user fu SET fu.language_id=:language_id WHERE fu.login_id=:login_id
2604 </hibernate-mapping>