﻿USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetCustomerSummary]    Script Date: 04/25/2017 16:38:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetCustomerSummary] 
	-- Add the parameters for the stored procedure here
	@sAccoutNumber varchar(50)='', @sLicenseeFullName varchar(100)='', @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), 
	@iLicenseType tinyint, @iAccountType tinyint, @sZip varchar(20) = '', @iState int, 
	@iCountry 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 @cGetLicenseDetails CURSOR
	DECLARE @iLicenseId INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @iLicenseTypeId TINYINT
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @iAccountTypeId TINYINT
	DECLARE @sLicenseStatus VARCHAR(8)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @bExists bit
    DECLARE @sLicenseState VARCHAR(50)
	DECLARE @sLicenseZip VARCHAR(20)
	DECLARE @sLicenseCountry VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @iLicenseSubscriptionId INT
	DECLARE @sEmailId VARCHAR(100)
	DECLARE @iCardNumber INT

	-- create a temporary table to store the desired results of licenses on the basis of parameter
	CREATE TABLE #CustomerReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		Edition VARCHAR(200),
		Email VARCHAR(100),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		AccountType VARCHAR(50),
		LicenseStatus VARCHAR(8),
		Price MONEY,
        LicenseState VARCHAR(50),
		LicenseZip VARCHAR(20),
		LicenseCountry VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		CardNumber INT
	)

	SET @sLicenseeFullName = REPLACE(@sLicenseeFullName,' ',' OR ')
	
	-- define the forward only, read-only cursor 
	SET @cGetLicenseDetails = CURSOR FAST_FORWARD 
	FOR 
		SELECT License.Id, License.AccountNumber, (License.LicenseeFirstName+' '+License.LicenseeLastName), 
		License.LicenseTypeId, License.AccountTypeId, License.EmailId,
		(CASE License.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
		State.StateName, License.Zip, Country.CountryName,
		License.InstitutionName,License.CreationDate,
		(CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
		FROM License
        INNER JOIN State ON License.StateId = State.Id
		INNER JOIN Country ON License.CountryId = Country.Id
 		WHERE 
		License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber END)
		AND License.LicenseTypeId = (CASE WHEN @iLicenseType > 0 THEN @iLicenseType ELSE License.LicenseTypeId END)
		AND License.AccountTypeId = (CASE WHEN @iAccountType > 0 THEN @iAccountType ELSE License.AccountTypeId END)
		AND State.Id = (CASE WHEN @iState > 0 THEN @iState ELSE State.Id END)
		AND Country.Id = (CASE WHEN @iCountry > 0 THEN @iCountry ELSE Country.Id END)
		AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
					
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseDetails 
		FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
		@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber 

		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			SET @sEdition = ''
			SET @bExists = 1
			-- fetch the latest license start/end date of a license on the basis of Subscription Start & End price if any
			SELECT @dtStartDate = MAX(LicenseSubscriptionDetail.SubscriptionValidFrom),
			@dtEndDate = MAX(LicenseSubscriptionDetail.SubscriptionValidThrough),
			@iLicenseSubscriptionId = MAX(LicenseSubscriptionDetail.Id)
			FROM LicenseSubscriptionDetail 
			WHERE LicenseSubscriptionDetail.LicenseId = @iLicenseId
			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
			
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN

				-- check whether the name of licensse matches the name entered by user

				IF LEN(@sLicenseeFullName) > 0
					BEGIN
						SELECT @bExists = 1 
						FROM License 
						WHERE Id = @iLicenseId AND (LicenseeFirstName LIKE '%'+@sLicenseeFullName+'%' OR LicenseeLastName LIKE '%'+@sLicenseeFullName+'%') --CONTAINS((LicenseeFirstName,LicenseeLastName)
						IF @@Rowcount = 0
							BEGIN
								SET @bExists = 0
							END				
					END

				-- check whether the above query returns any row
				IF @bExists = 1
				BEGIN
					-- fetch the licensetype of the license
					SELECT @sLicenseType = LicenseType.Title FROM LicenseType 
					WHERE LicenseType.Id = @iLicenseTypeId
					-- fetch the accounttype of the license
					SELECT @sAccountType = AccountType.Title FROM AccountType 
					WHERE AccountType.Id = @iAccountTypeId

					-- 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

					
					-- fetch the price of the license
					SELECT @mSubscriptionPrice = TotalAmount FROM LicenseSubscriptionDetail 
					WHERE Id = @iLicenseSubscriptionId

					-- insert into the temporary table
					INSERT INTO #CustomerReport 
					(AccountNumber, LicenseeName, LicenseType, Edition, Email, ValidFrom, ValidThrough, AccountType, LicenseStatus, Price, LicenseState, LicenseZip, LicenseCountry, InstitutionName, LicenseCreationDate, CardNumber) 
					VALUES(@sAccountNumber, @sLicenseeName, @sLicenseType, @sEdition, @sEmailId, @dtStartDate, @dtEndDate, @sAccountType, @sLicenseStatus, @mSubscriptionPrice, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber)
				END
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
			@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry, @sInstitutionName, @dtLicenseCreationDate, @iCardNumber
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseDetails
	DEALLOCATE @cGetLicenseDetails

	-- Selecting the desired result from temporary table
	SELECT AccountNumber, LicenseeName, LicenseType, AccountType, Edition, Email, CONVERT(VARCHAR,ValidFrom,101) as StartDate, 
	CONVERT(VARCHAR,ValidThrough,101) as EndDate, LicenseStatus, CONVERT(NUMERIC(14,2),Price) as Price, 
	LicenseZip, LicenseState, LicenseCountry,InstitutionName, CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate , CardNumber 
	FROM #CustomerReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #CustomerReport
END



