-- =============================================
-- Author: Magic Software
-- Create date: 12-May-2009
-- Description:	To get the list of blocked user who have attempt 5 times wrong login
-- =============================================
CREATE PROCEDURE [dbo].[GetBlockedUserByAccNoAndType] 
	-- Add the parameters for the stored procedure here
	@iUserTypeId tinyint, @iLicenseId int
AS
BEGIN
	-- returns the metadata
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	SELECT DISTINCT 
				AIAUser.Id, 
				AIAUser.FirstName, 
				AIAUser.LastName, 
				AIAUser.LoginId,
				AIAUser.Password,
				AIAUser.EmailId, 
				ISNULL(License.AccountNumber,'') AccountNumber, 
				IncorrectLoginAttempts.LoginTime
	FROM 
				IncorrectLoginAttempts 
				INNER JOIN AIAUser ON IncorrectLoginAttempts.UserId = AIAUser.Id
				INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
				LEFT JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
				LEFT JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
				LEFT JOIN License ON LicenseToEdition.LicenseId = License.Id 
	WHERE 
			IncorrectLoginAttempts.CntIncorrectLogins >= 5 
			AND UserType.Priority >= (SELECT UserType.Priority FROM UserType WHERE UserType.Id=@iUserTypeId)
			AND ((@iLicenseId =0) OR (License.Id = @iLicenseId))
			AND License.IsActive = 1
END