def add_api_transaction(json):
session = get_session()
added = 0
invalid = 0
for entry in json:
if valid_transaction_entry(entry):
if 'month' not in entry:
entry['month'] = entry['date'][0:2] + entry['date'][8:10]
if 'notes' not in entry:
entry['notes'] = ''
if 'category' not in entry:
entry['category'] = ''
transaction = Transactions(date=entry['date'],
month=entry['month'],
vendor=entry['vendor'],
amount=entry['amount'],
notes=entry['notes'],
flow=entry['flow'],
category=entry['category'])
session.add(transaction)
else:
invalid += 1
try:
session.commit()
added += 1
except sqlalchemy.exc.OperationalError as ex:
print(ex)
added = -1
except sqlalchemy.exc.IntegrityError as ex:
# TODO: This should be optimized to so we aren't getting new sessions all of the time
session = get_session()
print(ex)
return added, invalid
def get_categories(month):
session = get_session()
results = session.query(Budget.name).filter(Budget.month == month).all()
categories = []
for row in results:
categories.append(row[0])
return categories
def get_rules():
session = get_session()
results = session.query(CategoryRules).all()
rules = []
for row in results:
rules.append({'regex': row.name, 'category': row.category})
return rules
def remove_transaction(id):
session = get_session()
transaction_query = session.query(Transactions).filter(
Transactions.id == id)
if transaction_query.count() == 0:
raise KeyError('{} was not found!'.format(id))
transaction = transaction_query.all()[0]
session.delete(transaction)
return True
def add_budget(month, name, amount, flow):
print("Adding {} with amount {} to month {}".format(name, amount, month))
try:
session = get_session()
budget = Budget(month=month, name=name, amount=amount, flow=flow)
session.add(budget)
session.commit()
return True
except Exception as ex:
print(ex)
return False
def refresh_transactions(month):
rules = get_rules()
session = get_session()
query = session.query(Transactions).filter(Transactions.month == month)
for row in query.all():
dict_row = row.__dict__
for rule in rules:
print("checking {} against {}".format(dict_row['vendor'],
rule['regex']))
if re.match(rule['regex'], str.lower(dict_row['vendor'])):
print("updating {} to category {}".format(
dict_row['vendor'], rule['category']))
row.category = rule['category']
break
session.commit()
def add_rule(regex, category):
try:
session = get_session()
regex = str.lower(regex)
result = session.query(CategoryRules).filter(CategoryRules.name == regex).first()
if result is None:
rule = CategoryRules(name=str.lower(regex), category=category)
session.add(rule)
else:
result.category = category
session.commit()
return True
except Exception as ex:
print(ex)
# TODO: Make this more specific
return False
def login():
if current_user.is_authenticated:
return redirect(url_for('budget'))
form = LoginForm()
if form.validate_on_submit():
user = get_session().query(User).filter_by(
username=form.username.data).first()
if user is None or not user.check_password(form.password.data):
flash('Invalid login')
return redirect(url_for('login'))
login_user(user, remember=form.remember_me.data)
# next_page = request.args.get('next')
# if not next_page or url_for(next_page).netloc != '':
next_page = url_for('budget')
print(next_page)
return redirect(next_page)
return render_template('login.html', form=form)
def get_totals(month, category, flow):
session = get_session()
if flow != EXPENSE and flow != INCOME:
raise ValueError('Flow should be {} or {}'.format(EXPENSE, INCOME))
query = session.query(Transactions.category, Transactions.amount).filter(
Transactions.month == month, Transactions.flow == flow)
if category is not None:
query = query.filter(Transactions.category == category)
query.group_by(Transactions.category)
totals = {}
for item in query.all():
cat = item[0]
if cat == '':
cat = 'Uncategorized'
if cat not in totals:
totals[cat] = 0
totals[cat] += item[1]
return totals
def get_transactions(month, category=None, flow=None):
print(month)
session = get_session()
query = session.query(Transactions).filter(Transactions.month == month)
if category is not None:
query = query.filter(Transactions.category == category)
if flow is not None:
query = query.filter(Transactions.flow == flow)
transactions = []
for row in query.all():
dict_item = row.__dict__
transaction = {
'date': dict_item['date'],
'name': dict_item['vendor'],
'category': dict_item['category'],
'amount': dict_item['amount'],
'id': dict_item['id']
}
transactions.append(transaction)
return transactions
def remote_import_transactions():
session = get_session()
# Pull transactions for the last 30 days
start_date = '{:%Y-%m-%d}'.format(datetime.datetime.now() +
datetime.timedelta(-30))
end_date = '{:%Y-%m-%d}'.format(datetime.datetime.now())
rules = get_rules()
try:
# item_id = DgPQ0POqJrIEMLMrNxxPF4Z3jZnjPvFwyz1Kp
access_token = "access-development-7ae93fc0-c4f2-4f35-b2e2-952eeb011db7"
transactions_response = client.Transactions.get(
access_token, start_date, end_date)
with open('transactions.json', 'w') as f:
f.write(json.dumps(transactions_response, indent=2,
sort_keys=True))
print("Found {} transactions".format(
transactions_response['total_transactions']))
for transaction in transactions_response['transactions']:
# category = check_rules(rules, transactions_response['name'])
category = ''
entry = Transactions(date=transaction['date'],
month=transaction['date'][5:7] +
transaction['date'][2:4],
vendor=transaction['name'],
amount=transaction['amount'],
flow=EXPENSE,
category=category,
transaction_id=transaction['transaction_id'])
try:
session.add(entry)
session.commit()
except sqlalchemy.exc.IntegrityError:
# Just a duplicate entry, ignore it
session.rollback()
pass
except plaid.errors.PlaidError as e:
return jsonify(format_error(e))
def get_income_totals(month, category=None):
session = get_session()
return get_totals(month, category, INCOME)
def get_expense_totals(month, category=None):
session = get_session()
return get_totals(month, category, EXPENSE)
def get_budget(month, category=None):
session = get_session()
query = session.query(Budget).filter(Budget.month == month)
if category is not None:
query = query.filter(Budget.name == category)
return query.all()
def verify_password(username, password):
user = get_session().query(User).filter_by(username=username).first()
if not user or not user.check_password(password):
return False
g.user = user
return True
def load_user(id):
return get_session().query(User).get(int(id))