iVend Database Database : LogAuditInformation Stored Procedure

Creation Date

9/18/2015 10:23 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pSourceType

In

Object type for which the data has been updated

Integer

4

@pSourceKey

In

Reference key of the Source object

VarWChar

50

@pOperationType

In

Operation type that took place on the data

Integer

4

@pUserKey

In

The user who has made the changes

VarWChar

50

@pSiteId

In

Integer

4

@pBatchKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

DataNotification

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

CfgAuditLog

Table

Stores system logs.

1

CfgAuditLogFilter

Table

System Table. Defines for which columns the logs will be captured.

1

CfgAuditLogMaster

Table

System Table. Stores the list of tables for which the system logs are generated.

1

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

GetCompanyDateTime

User Defined Function

1

GetNextSiteNumber

Stored Procedure

1

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

1

RtlPOS

Table

Contains details about all POS defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

CREATE PROCEDURE [dbo].[LogAuditInformation]

(

    @pSourceType INT,

    @pSourceKey NVARCHAR(50),

    @pOperationType INT,

    @pUserKey NVARCHAR(50),

    @pSiteId INT,

    @pBatchKey NVARCHAR(50)

)

AS

BEGIN

  SET NOCOUNT ON

  DECLARE @lErrorDesc VARCHAR(255),

          @TableName NVARCHAR(255),

          @PrimaryKey NVARCHAR(255),

          @RecordXML  XML,

          @PreviousXML XML,

  @SqlGetId NVARCHAR(MAX),

          @FromSql NVARCHAR(MAX),

          @SqlExecute NVARCHAR(MAX),

          @IdColumn NVARCHAR(100),

          @RecordId NVARCHAR(20),

          @CreateLog BIT,

          @lNewKey NVARCHAR(50),

          @lStoreCount INT,

          @lMobileCount INT,

  @lAuditKey NVARCHAR(50),

  @lPOSSiteId INT,

  @lSiteId INT

IF @pSourceType = 0 -- NONE

      RETURN

IF @pSourceType = 375 AND @pOperationType = 3

BEGIN

DELETE FROM CfgAuditLog WHERE SourceType = @pSourceKey

IF @pSourceKey = 81

BEGIN

  DELETE FROM CfgAuditLog WHERE SourceType IN (80, 81)

END

SELECT @lNewKey = NEWID()

---EXEC GetNextSiteNumber @pSiteId, 'Table.ReplicationTransaction', @lNewKey OUTPUT, 0, 0

INSERT INTO RepReplicationTransaction (ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType, Flag)

VALUES(@lNewKey, 375, @pSourceKey, @pBatchKey, 3, 'FALSE')

END

  IF NOT EXISTS (SELECT TableName FROM CfgAuditLogMaster WHERE ObjectId = @pSourceType AND IsLoggingEnabled = 1 )

  RETURN

BEGIN TRY

BEGIN

SELECT @lStoreCount = COUNT(*) FROM RtlStore WHERE SiteId > 1

SELECT @lMobileCount = COUNT(*) FROM RtlPOS WHERE IsDeleted = 0 AND POSType = 1

SELECT @lPOSSiteId = ISNULL(POSSiteId, 0), @lSiteId = ISNULL(SiteId, 0) FROM CfgSiteInformation

SET @lStoreCount = ISNULL(@lStoreCount,0) + ISNULL(@lMobileCount,0)

---Select Id Column Name

  SELECT @TableName = TableName,

            @PrimaryKey = PrimaryKeyColumn,

            @IdColumn = RecordId,

    @FromSql = Query

      FROM   CfgAuditLogMaster

      WHERE  ObjectId = @pSourceType

  ----GENERATE QUERY TO CREATE XML WITH FILTERED COLUMNS

DECLARE @colNames AS NVARCHAR(MAX)

DECLARE @SelectQuery AS NVARCHAR(MAX)

