ALTER PROCEDURE [dbo].[BusinessAppGetSites] 
		@InstanceId int,-- = 11,
		@MetadataFilters nvarchar(max),-- = '',-- 'StringMetadata|eq|Gul¤NumberMetadata|lt|50'
		@SiteFilters nvarchar(max),--
		@RoleFilters nvarchar(max),-- = '',--'RoleCanEditMetadataAndAssignRoles|eq|1'
		@PrincipalId int,-- = 1337,
		@Take int,-- = 30,
		@Skip int,-- = 0,
		@TotalCountMax int,
		@IsAdmin bit,-- = 0,
		@IncludeMetadata bit,-- = 1,
		@IncludeRoles bit,-- = 1,
		@IncludeTotalCount bit,
		@OrderBy nvarchar(max)-- = ''--'NumberMetadata'
	AS
	BEGIN
		DECLARE @Cmd nvarchar(max)
		DECLARE @Wheres nvarchar(max)

		-- split @MetadataFilters up into a table and join with metadatadefinition to get the type.
		-- the inner join, will also remove invalid metadata names.
		-- there's also a column called query, which translate the filter into expressions, which we later can use in the where clause
		-- this will ex. turn 'color|eq|brown¤count|lt|50'
		-- into 
		--      -------------------------------------------
		--      |Name |Operator|Value|Type  |query        |
		--		-------------------------------------------
		--      |color|   eq   |brown|text  |color='brown'|
		--      |count|   lt   | 50  |number|count<50     |
		--      -------------------------------------------
		;With MetadataFilters as(
		SELECT 
			a.id, 
			b.id as columnIndex, 
			b.data
		FROM Split(@MetadataFilters, '¤') a
			CROSS APPLY Split(Data, '|') b
		) select 
			[1] as Name,
			[2] as Operator,
			[3] as value,
			BA_MetadataDefinition.Type,
			'(BA_MetadataDefinition.Name = ''' +
			REPLACE([1], '''', '''''') + 
			''' 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'
				else 'BA_Metadata.Value'
			end +
			[2] +
			case BA_MetadataDefinition.Type
				when 'text' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'textarea' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'email' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'checkbox' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'radio' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'datetime' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'imagepicker' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'select' then '''' + REPLACE([3], '''', '''''') + ''''
				when 'taxonomy' then '''' + REPLACE([3], '''', '''''') + ''''
				else [3]
			end + 
			')'
			as query
			into #MetadataFilters
			from MetadataFilters
		PIVOT (min(data) for columnIndex in ([1],[2],[3])) as P1
		INNER JOIN BA_MetadataDefinition on BA_MetadataDefinition.Instance_Id = @InstanceId and BA_MetadataDefinition.Name = [1]
	
		-- split @SiteFilters up into a table. Just like metadataFilters
		;With SiteFilters as(
		SELECT 
			a.id, 
			b.id as columnIndex, 
			b.data 
		FROM Split(@SiteFilters, '¤') a
			CROSS APPLY Split(Data, '|') b
		) 
		SELECT
			[1],
			[2],
			[3],
			'BA_Site.' + [1] + [2] + 
			case [1]
				when 'status' then REPLACE([3], '''', '''''')
				else '''' + REPLACE([3], '''', '''''') + ''''
			end 
			as query
		into #SiteFilters
		FROM SiteFilters 
		PIVOT (min(data) for columnIndex in ([1],[2],[3])) as P1
		where [1] in ('Name','Url','SharePointWebId','Status','StatusLastChanged') -- filter out invalid values

		-- split @RoleFilters up into a table. Just like metadataFilters
		;With RoleFilters as(
		SELECT 
			a.id, 
			b.id as columnIndex, 
			b.data --BA_Role.Name = 'RoleCanEditMetadataAndAssignRoles' AND BA_SiteRolePrincipals.BA_PrincipalPrincipalId = @PrincipalId
		FROM Split(@RoleFilters, '¤') a
			CROSS APPLY Split(Data, '|') b
		) 
		SELECT
			[1],
			[2],
			[3],
			'BA_Role.Name = ''' + [1] + ''' AND (BA_SiteRolePrincipals.BA_PrincipalPrincipalId = ' + [3] + ' or PrincipalGrouping.Members_PrincipalID = ' + [3] + ')' as query
		into #RoleFilters
		FROM RoleFilters 
		PIVOT (min(data) for columnIndex in ([1],[2],[3])) as P1
		WHERE EXISTS(SELECT RepositoryConfigRoleId FROM BA_Role INNER JOIN BA_SiteType ON BA_SiteType.Id = BA_Role.SiteType_Id where BA_Role.Name = [1] AND BA_SiteType.InstanceId = @InstanceId) -- need instance

		-- create temp table to hold the filteredSites from the dynamic SQL.
		CREATE TABLE #FilteredSites(RowId int IDENTITY(1,1) primary key, SiteId int)

		SET @Cmd = 'INSERT INTO #FilteredSites SELECT
			--IDENTITY( int ) as RowId,
			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 
		WHERE
		'
		-- metadatafilters
		IF(Exists(select query from #MetadataFilters))
		BEGIN
			SET @Wheres = null;
			SELECT @Wheres = COALESCE(@Wheres + ' OR ', '') + query FROM #MetadataFilters --COALESCE? see http://stackoverflow.com/a/194887 for more information
			SET @cmd = @cmd + ' ( ' + @Wheres + ' ) AND '
		END

		-- Site filters
		IF(EXISTS(SELECT query FROM #SiteFilters))
		BEGIN
			SET @Wheres = null;
			SELECT @Wheres = COALESCE(@Wheres + ' AND ', '') + query FROM #SiteFilters --COALESCE? see http://stackoverflow.com/a/194887 for more information
			SET @Cmd = @Cmd + @Wheres + ' AND '
		END

		-- rights check
		IF(@IsAdmin = 0)
		BEGIN
		SET @Cmd = @Cmd + ' 
			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
					PrincipalGrouping.Members_PrincipalID = @PrincipalId -- Access Thru group
				) 
			) AND '
		END

		-- Role filters
		IF(EXISTS(SELECT query FROM #RoleFilters))
		BEGIN
			SET @Wheres = null;
			SELECT @Wheres = COALESCE(@Wheres + ' OR ', '') + query FROM #RoleFilters --COALESCE? see http://stackoverflow.com/a/194887 for more information
			SET @cmd = @cmd + ' 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 (' + @Wheres + ')) AND '
		END

		-- 1=1 to "end last and" in the dynamic where's
		SET @Cmd = @Cmd + ' 1=1 GROUP BY BA_Site.Id'

		if(EXISTS(SELECT * FROM #MetadataFilters))
		BEGIN
			SELECT @Cmd = @Cmd + ' HAVING Count(*) = ' + CONVERT(nvarchar(3), Count(*)) FROM #MetadataFilters
		END

		if(@OrderBy != '')
		BEGIN
			SET @Cmd = @CMD + '
			ORDER BY 
			(
				SELECT VALUE 
				FROM BA_Metadata BA_Metadata2
					INNER JOIN BA_MetadataDefinition on BA_MetadataDefinition.Id = BA_Metadata2.MetadataDefinition_Id AND BA_MetadataDefinition.Instance_Id = @InstanceId
				WHERE BA_MetadataDefinition.Name = @OrderBy AND BA_Metadata2.Site_Id = BA_Metadata.Site_Id)
			'
		END
		ELSE
		BEGIN
			SET @Cmd = @CMD + '
			ORDER BY 
			BA_Site.Id desc
			'
		END

		-- Skip/Take	
		SET @Cmd = @CMD + '
		OFFSET @Skip rows
		FETCH NEXT @Take rows ONLY
		'

		-- execute the dynamic SQL. This will fill #FilteredSites with siteId of the sites we want to return 
		DECLARE @SkipForFiteredSites int = @Skip
		DECLARE @TakeForFilteredSites int = @Take
		IF(@IncludeTotalCount = 1)
		BEGIN
			SET @SkipForFiteredSites = 0;
			SET @TakeForFilteredSites = @TotalCountMax 
		END

		EXECUTE sp_executesql @Cmd, 
			N'@InstanceId nvarchar(max), @PrincipalId int, @IsAdmin bit, @OrderBy nvarchar(max), @Skip int, @Take int', 
			@InstanceId = @InstanceId, 
			@PrincipalId = @PrincipalId,
			@IsAdmin = @IsAdmin,
			@OrderBy = @OrderBy,
			@Skip = @SkipForFiteredSites,
			@Take = @TakeForFilteredSites

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

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

		-- Return sites
		SELECT 
			BA_Site.Id,
			BA_Site.Name,
			BA_Site.Url,
			BA_Site.SharePointWebId,
			BA_Site.Status,
			BA_Site.StatusLastChanged,
			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 for sites
		IF(@IncludeMetadata = 1)
		BEGIN
			-- definitions
			SELECT DISTINCT BA_MetadataDefinition.*
			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
			--WHERE BA_MetadataDefinition.Instance_Id = @InstanceId
		
			-- values
			SELECT BA_Metadata.* 
			FROM BA_Metadata
				INNER JOIN #FilteredSites FilteredSites on BA_Metadata.Site_Id = FilteredSites.SiteId
		END

		-- return roles for sites
		IF(@IncludeRoles = 1)
		BEGIN
			-- roles
			SELECT 
				BA_Role.RepositoryConfigRoleId,
				BA_Role.Name,
				BA_Role.InternalName,
				BA_SiteRole.Site_Id
			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
			-- 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

		END

		IF(@IncludeTotalCount = 1)
		BEGIN
			SELECT @TotalCount as TotalCount
		END
		SELECT @Cmd
		--SELECT * FROM #MetadataFilters
		--SELECT * FROM #RoleFilters
		--SELECT * FROM #FilteredSites

		--DROP TABLE #MetadataFilters
		--DROP TABLE #RoleFilters
		--DROP TABLE #FilteredSites
	END