def studentUpdateProfile(Student2, username):
try:
db = DatabaseConnection()
c = db.cursor()
stmt = "UPDATE Students SET FirstName = %s, LastName = %s, " \
"MiddleInitial = %s, Suffix = %s, PreferredName = %s, " \
"DateOfBirth = %s, Gender = %s, Race = %s, Address = %s, " \
"City = %s, State = %s, Zip = %s, Email = %s, " \
"PhoneNumber = %s, DisabilityInfo = %s, HealthConditions = %s," \
"Siblings = %s, SchoolName = %s, SchoolDistrict = %s, " \
"SchoolType = %s, GradeInFall = %s, ExpectedHighSchool = %s, " \
"ExpectedGradYear = %s, GT = %s, ELL = %s " \
"WHERE UserName = %s"
vals = (Student2.FirstName, Student2.LastName, Student2.MiddleInitial,
Student2.Suffix, Student2.PreferredName, Student2.DateOfBirth,
Student2.Gender, Student2.Race, Student2.Address,
Student2.City, Student2.State, Student2.Zip, Student2.Email,
Student2.PhoneNumber, Student2.DisabilityInfo,
Student2.HealthConditions, Student2.Siblings,
Student2.SchoolName, Student2.SchoolDistrict,
Student2.SchoolType, Student2.GradeInFall,
Student2.ExpectedHighSchool, Student2.ExpectedGradYear,
Student2.GT, Student2.ELL, username)
c.execute(stmt, vals)
db.commit()
except (sql.Error, sql.Warning) as e:
print(e)
exit(0)
def getEditStudentFromPersonnel2(username):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Students WHERE UserName = '******'"
mycursor.execute(statement)
data = mycursor.fetchall()
print data
statement = "SELECT * FROM Instructors"
mycursor.execute(statement)
obj = mycursor.fetchall()
val = "'" + username + "'"
sID = getStudentID(val)
statement = "SELECT * FROM Mentors WHERE StudentID = '" + sID[0] + "'"
mycursor.execute(statement)
preMentor = mycursor.fetchall()
print preMentor
if (len(preMentor) != 0):
statement = "SELECT * FROM Instructors WHERE InstructorID = '" + preMentor[
0][0] + "'"
mycursor.execute(statement)
mData = mycursor.fetchall()
print mData
# return render_template(filename, data=data, obj = obj)
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO RETURN STUDENTID')
exit(0)
def updateStudent(Student):
try:
db = DatabaseConnection()
c = db.cursor()
stmt = "UPDATE Students SET FirstName = %s, LastName = %s, " \
"MiddleInitial = %s, Suffix = %s, PreferredName = %s, " \
"DateOfBirth = %s, Gender = %s, Race = %s, Address = %s, " \
"City = %s, State = %s, Zip = %s, Email = %s, " \
"PhoneNumber = %s, DisabilityInfo = %s, HealthConditions = " \
"%s, Siblings %s, SchoolName = %s, SchoolDistrict = %s, " \
"SchoolType = %s, GradeInFall = %s, ExpectedHighSchool = %s, " \
"ExpectedGradYear = %s, GT = %s, ELL = %s, UserName = %s, " \
"Password = %s WHERE StudentID = %s"
vals = (Student.FirstName, Student.LastName, Student.MiddleInitial,
Student.Suffix, Student.PreferredName, Student.DateOfBirth,
Student.Gender, Student.Race, Student.Address, Student.City,
Student.State, Student.Zip, Student.Email, Student.PhoneNumber,
Student.DisabilityInfo, Student.HealthConditions,
Student.Siblings, Student.SchoolName, Student.SchoolDistrict,
Student.SchoolType, Student.GradeInFall,
Student.ExpectedHighSchool, Student.ExpectedGradYear,
Student.GT, Student.ELL, Student.UserName, Student.Password,
Student.id)
c.execute(stmt, vals)
db.commit()
except (sql.Error, sql.Warning) as e:
print(e)
exit(0)
def getEditCourseInfo(filename, courseId):
try:
val = "'" + courseId + "'"
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Classes WHERE ClassID = " + val
mycursor.execute(statement)
data = mycursor.fetchall()
statement = "SELECT * FROM Instructors"
mycursor.execute(statement)
obj = mycursor.fetchall()
statement = "SELECT Takes.StudentID, Takes.ClassID, Students.FirstName, Students.LastName, Students.UserName " \
"FROM Students, Takes WHERE Takes.StudentID = Students.StudentID AND Takes.ClassID = " + val + " AND Takes.IsDeleted = '0'"
mycursor.execute(statement)
currStudents = mycursor.fetchall()
print(currStudents)
statement = "SELECT Students.StudentID, Students.FirstName, Students.LastName, Students.UserName " \
"FROM Students WHERE Students.StudentID NOT IN (SELECT Students.StudentID " \
"FROM Students, Takes WHERE Takes.StudentID = Students.StudentID AND Takes.ClassID = " + val + " AND Takes.IsDeleted = '0')"
mycursor.execute(statement)
otherStudents = mycursor.fetchall()
print(otherStudents)
return render_template(filename,
data=data,
obj=obj,
cStu=currStudents,
oStu=otherStudents)
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO RETURN STUDENTID')
exit(0)
def checkIfCourseExists(courseid):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
val = "'" + courseid + "'"
print(courseid)
statement = "SELECT * FROM Classes WHERE ClassID = " + val
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO SELECT: TRY AGAIN')
exit(0)
def updatePersonnelProfile(firstName, lastName, username):
try:
db = DatabaseConnection()
c = db.cursor()
stmt = "UPDATE Instructors SET FirstName = %s, LastName = %s WHERE UserName = %s"
vals = (firstName, lastName, username)
c.execute(stmt, vals)
db.commit()
except (sql.Error, sql.Warning) as e:
print(e)
exit(0)
def deleteTake(studentID, classID):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "UPDATE Takes SET IsDeleted = 1 WHERE StudentID = %s AND ClassID = %s"
mycursor.execute(statement, (studentID, classID))
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO UPDATE: TRY AGAIN')
exit(0)
def insertMentor(Mentor):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "INSERT INTO Mentors (InstructorID, StudentID, IsDeleted) VALUES (%s,%s,%s)"
mycursor.execute(statement, (Mentor.InstructorID, Mentor.StudentID, Mentor.IsDeleted))
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO INSERT: TRY AGAIN')
exit(0)
def getStudentID(username):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT StudentID FROM Students WHERE UserName = " + username
mycursor.execute(statement)
data = mycursor.fetchone()
return data
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO RETURN STUDENTID')
exit(0)
def deleteClass(classID):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "UPDATE Classes SET IsDeleted = 1 WHERE ClassID = " + str(
classID)
mycursor.execute(statement)
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO UPDATE: TRY AGAIN')
exit(0)
def deleteAdditionalInfo(studentID):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "UPDATE AdditionalInfo SET IsDeleted = 1 WHERE StudentID = " + studentID
mycursor.execute(statement)
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO UPDATE: TRY AGAIN')
exit(0)
def getPersonnelInfoOnly(username):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT FirstName, LastName FROM Instructors WHERE UserName = '******'"
mycursor.execute(statement)
data = mycursor.fetchall()
print(data)
return data
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO RETURN USERNAME')
exit(0)
def getAllCourses(filename):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Classes WHERE IsDeleted = '0'"
mycursor.execute(statement)
data = mycursor.fetchall()
return render_template(filename, data=data)
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO SELECT: TRY AGAIN')
exit(0)
def getAllCoursesNoFile():
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Classes"
mycursor.execute(statement)
data = mycursor.fetchall()
return data
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO SELECT: TRY AGAIN')
exit(0)
def updateTake2(courseid, studentid):
try:
db = DatabaseConnection()
c = db.cursor()
stmt = "UPDATE Takes SET IsDeleted = '0' WHERE StudentID = %s AND ClassID = %s"
vals = (studentid, courseid)
c.execute(stmt, vals)
db.commit()
except (sql.Error, sql.Warning) as e:
print(e)
exit(0)
def updateTake(oldTake, Take):
try:
db = DatabaseConnection()
c = db.cursor()
stmt = "UPDATE Takes SET ClassID = %s WHERE StudentID = %s AND ClassID = %s"
vals = (Take.ClassID, Take.StudentID, oldTake.ClassID)
c.execute(stmt, vals)
db.commit()
except (sql.Error, sql.Warning) as e:
print(e)
exit(0)
def getStudentByUsernameOnly(username):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Students WHERE UserName = " + username
mycursor.execute(statement)
data = mycursor.fetchall()
if mycursor.rowcount == 0:
data = 0
return data
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO SELECT: TRY AGAIN')
exit(0)
def getCourseByRoomAndTime(building, room, time, session):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Classes WHERE Building = '" + building + "' AND RoomNumber = '" + room + "' AND TimeSlot = '" + time + "' AND Session = '" + session + "'"
mycursor.execute(statement)
data = mycursor.fetchall()
if mycursor.rowcount == 0:
data = 0
return data
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO SELECT: TRY AGAIN')
exit(0)
def insertInstructor(Instructor):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "INSERT INTO Instructors (InstructorID, FirstName, LastName, UserName, Password, IsDeleted) " \
"VALUES (%s,%s,%s,%s,%s,%s)"
mycursor.execute(statement, (Instructor.InstructorID, Instructor.FirstName, Instructor.LastName,
Instructor.Username, Instructor.Password, Instructor.IsDeleted))
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO INSERT: TRY AGAIN')
exit(0)
def decrementClassSize(courseid):
mydb = DatabaseConnection()
mycursor = mydb.cursor()
val = "'" + courseid + "'"
statement = "SELECT NumberOfStudentsRegistered FROM Classes WHERE ClassID = " + val
mycursor.execute(statement)
data = mycursor.fetchall()
num = int(data[0][0])
num = num - 1
statement = "UPDATE Classes SET NumberOfStudentsRegistered = " + str(
num) + " WHERE ClassID = " + val
mycursor.execute(statement)
mydb.commit()
def checkIfCorrectInstructorPassword(username, password):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT Password FROM Instructors WHERE UserName = '******'"
mycursor.execute(statement)
data = mycursor.fetchall()
if data[0][0] == password:
return "Correct Password"
else:
return "Incorrect Password"
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO SELECT: TRY AGAIN')
exit(0)
def getIfAlreadyInTakes(courseID, studentId):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Takes WHERE StudentID = '" + studentId + "' AND ClassID = '" + courseID + "'"
mycursor.execute(statement)
data = mycursor.fetchall()
if len(data) == 0:
return "Can input"
else:
return "don't input"
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO SELECT: TRY AGAIN')
exit(0)
def insertTake(Take):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
print Take.StudentID
print Take.ClassID
val = "'" + Take.StudentID[0][0] + "'"
print val
statement = "INSERT INTO Takes (StudentID, ClassID, IsDeleted) VALUES (%s,%s,%s)"
mycursor.execute(statement, (Take.StudentID, Take.ClassID, Take.IsDeleted))
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO INSERT: TRY AGAIN')
exit(0)
def updateInstructor(Instructor):
try:
db = DatabaseConnection()
c = db.cursor()
stmt = "UPDATE Instructors SET FirstName = %s, LastName = %s, " \
"UserName = %s, Password = %s WHERE StudentID = %s"
vals = (Instructor.FirstName, Instructor.LastName, Instructor.UserName,
Instructor.Password, Instructor.InstructorID)
c.execute(stmt, vals)
db.commit()
except (sql.Error, sql.Warning) as e:
print(e)
exit(0)
def updateMentor(oldMentor, newMentor):
try:
db = DatabaseConnection()
c = db.cursor()
stmt = "UPDATE Mentor SET InstructorID = %s, StudentID = %s WHERE InstructorID = %s AND StudentID = %s"
vals = (newMentor.InstructorID, newMentor.StudentID,
oldMentor.InstructorID, oldMentor.InstructorID)
c.execute(stmt, vals)
db.commit()
except (sql.Error, sql.Warning) as e:
print(e)
exit(0)
def checkCourseInSession(className, sess):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "SELECT * FROM Classes WHERE Classes.ClassName = %s AND Classes.Session = %s"
vals = (className, sess)
mycursor.execute(statement, vals)
data = mycursor.fetchall()
if mycursor.rowcount == 0:
data = 0
return data
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO RETURN STUDENTID')
exit(0)
def getCourseID(className, sess):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
print className
print sess
statement = "SELECT ClassID FROM Classes WHERE Classes.ClassName = %s AND Classes.Session = %s"
vals = (className, sess)
mycursor.execute(statement, vals)
data = mycursor.fetchone()
return data
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO RETURN COURSEID')
exit(0)
def insertAdditionalInfo(AdditionalInformation):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "INSERT INTO AdditionalInfo (StudentID, YearAccepted, GradeWhenAccepted, Status, FundingStatus, " \
"GrantName, NationalClearingHouse, IsDeleted) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
mycursor.execute(statement, (AdditionalInformation.StudentID, AdditionalInformation.YearAccepted,
AdditionalInformation.GradeWhenAccepted, AdditionalInformation.Status,
AdditionalInformation.FundingStatus, AdditionalInformation.GrantName,
AdditionalInformation.NationalClearingHouse, AdditionalInformation.IsDeleted))
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO INSERT: TRY AGAIN')
exit(0)
def insertParent(Parent):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "INSERT INTO Parents (ParentID, StudentID, FirstName, LastName, Address, City, State, Zip, Email, " \
"CellPhoneNumber, WorkPhoneNumber, HomePhoneNumber, IsDeleted) VALUES " \
"(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
mycursor.execute(statement, (Parent.ParentID, Parent.StudentID, Parent.FirstName, Parent.LastName, Parent.Address,
Parent.City, Parent.State, Parent.Zip, Parent.Email, Parent.CellPhoneNumber,
Parent.WorkPhoneNumber, Parent.HomePhoneNumber, Parent.IsDeleted))
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO INSERT: TRY AGAIN')
exit(0)
def insertClass(Class):
try:
mydb = DatabaseConnection()
mycursor = mydb.cursor()
statement = "INSERT INTO Classes (ClassID, ClassName, InstructorID, Session, Level, TimeSlot, Building, " \
"RoomNumber, Capacity, NumberOfStudentsRegistered, NumberOfStudentsWaitlisted, IsDeleted)" \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
mycursor.execute(statement, (Class.ClassID, Class.ClassName, Class.InstructorID, Class.Session, Class.Level,
Class.TimeSlot, Class.Building, Class.RoomNumber, Class.Capacity,
Class.NumOfStudentsRegistered, Class.NumOfStudentsWaitlisted, Class.IsDeleted))
mydb.commit()
except (mysql.connector.Error, mysql.connector.Warning) as e:
print(e)
print('FAILED TO INSERT: TRY AGAIN')
exit(0)
