DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10362
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[COUNT_SECURE_LDAP_ENABLED] int,[COUNT_TPPM_ENABLED] int,[COUNT_BOTH_SECURE_LDAP_AND_TPPM_ENABLED] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @product_table TABLE (
    IS_SECURE_LDAP_ENABLED			BIT,
    IS_TPPM_ENABLED				BIT,
    ARE_BOTH_SECURE_LDAP_AND_TPPM_ENABLED	BIT
);

INSERT INTO @product_table
SELECT
CASE
	WHEN UMP.useSecureLdap <> 0 THEN ''TRUE''
	ELSE ''FALSE''
END AS ''IS_SECURE_LDAP_ENABLED'' ,

CASE
	WHEN FTM.tppmParams IS NOT NULL  and FTM.status = 1 THEN ''TRUE''
	ELSE ''FALSE''
END AS ''IS_TPPM_ENABLED'' ,

CASE
	WHEN FTM.tppmParams IS NOT NULL and FTM.status = 1 and UMP.useSecureLdap <> 0 THEN ''TRUE''
	ELSE ''FALSE''
END AS ''ARE_BOTH_SECURE_LDAP_AND_TPPM_ENABLED''

FROM UMDSProviders UMP WITH (NOLOCK)
LEFT JOIN  APP_FirewallTPPM FTM WITH (NOLOCK)
ON FTM.tppmParams = UMP.id
WHERE UMP.serviceType = 2 and UMP.id > 1

SELECT SUM(CASE WHEN IS_SECURE_LDAP_ENABLED = 1 THEN 1 ELSE 0 END) As ''COUNT_SECURE_LDAP_ENABLED'' ,
SUM(CASE WHEN IS_TPPM_ENABLED = 1 THEN 1 ELSE 0 END) ''COUNT_TPPM_ENABLED'',
SUM(CASE WHEN ARE_BOTH_SECURE_LDAP_AND_TPPM_ENABLED = 1 THEN 1 ELSE 0 END) ''COUNT_BOTH_SECURE_LDAP_AND_TPPM_ENABLED''  from @product_table ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [COUNT_SECURE_LDAP_ENABLED] AS '@COUNT_SECURE_LDAP_ENABLED',[COUNT_TPPM_ENABLED] AS '@COUNT_TPPM_ENABLED',[COUNT_BOTH_SECURE_LDAP_AND_TPPM_ENABLED] AS '@COUNT_BOTH_SECURE_LDAP_AND_TPPM_ENABLED' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10362'), type
)
  FOR XML PATH ('SurveyResults') 
)

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'))