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)
