########################################################################
#  Copyright (C) 2007,8,9 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
""" ofxml2flat OFX-file flat-file
convert XML OFX file to flat lines.
"""
# TODO support INVOOLIST
from xml.dom.minidom import parse
import sys
from n3tagvalue import stagvalue,n3header,n3secid
import re

fout =sys.stdout # Where to write to. Modified by flat
hashing=False

def p(pnode,pre=''):
    """Debug utility"""
    #print >>fout,"%s:%s\n" % (pre,pnode.nodeType)
    nodelist=pnode.childNodes
    for node in nodelist:
        if node.nodeType == node.TEXT_NODE:
            print "%s%s\n" % (pre,node.data.strip())
        elif node.nodeType == node.ELEMENT_NODE:
            print "%s%s:\n" % (pre,node.tagName)
        p(node,'+'+pre)

def get(s,tag,mandatory=False):
    TAG=tag.upper().strip()
    try:
        VAL=s.getElementsByTagName(TAG)[0].firstChild.data.strip()
    except:
        if mandatory:
            raise Exception('Missing mandatory tag %s'%tag)
        return
    return VAL

def sprt_tag(s,tag,mandatory=True,scale=1,ntag=None):
    VAL=get(s,tag,mandatory)
    if ntag:
        ntag=ntag.upper()
    else:
        ntag=tag.upper()
    if VAL:
        if scale!=1:
            return stagvalue(ntag,"%f"%(float(VAL)*scale))
        else:
            return stagvalue(ntag,VAL)

def prt_tag(s,tags,mandatory=True,ntag=None,scale=1,scale_assetclass=None):
    mandatory=False # TODO, remove this!!!
    if isinstance(tags,basestring):
        tags=[tags]
    if scale_assetclass in ['DEBT']:
        scale=scale/100.0
    if scale_assetclass in ['OPT']:
        scale=scale*100.0
    for tag in tags:    
        v=sprt_tag(s,tag,mandatory=mandatory,scale=scale,ntag=ntag)
        if v:
            print >>fout,v,

def prt_secid(s,acctid=None):
    uniqueid=s.getElementsByTagName('UNIQUEID')[0].firstChild.data.strip()
    uniqueidtype=s.getElementsByTagName('UNIQUEIDTYPE')[0].firstChild.data.strip()
    print >>fout,n3secid(uniqueidtype,uniqueid),
    pacctid(acctid)

def pacctid(acctid):
    if acctid:
        print >>fout,stagvalue('ACCTID',acctid,hashing=hashing),

def prt_cashacc(acctid,currency,p=None,accttype='CHECKING'):
    if p:
        try:
            accttype=get(p,'SUBACCTFUND',mandatory=True)
        except:
            accttype=get(p,'ACCTTYPE',mandatory=True)
    print >>fout,n3secid('CHECKING',currency),
    pacctid(acctid)
    print >>fout,stagvalue("ACCTTYPE",accttype),
    if accttype in ['CHECKING', 'CASH']:
        print >>fout,stagvalue('ASSETCLASS','CHECKING'),
        
