Retrieving invoices from the Exact Online API

21/09/2022 dvdheiden

The Exact Online API has a lot of endpoints and options, which sometimes makes it a challenge to work with.

Luckily for the PHP developers among us, the people at Picqer created a package that takes away a lot of work for you. Still, there are some things you need to know when working with invoices.

The package can be found on GitHub and can be installed in (almost) every PHP project. The installation guide on GitHub is pretty clear, so check that out if you want to install and set up the package.

Make sure you have a working connection available before continuing.

Invoices

There are several types of invoices. In the examples, I will focus on the sales invoices.

Retrieving invoices

To efficiently retrieve invoices, it’s best to limit those. For example, by the creation date:

$invoices = (new SalesInvoice($connection))
    ->filter(sprintf(
        'Created gt datetime\\'%s\\'',
        $since->format('Y-m-d')
    ));

This will return an array of SalesInvoice objects:

foreach ($invoices as $invoice) {
    /** @var SalesInvoice $invoice **/
    $invoiceId = $invoice->InvoiceID; // ID in Exact
    $invoiceNumber = $invoice->InvoiceNumber;
    $debtorId = $invoice->OrderedBy; // ID of debtor in Exact
    ..
}

See:

Retrieving invoice lines

The SalesInvoice object doesn’t contain the invoice lines. You need to retrieve that from a separate endpoint. These can be easily retrieved with the InvoiceID:

$invoiceLines = (new SalesInvoiceLine($connection))
    ->filter(sprintf(
        'InvoiceID eq guid\\'%s\\'',
        $invoiceId
));

This will return an array of SalesInvoiceLine objects:

foreach ($invoiceLines as $invoiceLine) {
    /** @var SalesInvoiceLine $invoiceLine **/
    $invoiceLineId = $invoiceLine->ID; // ID in Exact
    $productId = $invoiceLine->Item; // ID of the product in Exact
    $description = $invoiceLine->Description;
    $quantity = $invoiceLine->Quantity;
    $amountWithoutVat = $invoiceLine->AmountFC * 100;
    $amountVat = $invoiceLine->VATAmountFC * 100;
    $vatPercentage = $invoiceLine->VATPercentage * 100;
    $amountWithVat = $invoiceLine->NetPrice * 100;
    $sortOrder = $invoiceLine->LineNumber;
    ..
}

The amounts are multiplied by 100 to be able to store those as an integer instead of floats, but feel free to process those however you like. It’s advised to use those together with Money objects.

See:

Retrieving documents

Invoices have 0 or more documents. These documents are NOT actual files, those are stored in attachments which we have to retrieve later on. First start with retrieving the documents for an invoice:

$documents = (new Document($this->getConnection()))
    ->filter(sprintf(
        '(Type eq 10 or Type eq 13) and SalesInvoiceNumber eq %s', // 10 = SalesInvoice, 13 = Subscription
        $invoiceNumber
    ));

This will return an array of Document objects:

foreach ($documents as $document) {
    /** @var Document $document **/
    $exactDocumentId = $exactDocument->ID; // ID of the document in Exact
    ..
}

See:

The Types might be documented somewhere (haven’t found it though), but I usually just figure them out in the interface (open document → inspect the Type field) or retrieve them from the API.

Retrieving attachments

So now we know which documents are related to the invoice, we could retrieve the attachments. The attachments have the URL to the actual files. A document might have multiple attachments.

The attachments can be retrieved by the document ID.

$attachments = (new DocumentAttachment($connection))
    ->filter(sprintf(
        'Document eq guid\\'%s\\'',
        $documentId
    ));

This will return an array of DocumentAttachment objects:

foreach ($attachments as $attachment) {
    /** @var DocumentAttachment $attachment **/
    $attachmentId = $attachment->ID; // ID of the attachment in Exact
    $fileSize = $attachment->FileSize;
    $fileName = $attachment->FileName;
    ..
}

See:

Downloading the attachment

The last step is to download the attachment in its original format. In this example, I’m using Laravel, so you might want to adjust this to your needs.

// Download the file with the token from the connection
$documentResponse = Http::withToken($connection->getAccessToken())
    ->get($attachment->getDownloadUrl());
if ($documentResponse->successful()) {
    // Save the file on the disk
    $invoiceStored = Storage::put('invoices/' . $fileName, $documentResponse->body());
}

Summary

We’ve taken the following steps to completely retrieve the invoice and its data:

  1. Retrieve invoices recently created.
  2. Retrieve invoice lines for the InvoiceID.
  3. Retrieve documents for the InvoiceNumber.
  4. Retrieve attachments for the DocumentID.
  5. Download the attachment.

Hopefully, this will help you out with downloading the invoice.