ecomp-portal-BE-common/src/main/webapp/WEB-INF/fusion/orm/EP.hbm.xml

   1 <?xml version="1.0"?>
   2 <!--
   3   ============LICENSE_START==========================================
   4   ONAP Portal
   5   ===================================================================
   6   Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
   7   ===================================================================
   8
   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
  13
  14               http://www.apache.org/licenses/LICENSE-2.0
  15
  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.
  21
  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
  26
  27               https://creativecommons.org/licenses/by/4.0/
  28
  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.
  34
  35   ============LICENSE_END============================================
  36
  37
  38   -->
  39 <!DOCTYPE hibernate-mapping PUBLIC
  40         "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  41         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
  42
  43 <!-- Publishes mappings and queries specific to the ONAP Portal application. -->
  44 <hibernate-mapping package="org.onap.portalapp.portal.domain">
  45
  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>
  51                         </generator>
  52                 </id>
  53                 <property name="languageName" column="language_name" />
  54                 <property name="languageAlias" column="language_alias" />
  55         </class>
  56
  57         <class name="DisplayText" table="fn_display_text">
  58                 <id name="id" column="id">
  59                         <generator class="native">
  60                                 <param name="sequence"></param>
  61                         </generator>
  62                 </id>
  63
  64                 <property name="languageId" column="language_id" />
  65                 <property name="textId" column="text_id" />
  66                 <property name="label" column="text_label" />
  67         </class>
  68
  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>
  74                         </generator>
  75                 </id>
  76                 <property name="width" type="integer">
  77                         <column name="WDG_WIDTH" not-null="true" default="0"></column>
  78                 </property>
  79                 <property name="height" type="integer">
  80                         <column name="WDG_HEIGHT" not-null="true" default="0"></column>
  81                 </property>
  82                 <property name="url" type="string">
  83                         <column name="WDG_URL" not-null="true" default="?"></column>
  84                 </property>
  85                 <property name="name" type="string">
  86                         <column name="WDG_NAME" not-null="true" default="?"></column>
  87                 </property>
  88
  89                 <property name="appId" type="long">
  90                         <column name="APP_ID" not-null="true"></column>
  91                 </property>
  92
  93         </class>
  94
  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>
  99                         </generator>
 100                 </id>
 101                 <property name="name" type="string">
 102                         <column name="url" not-null="true"></column>
 103                 </property>
 104         </class>
 105
 106
 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>
 111                         </generator>
 112                 </id>
 113                 <property name="ep_id" type="long">
 114                         <column name="ep_id"></column>
 115                 </property>
 116                 <property name="account_id" type="long">
 117                         <column name="account_id"></column>
 118                 </property>
 119         </class>
 120
 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>
 125                         </generator>
 126                 </id>
 127                 <property name="widgetId" type="long">
 128                         <column name="widget_id"></column>
 129                 </property>
 130                 <property name="userId" type="long">
 131                         <column name="user_id" not-null="true"></column>
 132                 </property>
 133                 <property name="paramId" type="long">
 134                         <column name="param_id" not-null="true"></column>
 135                 </property>
 136
 137                 <property name="user_value" type="string">
 138                         <column name="user_value" not-null="true"></column>
 139                 </property>
 140
 141         </class>
 142
 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>
 147                         </generator>
 148         </id>
 149                 <property name="applicationName" type="string">
 150                         <column name="ext_app_name" not-null="true" default="?"></column>
 151                 </property>
 152                 <property name="username" type="string">
 153                         <column name="username"></column>
 154                 </property>
 155                 <property name="password" type="string">
 156                         <column name="password"></column>
 157                 </property>
 158                 <property name="isActive" type="string">
 159                         <column name="active_yn" not-null="true" default="Y"></column>
 160                 </property>
 161     </class>
 162
 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>
 168                         </generator>
 169                 </id>
 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" />
 174         </class>
 175
 176
 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>
 182                         </generator>
 183                 </id>
 184
 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" />
 196
 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" />
 203
 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" />
 210
 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" />
 222
 223                 <property name="active" column="active_yn" type="yes_no" />
 224                 <property name="internal" column="is_internal_yn" type="yes_no" />
 225
 226                 <property name="created" type="timestamp" column="created_date" />
 227                 <property name="modified" type="timestamp" column="modified_date" />
 228
 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" />
 234
 235                 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
 236                         inverse="true">
 237                         <key column="user_id" />
 238                         <one-to-many class="org.onap.portalapp.portal.domain.EPUserApp" />
 239                 </set>
 240
 241         </class>
 242
 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>
 247                         </generator>
 248                 </id>
 249
 250                 <property name="name" type="string">
 251                         <column name="name" not-null="true" default="?"></column>
 252                 </property>
 253
 254                 <property name="desc" type="string">
 255                         <column name="description" not-null="true" default="?"></column>
 256                 </property>
 257
 258                 <property name="appId" type="long">
 259                         <column name="appId" not-null="true"></column>
 260                 </property>
 261
 262                 <property name="url" type="string">
 263                         <column name="endpoint_url" not-null="true" default="?"></column>
 264                 </property>
 265
 266                 <property name="securityType" type="string">
 267                         <column name="security_type" not-null="true" default="?"></column>
 268                 </property>
 269
 270
 271                 <property name="username" type="string">
 272                         <column name="username" not-null="true" default="?"></column>
 273                 </property>
 274
 275                 <property name="password" type="string">
 276                         <column name="password" not-null="true" default="?"></column>
 277                 </property>
 278
 279                 <property name="active" type="string">
 280                         <column name="active" not-null="true" default="Y"></column>
 281                 </property>
 282         </class>
 283
 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>
 288                         </generator>
 289                 </id>
 290
 291                 <property name="serviceId" type="long">
 292                         <column name="service_id" not-null="true"></column>
 293                 </property>
 294
 295                 <property name="para_key" type="string">
 296                         <column name="para_key" not-null="true" default="?"></column>
 297                 </property>
 298
 299                 <property name="para_value" type="string">
 300                         <column name="para_value" not-null="true" default="?"></column>
 301                 </property>
 302         </class>
 303
 304         <!-- UserApp class mapping details -->
 305         <class name="EPUserApp" table="fn_user_role">
 306                 <composite-id>
 307                         <key-property name="userId" type="long">
 308                                 <column name="user_id" precision="11" scale="0" />
 309                         </key-property>
 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" />
 314                 </composite-id>
 315                 <property name="priority">
 316                         <column name="priority" default="1" precision="4" scale="0" />
 317                 </property>
 318         </class>
 319
 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>
 325                         </generator>
 326                 </id>
 327                 <property name="name" type="string">
 328                         <column name="app_name" not-null="true" default="?"></column>
 329                 </property>
 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>
 338                 </property>
 339                 <property name="mlAppAdminId" type="string">
 340                         <column name="ml_app_admin_id" not-null="true" default="?"></column>
 341                 </property>
 342                 <property name="motsId" column="mots_id" />
 343                 <property name="appBasicAuthPassword" type="string">
 344                         <column name="app_password" not-null="true" default="?"></column>
 345                 </property>
 346                 <property name="thumbnail" column="thumbnail" />
 347                 <property name="appBasicAuthUsername" type="string">
 348                         <column name="app_username" not-null="true" default="?"></column>
 349                 </property>
 350                 <property name="open" type="yes_no">
 351                         <column name="open" not-null="true" default="Y"></column>
 352                 </property>
 353                 <property name="enabled" type="yes_no">
 354                         <column name="enabled" not-null="true" default="N"></column>
 355                 </property>
 356                 <property name="uebTopicName" type="string">
 357                         <column name="ueb_topic_name"></column>
 358                 </property>
 359                 <property name="uebKey" type="string">
 360                         <column name="ueb_key"></column>
 361                 </property>
 362                 <property name="uebSecret" type="string">
 363                         <column name="ueb_secret"></column>
 364                 </property>
 365                 <property name="appType" type="integer">
 366                         <column name="app_type"></column>
 367                 </property>
 368                 <property name="rolesInAAF" type="yes_no">
 369                         <column name="auth_central"></column>
 370                 </property>
 371                 <property name="nameSpace" type="string">
 372                         <column name="auth_namespace"></column>
 373                 </property>
 374                 <property name="modeOfIntegration" type="string">
 375                         <column name="mode_of_integration"></column>
 376                 </property>
 377                 <property name="appAck" type="yes_no">
 378                         <column name="ack_app"></column>
 379                 </property>
 380                 <property name="usesCadi" type="yes_no">
 381                         <column name="uses_cadi"></column>
 382                 </property>
 383
 384                 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
 385
 386         </class>
 387
 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>
 393                         </generator>
 394                 </id>
 395                 <property name="userId" column="USER_ID" />
 396                 <property name="sortPref" column="SORT_PREF" />
 397         </class>
 398
 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>
 404                         </generator>
 405                 </id>
 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" />
 411         </class>
 412
 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>
 417                         </generator>
 418                 </id>
 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" />
 425         </class>
 426
 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>
 432                         </generator>
 433                 </id>
 434
 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 -->
 442
 443                 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
 444                         sort="natural">
 445                         <key column="role_id" />
 446                         <many-to-many column="function_cd"
 447                                 class="org.onap.portalsdk.core.domain.RoleFunction" />
 448                 </set>
 449
 450                 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 451                         sort="natural">
 452                         <key column="parent_role_id" />
 453                         <many-to-many column="child_role_id"
 454                                 class="org.onap.portalapp.portal.domain.EPRole" />
 455                 </set>
 456
 457                 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 458                         sort="natural">
 459                         <key column="child_role_id" />
 460                         <many-to-many column="parent_role_id"
 461                                 class="org.onap.portalapp.portal.domain.EPRole" />
 462                 </set>
 463
 464         </class>
 465
 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>
 471                         </generator>
 472                 </id>
 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" />
 478
 479                 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
 480
 481         </class>
 482
 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>
 488                         </generator>
 489                 </id>
 490                 <property name="userId" column="user_id" />
 491                 <property name="appId" column="app_id" />
 492                 <property name="statusCode" column="status_cd" />
 493         </class>
 494
 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>
 500                         </generator>
 501                 </id>
 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>
 506         </class>
 507
 508         <!-- CentralRoleFunction class mapping details -->
 509         <class name="CentralV2RoleFunction" table="ep_app_function">
 510                 <composite-id>
 511                         <key-property name="appId"
 512                                 column="app_id" />
 513                         <key-property name="code"
 514                                 column="function_cd" />
 515                 </composite-id>
 516                 <property name="name" column="function_name" />
 517         </class>
 518
 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>
 523                         </generator>
 524                 </id>
 525                 <property name="userId" column="user_id" />
 526                 <property name="widgetId" column="widget_id" />
 527                 <property name="statusCode" column="status_cd" />
 528         </class>
 529
 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>
 535                         </generator>
 536                 </id>
 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" />
 542
 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" />
 546                         <one-to-many
 547                                 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
 548                 </set>
 549         </class>
 550
 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>
 556                         </generator>
 557                 </id>
 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" />
 563                 </many-to-one>
 564         </class>
 565
 566         <!-- multilanguage -->
 567         <query name="queryLanguage">
 568                 <![CDATA[
 569                         from Language
 570                 ]]>
 571         </query>
 572
 573         <query name="displayText">
 574                 <![CDATA[
 575                         from DisplayText where languageId = :language_id
 576                 ]]>
 577         </query>
 578
 579         <query name="queryLanguageByLanguageId">
 580                 <![CDATA[
 581                         from Language where languageId = :language_id
 582                 ]]>
 583         </query>
 584
 585         <query name="getActiveUsersForApp">
 586                 <![CDATA[
 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
 588                 ]]>
 589         </query>
 590
 591         <!-- show the current user plus related users -->
 592         <sql-query name="relatedUsers">
 593                 <return-scalar column="org_user_id" type="java.lang.String" />
 594                 <![CDATA[
 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
 598                                                 fn_user_role a,
 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,
 600                                                 fn_user c
 601                                                 where a.ROLE_ID = b.role_id
 602                                                 and a.APP_ID = b.app_id
 603                                                 and a.USER_ID = c.user_id
 604                                         union
 605                                         select org_user_id , last_login_date from fn_user where org_user_id=:userId
 606                                         ) c
 607                                 order by c.last_login_date desc limit 10
 608                         ) t
 609                 ]]>
 610         </sql-query>
 611
 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" />
 616                 <![CDATA[
 617                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 618                                 ( (
 619                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
 620                                 from fn_user where
 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, '%'))
 625                                 or
 626                         ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
 627                         AND
 628                        last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
 629                         OR
 630                        (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
 631                          AND
 632                        first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
 633                                 and  upper(active_yn) = 'Y'
 634                                 )
 635                                 union
 636                                 (
 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
 640                                 from
 641                                 (
 642                                 select c.*
 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
 654                                 )
 655                                 union
 656                                 (
 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
 660                                 from
 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
 672                                 (
 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
 678                                 and c.enabled = 'Y'
 679                                 ) b1
 680                                 on a1.menu_id = b1.menu_id
 681                                 )
 682                                 union
 683                                 select distinct CATEGORY, NAME, UUID, TARGET
 684                                 from
 685                                 (
 686                                 (
 687                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 688                                 d.widget_id UUID,
 689                                 d.wdg_desc TARGET
 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, '%')))
 697                                 )
 698                                 union
 699                                 (
 700                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 701                                 d.widget_id UUID,
 702                                 d.wdg_desc TARGET
 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, '%')))
 706                                 )
 707                                 union
 708                                 (
 709                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 710                                 d.widget_id UUID,
 711                                 d.wdg_desc TARGET
 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
 715                                 and b.role_id = 1
 716                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 717                                 )
 718                                 ) y
 719                                 ) t,
 720                                 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
 721                                                                         ;
 722
 723                 ]]>
 724         </sql-query>
 725
 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" />
 730                 <![CDATA[
 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
 732                         (
 733
 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
 735                         from
 736                                 (
 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
 738                                 from
 739                                 (
 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'
 747                                 ) a,
 748                                 (
 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)
 756                                 )b
 757                                 where
 758                                 (
 759                                 a.role_id = b.role_id
 760                                 )
 761                                 union
 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
 763                                 from
 764                                 (
 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'
 772                                 ) a
 773                                 where
 774                                 (
 775                                 a.recv_user_id=:user_id
 776                                 )
 777                                 union
 778                                 (
 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'
 785                                 )
 786                                 ) a
 787                                 where
 788                                         active_YN = 'Y'
 789                                 and
 790                                         not exists
 791                                 (
 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'
 793                                 )
 794                                 order by priority desc, created_date desc,start_Time desc
 795
 796
 797                          ) t,
 798                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 799                         ;
 800                 ]]>
 801
 802         </sql-query>
 803                 <sql-query name="getUsersByOrgIdsNotifications">
 804                 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
 805                 <![CDATA[
 806                         SELECT * from fn_user where org_user_id in (:OrgIds)
 807                 ]]>
 808
 809         </sql-query>
 810
 811
 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" />
 816                 <![CDATA[
 817                 SELECT * from (
 818 SELECT
 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,
 828                         n.priority,
 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
 833                 FROM
 834                         ep_notification n
 835                 LEFT JOIN
 836                         fn_user u on u.user_id = n.creator_id
 837                 WHERE
 838                         n.active_YN='Y' and msg_source='EP'
 839
 840       UNION
 841
 842       SELECT
 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,
 852                         n.priority,
 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
 857                 FROM
 858                         ep_notification n
 859                 LEFT JOIN
 860                         fn_user u on u.user_id = n.creator_id
 861     JOIN
 862                         ep_role_notification r on r.notification_ID=n.notification_ID
 863                 WHERE
 864                         n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
 865       )n order by n.startTime desc
 866                 ]]>
 867         </sql-query>
 868
 869         <sql-query name="getEpNotificationAppRoles">
 870                 <return alias="notificationAppRoles"
 871                         class="org.onap.portalapp.portal.domain.EcompAppRole" />
 872                 <!-- This query requires no parameters. -->
 873                 <![CDATA[
 874
 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
 878 union
 879 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
 880 from fn_app, fn_role
 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
 882
 883
 884
 885                 ]]>
 886         </sql-query>
 887
 888         <!-- Gets all notification history -->
 889         <sql-query name="getNotificationHistoryVO">
 890                 <return alias="notificationHistoryVOResult"
 891                         class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
 892                 <![CDATA[
 893                 SELECT
 894                         notificationId, isForOnlineUsers, isForAllRoles, msgHeader,     msgDescription,msgSource,
 895                         startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId,     activeYn
 896                 FROM
 897                         (
 898                                         select distinct
 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,
 906                                         end_time AS endTime,
 907                                         priority,
 908                                         created_date AS createdDate,
 909                                         creator_ID AS creatorId,
 910                                         notification_hyperlink AS notificationHyperlink,
 911                                         login_id AS loginId,
 912                                         active_YN AS activeYn,
 913                                         if (is_viewed is null, 'N', is_viewed)
 914                         from
 915                         (
 916                                 select
 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
 920                                 from
 921                                 (
 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
 925
 926                    ELSE NULL
 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'
 931                                 and (
 932                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 933                                 or
 934                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 935                                 or
 936                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 937                                 or
 938                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 939                                 )
 940                                 ) a,
 941                                 (
 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)
 949                                 ) b
 950                                 where
 951                                 (
 952                                 a.role_id = b.role_id
 953                                 )
 954                                    UNION
 955                         select
 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
 959                                 from
 960                                 (
 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
 964
 965                    ELSE NULL
 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'
 970                                 and (
 971                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 972                                 or
 973                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 974                                 or
 975                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 976                                 or
 977                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 978                                 )
 979                                 ) a
 980                         where
 981                         (
 982                         a.recv_user_id=:user_id
 983                         )
 984                                 union
 985                                 (
 986                                 select
 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'
 993                                 and (
 994                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 995                                 or
 996                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 997                                 or
 998                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 999                                 or
1000                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
1001                                 )
1002                                 )
1003                                 ) a left outer join (
1004                                 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
1005                                 ) m
1006                                 on  a.notification_id = m.notification_ID
1007                                 where
1008                                 active_YN = 'Y'
1009
1010                                 order by  start_Time desc,end_time desc
1011                         ) t,
1012                      (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
1013                 ]]>
1014
1015         </sql-query>
1016
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" />
1020                 <![CDATA[
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
1022                 ]]>
1023         </sql-query>
1024
1025
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" />
1030                 <![CDATA[
1031                         SELECT *
1032                         FROM
1033                                 ep_role_notification
1034                         WHERE
1035                                 notification_Id = :notificationId
1036                 ]]>
1037         </sql-query>
1038
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. -->
1044                 <![CDATA[
1045                         select
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
1050                         from
1051                                 fn_app a
1052                         left join
1053                                 fn_app_contact_us c
1054                                 on a.app_id = c.app_id
1055                         where
1056                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1057                         ;
1058                 ]]>
1059         </sql-query>
1060
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. -->
1066                 <![CDATA[
1067                         select
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
1071                         from
1072                                 fn_app_contact_us c
1073                         left join
1074                                 fn_app a
1075                                 on a.app_id = c.app_id
1076                         where
1077                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1078                         ;
1079                 ]]>
1080         </sql-query>
1081
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. -->
1087                 <![CDATA[
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
1091                         FROM (
1092                                 SELECT
1093                                 app_id, function_text, app_name,
1094                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1095                                 FROM (
1096                                         SELECT
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
1099                                         FROM (
1100                                                 SELECT distinct
1101                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1102                                                 FROM
1103                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1104                                                 WHERE
1105                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1106                                                 ) j,
1107                                         fn_menu_functional k
1108                                         WHERE j.parent_menu_id = k.menu_id
1109                                         ) fn,
1110                                 fn_menu_functional a
1111                                 WHERE fn.parent_menu_id = a.menu_id
1112                         ) r,
1113                         (SELECT @rn /*'*/:=/*'*/ 0) t2
1114                         group by r.category, r.app_id, r.app_name
1115                         order by category, app_name
1116                         ;
1117                 ]]>
1118         </sql-query>
1119
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. -->
1125                 <![CDATA[
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
1129                         from
1130                         (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1131                         from
1132                         (
1133                         select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1134                         from
1135                         FN_ROLE C, FN_APP D
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'
1139                         ) a left outer join
1140                         (
1141                         select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1142                         WHERE
1143                         A.MENU_ID = B.MENU_ID
1144                         and active_yn = 'Y'
1145                         )b
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
1154                         ;
1155                 ]]>
1156         </sql-query>
1157
1158         <query name="getCommonWidgetItem">
1159                 from CommonWidget where category = :cat order by
1160                 sortOrder, title
1161         </query>
1162
1163         <sql-query name="getGuestLastLogin">
1164                 <return-scalar column="audit_date" type="java.util.Date" />
1165                 <![CDATA[
1166                         select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1167                 ]]>
1168         </sql-query>
1169
1170         <sql-query name="getActiveUsers">
1171                 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1172                 <![CDATA[
1173                 SELECT * FROM FN_USER WHERE active_yn='Y'
1174                 ]]>
1175         </sql-query>
1176
1177         <sql-query name="getAppsAdmins">
1178                 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1179         <![CDATA[
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
1181         ]]>
1182         </sql-query>
1183
1184         <sql-query name="getPortalAdmins">
1185                 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1186         <![CDATA[
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
1188         ]]>
1189         </sql-query>
1190
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) -->
1195                 <![CDATA[
1196                     select
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
1203                     from FN_APP a
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'
1208                     and (
1209                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1210                         or
1211                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1212                         )
1213                         order by app_name
1214                     ;
1215                 ]]>
1216         </sql-query>
1217
1218         <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1219                 admin -->
1220         <sql-query name="getPersAdminApps">
1221                 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1222                 <!-- This query requires one parameter: userId (number) -->
1223                 <![CDATA[
1224                     select
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 ,
1231                                 a.AUTH_NAMESPACE
1232                     from FN_APP a
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'
1237                     and (
1238                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1239                         or
1240                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1241                                 or
1242                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1243                         )
1244                         order by app_name
1245                     ;
1246                 ]]>
1247         </sql-query>
1248
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) -->
1253                 <![CDATA[
1254                  select
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
1262                     from FN_APP a
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'
1267                     and (
1268                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1269                         or
1270                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1271                                 or
1272                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1273                         )
1274                         union
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
1284                   order by app_name
1285                   ;
1286
1287                 ]]>
1288         </sql-query>
1289
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) -->
1294                 <![CDATA[
1295                 select
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
1303                     from FN_APP a
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'
1308                     and (
1309                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1310                         or
1311                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1312                         )
1313                         union
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
1323                 order by app_name
1324                  ;
1325
1326                 ]]>
1327         </sql-query>
1328
1329
1330         <!--Gets personalized list of enabled and accessible Super Admin apps by
1331                 LastUsed -->
1332         <sql-query name="getAdminAppsOrderByLastUsed">
1333                 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1334                 <![CDATA[
1335                     select
1336                         *
1337                         from (select
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 ,
1344                                 a.AUTH_NAMESPACE
1345                     from FN_APP a
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'
1350                     and (
1351                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1352                         or
1353                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1354                                 or
1355                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1356                         )
1357                         union
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 ,
1364           b.AUTH_NAMESPACE
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
1367                         ) A
1368                 left outer join
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
1375                          )B
1376                         on A.app_id = B.Affected_record_id
1377                         order by AUDIT_DATE DESC;
1378                         ]]>
1379         </sql-query>
1380
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" />
1384                 <![CDATA[
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 ,
1392                                 a.AUTH_NAMESPACE
1393                     from FN_APP a
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'
1398                     and (
1399                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1400                         or
1401                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1402                         )union
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 ,
1409           b.AUTH_NAMESPACE
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
1412
1413                         ) A
1414                 left outer join
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
1421                          )B
1422                         on A.app_id = B.Affected_record_id
1423                         order by AUDIT_DATE DESC;
1424
1425
1426                 ]]>
1427
1428         </sql-query>
1429
1430         <!--Gets personalized list of enabled and accessible Super Admin apps by
1431                 Most Used -->
1432         <sql-query name="getAdminAppsOrderByMostUsed">
1433                 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1434                 <![CDATA[
1435                         select
1436                         *
1437                         from (select
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 ,
1444                                 a.AUTH_NAMESPACE
1445                     from FN_APP a
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'
1450                     and (
1451                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1452                         or
1453                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1454                                 or
1455                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1456                         )union
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 ,
1463           b.AUTH_NAMESPACE
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
1466                         ) A
1467                         left outer join
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
1474                          )B
1475                         on A.app_id = B.Affected_record_id
1476                         order by total_visits DESC;
1477                 ]]>
1478
1479         </sql-query>
1480
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" />
1485                 <![CDATA[
1486                         select * from
1487                         (select
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 ,
1494                                 a.AUTH_NAMESPACE
1495                     from FN_APP a
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'
1500                     and (
1501                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1502                         or
1503                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1504                         )union
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 ,
1511           b.AUTH_NAMESPACE
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
1514                         )A
1515                         left outer join
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
1522                          )B
1523                         on A.app_id = B.Affected_record_id
1524                         order by total_visits DESC;
1525                 ]]>
1526         </sql-query>
1527
1528         <!--Gets personalized list of enabled and accessible Super Admin apps by
1529                 Manual -->
1530         <sql-query name="getAdminAppsOrderByManual">
1531                 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1532                 <![CDATA[
1533                         select * from
1534                         (select
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 ,
1541                                 a.AUTH_NAMESPACE
1542                     from FN_APP a
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'
1547                     and (
1548                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1549                         or
1550                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1551                                 or
1552                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1553                         )union
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 ,
1560           b.AUTH_NAMESPACE
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
1563                         ) A
1564                         left outer join
1565                          (select m.app_id, m.sort_order
1566                         from ep_pers_user_app_man_sort m
1567                         where USER_ID = :userId
1568                         ) B
1569                         on A.APP_ID = B.app_id
1570                         order by sort_order ASC
1571                     ;
1572                 ]]>
1573
1574         </sql-query>
1575
1576         <!--Gets personalized list of enabled and accessible NON Super admin User
1577                 apps by Manual -->
1578         <sql-query name="getUserAppsOrderByManual">
1579                 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1580                 <![CDATA[
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 ,
1588                                 a.AUTH_NAMESPACE
1589                     from FN_APP a
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'
1594                     and (
1595                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1596                         or
1597                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1598                         )union
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 ,
1605           b.AUTH_NAMESPACE
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
1608                         ) A
1609                         left outer join
1610                          (select m.app_id, m.sort_order
1611                         from ep_pers_user_app_man_sort m
1612                         where USER_ID = :userId
1613                         ) B
1614                         on A.APP_ID = B.app_id
1615                         order by sort_order ASC
1616                     ;
1617                 ]]>
1618         </sql-query>
1619
1620         <!-- Gets regular user's list of enabled apps including accessible and select
1621                 statuses -->
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) -->
1626                 <![CDATA[
1627                                                 select
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,
1633                                 case
1634                                         when a.app_type = '2' and a.app_type = '3' then TRUE
1635                                         else FALSE
1636                                 end as restricted,
1637                             IF(a.open = 'Y', TRUE, FALSE) as open,
1638                 -- ACCESS(-ible) means user has a defined role OR the application is open
1639                                 case
1640                                         -- regular app and user has a role
1641                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1642                     -- open app
1643                     when a.OPEN = 'Y' then TRUE
1644                     else FALSE
1645                 end as 'access' ,
1646                 -- SELECT(-ed) indicates user personalization
1647                 case
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
1652                     else FALSE
1653                 end as 'select' ,
1654                 -- PENDING indicates user checked a box
1655                 case
1656                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1657                         else FALSE
1658                 end as 'pending'
1659                     from FN_APP a
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'
1665
1666                         -- Show accessible apps first, then the rest; sort by name within each set.
1667             order by access desc, app_name asc
1668                     ;
1669                 ]]>
1670         </sql-query>
1671
1672         <sql-query name="getMyloginAppDetails">
1673                 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1674                 <![CDATA[
1675                 SELECT * FROM fn_app  where ml_app_name =:appName
1676                 ;
1677                 ]]>
1678         </sql-query>
1679
1680         <sql-query name="getAppDetailsByAppName">
1681                 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1682                 <![CDATA[
1683                 SELECT * FROM fn_app  where app_name =:appName
1684                 ;
1685                 ]]>
1686         </sql-query>
1687
1688                 <sql-query name="getMyAppDetailsByUebKey">
1689                 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1690                 <![CDATA[
1691                 SELECT * FROM fn_app  where ueb_key =:appKey
1692                 ;
1693                 ]]>
1694         </sql-query>
1695
1696
1697         <sql-query name="deleteAccountEndpoint">
1698                 <![CDATA[
1699                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1700                 ;
1701                 ]]>
1702         </sql-query>
1703
1704         <sql-query name="deleteAccountEndpointRecord">
1705                 <![CDATA[
1706                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1707                 ;
1708                 ]]>
1709         </sql-query>
1710
1711         <sql-query name="deleteEPEndpoint">
1712                 <![CDATA[
1713                 DELETE FROM ep_endpoints WHERE id =:epId
1714                 ;
1715                 ]]>
1716         </sql-query>
1717
1718         <sql-query name="deleteBasicAuthAccount">
1719                 <![CDATA[
1720                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1721                 ;
1722                 ]]>
1723         </sql-query>
1724
1725         <sql-query name="getAppRoles">
1726                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1727                 <![CDATA[
1728                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1729                 ;
1730                 ]]>
1731         </sql-query>
1732
1733         <sql-query name="getPartnerAppRolesList">
1734                 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1735                 <![CDATA[
1736                 SELECT * FROM fn_role where app_id =:appId
1737                 ;
1738                 ]]>
1739         </sql-query>
1740
1741         <sql-query name="getPortalAppRolesList">
1742                 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1743                 <![CDATA[
1744                 SELECT * FROM fn_role where app_id is null
1745                 ;
1746                 ]]>
1747         </sql-query>
1748
1749         <sql-query name="getPortalAppRoleByRoleId">
1750                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1751                 <![CDATA[
1752                 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1753                 ;
1754                 ]]>
1755         </sql-query>
1756
1757         <sql-query name="getPartnerAppRoleByRoleId">
1758                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1759                 <![CDATA[
1760                 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1761                 ;
1762                 ]]>
1763         </sql-query>
1764
1765         <sql-query name="getPartnerAppRoleById">
1766                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1767                 <![CDATA[
1768                 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1769                 ;
1770                 ]]>
1771         </sql-query>
1772
1773         <sql-query name="getPortalAppRoles">
1774                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1775                 <![CDATA[
1776                 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1777                 ;
1778                 ]]>
1779         </sql-query>
1780
1781         <sql-query name="deleteMicroserviceParameter">
1782                 <![CDATA[
1783                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1784                 ;
1785                 ]]>
1786         </sql-query>
1787
1788         <sql-query name="deleteMicroserviceParameterById">
1789                 <![CDATA[
1790                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1791                 ;
1792                 ]]>
1793         </sql-query>
1794
1795         <sql-query name="deleteWidgetCatalogParameter">
1796                 <![CDATA[
1797                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1798                 ;
1799                 ]]>
1800         </sql-query>
1801
1802         <sql-query name="deleteMicroservice">
1803                 <![CDATA[
1804                 DELETE FROM ep_microservice WHERE id =:serviceId
1805                 ;
1806                 ]]>
1807         </sql-query>
1808
1809         <sql-query name="epUserAppId">
1810                 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1811                 <![CDATA[
1812                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1813                 ;
1814                 ]]>
1815         </sql-query>
1816
1817         <sql-query name="userAppsSortPreferenceQuery">
1818                 <return alias="appsSortPreferenceQuery"
1819                         class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1820                 <![CDATA[
1821                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1822
1823                 ]]>
1824         </sql-query>
1825
1826         <sql-query name="userAppsManualSortPrfQuery">
1827                 <return alias="AppsManualSortPrfQuery"
1828                         class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1829                 <![CDATA[
1830                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1831
1832                 ]]>
1833         </sql-query>
1834
1835         <sql-query name="userWidgetManualSortPrfQuery">
1836                 <return alias="widgetManualSortPrfQuery"
1837                         class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1838                 <![CDATA[
1839                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1840                 ]]>
1841         </sql-query>
1842
1843         <sql-query name="appRoles">
1844                 <return alias="rolesForApp"
1845                         class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1846                 <![CDATA[
1847                         SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1848                         ;
1849                 ]]>
1850         </sql-query>
1851
1852         <sql-query name="userAppRolesRequestList">
1853                 <return alias="appRolesRequestList"
1854                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1855                 <![CDATA[
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'
1857                         ;
1858                 ]]>
1859         </sql-query>
1860
1861         <sql-query name="userAppRolesRequestDetailList">
1862                 <return alias="appRolesRequestDetailList"
1863                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1864                 <![CDATA[
1865
1866                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1867                         ;
1868                 ]]>
1869         </sql-query>
1870
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) -->
1876                 <![CDATA[
1877                         select
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
1885                                 case
1886                                         -- regular app and user has a role
1887                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1888                     -- open app
1889                     when a.OPEN = 'Y' then TRUE
1890                     else FALSE
1891                 end as 'access' ,
1892                 -- SELECT(-ed) indicates user personalization
1893                 case
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
1902                     else FALSE
1903                 end as 'select' ,
1904                 -- PENDING indicates user checked a box
1905                 case
1906                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1907                         else FALSE
1908                 end as 'pending'
1909                     from FN_APP a
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
1917                     ;
1918                 ]]>
1919         </sql-query>
1920
1921         <sql-query name="userAppCatalogRoles">
1922                 <return alias="epUserAppCatalogRoles"
1923                         class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1924                 <![CDATA[
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'
1931        ;
1932                 ]]>
1933         </sql-query>
1934
1935         <sql-query name="getUserApproles">
1936                 <return alias="businessCardUserApplicationRoles"
1937                         class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1938                 <![CDATA[
1939
1940                 select   fr.role_name, fa.app_name
1941           from
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)
1944           ;
1945
1946                 ]]>
1947         </sql-query>
1948
1949     <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1950         <sql-query name="deleteNotificationsFromEpNotificationTable">
1951                 <![CDATA[
1952                 delete from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1953                 ]]>
1954         </sql-query>
1955
1956
1957                 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1958         <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1959                 <![CDATA[
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))
1961                 ]]>
1962         </sql-query>
1963
1964         <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1965         <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1966                 <![CDATA[
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))
1968                 ]]>
1969         </sql-query>
1970
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" />
1975                 <![CDATA[
1976
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
1979                         ;
1980
1981                 ]]>
1982         </sql-query>
1983
1984         <!-- Gets list of all role functions -->
1985         <sql-query name="getAllRoleFunctions">
1986                 <return alias="allRoleFunctions"
1987                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1988                 <![CDATA[
1989
1990                         SELECT * from ep_app_function where app_id =:appId
1991                         ;
1992
1993                 ]]>
1994         </sql-query>
1995
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" />
2000                         <![CDATA[
2001
2002                         SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
2003                         ;
2004
2005                         ]]>
2006         </sql-query>
2007
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" />
2012                         <![CDATA[
2013
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'
2015                         ;
2016                         ]]>
2017         </sql-query>
2018
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" />
2023                         <![CDATA[
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
2027                         ;
2028                         ]]>
2029         </sql-query>
2030
2031         <!-- Gets all functions of portal app -->
2032         <sql-query name="getAllFunctions">
2033                 <return alias="allFunctions"
2034                         class="org.onap.portalsdk.core.domain.RoleFunction" />
2035                         <![CDATA[
2036                         select * from fn_function
2037                         ;
2038                         ]]>
2039         </sql-query>
2040
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" />
2045                         <![CDATA[
2046                         select * from ep_app_function where app_id =:appId
2047                         ;
2048                         ]]>
2049         </sql-query>
2050
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" />
2055                         <![CDATA[
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
2057                                 ;
2058                         ]]>
2059         </sql-query>
2060
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" />
2065                         <![CDATA[
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
2067                                 ;
2068                         ]]>
2069         </sql-query>
2070
2071         <sql-query name="getRoletoUpdateInExternalAuthSystem">
2072                 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2073                         <![CDATA[
2074                                         SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2075                         ;
2076                         ]]>
2077         </sql-query>
2078
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" />
2082                 <![CDATA[
2083                 select * from fn_role where active_yn = 'Y' and app_id=:appId
2084                 ;
2085                 ]]>
2086         </sql-query>
2087
2088         <sql-query name="getBulkUserRoles">
2089                 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2090                         <![CDATA[
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
2095                         ;
2096                         ]]>
2097         </sql-query>
2098
2099         <sql-query name="getBulkUsersForSingleRole">
2100                 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2101                         <![CDATA[
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
2106                         ;
2107                         ]]>
2108         </sql-query>
2109
2110         <sql-query name="getCentralizedApps">
2111                 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2112                         <![CDATA[
2113                         select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2114                         ;
2115                         ]]>
2116         </sql-query>
2117
2118         <sql-query name="getUserRoles">
2119                         <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2120                 <![CDATA[
2121
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
2123          ;
2124                 ]]>
2125         </sql-query>
2126                 <sql-query name="getRoleFunctionsOfUser">
2127                         <![CDATA[
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
2133                     union
2134             select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2135                         where role_id = 999
2136                         and app_r_f.function_cd = a_f.function_cd
2137                         and exists
2138                         (
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'
2141                         );
2142                         ]]>
2143         </sql-query>
2144
2145         <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
2146                         <![CDATA[
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
2152                         and exists
2153                         (
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'
2156                         );
2157                         ]]>
2158         </sql-query>
2159
2160         <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
2161         <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2162                         <![CDATA[
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)
2166                         UNION
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 ;
2173                         ]]>
2174         </sql-query>
2175
2176         <sql-query name="getApplicationsofTheUserContainsApprover">
2177         <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2178                         <![CDATA[
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 ;
2185                         ]]>
2186         </sql-query>
2187
2188
2189
2190         <sql-query name="getUserRolesForLeftMenu">
2191                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2192
2193                 <![CDATA[
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)
2199         ;
2200                 ]]>
2201         </sql-query>
2202
2203         <sql-query name="getRolesForLeftMenu">
2204                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2205
2206                 <![CDATA[
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)
2212         ;
2213                 ]]>
2214         </sql-query>
2215
2216         <!-- Gets all functions for an application along with global functions and requires single parameter -->
2217         <sql-query name="getMenuFunctions">
2218                 <![CDATA[
2219                         select f.function_cd from ep_app_function f
2220                         where f.app_id =:appId
2221                         UNION
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
2224                  ;
2225                 ]]>
2226         </sql-query>
2227
2228         <sql-query name="getRequestIdsForApp">
2229         <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2230                 <![CDATA[
2231                 select * from ep_user_roles_request where app_id =:app_id
2232                 ;
2233                 ]]>
2234         </sql-query>
2235
2236         <sql-query name="ApplicationUserRoles">
2237                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2238                 <![CDATA[
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
2243            union
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';
2249                 ]]>
2250         </sql-query>
2251
2252         <sql-query name="getCurrentAppRoleFunctions">
2253                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2254                 <![CDATA[
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
2257                 ;
2258                 ]]>
2259         </sql-query>
2260
2261         <sql-query name="deleteUserWidgetPlacement">
2262                 <![CDATA[
2263                 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2264                 ;
2265                 ]]>
2266         </sql-query>
2267
2268         <sql-query name="getCentralizedAppsOfUser">
2269                 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2270                 <![CDATA[
2271
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)
2276                 ;
2277
2278                 ]]>
2279         </sql-query>
2280
2281     <query name="getEPUserByOrgUserId">
2282         <![CDATA[
2283                 FROM EPUser WHERE orgUserId = :org_user_id
2284         ]]>
2285     </query>
2286
2287         <query name="getEPUserByLoginId">
2288         <![CDATA[
2289                 FROM EPUser WHERE loginId = :login_id
2290         ]]>
2291     </query>
2292
2293     <query name="getEPUserByLoginIdLoginPwd">
2294         <![CDATA[
2295                 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2296         ]]>
2297     </query>
2298
2299     <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2300                 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2301                 <![CDATA[
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
2305                 ;
2306                 ]]>
2307         </sql-query>
2308
2309      <sql-query name="getGlobalRolesOfPortal">
2310                 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2311                 <![CDATA[
2312                 select * from fn_role where role_name  like 'global_%' and app_id is null and active_yn='Y'
2313                 ;
2314                 ]]>
2315         </sql-query>
2316
2317         <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2318                 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2319                 <![CDATA[
2320                 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2321                 ;
2322                 ]]>
2323         </sql-query>
2324
2325         <sql-query name="getAppFunctionOnCodeAndAppId">
2326                 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2327                 <![CDATA[
2328                 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2329                 ;
2330                 ]]>
2331         </sql-query>
2332
2333         <sql-query name="getGlobalRoleForRequestedApp">
2334                 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2335                 <![CDATA[
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
2339         ;
2340                 ]]>
2341         </sql-query>
2342
2343         <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2344                 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2345                 <![CDATA[
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
2349                 ;
2350                 ]]>
2351         </sql-query>
2352
2353         <sql-query name="updateMenuFunctionalAndRoles">
2354                 <![CDATA[
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
2356                  ;
2357                 ]]>
2358         </sql-query>
2359
2360         <!-- Remove any favorites associated with a menu item that is associated with this app -->
2361         <sql-query name="removeAppFromMenuFavorites">
2362                 <![CDATA[
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
2364                 ;
2365                 ]]>
2366         </sql-query>
2367
2368         <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2369         <sql-query name="removeAppFromMenuFunctionalRoles">
2370                 <![CDATA[
2371                 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2372                 ;
2373                 ]]>
2374         </sql-query>
2375
2376         <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2377         <sql-query name="removeAppFromEpAppRoleFunction">
2378                 <![CDATA[
2379                 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2380                 ;
2381                 ]]>
2382         </sql-query>
2383
2384         <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2385         <sql-query name="removeAppFromEpAppFunction">
2386                 <![CDATA[
2387                 DELETE FROM ep_app_function WHERE app_id=:app_id
2388                 ;
2389                 ]]>
2390         </sql-query>
2391
2392         <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2393         <sql-query name="removeAppFromFnUserRole">
2394                 <![CDATA[
2395                 DELETE FROM fn_user_role WHERE app_id=:app_id
2396                 ;
2397                 ]]>
2398         </sql-query>
2399
2400         <!-- Remove any widgets that is associated with this app-->
2401         <sql-query name="removeAppFromEpWidgetCatalogRole">
2402                 <![CDATA[
2403                 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2404                 ;
2405                 ]]>
2406         </sql-query>
2407
2408         <!-- Remove any notifications that is associated with this app-->
2409         <sql-query name="removeAppFromEpRoleNotification">
2410                 <![CDATA[
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
2413                 ;
2414                 ]]>
2415         </sql-query>
2416
2417         <!-- Remove all records from fn_role that is associated with this app-->
2418         <sql-query name="removeAppFromFnRole">
2419                 <![CDATA[
2420                 DELETE FROM fn_role where app_id=:app_id
2421                 ;
2422                 ]]>
2423         </sql-query>
2424
2425         <!-- Remove app contact us entries that is associated with this app-->
2426         <sql-query name="removeAppFromAppContactUs">
2427                 <![CDATA[
2428                 DELETE FROM fn_app_contact_us where app_id=:app_id
2429                 ;
2430                 ]]>
2431         </sql-query>
2432
2433         <!-- Remove app personalization entries that is associated with this app-->
2434         <sql-query name="removeAppFromEpPersUserAppSel">
2435                 <![CDATA[
2436                 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2437                 ;
2438                 ]]>
2439         </sql-query>
2440
2441         <!-- Remove app personalization sorting entries that is associated with this app-->
2442         <sql-query name="removeAppFromEpPersUserAppManSort">
2443                 <![CDATA[
2444                 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2445                 ;
2446                 ]]>
2447         </sql-query>
2448
2449         <!-- Remove rows from user role request table entries that is associated with this app-->
2450         <sql-query name="removeAppFromEpUserRolesRequest">
2451                 <![CDATA[
2452                 DELETE FROM ep_user_roles_request where app_id=:app_id
2453                 ;
2454                 ]]>
2455         </sql-query>
2456
2457         <!-- Remove rows from web analytics table entries that is associated with this app-->
2458         <sql-query name="removeAppFromEpWebAnalytics">
2459                 <![CDATA[
2460                 DELETE FROM ep_web_analytics_source where app_id=:app_id
2461                 ;
2462                 ]]>
2463         </sql-query>
2464
2465         <!-- Remove row from fn app table -->
2466         <sql-query name="removeAppFromFnApp">
2467                 <![CDATA[
2468                 DELETE FROM fn_app where app_id=:app_id
2469                 ;
2470                 ]]>
2471         </sql-query>
2472
2473
2474         <query name="getBasicauthAccount">
2475       select id,password from BasicAuthCredentials
2476     </query>
2477
2478      <query name="getMicroserviceInfo">
2479       select id,password from MicroserviceData
2480     </query>
2481
2482         <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2483                 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2484                 <![CDATA[
2485                 select * from fn_user_role where user_id= :userId
2486                 and role_id= :roleId
2487                 and app_id= :appId
2488                 ;
2489                 ]]>
2490         </sql-query>
2491
2492         <sql-query name="userAppGlobalRoles">
2493                 <return alias="userAppGlobalRoles"
2494                         class="org.onap.portalapp.portal.domain.EPRole" />
2495        <![CDATA[
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
2499         ;
2500        ]]>
2501         </sql-query>
2502
2503         <sql-query name="getAllCentralizedAppsRoles">
2504                 <return alias="allCentralizedAppsRoles"
2505                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2506        <![CDATA[
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 ;
2509        ]]>
2510         </sql-query>
2511
2512         <sql-query name="getUserCentralizedAppRoles">
2513                 <return alias="userCentralizedAppRoles"
2514                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2515        <![CDATA[
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'
2523                 order by role_id;
2524                 ]]>
2525         </sql-query>
2526         <sql-query name="getAprroverRoleFunctionsOfUser">
2527                 <![CDATA[
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|%'
2533                 and exists
2534                 (
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'
2537                 );
2538                 ]]>
2539 </sql-query>
2540 <sql-query name="getUserApproverRoles">
2541                 <![CDATA[
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|%'
2548                 and exists
2549                 (
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'
2552                 );
2553                 ]]>
2554 </sql-query>
2555 <sql-query name="getAdminAppsForTheUser">
2556                 <![CDATA[
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)
2558
2559
2560                 ]]>
2561 </sql-query>
2562
2563
2564 <sql-query name="getUserRoleOnUserIdAndAppId">
2565                 <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
2566                 <![CDATA[
2567
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'
2569
2570                 ]]>
2571         </sql-query>
2572
2573         <sql-query name="getAllAdminAppsofTheUser">
2574                 <![CDATA[
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
2576
2577                 ]]>
2578     </sql-query>
2579         <sql-query name="getAllAppsFunctionsOfUser">
2580                         <![CDATA[
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'
2588                     union
2589             select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2590                         where role_id = 999
2591                         and app_r_f.function_cd = a_f.function_cd
2592                         and exists
2593                         (
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'
2596                         );
2597                         ]]>
2598         </sql-query>
2599         <sql-query name="updateFnUser">
2600                 <![CDATA[
2601                         UPDATE fn_user fu SET fu.language_id=:language_id WHERE fu.login_id=:login_id
2602                 ]]>
2603         </sql-query>
2604 </hibernate-mapping>