UPDATE {databaseOwner}{objectQualifier}DMX_Notifications
SET PortalId=l.PortalId, Sent=GETDATE()
FROM {databaseOwner}{objectQualifier}DMX_Notifications n
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Log l ON l.LogId=n.LogId
GO

IF EXISTS (select * from {databaseOwner}sysobjects where id = object_id(N'{objectQualifier}vw_DMX_Notifications') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Notifications
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Notifications
AS
SELECT
 n.LogId,
 n.PortalId,
 n.Sent,
 n.Template, 
 n.UserId AS RecipientUserId,
 l.Action,
 l.Datime,
 l.EntryId,
 l.UserId, 
 lt.ResourceFile,
 lt.AttachFile,
 lt.CustomSend, 
 lt.NotifyApprovers,
 lt.NotifyAudit,
 lt.NotifyEditors, 
 lt.NotifyOwner,
 lt.NotifySelf,
 lt.NotifySubscribers, 
 lt.LogTypeKey
FROM {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.PortalId = lt.PortalId AND l.Action=lt.LogTypeKey
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Notifications n ON l.LogId = n.LogId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetNotification') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNotification
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNotification
 @LogId INT,
 @UserId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Notifications
WHERE
 [LogId] = @LogId
 AND [RecipientUserId] = @UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUnsentNotifications') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUnsentNotifications
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUnsentNotifications
 @PortalId int,
 @UserId Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Notifications
WHERE
 [RecipientUserId] = @UserId
 AND [PortalId] = @PortalId
 AND [Sent] IS NULL

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser
 @PortalId INT,
 @UserId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions ap
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles aur ON ap.RoleId=aur.RoleId
WHERE
 ap.PortalId = @PortalId
 AND ISNULL(aur.UserId, ap.UserId) = @UserId
 AND ap.AllowAccess = 1
GO


