IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ResetFolderSizes') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetFolderSizes
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetFolderSizes
 @PortalId Int,
 @EntryId Int = -1
AS
BEGIN
 IF @EntryId > 0
 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
 IF @EntryId = -1
  IF @PortalId = -1
   UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET FileSize=NULL
   WHERE EntryType LIKE 'Collection%';
  ELSE
   UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET FileSize=NULL
   WHERE PortalId=@PortalId
   AND EntryType LIKE 'Collection%';
DECLARE @Sizes TABLE (
 EntryId INT NOT NULL,
 FileSize BIGINT NOT NULL
);
WITH Sizes AS
(
    SELECT p.EntryId, p.CollectionId, p.FileSize
    FROM {databaseOwner}{objectQualifier}DMX_Entries p WHERE (p.PortalId=@PortalId OR @PortalId=-1)
  AND p.EntryId=p.LastVersionId
    UNION ALL
    SELECT c.EntryId, c.CollectionId, cte.FileSize
    FROM Sizes cte join
         {databaseOwner}{objectQualifier}DMX_Entries c
         ON c.EntryId = cte.CollectionId AND c.EntryId=c.LastVersionId
)
INSERT INTO @Sizes (EntryId, FileSize)
SELECT s.EntryId, ISNULL(SUM(s.FileSize), 0)
FROM Sizes s
GROUP BY s.EntryId, s.CollectionId;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET FileSize = s.FileSize
FROM {databaseOwner}{objectQualifier}DMX_Entries e
INNER JOIN @Sizes s ON s.EntryId=e.EntryId
WHERE e.EntryType LIKE 'Collection%'
 AND e.FileSize IS NULL
END
GO

