USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetSubscribedLicenses]    Script Date: 4/25/2017 1:26:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetSubscribedLicenses] 
	-- 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 subscribed licenses on the basis of parameter
	CREATE TABLE #SubscribedLicenseReport
	(
		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 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(SubscriptionValidFrom) 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, accountype of a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = (LicenseeFirstName+' '+LicenseeLastName),
			@sLicenseType = LicenseType.Title, @sAccountType = AccountType.Title,
			@iCardNumber = (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END),
			@sInstitutionName = License.InstitutionName,@dtLicenseCreationDate = License.CreationDate
			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
			WHERE License.Id = @iLicenseId AND 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 License.LicenseTypeId <> 5 
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch startdate, enddate, subscriptionprice of a license
				SELECT @mSubscriptionPrice = LicenseSubscriptionDetail.TotalAmount,
				@dtStartDate = LicenseSubscriptionDetail.SubscriptionValidFrom, 
				@dtEndDate = LicenseSubscriptionDetail.SubscriptionValidThrough
				FROM LicenseSubscriptionDetail 
				WHERE LicenseSubscriptionDetail.Id = @iLicenseSubscriptionDetail

				-- 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 #SubscribedLicenseReport 
				(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 #SubscribedLicenseReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #SubscribedLicenseReport
END