USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetExpiringLicenses]    Script Date: 04/25/2017 18:00:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetExpiringLicenses] 
	-- Add the parameters for the stored procedure here
	@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2),
	@iLicenseTypeId int, @iAccountTypeId int, @sZip varchar(20) = '', @iStateId int, @iCountryId int
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @cGetLicenseId CURSOR
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptionDetail INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @iDaysRemaining INT
	DECLARE @iCardNumber INT

	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	-- create a temporary table to store the desired results of license which are going to be expire on the basis of parameter
	CREATE TABLE #ExpiringLicenseReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		InstitutionName VARCHAR(100),
		Edition VARCHAR(200),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		LicenseCreationDate DATETIME,
		Price MONEY,
		AccountType VARCHAR(50),
		DaysRemaining INT,
		CardNumber INT
	)

	-- define the forward only, read-only cursor 
	SET @cGetLicenseId = CURSOR FAST_FORWARD 
	FOR 
		SELECT LicenseSubscriptionDetail.LicenseId, MAX(LicenseSubscriptionDetail.Id) 
		FROM LicenseSubscriptionDetail WHERE 
		(TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
		AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END)) 
		GROUP BY LicenseSubscriptionDetail.LicenseId 
		HAVING (MAX(SubscriptionValidThrough) BETWEEN @dtFromDate AND @dtToDate)
	
	-- open & fetch the cursor variables into the local variables
		OPEN @cGetLicenseId 
		FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetail
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			SET @sEdition = ''
			-- fetch the accountnumber, licenseename, licensetype, startdate, enddate, subscriptionprice, accountype & days remaining to expire for a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = LicenseeName, @sLicenseType = LicenseType,
			@sInstitutionName = InstitutionName, @dtLicenseCreationDate = CreationDate,
			@dtStartDate = SubscriptionValidFrom, @dtEndDate = SubscriptionValidThrough,
			@mSubscriptionPrice = TotalAmount, @sAccountType = AccountType, @iDaysRemaining = DaysRemaining, @iCardNumber = CardNumber
			FROM 
			(
				SELECT AccountNumber, (LicenseeFirstName+' '+LicenseeLastName) as LicenseeName,
				LicenseType.Title as LicenseType, AccountType.Title as AccountType,
				License.InstitutionName,License.CreationDate,
				LicenseSubscriptionDetail.TotalAmount,
				LicenseSubscriptionDetail.SubscriptionValidFrom, LicenseSubscriptionDetail.SubscriptionValidThrough, 
				DATEDIFF(dd,GETDATE(),LicenseSubscriptionDetail.SubscriptionValidThrough) as DaysRemaining, (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
				FROM License 
				INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
				INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id 
				INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId 
				INNER JOIN State ON License.StateId = State.Id
				INNER JOIN Country ON License.CountryId = Country.Id
				WHERE License.IsActive = 1
				AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
				AND License.AccountTypeId = (CASE WHEN @iAccountTypeId > 0 THEN @iAccountTypeId ELSE License.AccountTypeId END)
				AND State.Id = (CASE WHEN @iStateId > 0 THEN @iStateId ELSE State.Id END)
				AND Country.Id = (CASE WHEN @iCountryId > 0 THEN @iCountryId ELSE Country.Id END)
				AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
				AND LicenseSubscriptionDetail.Id =  @iLicenseSubscriptionDetail
				AND License.LicenseTypeId <> 5 
			) t1 
			WHERE DaysRemaining>=0
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch all the editions mapped as a string with a license
				SELECT @sEdition = Edition.Title + '; ' + @sEdition 
				FROM LicenseToEdition INNER JOIN Edition 
				ON LicenseToEdition.EditionId = Edition.Id 
				WHERE LicenseToEdition.LicenseId = @iLicenseId
				-- remove the trailing comma-separator from the edition-string
				--AMI SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);
					IF LEN(@sEdition)> 1
						-- remove the trailing comma-separator from the edition-string
						SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
					ELSE
						SET @sEdition = @sEdition
				
				-- insert into the temporary table
				INSERT INTO #ExpiringLicenseReport 
				(AccountNumber, LicenseeName, LicenseType,InstitutionName, Edition, ValidFrom, ValidThrough,LicenseCreationDate, Price, AccountType, DaysRemaining,CardNumber) 
				VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sInstitutionName,@sEdition,@dtStartDate,@dtEndDate,@dtLicenseCreationDate, @mSubscriptionPrice,@sAccountType,@iDaysRemaining,@iCardNumber)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId,@iLicenseSubscriptionDetail
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseId
	DEALLOCATE @cGetLicenseId

	-- Selecting the desired result from temporary table
	SELECT AccountNumber,LicenseeName,LicenseType,InstitutionName,Edition,
	CONVERT(VARCHAR,ValidFrom,101) as StartDate,CONVERT(VARCHAR,ValidThrough,101) as EndDate,
	CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate,
	CONVERT(NUMERIC(14,2),Price) as SubscriptionPrice,AccountType,DaysRemaining, CardNumber
	FROM #ExpiringLicenseReport ORDER BY AccountNumber

    
	-- Dropping the temporary table
	DROP TABLE #ExpiringLicenseReport
END