def SelectedDepartment(self): try: selectedDept = str(self.cmbDepartment.currentText()) #print(selectedDept) myDB._open_connection() mycursor = myDB.cursor(dictionary=True) sql = "SELECT CONCAT(Prefix, ' ', Lastname, ', ', Firstname) AS Fullname, UID FROM user_table WHERE Department = %s ORDER BY Lastname ASC" val = (selectedDept, ) mycursor.execute(sql, val) myresult = mycursor.fetchall() self.cmbInstructor.clear() self.cmbInstructor.addItem("--Select Instructor--", 0) for row in myresult: self.cmbInstructor.addItem(row["Fullname"], row["UID"]) print(myresult)#for testing the result except mysql.connector.Error as err: print("Error:", err.msg) finally: self.cmbInstructor.setCurrentIndex(0) mycursor.close() myDB.close() print("MySQL connection is closed")
def setstatusStudent(self): try: selected = [] # triggers when a row is selected in widget for i in range(1): selected.append( self.widgetAdmin1.item(self.widgetAdmin1.currentRow(), i).text()) print(selected) myDB._open_connection() mycursor = myDB.cursor() sql = 'UPDATE user_table SET Status = %s WHERE Student_ID = %s' val = (userStatus, selected[0]) mycursor.execute(sql, val) myDB.commit() self.populateRecordStudent() print(mycursor.rowcount, 'record updated') except mysql.connector.Error as err: print('Error:', err.msg) finally: mycursor.close() myDB.close() #-------------End of Manage Student
def __init__(self): super(createAccount, self).__init__() loadUi('newregistration.ui', self) self.btnRegister2.clicked.connect(self.createRegisterFunction) self.btnReturnToLogin.clicked.connect(self.returnToLogin) self.inputPassword2.setEchoMode(self.inputPassword2.Password) self.inputConfirmation.setEchoMode(self.inputConfirmation.Password) self.cmbCourse.clear() self.cmbCourse.activated.connect(self.on_currentIndexChanged) try: myDB._open_connection() mycursor = myDB.cursor(dictionary=True) sql = 'SELECT CID, CourseCode FROM course_table WHERE Status = 1 ORDER BY CourseCode ASC' mycursor.execute(sql) myresult = mycursor.fetchall() self.cmbCourse.addItem('--Course--', 0) for row in myresult: global getCourseID getCourseID = 0 #reset index to 0 self.cmbCourse.addItem(row["CourseCode"], row["CID"]) except mysql.connector.Error as err: print('Error:', err.msg) finally: self.cmbCourse.setCurrentIndex(0) mycursor.close() myDB.close() print('MySQL connection is close')
def filterTeacher(self, index): try: teacherFilter = self.cmbFilter2.itemText(index) myDB._open_connection() mycursor = myDB.cursor(dictionary=True) if teacherFilter == 'Active': sql = 'SELECT * FROM user_table WHERE Status = 1 AND Userlevel = 2 ORDER BY Lastname ASC' elif teacherFilter == 'Inactive': sql = 'SELECT * FROM user_table WHERE Status = 0 AND Userlevel = 2 ORDER BY Lastname ASC' else: sql = 'SELECT * FROM user_table WHERE Userlevel = 2 ORDER BY Lastname ASC' mycursor.execute(sql) # self.course_table.addItem(row['CourseCode'], row['CID']) myresult = mycursor.fetchall() self.widgetAdmin2.setRowCount(len(myresult)) # set number of rows self.widgetAdmin2.setColumnCount(5) rowcount = 0 for row in myresult: if row['Status'] == 1: status = 'Active' else: status = 'Inactive' self.widgetAdmin2.setItem( rowcount, 0, QTableWidgetItem(str(row['Employee_ID']))) self.widgetAdmin2.setItem(rowcount, 1, QTableWidgetItem(row['Lastname'])) self.widgetAdmin2.setItem(rowcount, 2, QTableWidgetItem(row['Firstname'])) self.widgetAdmin2.setItem(rowcount, 3, QTableWidgetItem(row['Email'])) self.widgetAdmin2.setItem(rowcount, 4, QTableWidgetItem(row['Department'])) self.widgetAdmin2.setItem(rowcount, 5, QTableWidgetItem(status)) rowcount = rowcount + 1 self.widgetAdmin2.horizontalHeader().setStretchLastSection(True) self.widgetAdmin2.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) except mysql.connector.Error as err: print('Error:', err.msg) finally: mycursor.close() myDB.close()
def populateRecord(self): try: SID = globals.getSID myDB._open_connection() mycursor = myDB.cursor(dictionary=True) sql = "SELECT CONCAT(U.Lastname, ', ', U.Firstname, ' ', U.MI) AS InstructorName, A.AID, A.Department, A.A_Date, A.Status FROM appointment_table AS A INNER JOIN user_table AS U ON(U.UID = A.EID) WHERE A.SID = %s ORDER BY A.A_Date AND A.Status=2 DESC" val = (SID,) mycursor.execute(sql, val) myresult = mycursor.fetchall() self.tblRecord.setRowCount(len(myresult)) ##set number of rows self.tblRecord.setColumnCount(5) rowcount = 0 for row in myresult: status = "" if row["Status"] == 1: status = "Accepted" if row["Status"] == 2: status = "On-Going" if row["Status"] == 3: status = "Declined" if row["Status"] == 4: status = "Cancelled" self.tblRecord.setItem(rowcount, 0, QTableWidgetItem(str(row["AID"]))) self.tblRecord.setColumnHidden(0, True) #-->> To Hide Column AID self.tblRecord.setItem(rowcount, 1, QTableWidgetItem(row["InstructorName"])) self.tblRecord.setItem(rowcount, 2, QTableWidgetItem(row["Department"])) self.tblRecord.setItem(rowcount, 3, QTableWidgetItem(str(row["A_Date"]))) self.tblRecord.setItem(rowcount, 4, QTableWidgetItem(status)) #self.tblRecord.setItem(rowcount, 3, QTableWidgetItem(QIcon("source-files/resources/images/cancel.png"),"", 1)) #THIS IS TO DISPLAY IMAGE TO TABLE WIDGET rowcount = rowcount + 1 self.tblRecord.horizontalHeader().setStretchLastSection(True) self.tblRecord.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) except mysql.connector.Error as err: print("Error:", err.msg) finally: mycursor.close() myDB.close() print("MySQL connection is closed")
def filterCourse(self, index): try: courseFilter = self.cmbFilter3.itemText(index) myDB._open_connection() mycursor = myDB.cursor(dictionary=True) if courseFilter == 'Active': sql = 'SELECT * from course_table WHERE Status = 1 ORDER BY CourseCode ASC' elif courseFilter == 'Inactive': sql = 'SELECT * FROM course_table WHERE Status = 0 ORDER BY CourseCode ASC' else: sql = 'SELECT * FROM course_table ORDER BY CourseCode ASC' mycursor.execute(sql) # self.course_table.addItem(row['CourseCode'], row['CID']) myresult = mycursor.fetchall() self.widgetAdmin3.setRowCount(len(myresult)) # set number of rows self.widgetAdmin3.setColumnCount(4) rowcount = 0 for row in myresult: if row['Status'] == 1: status = 'Active' else: status = 'Inactive' self.widgetAdmin3.setItem(rowcount, 0, QTableWidgetItem(str(row['CID']))) self.widgetAdmin3.setItem(rowcount, 1, QTableWidgetItem(row['CourseCode'])) self.widgetAdmin3.setItem(rowcount, 2, QTableWidgetItem(row['CourseDesc'])) self.widgetAdmin3.setItem(rowcount, 3, QTableWidgetItem(status)) rowcount = rowcount + 1 self.widgetAdmin3.horizontalHeader().setStretchLastSection(True) self.widgetAdmin3.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) except mysql.connector.Error as err: print("Error:", err.msg) finally: mycursor.close() myDB.close()
def LoginFunction(self): Email = self.inputEmail1.text().strip() Pass = self.inputPassword1.text().strip() try: myDB._open_connection() mycursor = myDB.cursor( dictionary=True) #use dictionary if there is a need to get #data in the database sql = 'SELECT * FROM user_table WHERE Email = %s AND Password = %s' val = (Email, base64.b64encode(Pass.encode())) mycursor.execute(sql, val) myresult = mycursor.fetchone() admin = 1 teacher = 2 student = 3 if not myresult == None: if myresult['Userlevel'] == admin: print(myresult['Email'], 'admin found') Login.hide(self) adminform = createAdmin() adminform.exec_() elif myresult['Userlevel'] == teacher: print(myresult['Email'], 'teacher found') Login.hide(self) teacherform = createTeacher() teacherform.exec_() elif myresult['Userlevel'] == student: print(myresult['Email'], 'student found') Login.hide(self) studentform = createStudent() studentform.exec_() else: QMessageBox.warning(self, "Invalid Input", "Invalid username/password") except mysql.connector.Error as err: print('Error:', err.msg) finally: mycursor.close() myDB.close()
def searchUser(self): try: inputSearch = self.inputSearch1.text().strip() myDB._open_connection() mycursor = myDB.cursor(dictionary=True) if len(inputSearch) > 0: sql = "SELECT * FROM user_table WHERE Lastname LIKE CONCAT('%', %s, '%') OR Student_ID = %s ORDER BY Lastname ASC" val = (inputSearch, inputSearch) mycursor.execute(sql, val) else: return myresult = mycursor.fetchall() self.widgetAdmin1.setRowCount(len(myresult)) # set number of rows self.widgetAdmin1.setColumnCount(5) rowcount = 0 for row in myresult: if row['Status'] == 1: status = 'Active' else: status = 'Inactive' self.widgetAdmin1.setItem( rowcount, 0, QTableWidgetItem(str(row['Student_ID']))) self.widgetAdmin1.setItem(rowcount, 1, QTableWidgetItem(row['Lastname'])) self.widgetAdmin1.setItem(rowcount, 2, QTableWidgetItem(row['Firstname'])) self.widgetAdmin1.setItem(rowcount, 3, QTableWidgetItem(row['Email'])) self.widgetAdmin1.setItem(rowcount, 4, QTableWidgetItem(status)) rowcount = rowcount + 1 self.widgetAdmin1.horizontalHeader().setStretchLastSection(True) self.widgetAdmin1.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) except mysql.connector.Error as err: print("Error:", err.msg) finally: mycursor.close() myDB.close()
def populateRecordTeacher(self): try: myDB._open_connection() mycursor = myDB.cursor(dictionary=True) sql = 'SELECT * FROM user_table WHERE Status = 1 AND Userlevel = 2 ORDER BY Lastname ASC' mycursor.execute(sql) myresult = mycursor.fetchall() self.widgetAdmin2.setRowCount(len(myresult)) # set number of rows self.widgetAdmin2.setColumnCount(5) rowcount = 0 for row in myresult: if row['Status'] == 1: status = 'Active' else: status = 'Inactive' self.widgetAdmin2.setItem( rowcount, 0, QTableWidgetItem(str(row['Employee_ID']))) self.widgetAdmin2.setItem(rowcount, 1, QTableWidgetItem(row['Lastname'])) self.widgetAdmin2.setItem(rowcount, 2, QTableWidgetItem(row['Email'])) self.widgetAdmin2.setItem(rowcount, 3, QTableWidgetItem(row['Department'])) self.widgetAdmin2.setItem(rowcount, 4, QTableWidgetItem(status)) rowcount = rowcount + 1 self.widgetAdmin2.horizontalHeader().setStretchLastSection(True) self.widgetAdmin2.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) except mysql.connector.Error as err: print('Error:', err.msg) finally: mycursor.close() myDB.close() print('MySQL connection is closed')
def populateRecord(self): try: myDB._open_connection() mycursor = myDB.cursor(dictionary=True) sql = 'SELECT * FROM course_table WHERE Status = 1 ORDER BY CourseCode ASC' mycursor.execute(sql) myresult = mycursor.fetchall() self.widgetAdmin3.setRowCount(len(myresult)) # set number of rows self.widgetAdmin3.setColumnCount(4) rowcount = 0 for row in myresult: if row['Status'] == 1: status = 'Active' else: status = 'Inactive' self.widgetAdmin3.setItem(rowcount, 0, QTableWidgetItem(str(row['CID']))) self.widgetAdmin3.setItem(rowcount, 1, QTableWidgetItem(row['CourseCode'])) self.widgetAdmin3.setItem(rowcount, 2, QTableWidgetItem(row['CourseDesc'])) self.widgetAdmin3.setItem(rowcount, 3, QTableWidgetItem(status)) rowcount = rowcount + 1 self.widgetAdmin3.horizontalHeader().setStretchLastSection(True) self.widgetAdmin3.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) except mysql.connector.Error as err: print('Error:', err.msg) finally: mycursor.close() myDB.close() print('MySQL connection is closed')
def saveCourse(self): CourseCode = self.inputCourseCode.text().strip() CourseDescription = self.inputCourseDescription.toPlainText() if CourseCode == "": QMessageBox.critical(self, "Required Input", "Please input Course Code") self.inputCourseCode.setFocus() return if CourseDescription == "": QMessageBox.critical(self, "Required Input", "Please input Course Code") self.inputCourseDescription.setFocus() return try: myDB._open_connection() mycursor = myDB.cursor() sql = 'INSERT INTO course_table(CourseCode, CourseDesc) VALUES (%s, %s)' val = (CourseCode, CourseDescription) mycursor.execute(sql, val) myDB.commit() print(mycursor.rowcount, 'record inserted') self.populateRecord() QMessageBox.information(self, "System Information", "Course Successfully Created!") except mysql.connector.Error as err: QMessageBox.information(self, "System Information", "The course code already used!") print('Error:', err.msg) finally: mycursor.close() myDB.close()
def setAppointment(self): global getEID getEID = 0 global getSID getSID = 0 EID = getEID SID = getSID Subject = self.inputAppointmentSubject.text().strip() DepartmentIndex = self.cmbDepartment.currentIndex() Instructor = str(self.cmbInstructor.currentIndex()) DepartmentText = str(self.cmbDepartment.currentText()) Details = self.txtDetails.toPlainText().strip() Date = datetime.today().strtime('%Y-%m-%d') Time = self.timeSet.time().toString() # -->>TIMER START self.timer = QTimer(self) self.timer.timeout.connect(self.timeprogress) # -->>TIME IN MILLISECONDS self.timer.start(1000) # set to 35 for fast load # -->>INITIAL TEXT self.lblTimeAndDate.setText(datetime.today().strftime('%m-%d-%Y | %I:%M:%S %p')) # -->>POPULATE DATA RECORD FROM DATABASE self.populateRecord() if len(Subject) == 0: QMessageBox.critical(self, 'Required Input', 'Please Enter Appointment Subject') self.txtAppointment.setFocus() return if DepartmentIndex == 0: QMessageBox.critical(self, 'Required Selection', 'Please Select a Department') self.cmbDepartment.setFocus() return if EID == 0 or EID == None or Instructor == "--Select Instructor--": QMessageBox.critical(self, 'Required Selection', 'Please Select an Instructor') self.cmbInstructor.setFocus() return try: myDB._open_connection() mycursor = myDB.cursor(dictionary = True) """---------------For trapping of once per day of appointment here--------------""" sql = "SELECT COUNT(AID) AS TotalCount, Department FROM appointment_table WHERE A_Date = %s AND Department = %s AND SID = %s AND Status = %s" val = (Date, DepartmentText, SID, 2) mycursor.execute(sql, val) myresult = mycursor.fetchall() print(myresult) # testing result of limit per day for getResult in myresult: if getResult["TotalCount"] < 1: print("pwede pa") else: QMessageBox.information(self, 'System Info', "You have pending appointment for " + DepartmentText + " Department! Please come back later or cancel your pending appointment!", QMessageBox.Ok) return """---------------End trapping of once per day of appointment--------------""" """---------------For trapping the priority number here--------------""" sql = "SELECT COUNT(AID) AS TotalCount, Department FROM appointment_table WHERE A_Date = %s AND Department = %s" val = (Date, DepartmentText) mycursor.execute(sql, val) myresult = mycursor.fetchall() print(myresult) # testing result of limit per day getLastPriorityNumber = 1 # default for first Priority number for getResult in myresult: if getResult["TotalCount"] < 5: print("pwede pa") else: QMessageBox.information(self, 'System Info', "Maximum appointment for " + DepartmentText + " Department! Please come back tomorrow!", QMessageBox.Ok) return getLastPriorityNumber = getResult["TotalCount"] + 1 """---------------End of trapping the priority number--------------""" msgboxButton = QMessageBox.question(self, 'System', "Do you want to set this appointment schedule?", QMessageBox.Yes | QMessageBox.No, QMessageBox.No) if msgboxButton == QMessageBox.Yes: sql = "INSERT INTO appointment_table(SID, EID, Subject, Department, Details, A_Date, A_Time, PriorityNo) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" val = (SID, EID, Subject, DepartmentText, Details, Date, Time, getLastPriorityNumber) mycursor.execute(sql, val) myDB.commit() print(mycursor.rowcount, "Record Inserted.") QMessageBox.warning(self, "System Info", "Successfully Saved Appointment!") self.populateRecord() #--> To refresh table record else: print("Nothing Inserted") except mysql.connector.Error as err: print("Error:", err.msg) finally: mycursor.close() myDB.close()
def saveTeacher(self): userlevel = 2 employeeID = self.inputEmployeeID.text().strip() department = self.cmbDepartment.currentText() teacherEmail = self.inputTeacherEmail.text().strip() teacherContact = self.inputTeacherContact.text().strip() teacherLast = self.inputTeacherLast.text().strip() teacherFirst = self.inputTeacherFirst.text().strip() teacherMI = self.inputTeacherMI.text().strip() teacherPre = self.cmbPrefix.currentText() if employeeID == "": QMessageBox.critical(self, "Required Input", "Please input Employee ID") self.inputEmployeeID.setFocus() return if teacherPre == '-Prefix-': QMessageBox.critical(self, "Required Selection", "Please select a Prefix") return if teacherEmail == "": QMessageBox.critical(self, "Required Input", "Please input Employee Email") self.inputTeacherEmail.setFocus() return if department == 0: QMessageBox.critical(self, "Required Selection", "Please select Department") self.cmbDepartment.setFocus() return if not re.match(r"[^@]+@[^@]+\.[^@]", teacherEmail): QMessageBox.critical(self, "Invalid Input", "Invalid Input Address") self.inputTeaacherEmail.setFocus() return if teacherContact == "": QMessageBox.critical(self, "Required Input", "Please input Employee's Contact") self.inputTeacherContact.setFocus() return if teacherLast == "": QMessageBox.critical(self, "Required Input", "Please input Employee's Surname") self.inputTeacherLast.setFocus() return if teacherFirst == "": QMessageBox.critical(self, "Required Input", "Please input Employee's First Name") self.inputTeacherFirst.setFocus() return try: myDB._open_connection() mycursor = myDB.cursor() sql = 'INSERT INTO user_table(Employee_ID, Department, Email, Contact, Prefix, Lastname, Firstname, MI, Userlevel) ' \ 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)' val = (employeeID, department, teacherEmail, teacherContact, teacherPre, teacherLast, teacherFirst, teacherMI, userlevel) mycursor.execute(sql, val) myDB.commit() print(mycursor.rowcount, "record inserted") self.populateRecord() QMessageBox.information(self, "System Information", "Employee Account Successfully Created!") except mysql.connector.Error as err: QMessageBox.warning(self, "System Information", "Employee already existed!") print("Error:", err.msg) finally: mycursor.close() myDB.close()
def createRegisterFunction(self): userlevel = 3 IDNumber = self.inputID.text().strip() Course = getCourseID FName = self.inputFirst.text().strip() LName = self.inputLast.text().strip() MI = self.inputMI.text().strip() Email = self.inputEmail2.text().strip() Contact = self.inputContact.text().strip() Password = self.inputPassword2.text().strip() Confirmation = self.inputConfirmation.text().strip() base64_encryption = base64.b64encode( Password.encode()) #encoding encryption if IDNumber == '': QMessageBox.critical(self, "Required Input", "Please input ID Number") self.inputID.setFocus() return if Course == 0: QMessageBox.critical(self, "Required Selection", "Please select Course") self.cmbCourse.setFocus() return if FName == '': QMessageBox.critical(self, "Required Input", "Please input Name") self.inputFirst.setFocus() return if LName == '': QMessageBox.critical(self, "Required Input", "Please input Name") self.inputLast.setFocus() return #EMAIL_REGEX = re.compile(r"[^@]+@[^@]+\.[^@]+") if not re.match(r"[^@]+@[^@]+\.[^@]+", Email): QMessageBox.critical(self, "Required Input", "Please input XU Email") self.inputEmail2.setFocus() return SpecialSymbols = [ '!', '@', '#', '$', '%', '^', '&', '*', '(', ')', '_', '-', '+', '=', '~', '`', '[', '{', ']', '}', ';', ':', '', '"', '<', ',', '.', '>', '/', '?', '|' ] if Password == '': QMessageBox.critical(self, "Required Input", "Please input Password") self.inputPassword2.setFocus() return if len(Password) < 6: QMessageBox.critical(self, "Invalid Input", "Please input at least 6 Password length") self.inputPassword2.setFocus() return if len(Password) > 15: QMessageBox.critical( self, "Invalid Input", "Please input not more than 15 Password length") self.inputPassword2.setFocus() return if not any(char.isdigit() for char in Password): QMessageBox.critical(self, "Invalid Input", "Please input at least one numeral") self.inputPassword2.setFocus() return if not any(char.isupper() for char in Password): QMessageBox.critical(self, "Invalid Input", "Please input at least one uppercase") self.inputPassword2.setFocus() return if not any(char.islower() for char in Password): QMessageBox.critical(self, "Invalid Input", "Please input at least one lowercase") self.inputPassword2.setFocus() return if not any(char in SpecialSymbols for char in Password): QMessageBox.critical(self, "Invalid Input", "Please input at least one special symbol") self.inputPassword2.setFocus() return if not Password == Confirmation: QMessageBox.critical(self, "Invalid Input", "The password does not match!") self.inputPassword2.setFocus() return try: myDB._open_connection() mycursor = myDB.cursor() #mycursor executes myDB in connection.py sql = 'INSERT INTO user_table (Student_ID, Firstname, Lastname, MI, Email, Password, Contact, Course, Userlevel) ' \ 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)' #VALUES converts variable val = (IDNumber, FName, LName, MI, Email, base64_encryption, Contact, Course, userlevel) mycursor.execute(sql, val) myDB.commit( ) #need to have changes in the database (except SELECT) print(mycursor.rowcount, 'record inserted') QMessageBox.information(self, "System Information", "Account Successfully Created!") except mysql.connector.Error as err: QMessageBox.information(self, "System Information", "Email already used") print('Error:', err.msg) finally: mycursor.close() myDB.close()