Skip to main content

Example 09: Complex Data Transformation Pipeline

Overview​

The dashboard implements sophisticated data transformation pipelines that convert between SYSPRO database formats, business objects, view models, and XML structures for integration, ensuring data integrity and type safety throughout.

Transformation Architecture​

Multi-Layer Transformation​

// Database Entity → Business Model → ViewModel → UI
public class TransformationPipeline
{
    // Level 1: Database to Business Model
    public InventoryItem TransformFromDatabase(CG_InventoryOrdering_View dbEntity)
    {
        return new InventoryItem
        {
            StockCode = dbEntity.StockCode,
            Description = dbEntity.Description,
            Warehouse = dbEntity.Warehouse,
            QtyToOrder = dbEntity.QtyToOrder ?? 0,
            QtyOnHand = dbEntity.QtyOnHand ?? 0,
            SafetyStock = dbEntity.SafetyQty ?? 0,
            MinimumQty = dbEntity.MinQty ?? 0,
            ReorderQty = dbEntity.ReOrderQty ?? 0,
            Supplier = new SupplierInfo
            {
                Code = dbEntity.Supplier,
                Name = dbEntity.SupplierName,
                Currency = dbEntity.Currency ?? "$"
            }
        };
    }
    
    // Level 2: Business Model to ViewModel
    public WarehouseOrderingListItem TransformToViewModel(InventoryItem item)
    {
        return new WarehouseOrderingListItem
        {
            StockCode = item.StockCode,
            StockDescription = item.Description,
            Warehouse = item.Warehouse,
            SelectedOrderQty = CalculateOrderQuantity(item),
            OrderQty = item.QtyToOrder,
            QtyOnHand = item.QtyOnHand,
            Supplier = item.Supplier.Code,
            SupplierName = item.Supplier.Name,
            LastPricePaid = FormatCurrency(item.LastPrice, item.Supplier.Currency),
            IsSelected = false,
            CanEdit = !item.IsOnHold
        };
    }
    
    // Level 3: ViewModel to Integration Format
    public XElement TransformToXml(WarehouseOrderingListItem item)
    {
        return new XElement("StockLine",
            new XElement("StockCode", item.StockCode),
            new XElement("Warehouse", item.Warehouse),
            new XElement("OrderQty", item.SelectedOrderQty),
            new XElement("OrderUom", item.OrderUom ?? "EA"),
            new XElement("Price", item.Price),
            new XElement("PriceUom", item.PriceUom),
            new XElement("TaxCode", item.TaxCode ?? ""),
            new XElement("DueDate", item.DueDate?.ToString("yyyy-MM-dd") ?? "")
        );
    }
}

Order Consolidation Transform​

public class OrderConsolidationTransformer
{
    public IEnumerable<SummaryOrder> Transform(
        IEnumerable<InvOrderingOrderSummaryListItem> items,
        ConsolidationSettings settings)
    {
        // Group items based on settings
        var groups = ApplyGrouping(items, settings);
        
        return groups.Select(group => new SummaryOrder
        {
            Supplier = group.Key.Supplier,
            SupplierName = group.First().SelectedSupplier.SupplierName,
            Warehouse = settings.OrderByWarehouse ? group.Key.Warehouse : null,
            DueDate = settings.OrderByDueDate ? group.Key.DueDate : null,
            OrderLines = TransformOrderLines(group)
        });
    }
    
    private IEnumerable<IGrouping<dynamic, InvOrderingOrderSummaryListItem>> 
        ApplyGrouping(IEnumerable<InvOrderingOrderSummaryListItem> items, 
                     ConsolidationSettings settings)
    {
        if (settings.OrderByWarehouse && settings.OrderByDueDate)
        {
            return items.GroupBy(x => new { 
                x.SelectedSupplier.Supplier, 
                x.DefaultWarehouse, 
                x.SelectedDueDate 
            });
        }
        else if (settings.OrderByWarehouse)
        {
            return items.GroupBy(x => new { 
                x.SelectedSupplier.Supplier, 
                x.DefaultWarehouse,
                SelectedDueDate = (DateTime?)null
            });
        }
        else if (settings.OrderByDueDate)
        {
            return items.GroupBy(x => new { 
                x.SelectedSupplier.Supplier,
                DefaultWarehouse = (string)null,
                x.SelectedDueDate 
            });
        }
        else
        {
            return items.GroupBy(x => new { 
                x.SelectedSupplier.Supplier,
                DefaultWarehouse = (string)null,
                SelectedDueDate = (DateTime?)null
            });
        }
    }
    
    private List<SummaryOrderLine> TransformOrderLines(
        IEnumerable<InvOrderingOrderSummaryListItem> items)
    {
        return items.Select(item => new SummaryOrderLine
        {
            Warehouse = item.DefaultWarehouse,
            StockCode = item.StockCode,
            StockDescription = item.StockDescription,
            OrderQty = ConvertQuantity(item.OrderQty, item.OrderUom, item.StockUom),
            OrderUom = item.OrderUom,
            Price = ConvertPrice(item.Price, item.PriceUom, item.OrderUom),
            PriceUom = item.PriceUom,
            DueDate = item.SelectedDueDate,
            TaxCode = item.TaxCode
        }).ToList();
    }
}

