Plan

dbcc dropcleanbuffers
use m5db;

declare @AccountId INT = 5926,
	@LocationId INT = -1,
	@PartitionId INT = -1

-- Setup.
	DECLARE @roles TABLE (RoleId INT, PersonId INT, AccountId INT, LocationId INT, PRIORITY INT);
	DECLARE @locations UdtIdTable;
    print  convert(varchar(100),GETDATE(),121) ;
	 -- Filling in account's locations.
	 IF (@LocationId > 0)
		INSERT INTO @locations values(@LocationId);
	 ELSE
		INSERT INTO @locations SELECT Id FROM Location WHERE AccountId = @AccountId;
    print  convert(varchar(100),GETDATE(),121) ;
	 -- Locating roles.
	 --INSERT INTO @roles SELECT * FROM dbo.UfnGetRolesByScope(@locations, @AccountId, 0, NULL);
 print  convert(varchar(100),GETDATE(),121) ;
	 -- Loading user data.
	 --SELECT
		--pe.Id AS PersonId,
		--pe.FirstName,
		--pe.LastName,
		--FullName = CASE
		--	WHEN pe.FirstName IS NULL AND pe.LastName IS NULL THEN ''
		--	ELSE LTRIM(RTRIM(COALESCE(pe.FirstName, '') + ' ' + COALESCE(pe.LastName, '')))
		--	END,
		--NULL AS ProfileId,
		--NULL AS ProfileStatusId,
		--'Unassigned' AS InternalCallerId,
		--pe.BusinessEmail,
		--pe.LocationId AS LocationId,
		--pe.AccountId AS AccountId,
		--NULL AS Tn,
		--NULL AS TnCountryId,
		--NULL AS Extension,
		--CAST(0 AS BIT) AS ProfileLocked,
		----CAST(CASE WHEN dbo.UfnlockUserStatus(pe.username, pe.accountid ) = 1 THEN 1 ELSE 0 END AS BIT) AS PersonLocked, -- Special case, fail on first login.
		----CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId = pe.Id AND r.roleId = 1) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsDM, -- Decision maker.
		----CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId = pe.Id AND r.roleId = 2) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsPM, -- Phone manager.
		----CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId = pe.Id AND r.roleId = 3) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsBC, -- Billing contact.
		----CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId = pe.Id AND r.roleId = 4) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsEC, -- Emergency contact.
		----CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId = pe.Id AND r.roleId = 8) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsTC, -- Technical.
		--NULL AS ProfileTypeId,
		--CAST(1 AS BIT) AS IsCloseable,
		--NULL AS PlatformTypeId,
		--NULL AS PartitionId
	 --FROM
		--[Person] pe WITH(NOLOCK)
		--LEFT JOIN Account ac WITH (NOLOCK) ON ac.Id = pe.AccountId
		--LEFT JOIN Location loc WITH (NOLOCK) ON loc.Id = pe.LocationId
		--LEFT JOIN Profile pro WITH(NOLOCK) ON pe.Id = pro.PersonId
	 --WHERE
		--ac.Id = @AccountId
		--AND (pe.LocationId IN (SELECT Id FROM @locations) OR pe.LocationId IS NULL)
		--AND pe.PersonStatusId = 0 -- Only Active.
		--AND pe.IsDeleted = 0 -- Non-deleted.
		--AND pe.PersonTypeId = 1 -- Actual persons.
		--AND (loc.IsDeleted IS NULL OR loc.IsDeleted = 0) -- Check if person's location exists and is not deleted.
		-- --Excluding persons with profiles.
		--AND pe.Id NOT IN (SELECT ipr.PersonId FROM [VwProfileDetails] ipr WITH (NOLOCK) WHERE ipr.AccountId = @AccountId AND ipr.PersonId IS NOT NULL)
		--AND pro.Id is null
	 --UNION ALL
	 -- Persons with profiles.
	 SELECT
		pe.Id AS PersonId,
		pe.FirstName,
		pe.LastName,
		FullName = CASE
			WHEN pe.Id IS NULL THEN 'Unassigned'
			WHEN pe.FirstName IS NULL AND pe.LastName IS NULL THEN COALESCE(pr.InternalCallerId, '')
			ELSE LTRIM(RTRIM(COALESCE(pe.FirstName, '') + ' ' + COALESCE(pe.LastName, '')))
			END,
		pr.Id AS ProfileId,
		pr.ProfileStatusId,
		COALESCE(pr.InternalCallerId, pr.Tn) AS InternalCallerId,
		pe.BusinessEmail,
		pr.LocationId AS LocationId,
		pr.AccountId AS AccountId,
		pr.Tn AS [Tn],
		pr.TnCountryId,
		pr.Extension AS Extension,
		pr.ProfileLocked AS ProfileLocked,
		--pr.PersonLocked AS PersonLocked,
		--CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId IS NOT NULL AND r.personId = pe.Id AND r.roleId = 1) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsDM, -- Decision maker.
		--CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId IS NOT NULL AND r.personId = pe.Id AND r.roleId = 2) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsPM, -- Phone manager.
		--CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId IS NOT NULL AND r.personId = pe.Id AND r.roleId = 3) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsBC, -- Billing contact.
		--CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId IS NOT NULL AND r.personId = pe.Id AND r.roleId = 4) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsEC, -- Emergency contact.
		--CAST((CASE WHEN (SELECT COUNT(*) FROM @roles r WHERE r.personId IS NOT NULL AND r.personId = pe.Id AND r.roleId = 8) > 0 THEN 1 ELSE 0 END) AS BIT) AS IsTC, -- Technical.
		pr.ProfileTypeId AS ProfileTypeId,
		-- US23479 Profile life-cycle is maintained by the Director for sky accounts, we should not allow "close profile".
		CASE WHEN  pr.PlatFormTypeId = 7 THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS IsCloseable, -- Only Premise profiles should be blocked from closing.
		pr.PlatformTypeId,
		pro.PartitionId
	 FROM
		VwProfileDetails pr WITH(NOLOCK)
		LEFT JOIN [Person] pe WITH (NOLOCK) ON pr.PersonId = pe.Id
		LEFT JOIN [Profile] pro WITH (NOLOCK) ON pro.Id = pr.Id
	 WHERE
		-- The filter below was placed there from the GetUserDetails() repository method.
		-- ENG-432057: Voicemail profiles should be displayed in Users grid.
		pr.ProfileTypeId IN (2, 3, 4, 5, 7, 11, 13, 12, 15) -- Managed, Analog, Virtual, Courtesy, Voicemail, Premise, PremiseRoutePoint, PremiseWorkgroup.
		AND
		 pr.LocationId IN (SELECT Id FROM @locations)
		AND pr.AccountId = @AccountId
		AND (@PartitionId <= 0 OR @PartitionId = pro.PartitionId);
		print  convert(varchar(100),GETDATE(),121) ;