def turnback(): # 还书
global win
win = tk.Tk()
win.title('读者')
win.geometry('550x600')
db = pymysql.connect("localhost", "root", "sunhao2268411762", "library")
cursor = db.cursor()
sql0 = "SELECT COUNT(*) FROM borrow WHERE id='%s'" % (ID.getid())
cursor.execute(sql0)
result = cursor.fetchone()
if result[0] == 0:
msg.showinfo(title='错误', message='您还没借过书呢！')
else:
lable1 = tk.Label(win,
text='查询到您有以下书目未还：',
bg='pink',
font=('微软雅黑', 20)).place(x=80, y=20)
tree = ttk.Treeview(win, columns=('1', '2'), show="headings")
tree.column('1', width=150, anchor='center')
tree.column('2', width=150, anchor='center')
tree.heading('1', text='书名')
tree.heading('2', text='作者')
tree.place(x=100, y=100)
sql1 = "SELECT bookname,author FROM borrow WHERE id='%s'" % (
ID.getid())
cursor.execute(sql1)
result1 = cursor.fetchall()
for i in range(0, result[0]):
tree.insert('', i, values=(result1[i]))
lable2 = tk.Label(win, text='请输入还书信息：', bg='pink',
font=('微软雅黑', 20)).place(x=80, y=360)
lable22 = tk.Label(win,
text='书名作者都要填写正确无误！',
bg='pink',
font=('微软雅黑', 20)).place(x=80, y=400)
global b_name
tk.Label(win, text='书名：', font=('宋体', 12)).place(x=80, y=480)
b_name = tk.Entry(win, font=('宋体', 12), width=10)
b_name.place(x=130, y=480)
global author
tk.Label(win, text='作者：', font=('宋体', 12)).place(x=230, y=480)
author = tk.Entry(win, font=('宋体', 12), width=10)
author.place(x=280, y=480)
tk.Button(win,
text='确认还书',
font=('宋体', 12),
width=10,
command=confirm_turnback).place(x=395, y=480)
db.close()
def frame():
global window2
window2 = tk.Tk()
window2.title('Borrower Operation')
window2.configure(background='#7DC0F8')
window2.geometry('700x600')
lable = tk.Label(window2, text="Student ID: ", font=('Microsoft YaHei', 50)).place(x=20, y=10)
lable0 = tk.Label(window2, text=ID.getid(), font=('Microsoft YaHei', 50)).place(x=280, y=10)
lable1 = tk.Label(window2, text='Please select:', font=('Microsoft YaHei', 20)).place(x=80, y=200)
lable2 = tk.Label(window2, text='General input borrow', font=('Microsoft YaHei', 20)).place(x=80, y=250)
lable3 = tk.Label(window2, text='QR code borrow', font=('Microsoft YaHei', 20)).place(x=80, y=550)
tk.Button(window2, text='Borrow', font=('Microsoft YaHei', 15), width=10, height=2, command=borrow).place(x=350,
y=250)
tk.Button(window2, text='Return', font=('Microsoft YaHei', 15), width=10, height=2, command=turnback).place(x=350,
y=350)
tk.Button(window2, text='Search', font=('Microsoft YaHei', 15), width=10, height=2, command=search.frame).place(
x=350, y=450)
tk.Button(window2, text='(1)QR code', font=('Microsoft YaHei', 15), width=10, height=2, command=scan).place(x=350,
y=550)
tk.Button(window2, text='(2)Borrow', font=('Microsoft YaHei', 15), width=10, height=2, command=qrcode_show).place(
x=500, y=550)
window2.mainloop()
def turnback():
global win
win = tk.Tk()
win.title('Borrower')
win.geometry('550x600')
db = pymysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='cla051063',
db='abes',
charset='utf8'
)
cursor = db.cursor()
sql0 = "SELECT COUNT(*) FROM borrow WHERE userid='%s'" % (ID.getid())
cursor.execute(sql0)
result = cursor.fetchone()
if result[0] == 0:
msg.showinfo(title='Error', message='You have not borrowed an equipment yet!')
else:
lable1 = tk.Label(win, text='The following equipment on the unreturned：', bg='pink',
font=('Microsoft YaHei', 20)).place(x=80, y=20)
tree = ttk.Treeview(win, columns=('1', '2'), show="headings")
tree.column('1', width=150, anchor='center')
tree.column('2', width=150, anchor='center')
tree.heading('1', text='Equipment')
tree.heading('2', text='Date')
tree.place(x=100, y=100)
sql1 = "SELECT name,date FROM borrow WHERE userid='%s'" % (ID.getid())
cursor.execute(sql1)
result1 = cursor.fetchall()
for i in range(0, result[0]):
tree.insert('', i, values=(result1[i]))
lable2 = tk.Label(win, text='Please enter your equipment return information：', bg='pink',
font=('Microsoft YaHei', 20)).place(x=80, y=360)
lable22 = tk.Label(win, text='The equipment name should be filled in correctly！', bg='pink',
font=('Microsoft YaHei', 20)).place(x=80, y=400)
global e_name
tk.Label(win, text='Equipment：', font=('Microsoft YaHei', 12)).place(x=80, y=480)
e_name = tk.Entry(win, font=('Microsoft YaHei', 12), width=10)
e_name.place(x=180, y=480)
tk.Button(win, text='Confirm Turn back', font=('Microsoft YaHei', 12), width=10,
command=confirm_turnback).place(x=395, y=480)
db.close()
def confirm_turnback():
db = pymysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='cla051063',
db='abes',
charset='utf8'
)
cursor = db.cursor()
sql0 = "DELETE FROM borrow WHERE name='%s' AND userid='%s'" % (e_name.get(), ID.getid())
sql1 = "UPDATE equipment SET amount=amount+1 WHERE name='%s'" % (e_name.get())
cursor.execute(sql1)
cursor.execute(sql0)
db.commit()
db.close()
msg.showinfo(title='Successful return', message='Return the book successfully')
win.destroy()
def frame():
global win
win = tk.Tk()
win.title('Manager Operation')
win.geometry('700x600')
win.configure(background='#7DC0F8')
lable = tk.Label(win, text="Manager ID:",
font=('Microsoft YaHei', 50)).place(x=20, y=10)
lable0 = tk.Label(win, text=ID.getid(),
font=('Microsoft YaHei', 50)).place(x=280, y=10)
lable1 = tk.Label(win, text='Please select:',
font=('Microsoft YaHei', 20)).place(x=80, y=200)
tk.Button(win,
text='Add equipment',
font=('Microsoft YaHei', 15),
width=10,
height=2,
command=purchase).place(x=350, y=250)
tk.Button(win,
text='Delete equipment',
font=('Microsoft YaHei', 15),
width=10,
height=2,
command=cancel).place(x=350, y=350)
tk.Button(win,
text='Equipment Search',
font=('Microsoft YaHei', 15),
width=10,
height=2,
command=search.frame).place(x=350, y=450)
tk.Button(win,
text='Borrow Search',
font=('Microsoft YaHei', 15),
width=10,
height=2,
command=search_borrower.frame).place(x=350, y=550)
win.mainloop()
def confirm_borrow():
db = pymysql.connect("localhost", "root", "sunhao2268411762", "library")
cursor = db.cursor()
sql0 = "SELECT amount FROM book WHERE name='%s' AND author='%s'" % (
b_name.get(), author.get())
cursor.execute(sql0)
result = cursor.fetchone()
if result:
if result != '0':
time = dt.datetime.now().strftime(
'%F') # 得到的时间不是字符串型，我们要把时间转化成字符串型
sql = "INSERT INTO borrow VALUES('%s','%s','%s','%s')" % (
ID.getid(), b_name.get(), author.get(), time)
sql1 = "UPDATE book SET amount=amount-1 WHERE name='%s' AND author='%s'" % (
b_name.get(), author.get())
cursor.execute(sql)
cursor.execute(sql1)
db.commit()
db.close()
msg.showinfo(title='成功！', message='借书成功！请一个月之内归还')
else:
msg.showinfo(title='失败！', message='您借的书库存不足！')
else:
msg.showinfo(title='错误！', message='未找到该书！')
def confirm_borrow():
db = pymysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='cla051063',
db='abes',
charset='utf8'
)
cursor = db.cursor()
sql3 = "SELECT name FROM book WHERE userid='%s' AND name='%s'" % (ID.getid(), e_name.get())
cursor.execute(sql3)
result3 = cursor.fetchone()
print(result3)
if result3 is None:
sql = "SELECT name FROM borrow WHERE userid='%s' AND name='%s'" % (ID.getid(), e_name.get())
cursor.execute(sql)
result2 = cursor.fetchone()
print('not book')
if result2 is None:
sql0 = "SELECT amount FROM equipment WHERE name='%s'" % (e_name.get())
cursor.execute(sql0)
result = cursor.fetchone()
if result:
if result != '0':
time = dt.datetime.now().strftime('%F.%H:%M:%S')
sql = "INSERT INTO borrow VALUES('%s','%s','%s')" % (ID.getid(), e_name.get(), time)
sql1 = "UPDATE equipment SET amount=amount-1 WHERE name='%s'" % (e_name.get())
cursor.execute(sql)
cursor.execute(sql1)
msg.showinfo(title='Success！', message='The equipment is successfully borrowed!')
db.commit()
db.close()
win.destroy()
else:
msg.showinfo(title='Error！', message='The equipment is out of stock!')
else:
msg.showinfo(title='Error！', message='The equipment was not found！')
else:
db.close()
msg.showinfo(title='Error！', message='You have borrow！')
else:
print('have book')
sql4 = "UPDATE equipment SET amount=amount+1 WHERE name='%s'" % (e_name.get())
cursor.execute(sql4)
sql5 = "DELETE FROM book WHERE name='%s' AND userid='%s'" % (e_name.get(), ID.getid())
cursor.execute(sql5)
sql6 = "SELECT name FROM borrow WHERE userid='%s' AND name='%s'" % (ID.getid(), e_name.get())
cursor.execute(sql6)
result2 = cursor.fetchone()
if result2 is None:
sql0 = "SELECT amount FROM equipment WHERE name='%s'" % (e_name.get())
cursor.execute(sql0)
result = cursor.fetchone()
if result:
if result != '0':
time = dt.datetime.now().strftime('%F')
sql = "INSERT INTO borrow VALUES('%s','%s','%s')" % (ID.getid(), e_name.get(), time)
sql1 = "UPDATE equipment SET amount=amount-1 WHERE name='%s'" % (e_name.get())
cursor.execute(sql)
cursor.execute(sql1)
msg.showinfo(title='Success！', message='The equipment is successfully borrowed!')
db.commit()
db.close()
win.destroy()
else:
msg.showinfo(title='Error！', message='The equipment is out of stock!')
else:
msg.showinfo(title='Error！', message='The equipment was not found！')
else:
db.close()
msg.showinfo(title='Error！', message='You have borrow！')
