

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/LicGetMSimUserCountV2.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.
--	Author: Satya Prakash Bhukar
--	Date:   1/26/2016
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/LicGetMSimUserCountV2.sp,v $ $Id: LicGetMSimUserCountV2.sp,v 1.1.4.20.20.2 2021/06/29 00:33:26 leiwang Exp $";
-- Following Line Indicates new Class.  It should be identical to filename+revision!
-- @i_clientId can be client ID or Library ID
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='LicGetMSimUserCountV2')
	delete from GXDBVersions where aliasname = 'LicGetMSimUserCountV2'
GO
print '... Creating Procedure: LicGetMSimUserCountV2'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure LicGetMSimUserCountV2
  @bDetails INTEGER = 0,
  @nUserId INTEGER = 0,
  @nUserCount INTEGER = 0 OUTPUT,
  @mSIMXML NVARCHAR(MAX) = '' OUTPUT
AS
SET ANSI_PADDING ON
SET NOCOUNT ON
DECLARE @o_isValidApptype INT
DECLARE @o_appTypeList NVARCHAR(1024) = ''
EXEC LicGetMSimAppType -2,0,@o_isValidApptype OUTPUT, @o_appTypeList OUTPUT
DECLARE @isMSimEnforced INT = 0
DECLARE @earliestSP INT = 0
SELECT TOP 1 @earliestSP = HighestSP
FROM (SELECT DISTINCT InstallHistory.C.value('@sp', 'int') AS HighestSP
	  FROM ( SELECT CAST(stringVal AS XML) AS historyXml
		     FROM APP_ComponentProp WITH(NOLOCK)
             WHERE propertyTypeId = 3624 AND componentId = 2
           ) AS results
      CROSS APPLY results.historyXml.nodes('/PatchInstallHistory/SPVer') AS InstallHistory(C)
      WHERE InstallHistory.C.value('../@Release', 'int') = 11
      UNION
      SELECT DISTINCT HighestSP
      FROM PatchInstallHistory WITH(NOLOCK)
      WHERE ClientId = 2
     ) L
WHERE L.HighestSP > 0
ORDER BY HighestSP
IF @earliestSP >= 19
SET @isMSimEnforced = 1
IF NOT EXISTS(SELECT 1 FROM GxGlobalParam WITH(NOLOCK) WHERE name = 'LicisMSimEnforced')
INSERT INTO GxGlobalParam (name, value, created, modified)
VALUES ('LicisMSimEnforced', @isMSimEnforced, dbo.GetUnixTime(GetUTCDate()),0)
DECLARE @filterSubclientStatus INT = (CAST(0x00002 AS INT)+CAST(0x00004 AS INT)+CAST(0x00020 AS INT))
IF object_id('tempdb.dbo.#tmpO365SharePointClients') IS NOT null DROP TABLE #tmpO365SharePointClients
CREATE TABLE #tmpO365SharePointClients (clientid INT)
DECLARE @MailBoxXML XML
IF object_id('tempdb.dbo.#mailBoxTable') is not null DROP TABLE #mailBoxTable
CREATE TABLE #mailBoxTable (clientId INT, appTypeId INT, mailbox NVARCHAR(1024), mailboxGUID VARCHAR(512), apptypename NVARCHAR(1024), subclientname NVARCHAR(1024), clientname NVARCHAR(1024), SubClientId  int, instance int, isCIEnabled int)
CREATE INDEX #mailBoxTable_SubClientId_mailbox_Index1 ON #mailBoxTable (SubClientId) include (mailbox)
INSERT INTO	#mailBoxTable
SELECT DISTINCT C.id, APP.appTypeId, MB.stringVal, GD.stringVal, IDT.name, APP.SubClientName, C.name ClientName, APP.id, 0,0
FROM APP_Client C (READUNCOMMITTED)
JOIN APP_Application APP (READUNCOMMITTED) ON APP.clientId = C.id
JOIN APP_iDAType IDT (READUNCOMMITTED) ON IDT.type = APP.appTypeId
JOIN APP_SubClientProp SP (READUNCOMMITTED) ON APP.subclientStatus&@filterSubclientStatus = 0
		AND SP.componentNameId = APP.id AND SP.modified=0 AND SP.attrType in (101,111) --PROPERTY_LNOTES_DOC, PROPERTY_EXCHANGE_MBOX
