home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
moodle.waes.ac.uk
/
moodle.waes.ac.uk.zip
/
moodle.waes.ac.uk
/
TMG
/
SP1-TMG-KB981324-AMD64-ENU.msp
/
PCW_CAB_SHFx2
/
F2143_msfpcui.dll
/
BINARY
/
25618
< prev
next >
Wrap
Text File
|
2010-06-15
|
7KB
|
156 lines
CREATE PROCEDURE [dbo].[ISA_spSoftBlockUsersRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopUsersCount int,
@TopSitesCount int
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblSoftBlockSummary', @FromDate, @ToDate, @ReportType)
CREATE TABLE #TopUsersSummary
(
SequenceNum nvarchar(3),
UserName nvarchar(514),
UserNameHidden nvarchar(514),
Site varchar(2048),
UrlCategoryName nvarchar(255),
Requests bigint,
RequestRatio float
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
-- build queries as a string and execute it, since there is no
-- other way to select from table, which name is in a variable
DECLARE @strQuery varchar(8000)
-- select top users, as requsted (will itereate over the result set)
CREATE TABLE #TopUsers
(
UserName nvarchar(514),
Requests bigint
)
SET @strQuery =
'SELECT TOP(' + CAST(@TopUsersCount AS varchar(10)) + ')
[UserName] AS [UserName],
SUM([Requests]) AS [Requests]
FROM ' + @SummTableName + '
WHERE
([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) +
' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ' AND [UserName] <> '''')
GROUP BY [UserName]
ORDER BY [Requests] DESC
'
INSERT INTO #TopUsers
EXEC(@strQuery)
-- from this temporary tables the data will be propagated to the #TopUsersSummary
CREATE TABLE #SingleUserSummary
(
Site varchar(2048),
UrlCategoryName nvarchar(255),
Requests bigint
)
CREATE TABLE #SingleUserSummaryCut
(
Site varchar(2048),
UrlCategoryName nvarchar(255),
Requests bigint,
RequestRatio float
)
DECLARE @UserName nvarchar(514)
DECLARE UsersIterator CURSOR FOR SELECT [UserName] FROM #TopUsers
OPEN UsersIterator
FETCH NEXT FROM UsersIterator INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Fetching data for user ' + @UserName
SET @strQuery =
'SELECT DISTINCT
summary.[Site] AS [Site],
urlMeta.[URLCategoryName] AS [URLCategoryName],
SUM(summary.[Requests]) AS [Requests]
FROM ' + @SummTableName + ' AS summary INNER JOIN [tblUrlfMetadata] AS urlMeta ON summary.[URLCategoryId] = urlMeta.[URLCategoryId]
WHERE
(summary.[date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) +
' AND summary.[date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) +
' AND summary.[UserName] = ''' + @UserName + ''')
GROUP BY [Site], [URLCategoryName]
ORDER BY [Requests] DESC
'
DELETE FROM #SingleUserSummary
INSERT INTO #SingleUserSummary
EXEC(@strQuery)
DECLARE @TotalRequests bigint
SET @TotalRequests = (SELECT SUM([Requests]) FROM #SingleUserSummary)
IF (@TotalRequests = 0)
SET @TotalRequests = 1
DELETE FROM #SingleUserSummaryCut
INSERT INTO #SingleUserSummaryCut
SELECT TOP(@TopSitesCount)
Site AS Site,
URLCategoryName AS UrlCategoryName,
Requests AS Requests,
(CAST([Requests] AS decimal) / @TotalRequests) AS RequestRatio
FROM #SingleUserSummary
WHERE [Site] != N'{[23124]}'
DECLARE @UserNameProcessed nvarchar(514) = [dbo].[fnUserOrIpAddressToText](@UserName)
INSERT INTO #TopUsersSummary
SELECT
ROW_NUMBER() OVER (ORDER BY [Requests] DESC) AS [SequenceNum],
CASE -- UserName should appear for the first line only
WHEN ROW_NUMBER() OVER (ORDER BY [Requests] DESC) = 1 THEN @UserNameProcessed
ELSE N''
END AS [UserName],
@UserNameProcessed AS [UserNameHidden], -- this field is needed for grouping when rendering the chart
[Site],
[UrlCategoryName],
[Requests],
[RequestRatio]
FROM #SingleUserSummaryCut
-- build the all others data
DELETE FROM #SingleUserSummaryCut
INSERT INTO #SingleUserSummaryCut
SELECT
N'{[23124]}' AS Site,
N' ' AS UrlCategoryName,
SUM([Requests]) AS Requests,
(CAST(SUM([Requests]) AS decimal) / @TotalRequests) AS RequestRatio
FROM #SingleUserSummary notIdentifiedData
WHERE NOT EXISTS
(
SELECT *
FROM
(
SELECT TOP(@TopSitesCount) *
FROM #SingleUserSummary
WHERE [Site] != N'{[23124]}'
) identifiedData
WHERE
(
(notIdentifiedData.Site = identifiedData.Site) AND
(notIdentifiedData.URLCategoryName = identifiedData.URLCategoryName)
)
)
-- insert only if there is actually some "All Others" data
INSERT INTO #TopUsersSummary
SELECT
N'' AS SequenceNum,
N'' AS UserName,
@UserNameProcessed AS UserNameHidden,
Site AS Site,
UrlCategoryName AS UrlCategoryName,
Requests AS Requests,
RequestRatio AS RequestRatio
FROM #SingleUserSummaryCut
WHERE [Requests] > 0
-- insert the total template per user (will be actually evaluated dynamically)
INSERT INTO #TopUsersSummary VALUES (N'', N'', N'', N'{[23133]}', N'', 0, 1)
-- insert empty delimiter
INSERT INTO #TopUsersSummary VALUES (N'', N'', N'', N'', N'', NULL, NULL)
FETCH NEXT FROM UsersIterator INTO @UserName
END
CLOSE UsersIterator
DEALLOCATE UsersIterator
END
SELECT * FROM #TopUsersSummary
END