def flat(fin,_fout,context=None,_hashing=False):
    global fout,hashing
    fout=_fout
    hashing=_hashing
    
    if isinstance(fin,str):
        fin=open(fin)
    else:
        fin.seek(0)

    n3header(fout,"$Id$"[1:-1],context)
    
    dom = parse(fin) # parse an XML file by name
    # Start with information on assets. In the examples I have of OFX files, this information
    # appeared in the end of the file, but it should be processed first so latter on when processing
    # holdings it will be easier to locate the relevant asset.
    seclist=dom.getElementsByTagName('SECLIST')
    if seclist:
        for s in seclist[0].childNodes:
            print >>fout,"a3:flatLine [ ",
            if s.nodeType != s.ELEMENT_NODE:
                continue # Skip comments
            prt_secid(s)
            ticker=get(s,'TICKER')
            if ticker:
                ticker=ticker.split()[0] # Some banks put their FIID after the ticker
                print >>fout,stagvalue('TICKER',ticker),
            v=get(s,'FIID')
            if v:
                print >>fout,stagvalue('FIID',v),
                v = get(dom,'BROKERID')
                if v:
                    print >>fout,stagvalue('BROKERID',v)
                else:
                    v=get(dom,'ORG')
                    print 'FIID found but cant find BROKERID, using ORG instead'
                    if v:
                        print >>fout,stagvalue('ORG',v)
                 
            if get(s,'YIELD'):
                if get(s,'DTYIELDASOF')!=get(s,'DTASOF'):
                    raise Exception("price and yield dates dont match")
            prt_tag(s,['SECNAME','DTASOF'])
            assetclass=s.tagName[:-4]
            print >>fout,stagvalue("ASSETCLASS",assetclass),
            prt_tag(s,'UNITPRICE',scale_assetclass=assetclass) # TODO check if it is better to scale the UNITS instead (looks like DEBT->UNITPRICE OPT->UNITS)
            prt_tag(s,['ASSETCLASS','PARVALUE','DEBTTYPE','DEBTCLASS','DTMAT',
                       'COUPONRT','COUPONFREQ','MFTYPE','STOCKTYPE','YIELD'],
                    mandatory=False)
            print >>fout,"];"

    # Credit card accounts
    stmttrnrs=dom.getElementsByTagName('CCSTMTTRNRS')
    if stmttrnrs:
        for stmtrs in stmttrnrs:
            print >>fout,"a3:flatLine [ ",
            acctid=get(stmtrs,'ACCTID')
            curdef=get(stmtrs,'CURDEF')
            # bind togther all the information about the cash holding.
            prt_cashacc(acctid,curdef,accttype='CREDITCRD')
            # info on the account
            # bind togther all the information about the account.
            # The DTSTART, DTEND are bounded to the unique pair <ACCTID,FILE> but the FILE is not
            # specified because it is implicitly assumed everywhere in the line file
            prt_tag(stmtrs,['CURDEF',
                            #'DTSTART','DTEND' # FIXME there are too many transaction outside range or the range is too big to be full
                        ])
            prt_tag(dom,['ORG','FID'],mandatory=False)
            # info on the cash holding
            l=stmtrs.getElementsByTagName('LEDGERBAL')[0]
            prt_tag(l,'DTASOF')
            prt_tag(l,'BALAMT',ntag='UNITS')         # Ignore AVAILBAL
            print >>fout,"];"
            
            # Generate information on all transactions
            for t in stmtrs.getElementsByTagName('STMTTRN'):
                print >>fout,"a3:flatLine [ ",
               # bind all information on transaction with the cash holding.
                prt_cashacc(acctid,curdef,accttype='CREDITCRD')
                prt_tag(t,['TRNTYPE','FITID'])
                prt_tag(t,'TRNAMT',ntag='UNITS')
                prt_tag(t,'DTPOSTED',ntag='DTSETTLE')
                prt_tag(t,'DTAVAIL',mandatory=False)
                prt_tag(t,['DTUSER','CHECKNUM','REFNUM','NAME','MEMO'],mandatory=False)
                print >>fout,"];"
            
    # Checking accounts
    stmttrnrs=dom.getElementsByTagName('STMTTRNRS')
    if stmttrnrs:
        for stmtrs in stmttrnrs:
            print >>fout,"a3:flatLine [ ",
            acctid=get(stmtrs,'ACCTID')
            curdef=get(stmtrs,'CURDEF')
            # bind togther all the information about the cash holding.
            prt_cashacc(acctid,curdef,stmtrs)
            # info on the account
            # bind togther all the information about the account.
            # The DTSTART, DTEND are bounded to the unique pair <ACCTID,FILE> but the FILE is not
            # specified because it is implicitly assumed everywhere in the line file
            prt_tag(stmtrs,['CURDEF',
                            #'DTSTART','DTEND' # FIXME check if/when this can be done
                        ])
            prt_tag(dom,['ORG','FID'],mandatory=False)
            # info on the cash holding
            l=stmtrs.getElementsByTagName('LEDGERBAL')[0]
            prt_tag(l,'DTASOF')
            prt_tag(l,'BALAMT',ntag='UNITS')         # Ignore AVAILBAL
            print >>fout,"];"
            
            # Generate information on all transactions
            for t in stmtrs.getElementsByTagName('STMTTRN'):
                print >>fout,"a3:flatLine [ ",
                # bind all information on transaction with the cash holding.
                prt_cashacc(acctid,curdef,stmtrs)
                prt_tag(t,['TRNTYPE','FITID'])
                prt_tag(t,'TRNAMT',ntag='UNITS')
                prt_tag(t,'DTPOSTED',ntag='DTSETTLE')
                prt_tag(t,'DTAVAIL',mandatory=False)
                prt_tag(t,['DTUSER','CHECKNUM','REFNUM','NAME','MEMO'],mandatory=False)
                print >>fout,"];"
            
    # Investment accounts
    invstmttrnrs=dom.getElementsByTagName('INVSTMTTRNRS')
    if invstmttrnrs:
        for invstmtrs in invstmttrnrs:
            print >>fout,"a3:flatLine [ ",
            # Every line should show the ACCTID
            acctid=get(invstmtrs,'ACCTID')
            curdef=get(invstmtrs,'CURDEF')
            # bind togther all the information about the account.
            # The DTSTART, DTEND are bounded to the unique pair <ACCTID,FILE> but the FILE is not
            # specified because it is implicitly assumed everywhere in the line file
            prt_tag(invstmtrs,['CURDEF','ACCTID',
                               #'DTSTART','DTEND' # Fixme check when it can be done
                           ])
            prt_tag(dom,['ORG','FID'],mandatory=False)
            print >>fout,"];"
            
            # generate statement line for CASH account
            print >>fout,"a3:flatLine [ ",
            prt_cashacc(acctid,curdef,accttype='CASH') # Make this match the CASH accounts used in investment transactions
            prt_tag(invstmtrs,'AVAILCASH',ntag='UNITS')
            prt_tag(invstmtrs,'DTASOF')
            print >>fout,"];"
            
            # Dump current portfolio of the account
            for p in invstmtrs.getElementsByTagName('INVPOS'):
                print >>fout,"a3:flatLine [ ",
                prt_secid(p,acctid)
                prt_tag(p,'DTPRICEASOF',ntag='DTASOF')
                assetclass=p.parentNode.tagName[3:]
                print >>fout,stagvalue("ASSETCLASS",assetclass),
                prt_tag(p,'UNITPRICE',scale_assetclass=assetclass)
                prt_tag(p,['POSTYPE','UNITS','MKTVAL'])
                prt_tag(p,'MEMO',ntag='POSMEMO',mandatory=False) # POSMEMO in order not to confuse with transaction's MEMO
                print >>fout,"];"
                
            # Dump transactions
            for trn in ['INVBUY','INVSELL']:
                for p in invstmtrs.getElementsByTagName(trn):
                    print >>fout,"a3:flatLine [ ",
                    prt_secid(p,acctid)
                    prt_tag(p,['FITID','DTTRADE','DTSETTLE','MEMO','UNITS','COMMISSION','FEES','TOTAL'])
                    if trn=='INVBUY':
                        print >>fout,stagvalue("TRNTYPE","BUY"),
                        assetclass = p.parentNode.tagName[3:]
                    else:
                        print >>fout,stagvalue("TRNTYPE","SELL"),
                        assetclass = p.parentNode.tagName[4:]
                    print >>fout,stagvalue("ASSETCLASS",assetclass),
                    prt_tag(p,'UNITPRICE',scale_assetclass=assetclass)
                    prt_tag(p,['MARKUP','MARKDOWN'],mandatory=False,
                            scale_assetclass=assetclass)

                    accrdint=get(p.parentNode,'ACCRDINT') # ACCRDINT is outside the INVBUY/SELL structure.
                    if accrdint:
                        print >>fout,stagvalue('ACCRDINT',accrdint),
                    print >>fout,"];"
                    
                    
                    # generate line for current account
                    print >>fout,"a3:flatLine [ ",
                    prt_cashacc(acctid,curdef,p)
                    prt_tag(p,'FITID',ntag='RELFITID')
                    prt_tag(p,'DTSETTLE')
                    prt_tag(p,'TOTAL',ntag='UNITS')
                    if trn=='INVBUY':
                        print >>fout,stagvalue('TRNTYPE','DEBIT'),
                    else:
                        print >>fout,stagvalue('TRNTYPE','CREDIT'),
                    print >>fout,"];"
                    
                    # ACCRDINT is real money when you buy/sell a debit but it does not appear in TOTAL
                    if accrdint:
                        print >>fout,"a3:flatLine [ ",
                        # generate line for current account
                        prt_cashacc(acctid,curdef,p)
                        prt_tag(p,'FITID',ntag='RELFITID')
                        prt_tag(p,'DTSETTLE')
                        print >>fout,stagvalue('UNITS',accrdint),
                        if trn=='INVBUY':
                            print >>fout,stagvalue('TRNTYPE','DEBIT'),
                        else:
                            print >>fout,stagvalue('TRNTYPE','CREDIT'), 
                        print >>fout,stagvalue('PAYEEID','ACCRDINT'), # The money is not coming from the Asset issuer but from the side selling/buying the asset to us.
                        print >>fout,"];"
                        
            for p in invstmtrs.getElementsByTagName('INCOME'):
                print >>fout,"a3:flatLine [ ",
                prt_secid(p,acctid)
                prt_tag(p,['FITID','DTTRADE','DTSETTLE','MEMO','TOTAL'])
                prt_tag(p,'INCOMETYPE',ntag='TRNTYPE')
                print >>fout,"];"
                
                # generate line for current account
                print >>fout,"a3:flatLine [ ",
                prt_cashacc(acctid,curdef,p)
                prt_tag(p,'FITID',ntag='RELFITID')
                prt_tag(p,'DTSETTLE')
                prt_tag(p,'TOTAL',ntag='UNITS')
                print >>fout,stagvalue('TRNTYPE','CREDIT'),
                print >>fout,"];"
                
            for p in invstmtrs.getElementsByTagName('INVEXPENSE'):
                print >>fout,"a3:flatLine [ ",
                prt_secid(p,acctid)
                prt_tag(p,['FITID','DTTRADE','DTSETTLE','MEMO'])
                prt_tag(p,'TOTAL',scale=-1)
                memo=get(p,'MEMO')               
                if re.search(r"\bTAX\b",memo,re.IGNORECASE):
                    prt_tag(p,'TOTAL',ntag='TAXES')
                    print >>fout,stagvalue("TRNTYPE","TAX"),
                elif re.search(r"\bFEE\b",memo,re.IGNORECASE):
                    prt_tag(p,'TOTAL',ntag='FEES')
                    print >>fout,stagvalue("TRNTYPE","FEE"),
                else:
                    print "Unknown expense",memo
                    prt_tag(p,'TOTAL',ntag='COMMISSION')
                    print >>fout,stagvalue("TRNTYPE","EXPENSE"),
                print >>fout,"];"
                
                # generate line for current account
                print >>fout,"a3:flatLine [ ",
                prt_cashacc(acctid,curdef,p)
                prt_tag(p,'FITID',ntag='RELFITID')
                prt_tag(p,'DTSETTLE')
                prt_tag(p,'TOTAL',ntag='UNITS',scale=-1)
                print >>fout,stagvalue('TRNTYPE','DEBIT'),
                print >>fout,"];"
                
            for p in invstmtrs.getElementsByTagName('TRANSFER'):
                print >>fout,"a3:flatLine [ ",
                prt_secid(p,acctid)
                prt_tag(p,['FITID','DTTRADE','DTSETTLE','MEMO','UNITS'])
                prt_tag(p,'TFERACTION',ntag='TRNTYPE')
                print >>fout,"];"
                # note that TRANSFER does not have a SUBACCTFUND to balance with
                
            for p in invstmtrs.getElementsByTagName('INVBANKTRAN'):
                print >>fout,"a3:flatLine [ ",
                prt_cashacc(acctid,curdef,p)
                prt_tag(p,['TRNTYPE','FITID','NAME','MEMO'])
                prt_tag(p,'DTPOSTED',ntag='DTSETTLE')
                prt_tag(p,'TRNAMT',ntag='UNITS')
                print >>fout,"];"
    
    dom.unlink()
    print >>fout,"."
