Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

VB.net Xero - Creating Invoice with one code patch vs another

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 https://developer.xero.com/documentation/api-guides/tax-in-xero
                        '(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

Visual Basic.NETXero

Avatar of undefined
Last Comment
Murray Brown
Avatar of ste5an
Flag of Germany image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image


Maybe I should rephrase part of this. I don't understand the first piece of code. Is there a way to simplify it so that it is a little easier to read

I realised that I had missed quite a bit out so am going to change the code in the original question

Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image


Thanks I saw that I can only accept one answer. Thanks for the input Rob
Visual Basic.NET
Visual Basic.NET

Visual Basic .NET (VB.NET) is an object-oriented programming language implemented on the .NET framework, but also supported on other platforms such as Mono and Silverlight. Microsoft launched VB.NET as the successor to the Visual Basic language. Though it is similar in syntax to Visual Basic pre-2002, it is not the same technology,

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo