/****** TABLES AND PRIMARY KEYS ******/
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_Entries') and OBJECTPROPERTY(id, N'IsTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries
ADD
 [Path] VARCHAR (2000),
 [PermissionsOnlyByAdmin] BIT

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_LogTypes') and OBJECTPROPERTY(id, N'IsTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_LogTypes
ADD
 [AttachFile] BIT,
 [CustomSend] BIT,
 [NotifyApprovers] NVARCHAR (100),
 [NotifyAudit] BIT,
 [NotifyEditors] NVARCHAR (100),
 [NotifyOwner] NVARCHAR (100),
 [NotifySelf] NVARCHAR (100),
 [NotifySubscribers] NVARCHAR (100),
 [PortalId] INT

GO

if exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_LogTypes') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_LogTypes 
 DROP CONSTRAINT PK_{objectQualifier}DMX_LogTypes
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes (
 [LogTypeKey],
 [ResourceFile],
 [AttachFile],
 [CustomSend],
 [NotifyApprovers],
 [NotifyAudit],
 [NotifyEditors],
 [NotifyOwner],
 [NotifySelf],
 [NotifySubscribers],
 [PortalId])
SELECT
 lt.[LogTypeKey],
 lt.[ResourceFile],
 0, 0, 
 'CORE_APPROVE', 
 1,
 NULL,
 NULL,
 NULL,
 'CORE_SUBSCRIBER',
 p.PortalID
FROM
 {databaseOwner}{objectQualifier}Portals p JOIN
 {databaseOwner}{objectQualifier}DMX_LogTypes lt ON 1=1
GO

DELETE FROM {databaseOwner}{objectQualifier}DMX_LogTypes
WHERE PortalId IS NULL
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_LogTypes') and OBJECTPROPERTY(id, N'IsTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_LogTypes
ALTER COLUMN
 [PortalId] INT NOT NULL

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_LogTypes') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_LogTypes ADD CONSTRAINT
	PK_{objectQualifier}DMX_LogTypes PRIMARY KEY CLUSTERED 
	(
	[LogTypeKey], [PortalId]
	) ON [PRIMARY]
GO

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes
SET
 [NotifyApprovers]=NULL,
 [NotifyAudit]=1,
 [NotifySubscribers]=NULL
WHERE
 (NOT ResourceFile IS NULL)
GO

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes
SET
 [NotifyApprovers]=NULL,
 [NotifySubscribers]=NULL
WHERE
 [LogTypeKey]='Download'
 OR [LogTypeKey]='Import'
 OR [LogTypeKey]='Lock'
 OR [LogTypeKey]='MailMe'
 OR [LogTypeKey]='Subscribe'
 OR [LogTypeKey]='Undelete'
 OR [LogTypeKey]='Unlock'
 OR [LogTypeKey]='Unsubscribe'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes
SET
 [NotifyApprovers]='CORE_APPROVER_APPROVE',
 [NotifyEditors]='CORE_EDITOR_APPROVE'
WHERE
 [LogTypeKey]='Approve'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes
SET
 [AttachFile]=1,
 [CustomSend]=1
WHERE
 [LogTypeKey]='MailMe'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes
SET
 [NotifySelf]='CORE_SUBSCRIBER'
WHERE
 [LogTypeKey]='Subscribe'
 OR [LogTypeKey]='Unsubscribe'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes
SET
 [NotifyOwner]='CORE_SUBSCRIBER'
WHERE
 [LogTypeKey]='Delete'
 OR [LogTypeKey]='Move'
GO



