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

CREATE PROCEDURE [dbo].[usp_GetUsageReport] 
	-- 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,
	@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 @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

	Select  RowNum,LoginId, FirstName, LastName, AccountNumber, CardNumber,UserType,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, LicenseZip, LicenseState,
	LicenseCountry,InstitutionName, TotalLogins, LastLogin
   from (  
 SELECT   ROW_NUMBER() OVER (ORDER BY LoginId) AS RowNum ,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) as usr
 WHERE RowNum > @pageLength * (@pageNo - 1) AND 	RowNum <= @pageLength * @pageNo   order by AccountNumber
    

	 --Calculate total number of records
  select @recordCount = count(ResultTable.LoginId) from (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) as ResultTable;
	-- Dropping the temporary table
	DROP TABLE #UsageReport
END