1<?php
  2/**
  3 * VAT MOSS Settlement Data Report
  4 *
  5 * This report is designed to provide the information necessary to be
  6 * able to complete a VAT MOSS return.
  7 *
  8 * @package    WHMCS
  9 * @author     WHMCS Limited <development@whmcs.com>
 10 * @copyright  Copyright (c) WHMCS Limited 2005-2019
 11 * @license    https://www.whmcs.com/license/ WHMCS Eula
 12 * @version    $Id$
 13 * @link       https://www.whmcs.com/
 14 */
 15
 16use WHMCS\Billing\Tax\Vat;
 17use WHMCS\Database\Capsule;
 18use WHMCS\Utility\Country;
 19
 20if (!defined("WHMCS")) {
 21    die("This file cannot be accessed directly");
 22}
 23
 24// Define report parameters.
 25$reportdata['title'] = "VAT MOSS Settlement Data";
 26$reportdata['description'] = "This report provides the information needed to complete a VATMOSS return. "
 27    . "Please check with your tax authority to confirm how you can upload your settlement data into the MOSS portal. "
 28    . "You should also contact your MOSS registration country if you have any further questions in relation to the MOSS return.";
 29
 30// Fetch input parameters.
 31$reportQuarter = $whmcs->get_req_var('reportquarter');
 32
 33// List of EU Countries.
 34$euCountries = array_keys(Vat::EU_COUNTRIES);
 35
 36// Quarter definitions.
 37$periodLabels = array(
 38    1 => 'January - March',
 39    2 => 'April - June',
 40    3 => 'July - September',
 41    4 => 'October - December',
 42);
 43
 44// Initialise variables.
 45$queryStartDate = '';
 46$queryEndDate = '';
 47$selectHtml = '';
 48$currencyCode = (isset($currency['code'])) ? $currency['code'] : '';
 49
 50// Build dropdown of quarters.
 51$periods = array();
 52for ($i = 2015; $i <= date("Y"); $i++) {
 53    $quartersToShow = ($i < date("Y")) ? 4 : ceil(date("m") / 3);
 54    for ($a = 1; $a <= $quartersToShow; $a++) {
 55        $periodLabel = $i . ' Q' . $a . ' - ' . $periodLabels[$a];
 56        $selectHtml .= '<option'
 57            . ($periodLabel == $reportQuarter ? ' selected' : '')
 58            . '>' . $periodLabel . '</option>';
 59        if ($periodLabel == $reportQuarter) {
 60            $queryStartDate = mktime(0, 0, 0, (($a-1)*3)+1, 1, $i);
 61            $queryEndDate = mktime(0, 0, 0, ($a*3)+1, 0, $i);
 62        }
 63    }
 64}
 65
 66// Form to select quarter.
 67$reportdata['description'] .= '<br /><br /><form method="post" action="?report=' . $report . '">
 68    <div align="center">
 69        Select Quarter:
 70        <select name="reportquarter" class="form-control select-inline">
 71            ' . $selectHtml . '
 72        </select>
 73        <input type="submit" value="Generate Report" class="btn btn-primary" />
 74    </div>
 75</form>
 76';
 77
 78if (!$reportQuarter) {
 79    $reportdata['headertext'] .= '<p align="center">Currency selection will become available on report generation.</p>';
 80}
 81// Generate report if period is selected.
 82if ($queryStartDate && $queryEndDate) {
 83
 84    $reportdata['currencyselections'] = true;
 85
 86    // Define table headings.
 87    $reportdata['tableheadings'] = array(
 88        'Country Name',
 89        'Country Code',
 90        'VAT Rate',
 91        'Number of Invoices',
 92        'Total Value Invoiced (Excl. VAT)',
 93        'Total VAT Collected',
 94        'Currency',
 95    );
 96
 97    // Output reporting period.
 98    $reportdata['headertext'] .= '<h2 style="margin:0;">For Period '
 99        . date("jS F Y", $queryStartDate)
100        . ' to '
101        . date("jS F Y", $queryEndDate)
102        . '</h2>';
103
104    // Fetch country names.
105    $countries = new Country();
106    $countries = $countries->getCountryNameArray();
107
108    // Fetch all configured country based tax rates.
109    $taxRates = Capsule::table('tbltax')
110        ->where('state', '')
111        ->where('country', '!=', '')
112        ->pluck('taxrate', 'country')
113        ->all();
114
115    // Build query to calculate data for report.
116    $results = Capsule::table('tblinvoices')
117        ->select(
118            'tblclients.country',
119            'tblinvoicedata.country as invoice_country',
120            Capsule::raw('count(tblinvoices.id) as `invoicecount`'),
121            Capsule::raw('sum(tblinvoices.subtotal) as `totalinvoiced`'),
122            Capsule::raw('sum(tblinvoices.tax + tblinvoices.tax2) as `totalvat`')
123        )
124        ->distinct()
125        ->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
126        ->leftJoin('tblinvoicedata', 'tblinvoices.id', '=', 'tblinvoicedata.invoice_id')
127        ->leftJoin('tblinvoiceitems', function ($join) {
128            $join->on('tblinvoiceitems.invoiceid', '=', 'tblinvoices.id');
129            $join->on(function ($join) {
130                $join
131                    ->on('tblinvoiceitems.type', '=', Capsule::raw('"Add Funds"'))
132                    ->orOn('tblinvoiceitems.type', '=', Capsule::raw('"Invoice"'));
133            });
134        })
135        ->where(function ($query) {
136            $query->where('tblinvoices.tax', '>', '0')
137                ->orWhere('tblinvoices.tax2', '>', '0');
138        })
139        ->where(function ($query) use ($euCountries) {
140            $query->where(function ($query) use ($euCountries) {
141                $query->whereNotNull('tblinvoicedata.country')
142                    ->whereIn('tblinvoicedata.country', $euCountries);
143            })
144                ->orWhere(function ($query) use ($euCountries) {
145                    $query->whereNull('tblinvoicedata.country')
146                        ->whereIn('tblclients.country', $euCountries);
147                });
148        })
149        ->whereBetween('datepaid', [
150            date("Y-m-d", $queryStartDate),
151            date("Y-m-d", $queryEndDate) . ' 23:59:59',
152        ])
153        ->where('tblinvoices.status', '=', 'Paid')
154        ->where('currency', '=', (int) $currencyid)
155        ->whereNull('tblinvoiceitems.id')
156        ->groupBy('tblclients.country')
157        ->orderBy('tblclients.country', 'asc')
158        ->get();
159
160    foreach ($results as $result) {
161        $countryCode = $result->country;
162        if ($result->invoice_country) {
163            $countryCode = $result->invoice_country;
164        }
165        $invoiceCount = $result->invoicecount;
166        $totalInvoiced = $result->totalinvoiced;
167        $totalVat = $result->totalvat;
168
169        if (isset($countries[$countryCode])) {
170            $countryName = $countries[$countryCode];
171        } else {
172            $countryName = 'Unrecognised Country';
173        }
174        if (isset($taxRates[$countryCode])) {
175            $taxRate = $taxRates[$countryCode] . '%';
176        } else {
177            $taxRate = 'Tax Rate Not Found';
178        }
179
180        $reportdata['tablevalues'][] = [
181            $countryName,
182            $countryCode,
183            $taxRate,
184            $invoiceCount,
185            $totalInvoiced,
186            $totalVat,
187            $currencyCode,
188        ];
189    }
190
191    $reportdata['footertext'] = "* If a country does not appear in the report, then no VAT was collected "
192        . "from customers in that country during the period selected.";
193    $reportdata['footertext'] .= "<br />Isle of Man (GB) and Monaco (FR) are listed in this report as "
194        . "EU Overseas Territories of their respective countries and should be included in any figures "
195        . "provided to tax authorities. "
196        . "<a href='http://europa.eu/youreurope/business/vat-customs/cross-border/index_en.htm' "
197        . "target='_blank'>More Information</a>";
198
199}
200