import wx
import datetime
import pyodbc
import re
import sys
sys.path.append('../')#向上回一级目录，否则会报错
from database.数据库 import database
from GUI.管理员界面 import manageMenu
from GUI.学生界面 import stumenu
from demo.Student import student
from demo.Textbook import textbook
from demo.Reserve import reserve
from demo.Cost import cost
#登录
login_sql ='select * from students where 姓名=? and 密码=?'
look_book='select * from books'#查看所有教材
#学生
reserve_book='insert into reserve values(?,?,?)'#向reserve表中插入信息
back_book='delete from reserve where 姓名=? and 书名=?'#通过姓名+书名删除reserve表的信息
select_reserved='select * from reserve where 姓名=?'#通过姓名查找reserve表该学生征订教材信息
select_stu='select * from students where 姓名=?'#通过姓名查找students表该学生个人信息
book_price='select 价格 from books where 书名=? '#通过书名查找books表该教材价格
insert_cost='insert into cost values (?,?)'#向cost表里插入教材费用
select_cost1='select * from cost where 姓名=?'#通过姓名查找cost表该学生的教材费用
update_cost='update cost set 教材总费用=? where 姓名=?'#通过姓名更改cost表该学生的教材费用
reserved_name='select * from reserve where 姓名=?'#通过姓名查找reserve表该学生所有的征订教材
reserve_1='select * from reserve where 姓名=? and 书名=?'
#管理员
insert_book='insert into books values(?,?,?,?,?)'#向books里增加教材
delete_book='delete from books where 书号=? and 书名=?'#通过书号和书名从books里删除教材
select_allstu='select * from students'#查询所有学生的个人信息
select_cost='select * from cost'#查询cost里的所有信息
# #主函数
if __name__=='__main__':
solve=database()
try:
class wxGUI(wx.App):
# ***********************登录界面************************************
# ******************************************************************
def Login(self):
# login框架 框架大小1000*800
self.frame_Login = wx.Frame(None, title="Login", size=(500, 320))
# 窗口的容器(面板） 父参数是wx.Frame()
self.panel_Login = wx.Panel(self.frame_Login, -1)
# 在面板上添加StaticText() 父参数是wx.StaticText()
self.label1 = wx.StaticText(self.panel_Login, -1, \
'教材征订系统', pos=(180, 60), style=wx.ALIGN_CENTER)
# 设置字体
font = wx.Font(18, wx.ROMAN, wx.ITALIC, wx.NORMAL)
self.label1.SetFont(font)
self.label2 = wx.StaticText(self.panel_Login, -1, '用户名:', \
pos=(180, 100), style=wx.ALIGN_LEFT)
self.textuser = wx.TextCtrl(self.panel_Login, -1, pos=(220, 100), size=(100, 20))
# self.Bind(wx.EVT_COMBOBOX, self.Oncombo2, self.combobox2)
# 密码
self.label3 = wx.StaticText(self.panel_Login, -1, '密 码:', \
pos=(180, 140), style=wx.ALIGN_LEFT)
self.textpasswd = wx.TextCtrl(self.panel_Login, -1, \
pos=(220, 140), size=(100, 20), style=wx.TE_PASSWORD)
self.textpasswd.SetMaxLength(8)
# 安全登录按钮
self.button_paswd = wx.Button(self.panel_Login, -1, '安全登录', pos=(180, 180), size=(144, 30))
# 绑定事件类型EVT_BUTTON,事件名OnButton_paswd,按钮button_paswd
self.Bind(wx.EVT_BUTTON, self.OnButton_paswd, self.button_paswd)
self.button_paswd.SetDefault()
self.frame_Login.Show()
# ***********************登录界面************************************
# ******************************************************************
# 校验用户名与密码
def OnButton_paswd(self, event):
userName = self.textuser.GetValue()
userPassword = self.textpasswd.GetValue()
stu = student('', '', '', '', '')
stu.setSname(userName)
stu.setPassword(userPassword)
# solve.cur 获得database中的游标
solve.cur.execute(login_sql, (stu.getSname(), stu.getPassword())) # 使用游标中的execute()方法执行sql语句
row = solve.cur.fetchone() # 获取一行数据，如果没有，则会返回null
# print(row)
if row:
stumenu.Student_Menu(self,stu.getSname()) # 把姓名传给转向的Student_Menu()界面
elif userName == "管理员" and userPassword == "cumtxhxy":
manageMenu.Management_Menu(self)
else:
wx.MessageBox("密码或用户名错误")
# 查看图书
def OnLook(self, event):
solve.cur.execute(look_book)
result=solve.cur.fetchall()
x=str(result).split("),")
s=""
for i in x:
s=s+str(i)+"\n"
wx.MessageBox(s)
# --------------------------------------------------------------------
# ***********************管理员功能开始************************************
#查询所有学生教材费用
def OnCost(self,event):
solve.cur.execute(select_cost)
result=solve.cur.fetchall()
x = str(result).split("),")
s = ""
for i in x:
s1=re.findall(r"[\u4E00-\u9FA5]+",i)#匹配汉字
s2=re.findall(r"\d+\.?\d*",i)#匹配价格
s = s+s1[0]+"的教材费用为："+s2[0]+ "\n"
wx.MessageBox(s)
# 查询所有学生信息
def OnLook_Allstudent(self, event):
solve.cur.execute(select_allstu)
result = solve.cur.fetchall()
x = str(result).split("),")
s = ""
for i in x:
s = s + str(i) + "\n"
wx.MessageBox(s)
#增加教材
def OnAdd(self,event):
self.frame_Onadd = wx.Frame(None, -1, title="增加教材", size=(500, 300))
self.panel_Onadd = wx.Panel(self.frame_Onadd, -1)
self.label1 = wx.StaticText(self.panel_Onadd, -1, '书号', pos=(20, 20), style=wx.ALIGN_LEFT)
self.text1 = wx.TextCtrl(self.panel_Onadd,-1,pos=(100, 20), size=(150, 20))
self.label2 = wx.StaticText(self.panel_Onadd, -1, '书名', pos=(20, 50), style=wx.ALIGN_LEFT)
self.text2 = wx.TextCtrl(self.panel_Onadd,-1,pos=(100, 50), size=(150, 20))
self.label3 = wx.StaticText(self.panel_Onadd, -1, '作者', pos=(20, 80), style=wx.ALIGN_LEFT)
self.text3 = wx.TextCtrl(self.panel_Onadd, -1, pos=(100, 80), size=(150, 20))
self.label4 = wx.StaticText(self.panel_Onadd, -1, '出版社', pos=(20, 110), style=wx.ALIGN_LEFT)
self.text4 = wx.TextCtrl(self.panel_Onadd, -1, pos=(100, 110), size=(150, 20))
self.label5 = wx.StaticText(self.panel_Onadd, -1, '价格', pos=(20, 140), style=wx.ALIGN_LEFT)
self.text5 = wx.TextCtrl(self.panel_Onadd, -1, pos=(100, 140), size=(150, 20))
self.button_Add=wx.Button(self.panel_Onadd,-1, "OK",pos=(175,165))
self.Bind(wx.EVT_BUTTON,self.OnButton_Add,self.button_Add)
self.frame_Onadd.Show()
def OnButton_Add(self,event):
book=textbook('','','','','')
book.setCno(self.text1.GetValue())
book.setCname(self.text2.GetValue())
book.setAuthor(self.text3.GetValue())
book.setPress(self.text4.GetValue())
book.setPrice(self.text5.GetValue())
solve.cur.execute(insert_book, (
book.getCno(), book.getCname(),book.getAuthor(),book.getPress(),book.getPrice()))
solve.conn.commit()
self.ltime = datetime.datetime.now() # 购买时间
self.ltimes = self.ltime.strftime('%Y-%m-%d %H:%M:%S') # 设置时间格式
message2 = "增加书籍成功！ \n" + "书名：" + self.text2.GetValue() + "\n" + "时间：" + self.ltimes
wx.MessageBox(message2)
#删除教材
def OnDelete(self,event):
self.frame_Ondelete = wx.Frame(None, -1, title="删除教材", size=(500, 300))
self.panel_Ondelete = wx.Panel(self.frame_Ondelete, -1)
self.label1 = wx.StaticText(self.panel_Ondelete, -1, '书号', pos=(20, 20), style=wx.ALIGN_LEFT)
self.text1 = wx.TextCtrl(self.panel_Ondelete, -1, pos=(100, 20), size=(150, 20))
self.label2 = wx.StaticText(self.panel_Ondelete, -1, '书名', pos=(20, 50), style=wx.ALIGN_LEFT)
self.text2 = wx.TextCtrl(self.panel_Ondelete, -1, pos=(100, 50), size=(150, 20))
self.button_delete=wx.Button(self.panel_Ondelete,-1,"OK",pos=(175,75))
self.Bind(wx.EVT_BUTTON,self.OnButton_Delete,self.button_delete)
self.frame_Ondelete.Show()
def OnButton_Delete(self,event):
solve.cur.execute(delete_book,(self.text1.GetValue(),self.text2.GetValue()))
solve.conn.commit()
self.ltime = datetime.datetime.now() # 删除时间
self.ltimes = self.ltime.strftime('%Y-%m-%d %H:%M:%S') # 设置时间格式
message2 = "删除书籍成功！ \n" + "书名：" + self.text2.GetValue() + "\n" + "时间：" + self.ltimes
wx.MessageBox(message2)
# 切换登陆
def OnSwitch_manage(self, event):
self.frame_Manage.Destroy()
self.Login()
# 退出
def OnOut_manage(self, event):
wx.MessageBox("谢谢您的使用！")
self.frame_Manage.Destroy()
# ***********************管理员功能结束************************************
# --------------------------------------------------------------------
# #<<----------------------------------------------------------------
# #***********************学生功能开始******************************>>
#教材费用
def OnLookmoney(self,event):
solve.cur.execute(select_reserved, self.name) # 执行查询预定语句，传入姓名
result = solve.cur.fetchall() # result是list类型
list1=[]
sum=0.0
for i in range(len(result)):
list1.append(result[i][1])#所有已预订的教材的书名
for i in list1:
solve.cur.execute(book_price,i)#根据书名到books表中找到对应的价格price
price=solve.cur.fetchone()
# print(price)
price1=re.findall(r"\d+\.?\d*",str(price))#正则表达式找到str类型的浮点数部分
sum+=float(price1[0])#转化为浮点数
sum=round(sum,1)
wx.MessageBox("学生"+self.name+"的教材总费用为："+str(sum))
# 查看个人信息
def OnLookstu(self, event):
solve.cur.execute(select_stu, self.name)
result = solve.cur.fetchone()
print(type(result))
wx.MessageBox(str(result))
# 征订部分
def OnReserve(self, event,):
solve.cur.execute(look_book)
result = solve.cur.fetchall()
book_name = []
for i in range(len(result)):
book_name.append(result[i][1])
print(book_name)
self.frame_Reserve = wx.Frame(None, -1, title="征订书籍", size=(500, 300))
self.panel_Reserve = wx.Panel(self.frame_Reserve, -1)
self.label13 = wx.StaticText(self.panel_Reserve, -1, '教材名称', pos=(20, 80), style=wx.ALIGN_LEFT)
self.combobox3 = wx.ComboBox(self.panel_Reserve, value="请选择教材", \
choices=book_name, pos=(100, 80), size=(150, 30))
self.label14 = wx.StaticText(self.panel_Reserve, -1, '征订数量', pos=(20, 120), style=wx.ALIGN_LEFT)
self.Number = wx.ComboBox(self.panel_Reserve, choices=["1"], pos=(100, 120), size=(150, 20))
self.button_Reserve = wx.Button(self.panel_Reserve, -1, 'OK', pos=(175, 160))
# 绑定事件类型EVT_BUTTON，事件内容Onbutton_Reserve，按钮button_Buy
self.Bind(wx.EVT_BUTTON, self.Onbutton_Reserve, self.button_Reserve)
self.frame_Reserve.Show()#展示框架
def Onbutton_Reserve(self, event):
#执行sql语句reserve_book,传入姓名，书名和预定数量
solve.cur.execute(reserve_1,(self.name,self.combobox3.GetValue()))
result=solve.cur.fetchone()
if result:
self.ltime = datetime.datetime.now() # 购买时间
self.ltimes = self.ltime.strftime('%Y-%m-%d %H:%M:%S') # 设置时间格式
wx.MessageBox("该教材已征订!\n"+"征订失败时间："+self.ltimes)
else:
res = reserve('', '', '')
res.setSname(self.name)
res.setCname(self.combobox3.GetValue())
res.setNumber(self.Number.GetValue())
solve.cur.execute(reserve_book,
(res.getSname(),res.getCname(),res.getNumber()))
solve.conn.commit()
solve.cur.execute(select_reserved, self.name) # 执行查询预定语句，传入姓名
result = solve.cur.fetchall() # result是list类型
list1 = []
sum = 0.0
for i in range(len(result)):
list1.append(result[i][1]) # 所有已预订的教材的书名
for i in list1:
solve.cur.execute(book_price, i) # 根据书名到books表中找到对应的价格price
price = solve.cur.fetchone()
print(price)
price1 = re.findall(r"\d+\.?\d*", str(price)) # 正则表达式找到str类型的浮点数部分
# print(price1)
sum += float(price1[0]) # 转化为浮点数
sum = round(sum, 1)
# print(sum)
solve.cur.execute(select_cost1, self.name) # 根据学生姓名找cost表里是否有对应的行
result = solve.cur.fetchone()
# print(result)
if result: # 如果有，更新该学生的教材费用
solve.cur.execute(update_cost, (sum, self.name))
else: # 如果没有，创建该学生的教材费用
cost=cost('','')
cost.setSname(self.name)
cost.setSmoney(sum)
solve.cur.execute(insert_cost, (cost.getSname(),cost.getMoney()))
solve.conn.commit()
self.ltime = datetime.datetime.now() # 购买时间
self.ltimes = self.ltime.strftime('%Y-%m-%d %H:%M:%S') # 设置时间格式
message2 = "征订成功！ \n" + "书名：" + self.combobox3.GetValue() + "\n" + "征订时间：" + self.ltimes
wx.MessageBox(message2) # wx.MessageBox()里是str
# self.frame_Reserve.Destroy()
# 退订部分
def OnBack(self, event):
solve.cur.execute(reserved_name,self.name)
result = solve.cur.fetchall()
book_name = []
for i in range(len(result)):
book_name.append(result[i][1])
self.frame_Back = wx.Frame(None, -1, title="Back Books", size=(500, 300))
self.panel_Back = wx.Panel(self.frame_Back, -1)
self.label15 = wx.StaticText(self.panel_Back, -1, '教材名称', pos=(20, 80), style=wx.ALIGN_LEFT)
self.combobox5 = wx.ComboBox(self.panel_Back, value="请选择教材", \
choices=book_name, pos=(100, 80), size=(150, 30))
self.label16 = wx.StaticText(self.panel_Back, -1, '退订数量', pos=(20, 120), style=wx.ALIGN_LEFT)
self.textBack_Number = wx.ComboBox(self.panel_Back, choices=["1"], pos=(100, 120), size=(150, 20))
self.button_Back = wx.Button(self.panel_Back, -1, 'OK', pos=(175, 160))
self.Bind(wx.EVT_BUTTON, self.OnButton_Back, self.button_Back)#ok控件绑定退订功能
self.frame_Back.Show()
def OnButton_Back(self, event):
solve.cur.execute(back_book,(self.name,self.combobox5.GetValue()))#语句执行
solve.conn.commit()#连接数据库实现
solve.cur.execute(select_reserved, self.name) # 执行查询预定语句，传入姓名
result = solve.cur.fetchall() # result是list类型
list1 = []
sum = 0.0
for i in range(len(result)):
list1.append(result[i][1]) # 所有已预订的教材的书名
for i in list1:
solve.cur.execute(book_price, i) # 根据书名到books表中找到对应的价格price
price = solve.cur.fetchone()
#print(price)
price1 = re.findall(r"\d+\.?\d*", str(price)) # 正则表达式找到str类型的浮点数部分
sum += float(price1[0]) # 转化为浮点数
sum = round(sum, 1)
# print(sum)
solve.cur.execute(update_cost, (sum, self.name))#更新该学生的教材费用
solve.conn.commit()
self.ltime2 = datetime.datetime.now() # 借出时间
self.ltimes2 = self.ltime2.strftime('%Y-%m-%d %H:%M:%S')
message3 = "退订成功！ \n" + "书名：" + self.combobox5.GetValue() + "\n" + "退订时间：" + self.ltimes2
wx.MessageBox(message3)#Wx.MessageBox()里是str
#查询已预订部分
def OnSelect(self,event):
solve.cur.execute(select_reserved,self.name)#执行查询预定语句，传入姓名
result=solve.cur.fetchall()#result是list类型
x=str(result).split("),")#用str的split方法切分字符串
# print(x)
s=""
for i in x:
s=s+i+"\n" #i是str类型
wx.MessageBox(s)
# 切换登陆
def OnSwitch_stu(self, event):
self.frame_Stu.Destroy()
self.Login()
# 退出
def OnOut_stu(self, event):
wx.MessageBox("谢谢您的使用！")
self.frame_Stu.Destroy()
# # <<--------------------学生功能结束--------------------------------
# # ***************************************************************>>#
app = wxGUI()
app.Login()
app.MainLoop()
finally:
solve.cur.close()
solve.conn.close()