

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/LicGetMailboxDetailsV3.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.
--
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/LicGetMailboxDetailsV3.sp,v $ $Id: LicGetMailboxDetailsV3.sp,v 1.1.2.9.36.1 2021/06/29 00:33:24 leiwang Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='LicGetMailboxDetailsV3')
	delete from GXDBVersions where aliasname = 'LicGetMailboxDetailsV3'
GO
print '... Creating Procedure: LicGetMailboxDetailsV3'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure LicGetMailboxDetailsV3
  @nUserId integer = 0
AS
--Calculate 3 licenses, including Archived MailBox, Search MailBox,MSim
SET ANSI_PADDING ON
SET NOCOUNT ON
DECLARE @currentTime_Unix  INT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @currentTime DATETIME = dbo.getdatetime(@currentTime_Unix)
DECLARE @monthStart DATETIME = DATEADD(MM, DATEDIFF(MM, 0, GETUTCDATE()), 0)
DECLARE @monthStart_Uix int  = dbo.GetUnixTime(@monthStart)
DECLARE @CCLPeakType INT = 0
DECLARE @SCLPeakType INT = 1
DECLARE @attrName varchar (256) ='LicGetMailboxDetailsV3Status'
DECLARE @attrType int = 1
DECLARE @attrVal varchar(1) = '0'    --0 successful  --1 failed
DECLARE @ArchivedMailBoxType int = 11
DECLARE @ArchivedMailBoxLicType int = 100013
DECLARE @SearchMailBoxType int = 12
DECLARE @SearchMailBoxLicType int = 200009
DECLARE @MSimUsersType int = 18
DECLARE @MSimLicType int = 100028
DECLARE @ExChangeMBLicType int = 200010
DECLARE @ExChangeONEPASSLicType int = 200011
DECLARE @Office365SPLicType int = 200012
DECLARE @GmailLicType int = 200013
DECLARE @GdriveLicType int = 200014
DECLARE @OneDriveLicType int = 200015
DECLARE @SalesForceLicType int = 200018
DECLARE @DominoLicType int = 200019
DECLARE @current_ArchivedMailBox       int = 0
DECLARE @current_SearchMailBox         int = 0
DECLARE @current_MSimUsers             int = 0
DECLARE @last_ArchivedMailBox       int = 0
DECLARE @last_SearchMailBox         int = 0
DECLARE @last_MSimUsers             int = 0
DECLARE @currentupdatelevel         int = 0
DECLARE @MailboxUpdateLevel nvarchar(64)  = 'LicGetMailboxDetailsUpdateLevel'
IF @nUserId > 1 --SECURITY
BEGIN
	IF object_id('tempdb.dbo.#LicCalUserObj') IS NOT NULL DROP TABLE #LicCalUserObj
create table  #LicCalUserObj ( [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  )
	EXEC LicGetSecurityObjV3 @nUserId OUTPUT,0
END
IF NOT EXISTS (SELECT 1 FROM GXGlobalParam WITH (NOLOCK) WHERE name = @MailboxUpdateLevel)
BEGIN
    INSERT INTO GXGlobalParam(name, value, created, modified)
    VALUES (@MailboxUpdateLevel, 0, dbo.GetUnixTime(getdate()), 0)
END
ELSE
BEGIN
	SELECT @currentupdatelevel = CONVERT(INT, value) FROM GXGlobalParam WITH (NOLOCK) WHERE name = @MailboxUpdateLevel
