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/25494

Text File | 2010-06-15 | 4KB | 105 lines

CREATE PROCEDURE [dbo].[ISA_spAppTopUsersRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @TopCount int,
    @SortOrder varchar(100)
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblUserSummary', @FromDate, @ToDate, @ReportType)
    DECLARE @OrderColumn varchar(100)
    SET @OrderColumn =
            CASE
                WHEN @SortOrder = 'Requests' THEN 'Requests'
                WHEN @SortOrder = 'BytesIn' THEN 'BytesIn'
                WHEN @SortOrder = 'BytesOut' THEN 'BytesOut'
                ELSE 'TotalBytes'
            END;
    CREATE TABLE #Table1
    (
        UserName nvarchar(514),
        Requests bigint,
        BytesIn bigint,
        BytesOut bigint,
        TotalBytes bigint
    )
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT
            [UserName] ,
            SUM([Requests]) - SUM([WebRequests]) AS Requests,
            SUM([BytesIn]) - SUM([WebBytesIn]) AS BytesIn,
            SUM([BytesOut]) - SUM([WebBytesOut]) AS BytesOut,
            SUM([TotalBytes]) - SUM([WebTotalBytes]) AS TotalBytes
        FROM ' + @SummTableName + '
        WHERE
            ([Requests] - [WebRequests]) >0 AND
            ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
        GROUP BY
            [UserName]
        ORDER BY ' + QUOTENAME(@OrderColumn) + ' DESC;
        '
        INSERT #Table1
        EXEC(@strQuery)
    END
    CREATE TABLE #Table2
    (
        UserName nvarchar(514),
        Requests bigint,
        BytesIn bigint,
        BytesOut bigint,
        TotalBytes bigint
    )
    INSERT INTO #Table2
    SELECT TOP(@TopCount)
        [UserName] AS UserName,
        [Requests] AS Requests,
        [BytesIn] AS BytesIn,
        [BytesOut] AS BytesOut,
        [TotalBytes] AS TotalBytes
    FROM #Table1
    WHERE [UserName] != N'{[23124]}'
    INSERT INTO #Table2
    SELECT
       N'{[23124]}' As UserName,
        SUM(Requests) AS Requests,
        SUM([BytesIn]) AS BytesIn,
        SUM([BytesOut]) AS BytesOut,
        SUM([TotalBytes]) AS TotalBytes
    FROM #Table1 t1
    WHERE NOT EXISTS
        (SELECT *
         FROM #Table2 t2
         WHERE t1.UserName = t2.UserName)
    DECLARE @TotalRequests bigint
    SET @TotalRequests = (SELECT SUM(Requests) FROM #Table2 )
    IF (@TotalRequests = 0)
        SET @TotalRequests = 1
    DECLARE @totalBytes bigint
    SET @totalBytes = (SELECT SUM(TotalBytes) FROM #Table2 )
    IF (@totalBytes = 0)
        SET @totalBytes = 1
    DECLARE @TotalBytesIn bigint
    SET @TotalBytesIn = (SELECT SUM(BytesIn) FROM #Table2)
    IF (@TotalBytesIn = 0)
        SET @TotalBytesIn = 1
    DECLARE @TotalBytesOut bigint
    SET @TotalBytesOut = (SELECT SUM(BytesOut) FROM #Table2)
    IF (@TotalBytesOut = 0)
        SET @TotalBytesOut = 1
    SELECT
        [dbo].[fnUserOrIpAddressToText]([UserName]) AS [UserName],
        [Requests] AS Requests,
        (CAST([Requests] AS decimal) / @TotalRequests) AS RequestRatio,
        [BytesIn] AS BytesIn,
        (CAST([BytesIn] AS decimal) / @TotalBytesIn) AS BytesInRatio,
        [BytesOut] AS BytesOut,
        (CAST([BytesOut] AS decimal) / @TotalBytesOut) AS BytesOutRatio,
        [TotalBytes] AS TotalBytes,
        (CAST([TotalBytes] AS decimal) / @totalBytes) AS TotalByteRatio
    FROM #Table2
END