troubleshooting Question Xero - Creating Invoice with one code patch vs another

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Visual Basic.NETXero
3 Comments1 Solution24 ViewsLast Modified:
I manged to get a test invoice created in Xero with the first patch of code below
I now want to change the second patch of code to be one loop just like the first so that it is easier to understand

    Protected Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim myxeroapi As XeroCoreApi
        myxeroapi = TryCast(Session("myxeroapi"), XeroCoreApi)
    End Sub

    Sub oCreateInvoice2(ByVal myxeroapi As XeroCoreApi)

        Dim oEx As String

        'Dim ITracking As ItemTracking

            '======== Reader Code ==========

            'Invoice header
            Dim oInvoiceNumber As String
            Dim oContactName As String
            Dim oPayableReceivable As String
            Dim oInvoiceDate, oDueDate As String
            Dim oTaxInclusiveExclusive As String
            Dim oReference As String

            'Line items
            Dim oItemCode, oDescription, oQuantity As String
            Dim oUnitAmount, oTaxType, oDiscountRate As String
            Dim oAccountCode, oTrackingCategoryName, oTrackingCategoryOption As String

            'Create the invoice
            Dim invoice As New Invoice

            Dim pContact As New Xero.Api.Core.Model.Contact
            pContact.Name = "Test Company"

            'Create the list of lineitems to add to the invoice
            Dim LineItems As New List(Of LineItem)


                '// open the connection

                '// execute the sql statement
                'Using reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                'While reader.Read()


                oContactName = "Test Company"
                oPayableReceivable = ""
                oInvoiceDate = "25 July 2019"
                oDueDate = "25 Aug 2019"
                oTaxInclusiveExclusive = ""
                oReference = "Test Ref"

                oItemCode = "656"
                oDescription = "Test Desc"
                oQuantity = 5
                oUnitAmount = 25
                oTaxType = ""
                oDiscountRate = ""
                oAccountCode = "201"
                oTrackingCategoryName = "Shaft"
                oTrackingCategoryOption = "joel air"

                'Only gather invoice header data if there is an invoice number there
                oInvoiceNumber = 1 'Just beause we need a number
                If Len(oInvoiceNumber) > 0 Then

                    '...and fill in it's properties
                    With invoice

                        'Might want to leave out invoice number so it autogenerates
                        '.Number = oInvoiceNumber

                        .Contact = pContact
                        .Status = Xero.Api.Core.Model.Status.InvoiceStatus.Draft '**IMPORTANT

                        If oPayableReceivable = "Payable" Then
                            .Type = Types.InvoiceType.AccountsPayable
                            .Type = Types.InvoiceType.AccountsReceivable
                        End If

                        If IsDate(oInvoiceDate) = True Then
                            .Date = oInvoiceDate
                            .Date = Now
                        End If

                        If IsDate(oDueDate) = True Then
                            .DueDate = oDueDate
                            If IsDate(oInvoiceDate) = True Then
                                .DueDate = CDate(oInvoiceDate).AddMonths(1)
                                .DueDate = Now.AddMonths(1)
                            End If
                        End If

                        'Following is tax inclusive
                        '(1) Inclusive, (2) Exclusive, (3) NoTax
                        '** Invoices will be TaxExclusive by default if LineAmountTypes isn’t specified
                        If oTaxInclusiveExclusive = "Exclusive" Then
                            .LineAmountTypes = Xero.Api.Core.Model.Types.LineAmountType.Exclusive
                        ElseIf oTaxInclusiveExclusive = "No Tax" Then
                            .LineAmountTypes = Xero.Api.Core.Model.Types.LineAmountType.NoTax
                            .LineAmountTypes = Xero.Api.Core.Model.Types.LineAmountType.Inclusive
                        End If

                        If Len(oReference) > 0 Then
                            .Reference = oReference
                        End If

                        .Reference = "Test Ref"
                        '*** ----- Optional -------
                        '.ExpectedPaymentDate = Now.AddDays(90) 'Optional
                        '.CurrencyCode = "USD"
                        '.BrandingThemeId = New Guid("4c82c365-35cb-467f-bb11-dce1f2f2f67c")
                        '.TotalTax = "10.89"
                        '.SubTotal = "87.11"
                        '.Total = "98.0"

                    End With

                End If

                'This section should be repeated for multiple items...
                'in this example we just add another lineitem for simplicity
                Dim pLineItem As New LineItem
                With pLineItem
                    .ItemCode = oItemCode 'eg "0026" 'Must be a valid code
                    .Description = oDescription 'eg "AX DRILLING 2 - 100 METERS"
                    .Quantity = oQuantity 'eg 20
                    .UnitAmount = oUnitAmount 'eg "1234.56"
                    If oTaxType = "" Then 'Won't specify most of the time
                        'If TaxType Then isn't specified then Xero will use the default
                        'tax Rate on the Chart of Accounts account that the line item Is coded to 
                        '(All other defaults such as from Contacts or Inventory Items are disregarded.)
                        .TaxType = oTaxType 'eg "OUTPUT2"
                    End If
                    If IsNumeric(oDiscountRate) = True Then
                        .DiscountRate = oDiscountRate 'eg "16"
                    End If

                    .AccountCode = oAccountCode 'eg "201"
                    Dim itc As New ItemTrackingCategory
                    '** Note do not need the item tracking category id
                    'itc.Id = New Guid("7a434b76-7927-4c56-b0ca-ee1afe6d4c84")
                    itc.Name = oTrackingCategoryName 'eg "Shaft"
                    itc.Option = oTrackingCategoryOption
                    .Tracking = New ItemTracking
                End With


                invoice.LineItems = LineItems

                'End While

                ' End Using

            Catch ex As Exception

                Response.Write(ex.Message & " x44")


            End Try

            'and submit it to the Xero API

            Dim oMsg As String = "Invoice created successfully"
            ClientScript.RegisterClientScriptBlock([GetType](), "alert", "alert('" & oMsg & "');", True)

        Catch ex As Exception
            oEx = ex.Message
            Response.Write("Error. Invoice not created " & oEx)
        End Try

    End Sub

