def usr_log_in(window, var_usr_name, var_usr_pwd): # 输入框获取用户名密码 usr_name = var_usr_name.get().strip() usr_pwd = var_usr_pwd.get().strip() # 用户名密码不能为空 if usr_name == '' or usr_pwd == '': tk.messagebox.showerror(message='用户名或密码不能为空!') else: # 从数据库中获取用户信息 query = ("SELECT COUNT(*) FROM `user` WHERE username = '******'" % usr_name) cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) if cursor.fetchone()[0] == 1: # 判断用户名和密码是否匹配 query = ("SELECT username, password, is_manager FROM `user` WHERE username = '******' AND password = '******'" % ( usr_name, usr_pwd)) cursor.execute(query) result = cursor.fetchone() DbUtil.close_db(cursor, cnx) if result is not None: # tk.messagebox.showinfo(title='welcome', message='欢迎您:' + usr_name) # 进入主界面 is_manger = False if (result[2] == 0) else True manager_main(window, usr_name, is_manger) else: tk.messagebox.showerror(message='密码错误') # 不在数据库中弹出是否注册的框 else: is_signup = tk.messagebox.askyesno('欢迎', '您还没有注册,是否现在注册') if is_signup: usr_sign_up()
def inserttodb(): # 获取输入框内的内容 bido = b_id_o.get().strip() bid = b_id.get().strip() bname = b_name.get().strip() bprice = b_price.get().strip() tid = t_id.get().strip() bcount = b_count.get().strip() if bido == '' or bid == '' or bname == '' or bprice == '' or tid == '' or bcount == '': tk.messagebox.showerror(message='输入框不能为空') else: query = ("SELECT COUNT(*) FROM `bookinfo` WHERE bid='%s'" % (bido)) cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) if cursor.fetchone()[0] == 1: query = ( "UPDATE `bookinfo` SET bid='%s', bname='%s', bprice='%s', tid='%s', bcount='%s' WHERE tid='%s'" % ( bid, bname, bprice, tid, bcount, bido)) try: cursor.execute(query) cnx.commit() DbUtil.close_db(cursor, cnx) tk.messagebox.showinfo('成功', '修改图书信息成功') book_u.destroy() build_book(frame_book) except: tk.messagebox.showerror('错误', '修改图书信息失败') else: tk.messagebox.showerror('错误', '修改图书信息失败, 该图书编号不存在')
def export(): file_name = f_name.get().strip() if file_name == '': tk.messagebox.showerror(message='文件名不能为空') else: wb = openpyxl.Workbook() sheet = wb.active sheet['A1'] = '图书编号' sheet['B1'] = '图书名称' sheet['C1'] = '图书价格' sheet['D1'] = '图书类别' sheet['E1'] = '图书库存' query = ( "SELECT bid, bname, bprice, tname, bcount FROM `booktype`, `bookinfo` WHERE `booktype`.tid=`bookinfo`.tid") cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) i = 2 for (bid, bname, bprice, tname, bcount) in cursor: sheet['A%s' % i] = bid sheet['B%s' % i] = bname sheet['C%s' % i] = bprice sheet['D%s' % i] = tname sheet['E%s' % i] = bcount i = i + 1 DbUtil.close_db(cursor, cnx) time_file = time.strftime("%Y-%m-%d-%H-%M", time.localtime()) file_name = file_name + '-' + time_file + '.xlsx' wb.save(file_name) tk.messagebox.showinfo('成功', '导出图书信息成功') book_e.destroy()
def inserttodb(): # 获取输入框内的内容 tid = t_id.get().strip() tidn = t_id_n.get().strip() tnamen = t_name_n.get().strip() if tid == '' or tidn == '' or tnamen == '': tk.messagebox.showerror(message='类别编号或名称为空') else: query = ("SELECT COUNT(*) FROM `booktype` WHERE tid='%s'" % (tid)) cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) if cursor.fetchone()[0] == 1: query = ("UPDATE `booktype` SET tid='%s', tname='%s' WHERE tid='%s'" % (tidn, tnamen, tid)) try: cursor.execute(query) cnx.commit() DbUtil.close_db(cursor, cnx) tk.messagebox.showinfo('成功', '修改类别成功') type_u.destroy() build_type(frame_type) except: tk.messagebox.showerror('错误', '修改类别失败') else: tk.messagebox.showerror('错误', '修改类别失败, 该类别编号不存在')
def build_book(frame_book): for widget in frame_book.winfo_children(): widget.destroy() columns = ("书编号", "书名", "书价", "类别", "库存") treeview = ttk.Treeview(frame_book, columns=columns, show='headings') treeview.column("书编号", width=100, anchor='center') # 表示列,不显示 treeview.column("书名", width=100, anchor='center') treeview.column("书价", width=100, anchor='center') treeview.column("类别", width=100, anchor='center') treeview.column("库存", width=100, anchor='center') treeview.heading('书编号', text='书编号') treeview.heading('书名', text='书名') treeview.heading('书价', text='书价') treeview.heading('类别', text='类别') treeview.heading('库存', text='库存') treeview.grid() query = ( "SELECT bid, bname, bprice, tname, bcount FROM `booktype`, `bookinfo` WHERE `booktype`.tid=`bookinfo`.tid") cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) i = 1 for (bid, bname, bprice, tname, bcount) in cursor: treeview.insert('', i, values=(bid, bname, bprice, tname, bcount)) i = i + 1 DbUtil.close_db(cursor, cnx) for col in columns: # 给所有标题加(循环上边的“手工”) treeview.heading(col, text=col, command=lambda _col=col: treeview_sort_column(treeview, _col, False))
def signtowcg(): # 获取输入框内的内容 nun = new_username.get().strip() np = new_pwd.get().strip() npf = new_pwd_confirm.get().strip() nn = new_name.get().strip() ng = new_gender.get().strip() ne = new_email.get().strip() nt = new_telephone.get().strip() nm = new_manager.get().strip() if np == '' or nun == '' or npf == '' or nn == '' or ng == '' or ne == '' or nt == '' or nm == '': tk.messagebox.showerror('错误', '输入框不能为空!') elif np != npf: tk.messagebox.showerror('错误', '密码前后不一致') elif re.match(r'^[a-zA-Z0-9_.-]+@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*\.[a-zA-Z0-9]{2,6}$', ne) is None: tk.messagebox.showerror('错误', '邮箱格式不正确') elif re.match(r'^1([38][0-9]|4[579]|5[0-3,5-9]|6[6]|7[0135678]|9[89])\d{8}$', nt) is None: tk.messagebox.showerror('错误', '手机号格式不正确') else: query = ("SELECT COUNT(*) FROM `user` WHERE username = '******'" % (nun)) cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) if cursor.fetchone()[0] != 0: tk.messagebox.showerror('错误', '用户名已存在') else: query = ("INSERT INTO `user`(username, password, is_manager) VALUES ('%s', '%s', '%s')" % (nun, np, nm)) query1 = ( "INSERT INTO `userinfo`(username, password, `name`, gender, email, telephone) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')" % ( nun, np, nn, ng, ne, nt)) try: cursor.execute(query) cursor.execute(query1) cnx.commit() DbUtil.close_db(cursor, cnx) tk.messagebox.showinfo('欢迎', '注册成功') # 注册成功关闭注册框 window_sign_up.destroy() except: print() tk.messagebox.showinfo('错误', '注册失败') cnx.rollback()
def inserttodb(): # 获取输入框内的内容 tid = t_id.get().strip() tname = t_name.get().strip() if tid == '' or tname == '': tk.messagebox.showerror(message='类别编号或名称为空') else: query = ("INSERT INTO `booktype`(tid, tname) VALUES ('%s', '%s')" % (tid, tname)) try: cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) cnx.commit() DbUtil.close_db(cursor, cnx) tk.messagebox.showinfo('成功', '新增类别成功') type_c.destroy() build_type(frame_type) except: tk.messagebox.showerror('错误', '新增类别失败')
def delete_book_by_id(): # 获取输入框内的内容 bid = b_id.get().strip() if bid == '': tk.messagebox.showerror(message='图书编号为空') else: query = ("SELECT COUNT(*) FROM `bookinfo` WHERE bid='%s'" % (bid)) cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) if cursor.fetchone()[0] == 1: query = ("DELETE FROM `bookinfo` WHERE bid='%s'" % (bid)) try: cursor.execute(query) cnx.commit() DbUtil.close_db(cursor, cnx) tk.messagebox.showinfo('成功', '删除图书成功') book_d.destroy() build_book(frame_book) except: tk.messagebox.showerror('错误', '删除图书失败') else: tk.messagebox.showerror('错误', '删除图书失败, 该图书编号不存在')
def inserttodb(): # 获取输入框内的内容 bid = b_id.get().strip() bname = b_name.get().strip() bprice = b_price.get().strip() tid = t_id.get().strip() bcount = b_count.get().strip() if bid == '' or bname == '' or bprice == '' or tid == '' or bcount == '': tk.messagebox.showerror(message='输入框不能为空') else: query = ("INSERT INTO `bookinfo`(bid, bname, bprice, tid, bcount) VALUES ('%s', '%s', '%s', '%s', '%s')" % ( bid, bname, bprice, tid, bcount)) try: cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) cnx.commit() DbUtil.close_db(cursor, cnx) tk.messagebox.showinfo('成功', '新增图书信息成功') book_c.destroy() build_book(frame_book) except: tk.messagebox.showerror('错误', '新增图书信息失败')
def delete_type_by_id(): # 获取输入框内的内容 tid = t_id.get().strip() if tid == '': tk.messagebox.showerror(message='类别编号为空') else: query = ("SELECT COUNT(*) FROM `booktype` WHERE tid='%s'" % (tid)) cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) if cursor.fetchone()[0] == 1: query = ("DELETE FROM `booktype` WHERE tid='%s'" % (tid)) try: cursor.execute(query) cnx.commit() DbUtil.close_db(cursor, cnx) tk.messagebox.showinfo('成功', '删除类别成功') type_d.destroy() build_type(frame_type) except: tk.messagebox.showerror('错误', '删除类别失败') else: tk.messagebox.showerror('错误', '删除类别失败, 该类别编号不存在')
def build_type(frame_type): for widget in frame_type.winfo_children(): widget.destroy() columns = ("类别编号", "类别名") treeview = ttk.Treeview(frame_type, columns=columns, show='headings') treeview.column("类别编号", width=100, anchor='center') # 表示列,不显示 treeview.column("类别名", width=250, anchor='center') treeview.heading('类别编号', text='类别编号') treeview.heading('类别名', text='类别名') treeview.grid() query = ("SELECT tid, tname FROM `booktype`") cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) i = 1 for (tid, tname) in cursor: treeview.insert('', i, values=(tid, tname)) i = i + 1 DbUtil.close_db(cursor, cnx) for col in columns: # 给所有标题加(循环上边的“手工”) treeview.heading(col, text=col, command=lambda _col=col: treeview_sort_column(treeview, _col, False))
def on_treeview_click(event): cnx = DbUtil.open_db() cursor = cnx.cursor() for item in treeview.selection(): item_text = treeview.item(item, "values") bid = item_text[0] query = ("SELECT bcount FROM `bookinfo` WHERE `bookinfo`.bid = '%s'" % (bid)) cursor.execute(query) book_balance = cursor.fetchone()[0] query = ("SELECT scount FROM `shoplist` WHERE bid = '%s' AND username = '******'" % (bid, username)) cursor.execute(query) result = cursor.fetchone() shop_balance = 0 if result is None else result[0] if item_text[5] == '双击从购物车删除': if shop_balance > 1: query_update_shop = ( "UPDATE `shoplist` SET scount = '%s' WHERE username = '******' AND bid = '%s'" % ( shop_balance - 1, username, bid)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance + 1, bid)) try: cursor.execute(query_update_shop) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='从购物车删除成功!') except: cnx.rollback() tk.messagebox.showerror(message='从购物车删除失败!') elif shop_balance == 1: query_delete = ( "DELETE FROM `shoplist` WHERE username = '******' AND bid = '%s'" % (username, bid)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance + 1, bid)) try: cursor.execute(query_delete) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='从购物车删除成功!') except: cnx.rollback() tk.messagebox.showerror(message='从购物车删除失败!') else: tk.messagebox.showerror(message='无法从购物车删除,数量异常!') elif item_text[5] == '双击加入购物车': if shop_balance > 0: query_update_shop = ( "UPDATE `shoplist` SET scount = '%s' WHERE username = '******' AND bid = '%s'" % ( shop_balance + 1, username, bid)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance - 1, bid)) try: cursor.execute(query_update_shop) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='加入购物车成功!') except: tk.messagebox.showerror(message='加入购物车失败!') elif shop_balance == 0: query_insert = ( "INSERT INTO `shoplist`(username, bid, scount) VALUES ('%s', '%s', '%s')" % (username, bid, 1)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance - 1, bid)) try: cursor.execute(query_insert) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='加入购物车成功!') except: tk.messagebox.showerror(message='加入购物车失败!') else: tk.messagebox.showerror(message='无库存,无法加入购物车!') elif item_text[5] == '去付款': tk.messagebox.showerror(message='支付功能开发中......') else: tk.messagebox.showerror(message='非法操作!') DbUtil.close_db(cursor, cnx) build_shopping(frame_shopping, username, option)
def build_shopping(frame_shopping, username, option): def on_treeview_click(event): cnx = DbUtil.open_db() cursor = cnx.cursor() for item in treeview.selection(): item_text = treeview.item(item, "values") bid = item_text[0] query = ("SELECT bcount FROM `bookinfo` WHERE `bookinfo`.bid = '%s'" % (bid)) cursor.execute(query) book_balance = cursor.fetchone()[0] query = ("SELECT scount FROM `shoplist` WHERE bid = '%s' AND username = '******'" % (bid, username)) cursor.execute(query) result = cursor.fetchone() shop_balance = 0 if result is None else result[0] if item_text[5] == '双击从购物车删除': if shop_balance > 1: query_update_shop = ( "UPDATE `shoplist` SET scount = '%s' WHERE username = '******' AND bid = '%s'" % ( shop_balance - 1, username, bid)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance + 1, bid)) try: cursor.execute(query_update_shop) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='从购物车删除成功!') except: cnx.rollback() tk.messagebox.showerror(message='从购物车删除失败!') elif shop_balance == 1: query_delete = ( "DELETE FROM `shoplist` WHERE username = '******' AND bid = '%s'" % (username, bid)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance + 1, bid)) try: cursor.execute(query_delete) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='从购物车删除成功!') except: cnx.rollback() tk.messagebox.showerror(message='从购物车删除失败!') else: tk.messagebox.showerror(message='无法从购物车删除,数量异常!') elif item_text[5] == '双击加入购物车': if shop_balance > 0: query_update_shop = ( "UPDATE `shoplist` SET scount = '%s' WHERE username = '******' AND bid = '%s'" % ( shop_balance + 1, username, bid)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance - 1, bid)) try: cursor.execute(query_update_shop) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='加入购物车成功!') except: tk.messagebox.showerror(message='加入购物车失败!') elif shop_balance == 0: query_insert = ( "INSERT INTO `shoplist`(username, bid, scount) VALUES ('%s', '%s', '%s')" % (username, bid, 1)) query_update = ("UPDATE `bookinfo` SET bcount = '%s' WHERE bid = '%s'" % (book_balance - 1, bid)) try: cursor.execute(query_insert) cursor.execute(query_update) cnx.commit() tk.messagebox.showinfo(message='加入购物车成功!') except: tk.messagebox.showerror(message='加入购物车失败!') else: tk.messagebox.showerror(message='无库存,无法加入购物车!') elif item_text[5] == '去付款': tk.messagebox.showerror(message='支付功能开发中......') else: tk.messagebox.showerror(message='非法操作!') DbUtil.close_db(cursor, cnx) build_shopping(frame_shopping, username, option) # 如果是查看购物车,显示删除、+1、-1,到0就是删除 if option == 'shop': query = ( "SELECT i.bid bid, i.bname bname, i.bprice bprice, b.tname tname, i.bcount bcount, l.scount scount FROM `booktype` b, `bookinfo` i, `shoplist` l WHERE b.tid = i.tid AND i.bid = l.bid AND l.username = '******'" % ( username)) null_remind = '温馨提示:购物车暂无物品' book_opt = '双击从购物车删除' table_4 = '已购数量' # 如果是购物列表,显示添加到购物车,若以已经在购物车,不在购物列表显示 elif option == 'book': query = ( "SELECT i.bid bid, i.bname bname, i.bprice bprice, b.tname tname, i.bcount bcount FROM `booktype` b, `bookinfo` i WHERE b.tid = i.tid AND i.bid not in (SELECT DISTINCT bid FROM shoplist l WHERE l.username != '%s')" % ( username)) null_remind = '温馨提示:系统中暂无可购物品' book_opt = '双击加入购物车' table_4 = '库存' else: tk.messagebox.showerror(message='非法选择!') return False for widget in frame_shopping.winfo_children(): widget.destroy() columns = ("书编号", "书名", "书价", "类别", table_4, "操作") treeview = ttk.Treeview(frame_shopping, columns=columns, show='headings') treeview.column("书编号", width=100, anchor='center') # 表示列,不显示 treeview.column("书名", width=150, anchor='center') treeview.column("书价", width=90, anchor='center') treeview.column("类别", width=100, anchor='center') treeview.column(table_4, width=90, anchor='center') treeview.column("操作", width=200, anchor='center') treeview.heading('书编号', text='书编号') treeview.heading('书名', text='书名') treeview.heading('书价', text='书价') treeview.heading('类别', text='类别') treeview.heading(table_4, text=table_4) treeview.heading('操作', text='操作') cnx = DbUtil.open_db() cursor = cnx.cursor() cursor.execute(query) result = cursor.fetchall() # 如果查询到数据则显示,无数据则显示“购物车无商品” if cursor.rowcount == 0: for widget in frame_shopping.winfo_children(): widget.destroy() tk.Label(frame_shopping, text=null_remind, width=80, height=5).pack() else: i = 0 for k in result: treeview.insert('', i, values=(k[0], k[1], k[2], k[3], k[4] if option == 'book' else k[5], book_opt)) i = i + 1 if option == 'shop': query = ( "SELECT SUM(bprice*scount) total FROM `bookinfo` i, `shoplist` l WHERE i.bid = l.bid AND l.username = '******'" % ( username)) cursor.execute(query) result = cursor.fetchone() treeview.insert('', i, values=('金额合计:', 'RMB ¥', 0 if result is None else result[0], '', '', '去付款')) treeview.grid() for col in columns: # 给所有标题加(循环上边的“手工”) treeview.heading(col, text=col, command=lambda _col=col: treeview_sort_column(treeview, _col, False)) treeview.bind("<Double-1>", on_treeview_click) DbUtil.close_db(cursor, cnx)