from PySide6.QtWidgets import QWidget, QTableWidgetItem, QApplication
from Ui_讲义与答题纸信息查询 import Ui_Form
from database_tools_2 import *


class MyWindow_jycx(QWidget,Ui_Form):
    def __init__(self,database_name):
        super().__init__()
        self.setupUi(self)
        self.database_name = database_name
        self.bind()
    def bind(self):
        self.tableWidget.setColumnWidth(0,100)
        self.tableWidget.setColumnWidth(1,350)
        self.tableWidget.setColumnWidth(2,240)
        self.pushButton_nid.clicked.connect(self.exec_nid)
        self.pushButton_name.clicked.connect(self.exec_name)
        self.pushButton_pid.clicked.connect(self.exec_pid)
    
    def setdbname(self,string):
        self.database_name = string
        # print(self.database_name)
    
    def select_data(self,method):
        field = {"nid": "notes.nid", "pid": "answersheets.pid", "name": "notes.name"}
        regex =  {"nid": self.lineEdit_nid.text(), "pid": self.lineEdit_pid.text(), "name": self.lineEdit_name.text()}
        mydb = connect(hostname = db_host, port = db_port, username=db_user, pwd=db_pwd, db = self.database_name)
        mycursor = mydb.cursor()
        sql = 'SELECT notes.nid,notes.name,answersheets.pid FROM notes LEFT JOIN answersheets ON notes.nid = answersheets.nid WHERE NOT notes.obsolete AND ' +field[method]+' REGEXP %s;'
        val = (regex[method],)
        mycursor.execute(sql,val)
        ret_raw_info = mycursor.fetchall()
        self.infos = []
        for nid,name,pid in ret_raw_info:
            if pid is None:
                self.infos.append((nid,name,"无答题纸"))
            else:
                sql = 'SELECT obsolete FROM answersheets WHERE pid = %s;'
                val = (pid,)
                mycursor.execute(sql,val)
                ret = mycursor.fetchall()
                if ret[0][0] == False:
                    self.infos.append((nid,name,pid))
                else:
                    self.infos.append((nid,name,"作废"))
        mydb.close()
    
    def show_infos(self):
        self.tableWidget.clearContents()
        self.tableWidget.setRowCount(len(self.infos))
        sorted_infos =  sorted(self.infos, key=lambda x: x[0][1:5]+x[0][0]+x[0][5:])
        for i in range(len(self.infos)):
            nid,name,pid = sorted_infos[i]
            self.tableWidget.setItem(i,0,QTableWidgetItem(nid))
            self.tableWidget.setItem(i,1,QTableWidgetItem(name))
            self.tableWidget.setItem(i,2,QTableWidgetItem(pid))


    def exec_nid(self):
        self.select_data("nid")
        self.show_infos()

    def exec_name(self):
        self.select_data("name")
        self.show_infos()

    def exec_pid(self):
        self.select_data("pid")
        self.show_infos()



    



if __name__ == '__main__':
    app = QApplication([])
    windows = MyWindow_jycx("tikutest")
    windows.show()
    app.exec()