VATMOSS Return: New Report Now Available

[Marcus]

Hi there,

i've manually worked out the VAT for diff. countrys and get different summarys.

To figure out where the error is - How can i print every single invoice with the report tool?

 

regards

marcus

[brian!]

Hi Marcus,

 

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

 

http://docs.whmcs.com/Reports#PDF_Batch

 

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.

 

http://docs.whmcs.com/Reports#Invoices

 

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.

[WHMCS Nate]

Marcus,

 

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

[nimonogi]

Does the VATMOSS report handle refunds?

[UH-Simon]

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?

[WHMCS Nate]

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