Open in new window

 Private Async Sub BtnCreateInvoice_Click(sender As Object, e As RibbonControlEventArgs) Handles btnCreateInvoice.Click

        Dim helper = ExcelHelper.GetInstance
        Dim data = ExcelHelper.GetData(Of ExcelInvoice)
        Dim result = Await ExcelHelper.CreateInvoices(data, _tracking1, _tracking2)
        Dim xeroResult = Await _xero.SetInvoice(cbCompanies.SelectedItem.Tag, result)
 End Sub

Imports System.ComponentModel.DataAnnotations
Imports System.Threading.Tasks
Imports Microsoft.Office.Interop.Excel
Imports Xero.NetStandard.OAuth2.Model.Accounting

Public Class ExcelHelper
    Private Shared _excelHelper As ExcelHelper

    Public Shared Function GetInstance() As ExcelHelper

        If _excelHelper Is Nothing Then
            _excelHelper = New ExcelHelper()
        End If
        Return _excelHelper
    End Function

    Public Shared Sub PopulateSheet(ByRef data As IEnumerable(Of IValidatableObject))
        Dim ws As Worksheet = Globals.ThisAddIn.Application.ActiveSheet
        Dim col = 1
        Dim row = 1
        Dim properties = data.GetType.GetGenericArguments()(0).GetProperties.ToList
                               ws.Cells(row, col) = prop.Name
                               col += 1
                           End Sub)
        row += 1
        col = 1
                                                       Dim value = prop.GetValue(account)
                                                       If value IsNot Nothing Then
                                                           ws.Cells(row, col) = value.ToString
                                                       End If
                                                       col += 1
                                                   End Sub)
                                row += 1
                                col = 1
                            End Sub)
    End Sub

    Public Shared Function GetData(Of T As New)() As IEnumerable(Of T)
        Dim range As Range = Globals.ThisAddIn.Application.Selection
        Dim rows = range.Rows.Count
        Dim cols = range.Columns.Count
        Dim data = New List(Of T)
        For rowIndex = 2 To rows
            Dim item = New T
            For colIndex = 1 To cols
                Dim cellValue = range(rowIndex, colIndex).Value
                Dim colName = range(1, colIndex).Value
                Dim prop = item.GetType.GetProperty(colName.ToString)
                If (cellValue IsNot Nothing) Then
                    Select Case prop.PropertyType
                        Case GetType(String)
                            prop.SetValue(item, cellValue.ToString)
                        Case GetType(Int32)
                            prop.SetValue(item, Int32.Parse(cellValue))
                        Case GetType(Decimal)
                            prop.SetValue(item, Decimal.Parse(cellValue))
                    End Select
                End If
        Return data
    End Function

    Public Shared Function CreateInvoices(invoices As List(Of ExcelInvoice), tracking1 As String, tracking2 As String) _
        As Task(Of Invoices)
        Dim xeroInvoices = New Invoices With {._Invoices = New List(Of Invoice)}
        Dim xeroInvoice As Invoice
                             Return invoice.Number
                         End Function).ToList.ForEach(Sub(einv)
                                                          If xeroInvoice Is Nothing Then
                                                              xeroInvoice = New Invoice _
                                                                                      With {.InvoiceNumber = einv.Number, .[Date] = DateTime.UtcNow,
                                                                                      .DueDate = DateTime.UtcNow,
                                                                                      .Reference = einv.Reference,
                                                                                      .LineAmountTypes = IIf(einv.TaxCode.Equals("NONE"), LineAmountTypes.NoTax, LineAmountTypes.Inclusive),
                                                                                      .Contact =
                                                                                      New Contact() With {.Name = einv.Contact, .AccountNumber = einv.Contact},
                                                                                      .Type = Invoice.TypeEnum.ACCREC,
                                                                                      .Status = Invoice.StatusEnum.DRAFT,
                                                                                      .LineItems = New List(Of LineItem)}

                                                          ElseIf Not xeroInvoice.InvoiceNumber.Equals(einv.Number) Then
                                                              xeroInvoice = New Invoice _
                                                         With {.InvoiceNumber = einv.Number, .[Date] = DateTime.UtcNow,
                                                         .DueDate = DateTime.UtcNow,
                                                         .Reference = einv.Reference,
                                                         .Status = Invoice.StatusEnum.DRAFT,
                                                         .Type = Invoice.TypeEnum.ACCREC,
                                                         .LineAmountTypes = IIf(einv.TaxCode.Equals("NONE"), LineAmountTypes.NoTax, LineAmountTypes.Inclusive),
                                                         .Contact =
                                                         New Contact() With {.Name = einv.Contact, .AccountNumber = einv.Contact},
                                                         .LineItems = New List(Of LineItem)}
                                                          End If
                                                          Dim item = New LineItem() With {.Description = einv.LineDescription, .LineAmount = einv.LineAmount,
                                     .Quantity = einv.LineQuantity, .TaxType = einv.TaxCode, .Tracking = New List(Of LineItemTracking)}
                                                          item.Tracking.Add(New LineItemTracking() With {.Name = tracking1, .[Option] = einv.Tracking1})
                                                          item.Tracking.Add(New LineItemTracking() With {.Name = tracking2, .[Option] = einv.Tracking2})
                                                      End Sub)
        If (xeroInvoice IsNot Nothing) Then
        End If
        Return Task.FromResult(xeroInvoices)
    End Function
End Class

Open in new window

Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 3 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 3 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004