def create_activity(self, amount):
return BankAccountActivity(
bank_account=self.bank_account,
reference='Reference',
other_name='John Doe',
other_account_number='0123456789',
other_routing_number='01245',
amount=amount,
imported_at=datetime.utcnow(),
posted_on=datetime.utcnow().date(),
valid_on=datetime.utcnow().date(),
)
def process_transactions(bank_account, statement):
transactions = [] # new transactions which would be imported
old_transactions = [] # transactions which are already imported
doubtful_transactions = [] # transactions which may be changed by the bank because they are to new
for transaction in statement:
iban = transaction.data.get('applicant_iban', '')
if iban is None: iban = ''
bic = transaction.data.get('applicant_bin', '')
if bic is None: bic = ''
other_name = transaction.data.get('applicant_name', '')
if other_name is None: other_name = ''
purpose = transaction.data.get('purpose', '')
if purpose is None: purpose = ''
if 'end_to_end_reference' in transaction.data and \
transaction.data['end_to_end_reference'] is not None:
purpose = purpose + ' EREF+' + transaction.data['end_to_end_reference']
new_activity = BankAccountActivity(
bank_account_id=bank_account.id,
amount=transaction.data['amount'].amount,
reference=purpose,
other_account_number=iban,
other_routing_number=bic,
other_name=other_name,
imported_at=session.utcnow(),
posted_on=transaction.data['guessed_entry_date'],
valid_on=transaction.data['date'],
)
if new_activity.posted_on >= date.today():
doubtful_transactions.append(new_activity)
elif BankAccountActivity.q.filter(and_(
BankAccountActivity.bank_account_id ==
new_activity.bank_account_id,
BankAccountActivity.amount == new_activity.amount,
BankAccountActivity.reference == new_activity.reference,
BankAccountActivity.other_account_number ==
new_activity.other_account_number,
BankAccountActivity.other_routing_number ==
new_activity.other_routing_number,
BankAccountActivity.other_name == new_activity.other_name,
BankAccountActivity.posted_on == new_activity.posted_on,
BankAccountActivity.valid_on == new_activity.valid_on
)).first() is None:
transactions.append(new_activity)
else:
old_transactions.append(new_activity)
return (transactions, old_transactions, doubtful_transactions)
def process_transactions(bank_account, statement):
transactions = [] # new transactions which would be imported
old_transactions = [] # transactions which are already imported
for transaction in statement:
iban = transaction.data['applicant_iban'] if \
transaction.data['applicant_iban'] is not None else ''
bic = transaction.data['applicant_bin'] if \
transaction.data['applicant_bin'] is not None else ''
other_name = transaction.data['applicant_name'] if \
transaction.data['applicant_name'] is not None else ''
new_activity = BankAccountActivity(
bank_account_id=bank_account.id,
amount=int(transaction.data['amount'].amount * 100),
reference=transaction.data['purpose'],
original_reference=transaction.data['purpose'],
other_account_number=iban,
other_routing_number=bic,
other_name=other_name,
imported_at=session.utcnow(),
posted_on=transaction.data['entry_date'],
valid_on=transaction.data['date'],
)
if BankAccountActivity.q.filter(
and_(
BankAccountActivity.bank_account_id
== new_activity.bank_account_id, BankAccountActivity.amount
== new_activity.amount, BankAccountActivity.reference
== new_activity.reference,
BankAccountActivity.other_account_number
== new_activity.other_account_number,
BankAccountActivity.other_routing_number
== new_activity.other_routing_number,
BankAccountActivity.other_name == new_activity.other_name,
BankAccountActivity.posted_on == new_activity.posted_on,
BankAccountActivity.valid_on
== new_activity.valid_on)).first() is None:
transactions.append(new_activity)
else:
old_transactions.append(new_activity)
return (transactions, old_transactions)
def import_bank_account_activities_csv(csv_file, expected_balance,
imported_at=None):
"""
Import bank account activities from a MT940 CSV file into the database.
The new activities are merged with the activities that are already saved to
the database.
:param csv_file:
:param expected_balance:
:param imported_at:
:return:
"""
if imported_at is None:
imported_at = session.utcnow()
# Convert to MT940Record and enumerate
reader = csv.reader(csv_file, dialect=MT940Dialect)
records = enumerate((MT940Record._make(r) for r in reader), 1)
try:
# Skip first record (header)
next(records)
activities = tuple(
process_record(index, record, imported_at=imported_at)
for index, record in records)
except StopIteration:
raise CSVImportError(gettext(u"No data present."))
except csv.Error as e:
raise CSVImportError(gettext(u"Could not read CSV."), e)
if not activities:
raise CSVImportError(gettext(u"No data present."))
if not is_ordered((a[8] for a in activities), operator.ge):
raise CSVImportError(gettext(
u"Transaction are not sorted according to transaction date in "
u"descending order."))
first_posted_on = activities[-1][8]
balance = session.session.query(
func.coalesce(func.sum(BankAccountActivity.amount), 0)
).filter(
BankAccountActivity.posted_on < first_posted_on
).scalar()
a = tuple(session.session.query(
BankAccountActivity.amount, BankAccountActivity.bank_account_id,
BankAccountActivity.reference, BankAccountActivity.reference,
BankAccountActivity.other_account_number,
BankAccountActivity.other_routing_number,
BankAccountActivity.other_name, BankAccountActivity.imported_at,
BankAccountActivity.posted_on, BankAccountActivity.valid_on
).filter(
BankAccountActivity.posted_on >= first_posted_on)
)
b = tuple(reversed(activities))
matcher = difflib.SequenceMatcher(a=a, b=b)
for tag, i1, i2, j1, j2 in matcher.get_opcodes():
if 'equal' == tag:
continue
elif 'insert' == tag:
balance += sum(a[0] for a in islice(activities, j1, j2))
session.session.add_all(
BankAccountActivity(
amount=e[0], bank_account_id=e[1], reference=e[3],
other_account_number=e[4],
other_routing_number=e[5], other_name=e[6],
imported_at=e[7], posted_on=e[8], valid_on=e[9]
) for e in islice(activities, j1, j2)
)
elif 'delete' == tag:
continue
elif 'replace' == tag:
raise CSVImportError(
gettext(u"Import conflict:\n"
u"Database bank account activities:\n{0}\n"
u"File bank account activities:\n{1}").format(
u'\n'.join(str(x) for x in islice(activities, i1, i2)),
u'\n'.join(str(x) for x in islice(activities, j1, j2))))
else:
raise AssertionError()
if balance != expected_balance:
message = gettext(u"Balance after does not equal expected balance: "
u"{0} != {1}.")
raise CSVImportError(message.format(balance, expected_balance))
def bank_account_activities_edit(activity_id):
activity = BankAccountActivity.get(activity_id)
if activity is None:
flash(f"Bankbewegung mit ID {activity_id} existiert nicht!", 'error')
abort(404)
if activity.transaction_id is not None:
form = BankAccountActivityReadForm(
obj=activity, bank_account_name=activity.bank_account.name)
if activity.transaction_id:
flash(f"Bankbewegung ist bereits zugewiesen!", 'warning')
form_args = {
'form': form,
'show_submit': False,
'show_cancel': False,
}
return render_template('generic_form.html',
page_title="Bankbewegung",
form_args=form_args,
form=form)
else:
form = BankAccountActivityEditForm(
obj=activity,
bank_account_name=activity.bank_account.name,
description=activity.reference)
if form.validate_on_submit():
debit_account = Account.q.filter(
Account.id == form.account_id.data).one()
credit_account = activity.bank_account.account
transaction = finance.simple_transaction(
description=form.description.data,
debit_account=debit_account,
credit_account=credit_account,
amount=activity.amount,
author=current_user,
valid_on=activity.valid_on,
confirmed=current_user.member_of(config.treasurer_group))
activity.split = next(split for split in transaction.splits
if split.account_id == credit_account.id)
session.add(activity)
end_payment_in_default_memberships(current_user)
session.commit()
flash("Transaktion erfolgreich erstellt.", 'success')
return redirect(url_for('.bank_accounts_list'))
form_args = {
'form': form,
'cancel_to': url_for('.bank_accounts_list'),
'submit_text': 'Zuweisen',
}
return render_template('generic_form.html',
page_title="Bankbewegung zuweisen",
form_args=form_args,
form=form)