def showAccounts():
orgas = yorm.getmany('orga', 'order by orga')
accounts = yorm.getmany('acc', ' order by orga, acc')
i0 = -1
i1 = 0
anOrga = None
lastAcc = None
while i1 < len(accounts):
anAccount = accounts[i1]
if lastAcc is None or lastAcc['orga'] != anAccount['orga']:
while i0 < len(orgas):
i0 = i0 + 1
anOrga = orgas[i0]
if anOrga['orga'] == anAccount['orga']:
break
s = '==' + str(anOrga['orga']) + '-' + str(
anAccount['acc']) + ', organization name: ' + anOrga[
'sname'] + ', acckey: ' + anAccount[
'accno'] + ', acctype: ' + anAccount[
'acctype'] + ', routine no: ' + anAccount[
'routineno'] + ', most days to download: ' + str(
anAccount['dayspan']
) + ', text: ' + anAccount['txt'] + '\n'
print(s)
lastAcc = anAccount
i1 = i1 + 1
def expAllAcc():
orgas = yorm.getmany('orga')
wd = makeChromeDriver()
for anOrga in orgas:
accs = yorm.getmany('acc', ' where orga = ' + anOrga['orga'])
for anAcc in accs:
expOne(anOrga, anAcc)
wd.close()
def impAllAcc():
orgas = yorm.getmany('orga', '')
wd = makeChromeDriver()
for anOrga in orgas:
accs = yorm.getmany('acc', ' where orga = ' + anOrga['orga'])
for anAcc in accs:
impOne(anOrga, anAcc, ptrack=track[0], driver=wd)
wd.close()
def initTrack():
tracks = yorm.getmany('track', ' order by trackid desc limit 0, 1')
if tracks is None or len(tracks) == 0:
atrack = {'trackid': 0, 'txt': 'First by sys', 'isdefault': True}
yorm.saveone('track', atrack)
yorm.db.query('commit')
track.append(atrack)
else:
track.extend(tracks)
def getDbOrga(self):
shortnames = {'wf': ['wellsfargo'], 'citi': ['citibank']}
sname = shortnames.get(self.fiorg)
if sname is not None:
orgas = yorm.getmany('orga', 'where sname = "' + sname + '"')
if orgas is not None and len(orgas) == 1:
return orgas[0]
warnings.warn('No organization found for short name: ', self.fiorg)
return None
def guessOrgaName(sonr, exAcc):
shortnames = {'wf': 'WellsFargo', 'citi': 'Citibank', 'chase': 'Chase'}
realname = None
try:
realname = sonr.org.lower()
except:
pass
if realname is not None:
reallongname = shortnames.get(realname)
if reallongname is not None:
realname = reallongname
else:
realname = sonr.org
else:
try:
dbAccs = yorm.getmany(
'acc', ' where lower(substr(accno, -4, 4)) = "' +
exAcc.acctid[-4:] + '"')
for dbAcc in dbAccs:
dbOrga = yorm.getone('orga', [dbAcc['orga']])
s = 'Import file does not have organization name specified. \n'\
+ 'But an account is found probably related to the organization: \n'\
+ dbOrga['sname'] + ' :: ' + exAcc.acctid[-4:] + '\n'\
+ ' y: Yes to use it\n'\
+ ' ****: Another name to remind system\n' \
+ ' q: Quit\n'
inputOrga = input(s).strip()
if inputOrga.lower() == 'q':
os._exit(-1)
elif inputOrga.lower() == 'y':
realname = dbOrga['sname']
else:
realname = inputOrga
break
except Exception as e:
tb.print_tb(e.__traceback__)
print(e)
pass
if realname is None:
realname = input(
'Financial instituiton does not specify its organization name. Input one(q will exit):\n'
).strip()
if realname.lower() == 'q':
os._exit(-1)
else:
print(
'Use ' + realname +
'. You have opportunity to creat it automatically if no DB record.\n'
)
return realname
def getOrga(sonr, exAcc):
realname = guessOrgaName(sonr, exAcc)
try:
dbOrga = yorm.getmany('orga', ' where sname = "' + realname + '"')
if dbOrga is None or len(dbOrga) == 0:
s = input(
'Bank organization ' + realname +
' is not in your database. Create it automatically or select one?\n'
' y: create\n'
' s: select\n'
'other: quit\n')
if s == 'y':
c = yorm.db.cursor()
c.execute('select max(orga) as orga from orga')
r = c.fetchall()
if r is None or len(r) == 0 or r[0] is None or r[0][0] is None:
maxOrg = 0
else:
maxOrg = r[0][0] + 1
anOrga = {
'orga': maxOrg,
'sname': realname,
'url': 'https://www.' + realname + '.com',
'usr': '',
'password': '',
'earliest': '',
'txt': '',
'usrxpath': '',
'passxpath': '',
'loginxpath': ''
}
yorm.saveone('orga', anOrga)
yorm.db.query('commit')
return anOrga
elif s == 's':
print('Manual organization selection is not implemented yet')
os._exit(-1)
else:
os._exit(-1)
else:
return dbOrga[0]
except Exception as e:
yorm.db.query('rollback')
tb.print_tb(e.__traceback__)
print(e)
os._exit(-1)
def changeTrack():
s0 = 'Choose track to change: \n'
tracks = yorm.getmany('track', ' order by trackid desc')
re1 = re.compile('\s+')
for i in len(tracks):
atrack = tracks[i]
s0 = s0 + str(i) + ': ' + atrack + '\n'
while 1:
s = input(s0)
if s == 'q':
return
try:
sa = re1.split(s)
if len(sa) != 2 and len(sa) != 1:
print('Input n xxx. n is track, xxx is description')
continue
if len(sa) == 2:
newTrack = {}
newTrack['trackid'] = int(sa[0])
newTrack['txt'] = sa[1]
newTrack['isdefault'] = True
yorm.saveone('track', newTrack)
print('New track created and set to default')
else:
i = int(sa[0])
if i < 0 or i >= len(tracks):
print(
'Input n to switch to default. n xxx to create new Track'
)
continue
for atrack in tracks:
if atrack['isdefault']:
atrack['isdefault'] = False
yorm.saveone('track', atrack)
break
tracks[i]['isdefault'] = True
yorm.saveone('trackid', tracks[i])
print('Track changed to', i)
yorm.db.query('commit')
return
except Exception as e:
yorm.db.query('rollback')
tb.print_tb(e.__traceback__)
print(e)
os._exit(-1)
def getDbAcc(self, orga):
try:
cnt = 0
selfLast4 = self.accno[-4:]
theAcc = None
accs = yorm.getmany('acc', ' where orga = ' + str(orga['orga']))
for acc in accs:
accLast4 = acc['accno'][-4:]
if selfLast4 == accLast4:
cnt = cnt + 1
theAcc = acc
if cnt > 1:
raise Exception(
'More than one account found matching last 4 digits of account no in your database: ',
selfLast4)
if cnt == 0:
warnings.warn(
'No account found matching last 4 digits of account no in your database: ',
selfLast4)
return theAcc
except Exception as e:
tb.print_tb(e.__traceback__)
print(e)
def loadCfgRecs():
c = yorm.db.cursor()
c.execute('select max(orga) as orga from orga')
r = c.fetchall()
if r is None or len(r) == 0 or r[0] is None or r[0][0] is None:
maxOrg = 0
else:
maxOrg = r[0][0] + 1
orgas = []
for cfgKey, aCfg in cfg.items():
if cfgKey[:4].lower() == 'org-':
anOrga = {
'orga': maxOrg,
'sname': aCfg['sname'],
'url': aCfg['url'],
'usr': aCfg['usr'],
'password': aCfg['password'],
'earliest': aCfg['earliest'],
'txt': aCfg['txt'],
'usrxpath': aCfg['usrxpath'],
'passxpath': aCfg['passxpath'],
'loginxpath': aCfg['loginxpath']
}
dbOrga = yorm.getmany('orga',
' where sname = "' + aCfg['sname'] + '"')
if dbOrga is None or len(dbOrga) == 0:
maxOrg = maxOrg + 1
else:
anOrga['orga'] = dbOrga[0]['orga']
orgas.append(anOrga)
try:
yorm.savemany('orga', orgas, insertOrUpdate=False)
yorm.db.query('commit')
except Exception as e:
tb.print_tb(e.__traceback__)
print(e)
yorm.db.query('rollback')
def mainloop():
initTrack()
s0 = 'Choose command to go\n' \
' 1: drop tables and clean data\n' \
' 2: create tables and load\n' \
' 3: show track id(used for export only)\n' \
' 4: show accounts\n' \
' 5: import for account \n' \
' 6: export for account \n' \
' 7: one step all accounts import / export\n'
while 1:
s = input(s0)
if s == '1':
dropTables()
os.system(
'rm -rf ' +
os.path.join(os.path.dirname(__file__), cfg['datadir']['dir']))
elif s == '2':
createTables()
loadCfgRecs()
elif s == '3':
print('Tracking: ', track[0])
changeTrack()
elif s[0] == '4':
showAccounts(s[1:])
elif s[0] == '5':
orgas = yorm.getmany('orga', '')
for orga in orgas:
print(orga)
impOrga(orga, ptrack=track[0])
break
elif s[0] == '6':
expAccount(s[1:])
elif s == '7':
impAllAcc()
expAccount()
def post(self, statement, impid):
l = len(self.trans)
toPost = []
toPostOrgas = []
toPostAccs = []
for i in range(l):
tran = self.trans[i]
orga = self.orgas[i]
acc = self.accs[i]
try:
if tran.fitid is not None and tran.fitid != '':
lsTrans = yorm.getmany(
'trans', ' where orga = ' + str(orga['orga']) +
' and acc = ' + str(acc['acc']) + ' and earthid = "' +
tran.fitid + '"')
if lsTrans is not None and len(lsTrans) > 0:
print('Duplicated found: ' + str(tran) +
' by org/account/global id. skipped')
continue
ptime = tran.dtposted
ansdate = sdate(ptime)
curr = tran.currency or statement.curdef
lsTrans = yorm.getmany(
'trans', ' where orga = ' + str(orga['orga']) +
' and acc = ' + str(acc['acc']) + ' and sdate = ' +
ansdate + ' and curr = "' + curr + '" and abs(amt - ' +
str(round(tran.trnamt)) + ') < 0.01')
if lsTrans is not None and len(lsTrans) > 0:
print('Duplicates found: ' + tran +
' by org/account/date/curr/amt. skippied')
continue
toPost.append(tran)
toPostOrgas.append(orga)
toPostAccs.append(acc)
except Exception as e:
tb.print_tb(e.__traceback__)
print(e)
os._exit(-1)
if len(toPost) > 1:
c = yorm.db.cursor()
c.execute('select max(id) as id from trans')
r = c.fetchall()
if r is None or len(r) == 0 or r[0] is None or r[0][0] is None:
maxTran = 0
else:
maxTran = r[0][0] + 1
dbPosts = []
anSdate = sdate(tran.dtposted)
fldName = None if tran.name is None else tran.name.replace(
'\'', '\\\'')
fldMemo = None if tran.memo is None else tran.memo.replace(
'\'', '\\\'')
for tran in toPost:
aDbtran = {
'id': maxTran,
'orga': orga['orga'],
'acc': acc['acc'],
'sdate': anSdate,
'impid': impid,
'earthid': tran.fitid,
'ptime': round(tran.dtposted.timestamp()),
'amt': round(tran.trnamt, 4),
'name': fldName,
'memo': fldMemo,
'trantype': tran.trntype
}
if tran.currency is None or tran.currency == '':
aDbtran['curr'] = statement.curdef
aDbtran['xrate'] = None
aDbtran['amt'] = round(tran.trnamt, 4)
else:
aDbtran['curr'] = tran.currency
aDbtran['xrate'] = tran.currate
aDbtran['amt'] = round(tran.trnamt, 4)
dbPosts.append(aDbtran)
maxTran = maxTran + 1
try:
yorm.savemany('trans', dbPosts)
yorm.db.query('commit')
except Exception as e:
yorm.db.query('rollback')
tb.print_tb(e.__traceback__)
print(e)
os._exit(-1)
self.trans.clear()
def getAcc(orga, statement):
try:
stmtAcc = statement.account
stmtLast4 = stmtAcc.acctid[-4:]
accs = yorm.getmany('acc', ' where orga = ' + str(orga['orga']))
if accs is not None and len(accs) > 0:
for acc in accs:
last4 = acc['accno'][-4:]
if last4 == stmtLast4:
syncdate = round(statement.ledgerbal.dtasof.timestamp())
if syncdate > acc['syncdate']:
acc['syncbal'] = round(statement.ledgerbal.balamt, 2)
acc['syncdate'] = syncdate
try:
yorm.saveone('acc', acc, insertOrReqplace=False)
yorm.db.query('commit')
except Exception as e:
yorm.db.query('rollback')
tb.print_tb(e.__traceback__)
print(e)
os._exit(-1)
return acc
sinput = input('Account ' + stmtLast4 +
' is not found. Create automatically or select one?\n'
' y: Create\n'
' s: Select\n'
'other: quit\n')
if sinput == 'y':
c = yorm.db.cursor()
c.execute('select max(acc) as acc from acc where orga = ' +
str(orga['orga']))
r = c.fetchall()
if r is None or len(r) == 0 or r[0] is None or r[0][0] is None:
maxAcc = 0
else:
maxAcc = r[0][0] + 1
syncdate = round(statement.ledgerbal.dtasof.timestamp())
anAcc = {
'orga': orga['orga'],
'acc': maxAcc,
'accno': stmtAcc.acctid,
'ledgertype': '',
'acctype': '',
'routineno': '',
'dayspan': 0,
'txt': '',
'curdef': statement.curdef,
'syncbal': round(statement.ledgerbal.balamt, 2),
'syncdate': syncdate
}
yorm.saveone('acc', anAcc)
yorm.db.query('commit')
return anAcc
elif sinput == 's':
print('Not implemented yet')
os._exit(-1)
else:
os._exit(-1)
except Exception as e:
yorm.db.query('rollback')
tb.print_tb(e.__traceback__)
print(e)
os._exit(-1)