# Fri, 3 Jul 2026 11:20:33 GMT
---
version: 23
data:


- type: Credential
  attributes:
    displayName: XDM Lookup Table Credential
    description: Credential for XDM Lookup Table
    user: sa
    password: ""

- type: MappingTableContainer
  attributes:
    displayName: XDM Risk Lookup Table
    description: Mapping table to use the xdm risk lookup table
    variableName: risklookupTable
    credential: XDM Lookup Table Credential

- type: ModificationMethod
  attributes:
    displayName: RiskClasses
    description: |
          Masks address data while preserving similar hazard zone or risk class classifications.
          This method requires the lookup table RiskByAddresses to be installed and populated with
          address and risk class data.
    scriptLanguage: "GROOVY"
    legacyScript: false
    inputType: "COLUMN"
    mappingTableContainers:
      - "XDM Risk Lookup Table"
    mappingTableAccessMode: READ
    code: |
        /*
         * *****************************************************************************
         * The following variables are available in this script:
         * =====================================================
         * ctx             : A context object that offers a number of methods.
         *                   The method indexOf() can be used to get the index of a
         *                   specific column within the dataArray.
         * data            : The data array. This array contains the contents of all
         *                   columns of the current row of the table that is being
         *                   masked by this method.
         *
         * Additionally for column based modification methods the following two
         * variables are available:
         * columnName      : The name of the column that matched the pattern.
         * columnIndex     : The position of the column that matched the pattern within
         *                   the data array. This value is zero-based.
         * *****************************************************************************
         */
        
        import de.ubs.xdm3.batch.modification.ModificationException
        import de.ubs.xdm3.batch.modification.HashUtils
        import groovy.sql.Sql
        
        xdmLookupTable = 'riskByAddresses'
        
        
        //index columns to mask
        gk = ctx.indexOf('gk')
        zone = ctx.indexOf('zone')
        sgk = ctx.indexOf('sgk')
        ems = ctx.indexOf('ems')
        slz = ctx.indexOf('slz')
        ldv = ctx.indexOf('ldv')
        
        zip = ctx.indexOf('zip')
        city = ctx.indexOf('city')
        street = ctx.indexOf('street')
        housenumber = ctx.indexOf('housenumber')
        
        
        /*
         * This function is called once before the processing starts. The method
         * is called once per table.
         */
        def init() {
        }
        
        /*
         * This function should modify the row that is provided by the dataArray. The
         * method is called for each single row of the table.
         */
        def apply() {
            def sql = Sql.newInstance(risklookupTable)
        
            // Check 1: each address sould have data for all possible risk classes
            def fields_str =  ['gk', 'zone', 'sgk', 'ems', 'slz', 'ldv']
            def fields_idx =  [gk, zone, sgk, ems, slz, ldv]
        
            if (fields_idx.any { !data[it] }) {
                // ++++ statement to get identical address -> update in case of different data structure
                def stmt_risk = """
                    SELECT gk, zone, sgk, ems, slz, ldv  
                    FROM ${xdmLookupTable} 
                    WHERE zip = ? 
                      AND city = ? 
                      AND street = ? 
                      AND housenumber = ?
                 """
        
                // Only one row exist with identical address
                def result_address = sql.rows(stmt_risk, [
                        data[zip],
                        data[city],
                        data[street],
                        data[housenumber]
                ])
        
                if (!result_address) {
                    throw new ModificationException("No Data for this address in lookup Table" );
                }else{
                    result_address.each { row ->
                        for (int i = 0; i < fields_str.size(); i++) {
        
                            def f = fields_str[i]
                            def f2 = fields_idx[i]
        
                            if (!data[f2]) {
                                data[f2] = row[i]
                            }
                        }
                    }
                }
            }
        
            // Check 2: get an address from lookupTable, which have identical risk classes
            // ++++ statement to get identical addresses -> update in case of different data structure
            def stmt_getAddress = """SELECT zip, city, street, housenumber FROM ${xdmLookupTable} WHERE gk  = ?
                  AND zone = ?
                  AND sgk = ? 
                  AND ems = ?
                  AND slz = ?
                  AND ldv = ?
               """
        
            def result_byrisk = sql.rows(stmt_getAddress, [
                    data[gk],
                    data[zone],
                    data[sgk],
                    data[ems],
                    data[slz],
                    data[ldv]
            ])
        
            // no address found -> thrown exception
            if (!result_byrisk || result_byrisk.size() < 1) {
                throw new ModificationException("No Data available")
            }
        
            result_byrisk.each{row -> println(row)}
        
            def key = "${data[zip]?.toString()?.trim()}${data[city]?.toString()?.trim()}${data[street]?.toString()?.trim()}${data[housenumber]?.toString()?.trim()}"
            def seed = HashUtils.hashStringLikeInt(key)
        
            def new_data = null
            for (int i = 0; i < result_byrisk.size(); i++) {
        
                def index = Math.floorMod(seed + i, result_byrisk.size())
        
                def candidate = result_byrisk[index]
        
                // normalize data
                def original = [
                        data[zip]?.toString()?.trim(),
                        data[city]?.toString()?.trim(),
                        data[street]?.toString()?.trim(),
                        data[housenumber]?.toString()?.trim()
                ]
        
                def candidateAddress = [
                        candidate[zip]?.toString()?.trim(),
                        candidate[city]?.toString()?.trim(),
                        candidate[street]?.toString()?.trim(),
                        candidate[housenumber]?.toString()?.trim()
                ]
        
                // use only in case of other data
                if (candidateAddress != original) {
                    new_data = candidate
                    break
                }
            }
        
            // update data
            data[zip] = new_data[0]
            data[city] = new_data[1]
            data[street] = new_data[2]
            data[housenumber] = new_data[3]
        
            return true
        }
        
        /*
         * This function is executed once after the modification of a table is complete. The method
         * is called once per table.
         */
        def close() {
        }
    parameters:
    - displayName: City
      description: |
          Address component: City
      variableName: city
      dataType: STRING
    - displayName: Zip
      description: |
          Address component: ZipCode
      variableName: zip
      dataType: STRING
    - displayName: Street
      description: |
          Address component: Street
      variableName: street
      dataType: STRING
    - displayName: House number
      description: |
          Address component: House number
      variableName: housenumber
      dataType: STRING
    - displayName: GK
      description: |
          Flood Hazard Classes (1 -4)
      variableName: gk
      dataType: STRING
    - displayName: SGK
      description: |
          Heavy rain class (1 - 3)
      variableName: sgk
      dataType: STRING
    - displayName: Zone
      description: |
          Zone of stream (B), coastal (K), island (I)
      variableName: zone
      dataType: STRING
    - displayName: EMS
      description: |
          Seismic hazard estimation (1 - 12).
      variableName: ems
      dataType: STRING
    - displayName: SLZ
      description: |
          Snow load zones (1, 1a, 2, 2a, 3, 3a).
      variableName: slz
      dataType: STRING
    - displayName: LDV
      description: |
          Lightning density (0 - 4).
      variableName: ldv
      dataType: STRING