END
IF object_id('tempdb.dbo.#tempAppTypes') IS NOT null DROP TABLE #tempAppTypes
CREATE TABLE #tempAppTypes (t_apptype INT, t_name varchar(1024), fname varchar(50))
----ProtectedMailbox
INSERT INTO #tempAppTypes
SELECT type, name, (CASE WHEN name like '%Archiv%' THEN 'ArchivedMailbox' ELSE 'ProtectedMailbox' END)
FROM   APP_iDAType WITH (NOLOCK)  where type < 1000
AND (name like '%Compliance Archiver'  or name like '%mail%box%'  or name LIKE '%Notes%')
AND name not like '%Database' AND name NOT LIKE 'SRM%'
--If mSIM is present remove counting of apptype 54,56,67,137 from archive mailbox
DECLARE @o_isValidApptype INT
DECLARE @o_appTypeList NVARCHAR(1024) = ''
EXEC LicGetMSimAppType -1,0,@o_isValidApptype OUTPUT, @o_appTypeList OUTPUT
IF @o_appTypeList <> ''
   DELETE #tempAppTypes  WHERE t_apptype in (select _ID from dbo.SplitIDs(@o_appTypeList))
-- Backup activity disabled Client.
IF object_id('tempdb.dbo.#tblClientIDA') IS NOT null DROP TABLE #tblClientIDA
CREATE TABLE  #tblClientIDA (clientId INT, appTypeId INT, disabled INT)
INSERT INTO #tblClientIDA
SELECT clientId, ida.appTypeId , 0 FROM  APP_IDAName IDA WITH (NOLOCK) WHERE (IDA.status & 6) =0
IF @nUserId > 1 --SECURITY
   delete #tblClientIDA where ClientId not in (select clientId from #LicCalUserObj where clientId > 0)
