USE [EUC]
GO
/****** Object: StoredProcedure [dbo].[SP_SME_QueryAuditLog] Script Date: 02/05/2015 15:54:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_SME_QueryAuditLog]
(
@ReferenceTable VARCHAR(50),
@ReferenceNumber NVARCHAR(100)
)
AS
BEGIN
DECLARE @NodeName VARCHAR(50);
DECLARE @NodeValue NVARCHAR(500);
DECLARE @NodePreValue NVARCHAR(500);
DECLARE @LogId uniqueidentifier;
DECLARE @LogXml xml;
DECLARE @ProcessDate datetime;
DECLARE @PreLogXml xml;
DECLARE @OperationLog NVARCHAR(4000);
DECLARE @FirstLogId uniqueidentifier;
DECLARE @FirstOperationLog NVARCHAR(4000);
DECLARE @FirstLogXml xml;
DECLARE @Table NVARCHAR(50);
set @Table=(case when @ReferenceTable='T_SME_CustInfo' then 'Customer Info' when @ReferenceTable='T_SME_IDExpiry' then 'ID Expiry'
when @ReferenceTable='T_SME_AC' then 'Account Info' when @ReferenceTable='T_SME_ParmACType' then 'Account Type'
when @ReferenceTable='T_SME_ParmCorpType' then 'Corporation Type' when @ReferenceTable='T_SME_ParmCountryCurrency' then 'Country Currency'
when @ReferenceTable='T_SME_ParmIDType' then 'ID Type' when @ReferenceTable='T_SME_ParmMultiCountry' then 'Multiple Country Type'
when @ReferenceTable='T_SME_ParmSettleType' then 'Settle Type' when @ReferenceTable='T_SME_ParmSpecialZone' then 'Special Zone Type'
when @ReferenceTable='T_SME_ParmTranType' then 'Transaction Type' when @ReferenceTable='T_SME_ParmRMBSource' then 'RMB Source'
when @ReferenceTable='T_SME_ParmBOPCode' then 'BOP Code' when @ReferenceTable='T_SME_ParmFinanceManager' then 'Finance Fanager' end );
SELECT TOP 1 @FirstLogId=LogId,@FirstOperationLog=OperationLog, @FirstLogXml=LogXml
FROM T_SME_AuditLog t WHERE ReferenceTable=@ReferenceTable AND (ReferenceNumber=@ReferenceNumber or LOWER(ReferenceNumber) = LOWER(@ReferenceNumber))
ORDER BY ProcessDate
IF (@FirstOperationLog IS NULL OR @FirstOperationLog = '')
BEGIN
print @FirstLogId;
SET @FirstOperationLog = '';
DECLARE nodename_cursor CURSOR FOR SELECT cast(LogItem.query('fn:local-name(.)') as VARCHAR(50)) as NodeName
FROM @FirstLogXml.nodes('/LogItem/*') AS T ( LogItem )
OPEN nodename_cursor
FETCH NEXT FROM nodename_cursor INTO @NodeName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @NodeValue = LogItem.value('(//*[local-name() = sql:variable("@NodeName")])[1]', 'nvarchar(500)')
FROM @FirstLogXml.nodes('/LogItem') AS T ( LogItem );
SET @FirstOperationLog = @FirstOperationLog + ' [' + @NodeName + ':] ' + @NodeValue;
FETCH NEXT FROM nodename_cursor INTO @NodeName
END
CLOSE nodename_cursor
DEALLOCATE nodename_cursor
UPDATE T_SME_AuditLog SET OperationLog = @FirstOperationLog WHERE LogId = @FirstLogId;
End
DECLARE audit_cursor CURSOR FOR SELECT LogId,OperationLog,LogXml,ProcessDate
FROM T_SME_AuditLog t WHERE ReferenceTable=@ReferenceTable AND (ReferenceNumber=@ReferenceNumber or LOWER(ReferenceNumber) = LOWER(@ReferenceNumber))
ORDER BY ProcessDate DESC;
OPEN audit_cursor
FETCH NEXT FROM audit_cursor INTO @LogId,@OperationLog,@LogXml,@ProcessDate
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@OperationLog IS NULL OR @OperationLog = '')
BEGIN
SET @OperationLog = '';
SELECT TOP 1 @PreLogXml = LogXml
FROM T_SME_AuditLog t
WHERE ReferenceTable=@ReferenceTable AND (ReferenceNumber=@ReferenceNumber or LOWER(ReferenceNumber) = LOWER(@ReferenceNumber)) AND ProcessDate<@ProcessDate
ORDER BY ProcessDate DESC;
DECLARE nodename_cursor CURSOR FOR SELECT cast(LogItem.query('fn:local-name(.)') as VARCHAR(50)) as NodeName
FROM @LogXml.nodes('/LogItem/*') AS T ( LogItem )
OPEN nodename_cursor
FETCH NEXT FROM nodename_cursor INTO @NodeName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @NodeValue = LogItem.value('(//*[local-name() = sql:variable("@NodeName")])[1]', 'nvarchar(500)')
FROM @LogXml.nodes('/LogItem') AS T ( LogItem );
SELECT @NodePreValue = LogItem.value('(//*[local-name() = sql:variable("@NodeName")])[1]', 'nvarchar(500)')
FROM @PreLogXml.nodes('/LogItem') AS T ( LogItem );
IF (@NodePreValue IS NOT NULL AND @NodePreValue != '')
BEGIN
IF (@NodeValue != @NodePreValue)
BEGIN
SET @OperationLog = @OperationLog+' ['+@NodeName + ':] '+@NodePreValue+' --> '+@NodeValue;
END
END
ELSE
BEGIN
SET @OperationLog = @OperationLog+' ['+@NodeName + ':] Empty --> '+@NodeValue;
END
FETCH NEXT FROM nodename_cursor INTO @NodeName
END
CLOSE nodename_cursor
DEALLOCATE nodename_cursor
END
UPDATE T_SME_AuditLog SET OperationLog = @OperationLog WHERE LogId = @LogId;
FETCH NEXT FROM audit_cursor INTO @LogId,@OperationLog,@LogXml,@ProcessDate
END
CLOSE audit_cursor;
DEALLOCATE audit_cursor;
SELECT LogId, Operation, OperationStatus, OperationLog, ProcessBy, ProcessDate ,@Table as 'OperationTable'
FROM T_SME_AuditLog t WHERE ReferenceTable=@ReferenceTable AND (ReferenceNumber=@ReferenceNumber or LOWER(ReferenceNumber) = LOWER(@ReferenceNumber))
ORDER BY ProcessDate DESC;
END