def refresh_expense(expenseid): sql = "select userid, notes, date, createtime from expenses where expenseid=%s;" % ( expenseid) out = Expense.query_sql(sql) if len(out) == 1: userid = out[0][0] content = out[0][1] date = out[0][2] createtime = out[0][3] if Expense.delete_obj_by_id(userid, expenseid) is True: ret_content = add_expense(userid, content) conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() sql_format = "update expenses as a inner join (select expenseid from " + \ " expenses where userid='%s' and notes='%s' limit 1) " + \ "as b on a.expenseid = b.expenseid set a.date='%s';" sql = sql_format % (userid, content, date) cursor.execute(sql) conn.commit() sql_format = "update expenses as a inner join (select expenseid from " + \ " expenses where userid='%s' and notes='%s' limit 1) " + \ "as b on a.expenseid = b.expenseid set a.createtime='%s';" sql = sql_format % (userid, content, createtime) cursor.execute(sql) conn.commit() print content, ret_content
def refresh_expense(expenseid): sql = "select userid, notes, date, createtime from expenses where expenseid=%s;" % (expenseid) out = Expense.query_sql(sql) if len(out) == 1: userid = out[0][0] content = out[0][1] date = out[0][2] createtime = out[0][3] if Expense.delete_obj_by_id(userid, expenseid) is True: ret_content = add_expense(userid, content) conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() sql_format = "update expenses as a inner join (select expenseid from " + \ " expenses where userid='%s' and notes='%s' limit 1) " + \ "as b on a.expenseid = b.expenseid set a.date='%s';" sql = sql_format % (userid, content, date) cursor.execute(sql) conn.commit() sql_format = "update expenses as a inner join (select expenseid from " + \ " expenses where userid='%s' and notes='%s' limit 1) " + \ "as b on a.expenseid = b.expenseid set a.createtime='%s';" sql = sql_format % (userid, content, createtime) cursor.execute(sql) conn.commit() print content, ret_content
def from_file(self, file, conn): # Loads previous expenses from a specified .txt file expenses = database.fetch_all(conn) for i in expenses: temp = Expense(i[2]) temp.date = datetime.strptime(i[3], '%Y-%m-%d').date() temp.currency = i[1] self.expense_list.append(temp)
def test_init(self): # If no parameters are provided with self.assertRaises(TypeError): exp3 = Expense() # If the cost is negative with self.assertRaises(ValueError): exp3 = Expense("Bag", -2)
def get_recent_expenses(self, lookback=5): """ Returns a list of Expense Class instances for the past {lookback} expenses""" cursor = self.conn.execute( 'SELECT * FROM expenses ORDER BY date DESC, id DESC') try: return [Expense(e) for e in cursor.fetchall()[:lookback]] except IndexError: # If lookback > length of recorded expenses return [Expense(e) for e in cursor.fetchall()]
def init_repo(self): self.__repo.add_item(Expense(1, 10, 'food')) self.__repo.add_item(Expense(1, 120, 'internet')) self.__repo.add_item(Expense(1, 112, 'food')) self.__repo.add_item(Expense(4, 20, 'food')) self.__repo.add_item(Expense(4, 100, 'electricity')) self.__repo.add_item(Expense(6, 980, 'phone')) self.__repo.add_item(Expense(8, 1, 'food')) self.__repo.add_item(Expense(8, 16, 'food')) self.__repo.add_item(Expense(23, 100, 'food')) self.__repo.add_item(Expense(30, 233, 'gas'))
def add_expense(self, username): amount, category_name, date = self.gather_info(category_type='expense') new_expense = Expense(amount, category_name, date) self.aggregated_object.expenses_list.append(new_expense) if date not in self.aggregated_object.dates_list: self.aggregated_object.dates_list.append(date) self.aggregated_object.save_changes(username)
def add_expense(): """ Adding expenses to the .csv file and the ListBox """ if name_field.get() == "" or cost_field.get() == "": error_message("You haven't entered any data!") try: # If the user tries to enter something else than a number cost = int(cost_field.get()) name = name_field.get() # Storing the name value temp_exp = Expense(name, cost) except ValueError: clear_entry() # Clearing the entry fields temp_exp = None error_message("You need to enter a number!") if temp_exp: """ If temp_exp is not None """ with open("data.csv", "a", newline='') as csv_file: """ Writing to the .csv file """ dict_writer = csv.DictWriter(csv_file, fieldnames=['name', 'cost']) dict_writer.writerow({ 'name': temp_exp.name, 'cost': temp_exp.cost }) expense_list.insert(tk.END, f"{temp_exp.name} - {temp_exp.cost}") clear_entry() # Clears entry fields exp.append(temp_exp) # Adding expense to expense list total_cost(exp) # Updating the total cost
def generate_general_comparision(userid): # TODO better performance count_threshold = 10 amount_threshold = 1000 sql_format = "select left(date, 7) as date, sum(amount) as sum, count(1) as count, avg(amount) as avg" + \ " from expenses where userid='%s' " + \ "and amount < %d group by left(date, 7) order by left(date, 7) desc limit 2;" sql = sql_format % (userid, amount_threshold) out = Expense.query_sql(sql) if len(out) == 0: sum_this = 0 count_this = 0 else: sum_this = float(out[0][1]) count_this = int(out[0][2]) # if count_this > count_threshold: # msg = "本月共花了%s元。按这速度,本月会花掉%s元。" % \ # (util.format_float_str(sum_this), \ # util.format_float_str(util.predict_amount(sum_this))) # else: msg = "本月共花了%s元。" % \ (util.format_float_str(sum_this)) if len(out) >= 2: sum_last = float(out[1][1]) count_last = int(out[1][2]) msg = "%s而上月共花了%s元。" % (msg, util.format_float_str(sum_last)) return msg
def read_expense(): day, amount, type = input('Input expense: <day> <money_amount> <type>\n').split(' ') day = int(day) amount = int(amount) expense = Expense(day, amount, type) return expense
def api_summary_daily(): userid = request.args.get('userid', '') fromdate = request.args.get('fromdate', '') enddate = request.args.get('enddate', '') sql_format = "select left(date, 10), sum(amount) from expenses" + \ " where userid='%s' and date >='%s' and date<='%s' group by left(date, 10) order by left(date, 10) asc;" sql = sql_format % (userid, fromdate, enddate) out = Expense.query_sql(sql) date_list = util.get_date_list(fromdate, enddate) date_dict = {} for date in date_list: date_dict[date] = "0" for entry in out: date_dict[entry[0]] = entry[1] key_list = sorted(date_dict.keys()) content = render_template('summary_daily.json', key_list=key_list, date_dict=date_dict) content = smarty_remove_extra_comma(content) return content
def api_summary(): userid = request.args.get('userid', '') categories = request.args.get('categories', '') fromdate = request.args.get('fromdate', '') enddate = request.args.get('enddate', '') if categories == "": category_list = [] else: category_list = categories.split(";") if len(category_list) == 0: sql = "select 'ALL' as name, sum(amount) as amount from expenses" + \ " where userid='" + userid +"' and date>='" + fromdate + \ "' and date<='" + enddate + "'" else: categories = ",".join(["'%s'" % (k) for k in category_list]) sql = "select category as name, sum(amount) as amount from expenses" + \ " where userid='" + userid +"' and date>='" + fromdate + \ "' and date<='" + enddate + "' and category in ("+ \ categories +") group by category" out = Expense.query_sql(sql) if len(out) == 0: out = [] else: if len(out) == 1: if out[0][1] is None: out = [] content = render_template('summary.json', objs=out) content = smarty_remove_extra_comma(content) return content
def preload_list(self): ''' Preloads the liist with 10 predefined expenses Input: - Output: - ''' self.add_expense(Expense(1, 5, "cloathing")) self.add_expense(Expense(30, 10, "food")) self.add_expense(Expense(2, 15, "housekeeping")) self.add_expense(Expense(29, 20, "internet")) self.add_expense(Expense(3, 25, "others")) self.add_expense(Expense(28, 30, "transport")) self.add_expense(Expense(4, 35, "others")) self.add_expense(Expense(27, 40, "internet")) self.add_expense(Expense(5, 45, "housekeeping")) self.add_expense(Expense(26, 50, "food"))
def generate_expense_msg(userid, category): (user_count, user_amount, avg_count, avg_amount) \ = Expense.get_expense_msg(userid, category) avg_count = avg_count * 3 # TODO: hack here category_msg = "你最近30天在'%s'上,一共消费%s元。" % \ (category, util.format_float_str(user_amount * user_count)) return category_msg
def preload_list(self): ''' Preloads the liist with 10 predefined expenses Input: - Output: - ''' self.add_expense(Expense(5, constants.CATEGORY_CLOATHING, 1)) self.add_expense(Expense(10, constants.CATEGORY_FOOD, 31)) self.add_expense(Expense(15, constants.CATEGORY_HOUSEKEEPING)) self.add_expense(Expense(20, constants.CATEGORY_INTERNET, 28)) self.add_expense(Expense(25, constants.CATEGORY_OTHERS, 29)) self.add_expense(Expense(30, constants.CATEGORY_TRANSPORT)) self.add_expense(Expense(35, constants.CATEGORY_OTHERS, 30)) self.add_expense(Expense(40, constants.CATEGORY_INTERNET, 12)) self.add_expense(Expense(45, constants.CATEGORY_HOUSEKEEPING)) self.add_expense(Expense(50, constants.CATEGORY_FOOD, 4))
def delete_last_record(userid): sql_format = "select expenseid, notes from expenses" + \ " where userid='%s' order by expenseid desc limit 1" sql = sql_format % (userid) out = Expense.query_sql(sql) if len(out) == 0: content = "坑爹呀,你都没数据,删除啥?" else: expenseid = out[0][0] notes = out[0][1] if Expense.delete_obj_by_id(userid, str(expenseid)) is True: content = "上一条记录删除好了: %s。/:sun\n查看最近记录,输入: 搜索" % (notes) else: content = "Oops, 删除失败了。" return content.encode('utf-8', 'ignore')
def csv_to_list(exp): """ Fills the list on startup """ with open("data.csv") as csv_file: reader = csv.DictReader(csv_file, fieldnames=['name', 'cost']) for row in reader: """ Fills both the ListBox and the expense list """ expense_list.insert(tk.END, f"{row['name']} - {row['cost']}") exp.append(Expense(row['name'], int(row['cost'])))
def api_expense_detail(): userid = request.args.get('userid', '') fromdate = request.args.get('fromdate', '') enddate = request.args.get('enddate', '') out = Expense.query_obj_by_date(userid, fromdate, enddate) content = render_template('list_expense.json', objs=out) content = smarty_remove_extra_comma(content) return content
def get_expenses(self): sql = f"SELECT * FROM {self.table_name};" self.cursor.execute(sql) entries = [] for entry in self.cursor: entries.append(Expense(entry[0], entry[2], entry[3], entry[4])) print(f"Found {len(entries)} entries in database.") return entries
def search_record(userid, notes): notes = notes.encode('utf-8', 'ignore').strip() search_keyword = notes.replace(MSG.CODE_SEARCH, "") search_keyword = search_keyword.replace(MSG.CODE_SEARCH2, "") search_keyword = search_keyword.replace(MSG.CODE_SEARCH3, "") search_keyword = util.wash_sentence(search_keyword) expense_list = Expense.query_obj_by_notes(userid, search_keyword, 15) (code, date, category, amount, branding, comment, token_list) \ = split_expense_word(notes) if category != config.DEFAULT_CATEGORY: expense_list2 = Expense.query_obj_by_category(userid, category.encode("utf8"), 15) expense_list = expense_list + expense_list2 if search_keyword.replace("-", "").isdigit(): search_date = search_keyword if search_keyword.find("-") == -1: search_date = "-%s" % (search_keyword) expense_list2 = Expense.query_obj_by_date2(userid, search_date, 15) expense_list = expense_list + expense_list2 message = "" for obj in expense_list: message = "%s %s%s %s\n" % (message, obj.date[5:10].replace('-', '/'), \ # str(obj.amount).replace(".0", "")+"元", \ get_emoji(obj.category.encode("utf8"), "category"), obj.notes) if message == "" and search_keyword == "": return u"系统里一条记录都没有啵。亲,多记账呀,多记账" if message == "": message = u"无符合条件的记录。试下只输入: 搜索" message = message.rstrip("\n") content = MSG.REPLAY_SEARCH % (search_keyword, message, userid) return content.encode('utf-8', 'ignore')
def add(self, params): ''' Adds to list an expense Input: list - params from input Output: string - in case of error ''' cost_string = params[0] category = params[1] self.validator.validate_add(cost_string, category) cost = int(cost_string) self.add_expense(Expense(cost, category))
def api_topn_category(): userid = request.args.get('userid', '') limit = request.args.get('limit', '') fromdate = request.args.get('fromdate', '') enddate = request.args.get('enddate', '') sql_format = "select category, sum(amount) as amount from expenses" + \ " where userid='%s' and date >='%s' and date<='%s'" + \ " group by category order by sum(amount) desc limit %s;" sql = sql_format % (userid, fromdate, enddate, limit) out = Expense.query_sql(sql) content = render_template('topn_category.json', objs=out) content = smarty_remove_extra_comma(content) return content
def api_category_daily(): userid = request.args.get('userid', '') fromdate = request.args.get('fromdate', '') enddate = request.args.get('enddate', '') sql_format = "select category, left(date, 10), sum(amount) from expenses" + \ " where userid='%s' and date >='%s' and date<='%s' group by category, " + \ "left(date, 10) order by left(date, 10) asc;" sql = sql_format % (userid, fromdate, enddate) out = Expense.query_sql(sql) content = render_template('category_daily.json', objs=out) content = smarty_remove_extra_comma(content) return content
def insert(self, params): ''' Adds to list an expense with a specified day Input: list - params from input Output: string - in case of error ''' day_string = params[0] cost_string = params[1] category = params[2] self.validator.validate_insert(day_string, cost_string, category) day = int(day_string) cost = int(cost_string) self.add_expense(Expense(cost, category, day))
def add(self, params): ''' Adds to list an expense Input: list - params from input Output: Error - in case of error ''' day_string = params[0] cost_string = params[1] category = params[2] self.validator.validate_add(day_string, cost_string, category) day = int(day_string) cost = int(cost_string) self.add_expense(Expense(day, cost, category))
def api_delete_expense(): userid = request.values["userid"] expenseid = request.values["expenseid"] if Expense.delete_obj_by_id(userid, expenseid) is True: content = """ { "errorcode":"200", "message":"删除成功" } """ else: content = """ { "errorcode":"500", "message":"删除失败" } """ return content
def insert_expense(expense): conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() sql = Expense.generate_insert_sql(expense) log.info("SQL:" + sql) #print sql try: cursor.execute(sql) conn.commit() except MySQLdb.Error: log.error("SQL fail: %s" % sql) conn.rollback() cursor.close() # TODO: defensive check return True
def add_expense(): token = request.headers.get('Authorization') if token is None: return make_response( jsonify({ 'success': False, 'error': 'No Auth Token' }), 400) else: db = Database() user_id = AuthSystem.validate_token(token) query = {'_id': user_id} user = db.findUser(query) if user: expense = request.json user = User.from_dict(user) for category in user.getUserCategories(): if category.name == expense['expenseCategory']: category.addExpense( Expense(expense['item'], expense['amount'], expense['date'], expense['expenseCategory'])) user.updateTotalExpenses(expense['amount']) break user_dict = user.as_dict() updates = { 'categories': user_dict['categories'], 'totalExpenses': user_dict['totalExpenses'] } did_update = db.updateUser(query, updates) if did_update: return make_response(jsonify({'success': True}), 201) else: return make_response( jsonify({ 'success': False, 'error': 'Failed to update User!' }), 304) else: return make_response( jsonify({ 'success': False, 'error': 'User not found!' }), 404)
def add_expense(userid, content): wechat_user = user.get_user_by_id(userid) content = content.encode('utf-8', 'ignore').strip() # bypass word split #content = util.wash_sentence(content) (code, date, category, amount, brand, comment, token_list) \ = split_expense_word(" %s " % (content)) expense = Expense() expense.init_with_mysql(userid, amount, category, \ brand, date, content, config.DEFAULT_COMMENT) if code == config.RECORD_STATE_QUESTION: return chat.response_fail(expense, "?") if code == config.RECORD_STATE_MULTIPLE_RECORDS: return chat.response_fail(expense, "multiple_record") # print "====== Before (%s, %s, %d; %s) ====" % (category, brand, amount, expense.memo) # store tokens, if detection fail if category == "" or brand == "" or amount == -1: expense.memo = "" for text, start, end in token_list: expense.memo = "%s; %s" % (expense.memo, text) expense.memo = expense.memo.strip("; ") # print "====== After (%s, %s, %d; %s) ====" % (category, brand, amount, expense.memo) if chat.is_comment_meaningless(comment) is True: return chat.response_fail(expense, "empty_notes") if chat.is_comment_pure_alpha(comment) is True: return chat.response_fail(expense, "pure_alpha") if expense.amount == -1: return chat.reply_chat(expense, wechat_user) if expense.amount != -1: if insert_expense(expense) is False: rep_content = MSG.REPLY_EXPENSE_FAILED % comment log.error("user(%s) add_expense fail, content:%s" % (userid, content)) if (expense.category != config.DEFAULT_CATEGORY) and (expense.branding != config.DEFAULT_BRANDING): tips = "" # TODO to be implemented rep_content = MSG.REPLY_EXPENSE_ADDED % ( category + get_emoji(category, "category"), str(amount)) rep_content = chat.attach_detected_extra_msg(wechat_user, rep_content, expense) else: if expense.category != config.DEFAULT_CATEGORY: tips = "" # TODO to be implemented rep_content = MSG.REPLY_EXPENSE_ADDED_WITH_CATEGORY % (\ category + get_emoji(category, "category"), str(amount)) else: if expense.branding != config.DEFAULT_BRANDING: tips = "" # TODO to be implemented rep_content = MSG.REPLY_EXPENSE_ADDED_WITH_BRANDING % (\ brand + get_emoji(brand, "brand"), str(amount)) else: tips = "" # TODO to be implemented rep_content = MSG.REPLY_EXPENSE_ADDED_SIMPLE % (\ str(date[5:7]).lstrip("0"), str(date[8:10]).lstrip("0"), \ str(amount), comment, tips) rep_content = chat.attach_undetected_extra_msg(wechat_user, expense, rep_content) if expense.category == config.CATEGORY_INCOMING: rep_content = rep_content.replace("消费", "赚了") return rep_content
def __init__(self,amount,paid_by,splits): Expense.__init__(self,amount,paid_by,splits)
total += i.get_income() return total def get_total_expense(self): total = 0 for e in self.expense: total += e.get_expense() return total def get_profit_loss(self): if self.get_total_income() > self.get_total_expense(): return "Profit = Rs " + str(self.get_total_income() - self.get_total_expense()) elif self.get_total_income() < self.get_total_expense(): return "Loss = Rs " + str(self.get_total_expense() - self.get_total_income()) else: return "No Profit No Loss" if __name__ == "__main__": inclist = [] explist = [] i1 = Income('Box1', 1200) i2 = Income('Box2', 1400) e1 = Expense('Babloo', 200) inclist.append(i1) inclist.append(i2) explist.append(e1) p = Page('1-1-2020', inclist, explist) print(p.get_profit_loss())
def testDeleteLastRecord(self): msg = delete_last_record(self.userid) ## print msg self.assertNotEqual(msg, "") def _assert_add_expense(self, original_content, part_result_content, print_content = False): content = add_expense(self.userid, original_content) if print_content is True: print content self.assertNotEqual(content.find(part_result_content), -1) def _assert_search_record(self, search_content, part_result_content, print_content = False): content = search_record(self.userid, search_content) if print_content is True: print content self.assertNotEqual(content.find(part_result_content), -1) def suite(): suite = unittest.TestSuite() suite.addTest(WeixinTestCase("testAddExpense1")) suite.addTest(WeixinTestCase("testAddExpense2")) suite.addTest(WeixinTestCase("testSearchRecord")) suite.addTest(WeixinTestCase("testDeleteLastRecord")) return suite if __name__ == "__main__": Expense.delete_obj_by_id(UNITTEST_USERID, "") unittest.TextTestRunner().run(suite()) ## File : weixin_unittest.py
while login_window.sign_up: sign_up_window = SignUp() if not sign_up_window.is_closed: username = sign_up_window.username password = sign_up_window.password connection = database.connect() database.add_user(connection, username, password) login_window = LogIn() if login_window.is_closed: is_true = False if login_window.create_homepage: homepage_window = HomePage(login_window.table_name) while not homepage_window.is_closed: if homepage_window.expense: expense_window = Expense(login_window.table_name) homepage_window = HomePage(login_window.table_name) if homepage_window.income: income_window = Income(login_window.table_name) homepage_window = HomePage(login_window.table_name) if homepage_window.transaction: transaction_window = Transaction(login_window.table_name) homepage_window = HomePage(login_window.table_name) if homepage_window.delete: delete_window = Delete(login_window.table_name) homepage_window = HomePage(login_window.table_name) if homepage_window.analysis: analysis_window = Analysis(login_window.table_name) homepage_window = HomePage(login_window.table_name) if homepage_window.suggestion: suggestion_window = Savings(login_window.table_name)
def refresh_not_detected_expense(): sql = "select expenseid from expenses where memo!='detected';" out = Expense.query_sql(sql) for obj in out: expenseid = str(obj[0]) refresh_expense(expenseid)