vb.NET Code to print unprinted invoices to a PDF and save them in a given directory

[planetbluau]

Recently I wanted to make an automation application that would run by scheduled task to regularly print unprinted invoices. I wanted more control than just a VBA Macro so wrote it in vb.NET with the ACCPAC.ADVANTAGE DLL reference.

There is more code around this project, like marking the invoice as now PRINTED. But for what it is worth, here is my end result that is working with Sage 300 2012. Enjoy. (The idea can be modified for other documents too.)

(The following code is given as is and without warranty! - Use at your own risk.)

Imports ACCPAC.Advantage

Module sage
    Public Sub PrintUnprintedInvoices(iINVUNIQ As Integer)
        'Reference to Accpac.Advantage.dll required
        'To look for unprinted invoices and print them.
        Dim rpt As Report ' declare accpac report
        Dim mSession As New ACCPAC.Advantage.Session 'declare a session
        mSession.Init("", "XY", "XY1000", "61A") 'init the session
        mSession.Open("ADMIN", "ADMIN", sSQLDBName, DateTime.Today, 0) 'open the session with appropriate credentials
        Dim rptPrintSetup As PrintSetup 'declare a PrintSetup

        'Need to supply a connection string
        Dim sqlConn As New SqlClient.SqlConnection(gsConnectionString) 'declare a connection
        Try
            Using sqlConn
                'Get list of unprinted invoice numbers (later I will add code to mark the invoices as PRINTED using the INVUNIQ id.) 
                Dim sSQL As String = _
                    "Select INVUNIQ, INVNUMBER FROM " & sSQLDBName & ".dbo.OEINVH WHERE INVPRINTED = 0 and INVUNIQ > " & iINVUNIQ & " ORDER BY INVUNIQ;"
                Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(sSQL, sqlConn)
                sqlConn.Open()
                Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()
                If reader.HasRows Then
                    Do While reader.Read()
                        iINVUNIQ = reader.Item("INVUNIQ")
                        sInvoiceNO = reader.Item("INVNUMBER").ToString.Trim
                        rpt = mSession.ReportSelect("OEINV01[OEINV01.RPT]", "      ", "      ")
                        rptPrintSetup = mSession.GetPrintSetup("      ", "      ")
                        rptPrintSetup.DeviceName = "HP LaserJet Professional P1102w"
                        rptPrintSetup.OutputName = "IP_192.168.12.102"
                        rptPrintSetup.Orientation = 1
                        rptPrintSetup.PaperSize = 9
                        rptPrintSetup.PaperSource = 7
                        rpt.PrinterSetup(rptPrintSetup)
                        rpt.SetParam("PRINTED", "1")              ' Report parameter: 4
                        rpt.SetParam("DELMETHOD", "1")            ' Report parameter: 6
                        rpt.SetParam("ECENABLED", "0")            ' Report parameter: 7
                        rpt.SetParam("DIRECTEC", "0")             ' Report parameter: 8
                        rpt.SetParam("QTYDEC", "0")               ' Report parameter: 5 - O/E Sales History:Detail,Sort by Item Number
                        rpt.SetParam("BOITEM", "1")               ' Report parameter: 9
                        rpt.SetParam("SORTFROM", sInvoiceNO)      ' Report parameter: 2 - limit by invoice
                        rpt.SetParam("SORTTO", sInvoiceNO)        ' Report parameter: 3 - limit by invoice
                        rpt.SetParam("SWDELMETHOD", "3")          ' Report parameter: 10
                        rpt.SetParam("SERIALLOTNUMBERS", "0")     ' Report parameter: 14
                        rpt.SetParam("PRINTKIT", "0")             ' Report parameter: 11
                        rpt.SetParam("PRINTBOM", "0")             ' Report parameter: 12
                        rpt.SetParam("RETAINAGE", "0")            ' Report parameter: 13
                        'set a selection criteria to print one PDF per invoice
                        rpt.SetParam("@SELECTION_CRITERIA", "(({OEINVH.INVNUMBER} >= " & sInvoiceNO & ") AND ({OEINVH.INVNUMBER} <= " & sInvoiceNO & ")) AND ({OEINVH.INVPRINTED} = 0)")   ' Report parameter: 0
                        rpt.NumberOfCopies = 1
                        rpt.Destination = PrintDestination.File
                        rpt.Format = PrintFormat.PDF
                        'give the PDF a unique name
                        rpt.PrintDirectory = "C:\users\rodney\desktop\" & sInvoiceNO & ".pdf"
                        rpt.Print()
                    Loop 'to pick up next invoice and print it

                End If
                reader.Close()
            End Using
            sqlConn.Close()
            sqlConn.Dispose()
        Catch ex As Exception
            CreateLog("Error trying to Get Invoice List") 'my own error recording routine
            sqlConn.Close()
            sqlConn.Dispose()
        End Try



    End Sub

End Module

 

[Jay Converse]

Use the CS0120 view, then no SQL connection required.

 

[DjangMan]

Thanks for the code - always appreciated!

And OE0270 is the proper way to flag an invoice (or other OE document) as having been printed. Not strictly necessary, but proper.