--@querytype CSV
--Name:- Get Gui Audit Trail Data

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET NOCOUNT ON

--Script is not for 9.0 CommCells
IF EXISTS (SELECT 1 FROM APP_Client WHERE releaseId <= 14 AND id = 2)
BEGIN 
GOTO EX_EXIT
END

DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @frequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END
 
IF ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode = '1')) --Private
BEGIN
	GOTO EX_EXIT
END

DECLARE @queryUpdate NVARCHAR(MAX)
SET  @queryUpdate = '
BEGIN TRY
DECLARE @startIndex INT = 0
IF OBJECT_ID(''GetSurveyParamValue'') IS NOT NULL
BEGIN
	DECLARE @startIndexSTR VARCHAR(256)='''';
	EXEC GetSurveyParamValue ''CommservSurveyGUIAuditTrailCollectionStartIndex'', @startIndexSTR OUTPUT 
	IF @startIndexSTR <> ''''
		SELECT @startIndex = CONVERT(INT,@startIndexSTR)
END
ELSE
BEGIN
	IF EXISTS (SELECT 1 FROM GXGlobalParam WHERE name = ''CommservSurveyGUIAuditTrailCollectionStartIndex'')
		SELECT @startIndex = CONVERT(INT,CONVERT(VARCHAR,value))
										FROM GXGlobalParam
										WHERE name = ''CommservSurveyGUIAuditTrailCollectionStartIndex''
END
									
DECLARE @now INTEGER = dbo.GetUnixTime(GetUTCdate())
DECLARE @guid NVARCHAR(MAX)

SET @guid = (SELECT csGUID FROM APP_Commcell WITH (NOLOCK) WHERE id = 2)

DECLARE @SNo NVARCHAR(32)
DECLARE @Registration NVARCHAR(16)
DECLARE @i INT = 0 
DECLARE @Slen INT = 0
DECLARE @Rlen INT = 0

