

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/LMGetGUIAuditDataSetForCI.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/LMGetGUIAuditDataSetForCI.sp,v $ $Id: LMGetGUIAuditDataSetForCI.sp,v 1.8.2.6.50.1 2021/07/13 17:51:05 snandhini Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='LMGetGUIAuditDataSetForCI')
	delete from GXDBVersions where aliasname = 'LMGetGUIAuditDataSetForCI'
GO
print '... Creating Procedure: LMGetGUIAuditDataSetForCI'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure LMGetGUIAuditDataSetForCI
  @i_xml XML 
AS
  DECLARE @xmlStr xml
SET NOCOUNT ON
DECLARE @startIndex INTEGER = ISNULL((SELECT ref.value('@startIndex','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),0)
DECLARE @endIndex INTEGER = ISNULL((SELECT ref.value('@endIndex','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),1000)
DECLARE @monitoringPolicyId INTEGER = ISNULL((SELECT ref.value('@monitoringPolicyId','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),0)
DECLARE @requestType INTEGER = ISNULL((SELECT ref.value('@type','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),0)
DECLARE @templateId INTEGER
SET @templateId = ISNULL((SELECT templateId FROM APP_MonitorTemplateAssoc WHERE monitorPolicyId = @monitoringPolicyId),0)
DECLARE @count INTEGER
SET @count = ISNULL((SELECT TOP 1 value FROM GxGlobalParam WHERE name like 'LMGUIAuditIndexBucket' AND modified = 0),350)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @startFromTime INTEGER = 0
DECLARE @now INTEGER = dbo.GEtUnixTime(GetUTCdate())
SET @startFromTime = ISNULL((SELECT modified FROM APP_MonitoringPolicyProp WHERE attrName = 'Do not monitor old data' AND attrVal = 1 AND monitorPolicyId = @monitoringPolicyId),0)
DECLARE  @EvGUIAuditForLM TABLE
(
	opid INTEGER,
	timeSource INTEGER,
	commcellId INTEGER,
	comcellName NVARCHAR(1024),
	userId INTEGER,
	userName NVARCHAR(1024),
	localeID INTEGER,
	auditLevel NVARCHAR(128),
	oldMsgId INTEGER,
	newMsgId INTEGER,
	msgSpanNum INTEGER,
	oldValue NVARCHAR(max),
	newValue NVARCHAR(max),
	operationText NVARCHAR(max),
	messageText NVARCHAR(max)
);
DECLARE @xmlDiffOps TABLE
(
	operationId INTEGER
)
DECLARE @minId INTEGER
DECLARE @maxId INTEGER
SET @minId = (SELECT MIN(opid) FROM EvGuiAuditMessage WITH (NOLOCK)  WHERE timeServer > @startFromTime AND opid  > @startIndex)
SET @maxId = ISNULL((SELECT MAX(TopCount.opid) FROM
							 (SELECT DISTINCT TOP (@count) opid FROM EvGuiAuditMessage WITH (NOLOCK) WHERE opId >= @minId) as TopCount
									),0)
INSERT INTO @xmlDiffOps
SELECT DISTINCT opId FROM EvGuiAuditMessage WITH (NOLOCK) WHERE
messageId IN ((78 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(79 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(80 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(81 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(82 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(83 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(273 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(343 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(282 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(275 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(276 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(283 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(344 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(346 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(347 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(348 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(349 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(350 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(179 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(89 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(284 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(369 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(371 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(372 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(392 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(393 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(394 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(430 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(431 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(432 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(296 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(297 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(77 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(75 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(76 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(298 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(450 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(451 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(452 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(453 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(424 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(425 | (CAST(POWER(2, 24) AS BIGINT) * 84)) ,
(426 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(322 | (CAST(POWER(2, 24) AS BIGINT) * 84))
				 ) AND opid >= @minId AND opid <= @maxId
DECLARE @DeleteOpIds TABLE
(
	opIds INTEGER
)
INSERT INTO @DeleteOpIds
SELECT DISTINCT opid
FROM EvGuiAuditMessage WITH (NOLOCK)  WHERE
messageId IN ((80 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(83 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(348 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(179 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(370 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(394 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(75 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(76 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(432 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(452 | (CAST(POWER(2, 24) AS BIGINT) * 84)),
(426 | (CAST(POWER(2, 24) AS BIGINT) * 84))
				) AND opid >= @minId AND opid <= @maxId
DECLARE @opId INTEGER
DECLARE @timeSource INTEGER
DEcLARE @auditLevel NVARCHAR(128)
DECLARE @commcellId INTEGER
DECLARE @comcellName NVARCHAR(1024)
DECLARE @userId INTEGER
DECLARE @paramId INTEGER
DECLARE @userName NVARCHAR(1024)
DECLARE @oldMsgId INTEGER
DECLARE @newMsgId INTEGER
DECLARE @msgSpanNum INTEGER
DECLARE @oldValue NVARCHAR(MAX) = ''
DECLARE @newValue NVARCHAR(MAX) = ''
DECLARE @messageText NVARCHAR(MAX)
DECLARE @opids TABLE(id INTEGER)
INSERT INTO @opids
SELECT DISTINCT TOP (@count) opid FROM EvGuiAuditMessage WITH (NOLOCK)  WHERE opid >= @minId AND opid <= @maxId ORDER BY opid
DECLARE @previousOpId INTEGER = 0
IF @requestType = 0
BEGIN
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb.dbo.#EvGUIAuditForLM1'))         DROP TABLE tempdb.dbo.#EvGUIAuditForLM1
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb.dbo.#ColumnRowFinal'))         DROP TABLE tempdb.dbo.#ColumnRowFinal
	CREATE TABLE  #EvGUIAuditForLM1
	(
		column0 INTEGER,
		column1 INTEGER,
		column2 INTEGER,
		column3 NVARCHAR(1024),
		column4 INTEGER,
		column5 NVARCHAR(1024),
		column6 INTEGER,
		column7 NVARCHAR(128),
		column8 INTEGER,
		column9 NVARCHAR(max),
		column10 NVARCHAR(max),
	);
	CREATE UNIQUE CLUSTERED INDEX EvGUIAuditForLM1_column0_idx ON #EvGUIAuditForLM1 (column0)
	DECLARE @ColumnRow TABLE
	(columnid INTEGER,
	 rowId INTEGER,
	columnName NVARCHAR(128),
	columnXML XML)
	CREATE TABLE #ColumnRowFinal
	(columnid INTEGER,
	 rowId INTEGER,
	columnName NVARCHAR(128),
	columnXML XML,
	columnData NVARCHAR(max),
	ColAndData XML)
	CREATE CLUSTERED INDEX ColumnRowFinal_rowId_idx ON #ColumnRowFinal (rowid)
	DECLARE @templ XML = CONVERT(XML,(SELECT templateXML FROM APP_Template WHERE templateId = @templateId))
	DECLARE @minColId INTEGER = (SELECT MIN(columnId) FROM APP_TemplateColumn WHERE templateId = @templateId)
	INSERT INTO @ColumnRow(columnid,columnXML)
	SELECT R.col.value('@index','INTEGER') as colIndex,
			R.col.query('.')
	FROM
	@templ.nodes('LogMonitoring_TemplateXML/columns') as R(col)
	UPDATE ColumnDataTemp
	SET ColumnDataTemp.columnName = tempCol.columnName
	FROM @ColumnRow ColumnDataTemp INNER JOIN APP_TemplateColumn tempCol
		ON ColumnDataTemp.columnid = (tempCol.columnId - @minColId) AND templateId = @templateId
	DECLARE @EvGuiAuditMessageTemp TABLE
		(opId INTEGER,
		timeServer INTEGER,
		auditLevel NVARCHAR(32),
		commCellID INTEGER,
		commCellName NVARCHAR(1024),
		userID INTEGER,
		paramID INTEGER,
		userName NVARCHAR(1024),
		locID INTEGER,
		oldMsgId INTEGER,
		newMsgID INTEGER,
		msgSpanNum INTEGER,
		oldValue NVARCHAR(MAX),
		newValue NVARCHAR(MAX),
		operationText NVARCHAR(MAX),
		messageText NVARCHAR(MAX),
		messageId INTEGER,
		isXMLDiff INTEGER,
		isDelOp INTEGER
		)
	INSERT INTO @EvGuiAuditMessageTemp
	SELECT
	opId,timeServer,(CASE auditLevel
	WHEN 1 THEN 'Critical'
	WHEN 2 THEN 'High'
	WHEN 3 THEN 'Medium'
	WHEN 4 THEN 'Low'
	END),commcellId,
	ISNULL(dbo.GetCommCellNameFromId(commCellID),''),
	userId,paramId,ISNULL(dbo.GetUserName(userid),''),
	locId,oldMsgId,newMsgId,msgSpanNum,oldValue,newValue,(CASE WHEN paramid = 0 THEN dbo.EncodeInvalidXMLChar(messageText) ELSE '' END),(CASE WHEN paramid <> 0 THEN dbo.EncodeInvalidXMLChar(messageText) ELSE '' END),messageId,0,0
	FROM EvGuiAuditMessage evgui WITH (NOLOCK)  INNER JOIN @opids op ON evgui.opid = op.id
	ORDER BY evgui.evMsgId ASC
	UPDATE evGuiTemp
	SET isXMLDiff = 1
	FROM @EvGuiAuditMessageTemp evGuiTemp INNER JOIN @xmlDiffOps xmlDiff
		ON evGuiTemp.opid = xmlDiff.operationId
	UPDATE evGuiTemp
	SET isDelOp = 1
	FROM @EvGuiAuditMessageTemp evGuiTemp INNER JOIN @DeleteOpIds delOps
		ON evGuiTemp.opid = delOps.opIds
	WHERE isXMLDiff = 1
	INSERT INTO @EvGUIAuditForLM
	SELECT opid,timeServer,commCellID,commCellName,userid,userName,locID,auditLevel,oldMsgId,newMsgID,msgSpanNum,oldValue,newValue,operationText,STUFF(
			(SELECT ' ' + messageText
			FROM @EvGuiAuditMessageTemp
			WHERE isXMLDiff = 0 AND opid = oEvGUI.opid AND userid = oEvGUI.userid
			FOR XML PATH(''))
			,1,1,N'') as msgTxt
	 FROM 	@EvGuiAuditMessageTemp AS oEvGUI
	 WHERE LEN(operationText) <> 0 AND isXMLDiff = 0
	GROUP BY opid,timeServer,commCellID,commCellName,userid,userName,locID,auditLevel,oldMsgId,newMsgID,msgSpanNum,oldValue,newValue,operationText
	INSERT INTO @EvGUIAuditForLM
	SELECT opid,timeServer,commCellID,commCellName,userid,userName,locID,auditLevel,oldMsgId,newMsgID,msgSpanNum,oldValue,newValue,operationText,STUFF(
			(SELECT ' ' + dbo.LMGetAuditMsgText(messageText,oldValue,newValue,isDelOp)
			FROM @EvGuiAuditMessageTemp
			WHERE isXMLDiff = 1 AND opid = oEvGUI.opid AND userid = oEvGUI.userid
			FOR XML PATH(''))
			,1,1,'') as msgTxt
	 FROM 	@EvGuiAuditMessageTemp AS oEvGUI
	 WHERE LEN(operationText) <> 0 AND isXMLDiff = 1
	GROUP BY opid,timeServer,commCellID,commCellName,userid,userName,locID,auditLevel,oldMsgId,newMsgID,msgSpanNum,oldValue,newValue,operationText
	DECLARE @localeId INT
	DECLARE @localeAttr NVARCHAR(10) = 'Locale Id'
	IF EXISTS (SELECT TOP 1 attrVal
				FROM APP_MonitoringPolicyProp WITH(NOLOCK)
				WHERE monitorPolicyId = @monitoringPolicyId AND attrName = @localeAttr)
	BEGIN
		SET @localeId = (SELECT TOP 1 attrVal
				FROM APP_MonitoringPolicyProp WITH(NOLOCK)
				WHERE monitorPolicyId = @monitoringPolicyId AND attrName = @localeAttr)
	END
	ELSE
	BEGIN
		SET @localeId = (SELECT TOP 1 locId FROM EvGuiAuditMessage WITH(NOLOCK)
						ORDER BY locId ASC)
		INSERT INTO APP_MonitoringPolicyProp
		VALUES(@monitoringPolicyId,2,@localeAttr,7,@localeId,@now,0)
	END
	INSERT INTO #EvGUIAuditForLM1
	SELECT opid,timeSource,timeSource, auditLevel, commcellId, comcellName, userId,userName,localeID, operationText,messageText
			FROM @EvGUIAuditForLM
			WHERE localeID = @localeId
	INSERT INTO #ColumnRowFinal
	SELECT ColRow.columnid,ids.column0,ColRow.columnName,ColRow.columnXML,'',NULL
	FROM @ColumnRow ColRow,#EvGUIAuditForLM1 ids
	DECLARE @columnCount INTEGER =  (SELECT COUNT(*) FROM APP_TemplateColumn WHERE templateId = @templateId)
	DECLARE @i INTEGER = 0
	WHILE @i < @columnCount
	BEGIN
	DECLARE @queryUpdate NVARCHAR(MAX) =  'UPDATE colFinal
										SET colFinal.columnData = rowLm.column'+ CONVERT(NVARCHAR(128),@i)+
									'  FROM #ColumnRowFinal colFinal INNER JOIN #EvGUIAuditForLM1 rowLm
									ON colFinal.rowId = rowLm.column0 AND colFinal.columnid = '+CONVERT(NVARCHAR(20),@i)
	--print @queryUpdate
	EXECUTE sp_executesql @queryUpdate
	SET @i = @i + 1
	END
	UPDATE #ColumnRowFinal
	SET ColAndData = ( SELECT columnData as '@dataAsWString', columnXML as 'node()' FOR XML PATH('rowColumn'))
	DECLARE @rowColumnWiseFinal TABLE(id INTEGER,ColumnList XML)
	INSERT INTO @rowColumnWiseFinal
	SELECT
	  rowId,(SELECT columnData as '@dataAsWString',columnXML as 'node()'
		FROM #ColumnRowFinal
		WHERE rowId = Result.rowId
		FOR XML PATH ('rowColumn'))
	FROM #ColumnRowFinal Result
	GROUP BY rowId
	SET @xmlStr = ISNULL((SELECT ColumnList as 'node()'
	FROM @rowColumnWiseFinal
	ORDER BY id
	FOR XML PATH('rows'),ROOT('LogMonitoring_DataSetRowsWithRowAsColumnList')),'<LogMonitoring_DataSetRowsWithRowAsColumnList/>')
END
ELSE IF @requestType = 1
BEGIN
	IF (SELECT COUNT(*) FROM @opids) >= 1
		SET @xmlStr = (SELECT 0 as '@errorCode' FOR XML PATH('App_GenericResp'))
	ELSE
		SET @xmlStr = (SELECT 1 as '@errorCode' FOR XML PATH('App_GenericResp'))
END
SELECT @xmlStr
GO

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

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

insert into GXDBVersions values(2, 'LMGetGUIAuditDataSetForCI',  'v1.8.2.6.50.1', 'LMGetGUIAuditDataSetForCI', 'v1.8.2.6.50.1')
GO