if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_Notifications') and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Notifications (
 [LogId] INT NOT NULL,
 [PortalId] INT NOT NULL,
 [Sent] DATETIME,
 [Template] NVARCHAR (100) NOT NULL,
 [UserId] INT NOT NULL
) ON [PRIMARY]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_Notifications') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Notifications ADD CONSTRAINT
	PK_{objectQualifier}DMX_Notifications PRIMARY KEY CLUSTERED 
	(
	[LogId], [UserId]
	) ON [PRIMARY]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_Notifications_{objectQualifier}DMX_') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Notifications ADD CONSTRAINT
	FK_{objectQualifier}DMX_Notifications_{objectQualifier}DMX_Log FOREIGN KEY
	(
    LogId
	) REFERENCES {databaseOwner}{objectQualifier}DMX_Log
	(
    id
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_Addons') and OBJECTPROPERTY(id, N'IsTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Addons
ADD
 [Controller] NVARCHAR (255),
 [Features] INT
GO

/****** DATA CLEANUP ******/

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls
WHERE ControlSrc='DesktopModules/Bring2mind/DMX/Admin/MenuEdit.ascx'
OR ControlSrc='DesktopModules/Bring2mind/DMX/Admin/MenuEditTexts.ascx' 
GO

/****** ENTRY PROCEDURES ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
	@Author NVarChar(500),
	@CollectionId Int,
	@Created DateTime,
	@Deleted Bit,
	@Entry NVarChar (255),
	@EntryType NVarChar (150),
	@FileSize Int,
	@IsApproved Bit,
	@Keywords NVarChar (255),
	@LastModified DateTime,
	@LastVersionId Int,
	@LockedBy Int,
	@LockedUntil DateTime,
	@LockKey UNIQUEIDENTIFIER,
	@MD5Hash NVarChar(50),
	@OriginalFileName NVarChar (255),
	@Owner Int,
	@Path NVarChar (255),
	@PermissionsOnlyByAdmin Bit,
	@PortalId Int,
	@State NVarChar (50),
	@Version Int,
	@VersionsToKeep Int,
	@WorkflowId NVarChar (50)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries (
	[Author],
	[CollectionId],
	[Created],
	[Deleted],
	[Entry],
	[EntryType],
	[FileSize],
	[IsApproved],
	[Keywords],
	[LastModified],
	[LastVersionId],
	[LockedBy],
	[LockedUntil],
	[LockKey],
	[MD5Hash],
	[OriginalFileName],
	[Owner],
	[Path],
	[PermissionsOnlyByAdmin],
	[PortalId],
	[State],
	[Version],
	[VersionsToKeep],
	[WorkflowId]
) VALUES (
	@Author,
	@CollectionId,
	@Created,
	@Deleted,
	@Entry,
	@EntryType,
	@FileSize,
	@IsApproved,
	@Keywords,
	@LastModified,
	@LastVersionId,
	@LockedBy,
	@LockedUntil,
	@LockKey,
	@MD5Hash,
	@OriginalFileName,
	@Owner,
	@Path,
	@PermissionsOnlyByAdmin,
	@PortalId,
	@State,
	@Version,
	@VersionsToKeep,
	@WorkflowId
)

select SCOPE_IDENTITY()
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
	@EntryId Int,
	@Author NVarChar(500),
	@CollectionId Int,
	@Created DateTime,
	@Deleted Bit,
	@Entry NVarChar (255),
	@EntryType NVarChar (150),
	@FileSize Int,
	@IsApproved Bit,
	@Keywords NVarChar (255),
	@LastModified DateTime,
	@LastVersionId Int,
	@LockedBy Int,
	@LockedUntil DateTime,
	@LockKey UNIQUEIDENTIFIER,
	@MD5Hash NVarChar(50),
	@OriginalFileName NVarChar (255),
	@Owner Int,
	@Path NVarChar (255),
	@PermissionsOnlyByAdmin Bit,
	@PortalId Int,
	@State NVarChar (50),
	@Version Int,
	@VersionsToKeep Int,
	@WorkflowId NVarChar (50)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
	[Author] = @Author,
	[CollectionId] = @CollectionId,
	[Created] = @Created,
	[Deleted] = @Deleted,
	[Entry] = @Entry,
	[EntryType] = @EntryType,
	[FileSize] = @FileSize,
	[IsApproved] = @IsApproved,
	[Keywords] = @Keywords,
	[LastModified] = @LastModified,
	[LastVersionId] = @LastVersionId,
	[LockedBy] = @LockedBy,
	[LockedUntil] = @LockedUntil,
	[LockKey] = @LockKey,
	[MD5Hash] = @MD5Hash,
	[OriginalFileName] = @OriginalFileName,
	[Owner] = @Owner,
	[Path] = @Path,
	[PermissionsOnlyByAdmin] = @PermissionsOnlyByAdmin,
	[PortalId] = @PortalId,
	[State] = @State,
	[Version] = @Version,
	[VersionsToKeep] = @VersionsToKeep,
	[WorkflowId] = @WorkflowId
WHERE
	[EntryId] = @EntryId
GO

/****** LOGTYPE PROCEDURES ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLogType
	@PortalId Int,	
	@LogTypeKey NVarChar (20),
	@AttachFile Bit,
	@CustomSend Bit,
	@NotifyApprovers NVarChar (100),
	@NotifyAudit BIT,
	@NotifyEditors NVarChar (100),
	@NotifyOwner NVarChar (100),
	@NotifySelf NVarChar (100),
	@NotifySubscribers NVarChar (100),
	@ResourceFile NVarChar (150)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes (
	[PortalId],
	[LogTypeKey],
	AttachFile,
	CustomSend,
	NotifyApprovers,
	NotifyAudit,
	NotifyEditors,
	NotifyOwner,
	NotifySelf,
	NotifySubscribers,
	ResourceFile
) VALUES (
	@PortalId,
	@LogTypeKey,
	@AttachFile,
	@CustomSend,
	@NotifyApprovers,
	@NotifyAudit,
	@NotifyEditors,
	@NotifyOwner,
	@NotifySelf,
	@NotifySubscribers,
	@ResourceFile
)

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLogType
	@PortalId Int,
	@LogTypeKey NVarChar (20),
	@AttachFile Bit,
	@CustomSend Bit,
	@NotifyApprovers NVarChar (100),
	@NotifyAudit BIT,
	@NotifyEditors NVarChar (100),
	@NotifyOwner NVarChar (100),
	@NotifySelf NVarChar (100),
	@NotifySubscribers NVarChar (100),
	@ResourceFile NVarChar (150)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes SET
	[AttachFile] = @AttachFile,
	[CustomSend] = @CustomSend,
	[NotifyApprovers] = @NotifyApprovers,
	[NotifyAudit] = @NotifyAudit,
	[NotifyEditors] = @NotifyEditors,
	[NotifyOwner] = @NotifyOwner,
	[NotifySelf] = @NotifySelf,
	[NotifySubscribers] = @NotifySubscribers,
	[ResourceFile] = @ResourceFile
WHERE
	[PortalId] = @PortalId
	AND [LogTypeKey] = @LogTypeKey
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLogType
	@PortalId Int,
	@LogTypeKey NVarChar (20)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_LogTypes
WHERE
	[PortalId] = @PortalId
	AND [LogTypeKey] = @LogTypeKey
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogType
	@PortalId Int,
	@LogTypeKey NVarChar (20)
AS

SELECT
 lt.*
FROM
 {databaseOwner}{objectQualifier}DMX_LogTypes lt
WHERE
 [PortalId] = @PortalId
 AND [LogTypeKey] = @LogTypeKey
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogTypes
 @PortalId Int
AS

SELECT
 lt.*
FROM
 {databaseOwner}{objectQualifier}DMX_LogTypes lt
WHERE
 [PortalId] = @PortalId
GO


/****** NOTIFICATION PROCEDURES ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddNotification
	@UserId Int,	
	@LogId Int,
	@PortalId Int,
	@Sent DateTime,
	@Template NVarChar (100)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Notifications (
	[UserId],
	[LogId],
	PortalId,
	Sent,
	Template
) VALUES (
	@UserId,
	@LogId,
	@PortalId,
	@Sent,
	@Template
)

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateNotification
	@UserId Int,
	@LogId Int,
	@PortalId Int,
	@Sent DateTime,
	@Template NVarChar (100)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Notifications SET
	[PortalId] = @PortalId,
	[Sent] = @Sent,
	[Template] = @Template
WHERE
	[UserId] = @UserId
	AND [LogId] = @LogId
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteNotification
	@UserId Int,
	@LogId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Notifications
WHERE
	[UserId] = @UserId
	AND [LogId] = @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
 @UserId Int,
 @LogId Int,
 @PortalId int
AS

SELECT
	[LogId],
	[PortalId],
	[Sent],
	[Template],
	[UserId]
FROM
	{databaseOwner}{objectQualifier}DMX_Notifications
WHERE
	[UserId] = @UserId
	AND [LogId] = @LogId
	AND [PortalId] = @PortalId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{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
 n.*
FROM
 {databaseOwner}{objectQualifier}DMX_Notifications n
WHERE
 n.[UserId] = @UserId
 AND n.[PortalId] = @PortalId
 AND n.[Sent] IS NULL

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify
 @PortalId int
AS

SELECT 
 u.*
FROM
 {databaseOwner}{objectQualifier}Users u
WHERE
 EXISTS (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Notifications n WHERE n.[UserId] = u.UserId AND n.[PortalId] = @PortalId AND n.[Sent] IS NULL)

GO


/****** EXTENDED LOG PROCEDURES ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLog
 @id Int
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 l.[id] = @id
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
	@EntId Int
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 l.EntId = @EntId

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByPortal
	@PortalId Int
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 e.PortalId = @PortalId

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByUser
	@UserId Int
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 l.UserId = @UserId

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogByTime
 @PortalId Int,
 @StartTime DATETIME,
 @EndTime DATETIME
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 e.PortalId = @PortalId
 AND l.Datime >= @StartTime
 AND l.Datime < @EndTime

GO

/****** NEW SCHEDULER TASK ******/

INSERT INTO {databaseOwner}{objectQualifier}Schedule 
(TypeFullName, TimeLapse, TimeLapseMeasurement, RetryTimeLapse, RetryTimeLapseMeasurement, RetainHistoryNum, CatchupEnabled, Enabled, AttachToEvent, ObjectDependencies)
VALUES
('Bring2mind.DNN.Modules.DMX.Services.Schedule.ScheduledTask, BRING2MIND.DNN.MODULES.DMX.CORE', 5, 'm', 2, 'm', 10, 0, 1, '', '')
GO

/****** NEW PATH AND PERMISSION METHODS ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePaths
 @PortalId Int,
 @EntryId Int,
 @Path VARCHAR(2000)
AS
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET Path=@Path WHERE LastVersionId=@EntryId
 
 DECLARE children CURSOR LOCAL FOR
  SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries
   WHERE CollectionId=@EntryId AND LastVersionId=EntryId
 OPEN children
  FETCH NEXT FROM children INTO @EntryID
 WHILE @@FETCH_STATUS=0 BEGIN
  DECLARE @CPath VARCHAR(2000) 
  SET @CPath = @Path+CAST(@EntryId AS NVARCHAR(10))+';';
  EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId, @CPath
  FETCH NEXT FROM children INTO @EntryId
 END
 CLOSE children
 DEALLOCATE children
GO

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

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER INSERT, UPDATE
AS
 BEGIN
  DECLARE @EntryId INT
  SELECT @EntryId = (SELECT EntryId FROM INSERTED)
  DECLARE @PortalId INT
  SELECT @PortalId = (SELECT PortalId FROM INSERTED)
  DECLARE @CollectionId INT
  SELECT @CollectionId = (SELECT CollectionId FROM INSERTED)
  DECLARE @Path VARCHAR(2000)
  IF @CollectionId=0
   SET @Path = '0;'+CAST(@EntryId AS VARCHAR(10))+';';
  ELSE
   SELECT @Path = (SELECT Path FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@CollectionId)+CAST(@EntryId AS VARCHAR(10))+';';
  DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
  EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId, @Path;
  EXEC {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId, @EntryId;
  ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 END
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetParentChain
 @PortalId Int,
 @EntryId Int,
 @Locale NVarChar(10)
AS
 DECLARE @CollectionId INT
 SELECT @CollectionId = (SELECT CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
 IF @CollectionId>0
  EXEC {databaseOwner}{objectQualifier}DMX_GetParentChain @PortalId, @CollectionId, @Locale
 SELECT e.*,
  ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE [EntryId] = @EntryId
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_IsEntryPermissionByAdmin
 @PortalId Int,
 @EntryId Int
AS
 IF EXISTS(SELECT PermissionsOnlyByAdmin FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PermissionsOnlyByAdmin=1)
  SELECT 1
 ELSE
  DECLARE @Path VARCHAR(2000)
  SELECT @Path = ';'+(SELECT Path FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)+';'
  IF EXISTS(SELECT PermissionsOnlyByAdmin FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE PermissionsOnlyByAdmin=1 AND @Path LIKE '%;'+CAST(EntryId AS VARCHAR(10))+';%')
   SELECT 1
  ELSE
   SELECT 0
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastModified
 @PortalId Int,
 @EntryId Int
AS
 DECLARE @CollectionId INT
 SELECT @CollectionId = (SELECT CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
 IF @CollectionId>0
  EXEC {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId, @CollectionId
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET LastModified=GETDATE()
 WHERE EntryId=@EntryId
 AND PortalId=@PortalId
GO

/****** ADDON SPROCS ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAddon
	@AddonKey NVarChar (50),
	@Availability NVarChar (255),
	@Controller NVarChar (255),
	@Description NVarChar (255),
	@Features Int,
	@Installed DateTime,
	@Name NVarChar (50)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Addons (
	[AddonKey],
	[Availability],
	[Controller],
	[Description],
	[Features],
	[Installed],
	[Name]
) VALUES (
	@AddonKey,
	@Availability,
	@Controller,
	@Description,
	@Features,
	@Installed,
	@Name
)

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAddon
	@AddonKey NVarChar (50),
	@Availability NVarChar (255),
	@Controller NVarChar (255),
	@Description NVarChar (255),
	@Features Int,
	@Installed DateTime,
	@Name NVarChar (50)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Addons SET
	[Availability] = @Availability,
	[Controller] = @Controller,
	[Description] = @Description,
	[Features] = @Features,
	[Installed] = @Installed,
	[Name] = @Name
WHERE
	[AddonKey] = @AddonKey
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddon
	@AddonKey NVarChar (50)
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_Addons
WHERE
	[AddonKey] = @AddonKey
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAddonByName
	@Name NVarChar (50)
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_Addons
WHERE
	[Name] = @Name
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddonsWithFeature
	@Feature INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 ISNULL([Features], 0) & @Feature = @Feature
GO

/****** NEW ENTRY SPs ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RenameEntry
 @PortalId Int,
 @EntryId Int,
 @Locale NVarChar(10),
 @NewName nvarchar(255)
AS
 IF EXISTS(SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
  BEGIN
   DELETE FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE 
    ObjectId=@EntryId
    AND TypeKey='ENT'
    AND SubTypeKey='TITLE'
    AND Locale=@Locale
   INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts 
    (ObjectId, TypeKey, SubTypeKey, Locale, Text)
    VALUES
    (@EntryId, 'ENT', 'TITLE', @Locale, @NewName)
  END
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry
 @PortalId Int,
 @EntryId Int,
 @NewCollectionId INT
AS
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET CollectionId=@NewCollectionId
 WHERE
  LastVersionId=@EntryId
  AND PortalId=@PortalId
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
 @PortalId Int,
 @SearchText nvarchar(255),
 @Locale nvarchar(10)
AS
SELECT
 e.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')) Remarks
FROM
 dbo.DMX_Entries e
WHERE
 PortalId=@PortalId AND
 (OriginalFilename LIKE '%'+@SearchText+'%' OR
 Keywords LIKE '%'+@SearchText+'%' OR
 EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale AND Text LIKE '%'+@SearchText+'%'))
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetDeleteRecursive
 @PortalId Int,
 @EntryId Int,
 @Delete BIT
AS
 DECLARE @Path VARCHAR(2000)
 SET @Path = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)+'%';
 DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET Deleted=@Delete
 WHERE
  PortalId=@PortalId
  AND (LastVersionId=@EntryId
  OR [Path] LIKE @Path);
 ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
GO

/****** NEW Unify SPROC ******/


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetPermissions
 @PortalId Int,
 @EntryId Int
AS
DECLARE @AdminRoleId INT
SELECT @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId);
DECLARE @BasePath VARCHAR(2000)
SELECT @BasePath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId);
DELETE {databaseOwner}{objectQualifier}DMX_EntryPermissions
 FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=ep.EntryId
 WHERE e.PortalId=@PortalId AND e.[Path] LIKE @BasePath+'%';
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT 1, e.EntryId, NULL, p.PermissionId, 0, @AdminRoleId, -10
 FROM {databaseOwner}{objectQualifier}DMX_Entries e, {databaseOwner}{objectQualifier}DMX_Permissions p
 WHERE 
  e.PortalId=@PortalId AND e.[Path] LIKE @BasePath+'%' AND (NOT e.EntryType LIKE 'Collection%')
  AND (p.PermissionKey = 'EDIT' OR p.PermissionKey = 'VIEW');
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT 1, e.EntryId, NULL, p.PermissionId, 0, @AdminRoleId, -10
 FROM {databaseOwner}{objectQualifier}DMX_Entries e, {databaseOwner}{objectQualifier}DMX_Permissions p
 WHERE 
  e.PortalId=@PortalId AND e.[Path] LIKE @BasePath+'%' AND e.EntryType LIKE 'Collection%'
  AND (p.PermissionKey = 'EDIT' OR p.PermissionKey = 'VIEW'  OR p.PermissionKey = 'ADD');
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifyPermissions
 @PortalId Int,
 @EntryId Int,
 @UserId INT
