DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10366
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[isloopy_on-prem] int,[isMetallic] int,[number of on-prem commcells registered] int,[jupiter IDP] int,[No of IDP Service commcells] int,[is IDP service commcell(Jupiter)] int,[is Service commcell(Traditional commcell app)] int,[number of traditional commcell apps] int,[isRouter] int,[number of Router Service commcells] int,[is Router service commcell] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; Use CommServ

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--case 1: loopy - on-prem
declare @loopy_isOnPrem INT = 0 

--case 2: metallic. 
declare @loopy_isMetallic INT = 0 
declare @loopy_NumberOfOnPremRegistered INT = 0 

-- case 3. Jupiter IDP
DECLARE @jupiter_isIDP INT = 0 
DECLARE @jupiter_numberOfIDPServiceCommcellRegistered INT = 0 

--Case 4: IDP Service commcell 
DECLARE @jupiter_isIDPServiceCommcell INT = 0 

-- Case 5: Router
DECLARE @router_isRouter INT = 0 
DECLARE @router_numberOfRouterServiceCommcellRegistered INT = 0 

--Case 6: Traditional commcell app.
DECLARE @commcellApp_numberOfCommcellAppsRegistered INT = 0 
DECLARE @commcellApp_isServiceCommcell INT = 0 

--Case 7: Router service commcell 
DECLARE @router_isRouterServiceCommcell INT = 0 

IF NOT EXISTS (SELECT 1 FROM sys.objects where name = ''App_thirdpartyApp'')
--App_thirdpartyApp is used in all the cases and if the table is not present, it is safe to say none of the login types have been configured.
BEGIN 
GOTO PROC_END
END 


--Case 1:
IF EXISTS((select 1 from GXGlobalParam where name =''completedSetups'' and value like ''%"metallic":true%''))
BEGIN 
	SET @loopy_isOnPrem = 1;
    GOTO PROC_END
END

--Case 2:
SELECT @loopy_NumberOfOnPremRegistered = COUNT(DISTINCT ATP.id) 
	from APP_ComponentProp as comp (nolock) 
	inner join App_ThirdPartyApp as ATP (nolock) 
		ON comp.componentId = ATP.id 
		where comp.componentType = 1034 and comp.propertyTypeId = 5 -- CV_COMPONENT_SUBSCRIBER_REDIRECTURL 
IF (@loopy_NumberOfOnPremRegistered > 0)
BEGIN 
	SET @loopy_isMetallic = 1 
    GOTO PROC_END
END 

--case 3: 
select @jupiter_numberOfIDPServiceCommcellRegistered = COUNT(DISTINCT ATP.id) from APP_ComponentProp as appcomp (nolock) 
	inner join app_commcell as commcell (nolock) on commcell.id = appcomp.componentId
	inner join App_ThirdPartyApp as ATP (nolock) ON ATP.appName = commcell.csGUID 
	WHERE appcomp.componentType = 1048 
		and appcomp.propertyTypeId = 2 
		and appcomp.longlongVal = 1
		and appcomp.componentId <> 2
IF (@jupiter_numberOfIDPServiceCommcellRegistered > 0)
BEGIN 
    SET @jupiter_isIDP = 1
    GOTO PROC_END
END 


--Case 4: IDP Service commcell 
IF EXISTS(select 1 from APP_ComponentProp as appcomp (nolock) 
	inner join app_commcell as commcell (nolock) on commcell.id = appcomp.componentId
	inner join App_ThirdPartyApp as ATP (nolock) ON ATP.appName = commcell.csGUID 
	WHERE appcomp.componentType = 1048 
		and appcomp.propertyTypeId = 2 
		and appcomp.longlongVal = 1
		and appcomp.componentId = 2)
BEGIN 
    SET @jupiter_isIDPServiceCommcell = 1
    GOTO PROC_END
END 


