import sqlite3
from PySide2.QtWidgets import QApplication, QMessageBox, QMainWindow, QTableWidgetItem, QStyleFactory
from PySide2.QtUiTools import QUiLoader
from main_ui import Ui_MainWindow

instrument_list = ['浓度仪_1', '浓度仪_2', '风速仪_1', '风速仪_2', '风速仪_3', '测厚仪']
database = 'borrowing.db'


class MainWindow(QMainWindow):

    def __init__(self):
        super().__init__()
        # 从文件中加载UI定义
        # self.ui = QUiLoader().load('main_ui.ui')
        # 使用ui文件导入定义界面类
        self.ui = Ui_MainWindow()
        # 初始化界面
        self.ui.setupUi(self)

        self.at_home = True
        # 加载仪器名列表
        self.ui.namebox.addItems(instrument_list)
        self.load_data()
        # 切换仪器名时更新表中的借用记录
        self.ui.namebox.currentIndexChanged.connect(self.load_data)
        self.ui.borrowbutton.clicked.connect(self.borrow_button)
        self.ui.backbutton.clicked.connect(self.back_button)

    def load_data(self):
        self.ui.infotable.clearContents()
        self.ui.infotable.setRowCount(0)
        name = self.ui.namebox.currentText()
        # 创建连接
        con = sqlite3.connect(database)
        # 创建游标对象
        cur = con.cursor()
        # 编写查询sql语句
        sql = f'select * from {name}'
        try:
            cur.execute(sql)
            # 获取结果集
            record = cur.fetchall()
            # 显示存放位置
            self.ui.placelabel.setText(f'{record[0][-1]}')
            for row in record:
                # 获取当前表格行数
                rowcount = self.ui.infotable.rowCount()
                # print(row)
                # 在下面插入一行
                self.ui.infotable.insertRow(rowcount)
                i = 0
                for info in row[1:-1]:
                    if info is None:
                        info = ''
                    # 将获取的信息填入相应行列的单元格中
                    self.ui.infotable.setItem(rowcount, i, QTableWidgetItem(info))
                    i += 1
            # 获取最后一次借用记录
            last_row = record[-1]
            if last_row[5]:
                # 有实际归还日期，说明已经归还，设备在实验室
                self.at_home = True
                self.ui.statelabel.setText('设备状态：已归还')
                # 操作界面清空
                self.ui.borrowtime.clear()
                self.ui.exceptback.clear()
                self.ui.borrowstate.clear()
                self.ui.borrower.clear()
            else:
                # 实际归还日期为空，说明还没有归还
                self.at_home = False
                self.ui.statelabel.setText('设备状态：未归还')
                self.ui.borrowtime.setText(last_row[1])
                self.ui.exceptback.setText(last_row[3])
                self.ui.borrowstate.setText(last_row[4])
                self.ui.borrower.setText(last_row[2])
        except Exception as e:
            print(e)
            QMessageBox.about(self.ui, '提示', '查询失败！')
        finally:
            # 关闭游标连接
            cur.close()
            # 关闭数据库连接
            con.close()

    def borrow_button(self):
        if self.at_home:
            if self.ui.borrowtime.text() == '':
                QMessageBox.about(self.ui, '提示', '请填入借用日期！')
            elif self.ui.exceptback.text() == '':
                QMessageBox.about(self.ui, '提示', '请填入预计归还日期！')
            elif self.ui.borrowstate.text() == '':
                QMessageBox.about(self.ui, '提示', '请填入借出时状态！')
            elif self.ui.borrower.text() == '':
                QMessageBox.about(self.ui, '提示', '请填入借用人！')
            else:
                # 信息都填好，往数据库里插入入一条新的借用记录
                name = self.ui.namebox.currentText()
                # 创建连接
                con = sqlite3.connect(database)
                # 创建游标对象
                cur = con.cursor()
                # 编写插入的sql语句
                sql = f'insert into {name}(借用日期,借用人,预计归还日期,借出时状态,存放位置) values(?,?,?,?,?)'
                new_row = (self.ui.borrowtime.text(), self.ui.borrower.text(), self.ui.exceptback.text(),
                           self.ui.borrowstate.text(), self.ui.placelabel.text())
                try:
                    # 执行sql
                    cur.execute(sql, new_row)
                    con.commit()
                    QMessageBox.about(self.ui, '提示', f'{name}借出成功！')
                except Exception as e:
                    print(e)
                    con.rollback()
                    QMessageBox.about(self.ui, '提示', f'{name}借出失败！')
                finally:
                    # 关闭游标连接
                    cur.close()
                    # 关闭数据库连接
                    con.close()
        else:
            QMessageBox.about(self.ui, '提示', '仪器未归还！')

    def back_button(self):
        if self.at_home:
            QMessageBox.about(self.ui, '提示', '仪器未借出！')
        else:
            if self.ui.actualback.text() == '':
                QMessageBox.about(self.ui, '提示', '请填入实际归还日期！')
            elif self.ui.backstate.text() == '':
                QMessageBox.about(self.ui, '提示', '请填入归还时状态！')
            else:
                # 信息都填好，往数据库里插入新的归还记录
                name = self.ui.namebox.currentText()
                # 创建连接
                con = sqlite3.connect(database)
                # 创建游标对象
                cur = con.cursor()
                # 编写修改的sql语句
                sql = f'update {name} set 实际归还日期=?, 归还时状态=? where 借用日期=?'
                new_back = (self.ui.actualback.text(), self.ui.backstate.text(), self.ui.borrowtime.text())
                try:
                    # 执行sql
                    cur.execute(sql, new_back)
                    # 提交事物
                    con.commit()
                    QMessageBox.about(self.ui, '提示', f'{name}归还成功！')
                except Exception as e:
                    print(e)
                    QMessageBox.about(self.ui, '提示', f'{name}归还失败！')
                    con.rollback()
                finally:
                    # 关闭游标连接
                    cur.close()
                    # 关闭数据库连接
                    con.close()


app = QApplication([])
app.setStyle(QStyleFactory.create('Fusion'))
mainw = MainWindow()
mainw.show()
app.exec_()