SET NOCOUNT ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

DECLARE @ErrorTag	NVARCHAR(20) = '###ERROR###'
DECLARE @WarningTag	NVARCHAR(20) = '###WARNING###'
DECLARE @AdviceTag	NVARCHAR(20) = '###ADVICE###'
DECLARE @HeaderTag	NVARCHAR(20) = '###HEADER###'
DECLARE @DataTag	NVARCHAR(20) = '###DATA###'
DECLARE @DescTag 	NVARCHAR(20) = '###DESCRIPTION###'
DECLARE @errorCode	INT = 0
DECLARE @LicName 	NVARCHAR(256) = 'Mailbox archive solution'
DECLARE @evalFld    varchar(1024) = ''
DECLARE @permFld    varchar(1024) = ''
DECLARE @evalTotal  integer = 0
DECLARE @evalUsed   integer = 0
DECLARE @bEval      integer = 0
DECLARE @evalExpiry integer = 0
DECLARE @permTotal  integer = 0
DECLARE @permUsed   integer = 0
DECLARE @bPerm      integer = 0
DECLARE @permExpiry integer = 0
DECLARE @currentTime int = dbo.GetUNIXTime(GETUTCDATE())
DECLARE @TotalLicensesIssued INT = 0
DECLARE @TotalLicensesUsed INT = 0
DECLARE @ErrorMsg 		NVARCHAR(256) = ' The CommServe does not have sufficient '
DECLARE @IssuedCountMsg NVARCHAR(256) = 'Number of licenses for '
DECLARE @WarningMsg		NVARCHAR(256) = ''
DECLARE @licTypeId INT = 100028
DECLARE @HighestSP INT
DECLARE @prevReleaseID INT = 0
DECLARE @isMSimPresent INT = 0
DECLARE @filterSubclientStatus INT = (CAST(0x00002 AS INT)+CAST(0x00004 AS INT)+CAST(0x00020 AS INT))
DECLARE @sql nvarchar(max) = ''
PRINT @DescTag + ' Check for Mailbox archive solution License'

SELECT TOP 1 @prevReleaseID = id FROM simAllGalaxyRel WITH (NOLOCK) ORDER BY id DESC

SELECT TOP 1 @HighestSP = HighestSP FROM simInstalledPackages WITH (NOLOCK) WHERE ClientId = 2 AND simPackageID = 801

SELECT @isMSimPresent = 1 FROM licAAL WITH (NOLOCK) WHERE simLicAppTypeId = @licTypeId

IF @prevReleaseID <> 16 OR @HighestSP < 3 OR @HighestSP >= 17  
BEGIN
	SET @errorCode = 0
	GOTO CX_EXIT
END
ELSE 
BEGIN
	IF object_id('tempdb.dbo.#tmpO365SharePointClients') IS NOT null DROP TABLE #tmpO365SharePointClients
	CREATE TABLE #tmpO365SharePointClients (clientid INT)			
	
	IF object_id('tempdb.dbo.#mailBoxCountTable') is not null 
        DROP TABLE #mailBoxCountTable
    CREATE TABLE #mailBoxCountTable (clientId INT, appTypeId INT, instanceId INT, SubClientId INT, mailbox NVARCHAR(1024), mailboxGUID VARCHAR(512))

	CREATE CLUSTERED INDEX #mailBoxTable_clientId_appTypeId_Index ON #mailBoxCountTable (clientId, appTypeId)
	CREATE INDEX #mailBoxTable_SubClientId_Index ON #mailBoxCountTable (SubClientId)
	CREATE INDEX #mailBoxTable_appTypeId_instance_mailbox_Index ON #mailBoxCountTable (appTypeId) include (mailbox)

	INSERT INTO #mailBoxCountTable 
    (clientId, appTypeId, instanceId, SubClientId, mailbox, mailboxGUID)
    SELECT DISTINCT APP.clientId, APP.appTypeId, APP.instance, APP.id, MB.stringVal, GD.stringVal AS mailboxGUID
    FROM APP_Application APP (READUNCOMMITTED)
    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_EXCHANGE_MBOX , PROPERTY_LNOTES_DOC 
    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 (54,137)
    UNION
    SELECT clientId, appTypeId, instance, id, rc.value('@smtpAdrress', 'NVARCHAR(1024)') mailbox, um.value('@userGUID', 'VARCHAR(512)') mailboxGUID
    FROM
    (
        SELECT APP.clientId, APP.appTypeId, APP.instance, APP.id, CAST(MB.stringVal AS XML) userEmails
        FROM APP_Application APP (READUNCOMMITTED)
        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
        WHERE APP.subclientStatus&@filterSubclientStatus = 0 AND APP.appTypeId IN (54,137)
    ) T
    CROSS APPLY userEmails.nodes('./recipients') R(rc)
    CROSS APPLY rc.nodes('./user') M(um)
    WHERE LEN(um.value('@userGUID', 'VARCHAR(512)')) > 0
    
	IF @HighestSP >= 13
	BEGIN
		SET @sql = 'INSERT INTO #mailBoxCountTable 
		(clientId, appTypeId, instanceId, SubClientId, mailbox, mailboxGUID)
		SELECT CP.clientId, APP.appTypeId, APP.instance, APP.id, CP.smtpAdrress, ''{''+userGuid+''}''
		FROM APP_EmailConfigPolicyAssoc CP (READUNCOMMITTED)
		JOIN APP_Application APP (READUNCOMMITTED) ON APP.id = CP.subClientId AND CP.modified = 0 AND CP.deleted = 0
		WHERE LEN(CP.userGuid) > 0 AND (CP.mailBoxType <> 1 OR CP.mailBoxType = 1 AND CP.msExchRecipientTypeDetails = 1) AND APP.appTypeId IN (54,137)'
		execute(@sql)
	END
	ELSE
	BEGIN
		INSERT INTO #mailBoxCountTable 
		(clientId, appTypeId, instanceId, SubClientId, mailbox, mailboxGUID)
		SELECT CP.clientId, APP.appTypeId, APP.instance, APP.id, CP.smtpAdrress, '{'+userGuid+'}'
		FROM APP_EmailConfigPolicyAssoc CP (READUNCOMMITTED)
		JOIN APP_Application APP (READUNCOMMITTED) ON APP.id = CP.subClientId AND CP.modified = 0 AND CP.deleted = 0
		WHERE LEN(CP.userGuid) > 0 AND APP.appTypeId IN (54,137)
	END

	;WITH Gmail_CTE (clientId,appTypeId,instance,subClientId,instanceVal,backupSet)
	AS
	(
		SELECT APP.clientId, APP.appTypeId, APP.instance, APP.id, Prop.attrVal, APP.backupSet
		FROM APP_Application APP (READUNCOMMITTED)
		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 = '7'
	)
	INSERT INTO #mailBoxCountTable
	SELECT DISTINCT clientId,appTypeId,instance,subClientId,SP.attrName, SP.attrName
	FROM Gmail_CTE
	JOIN APP_SubClientProp SP (READUNCOMMITTED) ON SP.componentNameId = subClientId AND SP.modified=0 AND SP.attrType = 134 --PROPERTY_CLOUD_APPS
		AND LEN(SP.attrName) > 0
		AND (CASE WHEN @HighestSP >= 14 and SP.attrVal like '%::oq' THEN 1
				  WHEN @HighestSP < 14 THEN 1
				  ELSE 0 END) = 1
	
	IF @HighestSP >= 14
	BEGIN
		EXEC LicGetO365Sharepoint '#tmpO365SharePointClients'    
		INSERT INTO #mailBoxCountTable
		SELECT DISTINCT C.clientid, APP.appTypeId, APP.instance, APP.id, P.attrName, P.attrName
		FROM APP_BackupSetProp P (READUNCOMMITTED)
		INNER JOIN APP_Application APP (READUNCOMMITTED) ON APP.backupSet = P.componentNameId
		INNER JOIN #tmpO365SharePointClients C ON C.clientid = APP.clientId
		WHERE P.attrType = 137
	END	
	
	--REMOVE DISABLED ACTIVITY AT SUBCLIENT LEVEL
    DELETE #mailBoxCountTable
    FROM #mailBoxCountTable 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 #mailBoxCountTable
    FROM #mailBoxCountTable 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 #mailBoxCountTable
    FROM #mailBoxCountTable 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 #mailBoxCountTable
    FROM #mailBoxCountTable 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 @isMSimPresent = 1
	SELECT @TotalLicensesUsed = COUNT(DISTINCT mailbox) FROM #mailBoxCountTable
	ELSE 
	SELECT @TotalLicensesUsed = COUNT(DISTINCT mailbox) FROM #mailBoxCountTable WHERE appTypeId <> 54
	PRINT @DescTag + ' Used: ' + cast(@TotalLicensesUsed  as varchar(10))
	IF @TotalLicensesUsed = 0
	BEGIN
		SET @errorCode = 0
		GOTO CX_EXIT
	END

	BEGIN TRY
		SELECT @evalFld = RTRIM(eval_fld1), @permFld = RTRIM(perm_fld1) FROM licAAL WITH (NOLOCK) WHERE simLicAppTypeId = @licTypeId
		IF len(@evalFld) > 0
			EXEC MASTER..xp_getAALInfo2 @evalFld, @evalTotal OUTPUT, @evalUsed OUTPUT, @bEval OUTPUT, @evalExpiry OUTPUT
		IF len(@permFld) > 0
			EXEC MASTER..xp_getAALInfo2 @permFld, @permTotal OUTPUT, @permUsed OUTPUT, @bPerm OUTPUT, @permExpiry OUTPUT
		IF @evalExpiry = 0 OR @evalExpiry > @currentTime
			SET @TotalLicensesIssued = @evalTotal
		IF @permExpiry = 0 OR @permExpiry > @currentTime
		BEGIN
			IF @TotalLicensesIssued < 0 OR @permTotal < 0 
				SET @TotalLicensesIssued = -1
			ELSE
				SET @TotalLicensesIssued = @TotalLicensesIssued + @permTotal
		END
		PRINT @DescTag + ' Purchased: ' + cast(@TotalLicensesIssued as varchar(10)) 
	END TRY
	BEGIN CATCH
		SET @TotalLicensesIssued = 0
		SET @errorCode = 2
		GOTO CX_EXIT
	END CATCH
	
	IF (@permTotal < 0) OR (@permTotal*1.1 > @TotalLicensesUsed)
	BEGIN
		SET @errorCode = 0
		GOTO CX_EXIT
	END

	IF (@TotalLicensesIssued*1.1 > @TotalLicensesUsed)  OR (@evalTotal < 0)
	BEGIN 
		SET @errorCode = 1
	END
	ELSE
	BEGIN
		SET @ErrorMsg = ' The CommServe does not have sufficient '+@LicName+' licenses.'+CHAR(13)
		SET @IssuedCountMsg += @LicName + ' Purchased: ' + cast(@TotalLicensesIssued as varchar(10)) + ' Used: ' + cast(@TotalLicensesUsed as varchar(10))
		SET @errorCode = 2
	END

	DROP TABLE #tmpO365SharePointClients
	DROP TABLE #mailBoxCountTable

END			
CX_EXIT:
	IF @errorCode = 2
	BEGIN
		PRINT  @ErrorTag + @ErrorMsg + @IssuedCountMsg 
		PRINT  @AdviceTag + ' Contact your Software Provider to obtain the necessary licenses'
	END
	IF @errorCode = 1
	BEGIN
		PRINT  @WarningTag + @LicName + ' Evaluation Licenses will expire soon after upgrade'
		PRINT  @AdviceTag + ' Make sure that the CommServe has the necessary permanent licenses'
	END
	PRINT @DescTag + ' errorCode = ' + cast(@errorCode as varchar(10)) 
	IF @errorCode = 2
		SELECT 2
	ELSE IF @errorCode = 1
		SELECT 1
	ELSE
		SELECT 0
RETURN