--Case 5: Traditional commcell app.
select @commcellApp_numberOfCommcellAppsRegistered = COUNT(DISTINCT ATP.id) 
    from App_ThirdPartyApp as ATP (nolock) inner join APP_ComponentProp as compProp (nolock) 
    ON ATP.id = compProp.componentId
    where ATP.appType = 3 and compProp.componentType = 1034 and compProp.propertyTypeId in (12,13,14,15,61)  -- propertyTypeId  ----> ENTITY TYPE 1.all users ----- 12 2. User----------13 3. all groups ----14 4. UserGroup-----15 5. NameServer----61
        AND (ATP.props.value(''(/props/nameValues[@name="Enable Sso Redirect"]/@value)[1]'',''nvarchar(max)'') = ''1''
            OR ATP.props.value(''(/props/nameValues[@name="Global IDP Commcell"]/@value)[1]'',''nvarchar(max)'') = ''1'')
IF(@commcellApp_numberOfCommcellAppsRegistered >0)
BEGIN 
	SET @commcellApp_isServiceCommcell = 1
    GOTO PROC_END
END 

-- Case 6: Router
select @router_numberOfRouterServiceCommcellRegistered = COUNT(DISTINCT ATP.id) from APP_ComponentProp as appcomp (nolock) 
	inner join app_commcell as commcell (nolock) on commcell.id = appcomp.componentId
	inner join App_ThirdPartyApp as ATP (nolock) ON ATP.appName = commcell.csGUID 
	WHERE appcomp.componentType = 1048 
		and appcomp.propertyTypeId = 2 
		and appcomp.longlongVal = 0
		and appcomp.componentId <> 2
IF(@router_numberOfRouterServiceCommcellRegistered > 0)
BEGIN 
	SET @router_isRouter = 1
    GOTO PROC_END
END 

--Case 7: Router service commcell. router commcell doesn''t involve SAML token exchange. added it to find how many customers are using it. 
IF EXISTS(select 1 from APP_ComponentProp as appcomp (nolock) 
	inner join app_commcell as commcell (nolock) on commcell.id = appcomp.componentId
	inner join App_ThirdPartyApp as ATP (nolock) ON ATP.appName = commcell.csGUID 
	WHERE appcomp.componentType = 1048 
		and appcomp.propertyTypeId = 2 
		and appcomp.longlongVal = 0
		and appcomp.componentId = 2)
BEGIN 
    SET @router_isRouterServiceCommcell = 1
    GOTO PROC_END
END 


PROC_END:
SELECT @loopy_isOnPrem ''isloopy_on-prem'',
        @loopy_isMetallic as ''isMetallic'', @loopy_NumberOfOnPremRegistered as ''number of on-prem commcells registered'',
        @jupiter_isIDP ''jupiter IDP'', @jupiter_numberOfIDPServiceCommcellRegistered ''No of IDP Service commcells'',
        @jupiter_isIDPServiceCommcell ''is IDP service commcell(Jupiter)'',
		@commcellApp_isServiceCommcell as ''is Service commcell(Traditional commcell app)'', @commcellApp_numberOfCommcellAppsRegistered as ''number of traditional commcell apps'',
        @router_isRouter as ''isRouter'', @router_numberOfRouterServiceCommcellRegistered ''number of Router Service commcells'',
		@router_isRouterServiceCommcell ''is Router service commcell'' ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [isloopy_on-prem] AS '@isloopy_on-prem',[isMetallic] AS '@isMetallic',[number of on-prem commcells registered] AS '@Col_-382237609',[jupiter IDP] AS '@Col_1888558354',[No of IDP Service commcells] AS '@Col_1717659397',[is IDP service commcell(Jupiter)] AS '@Col_1068703066',[is Service commcell(Traditional commcell app)] AS '@Col_-2124370520',[number of traditional commcell apps] AS '@Col_657192601',[isRouter] AS '@isRouter',[number of Router Service commcells] AS '@Col_1175656661',[is Router service commcell] AS '@Col_79400538' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10366'), 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'))