

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/NTD_DataVerificationFailureDetectionHTMLContent.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.
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='NTD_DataVerificationFailureDetectionHTMLContent')
	delete from GXDBVersions where aliasname = 'NTD_DataVerificationFailureDetectionHTMLContent'
GO
print '... Creating Procedure: NTD_DataVerificationFailureDetectionHTMLContent'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure NTD_DataVerificationFailureDetectionHTMLContent
  @o_htmlContent NVARCHAR(MAX) OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @timeStamp INT = 0
DECLARE @condition INT = 0
DECLARE @toggleConditionWorkFlowID INT = 0
DECLARE @corrupteddDataToFullBackWorkFlowID INT = 0
DECLARE @webConsoleURL NVARCHAR(MAX) = ''
DECLARE @adminConsoleURL NVARCHAR(MAX)
DECLARE @toggleOptionWorkFlowURL NVARCHAR(MAX)
DECLARE @corrupteddDataToFullBackURL NVARCHAR(MAX)
CREATE TABLE #appIdList (AppId INT PRIMARY KEY (AppId))
CREATE TABLE #libraryList (LibraryName NVARCHAR(128), MountPathName NVARCHAR(1024))
CREATE TABLE #ArchMarkNextBackupFullForDVFailedSCs_Output (retVal    INTEGER, timeStamp INT)
EXEC ArchMarkNextBackupFullForDVFailedSCs 0, 0
SELECT @timeStamp = timeStamp FROM #ArchMarkNextBackupFullForDVFailedSCs_Output
WHERE ((timeStamp IS NOT NULL) AND (timeStamp > 0))
DECLARE @outputTable TABLE
(clientName NVARCHAR(MAX),
 idaName NVARCHAR(MAX),
 instanceName NVARCHAR(MAX),
 backupSetName NVARCHAR(MAX),
 subclientName NVARCHAR(MAX))
