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

CREATE PROCEDURE [dbo].[usp_GetNetAdSummaryReport] --'2015-05-01','2018-05-01',0,0,0,1,100,1000
	-- Add the parameters for the stored procedure here
	-- FromDate & ToDate are mandatory
	@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseTypeId tinyint,
	@pageNo int, @pageLength int, @recordCount int out 
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 @cGetSummary CURSOR
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptioId INT
	DECLARE @iActiveSubscription INT
	DECLARE @iRenewSubscription INT
	DECLARE @iCancelSubscription INT
	DECLARE @iNetAdSubscription INT
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @sInstitutionname VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @IsActive BIT
	DECLARE @sRenew BIT
	
	-- set the default parameters to 0
	SET @iActiveSubscription = 0
	SET @iRenewSubscription = 0
	SET @iCancelSubscription = 0
	
	-- 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 first-level of result shown in the netad subscription report on the basis of parameter
	CREATE TABLE #NetAdSummaryReport
	(
		LicenseType VARCHAR(50),
		AccountType VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		IsActive BIT,
		IsRenew BIT
	)
	CREATE CLUSTERED INDEX IK_NetAdSummaryReport_1 ON #NetAdSummaryReport (LicenseType, AccountType) 
	CREATE NONCLUSTERED INDEX IK_NetAdSummaryReport_2 ON #NetAdSummaryReport (IsActive) 	
	
	-- create a temporary table
	CREATE TABLE #NetAdResult
	(
		LicenseType VARCHAR(50),
		AccountType VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		ActiveSubscription INT,
		RenewSubscription INT,
		InActiveSubscription INT,
		NetAdSubscription INT
	)
	
	-- define the forward only, read-only cursor 
	SET @cGetSummary = 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 (License.CreationDate BETWEEN @dtFromDate AND @dtToDate )
		OR (RenewalDate BETWEEN @dtFromDate AND @dtToDate))
		AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
		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)) 
		AND License.LicenseTypeId <> 5 
		GROUP BY License.Id, LicenseSubscriptionDetail.Id
		
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetSummary 
		FETCH NEXT FROM @cGetSummary INTO @iLicenseId, @iLicenseSubscriptioId
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
				
			-- fetch the licensetype, accountype & the status of a license
			SELECT @sLicenseType = LicenseType.Title, @sAccountType = AccountType.Title, 
			@sInstitutionname = License.InstitutionName, @dtLicenseCreationDate = License.CreationDate,
			@IsActive = License.IsActive, 
			@sRenew = (CASE WHEN LicenseSubscriptionDetail.RenewalDate IS NULL  THEN 0 ELSE 1 END)
			FROM License 
			INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
			INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id 
			INNER JOIN LicenseSubscriptionDetail ON LicenseSubscriptionDetail.LicenseId = License.Id
			WHERE License.Id = @iLicenseId 
			AND LicenseSubscriptionDetail.Id = @iLicenseSubscriptioId
			
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				
				IF @IsActive = 1 
				BEGIN
					IF @sRenew = 1
					BEGIN
						SET @iRenewSubscription = @iRenewSubscription + 1
					END
					ELSE
					BEGIN
						SET @iActiveSubscription = @iActiveSubscription + 1
					END
				END
				ELSE
				BEGIN
					IF @sRenew = 1
					BEGIN
						SET @iRenewSubscription = @iRenewSubscription + 1
					END
					ELSE
					BEGIN
						SET @iCancelSubscription = @iCancelSubscription + 1
					END
				END
				
				-- insert into the temporary table
				INSERT INTO #NetAdSummaryReport 
				(LicenseType,AccountType,InstitutionName,LicenseCreationDate,IsActive,IsRenew) 
				VALUES(@sLicenseType,@sAccountType,@sInstitutionname,@dtLicenseCreationDate,@IsActive,@sRenew)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetSummary INTO @iLicenseId, @iLicenseSubscriptioId
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetSummary
	DEALLOCATE @cGetSummary

	-- Selecting the desired result from temporary table
	INSERT INTO #NetAdResult (LicenseType, AccountType,InstitutionName,LicenseCreationDate,ActiveSubscription, RenewSubscription, InActiveSubscription,
	NetAdSubscription)
	SELECT LicenseType,AccountType,MAX(InstitutionName) as InstitutionName, MAX(LicenseCreationDate) as LicenseCreationDate,(SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 1 AND IsRenew = 0) as ActiveSubscription,
	(SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsRenew = 1) as RenewSubscription,
	(SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 0 AND IsRenew = 0) as InActiveSubscription,
	((SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 1 AND IsRenew = 0) + (SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsRenew = 1) - (SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 0)) as NetAdSubscription
	FROM #NetAdSummaryReport N1 GROUP BY LicenseType,AccountType

	-- to show the sum of active, renew, cancel & netad subscriptions
	if((Select COUNT(*) from #NetAdResult)>0)
	begin
	INSERT INTO #NetAdResult (LicenseType,LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
	NetAdSubscription) SELECT 'Total','9999-01-01', @iActiveSubscription, @iRenewSubscription, @iCancelSubscription,
	(@iActiveSubscription+@iRenewSubscription-@iCancelSubscription)
	End 
	
 Select  RowNum, LicenseType, AccountType,InstitutionName,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
		NetAdSubscription
   from (  
 SELECT ROW_NUMBER() OVER (ORDER BY LicenseCreationDate Asc) AS RowNum ,LicenseType, AccountType,InstitutionName,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
		NetAdSubscription FROM #NetAdResult) as usr
 WHERE RowNum > @pageLength * (@pageNo - 1) AND RowNum <= @pageLength * @pageNo  order by LicenseCreationDate desc
    
	 --Calculate total number of records
  select @recordCount = count(ResultTable.NetAdSubscription) from (SELECT LicenseType, AccountType,InstitutionName,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
		NetAdSubscription FROM #NetAdResult) as ResultTable;

	-- Dropping the temporary tables
	DROP TABLE #NetAdSummaryReport
	DROP TABLE #NetAdResult
END