def edit_user(id, email, name, displayName='', photoURL=''):
try:
cursor = mysql.get_db().cursor()
cursor.execute(
"update user (id, email, displayName, name, photoURL) "
"set (%s, %s, %s, %s, %s)",
(id, email, displayName, name, photoURL),
)
mysql.get_db().commit()
except Exception as e:
print(e)
def create_user(id, email, name, displayName='', photoURL=''):
try:
cursor = mysql.get_db().cursor()
cursor.execute(
"INSERT INTO user (id, email, displayName, name, photoURL) "
"VALUES (%s, %s, %s, %s, %s)",
(id, email, displayName, name, photoURL),
)
mysql.get_db().commit()
except Exception as e:
print(e)
def delete_budget(id):
try:
cursor = mysql.get_db().cursor()
cursor.execute("""
delete from budget
where id = '{}'
""".format(id))
mysql.get_db().commit()
return {"message": "successful"}, 200
except Exception as e:
print(e)
def create_default_categories():
default_categories = [("laptop","Máy tính"), ("tv","Tivi"), ("watch","Công nghệ"),
("train","Phương tiện"),("print","Giấy tờ"), ("home","Thuê nhà"), ("picture","Du lịch")]
uid = request.json['uid']
try:
cursor = mysql.get_db().cursor()
values = ",".join(["('{}','{}','{}')".format(x[1], x[0], uid) for x in default_categories])
cursor.execute("insert into \
category (name, icon, user_id) \
values " + values)
mysql.get_db().commit()
return {"message":"successful", "result":"successful"}, 200
except Exception as e:
print(e)
def update_category(id):
try:
name, icon = request.json.get('name',""), request.json.get('icon',"")
cursor = mysql.get_db().cursor()
cursor.execute(""" update category
set name='{}', icon='{}'
where id='{}'""".format(name, icon, id))
mysql.get_db().commit()
result = {
"id": id,
"name": name,
"icon": icon
}
return {"message":"successful", "result":result}, 200
except Exception as e:
print(e)
def get_all_budgets():
try:
fromDate, toDate = request.args.get('fromDate', ""), request.args.get(
'toDate', "")
cursor = mysql.get_db().cursor()
cursor.execute(""" select b.*, c.name from budget b, category c
where user_id='{}' and b.category_id = c.id and
b.fromDate >= '{}' and b.toDate <= '{}'""".format(
g.user_id, fromDate, toDate))
budgets_list = cursor.fetchall()
if budgets_list:
result = list(
map(
lambda x: {
"id": x[0],
"among": x[1],
"fromDate": x[2],
"toDate": x[3],
"category": x[5],
"receipts": get_receipt_in_budget(x)
}, budgets_list))
return {"message": "successful", "result": result}, 200
return {"message": "successful", "result": []}, 200
except Exception as e:
print(e)
def get_receipt_in_budget(budget):
try:
cursor = mysql.get_db().cursor()
among, fromDate, toDate, category_id, category = budget[1], budget[
2], budget[3], budget[4], budget[5]
cursor.execute(""" select r.*, c.name from receipt r, category c
where r.user_id='{}' and r.category_id=c.id and r.category_id={}
and purchaseDate >= '{}' and purchaseDate <= '{}'"""
.format(g.user_id, category_id, fromDate, toDate))
receipt_list = cursor.fetchall()
if receipt_list:
result = list(
map(
lambda x: {
"id": x[0],
"purchaseDate": x[1],
"total": x[2],
"merchant": x[3],
"url_image": x[4],
"category": x[7],
"products": get_product_in_receipt(x[0]),
"user_id": g.user_id
}, receipt_list))
return result
return []
except Exception as e:
print(e)
def get_all_receipts():
try:
cursor = mysql.get_db().cursor()
fromDate, toDate = request.args.get("fromDate", ""), request.args.get(
"toDate", "")
cursor.execute(""" select r.*, c.name
from receipt r, category c
where r.user_id='{}' and r.category_id = c.id and
r.purchaseDate >= '{}' and r.purchaseDate <= '{}'
""".format(g.user_id, fromDate, toDate))
receipts_list = cursor.fetchall()
if receipts_list:
result = list(
map(
lambda x: {
"id": x[0],
"purchaseDate": x[1],
"total": x[2],
"merchant": x[3],
"url_image": x[4],
"category": x[7],
"user_id": g.user_id
}, receipts_list))
return {"message": "successful", "result": result}, 200
return {"message": "successful", "result": []}, 200
except Exception as e:
print(e)
def report_by_week_of_month():
try:
cursor = mysql.get_db().cursor()
(fromDate, toDate) = (request.args['fromDate'], request.args['toDate'])
cursor.execute("""select * from receipt
where purchaseDate >= '{}' and purchaseDate <= '{}' and user_id='{}'"""
.format(fromDate, toDate, g.user_id))
res = cursor.fetchall()
if res:
result = [{
"name": "week" + str(i),
"among": 0
} for i in range(1, 5)]
for x in res:
convert_2_date = dateutil.parser.parse(x[1]).strftime('%d')
if int(convert_2_date) < 8:
result[0]['among'] += float(x[2])
elif int(convert_2_date) < 15:
result[1]['among'] += float(x[2])
elif int(convert_2_date) < 22:
result[2]['among'] += float(x[2])
else:
result[3]['among'] += float(x[2])
return {"message": "success", "result": result}
return {"message": "success", "result": res}
except Exception as e:
print(e)
def create_category():
try:
name, icon = request.json.get('name',""), request.json.get('icon',"")
cursor = mysql.get_db().cursor()
cursor.execute("insert into \
category (name, icon, user_id) \
values (%s,%s,%s)",
(name, icon, g.user_id))
mysql.get_db().commit()
c_id = cursor.lastrowid
result = {
"id": c_id,
"name": name,
"icon": icon
}
return {"message":"successful", "result":result}, 200
except Exception as e:
print(e)
def delete_category(id):
try:
cursor = mysql.get_db().cursor()
cursor.execute(""" select id from receipt
where category_id='{}'""".format(id))
receipt = cursor.fetchall()
cursor.execute(""" select id from budget
where category_id='{}'""".format(id))
category = cursor.fetchall()
if not (receipt and category):
cursor.execute(""" delete from category
where id='{}'""".format(id))
mysql.get_db().commit()
return {"message":"successful"}, 200
return {"message":"Fail"}, 404
except Exception as e:
print(e)
def get_user(id):
try:
cursor = mysql.get_db().cursor()
cursor.execute("select * from user where id='{}'".format(id))
user = cursor.fetchone()
if user:
return User(id=user[0], email=user[1], name=user[2])
return None
except Exception as e:
print(e)
def update_budget(id):
try:
(among, fromDate, toDate, category_id) = ( request.json.get('among',""), request.json.get('fromDate',""),\
request.json.get('toDate',""), request.json.get('category_id',""))
cursor = mysql.get_db().cursor()
cursor.execute(
"update budget \
set among=%s, fromDate=%s, toDate=%s, category_id=%s \
where id=%s",
(among, fromDate, toDate, category_id, id))
mysql.get_db().commit()
result = {
"id": id,
"among": among,
"fromDate": fromDate,
"toDate": toDate,
"category_id": category_id
}
return {"message": "successful", "result": result}, 200
except Exception as e:
print(e)
def create_budget():
try:
(among, fromDate, toDate, category_id) = ( request.json.get('among',""), request.json.get('fromDate',""),\
request.json.get('toDate',""), request.json.get('category_id',""))
cursor = mysql.get_db().cursor()
cursor.execute(
"insert into \
budget (among, fromDate, toDate, category_id) \
values (%s,%s,%s,%s)", (among, fromDate, toDate, category_id))
mysql.get_db().commit()
b_id = cursor.lastrowid
result = {
"id": b_id,
"among": among,
"fromDate": fromDate,
"toDate": toDate,
"category_id": category_id
}
return {"message": "successful", "result": result}, 200
except Exception as e:
print(e)
def update_receipt(id):
try:
(purchaseDate, total, merchant, category_id, url_image) = (request.json.get("purchaseDate",""), request.json.get("total",""),\
request.json.get("merchant",""), request.json.get("category_id",""), request.json.get("url_image",""))
products = request.json['products']
cursor = mysql.get_db().cursor()
cursor.execute("""
delete from product
where receipt_id = '{}'
""".format(id))
cursor.execute(
"update receipt \
set purchaseDate=%s, total=%s, merchant=%s, category_id=%s, url_image=%s \
where id=%s",
(purchaseDate, total, merchant, category_id, url_image, id))
if products:
products_query = ",".join([
"('{}','{}','{}')".format(x["name"], x["price"], id)
for x in products
])
cursor.execute("insert into \
product (name, price, receipt_id) \
values " + products_query)
mysql.get_db().commit()
result = {
"id": id,
"purchaseDate": purchaseDate,
"total": total,
"merchant": merchant,
"category_id": category_id,
"url_image": url_image,
"products": products,
"user_id": g.user_id
}
return {"message": "successful", "result": result}, 200
except Exception as e:
print(e)
def create_receipt():
try:
(purchaseDate, total, merchant, category_id, url_image) = (request.json.get("purchaseDate",""), request.json.get("total",""),\
request.json.get("merchant",""), request.json.get("category_id",""), request.json.get("url_image",""))
products = request.json["products"]
cursor = mysql.get_db().cursor()
cursor.execute(
"insert into \
receipt (purchaseDate, total, merchant, url_image, category_id, user_id) \
values (%s,%s,%s,%s,%s,%s)",
(purchaseDate, total, merchant, url_image, category_id, g.user_id))
mysql.get_db().commit()
r_id = cursor.lastrowid
if products:
products_query = ",".join([
"('{}','{}','{}')".format(x["name"], x["price"], r_id)
for x in products
])
cursor.execute("insert into \
product (name, price, receipt_id) \
values " + products_query)
mysql.get_db().commit()
result = {
"id": r_id,
"purchaseDate": purchaseDate,
"total": total,
"merchant": merchant,
"category_id": category_id,
"url_image": url_image,
"products": products,
"user_id": g.user_id
}
return {"message": "successful", "result": result}, 200
except Exception as e:
print(e)
def get_all_categories():
try:
cursor = mysql.get_db().cursor()
cursor.execute("select * from category where user_id='{}'".format(g.user_id))
categories_list = cursor.fetchall()
if categories_list:
result = list(map(lambda x: {
"id": x[0],
"name": x[1],
"icon": x[2]
},categories_list))
return {"message":"successful", "result":result}, 200
return {"message":"successful", "result":[]}, 200
except Exception as e:
print(e)
def get_product_in_receipt(receipt_id):
try:
cursor = mysql.get_db().cursor()
cursor.execute(""" select * from product
where receipt_id='{}'""".format(receipt_id))
products = cursor.fetchall()
if products:
result = list(
map(lambda x: {
"id": x[0],
"name": x[1],
"price": x[2],
}, products))
return result
return []
except Exception as e:
print(e)
def report_by_category():
try:
cursor = mysql.get_db().cursor()
(fromDate, toDate) = (request.args['fromDate'], request.args['toDate'])
cursor.execute(""" select category.name, sum(receipt.total)
from receipt, category
where purchaseDate >= '{}' and purchaseDate <= '{}' and receipt.user_id='{}'
and receipt.category_id = category.id
group by category.name """.format(
fromDate, toDate, g.user_id))
res = cursor.fetchall()
if res:
result = list(map(lambda x: {
"category": x[0],
"among": x[1]
}, res))
return {"message": "success", "result": result}
return {"message": "success", "result": res}
except Exception as e:
print(e)
def get_receipt(id):
try:
cursor = mysql.get_db().cursor()
cursor.execute(""" select r.*, c.name
from receipt r, category c
where r.id='{}' and r.category_id = c.id
and r.user_id='{}'""".format(id, g.user_id))
receipt = cursor.fetchone()
cursor.execute(""" select *
from product
where receipt_id={}""".format(id))
products_in_receipt = cursor.fetchall()
products = []
if products_in_receipt:
products = list(
map(lambda x: {
"id": x[0],
"name": x[1],
"price": x[2],
}, products_in_receipt))
if receipt:
result = {
"id": receipt[0],
"purchaseDate": receipt[1],
"total": receipt[2],
"merchant": receipt[3],
"url_image": receipt[4],
"category": receipt[7],
"products": products,
"user_id": g.user_id
}
return {"message": "successful", "result": result}, 200
return {"message": "successful", "result": {}}, 200
except Exception as e:
print(e)
