Database

ALTER PROCEDURE [dbo].[ChildSearch_sel]
	(
	@UserId INT,
	@AllNamesPattern VARCHAR(250) = NULL,
	@ChildNamePattern VARCHAR(250) = NULL,
	@NicknamePattern VARCHAR(250) = NULL,
	@ChildBirthDate DATETIME = NULL,
	@SiteId INT = NULL,
	@ClassScheduleId INT = NULL,
	@ParentNamePattern VARCHAR(250) = NULL,
	@SchoolYearId INT = 3,
	@StaffMemberId INT = NULL,
	@ContractorId INT = NULL,
	@SubcontractorId INT = NULL,
	@ChildStatuses VARCHAR(20) = NULL,
	@Page INT = 1,
	@RowsPerPage INT = 20,
	@TotalRows INT OUT,
	@SortExpression VARCHAR(50) = 'ChildLastName',
	@SortDirection VARCHAR(15) = 'Ascending',
	@HasHealthFollowUpAlerts BIT = NULL,
	@ChildId INT = NULL
	)
	AS
BEGIN
	DECLARE	@Print VARCHAR(1000),
		@IsAdmin BIT = 0,
		@IsFss BIT = 0,
		@IsTeacher BIT = 0,
		@FilterFamilyMembers BIT = 0,
		@FilterStaff BIT = 0,
		@FamilyMemberIds IdTableType,
		@Filter_ChildIds IdTableType,
		@Nickname VARCHAR(250) = NULL,
		@Nickname_ChildIds IdTableType,
		@StaffIds IdTableType,
		@FssRoleIds IdTableType,
		@TeacherRoleIds IdTableType,
		@RequestedCPS IdTableType,
		@SkipOrgFilterCheck BIT = 0,
		@SchoolYearBeginYear INT,
		@SchoolYearStartDate DATE
		;

	DECLARE	@ROLE_FSS INT = 15,
		@ROLE_FSA INT = 14,
		@ROLE_LT INT = 17,
		@ROLE_AT INT = 1,
		@ROLE_OCS INT = 19,
		
		@PROGSTATUS_PRESCREEN INT = 1,
		@PROGSTATUS_NOPRESCREEN INT = 2,
		@PROGSTATUS_APPLICATION INT = 3,
		@PROGSTATUS_NOAPPLICATION INT = 4,
		@PROGSTATUS_ONWAITLIST INT = 5,
		@PROGSTATUS_ENROLLED INT = 6,
		@PROGSTATUS_PENDINGTRANSFER INT = 7,
		@PROGSTATUS_INCOMPLETEEXIT INT = 8,
		@PROGSTATUS_EXITED INT = 9,
		@PROGSTATUS_PENDINGEXIT INT = 11,
		
		@SRAL_LEADTEACHER INT = 30,
		@SRAL_ASSTEACHER INT = 29,
		
		@FLAG_PRESCREEN INT = 1,
		@FLAG_NOPRESCREEN INT = 2,
		@FLAG_APPLICATION INT = 4,
		@FLAG_NOAPPLICATION INT = 8,
		@FLAG_ONWAITLIST INT = 16,
		@FLAG_ENROLLED INT = 32,
		@FLAG_PENDINGTRANSFER INT = 64,
		@FLAG_INCOMPLETEEXIT INT = 128,
		@FLAG_EXITED INT = 256,
		@FLAG_PENDINGEXIT INT = 512,
		@FLAG_EVERENROLLED INT = 1024;
		
	SELECT	@IsAdmin = dbo.fnIsAdmin(@UserId) | dbo.fnIsDelStaff(@UserId);

	IF @SiteId IS NOT NULL OR @SubcontractorId IS NOT NULL
		SELECT	@ContractorId = NULL;
	IF @SiteId IS NOT NULL
		SELECT	@SubcontractorId = NULL;

	IF @SiteId IS NULL AND @SubcontractorId IS NULL AND @ContractorId IS NULL
		SELECT	@SkipOrgFilterCheck = 1;

	DECLARE	@OrganizationFilter IdTableType,
		@NonSecureOrganizationRelationshipIds IdTableType,
		@SecurityOrganizationRelationshipIds IdTableType;

	IF @SkipOrgFilterCheck = 0
	BEGIN
		PRINT 'Pulling orgs (NOT SECURE) ...';
		INSERT	INTO @NonSecureOrganizationRelationshipIds
		SELECT	OrganizationRelationshipId
		FROM	dbo.fnFilterOrganizationRelationshipsWithExcludeOption(@UserId, @SchoolYearId, 0); --Do not exclude organizations where a user only has child level access.  Security is handled when filtering children

		PRINT 'Filtering orgs (NOT SECURE) check against supplied parameters for future filtering needs in sproc (after children pulled from security check) ...';
		INSERT	INTO @OrganizationFilter
		SELECT	ID
		FROM	dbo.fnProcessOrganizationRelationshipsByType(@UserId, @SchoolYearId, @ContractorId, @SubcontractorId, @SiteId, 'ALL', @NonSecureOrganizationRelationshipIds);
	END;

	SELECT	@Print = 'Parse requested CPS (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	INSERT	INTO @RequestedCPS
	SELECT	CONVERT(INT, Element)
	FROM	dbo.fnParseString(',', @ChildStatuses);

	--TODO: Have the filtering below for both FM and Staff bring in SchoolYear to limit the results.
	IF @AllNamesPattern IS NOT NULL OR @ChildNamePattern IS NOT NULL OR @ParentNamePattern IS NOT NULL	
	BEGIN
		SELECT	@FilterFamilyMembers = 1;
		
		DECLARE @FamilyMemberSearchString VARCHAR(250) = NULL;		
		
		IF @AllNamesPattern IS NOT NULL
			SELECT	@FamilyMemberSearchString = '%' + @AllNamesPattern + '%';
		ELSE IF @ChildNamePattern IS NOT NULL
			SELECT	@FamilyMemberSearchString = '%' + @ChildNamePattern + '%';
		ELSE IF @ParentNamePattern IS NOT NULL
			SELECT	@FamilyMemberSearchString = '%' + @ParentNamePattern + '%';							
			
		IF @AllNamesPattern IS NOT NULL
		BEGIN
			SELECT	@Print = 'Filtering name information from FamilyMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
			RAISERROR(@Print, 10, 1) WITH NOWAIT;
			INSERT	INTO @FamilyMemberIds
			SELECT	FamilyMemberId
			FROM	FamilyMember
			WHERE	IsDeleted = 0
			AND	(
					RTRIM(LTRIM(LegalFirstName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(LegalLastName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(LegalFirstName)) + ' ' + RTRIM(LTRIM(LegalLastName)) LIKE @FamilyMemberSearchString
				);
		END;
		ELSE IF @ChildNamePattern IS NOT NULL
		BEGIN
			SELECT	@Print = 'Filtering name information from FamilyMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
			RAISERROR(@Print, 10, 1) WITH NOWAIT;
			INSERT	INTO @FamilyMemberIds
			SELECT	DISTINCT
				FM.FamilyMemberId
			FROM	FamilyMember FM
				INNER JOIN Child C
					ON C.FamilyMemberId = FM.FamilyMemberId
			WHERE	FM.IsDeleted = 0
			AND	(
					RTRIM(LTRIM(FM.LegalFirstName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalFirstName)) + ' ' + RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				)
		END;
		ELSE IF @ParentNamePattern IS NOT NULL
		BEGIN
			SELECT	@Print = 'Filtering name information from FamilyMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
			RAISERROR(@Print, 10, 1) WITH NOWAIT;
			INSERT	INTO @FamilyMemberIds
			SELECT	FM.FamilyMemberId
			FROM	FamilyMember FM
			WHERE	FM.IsDeleted = 0
			AND	NOT EXISTS	(
							SELECT	1
							FROM	Child
							WHERE	FamilyMemberId = FM.FamilyMemberId
							)
			AND	(
					RTRIM(LTRIM(FM.LegalFirstName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalFirstName)) + ' ' + RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				);
		END;				
		
		SELECT	@Print = 'Moving FamilyMember matches to child (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		MERGE	@FamilyMemberIds IDS
		USING	Child C
		ON	(C.FamilyMemberId = IDS.ID)
		
		WHEN MATCHED
			THEN DELETE
			
		OUTPUT
			C.ChildId
			INTO @Filter_ChildIds;
				
		SELECT	@Print = 'Pulling ChildId from FamilyMembers (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Filter_ChildIds
		SELECT	DISTINCT
			FAM.ChildId
		FROM	@FamilyMemberIds IDS
			INNER JOIN HouseholdFamilyMember HFM
				ON HFM.IsDeleted = 0
				AND HFM.FamilyMemberId = IDS.ID
			INNER JOIN Household HSHLD
				ON HSHLD.HouseholdId = HFM.HouseholdId
			INNER JOIN Family FAM
				ON FAM.FamilyId = HSHLD.FamilyId
		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	@Filter_ChildIds
							WHERE	ID = FAM.ChildId
							);
	END;

	IF @AllNamesPattern IS NOT NULL OR @StaffMemberId IS NOT NULL
	BEGIN
		SELECT	@FilterStaff = 1;
		
		DECLARE @StaffSearchString VARCHAR(250) = NULL;
		
		IF @AllNamesPattern IS NOT NULL
			SELECT	@StaffSearchString = '%' + @AllNamesPattern + '%';
		--ELSE IF @StaffName IS NOT NULL
		--	SELECT	@StaffSearchString = '%' + @StaffName + '%';
			
		SELECT	@Print = 'Filtering name information from StaffMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @StaffIds
		SELECT	StaffMemberid
		FROM	StaffMember
		WHERE	IsDeleted = 0
		AND	(
				@StaffMemberId = StaffMemberId
			OR	(
					@StaffMemberId IS NULL
				AND	(
						RTRIM(LTRIM(FirstName)) LIKE @StaffSearchString
					OR	RTRIM(LTRIM(LastName)) LIKE @StaffSearchString
					OR	RTRIM(LTRIM(FirstName)) + ' ' + RTRIM(LTRIM(LastName)) LIKE @StaffSearchString
					)
				)
			);
		
		SELECT	@Print = 'Pulling ChildId from Family Support staff (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Filter_ChildIds
		SELECT	DISTINCT
			CCLS.ChildId
		FROM	@StaffIds IDS
			INNER JOIN ChildClass CCLS
				ON CCLS.FamilySupportSpecialistStaffId = IDS.ID
				AND CCLS.IsDeleted = 0
		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	@Filter_ChildIds
							WHERE	ID = CCLS.ChildId
							);
	
		
		SELECT	@Print = 'Pulling ChildId from Teaching staff (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Filter_ChildIds
		SELECT	DISTINCT
			CCLS.ChildId
		FROM	@StaffIds IDS
			INNER JOIN StaffClass STFCLS
				ON STFCLS.StaffMemberId = IDS.ID
				AND STFCLS.IsDeleted = 0
			INNER JOIN ChildClass CCLS
				ON CCLS.IsDeleted = 0
				AND CCLS.ClassScheduleId = STFCLS.ClassScheduleId
		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	@Filter_ChildIds
							WHERE	ID = CCLS.ChildId
							);
	END;
	
	IF @NicknamePattern IS NOT NULL
	BEGIN
		SET @Nickname= '' 			
		INSERT INTO @Nickname_ChildIds
		SELECT DISTINCT ChildId FROM Child C
		WHERE Nickname LIKE @NicknamePattern AND IsDeleted=0
		SELECT * FROM @Nickname_ChildIds		
	END	
		
	DECLARE	@Security_VisibleChildIds IdTableType;

	IF @IsAdmin = 0
	BEGIN
		SELECT	@Print = 'Non-Admin -- pulling children visible from security (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Security_VisibleChildIds
		SELECT	ChildId
		FROM	dbo.fnFilterChildren(@UserId, @SchoolYearId);
	END;
		
	CREATE TABLE #PreResults
		(
		ChildId INT NOT NULL PRIMARY KEY CLUSTERED,
		ChildFirstName VARCHAR(150) NOT NULL,
		ChildLastName VARCHAR(150) NOT NULL,
		CHildNickname VARCHAR(250) NULL, 
		ChildAge INT NOT NULL,
		SiteIds VARCHAR(250) NULL,
		Site VARCHAR(500) NULL,
		ClassScheduleId INT NULL,
		Class VARCHAR(500) NULL,
		TeacherIds VARCHAR(75) NULL,
		TeacherName VARCHAR(500) NULL,
		ChildStatusId INT NULL,
		ChildStatus VARCHAR(50) NULL,
		HasAlerts BIT NOT NULL DEFAULT(0),
		IsExpired BIT NOT NULL DEFAULT(0),
		IsReadOnly BIT NOT NULL DEFAULT(0),
		PrescreenId INT NULL,
		ApplicationDataId INT NULL,
		ChildStatusFlags INT NOT NULL,
		PriorityPoints INT NULL,
		SortKey VARCHAR(100) NOT NULL,
		ClassStarted BIT NULL DEFAULT(0)
		);
		
	CREATE INDEX #IX_PreResults_SortKey ON #PreResults (SortKey);
		
	--Mirror #PreResults table schema into #Results
	SELECT	*
		INTO #Results
	FROM	#PreResults;
	
	ALTER TABLE #Results
		ADD PRIMARY KEY CLUSTERED (ChildId);

	--Get School Start Date
	SELECT @SchoolYearBeginYear = DatePart(year,BeginDate)
	FROM SchoolYear 
	WHERE SchoolYearId = @SchoolYearId

	SET @SchoolYearStartDate = '08/31/' + CONVERT(VARCHAR,@SchoolYearBeginYear);
		
	SELECT	@Print = 'Pulling initial list of children (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	INSERT	INTO #PreResults
		(ChildId, ChildFirstName, ChildLastName, CHildNickname, ChildAge, ChildStatusFlags, SortKey)
	SELECT	C.ChildId,
		FM.LegalFirstName,
		FM.LegalLastName,
		C.Nickname,
		dbo.fnCalculateAge(FM.BirthDate,@SchoolYearStartDate) as ChildAge,
		CPS.StatusFlag,
		CASE @SortExpression
				WHEN 'ChildFirstName' THEN LTRIM(FM.LegalFirstName) + CONVERT(VARCHAR, C.ChildId)
				WHEN 'ChildLastName' THEN LTRIM(FM.LegalLastName) + CONVERT(VARCHAR, C.ChildId)
				WHEN 'ChildAge' THEN CONVERT(VARCHAR,CONVERT(DATE,FM.BirthDate))
				WHEN 'ChildStatus' THEN RIGHT('0000000000' + CONVERT(VARCHAR, CPS.StatusFlag), 10) + CONVERT(VARCHAR, C.ChildId)
				ELSE RIGHT('0000000000' + CONVERT(VARCHAR, C.ChildId), 10)
			END AS SortKey
	FROM	Child C
		INNER JOIN FamilyMember FM
			ON FM.FamilyMemberId = C.FamilyMemberId
		INNER JOIN vwChildProgramStatus CPS
			ON CPS.ChildId = C.ChildId
			AND CPS.SchoolYearId = @SchoolYearId
	WHERE	C.IsDeleted = 0
	AND	(
			@ChildId IS NULL
		OR	C.ChildId = @ChildId
		)
	AND	(
			@SkipOrgFilterCheck = 1
		OR	EXISTS	(
					SELECT	1
					FROM	vwChildOrganization CHILDORG WITH (NOEXPAND)
					WHERE	CHILDORG.SchoolYearId = @SchoolYearId
					AND	CHILDORG.ChildId = C.ChildId
					AND	EXISTS	(
								SELECT	1
								FROM	@OrganizationFilter
								WHERE	ID = CHILDORG.OrganizationRelationshipId
								)
					)
		)
	AND	(
			@ClassScheduleId IS NULL
		OR	EXISTS	(
					SELECT	1
					FROM	ChildClass CCLS
					WHERE	CCLS.ClassScheduleId = @ClassScheduleId
					AND	CCLS.IsExited = 0
					AND	CCLS.IsActive = 1
					AND	CCLS.IsDeleted = 0
					AND	CCLS.ChildId = C.ChildId
					)
		)
	AND	(
			(
				@IsAdmin = 1
			AND	EXISTS	(
						SELECT	1
						FROM	vwChildProgramStatus WITH (NOEXPAND)
						WHERE	SchoolYearId = @SchoolYearId
						AND	ChildId = C.ChildId
						)
			)
		OR	(
				@IsAdmin = 0
			AND	EXISTS	(
						SELECT	1
						FROM	@Security_VisibleChildIds
						WHERE	ID = C.ChildId
						)
			)
		)
	AND	(
			@ChildBirthDate IS NULL
		OR	FM.BirthDate = @ChildBirthDate
		)
	AND	(
			(
				@FilterFamilyMembers = 0
			AND	@FilterStaff = 0
			)
		OR	EXISTS	(
					SELECT	1
					FROM	@Filter_ChildIds
					WHERE	ID = C.ChildId
					)
		)
	AND	(
		NOT EXISTS	(
					SELECT	1
					FROM	@Nickname_ChildIds
					)
		OR	
		EXISTS	(
					SELECT	1
					FROM	@Nickname_ChildIds NC
					WHERE	 NC.ID = C.ChildId
					)
		);

	SELECT	@TotalRows = @@ROWCOUNT;

	SET NOCOUNT ON;

	DECLARE	@ReturnEnrolled TINYINT = 0,
		@ReturnWaitListed TINYINT = 0,
		@ReturnExited TINYINT = 0,
		@ReturnPendingExit TINYINT = 0,
		@ReturnPendingTransfer TINYINT = 0,
		@ReturnIncompleteExit TINYINT = 0,
		@ReturnCompleteApp TINYINT = 0,
		@ReturnIncompleteApp TINYINT = 0,
		@ReturnCompletePre TINYINT = 0,
		@ReturnIncompletePre TINYINT = 0,
		
		@AlwaysExcludeEnrolled TINYINT = 0;
		
	IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_NOPRESCREEN)
		SELECT	@ReturnIncompletePre = 1;
	ELSE IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_PRESCREEN)
		SELECT	@ReturnCompletePre = 1;

	ELSE IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_NOAPPLICATION)
		SELECT	@ReturnIncompleteApp = 1;
	ELSE IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_APPLICATION)
		SELECT	@ReturnCompleteApp = 1;
		
	IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_ONWAITLIST)
		SELECT	@ReturnWaitListed = 1;

	SELECT	@AlwaysExcludeEnrolled =
		CASE WHEN @ReturnIncompletePre + @ReturnCompletePre + @ReturnIncompleteApp + @ReturnCompleteApp > 0 THEN 1 ELSE 0 END

	--The stuff above is exclusive; if they select one of those, they want kids that are just in that status.  The ones
	--below can be inclusive.
	IF @AlwaysExcludeEnrolled = 0
	BEGIN
		IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_ENROLLED)
			SELECT	@ReturnEnrolled = 1;
		ELSE
		BEGIN
			IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_PENDINGEXIT)
				SELECT	@ReturnPendingExit = 1;

			IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_PENDINGTRANSFER)
				SELECT	@ReturnPendingTransfer = 1;

			IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_INCOMPLETEEXIT)
				SELECT	@ReturnIncompleteExit = 1;
		END;

		IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_EXITED)
			SELECT	@ReturnExited = 1;
	END;

	SET NOCOUNT OFF;

	IF EXISTS (SELECT 1 FROM @RequestedCPS)
	BEGIN
		SELECT	@Print = 'Applying CPS filter(s) (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		DELETE	RSLT
		FROM	#PreResults RSLT
		WHERE	(
					@ReturnIncompletePre = 0
				OR	@ReturnWaitListed = 1
				OR	(
						@ReturnIncompletePre = 1
					AND	@ReturnWaitListed = 0
					AND	(
							ChildStatusFlags & @FLAG_NOPRESCREEN = 0 --Remove anyone that does not have an incomplete prescreen
						OR	ChildStatusFlags & (@FLAG_PRESCREEN | @FLAG_APPLICATION | @FLAG_NOAPPLICATION | @FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past incomplete prescreen
						)
					)
				)
		AND	(
				@ReturnCompletePre = 0
			OR	@ReturnWaitListed = 1
			OR	(
					@ReturnCompletePre = 1
				AND	@ReturnWaitListed = 0
				AND	(
						ChildStatusFlags & @FLAG_PRESCREEN = 0 --Remove anyone that does not have a complete prescreeb
					OR	ChildStatusFlags & (@FLAG_APPLICATION | @FLAG_NOAPPLICATION | @FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past complete prescreen
					)
				)
			)
		AND	(
				@ReturnIncompleteApp = 0
			OR	@ReturnWaitListed = 1
			OR	(
					@ReturnIncompleteApp = 1
				AND	@ReturnWaitListed = 0
				AND	(
						ChildStatusFlags & @FLAG_NOAPPLICATION = 0 --Remove anyone that does not have an incomplete application
					OR	ChildStatusFlags & (@FLAG_APPLICATION | @FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past incomplete application
					)
				)
			)
		AND	(
				@ReturnCompleteApp = 0
			OR	@ReturnWaitListed = 1
			OR	(
					@ReturnCompleteApp = 1
				AND	@ReturnWaitListed = 0
				AND	(
						ChildStatusFlags & @FLAG_APPLICATION = 0 --Remove anyone that does not have a complete application
					OR	ChildStatusFlags & (@FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past complete application
					)
				)
			)

		--Wait List AND Pre/App
		AND	(
					@ReturnWaitListed = 0
			OR	(
					@ReturnCompleteApp = 0
				AND	@ReturnIncompleteApp = 0
				AND	@ReturnCompletePre = 0
				AND	@ReturnIncompletePre = 0
				)
			OR	(
					@ReturnWaitListed = 1
				AND	(@ReturnCompleteApp = 1 OR @ReturnIncompleteApp = 1 OR @ReturnCompletePre = 1 OR @ReturnIncompletePre = 1)
				AND	(
						ChildStatusFlags & (@FLAG_ONWAITLIST | 
							CASE WHEN @ReturnIncompletePre = 1 THEN @FLAG_NOPRESCREEN ELSE 0 END |
							CASE WHEN @ReturnCompletePre = 1 THEN @FLAG_PRESCREEN ELSE 0 END |
							CASE WHEN @ReturnIncompleteApp = 1 THEN @FLAG_NOAPPLICATION ELSE 0 END |
							CASE WHEN @ReturnCompleteApp = 1 THEN @FLAG_APPLICATION ELSE 0 END
							) != (@FLAG_ONWAITLIST | 
								CASE WHEN @ReturnIncompletePre = 1 THEN @FLAG_NOPRESCREEN ELSE 0 END |
								CASE WHEN @ReturnCompletePre = 1 THEN @FLAG_PRESCREEN ELSE 0 END |
								CASE WHEN @ReturnIncompleteApp = 1 THEN @FLAG_NOAPPLICATION ELSE 0 END |
								CASE WHEN @ReturnCompleteApp = 1 THEN @FLAG_APPLICATION ELSE 0 END
								)
					OR	ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past wait list
					)
				)
			)
			
		--JUST Wait List
		AND	(
				@ReturnWaitListed = 0
			OR	(@ReturnCompleteApp = 1 OR @ReturnIncompleteApp = 1 OR @ReturnCompletePre = 1 OR @ReturnIncompletePre = 1)
			OR	(
					@ReturnWaitListed = 1
				AND	ChildStatusFlags & @FLAG_ONWAITLIST = 0
				)
			)
		AND	(
				@ReturnEnrolled = 0
			OR	(
					@ReturnEnrolled = 1
				AND	ChildStatusFlags & @FLAG_ENROLLED = 0
				)
			)
		AND	(
				@ReturnExited = 0
			OR	(
					@ReturnExited = 1
				AND	ChildStatusFlags & @FLAG_EXITED = 0
				)
			)
		AND	(
				@ReturnPendingExit = 0
			OR	(
					@ReturnPendingExit = 1
				AND	ChildStatusFlags & @FLAG_PENDINGEXIT = 0
				)
			)
		AND	(
				@ReturnPendingTransfer = 0
			OR	(
					@ReturnPendingTransfer = 1
				AND	ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0
				)
			)
		AND	(
				@ReturnIncompleteExit = 0
			OR	(
					@ReturnIncompleteExit = 1
				AND	ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0
				)
			);
	END;

	SELECT	@TotalRows = @TotalRows - @@ROWCOUNT;

	PRINT 'Setting HasAlerts flag for children ...';
	UPDATE	RSLT
	SET	HasAlerts = 1
	FROM	#PreResults RSLT
	WHERE	EXISTS	(
					SELECT	1
					FROM	ChildHealthFollowUpAlert
					WHERE	ChildId = RSLT.ChildId
					AND	IsDeleted = 0
					AND	SchoolYearId = @SchoolYearId
					AND	GETDATE() BETWEEN EffectiveDate AND COALESCE(ExpirationDate, CONVERT(DATETIME, '12/31/9999'))
					);

	IF @HasHealthFollowUpAlerts IS NOT NULL
	BEGIN
		SELECT	@Print = 'Filter by passed in @HasHealthFollowUpAlerts (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		DELETE
		FROM	#PreResults
		WHERE	HasAlerts != @HasHealthFollowUpAlerts;

		SELECT	@TotalRows = @TotalRows - @@ROWCOUNT;
	END;

	--Check for users who have children on their list that are in the middle of a records transfer, and the contractor of the
	--user querying the data is the "to contractor" in that records transfer.  Those kids will get short-circuited so the new
	--contractor cannot exit, transfer, etc.
	IF @IsAdmin = 0
	BEGIN
		DECLARE	@OrganizationRelationshipIdsUserHasAccessTo IdTableType;
		INSERT	INTO @OrganizationRelationshipIdsUserHasAccessTo
		SELECT	OrganizationRelationshipId
		FROM	dbo.fnFilterOrganizationRelationships(@UserId, @SchoolYearId);

		UPDATE	RSLT
		SET	ChildStatusFlags = RSLT.ChildStatusFlags ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0 THEN 0 ELSE @FLAG_PENDINGTRANSFER END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT = 0 THEN 0 ELSE @FLAG_PENDINGEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0 THEN 0 ELSE @FLAG_INCOMPLETEEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED = 0 THEN 0 ELSE @FLAG_ENROLLED END ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_EXITED = 0 THEN 0 ELSE @FLAG_EXITED END ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST = 0 THEN 0 ELSE @FLAG_ONWAITLIST END,
			IsReadOnly = 1
		FROM	#PreResults RSLT
			INNER JOIN ChildTransferRequest RECTRANSFER
				ON RECTRANSFER.IsDeleted = 0
				AND RECTRANSFER.ChildId = RSLT.ChildId
				AND RECTRANSFER.ReadyToTransfer = 1	--Records transfer has occurred
				AND RECTRANSFER.IsProcessed = 0		--Classroom Transfer *or* Add to Waiting List has not occurred
			INNER JOIN @OrganizationRelationshipIdsUserHasAccessTo ORGSACCESS
				ON ORGSACCESS.ID = RECTRANSFER.ToOrganizationRelationshipId;
	END;

	--Note: if we're not sorting by site, class, or teacher, we can do paging right now and save a lot of processing
	--trying to create those concatenated lists!
	IF @TotalRows > @RowsPerPage AND @SortExpression NOT IN ('TeacherName', 'Class', 'Site')
	BEGIN
		--Paging: http://www.4guysfromrolla.com/webtech/042606-1.shtml
			--Also, maybe consider: http://stackoverflow.com/questions/3212692/sql-performance-where-vs-whererow-number/3215973#3215973
		DECLARE	@SortKeyToStart VARCHAR(100),
			@StartRow INT = (@RowsPerPage * (@Page - 1) + 1);

		SET ROWCOUNT @StartRow;
		SELECT	@SortKeyToStart = SortKey
		FROM	#PreResults
		ORDER BY
			CASE WHEN @SortDirection = 'Descending' THEN
				SortKey
			ELSE
				NULL
			END DESC,
			CASE WHEN @SortDirection = 'Ascending' THEN
				SortKey
			ELSE
				NULL
			END ASC;

		SET ROWCOUNT @RowsPerPage;

		INSERT	INTO #Results
		SELECT	*
		FROM	#PreResults
		WHERE	(
					@SortDirection = 'Ascending'
				AND	SortKey >= @SortKeyToStart
				)
		OR	(
				@SortDirection = 'Descending'
			AND	SortKey <= @SortKeyToStart
			)
		ORDER BY
			CASE WHEN @SortDirection = 'Descending' THEN
				SortKey
			ELSE
				NULL
			END DESC,
			CASE WHEN @SortDirection = 'Ascending' THEN
				SortKey
			ELSE
				NULL
			END ASC;

		SET ROWCOUNT 0;
	END;
	ELSE
		INSERT	INTO #Results
		SELECT	*
		FROM	#PreResults;
	
	IF @IsAdmin = 0
	BEGIN
		DECLARE	@OrgsWithChildOrGreaterAccess IdTableType,
			@INCLUDECHILDACCESS BIT = 0;
		INSERT	INTO @OrgsWithChildOrGreaterAccess
		SELECT	OrganizationRelationshipId
		FROM	dbo.fnFilterOrganizationRelationshipsWithExcludeOption(@UserId, @SchoolYearId, @INCLUDECHILDACCESS);
		
		--Force the expired kids (that were enrolled or exited) to exited
		WITH CTE AS
			(
			SELECT	RSLT.ChildId
			FROM	#Results RSLT
				INNER JOIN ChildOrganization CHILDORG
					ON CHILDORG.ChildId = RSLT.ChildId
					AND CHILDORG.IsDeleted = 0
				INNER JOIN OrganizationRelationship ORGREL
					ON ORGREL.OrganizationRelationshipId = CHILDORG.OrganizationRelationshipId
					AND ORGREL.IsDeleted = 0
					AND ORGREL.IsActive = 1
					AND ORGREL.SchoolYearId = @SchoolYearId
				INNER JOIN @OrgsWithChildOrGreaterAccess SEC
					ON SEC.ID = CHILDORG.OrganizationRelationshipId
			WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) > 0
			GROUP BY
				RSLT.ChildId
			HAVING
				MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 0 ELSE 1 END) > 0
			AND	MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 1 ELSE 0 END) = 0
			)
		UPDATE	RSLT
		SET	ChildStatusFlags = RSLT.ChildStatusFlags ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST = 0 THEN 0 ELSE @FLAG_ONWAITLIST END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0 THEN 0 ELSE @FLAG_PENDINGTRANSFER END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT = 0 THEN 0 ELSE @FLAG_PENDINGEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0 THEN 0 ELSE @FLAG_INCOMPLETEEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED = 0 THEN 0 ELSE @FLAG_ENROLLED END | @FLAG_EXITED,
			IsExpired = 1
		FROM	#Results RSLT
			INNER JOIN CTE
				ON CTE.ChildId = RSLT.ChildId
		WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) > 0;
		
		--Force the expired kids (that were NEVER enrolled or exited) to complete prescreen/incomplete prescreen/complete app/incomplete app
		WITH CTE AS
			(
			SELECT	RSLT.ChildId
			FROM	#Results RSLT
				INNER JOIN ChildOrganization CHILDORG
					ON CHILDORG.ChildId = RSLT.ChildId
					AND CHILDORG.IsDeleted = 0
				INNER JOIN OrganizationRelationship ORGREL
					ON ORGREL.OrganizationRelationshipId = CHILDORG.OrganizationRelationshipId
					AND ORGREL.IsDeleted = 0
					AND ORGREL.IsActive = 1
					AND ORGREL.SchoolYearId = @SchoolYearId
				INNER JOIN @OrgsWithChildOrGreaterAccess SEC
					ON SEC.ID = CHILDORG.OrganizationRelationshipId
			WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) = 0
			GROUP BY
				RSLT.ChildId
			HAVING
				MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 0 ELSE 1 END) > 0
			AND	MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 1 ELSE 0 END) = 0
			)
		UPDATE	RSLT
		SET	ChildStatusFlags = RSLT.ChildStatusFlags ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST = 0 THEN 0 ELSE @FLAG_ONWAITLIST END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0 THEN 0 ELSE @FLAG_PENDINGTRANSFER END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT = 0 THEN 0 ELSE @FLAG_PENDINGEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0 THEN 0 ELSE @FLAG_INCOMPLETEEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_EXITED = 0 THEN 0 ELSE @FLAG_EXITED END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED = 0 THEN 0 ELSE @FLAG_ENROLLED END,
			IsExpired = 1
		FROM	#Results RSLT
			INNER JOIN CTE
				ON CTE.ChildId = RSLT.ChildId
		WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) = 0;
	END;

	CREATE TABLE #NameConcat
		(
		AutoId INT NOT NULL IDENTITY(1, 1),
		ChildId INT NOT NULL,
		ClassScheduleId INT NULL,
		SiteIds VARCHAR(250) NOT NULL,
		TeacherIds VARCHAR(250) NULL,
		ClassName VARCHAR(150),
		SiteName VARCHAR(1000),
		TeacherName VARCHAR(1000) NULL,
		TeacherSortKey TINYINT DEFAULT(1),
		ClassStarted BIT NULL DEFAULT(0),
		SchoolYearId INT NOT NULL,
		PRIMARY KEY CLUSTERED (ChildId, AutoId, TeacherSortKey)
		);

	IF NOT EXISTS (SELECT 1 FROM @RequestedCPS) OR (@ReturnEnrolled +  @ReturnPendingExit + @ReturnPendingTransfer + @ReturnIncompleteExit) > 0
	BEGIN
		SELECT	@Print = 'Pull sites, classrooms, and teachers for enrolled children (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO #NameConcat
			(ChildId, ClassScheduleId, SiteIds, ClassName, SiteName, TeacherIds, TeacherName, TeacherSortKey, ClassStarted, SchoolYearId)
		SELECT	RSLT.ChildId,
			SCHED.ClassScheduleId,
			SCHED.OrganizationRelationshipId AS SiteId,
			SCHED.Name AS ClassName,
			SITE.Name AS SiteName,
			CLASSINFO.TeacherId,
			CLASSINFO.TeacherName,
			CASE
				WHEN CLASSINFO.IsLeadTeacher IS NULL THEN 4
				WHEN CLASSINFO.IsLeadTeacher = 1 THEN 2
				ELSE 3
			END AS TeacherSortKey,
			CASE WHEN SCHED.StartDate <= CONVERT(VARCHAR, GETDATE(), 101) THEN 1 ELSE 0 END AS ClassStarted,
			SITE.SchoolYearId
		FROM	#Results RSLT
			INNER JOIN ChildClass CCLS
				ON CCLS.IsDeleted = 0
				AND CCLS.IsActive = 1
				AND CCLS.IsExited = 0
				AND CCLS.ChildId = RSLT.ChildId
			INNER JOIN ClassSchedule SCHED
				ON SCHED.ClassScheduleId = CCLS.ClassScheduleId
			INNER JOIN OrganizationRelationship SITE
				ON SITE.OrganizationRelationshipId = SCHED.OrganizationRelationshipId
			LEFT OUTER JOIN vwClassTeachersPresentForAllHours CLASSINFO --WITH (NOEXPAND)
				ON CLASSINFO.ClassScheduleId = CCLS.ClassScheduleId
		WHERE	RSLT.IsExpired = 0
		AND	(RSLT.ChildStatusFlags & @FLAG_ENROLLED) > 0
		ORDER BY
			ChildId,
			TeacherSortKey;
	END;

	IF NOT EXISTS (SELECT 1 FROM @RequestedCPS) OR
		((@ReturnEnrolled +  @ReturnPendingExit + @ReturnPendingTransfer + @ReturnIncompleteExit) = 0 AND (@ReturnWaitListed + @ReturnExited) > 0)
	BEGIN
		SELECT	@Print = 'Pull sites for wait listed children (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO #NameConcat
			(ChildId, SiteIds, SiteName, SchoolYearId)
		SELECT	RSLT.ChildId,
			ORGREL.OrganizationRelationshipId,
			ORGREL.Name,
			ORGREL.SchoolYearId
		FROM	#Results RSLT
			INNER JOIN ChildWaitList CWL
				ON CWL.IsDeleted = 0
				AND CWL.ChildId = RSLT.ChildId
			INNER JOIN WaitList WL
				ON WL.WaitListId = CWL.WaitListId
				AND WL.IsDeleted = 0
			INNER JOIN OrganizationRelationship ORGREL
				ON ORGREL.OrganizationRelationshipId = WL.SiteId
				AND ORGREL.SchoolYearId = @SchoolYearId
				AND ORGREL.IsDeleted = 0
		WHERE	RSLT.IsExpired = 0
		AND	(RSLT.ChildStatusFlags & (@FLAG_ONWAITLIST | @FLAG_ENROLLED)) > 0
		AND	NOT EXISTS	(
						SELECT	1
						FROM	#NameConcat
						WHERE	ChildId = RSLT.ChildId
						);
	END;

	--Quirky update
	DECLARE	@PrevChildId INT,
		@SiteName VARCHAR(1000),
		@SiteIds VARCHAR(250),
		@TeacherName VARCHAR(1000),
		@TeacherIds VARCHAR(250);

	SELECT	@Print = 'Roll up site names for children in multiple sites (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	UPDATE	#NameConcat
	SET	@SiteName = SiteName =
			CASE WHEN ClassScheduleId IS NULL AND ChildId = @PrevChildId THEN @SiteName + ', ' + SiteName
			ELSE SiteName END,
		@SiteIds = SiteIds =
			CASE WHEN ClassScheduleId IS NULL AND ChildId = @PrevChildId THEN @SiteIds + ',' + SiteIds
			ELSE SiteIds END,
		@TeacherName = TeacherName =
			CASE WHEN ClassScheduleId IS NOT NULL AND ChildId = @PrevChildId THEN @TeacherName + ', ' + TeacherName
			ELSE TeacherName END,
		@TeacherIds = TeacherIds =
			CASE WHEN ClassScheduleId IS NOT NULL AND ChildId = @PrevChildId THEN @TeacherIds + ',' + TeacherIds
			ELSE TeacherIds END,
		@PrevChildId = ChildId
	FROM	#NameConcat
	WHERE @SchoolYearId IS NULL OR SchoolYearId = @SchoolYearId
	OPTION (MAXDOP 1);

	SELECT	@Print = 'Move joined site names to main result set (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	WITH MAXIDS AS
		(
		SELECT	ChildId,
			MAX(AutoId) AS MaxAutoId
		FROM	#NameConcat
		WHERE @SchoolYearId IS NULL OR SchoolYearId = @SchoolYearId
		GROUP BY
			ChildId
		),
		NAMES AS
		(
		SELECT	DATA.ChildId,
			DATA.SiteIds,
			DATA.SiteName,
			DATA.ClassScheduleId,
			DATA.ClassName,
			DATA.TeacherIds,
			DATA.TeacherName,
			DATA.ClassStarted
		FROM	#NameConcat DATA
			INNER JOIN MAXIDS
				ON MAXIDS.ChildId = DATA.ChildId
				AND MAXIDS.MaxAutoId = DATA.AutoId
		)
	UPDATE	RSLT
	SET	SiteIds = NAMES.SiteIds,
		Site = NAMES.SiteName,
		ClassScheduleId = NAMES.ClassScheduleId,
		Class = NAMES.ClassName,
		TeacherIds = NAMES.TeacherIds,
		TeacherName = NAMES.TeacherName,
		ClassStarted = NAMES.ClassStarted
	FROM	#Results RSLT
		INNER JOIN NAMES
			ON NAMES.ChildId = RSLT.ChildId
	WHERE	RSLT.IsExpired = 0;

	--Note: we're sorting by site, class, or teacher, so we had to wait until we pulled the data for the entire state
	--before pulling out our page ...
	IF @SortExpression IN ('TeacherName', 'Class', 'Site')
	BEGIN
		UPDATE	#Results
		SET	SortKey = CASE @SortExpression
				WHEN 'TeacherName' THEN COALESCE(LEFT(TeacherName, 75), '') + CONVERT(VARCHAR, ChildId)
				WHEN 'Class' THEN COALESCE(LEFT(Class, 75), '') + CONVERT(VARCHAR, ChildId)
				WHEN 'Site' THEN COALESCE(LEFT(Site, 75), '') + CONVERT(VARCHAR, ChildId)
				ELSE SortKey
			END;
			
		CREATE INDEX #IX_Results_SortKey ON #Results (SortKey);
	
		DECLARE	@DeleteUntilRow INT = (@RowsPerPage * (@Page - 1));

		WITH CTE AS
			(
			SELECT	TOP (@DeleteUntilRow)
				*
			FROM	#Results RSLT
			ORDER BY
				CASE WHEN @SortDirection = 'Descending' THEN
					SortKey
				ELSE
					NULL
				END DESC,
				CASE WHEN @SortDirection = 'Ascending' THEN
					SortKey
				ELSE
					NULL
				END ASC
			)
		DELETE
		FROM	CTE;
	END;

	SELECT	@Print = 'Pulling PrescreenId (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	UPDATE	RSLT
	SET	PrescreenId = P.PrescreenId,
		PriorityPoints = PDATA.PriorityPoints
	FROM	#Results RSLT
		INNER JOIN Prescreen P
			ON P.ChildId = RSLT.ChildId
			AND P.IsDeleted = 0
		INNER JOIN PrescreenData PDATA
			ON PDATA.PrescreenId = P.PrescreenId
			AND PDATA.IsDeleted = 0;

	SELECT	@Print = 'Pulling ApplicationDataId (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	WITH CTE AS
		(
		SELECT	RSLT.ChildId,
			ADATA.ApplicationDataId,
			CASE
				WHEN ADATA.IsComplete = 1 OR ADATA.PriorityPoints > RSLT.PriorityPoints
					THEN ADATA.PriorityPoints
				ELSE
					RSLT.PriorityPoints 
			END AS PriorityPoints,
			ROW_NUMBER() OVER	(
								PARTITION BY RSLT.ChildId
								ORDER BY CASE
									WHEN ADATA.SchoolYearId = 4 AND ADATA.IsComplete = 1 THEN -1000000
									WHEN ADATA.IsComplete = 1 THEN ADATA.ApplicationDataId * -1
									WHEN ADATA.SchoolYearId = 4 THEN 0 
									ELSE ADATA.ApplicationDataId 
								END) AS AppDataRank
		FROM	#Results RSLT
			INNER JOIN Application APP
				ON APP.ChildId = RSLT.ChildId
				AND APP.IsDeleted = 0
			INNER JOIN ApplicationData ADATA
				ON ADATA.ApplicationId = APP.ApplicationId
				AND ADATA.IsDeleted = 0
		)
	UPDATE	RSLT
	SET	ApplicationDataId = CTE.ApplicationDataId,
		PriorityPoints = CTE.PriorityPoints
	FROM	#Results RSLT
		INNER JOIN CTE
			ON CTE.ChildId = RSLT.ChildId
			AND CTE.AppDataRank = 1;

	PRINT 'Updating children who are enrolled in another year to have Complete App across all years ...';
	UPDATE	RSLT
	SET	ChildStatusFlags = (ChildStatusFlags ^ @FLAG_NOAPPLICATION) | @FLAG_APPLICATION
	FROM	#Results RSLT
	WHERE	(
				ChildStatusFlags & @FLAG_NOAPPLICATION > 0
			AND	EXISTS	(
					SELECT	1
					FROM	vwChildProgramStatus WITH (NOEXPAND)
					WHERE	ChildId = RSLT.ChildId
					AND	SchoolYearId != @SchoolYearId
					AND	(
							Enrolled = 1
						OR	Exited = 1
						)
					)
			)
	OR	(
		EXISTS	(
				--If there is a future completed application, use that.
				SELECT	1
				FROM	ApplicationData
				WHERE	ApplicationDataId = RSLT.ApplicationDataId
				AND	IsComplete = 1
				AND	SchoolYearId > @SchoolYearId
				)
		);

	PRINT 'Updating children who were ever enrolled ...';
	UPDATE	RSLT
	SET	ChildStatusFlags = ChildStatusFlags | @FLAG_EVERENROLLED
	FROM	#Results RSLT
	WHERE	EXISTS	(
				SELECT	1
				FROM	vwChildProgramStatus WITH (NOEXPAND)
				WHERE	ChildId = RSLT.ChildId
				AND	(
						Enrolled = 1
					OR	Exited = 1
					)
				);

	SELECT	@Print = 'Pulling current status (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
  	UPDATE	RSLT
	SET	ChildStatus = PROGSTATUS.Name,
		ChildStatusId = PROGSTATUS.ProgramStatusTypeId
	FROM	#Results RSLT
		INNER JOIN ProgramStatusType PROGSTATUS
			ON PROGSTATUS.IsDeleted = 0
			AND CASE
					WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER > 0 THEN @PROGSTATUS_PENDINGTRANSFER
					WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT > 0 THEN @PROGSTATUS_PENDINGEXIT
					WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT > 0 THEN @PROGSTATUS_INCOMPLETEEXIT
					WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED > 0 THEN @PROGSTATUS_ENROLLED
					WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST > 0 THEN @PROGSTATUS_ONWAITLIST
					WHEN RSLT.ChildStatusFlags & @FLAG_EXITED > 0 THEN @PROGSTATUS_EXITED
					WHEN RSLT.ChildStatusFlags & @FLAG_APPLICATION > 0 THEN @PROGSTATUS_APPLICATION
					WHEN RSLT.ChildStatusFlags & @FLAG_NOAPPLICATION > 0 THEN @PROGSTATUS_NOAPPLICATION
					WHEN RSLT.ChildStatusFlags & @FLAG_PRESCREEN > 0 THEN @PROGSTATUS_PRESCREEN
					WHEN RSLT.ChildStatusFlags & @FLAG_NOPRESCREEN > 0 THEN @PROGSTATUS_NOPRESCREEN
					ELSE NULL
				END = PROGSTATUS.ProgramStatusTypeId;

	SET ROWCOUNT @RowsPerPage;

	SELECT	@Print = 'Final results -> client (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	SELECT	*
	FROM	#Results
	ORDER BY
		CASE WHEN @SortDirection = 'Descending' THEN
			SortKey
		ELSE
			NULL
		END DESC,
		CASE WHEN @SortDirection = 'Ascending' THEN
			SortKey
		ELSE
			NULL
		END ASC;

	SET ROWCOUNT 0;

	DROP TABLE #Results;
	DROP TABLE #NameConcat;
	DROP TABLE #PreResults;
END;