SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  
SET NOCOUNT ON
DECLARE @SELECTED_COMPUTERS table (computer nvarchar(255))
DECLARE @SELECTED_FOLDERS table (RootPath nvarchar(max))
DECLARE @INCLUDE_SUB_FOLDERS bit
DECLARE @INCLUDE_DOMAIN_USERS bit

-- 1. Build list of subfolders

DECLARE @FolderTable TABLE (    ComputerID BIGINT,    NTFSID BINARY(32))
DECLARE @WorkingFolderTableWithSubFolders TABLE (    RootPath nvarchar(max),    NTFSID BINARY(32))
-- Make a copy of the parameter table
INSERT INTO @FolderTable
SELECT [tblComputer].ComputerID, [tblNTFS].[NTFSID]
FROM
[tblNTFSComputer] base
LEFT OUTER JOIN [tblNTFS] tblNTFS
ON ([base].[NTFSComputerID] = [tblNTFS].[NTFSComputerID] AND [tblNTFS].[LDM_TOMBSTONED] = 0)
LEFT OUTER JOIN [tblComputer] tblComputer
ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND [tblComputer].[LDM_TOMBSTONED] = 0)
WHERE (([tblComputer].[ComputerName] IN (SELECT * FROM @SELECTED_COMPUTERS)) AND ([tblNTFS].[Path] IN (SELECT * FROM @SELECTED_FOLDERS)) AND base.[LDM_TOMBSTONED] = 0)

-- 2. Fill WorkingFolderTableWithParentFolder with all the associated NTFSID's
DECLARE @tempNTFSID BINARY(32)
DECLARE NTFSCursor CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR
	SELECT NTFSID FROM @FolderTable

OPEN NTFSCursor

DECLARE @PUFQ_Break bit
SELECT @PUFQ_Break = 0
WHILE @PUFQ_Break = 0 BEGIN
    FETCH NEXT FROM NTFSCursor INTO @tempNTFSID
    SELECT @PUFQ_Break = @@FETCH_STATUS
   IF @@FETCH_STATUS = 0 BEGIN
	IF @INCLUDE_SUB_FOLDERS = 1 BEGIN
    INSERT INTO @WorkingFolderTableWithSubFolders
		SELECT
			NTFS.Path, GANUN.NTFSID 
		FROM
    	dbo.GetAllNTFSUnderNTFS(@tempNTFSID) GANUN
        INNER JOIN tblNTFS NTFS ON GANUN.NTFSID = NTFS.NTFSID
        INNER JOIN tblNTFSComputer NTFSComputer ON NTFS.NTFSComputerID = NTFSComputer.NTFSComputerID
        INNER JOIN tblComputer C ON NTFSComputer.ComputerID = C.ComputerID
		WHERE
    		C.ComputerID IN (SELECT ComputerID FROM @FolderTable)
    		AND NTFS.IsFolder = 1
    	END
    	ELSE BEGIN
    		INSERT INTO @WorkingFolderTableWithSubFolders
			SELECT TOP 1 tblNTFS.Path, @tempNTFSID FROM tblNTFS WHERE tblNTFS.NTFSID = @tempNTFSID
    	END
END
END
CLOSE NTFSCursor
DEALLOCATE NTFSCursor

DECLARE @ReturnTable TABLE    (	AccountID bigint, ComputerName nvarchar(255),	FullPath nvarchar(max),	AccountDisplayName nvarchar(255), Perm nvarchar(255), [DisplayName] nvarchar(255), [FriendlyName] nvarchar(255))       
DECLARE @ProcessedGroups TABLE    (		AccountID bigint    )    
DECLARE @PathID bigint
DECLARE @RootPath nvarchar(max)
DECLARE @AccountID bigint
DECLARE @ComputerName nvarchar(255)
DECLARE @FullPath nvarchar(max)
DECLARE @AccountType nvarchar(5)
DECLARE @AccountDisplayName nvarchar(255)
DECLARE @AceTypeText nvarchar(255)
DECLARE @PermText nvarchar(50)
DECLARE @PermTextExt nvarchar(305)
DECLARE @AccountSid nvarchar (50)
DECLARE @DisplayName nvarchar(255)
DECLARE @FriendlyName nvarchar(255)

DECLARE @ENPQ_Folder_Break bit
DECLARE @ENPQ_Account_Break bit

-- Declare folder cursor
DECLARE FolderCursor CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR
	SELECT RootPath, NTFSID FROM  @WorkingFolderTableWithSubFolders
-- End of Folder Cursor declaration
	