AS
DECLARE @BasePath VARCHAR(2000)
SELECT @BasePath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#ToChange%')
 DROP TABLE #ToChange;
CREATE TABLE #ToChange 
 (EntryId INT, [Path] VARCHAR(2000), EntryType nvarchar(150));
INSERT INTO #ToChange
 (EntryId, [Path], EntryType)
SELECT e.EntryId, e.[Path], e.EntryType FROM {databaseOwner}{objectQualifier}DMX_Entries e INNER JOIN
 (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
   LEFT JOIN {databaseOwner}{objectQualifier}UserRoles r ON r.RoleId=ep.RoleId 
   WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UserId OR r.UserId=@UserId OR ep.RoleId=-1)) perm ON perm.EntryId=e.EntryId
 WHERE PortalId=@PortalId AND [Path] LIKE @BasePath+'_%';
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#NewPerms%')
 DROP TABLE #NewPerms;
CREATE TABLE #NewPerms (
	[AllowAccess] [bit] NULL,
	[EntryId] [int] NOT NULL,
	[Expires] [datetime] NULL,
	[PermissionId] [int] NOT NULL,
	[PortalId] [int] NOT NULL,
	[RoleId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
    [EntryType] nvarchar(150))
INSERT INTO #NewPerms
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId, EntryType)
SELECT ep.AllowAccess, ep.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId, pet.EntryType
 FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntryTypes pet ON pet.PermissionId=ep.PermissionId
 WHERE ep.EntryId=@EntryId
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
 WHERE EntryId IN (SELECT EntryId FROM #ToChange);
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT ep.AllowAccess, tc.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId
  FROM #NewPerms ep
  INNER JOIN #ToChange tc ON tc.EntryType LIKE ep.EntryType+'%';
DROP TABLE #NewPerms;
DROP TABLE #ToChange;
GO

/****** SECURITY REPAIRS ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles
AS
SELECT ur.*
FROM {databaseOwner}{objectQualifier}UserRoles ur
WHERE 
 ISNULL(ur.EffectiveDate, DATEADD(day, -1, GETDATE())) < GETDATE()
 AND ISNULL(ur.ExpiryDate, DATEADD(day, 1, GETDATE())) > GETDATE()
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserPermissions
	@EntryId Int,
	@PortalId Int,
	@UserId Int
AS

SELECT DISTINCT p.PermissionKey
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId=p.PermissionId
LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ep.RoleId=ur.RoleID
LEFT JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=@UserId
WHERE ep.EntryId=@EntryId
AND ep.PortalId=@PortalId
AND p.PortalId=@PortalId
AND (ep.RoleID=-1 OR ur.UserId=@UserId OR ep.UserId=@UserId OR u.IsSuperUser=1)

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifyPermissions
 @PortalId Int,
 @EntryId Int,
 @UserId INT
AS
DECLARE @BasePath VARCHAR(2000)
SELECT @BasePath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#ToChange%')
 DROP TABLE #ToChange;
CREATE TABLE #ToChange 
 (EntryId INT, [Path] VARCHAR(2000), EntryType nvarchar(150));
INSERT INTO #ToChange
 (EntryId, [Path], EntryType)
SELECT e.EntryId, e.[Path], e.EntryType FROM {databaseOwner}{objectQualifier}DMX_Entries e INNER JOIN
 (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId 
   WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UserId OR r.UserId=@UserId OR ep.RoleId=-1)) perm ON perm.EntryId=e.EntryId
 WHERE PortalId=@PortalId AND [Path] LIKE @BasePath+'_%';
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#NewPerms%')
 DROP TABLE #NewPerms;
CREATE TABLE #NewPerms (
	[AllowAccess] [bit] NULL,
	[EntryId] [int] NOT NULL,
	[Expires] [datetime] NULL,
	[PermissionId] [int] NOT NULL,
	[PortalId] [int] NOT NULL,
	[RoleId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
    [EntryType] nvarchar(150))
INSERT INTO #NewPerms
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId, EntryType)
SELECT ep.AllowAccess, ep.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId, pet.EntryType
 FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntryTypes pet ON pet.PermissionId=ep.PermissionId
 WHERE ep.EntryId=@EntryId
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
 WHERE EntryId IN (SELECT EntryId FROM #ToChange);
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT ep.AllowAccess, tc.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId
  FROM #NewPerms ep
  INNER JOIN #ToChange tc ON tc.EntryType LIKE ep.EntryType+'%';
DROP TABLE #NewPerms;
DROP TABLE #ToChange;
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveDeadPermissions
AS
DELETE FROM
 {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE
 (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Roles r WHERE r.RoleId={databaseOwner}{objectQualifier}DMX_EntryPermissions.RoleId) AND RoleId>-1 AND UserId=-10)
OR
 (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Users s WHERE s.UserId={databaseOwner}{objectQualifier}DMX_EntryPermissions.UserId) AND UserId>-1 AND RoleId=-1)
GO

