Jump to content

VATMOSS Return: New Report Now Available


Recommended Posts

Hi Marcus,


if you mean the PDF versions of the invoices, you can use the PDF Batch Report.




This report can be used to generate a single PDF document containing multiple individual invoices (one per page). Can be filtered by date range, payment methods and status. Useful for printing invoices for record-keeping or mailing to clients.


or if you just want a list of invoices (with clients, totals etc), there is an Invoices Report.




Generate a custom export of invoice information by applying up to 5 filters. CSV Export is available via the download link at the bottom of the page.
Link to comment
Share on other sites



The report is provided unencoded so you can review the query used to calculate the reported information and adjust it to fit your use case.


I would also note that the report only counts invoices which have been paid and shows on a per-currency basis. So if you have clients in one country that paid with two different currencies, you need to consider both of them.


Hope those two points of clarity help. Have a great day,


Nate C

Link to comment
Share on other sites

I am not convinced the report is completely accurate. 2 issues I have spotted are for example minor discrepency between "Total VAT Collected" and the inc VAT total calculated against "Total Value Invoiced (Excl. VAT)". Also I did a spot check on Portugal - PT total invoices raised for the period and the report says 7 but I have manually identified 13 invoices paid by Portugal based clients during the period. Either the report is not presenting accurate results or there is an issue with the data in the system?

Link to comment
Share on other sites

The numbers / calculations are done via a database query in the source code of the report:


$query = "SELECT tblclients.country, COUNT(tblinvoices.id) as invoicecount, "

. "SUM(tblinvoices.subtotal) as totalinvoiced, "

. "SUM(tblinvoices.tax + tblinvoices.tax2) as totalvat "

. "FROM tblinvoices "

. "INNER JOIN tblclients ON tblclients.id = tblinvoices.userid "

. "WHERE (tblinvoices.tax > 0 OR tblinvoices.tax2 > 0) "

. "AND tblclients.country IN (" . db_build_in_array($euCountries) . ") "

. "AND datepaid >= '" . date("Y-m-d", $queryStartDate) . "' "

. "AND datepaid <= '" . date("Y-m-d", $queryEndDate) . " 23:59:59' "

. "AND tblinvoices.status = 'Paid' "

. "AND currency = " . (int) $currencyid . " "

. "AND (SELECT count(tblinvoiceitems.id) "

. "FROM tblinvoiceitems "

. "WHERE invoiceid = tblinvoices.id "

. "AND (type = 'AddFunds' OR type = 'Invoice') "

. ") = 0 "

. "GROUP BY tblclients.country "

. "ORDER BY tblclients.country ASC";


Refunding an invoice sets the status of the invoice to "Refunded" the query only selects invoices that have a status of "Paid". If you have concerns about your specific installation please open a support ticket and our support team will take a look at things.


Have a great day,


Nate C

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated