The Flat-File Society Does XML

A little while back, Phil introduced us to a group of developers from The Flat File Society who were forced to do relational database. Phil is still at the client site and was tapped to lend a hand with a communication project the programmers were struggling with. They were able to get their data in XML format, but the vendor still rejected it, despite the fact that they said they could transform one XML to another without problems ...

<?xml version="1.0" encoding="UTF-8"?>
<converter>
  <orderspec>
    [FlexibleOrderReader]
    algorithm = orderalgo.ini
    step = no

    ; Order algorithm for TPS Order Package

    [HEADER]
    ;beginning of order message

    format = order.PositionFormat
    recognizer = order.StartsWithRecognizer
    recognizedBy = HEADER

    ;Order Date in yyyyMMdd format
    ORDERDATE = 7,8
    SUPPLIER_ID = 15,8
    CUSTOMER_ID = 23,8
    ACCOUNT_NUMBER = 31,10
    CUSTOMER_CONTACT = 41,20
    PO_NUMBER = 61,15
    ORDERTYPE = 76,6
    SHIPTOCODE = 82,5
    SHIPTONAME = 87,30
    SHIPTOADDRESS1 = 117,30
    SHIPTOADDRESS2 = 147,30
    SHIPTOCITY = 177,30
    SHIPTOCOUNTY = 207,30
    SHIPTOZIP = 237,10
    SHIPTOCOUNTRY = 247,30
    SHIPVIACODE = 277,5
    HEADERNOTES = 282,250

    [LINE]
    ;order Line

    format = order.PositionFormat
    recognizer = order.StartsWithRecognizer
    recognizedBy = LINE

    CUST_LINENUMBER = 7,3
    CUSTOMER_PARTNUMBER = 10,30
    SUPPLIER_PARTNUMBER = 40,30
    PART_DESCRIPTION = 70,30
    PACKAGE_CODE = 100,2
    ;Quantity with no seperators, right formatted
    REQUIRED_QTY = 102,10
    ;Delivery Date in yyyyMMdd format
    REQUIRED_DATE = 112,8
    DISPATCH_POINT_CODE = 120,1
    ;Price with 2 decimal places, and . as seperator, right formatted
    PRICE = 121,8
    ;Additional Product Reference not yet mapped
    PRODUCT_REFERENCE1 = 129,20
    PRODUCT_REFERENCE1 = 149,20
    LINENOTES = 169,250
  </orderspec>
  <orderalgo>
    Format HEADER
    Format LINE

    Order order
    Line line

    Record recHEADER
    Record recLINE

    Field dash
    Field ENDID
    ; Field OTHER used for copying Vendor Part Number to Manufacturer Part Number if required
    Field OTHER
    Field SUPPLIER

    set dash = word -
    set OTHER = word OTHER
    if next is HEADER
      clear order
      read recHEADER

      
      copy ORDERDATE in recHEADER to ORDEREDDATETIMEYEAR in order
      reformat ORDEREDDATETIMEYEAR in order from yyyyMMdd to yyyy
      copy ORDERDATE in recHEADER to ORDEREDDATETIMEMONTH in order
      reformat ORDEREDDATETIMEMONTH in order from yyyyMMdd to MM
      copy ORDERDATE in recHEADER to ORDEREDDATETIMEDAY in order
      reformat ORDEREDDATETIMEDAY in order from yyyyMMdd to dd

      copy SUPPLIER_ID in recHEADER to ORDERTYPE in order
      copy SUPPLIER_ID in recHEADER to CUSTOMERACCOUNTID in order
      copy SUPPLIER_ID in recHEADER to SUPPLIERID in order
      copy SUPPLIER_ID in recHEADER to SUPPLIERCOMPANYNAME in order
      copy SUPPLIER_ID in recHEADER to SUPPLIER

      copy CUSTOMER_ID in recHEADER to CUSTOMERCOMPANYNAME in order

      copy ACCOUNT_NUMBER in recHEADER to CUSTOMERACCOUNTID in order

      copy CUSTOMER_CONTACT in recHEADER to CUSTOMERORDEREDBY in order
      
      copy PO_NUMBER in recHEADER to PONUMBER in order

      extend ORDERTYPE in order with dash
      extend ORDERTYPE in order with ORDERTYPE in recHEADER

      copy SHIPTOCODE in recHEADER to SHIPTOCODE in order
      copy SHIPTOADDRESS1 in recHEADER to SHIPTOADDRESSLINE1 in order
      copy SHIPTOADDRESS2 in recHEADER to SHIPTOADDRESSLINE2 in order
      copy SHIPTOCITY in recHEADER to SHIPTOCITY in order
      copy SHIPTOCOUNTY in recHEADER to SHIPTOCOUNTY in order
      copy SHIPTOZIP in recHEADER to SHIPTOPOSTALCODE in order
      copy SHIPTOCOUNTRY in recHEADER to SHIPTOCOUNTRY in order

      copy SHIPVIACODE in recHEADER to SHIPVIACODE in order

      copy HEADERNOTES in recHEADER to NOTES in order

    ; ENDID based on order number, registration date and customer code

      copy PO_NUMBER in recHEADER to ENDID in order     
      extend ENDID in order with ORDERDATE in recHEADER
      extend ENDID in order with CUSTOMER_ID in recHEADER     

      while next is LINE
        clear line
        read recLINE

        copy CUST_LINENUMBER in recLINE to CUSTOMERLINENUMBER in line

        copy CUSTOMER_PARTNUMBER in recLINE to CUSTOMERPARTNUMBER in line

        if not equal SUPPLIER and OTHER
          copy SUPPLIER_PARTNUMBER in recLINE to VENDORPARTNUMBER in line
        endif
        if equal SUPPLIER and OTHER
          copy VENDOR_PARTNUMBER in recLINE to MANUFACTURERPARTNUMBER in line
        endif
        
        copy PART_DESCRIPTION in recLINE to PARTDESCRIPTION in line

        copy PACKAGE_CODE in recLINE to PACKINGCODE in line

        copy REQUIRED_QTY in recLINE to REQUIREDQUANTITY in line
        
        copy REQUIRED_DATE in recLINE to REQUIREDDATETIMEYEAR in line
        reformat REQUIREDDATETIMEYEAR in line from yyyyMMdd to yyyy
        copy REQUIRED_DATE in recLINE to REQUIREDDATETIMEMONTH in line
        reformat REQUIREDDATETIMEMONTH in line from yyyyMMdd to MM
        copy REQUIRED_DATE in recLINE to REQUIREDDATETIMEDAY in line
        reformat REQUIREDDATETIMEDAY in line from yyyyMMdd to dd

        copy DISPATCH_POINT_CODE in recLINE to WAREHOUSECODE in line
        
        copy PRICE in recLINE to UNITOPERAMT in line
        
        copy LINENOTES in recLINE to NOTES in line

        copy ENDID in order to ENDID in line

        add line to order
      endwhile
      save order
    endif
  </orderalgo>
</converter>