JOIN APP_ComponentProp GD (READUNCOMMITTED) ON GD.componentId = SP.id AND GD.modified = 0 AND GD.propertyTypeId in (1103,1400) --_CV_COMPONENT_EXMB_GUID
JOIN APP_ComponentProp MB (READUNCOMMITTED) ON MB.componentId = SP.id AND MB.modified = 0 AND MB.propertyTypeId in (1100,1400) --_CV_COMPONENT_EXMB_MAILBOX
WHERE LEN(GD.stringVal) > 0 AND APP.appTypeId IN (SELECT _ID FROM dbo.SplitIDs(@o_appTypeList))
UNION
SELECT T.clientId, T.appTypeId, rc.value('@smtpAdrress', 'NVARCHAR(1024)') mailbox, um.value('@userGUID', 'VARCHAR(512)') mailboxGUID, T.name, T.subclientName, T.ClientName, T.subclientId,0,0
FROM
(
	SELECT C.id clientId, APP.appTypeId, CAST(MB.stringVal AS XML) userEmails, IDT.name, APP.SubClientName, C.name ClientName, APP.id subclientId--COUNT(DISTINCT CP.stringVal)
	FROM APP_Client C (READUNCOMMITTED)
	JOIN APP_Application APP (READUNCOMMITTED) ON APP.clientId = C.id AND APP.subclientStatus&@filterSubclientStatus = 0
	JOIN APP_iDAType IDT (READUNCOMMITTED) ON IDT.type = APP.appTypeId
JOIN APP_ComponentProp JM (READUNCOMMITTED) ON JM.componentId = APP.id AND JM.modified = 0 AND JM.propertyTypeId = 1150 --_CV_COMPONENT_EXONEPASS_JOURNAL_MAILBOX
JOIN APP_ComponentProp MB (READUNCOMMITTED) ON MB.componentId = JM.id AND MB.modified = 0 AND MB.propertyTypeId = 1151 --_CV_COMPONENT_EXONEPASS_USER_MAILBOX
) T
CROSS APPLY userEmails.nodes('./recipients') R(rc)
CROSS APPLY rc.nodes('./user') M(um)
WHERE LEN(um.value('@userGUID', 'VARCHAR(512)')) > 0
UNION
SELECT CP.clientId, APP.appTypeId, CP.smtpAdrress, CASE WHEN CP.mailBoxType = 1 THEN '{'+CP.userGuid+'}' ELSE CP.smtpAdrress END, IDT.name, APP.subclientName, C.name, APP.id, 0,0
FROM APP_EmailConfigPolicyAssoc CP (READUNCOMMITTED)
JOIN APP_Application APP (READUNCOMMITTED) ON APP.id = CP.subClientId AND APP.subclientStatus&@filterSubclientStatus = 0
AND CP.modified = 0 AND CP.deleted = 0
JOIN APP_Client C (READUNCOMMITTED) ON C.id = APP.clientId
JOIN APP_iDAType IDT (READUNCOMMITTED) ON IDT.type = APP.appTypeId
WHERE discoverByType = 13 OR (CP.mailBoxType = 1 AND CP.msExchRecipientTypeDetails = 1 AND LEN(CP.userGuid) > 0 AND CP.status = 0 /*Don't count disabled mailboxes*/)
DELETE M
FROM #mailBoxTable M
INNER JOIN APP_Application APP (READUNCOMMITTED) ON APP.id = M.SubClientId AND APP.appTypeId = M.appTypeId
INNER JOIN App_InstanceProp Ins (READUNCOMMITTED) ON APP.instance = Ins.componentNameId AND Ins.attrName = 'Exchange Online Enabled' AND Ins.attrVal = '1' AND Ins.modified = 0
WHERE M.appTypeId = 54
IF @bDetails > 0
BEGIN
	UPDATE M
	SET isCIEnabled = 1
	FROM #mailBoxTable M
	JOIN APP_EmailConfigPolicyAssoc E (READUNCOMMITTED) ON M.mailbox = E.smtpAdrress
	JOIN APP_EmailConfigPolicies EP (READUNCOMMITTED) ON Ep.componentNameId = E.assocId
	JOIN APP_ConfigurationPolicyDetails CD (READUNCOMMITTED) on CD.componentNameId = EP.policyId
	WHERE E.mailBoxType = 1 AND E.modified = 0 AND EP.policyType = 1 /* Ida::EmailPolicyType_ARCHIVE=1 */ AND EP.modified = 0
	AND CD.policyDetails.value('(/emailPolicy/archivePolicy/contentIndexProps/@enableContentIndex)[1]','INT') = 1
	AND E.status = 0 AND E.deleted = 0 AND E.modified = 0 AND CD.modified = 0
	UPDATE M
	SET isCIEnabled = 1
	FROM #mailBoxTable M
	JOIN APP_EmailConfigPolicyAssoc E (READUNCOMMITTED) ON M.clientId = E.clientId AND M.SubClientId = E.subClientId
	JOIN APP_EmailConfigPolicies EP ON Ep.componentNameId = E.assocId
	JOIN APP_ConfigurationPolicyDetails CD on CD.componentNameId = EP.policyId
	WHERE E.mailBoxType in (2, 3) AND E.modified = 0 AND EP.policyType = 4 /* Ida::EmailPolicyType_JOURNAL=4 */ AND EP.modified = 0
	AND CD.policyDetails.value('(/emailPolicy/journalPolicy/contentIndexProps/@enableContentIndex)[1]','INT') = 1
	AND E.status = 0 AND E.deleted = 0 AND E.modified = 0 AND CD.modified = 0
