USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetLoginFailureErrorReport]    Script Date: 02/06/2018 10:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		MAGIC SOFTWARE PVT LTD
-- Create date: 17 Jan, 2011
-- Description:	Stored Procedure to get LoginFailureErrorReport
-- =============================================
CREATE PROCEDURE [dbo].[GetLoginFailureErrorReport]
	-- Add the parameters for the stored procedure here
	-- FromDate & ToDate are mandatory
	@sFromDate VARCHAR(20), 
	@sToDate VARCHAR(20),  
	@sAccoutNumber CHAR(16)='',
	@sFailureCause TINYINT
AS
BEGIN
	SET NOCOUNT ON;
   
    DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME	
	
	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	SELECT UserLoginLog.AccountNumber as AccountNumber,
	(CASE WHEN ActiveEdition.Title IS NULL 
		THEN ISNULL(UserLoginLog.Edition,'')+' (Unknown)' 
		ELSE ActiveEdition.Title 
	END) AS EditionTitle,
	UserLoginLog.ReferalUrl as ReferalUrl,
	UserLoginLog.HttpReferer as HttpReferer,
	LoginFailureCause.Description as FailureCause,
	CONVERT(VARCHAR,UserLoginLog.LogDate,101) as LogDate
	FROM UserLoginLog
	LEFT JOIN (SELECT Edition.Title, Edition.Id FROM Edition WHERE Edition.IsActive=1) ActiveEdition
	ON UserLoginLog.Edition = cast(ActiveEdition.Id AS NVARCHAR)
	INNER JOIN LoginFailureCause
	ON UserLoginLog.FailureId=LoginFailureCause.Id 
	WHERE UserLoginLog.LogDate BETWEEN @dtFromDate AND @dtToDate 
	AND UserLoginLog.FailureId = (CASE WHEN @sFailureCause > 0 
										THEN @sFailureCause 
										ELSE UserLoginLog.FailureId 
								  END)
	AND UserLoginLog.AccountNumber = (CASE WHEN LEN(@sAccoutNumber) > 0 
											THEN @sAccoutNumber 
											ELSE UserLoginLog.AccountNumber 
									  END)
	
END
GO