DELETE  #tblClientIDA
FROM  #tblClientIDA T, JMJobAction A WITH (NOLOCK)
WHERE A.clientId = T.clientId AND (A.appType = 0 OR A.appType = T.appTypeId)
AND A.opType = 4 AND A.action = 1
-- Backup activity disabled Subclient client.
IF object_id('tempdb.dbo.#ArchiveSubclients') IS NOT null DROP TABLE #ArchiveSubclients
CREATE TABLE  #ArchiveSubclients (SubclientId INT,clientId INT, subclientName VARCHAR(1024), appTypeId INT, bIsArchive INT)
INSERT INTO #ArchiveSubclients
SELECT ID,clientId,subclientName,appTypeId, (case fname WHEN 'ProtectedMailbox' THEN 0 ELSE 1 END)
FROM APP_Application App WITH (NOLOCK), (select distinct t_apptype, fname from #tempAppTypes where fname IN ('ProtectedMailbox', 'ArchivedMailbox')) T
WHERE appTypeId = t_apptype
AND (dataArchGrpID > 1 OR logArchGrpID > 1) AND (subclientStatus & 65538) = 0
AND clientId in (Select distinct clientId from #tblClientIDA )
AND App.id not in (SELECT appId FROM  JMJobAction WITH (NOLOCK) WHERE appId > 1 AND action = 1)
DELETE #ArchiveSubclients
WHERE bIsArchive = 0 AND NOT EXISTS (select 1 from APP_SubclientProp a WITH (NOLOCK) where attrName = 'Is Turbo Subclient' and a.componentNameId=SubclientId and attrval='1' and a.modified = 0)
IF object_id('tempdb.dbo.#tblCI') IS NOT null DROP TABLE #tblCI
CREATE TABLE #tblCI (clientId INT, appTypeId INT, appId INT)
INSERT INTO #tblCI
SELECT DISTINCT APP.clientId, APP.appTypeId, APP.id
FROM APP_Application APP WITH (NOLOCK), APP_ClientProp CP WITH (NOLOCK)
WHERE APP.dataArchGrpID IN (SELECT archGroupId FROM archContentIndexingRules WITH (NOLOCK) WHERE enabled = 1)
      AND CP.componentNameId = APP.clientId AND CP.attrName = 'Content Indexing Enabled' AND CP.attrVal = '1' AND CP.modified = 0
--SearchMailBox
IF @nUserId > 1 --SECURITY
BEGIN
DELETE #tblCI
WHERE ClientId not in (SELECT clientId FROM #LicCalUserObj WHERE clientId > 0)
END
--Msim
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#msimCountTable'))
DROP TABLE #msimCountTable
CREATE TABLE #msimCountTable (clientId INT, appTypeId INT, mailbox NVARCHAR(448), mailboxGUID VARCHAR(512), apptypename NVARCHAR(1024), subclientname NVARCHAR(1024), clientname NVARCHAR(1024), subClientId int, instance int,isCIEnabled int)
EXEC LicGetMSimUserCountV2 1,0
IF object_id('tempdb.dbo.#tempUsage') IS NOT null DROP TABLE #tempUsage
CREATE TABLE #tempUsage (t_usageType INT,t_currentUsage INT, t_lastUsage INT, t_updateStatus INT DEFAULT 0)
DECLARE @clientList table (clientid INT, type VARCHAR(32))
--	Case Manager check
INSERT INTO @clientList
SELECT DISTINCT clientid, 'Case Manager'
FROM CMDefinition WITH (NOLOCK)
UNION
SELECT DISTINCT refcopyclientid, 'Case Manager'
FROM DM2CaseInfo WITH (NOLOCK)
WHERE status = 1
-- Compliance Search check
INSERT INTO @clientList
SELECT DISTINCT clientid, 'Compliance Search'
FROM simInstalledPackages WITH (NOLOCK) WHERE simPackageID = 255
--Check Whether MSim License is enforced
DECLARE @isMSimEnforced INT = -2
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 = -3
IF NOT EXISTS(SELECT 1 FROM GxGlobalParam WITH(NOLOCK) WHERE name = 'LicisMSimEnforced')
INSERT INTO GxGlobalParam (name, value, created, modified)
VALUES ('LicisMSimEnforced', -@isMSimEnforced-2, dbo.GetUnixTime(GetUTCDate()),0)
BEGIN TRY
BEGIN TRAN
IF EXISTS (SELECT 1 FROM Lic_CurrentUsage WITH (NOLOCK) WHERE UsageType in (0, @ArchivedMailBoxType,@SearchMailBoxType,@MSimUsersType))
DELETE FROM Lic_CurrentUsage WHERE UsageType in (0, @ArchivedMailBoxType,@SearchMailBoxType,@MSimUsersType)
INSERT INTO Lic_CurrentUsage (UsageType,ClientId,SubclientId,AppTypeId,LicType,CapacityFlag,VMFlag, ObjectName)
SELECT  DISTINCT @ArchivedMailBoxType,T.clientId, SCP.componentNameId,T.appTypeId, @ArchivedMailBoxLicType,-1,0,CP.stringVal
FROM  #tblClientIDA T, #ArchiveSubclients APP, APP_SubClientProp SCP WITH (NOLOCK), APP_ComponentProp CP WITH (NOLOCK), APP_iDAType IDT WITH (NOLOCK)
WHERE APP.clientId = T.clientId AND APP.appTypeId = T.appTypeId
AND SCP.componentNameId = APP.SubclientId AND SCP.modified = 0 AND SCP.attrType > 100
AND IDT.type =T.appTypeId
AND CP.componentId = SCP.id AND CP.modified = 0
AND CP.propertyTypeId IN (1100, 1400)
UNION ALL
SELECT  DISTINCT @SearchMailBoxType,CI.clientId,CI.appId,CI.appTypeId,@SearchMailBoxLicType,-1,0,CP.stringVal
FROM #tblCI CI, APP_SubClientProp SCP WITH (NOLOCK), APP_ComponentProp CP WITH (NOLOCK)
WHERE SCP.componentNameId = CI.appId AND SCP.modified = 0
AND CP.componentId = SCP.id AND CP.modified = 0
AND CP.propertyTypeId IN (1100, 1400)
UNION ALL
SELECT DISTINCT @MSimUsersType,clientId,SubClientId,appTypeId,
CASE WHEN appTypeId = 54 THEN @ExChangeMBLicType
	 WHEN appTypeId = 90 THEN @DominoLicType
	 WHEN appTypeId = 78 THEN @Office365SPLicType
	 WHEN appTypeId = 137 THEN @ExChangeONEPASSLicType
	 WHEN instance = 1 THEN @GmailLicType
	 WHEN instance = 2 THEN @GdriveLicType
	 WHEN instance = 3 THEN @SalesForceLicType
	 WHEN instance = 7 THEN @OneDriveLicType
	 ELSE @MSimLicType
END,
CASE WHEN appTypeId in (54,78,137) OR instance = 7 THEN @isMSimEnforced
	 ELSE -2
END,
isCIEnabled,mailbox
FROM  #msimCountTable
INSERT INTO Lic_CurrentUsage (UsageType, ClientId, ClientName)
SELECT 0, clientid, type
FROM @clientList
IF @currentupdatelevel = 0
BEGIN
	DELETE P
	FROM Lic_PeakUsage P
	INNER JOIN APP_EmailConfigPolicyAssoc WITH (NOLOCK) ON P.ObjectName = smtpAdrress AND mailBoxType <> 1 AND LEN(userGuid) > 0 AND modified = 0 AND deleted = 0
	WHERE MonthStart = @monthStart AND UsageType = @MSimUsersType AND AppTypeId = 137
	UPDATE GXGlobalParam SET value = @currentupdatelevel + 1 WHERE name = @MailboxUpdateLevel
	SET @currentupdatelevel = @currentupdatelevel + 1
END
IF @currentupdatelevel = 1
BEGIN
	DELETE P
	FROM Lic_PeakUsage P
	INNER JOIN APP_Application APP WITH (NOLOCK) ON APP.id = P.SubclientId AND APP.appTypeId = P.appTypeId
	INNER JOIN App_InstanceProp Ins WITH (NOLOCK) ON APP.instance = Ins.componentNameId AND Ins.attrName = 'Exchange Online Enabled' AND Ins.attrVal = '1' AND Ins.modified = 0
	WHERE P.MonthStart = @monthStart AND P.UsageType = @MSimUsersType AND P.appTypeId = 54
	UPDATE GXGlobalParam SET value = @currentupdatelevel + 1 WHERE name = @MailboxUpdateLevel
	SET @currentupdatelevel = @currentupdatelevel + 1
END
SELECT @current_ArchivedMailBox = [11],@current_SearchMailBox = [12],@current_MSimUsers = [18]
FROM (SELECT distinct ObjectName, UsageType FROM Lic_CurrentUsage WITH (NOLOCK) ) AS S
PIVOT
(COUNT(ObjectName) FOR UsageType in ([11],[12],[18])) AS P
UPDATE T
SET T.ObjectName = SP.newattrName, T.CapacityFlag = -2
FROM Lic_PeakUsage T
INNER JOIN (SELECT componentNameId,
		 CASE 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 newattrName,
		 SUBSTRING(attrName,1,CHARINDEX(';',attrName)-1) AS oldattrName
		 FROM APP_SubClientProp (READUNCOMMITTED)
		 WHERE attrType = 134 AND modified = 0 AND LEN(attrName) > 0 AND attrName like '%;%') SP ON SP.componentNameId = T.SubClientId AND T.ObjectName = SP.oldattrName
WHERE T.MonthStart = @monthStart AND T.UsageType = @MSimUsersType AND T.AppTypeId = 134 AND T.CapacityFlag = -1
UPDATE T
SET T.ObjectName = P.newattrName, T.CapacityFlag = -2
FROM Lic_PeakUsage T
INNER JOIN APP_Application APP WITH (NOLOCK) ON APP.id = T.SubClientId
INNER JOIN (SELECT componentNameId,
		 CASE 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 newattrName,
		 SUBSTRING(attrName,1,CHARINDEX(';',attrName)-1) AS oldattrName
		 FROM APP_BackupSetProp WITH (NOLOCK)
		 WHERE attrType = 137 AND LEN(attrName) > 0 AND attrName like '%;%') P ON APP.backupSet = P.componentNameId AND T.ObjectName = P.oldattrName
WHERE T.MonthStart = @monthStart AND T.UsageType = @MSimUsersType AND T.AppTypeId = 78 AND T.CapacityFlag = -1
SELECT @last_ArchivedMailBox = [11],@last_SearchMailBox = [12],@last_MSimUsers = [18]
FROM (SELECT distinct ObjectName, UsageType FROM Lic_PeakUsage WITH (NOLOCK) WHERE MonthStart = @monthStart AND PeakType = @CCLPeakType) AS S
PIVOT
(COUNT(ObjectName) FOR UsageType in ([11],[12],[18])) AS P
INSERT INTO #tempUsage (t_usageType,t_currentUsage,t_lastUsage)
VALUES (@ArchivedMailBoxType,@current_ArchivedMailBox,@last_ArchivedMailBox),
(@SearchMailBoxType,@current_SearchMailBox,@last_SearchMailBox),
(@MSimUsersType,@current_MSimUsers,@last_MSimUsers)
UPDATE #tempUsage
SET t_updateStatus = 1
WHERE t_currentUsage >= t_lastUsage AND t_currentUsage > 0
DELETE T
FROM Lic_PeakUsage T
INNER JOIN #tempUsage ON T.UsageType = t_usageType AND t_updateStatus = 1
WHERE T.MonthStart = @monthStart AND T.PeakType = @CCLPeakType
INSERT INTO Lic_PeakUsage (MonthStart,CreationDate,UsageType,PeakType,ClientId,SubclientId,AppTypeId,LicType,CapacityFlag,VMFlag,ObjectName)
SELECT @monthStart,@currentTime,UsageType,@CCLPeakType,ClientId,SubclientId,AppTypeId,LicType,CapacityFlag,VMFlag, ObjectName
FROM Lic_CurrentUsage WITH (NOLOCK)
INNER JOIN #tempUsage ON UsageType = t_usageType AND t_updateStatus = 1
MERGE Lic_PeakUsage AS T
USING (SELECT UsageType,ClientId,SubclientId,AppTypeId,LicType,CapacityFlag,VMFlag,ObjectName FROM Lic_CurrentUsage WITH (NOLOCK) WHERE UsageType in (@ArchivedMailBoxType,@SearchMailBoxType,@MSimUsersType)) AS S
ON T.MonthStart = @monthStart AND T.UsageType = S.UsageType AND T.PeakType = @SCLPeakType AND T.ObjectName = S.ObjectName AND T.ClientId = S.ClientId AND T.SubclientId = S.SubclientId AND T.LicType = S.LicType
WHEN MATCHED AND T.CapacityFlag <> S.CapacityFlag
	THEN Update
		SET  T.CapacityFlag = S.CapacityFlag
WHEN NOT MATCHED BY TARGET
	THEN INSERT (MonthStart, CreationDate, UsageType, PeakType,ClientId,SubclientId,AppTypeId,LicType,CapacityFlag,VMFlag,ObjectName)
		VALUES(@monthStart, @currentTime, S.UsageType,@SCLPeakType,S.ClientId,S.SubclientId,S.AppTypeId,S.LicType,S.CapacityFlag,S.VMFlag,S.ObjectName)
;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @attrVal = '1'
GOTO  CAL_ERROR
END CATCH
CAL_ERROR:
IF EXISTS(select 1 from LicUsageBasedConfig WITH (nolock) where attrname = @attrname)
	Update LicUsageBasedConfig
	SET attrVal = @attrVal
	WHERE attrname = @attrname AND attrVal <> @attrVal
ELSE
	INSERT INTO LicUsageBasedConfig (attrname,attrType,attrVal)
	VALUES (@attrname, @attrType, @attrVal)
IF @nUserId > 0 --SECURITY
DROP TABLE #LicCalUserObj
DROP TABLE #tempAppTypes
DROP TABLE #tblClientIDA
DROP TABLE #ArchiveSubclients
DROP TABLE #tblCI
DROP TABLE #msimCountTable
DROP TABLE #tempUsage
SET NOCOUNT OFF
GO
-- Tell the AWK processor that there are no more input lines to scan

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

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

insert into GXDBVersions values(2, 'LicGetMailboxDetailsV3',  'v1.1.2.9.36.1', 'LicGetMailboxDetailsV3', 'v1.1.2.9.36.1')
GO