OPEN FolderCursor
SELECT @ENPQ_Folder_Break = 0
WHILE @ENPQ_Folder_Break = 0 BEGIN
    FETCH NEXT FROM FolderCursor INTO @RootPath, @PathID
    SELECT @ENPQ_Folder_Break = @@FETCH_STATUS
    IF @@FETCH_STATUS = 0 BEGIN

		-- Declare account cursor
		DECLARE AccountCursor CURSOR LOCAL FAST_FORWARD READ_ONLY
		FOR
		SELECT  [tblComputer].[ComputerName] as [Computer Name (Computer)], 
				[tblNTFS].[Path] as [Path (NTFS)],
				[tblACE].[AccountID],
				[vwACEAccount].AccountType,
				[vwACEAccount].AccountSid, 
				CASE WHEN LEN([vwACEAccount].[SAMAccountDomain]) = 0 THEN [vwACEAccount].[SAMAccountName] ELSE [vwACEAccount].[SAMAccountDomain] + '\' + [vwACEAccount].[SAMAccountName] END as [Account Name (Account)], 
				[Type (ACE)] = CASE [tblACE].[AceType]
								WHEN 0 THEN N'Allow'
								WHEN 1 THEN N'Deny'
								ELSE ''
				END,
				dbo.udfBitmaskLookup('ACE', 'AccessMask', 1033, [tblACE].[AccessMask]) as [Permissions (ACE)],
				tblDomainUser.DisplayName,
				tblDomainUser.FriendlyName
				--DATEADD(minute, -180, [base].[CollectedTime])as [Last Collected Time (Computer)]
				FROM
				[tblNTFSComputer] base
				LEFT OUTER JOIN [tblNTFS] tblNTFS
				ON ([base].[NTFSComputerID] = [tblNTFS].[NTFSComputerID] AND [tblNTFS].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [tblACL] tblACL
				ON ([tblNTFS].[ACLID] = [tblACL].[ACLID] AND [tblACL].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [tblAccount] tblAccount
				ON ([tblACL].[OwnerAccountID] = [tblAccount].[AccountID] AND [tblAccount].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [tblACE] tblACE
				ON ([tblACL].[ACLID] = [tblACE].[ACLID] AND [tblACE].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [vwACEAccount] vwACEAccount
				ON ([tblACE].[AccountID] = [vwACEAccount].[AccountID])
				LEFT OUTER JOIN [tblDomainGroup] tblDomainGroup
				ON ([vwACEAccount].[AccountID] = [tblDomainGroup].[AccountID] AND [tblDomainGroup].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [tblComputerUser] tblComputerUser
				ON ([vwACEAccount].[AccountID] = [tblComputerUser].[AccountID] AND [tblComputerUser].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [tblDomainUser] tblDomainUser
				ON ([vwACEAccount].[AccountID] = [tblDomainUser].[AccountID] AND [tblDomainUser].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [vwComputerGroup] vwComputerGroup
				ON ([vwACEAccount].[AccountID] = [vwComputerGroup].[AccountID])
				LEFT OUTER JOIN [tblComputer] tblComputer
				ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND [tblComputer].[LDM_TOMBSTONED] = 0)
				LEFT OUTER JOIN [tblAccount] tblAccount_2
				ON ([tblComputerUser].[AccountID] = [tblAccount_2].[AccountID] AND [tblAccount_2].[LDM_TOMBSTONED] = 0)
				WHERE ([tblNTFS].[IsFolder] = 1 AND base.[LDM_TOMBSTONED] = 0) AND
				([tblComputer].[ComputerName] IN (SELECT computer from @SELECTED_COMPUTERS)) AND ([tblNTFS].[Path] IN (SELECT RootPath from @SELECTED_FOLDERS))
				ORDER BY [tblComputer].[ComputerName] ASC, [Permissions (ACE)] ASC
		-- End of Account Cursor declaration
    
		OPEN AccountCursor
		SELECT @ENPQ_Account_Break = 0
		WHILE @ENPQ_Account_Break = 0 BEGIN
			FETCH NEXT FROM AccountCursor INTO @ComputerName, @FullPath, @AccountID, @AccountType, @AccountSid, @AccountDisplayName, @AceTypeText, @PermText, @DisplayName, @FriendlyName
			SELECT @ENPQ_Account_Break = @@FETCH_STATUS
			IF @@FETCH_STATUS = 0 BEGIN

				SELECT @PermTextExt = @AceTypeText + ' ' + @PermText
	    
				IF @AccountType = 'G'
				 BEGIN					
					-- TODO: insert only once, do not allow duplicates
					INSERT INTO @ReturnTable
					SELECT distinct GM.AccountID,  @ComputerName, @RootPath, GM.AccountName, @PermTextExt, DU.DisplayName, DU.FriendlyName FROM vwGroupMemberAccount2 GM
					LEFT OUTER JOIN tblDomainUser DU on GM.AccountID = DU.AccountID and DU.[LDM_TOMBSTONED] = 0
					WHERE GM.GroupID IN (SELECT GroupAccountID FROM dbo.ExpandedGroupMembershipForAccountID(@AccountID, @INCLUDE_DOMAIN_USERS))	
					AND GM.AccountType <> 'G'
					AND GM.AccountID not in (select R.AccountID from @ReturnTable R)
				  END
				ELSE
					if (Not exists (select 1 from @ReturnTable where AccountId = @AccountID))
					begin
						-- TODO: insert only once, do not allow duplicates
						INSERT INTO @ReturnTable Values (@AccountId, @ComputerName, @RootPath, @AccountDisplayName, @PermTextExt, @DisplayName, @FriendlyName)
					end
			END
		END
		CLOSE AccountCursor
		DEALLOCATE AccountCursor
	END
END
CLOSE FolderCursor
DEALLOCATE FolderCursor

-- because abovemetioned TODO is not implemented so just use DISTINCT to get rid of duplicates
SELECT ComputerName as [Computer Name] ,  FullPath  as [Path],  AccountDisplayName as [Account Name], Perm as [Permissions], [DisplayName], [FriendlyName] FROM @ReturnTable