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()