USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetSearchUserList]    Script Date: 1/29/2018 1:04:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetSearchUserList]--'gagan','','','',0,0,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 '', 
  Createdby VARCHAR(50) DEFAULT '',
  Modifiedby VARCHAR(50) DEFAULT '',
  DeactivationDate DATETIME  
 )  
 /*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,Createdby,Modifiedby,DeactivationDate)  
   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,
   (select concat(u.FirstName,'' '',u.LastName) from  AIAUser u where  u.Id=AIAUser.CreatorId) Createdby,
   (select concat(u.FirstName,'' '',u.LastName) from  AIAUser u where  u.Id=AIAUser.ModifierId) Modifiedby,
   AIAUser.DeactivationDate  
   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,Createdby,Modifiedby,DeactivationDate)   
   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,
   (select concat(u.FirstName,'' '',u.LastName) from  AIAUser u where  u.Id=AIAUser.CreatorId) Createdby,
   (select concat(u.FirstName,'' '',u.LastName) from  AIAUser u where  u.Id=AIAUser.ModifierId) Modifiedby,
   AIAUser.DeactivationDate   
   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,Createdby,Modifiedby,DeactivationDate)   
   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 ,
   (select concat(u.FirstName,'' '',u.LastName) from  AIAUser u where  u.Id=AIAUser.CreatorId) Createdby,
   (select concat(u.FirstName,'' '',u.LastName) from  AIAUser u where  u.Id=AIAUser.ModifierId) Modifiedby,
   AIAUser.DeactivationDate 
   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,Createdby,Modifiedby,DeactivationDate FROM #UserResult  
 -- Dropping the temporary table  
 DROP TABLE #UserResult   
END