[PowerShell] USt-IdNr. (VAT-ID) Massenprüfung gegen VIES – mit Auto-Retry, HTML-Report, Mailversand
Hallo zusammen,
ich hatte die Anforderung, mehrere hundert USt-IdNr. aus einer SQL-Datenbank (bei mir JTL-Wawi, lässt sich aber auf jede SQL-Quelle anpassen) gegen das offizielle EU-Prüfsystem VIES zu validieren – regelmäßig, automatisiert, mit Ergebnisbericht per Mail. Da ich dazu nichts Fertiges gefunden habe, hier mein Ergebnis zum Nachnutzen.
Was das Skript macht:
Technische Stolperfallen, die ich unterwegs gefixt habe (evtl. hilfreich für andere):
Voraussetzungen / Setup:
Sicherheitshinweis: In der Beispielversion stehen SQL-/SMTP-Passwörter als Platzhalter im Klartext im Skript – das ist der einfachste Weg für den Eigenbedarf, aber für produktiven/geteilten Einsatz würde ich dringend zu einem Secret-Store, verschlüsselten Windows-Credentials (Export-Clixml) oder Umgebungsvariablen raten.
Skript ist im Anhang.
Hallo zusammen,
ich hatte die Anforderung, mehrere hundert USt-IdNr. aus einer SQL-Datenbank (bei mir JTL-Wawi, lässt sich aber auf jede SQL-Quelle anpassen) gegen das offizielle EU-Prüfsystem VIES zu validieren – regelmäßig, automatisiert, mit Ergebnisbericht per Mail. Da ich dazu nichts Fertiges gefunden habe, hier mein Ergebnis zum Nachnutzen.
Was das Skript macht:
- Zieht Firmenname / Kundennummer / USt-IdNr. per SQL-Query aus einer beliebigen SQL-Server-Quelle
- Prüft jede Nummer gegen den offiziellen SOAP-Webservice der EU-Kommission (checkVatService) – bewusst SOAP statt der (inoffiziellen, weniger stabilen) REST-API
- Nicht-EU-Präfixe (GB, NO, CH, …) werden automatisch übersprungen, da VIES die ohnehin nicht prüfen kann
- Bei technischen Fehlern (VIES ist notorisch überlastet, MS_MAX_CONCURRENT_REQ kommt öfter vor) automatischer Retry mit exponentiellem Backoff, zusätzlich bis zu 5 komplette Prüfrunden über die Gesamtliste – jede Folgerunde prüft nur noch das, was in der Vorrunde tatsächlich fehlgeschlagen ist
- Erzeugt HTML-Report (mit clientseitigem Filter/Sortierung per JavaScript – Achtung, funktioniert nur beim Öffnen der Datei im Browser, nicht im Mail-Client) + CSV
- Versand per SMTP, HTML sowohl als Mail-Body als auch als Anhang
- Automatische Archivierung nach Monatsordnern (JJJJMM), automatische Bereinigung alter Arbeitsdateien
Technische Stolperfallen, die ich unterwegs gefixt habe (evtl. hilfreich für andere):
- DataTable als Rückgabewert einer Funktion: PowerShell "entpackt" das beim return in ein Array von DataRow, wenn man nicht den unären Komma-Operator nutzt (return ,$table). Führt sonst zu kryptischen "Index auf ein NULL-Array"-Fehlern.
- $Variable: in einem interpolierten String wird als PowerShell-Drive-Syntax interpretiert (wie $env
ATH), nicht als Variable gefolgt von einem Doppelpunkt-Zeichen. Fix: ${Variable}: statt $Variable:. - SOAP-Fault-Parsing: VIES liefert bei Überlastung HTTP 500 mit SOAP-Fault im Body (faultstring), das muss man separat aus $_.ErrorDetails.Message auslesen, nicht aus dem regulären Response.
- New-WebServiceProxy (der "klassische" Weg für SOAP in PowerShell) ist unter PowerShell 7 / .NET Core nicht mehr verfügbar. Ich baue den SOAP-Envelope daher als rohen XML-String und schicke ihn per Invoke-WebRequest – läuft dadurch auf PS 5.1 und PS7 gleichermaßen.
Voraussetzungen / Setup:
- Windows PowerShell 5.1 oder PowerShell 7
- SQL-Zugriff auf die Quelldatenbank (im Skript oben eintragen)
- SMTP-Zugang für den Mailversand (idealerweise App-Passwort, kein Hauptkennwort)
- SQL-Query und Spaltennamen (cFirma, cKundenNr, cUSTID) im Skript an das eigene Datenmodell anpassen
Sicherheitshinweis: In der Beispielversion stehen SQL-/SMTP-Passwörter als Platzhalter im Klartext im Skript – das ist der einfachste Weg für den Eigenbedarf, aber für produktiven/geteilten Einsatz würde ich dringend zu einem Secret-Store, verschlüsselten Windows-Credentials (Export-Clixml) oder Umgebungsvariablen raten.
Skript ist im Anhang.
Code:
<#
.SYNOPSIS
Prueft USt-IdNr. aus einer SQL-Server-Quelle gegen den offiziellen VIES SOAP-Webservice
der EU-Kommission und versendet das Ergebnis per Mail (SMTP).
.DESCRIPTION
1. Verbindet sich mit SQL Server (Zugangsdaten unten im Abschnitt "SQL CONFIG" statisch hinterlegt)
2. Fuehrt die fest hinterlegte SQL-Abfrage aus (Abschnitt "SQL QUERY")
3. Prueft jede USt-IdNr. gegen den SOAP-Endpoint checkVatService
(http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl, Operation "checkVat")
4. Erzeugt HTML-Report (mit clientseitigem Filter per Status/Spalte) + CSV
5. Versendet den Report IMMER per SMTP (Zugangsdaten unten im Abschnitt "MAIL CONFIG" statisch
hinterlegt) - HTML als Mail-Body UND als Anhang, CSV zusaetzlich als Anhang
.NOTES
VIES ist ein oeffentlicher Dienst ohne SLA. Timeouts/MS_UNAVAILABLE sind normal -> Retry-Logik eingebaut.
SOAP-Aufruf erfolgt per rohem Envelope ueber Invoke-WebRequest (kein New-WebServiceProxy, da dieser
unter PowerShell 7 / .NET Core nicht verfuegbar ist). Laeuft damit auf Windows PowerShell 5.1 und PS7.
SICHERHEITSHINWEIS: SQL- und SMTP-Zugangsdaten werden in diesem Beispiel-Script im Klartext
hinterlegt. Fuer den produktiven Einsatz wird dringend empfohlen, stattdessen z.B. ein
verschluesseltes Windows-Credential (Export-Clixml, nur auf dem ausfuehrenden Rechner/Account
lesbar), einen Secret-Store, oder eine Umgebungsvariable zu verwenden. Falls Zugangsdaten
dennoch direkt im Script gepflegt werden, unbedingt per NTFS-Rechte zugriffsschuetzen und
nicht in oeffentliche Repos einchecken.
.PARAMETER OutputFolder
Zielordner fuer HTML/CSV-Report. Default: aktuelles Verzeichnis.
.EXAMPLE
.\Check-VatNumbers.ps1
#>
[CmdletBinding()]
param(
[string]$OutputFolder = "C:\VatCheck\output"
)
$ErrorActionPreference = "Stop"
# =====================================================================================
# SQL CONFIG -- HIER STATISCH PFLEGEN
# =====================================================================================
# Trag hier deine SQL-Server-Zugangsdaten fest ein. Diese Werte verlassen die Maschine nicht,
# werden aber im Klartext im Script gespeichert -> Datei entsprechend zugriffsgeschuetzt ablegen
# (NTFS-Rechte einschraenken, nicht in oeffentliche Repos committen).
$SqlServer = "DEIN-SQL-SERVER" # z.B. "SRV01" oder "SRV01\INSTANZ,1433"
$SqlDatabase = "DEINE_DATENBANK" # Ziel-Datenbank
$SqlUser = "DEIN_SQL_USER" # SQL-Login (SA oder dedizierter Read-Only-User empfohlen)
$SqlPassword = "HIER_PASSWORT_EINTRAGEN"
# =====================================================================================
# SQL QUERY -- HIER STATISCH PFLEGEN
# =====================================================================================
# Beispiel-Query. Erwartet werden mindestens die Spalten cFirma, cKundenNr, cUSTID
# (Firmenname, Kundennummer, vollstaendige USt-IdNr. inkl. Laenderpraefix, z.B. "DE123456789").
# An das eigene Datenmodell anpassen.
$SqlQuery = @"
select top 1 cFirma, cKundenNr, cUSTID
from Kunde.lvKundenDaten
where cLand in (
'Deutschland','Belgien','Bulgarien','Dänemark','Estland','Finnland',
'Frankreich','Griechenland','Irland','Italien','Kroatien','Lettland',
'Litauen','Luxemburg','Malta','Niederlande','Österreich','Polen',
'Portugal','Rumänien','Schweden','Slowakei','Slowenien','Spanien',
'Tschechische Republik','Ungarn','Zypern'
)
and nullif(ltrim(rtrim(cUSTID)), '') is not null order by cLand
"@
# =====================================================================================
# MAIL CONFIG -- HIER STATISCH PFLEGEN
# =====================================================================================
# ACHTUNG: Passwort liegt hier im Klartext im Script. Datei entsprechend absichern
# (NTFS-Rechte einschraenken, nicht in Git/Repo, idealerweise App-Passwort statt Hauptkennwort).
$MailFrom = "noreply@deine-domain.de"
$MailToDefault = "empfaenger1@deine-domain.de, empfaenger2@deine-domain.de"
$MailSubject = "USt-IdNr.-Pruefung (VIES) - Ergebnisliste"
$SmtpServer = "smtp.office365.com"
$SmtpPort = 587
$SmtpUser = "noreply@deine-domain.de"
$SmtpPassword = "HIER_PASSWORT_EINTRAGEN"
# =====================================================================================
# VIES CONFIG (SOAP)
# =====================================================================================
$ViesEndpoint = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
$ViesNamespace = "urn:ec.europa.eu:taxud:vies:services:checkVat:types"
$SoapAction = "" # checkVatService benoetigt keine explizite SOAPAction, leer lassen
$MaxRetries = 5
$RetryDelaySec = 15
$RequestTimeoutSec = 20
$InterRequestDelayMs = 800 # Pause zwischen JEDEM VIES-Call, angehoben um MS_MAX_CONCURRENT_REQ zu vermeiden
# Anzahl der GESAMTEN Pruef-Durchlaeufe ueber die Liste (nicht zu verwechseln mit $MaxRetries oben,
# das sind die HTTP-Retries innerhalb EINES einzelnen VIES-Aufrufs). Runde 1 prueft den kompletten
# Batch, Runde 2+ prueft nur die Eintraege, die in der Vorrunde mit Status "Fehler" endeten.
$MaxCheckRounds = 5
# =====================================================================================
# ARCHIV CONFIG
# =====================================================================================
# Zusaetzlich zu $OutputFolder werden HTML+CSV hier unter einem Monats-Unterordner (YYYYMM)
# archiviert. Wird bei Bedarf automatisch angelegt. Bei Nichterreichbarkeit (z.B. Netzlaufwerk
# down) bricht das Skript NICHT ab, sondern gibt nur eine Warnung aus - der Mailversand hat Vorrang.
$ArchiveBasePath = "C:\VatCheck\Archiv"
# =====================================================================================
# OUTPUT-BEREINIGUNG CONFIG
# =====================================================================================
# Dateien im Output-Ordner (VatCheck_*.csv/.html), die aelter als diese Anzahl Tage sind,
# werden nach jedem Lauf geloescht. Das Archiv unter $ArchiveBasePath ist davon NICHT
# betroffen und bleibt unbegrenzt erhalten.
$OutputRetentionDays = 7
# Gueltige EU-Laendercodes (VIES deckt nur diese ab; XI = Nordirland-Sonderfall wird von VIES noch akzeptiert)
$ValidCountryCodes = @(
"AT","BE","BG","CY","CZ","DE","DK","EE","EL","ES","FI","FR","HR","HU",
"IE","IT","LT","LU","LV","MT","NL","PL","PT","RO","SE","SI","SK","XI"
)
# Drittstaaten-Praefixe, die bewusst NICHT gegen VIES geprueft werden sollen (kein EU-Mitglied,
# VIES kann diese ohnehin nicht validieren). Diese Datensaetze werden im Report als "Uebersprungen"
# gefuehrt, nicht als Fehler. Liste bei Bedarf erweitern.
$SkipCountryCodes = @("GB", "NO", "CH")
# =====================================================================================
# FUNKTIONEN
# =====================================================================================
function Get-VatNumbersFromSql {
param(
[string]$Server,
[string]$Database,
[string]$User,
[string]$Password,
[string]$Query
)
Write-Host "Verbinde mit SQL Server '$Server' / DB '$Database' ..." -ForegroundColor Cyan
$connString = "Server=$Server;Database=$Database;User Id=$User;Password=$Password;TrustServerCertificate=True;Connection Timeout=15;"
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
try {
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $Query
$cmd.CommandTimeout = 60
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
$table = New-Object System.Data.DataTable
[void]$adapter.Fill($table)
Write-Host " -> $($table.Rows.Count) Datensaetze geladen." -ForegroundColor Green
# Komma-Operator verhindert, dass PowerShell das DataTable-Objekt beim Return
# in seine Zeilen "entpackt" (DataTable ist IEnumerable) - ohne das Komma kaeme
# im Aufrufer ggf. ein Array von DataRow statt eines DataTable-Objekts an.
return ,$table
}
catch {
Write-Error "SQL-Fehler: $($_.Exception.Message)"
throw
}
finally {
if ($conn.State -eq 'Open') { $conn.Close() }
}
}
function Split-VatNumber {
param([string]$RawVatId)
$clean = ($RawVatId -replace '[\s\-\.]', '').ToUpperInvariant()
if ($clean.Length -lt 3) {
return [PSCustomObject]@{ CountryCode = $null; Number = $null; Raw = $RawVatId; ParseError = "Zu kurz / leer"; Skip = $false }
}
$cc = $clean.Substring(0, 2)
$num = $clean.Substring(2)
if ($SkipCountryCodes -contains $cc) {
return [PSCustomObject]@{ CountryCode = $cc; Number = $num; Raw = $RawVatId; ParseError = $null; Skip = $true }
}
if ($ValidCountryCodes -notcontains $cc) {
return [PSCustomObject]@{ CountryCode = $cc; Number = $num; Raw = $RawVatId; ParseError = "Unbekannter/kein EU-Laendercode ('$cc')"; Skip = $false }
}
return [PSCustomObject]@{ CountryCode = $cc; Number = $num; Raw = $RawVatId; ParseError = $null; Skip = $false }
}
function Get-XmlChildText {
param($ParentNode, [string]$LocalName)
if (-not $ParentNode) { return $null }
foreach ($child in $ParentNode.ChildNodes) {
if ($child.LocalName -eq $LocalName) { return $child.InnerText }
}
return $null
}
function Invoke-ViesCheck {
param(
[string]$CountryCode,
[string]$Number
)
# SOAP 1.1 Envelope fuer die Operation checkVat des offiziellen VIES-Webservice
$envelope = @"
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="$ViesNamespace">
<soapenv:Header/>
<soapenv:Body>
<urn:checkVat>
<urn:countryCode>$CountryCode</urn:countryCode>
<urn:vatNumber>$Number</urn:vatNumber>
</urn:checkVat>
</soapenv:Body>
</soapenv:Envelope>
"@
$headers = @{
"Content-Type" = "text/xml; charset=utf-8"
}
if ($SoapAction) { $headers["SOAPAction"] = $SoapAction }
for ($attempt = 1; $attempt -le $MaxRetries; $attempt++) {
try {
$response = Invoke-WebRequest -Uri $ViesEndpoint -Method Post -Body $envelope -Headers $headers -TimeoutSec $RequestTimeoutSec -UseBasicParsing
[xml]$xmlResponse = $response.Content
$ns = New-Object System.Xml.XmlNamespaceManager($xmlResponse.NameTable)
$ns.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/")
$ns.AddNamespace("urn", $ViesNamespace)
$node = $xmlResponse.SelectSingleNode("//urn:checkVatResponse", $ns)
if (-not $node) {
# Fallback: manche Gateways antworten mit abweichendem/fehlendem Praefix
$candidates = $xmlResponse.GetElementsByTagName("checkVatResponse")
if ($candidates -and $candidates.Count -gt 0) { $node = $candidates[0] }
}
if (-not $node) {
throw "checkVatResponse nicht im XML gefunden. Rohantwort: $($response.Content.Substring(0, [Math]::Min(300, $response.Content.Length)))"
}
$validText = Get-XmlChildText -ParentNode $node -LocalName "valid"
$nameText = Get-XmlChildText -ParentNode $node -LocalName "name"
$addressText = Get-XmlChildText -ParentNode $node -LocalName "address"
return [PSCustomObject]@{
Success = $true
Valid = ($validText -eq "true")
Name = $nameText
Address = $addressText
Error = $null
}
}
catch {
# SOAP-Faults (INVALID_INPUT, MS_UNAVAILABLE, SERVICE_UNAVAILABLE, TIMEOUT, MS_MAX_CONCURRENT_REQ, ...)
# kommen bei Invoke-WebRequest i.d.R. als HTTP 500 mit SOAP-Fault-XML im Body.
$faultString = $null
try {
if ($_.ErrorDetails -and $_.ErrorDetails.Message) {
[xml]$faultXml = $_.ErrorDetails.Message
$faultNode = $faultXml.SelectSingleNode("//faultstring")
if ($faultNode) { $faultString = $faultNode.InnerText }
}
} catch {}
$retryableFaults = @("MS_UNAVAILABLE", "SERVICE_UNAVAILABLE", "TIMEOUT", "GLOBAL_MAX_CONCURRENT_REQ", "MS_MAX_CONCURRENT_REQ")
if ($faultString -and ($retryableFaults -contains $faultString) -and $attempt -lt $MaxRetries) {
# Exponentielles Backoff: 15s, 30s, 60s, 120s ... - MS_MAX_CONCURRENT_REQ braucht oft
# laenger als ein paar Sekunden, bis das jeweilige Mitgliedsstaat-System wieder frei ist.
$backoffSec = $RetryDelaySec * [Math]::Pow(2, $attempt - 1)
Write-Host " $faultString fuer $CountryCode$Number, Retry $attempt/$MaxRetries in $backoffSec s ..." -ForegroundColor Yellow
Start-Sleep -Seconds $backoffSec
continue
}
if ($faultString) {
return [PSCustomObject]@{
Success = $false
Valid = $null
Name = $null
Address = $null
Error = $faultString
}
}
if ($attempt -lt $MaxRetries) {
$backoffSec = $RetryDelaySec * [Math]::Pow(2, $attempt - 1)
Write-Host " Fehler bei $CountryCode$Number (Versuch $attempt/$MaxRetries): $($_.Exception.Message) -> Retry in $backoffSec s" -ForegroundColor Yellow
Start-Sleep -Seconds $backoffSec
continue
}
return [PSCustomObject]@{
Success = $false
Valid = $null
Name = $null
Address = $null
Error = "HTTP-/Verbindungsfehler: $($_.Exception.Message)"
}
}
}
return [PSCustomObject]@{
Success = $false
Valid = $null
Name = $null
Address = $null
Error = "Max. Retries erreicht ohne Ergebnis"
}
}
function New-HtmlReport {
param(
[array]$Results,
[datetime]$RunTime
)
$totalCount = $Results.Count
$validCount = ($Results | Where-Object { $_.Status -eq "Gueltig" }).Count
$invalidCount = ($Results | Where-Object { $_.Status -eq "Ungueltig" }).Count
$errorCount = ($Results | Where-Object { $_.Status -eq "Fehler" }).Count
$skippedCount = ($Results | Where-Object { $_.Status -eq "Uebersprungen" }).Count
$rows = foreach ($r in $Results) {
$rowColor = switch ($r.Status) {
"Gueltig" { "#d4edda" }
"Ungueltig" { "#f8d7da" }
"Uebersprungen" { "#e2e3e5" }
default { "#fff3cd" }
}
@"
<tr style="background-color:$rowColor;" data-status="$($r.Status)">
<td>$([System.Net.WebUtility]::HtmlEncode($r.KundenNr))</td>
<td>$([System.Net.WebUtility]::HtmlEncode($r.Bezeichnung))</td>
<td>$([System.Net.WebUtility]::HtmlEncode($r.UstId))</td>
<td><b>$($r.Status)</b></td>
<td>$([System.Net.WebUtility]::HtmlEncode($r.Firmenname))</td>
<td>$([System.Net.WebUtility]::HtmlEncode($r.Adresse))</td>
<td>$([System.Net.WebUtility]::HtmlEncode($r.Hinweis))</td>
</tr>
"@
}
$html = @"
<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<style>
body { font-family: Segoe UI, Arial, sans-serif; font-size: 13px; color: #222; }
h2 { margin-bottom: 4px; }
.summary { margin-bottom: 16px; }
.summary span { display:inline-block; margin-right: 20px; padding: 4px 10px; border-radius: 4px; }
.filterbar { margin-bottom: 12px; display: flex; gap: 10px; align-items: center; flex-wrap: wrap; }
.filterbar select, .filterbar input {
padding: 5px 8px; border: 1px solid #ccc; border-radius: 4px; font-size: 13px;
}
.filterbar input[type="text"] { min-width: 220px; }
.hint { color: #777; font-size: 11px; margin-bottom: 10px; }
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ccc; padding: 6px 8px; text-align: left; vertical-align: top; }
th { background-color: #2c3e50; color: white; cursor: pointer; user-select: none; white-space: nowrap; }
th:hover { background-color: #3d5470; }
th .arrow { font-size: 10px; opacity: 0.7; }
tr:hover { filter: brightness(0.96); }
tr.js-hidden { display: none; }
#resultCount { font-weight: bold; }
</style>
</head>
<body>
<h2>USt-IdNr.-Pruefung (VIES)</h2>
<div>Lauf: $($RunTime.ToString("dd.MM.yyyy HH:mm"))</div>
<div class="summary">
<span style="background:#e2e3e5;">Gesamt: $totalCount</span>
<span style="background:#d4edda;">Gueltig: $validCount</span>
<span style="background:#f8d7da;">Ungueltig: $invalidCount</span>
<span style="background:#fff3cd;">Fehler: $errorCount</span>
<span style="background:#e2e3e5;">Uebersprungen (Drittstaat): $skippedCount</span>
</div>
<div class="filterbar">
<label for="statusFilter"><b>Status:</b></label>
<select id="statusFilter" onchange="filterTable()">
<option value="">Alle</option>
<option value="Gueltig">Gueltig</option>
<option value="Ungueltig">Ungueltig</option>
<option value="Fehler">Fehler</option>
<option value="Uebersprungen">Uebersprungen</option>
</select>
<label for="textFilter"><b>Suche:</b></label>
<input type="text" id="textFilter" placeholder="Kd-Nr., Firma, USt-IdNr. ..." oninput="filterTable()">
<span>Treffer: <span id="resultCount">$totalCount</span></span>
</div>
<div class="hint">
Hinweis: Filter/Sortierung funktionieren nur, wenn dieser Report im Browser geoeffnet wird
(z.B. ueber den HTML-Anhang) - im Mail-Programm selbst wird nur die statische Tabelle angezeigt,
da die meisten Mail-Clients JavaScript aus Sicherheitsgruenden nicht ausfuehren.
</div>
<table id="reportTable">
<thead>
<tr>
<th onclick="sortTable(0)">Kd-Nr. <span class="arrow">↕</span></th>
<th onclick="sortTable(1)">Bezeichnung <span class="arrow">↕</span></th>
<th onclick="sortTable(2)">USt-IdNr. <span class="arrow">↕</span></th>
<th onclick="sortTable(3)">Status <span class="arrow">↕</span></th>
<th onclick="sortTable(4)">Firmenname (VIES) <span class="arrow">↕</span></th>
<th onclick="sortTable(5)">Adresse (VIES) <span class="arrow">↕</span></th>
<th onclick="sortTable(6)">Hinweis <span class="arrow">↕</span></th>
</tr>
</thead>
<tbody>
$($rows -join "`n")
</tbody>
</table>
<script>
function filterTable() {
var statusVal = document.getElementById('statusFilter').value;
var textVal = document.getElementById('textFilter').value.toLowerCase();
var rows = document.querySelectorAll('#reportTable tbody tr');
var visibleCount = 0;
rows.forEach(function(row) {
var matchesStatus = !statusVal || row.getAttribute('data-status') === statusVal;
var matchesText = !textVal || row.textContent.toLowerCase().indexOf(textVal) !== -1;
if (matchesStatus && matchesText) {
row.classList.remove('js-hidden');
visibleCount++;
} else {
row.classList.add('js-hidden');
}
});
document.getElementById('resultCount').textContent = visibleCount;
}
var sortState = {};
function sortTable(colIndex) {
var table = document.getElementById('reportTable');
var tbody = table.querySelector('tbody');
var rows = Array.prototype.slice.call(tbody.querySelectorAll('tr'));
var asc = !sortState[colIndex];
sortState = {};
sortState[colIndex] = asc;
rows.sort(function(a, b) {
var aText = a.children[colIndex].textContent.trim().toLowerCase();
var bText = b.children[colIndex].textContent.trim().toLowerCase();
if (aText < bText) return asc ? -1 : 1;
if (aText > bText) return asc ? 1 : -1;
return 0;
});
rows.forEach(function(row) { tbody.appendChild(row); });
}
</script>
</body>
</html>
"@
return $html
}
function Remove-OldOutputFiles {
param(
[string]$Folder,
[int]$RetentionDays
)
if (-not (Test-Path $Folder)) {
Write-Host "Bereinigung uebersprungen: Ordner '$Folder' nicht erreichbar." -ForegroundColor DarkGray
return
}
$cutoffDate = (Get-Date).AddDays(-$RetentionDays)
try {
# Nur eigene Dateien anfassen (Namensmuster VatCheck_*), nicht den ganzen Ordner leerraeumen -
# falls dort noch andere Dateien liegen, die nicht von diesem Skript stammen.
$oldFiles = Get-ChildItem -Path $Folder -Filter "VatCheck_*.*" -File -ErrorAction Stop |
Where-Object { $_.LastWriteTime -lt $cutoffDate }
if (-not $oldFiles -or $oldFiles.Count -eq 0) {
Write-Host "Bereinigung: keine Dateien aelter als $RetentionDays Tage im Output-Ordner gefunden." -ForegroundColor DarkGray
return
}
$deletedCount = 0
foreach ($file in $oldFiles) {
try {
Remove-Item -Path $file.FullName -Force -ErrorAction Stop
$deletedCount++
}
catch {
Write-Warning "Konnte Datei nicht loeschen: $($file.FullName) - $($_.Exception.Message)"
}
}
Write-Host "Bereinigung: $deletedCount von $($oldFiles.Count) Datei(en) aelter als $RetentionDays Tage geloescht." -ForegroundColor Green
}
catch {
Write-Warning "Bereinigung des Output-Ordners fehlgeschlagen: $($_.Exception.Message)"
}
}
function Send-ReportMail {
param(
[string]$HtmlBody,
[string]$CsvAttachmentPath,
[string]$HtmlAttachmentPath,
[int]$ValidCount,
[int]$InvalidCount,
[int]$ErrorCount
)
Write-Host ""
Write-Host "=== Mailversand ueber $SmtpServer ===" -ForegroundColor Cyan
$securePass = ConvertTo-SecureString $SmtpPassword -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ($SmtpUser, $securePass)
$recipients = $MailToDefault -split ',' | ForEach-Object { $_.Trim() } | Where-Object { $_ -ne "" }
$subject = "$MailSubject - $((Get-Date).ToString('dd.MM.yyyy')) - $ValidCount gueltig / $InvalidCount ungueltig / $ErrorCount Fehler"
$mailParams = @{
From = $MailFrom
To = $recipients
Subject = $subject
Body = $HtmlBody
BodyAsHtml = $true
SmtpServer = $SmtpServer
Port = $SmtpPort
UseSsl = $true
Credential = $cred
Attachments = @($CsvAttachmentPath, $HtmlAttachmentPath)
}
try {
Send-MailMessage @mailParams
Write-Host "Mail erfolgreich versendet an: $($recipients -join ', ')" -ForegroundColor Green
}
catch {
Write-Error "Mailversand fehlgeschlagen: $($_.Exception.Message)"
throw
}
}
function Test-VatEntry {
param(
[string]$KundenNr,
[string]$Bezeichnung,
[string]$RawVatId
)
$parsed = Split-VatNumber -RawVatId $RawVatId
if ($parsed.Skip) {
Write-Host " -> Uebersprungen (Drittstaat: $($parsed.CountryCode))" -ForegroundColor DarkGray
return [PSCustomObject]@{
KundenNr = $KundenNr
Bezeichnung = $Bezeichnung
UstId = $RawVatId
Status = "Uebersprungen"
Firmenname = ""
Adresse = ""
Hinweis = "Drittstaat ($($parsed.CountryCode)), nicht via VIES pruefbar"
}
}
if ($parsed.ParseError) {
Write-Host " -> Parse-Fehler: $($parsed.ParseError)" -ForegroundColor Red
return [PSCustomObject]@{
KundenNr = $KundenNr
Bezeichnung = $Bezeichnung
UstId = $RawVatId
Status = "Fehler"
Firmenname = ""
Adresse = ""
Hinweis = $parsed.ParseError
}
}
$viesResult = Invoke-ViesCheck -CountryCode $parsed.CountryCode -Number $parsed.Number
if (-not $viesResult.Success) {
Write-Host " -> Fehler: $($viesResult.Error)" -ForegroundColor Red
return [PSCustomObject]@{
KundenNr = $KundenNr
Bezeichnung = $Bezeichnung
UstId = $RawVatId
Status = "Fehler"
Firmenname = ""
Adresse = ""
Hinweis = $viesResult.Error
}
}
elseif ($viesResult.Valid -eq $true) {
Write-Host " -> Gueltig" -ForegroundColor Green
return [PSCustomObject]@{
KundenNr = $KundenNr
Bezeichnung = $Bezeichnung
UstId = $RawVatId
Status = "Gueltig"
Firmenname = $viesResult.Name
Adresse = $viesResult.Address
Hinweis = ""
}
}
else {
Write-Host " -> Ungueltig" -ForegroundColor Yellow
return [PSCustomObject]@{
KundenNr = $KundenNr
Bezeichnung = $Bezeichnung
UstId = $RawVatId
Status = "Ungueltig"
Firmenname = $viesResult.Name
Adresse = $viesResult.Address
Hinweis = ""
}
}
}
# =====================================================================================
# HAUPTABLAUF
# =====================================================================================
$runTime = Get-Date
Write-Host "=== VIES USt-IdNr.-Pruefung gestartet: $($runTime.ToString('dd.MM.yyyy HH:mm:ss')) ===" -ForegroundColor Cyan
# 1) Daten aus SQL holen
$sqlData = Get-VatNumbersFromSql -Server $SqlServer -Database $SqlDatabase -User $SqlUser -Password $SqlPassword -Query $SqlQuery
if ($sqlData -isnot [System.Data.DataTable]) {
Write-Error "Unerwarteter Rueckgabetyp aus SQL-Abfrage: $($sqlData.GetType().FullName). Erwartet: System.Data.DataTable. Abbruch."
return
}
if ($sqlData.Rows.Count -eq 0) {
Write-Warning "Keine Datensaetze aus SQL erhalten. Abbruch."
return
}
foreach ($col in @("cUSTID", "cFirma", "cKundenNr")) {
if (-not $sqlData.Columns.Contains($col)) {
Write-Error "Erwartete Spalte '$col' fehlt im SQL-Ergebnis. Vorhandene Spalten: $($sqlData.Columns.ColumnName -join ', '). Abbruch."
return
}
}
# 2) Runde 1: Jede USt-IdNr aus dem kompletten Batch pruefen
$results = New-Object System.Collections.Generic.List[PSObject]
$rowIndex = 0
$totalRows = $sqlData.Rows.Count
Write-Host ""
Write-Host "--- Runde 1/${MaxCheckRounds}: kompletter Batch ($totalRows Datensaetze) ---" -ForegroundColor Cyan
foreach ($row in $sqlData.Rows) {
$rowIndex++
$rawVatId = if ($row["cUSTID"] -is [DBNull]) { "" } else { [string]$row["cUSTID"] }
$bezeichnung = if ($row["cFirma"] -is [DBNull]) { "" } else { [string]$row["cFirma"] }
$kundenNr = if ($row["cKundenNr"] -is [DBNull]) { "" } else { [string]$row["cKundenNr"] }
Write-Host "[$rowIndex/$totalRows] Pruefe $rawVatId ($bezeichnung, Kd-Nr. $kundenNr) ..." -NoNewline
$entryResult = Test-VatEntry -KundenNr $kundenNr -Bezeichnung $bezeichnung -RawVatId $rawVatId
$results.Add($entryResult)
# kleine Pause um die oeffentliche API nicht zu ueberlasten (nur bei tatsaechlichem API-Call noetig,
# Skip/Parse-Fehler-Faelle erzeugen keine Last, schaden aber auch nicht durch die kurze Pause)
if ($entryResult.Status -ne "Uebersprungen") {
Start-Sleep -Milliseconds $InterRequestDelayMs
}
}
# 2b) Retry-Runden: nur Eintraege mit Status "Fehler" erneut pruefen, insgesamt max. $MaxCheckRounds Durchlaeufe
for ($round = 2; $round -le $MaxCheckRounds; $round++) {
$failedIndexes = for ($i = 0; $i -lt $results.Count; $i++) {
if ($results[$i].Status -eq "Fehler") { $i }
}
if ($failedIndexes.Count -eq 0) {
Write-Host ""
Write-Host "Keine Fehler-Eintraege mehr vorhanden, ueberspringe Runde $round." -ForegroundColor DarkGray
break
}
Write-Host ""
Write-Host "--- Runde ${round}/${MaxCheckRounds}: erneute Pruefung von $($failedIndexes.Count) fehlgeschlagenen Eintraegen ---" -ForegroundColor Cyan
Write-Host "Warte 30s vor Rundenstart, damit sich der VIES-Dienst erholen kann ..." -ForegroundColor DarkGray
Start-Sleep -Seconds 30
$retryCount = 0
foreach ($idx in $failedIndexes) {
$retryCount++
$prevResult = $results[$idx]
Write-Host "[$retryCount/$($failedIndexes.Count)] Re-Check $($prevResult.UstId) ($($prevResult.Bezeichnung)) ..." -NoNewline
$entryResult = Test-VatEntry -KundenNr $prevResult.KundenNr -Bezeichnung $prevResult.Bezeichnung -RawVatId $prevResult.UstId
$results[$idx] = $entryResult
# In Retry-Runden grosszuegigere Pause: diese Eintraege sind bereits einmal an
# Ueberlastung gescheitert, daher zusaetzliche Schonzeit fuer den betroffenen VIES-Dienst.
Start-Sleep -Milliseconds ($InterRequestDelayMs * 2)
}
}
# 3) Reports erzeugen
# Falls der konfigurierte (UNC-)Ausgabeordner nicht erreichbar ist, auf lokalen Temp-Ordner
# ausweichen, damit Report-Erzeugung und Mailversand trotzdem stattfinden koennen.
try {
if (-not (Test-Path $OutputFolder)) {
New-Item -ItemType Directory -Path $OutputFolder -Force -ErrorAction Stop | Out-Null
}
$effectiveOutputFolder = $OutputFolder
}
catch {
$effectiveOutputFolder = Join-Path $env:TEMP "VatCheck_Fallback"
if (-not (Test-Path $effectiveOutputFolder)) {
New-Item -ItemType Directory -Path $effectiveOutputFolder -Force | Out-Null
}
Write-Warning "Ausgabeordner '$OutputFolder' nicht erreichbar ($($_.Exception.Message)). Weiche aus auf: $effectiveOutputFolder"
}
$timestamp = $runTime.ToString("yyyyMMdd_HHmmss")
$csvPath = Join-Path $effectiveOutputFolder "VatCheck_$timestamp.csv"
$htmlPath = Join-Path $effectiveOutputFolder "VatCheck_$timestamp.html"
$results | Export-Csv -Path $csvPath -NoTypeInformation -Encoding UTF8 -Delimiter ';'
$htmlReport = New-HtmlReport -Results $results -RunTime $runTime
$htmlReport | Out-File -FilePath $htmlPath -Encoding UTF8
# 3b) Zusaetzlich ins Monats-Archiv kopieren (YYYYMM-Unterordner unter $ArchiveBasePath)
$archiveMonthFolder = $null
try {
$archiveMonthFolder = Join-Path $ArchiveBasePath $runTime.ToString("yyyyMM")
if (-not (Test-Path $archiveMonthFolder)) {
New-Item -ItemType Directory -Path $archiveMonthFolder -Force | Out-Null
Write-Host "Archiv-Ordner angelegt: $archiveMonthFolder" -ForegroundColor DarkGray
}
Copy-Item -Path $csvPath -Destination $archiveMonthFolder -Force
Copy-Item -Path $htmlPath -Destination $archiveMonthFolder -Force
Write-Host "Archiviert nach: $archiveMonthFolder" -ForegroundColor Green
}
catch {
Write-Warning "Archivierung nach '$ArchiveBasePath' fehlgeschlagen: $($_.Exception.Message). Mailversand laeuft trotzdem weiter."
}
$validCount = ($results | Where-Object { $_.Status -eq "Gueltig" }).Count
$invalidCount = ($results | Where-Object { $_.Status -eq "Ungueltig" }).Count
$errorCount = ($results | Where-Object { $_.Status -eq "Fehler" }).Count
$skippedCount = ($results | Where-Object { $_.Status -eq "Uebersprungen" }).Count
Write-Host ""
Write-Host "=== Zusammenfassung (nach bis zu $MaxCheckRounds Runden) ===" -ForegroundColor Cyan
Write-Host "Gesamt: $($results.Count) | Gueltig: $validCount | Ungueltig: $invalidCount | Fehler: $errorCount | Uebersprungen (Drittstaat): $skippedCount"
Write-Host "CSV: $csvPath"
Write-Host "HTML: $htmlPath"
if ($archiveMonthFolder) { Write-Host "Archiv: $archiveMonthFolder" } else { Write-Host "Archiv: nicht verfuegbar (siehe Warnung oben)" -ForegroundColor Yellow }
# 4) Mailversand (immer, kein Opt-in mehr noetig)
Send-ReportMail -HtmlBody $htmlReport -CsvAttachmentPath $csvPath -HtmlAttachmentPath $htmlPath -ValidCount $validCount -InvalidCount $invalidCount -ErrorCount $errorCount
# 5) Output-Ordner bereinigen (nur Output, Archiv bleibt unangetastet)
Remove-OldOutputFiles -Folder $effectiveOutputFolder -RetentionDays $OutputRetentionDays
Write-Host ""
Write-Host "=== Fertig ===" -ForegroundColor Cyan