Selection.py

# -*- coding: utf-8 -*-
import sys
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
import db

class TreeWidget(QWidget):
    back2 = pyqtSignal(str)
    def __init__(self):
        super(TreeWidget,self).__init__()
        self.num = 0
        self.initUI()
        self.run()
    #初始化界面
    def initUI(self):
        self.db = db.MysqlClient()
        self.db.connectDatabase()
        self.ins_id = None
        self.pro_id = None
        self.setWindowTitle("选课界面")
        #设置待选学生窗口
        self.table_selecting = QTableWidget()
        self.table_selecting.setColumnCount(3)
        self.table_selecting.setHorizontalHeaderLabels(['','学号','姓名'])
        self.table_selecting.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.table_selecting.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.table_selecting.setColumnWidth(0,10)

        #设置已选学生窗口
        self.table_selected = QTableWidget()
        self.table_selected.setColumnCount(5)
        self.table_selected.setHorizontalHeaderLabels(['','学号','姓名','班级','学院'])
        self.table_selected.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.table_selected.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.table_selected.setColumnWidth(0,10)
        #设置相关label、combobox、button
        Lab_1 = QLabel("开课学院:")
        self.Com_1 = QComboBox()     #设置开课学院复选框
        Lab_2 = QLabel("课程:")
        self.Com_2 = QComboBox(self)     #设置课程复选框
        Lab_3 = QLabel("学院:")
        self.Com_3 = QComboBox(self)     #设置学院复选框
        Lab_4 = QLabel("专业:")
        self.Com_4 = QComboBox(self)     #设置专业复选框
        Lab_5 = QLabel("班级")          #设置班级复选框
        self.Com_5 = QComboBox()
        self.button_add = QPushButton("保存")     #设置保存按钮

        grid = QGridLayout()
        grid.setSpacing(10)
        grid.addWidget(Lab_1, 1, 0)
        grid.addWidget(self.Com_1, 1, 1)
        grid.addWidget(Lab_2, 2, 0)
        grid.addWidget(self.Com_2, 2 , 1)
        grid.addWidget(self.button_add, 3, 0, 1, 3)

        grid.addWidget(Lab_3, 1, 4)
        grid.addWidget(self.Com_3, 1, 5)
        grid.addWidget(Lab_4, 2, 4)
        grid.addWidget(self.Com_4, 2, 5)
        grid.addWidget(Lab_5, 3, 4)
        grid.addWidget(self.Com_5, 3, 5)

        grid.addWidget(self.table_selected, 4, 0, 11, 3)
        grid.addWidget(self.table_selecting, 4, 4, 11, 2)

        self.setGeometry(300, 300, 960, 540)
        self.setLayout(grid)
        self.search_institute()
        self.search_profession()
        self.search_class()
        self.search_lesson()
        self.set_selecting()
        self.set_selected()

    #函数调用
    def run(self):
        self.Com_3.currentIndexChanged.connect(self.search_profession)
        self.Com_4.currentIndexChanged.connect(self.search_class)
        self.Com_5.currentIndexChanged.connect(self.set_selecting)

        self.Com_1.currentIndexChanged.connect(self.search_lesson)
        self.Com_2.currentIndexChanged.connect(self.set_selected)
        self.Com_2.currentIndexChanged.connect(self.set_selecting)
        self.button_add.clicked.connect(self.insert_lesson)
        self.button_add.clicked.connect(self.delete_course)
        self.button_add.clicked.connect(self.set_selected)
        self.button_add.clicked.connect(self.set_selecting)
    #填充待选学生信息
    def set_selecting(self):
        num_selecting = self.table_selecting.rowCount()
        for i in range(num_selecting):
            self.table_selecting.removeRow(0)
        i = 0
        stu= self.search_stu_selecting()
        for item in stu:
            a = QTableWidgetItem(str(stu[i][0]))
            b = QTableWidgetItem(stu[i][1])
            self.table_selecting.insertRow(i)
            checkBox = QTableWidgetItem("")
            checkBox.setCheckState(Qt.Unchecked)    # 设置单选框为选中
            self.table_selecting.setItem(i, 0, checkBox)
            self.table_selecting.setItem(i, 1, a)
            self.table_selecting.setItem(i, 2, b)
            i+=1
    #填充已选学生信息
    def set_selected(self):
        num_selected = self.table_selected.rowCount()
        for i in range(num_selected):
            self.table_selected.removeRow(0)
        i = 0
        stu,stu_id = self.search_stu_selected()
        for item in stu:
            a = QTableWidgetItem(str(stu[i][0]))
            b = QTableWidgetItem(stu[i][1])
            c = QTableWidgetItem(stu[i][2])
            d = QTableWidgetItem(stu[i][3])

            self.table_selected.insertRow(i)
            checkBox = QTableWidgetItem("")         # 设置单选框为选中
            checkBox.setCheckState(Qt.Checked)
            self.table_selected.setItem(i, 0, checkBox)
            self.table_selected.setItem(i, 1, a)
            self.table_selected.setItem(i, 2, b)
            self.table_selected.setItem(i, 3, c)
            self.table_selected.setItem(i, 4, d)
            i+=1
    #填充学院下拉框
    def search_institute(self):
        ins = []
        sql_ins = "SELECT id,name FROM face_recognition.institute"
        for item in self.db.search(sql_ins):
            ins.append(item)
        for i in ins:
            self.Com_1.addItem(i[1])
            self.Com_3.addItem(i[1])
    #填充专业下拉框
    def search_profession(self):
        self.Com_4.clear()
        pro = []
        a = self.Com_3.currentText()
        sql_pro = "SELECT profession.id,profession.name\
                   FROM face_recognition.profession,face_recognition.institute\
                    where ins_id = institute.id AND institute.name = '%s'"%(a)
        for item in self.db.search(sql_pro):
            pro.append(item)
        for i in pro:
            self.Com_4.addItem(i[1])
    #填充班级下拉框
    def search_class(self):
        self.Com_5.clear()
        cla = []
        a = self.Com_4.currentText()
        sql_cla = "SELECT class.id,class.cla_name \
                   FROM face_recognition.profession,face_recognition.class\
                    where pro_id = profession.id AND profession.name = '%s'"%(a)
        for item in self.db.search(sql_cla):
            cla.append(item)
        for i in cla:
            self.Com_5.addItem(i[1])
    #未选学生信息
    def search_stu_selecting(self):
        stu = []
        stu1,stu_id = self.search_stu_selected()
        a = self.Com_5.currentText()
        b = self.Com_2.currentText()
        sql_stu = "SELECT stu_id,students.name \
                  FROM students,class\
                  where class_id = class.id AND cla_name = '%s'" %(a)
        for item in self.db.search(sql_stu):
            if item[0] not in stu_id:
                stu.append(item)
        return stu
    #填充课程下拉框
    def search_lesson(self):
        self.Com_2.clear()
        les = []
        a = self.Com_1.currentText()
        sql_les = "SELECT lesson.id,lesson.lesson_name\
                   FROM face_recognition.lesson,face_recognition.institute\
                    where ins_id = institute.id AND institute.name = '%s'"%(a)
        for item in self.db.search(sql_les):
            les.append(item)
        for i in les:
            self.Com_2.addItem(i[1])
    #已选学生信息
    def search_stu_selected(self):
        stu = []
        stu_i = []
        a = self.Com_2.currentText()
        sql_stu = "SELECT students.stu_id,students.name,cla_name,institute.name \
                    FROM students,class,elective_course,institute,lesson\
                    where elective_course.stu_id = students.stu_id AND \
                           elective_course.class_id = class.id AND \
                           elective_course.lesson_id = lesson.id AND \
                           class.ins_id = institute.id AND\
                           lesson_name = '%s'"%(a)
        for item in self.db.search(sql_stu):
            stu.append(item)
            stu_i.append(item[0])
        stu_id = set(stu_i)
        return stu ,stu_id
    #插入新选课信息
    def insert_lesson(self):
        print("{}".format(self.num))
        self.num+=1
        for i in range(self.table_selecting.rowCount()):
            if self.table_selecting.item(i,0).checkState() == Qt.Checked:
                #获取当前课程总数
                sql_num = "SELECT MAX(idelectivecourse) FROM elective_course"
                n = self.db.search(sql_num)
                #获取添加记录学号
                a = self.table_selecting.item(i,1).text()
                stu_id = int(a)
                #获取添加记录班级号
                sql_cla = "SELECT class_id FROM students WHERE stu_id = %d"% stu_id
                class_id = self.db.search(sql_cla)
                #获取添加记录课程号
                b = self.Com_2.currentText()
                sql_les = "SELECT id FROM lesson WHERE lesson_name = '%s'"% b
                lesson_id = self.db.search(sql_les)
                #添加记录
                sql_insert = "INSERT INTO face_recognition.elective_course\
                              VALUES (%d,%d,%d,%d);"%(n[0][0]+1,lesson_id[0][0],class_id[0][0],stu_id)
                self.db.execute(sql_insert)
            else:
                continue
    #删除选课信息
    def delete_course(self):
        for i in range(self.table_selected.rowCount()):
            if self.table_selected.item(i, 0).checkState() == Qt.Unchecked:
                # 获取添加记录学号
                a = self.table_selected.item(i, 1).text()
                stu_id = int(a)
                # 获取添加记录课程号
                b = self.Com_2.currentText()
                sql_les = "SELECT id FROM lesson WHERE lesson_name = '%s'" % b
                lesson_id = self.db.search(sql_les)
                #删除elective_course记录
                sql_del = "DELETE FROM elective_course WHERE stu_id = %d AND lesson_id = %d"%(stu_id, lesson_id[0][0])
                self.db.execute(sql_del)
            else:
                continue


if __name__ == '__main__':
    app = QApplication(sys.argv)
    app.aboutToQuit.connect(app.deleteLater)
    tp = TreeWidget()
    tp.setWindowOpacity(0.9)
    tp.show()
    sys.exit(app.exec_())