USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetCancelledLicenses]    Script Date: 4/25/2017 1:27:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetCancelledLicenses] 
	-- Add the parameters for the stored procedure here
	@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseTypeId tinyint,
	@iAccountTypeId tinyint , @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 @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @sEdition VARCHAR(200)
	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 cancelled licenses on the basis of parameter
	CREATE TABLE #CancelledLicenseReport
	(
		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),
		CardNumber INT
	)

	-- define the forward only, read-only cursor 
	SET @cGetLicenseID = CURSOR FAST_FORWARD 
	FOR 
		SELECT LicenseSubscriptionDetail.LicenseId, MAX(LicenseSubscriptionDetail.Id) 
		FROM LicenseSubscriptionDetail 
		INNER JOIN License ON License.Id = LicenseSubscriptionDetail.LicenseId
		WHERE (License.CancellationDate BETWEEN @dtFromDate AND @dtToDate) AND
		(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 of a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = LicenseeName, @sLicenseType = LicenseType,
			@sInstitutionName = InstitutionName,
			@dtStartDate = SubscriptionValidFrom, @dtEndDate = SubscriptionValidThrough,
			@dtLicenseCreationDate = CreationDate,
			@mSubscriptionPrice = TotalAmount, @sAccountType = AccountType, @iCardNumber = CardNumber
			FROM 
			(
				SELECT AccountNumber, (LicenseeFirstName+' '+LicenseeLastName) as LicenseeName,
				LicenseType.Title as LicenseType, License.InstitutionName,
				AccountType.Title as AccountType, LicenseSubscriptionDetail.TotalAmount,
				LicenseSubscriptionDetail.SubscriptionValidFrom, LicenseSubscriptionDetail.SubscriptionValidThrough, 
				License.CreationDate,
				DATEDIFF(dd,GETDATE(),License.CancellationDate) 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 State ON License.StateId = State.Id
				INNER JOIN Country ON License.CountryId = Country.Id 
				INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId 
				WHERE License.IsActive = 0 
				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 

			-- 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 #CancelledLicenseReport 
				(AccountNumber,LicenseeName,LicenseType,InstitutionName,Edition,ValidFrom,ValidThrough,LicenseCreationDate,Price,AccountType,CardNumber) 
				VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sInstitutionName,@sEdition,@dtStartDate,@dtEndDate,@dtLicenseCreationDate,@mSubscriptionPrice,@sAccountType,@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,AccountType, 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, CardNumber
	FROM #CancelledLicenseReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #CancelledLicenseReport
END