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()