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="description" column="app_description" />
 332                 <property name="notes" column="app_notes" />
 333                 <property name="url" column="app_url" />
 334                 <property name="alternateUrl" column="app_alternate_url" />
 335                 <property name="appRestEndpoint" column="app_rest_endpoint" />
 336                 <property name="mlAppName" type="string">
 337                         <column name="ml_app_name" not-null="true" default="?"></column>
 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="appPassword" type="string">
 344                         <column name="app_password" not-null="true" default="?"></column>
 345                 </property>
 346                 <property name="thumbnail" column="thumbnail" />
 347                 <property name="username" 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="centralAuth" 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                 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
 375
 376         </class>
 377
 378         <!-- EPUserAppsSortPreference class mapping details -->
 379         <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
 380                 <id name="id" column="ID">
 381                         <generator class="native">
 382                                 <param name="sequence">seq_ep_usrappsrtprf</param>
 383                         </generator>
 384                 </id>
 385                 <property name="userId" column="USER_ID" />
 386                 <property name="sortPref" column="SORT_PREF" />
 387         </class>
 388
 389         <!-- EPUserAppsManualSortPreference class mapping details -->
 390         <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
 391                 <id name="id" column="ID">
 392                         <generator class="native">
 393                                 <param name="sequence">seq_ep_usrmanappsrtprf</param>
 394                         </generator>
 395                 </id>
 396                 <property name="userId" column="user_id"
 397                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 398                 <property name="appId" column="app_id"
 399                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 400                 <property name="appManualSortOrder" column="sort_order" type="int" />
 401         </class>
 402
 403         <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
 404                 <id name="id" column="ID">
 405                         <generator class="native">
 406                                 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
 407                         </generator>
 408                 </id>
 409                 <property name="userId" column="user_id" />
 410                 <property name="widgetId" column="widget_id" />
 411                 <property name="widgetRow" column="x" />
 412                 <property name="widgetCol" column="y" />
 413                 <property name="widgetHeight" column="height" />
 414                 <property name="widgetWidth" column="width" />
 415         </class>
 416
 417         <!-- User Role class mapping details -->
 418         <class name="EPRole" table="FN_ROLE" >
 419                 <id name="id" column="role_id">
 420                         <generator class="native">
 421                                 <param name="sequence">seq_fn_role</param>
 422                         </generator>
 423                 </id>
 424
 425                 <property name="name" column="role_name" />
 426                 <property name="priority" column="priority" />
 427                 <property name="active" column="active_yn" type="yes_no" />
 428                 <!-- 2 lines below may be removed! -->
 429                 <property name="appId" column="APP_ID" />
 430                 <property name="appRoleId" column="APP_ROLE_ID" />
 431                 <!-- 2 lines above may be removed -->
 432
 433                 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
 434                         sort="natural">
 435                         <key column="role_id" />
 436                         <many-to-many column="function_cd"
 437                                 class="org.onap.portalsdk.core.domain.RoleFunction" />
 438                 </set>
 439
 440                 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 441                         sort="natural">
 442                         <key column="parent_role_id" />
 443                         <many-to-many column="child_role_id"
 444                                 class="org.onap.portalapp.portal.domain.EPRole" />
 445                 </set>
 446
 447                 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 448                         sort="natural">
 449                         <key column="child_role_id" />
 450                         <many-to-many column="parent_role_id"
 451                                 class="org.onap.portalapp.portal.domain.EPRole" />
 452                 </set>
 453
 454         </class>
 455
 456         <!-- User App class mapping details -->
 457         <class name="AppContactUs" table="fn_app_contact_us">
 458                 <id name="id" column="app_id">
 459                         <generator class="foreign">
 460                                 <param name="property">app</param>
 461                         </generator>
 462                 </id>
 463                 <property name="url" column="url" />
 464                 <property name="description" column="description" />
 465                 <property name="contactName" column="contact_name" />
 466                 <property name="contactEmail" column="contact_email" />
 467                 <property name="activeYN" column="active_yn" />
 468
 469                 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
 470
 471         </class>
 472
 473         <!-- Personalization of user app selections -->
 474         <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
 475                 <id name="id" column="id">
 476                         <generator class="native">
 477                                 <param name="property">seq_fn_pers_user_app_sel</param>
 478                         </generator>
 479                 </id>
 480                 <property name="userId" column="user_id" />
 481                 <property name="appId" column="app_id" />
 482                 <property name="statusCode" column="status_cd" />
 483         </class>
 484
 485         <!-- EPAppRoleFunction class mapping details -->
 486         <class name="EPAppRoleFunction" table="ep_app_role_function">
 487                 <id name="id" column="id">
 488                         <generator class="native">
 489                                 <param name="property">seq_epp_app_role_func</param>
 490                         </generator>
 491                 </id>
 492                 <property name="roleId" column="role_id"></property>
 493                 <property name="appId" column="app_id"></property>
 494                 <property name="code" column="function_cd"></property>
 495                 <property name="roleAppId" column="role_app_id"></property>
 496         </class>
 497
 498         <!-- CentralRoleFunction class mapping details -->
 499         <class name="CentralV2RoleFunction" table="ep_app_function">
 500                 <composite-id>
 501                         <key-property name="appId"
 502                                 column="app_id" />
 503                         <key-property name="code"
 504                                 column="function_cd" />
 505                 </composite-id>
 506                 <property name="name" column="function_name" />
 507         </class>
 508
 509         <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
 510                 <id name="id" column="id">
 511                         <generator class="native">
 512                                 <param name="property">seq_ep_pers_user_widget_sel</param>
 513                         </generator>
 514                 </id>
 515                 <property name="userId" column="user_id" />
 516                 <property name="widgetId" column="widget_id" />
 517                 <property name="statusCode" column="status_cd" />
 518         </class>
 519
 520         <!-- EPUserAppRolesRequest class mapping details -->
 521         <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
 522                 <id name="id" column="req_id">
 523                         <generator class="native">
 524                                 <param name="sequence">seq_ep_user_role_request</param>
 525                         </generator>
 526                 </id>
 527                 <property name="userId" column="user_id" />
 528                 <property name="appId" column="app_id" />
 529                 <property name="createdDate" column="created_date" type="timestamp" />
 530                 <property name="updatedDate" column="updated_date" type="timestamp" />
 531                 <property name="requestStatus" column="request_status" />
 532
 533                 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
 534                         lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
 535                         <key column="req_id" />
 536                         <one-to-many
 537                                 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
 538                 </set>
 539         </class>
 540
 541         <!-- EPUserAppRolesRequestDetail class mapping details -->
 542         <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
 543                 <id name="id" column="id">
 544                         <generator class="native">
 545                                 <param name="sequence">seq_ep_user_role_request_det</param>
 546                         </generator>
 547                 </id>
 548                 <property name="reqRoleId" column="requested_role_id" />
 549                 <property name="reqType" column="request_type" />
 550                 <many-to-one name="epRequestIdData" fetch="select"
 551                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest">
 552                         <column name="req_id" not-null="true" />
 553                 </many-to-one>
 554         </class>
 555
 556         <!-- multilanguage -->
 557         <query name="queryLanguage">
 558                 <![CDATA[
 559                         from Language
 560                 ]]>
 561         </query>
 562
 563         <query name="displayText">
 564                 <![CDATA[
 565                         from DisplayText where languageId = :language_id
 566                 ]]>
 567         </query>
 568
 569         <query name="queryLanguageByLanguageId">
 570                 <![CDATA[
 571                         from Language where languageId = :language_id
 572                 ]]>
 573         </query>
 574
 575         <query name="getActiveUsersForApp">
 576                 <![CDATA[
 577                         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
 578                 ]]>
 579         </query>
 580
 581         <!-- show the current user plus related users -->
 582         <sql-query name="relatedUsers">
 583                 <return-scalar column="org_user_id" type="java.lang.String" />
 584                 <![CDATA[
 585                         select t.org_user_id from (
 586                                 select distinct c.org_user_id org_user_id, c.last_login_date from
 587                                         (select c.org_user_id, c.last_login_date from
 588                                                 fn_user_role a,
 589                                                 (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,
 590                                                 fn_user c
 591                                                 where a.ROLE_ID = b.role_id
 592                                                 and a.APP_ID = b.app_id
 593                                                 and a.USER_ID = c.user_id
 594                                         union
 595                                         select org_user_id , last_login_date from fn_user where org_user_id=:userId
 596                                         ) c
 597                                 order by c.last_login_date desc limit 10
 598                         ) t
 599                 ]]>
 600         </sql-query>
 601
 602         <!-- requires values for named parameters :searchQuery and :userId -->
 603         <sql-query name="searchPortal">
 604                 <return alias="searchResult"
 605                         class="org.onap.portalapp.portal.ecomp.model.SearchResultItem" />
 606                 <![CDATA[
 607                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 608                                 ( (
 609                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
 610                                 from fn_user where
 611                                 upper( :searchQuery ) != ''
 612                                 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
 613                                 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
 614                                 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%'))
 615                                 or
 616                         ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
 617                         AND
 618                        last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
 619                         OR
 620                        (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
 621                          AND
 622                        first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
 623                                 and  upper(active_yn) = 'Y'
 624                                 )
 625                                 union
 626                                 (
 627                                 select distinct 'Application' CATEGORY, b1.app_name NAME,
 628                                 if (b1.app_type = 2,'false','true') UUID,
 629                                 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
 630                                 from
 631                                 (
 632                                 select c.*
 633                                 from fn_user_role a, fn_user b, fn_app c
 634                                 where a.USER_ID = b.user_id
 635                                 and upper(b.org_user_id) = upper(  :userId )
 636                                 and a.app_id = c.app_id
 637                                 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 638                                 and upper(c.ENABLED) = 'Y'
 639                                 ) a1 right outer join
 640                                 (select * from fn_app where
 641                                 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 642                                 and upper(ENABLED) = 'Y') b1
 643                                 on a1.APP_ID = b1.app_id
 644                                 )
 645                                 union
 646                                 (
 647                                 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
 648                                 if (b1.app_type = 2,'false','true') UUID,
 649                                 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
 650                                 from
 651                                 (select distinct  d.*, e.app_type from fn_user a,
 652                                 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
 653                                 where a.USER_ID = b.user_id
 654                                 and b.role_id = c.role_id
 655                                 and c.menu_id = d.menu_id
 656                                 and upper(a.org_user_id) = upper( :userId )
 657                                 and c.APP_ID = e.app_id
 658                                 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 659                                 and upper(d.active_yn) = 'Y'
 660                                 and upper(e.enabled) = 'Y'
 661                                 ) a1 right outer join
 662                                 (
 663                                 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
 664                                 where active_yn = 'Y'
 665                                 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 666                                 and a.menu_id = b.menu_id
 667                                 and b.app_id = c.app_id
 668                                 and c.enabled = 'Y'
 669                                 ) b1
 670                                 on a1.menu_id = b1.menu_id
 671                                 )
 672                                 union
 673                                 select distinct CATEGORY, NAME, UUID, TARGET
 674                                 from
 675                                 (
 676                                 (
 677                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 678                                 d.widget_id UUID,
 679                                 d.wdg_desc TARGET
 680                                 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
 681                                 where upper(a.org_user_id) = upper( :userId )
 682                                 and a.user_id = b.user_id
 683                                 and b.role_id = c.role_id
 684                                 and c.widget_id = d.widget_id
 685                                 and d.all_user_flag<>'Y'
 686                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 687                                 )
 688                                 union
 689                                 (
 690                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 691                                 d.widget_id UUID,
 692                                 d.wdg_desc TARGET
 693                                 from ep_widget_catalog d
 694                                 where d.all_user_flag='Y'
 695                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 696                                 )
 697                                 union
 698                                 (
 699                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 700                                 d.widget_id UUID,
 701                                 d.wdg_desc TARGET
 702                                 from fn_user a, fn_user_role b, ep_widget_catalog d
 703                                 where upper(a.org_user_id) = upper( :userId )
 704                                 and a.user_id = b.user_id
 705                                 and b.role_id = 1
 706                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 707                                 )
 708                                 ) y
 709                                 ) t,
 710                                 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
 711                                                                         ;
 712
 713                 ]]>
 714         </sql-query>
 715
 716         <!-- requires values for named parameters :searchQuery and :userId -->
 717         <sql-query name="getNotifications">
 718                 <return alias="notificationResult"
 719                         class="org.onap.portalapp.portal.transport.EpNotificationItem" />
 720                 <![CDATA[
 721                         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
 722                         (
 723
 724                         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
 725                         from
 726                                 (
 727                                 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
 728                                 from
 729                                 (
 730                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 731                                         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
 732                                 from ep_notification a, ep_role_notification b
 733                                 where a.notification_id = b.notification_id
 734                                 and (end_time is null ||  SYSDATE() <= end_time )
 735                                 and (start_time is null ||  SYSDATE() >= start_time)
 736                                 and a.is_for_all_roles = 'N'
 737                                 ) a,
 738                                 (
 739                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 740                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 741                                 where COALESCE(c.app_id,1) = d.app_id
 742                         and a.user_id = b.user_id
 743                                 and a.user_id = :user_id
 744                                 and b.role_id = c.role_id
 745                         and (d.enabled='Y' or d.app_id=1)
 746                                 )b
 747                                 where
 748                                 (
 749                                 a.role_id = b.role_id
 750                                 )
 751                                 union
 752                                 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
 753                                 from
 754                                 (
 755                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 756                                         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
 757                                 from ep_notification a, ep_role_notification b
 758                                 where a.notification_id = b.notification_id
 759                                 and (end_time is null ||  SYSDATE() <= end_time )
 760                                 and (start_time is null ||  SYSDATE() >= start_time)
 761                                 and a.is_for_all_roles = 'N'
 762                                 ) a
 763                                 where
 764                                 (
 765                                 a.recv_user_id=:user_id
 766                                 )
 767                                 union
 768                                 (
 769                                 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
 770                                 from ep_notification a
 771                                 where a.notification_id
 772                                 and (end_time is null ||  SYSDATE() <= end_time )
 773                                 and (start_time is null ||  SYSDATE() >= start_time)
 774                                 and a.is_for_all_roles = 'Y'
 775                                 )
 776                                 ) a
 777                                 where
 778                                         active_YN = 'Y'
 779                                 and
 780                                         not exists
 781                                 (
 782                                 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'
 783                                 )
 784                                 order by priority desc, created_date desc,start_Time desc
 785
 786
 787                          ) t,
 788                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 789                         ;
 790                 ]]>
 791
 792         </sql-query>
 793                 <sql-query name="getUsersByOrgIdsNotifications">
 794                 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
 795                 <![CDATA[
 796                         SELECT * from fn_user where org_user_id in (:OrgIds)
 797                 ]]>
 798
 799         </sql-query>
 800
 801
 802         <!-- Gets all Admin notification history; accepts no parameters -->
 803         <sql-query name="getAdminNotificationHistoryVO">
 804                 <return alias="adminNotificationHistoryVOResult"
 805                         class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
 806                 <![CDATA[
 807                 SELECT * from (
 808 SELECT
 809                         n.notification_ID AS notificationId,
 810                         n.is_for_online_users AS isForOnlineUsers,
 811                         n.is_for_all_roles AS isForAllRoles,
 812                         n.active_YN AS activeYn,
 813                         n.msg_header AS msgHeader,
 814                         n.msg_description AS msgDescription,
 815                         n.msg_source AS msgSource,
 816                         n.start_Time AS startTime,
 817                         n.end_time AS endTime,
 818                         n.priority,
 819                         n.creator_ID AS creatorId,
 820                         n.created_date AS createdDate,
 821                         n.notification_hyperlink AS notificationHyperlink,
 822                         u.org_user_id AS loginId
 823                 FROM
 824                         ep_notification n
 825                 LEFT JOIN
 826                         fn_user u on u.user_id = n.creator_id
 827                 WHERE
 828                         n.active_YN='Y' and msg_source='EP'
 829
 830       UNION
 831
 832       SELECT
 833                         n.notification_ID AS notificationId,
 834                         n.is_for_online_users AS isForOnlineUsers,
 835                         n.is_for_all_roles AS isForAllRoles,
 836                         n.active_YN AS activeYn,
 837                         n.msg_header AS msgHeader,
 838                         n.msg_description AS msgDescription,
 839                         n.msg_source AS msgSource,
 840                         n.start_Time AS startTime,
 841                         n.end_time AS endTime,
 842                         n.priority,
 843                         n.creator_ID AS creatorId,
 844                         n.created_date AS createdDate,
 845                         n.notification_hyperlink AS notificationHyperlink,
 846                         u.org_user_id AS loginId
 847                 FROM
 848                         ep_notification n
 849                 LEFT JOIN
 850                         fn_user u on u.user_id = n.creator_id
 851     JOIN
 852                         ep_role_notification r on r.notification_ID=n.notification_ID
 853                 WHERE
 854                         n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
 855       )n order by n.startTime desc
 856                 ]]>
 857         </sql-query>
 858
 859         <sql-query name="getEpNotificationAppRoles">
 860                 <return alias="notificationAppRoles"
 861                         class="org.onap.portalapp.portal.domain.EcompAppRole" />
 862                 <!-- This query requires no parameters. -->
 863                 <![CDATA[
 864
 865 select  a.app_id, a.app_name, b.role_id, b.role_name from
 866 (select * from fn_app where app_id = 1) a,
 867 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
 868 union
 869 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
 870 from fn_app, fn_role
 871 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
 872
 873
 874
 875                 ]]>
 876         </sql-query>
 877
 878         <!-- Gets all notification history -->
 879         <sql-query name="getNotificationHistoryVO">
 880                 <return alias="notificationHistoryVOResult"
 881                         class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
 882                 <![CDATA[
 883                 SELECT
 884                         notificationId, isForOnlineUsers, isForAllRoles, msgHeader,     msgDescription,msgSource,
 885                         startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId,     activeYn
 886                 FROM
 887                         (
 888                                         select distinct
 889                                         a.notification_ID AS notificationId,
 890                                         is_for_online_users AS isForOnlineUsers,
 891                                         is_for_all_roles AS isForAllRoles,
 892                                         msg_header AS msgHeader,
 893                                         msg_description AS msgDescription,
 894                                         msg_source AS msgSource,
 895                                         start_Time AS startTime,
 896                                         end_time AS endTime,
 897                                         priority,
 898                                         created_date AS createdDate,
 899                                         creator_ID AS creatorId,
 900                                         notification_hyperlink AS notificationHyperlink,
 901                                         login_id AS loginId,
 902                                         active_YN AS activeYn,
 903                                         if (is_viewed is null, 'N', is_viewed)
 904                         from
 905                         (
 906                                 select
 907                                         user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 908                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 909                                         creator_ID,notification_hyperlink,active_YN
 910                                 from
 911                                 (
 912                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 913                                         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,
 914                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 915
 916                    ELSE NULL
 917               END  AS login_id,b.recv_user_id
 918                                 from ep_notification a, ep_role_notification b,fn_user u
 919                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 920                                 and a.is_for_all_roles = 'N'
 921                                 and (
 922                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 923                                 or
 924                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 925                                 or
 926                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 927                                 or
 928                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 929                                 )
 930                                 ) a,
 931                                 (
 932                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 933                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 934                                 where COALESCE(c.app_id,1) = d.app_id
 935                         and a.user_id = b.user_id
 936                                 and a.user_id = :user_id
 937                                 and b.role_id = c.role_id
 938                         and (d.enabled='Y' or d.app_id=1)
 939                                 ) b
 940                                 where
 941                                 (
 942                                 a.role_id = b.role_id
 943                                 )
 944                                    UNION
 945                         select
 946                                         :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 947                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 948                                         creator_ID,notification_hyperlink,active_YN
 949                                 from
 950                                 (
 951                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 952                                         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,
 953                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 954
 955                    ELSE NULL
 956               END  AS login_id,b.recv_user_id
 957                                 from ep_notification a, ep_role_notification b,fn_user u
 958                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 959                                 and a.is_for_all_roles = 'N'
 960                                 and (
 961                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 962                                 or
 963                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 964                                 or
 965                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 966                                 or
 967                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 968                                 )
 969                                 ) a
 970                         where
 971                         (
 972                         a.recv_user_id=:user_id
 973                         )
 974                                 union
 975                                 (
 976                                 select
 977                                         :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
 978                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
 979                                         creator_ID, a.notification_hyperlink,a.active_YN
 980                                 from ep_notification a  JOIN fn_user b on b.user_id=a.creator_ID
 981                                 where a.notification_id
 982                                 and a.is_for_all_roles = 'Y'
 983                                 and (
 984                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 985                                 or
 986                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 987                                 or
 988                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 989                                 or
 990                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 991                                 )
 992                                 )
 993                                 ) a left outer join (
 994                                 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
 995                                 ) m
 996                                 on  a.notification_id = m.notification_ID
 997                                 where
 998                                 active_YN = 'Y'
 999
1000                                 order by  start_Time desc,end_time desc
1001                         ) t,
1002                      (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
1003                 ]]>
1004
1005         </sql-query>
1006
1007                 <!-- shows the received recipient to whom the notification is delivered from external system -->
1008         <sql-query name="messageRecipients">
1009                 <return-scalar column="org_user_id" type="java.lang.String" />
1010                 <![CDATA[
1011                         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
1012                 ]]>
1013         </sql-query>
1014
1015
1016         <!-- Gets role details for a specified notification -->
1017         <sql-query name="getNotificationRoles">
1018                 <return alias="notificationRolesResult"
1019                         class="org.onap.portalapp.portal.transport.EpRoleNotificationItem" />
1020                 <![CDATA[
1021                         SELECT *
1022                         FROM
1023                                 ep_role_notification
1024                         WHERE
1025                                 notification_Id = :notificationId
1026                 ]]>
1027         </sql-query>
1028
1029         <!-- Gets all applications, possibly with contact information -->
1030         <sql-query name="getAppsAndContacts">
1031                 <return alias="appContactUs"
1032                         class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1033                 <!-- This query requires no parameters. -->
1034                 <![CDATA[
1035                         select
1036                                 a.app_id as appId, a.app_name as appName,
1037                                 c.contact_name as contactName,
1038                                 c.contact_email as contactEmail, c.url, c.description,
1039                                 c.active_yn as activeYN
1040                         from
1041                                 fn_app a
1042                         left join
1043                                 fn_app_contact_us c
1044                                 on a.app_id = c.app_id
1045                         where
1046                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1047                         ;
1048                 ]]>
1049         </sql-query>
1050
1051         <!-- Gets all rows from the app-contact-us table, extended with app information -->
1052         <sql-query name="getAppContactUsItems">
1053                 <return alias="appContactUs"
1054                         class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1055                 <!-- This query requires no parameters. -->
1056                 <![CDATA[
1057                         select
1058                                 c.app_id as appId, c.contact_name as contactName,
1059                                 c.contact_email as contactEmail, c.url, c.description,
1060                                 c.active_yn as activeYN, a.app_name as appName
1061                         from
1062                                 fn_app_contact_us c
1063                         left join
1064                                 fn_app a
1065                                 on a.app_id = c.app_id
1066                         where
1067                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1068                         ;
1069                 ]]>
1070         </sql-query>
1071
1072         <!-- Gets one row for each app with the category and catenated functions. -->
1073         <sql-query name="getAppCategoryFunctions">
1074                 <return alias="appCategoryFunctions"
1075                         class="org.onap.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1076                 <!-- This query requires no parameters. -->
1077                 <![CDATA[
1078                         SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1079                                 r.category as category, r.app_id as appId, r.app_name as application,
1080                                 group_concat(function_text separator ', ') as functions
1081                         FROM (
1082                                 SELECT
1083                                 app_id, function_text, app_name,
1084                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1085                                 FROM (
1086                                         SELECT
1087                                         j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1088                                         k.text parent_menu, k.parent_menu_id parent_menu_id
1089                                         FROM (
1090                                                 SELECT distinct
1091                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1092                                                 FROM
1093                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1094                                                 WHERE
1095                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1096                                                 ) j,
1097                                         fn_menu_functional k
1098                                         WHERE j.parent_menu_id = k.menu_id
1099                                         ) fn,
1100                                 fn_menu_functional a
1101                                 WHERE fn.parent_menu_id = a.menu_id
1102                         ) r,
1103                         (SELECT @rn /*'*/:=/*'*/ 0) t2
1104                         group by r.category, r.app_id, r.app_name
1105                         order by category, app_name
1106                         ;
1107                 ]]>
1108         </sql-query>
1109
1110         <!-- Gets one row for each function-application-role combination. -->
1111         <sql-query name="getAppAccessFunctionRole">
1112                 <return alias="appAccessFunctionRole"
1113                         class="org.onap.portalapp.portal.domain.GetAccessResult" />
1114                 <!-- This query requires one parameters. -->
1115                 <![CDATA[
1116                         select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1117                         (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,
1118                         o.requested_role_id,o.request_type
1119                         from
1120                         (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1121                         from
1122                         (
1123                         select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1124                         from
1125                         FN_ROLE C, FN_APP D
1126                         where COALESCE(c.app_id,1) = d.app_id
1127                         and (d.enabled='Y' or d.app_id=1)
1128                         and c.active_yn = 'Y'
1129                         ) a left outer join
1130                         (
1131                         select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1132                         WHERE
1133                         A.MENU_ID = B.MENU_ID
1134                         and active_yn = 'Y'
1135                         )b
1136                         on a.role_id = b.role_id) t left outer join
1137                         (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1138                         where er.req_id=ed.req_id
1139                         and upper(ed.request_type)='P'
1140                         and er.user_id =:userId) o
1141                         on t.app_id=o.app_id
1142                         and t.role_id = o.requested_role_id
1143                         JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1144                         ;
1145                 ]]>
1146         </sql-query>
1147
1148         <query name="getCommonWidgetItem">
1149                 from CommonWidget where category = :cat order by
1150                 sortOrder, title
1151         </query>
1152
1153         <sql-query name="getGuestLastLogin">
1154                 <return-scalar column="audit_date" type="java.util.Date" />
1155                 <![CDATA[
1156                         select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1157                 ]]>
1158         </sql-query>
1159
1160         <sql-query name="getActiveUsers">
1161                 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1162                 <![CDATA[
1163                 SELECT * FROM FN_USER WHERE active_yn='Y'
1164                 ]]>
1165         </sql-query>
1166
1167         <sql-query name="getAppsAdmins">
1168                 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1169         <![CDATA[
1170         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
1171         ]]>
1172         </sql-query>
1173
1174         <sql-query name="getPortalAdmins">
1175                 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1176         <![CDATA[
1177                 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
1178         ]]>
1179         </sql-query>
1180
1181         <!-- Gets personalized list of enabled and accessible apps for regular user -->
1182         <sql-query name="getPersUserApps">
1183                 <return alias="persUserApps" class="org.onap.portalapp.portal.domain.EPApp" />
1184                 <!-- This query requires one parameter: userId (number) -->
1185                 <![CDATA[
1186                     select
1187                                 distinct  -- multiple roles yield multiple rows
1188                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1189                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1190                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1191                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1192                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL
1193                     from FN_APP a
1194                     -- Portal assigns role 999 to app administrator
1195                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1196                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1197                         where a.ENABLED = 'Y'
1198                     and (
1199                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1200                         or
1201                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1202                         )
1203                         order by app_name
1204                     ;
1205                 ]]>
1206         </sql-query>
1207
1208         <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1209                 admin -->
1210         <sql-query name="getPersAdminApps">
1211                 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1212                 <!-- This query requires one parameter: userId (number) -->
1213                 <![CDATA[
1214                     select
1215                                 distinct  -- multiple roles yield multiple rows
1216                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1217                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1218                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1219                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1220                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE      , a.AUTH_CENTRAL ,
1221                                 a.AUTH_NAMESPACE
1222                     from FN_APP a
1223                     -- Portal assigns role 999 to app administrator
1224                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1225                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1226                         where a.ENABLED = 'Y'
1227                     and (
1228                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1229                         or
1230                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1231                                 or
1232                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1233                         )
1234                         order by app_name
1235                     ;
1236                 ]]>
1237         </sql-query>
1238
1239         <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1240         <sql-query name="getPersAdminAppsOrderByName">
1241                 <return alias="AdminAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1242                 <!-- This query requires one parameter: userId (number) -->
1243                 <![CDATA[
1244                  select
1245                                 distinct  -- multiple roles yield multiple rows
1246                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1247                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1248                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1249                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1250                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1251                                 a.AUTH_NAMESPACE
1252                     from FN_APP a
1253                     -- Portal assigns role 999 to app administrator
1254                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1255                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1256                         where a.ENABLED = 'Y'
1257                     and (
1258                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1259                         or
1260                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1261                                 or
1262                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1263                         )
1264                         union
1265                 select distinct  -- multiple roles yield multiple rows
1266                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1267                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1268                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1269                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1270           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1271           b.AUTH_NAMESPACE
1272           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1273           where a.user_id =:userId  and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1274                   order by app_name
1275                   ;
1276
1277                 ]]>
1278         </sql-query>
1279
1280         <!--Gets personalized list of enabled and accessible User apps by name -->
1281         <sql-query name="getPersUserAppsOrderByName">
1282                 <return alias="UserAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1283                 <!-- This query requires one parameter: userId (number) -->
1284                 <![CDATA[
1285                 select
1286                                 distinct  -- multiple roles yield multiple rows
1287                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1288                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1289                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1290                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1291                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1292                                 a.AUTH_NAMESPACE
1293                     from FN_APP a
1294                     -- Portal assigns role 999 to app administrator
1295                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1296                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1297                         where a.ENABLED = 'Y'
1298                     and (
1299                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1300                         or
1301                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1302                         )
1303                         union
1304                 select distinct  -- multiple roles yield multiple rows
1305                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1306                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1307                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1308                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1309           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1310           b.AUTH_NAMESPACE
1311           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1312           where a.user_id =:userId  and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1313                 order by app_name
1314                  ;
1315
1316                 ]]>
1317         </sql-query>
1318
1319
1320         <!--Gets personalized list of enabled and accessible Super Admin apps by
1321                 LastUsed -->
1322         <sql-query name="getAdminAppsOrderByLastUsed">
1323                 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1324                 <![CDATA[
1325                     select
1326                         *
1327                         from (select
1328                                 distinct  -- multiple roles yield multiple rows
1329                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1330                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1331                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1332                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1333                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1334                                 a.AUTH_NAMESPACE
1335                     from FN_APP a
1336                     -- Portal assigns role 999 to app administrator
1337                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1338                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1339                         where a.ENABLED = 'Y'
1340                     and (
1341                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1342                         or
1343                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1344                                 or
1345                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1346                         )
1347                         union
1348                   select distinct  -- multiple roles yield multiple rows
1349                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1350                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1351                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1352                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1353           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1354           b.AUTH_NAMESPACE
1355           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1356           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
1357                         ) A
1358                 left outer join
1359                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1360                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1361                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1362                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1363                         and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1364                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1365                          )B
1366                         on A.app_id = B.Affected_record_id
1367                         order by AUDIT_DATE DESC;
1368                         ]]>
1369         </sql-query>
1370
1371         <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1372         <sql-query name="getUserAppsOrderByLastUsed">
1373                 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1374                 <![CDATA[
1375                         select * from (select
1376                                 distinct  -- multiple roles yield multiple rows
1377                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1378                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1379                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1380                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1381                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1382                                 a.AUTH_NAMESPACE
1383                     from FN_APP a
1384                     -- Portal assigns role 999 to app administrator
1385                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1386                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1387                         where a.ENABLED = 'Y'
1388                     and (
1389                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1390                         or
1391                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1392                         )union
1393                 select distinct  -- multiple roles yield multiple rows
1394                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1395                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1396                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1397                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1398           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1399           b.AUTH_NAMESPACE
1400           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1401           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
1402
1403                         ) A
1404                 left outer join
1405                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1406                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1407                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1408                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1409                         and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1410                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1411                          )B
1412                         on A.app_id = B.Affected_record_id
1413                         order by AUDIT_DATE DESC;
1414
1415
1416                 ]]>
1417
1418         </sql-query>
1419
1420         <!--Gets personalized list of enabled and accessible Super Admin apps by
1421                 Most Used -->
1422         <sql-query name="getAdminAppsOrderByMostUsed">
1423                 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1424                 <![CDATA[
1425                         select
1426                         *
1427                         from (select
1428                                 distinct  -- multiple roles yield multiple rows
1429                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1430                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1431                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1432                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1433                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1434                                 a.AUTH_NAMESPACE
1435                     from FN_APP a
1436                     -- Portal assigns role 999 to app administrator
1437                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1438                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1439                         where a.ENABLED = 'Y'
1440                     and (
1441                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1442                         or
1443                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1444                                 or
1445                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1446                         )union
1447                    select distinct  -- multiple roles yield multiple rows
1448                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1449                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1450                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1451                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1452           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1453           b.AUTH_NAMESPACE
1454           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1455           where a.user_id =:userId  and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1456                         ) A
1457                         left outer join
1458                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1459                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1460                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1461                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1462                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1463                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1464                          )B
1465                         on A.app_id = B.Affected_record_id
1466                         order by total_visits DESC;
1467                 ]]>
1468
1469         </sql-query>
1470
1471         <!--Gets personalized list of enabled and accessible NON Super Admin User
1472                 apps by Most Used -->
1473         <sql-query name="getUserAppsOrderByMostUsed">
1474                 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1475                 <![CDATA[
1476                         select * from
1477                         (select
1478                                 distinct  -- multiple roles yield multiple rows
1479                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1480                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1481                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1482                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1483                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE             , a.AUTH_CENTRAL ,
1484                                 a.AUTH_NAMESPACE
1485                     from FN_APP a
1486                     -- Portal assigns role 999 to app administrator
1487                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1488                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1489                         where a.ENABLED = 'Y'
1490                     and (
1491                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1492                         or
1493                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1494                         )union
1495                   select distinct  -- multiple roles yield multiple rows
1496                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1497                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1498                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1499                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1500           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1501           b.AUTH_NAMESPACE
1502           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1503           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
1504                         )A
1505                         left outer join
1506                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1507                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1508                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1509                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1510                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1511                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1512                          )B
1513                         on A.app_id = B.Affected_record_id
1514                         order by total_visits DESC;
1515                 ]]>
1516         </sql-query>
1517
1518         <!--Gets personalized list of enabled and accessible Super Admin apps by
1519                 Manual -->
1520         <sql-query name="getAdminAppsOrderByManual">
1521                 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1522                 <![CDATA[
1523                         select * from
1524                         (select
1525                                 distinct  -- multiple roles yield multiple rows
1526                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1527                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1528                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1529                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1530                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1531                                 a.AUTH_NAMESPACE
1532                     from FN_APP a
1533                     -- Portal assigns role 999 to app administrator
1534                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1535                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1536                         where a.ENABLED = 'Y'
1537                     and (
1538                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1539                         or
1540                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1541                                 or
1542                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1543                         )union
1544                 select distinct  -- multiple roles yield multiple rows
1545                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1546                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1547                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1548                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1549           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1550           b.AUTH_NAMESPACE
1551           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1552           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
1553                         ) A
1554                         left outer join
1555                          (select m.app_id, m.sort_order
1556                         from ep_pers_user_app_man_sort m
1557                         where USER_ID = :userId
1558                         ) B
1559                         on A.APP_ID = B.app_id
1560                         order by sort_order ASC
1561                     ;
1562                 ]]>
1563
1564         </sql-query>
1565
1566         <!--Gets personalized list of enabled and accessible NON Super admin User
1567                 apps by Manual -->
1568         <sql-query name="getUserAppsOrderByManual">
1569                 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1570                 <![CDATA[
1571                                                         select * from   (select
1572                                 distinct  -- multiple roles yield multiple rows
1573                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1574                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1575                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1576                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1577                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1578                                 a.AUTH_NAMESPACE
1579                     from FN_APP a
1580                     -- Portal assigns role 999 to app administrator
1581                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1582                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1583                         where a.ENABLED = 'Y'
1584                     and (
1585                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1586                         or
1587                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1588                         )union
1589                 select distinct  -- multiple roles yield multiple rows
1590                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1591                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1592                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1593                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1594           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1595           b.AUTH_NAMESPACE
1596           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1597           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
1598                         ) A
1599                         left outer join
1600                          (select m.app_id, m.sort_order
1601                         from ep_pers_user_app_man_sort m
1602                         where USER_ID = :userId
1603                         ) B
1604                         on A.APP_ID = B.app_id
1605                         order by sort_order ASC
1606                     ;
1607                 ]]>
1608         </sql-query>
1609
1610         <!-- Gets regular user's list of enabled apps including accessible and select
1611                 statuses -->
1612         <sql-query name="getUserAppCatalog">
1613                 <return alias="userAppCatalog"
1614                         class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1615                 <!-- This query requires one parameter: userId (number) -->
1616                 <![CDATA[
1617                                                 select
1618                                 distinct  -- multiple roles yield multiple rows
1619                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1620                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1621                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1622                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1623                             IF(a.open = 'Y', TRUE, FALSE) as open,
1624                 -- ACCESS(-ible) means user has a defined role OR the application is open
1625                                 case
1626                                         -- regular app and user has a role
1627                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1628                     -- open app
1629                     when a.OPEN = 'Y' then TRUE
1630                     else FALSE
1631                 end as 'access' ,
1632                 -- SELECT(-ed) indicates user personalization
1633                 case
1634                                         -- regular app, user has a role, no personalization
1635                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1636                     -- open app and has personalization
1637                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1638                     else FALSE
1639                 end as 'select' ,
1640                 -- PENDING indicates user checked a box
1641                 case
1642                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1643                         else FALSE
1644                 end as 'pending'
1645                     from FN_APP a
1646                     -- Portal assigns role 999 to app administrator
1647             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1648                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1649       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'
1650       where a.ENABLED = 'Y'
1651
1652                         -- Show accessible apps first, then the rest; sort by name within each set.
1653             order by access desc, app_name asc
1654                     ;
1655                 ]]>
1656         </sql-query>
1657
1658         <sql-query name="getMyloginAppDetails">
1659                 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1660                 <![CDATA[
1661                 SELECT * FROM fn_app  where ml_app_name =:appName
1662                 ;
1663                 ]]>
1664         </sql-query>
1665
1666         <sql-query name="getAppDetailsByAppName">
1667                 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1668                 <![CDATA[
1669                 SELECT * FROM fn_app  where app_name =:appName
1670                 ;
1671                 ]]>
1672         </sql-query>
1673
1674                 <sql-query name="getMyAppDetailsByUebKey">
1675                 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1676                 <![CDATA[
1677                 SELECT * FROM fn_app  where ueb_key =:appKey
1678                 ;
1679                 ]]>
1680         </sql-query>
1681
1682
1683         <sql-query name="deleteAccountEndpoint">
1684                 <![CDATA[
1685                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1686                 ;
1687                 ]]>
1688         </sql-query>
1689
1690         <sql-query name="deleteAccountEndpointRecord">
1691                 <![CDATA[
1692                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1693                 ;
1694                 ]]>
1695         </sql-query>
1696
1697         <sql-query name="deleteEPEndpoint">
1698                 <![CDATA[
1699                 DELETE FROM ep_endpoints WHERE id =:epId
1700                 ;
1701                 ]]>
1702         </sql-query>
1703
1704         <sql-query name="deleteBasicAuthAccount">
1705                 <![CDATA[
1706                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1707                 ;
1708                 ]]>
1709         </sql-query>
1710
1711         <sql-query name="getAppRoles">
1712                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1713                 <![CDATA[
1714                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1715                 ;
1716                 ]]>
1717         </sql-query>
1718
1719         <sql-query name="getPartnerAppRolesList">
1720                 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1721                 <![CDATA[
1722                 SELECT * FROM fn_role where app_id =:appId
1723                 ;
1724                 ]]>
1725         </sql-query>
1726
1727         <sql-query name="getPortalAppRolesList">
1728                 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1729                 <![CDATA[
1730                 SELECT * FROM fn_role where app_id is null
1731                 ;
1732                 ]]>
1733         </sql-query>
1734
1735         <sql-query name="getPortalAppRoleByRoleId">
1736                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1737                 <![CDATA[
1738                 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1739                 ;
1740                 ]]>
1741         </sql-query>
1742
1743         <sql-query name="getPartnerAppRoleByRoleId">
1744                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1745                 <![CDATA[
1746                 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1747                 ;
1748                 ]]>
1749         </sql-query>
1750
1751         <sql-query name="getPartnerAppRoleById">
1752                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1753                 <![CDATA[
1754                 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1755                 ;
1756                 ]]>
1757         </sql-query>
1758
1759         <sql-query name="getPortalAppRoles">
1760                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1761                 <![CDATA[
1762                 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1763                 ;
1764                 ]]>
1765         </sql-query>
1766
1767         <sql-query name="deleteMicroserviceParameter">
1768                 <![CDATA[
1769                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1770                 ;
1771                 ]]>
1772         </sql-query>
1773
1774         <sql-query name="deleteMicroserviceParameterById">
1775                 <![CDATA[
1776                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1777                 ;
1778                 ]]>
1779         </sql-query>
1780
1781         <sql-query name="deleteWidgetCatalogParameter">
1782                 <![CDATA[
1783                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1784                 ;
1785                 ]]>
1786         </sql-query>
1787
1788         <sql-query name="deleteMicroservice">
1789                 <![CDATA[
1790                 DELETE FROM ep_microservice WHERE id =:serviceId
1791                 ;
1792                 ]]>
1793         </sql-query>
1794
1795         <sql-query name="epUserAppId">
1796                 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1797                 <![CDATA[
1798                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1799                 ;
1800                 ]]>
1801         </sql-query>
1802
1803         <sql-query name="userAppsSortPreferenceQuery">
1804                 <return alias="appsSortPreferenceQuery"
1805                         class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1806                 <![CDATA[
1807                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1808
1809                 ]]>
1810         </sql-query>
1811
1812         <sql-query name="userAppsManualSortPrfQuery">
1813                 <return alias="AppsManualSortPrfQuery"
1814                         class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1815                 <![CDATA[
1816                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1817
1818                 ]]>
1819         </sql-query>
1820
1821         <sql-query name="userWidgetManualSortPrfQuery">
1822                 <return alias="widgetManualSortPrfQuery"
1823                         class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1824                 <![CDATA[
1825                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1826                 ]]>
1827         </sql-query>
1828
1829         <sql-query name="appRoles">
1830                 <return alias="rolesForApp"
1831                         class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1832                 <![CDATA[
1833                         SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1834                         ;
1835                 ]]>
1836         </sql-query>
1837
1838         <sql-query name="userAppRolesRequestList">
1839                 <return alias="appRolesRequestList"
1840                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1841                 <![CDATA[
1842                         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'
1843                         ;
1844                 ]]>
1845         </sql-query>
1846
1847         <sql-query name="userAppRolesRequestDetailList">
1848                 <return alias="appRolesRequestDetailList"
1849                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1850                 <![CDATA[
1851
1852                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1853                         ;
1854                 ]]>
1855         </sql-query>
1856
1857         <!-- Gets list of enabled apps including accessible and select statuses -->
1858         <sql-query name="getAdminAppCatalog">
1859                 <return alias="adminAppCatalog"
1860                         class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1861                 <!-- This query requires one parameter: userId (number) -->
1862                 <![CDATA[
1863                         select
1864                                 distinct  -- multiple roles yield multiple rows
1865                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1866                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1867                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1868                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1869                             IF(a.open = 'Y', TRUE, FALSE) as open,
1870                 -- ACCESS(-ible) means user has a defined role OR the application is open
1871                                 case
1872                                         -- regular app and user has a role
1873                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1874                     -- open app
1875                     when a.OPEN = 'Y' then TRUE
1876                     else FALSE
1877                 end as 'access' ,
1878                 -- SELECT(-ed) indicates user personalization
1879                 case
1880                                         -- regular app, user has a role, no personalization
1881                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1882                                         -- regular app, user has role, admin forced a personalization
1883                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1884                                         -- regular app, user has no role, admin forced a personalization
1885                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1886                     -- open app and has personalization
1887                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1888                     else FALSE
1889                 end as 'select' ,
1890                 -- PENDING indicates user checked a box
1891                 case
1892                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1893                         else FALSE
1894                 end as 'pending'
1895                     from FN_APP a
1896                     -- Portal assigns role 999 to app administrator
1897             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1898                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1899                         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'
1900                         where a.ENABLED = 'Y'
1901                         -- Show accessible apps first, then the rest; sort by name within each set.
1902             order by access desc, app_name asc
1903                     ;
1904                 ]]>
1905         </sql-query>
1906
1907         <sql-query name="userAppCatalogRoles">
1908                 <return alias="epUserAppCatalogRoles"
1909                         class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1910                 <![CDATA[
1911         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
1912        from ep_user_roles_request A left join ep_user_roles_request_det B
1913                    on a.req_id = b.req_id
1914                    where  A.user_id=:userid
1915        and A.app_id IN (select app_id from fn_app where app_name=:appName)
1916        and A.request_status ='P'
1917        ;
1918                 ]]>
1919         </sql-query>
1920
1921         <sql-query name="getUserApproles">
1922                 <return alias="businessCardUserApplicationRoles"
1923                         class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1924                 <![CDATA[
1925
1926                 select   fr.role_name, fa.app_name
1927           from
1928         fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1929           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)
1930           ;
1931
1932                 ]]>
1933         </sql-query>
1934
1935     <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1936         <sql-query name="deleteNotificationsFromEpNotificationTable">
1937                 <![CDATA[
1938                 delete from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1939                 ]]>
1940         </sql-query>
1941
1942
1943                 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1944         <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1945                 <![CDATA[
1946         delete from ep_role_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1947                 ]]>
1948         </sql-query>
1949
1950         <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1951         <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1952                 <![CDATA[
1953         delete from ep_user_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1954                 ]]>
1955         </sql-query>
1956
1957         <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1958         <sql-query name="getAppRoleFunctionList">
1959                 <return alias="appRoleFunctionList"
1960                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1961                 <![CDATA[
1962
1963                         SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1964                         where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1965                         ;
1966
1967                 ]]>
1968         </sql-query>
1969
1970         <!-- Gets list of all role functions -->
1971         <sql-query name="getAllRoleFunctions">
1972                 <return alias="allRoleFunctions"
1973                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1974                 <![CDATA[
1975
1976                         SELECT * from ep_app_function where app_id =:appId
1977                         ;
1978
1979                 ]]>
1980         </sql-query>
1981
1982         <!-- Get ep_app_function records provided by single parameter -->
1983         <sql-query name="getRoleFunction">
1984                 <return alias="RoleFunction"
1985                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1986                         <![CDATA[
1987
1988                         SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1989                         ;
1990
1991                         ]]>
1992         </sql-query>
1993
1994         <!-- Gets the current user app roles records and requires two parameters  -->
1995         <sql-query name="getUserAppCurrentRoles">
1996                 <return alias="userAppCurrentRoles"
1997                         class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
1998                         <![CDATA[
1999
2000                         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'
2001                         ;
2002                         ]]>
2003         </sql-query>
2004
2005         <!-- Gets the current user app roles along with account administrator and requires two parameters  -->
2006         <sql-query name="getUserAppExistingRoles">
2007                 <return alias="userAppExistingRoles"
2008                         class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
2009                         <![CDATA[
2010                         select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
2011                         left outer join fn_role fr on fu.role_id = fr.role_id
2012                         where fu.user_id =:userId and fu.app_id =:appId
2013                         ;
2014                         ]]>
2015         </sql-query>
2016
2017         <!-- Gets all functions of portal app -->
2018         <sql-query name="getAllFunctions">
2019                 <return alias="allFunctions"
2020                         class="org.onap.portalsdk.core.domain.RoleFunction" />
2021                         <![CDATA[
2022                         select * from fn_function
2023                         ;
2024                         ]]>
2025         </sql-query>
2026
2027         <!-- Gets the current user app roles records and requires two parameters  -->
2028         <sql-query name="getPartnerAppFunctions">
2029                 <return alias="partnerAppFunctions"
2030                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2031                         <![CDATA[
2032                         select * from ep_app_function where app_id =:appId
2033                         ;
2034                         ]]>
2035         </sql-query>
2036
2037         <!-- Gets the  all role functions and requires one parameters -->
2038         <sql-query name="uploadAllRoleFunctions">
2039                 <return alias="allRoleFunctions"
2040                         class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2041                         <![CDATA[
2042                                 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
2043                                 ;
2044                         ]]>
2045         </sql-query>
2046
2047                 <!-- Gets the  all application role functions from ep_app_function table and requires one parameters -->
2048         <sql-query name="uploadPartnerRoleFunctions">
2049                 <return alias="partnerRoleFunctions"
2050                         class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2051                         <![CDATA[
2052                                 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
2053                                 ;
2054                         ]]>
2055         </sql-query>
2056
2057         <sql-query name="getRoletoUpdateInExternalAuthSystem">
2058                 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2059                         <![CDATA[
2060                                         SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2061                         ;
2062                         ]]>
2063         </sql-query>
2064
2065                 <!-- Gets all active records from fn_role that is associated with this app-->
2066         <sql-query name="getActiveRolesOfApplication">
2067                 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2068                 <![CDATA[
2069                 select * from fn_role where active_yn = 'Y' and app_id=:appId
2070                 ;
2071                 ]]>
2072         </sql-query>
2073
2074         <sql-query name="getBulkUserRoles">
2075                 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2076                         <![CDATA[
2077                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2078                         left outer join fn_role fr on fr.role_id = fur.role_id
2079                         left outer join fn_app fa on fa.app_id = fur.app_id
2080                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2081                         ;
2082                         ]]>
2083         </sql-query>
2084
2085         <sql-query name="getBulkUsersForSingleRole">
2086                 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2087                         <![CDATA[
2088                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2089                         left outer join fn_role fr on fr.role_id = fur.role_id
2090                         left outer join fn_app fa on fa.app_id = fur.app_id
2091                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2092                         ;
2093                         ]]>
2094         </sql-query>
2095
2096         <sql-query name="getCentralizedApps">
2097                 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2098                         <![CDATA[
2099                         select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2100                         ;
2101                         ]]>
2102         </sql-query>
2103
2104         <sql-query name="getUserRoles">
2105                         <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2106                 <![CDATA[
2107
2108         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
2109          ;
2110                 ]]>
2111         </sql-query>
2112                 <sql-query name="getRoleFunctionsOfUser">
2113                         <![CDATA[
2114                         select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2115                         where fu.role_id = ep.role_id
2116                         and fu.app_id = ep.app_id
2117                         and fu.user_id =:userId and ep.app_id = 1
2118                         and ea.function_cd = ep.function_cd
2119                     union
2120             select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2121                         where role_id = 999
2122                         and app_r_f.function_cd = a_f.function_cd
2123                         and exists
2124                         (
2125                         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
2126                         and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2127                         );
2128                         ]]>
2129         </sql-query>
2130
2131         <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
2132                         <![CDATA[
2133                         select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2134                         where fu.role_id = ep.role_id
2135                         and fu.app_id = ep.app_id
2136                         and fu.user_id =:userId
2137                         and ea.function_cd = ep.function_cd
2138                         and exists
2139                         (
2140                         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
2141                         and ur.app_id = fa.app_id and fa.enabled = 'Y'
2142                         );
2143                         ]]>
2144         </sql-query>
2145
2146         <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
2147         <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2148                         <![CDATA[
2149                         SELECT app.APP_ID, app.APP_NAME, app.APP_TYPE FROM FN_APP app
2150                         inner join FN_USER_ROLE userrole ON userrole.APP_ID=app.APP_ID
2151                         where userrole.USER_ID = :userId AND userrole.ROLE_ID=999 AND (app.ENABLED = 'Y' OR app.APP_ID=1)
2152                         UNION
2153                         SELECT DISTINCT app.APP_ID, app.app_name, app.APP_TYPE FROM fn_user user
2154                                         INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2155                                          INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2156                         INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y'  or app.app_id=1)
2157                         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%')
2158                         WHERE user.user_id = :userId ;
2159                         ]]>
2160         </sql-query>
2161
2162         <sql-query name="getApplicationsofTheUserContainsApprover">
2163         <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2164                         <![CDATA[
2165                         SELECT DISTINCT app.APP_ID, app.APP_TYPE, app.app_name FROM fn_user user
2166                                         INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2167                                          INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2168                         INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y'  or app.app_id=1)
2169                         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%')
2170                         WHERE user.user_id = :userId ;
2171                         ]]>
2172         </sql-query>
2173
2174
2175
2176         <sql-query name="getUserRolesForLeftMenu">
2177                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2178
2179                 <![CDATA[
2180                 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
2181         INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2182         INNER JOIN fn_app app ON app.app_id= userrole.app_id
2183         INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2184         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)
2185         ;
2186                 ]]>
2187         </sql-query>
2188
2189         <sql-query name="getRolesForLeftMenu">
2190                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2191
2192                 <![CDATA[
2193                 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
2194         INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2195         INNER JOIN fn_app app ON app.app_id= userrole.app_id
2196         INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2197         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)
2198         ;
2199                 ]]>
2200         </sql-query>
2201
2202         <!-- Gets all functions for an application along with global functions and requires single parameter -->
2203         <sql-query name="getMenuFunctions">
2204                 <![CDATA[
2205                         select f.function_cd from ep_app_function f
2206                         where f.app_id =:appId
2207                         UNION
2208                         select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2209                         and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2210                  ;
2211                 ]]>
2212         </sql-query>
2213
2214         <sql-query name="getRequestIdsForApp">
2215         <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2216                 <![CDATA[
2217                 select * from ep_user_roles_request where app_id =:app_id
2218                 ;
2219                 ]]>
2220         </sql-query>
2221
2222         <sql-query name="ApplicationUserRoles">
2223                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2224                 <![CDATA[
2225                    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,
2226            fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2227            from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2228            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
2229            union
2230            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,
2231            fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2232            from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2233            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'
2234            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';
2235                 ]]>
2236         </sql-query>
2237
2238         <sql-query name="getCurrentAppRoleFunctions">
2239                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2240                 <![CDATA[
2241                 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
2242                 and ef.function_cd =:functionCd and epr.app_id =:appId
2243                 ;
2244                 ]]>
2245         </sql-query>
2246
2247         <sql-query name="deleteUserWidgetPlacement">
2248                 <![CDATA[
2249                 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2250                 ;
2251                 ]]>
2252         </sql-query>
2253
2254         <sql-query name="getCentralizedAppsOfUser">
2255                 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2256                 <![CDATA[
2257
2258                 select distinct fa.app_id, fa.app_name
2259                 from  fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2260                 Where  fu.user_id =  fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2261                 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)
2262                 ;
2263
2264                 ]]>
2265         </sql-query>
2266
2267     <query name="getEPUserByOrgUserId">
2268         <![CDATA[
2269                 FROM EPUser WHERE orgUserId = :org_user_id
2270         ]]>
2271     </query>
2272
2273         <query name="getEPUserByLoginId">
2274         <![CDATA[
2275                 FROM EPUser WHERE loginId = :login_id
2276         ]]>
2277     </query>
2278
2279     <query name="getEPUserByLoginIdLoginPwd">
2280         <![CDATA[
2281                 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2282         ]]>
2283     </query>
2284
2285     <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2286                 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2287                 <![CDATA[
2288                 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
2289                 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2290                 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
2291                 ;
2292                 ]]>
2293         </sql-query>
2294
2295      <sql-query name="getGlobalRolesOfPortal">
2296                 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2297                 <![CDATA[
2298                 select * from fn_role where role_name  like 'global_%' and app_id is null and active_yn='Y'
2299                 ;
2300                 ]]>
2301         </sql-query>
2302
2303         <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2304                 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2305                 <![CDATA[
2306                 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2307                 ;
2308                 ]]>
2309         </sql-query>
2310
2311         <sql-query name="getAppFunctionOnCodeAndAppId">
2312                 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2313                 <![CDATA[
2314                 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2315                 ;
2316                 ]]>
2317         </sql-query>
2318
2319         <sql-query name="getGlobalRoleForRequestedApp">
2320                 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2321                 <![CDATA[
2322                 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
2323         from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2324         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
2325         ;
2326                 ]]>
2327         </sql-query>
2328
2329         <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2330                 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2331                 <![CDATA[
2332                 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
2333                 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2334                 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
2335                 ;
2336                 ]]>
2337         </sql-query>
2338
2339         <sql-query name="updateMenuFunctionalAndRoles">
2340                 <![CDATA[
2341                   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
2342                  ;
2343                 ]]>
2344         </sql-query>
2345
2346         <!-- Remove any favorites associated with a menu item that is associated with this app -->
2347         <sql-query name="removeAppFromMenuFavorites">
2348                 <![CDATA[
2349                 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
2350                 ;
2351                 ]]>
2352         </sql-query>
2353
2354         <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2355         <sql-query name="removeAppFromMenuFunctionalRoles">
2356                 <![CDATA[
2357                 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2358                 ;
2359                 ]]>
2360         </sql-query>
2361
2362         <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2363         <sql-query name="removeAppFromEpAppRoleFunction">
2364                 <![CDATA[
2365                 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2366                 ;
2367                 ]]>
2368         </sql-query>
2369
2370         <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2371         <sql-query name="removeAppFromEpAppFunction">
2372                 <![CDATA[
2373                 DELETE FROM ep_app_function WHERE app_id=:app_id
2374                 ;
2375                 ]]>
2376         </sql-query>
2377
2378         <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2379         <sql-query name="removeAppFromFnUserRole">
2380                 <![CDATA[
2381                 DELETE FROM fn_user_role WHERE app_id=:app_id
2382                 ;
2383                 ]]>
2384         </sql-query>
2385
2386         <!-- Remove any widgets that is associated with this app-->
2387         <sql-query name="removeAppFromEpWidgetCatalogRole">
2388                 <![CDATA[
2389                 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2390                 ;
2391                 ]]>
2392         </sql-query>
2393
2394         <!-- Remove any notifications that is associated with this app-->
2395         <sql-query name="removeAppFromEpRoleNotification">
2396                 <![CDATA[
2397                 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2398                 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2399                 ;
2400                 ]]>
2401         </sql-query>
2402
2403         <!-- Remove all records from fn_role that is associated with this app-->
2404         <sql-query name="removeAppFromFnRole">
2405                 <![CDATA[
2406                 DELETE FROM fn_role where app_id=:app_id
2407                 ;
2408                 ]]>
2409         </sql-query>
2410
2411         <!-- Remove app contact us entries that is associated with this app-->
2412         <sql-query name="removeAppFromAppContactUs">
2413                 <![CDATA[
2414                 DELETE FROM fn_app_contact_us where app_id=:app_id
2415                 ;
2416                 ]]>
2417         </sql-query>
2418
2419         <!-- Remove app personalization entries that is associated with this app-->
2420         <sql-query name="removeAppFromEpPersUserAppSel">
2421                 <![CDATA[
2422                 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2423                 ;
2424                 ]]>
2425         </sql-query>
2426
2427         <!-- Remove app personalization sorting entries that is associated with this app-->
2428         <sql-query name="removeAppFromEpPersUserAppManSort">
2429                 <![CDATA[
2430                 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2431                 ;
2432                 ]]>
2433         </sql-query>
2434
2435         <!-- Remove rows from user role request table entries that is associated with this app-->
2436         <sql-query name="removeAppFromEpUserRolesRequest">
2437                 <![CDATA[
2438                 DELETE FROM ep_user_roles_request where app_id=:app_id
2439                 ;
2440                 ]]>
2441         </sql-query>
2442
2443         <!-- Remove rows from web analytics table entries that is associated with this app-->
2444         <sql-query name="removeAppFromEpWebAnalytics">
2445                 <![CDATA[
2446                 DELETE FROM ep_web_analytics_source where app_id=:app_id
2447                 ;
2448                 ]]>
2449         </sql-query>
2450
2451         <!-- Remove row from fn app table -->
2452         <sql-query name="removeAppFromFnApp">
2453                 <![CDATA[
2454                 DELETE FROM fn_app where app_id=:app_id
2455                 ;
2456                 ]]>
2457         </sql-query>
2458
2459
2460         <query name="getBasicauthAccount">
2461       select id,password from BasicAuthCredentials
2462     </query>
2463
2464      <query name="getMicroserviceInfo">
2465       select id,password from MicroserviceData
2466     </query>
2467
2468         <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2469                 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2470                 <![CDATA[
2471                 select * from fn_user_role where user_id= :userId
2472                 and role_id= :roleId
2473                 and app_id= :appId
2474                 ;
2475                 ]]>
2476         </sql-query>
2477
2478         <sql-query name="userAppGlobalRoles">
2479                 <return alias="userAppGlobalRoles"
2480                         class="org.onap.portalapp.portal.domain.EPRole" />
2481        <![CDATA[
2482         select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2483         from fn_user_role a, fn_role fr, fn_user fu
2484         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
2485         ;
2486        ]]>
2487         </sql-query>
2488
2489         <sql-query name="getAllCentralizedAppsRoles">
2490                 <return alias="allCentralizedAppsRoles"
2491                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2492        <![CDATA[
2493         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
2494                 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 ;
2495        ]]>
2496         </sql-query>
2497
2498         <sql-query name="getUserCentralizedAppRoles">
2499                 <return alias="userCentralizedAppRoles"
2500                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2501        <![CDATA[
2502                 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
2503                 where fa.app_id = fur.app_id
2504                 and fr.role_id = fur.role_id
2505                 and fu.user_id = fur.user_id
2506                 and fu.ORG_USER_ID = :orgUserId
2507                 and fa.auth_namespace is not null
2508                 and fr.active_yn = 'Y'
2509                 order by role_id;
2510                 ]]>
2511         </sql-query>
2512         <sql-query name="getAprroverRoleFunctionsOfUser">
2513                 <![CDATA[
2514                 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2515                 where fu.role_id = ep.role_id
2516                 and fu.app_id = ep.app_id
2517                 and fu.user_id =:userId
2518                 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2519                 and exists
2520                 (
2521                 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
2522                 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2523                 );
2524                 ]]>
2525 </sql-query>
2526 <sql-query name="getUserApproverRoles">
2527                 <![CDATA[
2528                 select distinct fr.role_name from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_role fr
2529                 where fu.role_id = ep.role_id
2530                 and fu.app_id = ep.app_id
2531                 and fu.user_id = :userId
2532                 and fu.role_id = fr.role_id and fr.active_yn='Y'
2533                 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2534                 and exists
2535                 (
2536                 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
2537                 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2538                 );
2539                 ]]>
2540 </sql-query>
2541 <sql-query name="getAdminAppsForTheUser">
2542                 <![CDATA[
2543                 select fa.app_id  from fn_user_role ur,fn_app fa where ur.user_id =:userId and ur.app_id=fa.app_id and ur.role_id= 999 and (fa.enabled = 'Y' || fa.app_id=1)
2544
2545
2546                 ]]>
2547 </sql-query>
2548
2549
2550 <sql-query name="getUserRoleOnUserIdAndAppId">
2551                 <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
2552                 <![CDATA[
2553
2554                 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'
2555
2556                 ]]>
2557         </sql-query>
2558
2559         <sql-query name="getAllAdminAppsofTheUser">
2560                 <![CDATA[
2561                 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
2562
2563                 ]]>
2564     </sql-query>
2565         <sql-query name="getAllAppsFunctionsOfUser">
2566                         <![CDATA[
2567                         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
2568                         where fu.role_id = ep.role_id
2569                         and fu.app_id = ep.app_id
2570                         and fu.user_id =:userId
2571                         and ea.function_cd = ep.function_cd
2572             and ((fu.app_id = fa.app_id  and fa.enabled = 'Y' ) or (fa.app_id = 1))
2573             and fr.role_id = fu.role_id and fr.active_yn='Y'
2574                     union
2575             select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2576                         where role_id = 999
2577                         and app_r_f.function_cd = a_f.function_cd
2578                         and exists
2579                         (
2580                         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
2581                         and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2582                         );
2583                         ]]>
2584         </sql-query>
2585         <sql-query name="updateFnUser">
2586                 <![CDATA[
2587                         UPDATE fn_user fu SET fu.language_id=:language_id WHERE fu.login_id=:login_id
2588                 ]]>
2589         </sql-query>
2590 </hibernate-mapping>