--- Please follow the below comments to insert SQL statements.
-- SLOWEST CLIENT BASED ON THROUGHPUT
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 44
DECLARE @surveyXML NVARCHAR(MAX)

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 
-- This query is to get the slowest clients based on throughput
-- Throughput is calculated as the ratio of Total Application Size to Total Write time per client
--		Shouldn't this be media size???
DECLARE @isPrivateRunning varchar(256)='';

IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting') or (@isPrivateRunning = 'Metrics Direct Dip'))
BEGIN	
	--In case of private metrics, enable hourly frequency thread along with activity
	IF OBJECT_ID('GetSurveyParamValue') IS NULL
	BEGIN
		IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = 'CommservSurveyScheduleIntervalOption')
		BEGIN
			IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
				INSERT INTO GXGlobalParam (name, value) SELECT 'CommservSurveyScheduleIntervalOption', '1'
			ELSE
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''CommservSurveyScheduleIntervalOption'', ''1'', 0, 0')
		END	
	END
END

DECLARE @TopN     int = 10
DECLARE @LastDays INTEGER = 1
DECLARE @CountAgedJobs INTEGER= 0
DECLARE	@TimeRangeBegin	INT = dbo.GetUnixTime(DATEADD(DAY, -@LastDays, GETUTCDATE()))
DECLARE @TimeRangeEnd INT = 0

IF @TimeRangeEnd <= 0
	SET @TimeRangeEnd = 0x7FFFFFFF

DECLARE @clients XML

IF OBJECT_ID('tempdb..#slowestClients') IS NOT NULL DROP TABLE #slowestClients
CREATE TABLE #slowestClients (
	clientId INT,
	clientName NVARCHAR(1024),
	appSize BIGINT,
	ThroughputMBS DECIMAL(10,2),
	NumberOfJobs INT
)
IF OBJECT_ID('tempdb..#TenantClients') IS NOT NULL DROP TABLE #TenantClients
CREATE TABLE #TenantClients (
	companyId INT,
	clientId INT,
	ThroughputMBS DECIMAL(10,2)
)

INSERT INTO #slowestClients
SELECT 
        A.clientId as '@clientId',
        dbo.NormalizeForXML(name) AS '@clientName',
        ISNULL(A.AppSize,0) AS '@appsize',
        (CASE WHEN Duration > 0 THEN ROUND(1.0*AppSize/Duration/1024.0/1024.0, 2) ELSE 0 END) as '@ThroughputMBS',
        NumberOfJobs as '@NumberOfJobs'
        FROM (
            SELECT  clientId, SUM(ISNULL(totalUnCompBytes, 0)) AppSize, SUM(ISNULL(totalWriteTime, 0)) Duration, COUNT(jobId) NumberOfJobs
            FROM    APP_Application A INNER JOIN JMBkpStats B ON A.id = B.appId
            WHERE   servEndDate BETWEEN @TimeRangeBegin AND @TimeRangeEnd
                AND status IN (1, 3, 14) and opType IN (4, 18, 30, 43, 76, 87, 91, 98, 101) AND commCellId = 2
                AND appType < 1000 AND appType NOT BETWEEN 600 AND 700
                AND (@CountAgedJobs = 1 OR dataStatus = 0)
            GROUP BY clientId 
            ) A
     INNER JOIN APP_Client C ON C.id = A.ClientId
     ORDER BY (CASE WHEN Duration > 0 THEN ROUND(1.0*AppSize/Duration/1024.0/1024.0, 2) ELSE 0 END) ASC,NumberOfJobs DESC
    
INSERT INTO #TenantClients
SELECT attrVal, 
	   componentNameId,
	   ThroughputMBS
FROM(
		SELECT attrVal, 
		   componentNameId,
		   ThroughputMBS,
		   row_number() over (partition by attrVal order by ThroughputMBS) as client_rank
		FROM APP_ClientProp WITH (NOLOCK)
		INNER JOIN #slowestClients SC ON SC.clientId = componentNameId
	    WHERE attrName = 'Installation Company Id' AND attrVal <> '0' AND modified = 0 AND LEN(attrVal) <= 10 AND ISNUMERIC(attrVal) = 1
) companyClients
where client_rank <= @TopN;

SET @clients = ( 
SELECT clientId AS '@clientId',
       clientName AS '@clientName',
       appSize AS '@appsize',
       ThroughputMBS AS '@ThroughputMBS',
       NumberOfJobs AS '@NumberOfJobs' FROM (
	SELECT TOP (@TopN) * FROM #slowestClients ORDER BY ThroughputMBS, NumberOfJobs DESC
	UNION
	SELECT SC.* FROM #slowestClients SC
	INNER JOIN #TenantClients TC ON TC.clientId = SC.clientId) C ORDER BY ThroughputMBS, NumberOfJobs DESC
FOR XML PATH ('Client'))

IF OBJECT_ID('tempdb..#slowestClients') IS NOT NULL DROP TABLE #slowestClients
IF OBJECT_ID('tempdb..#TenantClients') IS NOT NULL DROP TABLE #TenantClients

	 
SET @surveyXML = (
	SELECT
	@TopN as '@TopN',
	@clients
	FOR XML PATH ('SlowestClients') 
	-- Replace <<RESULTS_NAME>> with specific name
)

--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------


DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

--SELECT cast (@surveyXML as XML)

SET NOCOUNT OFF

--------- END - GENERATED CODE ---------