quacken /

The default branch has multiple heads

'''gctomg -- convert GnuCash to moneyGuru

import logging
from xml.etree import cElementTree as ElementTree
from itertools import groupby

import sqlalchemy

log = logging.getLogger(__name__)

def main(argv):
    db_url = argv[1]
    e = sqlalchemy.create_engine(db_url)
    print ElementTree.tostring(convert(e))

def convert(e):
    guid = e.execute('select guid from books').fetchone()[0]
    f = mg_file(guid)
    # properties?
    return f

def mg_file(uuid):
    return ElementTree.Element('moneyguru-file',

TY = {'ROOT': None,
      'ASSET': 'asset',
      'BANK': 'asset',
      'CASH': 'asset',
      'RECEIVABLE': 'asset',
      'EQUITY': 'asset',  # hmm...
      'LIABILITY': 'liability',
      'CREDIT': 'liability',
      'PAYABLE': 'liability',
      'INCOME': 'income',
      'EXPENSE': 'expense'

def groups(e):
    parents = e.execute('''select distinct, p.account_type
                           from accounts p
                           join accounts ch
                           on ch.parent_guid = p.guid''').fetchall()

    log.warn('moneyguru account groups do not nest.')
    return [ElementTree.Element('group',
                                {'name': acct[0],
                                 'type': TY[acct[1]], # TODO: just 4 types
            for acct in parents if TY[acct[1]]]

def accounts(e):
    multi_currency_accounts = e.execute('''
        select count(*), account_guid, name

        (select distinct a.guid account_guid,
              , cur.guid currency_guid, cur.mnemonic
        from accounts a
        join splits s on s.account_guid = a.guid
        join transactions tx on s.tx_guid = tx.guid
        join commodities cur on cur.guid = tx.currency_guid) t
        group by account_guid, name
          having count(*) > 1'''

    if multi_currency_accounts:
        log.critical('multi-currency accounts! %s', multi_currency_accounts)

    for a in e.execute('''
        select count(*), name
        from (
         select distinct a.guid,
         from accounts a
         join splits s on s.account_guid = a.guid
        ) t
        group by name
          having count(*) > 1'''
        log.critical('%s accounts named: %s', a[0], a[1])

    accounts = e.execute('''
        select distinct cur.mnemonic,, a.account_type
             ,, p.account_type, ofx.string_val
        from accounts a
        join accounts p on a.parent_guid = p.guid
        left join slots ofx on'online_id' and ofx.obj_guid = a.guid
        join splits s on s.account_guid = a.guid
        join transactions tx on s.tx_guid = tx.guid
        join commodities cur on cur.guid = tx.currency_guid'''

    for a in accounts:
        t = TY[a[2]]
        if a[2] in ('EQUITY', ):
            log.warn('account: %s type %s converted to moneyguru %s',
                     a[1], a[2], t)
        pt = TY[a[4]]
        if pt and t != pt:
            log.warn('account type mismatch: %s[%s] in %s[%s]',
                     a[1], t, a[3], pt)

    return [ElementTree.Element('account',
                                ofxref({'currency': a[0],
                                        'name': a[1],
                                        'group': a[3],
                                        'type': TY[a[2]]}, a[5]))
            for a in accounts if TY[a[2]]]

def ofxref(d, ref):
    return dict(d, reference='||'.join(ref.split())) if ref else d

def transactions(e):
    txs = e.execute('''
      select tx.guid, tx.post_date, tx.description
           ,, cur.mnemonic
           , s.value_num / s.value_denom, s.memo
           , ofx.string_val
      from transactions tx
      join commodities cur on tx.currency_guid = cur.guid
      join splits s on s.tx_guid = tx.guid
      join accounts a on s.account_guid = a.guid
      left join slots ofx on'online_id' and ofx.obj_guid = s.guid
    return [transaction(guid, splits)
            for guid, splits in groupby(txs, lambda row: row[0])]

def transaction(guid, splits):
    splits = list(splits)
    children = [ElementTree.Element('split',
                                                amount='%s %s' % (
                        cur, amount),
                                                notes=memo), ref))
                for _0, _1, _2, account, cur, amount, memo, ref in splits]

    _, date, description = splits[0][:3]

    tx = ElementTree.Element('transaction',
    return tx

if __name__ == '__main__':
    def _hide_sys():
        import sys
        return sys.argv