## Overview AutostacjaSync is a production Windows service providing automatic synchronization of business documents between external systems (car service stations, automotive wholesalers) and Prestiž ERP. The system processes dozens of XML document types, manages contractors across 25+ EU countries, handles document corrections, and ensures data consistency through advanced Firebird stored procedures. Key role: eliminating manual invoice transcription, automating warehouse receipts, synchronizing contractor data from external sources while maintaining referential integrity and uniqueness in the accounting system. ## Business Context ### Problem Automotive companies use dedicated workshop systems (external XML APIs) that generate sales documents, but the main accounting and warehouse management is in Prestiž ERP (Firebird). Manual transcription: - Generates errors in VAT IDs, prices, quantities - Delays document realization by hours - Requires duplicate contractor data entry - Doesn't handle corrections automatically ### Solution Automated pipeline: 1. External systems export XML to network directory 2. AutostacjaSync detects, parses, validates 3. Intelligent contractor mapping (search by VAT ID, create new with EU VAT handling) 4. Import to URZZEWNAGL/URZZEWNPOZ buffer in Prestiž 5. Prestiž user realizes imported document with one click ### Value - **Zero manual work** for standard documents - **Error elimination** in VAT IDs, prices, names - **International compliance** - automatic EU VAT mapping (25 countries) - **Correction handling** - intelligent line mapping despite ERP reordering - **Audit trail** - complete history for every document in NLog logs ## Technical Architecture ### Clean Architecture + DDD Lite ``` Domain/ Abstractions/ IContractorProcedures.cs - ARIT_AUTOSTACJA_ADD_CONTRACTOR contract IItemProcedures.cs - ARIT_AUTOSTACJA_ADD_ITEM IExternalDocProcedures.cs - URZZEWNAGL_ADD_10, URZZEWNPOZ_ADD_10 ValueObjects/ ContractorRef.cs - VAT ID, name, address (immutable) ItemRef.cs - code, name, price Entities/ ExternalDocument.cs - document model with lines Application/ Commands/ ProcessLocalFileCommand.cs - CQRS command for single XML Services/ ContractorService.cs - contractor business logic ItemService.cs - catalog mapping DocumentProcessor.cs - entire process orchestration Mappers/ ImportMappers.cs - ToAddContractorArgs(), NormalizeVatIsoAndDigits() Options/ DetailContractorOptions.cs - retail contractor configuration Infrastructure/ Persistence/ FirebirdDbContext.cs - EF Core (read-only dictionaries) Procedures/ FirebirdContractorProcedures.cs - ADO.NET calls FirebirdProcedureBase.cs - abstraction with connection management XmlParsing/ TemplateDetector.cs - detects XML type by structure Parsers/ SpFvatParser.cs - VAT invoices SP system MgPkwParser.cs - inter-warehouse transfers MG ZlPayloadBuilder.cs - warehouse receipts ZL Host/ Workers/ MainWorker.cs - FileSystemWatcher + polling Program.cs - DI setup, NLog, MediatR ``` ### Key Patterns **CQRS** - ProcessLocalFileCommand separates request from execution **Repository + Procedure Facade** - FirebirdProcedureBase enforces: ```csharp public abstract class FirebirdProcedureBase { protected FbConnection CreateConnection() { // Independent connection, NOT from DbContext // ConnectionLifeTime=60 prevents SocketException return new FbConnection(_connectionString); } } ``` **Strategy Pattern** - different XML parsers for different sources **Three-Tier Document Type Resolution**: 1. Matrix lookup (DOC_ID from XML → Prestiž type) 2. Parser-determined (parser deduces itself) 3. Inference (regex on document number "FA/2024/001" → FA) ## Firebird Database Deep Dive ### Key Constraints and Pitfalls **KONTRAH.NAZWASKR** - UNIQUE(BAZAKONTRAH, NAZWASKR): ```sql -- Problem: "John Smith" already exists as TYPKONTRAH=1 (company) -- New "John Smith" as TYPKONTRAH=2 (individual) → CONSTRAINT VIOLATION -- Solution in procedure: IF (TYPKONTRAH = 2) THEN NAZWA = NAZWA || ' (' || NEW_NRKONTRAH || ')'; -- Result: "John Smith (10821)" ``` **NRKONTRAH Uniqueness**: ```sql -- Must be unique in KONTRAH (PK) AND in SLOWNIK (NRKSIEGOWY) -- Trigger KONTRAH_BI automatically inserts to SLOWNIK: CREATE TRIGGER KONTRAH_BI FOR KONTRAH BEFORE INSERT AS BEGIN INSERT INTO SLOWNIK (NRKSIEGOWY, ...) VALUES (NEW.NRKONTRAH, ...); END -- Generation requires checking BOTH tables: WHILE EXISTS(SELECT 1 FROM KONTRAH WHERE NRKONTRAH = :candidate) OR EXISTS(SELECT 1 FROM SLOWNIK WHERE NRKSIEGOWY = :candidate) DO candidate = candidate + 1; ``` **ID_DEFDOK Magic Numbers**: - 5 = Standard VAT invoice - 10 = Retail invoice (no VAT ID) ```csharp // After creating document for individual without VAT ID: if (parsedDoc.Contractor.Nip == null && docType.StartsWith("F")) { await _dbContext.Database.ExecuteSqlRawAsync( "UPDATE URZZEWNAGL SET ID_DEFDOK = 10 WHERE ID_URZZEWNAGL = {0}", docId ); } ``` ### ARIT_AUTOSTACJA_ADD_CONTRACTOR Procedure **Signature**: ```sql CREATE PROCEDURE ARIT_AUTOSTACJA_ADD_CONTRACTOR ( ID_ZRODLA INTEGER, NIP VARCHAR(25), -- digits only, no country prefix NIP_KRAJ CHAR(2), -- "PL", "DE", "CZ", ... NAZWA VARCHAR(240), MIEJSCOWOSC VARCHAR(100), ULICA VARCHAR(100), NRDOMU VARCHAR(20), NRLOKALU VARCHAR(20), KODPOCZTOWY VARCHAR(20), TELEFON VARCHAR(50), EMAIL VARCHAR(100), ID_KRAJ INTEGER, REGON VARCHAR(20), KONTR_RODZAJ VARCHAR(10), KOD_FK VARCHAR(50), KOD_EWIDENCYJNY VARCHAR(50), TYPKONTRAH SMALLINT -- 1=company, 2=individual ) RETURNS ( ID_KONTRAH INTEGER, ID_DANEKONTRAH INTEGER, STATUS VARCHAR(20), -- 'EXISTS', 'CREATED', 'FAILED' ERROR_MSG VARCHAR(500) ) ``` **Search Logic**: ```sql -- 1. If VAT ID not empty → search in DANEKONTRAH IF (NIP IS NOT NULL AND NIP <> '') THEN BEGIN SELECT FIRST 1 ID_KONTRAH, ID_DANEKONTRAH FROM DANEKONTRAH WHERE NIP = :NIP OR NIPW = :NIP_KRAJ || :NIP INTO :ID_KONTRAH, :ID_DANEKONTRAH; IF (ID_KONTRAH IS NOT NULL) THEN SUSPEND; -- return existing END -- 2. VAT ID empty or not found → create new -- Generate NRKONTRAH: CANDIDATE = (SELECT MAX(NRKONTRAH) FROM KONTRAH) + 1; WHILE EXISTS(...) DO CANDIDATE = CANDIDATE + 1; -- For individuals add number to name: IF (TYPKONTRAH = 2) THEN NAZWA = NAZWA || ' (' || CANDIDATE || ')'; INSERT INTO KONTRAH (...) VALUES (...); INSERT INTO DANEKONTRAH (...) VALUES (...); -- Trigger KONTRAH_BI automatically inserts to SLOWNIK ``` ### Country ISO2 → ID_KRAJ Mapping ```csharp // ImportMappers.cs private static readonly Dictionary CountryMapping = new() { ["PL"] = 1, ["AT"] = 10001, ["BE"] = 10002, ["DK"] = 10003, ["FI"] = 10004, ["FR"] = 10005, ["EL"] = 10006, ["ES"] = 10007, ["NL"] = 10008, ["IE"] = 10009, ["LU"] = 10010, ["DE"] = 10011, ["IT"] = 10012, ["PT"] = 10013, ["SE"] = 10014, ["GB"] = 10015, ["CY"] = 10016, ["CZ"] = 10017, ["EE"] = 10018, ["LT"] = 10019, ["LV"] = 10020, ["MT"] = 10021, ["SK"] = 10022, ["SI"] = 10023, ["HU"] = 10024 }; public static (string iso2, string digits) NormalizeVatIsoAndDigits(string nip) { // "DE123456789" → ("DE", "123456789") // "PL1234567890" → ("PL", "1234567890") // "123456" → ("PL", "123456") // fallback for Polish without prefix if (string.IsNullOrWhiteSpace(nip)) return ("PL", string.Empty); var match = Regex.Match(nip, @"^([A-Z]{2})([0-9]+)$"); if (match.Success) return (match.Groups[1].Value, match.Groups[2].Value); // Digits only → assume PL var digitsOnly = Regex.Replace(nip, @"[^0-9]", ""); return ("PL", digitsOnly); } ``` ## Contractor Logic - Decision Tree ### Document HAS VAT ID ```csharp // ContractorService.EnsureContractorExistsAsync() public async Task EnsureContractorExistsAsync( ContractorRef contractor, string documentType) { if (!string.IsNullOrWhiteSpace(contractor.Nip)) { // 1. Parse VAT ID: "DE123" → ("DE", "123") var (iso2, digits) = NormalizeVatIsoAndDigits(contractor.Nip); var idKraj = CountryIdByIso2(iso2); // 2. Call procedure with TYPKONTRAH=1 (company) var result = await _contractorProcs.AddContractorAsync( new AddContractorArgs( IdZrodla: _sourceId, Nip: digits, NipVatCountryIso2: iso2, Nazwa: contractor.Name, // ... address ... IdKraj: idKraj, TypKontrah: 1 // company ) ); // 3. Procedure returns existing or creates new if (result.Status == "FAILED") throw new InvalidOperationException(result.ErrorMsg); return result.IdKontrah; } // ... logic for no VAT ID ... } ``` ### Document WITHOUT VAT ID **Receipts (PAR/KPAR)**: ```csharp if (documentType.StartsWith("PAR") || documentType == "KPAR") { // Use retail contractor from configuration // appsettings.json: "DetailContractor": { "Id": 10818, "Code": "DETAL" } return await GetDetailContractorIdAsync(); } ``` **Retail invoices (FS/FA without VAT ID)**: ```csharp if (documentType.StartsWith("F")) // FA, FS, FV, FVAT { // Create individual (TYPKONTRAH=2) var result = await _contractorProcs.AddContractorAsync( new AddContractorArgs( // ... full data from XML ... TypKontrah: 2 // individual ) ); // Procedure adds " (NRKONTRAH)" to name automatically // Example: "John Smith" → "John Smith (10821)" return result.IdKontrah; } ``` **Other documents without VAT ID**: ```csharp // Cash/warehouse document without contractor return 1; // "one-time" contractor ID in Prestiž ``` **Post-processing for retail invoices**: ```csharp // DocumentProcessor.ProcessAsync() if (contractorWasCreatedAsIndividual && docType.StartsWith("F")) { // Change document definition to retail invoice await _dbContext.Database.ExecuteSqlRawAsync( "UPDATE URZZEWNAGL SET ID_DEFDOK = 10 WHERE ID_URZZEWNAGL = {0}", externalDocId ); // ID_DEFDOK=10 informs Prestiž this is invoice without VAT ID } ``` ## Supported Document Types ### Sales **FA/FVAT** - VAT Invoice: - With VAT ID → TYPKONTRAH=1, ID_DEFDOK=5 - Without VAT ID → TYPKONTRAH=2, ID_DEFDOK=10 - Gross prices in XML **FS** - Sales Invoice (simplified): - Analogous to FA **PAR** - Fiscal Receipt: - Always DetailContractor (ID from configuration) - ID_DEFDOK=10 - No contractor in header (may be DetailContractor in lines for other purposes) **KPAR/KORFAS** - Receipt/invoice correction: - Line mapping by ID_KARTOTEKA (not by line numbers!) - Prestiž changes order during realization: ```csharp // ZlPayloadBuilder.BuildCorrectionPositions() foreach (var xmlLine in correctionXml.Lines) { // XML: position 1, 2, 3 // Prestiž in base document: position 3, 1, 2 (sorted alphabetically) // Map by ID_KARTOTEKA instead of number: var baseDocLine = baseDocLines.FirstOrDefault( l => l.IdKartoteka == xmlLine.CatalogId ); if (baseDocLine == null) throw new Exception($"Line not found for catalog {xmlLine.CatalogId}"); yield return new CorrectionPosition { BaseLineNumber = baseDocLine.ActualLineNumber, // from Prestiž CorrectedQty = xmlLine.Qty, CorrectedPrice = xmlLine.Price }; } ``` ### Warehouse **PZ** - External Receipt: - NET prices in XML (exception!) - Supplier as contractor **WZ** - External Issue: - Recipient as contractor - Gross prices **PKW** - Inter-warehouse Transfer (value): - No contractor (source warehouse → destination) - Value transfer **PK** - Inter-warehouse Transfer (quantity): - Analogous to PKW but quantity only **ZPZ/ZWPZ** - Receipt order/return: - Buffer before actual PZ - Prestiž can realize as PZ ### Cash **KP** - Cash Receipt: - Requires open cash report (RAPORTKBI) - System inserts to buffer, Prestiž assigns report during realization - Contractor optional **KW** - Cash Disbursement: - Analogous to KP - Cash withdrawal ## Connection Pooling Crisis - Case Study ### Problem ``` System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host at FirebirdSql.Data.FbClient.GdsConnection.Receive() ``` Occurred randomly during batch processing of 10+ documents. ### Root Cause Analysis **Original architecture** (incorrect): ```csharp public class FirebirdContractorProcedures : IContractorProcedures { private readonly FirebirdDbContext _context; public async Task AddContractorAsync(AddContractorArgs args) { // ERROR: using connection from DbContext using var cmd = _context.Database.GetDbConnection().CreateCommand(); cmd.CommandText = "EXECUTE PROCEDURE ARIT_AUTOSTACJA_ADD_CONTRACTOR ..."; // ... } } ``` EF Core DbContext: - Holds one long-lived connection - Connection pooling managed by EF - Concurrent queries on same connection → race conditions ### Solution **New architecture**: ```csharp public abstract class FirebirdProcedureBase { private readonly string _connectionString; protected FirebirdProcedureBase(IConfiguration config) { _connectionString = config.GetConnectionString("Firebird") + ";ConnectionLifeTime=60"; // CRITICAL } protected FbConnection CreateConnection() { return new FbConnection(_connectionString); } } public class FirebirdContractorProcedures : FirebirdProcedureBase, IContractorProcedures { public async Task AddContractorAsync(AddContractorArgs args) { // Independent connection for EVERY procedure call using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = connection.BeginTransaction(); try { using var cmd = connection.CreateCommand(); cmd.Transaction = transaction; cmd.CommandText = "EXECUTE PROCEDURE ..."; // ... parameters ... using var reader = await cmd.ExecuteReaderAsync(); // ... read results ... transaction.Commit(); return result; } catch { transaction.Rollback(); throw; } } } ``` **ConnectionLifeTime=60**: - Firebird 3/4 has bug with long-lived connections - After 60s connection returns to pool and is renewed - Prevents "stale connection" errors ### Lessons Learned 1. **DON'T share connections** between EF Core and ADO.NET 2. **ConnectionLifeTime** is critical for Firebird 3. **Transactions** always for procedures modifying data 4. **using statements** everywhere - Firebird doesn't tolerate leaked connections ## XML Parsing Architecture ### Template Detection ```csharp public class TemplateDetector { public DocumentTemplate Detect(XDocument xml) { var root = xml.Root?.Name.LocalName; // Detection strategy by XML structure: if (root == "SalesInvoice" && xml.Descendants("InvoiceNumber").Any()) return DocumentTemplate.SpFvat; if (root == "WarehouseMovement" && xml.Descendants("MovementType").Any()) return DocumentTemplate.MgPkw; if (xml.Descendants("ZamowienieHeader").Any()) return DocumentTemplate.ZlOrder; throw new UnsupportedTemplateException($"Unknown XML structure: {root}"); } } ``` ### Parser Strategy ```csharp public interface IXmlParser { DocumentTemplate Template { get; } IEnumerable Parse(XDocument xml); } public class SpFvatParser : IXmlParser { public DocumentTemplate Template => DocumentTemplate.SpFvat; public IEnumerable Parse(XDocument xml) { var invoices = xml.Descendants("Invoice"); foreach (var inv in invoices) { yield return new ParsedDocument { DocumentType = inv.Element("Type")?.Value ?? "FA", DocumentNumber = inv.Element("Number")?.Value, DocumentDate = ParseDate(inv.Element("Date")?.Value), Contractor = new ContractorRef { Nip = inv.Element("BuyerNIP")?.Value, Name = inv.Element("BuyerName")?.Value, // ... full address ... }, Lines = inv.Descendants("Line").Select(line => new DocumentLine { CatalogCode = line.Element("ProductCode")?.Value, Name = line.Element("ProductName")?.Value, Qty = decimal.Parse(line.Element("Quantity")?.Value ?? "0"), PriceGross = decimal.Parse(line.Element("GrossPrice")?.Value ?? "0"), // PZ uses net, others gross: PriceNet = CalculateNetPrice(line, documentType) }).ToList() }; } } private decimal CalculateNetPrice(XElement line, string docType) { if (docType == "PZ") { // Warehouse receipt: XML contains net price return decimal.Parse(line.Element("NetPrice")?.Value ?? "0"); } else { // Invoices: calculate net from gross var gross = decimal.Parse(line.Element("GrossPrice")?.Value ?? "0"); var vatRate = decimal.Parse(line.Element("VATRate")?.Value ?? "23"); return gross / (1 + vatRate / 100); } } } ``` ### Document Type Resolution - Three Tiers ```csharp public class DocumentTypeResolver { private readonly IDocumentMatrixRepository _matrix; public string Resolve(ParsedDocument doc, XDocument originalXml) { // Tier 1: Matrix lookup (DOC_ID from XML → type in Prestiž) var docId = originalXml.Root?.Attribute("DOC_ID")?.Value; if (!string.IsNullOrEmpty(docId)) { var mapped = _matrix.GetTypeByDocId(docId); if (mapped != null) { _logger.Debug($"Matrix mapped DOC_ID={docId} → {mapped}"); return mapped; } } // Tier 2: Parser-determined (parser knows type from XML context) if (!string.IsNullOrEmpty(doc.DocumentType)) { _logger.Debug($"Parser determined type: {doc.DocumentType}"); return doc.DocumentType; } // Tier 3: Inference from document number var inferred = InferFromNumber(doc.DocumentNumber); _logger.Debug($"Inferred from number '{doc.DocumentNumber}': {inferred}"); return inferred; } private string InferFromNumber(string number) { // "FA/2024/001" → "FA" // "PZ/MAG1/123" → "PZ" // "2024/12/PAR/001" → "PAR" var match = Regex.Match(number, @"\b(FA|FS|FVAT|PZ|WZ|PAR|KP|KW|PKW|PK)\b"); return match.Success ? match.Value : "FA"; // fallback } } ``` ## Document Processing Pipeline ```csharp public class DocumentProcessor { private readonly IContractorService _contractorSvc; private readonly IItemService _itemSvc; private readonly IExternalDocProcedures _docProcs; private readonly FirebirdDbContext _context; public async Task ProcessAsync( ParsedDocument doc, string sourceFile) { _logger.Info($"Processing document {doc.DocumentNumber} from {sourceFile}"); try { // 1. Ensure contractor exists var contractorId = await _contractorSvc.EnsureContractorExistsAsync( doc.Contractor, doc.DocumentType ); _logger.Debug($"Contractor ID: {contractorId}"); // 2. Map catalog (all lines) var catalogMapping = new Dictionary(); foreach (var line in doc.Lines) { if (!catalogMapping.ContainsKey(line.CatalogCode)) { var itemId = await _itemSvc.EnsureItemExistsAsync( line.CatalogCode, line.Name, line.IsService ); catalogMapping[line.CatalogCode] = itemId; } } _logger.Debug($"Mapped {catalogMapping.Count} catalog items"); // 3. Create document header var docId = await _docProcs.CreateHeaderAsync(new CreateHeaderArgs { DocumentType = doc.DocumentType, DocumentNumber = doc.DocumentNumber, DocumentDate = doc.DocumentDate, ContractorId = contractorId, TotalGross = doc.Lines.Sum(l => l.Qty * l.PriceGross), TotalNet = doc.Lines.Sum(l => l.Qty * l.PriceNet), SourceId = _sourceId, UserId = _defaultUserId }); _logger.Info($"Created document header ID={docId}"); // 4. Add lines int lineNumber = 0; foreach (var line in doc.Lines) { lineNumber++; await _docProcs.AddLineAsync(new AddLineArgs { HeaderId = docId, LineNumber = lineNumber, ItemId = catalogMapping[line.CatalogCode], Qty = line.Qty, PriceNet = line.PriceNet, PriceGross = line.PriceGross, VatRate = line.VatRate }); } _logger.Debug($"Added {lineNumber} lines"); // 5. Post-processing for retail invoices if (contractorId != 1 && string.IsNullOrEmpty(doc.Contractor.Nip) && doc.DocumentType.StartsWith("F")) { await _context.Database.ExecuteSqlRawAsync( "UPDATE URZZEWNAGL SET ID_DEFDOK = 10 WHERE ID_URZZEWNAGL = {0}", docId ); _logger.Info($"Updated to retail invoice (ID_DEFDOK=10)"); } return ProcessResult.Success(docId); } catch (Exception ex) { _logger.Error(ex, $"Failed to process document {doc.DocumentNumber}"); return ProcessResult.Failure(ex.Message); } } } ``` ## Worker Service Architecture ```csharp public class MainWorker : BackgroundService { private readonly IMediator _mediator; private readonly SyncOptions _options; private FileSystemWatcher? _watcher; protected override async Task ExecuteAsync(CancellationToken ct) { _logger.Info($"Starting AutostacjaSync v{Assembly.GetExecutingAssembly().GetName().Version}"); _logger.Info($"Watch path: {_options.WatchPath}"); // FileSystemWatcher for immediate detection _watcher = new FileSystemWatcher(_options.WatchPath, "*.xml") { NotifyFilter = NotifyFilters.FileName | NotifyFilters.LastWrite, EnableRaisingEvents = true }; _watcher.Created += OnFileCreated; // Backup polling every N seconds (if watcher fails) while (!ct.IsCancellationRequested) { await ScanDirectoryAsync(); await Task.Delay( TimeSpan.FromSeconds(_options.PollingIntervalSeconds), ct ); } } private void OnFileCreated(object sender, FileSystemEventArgs e) { _logger.Debug($"FileSystemWatcher detected: {e.Name}"); _ = ProcessFileAsync(e.FullPath); // fire and forget } private async Task ScanDirectoryAsync() { var files = Directory.GetFiles(_options.WatchPath, "*.xml"); _logger.Debug($"Polling scan found {files.Length} XML files"); foreach (var file in files) { await ProcessFileAsync(file); } } private async Task ProcessFileAsync(string filePath) { try { // Wait for file to be fully written (antivirus, network delays) await WaitForFileReady(filePath); var command = new ProcessLocalFileCommand(filePath); var result = await _mediator.Send(command); if (result.Success) { var archivePath = Path.Combine( _options.ArchivePath, $"{Path.GetFileNameWithoutExtension(filePath)}_" + $"{DateTime.Now:yyyyMMdd_HHmmss}{Path.GetExtension(filePath)}" ); File.Move(filePath, archivePath); _logger.Info($"Archived: {archivePath}"); } else { var failedPath = Path.Combine( _options.FailedPath, Path.GetFileName(filePath) ); File.Move(filePath, failedPath); _logger.Error($"Failed, moved to: {failedPath}. Error: {result.ErrorMessage}"); } } catch (Exception ex) { _logger.Error(ex, $"Error processing file {filePath}"); } } private async Task WaitForFileReady(string filePath, int maxWaitMs = 5000) { var waited = 0; while (waited < maxWaitMs) { try { using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.None); return; // File ready } catch (IOException) { await Task.Delay(100); waited += 100; } } throw new TimeoutException($"File {filePath} not ready after {maxWaitMs}ms"); } } ``` ## Production Challenges ### Environment-Specific Data Inconsistencies Identical code, different behavior DEV vs PROD: **Problem**: ARIT_AUTOSTACJA_ADD_CONTRACTOR procedure works in PROD, fails in DEV **Root cause**: ```sql -- DEV: KRAJ table has gaps in ID_KRAJ SELECT ID_KRAJ, KODKRAJU_VAT_UE FROM KRAJ ORDER BY ID_KRAJ; -- 1 (PL), 10001 (AT), missing 10011 (DE) ← MISSING! -- PROD: Complete table -- 1 (PL), 10001 (AT), 10011 (DE), ... -- C# code: var idKraj = CountryIdByIso2("DE"); // 10011 // DEV: FK constraint violation, ID=10011 missing in KRAJ // PROD: OK ``` **Solution**: Check before calling procedure ```csharp var idKraj = CountryIdByIso2(iso2); if (idKraj != 1) // not PL { var exists = await _context.Kraj .AnyAsync(k => k.IdKraj == idKraj); if (!exists) { _logger.Warning($"Country {iso2} (ID={idKraj}) missing in DEV, fallback to PL"); idKraj = 1; } } ``` ### Cash Document Validation KP/KW documents require open cash report: ```csharp // Problem: How to find open report for document date? // RAPORTKBI.ZAMKNIETY = 0 AND DATAOD <= DocDate <= DATADO // Solution: DON'T validate in synchronization // Prestiž will assign report during document realization // If no open report → user gets error in Prestiž public async Task CreateCashDocumentAsync(CreateCashDocArgs args) { // Insert to buffer WITHOUT checking RAPORTKBI await _docProcs.CreateHeaderAsync(new CreateHeaderArgs { DocumentType = args.Type, // KP or KW // ... other fields ... // No ID_RAPORTKBI - Prestiž will fill }); _logger.Info($"Cash document queued, Prestiž will assign report on realization"); } ``` ### Correction Document Mapping Hell **Problem**: Correction XML has lines 1,2,3. Prestiž in base document has 3,1,2. **Why?**: Prestiž sorts lines alphabetically by product name during realization. **Solution**: Map by ID_KARTOTEKA ```csharp public async Task> MapCorrectionLines( int baseDocId, List xmlLines) { // 1. Get actual lines from realized document var baseLines = await _context.URZZEWNPOZ .Where(p => p.ID_URZZEWNAGL == baseDocId) .OrderBy(p => p.LP) // LP = line position in Prestiž .Select(p => new { p.LP, p.ID_KARTOTEKA, p.ILOSC, p.CENA }) .ToListAsync(); // 2. Map XML by ID_KARTOTEKA (not by LP!) var mappings = new List(); foreach (var xmlLine in xmlLines) { var baseLine = baseLines.FirstOrDefault( bl => bl.ID_KARTOTEKA == xmlLine.CatalogId ); if (baseLine == null) throw new InvalidOperationException( $"Product {xmlLine.CatalogCode} not found in base document {baseDocId}" ); mappings.Add(new CorrectionLineMapping { BaseLineNumber = baseLine.LP, // Position in Prestiž XmlLineNumber = xmlLine.LineNumber, // Position in XML CatalogId = xmlLine.CatalogId, BaseQty = baseLine.ILOSC, BasePrice = baseLine.CENA, CorrectedQty = xmlLine.Qty, CorrectedPrice = xmlLine.Price, QtyDelta = xmlLine.Qty - baseLine.ILOSC, PriceDelta = xmlLine.Price - baseLine.CENA }); } return mappings; } ``` ## Configuration and Deployment ### appsettings.json ```json { "ConnectionStrings": { "Firebird": "Server=192.168.1.10;Port=3050;Database=C:\\Prestiz\\DATA\\PRESTIZ.FDB;User=SYSDBA;Password=masterkey;Charset=UTF8;ConnectionLifeTime=60" }, "Sync": { "WatchPath": "\\\\file-server\\AutoSync\\IMP_FK", "ArchivePath": "\\\\file-server\\AutoSync\\IMP_FK\\Archive", "FailedPath": "\\\\file-server\\AutoSync\\IMP_FK\\Failed", "PollingIntervalSeconds": 15, "SourceId": 1, "DefaultUserId": "MWD" }, "DetailContractor": { "Id": 10818, "Code": "DETAL", "Name": "Retail Customer" }, "Cash": { "DefaultCashCode": "1", "DefaultCashName": "Main Cash" }, "ExternalDocuments": { "DefaultUserCode": "MWD", "RelatedDocCechaMapping": { "FA": "CECHA2", "PZ": "CECHA1" } }, "NLog": { "targets": { "file": { "type": "File", "fileName": "${basedir}/logs/sync-${shortdate}.log", "archiveEvery": "Day", "archiveNumbering": "Date", "maxArchiveFiles": 30 } }, "rules": [ { "logger": "*", "minLevel": "Debug", "writeTo": "file" } ] } } ``` ### Windows Service Installation ```powershell # Build Release dotnet publish -c Release -r win-x64 --self-contained true # Install as Windows Service sc.exe create AutostacjaSync binPath= "C:\Services\AutostacjaSync\AutostacjaSync.Host.exe" sc.exe description AutostacjaSync "XML document synchronization to Prestiž ERP" sc.exe start AutostacjaSync # Auto-start on boot sc.exe config AutostacjaSync start= auto # Service account (instead of LocalSystem) sc.exe config AutostacjaSync obj= "DOMAIN\\SvcAritSync" password= "..." ``` ### Monitoring ```csharp // HealthCheck endpoint (optional) public class SyncHealthCheck : IHealthCheck { private readonly FirebirdDbContext _context; public async Task CheckHealthAsync(HealthCheckContext context) { try { // Test DB connectivity await _context.Database.ExecuteSqlRawAsync("SELECT 1 FROM RDB$DATABASE"); // Test filesystem access var testFile = Path.Combine(_options.WatchPath, ".health"); File.WriteAllText(testFile, DateTime.Now.ToString()); File.Delete(testFile); return HealthCheckResult.Healthy("All systems operational"); } catch (Exception ex) { return HealthCheckResult.Unhealthy("System failure", ex); } } } ``` ## Performance Characteristics - **Throughput**: ~50 documents/minute (average document 5-10 lines) - **Latency**: 200-500ms from file detection to database write - **Memory**: ~80MB RAM baseline, +5MB per concurrent document - **Database load**: 3-5 queries per document (contractor, catalog, header, lines) - **Connection pool**: Max 10 connections (Firebird limit), average 2-3 active ## Lessons Learned & Best Practices 1. **Firebird Connections**: Always independent connections for procedures, ConnectionLifeTime=60 2. **VAT ID Handling**: Always parse to (ISO2, digits), validate ID_KRAJ before FK insert 3. **Unique Constraints**: For TYPKONTRAH=2 always add number to name 4. **Document Corrections**: Map by ID_KARTOTEKA, never by line numbers 5. **Transaction Scope**: Each procedure in separate transaction, rollback on exception 6. **Logging**: Debug level in DEV, Info in PROD, Error always with full stack trace 7. **File Watching**: FileSystemWatcher + polling backup (network shares may lose events) 8. **Error Handling**: Failed files to separate directory with timestamp, never delete 9. **Environment Parity**: DEV DB must have complete dictionaries (KRAJ, DEFDOK, etc.) 10. **Cash Documents**: Don't validate RAPORTKBI in synchronization, Prestiž will do it ## Tech Stack - Complete List **Backend**: - .NET 8 (C# 12) - Worker Service (BackgroundService) - MediatR 12.x (CQRS) - LinqToDB 5.x (for Firebird-specific queries) - EF Core 8.x (read-only dictionaries only) - NLog 5.x **Database**: - Firebird 3.0 / 4.0 - FirebirdSql.Data.FirebirdClient 10.x - ADO.NET for stored procedures **Testing** (planned): - xUnit - Moq - Testcontainers.Firebird **Infrastructure**: - Windows Server 2019+ - Network share (SMB) for watched directory - Windows Service hosting ## Potential Improvements - [ ] Retry policy for transient Firebird errors (Polly) - [ ] Batch processing for large imports (>100 documents) - [ ] Health check HTTP endpoint - [ ] Metrics (Prometheus exporter) - [ ] Document preview before import (optional validation) - [ ] Web UI for browsing synchronization history - [ ] Support for other ERPs (SAP, Comarch XL) via adapter pattern - [ ] E2E tests with Testcontainers + sample XMLs