def avg_amt(income):
curs, conn = dbm.DBconnect()
if income == 1:
income = 'true'
elif income == 2:
income = 'true or income= false'
else:
income = 'false'
query = "SELECT user_id,amount from transaction where user_id = {} and income=false".format(
session['id'])
curs.execute(query)
res = curs.fetchall()
sum1 = 0
count = 0
for i in res:
sum1 += i['amount']
count += 1
if (count == 0):
avg = 0
else:
avg = sum1 / count
average = {'avg': float(avg)}
dbm.DBclose(curs, conn)
return jsonify(average)
def testCredentials(username, password):
c, conn = dbm.DBconnect()
c.execute(
"SELECT * from user where username = '******' and password = '******'".format(
username, password))
res = c.fetchone()
dbm.DBclose(c, conn)
if res:
return True, res
return False, None
def readFromDebt(user_id):
c, conn = dbm.DBconnect()
try:
s = "Select * from debt where user_id = {} order by timestamp desc ".format(user_id)
# print(s)
c.execute(s)
result = c.fetchall()
conn.commit()
dbm.DBclose(c, conn)
return result
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
dbm.DBclose(c, conn)
def insertIntoDebt(user_id, amount, income, name, participant, category, timestamp,duedate):
c, conn = dbm.DBconnect();
# time = str(timestamp)
try:
s = "INSERT into debt(user_id, amount, income, name, participant, category, timestamp,duedate) values({},{},{},'{}', '{}', '{}','{}','{}');".format(
user_id, amount, income, name, participant, category, timestamp, duedate)
c.execute(s)
conn.commit()
dbm.DBclose(c, conn)
return True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
dbm.DBclose(c, conn)
return False
def registerUser(username, password):
c, conn = dbm.DBconnect()
try:
c.execute(
"INSERT into user(username,password) Values('{}', '{}')".format(
username, password))
conn.commit()
dbm.DBclose(c, conn)
return True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
dbm.DBclose(c, conn)
return False
def mean_ex_category(income):
curs, conn = dbm.DBconnect()
if income == 1:
income = 'true'
elif income == 2:
income = 'true or income= false'
else:
income = 'false'
query = "SELECT category ,SUM(amount) as 'sum', COUNT(amount) as 'count' FROM transaction where user_id = {} and (income={}) GROUP BY category".format(
session['id'], income)
curs.execute(query)
res = curs.fetchall()
average_expenditures_cat = dict()
for i in res:
average_expenditures_cat[i['category']] = float(i['sum'] / i['count'])
dbm.DBclose(curs, conn)
return jsonify(average_expenditures_cat)
def mean_ex_month(income):
curs, conn = dbm.DBconnect()
if income == 1:
income = 'true'
elif income == 2:
income = 'true or income= false'
else:
income = 'false'
query = "SELECT monthname(timestamp) as 'month' ,SUM(amount) as 'sum', COUNT(amount) as 'count' FROM transaction where user_id = {} and (income={}) GROUP BY monthname(timestamp)".format(
session['id'], income)
curs.execute(query)
average_expenditures = dict()
res = curs.fetchall()
for i in res:
average_expenditures[i['month']] = float(i['sum'] / i['count'])
dbm.DBclose(curs, conn)
return jsonify(average_expenditures)
def expenditures(income):
curs, conn = dbm.DBconnect()
if income == 1:
income = 'true'
elif income == 2:
income = 'true or income= false'
else:
income = 'false'
query = "SELECT monthname(timestamp) as 'month' ,SUM(amount) as 'sum' FROM transaction where user_id = {} and (income = {}) GROUP BY monthname(timestamp)".format(
session['id'], income)
curs.execute(query)
month = []
exps = []
for i in curs:
month.append(i['month'])
exps.append(float(i['sum']))
trace1 = {'x': month, 'y': exps, 'type': 'scatter'}
dbm.DBclose(curs, conn)
return jsonify(trace1)
def pie_chart(income):
curs, conn = dbm.DBconnect()
if income == 1:
income = 'true'
elif income == 2:
income = 'true or income= false'
else:
income = 'false'
query = "SELECT category ,SUM(amount)as 'sum' FROM transaction where user_id = {} and (income={}) GROUP BY category".format(
session['id'], income)
curs.execute(query)
category = []
exps = []
for i in curs:
category.append(i['category'])
exps.append(float(i['sum']))
trace1 = {'values': exps, 'labels': category, 'type': 'pie'}
dbm.DBclose(curs, conn)
return jsonify(trace1)