

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/LicUpdateLicenseDetailsV3.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/LicUpdateLicenseDetailsV3.sp,v $ $Id: LicUpdateLicenseDetailsV3.sp,v 1.1.2.12.8.2 2021/06/10 02:39:06 leiwang Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='LicUpdateLicenseDetailsV3')
	delete from GXDBVersions where aliasname = 'LicUpdateLicenseDetailsV3'
GO
print '... Creating Procedure: LicUpdateLicenseDetailsV3'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure LicUpdateLicenseDetailsV3
AS
--Update ClientName, SubclientName, Organization, OrgGUID AND Plan
SET ANSI_PADDING ON
SET NOCOUNT ON
DECLARE @isMSP							int = 0
DECLARE @isCasemanaerEnabled			int = 0
DECLARE @isComplianceSearchEnabled		int = 0
DECLARE @monthStart DATETIME = DATEADD(MM, DATEDIFF(MM, 0, GETUTCDATE()), 0)
DECLARE @sql							NVARCHAR (1000)
SELECT TOP 1 @isMSP = CAST(value AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'IsMSPCommcell' AND modified = 0
IF object_id('tempdb.dbo.#tempAssocIdMap') IS NOT null DROP TABLE #tempAssocIdMap
CREATE TABLE #tempAssocIdMap (t_cid INT, t_appid INT, t_VMClientid INT, t_objectid INT, t_org NVARCHAR(1024), t_orgGUID uniqueidentifier, t_planid INT, t_baseplainid INT, t_ownerid INT,t_ownerGUID uniqueidentifier)
CREATE CLUSTERED INDEX tempAssocIdMap_idx ON #tempAssocIdMap (t_cid, t_appid, t_VMClientid, t_objectid)
INSERT INTO #tempAssocIdMap
SELECT DISTINCT clientId, subclientid, CASE WHEN VMFlag = 2 AND UsageType NOT IN (1,2,3,4,18) THEN objectId ELSE 0 END, CASE WHEN UsageType IN (22, 23, 24) THEN objectId ELSE 0 END, NULL,NULL,0,0,0,NULL
FROM Lic_CurrentUsage WITH (NOLOCK)
IF object_id('tempdb.dbo.#tempCompanyMap') IS NOT null DROP TABLE #tempCompanyMap
CREATE TABLE #tempCompanyMap (t_did INT, t_cid INT)
IF object_id('tempdb.dbo.#tempOwnerMap') IS NOT null DROP TABLE #tempOwnerMap
CREATE TABLE #tempOwnerMap (t_cid INT, t_VMClientid INT, t_did INT, t_oid INT)
IF @isMSP = 1
BEGIN
	INSERT INTO #tempCompanyMap
	SELECT DISTINCT id,id FROM UMDSProviders WITH (NOLOCK) WHERE serviceType = 5
	UNION
	SELECT DISTINCT id,ownerCompany FROM UMDSProviders WITH (NOLOCK) WHERE serviceType <> 5
	DELETE T
	FROM #tempCompanyMap T
	INNER JOIN UMDSProviders D WITH (NOLOCK) ON T.t_cid = D.id
	WHERE D.serviceType <> 5
	INSERT INTO #tempOwnerMap
	SELECT DISTINCT Map.t_cid,Map.t_VMClientid,D.id,D.ownerCompany
	FROM #tempAssocIdMap Map
	JOIN APP_ClientGroupAssoc A WITH(NOLOCK) ON  A.ClientId = CASE t_VMClientid WHEN 0 THEN Map.t_cid ELSE Map.t_VMClientid END
	JOIN APP_ClientGroup B WITH (NOLOCK) ON A.clientGroupId = B.id
JOIN App_CompanyProp C WITH (NOLOCK) ON C.attrName = 'Associated Smart Client Group' AND A.clientGroupId=C.attrValInt AND C.modIFied = 0 AND C.cs_attrName = checksum('Associated Smart Client Group')
	JOIN #tempCompanyMap M ON C.componentNameId = M.t_did
	JOIN UMDSProviders D WITH (NOLOCK) ON M.t_cid = D.id
WHERE B.flag&0x1000 > 0
	DELETE  O
	FROM  #tempOwnerMap O
	INNER JOIN  #tempOwnerMap O1 ON O.t_cid = O1.t_cid AND O.t_VMClientid = O1.t_VMClientid AND O.t_did = O1.t_oid
	UPDATE Map
	SET t_org = domainName, t_orgGUID = D.GUID, t_ownerid = D.ownerCompany
	FROM #tempAssocIdMap Map
	JOIN #tempOwnerMap O ON O.t_cid = Map.t_cid AND O.t_VMClientid = Map.t_VMClientid
	JOIN UMDSProviders D WITH (NOLOCK) ON O.t_did = D.id
	UPDATE Map
	SET t_org = domainName, t_orgGUID = D.GUID, t_ownerid = D.ownerCompany
	FROM #tempAssocIdMap Map
	INNER JOIN UMUsers U  WITH(NOLOCK) ON U.id = Map.t_objectid
	INNER JOIN #tempCompanyMap M ON U.umDSproviderId = M.t_did
	INNER JOIN UMDSProviders D WITH(NOLOCK) ON M.t_cid = D.id
	WHERE Map.t_objectid <> 0
	UPDATE Map
	SET t_ownerGUID = D.GUID
	FROM #tempAssocIdMap Map
	JOIN UMDSProviders D WITH (NOLOCK) ON Map.t_ownerid = D.id
	WHERE Map.t_ownerid <> 0
END
ELSE
BEGIN
	UPDATE Map
	SET t_org = B.name, t_orgGUID = B.GUID
	FROM #tempAssocIdMap Map
	INNER JOIN APP_ClientGroupAssoc A WITH(NOLOCK) ON  A.ClientId = CASE t_VMClientid WHEN 0 THEN Map.t_cid ELSE Map.t_VMClientid END
	INNER JOIN APP_ClientGroup B WITH (NOLOCK) ON A.clientGroupId = B.id
WHERE B.flag&1073741824 > 0
	UPDATE Map
	SET t_org = B.name, t_orgGUID = B.GUID
	FROM #tempAssocIdMap Map
	INNER JOIN UMUsers U  WITH(NOLOCK) ON U.id = Map.t_objectid
	INNER JOIN APP_ClientGroup B WITH (NOLOCK) ON U.id = B.userid
WHERE B.flag&1073741824 > 0 AND Map.t_objectid <> 0
END
UPDATE Map
SET t_planid = CASE  WHEN AC.status & 4096 = 0 THEN CAST(ISNULL(ASCP.attrVal, 0) AS INT)
                     ELSE CAST(ISNULL(ACP.attrVal, 0) AS INT)
               END
FROM #tempAssocIdMap Map
INNER JOIN APP_Application AAP WITH(NOLOCK) ON Map.t_appid = AAP.id AND Map.t_cid = AAP.clientId
INNER JOIN APP_Client AC WITH(NOLOCK) ON AC.id = AAP.clientId
LEFT JOIN APP_SubClientProp ASCP WITH(NOLOCK) ON ASCP.componentNameId = AAP.id AND ASCP.attrName='Associated Plan' AND ASCP.modified = 0
LEFT JOIN APP_ClientProp ACP WITH(NOLOCK) ON ACP.componentNameId = AC.id AND ACP.attrName = 'Associated Plan' AND ACP.modified = 0
IF object_id('tempdb.dbo.#PlanMap') IS NOT null DROP TABLE #PlanMap
CREATE TABLE #PlanMap (planid int, baseplanid int)
INSERT INTO #PlanMap
SELECT DISTINCT componentNameId, Case When attrVal LIKE '%[^0-9]%' THEN -1 ELSE Convert(INT, attrVal) END
FROM App_PlanProp WITH(NOLOCK)
WHERE attrname = 'Base plan' and modified = 0 AND attrVal IS NOT NULL
DECLARE @planCount INT = (SELECT COUNT(*) FROM #PlanMap)
DECLARE GetBasePlan CURSOR FOR SELECT DISTINCT t_planid FROM #tempAssocIdMap
DECLARE @planid INT
OPEN GetBasePlan
FETCH NEXT FROM GetBasePlan INTO @planid
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @baseplainid INT = @planid
	DECLARE @nextplainid INT = 0
	DECLARE @iterations INT = 0
	WHILE EXISTS (SELECT 1 FROM #PlanMap WHERE planid = @baseplainid)
	BEGIN
		SELECT @nextplainid = baseplanid FROM #PlanMap WHERE planid = @baseplainid
		IF @nextplainid = @baseplainid OR @nextplainid = -1 OR @iterations > @planCount
		BEGIN
			SET @baseplainid = @nextplainid
			BREAK
		END
		ELSE
			SET @baseplainid = @nextplainid
		SET @iterations += 1
	END
	IF @baseplainid <> -1
	BEGIN
		UPDATE #tempAssocIdMap
		SET t_baseplainid = @baseplainid
		WHERE t_planid = @planid
	END
FETCH NEXT FROM GetBasePlan INTO @planid
END
CLOSE GetBasePlan
DEALLOCATE GetBasePlan
DROP TABLE #PlanMap
--Case Manager
IF EXISTS (SELECT TOP 1 1 FROM Lic_CurrentUsage WITH (NOLOCK) WHERE UsageType = 0 AND ClientName = 'Case Manager')
BEGIN
	IF @isMSP = 0
		SET @isCasemanaerEnabled = 1
	ELSE
		SET @isCasemanaerEnabled = 2
END
-- Compliance Search
IF object_id('tempdb.dbo.#tmpCompanyList') IS NOT null DROP TABLE #tmpCompanyList
CREATE TABLE #tmpCompanyList (companyGUID uniqueidentifier)
EXEC LicGetCplSearchPerms '#tmpCompanyList'
IF EXISTS (SELECT TOP 1 1 FROM Lic_CurrentUsage WITH(NOLOCK) WHERE UsageType = 0 AND ClientName = 'Compliance Search') AND EXISTS (SELECT TOP 1 1 from #tmpCompanyList)
BEGIN
	IF @isMSP = 0
		SET @isComplianceSearchEnabled = 1
	ELSE
		SET @isComplianceSearchEnabled = 2
END
BEGIN TRY
Update T
Set T.ClientName = C.name
From Lic_CurrentUsage T
INNER JOIN APP_Client C WITH (NOLOCK) on T.clientid = C.id
WHERE T.clientid > 0 and T.ClientName is null
Update T
Set	T.SubclientName = A.subclientName
From Lic_CurrentUsage T
INNER JOIN APP_Application A WITH (NOLOCK) on T.SubclientId = A.id and T.ClientId = A.clientId
WHERE T.clientid > 0 and T.subclientid > 0 AND T.SubclientName is null
Update T
Set T.ClientName = C.name
From Lic_PeakUsage T
INNER JOIN APP_Client C WITH (NOLOCK) on T.clientid = C.id
WHERE T.clientid > 0 and T.ClientName is null
Update T
Set	T.SubclientName = A.subclientName
From Lic_PeakUsage T
INNER JOIN APP_Application A WITH (NOLOCK) on T.SubclientId = A.id and T.ClientId = A.clientId
WHERE T.clientid > 0 and T.subclientid > 0 AND T.SubclientName is null
UPDATE Lic_CurrentUsage
SET Organization = t_org, OrgGUID = t_orgGUID, PlanName = P.Name, BasePlanName = B.Name, OrgOwnerGUID = t_ownerGUID
FROM #tempAssocIdMap
LEFT JOIN App_Plan P WITH(NOLOCK) ON t_planid > 0 AND t_planid = P.id AND P.modified = 0
LEFT JOIN App_Plan B WITH(NOLOCK) ON t_baseplainid > 0 AND t_baseplainid = B.id AND B.modified = 0
WHERE ClientId = t_cid AND SubclientId = t_appid AND t_VMClientid = CASE WHEN VMFlag = 2 AND UsageType <> 18 THEN ObjectId ELSE 0 END AND t_objectid = CASE WHEN UsageType IN (22, 23, 24) THEN ObjectId ELSE 0 END AND UsageType NOT IN (35,37)
UPDATE Lic_PeakUsage
SET Organization = t_org, OrgGUID = t_orgGUID, PlanName = P.Name, BasePlanName = B.Name, OrgOwnerGUID = t_ownerGUID
FROM #tempAssocIdMap
LEFT JOIN App_Plan P WITH(NOLOCK) ON t_planid > 0 AND t_planid = P.id AND P.modified = 0
LEFT JOIN App_Plan B WITH(NOLOCK) ON t_baseplainid > 0 AND t_baseplainid = B.id AND B.modified = 0
WHERE MonthStart = @monthStart AND ClientId = t_cid AND SubclientId = t_appid AND t_VMClientid = CASE WHEN VMFlag = 2 AND UsageType <> 18 THEN ObjectId ELSE 0 END AND t_objectid = CASE WHEN UsageType IN (22, 23, 24) THEN ObjectId ELSE 0 END AND UsageType NOT IN (35,37)
IF @isCasemanaerEnabled > 0
BEGIN
SET @sql = N'UPDATE Usage SET VMFlag = VMFlag|2 FROM Lic_CurrentUsage Usage '
IF @isCasemanaerEnabled = 1
	SELECT	@sql = @sql + N'WHERE Usage.UsageType = 18'
ELSE IF @isCasemanaerEnabled = 2
	SELECT	@sql = @sql + N'INNER JOIN (SELECT DISTINCT OrgGUID FROM Lic_CurrentUsage WITH(NOLOCK) WHERE UsageType = 0 AND ClientName = ''Case Manager'') T
ON T.OrgGUID = Usage.OrgGUID WHERE Usage.UsageType = 18'
EXEC (@sql)
END
IF @isComplianceSearchEnabled > 0
BEGIN
SET @sql = N'UPDATE Usage SET VMFlag = VMFlag|4 FROM Lic_CurrentUsage Usage '
IF @isComplianceSearchEnabled = 1
	SELECT	@sql = @sql + N'WHERE Usage.UsageType = 18'
ELSE IF @isComplianceSearchEnabled = 2
	SELECT	@sql = @sql + N'INNER JOIN (SELECT DISTINCT OrgGUID FROM Lic_CurrentUsage WITH(NOLOCK) WHERE UsageType = 0 AND ClientName = ''Compliance Search'') T
ON T.OrgGUID = Usage.OrgGUID INNER JOIN #tmpCompanyList List ON List.companyGUID = T.OrgGUID WHERE Usage.UsageType = 18'
EXEC (@sql)
END
UPDATE T
SET T.VMFlag = T.VMFlag|S.VMFlag
FROM Lic_PeakUsage T
INNER JOIN Lic_CurrentUsage S WITH(NOLOCK) ON T.UsageType = S.UsageType AND T.ObjectName = S.ObjectName AND T.ClientId = S.ClientId AND T.SubclientId = S.SubclientId AND T.LicType = S.LicType
WHERE T.MonthStart = @monthStart AND T.UsageType = 18
DELETE FROM Lic_CurrentUsage WHERE UsageType = 0
END TRY
BEGIN CATCH
GOTO  CAL_ERROR
END CATCH
CAL_ERROR:
DROP TABLE #tmpCompanyList
DROP TABLE #tempAssocIdMap
DROP TABLE #tempCompanyMap
DROP TABLE #tempOwnerMap
IF EXISTS (SELECT 1 FROM APP_AdvanceSettings WITH (NOLOCK) WHERE keyName = 'nRptCalcUsageFreqHrs' AND entityId = 2 AND entityType = 3 AND enabled = 1)
EXEC RptSaveStorageUsage 0
SET NOCOUNT OFF
GO
-- Tell the AWK processor that there are no more input lines to scan

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

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

insert into GXDBVersions values(2, 'LicUpdateLicenseDetailsV3',  'v1.1.2.12.8.2', 'LicUpdateLicenseDetailsV3', 'v1.1.2.12.8.2')
GO

