USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertLogWeb]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    Tim Hsu
-- Description:  Insert Logon/Retrieve Log.
-- Ref        : http://devwithdave.com/SQLServer/stored-procedures/SQLServer-StoredProcedures--HOW-TO-WRITE-TO-A-TEXT-FILE.asp
-- =============================================

IF OBJECT_ID('[dbo].[sp_InsertLogWeb]') IS not NULL
    DROP PROCEDURE [dbo].[sp_InsertLogWeb]
GO

CREATE PROCEDURE [dbo].[sp_InsertLogWeb] 
  @LogDateTime DATETIME,
  @UserId VARCHAR(50),
  @UserGroup VARCHAR(20),
  @Source VARCHAR(10),
  @LogType CHAR(1),
  @Result CHAR(1),
  @Bank VARCHAR(3),
  @Category CHAR(1),
  @AcctNo VARCHAR(30),
  @WorkstationId VARCHAR(50),
  @Remarks VARCHAR(200)
AS
BEGIN

DECLARE @OLE            INT        -- Object link/embed interface
DECLARE @FileID         INT        -- File descriptor Id
DECLARE @Path      VARCHAR(2048)  -- path to log file
DECLARE @Txt      VARCHAR(2048)  -- concatenated log string
DECLARE @RW_Status    VARCHAR(128)  -- DB read/write status

SET NOCOUNT ON;

SELECT @RW_Status = (
  CONVERT(VARCHAR, DATABASEPROPERTYEX(DB_Name(), 'Updateability'))
  )

IF @RW_Status = 'READ_WRITE'
BEGIN

  -- Insert log into Audit table
  INSERT INTO SDSAuditLog(LogDateTime,
              UserId,
              UserGroup,
              Source,
              LogType,
              Result,
              Bank,
              Category,
              AcctNo,
              WorkstationId,
              Remarks)
          VALUES (
              @LogDateTime,
              UPPER(@UserId),
              UPPER(@UserGroup),
              @Source,
              @LogType,
              @Result,
              @Bank,
              @Category,
              @AcctNo,
              UPPER(@WorkstationId),
              @Remarks)

END
ELSE
BEGIN
  -- create / write / append to daily log
  SET @Path = 'C:\Comp\Logs\SDSAuditLog_' + 
      CONVERT(VARCHAR, GETDATE(), 112) + '.log'

  -- create concatenated log string
  SET @Txt = CONVERT(VARCHAR, GETDATE(), 121) + ',' 
        + ISNULL(@UserId, '')      + ','
        + ISNULL(@UserGroup, '')    + ','
        + ISNULL(@Source, '')      + ','
        + ISNULL(@LogType, '')      + ','
        + ISNULL(@Result, '')      + ','
        + ISNULL(@Bank, '')        + ','
        + ISNULL(@Category, '')      + ','
        + RTRIM(ISNULL(@AcctNo, ''))  + ','
        + ISNULL(@WorkstationId, '')  + ','
        + RTRIM(ISNULL(@Remarks, ''))

  BEGIN TRY

    -- open file I/O, write, then close file
    EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT  
    EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @Path, 8, 1  
    EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Txt 
    EXECUTE sp_OADestroy @FileID  
    EXECUTE sp_OADestroy @OLE  

  END TRY

  BEGIN CATCH
    
    -- open/write file I/O error
    PRINT N'Error writing to file.'

  END CATCH;

END
END