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

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