

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

-- ----------------------------------------------------------------------
--
--           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.
-- ----------------------------------------------------------------------*/
-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/AppEmailConfigPolicyAssocGetInfo.sp,v $ $Id: AppEmailConfigPolicyAssocGetInfo.sp,v 1.1.4.56.14.4 2021/07/07 00:29:05 nuppu Exp $";
--	+===================================================================+
--	|  				AppEmailConfigPolicyAssocGetInfo					|
--	| Procedure for get info from APP_EmailConfigPolicyAssoc table		|
--	+===================================================================+
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppEmailConfigPolicyAssocGetInfo')
	delete from GXDBVersions where aliasname = 'AppEmailConfigPolicyAssocGetInfo'
GO
print '... Creating Procedure: AppEmailConfigPolicyAssocGetInfo'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppEmailConfigPolicyAssocGetInfo
-----------------------------------------------------------
---    PARAMETERS   &   OUTPUTS							---
  @i_xmlString XML
-----------------------------------------------------------
AS
SET NOCOUNT ON
BEGIN
	DECLARE	@o_xmlString		XML	= NULL
	DECLARE @errorCode			INT = 0
	DECLARE @errorString		NVARCHAR(MAX) = ''
	DECLARE @nowTime			INT = datediff(second, '01/01/1970', GETDATE())
	DECLARE @userId				INT = (SELECT TOP 1 id FROM UMUsers WHERE flags&0x40=0x40)
	DECLARE @inPropertyLevel		INT	= 10 -- ListPropertyLevels_BasicProperties
	DECLARE @subClientId			INT	= 0
	DECLARE @discoverMailboxes		INT = 0
	DECLARE @discoverByType			INT = 1	-- EmailDiscoverByType.USER
	DECLARE @DISCOVER_TYPE_JOURNALRECIPIENT	INT = 13
	--Email status
	DECLARE @DELETED				INT = 1
	DECLARE @DO_NOT_ARCHIVE 		INT = 3
	DECLARE @isSelectInactiveMailboxes BIT = 0
	DECLARE @isSelectDoNotArchiveMailboxes BIT = 0
	DECLARE @discoverByStatus	INT = 0
	DECLARE @environmentType    INT = 0
	DECLARE @ClientId			INT = 0
	DECLARE @backupsetId		INT = 0
	DECLARE @instanceId 		INT = 0
	DECLARE @appType			INT = 0
	DECLARE @hasPermission INT = 0
	DECLARE @INSUFFICIENT_PERMISSIONS	INT = 111
	DECLARE @O365Field_DISPLAYNAME INT = 1
	DECLARE @O365Field_URL INT = 2
	DECLARE @O365Field_WEBTYPE INT = 3
	DECLARE @O365Field_ITEMTYPE INT = 4
	DECLARE @O365Field_STATUS INT = 5
	DECLARE @O365Field_SIZE	  INT = 6
	DECLARE @O365Field_PLAN	INT = 7
	DECLARE @O365Field_BACKUP_REF_TIME INT = 8
	DECLARE @O365Field_AUTO_DISCOVER  INT = 9
	DECLARE @O365Field_ONEDRIVE_QUOTA INT = 10
	DECLARE @O365Field_NUMBER_OF_FILES INT = 11
	DECLARE @O365Field_SMTP_ADDRESS INT = 12
	DECLARE @O365Field_MAILBOX_TYPE INT = 13
	DECLARE @O365Field_DATABASE INT = 14
	DECLARE @O365Field_DISCOVER_TYPE INT = 15
	DECLARE @O365Field_EMAILS_BACKEDUP INT = 16
	DECLARE @O365Field_ALIAS INT = 17
	--Subclient aggregate Stats
	DECLARE @emailCount BIGINT = 0
	DECLARE @ciCount BIGINT = 0
	DECLARE @appSize BIGINT = 0
	DECLARE @lastBackupJobTime  INT = 0
	DECLARE @lastCiJobRantime	INT = 0
	DECLARE @idxCollectionTime  INT = 0
	DECLARE @modified  INT = 0
	DECLARE @mailboxCount BIGINT = 0
	DECLARE @idxXml XML = ''
	DECLARE @pageSz INT = 0
	DECLARE @pageNo	INT = 0
	DECLARE @search	INT = 0
	DECLARE @searchKey NVARCHAR(MAX) = ''
	DECLARE @filterColumn 					INT = 0
	DECLARE	@filterString 						NVARCHAR(40) = ''
	DECLARE @SortColumn						INT = 0
	DECLARE @SortOrder 						INT  = 0
	DECLARE @bIncludeDeleted				INT = 0
	--Prepare FIlter list table
	DECLARE @filtertbl TABLE (filter xml)
	DECLARE @filterlisttbl TABLE (fCol nvarchar(max), fStr nvarchar(max), rangeMin BIGINT, rangeMax BIGINT)
	INSERT INTO @filtertbl SELECT R.ref.query('.') AS filter
	FROM @i_xmlString.nodes ('Ida_GetEmailPolicyAssociationReq/filterInfoList') R(ref)
	INSERT INTO @filterlisttbl SELECT DISTINCT
		filter.value ('filterInfoList[1]/@filterColumn', 'INT') AS fCol ,
		filter.value('filterInfoList[1]/@filterString', 'nvarchar(max)') AS fStr,
		filter.value ('filterInfoList[1]/@filterRangeMin', 'BIGINT') AS rangeMin ,
		filter.value('filterInfoList[1]/@filterRangeMax', 'BIGINT') AS rangeMax
    FROM @filtertbl
	IF EXISTS (SELECT TOP 1 1 FROM @filterlisttbl) set @filterColumn = 1
	Update @filterlisttbl set rangeMax = 9223372036854775807 -- maxBigInt value
	WHERE fCol IN (@O365Field_SIZE, @O365Field_EMAILS_BACKEDUP, @O365Field_BACKUP_REF_TIME) AND ((rangeMax IS NULL) OR (rangeMax = 0))
	Update @filterlisttbl set rangeMin = 0
	WHERE fCol IN (@O365Field_SIZE, @O365Field_EMAILS_BACKEDUP, @O365Field_BACKUP_REF_TIME) AND (rangeMin IS NULL)
	SELECT @subClientId = ISNULL(ref.value('emailAssociation[1]/subclientEntity[1]/@subclientId', 'INT'), 0), @discoverByType = ISNULL(ref.value('@discoverType', 'INT'), @discoverByType),
		@inPropertyLevel = ISNULL(ref.value('@level', 'INT'), @inPropertyLevel), @userId = ISNULL(ref.value('processinginstructioninfo[1]/user[1]/@userId', 'int'), @userId),
		@discoverMailboxes = ISNULL(ref.value('@discoverMailboxes', 'INT'), @discoverMailboxes),
		@isSelectInactiveMailboxes = ISNULL(ref.value('emailAssociation[1]/advanceOptions[1]/@isSelectInactiveMailboxes' , 'BIT'), 0),
		@isSelectDoNotArchiveMailboxes = ISNULL(ref.value('emailAssociation[1]/advanceOptions[1]/@isSelectDoNotArchiveMailboxes' , 'BIT'), 0),
		@discoverByStatus = ISNULL(ref.value('emailAssociation[1]/@emailStatus','INT'), 0),
		@pageNo = ISNULL(ref.value('pagingInfo[1]/@pageNumber', 'INTEGER'),0),
		@pageSz = ISNULL(ref.value('pagingInfo[1]/@pageSize', 'INTEGER'),0),
		@search = ISNULL(ref.value('searchInfo[1]/@isSearch', 'INTEGER'),0),
		@searchKey = ISNULL(ref.value('searchInfo[1]/@searchKey', 'NVARCHAR(MAX)'),0),
		@SortColumn = ISNULL(ref.value('sortInfo[1]/@sortColumn', 'INTEGER'),0),
		@SortOrder = ISNULL(ref.value('sortInfo[1]/@sortOrder', 'INTEGER'),0),
		@bIncludeDeleted = ISNULL(ref.value('@bIncludeDeleted', 'INT'),0)
		FROM @i_xmlString.nodes ('Ida_GetEmailPolicyAssociationReq') R(ref)
	IF @pageNo < 0  SET @pageNo = 0 --default page number is 0
	IF @pageSz <= 0 SET @pageSz = 0 -- default no paging
	IF @search = 1 AND @searchKey = ''
	BEGIN
		SET @errorCode = 1
		SET @errorString = 'Error : Invalid Search key'
		GOTO ERROR
	END
	IF EXISTS  (select TOP 1 1 FROM @filterlisttbl WHERE fCOl = @O365Field_STATUS AND CONVERT(INTEGER, fStr)  = 1)
	SET @isSelectInactiveMailboxes = 1
	IF object_id('tempdb.dbo.#emailConfiguration_DiscoveryTable') is not null
		DROP TABLE #emailConfiguration_DiscoveryTable
	CREATE TABLE #emailConfiguration_DiscoveryTable
