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)