IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{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

EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;

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'{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

EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;

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

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByEntry
 @PortalId INT,
 @Entry AS NVARCHAR(2000)
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Entries
WHERE
 PortalId=@PortalId
AND
 Entry=@Entry
GO

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

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER UPDATE
AS

IF TRIGGER_NESTLEVEL() < 2
 AND (UPDATE(Author) OR UPDATE(CollectionId) OR UPDATE([Entry]) OR UPDATE(EntryType) 
  OR UPDATE(IsApproved) OR UPDATE(Keywords) OR UPDATE(MD5Hash) OR UPDATE(OriginalFilename)
  OR UPDATE([State]) OR UPDATE(PermissionsOnlyByAdmin) OR UPDATE(InheritAttributes))
BEGIN

 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);
 DECLARE @Now DATETIME
 SET @Now = GETDATE()

-- move: select old parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] - i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN DELETED d ON d.CollectionId=e1.EntryId
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId
 WHERE e.EntryType LIKE 'Collection%';

-- move: select new parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] + i.FileSize,
  e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId
 WHERE e.EntryType LIKE 'Collection%';

-- move: select self and children
 UPDATE e 
 SET [Path]=NULL
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN DELETED d ON e.[Path] LIKE d.[Path]+'%'
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId;
 EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, -1;

-- nomove/nonewversion: select parents
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize=d.FileSize
 WHERE e.EntryType LIKE 'Collection%';

-- nomove/newversion: select parents
 UPDATE e
 SET e.LastModified = @Now,
  e.FileSize = e.FileSize - d.FileSize + i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize<>d.FileSize;

-- move: select self
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND d.LastModified=i.LastModified;
 
END
GO

-- repair mime-types for Office 2007+ files
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions
ALTER COLUMN [MimeType] VARCHAR(200)
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
 @PortalId INT,	
 @ExtensionKey NVARCHAR (50),
 @AccessRights NVARCHAR (255),
 @Addon NVARCHAR (50),
 @ControlToLoad NVARCHAR (255),
 @Custom NVARCHAR (50),
 @DownloadUrl NVARCHAR (255),
 @EditControl NVARCHAR (255),
 @EntryTypes NVARCHAR (255),
 @Icon16 NVARCHAR (100),
 @Icon32 NVARCHAR (100),
 @IsPrivate BIT,
 @MimeType VARCHAR(200),
 @NodePattern NVARCHAR (50),
 @ResourceFile NVARCHAR (255),
 @SettingsControl NVARCHAR (250),
 @ViewByDefault BIT,
 @ViewControl NVARCHAR (255)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions (
 [PortalId],
 [ExtensionKey],
 AccessRights,
 Addon,
 ControlToLoad,
 Custom,
 DownloadUrl,
 EditControl,
 EntryTypes,
 Icon16,
 Icon32,
 IsPrivate,
 MimeType,
 NodePattern,
 ResourceFile,
 SettingsControl,
 ViewByDefault,
 ViewControl
) VALUES (
 @PortalId,
 @ExtensionKey,
 @AccessRights,
 @Addon,
 @ControlToLoad,
 @Custom,
 @DownloadUrl,
 @EditControl,
 @EntryTypes,
 @Icon16,
 @Icon32,
 @IsPrivate,
 @MimeType,
 @NodePattern,
 @ResourceFile,
 @SettingsControl,
 @ViewByDefault,
 @ViewControl
)

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
	@PortalId INT,
	@ExtensionKey NVARCHAR (50),
	@AccessRights NVARCHAR (255),
	@Addon NVARCHAR (50),
	@ControlToLoad NVARCHAR (255),
	@Custom NVARCHAR (50),
	@DownloadUrl NVARCHAR (255),
	@EditControl NVARCHAR (255),
	@EntryTypes NVARCHAR (255),
	@Icon16 NVARCHAR (100),
	@Icon32 NVARCHAR (100),
	@IsPrivate BIT,
	@MimeType VARCHAR(200),
	@NodePattern NVARCHAR (50),
	@ResourceFile NVARCHAR (255),
	@SettingsControl NVARCHAR (250),
	@ViewByDefault BIT,
	@ViewControl NVARCHAR (255)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET
	[AccessRights] = @AccessRights,
	[Addon] = @Addon,
	[ControlToLoad] = @ControlToLoad,
	[Custom] = @Custom,
	[DownloadUrl] = @DownloadUrl,
	[EditControl] = @EditControl,
	[EntryTypes] = @EntryTypes,
	[Icon16] = @Icon16,
	[Icon32] = @Icon32,
	[IsPrivate] = @IsPrivate,
	[MimeType] = @MimeType,
	[NodePattern] = @NodePattern,
	[ResourceFile] = @ResourceFile,
	[SettingsControl] = @SettingsControl,
	[ViewByDefault] = @ViewByDefault,
	[ViewControl] = @ViewControl
WHERE
	[PortalId] = @PortalId
	AND [ExtensionKey] = @ExtensionKey
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET MimeType = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' 
 WHERE Custom = 'docx';
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET MimeType = 'application/vnd.openxmlformats-officedocument.wordprocessingml.template' 
 WHERE Custom = 'dotx';
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET MimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
 WHERE Custom = 'xlsx';
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET MimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.template' 
 WHERE Custom = 'xltx';
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET MimeType = 'application/vnd.openxmlformats-officedocument.presentationml.presentation' 
 WHERE Custom = 'pptx';
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET MimeType = 'application/vnd.openxmlformats-officedocument.presentationml.template' 
 WHERE Custom = 'potx';
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET MimeType = 'application/vnd.openxmlformats-officedocument.presentationml.slideshow' 
 WHERE Custom = 'ppsx';
GO

DROP FUNCTION {databaseOwner}{objectQualifier}DMXDAV_URLEncode
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}DMXDAV_URLEncode(@String NVARCHAR(4000))
RETURNS VARCHAR(8000)
AS
BEGIN
 DECLARE @URLEncodedString VARCHAR(8000)
 SELECT @URLEncodedString=''
 SELECT @URLEncodedString=@URLEncodedString + 
  CASE WHEN theChar LIKE '[A-Za-z0-9()''*-._!/]'
   THEN theChar
   ELSE '%' + substring ('0123456789ABCDEF',(ASCII(theChar) / 16)+1,1) +  SUBSTRING('0123456789ABCDEF',(ASCII(theChar) % 16)+1,1)
  END
  FROM (SELECT [theChar]=SUBSTRING(@string,number,1)
   FROM {databaseOwner}{objectQualifier}DMXDAV_Numbers WHERE Number <= LEN(@String)) CHARACTERARRAY
 RETURN @URLEncodedString
END

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ReparseDMXFileTypes
	@PortalId INT,
	@ExtensionKey NVARCHAR (50),
	@Extension VARCHAR (10)
AS

DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET EntryType=@ExtensionKey
 WHERE OriginalFilename LIKE '%.'+@Extension
  AND PortalId=@PortalId;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate ON {databaseOwner}{objectQualifier}DMX_Entries;
GO