(assocId INT, userGuid VARCHAR(40), displayName NVARCHAR(255) NULL DEFAULT '', aliasName NVARCHAR(255) NULL DEFAULT '', smtpAdrress NVARCHAR(255) NULL DEFAULT '',
				discoverByType INT NULL DEFAULT 0, isAutoDiscovered INT NULL DEFAULT 0, mailBoxType INT, emailStatus INT NULL DEFAULT 0, deleted INT, autoDiscover INT, subclientId INT, planId INT DEFAULT 0, numberOfEmails BIGINT DEFAULT 0 , idxCollectionTime INT DEFAULT 0, backupReferenceTime INT DEFAULT 0, mailboxSize BIGINT DEFAULT 0, modified INT DEFAULT 0)
	CREATE CLUSTERED INDEX #emailConfiguration_DiscoveryTable_userGuid_Index1 ON #emailConfiguration_DiscoveryTable ([userGuid])
	IF object_id('tempdb.dbo.#subClientTable') IS NOT NULL
		DROP TABLE #subClientTable
	CREATE TABLE #subClientTable
				(id INT IDENTITY(1,1), subclientId INT)
	CREATE UNIQUE CLUSTERED INDEX subClientTable_subclientId_Index1 ON #subClientTable ([subclientId])
	SELECT @appType = appTypeId , @ClientId = clientId , @backupSetId = backupset , @instanceId = instance
	FROM App_Application (NOLOCK) where id = @subclientId
EXEC sec_checkPermissionOnEntity @userId, 0, @hasPermission OUTPUT, 3, @ClientId, 4, @appType, 5, @instanceId, 6, @backupsetId, 7, @subclientId
	IF @hasPermission <> 1
	BEGIN
			SET @errorCode = @INSUFFICIENT_PERMISSIONS
			SET @errorString = 'Error : User does not have required permission to perform this operation'
			GOTO ERROR
	END
	INSERT INTO #subClientTable
	SELECT @subClientId
	WHERE @subClientId > 0
	SET @environmentType = (SELECT attrVal from APP_IDAProp (NOLOCK) IP
							JOIN APP_IDAName (NOLOCK) IDN ON IP.componentNameId = IDN.id
							JOIN APP_Application (NOLOCK) APP ON APP.clientId = IDN.clientId AND APP.id = @subClientId
WHERE IP.attrName = 'Exchange Environment Type' AND IP.modified = 0)
	IF @discoverMailboxes = 1 AND @discoverByType = 6		--EmailDiscoverByType.CONTENTSTORE
	BEGIN
		IF object_id('tempdb.dbo.#tempSecurityObjects') IS NOT NULL
			DROP TABLE #tempSecurityObjects
create table  #tempSecurityObjects ( [capabilities]  bigint	NOT null, [flag]		  int NOT null default 0, [commCellId]    int	NOT null default 2, [clientGroupId] int	NOT null default 0, [clientId]      int	NOT null default 0, [appTypeId]     int	NOT null default 0, [instanceId]    int	NOT null default 0, [backupsetId]   int	NOT null default 0, [subClientId]   int	NOT null default 0, [mediaAgentId]  int	NOT null default 0, [libraryId]     int	NOT null default 0, [archGroupId]   int	NOT null default 0, [vtContainerId] int	NOT null default 0, [vtPolicyId]    int	NOT null default 0, [vtShelfId]     int	NOT null default 0, [reviewSetId]   int	NOT null default 0, [querySetId]	  int	NOT null default 0, [downloadSetId] int	NOT null default 0, [ermId]   	  int	NOT null default 0, [legalHoldId]   int	NOT null default 0, [tagId]		  int	NOT null default 0, [cdPolicyId]    int	NOT null default 0, [complianceReportId]   int	NOT null default 0, [taskId]        int	NOT null default 0, [workflowId]    int	NOT null default 0, [logMonitoringPolicyId] int	NOT null default 0, [arrayId]       int	NOT null default 0, [cloudId]       int	NOT null default 0, [userGroupId]   int	NOT null default 0, [providerId]    int	NOT null default 0, [entityId1]     int	NOT null default 0, [entityId2]     int	NOT null default 0, [entityId3]     int	NOT null default 0, [entityId4]     int	NOT null default 0, [entityId5]     int	NOT null default 0, [entityId6]     int	NOT null default 0, [entityId7]     int	NOT null default 0, [entityId8]     int	NOT null default 0, [entityId9]     int	NOT null default 0  )
		CREATE CLUSTERED INDEX #tempSecurityObjectsTable_clientId_Index1 ON #tempSecurityObjects ([clientId])
