USE [ClickDW]
GO
/****** Object:  StoredProcedure [portal].[pUserByUserName]    Script Date: 9/29/2021 1:27:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DROP PROC portal.pUserByUserNameTest;

ALTER   PROC [portal].[pUserByUserName]
	@UserName		nvarchar(250),
	@ProgrammeCode	varchar(10) = null,
	@ActiveOnly		bit = 1
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRY
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	DECLARE 
		@FilterProgrammeID	smallint = null,
		@UserID				uniqueidentifier,
		@Password			nvarchar(250),
		@PersonID			int,
		@ProgrammeID		smallint,
		@SysStartTime		datetime2(0),
		@SysEndTime			datetime2(0);

	IF @ProgrammeCode IS NOT NULL SET @FilterProgrammeID = (SELECT ProgrammeID FROM Programme WHERE Code = @ProgrammeCode);

	IF @UserName <> ''
	BEGIN
		SELECT TOP 1 
			@UserID			= PP.UserID,
			@PersonID		= PP.PersonID,
			@ProgrammeID	= PP.ProgrammeID, 
			@UserName		= PP.UserName,
			@Password		= PP.[Password],
			@SysStartTime	= PP.SysStartTime, 
			@SysEndTime		= PP.SysEndTime
		FROM PersonHasProgramme PP
		WHERE ((@FilterProgrammeID IS NULL) OR (@FilterProgrammeID IS NOT NULL AND PP.ProgrammeID = @FilterProgrammeID))
			AND PP.UserName = @UserName
		ORDER BY PP.SysStartTime

		IF @PersonID IS NULL
		BEGIN
			SELECT TOP 1 
				@UserID			= PP.UserID,
				@PersonID		= PP.PersonID,
				@ProgrammeID	= PP.ProgrammeID, 
				@UserName		= PP.UserName,
				@Password		= PP.[Password],
				@SysStartTime	= PP.SysStartTime, 
				@SysEndTime		= PP.SysEndTime
			FROM PersonHasProgrammeHistory PP
			WHERE ((@FilterProgrammeID IS NULL) OR (@FilterProgrammeID IS NOT NULL AND PP.ProgrammeID = @FilterProgrammeID))
				AND PP.UserName = @UserName
			ORDER BY PP.SysEndTime DESC
		END
	END

	SELECT
		CAST(@UserID AS varchar(50))	'ID',
		@PersonID						'PersonID',
		PROG.Code						'Programme',
		@UserName						'Username',
		CASE 
			WHEN PE.LearnerPassword IS NOT NULL THEN PE.LearnerPassword
			WHEN @Password IS NOT NULL THEN @Password
			ELSE NULL
		END								'Password',
		P.FirstName						'FirstName',
		P.LastName						'LastName',
		CAST(CASE WHEN @SysEndTime = '9999-12-31 23:59:59' THEN 1 ELSE 0 END AS bit) 'ActiveStatus',
		SL.SchoolID						'SchoolID',
		SL.SchoolLearnerID				'SchoolLearnerID',
		CAST(CASE WHEN RDEMO.PersonRoleID IS NOT NULL THEN 1 ELSE 0 END AS bit) 'RoleDemo'
	FROM Person P
	JOIN Programme PROG ON PROG.ProgrammeID = @ProgrammeID
	OUTER APPLY
	(
		SELECT TOP 1 SL.SchoolID, SL.SchoolLearnerID
		FROM SchoolLearner SL
		JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
		WHERE SL.PersonID = P.PersonID
			AND GETDATE() BETWEEN SL.StartDate AND SL.EndDate
		ORDER BY SLY.[Year] DESC, SL.StartDate DESC, SL.SchoolLearnerID DESC
	) SL
	LEFT JOIN SchoolEnvironment SE ON SL.SchoolID = SE.SchoolID
	LEFT JOIN ProgrammeEnvironment PE ON SE.EnvironmentID = PE.EnvironmentID AND PROG.ProgrammeID = PE.ProgrammeID AND PE.ImplementationStatusID IN (4,13)
	LEFT JOIN PersonRoleHasPerson RDEMO ON P.PersonID = RDEMO.PersonID AND RDEMO.PersonRoleID = 4
	WHERE P.PersonID = @PersonID;
	
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
	END TRY
	BEGIN CATCH
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
	THROW;
	END CATCH
	RETURN 0;
END