END
-- Count Gmail, Gdrive, OneDrive and Salesforce
;WITH Gmail_CTE (clientId,appTypeId,apptypename,subclientname,clientname,subClientId,instance,backupSet,instanceid)
AS
(
	SELECT C.id, APP.appTypeId, IDT.name, APP.SubClientName, C.name, APP.id, Prop.attrVal, APP.backupSet, APP.instance
	FROM APP_Client C (READUNCOMMITTED)
	JOIN APP_Application APP (READUNCOMMITTED) ON APP.clientId = C.id
	JOIN APP_iDAType IDT (READUNCOMMITTED) ON IDT.type = APP.appTypeId
	JOIN APP_InstanceProp Prop (READUNCOMMITTED) ON APP.instance = Prop.componentNameId AND APP.subclientStatus&@filterSubclientStatus = 0
		AND attrName = 'Cloud Apps Instance Type' AND LEN(attrVal) > 0 AND attrVal in ('1','2','3','7')
)
INSERT INTO #mailBoxTable
SELECT DISTINCT clientId,appTypeId,SP.attrName,SP.attrName,apptypename,subclientname,clientname,subClientId, case when isnumeric(instance) = 1 then cast(instance AS int) else 0 end,0
FROM Gmail_CTE
JOIN (SELECT componentNameId, attrVal,
	 CASE WHEN CHARINDEX(';',attrName) = 0 THEN attrName
		  WHEN CHARINDEX(';',attrName) = len(attrName) THEN SUBSTRING(attrName,1,CHARINDEX(';',attrName)-1)
		  WHEN CHARINDEX(';',attrName) < len(attrName) THEN SUBSTRING(attrName,CHARINDEX(';',attrName)+1,len(attrName))
	 END AS attrName
	 FROM APP_SubClientProp (READUNCOMMITTED) WHERE attrType = 134 AND modified=0 AND LEN(attrName) > 0) SP ON SP.componentNameId = subClientId --PROPERTY_CLOUD_APPS
	AND (CASE WHEN instance = '7' and SP.attrVal like '%::oq' THEN 1
			  WHEN instance <> '7' THEN 1 ELSE 0 END) = 1
WHERE instance <> '3'
UNION
SELECT DISTINCT clientId,appTypeId,INFO.userName,INFO.userName,apptypename,subclientname,clientname,CTE.subClientId,3,0
FROM Gmail_CTE CTE
JOIN CloudAppsLicensingInfo INFO (READUNCOMMITTED) ON CTE.instanceid = INFO.instanceid AND CTE.backupSet = INFO.backupsetId and CTE.subClientId = INFO.subClientId AND INFO.isActive = 1
JOIN APP_InstanceProp P WITH(NOLOCK) ON P.componentNameId = INFO.instanceID AND P.attrName ='Salesforce Organization Type' AND P.attrVal = 0 AND P.modified = 0
WHERE CTE.instance = '3'
UNION
SELECT DISTINCT CTE.clientId,CTE.appTypeId,CA.smtpAddress,CA.smtpAddress,CTE.apptypename,CTE.subclientname,CTE.clientname,CTE.subClientId,7,0  --OneDrive V2 clients
FROM Gmail_CTE CTE
INNER JOIN APP_CloudAppUserDetails CA (READUNCOMMITTED) ON CTE.subClientId = CA.subClientId AND CA.discoverByType = 1 and CA.flags = 1 and CA.modified = 0
INNER JOIN APP_ClientProp P (READUNCOMMITTED) ON P.componentNameId = CTE.clientId AND P.attrName = 'IndexingV2_OneDrive' and P.attrVal = '1' and P.modified = 0
-- Count Office 365 SharePoint
EXEC LicGetO365Sharepoint '#tmpO365SharePointClients'
INSERT INTO #mailBoxTable
SELECT DISTINCT C.id, APP.appTypeId, P.attrName, P.attrName, IDT.name, APP.SubClientName, C.name, APP.id, 0,0
FROM (SELECT componentNameId,
		  CASE WHEN CHARINDEX(';',attrName) = 0 THEN attrName
			   WHEN CHARINDEX(';',attrName) = len(attrName) THEN SUBSTRING(attrName,1,CHARINDEX(';',attrName)-1)
			   WHEN CHARINDEX(';',attrName) < len(attrName) THEN SUBSTRING(attrName,CHARINDEX(';',attrName)+1,len(attrName))
			   END AS attrName
		 FROM APP_BackupSetProp (READUNCOMMITTED) WHERE attrType = 137) P
