﻿USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetUsageReport_OLD_PROC]    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].[GetUsageReport_OLD_PROC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUsageReport_OLD_PROC]
GO

CREATE PROCEDURE [dbo].[GetUsageReport_OLD_PROC] 
	-- Add the parameters for the stored procedure here
	-- FromDate & ToDate are mandatory parameters
	@sFromDate varchar(20), @sToDate varchar(20), @sAccoutNumber varchar(50)='',
	@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 @cGetUserDetails CURSOR
	DECLARE @iUserId INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @iCardNumber INT
	DECLARE @sLoginId VARCHAR(50)
	DECLARE @sFirstName VARCHAR(100)
	DECLARE @sLastName VARCHAR(100)
	DECLARE @sUserType VARCHAR(50)
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @sLicenseState VARCHAR(50)
	DECLARE @sLicenseZip VARCHAR(20)
	DECLARE @sLicenseCountry VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @iTotalLogins INT
	DECLARE @dtLastLogin DATETIME

		-- 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 results of users logged into the system within a particular time period
	CREATE TABLE #UsageReport
	(
		LoginId VARCHAR(50),
		FirstName VARCHAR(100),
		LastName VARCHAR(100),
		AccountNumber VARCHAR(50),
		CardNumber INT,
		UserType VARCHAR(50),
		LicenseCreationDate DATETIME,
		LicenseState VARCHAR(50),
		LicenseZip VARCHAR(20),
		LicenseCountry VARCHAR(50),
		InstitutionName VARCHAR(100),
		TotalLogins INT,
		LastLoginDate DATETIME
	)

	-- define the forward only, read-only cursor 
	SET @cGetUserDetails = CURSOR FAST_FORWARD 
	FOR 
		SELECT LoginDetail.UserId, COUNT(1) as TotalLogins, MAX(LoginDetail.LoginTime)
		FROM LoginDetail WHERE
		(LoginTime) BETWEEN @dtFromDate AND @dtToDate
		GROUP BY LoginDetail.UserId
		
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetUserDetails 
		FETCH NEXT FROM @cGetUserDetails INTO @iUserId, @iTotalLogins, @dtLastLogin  
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- fetch account number, state, zip, country of the license to which the user is belonged		
			SELECT @sAccountNumber = License.AccountNumber,
			@dtLicenseCreationDate = License.CreationDate,
			@sInstitutionName = License.InstitutionName,
			@sLicenseState =  State.StateName,
			@sLicenseZip = License.Zip, 
			@sLicenseCountry = Country.CountryName, 
			@iCardNumber = (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END)
			FROM AIAUserToLicenseEdition
			INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
			INNER JOIN License ON LicenseToEdition.LicenseId = License.Id
            INNER JOIN State ON License.StateId = State.Id
		    INNER JOIN Country ON License.CountryId = Country.Id
			WHERE AIAUserToLicenseEdition.UserId = @iUserId
			AND License.IsActive = 1
			AND License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber 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)	
			--AND License.LicenseTypeId <> 5 
			--AND License.Country = (CASE WHEN LEN(@sCountry)>0 THEN @sCountry ELSE License.Country END)
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch loginid, firstname, lastname, usertype of the user
				SELECT @sLoginId = AIAUser.LoginId, @sFirstName = AIAUser.Firstname, 
				@sLastName = AIAUser.LastName, @sUserType = UserType.Title
				FROM AIAUser 
				INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
				WHERE AIAUser.Id = @iUserId
				AND AIAUser.IsActive = 1
				
				IF @@Rowcount > 0
				Begin
				-- insert into the temporary table
				INSERT INTO #UsageReport 
				(LoginId, FirstName, LastName, AccountNumber,CardNumber ,UserType,LicenseCreationDate, LicenseState, LicenseZip,
				LicenseCountry,InstitutionName, TotalLogins, LastLoginDate) 
				VALUES(@sLoginId, @sFirstName, @sLastName, @sAccountNumber, @iCardNumber, @sUserType,@dtLicenseCreationDate,
				@sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName, @iTotalLogins, @dtLastLogin)
				end
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetUserDetails INTO @iUserId, @iTotalLogins, @dtLastLogin
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetUserDetails
	DEALLOCATE @cGetUserDetails

	-- Selecting the desired result from temporary table
	SELECT LoginId, FirstName, LastName, AccountNumber, CardNumber,UserType,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, LicenseZip, LicenseState,
	LicenseCountry,InstitutionName, TotalLogins, CONVERT(VARCHAR,LastLoginDate,101) as LastLogin FROM #UsageReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #UsageReport
END



GO