SELECT @colNames = COALESCE(@colNames + ',' , '') +

 (

  CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND DATA_TYPE = 'bit'

  AND COLUMN_NAME = SUBSTRING(ColumnName,CHARINDEX('.',ColumnName) + 1,LEN(ColumnName))) THEN

   (' case when '+ ColumnName + ' = 1 then ''True'' else ''False'' End  '''+ SUBSTRING(ColumnName,CHARINDEX('.',ColumnName)+1,LEN(ColumnName)) + '''')

  ELSE ColumnName

  END

 )

FROM CfgAuditLogFilter

WHERE ObjectId = @pSourceType

ORDER BY LogFilterKey

IF( @colNames IS NULL)

BEGIN

  SELECT @colNames = COALESCE(@colNames + ',' , '') +

  (

    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND DATA_TYPE = 'bit'

    AND COLUMN_NAME = SUBSTRING([name],CHARINDEX('.',[name]) + 1,LEN([name]))) THEN

    (' case when '+ [name] + ' = 1 then ''True'' else ''False'' End  '''+ SUBSTRING([name],CHARINDEX('.',[name])+1,LEN([name])) + '''')

    ELSE [name]

    END

  )

  FROM syscolumns

  WHERE id = (SELECT id FROM sysobjects WHERE type = 'U'

  AND [name] = @TableName)

  AND [name] <> 'Modified'

  AND [xtype] NOT IN (34, 165, 173, 189, 241) --Image, varbinary, binary, timestamp, xml

END

SET @FromSql = (SELECT CAM.Query FROM dbo.CfgAuditLogMaster CAM WHERE CAM.ObjectId= @pSourceType)

IF( @FromSql IS NULL OR LEN(RTRIM(LTRIM(@FromSql))) =0 )

BEGIN

  --SET @colNames = '*'

  SET @FromSql = 'FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' = ' --convert(nvarchar(255),' + ''''+ @pSourceKey + '''' +')'

END

SET @SelectQuery = 'SELECT  ' + @colNames + ' ' + @FromSql

SET @SqlExecute =('SELECT @RecordXML = ( '+ @SelectQuery + 'convert(nvarchar(255),''' + @pSourceKey +''')'+ ' FOR XML RAW , ROOT , ELEMENTS )')

---THIS WILL GENERATE THE ID COLUMN FOR THE RECORD

SET @SqlGetId = ('SELECT  @RecordId = ' + @IdColumn + ' ' + @FromSql + 'convert(nvarchar(255),'''+ @pSourceKey +''')' )

EXEC sp_executesql @SqlGetId, N'@pSourceKey NVARCHAR(50), @RecordId nvarchar(20) out', @pSourceKey, @RecordId OUT

---END OF GENERATION OF THE ID COLUMN FOR THE RECORD

EXEC sp_executesql @SqlExecute , N'@RecordXML xml out',@RecordXML OUT

SELECT TOP(1) @PreviousXML = RecordXML FROM CfgAuditLog(nolock) AUL WHERE SourceKey= @pSourceKey AND SourceType = @pSourceType ORDER BY AuditKey DESC

SELECT @CreateLog = CASE WHEN CAST(@RecordXML AS NVARCHAR(MAX)) = CAST(@PreviousXML AS NVARCHAR(MAX)) THEN 0 ELSE 1 END

IF @CreateLog = 1

BEGIN

  SELECT @lAuditKey = NEWID()

  EXEC GetNextSiteNumber @pSiteId,'Table.AuditLog', @lAuditKey OUT, 0, @lPOSSiteId

  INSERT INTO dbo.CfgAuditLog

  (

   AuditKey, RecordId, SourceType, SourceKey, OperationType, CreatedBy, Created, RecordXML, SiteId

  )

  VALUES

  (

  @lAuditKey, @RecordId, @pSourceType, @pSourceKey, @pOperationType, @pUserKey, dbo.GetCompanyDateTime(), @RecordXML, @lSiteId

  )

  IF @lStoreCount > 0

  BEGIN

    SELECT @lNewKey = NEWID()

    --EXEC GetNextSiteNumber @pSiteId, 'Table.ReplicationTransaction', @lNewKey OUTPUT, 0, @lPOSSiteId

    INSERT INTO RepReplicationTransaction

    (ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType, Flag)

    VALUES(@lNewKey, 375, @lAuditKey, @pBatchKey, 0,'FALSE')

  END

  END

END

END TRY

BEGIN CATCH

--EXECUTE usp_RethrowError

return 0

END CATCH

END