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

   1 <?xml version="1.0"?>
   2 <!--
   3   ================================================================================
   4   ECOMP Portal
   5   ================================================================================
   6   Copyright (C) 2017 AT&T Intellectual Property
   7   ================================================================================
   8   Licensed under the Apache License, Version 2.0 (the "License");
   9   you may not use this file except in compliance with the License.
  10   You may obtain a copy of the License at
  11
  12        http://www.apache.org/licenses/LICENSE-2.0
  13
  14   Unless required by applicable law or agreed to in writing, software
  15   distributed under the License is distributed on an "AS IS" BASIS,
  16   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17   See the License for the specific language governing permissions and
  18   limitations under the License.
  19   ================================================================================
  20   -->
  21 <!DOCTYPE hibernate-mapping PUBLIC
  22         "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  23         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
  24
  25 <!-- Publishes mappings and queries specific to the ECOMP Portal application. -->
  26 <hibernate-mapping package="org.openecomp.portalapp.portal.domain">
  27
  28         <!-- Widget class mapping details -->
  29         <class name="Widget" table="FN_WIDGET">
  30                 <id name="id" column="WIDGET_ID">
  31                         <generator class="native">
  32                                 <param name="sequence">seq_fn_widget</param>
  33                         </generator>
  34                 </id>
  35                 <property name="width" type="integer">
  36                         <column name="WDG_WIDTH" not-null="true" default="0"></column>
  37                 </property>
  38                 <property name="height" type="integer">
  39                         <column name="WDG_HEIGHT" not-null="true" default="0"></column>
  40                 </property>
  41                 <property name="url" type="string">
  42                         <column name="WDG_URL" not-null="true" default="?"></column>
  43                 </property>
  44                 <property name="name" type="string">
  45                         <column name="WDG_NAME" not-null="true" default="?"></column>
  46                 </property>
  47
  48                 <property name="appId" type="long">
  49                         <column name="APP_ID" not-null="true"></column>
  50                 </property>
  51
  52         </class>
  53
  54         <class name="EPEndpoint" table="EP_ENDPOINTS">
  55                 <id name="id" column="id">
  56                         <generator class="native">
  57                                 <param name="sequence">seq_ep_endpoint</param>
  58                         </generator>
  59                 </id>
  60                 <property name="name" type="string">
  61                         <column name="url" not-null="true"></column>
  62                 </property>
  63         </class>
  64
  65
  66         <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
  67                 <id name="id" column="id">
  68                         <generator class="native">
  69                                 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
  70                         </generator>
  71                 </id>
  72                 <property name="ep_id" type="long">
  73                         <column name="ep_id"></column>
  74                 </property>
  75                 <property name="account_id" type="long">
  76                         <column name="account_id"></column>
  77                 </property>
  78         </class>
  79
  80         <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
  81                 <id name="id" column="id">
  82                         <generator class="native">
  83                                 <param name="sequence">seq_ep_widget_catalog_parameter</param>
  84                         </generator>
  85                 </id>
  86                 <property name="widgetId" type="long">
  87                         <column name="widget_id"></column>
  88                 </property>
  89                 <property name="userId" type="long">
  90                         <column name="user_id" not-null="true"></column>
  91                 </property>
  92                 <property name="paramId" type="long">
  93                         <column name="param_id" not-null="true"></column>
  94                 </property>
  95
  96                 <property name="user_value" type="string">
  97                         <column name="user_value" not-null="true"></column>
  98                 </property>
  99
 100         </class>
 101
 102         <class name="BasicAuthCredentials" table="ep_basic_auth_account">
 103         <id name="id" column="id">
 104             <generator class="native">
 105                                 <param name="sequence">seq_ep_basic_auth_account</param>
 106                         </generator>
 107         </id>
 108                 <property name="applicationName" type="string">
 109                         <column name="ext_app_name" not-null="true" default="?"></column>
 110                 </property>
 111                 <property name="username" type="string">
 112                         <column name="username"></column>
 113                 </property>
 114                 <property name="password" type="string">
 115                         <column name="password"></column>
 116                 </property>
 117                 <property name="isActive" type="string">
 118                         <column name="active_yn" not-null="true" default="Y"></column>
 119                 </property>
 120     </class>
 121
 122         <!-- EPUserNotification class mapping details -->
 123         <class name="EPUserNotification" table="ep_user_notification">
 124                 <id name="id" column="id">
 125                         <generator class="native">
 126                                 <param name="sequence">seq_ep_user_notification</param>
 127                         </generator>
 128                 </id>
 129                 <property name="userId" column="User_ID" />
 130                 <property name="notificationId" column="notification_ID" />
 131                 <property name="viewed" column="is_viewed" />
 132                 <property name="updateTime" column="updated_time" />
 133         </class>
 134
 135
 136         <!-- User class mapping details -->
 137         <class name="EPUser" table="FN_USER">
 138                 <id name="id" column="user_id">
 139                         <generator class="native">
 140                                 <param name="sequence">seq_fn_user</param>
 141                         </generator>
 142                 </id>
 143
 144                 <property name="orgId" column="org_id" />
 145                 <property name="managerId" column="manager_id" />
 146                 <property name="firstName" column="first_name" />
 147                 <property name="middleInitial" column="middle_name" />
 148                 <property name="lastName" column="last_name" />
 149                 <property name="phone" column="phone" />
 150                 <property name="fax" column="fax" />
 151                 <property name="cellular" column="cellular" />
 152                 <property name="email" column="email" />
 153                 <property name="addressId" column="address_id" />
 154                 <property name="alertMethodCd" column="alert_method_cd" />
 155
 156                 <property name="address1" column="address_line_1" />
 157                 <property name="address2" column="address_line_2" />
 158                 <property name="city" column="city" />
 159                 <property name="state" column="state_cd" />
 160                 <property name="zipCode" column="zip_code" />
 161                 <property name="country" column="country_cd" />
 162
 163                 <property name="hrid" column="hrid" />
 164                 <property name="orgUserId" column="org_user_id" />
 165                 <property name="orgCode" column="org_code" />
 166                 <property name="loginId" column="login_id" />
 167                 <property name="loginPwd" column="login_pwd" />
 168                 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
 169
 170                 <property name="locationClli" column="location_clli" />
 171                 <property name="orgManagerUserId" column="org_manager_userid" />
 172                 <property name="company" column="company" />
 173                 <property name="department" column="department" />
 174                 <property name="departmentName" column="department_name" />
 175                 <property name="businessUnit" column="business_unit" />
 176                 <property name="businessUnitName" column="business_unit_name" />
 177                 <property name="jobTitle" column="job_title" />
 178                 <property name="siloStatus" column="silo_status" />
 179                 <property name="financialLocCode" column="fin_loc_code" />
 180
 181                 <property name="active" column="active_yn" type="yes_no" />
 182                 <property name="internal" column="is_internal_yn" type="yes_no" />
 183
 184                 <property name="created" type="timestamp" column="created_date" />
 185                 <property name="modified" type="timestamp" column="modified_date" />
 186
 187                 <property name="createdId" column="created_id" />
 188                 <property name="modifiedId" column="modified_id" />
 189                 <property name="timeZoneId" column="timezone" />
 190
 191                 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
 192                         inverse="true">
 193                         <key column="user_id" />
 194                         <one-to-many class="org.openecomp.portalapp.portal.domain.EPUserApp" />
 195                 </set>
 196
 197         </class>
 198
 199         <class name="MicroserviceData" table="ep_microservice">
 200                 <id name="id" column="id">
 201                         <generator class="native">
 202                                 <param name="sequence">seq_ep_microservice</param>
 203                         </generator>
 204                 </id>
 205
 206                 <property name="name" type="string">
 207                         <column name="name" not-null="true" default="?"></column>
 208                 </property>
 209
 210                 <property name="desc" type="string">
 211                         <column name="description" not-null="true" default="?"></column>
 212                 </property>
 213
 214                 <property name="appId" type="long">
 215                         <column name="appId" not-null="true"></column>
 216                 </property>
 217
 218                 <property name="url" type="string">
 219                         <column name="endpoint_url" not-null="true" default="?"></column>
 220                 </property>
 221
 222                 <property name="securityType" type="string">
 223                         <column name="security_type" not-null="true" default="?"></column>
 224                 </property>
 225
 226
 227                 <property name="username" type="string">
 228                         <column name="username" not-null="true" default="?"></column>
 229                 </property>
 230
 231                 <property name="password" type="string">
 232                         <column name="password" not-null="true" default="?"></column>
 233                 </property>
 234
 235                 <property name="active" type="string">
 236                         <column name="active" not-null="true" default="Y"></column>
 237                 </property>
 238         </class>
 239
 240         <class name="MicroserviceParameter" table="ep_microservice_parameter">
 241                 <id name="id" column="id">
 242                         <generator class="native">
 243                                 <param name="sequence">seq_ep_microservice_parameter</param>
 244                         </generator>
 245                 </id>
 246
 247                 <property name="serviceId" type="long">
 248                         <column name="service_id" not-null="true"></column>
 249                 </property>
 250
 251                 <property name="para_key" type="string">
 252                         <column name="para_key" not-null="true" default="?"></column>
 253                 </property>
 254
 255                 <property name="para_value" type="string">
 256                         <column name="para_value" not-null="true" default="?"></column>
 257                 </property>
 258         </class>
 259
 260         <!-- UserApp class mapping details -->
 261         <class name="EPUserApp" table="fn_user_role">
 262                 <composite-id>
 263                         <key-property name="userId" type="long">
 264                                 <column name="user_id" precision="11" scale="0" />
 265                         </key-property>
 266                         <key-many-to-one name="app"
 267                                 class="org.openecomp.portalapp.portal.domain.EPApp" column="app_id" />
 268                         <key-many-to-one name="role"
 269                                 class="org.openecomp.portalapp.portal.domain.EPRole" column="role_id" />
 270                 </composite-id>
 271                 <property name="priority" type="java.lang.Short">
 272                         <column name="priority" precision="4" scale="0" />
 273                 </property>
 274         </class>
 275
 276         <!-- User App class mapping details -->
 277         <class name="EPApp" table="fn_app">
 278                 <id name="id" column="app_id">
 279                         <generator class="native">
 280                                 <param name="sequence">seq_fn_app</param>
 281                         </generator>
 282                 </id>
 283                 <property name="name" type="string">
 284                         <column name="app_name" not-null="true" default="?"></column>
 285                 </property>
 286                 <property name="imageUrl" column="app_image_url" />
 287                 <property name="description" column="app_description" />
 288                 <property name="notes" column="app_notes" />
 289                 <property name="url" column="app_url" />
 290                 <property name="alternateUrl" column="app_alternate_url" />
 291                 <property name="appRestEndpoint" column="app_rest_endpoint" />
 292                 <property name="mlAppName" type="string">
 293                         <column name="ml_app_name" not-null="true" default="?"></column>
 294                 </property>
 295                 <property name="mlAppAdminId" type="string">
 296                         <column name="ml_app_admin_id" not-null="true" default="?"></column>
 297                 </property>
 298                 <property name="motsId" column="mots_id" />
 299                 <property name="appPassword" type="string">
 300                         <column name="app_password" not-null="true" default="?"></column>
 301                 </property>
 302                 <property name="thumbnail" column="thumbnail" />
 303                 <property name="username" type="string">
 304                         <column name="app_username" not-null="true" default="?"></column>
 305                 </property>
 306                 <property name="open" type="yes_no">
 307                         <column name="open" not-null="true" default="Y"></column>
 308                 </property>
 309                 <property name="enabled" type="yes_no">
 310                         <column name="enabled" not-null="true" default="N"></column>
 311                 </property>
 312                 <property name="uebTopicName" type="string">
 313                         <column name="ueb_topic_name"></column>
 314                 </property>
 315                 <property name="uebKey" type="string">
 316                         <column name="ueb_key"></column>
 317                 </property>
 318                 <property name="uebSecret" type="string">
 319                         <column name="ueb_secret"></column>
 320                 </property>
 321                 <property name="appType" type="integer">
 322                         <column name="app_type"></column>
 323                 </property>
 324                 <property name="centralAuth" type="yes_no">
 325                         <column name="auth_central"></column>
 326                 </property>
 327                 <property name="nameSpace" type="string">
 328                         <column name="auth_namespace"></column>
 329                 </property>
 330                 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
 331
 332         </class>
 333
 334         <!-- EPUserAppsSortPreference class mapping details -->
 335         <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
 336                 <id name="id" column="ID">
 337                         <generator class="native">
 338                                 <param name="sequence">seq_ep_usrappsrtprf</param>
 339                         </generator>
 340                 </id>
 341                 <property name="userId" column="USER_ID" />
 342                 <property name="sortPref" column="SORT_PREF" />
 343         </class>
 344
 345         <!-- EPUserAppsManualSortPreference class mapping details -->
 346         <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
 347                 <id name="id" column="ID">
 348                         <generator class="native">
 349                                 <param name="sequence">seq_ep_usrmanappsrtprf</param>
 350                         </generator>
 351                 </id>
 352                 <property name="userId" column="user_id"
 353                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 354                 <property name="appId" column="app_id"
 355                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 356                 <property name="appManualSortOrder" column="sort_order" type="int" />
 357         </class>
 358
 359         <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
 360                 <id name="id" column="ID">
 361                         <generator class="native">
 362                                 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
 363                         </generator>
 364                 </id>
 365                 <property name="userId" column="user_id" />
 366                 <property name="widgetId" column="widget_id" />
 367                 <property name="widgetRow" column="x" />
 368                 <property name="widgetCol" column="y" />
 369                 <property name="widgetHeight" column="height" />
 370                 <property name="widgetWidth" column="width" />
 371         </class>
 372
 373         <!-- User Role class mapping details -->
 374         <class name="EPRole" table="FN_ROLE">
 375                 <id name="id" column="role_id">
 376                         <generator class="native">
 377                                 <param name="sequence">seq_fn_role</param>
 378                         </generator>
 379                 </id>
 380
 381                 <property name="name" column="role_name" />
 382                 <property name="priority" column="priority" />
 383                 <property name="active" column="active_yn" type="yes_no" />
 384                 <!-- 2 lines below may be removed! -->
 385                 <property name="appId" column="APP_ID" />
 386                 <property name="appRoleId" column="APP_ROLE_ID" />
 387                 <!-- 2 lines above may be removed -->
 388
 389                 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
 390                         sort="natural">
 391                         <key column="role_id" />
 392                         <many-to-many column="function_cd"
 393                                 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
 394                 </set>
 395
 396                 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 397                         sort="natural">
 398                         <key column="parent_role_id" />
 399                         <many-to-many column="child_role_id"
 400                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 401                 </set>
 402
 403                 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 404                         sort="natural">
 405                         <key column="child_role_id" />
 406                         <many-to-many column="parent_role_id"
 407                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 408                 </set>
 409
 410         </class>
 411
 412         <!-- User App class mapping details -->
 413         <class name="AppContactUs" table="fn_app_contact_us">
 414                 <id name="id" column="app_id">
 415                         <generator class="foreign">
 416                                 <param name="property">app</param>
 417                         </generator>
 418                 </id>
 419                 <property name="url" column="url" />
 420                 <property name="description" column="description" />
 421                 <property name="contactName" column="contact_name" />
 422                 <property name="contactEmail" column="contact_email" />
 423                 <property name="activeYN" column="active_yn" />
 424
 425                 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
 426
 427         </class>
 428
 429         <!-- Personalization of user app selections -->
 430         <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
 431                 <id name="id" column="id">
 432                         <generator class="native">
 433                                 <param name="property">seq_fn_pers_user_app_sel</param>
 434                         </generator>
 435                 </id>
 436                 <property name="userId" column="user_id" />
 437                 <property name="appId" column="app_id" />
 438                 <property name="statusCode" column="status_cd" />
 439         </class>
 440
 441         <!-- EPAppRoleFunction class mapping details -->
 442         <class name="EPAppRoleFunction" table="ep_app_role_function">
 443                 <id name="id" column="id">
 444                         <generator class="native">
 445                                 <param name="property">seq_epp_app_role_func</param>
 446                         </generator>
 447                 </id>
 448                 <property name="roleId" column="role_id"></property>
 449                 <property name="appId" column="app_id"></property>
 450                 <property name="code" column="function_cd"></property>
 451         </class>
 452
 453         <!-- CentralRoleFunction class mapping details -->
 454         <class name="CentralRoleFunction" table="ep_app_function">
 455                 <composite-id>
 456                         <key-property name="appId"
 457                                 column="app_id" />
 458                         <key-property name="code"
 459                                 column="function_cd" />
 460                 </composite-id>
 461                 <property name="name" column="function_name" />
 462         </class>
 463
 464         <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
 465                 <id name="id" column="id">
 466                         <generator class="native">
 467                                 <param name="property">seq_ep_pers_user_widget_sel</param>
 468                         </generator>
 469                 </id>
 470                 <property name="userId" column="user_id" />
 471                 <property name="widgetId" column="widget_id" />
 472                 <property name="statusCode" column="status_cd" />
 473         </class>
 474
 475         <!-- EPUserAppRolesRequest class mapping details -->
 476         <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
 477                 <id name="id" column="req_id">
 478                         <generator class="native">
 479                                 <param name="sequence">seq_ep_user_role_request</param>
 480                         </generator>
 481                 </id>
 482                 <property name="userId" column="user_id" />
 483                 <property name="appId" column="app_id" />
 484                 <property name="createdDate" column="created_date" type="timestamp" />
 485                 <property name="updatedDate" column="updated_date" type="timestamp" />
 486                 <property name="requestStatus" column="request_status" />
 487
 488                 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
 489                         lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
 490                         <key column="req_id" />
 491                         <one-to-many
 492                                 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
 493                 </set>
 494         </class>
 495
 496         <!-- EPUserAppRolesRequestDetail class mapping details -->
 497         <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
 498                 <id name="id" column="id">
 499                         <generator class="native">
 500                                 <param name="sequence">seq_ep_user_role_request_det</param>
 501                         </generator>
 502                 </id>
 503                 <property name="reqRoleId" column="requested_role_id" />
 504                 <property name="reqType" column="request_type" />
 505                 <many-to-one name="epRequestIdData" fetch="select"
 506                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
 507                         <column name="req_id" not-null="true" />
 508                 </many-to-one>
 509         </class>
 510
 511         <!-- show the current user plus related users -->
 512         <sql-query name="relatedUsers">
 513                 <return-scalar column="org_user_id" type="java.lang.String" />
 514                 <![CDATA[
 515                         select t.org_user_id from (
 516                                 select distinct c.org_user_id org_user_id, c.last_login_date from
 517                                         (select c.org_user_id, c.last_login_date from
 518                                                 fn_user_role a,
 519                                                 (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,
 520                                                 fn_user c
 521                                                 where a.ROLE_ID = b.role_id
 522                                                 and a.APP_ID = b.app_id
 523                                                 and a.USER_ID = c.user_id
 524                                         union
 525                                         select org_user_id , last_login_date from fn_user where org_user_id=:userId
 526                                         ) c
 527                                 order by c.last_login_date desc limit 10
 528                         ) t
 529                 ]]>
 530         </sql-query>
 531
 532         <!-- requires values for named parameters :searchQuery and :userId -->
 533         <sql-query name="searchPortal">
 534                 <return alias="searchResult"
 535                         class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
 536                 <![CDATA[
 537                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 538                                 ( (
 539                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
 540                                 from fn_user where
 541                                 upper( :searchQuery ) != ''
 542                                 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
 543                                 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
 544                                 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
 545                                 and  upper(active_yn) = 'Y'
 546                                 )
 547                                 union
 548                                 (
 549                                 select distinct 'Application' CATEGORY, b1.app_name NAME,
 550                                 if (b1.app_type = 2,'false','true') UUID,
 551                                 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
 552                                 from
 553                                 (
 554                                 select c.*
 555                                 from fn_user_role a, fn_user b, fn_app c
 556                                 where a.USER_ID = b.user_id
 557                                 and upper(b.org_user_id) = upper(  :userId )
 558                                 and a.app_id = c.app_id
 559                                 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 560                                 and upper(c.ENABLED) = 'Y'
 561                                 ) a1 right outer join
 562                                 (select * from fn_app where
 563                                 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 564                                 and upper(ENABLED) = 'Y') b1
 565                                 on a1.APP_ID = b1.app_id
 566                                 )
 567                                 union
 568                                 (
 569                                 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
 570                                 if (b1.app_type = 2,'false','true') UUID,
 571                                 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
 572                                 from
 573                                 (select distinct  d.*, e.app_type from fn_user a,
 574                                 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
 575                                 where a.USER_ID = b.user_id
 576                                 and b.role_id = c.role_id
 577                                 and c.menu_id = d.menu_id
 578                                 and upper(a.org_user_id) = upper( :userId )
 579                                 and c.APP_ID = e.app_id
 580                                 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 581                                 and upper(d.active_yn) = 'Y'
 582                                 and upper(e.enabled) = 'Y'
 583                                 ) a1 right outer join
 584                                 (
 585                                 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
 586                                 where active_yn = 'Y'
 587                                 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 588                                 and a.menu_id = b.menu_id
 589                                 and b.app_id = c.app_id
 590                                 and c.enabled = 'Y'
 591                                 ) b1
 592                                 on a1.menu_id = b1.menu_id
 593                                 )
 594                                 union
 595                                 select distinct CATEGORY, NAME, UUID, TARGET
 596                                 from
 597                                 (
 598                                 (
 599                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 600                                 d.widget_id UUID,
 601                                 d.wdg_desc TARGET
 602                                 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
 603                                 where upper(a.org_user_id) = upper( :userId )
 604                                 and a.user_id = b.user_id
 605                                 and b.role_id = c.role_id
 606                                 and c.widget_id = d.widget_id
 607                                 and d.all_user_flag<>'Y'
 608                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 609                                 )
 610                                 union
 611                                 (
 612                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 613                                 d.widget_id UUID,
 614                                 d.wdg_desc TARGET
 615                                 from ep_widget_catalog d
 616                                 where d.all_user_flag='Y'
 617                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 618                                 )
 619                                 union
 620                                 (
 621                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 622                                 d.widget_id UUID,
 623                                 d.wdg_desc TARGET
 624                                 from fn_user a, fn_user_role b, ep_widget_catalog d
 625                                 where upper(a.org_user_id) = upper( :userId )
 626                                 and a.user_id = b.user_id
 627                                 and b.role_id = 1
 628                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 629                                 )
 630                                 ) y
 631                                 ) t,
 632                                 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
 633                                                                         ;
 634
 635                 ]]>
 636         </sql-query>
 637
 638         <!-- requires values for named parameters :searchQuery and :userId -->
 639         <sql-query name="getNotifications">
 640                 <return alias="notificationResult"
 641                         class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
 642                 <![CDATA[
 643                         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
 644                         (
 645
 646                         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
 647                         from
 648                                 (
 649                                 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
 650                                 from
 651                                 (
 652                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 653                                         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
 654                                 from ep_notification a, ep_role_notification b
 655                                 where a.notification_id = b.notification_id
 656                                 and (end_time is null ||  SYSDATE() <= end_time )
 657                                 and (start_time is null ||  SYSDATE() >= start_time)
 658                                 and a.is_for_all_roles = 'N'
 659                                 ) a,
 660                                 (
 661                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 662                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 663                                 where COALESCE(c.app_id,1) = d.app_id
 664                         and a.user_id = b.user_id
 665                                 and a.user_id = :user_id
 666                                 and b.role_id = c.role_id
 667                         and (d.enabled='Y' or d.app_id=1)
 668                                 )b
 669                                 where
 670                                 (
 671                                 a.role_id = b.role_id
 672                                 )
 673                                 union
 674                                 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
 675                                 from
 676                                 (
 677                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 678                                         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
 679                                 from ep_notification a, ep_role_notification b
 680                                 where a.notification_id = b.notification_id
 681                                 and (end_time is null ||  SYSDATE() <= end_time )
 682                                 and (start_time is null ||  SYSDATE() >= start_time)
 683                                 and a.is_for_all_roles = 'N'
 684                                 ) a
 685                                 where
 686                                 (
 687                                 a.recv_user_id=:user_id
 688                                 )
 689                                 union
 690                                 (
 691                                 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
 692                                 from ep_notification a
 693                                 where a.notification_id
 694                                 and (end_time is null ||  SYSDATE() <= end_time )
 695                                 and (start_time is null ||  SYSDATE() >= start_time)
 696                                 and a.is_for_all_roles = 'Y'
 697                                 )
 698                                 ) a
 699                                 where
 700                                         active_YN = 'Y'
 701                                 and
 702                                         not exists
 703                                 (
 704                                 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'
 705                                 )
 706                                 order by priority desc, created_date desc,start_Time desc
 707
 708
 709                          ) t,
 710                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 711                         ;
 712                 ]]>
 713
 714         </sql-query>
 715
 716                 <sql-query name="getUsersByOrgIdsNotifications">
 717                 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
 718                 <![CDATA[
 719                         SELECT * from fn_user where org_user_id in (:OrgIds)
 720                 ]]>
 721
 722         </sql-query>
 723
 724
 725         <!-- Gets all Admin notification history; accepts no parameters -->
 726         <sql-query name="getAdminNotificationHistoryVO">
 727                 <return alias="adminNotificationHistoryVOResult"
 728                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 729                 <![CDATA[
 730                 SELECT * from (
 731 SELECT
 732                         n.notification_ID AS notificationId,
 733                         n.is_for_online_users AS isForOnlineUsers,
 734                         n.is_for_all_roles AS isForAllRoles,
 735                         n.active_YN AS activeYn,
 736                         n.msg_header AS msgHeader,
 737                         n.msg_description AS msgDescription,
 738                         n.msg_source AS msgSource,
 739                         n.start_Time AS startTime,
 740                         n.end_time AS endTime,
 741                         n.priority,
 742                         n.creator_ID AS creatorId,
 743                         n.created_date AS createdDate,
 744                         n.notification_hyperlink AS notificationHyperlink,
 745                         u.org_user_id AS loginId
 746                 FROM
 747                         ep_notification n
 748                 LEFT JOIN
 749                         fn_user u on u.user_id = n.creator_id
 750                 WHERE
 751                         n.active_YN='Y' and msg_source='EP'
 752
 753       UNION
 754
 755       SELECT
 756                         n.notification_ID AS notificationId,
 757                         n.is_for_online_users AS isForOnlineUsers,
 758                         n.is_for_all_roles AS isForAllRoles,
 759                         n.active_YN AS activeYn,
 760                         n.msg_header AS msgHeader,
 761                         n.msg_description AS msgDescription,
 762                         n.msg_source AS msgSource,
 763                         n.start_Time AS startTime,
 764                         n.end_time AS endTime,
 765                         n.priority,
 766                         n.creator_ID AS creatorId,
 767                         n.created_date AS createdDate,
 768                         n.notification_hyperlink AS notificationHyperlink,
 769                         u.org_user_id AS loginId
 770                 FROM
 771                         ep_notification n
 772                 LEFT JOIN
 773                         fn_user u on u.user_id = n.creator_id
 774     JOIN
 775                         ep_role_notification r on r.notification_ID=n.notification_ID
 776                 WHERE
 777                         n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
 778       )n order by n.startTime desc
 779                 ]]>
 780         </sql-query>
 781
 782         <sql-query name="getEpNotificationAppRoles">
 783                 <return alias="notificationAppRoles"
 784                         class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
 785                 <!-- This query requires no parameters. -->
 786                 <![CDATA[
 787
 788 select  a.app_id, a.app_name, b.role_id, b.role_name from
 789 (select * from fn_app where app_id = 1) a,
 790 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
 791 union
 792 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
 793 from fn_app, fn_role
 794 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
 795
 796
 797
 798                 ]]>
 799         </sql-query>
 800
 801         <!-- Gets all notification history -->
 802         <sql-query name="getNotificationHistoryVO">
 803                 <return alias="notificationHistoryVOResult"
 804                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 805                 <![CDATA[
 806                 SELECT
 807                         notificationId, isForOnlineUsers, isForAllRoles, msgHeader,     msgDescription,msgSource,
 808                         startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId,     activeYn
 809                 FROM
 810                         (
 811                                         select distinct
 812                                         a.notification_ID AS notificationId,
 813                                         is_for_online_users AS isForOnlineUsers,
 814                                         is_for_all_roles AS isForAllRoles,
 815                                         msg_header AS msgHeader,
 816                                         msg_description AS msgDescription,
 817                                         msg_source AS msgSource,
 818                                         start_Time AS startTime,
 819                                         end_time AS endTime,
 820                                         priority,
 821                                         created_date AS createdDate,
 822                                         creator_ID AS creatorId,
 823                                         notification_hyperlink AS notificationHyperlink,
 824                                         login_id AS loginId,
 825                                         active_YN AS activeYn,
 826                                         if (is_viewed is null, 'N', is_viewed)
 827                         from
 828                         (
 829                                 select
 830                                         user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 831                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 832                                         creator_ID,notification_hyperlink,active_YN
 833                                 from
 834                                 (
 835                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 836                                         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,
 837                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 838
 839                    ELSE NULL
 840               END  AS login_id,b.recv_user_id
 841                                 from ep_notification a, ep_role_notification b,fn_user u
 842                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 843                                 and a.is_for_all_roles = 'N'
 844                                 and (
 845                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 846                                 or
 847                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 848                                 or
 849                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 850                                 or
 851                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 852                                 )
 853                                 ) a,
 854                                 (
 855                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 856                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 857                                 where COALESCE(c.app_id,1) = d.app_id
 858                         and a.user_id = b.user_id
 859                                 and a.user_id = :user_id
 860                                 and b.role_id = c.role_id
 861                         and (d.enabled='Y' or d.app_id=1)
 862                                 ) b
 863                                 where
 864                                 (
 865                                 a.role_id = b.role_id
 866                                 )
 867                                    UNION
 868                         select
 869                                         :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 870                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 871                                         creator_ID,notification_hyperlink,active_YN
 872                                 from
 873                                 (
 874                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 875                                         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,
 876                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 877
 878                    ELSE NULL
 879               END  AS login_id,b.recv_user_id
 880                                 from ep_notification a, ep_role_notification b,fn_user u
 881                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 882                                 and a.is_for_all_roles = 'N'
 883                                 and (
 884                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 885                                 or
 886                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 887                                 or
 888                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 889                                 or
 890                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 891                                 )
 892                                 ) a
 893                         where
 894                         (
 895                         a.recv_user_id=:user_id
 896                         )
 897                                 union
 898                                 (
 899                                 select
 900                                         :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
 901                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
 902                                         creator_ID, a.notification_hyperlink,a.active_YN
 903                                 from ep_notification a  JOIN fn_user b on b.user_id=a.creator_ID
 904                                 where a.notification_id
 905                                 and a.is_for_all_roles = 'Y'
 906                                 and (
 907                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 908                                 or
 909                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 910                                 or
 911                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 912                                 or
 913                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 914                                 )
 915                                 )
 916                                 ) a left outer join (
 917                                 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
 918                                 ) m
 919                                 on  a.notification_id = m.notification_ID
 920                                 where
 921                                 active_YN = 'Y'
 922
 923                                 order by  start_Time desc,end_time desc
 924                         ) t,
 925                      (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
 926                 ]]>
 927
 928         </sql-query>
 929
 930                 <!-- shows the received recipient to whom the notification is delivered from external system -->
 931         <sql-query name="messageRecipients">
 932                 <return-scalar column="org_user_id" type="java.lang.String" />
 933                 <![CDATA[
 934                         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
 935                 ]]>
 936         </sql-query>
 937
 938
 939         <!-- Gets role details for a specified notification -->
 940         <sql-query name="getNotificationRoles">
 941                 <return alias="notificationRolesResult"
 942                         class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
 943                 <![CDATA[
 944                         SELECT *
 945                         FROM
 946                                 ep_role_notification
 947                         WHERE
 948                                 notification_Id = :notificationId
 949                 ]]>
 950         </sql-query>
 951
 952         <!-- Gets all applications, possibly with contact information -->
 953         <sql-query name="getAppsAndContacts">
 954                 <return alias="appContactUs"
 955                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 956                 <!-- This query requires no parameters. -->
 957                 <![CDATA[
 958                         select
 959                                 a.app_id as appId, a.app_name as appName,
 960                                 c.contact_name as contactName,
 961                                 c.contact_email as contactEmail, c.url, c.description,
 962                                 c.active_yn as activeYN
 963                         from
 964                                 fn_app a
 965                         left join
 966                                 fn_app_contact_us c
 967                                 on a.app_id = c.app_id
 968                         where
 969                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 970                         ;
 971                 ]]>
 972         </sql-query>
 973
 974         <!-- Gets all rows from the app-contact-us table, extended with app information -->
 975         <sql-query name="getAppContactUsItems">
 976                 <return alias="appContactUs"
 977                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 978                 <!-- This query requires no parameters. -->
 979                 <![CDATA[
 980                         select
 981                                 c.app_id as appId, c.contact_name as contactName,
 982                                 c.contact_email as contactEmail, c.url, c.description,
 983                                 c.active_yn as activeYN, a.app_name as appName
 984                         from
 985                                 fn_app_contact_us c
 986                         left join
 987                                 fn_app a
 988                                 on a.app_id = c.app_id
 989                         where
 990                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 991                         ;
 992                 ]]>
 993         </sql-query>
 994
 995         <!-- Gets one row for each app with the category and catenated functions. -->
 996         <sql-query name="getAppCategoryFunctions">
 997                 <return alias="appCategoryFunctions"
 998                         class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
 999                 <!-- This query requires no parameters. -->
1000                 <![CDATA[
1001                         SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1002                                 r.category as category, r.app_id as appId, r.app_name as application,
1003                                 group_concat(function_text separator ', ') as functions
1004                         FROM (
1005                                 SELECT
1006                                 app_id, function_text, app_name,
1007                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1008                                 FROM (
1009                                         SELECT
1010                                         j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1011                                         k.text parent_menu, k.parent_menu_id parent_menu_id
1012                                         FROM (
1013                                                 SELECT distinct
1014                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1015                                                 FROM
1016                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1017                                                 WHERE
1018                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1019                                                 ) j,
1020                                         fn_menu_functional k
1021                                         WHERE j.parent_menu_id = k.menu_id
1022                                         ) fn,
1023                                 fn_menu_functional a
1024                                 WHERE fn.parent_menu_id = a.menu_id
1025                         ) r,
1026                         (SELECT @rn /*'*/:=/*'*/ 0) t2
1027                         group by r.category, r.app_id, r.app_name
1028                         order by category, app_name
1029                         ;
1030                 ]]>
1031         </sql-query>
1032
1033         <!-- Gets one row for each function-application-role combination. -->
1034         <sql-query name="getAppAccessFunctionRole">
1035                 <return alias="appAccessFunctionRole"
1036                         class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
1037                 <!-- This query requires one parameters. -->
1038                 <![CDATA[
1039                         select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1040                         (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,
1041                         o.requested_role_id,o.request_type
1042                         from
1043                         (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1044                         from
1045                         (
1046                         select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1047                         from
1048                         FN_ROLE C, FN_APP D
1049                         where COALESCE(c.app_id,1) = d.app_id
1050                         and (d.enabled='Y' or d.app_id=1)
1051                         and c.active_yn = 'Y'
1052                         ) a left outer join
1053                         (
1054                         select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1055                         WHERE
1056                         A.MENU_ID = B.MENU_ID
1057                         and active_yn = 'Y'
1058                         )b
1059                         on a.role_id = b.role_id) t left outer join
1060                         (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1061                         where er.req_id=ed.req_id
1062                         and upper(ed.request_type)='P'
1063                         and er.user_id =:userId) o
1064                         on t.app_id=o.app_id
1065                         and t.role_id = o.requested_role_id
1066                         JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1067                         ;
1068                 ]]>
1069         </sql-query>
1070
1071         <query name="getCommonWidgetItem">
1072                 from CommonWidget where category = :cat order by
1073                 sortOrder, title
1074         </query>
1075
1076         <sql-query name="getGuestLastLogin">
1077                 <return-scalar column="audit_date" type="java.util.Date" />
1078                 <![CDATA[
1079                         select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1080                 ]]>
1081         </sql-query>
1082
1083         <sql-query name="getActiveUsers">
1084                 <return alias="EPUser" class="org.openecomp.portalapp.portal.domain.EPUser" />
1085                 <![CDATA[
1086                 SELECT * FROM FN_USER WHERE active_yn='Y'
1087                 ]]>
1088         </sql-query>
1089
1090         <sql-query name="getAppsAdmins">
1091                 <return alias="adminUserApp" class="org.openecomp.portalapp.portal.domain.AdminUserApp" />
1092         <![CDATA[
1093         SELECT apps.APP_NAME, apps.APP_ID, user.USER_ID, user.FIRST_NAME, user.LAST_NAME, user.org_user_id FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_app apps ON apps.APP_ID = userrole.APP_ID  WHERE user.active_yn='Y' AND userrole.ROLE_ID = :accountAdminRoleId AND (apps.ENABLED = 'Y' OR apps.APP_ID=1)
1094         ]]>
1095         </sql-query>
1096
1097         <sql-query name="getPortalAdmins">
1098                 <return alias="portalAdmin" class="org.openecomp.portalapp.portal.transport.PortalAdmin" />
1099         <![CDATA[
1100                 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
1101         ]]>
1102         </sql-query>
1103
1104         <!-- Gets personalized list of enabled and accessible apps for regular user -->
1105         <sql-query name="getPersUserApps">
1106                 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1107                 <!-- This query requires one parameter: userId (number) -->
1108                 <![CDATA[
1109                     select
1110                                 distinct  -- multiple roles yield multiple rows
1111                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1112                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1113                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1114                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1115                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL
1116                     from FN_APP a
1117                     -- Portal assigns role 999 to app administrator
1118                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1119                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1120                         where a.ENABLED = 'Y'
1121                     and (
1122                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1123                         or
1124                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1125                         )
1126                         order by app_name
1127                     ;
1128                 ]]>
1129         </sql-query>
1130
1131         <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1132                 admin -->
1133         <sql-query name="getPersAdminApps">
1134                 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1135                 <!-- This query requires one parameter: userId (number) -->
1136                 <![CDATA[
1137                     select
1138                                 distinct  -- multiple roles yield multiple rows
1139                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1140                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1141                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1142                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1143                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE      , a.AUTH_CENTRAL ,
1144                                 a.AUTH_NAMESPACE
1145                     from FN_APP a
1146                     -- Portal assigns role 999 to app administrator
1147                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1148                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1149                         where a.ENABLED = 'Y'
1150                     and (
1151                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1152                         or
1153                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1154                                 or
1155                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1156                         )
1157                         order by app_name
1158                     ;
1159                 ]]>
1160         </sql-query>
1161
1162         <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1163         <sql-query name="getPersAdminAppsOrderByName">
1164                 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1165                 <!-- This query requires one parameter: userId (number) -->
1166                 <![CDATA[
1167                  select
1168                                 distinct  -- multiple roles yield multiple rows
1169                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1170                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1171                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1172                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1173                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1174                                 a.AUTH_NAMESPACE
1175                     from FN_APP a
1176                     -- Portal assigns role 999 to app administrator
1177                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1178                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1179                         where a.ENABLED = 'Y'
1180                     and (
1181                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1182                         or
1183                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1184                                 or
1185                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1186                         )
1187                         order by app_name
1188                     ;
1189
1190                 ]]>
1191         </sql-query>
1192
1193         <!--Gets personalized list of enabled and accessible User apps by name -->
1194         <sql-query name="getPersUserAppsOrderByName">
1195                 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1196                 <!-- This query requires one parameter: userId (number) -->
1197                 <![CDATA[
1198                 select
1199                                 distinct  -- multiple roles yield multiple rows
1200                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1201                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1202                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1203                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1204                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1205                                 a.AUTH_NAMESPACE
1206                     from FN_APP a
1207                     -- Portal assigns role 999 to app administrator
1208                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1209                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1210                         where a.ENABLED = 'Y'
1211                     and (
1212                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1213                         or
1214                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1215                         )
1216                         order by app_name
1217                     ;
1218
1219                 ]]>
1220         </sql-query>
1221
1222
1223         <!--Gets personalized list of enabled and accessible Super Admin apps by
1224                 LastUsed -->
1225         <sql-query name="getAdminAppsOrderByLastUsed">
1226                 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1227
1228                 <![CDATA[
1229                     select
1230                         *
1231                         from (select
1232                                 distinct  -- multiple roles yield multiple rows
1233                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1234                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1235                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1236                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1237                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1238                                 a.AUTH_NAMESPACE
1239                     from FN_APP a
1240                     -- Portal assigns role 999 to app administrator
1241                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1242                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1243                         where a.ENABLED = 'Y'
1244                     and (
1245                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1246                         or
1247                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1248                                 or
1249                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1250                         )
1251                         ) A
1252                 left outer join
1253                         (select  distinct
1254                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1255                         from FN_AUDIT_LOG c
1256                          )B
1257                         on A.app_id = B.Affected_record_id
1258                         order by AUDIT_DATE DESC;
1259                         ]]>
1260         </sql-query>
1261
1262         <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1263         <sql-query name="getUserAppsOrderByLastUsed">
1264                 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1265
1266                 <![CDATA[
1267                         select * from (select
1268                                 distinct  -- multiple roles yield multiple rows
1269                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1270                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1271                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1272                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1273                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1274                                 a.AUTH_NAMESPACE
1275                     from FN_APP a
1276                     -- Portal assigns role 999 to app administrator
1277                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1278                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1279                         where a.ENABLED = 'Y'
1280                     and (
1281                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1282                         or
1283                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1284                         )) A
1285                 left outer join
1286                         (select  distinct
1287                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1288                         from FN_AUDIT_LOG c
1289                          )B
1290                         on A.app_id = B.Affected_record_id
1291                         order by AUDIT_DATE DESC;
1292
1293
1294                 ]]>
1295
1296         </sql-query>
1297
1298         <!--Gets personalized list of enabled and accessible Super Admin apps by
1299                 Most Used -->
1300         <sql-query name="getAdminAppsOrderByMostUsed">
1301                 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1302
1303                 <![CDATA[
1304                         select
1305                         *
1306                         from (select
1307                                 distinct  -- multiple roles yield multiple rows
1308                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1309                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1310                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1311                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1312                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1313                                 a.AUTH_NAMESPACE
1314                     from FN_APP a
1315                     -- Portal assigns role 999 to app administrator
1316                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1317                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1318                         where a.ENABLED = 'Y'
1319                     and (
1320                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1321                         or
1322                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1323                                 or
1324                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1325                         )
1326                         ) A
1327                         left outer join
1328                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1329                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1330                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1331                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1332                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1333                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1334                          )B
1335                         on A.app_id = B.Affected_record_id
1336                         order by total_visits DESC;
1337                 ]]>
1338
1339         </sql-query>
1340
1341         <!--Gets personalized list of enabled and accessible NON Super Admin User
1342                 apps by Most Used -->
1343         <sql-query name="getUserAppsOrderByMostUsed">
1344                 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1345
1346                 <![CDATA[
1347                         select * from
1348                         (select
1349                                 distinct  -- multiple roles yield multiple rows
1350                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1351                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1352                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1353                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1354                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE             , a.AUTH_CENTRAL ,
1355                                 a.AUTH_NAMESPACE
1356                     from FN_APP a
1357                     -- Portal assigns role 999 to app administrator
1358                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1359                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1360                         where a.ENABLED = 'Y'
1361                     and (
1362                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1363                         or
1364                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1365                         ))A
1366                         left outer join
1367                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1368                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1369                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1370                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1371                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1372                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1373                          )B
1374                         on A.app_id = B.Affected_record_id
1375                         order by total_visits DESC;
1376                 ]]>
1377         </sql-query>
1378
1379         <!--Gets personalized list of enabled and accessible Super Admin apps by
1380                 Manual -->
1381         <sql-query name="getAdminAppsOrderByManual">
1382                 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1383
1384                 <![CDATA[
1385                         select * from
1386                         (select
1387                                 distinct  -- multiple roles yield multiple rows
1388                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1389                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1390                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1391                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1392                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1393                                 a.AUTH_NAMESPACE
1394                     from FN_APP a
1395                     -- Portal assigns role 999 to app administrator
1396                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1397                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1398                         where a.ENABLED = 'Y'
1399                     and (
1400                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1401                         or
1402                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1403                                 or
1404                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1405                         )) A
1406                         left outer join
1407                          (select m.app_id, m.sort_order
1408                         from ep_pers_user_app_man_sort m
1409                         where USER_ID = :userId
1410                         ) B
1411                         on A.APP_ID = B.app_id
1412                         order by sort_order ASC
1413                     ;
1414                 ]]>
1415
1416         </sql-query>
1417
1418         <!--Gets personalized list of enabled and accessible NON Super admin User
1419                 apps by Manual -->
1420         <sql-query name="getUserAppsOrderByManual">
1421                 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1422
1423                 <![CDATA[
1424                                                         select * from   (select
1425                                 distinct  -- multiple roles yield multiple rows
1426                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1427                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1428                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1429                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1430                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1431                                 a.AUTH_NAMESPACE
1432                     from FN_APP a
1433                     -- Portal assigns role 999 to app administrator
1434                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1435                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1436                         where a.ENABLED = 'Y'
1437                     and (
1438                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1439                         or
1440                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1441                         )) A
1442                         left outer join
1443                          (select m.app_id, m.sort_order
1444                         from ep_pers_user_app_man_sort m
1445                         where USER_ID = :userId
1446                         ) B
1447                         on A.APP_ID = B.app_id
1448                         order by sort_order ASC
1449                     ;
1450                 ]]>
1451         </sql-query>
1452
1453         <!-- Gets regular user's list of enabled apps including accessible and select
1454                 statuses -->
1455         <sql-query name="getUserAppCatalog">
1456                 <return alias="userAppCatalog"
1457                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1458                 <!-- This query requires one parameter: userId (number) -->
1459                 <![CDATA[
1460                                                 select
1461                                 distinct  -- multiple roles yield multiple rows
1462                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1463                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1464                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1465                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1466                             IF(a.open = 'Y', TRUE, FALSE) as open,
1467                 -- ACCESS(-ible) means user has a defined role OR the application is open
1468                                 case
1469                                         -- regular app and user has a role
1470                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1471                     -- open app
1472                     when a.OPEN = 'Y' then TRUE
1473                     else FALSE
1474                 end as 'access' ,
1475                 -- SELECT(-ed) indicates user personalization
1476                 case
1477                                         -- regular app, user has a role, no personalization
1478                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1479                     -- open app and has personalization
1480                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1481                     else FALSE
1482                 end as 'select' ,
1483                 -- PENDING indicates user checked a box
1484                 case
1485                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1486                         else FALSE
1487                 end as 'pending'
1488                     from FN_APP a
1489                     -- Portal assigns role 999 to app administrator
1490             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1491                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1492       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'
1493       where a.ENABLED = 'Y'
1494
1495                         -- Show accessible apps first, then the rest; sort by name within each set.
1496             order by access desc, app_name asc
1497                     ;
1498                 ]]>
1499         </sql-query>
1500
1501         <sql-query name="getMyloginAppDetails">
1502                 <return alias="myloginAppDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1503
1504                 <![CDATA[
1505                 SELECT * FROM fn_app  where ml_app_name =:appName
1506                 ;
1507                 ]]>
1508         </sql-query>
1509
1510                 <sql-query name="getMyAppDetailsByUebKey">
1511                 <return alias="myAppDetailsByUebKey" class="org.openecomp.portalapp.portal.domain.EPApp" />
1512
1513                 <![CDATA[
1514
1515                 SELECT * FROM fn_app  where ueb_key =:appKey
1516                 ;
1517                 ]]>
1518         </sql-query>
1519
1520
1521         <sql-query name="deleteAccountEndpoint">
1522                 <![CDATA[
1523                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1524                 ;
1525                 ]]>
1526         </sql-query>
1527
1528         <sql-query name="deleteAccountEndpointRecord">
1529                 <![CDATA[
1530                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1531                 ;
1532                 ]]>
1533         </sql-query>
1534
1535         <sql-query name="deleteEPEndpoint">
1536                 <![CDATA[
1537                 DELETE FROM ep_endpoints WHERE id =:epId
1538                 ;
1539                 ]]>
1540         </sql-query>
1541
1542         <sql-query name="deleteBasicAuthAccount">
1543                 <![CDATA[
1544                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1545                 ;
1546                 ]]>
1547         </sql-query>
1548
1549         <sql-query name="getAppRoles">
1550                 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1551
1552                 <![CDATA[
1553                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1554                 ;
1555                 ]]>
1556         </sql-query>
1557
1558         <sql-query name="getPortalAppRoles">
1559                 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1560
1561                 <![CDATA[
1562
1563                 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1564                 ;
1565                 ]]>
1566         </sql-query>
1567
1568         <sql-query name="deleteMicroserviceParameter">
1569                 <![CDATA[
1570
1571                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1572                 ;
1573                 ]]>
1574         </sql-query>
1575
1576         <sql-query name="deleteMicroserviceParameterById">
1577                 <![CDATA[
1578
1579                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1580                 ;
1581                 ]]>
1582         </sql-query>
1583
1584         <sql-query name="deleteWidgetCatalogParameter">
1585                 <![CDATA[
1586
1587                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1588                 ;
1589                 ]]>
1590         </sql-query>
1591
1592         <sql-query name="deleteMicroservice">
1593                 <![CDATA[
1594
1595                 DELETE FROM ep_microservice WHERE id =:serviceId
1596                 ;
1597                 ]]>
1598         </sql-query>
1599
1600         <sql-query name="epUserAppId">
1601                 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1602
1603                 <![CDATA[
1604
1605                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1606                 ;
1607                 ]]>
1608         </sql-query>
1609
1610         <sql-query name="userAppsSortPreferenceQuery">
1611                 <return alias="appsSortPreferenceQuery"
1612                         class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1613
1614                 <![CDATA[
1615
1616                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1617
1618                 ]]>
1619         </sql-query>
1620
1621         <sql-query name="userAppsManualSortPrfQuery">
1622                 <return alias="AppsManualSortPrfQuery"
1623                         class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1624
1625                 <![CDATA[
1626
1627                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1628
1629                 ]]>
1630         </sql-query>
1631
1632         <sql-query name="userWidgetManualSortPrfQuery">
1633                 <return alias="widgetManualSortPrfQuery"
1634                         class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1635                 <![CDATA[
1636                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1637                 ]]>
1638         </sql-query>
1639
1640         <sql-query name="appRoles">
1641                 <return alias="rolesForApp"
1642                         class="org.openecomp.portalapp.portal.domain.EPUserAppRoles" />
1643                 <![CDATA[
1644                         SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1645                         ;
1646                 ]]>
1647         </sql-query>
1648
1649         <sql-query name="userAppRolesRequestList">
1650                 <return alias="appRolesRequestList"
1651                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1652                 <![CDATA[
1653                         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'
1654                         ;
1655                 ]]>
1656         </sql-query>
1657
1658         <sql-query name="userAppRolesRequestDetailList">
1659                 <return alias="appRolesRequestDetailList"
1660                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1661                 <![CDATA[
1662
1663                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1664                         ;
1665                 ]]>
1666         </sql-query>
1667
1668         <!-- Gets list of enabled apps including accessible and select statuses -->
1669         <sql-query name="getAdminAppCatalog">
1670                 <return alias="adminAppCatalog"
1671                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1672                 <!-- This query requires one parameter: userId (number) -->
1673                 <![CDATA[
1674                         select
1675                                 distinct  -- multiple roles yield multiple rows
1676                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1677                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1678                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1679                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1680                             IF(a.open = 'Y', TRUE, FALSE) as open,
1681                 -- ACCESS(-ible) means user has a defined role OR the application is open
1682                                 case
1683                                         -- regular app and user has a role
1684                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1685                     -- open app
1686                     when a.OPEN = 'Y' then TRUE
1687                     else FALSE
1688                 end as 'access' ,
1689                 -- SELECT(-ed) indicates user personalization
1690                 case
1691                                         -- regular app, user has a role, no personalization
1692                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1693                                         -- regular app, user has role, admin forced a personalization
1694                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1695                                         -- regular app, user has no role, admin forced a personalization
1696                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1697                     -- open app and has personalization
1698                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1699                     else FALSE
1700                 end as 'select' ,
1701                 -- PENDING indicates user checked a box
1702                 case
1703                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1704                         else FALSE
1705                 end as 'pending'
1706                     from FN_APP a
1707                     -- Portal assigns role 999 to app administrator
1708             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1709                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1710                         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'
1711                         where a.ENABLED = 'Y'
1712                         -- Show accessible apps first, then the rest; sort by name within each set.
1713             order by access desc, app_name asc
1714                     ;
1715                 ]]>
1716         </sql-query>
1717
1718         <sql-query name="userAppCatalogRoles">
1719                 <return alias="epUserAppCatalogRoles"
1720                         class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1721                 <![CDATA[
1722         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
1723        from ep_user_roles_request A left join ep_user_roles_request_det B
1724                    on a.req_id = b.req_id
1725                    where  A.user_id=:userid
1726        and A.app_id IN (select app_id from fn_app where app_name=:appName)
1727        and A.request_status ='P'
1728        ;
1729                 ]]>
1730         </sql-query>
1731
1732         <sql-query name="getUserApproles">
1733                 <return alias="businessCardUserApplicationRoles"
1734                         class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1735
1736                 <![CDATA[
1737
1738                 select   fr.role_name, fa.app_name
1739           from
1740         fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1741           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)
1742           ;
1743
1744                 ]]>
1745         </sql-query>
1746
1747     <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1748         <sql-query name="deleteNotificationsFromEpNotificationTable">
1749
1750                 <![CDATA[
1751                 delete from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1752                 ]]>
1753         </sql-query>
1754
1755
1756                 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1757         <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1758
1759                 <![CDATA[
1760         delete from ep_role_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1761                 ]]>
1762         </sql-query>
1763
1764         <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1765         <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1766
1767                 <![CDATA[
1768         delete from ep_user_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1769                 ]]>
1770         </sql-query>
1771
1772         <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1773         <sql-query name="getAppRoleFunctionList">
1774                 <return alias="appRoleFunctionList"
1775                         class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1776                 <![CDATA[
1777
1778                         SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1779                         where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1780                         ;
1781
1782                 ]]>
1783         </sql-query>
1784
1785         <!-- Gets list of all role functions -->
1786         <sql-query name="getAllRoleFunctions">
1787                 <return alias="allRoleFunctions"
1788                         class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1789                 <![CDATA[
1790
1791                         SELECT * from ep_app_function where app_id =:appId
1792                         ;
1793
1794                 ]]>
1795         </sql-query>
1796
1797         <!-- Get ep_app_function records provided by single parameter -->
1798         <sql-query name="getRoleFunction">
1799                 <return alias="RoleFunction"
1800                         class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1801                         <![CDATA[
1802
1803                         SELECT * from ep_app_function where function_cd = :functionCode and app_id =:appId
1804                         ;
1805
1806                         ]]>
1807         </sql-query>
1808
1809         <!-- Gets the record from ep_app_function table and requires two parameters  -->
1810         <sql-query name="getAppFunctionDetails">
1811                 <return alias="appFunctionDetails"
1812                         class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1813                         <![CDATA[
1814
1815                                         select * from ep_app_function where app_id =:appId and function_cd =:functionCd
1816                         ;
1817                         ]]>
1818         </sql-query>
1819
1820         <!-- Gets the current user app roles records and requires two parameters  -->
1821         <sql-query name="getUserAppCurrentRoles">
1822                 <return alias="userAppCurrentRoles"
1823                         class="org.openecomp.portalapp.portal.transport.EPUserAppCurrentRoles" />
1824                         <![CDATA[
1825
1826                         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
1827                         ;
1828                         ]]>
1829         </sql-query>
1830
1831         <!-- Gets the current user app roles along with account administrator and requires two parameters  -->
1832         <sql-query name="getUserAppExistingRoles">
1833                 <return alias="userAppExistingRoles"
1834                         class="org.openecomp.portalapp.portal.transport.EcompUserAppRoles" />
1835                         <![CDATA[
1836                         select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1837                         left outer join fn_role fr on fu.role_id = fr.role_id
1838                         where fu.user_id =:userId and fu.app_id =:appId
1839                         ;
1840                         ]]>
1841         </sql-query>
1842
1843         <!-- Gets the current user app roles records and requires two parameters  -->
1844         <sql-query name="getAllFunctions">
1845                 <return alias="allFunctions"
1846                         class="org.openecomp.portalsdk.core.domain.RoleFunction" />
1847                         <![CDATA[
1848                         select * from fn_function
1849                         ;
1850                         ]]>
1851         </sql-query>
1852
1853         <!-- Gets the  all role functions and requires one parameters -->
1854         <sql-query name="uploadAllRoleFunctions">
1855                 <return alias="allRoleFunctions"
1856                         class="org.openecomp.portalapp.portal.transport.BulkUploadRoleFunction" />
1857                         <![CDATA[
1858                                 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
1859                                 ;
1860                         ]]>
1861         </sql-query>
1862
1863         <sql-query name="getRoletoUpdateAAF">
1864                 <return alias="getRoletoUpdateAAF" class="org.openecomp.portalapp.portal.domain.EPRole" />
1865                         <![CDATA[
1866                                         SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
1867                         ;
1868                         ]]>
1869         </sql-query>
1870
1871         <sql-query name="getBulkUserRoles">
1872                 <return alias="bulkUserRoles" class="org.openecomp.portalapp.portal.transport.BulkUploadUserRoles" />
1873                         <![CDATA[
1874                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
1875                         left outer join fn_role fr on fr.role_id = fur.role_id
1876                         left outer join fn_app fa on fa.app_id = fur.app_id
1877                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fu.org_user_id != 'su1234'
1878                         ;
1879                         ]]>
1880         </sql-query>
1881
1882         <sql-query name="getCentralizedApps">
1883                 <return alias="centralizedApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1884                         <![CDATA[
1885                         select * from fn_app where auth_central = 'Y' and open = 'N';
1886                         ;
1887                         ]]>
1888         </sql-query>
1889
1890         <sql-query name="getUserRoles">
1891                         <return alias="getUserRolesList" class="org.openecomp.portalapp.portal.domain.UserRole" />
1892
1893                 <![CDATA[
1894
1895         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
1896          ;
1897                 ]]>
1898         </sql-query>
1899                 <sql-query name="getRoleFunctionsOfUser">
1900                         <![CDATA[
1901                         select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
1902                         where fu.role_id = ep.role_id
1903                         and fu.app_id = ep.app_id
1904                         and fu.user_id =:userId and ep.app_id = 1
1905                         and ea.function_cd = ep.function_cd
1906                         ;
1907                         ]]>
1908         </sql-query>
1909
1910         <sql-query name="getUserRolesForLeftMenu">
1911                         <return alias="getUserRolesListForLeftMenu" class="org.openecomp.portalapp.portal.domain.UserRole" />
1912
1913                 <![CDATA[
1914
1915         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 or role.role_id =   999)
1916          ;
1917                 ]]>
1918         </sql-query>
1919
1920         <!-- Gets all functions for an application along with global functions and requires single parameter -->
1921         <sql-query name="getMenuFunctions">
1922                 <![CDATA[
1923                         select f.function_cd from ep_app_function f
1924                         where f.app_id =:appId
1925                         UNION
1926                         select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
1927                         and epa.function_cd = epr.function_cd and fnr.role_name like 'global%'
1928                  ;
1929                 ]]>
1930         </sql-query>
1931
1932         <sql-query name="getRequestIdsForApp">
1933         <return alias="getRequestIdsForApp" class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1934                 <![CDATA[
1935                 select * from ep_user_roles_request where app_id =:app_id
1936                 ;
1937                 ]]>
1938         </sql-query>
1939
1940 </hibernate-mapping>