USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetLicenses]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ====================================================  
-- Author:  Magic Software  
-- Create date: 22-Jan-2018  
-- Description: To get all the licenses based on criteria  
-- ====================================================  

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

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
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;

	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
		  
END


GO