ALTER PROCEDURE [dbo].[BusinessAppGetSites] 
	@InstanceId int,
	@MetadataFilters nvarchar(max),
	@SiteFilters nvarchar(max),
	@RoleFilters nvarchar(max),
	@PrincipalId int,	
	@Take int,
	@Skip int,
	@TotalCountMax int,
	@IsAdmin bit,
	@IsExternal bit,
	@IncludeMetadata bit,
	@IncludeRoles bit,
	@IncludeTotalCount bit,
	@OrderByMetadata nvarchar(max),
	@OrderByRole nvarchar(max),
	@OrderBySiteInformation nvarchar(max),
	@OrderDesc bit,
	@Freetext nvarchar(max)
AS
BEGIN
	DECLARE @Cmd nvarchar(max)
	DECLARE @CmdFrom nvarchar(max) = ''
	DECLARE @CmdWhereRightsCheck nvarchar(max) = ''
	DECLARE @CmdWhereMetadata nvarchar(max) = ''
	DECLARE @CmdWhereSiteInformation nvarchar(max) = ''
	DECLARE @CmdWhereRole nvarchar(max) = ''
	DECLARE @CmdWhereFreetext nvarchar(max) = ''
	DECLARE @CmdOrderBy nvarchar(max) = ''
	DECLARE @CmdGroupBy nvarchar(max) = ''
	DECLARE @CmdGroupHaving nvarchar(max) = ''
	DECLARE @PrevValue nvarchar(max) = ''
	CREATE TABLE #FilteredSites(RowId int IDENTITY(1,1) primary key, SiteId int); -- This table will contain a list of siteId for all the filtered sites, returned by the dynamic Command

-- #################################################################################
-- ############# CONVERT Metadatafilters into query ################################ 
-- #################################################################################

	SET @PrevValue = '';
	DECLARE @NeedQuoteAroundValue bit = 0
	DECLARE @UseAndOperator bit = 0
	DECLARE @UniqueCount int = 0
	SELECT 
		-- Do we need quotes around the value... metadata definition get join when id%3 == 1. So we detect it there, and for all other ids, just use the previous value
		@NeedQuoteAroundValue = CASE split.id % 3 WHEN 1 THEN 
			CASE BA_MetadataDefinition.Type
				WHEN 'text' THEN 1
				WHEN 'textarea' THEN 1
				WHEN 'email' THEN 1
				WHEN 'checkbox' THEN 1
				WHEN 'radio' THEN 1
				WHEN 'datetime' THEN 1
				WHEN 'imagepicker' THEN 1
				WHEN 'select' THEN 1
				WHEN 'taxonomy' THEN 1
				ELSE 0
			END
		else @NeedQuoteAroundValue END,
		-- Count Unique metadata names, being filtered
		@UniqueCount += CASE split.id % 3 
			WHEN 1 THEN 
				CASE split.data
					WHEN @PrevValue THEN 0
					ELSE 1
				END
			ELSE 0
		END,
		-- Is metadata name, the same as previous. Then we need AND operator instead of OR
		@UseAndOperator = CASE split.id % 3 
			WHEN 1 THEN 
				CASE split.data
					WHEN @PrevValue THEN 1
					ELSE 0
				END
			ELSE @UseAndOperator
		END,
		-- Set PreValue. Used by @UseAndOperator and @UniqueCount to determine if "current" metadata name is the same as previous loop
		@PrevValue = CASE split.id % 3 
			WHEN 1 THEN split.data
			ELSE @PrevValue
		END,
		
		-- Append to Where
		@CmdWhereMetadata = @CmdWhereMetadata +
			CASE split.id % 3 
				WHEN 1 THEN 
					CASE split.id 
						WHEN 1 THEN '' -- we dont prepend AND or OR before the very first expression
						ELSE
							CASE @UseAndOperator
								WHEN 1 THEN ' AND '
								ELSE ' OR '
							END
					END + 

					'BA_MetadataDefinition.Name = ''' + REPLACE(split.data, '''', '''''') + ''' AND ' +
					CASE BA_MetadataDefinition.Type
						WHEN 'number' then 'BA_Metadata.ValueDecimal '
						WHEN 'datetime' then 'BA_Metadata.ValueDate '
						WHEN 'select' then 'BA_Metadata.ValueText '
						WHEN 'taxonomy' then 'BA_Metadata.ValueText '
						WHEN 'text' then 'BA_Metadata.Value '
						WHEN 'textarea' then 'BA_Metadata.Value '
						WHEN 'email' then 'BA_Metadata.Value '
						WHEN 'checkbox' then 'BA_Metadata.Value '
						WHEN 'radio' then 'BA_Metadata.Value '
						WHEN 'imagepicker' then 'BA_Metadata.Value '
						ELSE 'null ' -- if a wrong metadata name is defined, we cant detect the type. So we'll just compare to null, with the filter, resulting is no results, which is expected
					END
				WHEN 2 THEN split.data
				WHEN 0 THEN 
					CASE @NeedQuoteAroundValue WHEN 1 THEN ' ''' ELSE ' ' END +
					REPLACE(split.data, '''', '''''') +
					CASE @NeedQuoteAroundValue WHEN 1 THEN ''' ' ELSE ' ' END
			END
	FROM Split(@MetadataFilters, '|') split
		LEFT OUTER JOIN BA_MetadataDefinition on BA_MetadataDefinition.Instance_Id = @InstanceId and split.id%3=1  and BA_MetadataDefinition.Name = split.data
	WHERE split.id>1 or split.data <> ''

	IF(@UniqueCount>0)
	BEGIN
		SET @CmdGroupHaving = ' Count(*) = ' + CONVERT(nvarchar(3),  @UniqueCount)
	END

-- #################################################################################
-- ############# CONVERT Siteinformationfilters into query ######################### 
-- #################################################################################

	SELECT 
		@NeedQuoteAroundValue = CASE split.id % 3
			WHEN 1 THEN
				CASE split.data
					WHEN 'Status' THEN 0
					WHEN 'HasExternalUrl' THEN 0
					ELSE 1
				END
			ELSE @NeedQuoteAroundValue
		END,
		@CmdWhereSiteInformation += CASE split.id % 3
			WHEN 1 THEN
				CASE split.id -- We dont prepend AND, before the first filter
					WHEN 1 THEN ''
					ELSE ' AND '
				END +
				CASE split.data
					WHEN 'IsFavourite' THEN ''
					WHEN 'HasUrl' THEN ''
					ELSE 'BA_Site.' + split.data
				END
			WHEN 2 THEN 
				CASE @PrevValue
					WHEN 'IsFavourite' THEN
						CASE split.data
							WHEN '<>' THEN 'NOT '
							ELSE ''
						END
					WHEN 'HasUrl' THEN
						CASE split.data
							WHEN '<>' THEN 'NOT '
							ELSE ''
						END
					ELSE ' ' + split.data + ' '
				END				
			WHEN 0 THEN 
				CASE @PrevValue
					WHEN 'IsFavourite' THEN 'EXISTS (SELECT FavouriteSites_Id FROM BA_PrincipalBA_Site where FavouriteSites_Id = BA_Site.id AND FavouriteBy_PrincipalId = @PrincipalId)'
					WHEN 'HasUrl' THEN '(BA_Site.status = 4 or (BA_Site.status = 0 and BA_Site.O365GroupUrl <> ''''))'
					ELSE
						CASE @NeedQuoteAroundValue WHEN 1 THEN ' ''' ELSE ' ' END +
						REPLACE(split.data, '''', '''''') +
						CASE @NeedQuoteAroundValue WHEN 1 THEN ''' ' ELSE ' ' END
					END
			ELSE '' 
		END,
		@PrevValue = CASE split.id % 3 
			WHEN 1 THEN split.data
			ELSE @PrevValue
		END
	FROM Split(@SiteFilters, '|') split
	WHERE split.id>1 or split.data <> ''

-- #################################################################################
-- ############# CONVERT RoleFilters into query #################################### 
-- #################################################################################

	SELECT 
		@CmdWhereRole += CASE split.id % 3
			WHEN 1 THEN	
				CASE split.id -- We dont prepend OR, before the first filter
					WHEN 1 THEN ''
					ELSE ' OR '
				END +
				'BA_Role.Name = ''' + split.data + ''''
			--WHEN 2 is not used for roles ATM
			WHEN 0 THEN ' AND (BA_SiteRolePrincipals.BA_PrincipalPrincipalId = ' + split.data + ' or PrincipalGrouping.Members_PrincipalID = ' + split.data + ')'
			ELSE '' 
		END,
		@PrevValue = CASE split.id % 3 
			WHEN 1 THEN split.data
			ELSE @PrevValue
		END
	FROM Split(@RoleFilters, '|') split
	WHERE split.id>1 or split.data <> ''

	IF(@CmdWhereRole<> '')
	BEGIN
		SET @CmdWhereRole = 'EXISTS(
			SELECT BA_SiteRole.Id
			FROM 
				BA_SiteRole
				INNER JOIN BA_Role ON BA_SiteRole.Role_RepositoryConfigRoleId = BA_Role.RepositoryConfigRoleId
				INNER JOIN BA_SiteRolePrincipals ON BA_SiteRolePrincipals.BA_SiteRoleId = BA_SiteRole.Id
				LEFT OUTER JOIN PrincipalGrouping ON PrincipalGrouping.Groups_PrincipalID = BA_SiteRolePrincipals.BA_PrincipalPrincipalId
			WHERE BA_SiteRole.Site_Id = BA_Site.Id AND (' + @CmdWhereRole + '))'
	END

-- #################################################################################
-- ############# FreeTextSearch #################################################### 
-- #################################################################################
	IF(@Freetext <> '')
	BEGIN
		SET @CmdWhereFreetext = '(
			BA_Site.Name LIKE @Freetext OR
			BA_Site.Url LIKE @Freetext OR
			EXISTS(SELECT m.ID 
				FROM BA_Metadata m
					INNER JOIN BA_MetadataDefinition md ON m.metadatadefinition_id = md.id
				WHERE m.site_ID = BA_Site.ID AND 
					ISNULL(m.ValueText, m.Value) LIKE @Freetext 
			)
		)'
	END

-- #################################################################################
-- ############# RightsCheck (Only if user is not admin) ########################### 
-- #################################################################################

	IF(@IsAdmin = 0)
	BEGIN
		DECLARE @CmdWhereRightsCheckExternals nvarchar(max);
		IF(@IsExternal = 1)
		BEGIN
			SELECT @CmdWhereRightsCheckExternals = 'BA_SiteRolePrincipals.BA_PrincipalPrincipalId = ' + CONVERT(nvarchar(10), PrincipalID) from Principal WHERE LoginName = 'Externals'
		END
		ELSE
		BEGIN
			SELECT @CmdWhereRightsCheckExternals = 'BA_SiteRolePrincipals.BA_PrincipalPrincipalId = ' + CONVERT(nvarchar(10), PrincipalID) from Principal WHERE LoginName = 'Internals'
		END

		SET @CmdWhereRightsCheck = 'EXISTS(
			SELECT BA_SiteRole.Id
			FROM 
				BA_SiteRole
				INNER JOIN BA_SiteRolePrincipals on BA_SiteRolePrincipals.BA_SiteRoleId = BA_SiteRole.Id
				LEFT OUTER JOIN PrincipalGrouping ON PrincipalGrouping.Groups_PrincipalID = BA_SiteRolePrincipals.BA_PrincipalPrincipalId
			WHERE BA_SiteRole.Site_Id = BA_Site.Id AND
				(
					BA_SiteRolePrincipals.BA_PrincipalPrincipalId = @PrincipalId OR -- Personal added
					BA_SiteRolePrincipals.BA_PrincipalPrincipalId = 1 OR -- everyone
					' + @CmdWhereRightsCheckExternals + ' OR
					PrincipalGrouping.Members_PrincipalID = @PrincipalId -- Access Thru group
				) 
			)'
	END

-- #################################################################################
-- ############# ORDER BY - Metadata ############################################### 
-- #################################################################################

	IF(@OrderByMetadata <> '')
	BEGIN
		DECLARE @OrderByMetadataDefinitionId int
		DECLARE @OrderByMetadataDefinitionType nvarchar(125)
		SELECT 
			@OrderByMetadataDefinitionId = Id,
			@OrderByMetadataDefinitionType = Type
		FROM
			BA_MetadataDefinition
		WHERE 
			Instance_Id = @InstanceId AND 
			Name = @OrderByMetadata

		IF(@OrderByMetadataDefinitionId>0)
		BEGIN
			SET @CmdFrom += ' LEFT OUTER JOIN BA_Metadata BAMetadataOrderBy ON BAMetadataOrderBy.site_Id = BA_Site.id AND BAMetadataOrderBy.MetadataDefinition_Id = ' + CONVERT(nvarchar(10),  @OrderByMetadataDefinitionId)
			SET @CmdOrderBy +=	CASE @OrderByMetadataDefinitionType 
									WHEN 'number' THEN ' BAMetadataOrderBy.ValueDecimal'
									WHEN 'datetime' THEN ' BAMetadataOrderBy.ValueDate'
									WHEN 'select' THEN ' BAMetadataOrderBy.ValueText'
									WHEN 'taxonomy' THEN ' BAMetadataOrderBy.ValueText'
									ELSE ' BAMetadataOrderBy.Value'
								END
			SET @CmdGroupBy += CASE @OrderByMetadataDefinitionType 
									WHEN 'number' THEN ', BAMetadataOrderBy.ValueDecimal'
									WHEN 'datetime' THEN ', BAMetadataOrderBy.ValueDate'
									WHEN 'select' THEN ', BAMetadataOrderBy.ValueText'
									WHEN 'taxonomy' THEN ', BAMetadataOrderBy.ValueText'
									ELSE ', BAMetadataOrderBy.Value'
								END
			IF(@OrderDesc = 1)
			BEGIN
				SET @CmdOrderBy += ' DESC'
			END
		END
	END

-- #################################################################################
-- ############# ORDER BY - Siteinformation ######################################## 
-- #################################################################################

	IF(@CmdOrderBy = '' AND @OrderBySiteInformation<> '')
	BEGIN
		SET @CmdOrderBy += 'BA_Site.' + @OrderBySiteInformation
		IF(@OrderDesc = 1)
		BEGIN
			SET @CmdOrderBy += ' DESC'
		END
	END

-- #################################################################################
-- ############# ORDER BY - role ######################################## 
-- #################################################################################

	IF(@CmdOrderBy = '' AND @OrderByRole <> '')
	BEGIN
		SET @CmdOrderBy += '(
				select
					min(principal.DisplayName)
				FROM
					BA_SiteRolePrincipals 
					inner join BA_SiteRole on BA_SiteRolePrincipals.BA_SiteRoleId = BA_SiteRole.Id AND BA_SiteRole.site_id = BA_Site.id
					inner join Principal on BA_SiteRolePrincipals.BA_PrincipalPrincipalId = Principal.PrincipalID
					inner join BA_Role on BA_SiteRole.Role_RepositoryConfigRoleId = BA_Role.RepositoryConfigRoleId AND BA_Role.Instance_id = @InstanceId AND BA_Role.Name = ''' + Replace(@OrderByRole, '''', '''''') + '''
			)'
		IF(@OrderDesc = 1)
		BEGIN
			SET @CmdOrderBy += ' DESC'
		END
	END
-- #################################################################################
-- ############# ORDER BY - Default, fallback ###################################### 
-- #################################################################################

	IF(@CmdOrderBy = '')
	BEGIN
		SET @CmdOrderBy = 'BA_Site.Id desc'
	End

-- #################################################################################
-- ############# Combine everything above, to create the dynamic query #############
-- #################################################################################

	SET @Cmd = 'INSERT INTO #FilteredSites
				SELECT 
					BA_Site.Id as SiteId
				FROM
					BA_Site
						inner join BA_SiteType on BA_Site.SiteType_Id = BA_SiteType.Id AND BA_SiteType.InstanceId = @InstanceId
						left outer join BA_Metadata on BA_Metadata.Site_Id = BA_Site.Id
						left outer join BA_MetadataDefinition on BA_Metadata.MetadataDefinition_Id = BA_MetadataDefinition.Id ' + 
						@CmdFrom + '
				WHERE ' +
					CASE @CmdWhereSiteInformation WHEN '' THEN '' ELSE @CmdWhereSiteInformation + ' AND ' END + 
					CASE @CmdWhereMetadata WHEN '' THEN '' ELSE @CmdWhereMetadata + ' AND ' END + 
					CASE @CmdWhereRightsCheck WHEN '' THEN '' ELSE @CmdWhereRightsCheck + ' AND ' END + 
					CASE @CmdWhereRole WHEN '' THEN '' ELSE @CmdWhereRole + ' AND ' END + 
					CASE @CmdWhereFreetext WHEN '' THEN '' ELSE @CmdWhereFreetext + ' AND ' END + 
					' 1=1
				GROUP BY BA_Site.Id, BA_Site.Name, BA_Site.url, BA_Site.sharepointWebId, BA_Site.status ' + @CmdGroupBy + 
					CASE @CmdGroupHaving WHEN '' THEN '' ELSE ' Having ' + @CmdGroupHaving END + '
				ORDER BY ' +
					@CmdOrderBy + '
				OFFSET @Skip rows
				FETCH NEXT @Take rows ONLY'

-- #################################################################################
-- ############# Execute the dynamic query, into #FilteredSites ####################
-- #################################################################################

	DECLARE @TakeForFilteredSites int = CASE @IncludeTotalCount WHEN 1 THEN @TotalCountMax ELSE @Take END
	DECLARE @SkipForFilteredSites int = CASE @IncludeTotalCount WHEN 1 THEN 0 ELSE @Skip END

	EXECUTE sp_executesql @Cmd, 
			N'@InstanceId nvarchar(max), @PrincipalId int, @IsAdmin bit, @Skip int, @Take int, @Freetext nvarchar(max)', 
			@InstanceId = @InstanceId, 
			@PrincipalId = @PrincipalId,
			@IsAdmin = @IsAdmin,
			@Skip = @SkipForFilteredSites,
			@Take = @TakeForFilteredSites,
			@Freetext = @Freetext
					
-- #################################################################################
-- ############# Calculate totalCount, and adjust #FilteredSites ###################
-- ############# according to @Skip and @Take                    ###################
-- #################################################################################

	DECLARE @TotalCount int
	IF(@IncludeTotalCount = 1)
	BEGIN
		SELECT @TotalCount = Count(*)
		FROM #FilteredSites

		DELETE FROM #FilteredSites
		WHERE 
			RowId <= @Skip OR 
			RowId > @Skip + @Take
	END

-- #################################################################################
-- ############# Return Sites, using #FilteredSites ################################
-- #################################################################################

	SELECT 
		BA_Site.Id,
		BA_Site.Name,
		BA_Site.Url,
		BA_Site.SharePointWebId,
		BA_Site.Status,
		BA_Site.StatusLastChanged,
		BA_Site.O365GroupId,
		BA_Site.O365GroupUrl,
		BA_SiteType.Id as SiteTypeId,
		BA_SiteType.Name as SiteTypeName,
		Principal.PrincipalID as StatusLastChangedById,
		Principal.Type as StatusLastChangedByType,
		Principal.LoginName as StatusLastChangedByLoginName,
		Principal.DisplayName as StatusLastChangedByDisplayName,
		Principal.GroupId as StatusLastChangedByGroupId,
		Principal.Email as StatusLastChangedByEmail,
		CASE WHEN Favorite.FavouriteBy_PrincipalId IS NULL THEN 0 ELSE 1 END as IsFavorite
	FROM BA_Site
	INNER JOIN #FilteredSites FilteredSites on BA_Site.Id = FilteredSites.SiteId
	INNER JOIN BA_SiteType on BA_Site.SiteType_Id = BA_SiteType.Id
	LEFT OUTER JOIN Principal ON BA_Site.StatusLastChangedBy_PrincipalId = Principal.PrincipalID
	LEFT OUTER JOIN BA_PrincipalBA_Site Favorite ON Favorite.FavouriteSites_Id = BA_Site.Id AND Favorite.FavouriteBy_PrincipalId = @PrincipalId

-- #################################################################################
-- ############# Return Metadata and definitions, using #FilteredSites #############
-- #################################################################################

	IF(@IncludeMetadata = 1)
	BEGIN
		-- definitions
		SELECT DISTINCT 
			BA_MetadataDefinition.*, 
			BA_SiteTypeMetadataDefinitions.SortOrder
		FROM BA_Site
			INNER JOIN #FilteredSites filteredSites on BA_Site.Id = filteredSites.SiteId
			INNER JOIN BA_SiteType on BA_SiteType.Id = BA_Site.SiteType_Id
			INNER JOIN BA_SiteTypeMetadataDefinitions on BA_SiteTypeMetadataDefinitions.BA_SiteTypeId = BA_SiteType.Id
			INNER JOIN BA_MetadataDefinition on BA_MetadataDefinition.Id = BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId
		
		-- values
		SELECT DISTINCT 
			BA_Metadata.Value,
			BA_Metadata.ValueDecimal,
			BA_Metadata.ValueDate,
			BA_Metadata.ValueText,
			--BA_Metadata.Id,
			BA_Site.Id as Site_Id,
			--BA_MetadataDefinition.Name,
			BA_MetadataDefinition.Id as MetadataDefinition_Id
		FROM BA_Site
			INNER JOIN #FilteredSites filteredSites on BA_Site.Id = filteredSites.SiteId
			INNER JOIN BA_SiteType on BA_SiteType.Id = BA_Site.SiteType_Id
			INNER JOIN BA_SiteTypeMetadataDefinitions on BA_SiteTypeMetadataDefinitions.BA_SiteTypeId = BA_SiteType.Id
			INNER JOIN BA_MetadataDefinition on BA_MetadataDefinition.Id = BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId
			LEFT OUTER JOIN BA_Metadata on BA_Metadata.MetadataDefinition_Id = BA_MetadataDefinition.Id AND BA_Metadata.Site_Id = BA_Site.Id
			
		-- definitions
		--SELECT DISTINCT BA_MetadataDefinition.*, 
		--BA_SiteTypeMetadataDefinitions.SortOrder
		--FROM BA_MetadataDefinition
		--	INNER JOIN BA_Metadata on BA_Metadata.MetadataDefinition_Id = BA_MetadataDefinition.Id
		--	INNER JOIN #FilteredSites FilteredSites ON BA_Metadata.Site_Id = FilteredSites.SiteId
		--	INNER JOIN BA_SiteTypeMetadataDefinitions on BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId = BA_MetadataDefinition.Id
		
		---- values
		--SELECT BA_Metadata.* 
		--FROM BA_Metadata
		--	INNER JOIN #FilteredSites FilteredSites on BA_Metadata.Site_Id = FilteredSites.SiteId
	END

-- #################################################################################
-- ############# Return Roles and Siteroles, using #FilteredSites ##################
-- #################################################################################

	IF(@IncludeRoles = 1)
	BEGIN
		-- roles
		SELECT 
			BA_Role.RepositoryConfigRoleId,
			BA_Role.Name,
			BA_Role.InternalName,
			BA_SiteRole.Site_Id,
			BA_Role.HideOnDisplayForm,
			BA_Role.HideOnEditForm,
			BA_Role.HideOnNewForm,
			BA_Role.CanEditMetadata,
			BA_Role.CanAssignRoles,
			BA_Role.CanReadNoticeboard,
			BA_Role.CanWriteNoticeboard,
            BA_Role.AllowMultiple,
			BA_Role.AllowPersons,
			BA_Role.AllowGroups,
            BA_Role.AllowInternals,
            BA_Role.AllowExternals
		FROM BA_Role
			inner join BA_SiteRole on BA_SiteRole.Role_RepositoryConfigRoleId = BA_Role.RepositoryConfigRoleId
			inner join #FilteredSites FilteredSites on BA_SiteRole.Site_Id = FilteredSites.SiteId
		GROUP BY 
			BA_Role.RepositoryConfigRoleId,
			BA_Role.Name,
			BA_Role.InternalName,
			BA_SiteRole.Site_Id,
			BA_Role.HideOnDisplayForm,
			BA_Role.HideOnEditForm,
			BA_Role.HideOnNewForm,
			BA_Role.CanEditMetadata,
			BA_Role.CanAssignRoles,
			BA_Role.CanReadNoticeboard,
			BA_Role.CanWriteNoticeboard,
            BA_Role.AllowMultiple,
			BA_Role.AllowPersons,
			BA_Role.AllowGroups,
            BA_Role.AllowInternals,
            BA_Role.AllowExternals,
			BA_Role.SortOrder
		ORDER BY
			BA_Role.SortOrder
		-- principals
		SELECT 
			Principal.PrincipalID,
			Principal.Type,
			Principal.DisplayName,
			Principal.GroupId,
			Principal.LoginName,
			Principal.Email
		FROM BA_SiteRolePrincipals
				inner join BA_SiteRole on BA_SiteRole.Id = BA_SiteRolePrincipals.BA_SiteRoleId
				inner join #FilteredSites FilteredSites on BA_SiteRole.Site_Id = FilteredSites.SiteId
				inner join Principal on Principal.PrincipalID = BA_SiteRolePrincipals.BA_PrincipalPrincipalId
		GROUP BY 
			Principal.PrincipalID,
			Principal.Type,
			Principal.DisplayName,
			Principal.GroupId,
			Principal.LoginName,
			Principal.Email

		-- site role principals
		SELECT 
			BA_SiteRolePrincipals.BA_PrincipalPrincipalId,
			--BA_SiteRolePrincipals.BA_SiteRoleId,
			BA_SiteRole.Site_Id,
			BA_SiteRole.Role_RepositoryConfigRoleId
		FROM BA_SiteRolePrincipals
				inner join BA_SiteRole on BA_SiteRole.Id = BA_SiteRolePrincipals.BA_SiteRoleId
				inner join #FilteredSites FilteredSites on BA_SiteRole.Site_Id = FilteredSites.SiteId

		-- current users roles
		SELECT 
			FilteredSites.SiteId,
			BA_SiteRole.Role_RepositoryConfigRoleId as RoleId
		FROM BA_SiteRolePrincipals
				inner join BA_SiteRole on BA_SiteRole.Id = BA_SiteRolePrincipals.BA_SiteRoleId
				inner join #FilteredSites FilteredSites on BA_SiteRole.Site_Id = FilteredSites.SiteId
				left outer join PrincipalGrouping on PrincipalGrouping.Groups_PrincipalID = BA_SiteRolePrincipals.BA_PrincipalPrincipalId
		WHERE 
			BA_PrincipalPrincipalId = 1 OR -- everyone
			BA_PrincipalPrincipalId = @PrincipalId OR -- personal
			PrincipalGrouping.Members_PrincipalID = @PrincipalId --group
		GROUP BY 
			FilteredSites.SiteId, 
			BA_SiteRole.Role_RepositoryConfigRoleId
	END

-- #################################################################################
-- ############# Return TotalCount #################################################
-- #################################################################################

	IF(@IncludeTotalCount = 1)
	BEGIN
		SELECT @TotalCount as TotalCount
	END

-- #################################################################################
-- ############# Return InstanceInformation ########################################
-- #################################################################################

	SELECT 
		Id, 
		Name, 
		Url, 
		AppId 
	FROM 
		BA_Instance 
	WHERE
		id = @InstanceId
END