elwig-misc/tools/wgw-fb.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import argparse
import openpyxl


KG_MAP = {
    'Dobermannsdorf': 6104,
    'Gaiselberg': 6109,
    'Gösting': 6110,
    'Groß Inzersdorf': 6113,
    'Groß Krut': 15111,
    'Hausbrunn': 15114,
    'Hauskirchen': 6111,
    'Herrnbaumgarten': 15115,
    'Jedenspeigen': 6114,
    'Obersiebenbrunn': 6217,
    'Loidesthal': 6115,
    'Zistersdorf': 6128,
    'Maustrenk': 6116,
    'Niederabsdorf': 6101,
    'Obersulz': 6125,
    'Palterndorf': 6119,
    'Prinzendorf': 6120,
    'Sierndorf': 6123,
    'Windisch-Baumgarten': 6102,
}

RD_MAP = {
    ('Schilling', 6117): 1,
    ('Kreiten', 6103): 1,
    ('Feldweingarten', 6103): 2,
    ('Hausweingarten', 6103): 3,
    ('Sommerberg', 6103): 4,
    ('Schilling', 6104): 1,
    ('Mittlere Bloten', 6104): 2,
    ('Schotter', 6104): 3,
    ('Obere Bloten', 6104): 4,
    ('Oberer Sand', 6104): 5,
    ('Kirchenried', 6106): 1,
    ('Steigewanten', 6107): 1,
    ('Loidesthaler', 6109): 1,
    ('Tillern', 6109): 2,
    ('Hintauskreuten', 6109): 3,
    ('Spitz', 6109): 4,
    ('Saatzen', 6109): 5,
    ('Hausberg', 6109): 6,
    ('Kreiten', 6109): 7,
    ('Götzenthaler', 6109): 8,
    ('Kreuten alt', 6109): 9,
    ('Steinberg', 6110): 1,
    ('Steinberg Wald', 6110): 2,
    ('Ebenacker', 6110): 3,
    ('Junge Reinberger', 6110): 4,
    ('Flederwisch', 6110): 5,
    ('Hageln', 6110): 6,
    ('Alte Kreuten', 6113): 1,
    ('Bockstaller', 6113): 2,
    ('Huberischer', 6113): 3,
    ('Kreiten', 6113): 4,
    ('Junge Hofäcker', 6113): 5,
    ('Sommerleiten', 6113): 6,
    ('Winterleiten', 6113): 7,
    ('Satz', 6113): 8,
    ('Hofacker Frohner', 6113): 9,
    ('Goldberg', 6113): 10,
    ('Hofacker', 6113): 11,
    ('Dorfackerl', 6113): 12,
    ('Altenberg', 6113): 13,
    ('Weinberg', 6113): 14,
    ('Zulus', 6113): 15,
    ('Anger', 6102): 1,
    ('Winterleiten', 6102): 2,
    ('Steinstück', 6102): 3,
    ('Hirschfeld', 6111): 1,
    ('Kirchberg2', 6111): 2,
    ('alter Hausberg', 6111): 3,
    ('Kirchberg oben', 6111): 4,
    ('Kirchberg unten', 6111): 5,
    ('Hausweingarten', 6111): 6,
    ('äußere Kirchenried', 6114): 1,
    ('Adamsberg', 6114): 2,
    ('Gießhübl', 6114): 3,
    ('Hundsberg', 6114): 4,
    ('Kirchenried', 6114): 5,
    ('Neusatz', 6114): 6,
    ('Ebner', 6114): 7,
    ('Ober der Straße', 6114): 8,
}


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument('file')
    args = parser.parse_args()

    area_coms = []
    wb = openpyxl.load_workbook(args.file)
    sheet = wb.worksheets[0]
    mgnr, ort, ried, gstnr, kgnr = None, None, None, None, None
    for row in sheet.rows:
        if row[0].value is not None:
            mgnr, ort, ried, gstnr, kgnr = row[0].value, row[4].value, None, None, None
        if mgnr is None:
            continue
        rd, gst, total_area, area, sortid = row[5].value, row[6].value, row[7].value, row[8].value, row[9].value
        if rd is not None:
            if type(rd) == int:
                kgnr = rd
            else:
                ried = rd
        gstnr = f'{gstnr}, {gst}' if gstnr else gst
        if sortid is not None:
            sortid = sortid.strip()
            if sortid == 'MTH':
                sortid = 'MT'  # Müller Thurgau
            elif sortid == 'GMU':
                sortid = 'MU'  # Muskateller?
            if area is None:
                gstnr = None
                continue
            kgnr = kgnr or KG_MAP[ort]
            gstnr = (str(gstnr).replace(' / ', '/').replace(' ,', ', ').replace(',', ', ')
                     .replace('  ', ' ').replace(', ,', ',').replace(' - ', '-').replace('.', ', '))
            if gstnr == 'None':
                gstnr = '-'
            if gstnr.startswith('06') or gstnr.startswith('6128,') or gstnr.startswith('6110,'):
                kgnr, gstnr = gstnr.split(' ', 1)
                kgnr = int(kgnr.strip(','))
                gstnr = gstnr.strip(',').strip()
            area_coms.append((mgnr, kgnr, ried, gstnr, area, sortid))
            gstnr = None
            continue

    print('\nINSERT INTO area_commitment_type (vtrgid, sortid, attrid, disc, min_kg_per_ha, penalty_per_kg, penalty_amount, penalty_none) VALUES')
    first = True
    for t in set(a[5] for a in area_coms):
        if not first:
            print(',')
        print(f"('{t}', '{t}', NULL, NULL, NULL, NULL, NULL, NULL)", end='')
        first = False
    print(';\n')

    print('\nINSERT INTO wb_rd (kgnr, rdnr, name) VALUES')
    first = True
    for n, k in set((a[2], a[1]) for a in area_coms):
        try:
            rdnr = RD_MAP[(n, k)]
        except KeyError:
            if not first:
                print(',')
            keys = [b for (_, a), b in RD_MAP.items() if a == k]
            if len(keys) == 0:
                rdnr = 1
            else:
                rdnr = max(keys) + 1
            RD_MAP[(n, k)] = rdnr
            print(f"({k:5}, {rdnr:3}, '{n}')", end='')
            first = False
    print(';\n')

    fbnr = 0
    print('\nINSERT INTO area_commitment (fbnr, mgnr, vtrgid, cultid, area, year_from, year_to, kgnr, rdnr, gstnr, comment) VALUES')
    for a in area_coms:
        fbnr += 1
        if fbnr > 1:
            print(',')
        rdnr = RD_MAP[(a[2], a[1])]
        print(f"({fbnr:3}, {a[0]:3}, '{a[5]}', 'KIP', {a[4]:6}, 2025, NULL, {a[1]:5}, {rdnr:2}, '{a[3]}', NULL)", end='')
    print(';\n')


if __name__ == '__main__':
    main()