SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSearchUserList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSearchUserList]
GO

Create PROCEDURE [dbo].[GetSearchUserList]--'','','','',1,1,1  
 -- Add the parameters for the stored procedure here  
 @sFirstName varchar(100) = '', @sLastName varchar(100) = '', @sEmailId varchar(100) = '',   
 @sAccoutNumber varchar(100) ='', @iUserTypeId int, @iAccountTypeId int, @iLoginUserType int  
AS  
BEGIN  
 IF 1=0 BEGIN  
  SET FMTONLY OFF  
 END  
   
 DECLARE @SQL NVARCHAR(MAX)  
 -- create a temporary table to store the desired results of user on the basis of parameter  
 CREATE TABLE #UserResult  
 (  
  Id INT,  
  FirstName VARCHAR(100),  
  LastName VARCHAR(100),  
  LoginId VARCHAR(50),  
  EmailId VARCHAR(50),  
  UserTypeTitle VARCHAR(50),  
  Password VARCHAR(50),  
  CreationDate DATETIME,  
  ModifiedDate DATETIME,  
  AccountNumber VARCHAR(50) DEFAULT '',  
  AccountTypeTitle VARCHAR(50) DEFAULT '',  
  EditionType VARCHAR(50) DEFAULT '',  
  UserStatus VARCHAR(8),  
  UserTypeId INT,  
  EditionTypeId INT DEFAULT ''  
 )  
 /*SET @sFirstName = REPLACE(@sFirstName,' ',' OR ')  
 SET @sLastName = REPLACE(@sLastName,' ',' OR ')*/  
 SET @SQL = ''  
 IF LEN(@sAccoutNumber) > 0 OR @iAccountTypeId > 0  
 BEGIN  
  -- fetch account number, state, zip, country of the license to which the user is belonged    
   
  SET @SQL = 'INSERT INTO #UserResult (Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,  
   ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId)  
   SELECT  AIAUser.Id, ISNULL(AIAUser.FirstName,''''), ISNULL(AIAUser.LastName,''''), AIAUser.LoginId, ISNULL(AIAUser.EmailId,'''') as EmailId,  
   UserType.Title as UserTypeTitle, AIAUser.Password, AIAUser.CreationDate, ISNULL(AIAUser.ModifiedDate,'''') as ModifiedDate,   
   ISNULL(License.AccountNumber,'''') as AccountNumber, ISNULL(AccountType.Title,'''') as AccountTypeTitle,  
   ISNULL(Edition.Title,'''') as EditionType,   
   (CASE AIAUser.IsActive WHEN 1 THEN ''Active'' ELSE ''Inactive'' END) as UserStatus,   
   UserType.Id as UserTypeId, ISNULL(Edition.Id,'''') as EditionTypeId  
   FROM AIAUser   
   INNER JOIN UserType ON UserType.Id = AIAUser.UserTypeId  
   INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId  
   INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id  
   INNER JOIN License ON LicenseToEdition.LicenseId = License.Id  
   INNER JOIN AccountType ON AccountType.Id = License.AccountTypeId  
   INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId  
      WHERE  
   License.IsActive = 1  
   AND UserType.Priority >' +CONVERT(VARCHAR(20),@iLoginUserType)  
  
  IF LEN(@sAccoutNumber)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND License.AccountNumber = '''+@sAccoutNumber+''''  
  END  
  IF @iAccountTypeId > 0  
  BEGIN  
   SET @SQL = @SQL + ' AND License.AccountTypeId = '''+CONVERT(VARCHAR(20),@iAccountTypeId)+''''  
  END  
  IF LEN(@sFirstName)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND (AIAUser.FirstName LIKE ''%'+@sFirstName+'%'')' --CONTAINS(AIAUser.FirstName, '''+@sFirstName+''')'  
  END  
  IF LEN(@sLastName)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND (AIAUser.LastName LIKE ''%'+@sLastName+'%'')'--CONTAINS(AIAUser.LastName, '''+@sLastName+''')'  
  END  
  IF LEN(@sEmailId)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND AIAUser.EmailId = '''+@sEmailId+''''  
  END  
  IF @iUserTypeId>0  
  BEGIN  
   SET @SQL = @SQL + ' AND AIAUser.UserTypeId = '''+CONVERT(VARCHAR(20),@iUserTypeId)+''''  
  END  
    Print @SQL
  EXEC SP_EXECUTESQL @SQL  
     
 END  
 ELSE  
 BEGIN  
  
  SET @SQL = 'INSERT INTO #UserResult (Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,  
   ModifiedDate, UserStatus, UserTypeId)   
   SELECT AIAUser.Id, ISNULL(AIAUser.FirstName,''''), ISNULL(AIAUser.LastName,''''),   
   AIAUser.LoginId, ISNULL(AIAUser.EmailId,''''), UserType.Title, AIAUser.Password, AIAUser.CreationDate,   
   ISNULL(AIAUser.ModifiedDate,''''), (CASE AIAUser.IsActive WHEN 1 THEN ''Active'' ELSE ''Inactive'' END),   
   UserType.Id   
   FROM AIAUser   
   INNER JOIN UserType ON UserType.Id = AIAUser.UserTypeId  
   WHERE UserType.Title in (''General Admin'')'  
    
  IF LEN(@sFirstName)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND (AIAUser.FirstName LIKE ''%'+@sFirstName+'%'')'--CONTAINS(AIAUser.FirstName, '''+@sFirstName+''')'  
  END  
  IF LEN(@sLastName)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND (AIAUser.LastName LIKE ''%'+@sLastName+'%'')'--CONTAINS(AIAUser.LastName, '''+@sLastName+''')'  
  END  
  IF LEN(@sEmailId)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND AIAUser.EmailId = '''+@sEmailId+''''
  END  
  IF @iUserTypeId>0  
  BEGIN  
   SET @SQL = @SQL + ' AND AIAUser.UserTypeId = '''+CONVERT(VARCHAR(20),@iUserTypeId)+''''  
  END  
  -- Print  @SQL
  EXEC SP_EXECUTESQL @SQL  
    
  -- fetch account number, state, zip, country of the license to which the user is belonged  
  SET @SQL = 'INSERT INTO #UserResult (Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,  
   ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId)   
   SELECT  AIAUser.Id, ISNULL(AIAUser.FirstName,''''), ISNULL(AIAUser.LastName,''''), AIAUser.LoginId, ISNULL(AIAUser.EmailId,''''),   
   UserType.Title, AIAUser.Password, AIAUser.CreationDate, ISNULL(AIAUser.ModifiedDate,''''),   
   License.AccountNumber, AccountType.Title, Edition.Title,   
   (CASE AIAUser.IsActive WHEN 1 THEN ''Active'' ELSE ''Inactive'' END), UserType.Id, Edition.Id  
   FROM AIAUser   
   INNER JOIN UserType ON UserType.Id = AIAUser.UserTypeId  
   INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId  
   INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id  
   INNER JOIN License ON LicenseToEdition.LicenseId = License.Id  
   INNER JOIN AccountType ON AccountType.Id = License.AccountTypeId  
   INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId  
         WHERE   
   UserType.Title NOT IN (''Super Admin'',''General Admin'')  
   AND License.IsActive = 1'  
     
  IF LEN(@sAccoutNumber)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND License.AccountNumber = '''+@sAccoutNumber+''''  
  END  
  IF @iAccountTypeId > 0  
  BEGIN  
   SET @SQL = @SQL + ' AND License.AccountTypeId = '''+CONVERT(VARCHAR(20),@iAccountTypeId)+''''  
  END  
  IF LEN(@sFirstName)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND (AIAUser.FirstName LIKE ''%'+@sFirstName+'%'')'--CONTAINS(AIAUser.FirstName, '''+@sFirstName+''')'  
  END  
  IF LEN(@sLastName)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND (AIAUser.LastName LIKE ''%'+@sLastName+'%'')'--CONTAINS(AIAUser.LastName, '''+@sLastName+''')'  
  END  
  IF LEN(@sEmailId)>0  
  BEGIN  
   SET @SQL = @SQL + ' AND AIAUser.EmailId = '''+@sEmailId+''''  
  END  
  IF @iUserTypeId>0  
  BEGIN  
   SET @SQL = @SQL + ' AND AIAUser.UserTypeId = '''+CONVERT(VARCHAR(20),@iUserTypeId)+''''  
  END  
 -- Print @SQL
  EXEC SP_EXECUTESQL @SQL  
  
 END  
 -- Selecting the desired result from temporary table  
 SELECT Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,  
  ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId FROM #UserResult  
 -- Dropping the temporary table  
 DROP TABLE #UserResult   
END  

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO