fnIsFolderEmpty

CREATE FUNCTION [dbo].[fnIsFolderEmpty]
(
    @FolderID int,
    @FolderType int
)
RETURNS bit
AS
BEGIN
    DECLARE @empty bit
    SET @empty = 1

    -- Folder Type 0 (come from SiteFolderTypes table): Web Site
    IF @FolderType = 0   
        begin
            IF EXISTS (SELECT TOP 1 ID
                 FROM dbo.SitePages
                WHERE SitePages.FolderID = @FolderID
                  AND Deleted = 0)
            SET @empty = 0
        end

    -- Folder Type 1: EmailProjects
    IF @FolderType = 1
        begin
            IF EXISTS(SELECT TOP 1 ID
                    FROM dbo.EmailProject
                    WHERE EmailProject.FolderID = @FolderID
                    AND Deleted = 0)
              SET @empty = 0
        end
    -- Folder Type 2: Images
    IF @FolderType = 2            
        begin
            IF EXISTS (SELECT TOP 1 SiteImageFolders.ID
                 FROM dbo.SiteImageFolders 
                 inner join dbo.SiteImages on SiteImages.ID = SiteImageFolders.SiteImageId
                WHERE SiteImageFolders.SiteFolderID = @FolderID and SiteImages.deleted = 0)
            SET @empty = 0
        end    

    -- Folder Type 3: eCard Templates
    IF @FolderType = 3        
        begin
            IF EXISTS (SELECT TOP 1 ID
                 FROM dbo.ECardTemplateFolder 
                WHERE ECardTemplateFolder.SiteFoldersID = @FolderID)
            SET @empty = 0
        end    

    -- Folder Type 4: Custom Forms
        begin
            IF EXISTS (SELECT TOP 1 ID
                 FROM dbo.CustomFormFolder
                WHERE CustomFormFolder.SiteFoldersID = @FolderID)
            SET @empty = 0
        end

    -- Folder Type 7: Site Content (Parts)
    IF @FolderType = 7            
        begin
            IF EXISTS (SELECT TOP 1 SiteContentFolders.ID
                 FROM dbo.SiteContentFolders
                 inner join dbo.SiteContent SC on SC.ID = SiteContentFolders.ContentID
                WHERE SiteContentFolders.SiteFoldersID = @FolderID and SC.Deleted = 0)
            SET @empty = 0
        end    

    -- Folder Type 8: Email Lists
    IF @FolderType = 8
        begin
            IF EXISTS (SELECT TOP 1 ID
                 FROM dbo.EmailList
                WHERE EmailList.SiteFoldersID = @FolderID and EmailList.Deleted = 0)
            SET @empty = 0
        end            

    -- Folder Type 10: Files
    IF @FolderType = 10
        begin
            IF EXISTS (SELECT TOP 1 ID
                 FROM dbo.DOCUPLOADFILES
                WHERE DOCUPLOADFILES.FOLDERID = @FolderID and DOCUPLOADFILES.INTRASHBIN = 0)
            SET @empty = 0
        end        

    -- the following case does not apply to Images because they don't support parent-child folders
    IF EXISTS (SELECT TOP 1 FolderID
                 FROM dbo.SiteFolders
                WHERE SiteFolders.ParentFolderID = @FolderID
                  AND Deleted = 0)
        SET @empty = 0

    RETURN @empty
END