SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- ====================================================  
-- Author:  Magic Software  
-- Create date: 22-Jan-2018  
-- Description: To get all the licenses based on criteria  
-- ====================================================  
create PROCEDURE [dbo].[usp_GetLicenses]
	-- 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
	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)
	order by CreationDate
	OFFSET ((@pageNo - 1) * @pageLength) ROWS
	FETCH NEXT @pageLength ROWS ONLY
	
	--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

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO