

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/CT_appGetMQConnectionString.sp] ---------- 

-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/CT_appGetMQConnectionString.sp,v $ $Id: CT_appGetMQConnectionString.sp,v 1.1.2.6.20.1 2021/06/22 13:01:16 mnatarajan Exp $";
-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='CT_appGetMQConnectionString')
BEGIN
	print '>>> Drop Stored Procedure: CT_appGetMQConnectionString <<<'
	drop procedure CT_appGetMQConnectionString
END
IF EXISTS (select * from GxQscripts where name='CT_appGetMQConnectionString')
	delete from GxQscripts where name = 'CT_appGetMQConnectionString'
GO

IF EXISTS (select * from GXDBVersions where aliasname='CT_appGetMQConnectionString')
	delete from GXDBVersions where aliasname = 'CT_appGetMQConnectionString'
GO
print '... Creating Procedure: CT_appGetMQConnectionString'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure CT_appGetMQConnectionString
  @clientId INT,
  @out_xml xml output,
  @connectionType INT = 0,
  @generateOutput int = 1
AS
  DECLARE @outXml XML
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
DECLARE @r_errorCode INTEGER
DECLARE @r_errorMessage NVARCHAR(MAX)
DECLARE @r_connectString NVARCHAR(MAX)
DECLARE @r_mqClientId INTEGER
-- Having persistent connection here since its used in change tracking
DECLARE @isPersistentConn INT = 1
declare @mqClientId int = 0
----------- Below code is from APPGetMQConnectString.spb---------
-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
BEGIN
	SET @r_errorCode = 0
	SET @r_errorMessage = ''
	SET @r_connectString = ''
	declare @MQConnectionInfo table
	(
		clientId int,
		tcpPort int
	)
	declare @hostPorts nvarchar(2048)
	declare @semi nvarchar(1) = ''
	DECLARE @isIDPCommcellId INT = 0
	DECLARE @hostname NVARCHAR(256)
	-- Max inactivity duration in milliseconds. 30 seconds..
	DECLARE @maxInactivityDuration INT = 30000
	IF @isPersistentConn=1
	BEGIN
		SET @maxInactivityDuration = 0
	END
-- Check if Global IDP Commcell
IF EXISTS (SELECT 1 FROM APP_CommCell WHERE clientId = @clientId AND @clientId > 2)
BEGIN
    SELECT @isIDPCommcellId = 1
        FROM APP_CommCell AC
            INNER JOIN App_ThirdPartyApp ATPA
                ON ATPA.appName = AC.csGUID
                AND AC.clientId = @clientId
                AND AC.clientId > 2
            WHERE PROPS.value('(/props/nameValues[@name="Global IDP Commcell"]/@value)[1]','INT') = 1
END
IF @isIDPCommcellId = 1
    BEGIN
        SET @mqClientId = @clientId
    END
ELSE
	BEGIN
		IF NOT EXISTS(SELECT 1 FROM APP_VMToPMMap WHERE VMClientId = @clientId)
		BEGIN
SELECT @mqClientId = clientId FROM SimInstalledPackages WHERE simPackageID in (954, 1602) and clientId=@clientId
		END
		ELSE
		BEGIN
		        -- for cluster CS SimInstalledPAckages has entry on physical client and not on VMClient.
		        SELECT @mqClientId = App_VMToPMMAp.VMClientId FROM SimInstalledPackages
		        INNER JOIN App_VMToPMMAp on SimInstalledPackages.clientid = App_VMToPMMAp.PMClientId
where simPackageID in (954, 1602) and app_vmtopmmap.VMClientId=@clientId
		END
		if @mqClientId=0
		begin
		    select top 1 @mqClientId = clientId from SimInstalledPackages
		    inner join App_client C on SimInstalledPackages.clientId = C.Id
and (C.status&2)=0 and simPackageID in (954, 1602)
		    order by C.id ASC
		end
	END
IF @mqClientId > 1
        BEGIN
        --Insert into @MQConnectionInfo
        --select top 1 componentNameId, convert(xml, convert(varchar(max), attrval)).value('(CVInstallManager_MessageQueue/@messagequeueTCPPort)[1]', 'int')
        --from App_clientprop
        --where attrname='Message Queue Config' -- CV_PROP_MESSAGE_QUEUE_CONFIG
        --and componentNameId = @mqClientId
        -- Check if clientId belongs to a cluster.
        IF EXISTS(SELECT 1 FROM APP_VMToPMMap WHERE PMClientId = @mqClientId)
        BEGIN
            INSERT INTO @MQConnectionInfo(clientId) SELECT VMClientId from APP_VMToPMMap WHERE PMClientId = @mqClientId
        END
        ELSE
        BEGIN
            INSERT INTO @MQConnectionInfo(clientId) values(@mqClientId)
        END
        UPDATE M SET M.tcpPort = convert(xml, convert(varchar(max), attrval)).value('(CVInstallManager_MessageQueue/@messagequeueTCPPort)[1]', 'int')
        FROM App_ClientProp C JOIN @MQConnectionInfo M ON C.componentNameId = M.clientId
        WHERE C.attrName = 'Message Queue Config'
        -- If there are no entries in MQConnectionInfo then check for any client.
        if not exists(select 1 from @MQConnectionInfo WHERE tcpPort IS NOT NULL)
        begin
            DELETE FROM @MQConnectionInfo
            Insert into @MQConnectionInfo
            select top 1 componentNameId, convert(xml, convert(varchar(max), attrval)).value('(CVInstallManager_MessageQueue/@messagequeueTCPPort)[1]', 'int')
            from App_clientprop with (nolock)
            where attrname='Message Queue Config' -- CV_PROP_MESSAGE_QUEUE_CONFIG
            ORDER BY componentNameId
        end
        --tcp:--anantvm:80
        IF @isIDPCommcellId = 1
        BEGIN
            SELECT @hostname = attrVal
                FROM APP_ClientProp
                WHERE componentNameId = @clientId
                AND modified = 0
                AND attrName = N'Default Message Queue Hostname'
        END
        ELSE
        BEGIN
            select @hostname = App_client.net_hostName
            from @MQConnectionInfo MQConnectionInfo
            inner join App_client on MQConnectionInfo.clientId=App_client.id
        END
        select @hostPorts = @semi + @hostname+':'+convert(nvarchar(12),tcpPort), @semi=';'
        from @MQConnectionInfo MQConnectionInfo
        inner join App_client on MQConnectionInfo.clientId=App_client.id
        --- Any changes to format of connection string, please change it in MessageNotifier.cs GetMQConnectionInfo(int qClientId) method
		-- Changing maxInactivityDuration to 30 seconds. Its in milliseconds
        SET @r_connectString='failover:(tcp://'+@hostPorts+ ')?transport.timeout=3000&wireFormat.maxInactivityDuration='+CAST(@maxInactivityDuration AS NVARCHAR(MAX))+'&transport.maxReconnectAttempts=3&transport.startupMaxReconnectAttempts=5&connection.requestTimeout=3000&connection.watchTopicAdvisories=false'
    END
    ELSE
    BEGIN
        SET @r_errorMessage = 'Active MQ is not installed on this environment.'
        SET @r_errorCode = 1
    END
END
------------Code end---------------------------------------------
END
--SELECT @r_errorCode as r_errorCode, @r_errorMessage as r_errorMessage, @r_connectString as r_connectString, @mqClientId as r_mqClientId
set @outXml = (select @r_connectString '@connectionString',@mqClientId 'clientEntity/@clientId', @r_errorCode 'errorResponse/@errorCode', @r_errorMessage 'errorResponse/@errorString'
		for xml path('Api_MQConnectionInfo'))
set @out_xml = @outXml
GENERATE_OUTPUT:
	if @generateOutput = 1
		BEGIN
			select @outXml
		END
SET NOCOUNT OFF

IF EXISTS (select * from GxQscripts where name = 'CT_appGetMQConnectionString')
	delete from GxQscripts where name = 'CT_appGetMQConnectionString'
GO

IF EXISTS (select * from GXDBVersions where aliasname='CT_appGetMQConnectionString')
	delete from GXDBVersions where aliasname = 'CT_appGetMQConnectionString'
GO

insert into GXDBVersions values(2, 'CT_appGetMQConnectionString',  'v1.1.2.6.20.1', 'CT_appGetMQConnectionString', 'v1.1.2.6.20.1')
GO

