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,
		@OrderByMetadata nvarchar(max),
		@OrderByRole nvarchar(max),
		@OrderBySiteInformation nvarchar(max),
		@OrderDesc bit
	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], '''', '''''') + '''' as QueryName,
			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 queryValue
			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]
	
		--SELECT * FROM #MetadataFilters

		DECLARE @MetadataFiltersParsed nvarchar(max) = ''
		DECLARE @prevName nvarchar(max) =''
		SELECT 
			@MetadataFiltersParsed = @MetadataFiltersParsed + 
			CASE QueryName
				WHEN @prevName THEN ' AND '  + queryValue
				ELSE
					CASE @MetadataFiltersParsed WHEN '' THEN '' ELSE ') OR ' END -- dont prepend the OR seperator for the first item
					 + '(' + QueryName + ' AND ' + queryValue
			END,
			@prevName = QueryName
		From #MetadataFilters
		IF(@MetadataFiltersParsed != '')
			SET @MetadataFiltersParsed = @MetadataFiltersParsed + ')'
		

		-- split @SiteFilters up into a table. Just like metadataFilters. IsFavorite is not included here, as its being handled different (its a binding table)
		;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', 'IsFavourite') -- 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(@MetadataFiltersParsed != '')
		BEGIN
			SET @Cmd = @Cmd + ' (' + @MetadataFiltersParsed + ') AND '
		END

		-- Site filters
		IF(EXISTS(SELECT query FROM #SiteFilters where #Sitefilters.[1] != 'IsFavourite'))
		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

		-- Site filter - Specialcase: IsFavorite
		SELECT @Cmd = @Cmd + 
			case [2]
				when '<>' then 'NOT '
				else  ''
			end +
			'EXISTS (SELECT FavouriteSites_Id FROM BA_PrincipalBA_Site where FavouriteSites_Id = BA_Site.id AND FavouriteBy_PrincipalId = @PrincipalId) AND '
		 FROM #SiteFilters where #Sitefilters.[1] = 'IsFavourite'

		-- 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, BA_Site.Name, BA_Site.url, BA_Site.sharepointWebId, BA_Site.status'

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

		--case [1]
		--		when 'status' then REPLACE([3], '''', '''''')
		--		else '''' + REPLACE([3], '''', '''''') + ''''
		--	end 

		IF(@OrderByMetadata != '')
		BEGIN
			DECLARE @OrderByType nvarchar(10);
			SELECT @OrderByType = Type FROM BA_MetadataDefinition WHERE BA_MetadataDefinition.Instance_Id = @InstanceId and BA_MetadataDefinition.Name = @OrderByMetadata
			SET @Cmd = @CMD + '
			ORDER BY 
			(
				SELECT 
				' + case @OrderByType
						when 'number' then 'ValueDecimal '
						when 'datetime' then 'ValueDate '
						when 'select' then 'ValueText '
						when 'taxonomy' then 'ValueText '
						else 'Value '
					end + ' 
				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 = ''' + Replace(@OrderByMetadata, '''', '''''') + ''' AND BA_Metadata2.Site_Id = BA_Site.Id
			)' + CASE @OrderDesc WHEN 1 THEN ' DESC' ELSE '' END
		END
		ELSE IF(@OrderBySiteInformation != '')
		BEGIN
			SET @Cmd = @CMD + '
			ORDER BY 
			BA_Site.' + @OrderBySiteInformation + CASE @OrderDesc WHEN 1 THEN ' DESC' ELSE '' END
		END
		ELSE IF(@OrderByRole != '')
		BEGIN
			SET @Cmd = @Cmd + '
			ORDER BY
			(
				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, '''', '''''') + '''
			)' + CASE @OrderDesc WHEN 1 THEN ' DESC' ELSE '' END
		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, @Skip int, @Take int', 
			@InstanceId = @InstanceId, 
			@PrincipalId = @PrincipalId,
			@IsAdmin = @IsAdmin,
			--@OrderBy = @OrderByMetadata,
			@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