﻿USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetBlockedUserByUserId]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================  
-- Author:  <>  
-- Create date: <>  
-- Description: <Description,,>  
-- ============================================= 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBlockedUserByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetBlockedUserByUserId]
GO
 
CREATE PROCEDURE [dbo].[GetBlockedUserByUserId] 
 -- Add the parameters for the stored procedure here  
 @userId 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 AIAUser.Id = @userId  
   --AND UserType.Priority >= (SELECT UserType.Priority FROM UserType WHERE UserType.Id=@iUserTypeId)    
   --AND ((@iLicenseId =0) OR (License.Id = @iLicenseId))    
   --AND License.IsActive = 1    
END 
GO
