transform.py

import xml.etree.ElementTree as ET
import pandas as pd


def make_field(parent_el, attrib={}, text=None):
    attrib = dict([e for e in attrib.items() if e[1] is not None])
    e = ET.SubElement(parent_el, 'field', attrib)
    if not text is None:
        e.text = text
    return e

def make_comment(el, text):
    el.append(ET.Comment(f" {' ' * len(text)} "))
    el.append(ET.Comment(f" {text} "))
    el.append(ET.Comment(f" {' ' * len(text)} "))

def check_unique(prefix, df, col):
    if not df[col].is_unique:
        raise Exception(f"{prefix} Column \"{col}\" is not unique:\n{df[df[col].duplicated()]}")

# pd.read_excel requires me to run this inside the container: pip install odfpy
# Note on keep_default_na=False: we use '' to mark empty cells
tax_groups = pd.read_excel("src/tryton_setup.ods", engine="odf",
                           sheet_name="Tax Groups", keep_default_na=False)
tax_templates = pd.read_excel("src/tryton_setup.ods", engine="odf", sheet_name="Tax Templates",
                              dtype={'invoice_account': str, 'credit_note_account': str},
                              keep_default_na=False)
tax_code_templates = pd.read_excel("src/tryton_setup.ods", engine="odf", sheet_name="Tax Code Templates",
                                   dtype={'code': str},
                                   keep_default_na=False)
tax_code_line_templates = pd.read_excel("src/tryton_setup.ods", engine="odf", sheet_name="Tax Code Line Templates",
                                        dtype={'code': str, 'tax': str},
                              keep_default_na=False)
account_type_templates = pd.read_excel("src/tryton_setup.ods", engine="odf", sheet_name="Account Type Templates",
                                       dtype={'sequence': str, 'assets': str, 'receivable': str, 'payable': str},
                                       keep_default_na=False)
account_templates = pd.read_excel("src/tryton_setup.ods", engine="odf", sheet_name="Account Templates",
                                  dtype={'reconcile': str, 'party_required': str},
                                  keep_default_na=False)


t = ET.Element('tryton')
data = ET.SubElement(t, 'data')

make_comment(data, "Tax Groups")

check_unique('Tax Groups,', tax_groups, 'code')
check_unique('Tax Templates,', tax_templates, 'code')
check_unique('Tax Code Templates,', tax_code_templates, 'code')
check_unique('Account Type Templates,', account_type_templates, 'code')
check_unique('Account Templates,', account_templates, 'code')


for r in tax_groups.iterrows():
    row = r[1]
    record = ET.SubElement(data, 'record', attrib={
        'model': 'account.tax.group',
        'id': f"group_{row['code']}"
    })
    make_field(record, { 'name': 'name' }, row['name'])
    make_field(record, { 'name': 'code' }, row['code'])
    make_field(record, { 'name': 'kind' }, row['kind'])

make_comment(data, "Tax Templates")
    
for r in tax_templates.iterrows():
    row = r[1]
    record = ET.SubElement(data, 'record', attrib={
        'model': 'account.tax.template',
        'id': f"tax_{row['code']}"
    })
    make_field(record, { 'name': 'name' }, row['code'])
    make_field(record, { 'name': 'description' }, row['description'])
    make_field(record, {
        'name': 'group', 
        'ref': f"group_{row['group']}"
    })
    make_field(record, {
        'name': 'rate',
        'eval': f"Decimal('{row['rate']}')" if not row['rate'] == '' else "None"
    })
    make_field(record, {
        'name': 'type',
        # 'eval': row['type']
    }, row['type'])

    invoice_account = row['invoice_account']
    credit_note_account = row['credit_note_account']
    if invoice_account != '' and not invoice_account in account_templates['code'].values:
        raise Exception(f"Account code {invoice_account} not found in account templates, row:\n{row}")
    make_field(record, {
        'name': 'invoice_account',
        'eval': "None" if invoice_account == '' else None,
        'ref': None if invoice_account == '' else f"account_{invoice_account}"
    })
    if credit_note_account != '' and not credit_note_account in account_templates['code'].values:
        raise Exception(f"Account code {credit_note_account} not found in account templates, row:\n{row}")
    make_field(record, {
        'name': 'credit_note_account',
        'eval': "None" if credit_note_account == '' else None,
        'ref': None if credit_note_account == '' else f"account_{credit_note_account}"
    })
    
    make_field(record, {
        'name': 'account',
        'ref': f"account_{row['account']}"
    })

