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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddHostSetting
	@SettingName nvarchar(50),
	@SettingValue nvarchar(256)
AS

INSERT INTO {databaseOwner}{objectQualifier}HostSettings (
 SettingName,
 SettingValue,
 SettingIsSecure
) 
VALUES (
 @SettingName,
 @SettingValue,
 0
)
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetHostSetting
	@SettingName nvarchar(50)
AS

SELECT *
FROM {databaseOwner}{objectQualifier}HostSettings
WHERE SettingName = @SettingName
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateHostSetting
	@SettingName nvarchar(50),
	@SettingValue nvarchar(256)
AS

UPDATE {databaseOwner}{objectQualifier}HostSettings
SET SettingValue = @SettingValue
WHERE SettingName = @SettingName
GO

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
 LEFT JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 l.[id] = @id
GO

-- clean up Remarks that were filled with <p>&#160;</p> by the HTML editor
DELETE FROM {databaseOwner}{objectQualifier}DMX_LongTexts
WHERE [Text]='<p>&#160;</p>'
GO

-- new set last modified SPROC
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,
 @LastModified DATETIME = NULL
AS

BEGIN
 IF @LastModified IS NULL
  SET @LastModified = GETDATE();
 DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET LastModified=@LastModified
 WHERE PortalId=@PortalId
 AND EntryId=@EntryId;
 ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
END

GO

-- repair missing LastModified where they are NULL
BEGIN
 DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET LastModified = (SELECT MAX(l.Datime) FROM {databaseOwner}{objectQualifier}DMX_Log l WHERE l.EntId=EntryId)
 WHERE LastModified IS NULL;
 ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
END
GO

-- fix to move entry

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

 DECLARE @destpath VARCHAR(4000)
 SET @destpath = (SELECT [Path] 
   FROM {databaseOwner}{objectQualifier}DMX_Entries
   WHERE EntryId = @NewCollectionId)
 IF NOT @destpath LIKE '%;'+CAST(@EntryId AS VARCHAR(10))+';%' AND @EntryId <> @NewCollectionId
 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_EntryIsIn') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryIsIn
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryIsIn
 @PortalId Int,
 @EntryId Int,
 @CollectionId INT
AS

DECLARE @entrypath VARCHAR(4000)
SET @entrypath = (SELECT [Path] 
 FROM {databaseOwner}{objectQualifier}DMX_Entries
 WHERE EntryId = @EntryId)
IF @entrypath LIKE '%;'+CAST(@CollectionId AS VARCHAR(10))+';%' OR @EntryId = @CollectionId
 SELECT 1
ELSE
 SELECT 0

GO


-- fix to hard delete

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
	@PortalId INT,
	@EntryId INT,
	@AllVersions BIT,
 @IncludeSelf BIT,
	@Locale NVARCHAR(6)
AS

DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;

SELECT e.*, ISNULL(stLoc.[Text], stDef.[Text]) Title, ISNULL(ltLoc.[Text], ltDef.[Text]) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stDef ON e.EntryId=stDef.ObjectId AND stDef.TypeKey='ENT' AND stDef.SubTypeKey='TITLE' AND stDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stLoc ON e.EntryId=stLoc.ObjectId AND stLoc.TypeKey='ENT' AND stLoc.SubTypeKey='TITLE' AND stLoc.Locale=@Locale
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltDef ON e.EntryId=ltDef.ObjectId AND ltDef.TypeKey='ENT' AND ltDef.SubTypeKey='REMARKS' AND ltDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltLoc ON e.EntryId=ltLoc.ObjectId AND ltLoc.TypeKey='ENT' AND ltLoc.SubTypeKey='REMARKS' AND ltLoc.Locale=@Locale
WHERE ((e.[Path] LIKE @Path+'%') OR (e.LastVersionId=@EntryId AND @AllVersions=1 AND @IncludeSelf=1))
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId) OR @AllVersions=1)
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
	@PortalId INT,
	@EntryId INT,
 @IncludeSelf BIT
AS

DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
 FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ea.EntryId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON a.CollectionId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_ShortTexts
FROM {databaseOwner}{objectQualifier}DMX_ShortTexts st
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=st.[ObjectId]
WHERE	st.[TypeKey] = 'ENT'	
 AND ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_LongTexts
FROM {databaseOwner}{objectQualifier}DMX_LongTexts lt
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=lt.[ObjectId]
WHERE	lt.[TypeKey] = 'ENT'	
 AND ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
 WHERE (([Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
GO

-- new cleanup code for notifications

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=s.EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}UserPortals up ON up.UserId=s.UserId AND up.PortalId=e.PortalId
WHERE up.UserPortalId IS NULL
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserNotifications
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Notifications
FROM {databaseOwner}{objectQualifier}DMX_Notifications n
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Log l ON l.Id=n.LogId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=l.EntId
 LEFT JOIN {databaseOwner}{objectQualifier}UserPortals up ON up.UserId=n.UserId AND up.PortalId=e.PortalId
WHERE n.Sent IS NULL AND
 up.UserPortalId IS NULL
GO

-- soft delete repair

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SoftDeleteEntry
 @PortalId Int,
 @EntryId Int,
 @Delete Bit
AS

BEGIN
 DECLARE @LastVersionId INT
 SELECT @LastVersionId=(SELECT LastVersionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
 DECLARE @Path VARCHAR(4000)
 SELECT @Path = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@LastVersionId AND PortalId=@PortalId)
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET Deleted=@Delete
  WHERE LastVersionId=@LastVersionId AND PortalId=@PortalId;
 IF @Delete=1
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET Deleted=1
   WHERE Path LIKE @Path + '%' AND PortalId=@PortalId;
END
GO

