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
 325                 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
 326
 327         </class>
 328
 329         <!-- EPUserAppsSortPreference class mapping details -->
 330         <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
 331                 <id name="id" column="ID">
 332                         <generator class="native">
 333                                 <param name="sequence">seq_ep_usrappsrtprf</param>
 334                         </generator>
 335                 </id>
 336                 <property name="userId" column="USER_ID" />
 337                 <property name="sortPref" column="SORT_PREF" />
 338         </class>
 339
 340         <!-- EPUserAppsManualSortPreference class mapping details -->
 341         <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
 342                 <id name="id" column="ID">
 343                         <generator class="native">
 344                                 <param name="sequence">seq_ep_usrmanappsrtprf</param>
 345                         </generator>
 346                 </id>
 347                 <property name="userId" column="user_id"
 348                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 349                 <property name="appId" column="app_id"
 350                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 351                 <property name="appManualSortOrder" column="sort_order" type="int" />
 352         </class>
 353
 354         <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
 355                 <id name="id" column="ID">
 356                         <generator class="native">
 357                                 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
 358                         </generator>
 359                 </id>
 360                 <property name="userId" column="user_id" />
 361                 <property name="widgetId" column="widget_id" />
 362                 <property name="widgetRow" column="x" />
 363                 <property name="widgetCol" column="y" />
 364                 <property name="widgetHeight" column="height" />
 365                 <property name="widgetWidth" column="width" />
 366         </class>
 367
 368         <!-- User Role class mapping details -->
 369         <class name="EPRole" table="FN_ROLE">
 370                 <id name="id" column="role_id">
 371                         <generator class="native">
 372                                 <param name="sequence">seq_fn_role</param>
 373                         </generator>
 374                 </id>
 375
 376                 <property name="name" column="role_name" />
 377                 <property name="priority" column="priority" />
 378                 <property name="active" column="active_yn" type="yes_no" />
 379                 <!-- 2 lines below may be removed! -->
 380                 <property name="appId" column="APP_ID" />
 381                 <property name="appRoleId" column="APP_ROLE_ID" />
 382                 <!-- 2 lines above may be removed -->
 383
 384                 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
 385                         sort="natural">
 386                         <key column="role_id" />
 387                         <many-to-many column="function_cd"
 388                                 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
 389                 </set>
 390
 391                 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 392                         sort="natural">
 393                         <key column="parent_role_id" />
 394                         <many-to-many column="child_role_id"
 395                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 396                 </set>
 397
 398                 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 399                         sort="natural">
 400                         <key column="child_role_id" />
 401                         <many-to-many column="parent_role_id"
 402                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 403                 </set>
 404
 405         </class>
 406
 407         <!-- User App class mapping details -->
 408         <class name="AppContactUs" table="fn_app_contact_us">
 409                 <id name="id" column="app_id">
 410                         <generator class="foreign">
 411                                 <param name="property">app</param>
 412                         </generator>
 413                 </id>
 414                 <property name="url" column="url" />
 415                 <property name="description" column="description" />
 416                 <property name="contactName" column="contact_name" />
 417                 <property name="contactEmail" column="contact_email" />
 418                 <property name="activeYN" column="active_yn" />
 419
 420                 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
 421
 422         </class>
 423
 424         <!-- Personalization of user app selections -->
 425         <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
 426                 <id name="id" column="id">
 427                         <generator class="native">
 428                                 <param name="property">seq_fn_pers_user_app_sel</param>
 429                         </generator>
 430                 </id>
 431                 <property name="userId" column="user_id" />
 432                 <property name="appId" column="app_id" />
 433                 <property name="statusCode" column="status_cd" />
 434         </class>
 435
 436         <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
 437                 <id name="id" column="id">
 438                         <generator class="native">
 439                                 <param name="property">seq_ep_pers_user_widget_sel</param>
 440                         </generator>
 441                 </id>
 442                 <property name="userId" column="user_id" />
 443                 <property name="widgetId" column="widget_id" />
 444                 <property name="statusCode" column="status_cd" />
 445         </class>
 446
 447         <!-- EPUserAppRolesRequest class mapping details -->
 448         <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
 449                 <id name="id" column="req_id">
 450                         <generator class="native">
 451                                 <param name="sequence">seq_ep_user_role_request</param>
 452                         </generator>
 453                 </id>
 454                 <property name="userId" column="user_id" />
 455                 <property name="appId" column="app_id" />
 456                 <property name="createdDate" column="created_date" type="timestamp" />
 457                 <property name="updatedDate" column="updated_date" type="timestamp" />
 458                 <property name="requestStatus" column="request_status" />
 459
 460                 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
 461                         lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
 462                         <key column="req_id" />
 463                         <one-to-many
 464                                 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
 465                 </set>
 466         </class>
 467
 468         <!-- EPUserAppRolesRequestDetail class mapping details -->
 469         <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
 470                 <id name="id" column="id">
 471                         <generator class="native">
 472                                 <param name="sequence">seq_ep_user_role_request_det</param>
 473                         </generator>
 474                 </id>
 475                 <property name="reqRoleId" column="requested_role_id" />
 476                 <property name="reqType" column="request_type" />
 477                 <many-to-one name="epRequestIdData" fetch="select"
 478                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
 479                         <column name="req_id" not-null="true" />
 480                 </many-to-one>
 481         </class>
 482
 483         <!-- show the current user plus related users -->
 484         <sql-query name="relatedUsers">
 485                 <return-scalar column="org_user_id" type="java.lang.String" />
 486                 <![CDATA[
 487                         select t.org_user_id from (
 488                                 select distinct c.org_user_id org_user_id, c.last_login_date from
 489                                         (select c.org_user_id, c.last_login_date from
 490                                                 fn_user_role a,
 491                                                 (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,
 492                                                 fn_user c
 493                                                 where a.ROLE_ID = b.role_id
 494                                                 and a.APP_ID = b.app_id
 495                                                 and a.USER_ID = c.user_id
 496                                         union
 497                                         select org_user_id , last_login_date from fn_user where org_user_id=:userId
 498                                         ) c
 499                                 order by c.last_login_date desc limit 10
 500                         ) t
 501                 ]]>
 502         </sql-query>
 503
 504         <!-- requires values for named parameters :searchQuery and :userId -->
 505         <sql-query name="searchPortal">
 506                 <return alias="searchResult"
 507                         class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
 508                 <![CDATA[
 509                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 510                                 ( (
 511                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
 512                                 from fn_user where
 513                                 upper( :searchQuery ) != ''
 514                                 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
 515                                 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
 516                                 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
 517                                 and  upper(active_yn) = 'Y'
 518                                 )
 519                                 union
 520                                 (
 521                                 select distinct 'Application' CATEGORY, b1.app_name NAME,
 522                                 if (b1.app_type = 2,'false','true') UUID,
 523                                 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
 524                                 from
 525                                 (
 526                                 select c.*
 527                                 from fn_user_role a, fn_user b, fn_app c
 528                                 where a.USER_ID = b.user_id
 529                                 and upper(b.org_user_id) = upper(  :userId )
 530                                 and a.app_id = c.app_id
 531                                 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 532                                 and upper(c.ENABLED) = 'Y'
 533                                 ) a1 right outer join
 534                                 (select * from fn_app where
 535                                 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 536                                 and upper(ENABLED) = 'Y') b1
 537                                 on a1.APP_ID = b1.app_id
 538                                 )
 539                                 union
 540                                 (
 541                                 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
 542                                 if (b1.app_type = 2,'false','true') UUID,
 543                                 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
 544                                 from
 545                                 (select distinct  d.*, e.app_type from fn_user a,
 546                                 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
 547                                 where a.USER_ID = b.user_id
 548                                 and b.role_id = c.role_id
 549                                 and c.menu_id = d.menu_id
 550                                 and upper(a.org_user_id) = upper( :userId )
 551                                 and c.APP_ID = e.app_id
 552                                 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 553                                 and upper(d.active_yn) = 'Y'
 554                                 and upper(e.enabled) = 'Y'
 555                                 ) a1 right outer join
 556                                 (
 557                                 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
 558                                 where active_yn = 'Y'
 559                                 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 560                                 and a.menu_id = b.menu_id
 561                                 and b.app_id = c.app_id
 562                                 and c.enabled = 'Y'
 563                                 ) b1
 564                                 on a1.menu_id = b1.menu_id
 565                                 )
 566                                 union
 567                                 select distinct CATEGORY, NAME, UUID, TARGET
 568                                 from
 569                                 (
 570                                 (
 571                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 572                                 d.widget_id UUID,
 573                                 d.wdg_desc TARGET
 574                                 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
 575                                 where upper(a.org_user_id) = upper( :userId )
 576                                 and a.user_id = b.user_id
 577                                 and b.role_id = c.role_id
 578                                 and c.widget_id = d.widget_id
 579                                 and d.all_user_flag<>'Y'
 580                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 581                                 )
 582                                 union
 583                                 (
 584                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 585                                 d.widget_id UUID,
 586                                 d.wdg_desc TARGET
 587                                 from ep_widget_catalog d
 588                                 where d.all_user_flag='Y'
 589                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 590                                 )
 591                                 union
 592                                 (
 593                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 594                                 d.widget_id UUID,
 595                                 d.wdg_desc TARGET
 596                                 from fn_user a, fn_user_role b, ep_widget_catalog d
 597                                 where upper(a.org_user_id) = upper( :userId )
 598                                 and a.user_id = b.user_id
 599                                 and b.role_id = 1
 600                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 601                                 )
 602                                 ) y
 603                                 ) t,
 604                                 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
 605                                                                         ;
 606
 607                 ]]>
 608         </sql-query>
 609
 610         <!-- requires values for named parameters :searchQuery and :userId -->
 611         <sql-query name="getNotifications">
 612                 <return alias="notificationResult"
 613                         class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
 614                 <![CDATA[
 615                         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, active_YN from
 616                         (
 617
 618                         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,active_YN
 619                         from
 620                                 (
 621                                 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, creator_ID,active_YN
 622                                 from
 623                                 (
 624                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 625                                         a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,b.role_id,b.recv_user_id
 626                                 from ep_notification a, ep_role_notification b
 627                                 where a.notification_id = b.notification_id
 628                                 and (end_time is null ||  SYSDATE() <= end_time )
 629                                 and (start_time is null ||  SYSDATE() >= start_time)
 630                                 and a.is_for_all_roles = 'N'
 631                                 ) a,
 632                                 (
 633                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 634                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 635                                 where COALESCE(c.app_id,1) = d.app_id
 636                         and a.user_id = b.user_id
 637                                 and a.user_id = :user_id
 638                                 and b.role_id = c.role_id
 639                         and (d.enabled='Y' or d.app_id=1)
 640                                 )b
 641                                 where
 642                                 (
 643                                 a.role_id = b.role_id
 644                                 )
 645                                 union
 646                                 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, creator_ID,active_YN
 647                                 from
 648                                 (
 649                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 650                                         a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date, b.role_id,b.recv_user_id
 651                                 from ep_notification a, ep_role_notification b
 652                                 where a.notification_id = b.notification_id
 653                                 and (end_time is null ||  SYSDATE() <= end_time )
 654                                 and (start_time is null ||  SYSDATE() >= start_time)
 655                                 and a.is_for_all_roles = 'N'
 656                                 ) a
 657                                 where
 658                                 (
 659                                 a.recv_user_id=:user_id
 660                                 )
 661                                 union
 662                                 (
 663                                 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, creator_ID,active_YN
 664                                 from ep_notification a
 665                                 where a.notification_id
 666                                 and (end_time is null ||  SYSDATE() <= end_time )
 667                                 and (start_time is null ||  SYSDATE() >= start_time)
 668                                 and a.is_for_all_roles = 'Y'
 669                                 )
 670                                 ) a
 671                                 where
 672                                         active_YN = 'Y'
 673                                 and
 674                                         not exists
 675                                 (
 676                                 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'
 677                                 )
 678                                 order by priority desc, created_date desc,start_Time desc
 679
 680
 681                          ) t,
 682                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 683                         ;
 684                 ]]>
 685
 686         </sql-query>
 687
 688                 <sql-query name="getUsersByOrgIdsNotifications">
 689                 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
 690                 <![CDATA[
 691                         SELECT * from fn_user where org_user_id in (:OrgIds)
 692                 ]]>
 693
 694         </sql-query>
 695
 696
 697         <!-- Gets all Admin notification history; accepts no parameters -->
 698         <sql-query name="getAdminNotificationHistoryVO">
 699                 <return alias="adminNotificationHistoryVOResult"
 700                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 701                 <![CDATA[
 702                 SELECT * from (
 703 SELECT
 704                         n.notification_ID AS notificationId,
 705                         n.is_for_online_users AS isForOnlineUsers,
 706                         n.is_for_all_roles AS isForAllRoles,
 707                         n.active_YN AS activeYn,
 708                         n.msg_header AS msgHeader,
 709                         n.msg_description AS msgDescription,
 710                         n.msg_source AS msgSource,
 711                         n.start_Time AS startTime,
 712                         n.end_time AS endTime,
 713                         n.priority,
 714                         n.creator_ID AS creatorId,
 715                         n.created_date AS createdDate,
 716                         u.org_user_id AS loginId
 717                 FROM
 718                         ep_notification n
 719                 LEFT JOIN
 720                         fn_user u on u.user_id = n.creator_id
 721                 WHERE
 722                         n.active_YN='Y' and msg_source='EP'
 723
 724       UNION
 725
 726       SELECT
 727                         n.notification_ID AS notificationId,
 728                         n.is_for_online_users AS isForOnlineUsers,
 729                         n.is_for_all_roles AS isForAllRoles,
 730                         n.active_YN AS activeYn,
 731                         n.msg_header AS msgHeader,
 732                         n.msg_description AS msgDescription,
 733                         n.msg_source AS msgSource,
 734                         n.start_Time AS startTime,
 735                         n.end_time AS endTime,
 736                         n.priority,
 737                         n.creator_ID AS creatorId,
 738                         n.created_date AS createdDate,
 739                         u.org_user_id AS loginId
 740                 FROM
 741                         ep_notification n
 742                 LEFT JOIN
 743                         fn_user u on u.user_id = n.creator_id
 744     JOIN
 745                         ep_role_notification r on r.notification_ID=n.notification_ID
 746                 WHERE
 747                         n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
 748       )n order by n.startTime desc
 749                 ]]>
 750         </sql-query>
 751
 752         <sql-query name="getEpNotificationAppRoles">
 753                 <return alias="notificationAppRoles"
 754                         class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
 755                 <!-- This query requires no parameters. -->
 756                 <![CDATA[
 757
 758 select  a.app_id, a.app_name, b.role_id, b.role_name from
 759 (select * from fn_app where app_id = 1) a,
 760 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
 761 union
 762 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
 763 from fn_app, fn_role
 764 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' order by app_name
 765
 766
 767
 768                 ]]>
 769         </sql-query>
 770
 771         <!-- Gets all notification history -->
 772         <sql-query name="getNotificationHistoryVO">
 773                 <return alias="notificationHistoryVOResult"
 774                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 775                 <![CDATA[
 776                 SELECT
 777                         notificationId, isForOnlineUsers, isForAllRoles, msgHeader,     msgDescription,msgSource,
 778                         startTime, endTime, priority, createdDate,      creatorId, loginId,     activeYn
 779                 FROM
 780                         (
 781                                         select distinct
 782                                         a.notification_ID AS notificationId,
 783                                         is_for_online_users AS isForOnlineUsers,
 784                                         is_for_all_roles AS isForAllRoles,
 785                                         msg_header AS msgHeader,
 786                                         msg_description AS msgDescription,
 787                                         msg_source AS msgSource,
 788                                         start_Time AS startTime,
 789                                         end_time AS endTime,
 790                                         priority,
 791                                         created_date AS createdDate,
 792                                         creator_ID AS creatorId,
 793                                         login_id AS loginId,
 794                                         active_YN AS activeYn,
 795                                         if (is_viewed is null, 'N', is_viewed)
 796                         from
 797                         (
 798                                 select
 799                                         user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 800                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 801                                         creator_ID,active_YN
 802                                 from
 803                                 (
 804                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 805                                         a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,
 806                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 807
 808                    ELSE NULL
 809               END  AS login_id,b.recv_user_id
 810                                 from ep_notification a, ep_role_notification b,fn_user u
 811                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 812                                 and a.is_for_all_roles = 'N'
 813                                 and (
 814                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 815                                 or
 816                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 817                                 or
 818                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 819                                 or
 820                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 821                                 )
 822                                 ) a,
 823                                 (
 824                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 825                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 826                                 where COALESCE(c.app_id,1) = d.app_id
 827                         and a.user_id = b.user_id
 828                                 and a.user_id = :user_id
 829                                 and b.role_id = c.role_id
 830                         and (d.enabled='Y' or d.app_id=1)
 831                                 ) b
 832                                 where
 833                                 (
 834                                 a.role_id = b.role_id
 835                                 )
 836                                    UNION
 837                         select
 838                                         :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 839                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 840                                         creator_ID,active_YN
 841                                 from
 842                                 (
 843                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 844                                         a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,
 845                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 846
 847                    ELSE NULL
 848               END  AS login_id,b.recv_user_id
 849                                 from ep_notification a, ep_role_notification b,fn_user u
 850                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 851                                 and a.is_for_all_roles = 'N'
 852                                 and (
 853                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 854                                 or
 855                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 856                                 or
 857                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 858                                 or
 859                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 860                                 )
 861                                 ) a
 862                         where
 863                         (
 864                         a.recv_user_id=:user_id
 865                         )
 866                                 union
 867                                 (
 868                                 select
 869                                         :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
 870                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
 871                                         creator_ID, a.active_YN
 872                                 from ep_notification a  JOIN fn_user b on b.user_id=a.creator_ID
 873                                 where a.notification_id
 874                                 and a.is_for_all_roles = 'Y'
 875                                 and (
 876                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 877                                 or
 878                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 879                                 or
 880                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 881                                 or
 882                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 883                                 )
 884                                 )
 885                                 ) a left outer join (
 886                                 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
 887                                 ) m
 888                                 on  a.notification_id = m.notification_ID
 889                                 where
 890                                 active_YN = 'Y'
 891
 892                                 order by  start_Time desc,end_time desc
 893                         ) t,
 894                      (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
 895                 ]]>
 896
 897         </sql-query>
 898
 899                 <!-- shows the received recipient to whom the notification is delivered from external system -->
 900         <sql-query name="messageRecipients">
 901                 <return-scalar column="org_user_id" type="java.lang.String" />
 902                 <![CDATA[
 903                         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
 904                 ]]>
 905         </sql-query>
 906
 907
 908         <!-- Gets role details for a specified notification -->
 909         <sql-query name="getNotificationRoles">
 910                 <return alias="notificationRolesResult"
 911                         class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
 912                 <![CDATA[
 913                         SELECT *
 914                         FROM
 915                                 ep_role_notification
 916                         WHERE
 917                                 notification_Id = :notificationId
 918                 ]]>
 919         </sql-query>
 920
 921         <!-- Gets all applications, possibly with contact information -->
 922         <sql-query name="getAppsAndContacts">
 923                 <return alias="appContactUs"
 924                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 925                 <!-- This query requires no parameters. -->
 926                 <![CDATA[
 927                         select
 928                                 a.app_id as appId, a.app_name as appName,
 929                                 c.contact_name as contactName,
 930                                 c.contact_email as contactEmail, c.url, c.description,
 931                                 c.active_yn as activeYN
 932                         from
 933                                 fn_app a
 934                         left join
 935                                 fn_app_contact_us c
 936                                 on a.app_id = c.app_id
 937                         where
 938                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 939                         ;
 940                 ]]>
 941         </sql-query>
 942
 943         <!-- Gets all rows from the app-contact-us table, extended with app information -->
 944         <sql-query name="getAppContactUsItems">
 945                 <return alias="appContactUs"
 946                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 947                 <!-- This query requires no parameters. -->
 948                 <![CDATA[
 949                         select
 950                                 c.app_id as appId, c.contact_name as contactName,
 951                                 c.contact_email as contactEmail, c.url, c.description,
 952                                 c.active_yn as activeYN, a.app_name as appName
 953                         from
 954                                 fn_app_contact_us c
 955                         left join
 956                                 fn_app a
 957                                 on a.app_id = c.app_id
 958                         where
 959                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 960                         ;
 961                 ]]>
 962         </sql-query>
 963
 964         <!-- Gets one row for each app with the category and catenated functions. -->
 965         <sql-query name="getAppCategoryFunctions">
 966                 <return alias="appCategoryFunctions"
 967                         class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
 968                 <!-- This query requires no parameters. -->
 969                 <![CDATA[
 970                         SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
 971                                 r.category as category, r.app_id as appId, r.app_name as application,
 972                                 group_concat(function_text separator ', ') as functions
 973                         FROM (
 974                                 SELECT
 975                                 app_id, function_text, app_name,
 976                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
 977                                 FROM (
 978                                         SELECT
 979                                         j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
 980                                         k.text parent_menu, k.parent_menu_id parent_menu_id
 981                                         FROM (
 982                                                 SELECT distinct
 983                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
 984                                                 FROM
 985                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
 986                                                 WHERE
 987                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
 988                                                 ) j,
 989                                         fn_menu_functional k
 990                                         WHERE j.parent_menu_id = k.menu_id
 991                                         ) fn,
 992                                 fn_menu_functional a
 993                                 WHERE fn.parent_menu_id = a.menu_id
 994                         ) r,
 995                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 996                         group by r.category, r.app_id, r.app_name
 997                         order by category, app_name
 998                         ;
 999                 ]]>
1000         </sql-query>
1001
1002         <!-- Gets one row for each function-application-role combination. -->
1003         <sql-query name="getAppAccessFunctionRole">
1004                 <return alias="appAccessFunctionRole"
1005                         class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
1006                 <!-- This query requires one parameters. -->
1007                 <![CDATA[
1008                         select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1009                         (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,
1010                         o.requested_role_id,o.request_type
1011                         from
1012                         (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1013                         from
1014                         (
1015                         select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1016                         from
1017                         FN_ROLE C, FN_APP D
1018                         where COALESCE(c.app_id,1) = d.app_id
1019                         and (d.enabled='Y' or d.app_id=1)
1020                         and c.active_yn = 'Y'
1021                         ) a left outer join
1022                         (
1023                         select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1024                         WHERE
1025                         A.MENU_ID = B.MENU_ID
1026                         and active_yn = 'Y'
1027                         )b
1028                         on a.role_id = b.role_id) t left outer join
1029                         (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1030                         where er.req_id=ed.req_id
1031                         and upper(ed.request_type)='P'
1032                         and er.user_id =:userId) o
1033                         on t.app_id=o.app_id
1034                         and t.role_id = o.requested_role_id
1035                         JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1036                         ;
1037                 ]]>
1038         </sql-query>
1039
1040         <query name="getCommonWidgetItem">
1041                 from CommonWidget where category = :cat order by
1042                 sortOrder, title
1043         </query>
1044
1045         <sql-query name="getGuestLastLogin">
1046                 <return-scalar column="audit_date" type="java.util.Date" />
1047                 <![CDATA[
1048                         select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1049                 ]]>
1050         </sql-query>
1051
1052         <sql-query name="getAppsAdmins">
1053                 <return alias="adminUserApp" class="org.openecomp.portalapp.portal.domain.AdminUserApp" />
1054         <![CDATA[
1055         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)
1056         ]]>
1057         </sql-query>
1058
1059         <sql-query name="getPortalAdmins">
1060                 <return alias="portalAdmin" class="org.openecomp.portalapp.portal.transport.PortalAdmin" />
1061         <![CDATA[
1062                 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
1063         ]]>
1064         </sql-query>
1065
1066         <!-- Gets personalized list of enabled and accessible apps for regular user -->
1067         <sql-query name="getPersUserApps">
1068                 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1069                 <!-- This query requires one parameter: userId (number) -->
1070                 <![CDATA[
1071                     select
1072                                 distinct  -- multiple roles yield multiple rows
1073                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1074                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1075                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1076                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1077                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1078                     from FN_APP a
1079                     -- Portal assigns role 999 to app administrator
1080                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1081                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1082                         where a.ENABLED = 'Y'
1083                     and (
1084                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1085                         or
1086                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1087                         )
1088                         order by app_name
1089                     ;
1090                 ]]>
1091         </sql-query>
1092
1093         <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1094                 admin -->
1095         <sql-query name="getPersAdminApps">
1096                 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1097                 <!-- This query requires one parameter: userId (number) -->
1098                 <![CDATA[
1099                     select
1100                                 distinct  -- multiple roles yield multiple rows
1101                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1102                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1103                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1104                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1105                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1106                     from FN_APP a
1107                     -- Portal assigns role 999 to app administrator
1108                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1109                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1110                         where a.ENABLED = 'Y'
1111                     and (
1112                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1113                         or
1114                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1115                                 or
1116                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1117                         )
1118                         order by app_name
1119                     ;
1120                 ]]>
1121         </sql-query>
1122
1123         <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1124         <sql-query name="getPersAdminAppsOrderByName">
1125                 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1126                 <!-- This query requires one parameter: userId (number) -->
1127                 <![CDATA[
1128                  select
1129                                 distinct  -- multiple roles yield multiple rows
1130                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1131                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1132                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1133                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1134                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1135                     from FN_APP a
1136                     -- Portal assigns role 999 to app administrator
1137                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1138                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1139                         where a.ENABLED = 'Y'
1140                     and (
1141                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1142                         or
1143                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1144                                 or
1145                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1146                         )
1147                         order by app_name
1148                     ;
1149
1150                 ]]>
1151         </sql-query>
1152
1153         <!--Gets personalized list of enabled and accessible User apps by name -->
1154         <sql-query name="getPersUserAppsOrderByName">
1155                 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1156                 <!-- This query requires one parameter: userId (number) -->
1157                 <![CDATA[
1158                 select
1159                                 distinct  -- multiple roles yield multiple rows
1160                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1161                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1162                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1163                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1164                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1165                     from FN_APP a
1166                     -- Portal assigns role 999 to app administrator
1167                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1168                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1169                         where a.ENABLED = 'Y'
1170                     and (
1171                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1172                         or
1173                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1174                         )
1175                         order by app_name
1176                     ;
1177
1178                 ]]>
1179         </sql-query>
1180
1181
1182         <!--Gets personalized list of enabled and accessible Super Admin apps by
1183                 LastUsed -->
1184         <sql-query name="getAdminAppsOrderByLastUsed">
1185                 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1186
1187                 <![CDATA[
1188                     select
1189                         *
1190                         from (select
1191                                 distinct  -- multiple roles yield multiple rows
1192                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1193                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1194                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1195                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1196                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1197                     from FN_APP a
1198                     -- Portal assigns role 999 to app administrator
1199                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1200                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1201                         where a.ENABLED = 'Y'
1202                     and (
1203                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1204                         or
1205                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1206                                 or
1207                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1208                         )
1209                         ) A
1210                 left outer join
1211                         (select  distinct
1212                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1213                         from FN_AUDIT_LOG c
1214                          )B
1215                         on A.app_id = B.Affected_record_id
1216                         order by AUDIT_DATE DESC;
1217                         ]]>
1218         </sql-query>
1219
1220         <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1221         <sql-query name="getUserAppsOrderByLastUsed">
1222                 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1223
1224                 <![CDATA[
1225                         select * from (select
1226                                 distinct  -- multiple roles yield multiple rows
1227                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1228                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1229                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1230                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1231                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1232                     from FN_APP a
1233                     -- Portal assigns role 999 to app administrator
1234                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1235                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1236                         where a.ENABLED = 'Y'
1237                     and (
1238                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1239                         or
1240                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1241                         )) A
1242                 left outer join
1243                         (select  distinct
1244                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1245                         from FN_AUDIT_LOG c
1246                          )B
1247                         on A.app_id = B.Affected_record_id
1248                         order by AUDIT_DATE DESC;
1249
1250
1251                 ]]>
1252
1253         </sql-query>
1254
1255         <!--Gets personalized list of enabled and accessible Super Admin apps by
1256                 Most Used -->
1257         <sql-query name="getAdminAppsOrderByMostUsed">
1258                 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1259
1260                 <![CDATA[
1261                         select
1262                         *
1263                         from (select
1264                                 distinct  -- multiple roles yield multiple rows
1265                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1266                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1267                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1268                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1269                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1270                     from FN_APP a
1271                     -- Portal assigns role 999 to app administrator
1272                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1273                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1274                         where a.ENABLED = 'Y'
1275                     and (
1276                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1277                         or
1278                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1279                                 or
1280                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1281                         )
1282                         ) A
1283                         left outer join
1284                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1285                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1286                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1287                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1288                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1289                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1290                          )B
1291                         on A.app_id = B.Affected_record_id
1292                         order by total_visits DESC;
1293                 ]]>
1294
1295         </sql-query>
1296
1297         <!--Gets personalized list of enabled and accessible NON Super Admin User
1298                 apps by Most Used -->
1299         <sql-query name="getUserAppsOrderByMostUsed">
1300                 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1301
1302                 <![CDATA[
1303                         select * from
1304                         (select
1305                                 distinct  -- multiple roles yield multiple rows
1306                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1307                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1308                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1309                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1310                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1311                     from FN_APP a
1312                     -- Portal assigns role 999 to app administrator
1313                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1314                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1315                         where a.ENABLED = 'Y'
1316                     and (
1317                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1318                         or
1319                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1320                         ))A
1321                         left outer join
1322                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1323                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1324                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1325                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1326                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1327                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1328                          )B
1329                         on A.app_id = B.Affected_record_id
1330                         order by total_visits DESC;
1331                 ]]>
1332         </sql-query>
1333
1334         <!--Gets personalized list of enabled and accessible Super Admin apps by
1335                 Manual -->
1336         <sql-query name="getAdminAppsOrderByManual">
1337                 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1338
1339                 <![CDATA[
1340                         select * from
1341                         (select
1342                                 distinct  -- multiple roles yield multiple rows
1343                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1344                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1345                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1346                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1347                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1348                     from FN_APP a
1349                     -- Portal assigns role 999 to app administrator
1350                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1351                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1352                         where a.ENABLED = 'Y'
1353                     and (
1354                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1355                         or
1356                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1357                                 or
1358                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1359                         )) A
1360                         left outer join
1361                          (select m.app_id, m.sort_order
1362                         from ep_pers_user_app_man_sort m
1363                         where USER_ID = :userId
1364                         ) B
1365                         on A.APP_ID = B.app_id
1366                         order by sort_order ASC
1367                     ;
1368                 ]]>
1369
1370         </sql-query>
1371
1372         <!--Gets personalized list of enabled and accessible NON Super admin User
1373                 apps by Manual -->
1374         <sql-query name="getUserAppsOrderByManual">
1375                 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1376
1377                 <![CDATA[
1378                                                         select * from   (select
1379                                 distinct  -- multiple roles yield multiple rows
1380                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1381                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1382                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1383                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1384                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1385                     from FN_APP a
1386                     -- Portal assigns role 999 to app administrator
1387                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1388                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1389                         where a.ENABLED = 'Y'
1390                     and (
1391                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1392                         or
1393                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1394                         )) A
1395                         left outer join
1396                          (select m.app_id, m.sort_order
1397                         from ep_pers_user_app_man_sort m
1398                         where USER_ID = :userId
1399                         ) B
1400                         on A.APP_ID = B.app_id
1401                         order by sort_order ASC
1402                     ;
1403                 ]]>
1404         </sql-query>
1405
1406         <!-- Gets regular user's list of enabled apps including accessible and select
1407                 statuses -->
1408         <sql-query name="getUserAppCatalog">
1409                 <return alias="userAppCatalog"
1410                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1411                 <!-- This query requires one parameter: userId (number) -->
1412                 <![CDATA[
1413                                                 select
1414                                 distinct  -- multiple roles yield multiple rows
1415                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1416                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1417                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1418                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1419                             IF(a.open = 'Y', TRUE, FALSE) as open,
1420                 -- ACCESS(-ible) means user has a defined role OR the application is open
1421                                 case
1422                                         -- regular app and user has a role
1423                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1424                     -- open app
1425                     when a.OPEN = 'Y' then TRUE
1426                     else FALSE
1427                 end as 'access' ,
1428                 -- SELECT(-ed) indicates user personalization
1429                 case
1430                                         -- regular app, user has a role, no personalization
1431                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1432                     -- open app and has personalization
1433                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1434                     else FALSE
1435                 end as 'select' ,
1436                 -- PENDING indicates user checked a box
1437                 case
1438                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1439                         else FALSE
1440                 end as 'pending'
1441                     from FN_APP a
1442                     -- Portal assigns role 999 to app administrator
1443             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1444                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1445       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'
1446       where a.ENABLED = 'Y'
1447
1448                         -- Show accessible apps first, then the rest; sort by name within each set.
1449             order by access desc, app_name asc
1450                     ;
1451                 ]]>
1452         </sql-query>
1453
1454         <sql-query name="getMyloginAppDetails">
1455                 <return alias="myloginAppDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1456
1457                 <![CDATA[
1458                 SELECT * FROM fn_app  where ml_app_name =:appName
1459                 ;
1460                 ]]>
1461         </sql-query>
1462
1463
1464         <sql-query name="deleteAccountEndpoint">
1465                 <![CDATA[
1466                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1467                 ;
1468                 ]]>
1469         </sql-query>
1470
1471         <sql-query name="deleteAccountEndpointRecord">
1472                 <![CDATA[
1473                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1474                 ;
1475                 ]]>
1476         </sql-query>
1477
1478         <sql-query name="deleteEPEndpoint">
1479                 <![CDATA[
1480                 DELETE FROM ep_endpoints WHERE id =:epId
1481                 ;
1482                 ]]>
1483         </sql-query>
1484
1485         <sql-query name="deleteBasicAuthAccount">
1486                 <![CDATA[
1487                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1488                 ;
1489                 ]]>
1490         </sql-query>
1491
1492         <sql-query name="getAppRoles">
1493                 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1494
1495                 <![CDATA[
1496                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1497                 ;
1498                 ]]>
1499         </sql-query>
1500
1501         <sql-query name="getPortalAppRoles">
1502                 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1503
1504                 <![CDATA[
1505
1506                 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1507                 ;
1508                 ]]>
1509         </sql-query>
1510
1511         <sql-query name="deleteMicroserviceParameter">
1512                 <![CDATA[
1513
1514                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1515                 ;
1516                 ]]>
1517         </sql-query>
1518
1519         <sql-query name="deleteMicroserviceParameterById">
1520                 <![CDATA[
1521
1522                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1523                 ;
1524                 ]]>
1525         </sql-query>
1526
1527         <sql-query name="deleteWidgetCatalogParameter">
1528                 <![CDATA[
1529
1530                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1531                 ;
1532                 ]]>
1533         </sql-query>
1534
1535         <sql-query name="deleteMicroservice">
1536                 <![CDATA[
1537
1538                 DELETE FROM ep_microservice WHERE id =:serviceId
1539                 ;
1540                 ]]>
1541         </sql-query>
1542
1543         <sql-query name="epUserAppId">
1544                 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1545
1546                 <![CDATA[
1547
1548                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1549                 ;
1550                 ]]>
1551         </sql-query>
1552
1553         <sql-query name="userAppsSortPreferenceQuery">
1554                 <return alias="appsSortPreferenceQuery"
1555                         class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1556
1557                 <![CDATA[
1558
1559                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1560
1561                 ]]>
1562         </sql-query>
1563
1564         <sql-query name="userAppsManualSortPrfQuery">
1565                 <return alias="AppsManualSortPrfQuery"
1566                         class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1567
1568                 <![CDATA[
1569
1570                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1571
1572                 ]]>
1573         </sql-query>
1574
1575         <sql-query name="userWidgetManualSortPrfQuery">
1576                 <return alias="widgetManualSortPrfQuery"
1577                         class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1578                 <![CDATA[
1579                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1580                 ]]>
1581         </sql-query>
1582
1583         <sql-query name="appRoles">
1584                 <return alias="rolesForApp"
1585                         class="org.openecomp.portalapp.portal.domain.EpUserAppRoles" />
1586                 <![CDATA[
1587                         SELECT ROLE_ID, APP_ID from  FN_ROLE where APP_ROLE_ID =:appRoleId and app_id =:appId
1588                         ;
1589                 ]]>
1590         </sql-query>
1591
1592         <sql-query name="userAppRolesRequestList">
1593                 <return alias="appRolesRequestList"
1594                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1595                 <![CDATA[
1596                         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'
1597                         ;
1598                 ]]>
1599         </sql-query>
1600
1601         <sql-query name="userAppRolesRequestDetailList">
1602                 <return alias="appRolesRequestDetailList"
1603                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1604                 <![CDATA[
1605
1606                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1607                         ;
1608                 ]]>
1609         </sql-query>
1610
1611         <!-- Gets list of enabled apps including accessible and select statuses -->
1612         <sql-query name="getAdminAppCatalog">
1613                 <return alias="adminAppCatalog"
1614                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1615                 <!-- This query requires one parameter: userId (number) -->
1616                 <![CDATA[
1617                         select
1618                                 distinct  -- multiple roles yield multiple rows
1619                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1620                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1621                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1622                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1623                             IF(a.open = 'Y', TRUE, FALSE) as open,
1624                 -- ACCESS(-ible) means user has a defined role OR the application is open
1625                                 case
1626                                         -- regular app and user has a role
1627                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1628                     -- open app
1629                     when a.OPEN = 'Y' then TRUE
1630                     else FALSE
1631                 end as 'access' ,
1632                 -- SELECT(-ed) indicates user personalization
1633                 case
1634                                         -- regular app, user has a role, no personalization
1635                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1636                                         -- regular app, user has role, admin forced a personalization
1637                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1638                                         -- regular app, user has no role, admin forced a personalization
1639                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1640                     -- open app and has personalization
1641                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1642                     else FALSE
1643                 end as 'select' ,
1644                 -- PENDING indicates user checked a box
1645                 case
1646                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1647                         else FALSE
1648                 end as 'pending'
1649                     from FN_APP a
1650                     -- Portal assigns role 999 to app administrator
1651             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1652                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1653                         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'
1654                         where a.ENABLED = 'Y'
1655                         -- Show accessible apps first, then the rest; sort by name within each set.
1656             order by access desc, app_name asc
1657                     ;
1658                 ]]>
1659         </sql-query>
1660
1661         <sql-query name="userAppCatalogRoles">
1662                 <return alias="epUserAppCatalogRoles"
1663                         class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1664                 <![CDATA[
1665         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
1666        from ep_user_roles_request A left join ep_user_roles_request_det B
1667                    on a.req_id = b.req_id
1668                    where  A.user_id=:userid
1669        and A.app_id IN (select app_id from fn_app where app_name=:appName)
1670        and A.request_status ='P'
1671        ;
1672                 ]]>
1673         </sql-query>
1674
1675         <sql-query name="getUserApproles">
1676                 <return alias="businessCardUserApplicationRoles"
1677                         class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1678
1679                 <![CDATA[
1680
1681                 select   fr.role_name, fa.app_name
1682           from
1683         fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1684           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
1685           ;
1686
1687                 ]]>
1688         </sql-query>
1689
1690     <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1691         <sql-query name="deleteNotificationsFromEpNotificationTable">
1692
1693                 <![CDATA[
1694                 delete from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1695                 ]]>
1696         </sql-query>
1697
1698
1699                 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1700         <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1701
1702                 <![CDATA[
1703         delete from ep_role_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1704                 ]]>
1705         </sql-query>
1706
1707         <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1708         <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1709
1710                 <![CDATA[
1711         delete from ep_user_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1712                 ]]>
1713         </sql-query>
1714
1715 </hibernate-mapping>