make_comment(data, "Tax Code Templates")
    
for r in tax_code_templates.iterrows():
    row = r[1]
    record = ET.SubElement(data, 'record', attrib={
        'model': 'account.tax.code.template',
        'id': f"tax_code_{row['code']}"
    })
    make_field(record, { 'name': 'code' }, row['code'])
    make_field(record, { 'name': 'name' }, row['name'])
    if row['account'] != '':
        if not row['account'] in account_templates['code'].values:
            raise Exception(f"Account code {row['account']} not found in account templates, row:\n{row}")
        make_field(record, { 'name': 'account', 'ref': f"account_{row['account']}" })
    if row['parent'] != '':
        if not row['parent'] in tax_code_templates['code'].values:
            raise Exception(f"Parent code {row['parent']} not found in tax code templates, row:\n{row}")
        make_field(record, { 'name': 'parent', 'ref': f"tax_code_{row['parent']}" })

make_comment(data, "Tax Code Line Templates")
    
for r in tax_code_line_templates.iterrows():
    row = r[1]
    record = ET.SubElement(data, 'record', attrib={
        'model': 'account.tax.code.line.template',
        'id': f"tax_code_{row['code']}-tax_{row['tax']}-{r[0]}"
    })
    if not row['code'] in tax_code_templates['code'].values:
        raise Exception(f"Tax code {row['code']} not found in tax code templates, row:\n{row}")
    make_field(record, { 'name': 'code', 'ref': f"tax_code_{row['code']}" })
    if not row['tax'] in tax_templates['code'].values:
        raise Exception(f"Tax code {row['tax']} not found in tax templates, row:\n{row}")
    make_field(record, { 'name': 'tax', 'ref': f"tax_{row['tax']}"})
    make_field(record, { 'name': 'operator' }, row['operator'])
    make_field(record, { 'name': 'amount' }, row['amount'])
    make_field(record, { 'name': 'type' }, row['type'])

make_comment(data, "TODO: No support for tax rules yet")

# new file for accounts and account types
a = ET.Element('tryton')
data = ET.SubElement(a, 'data')

make_comment(data, "Account Type Templates")
    
for r in account_type_templates.iterrows():
    row = r[1]
    record = ET.SubElement(data, 'record', attrib={
        'model': 'account.account.type.template',
        'id': f"type_{row['code']}"
    })
    make_field(record, { 'name': 'name' }, row['name'])
    if row['parent'] != '':
        if not row['parent'] in account_type_templates['code'].values:
            raise Exception(f"Parent code {row['parent']} not found in account type templates, row:\n{row}")
        make_field(record, { 'name': 'parent', 'ref': f"type_{row['parent']}" })
    if row['sequence'] != '':
        make_field(record, { 'name': 'sequence', 'eval': row['sequence'] })
    make_field(record, { 'name': 'statement', 'eval': "None" if row['statement'] == '' else None }, row['statement'])
    if row['assets'] == 'True':
        make_field(record, { 'name': 'assets', 'eval': "True" })
    if row['receivable'] == 'True':
        make_field(record, { 'name': 'receivable', 'eval': "True" })
    if row['payable'] == 'True':
        make_field(record, { 'name': 'payable', 'eval': "True" })

make_comment(data, "Account Templates")

for r in account_templates.iterrows():
    row = r[1]
    record = ET.SubElement(data, 'record', attrib={
        'model': 'account.account.template',
        'id': f"account_{row['code']}"
    })
    make_field(record, { 'name': 'name' }, row['name'])
    make_field(record, { 'name': 'code' }, row['code'])
    if row['parent'] != '':
        if not row['parent'] in account_templates['code'].values:
            raise Exception(f"Parent code {row['parent']} not found in account templates, row:\n{row}")
        make_field(record, { 'name': 'parent', 'ref': f"account_{row['parent']}" })
    if row['type'] != '':
        if not row['type'] in account_type_templates['code'].values:
            raise Exception(f"Account Type code {row['type']} not found in account type templates, row:\n{row}")
        make_field(record, { 'name': 'type', 'ref': f"type_{row['type']}" })
    if row['reconcile'] == 'True':
        make_field(record, { 'name': 'reconcile', 'eval': "True" })
    if row['party_required'] == 'True':
        make_field(record, { 'name': 'party_required', 'eval': "True" })


ET.indent(t)
ET.indent(a)
ET.ElementTree(t).write("out/tax.xml")
ET.ElementTree(a).write("out/account.xml")