ecomp-portal-BE/war/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
  22 <!DOCTYPE hibernate-mapping PUBLIC
  23         "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  24         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
  25
  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
  55         <!-- User class mapping details -->
  56         <class name="EPUser" table="FN_USER">
  57                 <id name="id" column="user_id">
  58                         <generator class="native">
  59                                 <param name="sequence">seq_fn_user</param>
  60                         </generator>
  61                 </id>
  62
  63                 <property name="orgId" column="org_id" />
  64                 <property name="managerId" column="manager_id" />
  65                 <property name="firstName" column="first_name" />
  66                 <property name="middleInitial" column="middle_name" />
  67                 <property name="lastName" column="last_name" />
  68                 <property name="phone" column="phone" />
  69                 <property name="fax" column="fax" />
  70                 <property name="cellular" column="cellular" />
  71                 <property name="email" column="email" />
  72                 <property name="addressId" column="address_id" />
  73                 <property name="alertMethodCd" column="alert_method_cd" />
  74
  75                 <property name="address1" column="address_line_1" />
  76                 <property name="address2" column="address_line_2" />
  77                 <property name="city" column="city" />
  78                 <property name="state" column="state_cd" />
  79                 <property name="zipCode" column="zip_code" />
  80                 <property name="country" column="country_cd" />
  81
  82                 <property name="hrid" column="hrid" />
  83                 <property name="orgUserId" column="org_user_id" />
  84                 <property name="orgCode" column="org_code" />
  85                 <property name="loginId" column="login_id" />
  86                 <property name="loginPwd" column="login_pwd" />
  87                 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
  88
  89                 <property name="locationClli" column="location_clli" />
  90                 <property name="orgManagerUserId" column="org_manager_userid" />
  91                 <property name="company" column="company" />
  92                 <property name="department" column="department" />
  93                 <property name="departmentName" column="department_name" />
  94                 <property name="businessUnit" column="business_unit" />
  95                 <property name="businessUnitName" column="business_unit_name" />
  96                 <property name="jobTitle" column="job_title" />
  97                 <property name="siloStatus" column="silo_status" />
  98                 <property name="financialLocCode" column="fin_loc_code" />
  99
 100                 <property name="active" column="active_yn" type="yes_no" />
 101                 <property name="internal" column="is_internal_yn" type="yes_no" />
 102
 103                 <property name="created" type="timestamp" column="created_date" />
 104                 <property name="modified" type="timestamp" column="modified_date" />
 105
 106                 <property name="createdId" column="created_id" />
 107                 <property name="modifiedId" column="modified_id" />
 108                 <property name="timeZoneId" column="timezone" />
 109
 110         <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural" inverse="true">
 111             <key column="user_id"/>
 112             <one-to-many class="org.openecomp.portalapp.portal.domain.EPUserApp" />
 113         </set>
 114
 115         </class>
 116
 117         <!-- UserApp class mapping details -->
 118         <class name="EPUserApp" table="fn_user_role">
 119                 <composite-id>
 120                         <key-property name="userId" type="long">
 121                                 <column name="user_id" precision="11" scale="0" />
 122                         </key-property>
 123                         <key-many-to-one name="app" class="org.openecomp.portalapp.portal.domain.EPApp" column="app_id" />
 124                         <key-many-to-one name="role" class="org.openecomp.portalapp.portal.domain.EPRole" column="role_id" />
 125                 </composite-id>
 126                 <property name="priority" type="java.lang.Short">
 127                         <column name="priority" precision="4" scale="0" />
 128                 </property>
 129                 </class>
 130
 131         <!-- User App class mapping details -->
 132         <class name="EPApp" table="fn_app">
 133                 <id name="id" column="app_id">
 134                         <generator class="native">
 135                                 <param name="sequence">seq_fn_app</param>
 136                         </generator>
 137                 </id>
 138                 <property name="name" type="string">
 139                         <column name="app_name" not-null="true" default="?"></column>
 140                 </property>
 141                 <property name="imageUrl" column="app_image_url" />
 142                 <property name="description" column="app_description" />
 143                 <property name="notes" column="app_notes" />
 144                 <property name="url" column="app_url" />
 145                 <property name="alternateUrl" column="app_alternate_url" />
 146                 <property name="appRestEndpoint" column="app_rest_endpoint" />
 147                 <property name="mlAppName" type="string">
 148                         <column name="ml_app_name" not-null="true" default="?"></column>
 149                 </property>
 150                 <property name="mlAppAdminId" type="string">
 151                         <column name="ml_app_admin_id" not-null="true" default="?"></column>
 152                 </property>
 153                 <property name="motsId" column="mots_id" />
 154                 <property name="appPassword" type="string">
 155                         <column name="app_password" not-null="true" default="?"></column>
 156                 </property>
 157                 <property name="thumbnail" column="thumbnail"/>
 158                 <property name="username" type="string">
 159                         <column name="app_username" not-null="true" default="?"></column>
 160                 </property>
 161                 <property name="open" type="yes_no">
 162                         <column name="open" not-null="true" default="Y"></column>
 163                 </property>
 164                 <property name="enabled" type="yes_no">
 165                         <column name="enabled" not-null="true" default="N"></column>
 166                 </property>
 167                 <property name="uebTopicName" type="string">
 168                         <column name="ueb_topic_name"></column>
 169                 </property>
 170                 <property name="uebKey" type="string">
 171                         <column name="ueb_key"></column>
 172                 </property>
 173                 <property name="uebSecret" type="string">
 174                         <column name="ueb_secret"></column>
 175                 </property>
 176                 <property name="appType" type="integer">
 177                         <column name="app_type"></column>
 178                 </property>
 179
 180                 <one-to-one name="contactUs" class="AppContactUs"  cascade="save-update"></one-to-one>
 181
 182         </class>
 183
 184
 185         <!-- User Role class mapping details -->
 186         <class name="EPRole" table="FN_ROLE">
 187                 <id name="id" column="role_id">
 188                         <generator class="native">
 189                                 <param name="sequence">seq_fn_role</param>
 190                         </generator>
 191                 </id>
 192
 193                 <property name="name" column="role_name" />
 194                 <property name="priority" column="priority" />
 195                 <property name="active" column="active_yn" type="yes_no" />
 196                 <!-- 2 lines below may be removed! -->
 197         <property name="appId"     column="APP_ID" />
 198         <property name="appRoleId" column="APP_ROLE_ID" />
 199         <!-- 2 lines above may be removed -->
 200
 201         <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false" sort="natural">
 202                         <key column="role_id" />
 203             <many-to-many column="function_cd" class="org.openecomp.portalsdk.core.domain.RoleFunction"/>
 204                 </set>
 205
 206         <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false" sort="natural">
 207                         <key column="parent_role_id" />
 208                         <many-to-many column="child_role_id" class="org.openecomp.portalapp.portal.domain.EPRole" />
 209                 </set>
 210
 211         <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false" sort="natural">
 212                         <key column="child_role_id" />
 213                         <many-to-many column="parent_role_id" class="org.openecomp.portalapp.portal.domain.EPRole" />
 214                 </set>
 215
 216         </class>
 217
 218         <!-- User App class mapping details -->
 219         <class name="AppContactUs" table="fn_app_contact_us">
 220                 <id name="id" column="app_id">
 221                         <generator class="foreign">
 222                                 <param name="property">app</param>
 223                         </generator>
 224                 </id>
 225                 <property name="url" column="url" />
 226                 <property name="description" column="description" />
 227                 <property name="contactName" column="contact_name" />
 228                 <property name="contactEmail" column="contact_email" />
 229                 <property name="activeYN" column="active_yn" />
 230
 231                 <one-to-one name="app" class="EPApp"  constrained="true" ></one-to-one>
 232
 233         </class>
 234
 235         <!-- Personalization of user app selections -->
 236         <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
 237                 <id name="id" column="id">
 238                         <generator class="native">
 239                                 <param name="property">seq_fn_pers_user_app_sel</param>
 240                         </generator>
 241                 </id>
 242                 <property name="userId" column="user_id" />
 243                 <property name="appId" column="app_id" />
 244                 <property name="statusCode" column="status_cd" />
 245         </class>
 246
 247         <!-- requires values for named parameters :searchQuery and :userId -->
 248         <sql-query name="searchPortal">
 249                 <return alias="searchResult" class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem"/>
 250                 <![CDATA[
 251                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 252                         ( (
 253                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, email TARGET
 254                                 from fn_user where
 255                                 upper( :searchQuery ) != ''
 256                                 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
 257                                 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
 258                                 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
 259                                 and  upper(active_yn) = 'Y'
 260                                 )
 261                                 union
 262                                 (
 263                                 select distinct 'Application' CATEGORY, b1.app_name NAME,
 264                                 if (b1.app_type = 2,'false','true') UUID,
 265                                 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
 266                                 from
 267                                 (
 268                                 select c.*
 269                                 from fn_user_role a, fn_user b, fn_app c
 270                                 where a.USER_ID = b.user_id
 271                                 and upper(b.org_user_id) = upper(  :userId )
 272                                 and a.app_id = c.app_id
 273                                 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 274                                 and upper(c.ENABLED) = 'Y'
 275                                 ) a1 right outer join
 276                                 (select * from fn_app where
 277                                 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 278                                 and upper(ENABLED) = 'Y') b1
 279                                 on a1.APP_ID = b1.app_id
 280                                 )
 281                                 union
 282                                 (
 283                                 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
 284                                 if (b1.app_type = 2,'false','true') UUID,
 285                                 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
 286                                 from
 287                                 (select distinct  d.*, e.app_type from fn_user a,
 288                                 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
 289                                 where a.USER_ID = b.user_id
 290                                 and b.role_id = c.role_id
 291                                 and c.menu_id = d.menu_id
 292                                 and upper(a.org_user_id) = upper( :userId )
 293                                 and c.APP_ID = e.app_id
 294                                 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 295                                 and upper(d.active_yn) = 'Y'
 296                                 and upper(e.enabled) = 'Y'
 297                                 ) a1 right outer join
 298                                 (
 299                                 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
 300                                 where active_yn = 'Y'
 301                                 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 302                                 and a.menu_id = b.menu_id
 303                                 and b.app_id = c.app_id
 304                                 and c.enabled = 'Y'
 305                                 ) b1
 306                                 on a1.menu_id = b1.menu_id
 307                                 ) ) t,
 308                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 309                         ;
 310                 ]]>
 311         </sql-query>
 312
 313         <!-- Gets one row for each function-application-role combination. -->
 314         <sql-query name="getAppAccessFunctionRole">
 315                 <return alias="appAccessFunctionRole" class="org.openecomp.portalapp.portal.domain.GetAccessResult"/>
 316                 <!-- This query requires no parameters. -->
 317                 <![CDATA[
 318                         SELECT
 319                                 A.TEXT ECOMP_FUNCTION, D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME
 320                         FROM
 321                                 fn_menu_functional A, fn_menu_functional_roles B, FN_ROLE C, FN_APP D
 322                         WHERE
 323                                 A.MENU_ID = B.MENU_ID AND B.ROLE_ID = C.ROLE_ID AND B.APP_ID = D.APP_ID
 324                         ;
 325                 ]]>
 326         </sql-query>
 327
 328         <!-- Gets all applications, possibly with contact information -->
 329          <sql-query name="getAppsAndContacts">
 330                 <return alias="appContactUs" class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem"/>
 331                 <!-- This query requires no parameters. -->
 332                 <![CDATA[
 333                         select
 334                                 a.app_id as appId, a.app_name as appName,
 335                                 c.contact_name as contactName,
 336                                 c.contact_email as contactEmail, c.url, c.description,
 337                                 c.active_yn as activeYN
 338                         from
 339                                 fn_app a
 340                         left join
 341                                 fn_app_contact_us c
 342                                 on a.app_id = c.app_id
 343                         where
 344                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 345                         ;
 346                 ]]>
 347         </sql-query>
 348         <!-- Gets all rows from the app-contact-us table, extended with app information -->
 349          <sql-query name="getAppContactUsItems">
 350                 <return alias="appContactUs" class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem"/>
 351                 <!-- This query requires no parameters. -->
 352                 <![CDATA[
 353                         select
 354                                 c.app_id as appId, c.contact_name as contactName,
 355                                 c.contact_email as contactEmail, c.url, c.description,
 356                                 c.active_yn as activeYN, a.app_name as appName
 357                         from
 358                                 fn_app_contact_us c
 359                         left join
 360                                 fn_app a
 361                                 on a.app_id = c.app_id
 362                         where
 363                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 364                         ;
 365                 ]]>
 366         </sql-query>
 367         <sql-query name="getAppCategoryFunctions">
 368                 <return alias="appCategoryFunctions" class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem"/>
 369                 <!-- This query requires no parameters. -->
 370                 <![CDATA[
 371                         SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
 372                                 r.category as category, r.app_id as appId, r.app_name as application,
 373                                 group_concat(function_text separator ', ') as functions
 374                         FROM (
 375                                 SELECT
 376                                 app_id, function_text, app_name,
 377                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
 378                                 FROM (
 379                                         SELECT
 380                                         j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
 381                                         k.text parent_menu, k.parent_menu_id parent_menu_id
 382                                         FROM (
 383                                                 SELECT distinct
 384                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
 385                                                 FROM
 386                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
 387                                                 WHERE
 388                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
 389                                                 ) j,
 390                                         fn_menu_functional k
 391                                         WHERE j.parent_menu_id = k.menu_id
 392                                         ) fn,
 393                                 fn_menu_functional a
 394                                 WHERE fn.parent_menu_id = a.menu_id
 395                         ) r,
 396                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 397                         group by r.category, r.app_id, r.app_name
 398                         order by category, app_name
 399                         ;
 400                 ]]>
 401         </sql-query>
 402
 403
 404     <sql-query name="getGuestLastLogin">
 405                 <return-scalar  column="audit_date" type="java.util.Date"/>
 406                 <![CDATA[
 407                         select audit_date from fn_audit_log where affected_record_id =:attuid order by audit_date desc limit 2;
 408                 ]]>
 409         </sql-query>
 410
 411         <!-- Gets personalized list of enabled and accessible apps for regular user -->
 412         <sql-query name="getPersUserApps">
 413                 <return alias="persUserApps" class="EPApp"/>
 414                 <!-- This query requires one parameter: userId (number) -->
 415                 <![CDATA[
 416                     select
 417                                 distinct  -- multiple roles yield multiple rows
 418                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
 419                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
 420                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
 421                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
 422                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
 423                     from FN_APP a
 424                     -- Portal assigns role 999 to app administrator
 425                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
 426                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
 427                         where a.ENABLED = 'Y'
 428                     and (
 429                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
 430                         or
 431                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
 432                         )
 433                         order by app_name
 434                     ;
 435                 ]]>
 436         </sql-query>
 437
 438         <!-- Gets personalized list of enabled and accessible apps for Portal (super) admin -->
 439         <sql-query name="getPersAdminApps">
 440                 <return alias="persAdminApps" class="EPApp"/>
 441                 <!-- This query requires one parameter: userId (number) -->
 442                 <![CDATA[
 443                     select
 444                                 distinct  -- multiple roles yield multiple rows
 445                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
 446                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
 447                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
 448                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
 449                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
 450                     from FN_APP a
 451                     -- Portal assigns role 999 to app administrator
 452                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
 453                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
 454                         where a.ENABLED = 'Y'
 455                     and (
 456                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
 457                         or
 458                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
 459                                 or
 460                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
 461                         )
 462                         order by app_name
 463                     ;
 464                 ]]>
 465         </sql-query>
 466
 467         <!-- Gets regular user's list of enabled apps including accessible and select statuses -->
 468         <sql-query name="getUserAppCatalog">
 469                 <return alias="userAppCatalog" class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem"/>
 470                 <!-- This query requires one parameter: userId (number) -->
 471                 <![CDATA[
 472                         select
 473                                 distinct  -- multiple roles yield multiple rows
 474                             a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
 475                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
 476                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
 477                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
 478                             IF(a.open = 'Y', TRUE, FALSE) as open,
 479                 -- ACCESS(-ible) means user has a defined role OR the application is open
 480                                 case
 481                                         -- regular app and user has a role
 482                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
 483                     -- open app
 484                     when a.OPEN = 'Y' then TRUE
 485                     else FALSE
 486                 end as 'access' ,
 487                 -- SELECT(-ed) indicates user personalization
 488                 case
 489                                         -- regular app, user has a role, no personalization
 490                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
 491                     -- open app and has personalization
 492                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
 493                     else FALSE
 494                 end as 'select' ,
 495                 -- PENDING indicates user checked a box
 496                 case
 497                         when a.OPEN = 'N' and p.STATUS_CD = 'P' then TRUE
 498                         else FALSE
 499                 end as 'pending'
 500                     from FN_APP a
 501                     -- Portal assigns role 999 to app administrator
 502             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
 503                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
 504                         where a.ENABLED = 'Y'
 505                         -- Show accessible apps first, then the rest; sort by name within each set.
 506             order by access desc, app_name asc
 507                     ;
 508                 ]]>
 509         </sql-query>
 510
 511         <!-- Gets list of enabled apps including accessible and select statuses -->
 512         <sql-query name="getAdminAppCatalog">
 513                 <return alias="adminAppCatalog" class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem"/>
 514                 <!-- This query requires one parameter: userId (number) -->
 515                 <![CDATA[
 516                         select
 517                                 distinct  -- multiple roles yield multiple rows
 518                             a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
 519                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
 520                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
 521                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
 522                             IF(a.open = 'Y', TRUE, FALSE) as open,
 523                 -- ACCESS(-ible) means user has a defined role OR the application is open
 524                                 case
 525                                         -- regular app and user has a role
 526                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
 527                     -- open app
 528                     when a.OPEN = 'Y' then TRUE
 529                     else FALSE
 530                 end as 'access' ,
 531                 -- SELECT(-ed) indicates user personalization
 532                 case
 533                                         -- regular app, user has a role, no personalization
 534                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
 535                                         -- regular app, user has no role, admin forced a personalization
 536                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
 537                     -- open app and has personalization
 538                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
 539                     else FALSE
 540                 end as 'select' ,
 541                 -- PENDING indicates user checked a box
 542                 case
 543                         when a.OPEN = 'N' and p.STATUS_CD = 'P' then TRUE
 544                         else FALSE
 545                 end as 'pending'
 546                     from FN_APP a
 547                     -- Portal assigns role 999 to app administrator
 548             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
 549                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
 550                         where a.ENABLED = 'Y'
 551                         -- Show accessible apps first, then the rest; sort by name within each set.
 552             order by access desc, app_name asc
 553                     ;
 554                 ]]>
 555         </sql-query>
 556
 557
 558 </hibernate-mapping>