dbo.usp_GetUsageReport.sql
6.23 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
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