def update(transaction):
sql = "UPDATE transactions SET (amount, merchant_id, tag_id) = (%s, %s, %s) WHERE id = %s"
values = [
transaction.amount, transaction.merchant.id, transaction.tag.id,
transaction.id
]
print(values)
run_sql(sql, values)
def save(transaction):
transaction.convert_to_pennies()
sql = "INSERT INTO transactions (amount, merchant_id, tag_id) VALUES (%s, %s, %s) RETURNING id"
values = [transaction.amount, transaction.merchant.id, transaction.tag.id]
results = run_sql(sql, values)
# pdb.set_trace()
transaction.id = results[0]['id']
return transaction
def select_all():
merchants = []
sql = "SELECT * FROM merchants"
results = run_sql(sql)
for row in results:
merchant = Merchant(row['name'], row['id'])
merchants.append(merchant)
return merchants
def select_all():
tags = []
sql = "SELECT * FROM tags"
results = run_sql(sql)
for row in results:
tag = Tag(row['category'], row['id'])
tags.append(tag)
return tags
def select(id):
tag = None
sql = "SELECT * FROM tags WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
tag = Tag(result['category'], result['id'])
return tag
def select(id):
merchant = None
sql = "SELECT * FROM merchants WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
merchant = Merchant(result['name'], result['id'])
return merchant
def merchants(tag):
merchants = []
sql = "SELECT merchants.* FROM merchants INNER JOIN transactions ON transactions.merchant_id = merchants.id WHERE tag_id = %s"
values = [tag.id]
results = run_sql(sql, values)
for row in results:
merchant = Merchant(row['name'], row['id'])
merchants.append(merchant)
return merchants
def select(id):
transaction = None
sql = "SELECT * FROM transactions WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
merchant = merchant_repository.select(result['merchant_id'])
tag = tag_repository.select(result['tag_id'])
transaction = Transaction(result['amount'], merchant, tag,
result['id'])
return transaction
def tags(merchant):
tags = []
sql = "SELECT tags.* FROM tags INNER JOIN transactions ON transactions.tag_id = tags.id WHERE merchant_id = %s"
values = [merchant.id]
results = run_sql(sql, values)
for row in results:
tag = Tag(row['category'], row['id'])
tags.append(tag)
return tags
def select_all():
transactions = []
sql = "SELECT * FROM transactions"
results = run_sql(sql)
for row in results:
merchant = merchant_repository.select(row['merchant_id'])
tag = tag_repository.select(row['tag_id'])
transaction = Transaction(row['amount'], merchant, tag, row['id'])
transactions.append(transaction)
return transactions
def delete_all():
sql = "DELETE FROM merchants"
run_sql(sql)
def total_transactions_by_tag():
sql = "SELECT tag_id, SUM (amount) AS totals FROM transactions GROUP BY tag_id ORDER BY totals DESC"
totals = run_sql(sql)
return totals
def delete_all():
sql = "DELETE FROM tags"
run_sql(sql)
def total_transactions_by_merchant():
sql = "SELECT merchant_id, SUM (amount) AS totals FROM transactions GROUP BY merchant_id ORDER BY totals DESC"
totals = run_sql(sql)
return totals
def save(tag):
sql = "INSERT INTO tags (category) VALUES (%s) RETURNING id"
values = [tag.category]
results = run_sql(sql, values)
tag.id = results[0]['id']
return tag
def tag(transaction):
sql = "SELECT * FROM tags WHERE id = %s"
values = [transaction.tag.id]
results = run_sql(sql, values)[0]
tag = Tag(results['category'], results['id'])
return tag
def delete(id):
sql = "DELETE FROM transactions WHERE id = %s"
values = [id]
run_sql(sql, values)
def update(merchant):
sql = "UPDATE merchants SET (name) = (%s) WHERE id = %s"
values = [merchant.name, merchant.id]
run_sql(sql, values)
def delete_all():
sql = "DELETE FROM transactions"
run_sql(sql)
def delete(id):
sql = "DELETE FROM merchants WHERE id = %s"
values = [id]
run_sql(sql, values)
def merchant(transaction):
sql = "SELECT * FROM merchants WHERE id = %s"
values = [transaction.merchant.id]
results = run_sql(sql, values)[0]
merchant = Merchant(results['name'], results['id'])
return merchant
def save(merchant):
sql = "INSERT INTO merchants (name) VALUES (%s) RETURNING id"
values = [merchant.name]
results = run_sql(sql, values)
merchant.id = results[0]['id']
return merchant
def total_transactions():
sql = "SELECT SUM (amount) FROM transactions"
result = run_sql(sql)
# pdb.set_trace()
total_as_pennies = result[0][0]
return total_as_pennies / 100
def update(tag):
sql = "UPDATE tags SET (category) = (%s) WHERE id = %s"
values = [tag.category, tag.id]
run_sql(sql, values)