EXEC sec_getUserObjects @userId, 2013, 0, '#tempSecurityObjects'
		INSERT INTO #subClientTable
		SELECT DISTINCT APP.id
		FROM #tempSecurityObjects SO
JOIN APP_Application APP (READUNCOMMITTED) ON APP.clientId = SO.clientId AND APP.appTypeId = 137 AND APP.subclientStatus&(CAST(0x00002 AS INT)+CAST(0x00004 AS INT)) = 0
WHERE capabilities & CAST(POWER(2.0, 2 - 1) AS BIGINT) <> 0
		AND APP.id <> @subClientId
		IF object_id('tempdb.dbo.#tempSecurityObjects') IS NOT NULL
			DROP TABLE #tempSecurityObjects
	END
	IF object_id('tempdb.dbo.#contentStore_mailbox_properties') is not null
		DROP TABLE #contentStore_mailbox_properties
	CREATE TABLE #contentStore_mailbox_properties
(componenetNameId_t INT, attrName_t NVARCHAR(255), attrVal_t NVARCHAR(1024))
	IF object_id('tempdb.dbo.#contentStore_mailbox_properties_real') is not null
		DROP TABLE #contentStore_mailbox_properties_real
	CREATE TABLE #contentStore_mailbox_properties_real
(componenetNameId INT, enableAutoIPDetection INT, whiteListIPs NVARCHAR(1024), certificateName NVARCHAR(255))
	CREATE UNIQUE CLUSTERED INDEX contentStore_mailbox_properties_real_componenetNameId_Index1 ON #contentStore_mailbox_properties_real ([componenetNameId])
	INSERT INTO #contentStore_mailbox_properties
	SELECT ECMP.componentNameId, ECMP.attrName, ECMP.attrVal
	FROM APP_EmailContentStoreMailBoxProperties ECMP
	JOIN APP_EmailConfigPolicyAssoc EP ON EP.assocId = ECMP.componentNameId AND ECMP.attrVal IS NOT NULL AND ECMP.modified = 0 AND ECMP.attrType NOT IN (3,5)
	JOIN #subClientTable ST ON ST.subclientId = EP.subClientId
	INSERT INTO #contentStore_mailbox_properties_real (componenetNameId,enableAutoIPDetection,whiteListIPs,certificateName)
	SELECT [componenetNameId_t], [enableAutoIPDetection],[whiteListIPs], [certificateName]
	FROM
	( SELECT componenetNameId_t,attrName_t,attrVal_t FROM #contentStore_mailbox_properties) as src
	PIVOT
	( MAX(attrVal_t) FOR attrName_t IN ([enableAutoIPDetection],[whiteListIPs], [certificateName])) as piv;
	IF object_id('tempdb.dbo.#contentStore_mailbox_properties') is not null 	DROP TABLE #contentStore_mailbox_properties
	SELECT @lastBackupJobTime = ISNULL(max(J.servEndDate), 0)
    From Jmbkpstats J (NOLOCK)
    JOIN JmJobStats T (NOLOCK) ON T.jobId = J.jobId AND T.commCellId = J.commCellId
	WHERE J.appType = 137 AND J.appId = @subClientId AND J.status IN (1, 3)
	AND T.opType = 4 AND T.subOpType =98
	SELECT   @mailboxCount = ISNULL(count(Distinct A.assocId), 0)
    FROM APP_EmailConfigPolicyAssoc (NOLOCK) A
        WHERE  A.subClientId = @subClientId
        AND A.mailBoxType IN (1, 2, 3)
        AND A.modified = 0
	SELECT @lastCiJobRantime = ISNULL(max(JM.servStart), 0)
	FROM JMJobStats (NOLOCK) J
	JOIN JMAdminJobStatsTable (NOLOCK) JM ON JM.jobId = J.jobId AND JM.commCellId = J.commCellId
	WHERE J.opType = 113 AND  JM.appID = @subClientId AND  JM.status IN (1,3)
	SELECT @idxXml = I.properties  From IdxDbState (nolock) I
INNER JOIN App_IndexDBInfo (nolock) AI ON AI.Id = I.dbId AND AI.type = 137
	INNER JOIN APP_Application (nolock) A ON A.id = @subClientId AND A.backupSet = AI.backupSetId
	SET @ciCount = ISNULL((SELECT R.ref.value('@itemsContentIndexed', 'BIGINT') from @idxXml.nodes('(/Indexing_DbStats/apps/stat)[1]') R(ref)), 0)
	SET @emailCount = ISNULL((SELECT R.ref.value('@itemsIndexed', 'BIGINT') from @idxXml.nodes('(/Indexing_DbStats/apps/stat)[1]') R(ref)), 0)
	SET @appSize = ISNULL((SELECT R.ref.value('@applicationSize', 'BIGINT') from @idxXml.nodes('(/Indexing_DbStats/apps/stat)[1]') R(ref)), 0)
	SET @idxCollectionTime = ISNULL((SELECT R.ref.value('@lastPlayedBkpJobTime', 'BIGINT') from @idxXml.nodes('(/Indexing_DbStats/apps)[1]') R(ref)), 0)
	--Handle journal recipient request
	IF @discoverByType = @DISCOVER_TYPE_JOURNALRECIPIENT
	BEGIN
		SET @o_xmlString =
		( Select @environmentType '@environmentType',(
				SELECT @discoverByType '@discoverType',
				(
					SELECT EA.aliasName '@aliasName', EA.smtpAdrress '@smtpAdrress'
					FOR XML path('userMailBoxInfo'), TYPE
				),
				(
						SELECT EA.subClientId '@subclientId', EA.clientId '@clientId', EA.backupsetId '@backupsetId'
						FOR XML PATH('subclientEntity'), TYPE
				)
				FROM APP_EmailConfigPolicyAssoc EA (READUNCOMMITTED) WHERE EA.discoverByType = @discoverByType  and EA.subClientId = @subClientId AND EA.modified = 0
				FOR XML PATH('associations'), TYPE)
				FOR XML PATH('Ida_GetEmailAssociationsResp')
		)
	END
	ELSE
	BEGIN
	IF @inPropertyLevel = 30 -- ListPropertyLevels_AllProperties
	BEGIN
	-- FORM THE XML RESPONSE
		SET @o_xmlString =
				( Select @environmentType '@environmentType',(
					SELECT discoverByType '@discoverType',
					CASE WHEN EA.deleted=0 AND EA.status <> 0 THEN  EA.status ELSE EA.deleted END '@emailStatus' ,
					(
						CASE WHEN @discoverByType <> 6	--EmailDiscoverByType.CONTENTSTORE
							AND EA.mailBoxType <> 3		--MailboxType.SMTP_MAILBOXES
							THEN
						(
							SELECT EA.displayName '@displayName', EA.aliasName '@aliasName', EA.smtpAdrress '@smtpAdrress',
							EA.databaseName '@databaseName', EA.exchangeServer '@exchangeServer', EA.mailBoxType '@mailBoxType', isAutoDiscovered '@isAutoDiscoveredUser',
							EA.exchangeVersion '@exchangeVersion', EA.msExchRecipientTypeDetails '@msExchRecipientTypeDetails',
							EA.objectGuid '@objectGuid', EA.azureGuid '@azureGuid', EA.immutableId '@immutableId', EA.numberOfEmails '@numberOfEmails', EA.mailboxSize '@mailboxSize',
							(
SELECT 13 '@_type_',  EA.userGuid '@userGUID'
								FOR XML PATH('user'), TYPE
							)
							, ( SELECT EA.backupReferenceTime as '@time'  FOR XML PATH ('lastArchiveJobRanTime'), TYPE )
							, ( SELECT EA.idxCollectionTime as '@time'  FOR XML PATH ('idxCollectionTime'), TYPE )
							, ( SELECT EA.modified as '@time'  FOR XML PATH ('modified'), TYPE )
							, (SELECT TT.created as '@time'
                                FROM
                                (
                                   SELECT TOP 1 AAA.created FROM APP_EmailConfigPolicyAssoc (NOLOCK) AAA where AAA.userGuid = EA.userGuid AND AAA.subClientId = @subClientId ORDER BY AAA.created ASC
                                ) TT
                                FOR XML PATH ('created'), TYPE )
							FOR XML PATH('userMailBoxInfo'), TYPE
						) END
					),
					(
						CASE WHEN @discoverByType = 6	--EmailDiscoverByType.CONTENTSTORE
							AND EA.mailBoxType = 3		--MailboxType.SMTP_MAILBOXES
							THEN
						(
							SELECT EA.displayName '@displayName', EA.aliasName '@aliasName', EA.smtpAdrress '@smtpAdrress',
							EA.databaseName '@databaseName', EA.exchangeServer '@exchangeServer', EA.mailBoxType '@mailBoxType', isAutoDiscovered '@isAutoDiscoveredUser',
							CMPR.enableAutoIPDetection '@enableAutoIPDetection', CMPR.whiteListIPs '@whiteListIPs', CMPR.certificateName '@certificateName', EA.numberOfEmails '@numberOfEmails',  EA.mailboxSize '@mailboxSize',
							(
SELECT 13 '@_type_',  EA.userGuid '@userGUID'
								FOR XML PATH('user'), TYPE
							),
							(
								SELECT CAST(CP.stringVal AS XML) FROM APP_ComponentProp CP (READUNCOMMITTED)
WHERE CP.componentType = 12		--_CV_COMPONENT_TYPE_CONENTSTORE_PROP
AND CP.propertyTypeId = 1160	--_CV_COMPONENT_EXONEPASS_CONTENTSTORE_CLIENTS
								AND CP.componentId = assocId
								AND CP.modified = 0
							)
							, ( SELECT EA.backupReferenceTime as '@time'  FOR XML PATH ('lastArchiveJobRanTime'), TYPE )
							, ( SELECT EA.idxCollectionTime as '@time'  FOR XML PATH ('idxCollectionTime'), TYPE )
							, ( SELECT EA.modified as '@time'  FOR XML PATH ('modified'), TYPE )
							, (SELECT TT.created as '@time'
                                FROM
                                (
                                   SELECT TOP 1 AAA.created FROM APP_EmailConfigPolicyAssoc (NOLOCK) AAA where AAA.userGuid = EA.userGuid AND AAA.subClientId = @subClientId ORDER BY AAA.created ASC
                                ) TT
                                FOR XML PATH ('created'), TYPE )
							FOR XML PATH('contentStoreMailbox'), TYPE
						) END
					),
					(
						SELECT EA.databaseName '@databaseName', EA.exchangeServer '@exchangeServer'
						FOR XML PATH('databaseInfo'), TYPE
					),
					(
						SELECT EA.adGroupName '@adGroupName'
						FOR XML PATH('adGroupsInfo'), TYPE
					),
					(
						CASE WHEN (@discoverByType = 4 OR @discoverByType = 8)	--EmailDiscoverByType.AUTO_DISCOVER || EmailDiscoverByType.ALL_USERS
							AND EA.discoverByType = 8	--MailboxType.ALL_USERS
						THEN
						(	SELECT EA.displayName '@associationName',
								EA.discoverByType '@associationType'
							FOR XML PATH('genericAssociationInfo'), TYPE
						)
						END
					),
					(
						CASE WHEN (@discoverByType = 4 OR @discoverByType = 11)	--EmailDiscoverByType.AUTO_DISCOVER || EmailDiscoverByType.ALL_OFFICE365_GROUPS
							AND EA.discoverByType = 11	--MailboxType.ALL_OFFICE365_GROUPS
						THEN
						(	SELECT EA.displayName '@associationName',
								EA.discoverByType '@associationType'
							FOR XML PATH('genericAssociationInfo'), TYPE
						)
						END
					),
					(
						CASE WHEN (@discoverByType = 4 OR @discoverByType = 12)	--EmailDiscoverByType.AUTO_DISCOVER || EmailDiscoverByType.ALL_PUBLIC_FOLDERS
							AND EA.discoverByType = 12	--MailboxType.ALL_PUBLIC_FOLDERS
						THEN
						(	(SELECT
							(SELECT EA.userGuid 'user/@userGUID' FOR XML PATH('publicFolderInfo'), TYPE),
							(SELECT EA.displayName '@associationName',	EA.discoverByType '@associationType'FOR XML PATH('genericAssociationInfo'),	TYPE)
							FOR XML PATH(''), TYPE)
						)
						END
					),
					(
						SELECT
						(
							SELECT CP.ConfigPolicytype AS '@policyType',
							(
								-- Valid for ARCHIVE, CLEANUP, RETENTION, JOURNAL
								CASE WHEN Cp.policyType IN (1,2,3,4)
								THEN
									(SELECT CP.policyType 'emailPolicy/@emailPolicyType'
									FOR XML PATH ('detail'), TYPE)
								-- Valid for CONTAINERS
								 WHEN CP.policyType IN (6)
								 THEN
									(SELECT CP.policyType 'eePolicy/@extractionPolicyType'
										FOR XML PATH ('detail'), TYPE)
								 END
							),
							(
								SELECT CP.policyId '@policyId', CP.policyName '@policyName'
								FOR XML PATH ('policyEntity'), TYPE
							)
							FROM
							(
								SELECT EP.policyId, T.policyName, EP.policyType, T.policyType AS 'ConfigPolicytype'
								FROM APP_ConfigurationPolicy (READUNCOMMITTED) T
								JOIN APP_EmailConfigPolicies (READUNCOMMITTED) EP ON EP.modified = 0 AND EP.componentNameId = EA.assocId AND T.policyId = EP.policyId
								WHERE T.status = 0
							) CP
							FOR XML PATH ('emailPolicies'), TYPE
						)
						FOR XML PATH ('policies'), TYPE
					),
					(
						SELECT EA.autoDiscover '@enableAutoDiscovery'
						FOR XML PATH('additionalOptions'), TYPE
					),
					(
						SELECT EA.subClientId '@subClientId'
						FOR XML PATH('subclientEntity'), TYPE
					),
					(
SELECT  158 '@_type_', EA.planId '@planId', EA.planName '@planName'
						FOR XML PATH('plan'), TYPE
					),
					( SELECT EA.TotalRows as '@totalRecords' FOR XML PATH ('pagingInfo'), TYPE )
					FROM
					(
						SELECT *,
						ROW_NUMBER() OVER ( ORDER BY CASE WHEN @SortColumn = @O365Field_DISPLAYNAME AND @SortOrder = 1 THEN displayName END DESC,
							CASE WHEN @SortColumn = @O365Field_DISPLAYNAME AND @SortOrder = 0 THEN displayName END ,
							CASE WHEN @SortColumn = @O365Field_SMTP_ADDRESS AND @SortOrder = 1 THEN smtpAdrress END DESC,
							CASE WHEN @SortColumn = @O365Field_SMTP_ADDRESS AND @SortOrder = 0 THEN smtpAdrress END ,
							CASE WHEN @SortColumn = @O365Field_ALIAS AND @SortOrder = 1 THEN aliasName END DESC,
							CASE WHEN @SortColumn = @O365Field_ALIAS AND @SortOrder = 0  THEN aliasName END,
							CASE WHEN @SortColumn = @O365Field_DATABASE AND @SortOrder = 1 THEN databaseName END DESC,
							CASE WHEN @SortColumn = @O365Field_DATABASE AND @SortOrder = 0  THEN databaseName END,
							CASE WHEN @SortColumn = @O365Field_MAILBOX_TYPE AND @SortOrder = 1 THEN  msExchRecipientTypeDetails END DESC,
							CASE WHEN @SortColumn = @O365Field_MAILBOX_TYPE AND @SortOrder = 0 THEN  msExchRecipientTypeDetails END ,
							CASE WHEN @SortColumn = @O365Field_STATUS  AND @SortOrder = 1 THEN  status  END DESC,
							CASE WHEN @SortColumn = @O365Field_STATUS  AND @SortOrder = 0 THEN  status  END ,
							CASE WHEN @SortColumn = @O365Field_AUTO_DISCOVER AND @SortOrder = 1 THEN   isAutoDiscovered   END DESC,
							CASE WHEN @SortColumn = @O365Field_AUTO_DISCOVER AND @SortOrder = 0 THEN   isAutoDiscovered   END ,
							CASE WHEN @SortColumn = @O365Field_SIZE AND @SortOrder = 1  THEN   mailboxSize  END DESC,
							CASE WHEN @SortColumn = @O365Field_SIZE AND @SortOrder = 0  THEN   mailboxSize  END ,
							CASE WHEN @SortColumn = @O365Field_BACKUP_REF_TIME AND @SortOrder = 1 THEN backupReferenceTime END DESC,
							CASE WHEN @SortColumn = @O365Field_BACKUP_REF_TIME AND @SortOrder = 0 THEN  backupReferenceTime  END ,
							CASE WHEN @SortColumn = @O365Field_PLAN	AND @SortOrder = 1 THEN planName	 END DESC,
							CASE WHEN @SortColumn = @O365Field_PLAN	AND @SortOrder = 0 THEN planName END,
							CASE WHEN @SortColumn = @O365Field_EMAILS_BACKEDUP	AND @SortOrder = 1 THEN numberOfEmails END DESC,
							CASE WHEN @SortColumn = @O365Field_EMAILS_BACKEDUP	AND @SortOrder = 0 THEN numberOfEmails END	,
							CASE WHEN @SortColumn = @O365Field_DISCOVER_TYPE	AND @SortOrder = 1 THEN discoverByType END DESC,
							CASE WHEN @SortColumn = @O365Field_DISCOVER_TYPE	AND @SortOrder = 0 THEN discoverByType END  ) AS RowNum  FROM
						(
							SELECT R.*, N.name as planName, COUNT(assocId) OVER (PARTITION BY 1) as TotalRows  FROM
							(
								SELECT U.assocId, U.userGuid, U.displayName, U.aliasName, U.smtpAdrress, U.databaseName, U.exchangeServer, U.discoverByType, U.isAutoDiscovered, U.mailBoxType,
									U.subClientId, U.adGroupName, U.autoDiscover, U.planId, U.deleted, U.status,
									U.exchangeVersion, (CASE WHEN U.msExchRecipientTypeDetails IS NULL OR U.msExchRecipientTypeDetails = 0 THEN 1 ELSE U.msExchRecipientTypeDetails  END) as msExchRecipientTypeDetails, U.objectGuid, U.azureGuid, U.immutableId, U.backupReferenceTime, U.numberOfEmails, U.idxCollectionTime, U.mailboxSize, U.modified
								FROM APP_EmailConfigPolicyAssoc U (READUNCOMMITTED)
								JOIN #subClientTable S ON S.subclientId = U.subClientId
								WHERE
								@discoverByType NOT IN (8,11,12)
								AND discoverByType = @discoverByType
								AND U.modified = 0
								AND ((U.Status = @discoverByStatus) OR (U.status = @DO_NOT_ARCHIVE AND @isSelectDoNotArchiveMailboxes = 1))
								UNION ALL
								SELECT DISTINCT U.assocId, U.userGuid, U.displayName, U.aliasName, U.smtpAdrress, U.databaseName, U.exchangeServer, U.discoverByType, U.isAutoDiscovered, U.mailBoxType,
									U.subClientId, U.adGroupName, U.autoDiscover, ISNULL(U.planId, 0) as planId, U.deleted, U.status,
									U.exchangeVersion, (CASE WHEN U.msExchRecipientTypeDetails IS NULL OR U.msExchRecipientTypeDetails = 0 THEN 1 ELSE U.msExchRecipientTypeDetails  END) as msExchRecipientTypeDetails, U.objectGuid, U.azureGuid, U.immutableId, U.backupReferenceTime, U.numberOfEmails, U.idxCollectionTime, U.mailboxSize, U.modified
								FROM APP_EmailConfigPolicyAssoc U (READUNCOMMITTED)
								INNER JOIN #subClientTable S ON S.subclientId = U.subClientId
								INNER JOIN ( SELECT max(modified)as modified, min(modified)as activeAssociation, userGuid from APP_EmailConfigPolicyAssoc(READUNCOMMITTED) where subClientId=@subClientId and discoverByType=1 group by userGuid) as DS
									ON DS.activeAssociation != 0 AND DS.userGuid =  U.userGuid AND DS.Modified=U.modified
								WHERE
								U.modified!=0 AND ((@isSelectInactiveMailboxes = 1) OR (@DELETED = @discoverByStatus))
								UNION ALL
								SELECT A.assocId, A.userGuid, A.displayName, A.aliasName, A.smtpAdrress, A.databaseName, A.exchangeServer, A.discoverByType, A.isAutoDiscovered, A.mailBoxType,
									A.subClientId, A.adGroupName, A.autoDiscover, A.planId, A.deleted, A.status,
									A.exchangeVersion,(CASE WHEN A.msExchRecipientTypeDetails IS NULL OR A.msExchRecipientTypeDetails = 0 THEN 1 ELSE A.msExchRecipientTypeDetails  END) as msExchRecipientTypeDetails, A.objectGuid, A.azureGuid, A.immutableId, A.backupReferenceTime, A.numberOfEmails, A.idxCollectionTime, A.mailboxSize, A.modified
								FROM APP_EmailConfigPolicyAssoc A (READUNCOMMITTED)
								JOIN #subClientTable S ON S.subclientId = A.subClientId
								WHERE
								((@discoverByType = 4		--EmailDiscoverByType.AUTO_DISCOVERED
									AND discoverByType IN (2, 3, 6, 8, 11, 12))	 --	For EmailDiscoverByType.DATABASE, EmailDiscoverByType.ADGROUPS, EmailDiscoverByType.CONTENTSTORE, EmailDiscoverByType.ALL_USERS
									OR (@discoverByType = 8 AND @discoverByType = discoverByType)
									OR (@discoverByType = 11 AND @discoverByType = discoverByType)
									OR (@discoverByType = 12 AND @discoverByType = discoverByType))
								AND A.modified = 0
								UNION ALL
								SELECT A.assocId, A.userGuid, A.displayName, A.aliasName, A.smtpAdrress, A.databaseName, A.exchangeServer, A.discoverByType, A.isAutoDiscovered, A.mailBoxType,
									A.subClientId, A.adGroupName, A.autoDiscover, A.planId, A.deleted, A.status,
									A.exchangeVersion, (CASE WHEN A.msExchRecipientTypeDetails IS NULL OR A.msExchRecipientTypeDetails = 0 THEN 1 ELSE A.msExchRecipientTypeDetails  END) as msExchRecipientTypeDetails, A.objectGuid, A.azureGuid, A.immutableId, A.backupReferenceTime, A.numberOfEmails, A.idxCollectionTime, A.mailboxSize, A.modified
								FROM APP_EmailConfigPolicyAssoc A (READUNCOMMITTED)
								JOIN #subClientTable S ON S.subclientId = A.subClientId
								WHERE
								@discoverByType = 7	--EmailDiscoverByType.ALL_JOURNAL
								AND discoverByType IN (5)	--EmailDiscoverByType.MICROSOFT_JOURNAL
								AND A.modified = 0
							) R
							LEFT OUTER JOIN App_Plan (READUNCOMMITTED) N ON N.id = R.planId
							WHERE ((@search = 0) OR  (@search = 1 AND  (displayName LIKE @searchKey OR smtpAdrress LIKE @searchKey OR aliasName like @searchKey OR userGUID like @searchKey)   ))
							AND ( (@filterColumn = 0 )
							OR ((  displayName LIKE ISNULL((select fStr from @filterlisttbl WHERE fCOl = @O365Field_DISPLAYNAME),  '%%' ) OR  (@O365Field_DISPLAYNAME NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  smtpAdrress LIKE ISNULL((select fStr from @filterlisttbl WHERE fCOl = @O365Field_SMTP_ADDRESS) , '%%' ) OR  (@O365Field_SMTP_ADDRESS NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  aliasName LIKE ISNULL ((select fStr from @filterlisttbl WHERE fCOl = @O365Field_ALIAS), '%%' ) OR  (@O365Field_ALIAS NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  databaseName IN (select fStr from @filterlisttbl WHERE fCOl = @O365Field_DATABASE) OR  (@O365Field_DATABASE NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  (planId IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_PLAN)) OR (@O365Field_PLAN NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  ( (  @DELETED IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_STATUS) AND deleted = @DELETED) OR status IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_STATUS)) OR  (@O365Field_STATUS NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  (msExchRecipientTypeDetails IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_MAILBOX_TYPE )) OR  (@O365Field_MAILBOX_TYPE NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  (discoverByType IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_DISCOVER_TYPE )) OR  (@O365Field_DISCOVER_TYPE NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  (isAutoDiscovered IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_AUTO_DISCOVER )) OR  (@O365Field_AUTO_DISCOVER NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  ( EXISTS (SELECT 1 fCol from @filterlisttbl WHERE fCol = @O365Field_SIZE AND mailboxSize  >= rangeMin AND mailboxSize <= rangeMax)  ) OR   (@O365Field_SIZE NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  ( EXISTS (SELECT 1 fCol from @filterlisttbl WHERE fCol = @O365Field_EMAILS_BACKEDUP AND numberOfEmails  >= rangeMin AND numberOfEmails <= rangeMax)  ) OR   (@O365Field_EMAILS_BACKEDUP NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  ( EXISTS (SELECT 1 fCol from @filterlisttbl WHERE fCol = @O365Field_BACKUP_REF_TIME AND (((backupReferenceTime IS NULL) AND rangeMax = -1 AND rangeMin = -1) OR (backupReferenceTime  >= rangeMin AND backupReferenceTime <= rangeMax)))  ) OR   (@O365Field_BACKUP_REF_TIME NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							))
						) P
					)EA
					LEFT OUTER JOIN App_Plan (READUNCOMMITTED) P ON P.id = EA.planId
					LEFT JOIN #contentStore_mailbox_properties_real CMPR ON CMPR.componenetNameId = EA.assocId
					WHERE (@pageSz = 0) OR   ((RowNum > (@pageNo * @pageSz))  AND  (RowNum <= ((@pageNo * @pageSz)+ @pageSz)))
					ORDER BY RowNum
					FOR XML PATH('associations'), TYPE),
					(SELECT  @emailCount  '@emailCount',  @ciCount  '@ciCount', @appSize  '@appArchiveSize', @lastCiJobRanTime  '@lastCiJobRanTime',
					@lastBackupJobTime   '@lastArchiveJobRanTime', @idxCollectionTime  '@indexCreationTime' , @mailboxCount  '@numberOfItems', @modified '@modified'
					FOR XML PATH('stats'), TYPE)
					FOR XML PATH ('Ida_GetEmailAssociationsResp'), TYPE
				)
		IF object_id('tempdb.dbo.#contentStore_mailbox_properties_real') is not null	DROP TABLE #contentStore_mailbox_properties_real
	END
	ELSE IF @inPropertyLevel = 10 -- ListPropertyLevels_BasicProperties
	BEGIN
		INSERT INTO #emailConfiguration_DiscoveryTable
		(assocId, userGuid, displayName, aliasName, smtpAdrress, discoverByType, isAutoDiscovered, mailBoxType, emailStatus, deleted, autoDiscover, subclientId, planId, numberOfEmails, idxCollectionTime, backupReferenceTime, mailboxSize, modified )
		SELECT U.assocId, U.userGuid, U.displayName, U.aliasName, U.smtpAdrress, U.discoverByType, U.isAutoDiscovered, U.mailBoxType, U.status, U.deleted, U.autoDiscover, U.subClientId, U.planId, U.numberOfEmails, U.idxCollectionTime, U.backupReferenceTime, U.mailboxSize, U.modified
		FROM APP_EmailConfigPolicyAssoc U (READUNCOMMITTED)
		JOIN #subClientTable S ON S.subclientId = U.subClientId
		WHERE
		discoverByType = @discoverByType
		AND modified = 0
		UNION
		SELECT A.assocId, A.userGuid, A.displayName, A.aliasName, A.smtpAdrress, A.discoverByType, A.isAutoDiscovered, A.mailBoxType, A.status, A.deleted, A.autoDiscover, A.subClientId, A.planId, A.numberOfEmails, A.idxCollectionTime, A.backupReferenceTime, A.mailboxSize, A.modified
		FROM APP_EmailConfigPolicyAssoc A (READUNCOMMITTED)
		JOIN #subClientTable S ON S.subclientId = A.subClientId
		WHERE
		@discoverByType = 4	--EmailDiscoverByType.AUTO_DISCOVERED
		AND discoverByType IN (2, 3, 6) --EmailDiscoverByType.DATABASE, EmailDiscoverByType.ADGROUPS, EmailDiscoverByType.CONTENTSTORE
		AND modified = 0
		IF object_id('tempdb.dbo.#subClientTable') IS NOT NULL	DROP TABLE #subClientTable
		--REMOVE DUPLICATE ENTRIES BY DELETING THOSE MAILBOXES WHICH ARE ON OTHER SUBCLIENTS AND ON THIS SUBCLIENT
		DELETE #emailConfiguration_DiscoveryTable
		FROM #emailConfiguration_DiscoveryTable CT
		JOIN
		(
			SELECT COUNT(userGuid) nGuid, userGuid
			FROM #emailConfiguration_DiscoveryTable
			GROUP BY userGuid
		) DG ON DG.nGuid > 1
		AND DG.userGuid = CT.userGuid
		WHERE CT.subclientId <> @subClientId
	-- FORM THE XML RESPONSE
				DECLARE @tempExchxml TABLE (Exchangexml XML)
				INSERT INTO @tempExchxml
				SELECT (Select @environmentType '@environmentType',(
					SELECT discoverByType '@discoverType',
					(
						CASE WHEN @discoverByType = 6	--EmailDiscoverByType.CONTENTSTORE
							AND EA.mailBoxType = 3		--MailboxType.SMTP_MAILBOXES
							THEN
						(
							SELECT EA.displayName '@displayName', EA.smtpAdrress '@smtpAdrress', CASE WHEN EA.subClientId = @subClientId AND @discoverMailboxes = 1 THEN 1 END '@associated',
							EA.mailBoxType '@mailBoxType', EA.numberOfEmails '@numberOfEmails', EA.mailboxSize '@mailboxSize',
							(
								SELECT 13 '@_type_',  EA.userGuid '@userGUID'
								FOR XML PATH('user'), TYPE
							),
							(
								SELECT CAST(CP.stringVal AS XML) FROM APP_ComponentProp CP (READUNCOMMITTED)
WHERE CP.componentType = 12		--_CV_COMPONENT_TYPE_CONENTSTORE_PROP
AND CP.propertyTypeId = 1160	--_CV_COMPONENT_EXONEPASS_CONTENTSTORE_CLIENTS
								AND CP.componentId = assocId
								AND CP.modified = 0
							)
							, ( SELECT EA.backupReferenceTime as '@time'  FOR XML PATH ('lastArchiveJobRanTime'), TYPE )
							, ( SELECT EA.idxCollectionTime as '@time'  FOR XML PATH ('idxCollectionTime'), TYPE )
							, ( SELECT EA.modified as '@time'  FOR XML PATH ('modified'), TYPE )
							FOR XML PATH('contentStoreMailbox'), TYPE
						) END
					),
					( SELECT EA.TotalRows as '@totalRecords' FOR XML PATH ('pagingInfo'), TYPE )
					FROM
					(
						SELECT *, ROW_NUMBER() OVER ( ORDER BY CASE WHEN @SortColumn = @O365Field_DISPLAYNAME AND @SortOrder = 1 THEN displayName END  DESC ,
							CASE WHEN @SortColumn = @O365Field_DISPLAYNAME AND @SortOrder = 0 THEN displayName END ,
							CASE WHEN @SortColumn = @O365Field_SMTP_ADDRESS AND @SortOrder = 1 THEN smtpAdrress END DESC,
							CASE WHEN @SortColumn = @O365Field_SMTP_ADDRESS AND @SortOrder = 0 THEN smtpAdrress END ,
							CASE WHEN @SortColumn = @O365Field_ALIAS AND @SortOrder = 1 THEN aliasName END DESC,
							CASE WHEN @SortColumn = @O365Field_ALIAS AND @SortOrder = 0  THEN aliasName END,
							CASE WHEN @SortColumn = @O365Field_MAILBOX_TYPE AND @SortOrder = 1 THEN  mailBoxType END DESC,
							CASE WHEN @SortColumn = @O365Field_MAILBOX_TYPE AND @SortOrder = 0 THEN  mailBoxType END ,
							CASE WHEN @SortColumn = @O365Field_AUTO_DISCOVER AND @SortOrder = 1 THEN   isAutoDiscovered   END DESC,
							CASE WHEN @SortColumn = @O365Field_AUTO_DISCOVER AND @SortOrder = 0 THEN   isAutoDiscovered   END ,
							CASE WHEN @SortColumn = @O365Field_SIZE AND @SortOrder = 1  THEN   mailboxSize  END DESC,
							CASE WHEN @SortColumn = @O365Field_SIZE AND @SortOrder = 0  THEN   mailboxSize  END ,
							CASE WHEN @SortColumn = @O365Field_BACKUP_REF_TIME AND @SortOrder = 1 THEN backupReferenceTime END DESC,
							CASE WHEN @SortColumn = @O365Field_BACKUP_REF_TIME AND @SortOrder = 0 THEN  backupReferenceTime  END ,
							CASE WHEN @SortColumn = @O365Field_EMAILS_BACKEDUP	AND @SortOrder = 1 THEN numberOfEmails END DESC,
							CASE WHEN @SortColumn = @O365Field_EMAILS_BACKEDUP	AND @SortOrder = 0 THEN numberOfEmails END	,
							CASE WHEN @SortColumn = @O365Field_DISCOVER_TYPE	AND @SortOrder = 1 THEN discoverByType END DESC,
							CASE WHEN @SortColumn = @O365Field_DISCOVER_TYPE	AND @SortOrder = 0 THEN discoverByType END	,
							CASE WHEN @SortColumn = @O365Field_PLAN	AND @SortOrder = 1 THEN planName	END DESC ,
							CASE WHEN @SortColumn = @O365Field_PLAN	AND @SortOrder = 0 THEN planName END ) AS RowNum FROM
						(
							SELECT E.*,  N.name AS planName, COUNT(assocId) OVER (PARTITION BY 1) as TotalRows
							FROM #emailConfiguration_DiscoveryTable E
							LEFT OUTER JOIN App_Plan (READUNCOMMITTED) N ON N.id = E.planId
							WHERE ((@search = 0) OR  (@search = 1 AND  (displayName LIKE @searchKey OR smtpAdrress LIKE @searchKey OR aliasName like @searchKey OR userGUID like @searchKey)   ))
							AND ( (@filterColumn = 0 )
							OR ((  displayName LIKE ISNULL((select fStr from @filterlisttbl WHERE fCOl = @O365Field_DISPLAYNAME),  '%%' ) OR  (@O365Field_DISPLAYNAME NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  smtpAdrress LIKE ISNULL((select fStr from @filterlisttbl WHERE fCOl = @O365Field_SMTP_ADDRESS) , '%%' ) OR  (@O365Field_SMTP_ADDRESS NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  aliasName LIKE ISNULL ((select fStr from @filterlisttbl WHERE fCOl = @O365Field_ALIAS), '%%' ) OR  (@O365Field_ALIAS NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  (planId IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_PLAN)) OR (@O365Field_PLAN NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  (mailBoxType IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_MAILBOX_TYPE )) OR  (@O365Field_MAILBOX_TYPE NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  (discoverByType IN (select CONVERT(INTEGER, fStr) from @filterlisttbl WHERE fCOl = @O365Field_DISCOVER_TYPE )) OR  (@O365Field_DISCOVER_TYPE NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0) )  )
							AND	(  ( EXISTS (SELECT 1 fCol from @filterlisttbl WHERE fCol = @O365Field_SIZE AND mailboxSize  >= rangeMin AND mailboxSize <= rangeMax)  ) OR   (@O365Field_SIZE NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  ( EXISTS (SELECT 1 fCol from @filterlisttbl WHERE fCol = @O365Field_EMAILS_BACKEDUP AND numberOfEmails  >= rangeMin AND numberOfEmails <= rangeMax)  ) OR   (@O365Field_EMAILS_BACKEDUP NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							AND	(  ( EXISTS (SELECT 1 fCol from @filterlisttbl WHERE fCol = @O365Field_BACKUP_REF_TIME AND (((backupReferenceTime IS NULL) AND rangeMax = -1 AND rangeMin = -1) OR (backupReferenceTime  >= rangeMin AND backupReferenceTime <= rangeMax)) )  ) OR   (@O365Field_BACKUP_REF_TIME NOT IN (select fCol from @filterlisttbl) AND (@filterColumn > 0)))
							))
						) P
					)EA
					WHERE (@pageSz = 0) OR   ((RowNum > (@pageNo * @pageSz))  AND  (RowNum <= ((@pageNo * @pageSz)+ @pageSz)))
					ORDER BY RowNum
					FOR XML PATH('associations'), TYPE),
					(SELECT  @emailCount  '@emailCount',  @ciCount  '@ciCount', @appSize  '@appArchiveSize', @lastCiJobRantime  '@lastCiJobRanTime',
					@lastBackupJobTime   '@lastArchiveJobRanTime', @idxCollectionTime  '@indexCreationTime' , @mailboxCount  '@numberOfItems', @modified '@modified'
					FOR XML PATH('stats'), TYPE)
					FOR XML PATH ('Ida_GetEmailAssociationsResp'), TYPE ) as 'Exchangexml'
					OPTION(MAXDOP 1)
				SET  @o_xmlString = (SELECT Exchangexml	FROM @tempExchxml)
	END
	END
ERROR:
	IF @o_xmlString IS null
	BEGIN
	SET @o_xmlString =
		(
			SELECT
				(
					SELECT @errorCode '@errorCode', @errorString '@errorString'
					FOR XML PATH('resp'), Type
				)
				FOR XML PATH('Ida_GetEmailAssociationsResp')
		)
	END
	SELECT @o_xmlString
	IF object_id('tempdb.dbo.#subClientTable') IS NOT NULL
		DROP TABLE #subClientTable
	IF object_id('tempdb.dbo.#emailConfiguration_DiscoveryTable') is not null
		DROP TABLE #emailConfiguration_DiscoveryTable
	IF object_id('tempdb.dbo.#contentStore_mailbox_properties') is not null
		DROP TABLE #contentStore_mailbox_properties
	IF object_id('tempdb.dbo.#contentStore_mailbox_properties_real') is not null
		DROP TABLE #contentStore_mailbox_properties_real
END
SET NOCOUNT OFF
GO

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

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

insert into GXDBVersions values(2, 'AppEmailConfigPolicyAssocGetInfo',  'v1.1.4.56.14.4', 'AppEmailConfigPolicyAssocGetInfo', 'v1.1.4.56.14.4')
GO

