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()
