def test_unreconciled(self):
m_db = Mock()
m_q = Mock(spec_set=Query)
m_filt = Mock(spec_set=Query)
m_db.query.return_value = m_q
m_q.filter.return_value = m_filt
res = OFXTransaction.unreconciled(m_db)
assert res == m_filt
assert len(m_db.mock_calls) == 2
assert m_db.mock_calls[0] == call.query(OFXTransaction)
kall = m_db.mock_calls[1]
assert kall[0] == 'query().filter'
expected1 = OFXTransaction.reconcile.__eq__(null())
cutoff = datetime(2017, 3, 17, 0, 0, 0, tzinfo=UTC)
expected2 = OFXTransaction.date_posted.__ge__(cutoff)
expected3 = OFXTransaction.account.has(reconcile_trans=True)
assert len(kall[1]) == 8
assert str(expected1) == str(kall[1][0])
assert binexp_to_dict(expected2) == binexp_to_dict(kall[1][1])
assert str(kall[1][2]) == str(expected3)
assert str(OFXTransaction.is_payment.__ne__(True)) == str(kall[1][3])
assert str(OFXTransaction.is_late_fee.__ne__(True)) == str(kall[1][4])
assert str(OFXTransaction.is_interest_charge.__ne__(True)) == str(
kall[1][5])
assert str(OFXTransaction.is_other_fee.__ne__(True)) == str(kall[1][6])
assert str(OFXTransaction.is_interest_payment.__ne__(True)) == str(
kall[1][7])
def get(self):
res = []
for t in OFXTransaction.unreconciled(db_session).order_by(
OFXTransaction.date_posted).all():
d = t.as_dict
d['account_name'] = t.account.name
d['account_amount'] = t.account_amount
res.append(d)
return jsonify(res)
def num_unreconciled_ofx(sess=None):
"""
Return the number of unreconciled OFXTransactions.
:return: number of unreconciled OFXTransactions
:rtype: int
"""
if sess is None:
sess = db_session
return OFXTransaction.unreconciled(sess).count()
def _reconcile_drag_postshot(self):
logger.info('Reconcile drag postshot')
conn = engine.connect()
logger.info('Updating DB')
data_sess = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=conn))
for t in OFXTransaction.unreconciled(data_sess):
data_sess.delete(t)
data_sess.flush()
data_sess.commit()
data_sess.close()
conn.close()
def test_000_setup_interest_charge_in_db(self, testdb):
acct = testdb.query(Account).get(4)
stmt = testdb.query(OFXStatement).get(7)
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='%s-MANUAL-CCPAYOFF' %
dtnow().strftime('%Y%m%d%H%M%S'),
trans_type='debit',
date_posted=stmt.as_of,
amount=Decimal('46.9061'),
name='Interest Charged - MANUALLY ENTERED',
is_interest_charge=True)
testdb.add(txn)
testdb.commit()
def test_simple(self):
res = OFXTransaction.params_from_ofxparser_transaction(
self.trans, self.acct_id, self.stmt)
assert res == {
'account_id': self.acct_id,
'statement': self.stmt,
'memo': 'TMemo',
'name': 'PayeeName',
'amount': 123.45,
'trans_type': 'TType',
'date_posted': datetime(2017, 3, 10, 14, 15, 16, tzinfo=UTC),
'fitid': 'ABC123',
'sic': None,
'mcc': ''
}
def submit(self, data):
"""
Handle form submission; create or update models in the DB. Raises an
Exception for any errors.
:param data: submitted form data
:type data: dict
:return: message describing changes to DB (i.e. link to created record)
:rtype: str
"""
acct = db_session.query(Account).get(int(data['id']))
if acct is None:
raise RuntimeError('ERROR: No Account with ID %s' % data['id'])
stmt = db_session.query(OFXStatement).filter(
OFXStatement.account_id.__eq__(acct.id),
OFXStatement.filename.__eq__(data['filename'])
).one()
if stmt is None:
raise RuntimeError(
'ERROR: No OFXStatement for account %d with filename %s' % (
acct.id, data['filename']
)
)
int_amt = Decimal(data['interest_amt'])
if int_amt < Decimal('0'):
int_amt = int_amt * Decimal('-1')
trans = OFXTransaction(
account=acct,
statement=stmt,
fitid='%s-MANUAL-CCPAYOFF' % dtnow().strftime('%Y%m%d%H%M%S'),
trans_type='debit',
date_posted=stmt.as_of,
amount=int_amt,
name='Interest Charged - MANUALLY ENTERED',
is_interest_charge=True
)
logger.info(
'Adding manual interest transaction to OFXTransactions: '
'account_id=%d statement_filename=%s statement=%s '
'OFXTransaction=%s', acct.id, data['filename'], stmt,
trans
)
db_session.add(trans)
db_session.commit()
return 'Successfully saved OFXTransaction with FITID %s in database' \
'.' % trans.fitid
def _update_bank_or_credit(self, acct, ofx, stmt):
"""
Update a single OFX file for this Bank or Credit account.
:param acct: the Account this statement is for
:type acct: biweeklybudget.models.account.Account
:param ofx: Ofx instance for parsed file
:type ofx: ``ofxparse.ofxparse.Ofx``
:param stmt: the OFXStatement for this statement
:type stmt: biweeklybudget.models.ofx_statement.OFXStatement
:returns: the OFXStatement object
:rtype: biweeklybudget.models.ofx_statement.OFXStatement
"""
# Note that as of 0.16, OfxParser returns tz-naive UTC datetimes
logger.debug('Updating Bank/Credit account')
if hasattr(ofx.account.statement, 'available_balance'):
stmt.avail_bal = ofx.account.statement.available_balance
if hasattr(ofx.account.statement, 'available_balance_date'):
stmt.avail_bal_as_of = \
ofx.account.statement.available_balance_date.replace(tzinfo=UTC)
stmt.ledger_bal = ofx.account.statement.balance
stmt.ledger_bal_as_of = \
ofx.account.statement.balance_date.replace(tzinfo=UTC)
db_session.add(stmt)
acct.set_balance(
overall_date=stmt.as_of,
ledger=stmt.ledger_bal,
ledger_date=stmt.ledger_bal_as_of,
avail=stmt.avail_bal,
avail_date=stmt.avail_bal_as_of
)
for txn in ofx.account.statement.transactions:
try:
kwargs = OFXTransaction.params_from_ofxparser_transaction(
txn, acct.id, stmt, cat_memo=acct.ofx_cat_memo_to_name
)
except RuntimeError as ex:
logger.error(ex)
continue
upsert_record(
OFXTransaction,
['account_id', 'fitid'],
**kwargs
)
return stmt
def test_extra_attrs(self):
self.trans.mcc = 'TMCC'
self.trans.sic = 456
self.trans.checknum = 789
res = OFXTransaction.params_from_ofxparser_transaction(
self.trans, self.acct_id, self.stmt)
assert res == {
'account_id': self.acct_id,
'statement': self.stmt,
'memo': 'TMemo',
'name': 'PayeeName',
'amount': 123.45,
'trans_type': 'TType',
'date_posted': datetime(2017, 3, 10, 14, 15, 16, tzinfo=UTC),
'fitid': 'ABC123',
'sic': 456,
'mcc': 'TMCC',
'checknum': 789
}
def test_0_update_db(self, testdb):
b = testdb.query(Budget).get(4)
b.current_balance = 1617.56
testdb.add(b)
acct = testdb.query(Account).get(1)
budget = testdb.query(Budget).get(1)
pp = BiweeklyPayPeriod.period_for_date(dtnow(), testdb)
tdate = pp.start_date + timedelta(days=2)
stmt1 = OFXStatement(account=acct,
filename='a1.ofx',
file_mtime=dtnow(),
as_of=dtnow(),
currency='USD',
acctid='1',
bankid='b1',
routing_number='r1')
testdb.add(stmt1)
o = OFXTransaction(account=acct,
statement=stmt1,
fitid='OFX8',
trans_type='Purchase',
date_posted=dtnow(),
amount=-600.0,
name='ofx8-trans4')
testdb.add(o)
t = Transaction(date=tdate,
actual_amount=600.00,
description='trans6',
account=acct,
budget=budget)
testdb.add(t)
testdb.add(TxnReconcile(transaction=t, ofx_trans=o))
testdb.add(
Transaction(date=tdate,
actual_amount=34000.00,
description='transFoo',
account=acct,
budget=budget))
testdb.flush()
testdb.commit()
def _credit_payoff_preshot(self):
logger.info('credit payoff preshot - DB update')
conn = engine.connect()
data_sess = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=conn))
acct = data_sess.query(Account).get(4)
stmt = data_sess.query(OFXStatement).get(7)
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='%s-MANUAL-CCPAYOFF' %
dtnow().strftime('%Y%m%d%H%M%S'),
trans_type='debit',
date_posted=stmt.as_of,
amount=Decimal('46.9061'),
name='Interest Charged - MANUALLY ENTERED',
is_interest_charge=True)
data_sess.add(txn)
data_sess.commit()
data_sess.close()
conn.close()
logger.info('credit payoff preshot done')
self.get('/accounts/credit-payoff')
def test_account_amount_negated(self):
ot = OFXTransaction(account=Mock(spec_set=Account,
negate_ofx_amounts=True),
amount=Decimal(123.45))
assert float(ot.account_amount) == -123.45
def test_account_amount(self):
ot = OFXTransaction(account=Mock(spec_set=Account,
negate_ofx_amounts=False),
amount=Decimal(123.45))
assert ot.account_amount == 123.45
def test_0_is_fields_set_by_ofxtxn_event_handler(self, testdb):
"""
Test for
:py:func:`~.db_event_handlers.handle_ofx_transaction_new_or_change`
"""
acct = testdb.query(Account).get(1)
acct.re_interest_paid = None
testdb.commit()
assert acct.re_interest_charge == '^interest-charge'
assert acct.re_interest_paid is None
assert acct.re_payment == '^(payment|thank you)'
assert acct.re_late_fee == '^Late Fee'
assert acct.re_other_fee == '^re-other-fee'
stmt = testdb.query(OFXStatement).get(1)
assert stmt.account_id == 1
assert stmt.filename == '/stmt/BankOne/0'
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='BankOne-9-1',
trans_type='Credit',
date_posted=stmt.ledger_bal_as_of,
amount=Decimal('1234.56'),
name='BankOne-9-1')
testdb.add(txn)
testdb.commit()
assert txn.is_payment is False
assert txn.is_late_fee is False
assert txn.is_interest_charge is False
assert txn.is_other_fee is False
assert txn.is_interest_payment is False
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='BankOne-9-2',
trans_type='Credit',
date_posted=stmt.ledger_bal_as_of,
amount=Decimal('1234.56'),
name='re-other-fee BankOne-9-2')
testdb.add(txn)
testdb.commit()
assert txn.is_payment is False
assert txn.is_late_fee is False
assert txn.is_interest_charge is False
assert txn.is_other_fee is True
assert txn.is_interest_payment is False
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='BankOne-9-3',
trans_type='Credit',
date_posted=stmt.ledger_bal_as_of,
amount=Decimal('1234.56'),
name='Late Fee BankOne-9-3')
testdb.add(txn)
testdb.commit()
assert txn.is_payment is False
assert txn.is_late_fee is True
assert txn.is_interest_charge is False
assert txn.is_other_fee is False
assert txn.is_interest_payment is False
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='BankOne-9-4',
trans_type='Credit',
date_posted=stmt.ledger_bal_as_of,
amount=Decimal('1234.56'),
name='payment BankOne-9-4')
testdb.add(txn)
testdb.commit()
assert txn.is_payment is True
assert txn.is_late_fee is False
assert txn.is_interest_charge is False
assert txn.is_other_fee is False
assert txn.is_interest_payment is False
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='BankOne-9-5',
trans_type='Credit',
date_posted=stmt.ledger_bal_as_of,
amount=Decimal('1234.56'),
name='Thank You BankOne-9-5')
testdb.add(txn)
testdb.commit()
assert txn.is_payment is True
assert txn.is_late_fee is False
assert txn.is_interest_charge is False
assert txn.is_other_fee is False
assert txn.is_interest_payment is False
txn = OFXTransaction(account=acct,
statement=stmt,
fitid='BankOne-9-6',
trans_type='Credit',
date_posted=stmt.ledger_bal_as_of,
amount=Decimal('1234.56'),
name='interest-paid')
testdb.add(txn)
testdb.commit()
assert txn.is_payment is False
assert txn.is_late_fee is False
assert txn.is_interest_charge is False
assert txn.is_other_fee is False
assert txn.is_interest_payment is False
def test_account_amount_negated(self):
ot = OFXTransaction(account=Mock(spec_set=Account,
negate_ofx_amounts=True),
amount=Decimal('123.45'))
assert ot.account_amount == Decimal('-123.45')
def test_03_add_ofx(self, testdb):
acct1 = testdb.query(Account).get(1)
stmt1 = OFXStatement(account=acct1,
filename='a1.ofx',
file_mtime=datetime(2017,
4,
10,
12,
31,
42,
tzinfo=UTC),
as_of=datetime(2017,
4,
10,
12,
31,
42,
tzinfo=UTC),
currency='USD',
acctid='1',
bankid='b1',
routing_number='r1')
testdb.add(stmt1)
testdb.add(
OFXTransaction(account=acct1,
statement=stmt1,
fitid='OFX1',
trans_type='Deposit',
date_posted=datetime(2017,
4,
10,
12,
3,
4,
tzinfo=UTC),
amount=-100.0,
name='ofx1-income'))
# matches Transaction 2
testdb.add(
OFXTransaction(account=acct1,
statement=stmt1,
fitid='OFX2',
trans_type='Debit',
date_posted=datetime(2017,
4,
11,
12,
3,
4,
tzinfo=UTC),
amount=250.0,
name='ofx2-trans1'))
# matches Transcation 3
testdb.add(
OFXTransaction(account=acct1,
statement=stmt1,
fitid='OFX3',
trans_type='Purchase',
date_posted=datetime(2017,
4,
9,
12,
3,
4,
tzinfo=UTC),
amount=-600.0,
name='ofx3-trans2-st1'))
testdb.flush()
testdb.commit()
