Malin
f40e57f576
Added 31 missing entities across three categories: Standalone (16 new): companyInformation, itemCategories, shipmentMethods, taxAreas, taxGroups, unitsOfMeasure, timeRegistrationEntries, contacts, generalProductPostingGroups, inventoryPostingGroups, itemLedgerEntries, opportunities, locations, projects, journalLines, irs1099 Financial reports (10 new, always full export): agedAccountsPayable, agedAccountsReceivable, balanceSheet, cashFlowStatement, incomeStatement, retainedEarningsStatement, trialBalance, customerFinancialDetails, customerSales, vendorPurchases Document+lines (5 new): salesQuotes, salesShipments, purchaseReceipts, customerPaymentJournals, vendorPaymentJournals Total entities: 19 → 50 Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
543 lines
19 KiB
PowerShell
Executable File
543 lines
19 KiB
PowerShell
Executable File
#!/usr/bin/env pwsh
|
|
#
|
|
# Business Central Data Export via BC API v2.0
|
|
# Authenticates to Azure AD and extracts critical business data as JSON
|
|
#
|
|
|
|
param(
|
|
[Parameter(Mandatory=$true)]
|
|
[string]$OutputPath,
|
|
[string]$SinceDateTime = "" # ISO 8601, e.g. "2026-02-15T00:00:00Z" for incremental
|
|
)
|
|
|
|
# Get configuration from environment variables
|
|
$tenantId = $env:AZURE_TENANT_ID
|
|
$clientId = $env:AZURE_CLIENT_ID
|
|
$clientSecret = $env:AZURE_CLIENT_SECRET
|
|
$environmentName = $env:BC_ENVIRONMENT_NAME
|
|
$bcCompanyName = $env:BC_COMPANY_NAME # optional: filter to specific company
|
|
|
|
$baseUrl = "https://api.businesscentral.dynamics.com/v2.0/$tenantId/$environmentName/api/v2.0"
|
|
|
|
# Standalone entities to extract (support lastModifiedDateTime filter for incremental)
|
|
$entities = @(
|
|
"accounts",
|
|
"customers",
|
|
"vendors",
|
|
"items",
|
|
"generalLedgerEntries",
|
|
"bankAccounts",
|
|
"employees",
|
|
"dimensions",
|
|
"dimensionValues",
|
|
"currencies",
|
|
"paymentTerms",
|
|
"paymentMethods",
|
|
"journals",
|
|
"journalLines",
|
|
"countriesRegions",
|
|
"companyInformation",
|
|
"itemCategories",
|
|
"shipmentMethods",
|
|
"taxAreas",
|
|
"taxGroups",
|
|
"unitsOfMeasure",
|
|
"timeRegistrationEntries",
|
|
"contacts",
|
|
"generalProductPostingGroups",
|
|
"inventoryPostingGroups",
|
|
"itemLedgerEntries",
|
|
"opportunities",
|
|
"locations",
|
|
"projects",
|
|
"irs1099"
|
|
)
|
|
|
|
# Financial report entities (always full export, no incremental filter support)
|
|
$reportEntities = @(
|
|
"agedAccountsPayable",
|
|
"agedAccountsReceivable",
|
|
"balanceSheet",
|
|
"cashFlowStatement",
|
|
"incomeStatement",
|
|
"retainedEarningsStatement",
|
|
"trialBalance",
|
|
"customerFinancialDetails",
|
|
"customerSales",
|
|
"vendorPurchases"
|
|
)
|
|
|
|
# Document entities with line items
|
|
# Lines cannot be queried standalone at the top level.
|
|
# We fetch document headers first, then fetch lines per document.
|
|
$documentEntities = @{
|
|
"salesInvoices" = "salesInvoiceLines"
|
|
"salesOrders" = "salesOrderLines"
|
|
"salesCreditMemos" = "salesCreditMemoLines"
|
|
"purchaseInvoices" = "purchaseInvoiceLines"
|
|
"purchaseOrders" = "purchaseOrderLines"
|
|
"salesQuotes" = "salesQuoteLines"
|
|
"salesShipments" = "salesShipmentLines"
|
|
"purchaseReceipts" = "purchaseReceiptLines"
|
|
"customerPaymentJournals" = "customerPayments"
|
|
"vendorPaymentJournals" = "vendorPayments"
|
|
}
|
|
|
|
# Token management
|
|
$script:currentToken = $null
|
|
$script:tokenExpiry = [datetime]::MinValue
|
|
|
|
function Write-Log {
|
|
param([string]$Message, [string]$Level = "INFO")
|
|
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
|
|
Write-Host "[$timestamp] [$Level] $Message"
|
|
}
|
|
|
|
function Get-AzureADToken {
|
|
Write-Log "Authenticating to Azure AD..."
|
|
|
|
$tokenUrl = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
|
|
|
|
$body = @{
|
|
client_id = $clientId
|
|
client_secret = $clientSecret
|
|
scope = "https://api.businesscentral.dynamics.com/.default"
|
|
grant_type = "client_credentials"
|
|
}
|
|
|
|
try {
|
|
$response = Invoke-RestMethod -Uri $tokenUrl -Method Post -Body $body -ContentType "application/x-www-form-urlencoded"
|
|
$script:currentToken = $response.access_token
|
|
# Refresh 5 minutes before actual expiry (tokens typically last 60-90 min)
|
|
$script:tokenExpiry = (Get-Date).AddSeconds($response.expires_in - 300)
|
|
Write-Log "Successfully authenticated (token valid for $($response.expires_in)s)"
|
|
return $script:currentToken
|
|
}
|
|
catch {
|
|
Write-Log "Failed to authenticate: $_" "ERROR"
|
|
throw
|
|
}
|
|
}
|
|
|
|
function Get-ValidToken {
|
|
if ($null -eq $script:currentToken -or (Get-Date) -ge $script:tokenExpiry) {
|
|
Write-Log "Token expired or missing, refreshing..."
|
|
Get-AzureADToken | Out-Null
|
|
}
|
|
return $script:currentToken
|
|
}
|
|
|
|
function Invoke-BCApi {
|
|
param(
|
|
[string]$Url,
|
|
[int]$TimeoutSec = 120,
|
|
[int]$MaxRetries = 10
|
|
)
|
|
|
|
for ($attempt = 1; $attempt -le $MaxRetries; $attempt++) {
|
|
$token = Get-ValidToken
|
|
$headers = @{
|
|
"Authorization" = "Bearer $token"
|
|
"Accept" = "application/json"
|
|
}
|
|
|
|
try {
|
|
$response = Invoke-RestMethod -Uri $Url -Method Get -Headers $headers -TimeoutSec $TimeoutSec
|
|
return $response
|
|
}
|
|
catch {
|
|
$statusCode = $null
|
|
$errorBody = ""
|
|
if ($_.Exception.Response) {
|
|
$statusCode = [int]$_.Exception.Response.StatusCode
|
|
}
|
|
if ($_.ErrorDetails.Message) {
|
|
$errorBody = $_.ErrorDetails.Message
|
|
}
|
|
|
|
# Log the actual error so we can diagnose issues
|
|
$shortError = if ($errorBody.Length -gt 200) { $errorBody.Substring(0, 200) + "..." } else { $errorBody }
|
|
if (-not $shortError) { $shortError = "$_" }
|
|
|
|
# Table lock: BC returns 500 with very specific wording
|
|
$isTableLock = $errorBody -match "transaction done by another session|being updated in a transaction|deadlock victim"
|
|
# Rate limit
|
|
$isThrottled = ($statusCode -eq 429)
|
|
# Timeout
|
|
$isTimeout = ($_ -match "Timeout")
|
|
# Other server errors (500+)
|
|
$isServerError = ($statusCode -ge 500 -and -not $isTableLock)
|
|
|
|
$isRetryable = $isTableLock -or $isThrottled -or $isServerError -or $isTimeout
|
|
|
|
if ($isRetryable -and $attempt -lt $MaxRetries) {
|
|
if ($isTableLock) {
|
|
$wait = [math]::Min(30 + ($attempt * 15), 120)
|
|
Write-Log " Table lock (attempt $attempt/$MaxRetries), waiting ${wait}s... Error: $shortError" "WARN"
|
|
}
|
|
elseif ($isThrottled) {
|
|
$wait = [math]::Min(30 * $attempt, 300)
|
|
Write-Log " Rate limited (attempt $attempt/$MaxRetries), waiting ${wait}s..." "WARN"
|
|
}
|
|
elseif ($isTimeout) {
|
|
$wait = [math]::Min(15 * $attempt, 120)
|
|
Write-Log " Timeout (attempt $attempt/$MaxRetries), retrying in ${wait}s..." "WARN"
|
|
}
|
|
else {
|
|
$wait = [math]::Min(15 * $attempt, 120)
|
|
Write-Log " HTTP $statusCode (attempt $attempt/$MaxRetries), retrying in ${wait}s... Error: $shortError" "WARN"
|
|
}
|
|
Start-Sleep -Seconds $wait
|
|
continue
|
|
}
|
|
throw
|
|
}
|
|
}
|
|
}
|
|
|
|
function Get-BCData {
|
|
param(
|
|
[string]$Url
|
|
)
|
|
|
|
$allRecords = @()
|
|
$currentUrl = $Url
|
|
|
|
while ($currentUrl) {
|
|
$response = Invoke-BCApi -Url $currentUrl
|
|
|
|
if ($response.value) {
|
|
$allRecords += $response.value
|
|
}
|
|
|
|
$currentUrl = $response.'@odata.nextLink'
|
|
}
|
|
|
|
return $allRecords
|
|
}
|
|
|
|
function Get-Companies {
|
|
Write-Log "Fetching companies..."
|
|
$companiesUrl = "$baseUrl/companies"
|
|
$companies = Get-BCData -Url $companiesUrl
|
|
Write-Log "Found $($companies.Count) company/companies"
|
|
return $companies
|
|
}
|
|
|
|
function Export-EntityData {
|
|
param(
|
|
[string]$CompanyId,
|
|
[string]$CompanyName,
|
|
[string]$EntityName,
|
|
[string]$OutputDir,
|
|
[switch]$NoFilter
|
|
)
|
|
|
|
$entityUrl = "$baseUrl/companies($CompanyId)/$EntityName"
|
|
if ($SinceDateTime -and -not $NoFilter) {
|
|
$entityUrl += "?`$filter=lastModifiedDateTime gt $SinceDateTime"
|
|
}
|
|
$maxEntityRetries = 5
|
|
|
|
for ($entityAttempt = 1; $entityAttempt -le $maxEntityRetries; $entityAttempt++) {
|
|
Write-Log " Exporting $EntityName..."
|
|
|
|
try {
|
|
$data = Get-BCData -Url $entityUrl
|
|
$count = 0
|
|
if ($data) { $count = $data.Count }
|
|
|
|
$outputFile = Join-Path $OutputDir "$EntityName.json"
|
|
$data | ConvertTo-Json -Depth 10 | Out-File -FilePath $outputFile -Encoding utf8
|
|
|
|
Write-Log " $EntityName : $count records"
|
|
return $count
|
|
}
|
|
catch {
|
|
$errorMsg = "$_"
|
|
$isTableLock = $errorMsg -match "transaction done by another session|being updated in a transaction|deadlock victim"
|
|
|
|
if ($isTableLock -and $entityAttempt -lt $maxEntityRetries) {
|
|
$wait = 60 * $entityAttempt
|
|
Write-Log " Table lock on $EntityName (attempt $entityAttempt/$maxEntityRetries), restarting in ${wait}s..." "WARN"
|
|
Start-Sleep -Seconds $wait
|
|
continue
|
|
}
|
|
|
|
Write-Log " Failed to export ${EntityName}: $errorMsg" "WARN"
|
|
$outputFile = Join-Path $OutputDir "$EntityName.json"
|
|
"[]" | Out-File -FilePath $outputFile -Encoding utf8
|
|
return 0
|
|
}
|
|
}
|
|
|
|
return 0
|
|
}
|
|
|
|
function Export-DocumentWithLines {
|
|
param(
|
|
[string]$CompanyId,
|
|
[string]$CompanyName,
|
|
[string]$DocumentEntity,
|
|
[string]$LineEntity,
|
|
[string]$OutputDir
|
|
)
|
|
|
|
# Retry the entire entity export if it fails (e.g. table lock on first page)
|
|
$maxEntityRetries = 5
|
|
|
|
for ($entityAttempt = 1; $entityAttempt -le $maxEntityRetries; $entityAttempt++) {
|
|
Write-Log " Exporting $DocumentEntity (headers + lines)..."
|
|
|
|
$docFile = Join-Path $OutputDir "$DocumentEntity.jsonl"
|
|
$lineFile = Join-Path $OutputDir "$LineEntity.jsonl"
|
|
[System.IO.File]::WriteAllText($docFile, "")
|
|
[System.IO.File]::WriteAllText($lineFile, "")
|
|
|
|
$docCount = 0
|
|
$lineCount = 0
|
|
$failed = $false
|
|
|
|
try {
|
|
# Step 1: Fetch document headers page by page (no $expand)
|
|
# BC API default page size is ~100, with @odata.nextLink for more
|
|
$currentUrl = "$baseUrl/companies($CompanyId)/$DocumentEntity"
|
|
if ($SinceDateTime) {
|
|
$currentUrl += "?`$filter=lastModifiedDateTime gt $SinceDateTime"
|
|
}
|
|
|
|
while ($currentUrl) {
|
|
$response = Invoke-BCApi -Url $currentUrl
|
|
|
|
if (-not $response.value -or $response.value.Count -eq 0) {
|
|
break
|
|
}
|
|
|
|
# Step 2: For each document in this page, fetch its lines
|
|
foreach ($doc in $response.value) {
|
|
$docCount++
|
|
$docId = $doc.id
|
|
|
|
# Write document header to disk
|
|
$jsonLine = $doc | ConvertTo-Json -Depth 10 -Compress
|
|
[System.IO.File]::AppendAllText($docFile, $jsonLine + "`n")
|
|
|
|
# Fetch lines for this document
|
|
$linesUrl = "$baseUrl/companies($CompanyId)/$DocumentEntity($docId)/$LineEntity"
|
|
try {
|
|
$linesResponse = Invoke-BCApi -Url $linesUrl -TimeoutSec 60
|
|
if ($linesResponse.value -and $linesResponse.value.Count -gt 0) {
|
|
foreach ($line in $linesResponse.value) {
|
|
$lineCount++
|
|
$lineJson = $line | ConvertTo-Json -Depth 10 -Compress
|
|
[System.IO.File]::AppendAllText($lineFile, $lineJson + "`n")
|
|
}
|
|
|
|
# Handle pagination within lines (unlikely but possible)
|
|
$nextLinesUrl = $linesResponse.'@odata.nextLink'
|
|
while ($nextLinesUrl) {
|
|
$moreLinesResponse = Invoke-BCApi -Url $nextLinesUrl -TimeoutSec 60
|
|
if ($moreLinesResponse.value) {
|
|
foreach ($line in $moreLinesResponse.value) {
|
|
$lineCount++
|
|
$lineJson = $line | ConvertTo-Json -Depth 10 -Compress
|
|
[System.IO.File]::AppendAllText($lineFile, $lineJson + "`n")
|
|
}
|
|
}
|
|
$nextLinesUrl = $moreLinesResponse.'@odata.nextLink'
|
|
}
|
|
}
|
|
}
|
|
catch {
|
|
Write-Log " Warning: failed to fetch lines for $DocumentEntity $docId : $_" "WARN"
|
|
}
|
|
|
|
# Progress every 100 documents
|
|
if ($docCount % 100 -eq 0) {
|
|
Write-Log " Progress: $docCount documents, $lineCount lines"
|
|
}
|
|
}
|
|
|
|
# Next page of documents
|
|
$currentUrl = $response.'@odata.nextLink'
|
|
}
|
|
|
|
Write-Log " $DocumentEntity : $docCount documents, $lineCount lines (complete)"
|
|
return ($docCount + $lineCount)
|
|
}
|
|
catch {
|
|
$errorMsg = "$_"
|
|
$isTableLock = $errorMsg -match "transaction done by another session|being updated in a transaction|deadlock victim"
|
|
|
|
if ($isTableLock -and $entityAttempt -lt $maxEntityRetries) {
|
|
$wait = 60 * $entityAttempt
|
|
Write-Log " Table lock on $DocumentEntity (attempt $entityAttempt/$maxEntityRetries), restarting in ${wait}s..." "WARN"
|
|
Start-Sleep -Seconds $wait
|
|
continue
|
|
}
|
|
|
|
Write-Log " Failed to export ${DocumentEntity} at doc #$docCount : $errorMsg" "WARN"
|
|
Write-Log " Partial data saved ($docCount docs, $lineCount lines)" "WARN"
|
|
return ($docCount + $lineCount)
|
|
}
|
|
}
|
|
|
|
return 0
|
|
}
|
|
|
|
# Main execution
|
|
try {
|
|
$exportMode = if ($SinceDateTime) { "incremental" } else { "full" }
|
|
|
|
Write-Log "========================================="
|
|
Write-Log "BC Data Export Script (API v2.0)"
|
|
Write-Log "========================================="
|
|
Write-Log "Environment: $environmentName"
|
|
Write-Log "Mode: $exportMode"
|
|
if ($SinceDateTime) {
|
|
Write-Log "Changes since: $SinceDateTime"
|
|
}
|
|
Write-Log "Output Path: $OutputPath"
|
|
$totalEntityCount = $entities.Count + $reportEntities.Count + $documentEntities.Count
|
|
Write-Log "Entities to extract: $totalEntityCount ($($entities.Count) standalone, $($reportEntities.Count) reports, $($documentEntities.Count) with line items)"
|
|
|
|
# Create output directory
|
|
$exportDir = $OutputPath
|
|
if (-not (Test-Path $exportDir)) {
|
|
New-Item -ItemType Directory -Path $exportDir -Force | Out-Null
|
|
}
|
|
|
|
# Step 1: Get Azure AD token
|
|
Get-AzureADToken | Out-Null
|
|
|
|
# Step 2: Get companies
|
|
$companies = Get-Companies
|
|
|
|
if ($companies.Count -eq 0) {
|
|
Write-Log "No companies found in environment $environmentName" "ERROR"
|
|
exit 1
|
|
}
|
|
|
|
# Save companies list
|
|
$companies | ConvertTo-Json -Depth 10 | Out-File -FilePath (Join-Path $exportDir "companies.json") -Encoding utf8
|
|
|
|
# Filter to specific company if configured
|
|
$targetCompanies = $companies
|
|
if ($bcCompanyName) {
|
|
$targetCompanies = $companies | Where-Object { $_.name -eq $bcCompanyName -or $_.displayName -eq $bcCompanyName }
|
|
if ($targetCompanies.Count -eq 0) {
|
|
Write-Log "Company '$bcCompanyName' not found. Available: $($companies.name -join ', ')" "ERROR"
|
|
exit 1
|
|
}
|
|
Write-Log "Filtering to company: $bcCompanyName"
|
|
}
|
|
|
|
$totalRecords = 0
|
|
$totalEntities = 0
|
|
$failedEntities = @()
|
|
|
|
# Step 3: Export data for each company
|
|
foreach ($company in $targetCompanies) {
|
|
$companyName = $company.name
|
|
$companyId = $company.id
|
|
|
|
Write-Log "-----------------------------------------"
|
|
Write-Log "Exporting company: $companyName ($companyId)"
|
|
|
|
# Create company directory (sanitize name for filesystem)
|
|
$safeName = $companyName -replace '[\\/:*?"<>|]', '_'
|
|
$companyDir = Join-Path $exportDir $safeName
|
|
if (-not (Test-Path $companyDir)) {
|
|
New-Item -ItemType Directory -Path $companyDir -Force | Out-Null
|
|
}
|
|
|
|
# Export standalone entities
|
|
foreach ($entity in $entities) {
|
|
$count = Export-EntityData `
|
|
-CompanyId $companyId `
|
|
-CompanyName $companyName `
|
|
-EntityName $entity `
|
|
-OutputDir $companyDir
|
|
|
|
$totalRecords += $count
|
|
$totalEntities++
|
|
|
|
if ($count -eq 0) {
|
|
$failedEntities += "$companyName/$entity"
|
|
}
|
|
}
|
|
|
|
# Export financial report entities (always full, no incremental filter)
|
|
foreach ($entity in $reportEntities) {
|
|
$count = Export-EntityData `
|
|
-CompanyId $companyId `
|
|
-CompanyName $companyName `
|
|
-EntityName $entity `
|
|
-OutputDir $companyDir `
|
|
-NoFilter
|
|
|
|
$totalRecords += $count
|
|
$totalEntities++
|
|
|
|
if ($count -eq 0) {
|
|
$failedEntities += "$companyName/$entity"
|
|
}
|
|
}
|
|
|
|
# Export document entities with their line items
|
|
foreach ($docEntity in $documentEntities.Keys) {
|
|
$lineEntity = $documentEntities[$docEntity]
|
|
|
|
$count = Export-DocumentWithLines `
|
|
-CompanyId $companyId `
|
|
-CompanyName $companyName `
|
|
-DocumentEntity $docEntity `
|
|
-LineEntity $lineEntity `
|
|
-OutputDir $companyDir
|
|
|
|
$totalRecords += $count
|
|
$totalEntities++
|
|
|
|
if ($count -eq 0) {
|
|
$failedEntities += "$companyName/$docEntity"
|
|
}
|
|
}
|
|
}
|
|
|
|
# Save export metadata
|
|
$metadata = @{
|
|
exportDate = (Get-Date -Format "yyyy-MM-dd HH:mm:ss UTC" -AsUTC)
|
|
environment = $environmentName
|
|
mode = $exportMode
|
|
sinceDateTime = if ($SinceDateTime) { $SinceDateTime } else { $null }
|
|
companies = @($targetCompanies | ForEach-Object { $_.name })
|
|
entitiesExported = $totalEntities
|
|
totalRecords = $totalRecords
|
|
failedEntities = $failedEntities
|
|
}
|
|
$metadata | ConvertTo-Json -Depth 5 | Out-File -FilePath (Join-Path $exportDir "export-metadata.json") -Encoding utf8
|
|
|
|
Write-Log "========================================="
|
|
Write-Log "Export completed"
|
|
Write-Log "Mode: $exportMode"
|
|
Write-Log "Companies: $($targetCompanies.Count)"
|
|
Write-Log "Entities: $totalEntities"
|
|
Write-Log "Total records: $totalRecords"
|
|
if ($failedEntities.Count -gt 0) {
|
|
Write-Log "Failed/empty: $($failedEntities.Count) entities" "WARN"
|
|
}
|
|
Write-Log "========================================="
|
|
|
|
# Exit code 2 = success but no records (used by bc-backup.sh to skip empty incrementals)
|
|
if ($totalRecords -eq 0 -and $exportMode -eq "incremental") {
|
|
Write-Log "No changes detected since $SinceDateTime"
|
|
exit 2
|
|
}
|
|
exit 0
|
|
}
|
|
catch {
|
|
Write-Log "Unexpected error: $_" "ERROR"
|
|
Write-Log "Stack trace: $($_.ScriptStackTrace)" "ERROR"
|
|
exit 1
|
|
}
|