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

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

CREATE PROCEDURE [dbo].[GetNetAdDetailReport] 
	-- Add the parameters for the stored procedure here
	-- FromDate, ToDate, LicenseType & AccountType are mandatory
	@sFromDate varchar(20), @sToDate varchar(20), @sLicenseType varchar(50), @sAccountType varchar(50),
	@iStartPrice numeric(14,2), @iEndPrice numeric(14,2)
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 @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptionDetailId INT
	DECLARE @sEdition VARCHAR(200)
	DECLARE @dtRenewalDate DATETIME
	DECLARE @LicenseStatus VARCHAR(10)

	-- 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 second-level after clicking on the first-level of result shown in the netad subscription report on the basis of parameter
	CREATE TABLE #NetAdDetailReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		Edition VARCHAR(200),
		RenewalDate DATETIME
	)
	
	-- define the forward only, read-only cursor 
	SET @cGetLicenseId = CURSOR FAST_FORWARD 
	FOR 
		SELECT License.Id, LicenseSubscriptionDetail.Id
		FROM LicenseSubscriptionDetail
		INNER JOIN License ON LicenseSubscriptionDetail.LicenseId = License.Id WHERE 
		((License.CancellationDate BETWEEN @dtFromDate AND @dtToDate AND License.IsActive = 0 )
		OR (SubscriptionValidFrom 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 License.Id, LicenseSubscriptionDetail.Id
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseId 
		FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetailId
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @sEdition = ''
			-- fetch the accountnumber, licenseename of a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = (LicenseeFirstName+' '+LicenseeLastName)
			FROM License 
			INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
			INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id
			WHERE License.Id = @iLicenseId  
			AND License.IsActive = 1
			AND LicenseType.Title = @sLicenseType
			AND AccountType.Title = @sAccountType
			
		
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch the renewal date of the license
				SELECT @dtRenewalDate = LicenseSubscriptionDetail.RenewalDate
				FROM LicenseSubscriptionDetail
				WHERE LicenseSubscriptionDetail.Id = @iLicenseSubscriptionDetailId
		
				-- 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
				SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);

				-- insert into the temporary table
				INSERT INTO #NetAdDetailReport 
				(AccountNumber,LicenseeName,LicenseType,Edition,RenewalDate) 
				VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sEdition,@dtRenewalDate)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetailId
		-- 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, Edition, ISNULL(CONVERT(VARCHAR,RenewalDate,101),'') as RenewDate
	FROM #NetAdDetailReport

    
	-- Dropping the temporary table
	DROP TABLE #NetAdDetailReport
END


GO