-- ====================================================  
-- Author:  Magic Software  
-- Create date: 22-Jan-2018  
-- Description: To get all the licenses based on criteria  
-- ====================================================  
alter PROCEDURE [dbo].[usp_GetlicensesList]
	-- Add the parameters for the stored procedure here
	@sStartDate varchar(20) = '', @sEndDate varchar(20) = '', @sAccoutNumber varchar(50)='', 
	@sLicenseeFirstName varchar(50)='', @sLicenseeLastName varchar(50)='', @iLicenseTypeId tinyint, 
	@sInstituteName varchar(100) = '', @sEmail varchar(50) = '', @iStateId int, @iCountryId int, 
	@bisActive bit = 1, @pageNo int, @pageLength int, @recordCount int out
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Get the records on the basis of parameters page length and page number rows
	select 
	LD.id as LicenseId,
	LD.AccountNumber,
	LD.LicenseType,
	LD.AccountType,
	LD.InstitutionName,
	LD.StateName as LicenseState,
	LD.CountryName as LicenseCountry,
	LD.EmailId,
	LD.CardNumber,
	LD.ProductId as ProductKey,
	(case 
    when len(LD.AdminName)>=990
    then left(LD.AdminName, 990) + '...' 
    else LD.AdminName end)
	 as ClientAdmin,
	(LD.LicenseeFirstName + ' ' + LD.LicenseeLastName) as  LicenseeName,
	(ISNULL(LD.Address1,'')+' '+ ISNULL(LD.Address2,'')+' ' +ISNULL(LD.City,'')) as ContactAddress,
	CONVERT(VARCHAR,LD.CreationDate,101) as EntryDate,
	(CASE LD.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
	ISNULL(CONVERT(VARCHAR,LD.ModifiedDate,101),'') as ModifyDate, 
	CONVERT(VARCHAR,LD.SubscriptionStartDate,101) as StartDate,
	ISNULL(CONVERT(VARCHAR,LD.SubscriptionRenewalDate,101),'') as RenewDate,
	CONVERT(VARCHAR,LD.SubscriptionEndDate,101) as EndDate,
	LD.NoofImages, LD.RowNo
	from
			(Select ROW_NUMBER() OVER (ORDER BY L.id) AS RowNo,
			L.id,
			L.AccountNumber,
			L.LicenseeFirstName,
			L.LicenseeLastName,
			L.LicenseTypeId,
			LT.Title as LicenseType,
			A.Title as AccountType,
			L.InstitutionName,
			L.Address1,
			L.Address2,
			L.City,
			L.StateId,
			S.StateName,
			L.CountryId,
			C.CountryName,
			L.EmailId,
			L.CreationDate,
			L.IsActive,
			L.ModifiedDate, 
			L.CardNumber,
			L.ProductId,
			STUFF((Select  ',' +  AIAUser.FirstName + ' ' + AIAUser.LastName 
					FROM LicenseToEdition 
					INNER JOIN AIAUserToLicenseEdition ON LicenseToEdition.Id = AIAUserToLicenseEdition.LicenseEditionId 
					INNER JOIN AIAUser ON AIAUserToLicenseEdition.UserId = AIAUser.Id 
					INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
					WHERE LicenseToEdition.LicenseId = L.Id AND AIAUser.IsActive = 1
					AND UserType.Title in ('District Admin','Client Admin','Single User','Reseller')
			 FOR XML PATH ('')), 1, 1, '') AS AdminName,
			 (SELECT MAX(lsd.SubscriptionValidFrom)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionStartDate,
			 (SELECT MAX(lsd.RenewalDate)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionRenewalDate,
			 (SELECT MAX(lsd.SubscriptionValidThrough)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionEndDate,
			 (SELECT TOP(1) lsd.NoofImages FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id order by lsd.SubscriptionValidFrom desc) AS NoofImages
			 from License L 
			 inner join LicenseType LT on L.LicenseTypeId = LT.Id
			 inner join AccountType A on L.AccountTypeId=A.Id
			 inner join State S on L.StateId =S.Id
			 inner join Country C on L.CountryId = C.Id
			where
			((LEN(@sStartDate)=0) OR ((SELECT MAX(lsd.SubscriptionValidFrom)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) >= (CONVERT(DATETIME,@sStartDate)))) AND
			((LEN(@sEndDate)=0) OR ((SELECT MAX(lsd.SubscriptionValidThrough)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) <= (DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))))) AND
			((LEN(@sAccoutNumber)=0) OR (L.AccountNumber LIKE '%'+@sAccoutNumber+'%')) AND 
			((LEN(@sLicenseeFirstName)=0) OR (L.LicenseeFirstName LIKE '%'+@sLicenseeFirstName+'%')) AND 
			((LEN(@sLicenseeLastName)=0) OR (L.LicenseeLastName LIKE '%'+@sLicenseeLastName+'%')) AND 
			((LEN(@sInstituteName)=0) OR (L.InstitutionName LIKE '%'+@sInstituteName+ '%')) AND 
			((@iLicenseTypeId = 0) OR (L.LicenseTypeId = @iLicenseTypeId)) AND 
			((LEN(@sEmail)=0) OR (L.EmailId = @sEmail)) AND 
			((@iStateId =0) OR (L.StateId = @iStateId)) AND 
			((@iCountryId =0) OR (L.CountryId = @iCountryId))  AND 
			(L.isActive = @bisActive)
			) as LD
			where
			RowNo > @pageLength * (@pageNo - 1) AND 
			RowNo <= @pageLength * @pageNo
			order by creationdate

	--Calculate total number of records
	select @recordCount = count(ResultTable.LicenseId) from (
	select
	LD.id as LicenseId,
	LD.AccountNumber,
	LD.LicenseType,
	LD.AccountType,
	LD.InstitutionName,
	LD.StateName as LicenseState,
	LD.CountryName as LicenseCountry,
	LD.EmailId,
	LD.CardNumber,
	LD.ProductId as ProductKey,
	(case 
    when len(LD.AdminName)>=990
    then left(LD.AdminName, 990) + '...' 
    else LD.AdminName end)
	 as ClientAdmin,
	(LD.LicenseeFirstName + ' ' + LD.LicenseeLastName) as  LicenseeName,
	(ISNULL(LD.Address1,'')+' '+ ISNULL(LD.Address2,'')+' ' +ISNULL(LD.City,'')) as ContactAddress,
	CONVERT(VARCHAR,LD.CreationDate,101) as EntryDate,
	(CASE LD.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
	ISNULL(CONVERT(VARCHAR,LD.ModifiedDate,101),'') as ModifyDate, 
	CONVERT(VARCHAR,LD.SubscriptionStartDate,101) as StartDate,
	ISNULL(CONVERT(VARCHAR,LD.SubscriptionRenewalDate,101),'') as RenewDate,
	CONVERT(VARCHAR,LD.SubscriptionEndDate,101) as EndDate,
	LD.NoofImages
	from
			(Select 
			L.id,
			L.AccountNumber,
			L.LicenseeFirstName,
			L.LicenseeLastName,
			L.LicenseTypeId,
			LT.Title as LicenseType,
			A.Title as AccountType,
			L.InstitutionName,
			L.Address1,
			L.Address2,
			L.City,
			L.StateId,
			S.StateName,
			L.CountryId,
			C.CountryName,
			L.EmailId,
			L.CreationDate,
			L.IsActive,
			L.ModifiedDate, 
			L.CardNumber,
			L.ProductId,
			STUFF((Select  ',' +  AIAUser.FirstName + ' ' + AIAUser.LastName 
					FROM LicenseToEdition 
					INNER JOIN AIAUserToLicenseEdition ON LicenseToEdition.Id = AIAUserToLicenseEdition.LicenseEditionId 
					INNER JOIN AIAUser ON AIAUserToLicenseEdition.UserId = AIAUser.Id 
					INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
					WHERE LicenseToEdition.LicenseId = L.Id AND AIAUser.IsActive = 1
					AND UserType.Title in ('District Admin','Client Admin','Single User','Reseller')
			 FOR XML PATH ('')), 1, 1, '') AS AdminName,
			 (SELECT MAX(lsd.SubscriptionValidFrom)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionStartDate,
			 (SELECT MAX(lsd.RenewalDate)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionRenewalDate,
			 (SELECT MAX(lsd.SubscriptionValidThrough)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionEndDate,
			 (SELECT TOP(1) lsd.NoofImages FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id order by lsd.SubscriptionValidFrom desc) AS NoofImages
			 from License L 
			 inner join LicenseType LT on L.LicenseTypeId = LT.Id
			 inner join AccountType A on L.AccountTypeId=A.Id
			 inner join State S on L.StateId =S.Id
			 inner join Country C on L.CountryId = C.Id) 
	as LD
	where
	((LEN(@sStartDate)=0) OR (SubscriptionStartDate >= (CONVERT(DATETIME,@sStartDate)))) AND
	((LEN(@sEndDate)=0) OR (SubscriptionEndDate <= (DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))))) AND
	((LEN(@sAccoutNumber)=0) OR (AccountNumber LIKE '%'+@sAccoutNumber+'%')) AND 
	((LEN(@sLicenseeFirstName)=0) OR (LicenseeFirstName LIKE '%'+@sLicenseeFirstName+'%')) AND 
	((LEN(@sLicenseeLastName)=0) OR (LicenseeLastName LIKE '%'+@sLicenseeLastName+'%')) AND 
	((LEN(@sInstituteName)=0) OR (InstitutionName LIKE '%'+@sInstituteName+ '%')) AND 
	((@iLicenseTypeId = 0) OR (LicenseTypeId = @iLicenseTypeId)) AND 
	((LEN(@sEmail)=0) OR (EmailId = @sEmail)) AND 
	((@iStateId =0) OR (StateId = @iStateId)) AND 
	((@iCountryId =0) OR (CountryId = @iCountryId))  AND 
	(isActive = @bisActive)) as ResultTable;

END