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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
 @EntryList VARCHAR(300),
 @UserId INT
AS

SELECT DISTINCT Entry.Author, Entry.CollectionId, Entry.Created, Entry.Deleted, Entry.Entry, 
Entry.EntryId, Entry.EntryType, Entry.FileSize, Entry.IsApproved, Entry.Keywords, 
Entry.LastVersionId, Entry.LockedBy, Entry.OriginalFileName, Entry.PortalId, Entry.Owner, 
Entry.Version, Entry.LastModified, Entry.LockedUntil, Entry.LockKey, Entry.MD5Hash, Entry.State, 
Entry.VersionsToKeep, Entry.WorkflowId, 
REPLACE({databaseOwner}{objectQualifier}DMXDAV_URLEncode({databaseOwner}{objectQualifier}DMXDAV_GetPath(Entry.EntryId, Entry.PortalId, '')), '''', '\''') AS FullPath, 
CAST (CASE WHEN Entry.LockedUntil > GETDATE() THEN 1 ELSE 0 END AS BIT) AS IsLocked,
CAST (CASE WHEN subs.EntryID IS NULL THEN 0 ELSE 1 END AS BIT) AS Subscription,
Permission.PermissionKey 
FROM (SELECT DMXEntry.*, 
ext.ControlToLoad, ext.Custom, ext.DownloadUrl, ext.EntryTypes, ext.Icon16, 
ext.Icon32, ext.IsPrivate, ext.MimeType, ext.ResourceFile, ext.SettingsControl, 
ext.ViewByDefault
FROM (SELECT s FROM {databaseOwner}{objectQualifier}DMX_Split(@EntryList,';')) recs
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries DMXEntry ON recs.s=DMXEntry.EntryId
INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON ext.ExtensionKey=DMXEntry.EntryType AND DMXEntry.PortalId=ext.PortalId) AS Entry 
INNER JOIN (SELECT ep1.EntryId, Permission.PermissionKey
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep1
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions Permission ON Permission.PermissionId=ep1.PermissionId
LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep1.RoleId AND r.UserId=@UserId WHERE (ep1.UserId=@UserId OR r.UserId=@UserId OR ep1.RoleId=-1)) Permission
ON Permission.EntryId=Entry.EntryId 
LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE UserID=@UserId) subs ON subs.EntryId=Entry.EntryId
FOR XML AUTO

GO

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

IF TRIGGER_NESTLEVEL() < 2
BEGIN
 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);
 -- reset folder sizes on old paths
 UPDATE e SET [FileSize]=NULL
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.[Path] LIKE e.[Path]+'%'
  WHERE e.EntryType LIKE 'Collection%';
 -- reset new paths
 UPDATE e SET [Path]=NULL
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON e.[Path] LIKE i.[Path]+'%';
 EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, -1;
 -- set last modif for new path parents
 UPDATE e SET [LastModified]=GETDATE()
 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.EntryId = e1.EntryId;
 -- reset folder sizes on new paths
 UPDATE e SET [FileSize]=
 (SELECT ISNULL(SUM(e1.FileSize), 0) FROM {databaseOwner}{objectQualifier}DMX_Entries e1 
  WHERE e1.EntryType LIKE 'File%'
  AND e1.[Path] LIKE e.Path+'%'
  AND e1.PortalId=e.PortalId
  AND e1.LastVersionId=e1.EntryId)
 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.EntryId = e1.EntryId
  WHERE e.EntryType LIKE 'Collection%';
 -- update last mod, lastversionid, folder size
 EXEC {databaseOwner}{objectQualifier}DMX_SetLastVersionID @PortalId;
 EXEC {databaseOwner}{objectQualifier}DMX_ResetFolderSizes @PortalId;
END

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetFolderSizes
 @PortalId Int,
 @EntryId Int = -1
AS

BEGIN
 IF @EntryId > -1
 BEGIN
  DECLARE @entrypath VARCHAR(4000)
  SET @entrypath = (SELECT [Path] 
    FROM {databaseOwner}{objectQualifier}DMX_Entries
    WHERE EntryId = @EntryId);
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET FileSize=NULL
  WHERE PortalId=@PortalId
  AND @entrypath LIKE [Path]+'%'
  AND EntryType LIKE 'Collection%';
 END
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET FileSize=
  (SELECT ISNULL(SUM(e1.FileSize), 0) FROM {databaseOwner}{objectQualifier}DMX_Entries e1 
  WHERE e1.EntryType LIKE 'File%'
  AND e1.[Path] LIKE e.Path+'%'
  AND e1.PortalId=e.PortalId
  AND e1.LastVersionId=e1.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 WHERE e.EntryType LIKE 'Collection%'
  AND e.FileSize=NULL
  AND (e.PortalId=@PortalId OR @PortalId=-1);
END

GO


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

BEGIN

-- NULLIFY what is 'dirty' 
IF @EntryId is NULL 
 BEGIN
  DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET [Path]=NULL WHERE PortalID = @PortalID;
  ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 END 
ELSE 
 BEGIN
  DECLARE @OldPath VARCHAR(4000)
  SELECT @OldPath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries 
   SET [Path]=NULL 
  WHERE [Path] LIKE @OldPath + '%' 
   AND PortalID = @PortalID 
 END

-- If we have any root items with NULL we set the [Path] for these 
UPDATE {databaseOwner}{objectQualifier}DMX_Entries 
 SET [Path] = '0;' + Cast(EntryId as NVARCHAR(32)) + ';' 
WHERE
 CollectionId=0
 And PortalId=@PortalId
 AND [Path] IS NULL

-- As long as we find items that are nullified but whose parents are not NULL, we set the path for them
WHILE EXISTS 
 (SELECT 'X' 
  FROM {databaseOwner}{objectQualifier}DMX_Entries 
  WHERE 
   PortalId=@PortalId
   AND [Path] IS NULL
   AND EXISTS (SELECT 'X' 
               FROM {databaseOwner}{objectQualifier}DMX_Entries e1 
               WHERE e1.[Path] IS NOT NULL 
                AND e1.EntryID={databaseOwner}{objectQualifier}DMX_Entries.CollectionID))
 BEGIN
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET [Path] = (SELECT e2.[Path] + CAST({databaseOwner}{objectQualifier}DMX_Entries.EntryId AS NVARCHAR(32)) + ';' 
                 FROM {databaseOwner}{objectQualifier}DMX_Entries e2 
                 WHERE e2.EntryID={databaseOwner}{objectQualifier}DMX_Entries.CollectionID)
  WHERE 
   {databaseOwner}{objectQualifier}DMX_Entries.PortalId=@PortalId
   AND [Path] IS NULL
   AND EXISTS (SELECT 'X' 
               FROM {databaseOwner}{objectQualifier}DMX_Entries e3 
               WHERE e3.[Path] IS NOT NULL 
                AND e3.EntryID={databaseOwner}{objectQualifier}DMX_Entries.CollectionID)
 END;

END
GO

