registration.py

# -*- coding: utf-8 -*-
import mysql.connector
# Form implementation generated from reading ui file 'Registration.ui'
#
# Created by: PyQt5 UI code generator 5.15.7
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt5 import QtCore, QtGui, QtWidgets

#############################################################
#                                                           #
#                       UI Generation                       #
#                                                           #
#############################################################

class Ui_Dialog(object):
    def setupUi(self, Dialog, listValues):
        Dialog.setObjectName("Dialog")
        Dialog.resize(453, 167)
        self.buttonBox = QtWidgets.QDialogButtonBox(Dialog)
        self.buttonBox.setGeometry(QtCore.QRect(180, 120, 251, 32))
        self.buttonBox.setOrientation(QtCore.Qt.Horizontal)
        self.buttonBox.setStandardButtons(QtWidgets.QDialogButtonBox.Cancel|QtWidgets.QDialogButtonBox.Ok)
        self.buttonBox.setObjectName("buttonBox")
        self.formLayoutWidget = QtWidgets.QWidget(Dialog)
        self.formLayoutWidget.setGeometry(QtCore.QRect(20, 20, 411, 91))
        self.formLayoutWidget.setObjectName("formLayoutWidget")
        self.formLayout = QtWidgets.QFormLayout(self.formLayoutWidget)
        self.formLayout.setLabelAlignment(QtCore.Qt.AlignLeading|QtCore.Qt.AlignLeft|QtCore.Qt.AlignVCenter)
        self.formLayout.setContentsMargins(0, 0, 0, 0)
        self.formLayout.setObjectName("formLayout")
        self.lblStudentRegistration = QtWidgets.QLabel(self.formLayoutWidget)
        self.lblStudentRegistration.setObjectName("lblStudentRegistration")
        self.formLayout.setWidget(0, QtWidgets.QFormLayout.LabelRole, self.lblStudentRegistration)
        self.lblScheduleRegistration = QtWidgets.QLabel(self.formLayoutWidget)
        self.lblScheduleRegistration.setObjectName("lblScheduleRegistration")
        self.formLayout.setWidget(1, QtWidgets.QFormLayout.LabelRole, self.lblScheduleRegistration)
        self.cmbStudentRegistration = QtWidgets.QComboBox(self.formLayoutWidget)
        self.cmbStudentRegistration.setMaxVisibleItems(100)
        self.cmbStudentRegistration.setObjectName("cmbStudentRegistration")
        self.formLayout.setWidget(0, QtWidgets.QFormLayout.FieldRole, self.cmbStudentRegistration)
        self.cmbScheduleRegistration = QtWidgets.QComboBox(self.formLayoutWidget)
        self.cmbScheduleRegistration.setEditable(False)
        self.cmbScheduleRegistration.setObjectName("cmbCourseScheduleRegistration")
        self.formLayout.setWidget(1, QtWidgets.QFormLayout.FieldRole, self.cmbScheduleRegistration)

        self.retranslateUi(Dialog)
        self.buttonBox.accepted.connect(Dialog.accept) # type: ignore
        self.buttonBox.rejected.connect(Dialog.reject) # type: ignore
        QtCore.QMetaObject.connectSlotsByName(Dialog)

        # Update UI here
        self.listValues = listValues
        self.initialSetup()

    def retranslateUi(self, Dialog):
        _translate = QtCore.QCoreApplication.translate
        Dialog.setWindowTitle(_translate("Dialog", "Dialog"))
        self.lblStudentRegistration.setText(_translate("Dialog", "Student:"))
        self.lblScheduleRegistration.setText(_translate("Dialog", "Schedule:"))

##################### End UI Generation ###################

#############################################################
#                                                           #
#                          Events                           #
#                                                           #
#############################################################

    def initialSetup(self):
        self.setupDatabase()
        self.setValues()

    def setValues(self):
        if self.listValues == None:     #No list
            return
        self.cmbStudentRegistration.setCurrentText(self.listValues[0])
        details = self.getScheduleDetails(self.listValues)
        if details is not None:
            self.cmbScheduleRegistration.setCurrentText(details)



    def getValues(self):
        listResult = []
        listResult.append(self.cmbStudentRegistration.currentText())
        listResult.append(self.cmbScheduleRegistration.currentText())
        return listResult

##################### End Events ###################


#############################################################
#                                                           #
#                          Database                         #
#                                                           #
#############################################################
    def setupDatabase(self):
        self.connect()
        self.refresh()


    def connect(self):
        self.cnx = mysql.connector.connect(user="root",
                                           password="222488842dahy",
                                           host="127.0.0.1",
                                           database="mydb")

    def getAllStudents(self):
        # Create a cursor object to execute queries
        cursor = self.cnx.cursor()

        # Execute a query to retrieve data
        query = "SELECT student_id, student_first_name, student_last_name " \
                "FROM student " \
                "ORDER BY student_first_name, student_last_name ASC"
        cursor.execute(query)

        # Fetch all the rows returned by the query
        rows = cursor.fetchall()

        # Close the cursor
        cursor.close()

        # Return the rows
        return rows

    def getAllSchedules(self):
        # Create a cursor object to execute queries
        cursor = self.cnx.cursor()

        # Execute a query to retrieve data
        query = """SELECT s.schedule_id, s.semester, s.course_id, s.section, i.instructor_name
                   FROM schedule s
                   JOIN instructor i ON s.instructor_id = i.instructor_id
                   ORDER BY s.semester, s.course_id, s.section, i.instructor_name ASC"""
        cursor.execute(query)

        # Fetch all the rows returned by the query
        rows = cursor.fetchall()

        # Close the cursor
        cursor.close()

        # Return the rows
        return rows

    def getScheduleDetails(self, a_listValues):
        # Returns the details of a schedule based on its id
        course_id = a_listValues[1]
        section = a_listValues[2]
        semester = a_listValues[3]

        cursor = self.cnx.cursor()

        query = """SELECT s.semester, s.course_id, s.section, i.instructor_name
                   FROM schedule s
                   JOIN instructor i ON s.instructor_id = i.instructor_id
                   WHERE s.semester = %s AND s.course_id = %s AND s.section = %s"""
        cursor.execute(query, [semester, course_id, section])
        result = cursor.fetchone()
        cursor.close()

        if result is not None:
            return ' - '.join(map(str, result))

        return None

    def setupComboBoxStudentRegistration(self):
        # Get all students from the database
        students = self.getAllStudents()

        # Clear the combobox
        self.cmbStudentRegistration.clear()

        # Add each student to the combobox
        for student_id, first_name, last_name in students:
            self.cmbStudentRegistration.addItem(f"{first_name} {last_name}", student_id)

    def setupComboBoxScheduleRegistration(self):
        # Get all schedules from the database
        schedules = self.getAllSchedules()

        # Clear the combobox
        self.cmbScheduleRegistration.clear()

        # Add each schedule to the combobox
        for schedule_id, semester, course_id, section, instructor_name in schedules:
            self.cmbScheduleRegistration.addItem(f"{semester} - {course_id} - {section} - {instructor_name}",
                                                 schedule_id)

    def refresh(self):
        self.setupComboBoxStudentRegistration()
        self.setupComboBoxScheduleRegistration()


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    Dialog = QtWidgets.QDialog()
    ui = Ui_Dialog()
    ui.setupUi(Dialog, None)
    Dialog.show()
    sys.exit(app.exec_())