if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSearchUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLicenseIdByUserId]
GO
alter PROCEDURE [dbo].[GetSearchUsers]--'','','','',0,0,0,1,10,0  
 -- 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,
 @pageNo int, @pageLength int, @recordCount int out  
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  
 ( 
  RowNums int IDENTITY PRIMARY KEY, 
  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  
   -- select @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  
  -- select  @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  
  --select @SQL
  EXEC SP_EXECUTESQL @SQL  
  
 END  
 -- Selecting the desired result from temporary table
 Select  RowNum,Id, FirstName, LastName,LoginId, EmailId,UserTypeTitle, Password, CreationDate,  
  ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId,EditionTypeId
   from (  
 SELECT   ROW_NUMBER() OVER (ORDER BY Id) AS RowNum ,Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,  
  ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId FROM #UserResult) as usr
 WHERE RowNum > @pageLength * (@pageNo - 1) AND 	RowNum <= @pageLength * @pageNo   order by Id --RowNum BETWEEN @pageNo AND (@pageNo - 1) * @pageLength
--SELECT  RowNum, Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,  
--  ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId FROM #UserResult
--  where RowNum > (@pageLength * (@pageNo - 1)) AND (RowNo <= (@pageLength * @pageNo))   order by RowNum
 -- order by Id
 -- order by Id OFFSET ((@pageNo - 1) * @pageLength) ROWS	FETCH NEXT @pageLength ROWS ONLY;



  --Calculate total number of records
  select @recordCount = count(ResultTable.Id) from (SELECT Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,  
  ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId FROM #UserResult) as ResultTable;

 -- Dropping the temporary table  
  DROP TABLE #UserResult   
END