<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>
<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
+ (
+ 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, creator_ID,active_YN
+ 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 = :functionCode and app_id =:appId
+ ;
+
+ ]]>
+ </sql-query>
+
+ <!-- Gets the record from ep_app_function table and requires two parameters -->
+ <sql-query name="getAppFunctionDetails">
+ <return alias="appFunctionDetails"
+ class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
+ <![CDATA[
+
+ select * from ep_app_function where app_id =:appId and function_cd =:functionCd
+ ;
+ ]]>
+ </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_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)
+ ;
+ ]]>
+ </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>
+
</hibernate-mapping>