Unit of Measure Conversions​

UOM Transformation Service​

public class UomConversionService
{
    private readonly Dictionary<string, decimal> _conversionFactors;
    
    public decimal ConvertQuantity(
        decimal quantity, 
        string fromUom, 
        string toUom)
    {
        if (fromUom == toUom)
            return quantity;
            
        // Get conversion factor
        var factor = GetConversionFactor(fromUom, toUom);
        return quantity * factor;
    }
    
    private decimal GetConversionFactor(string fromUom, string toUom)
    {
        // Check direct conversion
        var key = $"{fromUom}:{toUom}";
        if (_conversionFactors.ContainsKey(key))
            return _conversionFactors[key];
            
        // Check inverse conversion
        var inverseKey = $"{toUom}:{fromUom}";
        if (_conversionFactors.ContainsKey(inverseKey))
            return 1 / _conversionFactors[inverseKey];
            
        // Use base unit conversion
        return ConvertThroughBase(fromUom, toUom);
    }
}

Currency Transformations​

Multi-Currency Handling​

public class CurrencyTransformer
{
    public decimal ConvertToLocal(
        decimal amount, 
        string currency, 
        decimal exchangeRate, 
        string mulDiv)
    {
        if (currency == "$" || exchangeRate == 1)
            return amount;
            
        return mulDiv == "M" 
            ? amount * exchangeRate 
            : amount / exchangeRate;
    }
    
    public string FormatCurrency(decimal amount, string currency)
    {
        var culture = GetCultureForCurrency(currency);
        return amount.ToString("C", culture);
    }
}

XML Transformation​

SYSPRO XML Generation​

public class SysproXmlTransformer
{
    public XDocument TransformToPostXml(SummaryOrder order)
    {
        return new XDocument(
            new XElement("PostPurchaseOrders",
                new XElement("Item",
                    new XElement("Supplier", order.Supplier),
                    new XElement("Warehouse", order.Warehouse ?? ""),
                    new XElement("OrderDate", DateTime.Now.ToString("yyyy-MM-dd")),
                    new XElement("DueDate", order.DueDate?.ToString("yyyy-MM-dd") ?? ""),
                    new XElement("Buyer", order.Buyer),
                    new XElement("StockLines",
                        order.OrderLines.Select(TransformStockLine)
                    )
                )
            )
        );
    }
    
    private XElement TransformStockLine(SummaryOrderLine line)
    {
        return new XElement("StockLine",
            new XElement("StockCode", line.StockCode),
            new XElement("Warehouse", line.Warehouse),
            new XElement("OrderQty", line.OrderQty.ToString("F3")),
            new XElement("OrderUom", line.OrderUom),
            new XElement("Price", line.Price.ToString("F5")),
            new XElement("PriceUom", line.PriceUom),
            new XElement("TaxCode", line.TaxCode ?? ""),
            new XElement("DueDate", line.DueDate?.ToString("yyyy-MM-dd") ?? "")
        );
    }
}

Response Parsing​

XML to Object Transformation​

public class ResponseTransformer
{
    public OrderPostResult TransformResponse(string xmlResponse)
    {
        var doc = XDocument.Parse(xmlResponse);
        
        // Check for errors
        var errors = doc.Descendants("ErrorMessage")
            .Select(e => e.Value)
            .ToList();
            
        if (errors.Any())
        {
            return new OrderPostResult
            {
                Success = false,
                Errors = errors
            };
        }
        
        // Extract success data
        return new OrderPostResult
        {
            Success = true,
            OrderNumber = doc.Descendants("PurchaseOrder").FirstOrDefault()?.Value,
            TotalValue = decimal.Parse(doc.Descendants("TotalValue").FirstOrDefault()?.Value ?? "0"),
            LineCount = int.Parse(doc.Descendants("NumberOfLines").FirstOrDefault()?.Value ?? "0")
        };
    }
}

Validation During Transformation​

Transform Validation​

public class ValidatingTransformer<TSource, TTarget>
{
    private readonly ITransformer<TSource, TTarget> _transformer;
    private readonly IValidator<TSource> _sourceValidator;
    private readonly IValidator<TTarget> _targetValidator;
    
    public TransformResult<TTarget> Transform(TSource source)
    {
        // Validate source
        var sourceValidation = _sourceValidator.Validate(source);
        if (!sourceValidation.IsValid)
        {
            return new TransformResult<TTarget>
            {
                Success = false,
                Errors = sourceValidation.Errors
            };
        }
        
        // Transform
        TTarget target;
        try
        {
            target = _transformer.Transform(source);
        }
        catch (Exception ex)
        {
            return new TransformResult<TTarget>
            {
                Success = false,
                Errors = new[] { $"Transformation failed: {ex.Message}" }
            };
        }
        
        // Validate target
        var targetValidation = _targetValidator.Validate(target);
        return new TransformResult<TTarget>
        {
            Success = targetValidation.IsValid,
            Data = target,
            Errors = targetValidation.Errors
        };
    }
}

Benefits​

  • Type safety throughout pipeline
  • Clear separation of concerns
  • Validation at each level
  • Reusable transformation logic
  • Easy testing of transformations
  • Performance optimization opportunities