<?xml version="1.0"?>
<!--
- ================================================================================
- ECOMP Portal
- ================================================================================
- Copyright (C) 2017 AT&T Intellectual Property
- ================================================================================
- Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License.
+ ============LICENSE_START==========================================
+ ONAP Portal
+ ===================================================================
+ Copyright © 2017 AT&T Intellectual Property. All rights reserved.
+ ===================================================================
+
+ Unless otherwise specified, all software contained herein is licensed
+ under the Apache License, Version 2.0 (the “License”);
+ you may not use this software except in compliance with the License.
You may obtain a copy of the License at
-
- http://www.apache.org/licenses/LICENSE-2.0
-
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
- ================================================================================
+
+ Unless otherwise specified, all documentation contained herein is licensed
+ under the Creative Commons License, Attribution 4.0 Intl. (the “License”);
+ you may not use this documentation except in compliance with the License.
+ You may obtain a copy of the License at
+
+ https://creativecommons.org/licenses/by/4.0/
+
+ Unless required by applicable law or agreed to in writing, documentation
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ ============LICENSE_END============================================
+
+ ECOMP is a trademark and service mark of AT&T Intellectual Property.
-->
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
<property name="appType" type="integer">
<column name="app_type"></column>
</property>
-
+ <property name="centralAuth" type="yes_no">
+ <column name="auth_central"></column>
+ </property>
+ <property name="nameSpace" type="string">
+ <column name="auth_namespace"></column>
+ </property>
<one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
</class>
</class>
<!-- User Role class mapping details -->
- <class name="EPRole" table="FN_ROLE">
+ <class name="EPRole" table="FN_ROLE" >
<id name="id" column="role_id">
<generator class="native">
<param name="sequence">seq_fn_role</param>
<property name="statusCode" column="status_cd" />
</class>
+ <!-- EPAppRoleFunction class mapping details -->
+ <class name="EPAppRoleFunction" table="ep_app_role_function">
+ <id name="id" column="id">
+ <generator class="native">
+ <param name="property">seq_epp_app_role_func</param>
+ </generator>
+ </id>
+ <property name="roleId" column="role_id"></property>
+ <property name="appId" column="app_id"></property>
+ <property name="code" column="function_cd"></property>
+ </class>
+
+ <!-- CentralRoleFunction class mapping details -->
+ <class name="CentralRoleFunction" table="ep_app_function">
+ <composite-id>
+ <key-property name="appId"
+ column="app_id" />
+ <key-property name="code"
+ column="function_cd" />
+ </composite-id>
+ <property name="name" column="function_name" />
+ </class>
+
<class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
<id name="id" column="id">
<generator class="native">
<return alias="notificationResult"
class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
<![CDATA[
- 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
+ select @rn /*'*/:=/*'*/ @rn+1 AS rowId, notification_ID, is_for_online_users,is_for_all_roles, msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date, creator_ID,notification_hyperlink, active_YN from
(
- 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
+ select notification_ID, is_for_online_users, is_for_all_roles, msg_header, msg_description, msg_source,start_Time, end_time, priority,created_date, creator_ID,notification_hyperlink,active_YN
from
(
- 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
+ select user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN
from
(
- select a.*, b.role_id,b.recv_user_id
+ select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
+ a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.notification_hyperlink,a.created_date,b.role_id,b.recv_user_id
from ep_notification a, ep_role_notification b
where a.notification_id = b.notification_id
and (end_time is null || SYSDATE() <= end_time )
and a.is_for_all_roles = 'N'
) a,
(
- select a.user_id, c.role_id, c.app_id, d.APP_NAME
+ select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
from fn_user a, fn_user_role b, fn_role c, fn_app d
- where a.user_id = b.user_id
+ where COALESCE(c.app_id,1) = d.app_id
+ and a.user_id = b.user_id
and a.user_id = :user_id
and b.role_id = c.role_id
- and c.app_id = d.app_id
- and d.enabled='Y'
+ and (d.enabled='Y' or d.app_id=1)
)b
where
(
- a.role_id = b.role_id||a.recv_user_id=:user_id
+ a.role_id = b.role_id
)
union
+ select :user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN
+ from
+ (
+ select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
+ a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date, a.notification_hyperlink,b.role_id,b.recv_user_id
+ from ep_notification a, ep_role_notification b
+ where a.notification_id = b.notification_id
+ and (end_time is null || SYSDATE() <= end_time )
+ and (start_time is null || SYSDATE() >= start_time)
+ and a.is_for_all_roles = 'N'
+ ) a
+ where
(
- 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
+ a.recv_user_id=:user_id
+ )
+ union
+ (
+ select :user_id user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description, msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN
from ep_notification a
where a.notification_id
and (end_time is null || SYSDATE() <= end_time )
and
not exists
(
- select * from ep_user_notification m where user_id = :user_id and m.notification_id = a.notification_id and is_viewed = 'Y'
+ 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'
)
- order by priority desc, start_Time desc,created_date desc
+ order by priority desc, created_date desc,start_Time desc
) t,
<return alias="adminNotificationHistoryVOResult"
class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
<![CDATA[
- SELECT
+ SELECT * from (
+SELECT
n.notification_ID AS notificationId,
n.is_for_online_users AS isForOnlineUsers,
n.is_for_all_roles AS isForAllRoles,
n.priority,
n.creator_ID AS creatorId,
n.created_date AS createdDate,
+ n.notification_hyperlink AS notificationHyperlink,
u.org_user_id AS loginId
FROM
ep_notification n
LEFT JOIN
fn_user u on u.user_id = n.creator_id
WHERE
- n.active_YN='Y' order by n.start_time desc
+ n.active_YN='Y' and msg_source='EP'
+
+ UNION
+
+ SELECT
+ n.notification_ID AS notificationId,
+ n.is_for_online_users AS isForOnlineUsers,
+ n.is_for_all_roles AS isForAllRoles,
+ n.active_YN AS activeYn,
+ n.msg_header AS msgHeader,
+ n.msg_description AS msgDescription,
+ n.msg_source AS msgSource,
+ n.start_Time AS startTime,
+ n.end_time AS endTime,
+ n.priority,
+ n.creator_ID AS creatorId,
+ n.created_date AS createdDate,
+ n.notification_hyperlink AS notificationHyperlink,
+ u.org_user_id AS loginId
+ FROM
+ ep_notification n
+ LEFT JOIN
+ fn_user u on u.user_id = n.creator_id
+ JOIN
+ ep_role_notification r on r.notification_ID=n.notification_ID
+ WHERE
+ n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
+ )n order by n.startTime desc
]]>
</sql-query>
union
select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
from fn_app, fn_role
-where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' order by app_name
+where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
<![CDATA[
SELECT
notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
- startTime, endTime, priority, createdDate, creatorId, loginId, activeYn
+ startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
FROM
(
select distinct
priority,
created_date AS createdDate,
creator_ID AS creatorId,
+ notification_hyperlink AS notificationHyperlink,
login_id AS loginId,
active_YN AS activeYn,
if (is_viewed is null, 'N', is_viewed)
select
user_id, login_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
+ creator_ID,notification_hyperlink,active_YN
from
(
- select a.*, b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
+ select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
+ a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.notification_hyperlink,a.created_date,
+ b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
ELSE NULL
END AS login_id,b.recv_user_id
)
) a,
(
- select a.user_id, c.role_id, c.app_id, d.APP_NAME
+ select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
from fn_user a, fn_user_role b, fn_role c, fn_app d
- where a.user_id = b.user_id
+ where COALESCE(c.app_id,1) = d.app_id
+ and a.user_id = b.user_id
and a.user_id = :user_id
and b.role_id = c.role_id
- and c.app_id = d.app_id
- and d.enabled='Y'
+ and (d.enabled='Y' or d.app_id=1)
) b
where
(
- a.role_id = b.role_id||a.recv_user_id=:user_id
+ a.role_id = b.role_id
+ )
+ UNION
+ select
+ :user_id, login_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,notification_hyperlink,active_YN
+ from
+ (
+ select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
+ a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,a.notification_hyperlink,
+ b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
+
+ ELSE NULL
+ END AS login_id,b.recv_user_id
+ from ep_notification a, ep_role_notification b,fn_user u
+ where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
+ and a.is_for_all_roles = 'N'
+ and (
+ (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
+ or
+ (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
+ or
+ (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
+ or
+ (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
)
+ ) a
+ where
+ (
+ a.recv_user_id=:user_id
+ )
union
(
select
:user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
- creator_ID, a.active_YN
+ creator_ID, a.notification_hyperlink,a.active_YN
from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
where a.notification_id
and a.is_for_all_roles = 'Y'
select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
]]>
</sql-query>
+
+ <sql-query name="getActiveUsers">
+ <return alias="EPUser" class="org.openecomp.portalapp.portal.domain.EPUser" />
+ <![CDATA[
+ SELECT * FROM FN_USER WHERE active_yn='Y'
+ ]]>
+ </sql-query>
+ <sql-query name="getAppsAdmins">
+ <return alias="adminUserApp" class="org.openecomp.portalapp.portal.domain.AdminUserApp" />
+ <![CDATA[
+ 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)
+ ]]>
+ </sql-query>
+
+ <sql-query name="getPortalAdmins">
+ <return alias="portalAdmin" class="org.openecomp.portalapp.portal.transport.PortalAdmin" />
+ <![CDATA[
+ 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
+ ]]>
+ </sql-query>
+
<!-- Gets personalized list of enabled and accessible apps for regular user -->
<sql-query name="getPersUserApps">
<return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
- a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
+ a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
+ a.AUTH_NAMESPACE
from FN_APP a
-- Portal assigns role 999 to app administrator
left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
<![CDATA[
select
distinct -- multiple roles yield multiple rows
- a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
+ a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
IF(a.app_type = '2', TRUE, FALSE) as restricted,
]]>
</sql-query>
- <sql-query name="getAppDetails">
- <return alias="appDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
+ <sql-query name="getMyloginAppDetails">
+ <return alias="myloginAppDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
+
+ <![CDATA[
+ SELECT * FROM fn_app where ml_app_name =:appName
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getMyAppDetailsByUebKey">
+ <return alias="myAppDetailsByUebKey" class="org.openecomp.portalapp.portal.domain.EPApp" />
<![CDATA[
- SELECT * FROM fn_app where app_name =:appName
+ SELECT * FROM fn_app where ueb_key =:appKey
;
]]>
</sql-query>
<sql-query name="deleteAccountEndpoint">
<![CDATA[
-
DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
;
]]>
<sql-query name="deleteAccountEndpointRecord">
<![CDATA[
-
DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
;
]]>
<sql-query name="deleteEPEndpoint">
<![CDATA[
-
DELETE FROM ep_endpoints WHERE id =:epId
;
]]>
<sql-query name="deleteBasicAuthAccount">
<![CDATA[
-
DELETE FROM ep_basic_auth_account WHERE id =:accountId
;
]]>
<return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
<![CDATA[
-
SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
;
]]>
</sql-query>
-
+
+ <sql-query name="getPortalAppRoles">
+ <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
+
+ <![CDATA[
+
+ SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
+ ;
+ ]]>
+ </sql-query>
<sql-query name="deleteMicroserviceParameter">
<![CDATA[
<sql-query name="appRoles">
<return alias="rolesForApp"
- class="org.openecomp.portalapp.portal.domain.EpUserAppRoles" />
+ class="org.openecomp.portalapp.portal.domain.EPUserAppRoles" />
<![CDATA[
- SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID =:appRoleId and app_id =:appId
+ SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
;
]]>
</sql-query>
<![CDATA[
select
distinct -- multiple roles yield multiple rows
- a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
+ a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
IF(a.app_type = '2', TRUE, FALSE) as restricted,
case
-- regular app, user has a role, no personalization
when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
+ -- regular app, user has role, admin forced a personalization
+ when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
-- regular app, user has no role, admin forced a personalization
when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
-- open app and has personalization
select fr.role_name, fa.app_name
from
fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
- 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
+ Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id and fu.org_user_id = :userId and fr.active_yn='Y' and (fa.enabled = 'Y' or fa.app_id=1)
;
]]>
</sql-query>
+
+ <!--delete the records from ep_notification table when the endtime is more than 3 months-->
+ <sql-query name="deleteNotificationsFromEpNotificationTable">
+
+ <![CDATA[
+ delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
+ ]]>
+ </sql-query>
+
+
+ <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
+ <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
+
+ <![CDATA[
+ delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
+ ]]>
+ </sql-query>
+
+ <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
+ <sql-query name="deleteNotificationsFromEpUserNotificationTable">
+
+ <![CDATA[
+ delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
+ ]]>
+ </sql-query>
+
+ <!-- Gets list of role functions and it requires two parameters appId and roleId-->
+ <sql-query name="getAppRoleFunctionList">
+ <return alias="appRoleFunctionList"
+ class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
+ <![CDATA[
+
+ SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
+ where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
+ ;
+
+ ]]>
+ </sql-query>
+
+ <!-- Gets list of all role functions -->
+ <sql-query name="getAllRoleFunctions">
+ <return alias="allRoleFunctions"
+ class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
+ <![CDATA[
+
+ SELECT * from ep_app_function where app_id =:appId
+ ;
+
+ ]]>
+ </sql-query>
+
+ <!-- Get ep_app_function records provided by single parameter -->
+ <sql-query name="getRoleFunction">
+ <return alias="RoleFunction"
+ class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
+ <![CDATA[
+
+ SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
+ ;
+
+ ]]>
+ </sql-query>
+
+ <!-- Gets the current user app roles records and requires two parameters -->
+ <sql-query name="getUserAppCurrentRoles">
+ <return alias="userAppCurrentRoles"
+ class="org.openecomp.portalapp.portal.transport.EPUserAppCurrentRoles" />
+ <![CDATA[
+
+ select distinct fu.role_id, fr.user_id, fu.role_name, fu.priority from fn_role fu left outer join fn_user_role fr ON fu.role_id = fr.role_id and fu.app_id = fr.app_id and fr.role_id != 999 where fu.app_id =:appId and fr.user_id =:userId
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Gets the current user app roles along with account administrator and requires two parameters -->
+ <sql-query name="getUserAppExistingRoles">
+ <return alias="userAppExistingRoles"
+ class="org.openecomp.portalapp.portal.transport.EcompUserAppRoles" />
+ <![CDATA[
+ select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
+ left outer join fn_role fr on fu.role_id = fr.role_id
+ where fu.user_id =:userId and fu.app_id =:appId
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Gets the current user app roles records and requires two parameters -->
+ <sql-query name="getAllFunctions">
+ <return alias="allFunctions"
+ class="org.openecomp.portalsdk.core.domain.RoleFunction" />
+ <![CDATA[
+ select * from fn_function
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Gets the all role functions and requires one parameters -->
+ <sql-query name="uploadAllRoleFunctions">
+ <return alias="allRoleFunctions"
+ class="org.openecomp.portalapp.portal.transport.BulkUploadRoleFunction" />
+ <![CDATA[
+ select fr.function_cd, fn.function_name from fn_role_function fr left outer join fn_function fn on fr.function_cd = fn.function_cd where role_id =:roleId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getRoletoUpdateAAF">
+ <return alias="getRoletoUpdateAAF" class="org.openecomp.portalapp.portal.domain.EPRole" />
+ <![CDATA[
+ SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getBulkUserRoles">
+ <return alias="bulkUserRoles" class="org.openecomp.portalapp.portal.transport.BulkUploadUserRoles" />
+ <![CDATA[
+ select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
+ left outer join fn_role fr on fr.role_id = fur.role_id
+ left outer join fn_app fa on fa.app_id = fur.app_id
+ left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fu.org_user_id != 'su1234'
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getCentralizedApps">
+ <return alias="centralizedApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
+ <![CDATA[
+ select * from fn_app where auth_central = 'Y' and open = 'N';
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getUserRoles">
+ <return alias="getUserRolesList" class="org.openecomp.portalapp.portal.domain.UserRole" />
+
+ <![CDATA[
+
+ SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID WHERE user.org_user_id =:org_user_id and userrole.app_id = 1
+ ;
+ ]]>
+ </sql-query>
+ <sql-query name="getRoleFunctionsOfUser">
+ <![CDATA[
+ select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
+ where fu.role_id = ep.role_id
+ and fu.app_id = ep.app_id
+ and fu.user_id =:userId and ep.app_id = 1
+ and ea.function_cd = ep.function_cd
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getUserRolesForLeftMenu">
+ <return alias="getUserRolesListForLeftMenu" class="org.openecomp.portalapp.portal.domain.UserRole" />
+
+ <![CDATA[
+ SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole
+ INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
+ INNER JOIN fn_app app ON app.app_id= userrole.app_id
+ INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
+ WHERE user.org_user_id =:org_user_id and (userrole.app_id = 1 or role.role_id = 999) and (app.enabled='Y' or app.app_id=1)
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Gets all functions for an application along with global functions and requires single parameter -->
+ <sql-query name="getMenuFunctions">
+ <![CDATA[
+ select f.function_cd from ep_app_function f
+ where f.app_id =:appId
+ UNION
+ select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
+ and epa.function_cd = epr.function_cd and fnr.role_name like 'global%'
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getRequestIdsForApp">
+ <return alias="getRequestIdsForApp" class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
+ <![CDATA[
+ select * from ep_user_roles_request where app_id =:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="ApplicationUserRoles">
+ <return alias="ApplicationUserRoles" class="org.openecomp.portalapp.portal.transport.EcompUserRoles" />
+ <![CDATA[
+ select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title, fu.login_id,
+ fu.active_yn , fr.app_role_id, fr.role_name
+ from fn_user fu, fn_role fr, fn_user_role fur
+ where fu.user_id = fur.user_id and fu.active_yn='Y' and fur.role_id = fr.role_id and fr.app_id =:appId and fr.active_yn='Y'
+ union
+ select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title, fu.login_id, fu.active_yn , fr.role_id, fr.role_name
+ from fn_user fu, fn_role fr, fn_user_role fur
+ where fu.user_id = fur.user_id and fu.active_yn='Y' and fur.app_id=:appId and fr.role_name like 'global%' and fr.active_yn='Y'
+ ;
+ ]]>
+ </sql-query>
+
+ <query name="getEPUserByOrgUserId">
+ <![CDATA[
+ FROM EPUser WHERE orgUserId = :org_user_id
+ ]]>
+ </query>
+
+ <query name="getEPUserByLoginId">
+ <![CDATA[
+ FROM EPUser WHERE loginId = :login_id
+ ]]>
+ </query>
+
+ <query name="getEPUserByLoginIdLoginPwd">
+ <![CDATA[
+ FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
+ ]]>
+ </query>
</hibernate-mapping>
Code Review / portal.git / blobdiff