INNER JOIN APP_Application APP (READUNCOMMITTED) ON APP.backupSet = P.componentNameId AND APP.subclientStatus&@filterSubclientStatus = 0
INNER JOIN #tmpO365SharePointClients T ON T.clientid =  APP.clientId
INNER JOIN APP_Client C (READUNCOMMITTED) ON C.id = APP.clientId
INNER JOIN APP_iDAType IDT (READUNCOMMITTED) ON IDT.type = APP.appTypeId
IF @bDetails > 0
BEGIN
	UPDATE M
	SET isCIEnabled = 1
	FROM #mailBoxTable M
	INNER JOIN APP_ClientProp P (READUNCOMMITTED) ON P.componentNameId = M.clientId
	WHERE M.appTypeId in (78, 90, 134) AND P.attrName = 'Content Indexing Enabled' and P.attrVal = 1 and P.modified = 0
END
-- Remove the mailbox not belonging to user
IF @nUserId > 1 AND object_id('tempdb.dbo.#LicCalUserObj') IS NOT NULL
DELETE #mailBoxTable
WHERE clientId not in (select clientId from #LicCalUserObj where clientId > 0)
--REMOVE DISABLED ACTIVITY AT SUBCLIENT LEVEL
DELETE #mailBoxTable
FROM #mailBoxTable MC
JOIN JMJobAction (READUNCOMMITTED) JA ON JA.opType = 4 AND JA.action = 1 AND JA.appId = MC.SubClientId
--REMOVE DISABLED ACTIVITY AT CLIENT LEVEL
DELETE #mailBoxTable
FROM #mailBoxTable MC
JOIN JMJobAction (READUNCOMMITTED) JA ON JA.opType = 4 AND JA.action = 1 AND JA.clientId = MC.clientId AND JA.appType = 0
--REMOVE DISABLED ACTIVITY AT APPTYPE LEVEL
DELETE #mailBoxTable
FROM #mailBoxTable MC
JOIN JMJobAction (READUNCOMMITTED) JA ON JA.opType = 4 AND JA.action = 1 AND JA.appType = MC.appTypeId AND JA.clientId = MC.clientId
--REMOVE DISABLED ACTIVITY AT CLIENT GROUP LEVEL
DELETE #mailBoxTable
FROM #mailBoxTable MC
JOIN JMJobAction (READUNCOMMITTED) JA ON JA.opType = 4 AND JA.action = 1 AND JA.clientGroupId > 0
JOIN APP_ClientGroupAssoc (READUNCOMMITTED) GA ON GA.clientGroupId = JA.clientGroupId AND GA.clientId = MC.clientId
IF @isMSimEnforced = 1
	SELECT @nUserCount = COUNT(DISTINCT mailbox) FROM #mailBoxTable WHERE appTypeId in (54,78,137) OR (appTypeId = 134 AND instance = 7)
ELSE
	SELECT @nUserCount = COUNT(DISTINCT mailbox) FROM #mailBoxTable
SELECT @mSIMXML = ''
IF @bDetails > 0
BEGIN
	SELECT @MailBoxXML =
	(
		SELECT distinct
		dbo.NormalizeForXML(mailbox) as '@SMTP', apptypename as '@Apptype', subclientname  as '@SubClientName',
		clientname  as '@ClientName'
		FROM
		(
			SELECT * FROM #mailBoxTable WHERE 1 = CASE WHEN @isMSimEnforced = 1 AND (appTypeId in (54,78,137) OR (appTypeId = 134 AND instance = 7)) THEN 1
													   WHEN @isMSimEnforced = 0 THEN 1
													   ELSE 0 END
		) T
		FOR XML PATH('mailBoxList'),
		ROOT('MailBoxes')
	)
	IF object_id('tempdb.dbo.#msimCountTable') IS NOT NULL
	BEGIN
		INSERT INTO #msimCountTable (clientId, appTypeId, mailbox, mailboxGUID, apptypename, subclientname, clientname, subClientId, instance, isCIEnabled)
		SELECT * FROM #mailBoxTable
	END
	SET @mSIMXML = CAST(@MailBoxXML AS NVARCHAR(MAX))
END
IF object_id('tempdb.dbo.#mailBoxTable') is not null DROP TABLE #mailBoxTable
IF object_id('tempdb.dbo.#tmpO365SharePointClients') is not null DROP TABLE #tmpO365SharePointClients
SET NOCOUNT OFF
GO
-- Tell the AWK processor that there are no more input lines to scan

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

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

insert into GXDBVersions values(2, 'LicGetMSimUserCountV2',  'v1.1.4.20.20.2', 'LicGetMSimUserCountV2', 'v1.1.4.20.20.2')
GO

