Knowledge Base

This Visual Basic script connects to the PaperCut external database, lists payment gateway transactions, and emails them. It could be used for reconciliation purposes, for example.

Script contributed by Justin from SUNY College at Oneonta.

Dim strCon, strBeginDate, strEndDate, strSearchBeginDate, strSearchEndDate, strReportFileName, strReportPath
Dim strEmailFrom, strEmailTo, strEmailServer, strEmailSubject, strEmailBody
Dim oCon, oRs

strCon = "Data Source=PaperCutDB;"

Set oCon = WScript.CreateObject("ADODB.Connection")
Set oRs = WScript.CreateObject("ADODB.Recordset")

strEndDate = Date
strBeginDate = DateAdd("d", -7, strEndDate)

strEmailBody = "PaperCut Report for CBORD Transactions between " _
        & WeekDayName(WeekDay(strBeginDate)) & " " & strBeginDate  & " 02:31:00 and " _
        & WeekDayName(WeekDay(strEndDate)) & " " & strEndDate & " 02:30:00." & vbCRLF

strReportFileName = Year(strBeginDate) & "-" & Month(strBeginDate) & "-" & Day(strBeginDate) & " To " & Year(strEndDate) & "-" & Month(strEndDate) & "-" & Day(strEndDate) & ".csv"
strReportPath = "C:\\webprintsoftware\\Reports\\" & strReportFileName

strSearchBeginDate = Year(strBeginDate) & "-" & Month(strBeginDate) & "-" & Day(strBeginDate) & " 02:31:00"
strSearchEndDate = Year(strEndDate) & "-" & Month(strEndDate) & "-" & Day(strEndDate) & " 02:30:00"

strEmailFrom = "user@domain.edu"
strEmailTo = " user@domain.edu "
strEmailServer = "server.domain.edu"
strEmailSubject = "PaperCut Report for week ending " & strEndDate

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateTextFile strReportPath
Set f1 = fso.OpenTextFile(strReportPath, 8, True, False)

oCon.Open strCon
Set oRs = oCon.Execute("select u.user_name, t.transaction_date, t.amount " _
        & " from " _
        & "   tbl_account_transaction t " _
        & "   inner join tbl_account a "_
        & "          on t.account_id = a.account_id " _
        & "   inner join tbl_user_account ua " _
        & "          on a.account_id = ua.account_id " _
        & "   inner join tbl_user u " _
        & "          on u.user_id = ua.user_id " _
        & " where " _
        & "    t.transaction_date > '" & strSearchBeginDate & "'  and " _
        & "    t.transaction_date < '" & strSearchEndDate & "'  and " _
        & "    t.transaction_type = 'PAYMENT_GATEWAY';")

'Report Title
f1.WriteLine "Username,Transaction Date,Amount"
strEmailBody = strEmailBody & "Username,Transaction Date,Amount" & vbCRLF

If oRs.EOF Then
    'f1.WriteLine "No transactions found"
    strEmailBody = strEmailBody & "No transactions found" & vbCRLF
Else
    While Not oRs.EOF
        f1.WriteLine oRs.Fields(0).Value & "," & oRs.Fields(1).Value & "," & oRs.Fields(2).Value
        strEmailBody = strEmailBody & oRs.Fields(0).Value & "," & oRs.Fields(1).Value & "," & oRs.Fields(2).Value & vbCRLF
        oRs.MoveNext
    Wend
End If

oCon.Close
f1.Close

'Send Email

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = strEmailSubject
objMessage.From = strEmailFrom
objMessage.To = strEmailTo
objMessage.TextBody = strEmailBody
objMessage.AddAttachment strReportPath

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strEmailServer

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==

objMessage.Send

Set f1 = Nothing
Set oRs = Nothing
Set oCon = Nothing

See also

• Scripts to fetch information from PaperCut

Categories: Scripting

Comments

Share your findings and experience with other PaperCut users. Feel free to add comments and suggestions about this Knowledge Base article. Please don't use this for support requests.