SELECT @guid = REPLACE(@guid,''-'','''')
SELECT @SNo = SUBSTRING(@guid,1,22)
SELECT @Registration = SUBSTRING(@guid,LEN(@SNo)+1,LEN(@guid))
   
WHILE @i < 4 AND (@Slen + 5) < LEN(@SNo)
BEGIN
	SET @Slen = @Slen + 5
   	SELECT @SNo = STUFF(@SNo,@Slen,0,''-'')
   	SET @Rlen = @Rlen + 4
   	IF @i < 2 AND @Rlen < LEN(@Registration)
   	BEGIN 
   		SELECT @Registration = STUFF(@Registration,@Rlen,0,''-'')
   	END
   	SET @i = @i + 1
END
IF OBJECT_ID(''tempdb.dbo.#EvGUIAuditForLM'') IS NOT NULL  DROP TABLE #EvGUIAuditForLM
CREATE  TABLE #EvGUIAuditForLM  ( opid INT,	timeSource INT,	comcellName NVARCHAR(1024),	userId INT,	userName NVARCHAR(1024), auditLevel NVARCHAR(128),	operationText NVARCHAR(MAX),messageText NVARCHAR(MAX), dt datetime  )
CREATE CLUSTERED INDEX IX_EvGuiAuditMForLM ON #EvGUIAuditForLM (opId, userId, timesource ) 

DECLARE @DeleteOpIds TABLE ( opids INT)	
DECLARE @xmlDiffOps TABLE (	operationId INT)
DECLARE @opids TABLE (id INTEGER)

IF OBJECT_ID(''tempdb.dbo.#EvGuiAuditMessageTemp'') IS NOT NULL  DROP TABLE #EvGuiAuditMessageTemp
CREATE TABLE #EvGuiAuditMessageTemp (opId INT , timeServer INT,	auditLevel NVARCHAR(32), commCellID INT, 
	commCellName NVARCHAR(1024), userID INT, paramID INT,	userName NVARCHAR(1024),
	locID INT, oldMsgId INT, newMsgID INT, msgSpanNum INT, oldValue NVARCHAR(MAX), 
	newValue NVARCHAR(MAX),	operationText NVARCHAR(MAX), messageText NVARCHAR(MAX), messageId INT, isXMLDiff INT, isDelOp INT, EvMsgId BIGINT , status INT , ActMsg NVARCHAR(MAX) )



DECLARE @csTimeZone NVARCHAR(MAX)
SET @csTimeZone = ''Eastern Standard Time''


DECLARE @minId INT
DECLARE @maxId INT

IF @startIndex <> 0 
BEGIN
	SET @minId = @startIndex
	  SET @maxId = ISNULL((SELECT MAX(opid) FROM EvGuiAuditMessage WITH (NOLOCK) WHERE opId >= @minId ),0)
	  
		
END
ELSE
BEGIN
	 SET @maxId = ISNULL((SELECT MAX(opid) FROM
		                  EvGuiAuditMessage WITH (NOLOCK) ),0)
	
	SET @minId =  @maxId - 5000
	IF @minId < 0 
		SET @minId = 0 			
END 

	
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))
				 ) AND  opid >= @minId AND opid <= @maxId 				 
				 				
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))) AND opid >= @minId AND opid <= @maxId				

INSERT @opids
SELECT DISTINCT opid FROM EvGuiAuditMessage WITH (NOLOCK)  WHERE opid >= @minId AND opid <= @maxId ORDER BY opid 

INSERT INTO #EvGuiAuditMessageTemp (opId  , timeServer ,	auditLevel , commCellID , 
	commCellName , userID , paramID ,	userName ,	locID , oldMsgId , newMsgID , msgSpanNum , oldValue ,
	newValue ,	operationText , messageText , messageId , isXMLDiff , isDelOp, EvMsgId ,status  )
SELECT 	opId,timeServer,(CASE auditLevel
						WHEN 1 THEN '' Critical''
						WHEN 2 THEN ''High''
						WHEN 3 THEN ''Medium''
						WHEN 4 THEN ''Low''
						END),
commcellId, ISNULL(AC.aliasName,''''),	userId,paramId,ISNULL(U.name,''''),
locId,oldMsgId,newMsgId,msgSpanNum,oldValue,newValue, 
 (CASE WHEN paramid = 0 THEN messageText  ELSE '''' END)
,(CASE WHEN paramid <> 0 THEN messageText ELSE '''' END),
messageId,0,0, (626 | (CAST(POWER(2, 24) AS BIGINT) * 85)), 0 
FROM EvGuiAuditMessage evgui WITH (NOLOCK)
	INNER JOIN @opids op 
		ON evgui.opid = op.id
	INNER JOIN APP_Commcell AC 
	ON evgui.commcellId = AC.id 
	INNER JOIN UMUsers U 
	ON evgui.userId = U.id
ORDER BY evgui.evMsgId ASC

CREATE CLUSTERED INDEX IX_EvGuiAuditMessageTemp ON #EvGuiAuditMessageTemp (opId, userId, paramId )
CREATE NONCLUSTERED INDEX NONIX_EvGuiAuditMessageTemp1 ON #EvGuiAuditMessageTemp ( isXmlDiff,status) INCLUDE( oldValue, newvalue)

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

UPDATE #EvGuiAuditMessageTemp
SET status = 0
WHERE isXMLDiff = 1 AND (RTRIM(LTRIM(oldValue)) LIKE ''%\[DoNotAudit\]%'' ESCAPE(''\'') OR RTRIM(LTRIM(newValue)) LIKE ''%\[DoNotAudit\]%'' ESCAPE(''\'')) 

UPDATE #EvGuiAuditMessageTemp
SET status = 0 , messageText = N''''
WHERE isXMLDiff = 1  AND (messageText LIKE (''Xml Root'') OR LEN(messageText) = 0)

UPDATE A
SET A.EvMsgId = (835 | (CAST(POWER(2, 24) AS BIGINT) * 85)) , status = 1
FROM #EvGuiAuditMessageTemp A
WHERE isXMLDiff = 1  AND ( (LEN(oldValue) =0 OR oldValue= ''0'') AND (LEN(newValue)=1 AND newValue = ''1''))



UPDATE #EvGuiAuditMessageTemp
SET EvMsgId = (838 | (CAST(POWER(2, 24) AS BIGINT) * 85)) , status = 1
WHERE isXMLDiff = 1  AND ( (LEN(oldValue) =1 OR oldValue= ''1'') AND (LEN(newValue)=0 OR newValue = ''0''))


UPDATE #EvGuiAuditMessageTemp
SET EvMsgId = (838 | (CAST(POWER(2, 24) AS BIGINT) * 85)) , status = 1
WHERE  isXMLDiff = 1  AND (LEN(oldValue) =0 AND LEN(newValue) > 0 AND newValue = ''0'' )


UPDATE #EvGuiAuditMessageTemp
SET  EvMsgId = (838 | (CAST(POWER(2, 24) AS BIGINT) * 85)) , status = 1
WHERE isXMLDiff = 1  AND (LEN(newValue) =0 AND LEN(oldValue) > 0 AND oldValue = ''0'' )



UPDATE #EvGuiAuditMessageTemp
SET EvMsgId = 0 , status = 1
WHERE isXMLDiff = 1  AND ((LEN(oldValue) = 0) AND (LEN(newValue) = 0))


UPDATE #EvGuiAuditMessageTemp
SET EvMsgId = (833 | (CAST(POWER(2, 24) AS BIGINT) * 85)) , status = 1
 WHERE isXMLDiff = 1  AND (LEN(oldValue) = 0)

 
UPDATE #EvGuiAuditMessageTemp
SET EvMsgId = (834 | (CAST(POWER(2, 24) AS BIGINT) * 85)) , status = 1
WHERE isXMLDiff = 1  AND  (LEN(newValue) = 0)


UPDATE A
SET A.ActMsg = B.Message
FROM #EvGuiAuditMessageTemp  A INNER JOIN  EvLocaleMsgs B  ON A.EvMsgId = B.MessageID AND B.LocaleID = 0
WHERE status = 1  AND isXMLDiff = 1

UPDATE #EvGuiAuditMessageTemp
SET messageText = CASE  
					WHEN  (EvMsgId = (626 | (CAST(POWER(2, 24) AS BIGINT) * 85)))  AND isDelOp = 0 
						THEN REPLACE(REPLACE(REPLACE(ActMsg,''^1%s'',messageText), ''^2%s'', newValue),''^3%s'',oldValue)
					WHEN  (EvMsgId = (626 | (CAST(POWER(2, 24) AS BIGINT) * 85)))  AND isDelOp = 1 
				         THEN REPLACE(REPLACE(REPLACE(ActMsg,''^1%s'',messageText), ''^2%s'', oldValue),''^3%s'',newValue)
					WHEN (EvMsgId = (835 | (CAST(POWER(2, 24) AS BIGINT) * 85)) OR EvMsgId = (838 | (CAST(POWER(2, 24) AS BIGINT) * 85)) )
						THEN  REPLACE(ActMsg,''^1%s'',messageText)
					WHEN (EvMsgId = (834 | (CAST(POWER(2, 24) AS BIGINT) * 85))) AND isDelOp = 0
						THEN  REPLACE(REPLACE(ActMsg,''^1%s'',messageText), ''^2%s'',oldValue)
						WHEN (EvMsgId = (834 | (CAST(POWER(2, 24) AS BIGINT) * 85))) AND isDelOp = 1
						THEN  REPLACE(REPLACE(ActMsg,''^1%s'',messageText), ''^2%s'',newValue)
					WHEN (EvMsgId = (833 | (CAST(POWER(2, 24) AS BIGINT) * 85))) AND isDelOp = 0 
						THEN REPLACE(REPLACE(ActMsg,''^1%s'',messageText), ''^2%s'',newValue)
					WHEN (EvMsgId = (833 | (CAST(POWER(2, 24) AS BIGINT) * 85))) AND isDelOp = 1 
						THEN REPLACE(REPLACE(ActMsg,''^1%s'',messageText), ''^2%s'',oldValue)
					ELSE messageText
					END
WHERE status = 1 AND isXMLDiff = 1


INSERT #EvGUIAuditForLM (opId , userId) 
SELECT DISTINCT opid , userID    
FROM 	#EvGuiAuditMessageTemp AS oEvGUI



UPDATE A
SET A.timesource = B.timeServer,   A.operationText = B.operationText, A.comcellName = B.commCellName, A.auditLevel = B.auditLevel, A.userName = B.userName
FROM #EvGUIAuditForLM A INNER JOIN #EvGuiAuditMessageTemp B
ON A.opid = B.opId AND A.userId = B.userID AND B.paramID = 0
WHERE B.paramID = 0 
   

UPDATE oEvGUi
SET  oEvGUi.messageText = STUFF( (SELECT '' '' +  messageText
	FROM #EvGuiAuditMessageTemp
		WHERE  	 opid = oEvGUI.opid AND userid = oEvGUI.userid
		FOR XML PATH(''''))
        , 1, 2, '''')
FROM #EvGUIAuditForLM oEvGUi

UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''&#x20;'',N'' '')

UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''x20;'',N'''')

UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''&#x0D;'',N'' '')

UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''&quot;'',N''"'')


UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''&apos;'',N'''')

UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''&gt;'',N''>'')

 
UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''&lt;'',N''<'')

UPDATE #EvGUIAuditForLM
SET messageText = REPLACE(messagetext, N''&amp;'',N''&'')

UPDATE #EvGUIAuditForLM
SEt dt = dbo.UTCToLocalTime(dbo.GetDatetime(timeSource),@csTimeZone)
   
IF @maxId <> 0
	SET @startIndex = @maxId + 1

SELECT @SNo ''Serial No'', @Registration ''Reg Code'', CONVERT(date, dt) ''Audit Date'', CONVERT(NVARCHAR(MAX), dt, 108) ''Audit Time'', auditLevel, comcellName, userName, operationText,REPLACE(REPLACE(messageText, CHAR(13), ''''), CHAR(10), '''') AS messageText 
FROM #EvGUIAuditForLM  
 

IF OBJECT_ID(''tempdb.dbo.#EvGUIAuditForLM'') IS NOT NULL  DROP TABLE #EvGUIAuditForLM
IF OBJECT_ID(''tempdb.dbo.#EvGuiAuditMessageTemp'') IS NOT NULL  DROP TABLE #EvGuiAuditMessageTemp

END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH
	
IF OBJECT_ID(''SetSurveyParamValue'') IS NOT NULL
BEGIN
	EXEC SetSurveyParamValue ''CommservSurveyGUIAuditTrailCollectionStartIndex'', @startIndex
END
ELSE
BEGIN
	IF EXISTS (	SELECT 1  
				FROM GXGlobalParam WITH (NOLOCK)
				WHERE name = ''CommservSurveyGUIAuditTrailCollectionStartIndex'')
	BEGIN 
		UPDATE GXGlobalParam
		SET value = @startIndex , modified = dbo.GetUnixTime(GETUTCDATE())
		WHERE name = ''CommservSurveyGUIAuditTrailCollectionStartIndex''
	END 
	ELSE
	BEGIN 
		INSERT GXGlobalParam(name, value, created, modified)
		VALUES(''CommservSurveyGUIAuditTrailCollectionStartIndex'', @startIndex, dbo.GetUnixTime(GETUTCDATE()), 0)
END
END'

EXEC sp_executesql @queryUpdate

EX_EXIT: 

SET NOCOUNT OFF