Beautiful Open-Source Form Builder
<?php
namespace App\Console\Commands\Tax;
use App\Services\Tax\StripeExportDatasetService;
use App\Services\Tax\StripeExportDatasetStore;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Laravel\Cashier\Cashier;
use Stripe\Invoice;
/**
* This command generates an XML file for DES (Déclaration Européenne de Services) reporting
* to French customs authorities. It processes Stripe invoices within a given date range
* and creates an XML file following the official DES schema.
*
* The XML file includes:
* - Company VAT number
* - Declaration period (month/year)
* - Declaration type (DES)
* - Flow type (INTRODUCTION)
* - Line items for each EU customer with:
* - Line number
* - Value
* - Partner VAT number
* - Country code
*
* Usage:
* php artisan stripe:generate-des-xml --vat=FR12345678900 --start-date=2024-01-01 --end-date=2024-01-31
*
* Options:
* --start-date : Start date in YYYY-MM-DD format (defaults to first day of previous month)
* --end-date : End date in YYYY-MM-DD format (defaults to last day of start date's month)
* --full-month : Use the full month of the start date
* --vat : Company VAT number (required, must start with FR)
*/
class GenerateDesXmlExport extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'stripe:generate-des-xml
{--start-date= : Start date (YYYY-MM-DD)}
{--end-date= : End date (YYYY-MM-DD)}
{--dataset= : Reuse a built dataset id}
{--full-month : Use the full month of the start date}
{--vat= : French VAT number (must start with FR)}';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Generate DES XML file for French customs';
private const DECLARATION_TYPE = 'DES';
private const FLUX_TYPE = 'INTRODUCTION';
public function handle(StripeExportDatasetService $collector, StripeExportDatasetStore $store)
{
// Validate required options
$vatNumber = $this->option('vat');
if (!$vatNumber) {
$this->error('VAT number is required');
return Command::FAILURE;
}
// Validate VAT number format
if (!preg_match('/^FR[0-9A-Z]{11}$/', $vatNumber)) {
$this->error('Invalid French VAT number format. Must start with FR followed by 11 characters.');
return Command::FAILURE;
}
// Get dates
$startDate = $this->option('start-date');
$endDate = $this->option('end-date');
// If no start date, use first day of previous month
if (!$startDate) {
$startDate = Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d');
if (!$this->confirm("No start date specified. Use {$startDate}?", true)) {
return Command::FAILURE;
}
} elseif (!Carbon::createFromFormat('Y-m-d', $startDate)) {
$this->error('Invalid start date format. Use YYYY-MM-DD.');
return Command::FAILURE;
}
// If no end date, use end of the month from start date
if (!$endDate) {
$endDate = Carbon::parse($startDate)->endOfMonth()->format('Y-m-d');
$this->info("Using end date: {$endDate}");
} elseif (!Carbon::createFromFormat('Y-m-d', $endDate)) {
$this->error('Invalid end date format. Use YYYY-MM-DD.');
return Command::FAILURE;
}
$this->info('Start date: ' . $startDate);
$this->info('End date: ' . $endDate);
$datasetId = $this->option('dataset');
if ($datasetId) {
$rows = $store->loadRows($datasetId);
} else {
$rows = $collector->collect($startDate, $endDate)['rows'];
}
$invoices = array_values(array_filter(array_map(function (array $row) {
if (!($row['des_eligible'] ?? false)) {
return null;
}
return [
'country_code' => $row['des_country_code'],
'vat_number' => $row['des_vat_number'],
'amount_eur' => $row['des_amount_eur'],
'created_at' => $row['accounting_ts'] ?? $row['created_ts'],
];
}, $rows)));
// Generate XML
$xml = $this->generateXml($invoices);
// Save XML file with .xml extension
$period = Carbon::parse($startDate)->format('Ym');
$filename = "DES_{$period}.xml";
file_put_contents(storage_path("app/{$filename}"), $xml->asXML());
$this->info("XML file generated: " . storage_path("app/{$filename}"));
return Command::SUCCESS;
}
private function getInvoices($startDate, $endDate)
{
$processedInvoices = [];
$queryOptions = [
'limit' => 100,
'expand' => [
'data.customer',
'data.customer.address',
'data.customer.tax_ids',
'data.payment_intent',
'data.payment_intent.payment_method',
'data.charge.balance_transaction'
],
'status' => 'paid',
];
if ($startDate) {
$queryOptions['created']['gte'] = Carbon::parse($startDate)->startOfDay()->timestamp;
}
if ($endDate) {
$queryOptions['created']['lte'] = Carbon::createFromFormat('Y-m-d', $endDate)->endOfDay()->timestamp;
}
$this->info('Fetching invoices from Stripe...');
$invoices = Cashier::stripe()->invoices->all($queryOptions);
$totalResults = $invoices->count();
$this->info("Initial batch contains {$totalResults} invoices");
$bar = $this->output->createProgressBar();
$bar->start();
do {
if (empty($invoices->data)) {
$this->line('No invoices found in this batch.');
break;
}
foreach ($invoices as $invoice) {
// Skip cancelled or uncollectible invoices
if ($invoice->status === 'void' || $invoice->status === 'uncollectible' || $invoice->amount_remaining === $invoice->total) {
continue;
}
// Check for payment status
// Since we query with status='paid', check invoice status first
// payment_intent->status may not always be available/expanded
$invoiceStatus = $invoice->status ?? null;
$paymentIntentStatus = $invoice->payment_intent->status ?? null;
// Accept if invoice is paid OR payment_intent succeeded
if ($invoiceStatus !== 'paid' && $paymentIntentStatus !== 'succeeded') {
continue;
}
$netInvoiceAmount = $this->getNetInvoiceAmount($invoice);
if (($invoice->total ?? 0) > 0 && $netInvoiceAmount <= 0) {
continue;
}
// Only process EU B2B invoices with VAT number
if (!$this->isEligibleForDes($invoice)) {
continue;
}
try {
$processedInvoices[] = $this->formatInvoiceForDes($invoice);
$bar->advance();
} catch (\Exception $e) {
$this->warn("Skipping invoice {$invoice->id}: {$e->getMessage()}");
}
}
if (!empty($invoices->data)) {
$queryOptions['starting_after'] = end($invoices->data)->id;
} else {
break;
}
// No need for sleep - Stripe API can handle the request rate
$invoices = Cashier::stripe()->invoices->all($queryOptions);
$batchSize = count($invoices->data);
$totalResults += $batchSize;
} while ($invoices->has_more);
$bar->finish();
$this->line('');
return $processedInvoices;
}
private function isEligibleForDes(Invoice $invoice): bool
{
$country = $invoice->customer->address->country ?? null;
// Find VAT ID among tax IDs
$vatId = null;
if (!empty($invoice->customer->tax_ids->data)) {
foreach ($invoice->customer->tax_ids->data as $taxId) {
if ($taxId->type === 'eu_vat') {
$vatId = $taxId->value;
break;
}
}
}
// Only include EU B2B transactions (with VAT number)
if (!$country || $country === 'FR' || !isset(GenerateTaxExport::EU_TAX_RATES[$country]) || !$vatId) {
return false;
}
// Validate VAT number format: should start with 2 letters and contain at least one number
$vatId = $this->cleanVatNumber($vatId);
if (!preg_match('/^[A-Z]{2}[A-Z0-9]+$/', $vatId)) {
$this->warn("Invalid VAT number format for invoice {$invoice->id}: {$vatId} (country: {$country})");
return false;
}
// Also verify that the country code matches
if (substr($vatId, 0, 2) !== $country) {
$this->warn("VAT number country code doesn't match address for invoice {$invoice->id}: {$vatId} (country: {$country})");
return false;
}
return true;
}
private function formatInvoiceForDes(Invoice $invoice): array
{
$country = $invoice->customer->address->country;
// Find VAT ID
$vatId = null;
foreach ($invoice->customer->tax_ids->data as $taxId) {
if ($taxId->type === 'eu_vat') {
$vatId = $taxId->value;
break;
}
}
if (!$vatId) {
throw new \InvalidArgumentException("No EU VAT number found for invoice {$invoice->id}");
}
// Get amount in EUR
$amount = $this->getInvoiceAmountInEur($invoice);
if ($amount === null) {
throw new \RuntimeException("Could not determine EUR amount for invoice {$invoice->id}");
}
return [
'country_code' => $country,
'vat_number' => $this->cleanVatNumber($vatId),
'amount_eur' => $amount,
'created_at' => $invoice->created,
];
}
private function getInvoiceAmountInEur(Invoice $invoice): ?float
{
$netInvoiceAmount = $this->getNetInvoiceAmount($invoice);
// If the invoice is already in EUR, just convert from cents
if ($invoice->currency === 'eur') {
return $netInvoiceAmount / 100;
}
// Try to get the converted amount from the balance transaction
// Fast path: invoice has embedded charge
if (isset($invoice->charge) && isset($invoice->charge->balance_transaction)) {
$bt = $invoice->charge->balance_transaction;
// balance_transaction->amount is NET (after fees), add fees back to get GROSS
$netEur = $bt->amount ?? 0;
$feeEur = $bt->fee ?? 0;
$totalEur = $netEur + $feeEur; // GROSS = NET + fees
return $this->applyInvoiceAdjustmentsToGrossAmount($invoice, $totalEur / 100);
}
// Fallback: Stripe no longer embeds charge on invoice
// Fetch the Charge explicitly by invoice ID, then read its balance_transaction
try {
$charges = Cashier::stripe()->charges->all([
'invoice' => $invoice->id,
'limit' => 1,
'expand' => ['data.balance_transaction'],
]);
$charge = $charges->data[0] ?? null;
if ($charge && isset($charge->balance_transaction)) {
$bt = $charge->balance_transaction;
// balance_transaction->amount is NET (after fees), add fees back to get GROSS
$netEur = $bt->amount ?? 0;
$feeEur = $bt->fee ?? 0;
$totalEur = $netEur + $feeEur; // GROSS = NET + fees
return $this->applyInvoiceAdjustmentsToGrossAmount($invoice, $totalEur / 100);
}
} catch (\Exception $e) {
// Silently continue if charge retrieval fails
}
// Try to get the amount from payment intent (if expanded)
if (isset($invoice->payment_intent) && isset($invoice->payment_intent->charges) && isset($invoice->payment_intent->charges->data)) {
foreach ($invoice->payment_intent->charges->data as $charge) {
if (isset($charge->balance_transaction)) {
$bt = $charge->balance_transaction;
$netEur = $bt->amount ?? 0;
$feeEur = $bt->fee ?? 0;
$totalEur = $netEur + $feeEur;
return $this->applyInvoiceAdjustmentsToGrossAmount($invoice, $totalEur / 100);
}
}
}
// If we can't find the EUR amount, log it and return null
$this->warn("Could not find EUR amount for invoice {$invoice->id} in {$invoice->currency}");
return null;
}
private function getInvoiceRefundAmount(Invoice $invoice): int
{
$invoiceRefundAmount = (int) ($invoice->amount_refunded ?? 0);
$chargeRefundAmount = 0;
if (isset($invoice->charge)) {
$chargeRefundAmount = (int) ($invoice->charge->amount_refunded ?? 0);
if ($chargeRefundAmount === 0 && isset($invoice->charge->refunded) && $invoice->charge->refunded) {
$chargeRefundAmount = (int) ($invoice->total ?? 0);
}
}
return max($invoiceRefundAmount, $chargeRefundAmount);
}
private function getInvoiceCreditNotesAmount(Invoice $invoice): int
{
return (int) (($invoice->post_payment_credit_notes_amount ?? 0) + ($invoice->pre_payment_credit_notes_amount ?? 0));
}
private function getNetInvoiceAmount(Invoice $invoice): int
{
return (int) (($invoice->total ?? 0) - $this->getInvoiceRefundAmount($invoice) - $this->getInvoiceCreditNotesAmount($invoice));
}
private function applyInvoiceAdjustmentsToGrossAmount(Invoice $invoice, float $grossAmount): float
{
$originalAmount = (int) ($invoice->total ?? 0);
$netAmount = $this->getNetInvoiceAmount($invoice);
if ($originalAmount === 0 || $grossAmount == 0.0 || $netAmount === $originalAmount) {
return $grossAmount;
}
return $grossAmount * ($netAmount / $originalAmount);
}
/**
* Get exchange rate for a currency to EUR
* You might want to use a more sophisticated exchange rate service in production
*/
private function getExchangeRate(string $currency): float
{
// For now, we'll throw an exception if we can't get the exchange rate from Stripe
throw new \RuntimeException("Unable to convert {$currency} to EUR. No exchange rate available.");
}
private function cleanVatNumber(string $vatId): string
{
// Clean any special characters and convert to uppercase
return strtoupper(str_replace(['.', '-', ' '], '', $vatId));
}
private function generateXml(array $invoices): \SimpleXMLElement
{
$this->info('Generating XML file...');
$bar = $this->output->createProgressBar(3);
// Create XML without namespace (as per example)
$xml = new \SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><fichier_des></fichier_des>');
// Group invoices by month first, then by VAT number
$invoicesByMonth = [];
foreach ($invoices as $invoice) {
$month = Carbon::createFromTimestamp($invoice['created_at'])->format('Y-m');
if (!isset($invoicesByMonth[$month])) {
$invoicesByMonth[$month] = [];
}
$key = $invoice['vat_number']; // Use full VAT number as key since it includes country code
if (!isset($invoicesByMonth[$month][$key])) {
$invoicesByMonth[$month][$key] = [
'country_code' => $invoice['country_code'],
'vat_number' => $invoice['vat_number'],
'amount_eur' => 0,
];
}
$invoicesByMonth[$month][$key]['amount_eur'] += $invoice['amount_eur'];
}
$bar->advance();
// Sort months chronologically
ksort($invoicesByMonth);
// Create a declaration for each month
foreach ($invoicesByMonth as $month => $groupedInvoices) {
$monthDate = Carbon::createFromFormat('Y-m', $month);
// Add declaration header for this month
$declaration = $xml->addChild('declaration_des');
$declaration->addChild('num_des', '00001');
$declaration->addChild('num_tvaFr', $this->option('vat'));
$declaration->addChild('mois_des', $monthDate->format('m'));
$declaration->addChild('an_des', $monthDate->format('Y'));
// Add line items for this month
$lineNumber = 1;
foreach ($groupedInvoices as $line) {
$item = $declaration->addChild('ligne_des');
$item->addChild('numlin_des', str_pad($lineNumber++, 6, '0', STR_PAD_LEFT)); // 6 digits as per example
$item->addChild('valeur', round($line['amount_eur'])); // Round to nearest euro as per DES requirements
$item->addChild('partner_des', $line['vat_number']); // Use full VAT number from Stripe
}
}
$bar->advance();
$bar->finish();
$this->line('');
// Format XML with proper indentation
$dom = new \DOMDocument('1.0');
$dom->preserveWhiteSpace = false;
$dom->formatOutput = true;
$dom->loadXML($xml->asXML());
return new \SimpleXMLElement($dom->saveXML());
}
}