--SELECT * FROM #appIdList
IF EXISTS(SELECT 1 FROM #appIdList)
BEGIN
	IF EXISTS (SELECT 1 FROM GXGlobalParam WHERE name = 'WebConsoleURL')
	BEGIN
		DECLARE @dbURL NVARCHAR(MAX)
		DECLARE @lastIndex INT = 0
		SELECT @dbURL = value from GXGlobalParam where name = 'WebConsoleURL'
		SELECT @lastIndex = CHARINDEX('elosnocbew/', REVERSE(@dbURL))
		IF @lastIndex > 0
		BEGIN
			SET @webConsoleURL = SUBSTRING(@dbURL, 1, LEN(@dbURL)-@lastIndex+1)
		END
	END
	IF @webConsoleURL = ''
	BEGIN
		SET @webConsoleURL = 'http://localhost:80/webconsole'
	END
	SET @webConsoleURL = @webConsoleURL + '/'
	SET @adminConsoleURL = REPLACE(@webConsoleURL, 'webconsole', 'adminconsole')
	DECLARE @clientURL NVARCHAR(MAX) = @adminConsoleURL + '#/clientDetails/'
	SELECT @condition = value from MMConfigs  where name = 'MMCONFIG_MARK_NEXT_BKP_FULL_FOR_DV_FAILED_SUBCLIENTS'
	SELECT  @toggleConditionWorkFlowID = WorkflowId FROM WF_Definition WHERE Name = 'Toggle Automatic Conversion To Full Backup'
	SELECT  @corrupteddDataToFullBackWorkFlowID = WorkflowId FROM WF_Definition WHERE Name = 'Mark Selected Subclients To Run Full Backup'
	INSERT INTO @outputTable
	SELECT   N'<a href="'+ @clientURL+ CONVERT(NVARCHAR(MAX),C.id) + '"' + '>'+ C.name+ '</a>', ida.displayName, inst.name, bs.name, app.subclientName
	FROM #appIdList ait
		INNER JOIN APP_Application app ON app.id = ait.AppId
		INNER JOIN App_Client C ON c.id= app.clientId
		INNER JOIN APP_iDAType ida ON  ida.type = app.appTypeId
		INNER JOIN APP_InstanceName inst ON inst.id = app.instance
		INNER JOIN APP_BackupSetName  bs ON  bs.id  = app.backupSet
	SET     @o_htmlContent  = N'<html>
						<head>
						<style>
						table, th, td {
							border: 1px solid black;
							border-collapse: collapse;
						}
						th, td {
							padding: 15px;
						}
						</style>
						</head>
						<body>'
					+ N'<h4 align="center">The software detected corrupted data on backup disk. The following servers are affected.</h4>'
	If @condition = 1
	BEGIN
		SET @toggleOptionWorkFlowURL = @webConsoleURL + 'forms/?tab=0&workflowId=' + CONVERT(NVARCHAR(256), @toggleConditionWorkFlowID) + '&toggleCondition=0' + '&timeStamp=0' +'&_submit=false'
		SET  @o_htmlContent  = @o_htmlContent
					+ N'<p>Next backup will be automatically converted to Full. '
					+ N'<a href="'+ @toggleOptionWorkFlowURL + '"' + 'target="_top">Click here</a>'
					+ N' to disable automatic conversion.</p>'
	END
	ELSE
	BEGIN
		SET @toggleOptionWorkFlowURL = @webConsoleURL + 'forms/?tab=0&workflowId=' + CONVERT(NVARCHAR(256), @toggleConditionWorkFlowID) + '&toggleCondition=1' + '&timeStamp=' + CONVERT(NVARCHAR(MAX), @timeStamp) + '&_submit=false'
		SET @corrupteddDataToFullBackURL = @webConsoleURL + 'forms/?tab=0&workflowId='+ CONVERT(NVARCHAR(256),@corrupteddDataToFullBackWorkFlowID) + '&timeStamp=' + CONVERT(NVARCHAR(MAX), @timeStamp) + '&_submit=false'
		SET @o_htmlContent  = @o_htmlContent
						+ N'<p>Automatic conversion to Full on verification failure is disabled, please '
						+ N'<a href="' + @toggleOptionWorkFlowURL + '"' + 'target="_top">click here</a>'
						+ N' to enable the option.</p>'
						+ N'<p>Please '
						+ N'<a href="' + @corrupteddDataToFullBackURL + '"' + 'target="_top">click here</a>'
						+ N' to convert these subclient’s next backup to FULL.</p>'
	END
	SET  @o_htmlContent  = @o_htmlContent  + N'<table>'
		+ N'<tr><th>Client Name</th><th>Agent Name</th><th>Instance Name</th><th>Backupset Name</th><th>Subclient Name</th></tr>'
		+ CAST((
			SELECT  o.clientName AS td,
					o.idaName AS td,
					o.instanceName AS td,
					o.backupSetName AS td,
					o.subclientName AS td
			FROM     @outputTable o
			FOR XML RAW('tr'), ELEMENTS
		) AS NVARCHAR(MAX))
		+ N'</table>'
	IF EXISTS (SELECT 1 FROM #libraryList)
	BEGIN
		SET  @o_htmlContent  = @o_htmlContent  + N'<br><br>'
		SET  @o_htmlContent  = @o_htmlContent  + N'<table>'
			+ N'<tr><th>Library Name</th><th>MountPath Name</th></tr>'
			+ CAST((
				SELECT  o.LibraryName AS td,
						o.MountPathName AS td
				FROM     #libraryList o
				FOR XML RAW('tr'), ELEMENTS
			) AS NVARCHAR(MAX))
			+ N'</table>'
	END
	SET @o_htmlContent  = @o_htmlContent  + N'</body>'
					+ N'</html>'
END
ELSE
BEGIN
	SET @o_htmlContent = ''
END
--SELECT @o_htmlContent
SET NOCOUNT OFF
GO

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

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

insert into GXDBVersions values(2, 'NTD_DataVerificationFailureDetectionHTMLContent',  '00000000000000000000', 'NTD_DataVerificationFailureDetectionHTMLContent', '00000000000000000000')
GO

