Ejemplo n.º 1
0
def insert_update_user(id, username, password, type, update):
    try:
        user = (id, username, password, type)
        user2 = (username, password, type, id)

        conn = db.connect()
        cursor = conn.conn.cursor()

        if not update:
            cursor.execute(
                "INSERT INTO User (userId, username," +
                " password, type) VALUES(%s,%s,%s,%s)", user)
            conn.conn.commit()
        else:
            cursor.execute(
                "UPDATE User SET username = %s, password = %s, type = %s WHERE userId = %s",
                user2)
            conn.conn.commit()

    except Error as e:
        ret = AdmReturn()
        print("Error while connecting to MySQL", e)
        ret.result = None
        ret.msg = "An error occurred while searching user"
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 2
0
def find_user(userId):
    try:

        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT * FROM User WHERE userId = %s ", (userId, ))
        result = cursor.fetchone()

        ret = AdmReturn()
        ret.result = result
        ret.msg = ""

        return ret

    except Error as e:
        ret = AdmReturn()
        print("Error while connecting to MySQL", e)
        ret.result = None
        ret.msg = "An error occurred while searching user"
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 3
0
def find_professor(idProf):
    try:
        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT * FROM Professor WHERE idProf= %s", (idProf, ))
        prof = cursor.fetchone()

        ret = AdmReturn()
        if prof is not None:
            ret.result = prof
            ret.msg = ""
            return ret
        else:
            ret.result = ""
            ret.msg = "Professor not found."
            return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        return "An error occurred while searching"

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 4
0
def find_course(code):
    ret = ProfReturn()
    try:
        conn = db.connect()
        cursor = conn.conn.cursor()
        if code is not None:
            cursor.execute(
                "SELECT courseName FROM Course WHERE courseCode= %s", (code, ))
            course = cursor.fetchone()

            cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
                           (code, ))
            std = cursor.fetchone()
            if course is not None:
                ret.std = std
                ret.course = course
                return ret
            else:
                ret.msg = "Course not found"
                return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        ret.msg = "Course not found"
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 5
0
def find_student(idStd):
    try:
        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT * FROM Student WHERE idStudents= %s", (idStd, ))
        std = cursor.fetchone()

        ret = AdmReturn()
        if std is not None:
            ret.result = std
            ret.msg = ""
            return ret
        else:
            ret.result = ""
            ret.msg = "Student not found."
            return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        return "An error occurred while searching"

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 6
0
def delete_course(code):
    try:
        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
                       (code, ))
        student = cursor.fetchone()

        ret = AdmReturn()

        if student is None:
            cursor.execute("DELETE FROM Course WHERE courseCode = %s",
                           (code, ))
            conn.conn.commit()
            ret.result = ""
            ret.msg = "Course successfully deleted!"
        else:
            ret.result = ""
            ret.msg = "This course can't be deleted! Students have been graded."

        return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        ret = AdmReturn()
        ret.result = ""
        ret.msg = "An error occurred while deleting"
        conn.conn.rollback()
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 7
0
def save(stdId, grade, course):
    ret = ProfReturn()
    data = (grade, stdId, course)

    try:
        conn = db.connect()
        cursor = conn.conn.cursor()
        if stdId and grade and course:
            cursor.execute(
                "UPDATE Student SET grade = %s WHERE profIdStd = %s AND courseCodeStd = %s",
                data)
            conn.conn.commit()

            ret.msg = "Grade updated"
            return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        ret.msg = "Error while updating"
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 8
0
def get_std_grades_by_user_id(userId, login_main):

    try:

        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT * FROM Student WHERE userIdStd = %s",
                       (userId, ))
        result = cursor.fetchall()

        from GUI import StudentGUI as std

        # close parent before open adm
        login_main.withdraw()
        win = tkinter.Toplevel(login_main)
        std.StudentGUI(win, result)
        return result

    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 9
0
def delete_professor(idProf, codeCourse):
    try:

        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
                       (codeCourse, ))
        student = cursor.fetchone()
        ret = AdmReturn()

        if student is not None:

            cursor.execute(
                "SELECT * FROM Professor WHERE courseCodeProf= %s and idProf = %s",
                (codeCourse, idProf))
            prof = cursor.fetchone()

            if prof is None:
                cursor.execute(
                    "DELETE FROM Professor WHERE idProf = %s and courseCodeProf= %s",
                    (idProf, codeCourse))
                # delete User
                cursor.execute("DELETE FROM User WHERE userId = %s",
                               (idProf, ))
                conn.conn.commit()
                ret.result = ""
                ret.msg = "Professor successfully deleted!"

            else:
                ret.result = ""
                ret.msg = "Can't be deleted! This professor is linked to a grade student."
        else:
            cursor.execute(
                "DELETE FROM Professor WHERE idProf = %s and courseCodeProf= %s",
                (idProf, codeCourse))
            #delete User
            cursor.execute("DELETE FROM User WHERE userId = %s", (idProf, ))
            conn.conn.commit()
            ret.result = ""
            ret.msg = "Professor successfully deleted!"

        return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        ret = AdmReturn()
        ret.result = ""
        ret.msg = "An error occurred while deleting"
        conn.conn.rollback()
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 10
0
def save_student(idStudents, studentName, courseCodeStd, grade, userIdStd,
                 username, password, type, profId):
    try:
        if not grade:
            grade = None

        data = (idStudents, studentName, courseCodeStd, grade, userIdStd,
                profId)
        data2 = (studentName, courseCodeStd, grade, profId, idStudents)

        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT * FROM STUDENT WHERE idStudents= %s",
                       (idStudents, ))
        student = cursor.fetchone()

        if student is None:
            # create user
            insert_update_user(idStudents, username, password, type, False)
            # create student
            cursor.execute(
                "INSERT INTO Student (idStudents, studentName, courseCodeStd,"
                + " grade, userIdStd, profIdStd) VALUES(%s,%s,%s,%s,%s,%s)",
                data)
            conn.conn.commit()
            return "Student saved."

        else:
            if student[3] is None or student[3] == "":
                # update user
                insert_update_user(idStudents, username, password, type, True)
                # update student
                cursor.execute(
                    "UPDATE Student SET studentName = %s, " +
                    "courseCodeStd = %s, grade = %s, profIdStd = %s " +
                    "WHERE idStudents = %s", data2)
                conn.conn.commit()
                return "Student updated."
            else:
                return "Can't be updated. This student was graded."

    except Error as e:
        print("Error while connecting to MySQL", e)
        return "An error occurred while saving"

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 11
0
def find_course(code):
    try:
        conn = db.connect()
        cursor = conn.conn.cursor()
        if code is not None:
            cursor.execute("SELECT * FROM COURSE WHERE courseCode= %s",
                           (code, ))
            course = cursor.fetchone()

            ret = AdmReturn()
            if course is not None:
                ret.result = course
                ret.msg = ""
                return ret
            else:
                ret.result = ""
                ret.msg = "Course not found"
                return ret
        else:
            cursor.execute("SELECT * FROM COURSE")
            course = cursor.fetchall()
            courses = []
            ret = AdmReturn()

            if course is not None:
                for cor in course:
                    courses.append(cor[0])

                ret.result = courses
                ret.msg = ""
                return ret
            else:
                ret = AdmReturn()
                ret.result = ""
                ret.msg = "Course not found"
                return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        ret = AdmReturn()
        ret.result = ""
        ret.msg = "Course not found"
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 12
0
def login(username, password, type, login_main):

    try:
        data = (username, password, type)

        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT * FROM User WHERE username = %s " +
                       "AND password = %s " +
                       "AND type = %s", data)
        result = cursor.fetchone()

        if result is not None:
            types = enum.UserTypes

            if result[3] == "ADM":
                from GUI import AdminGUI as adm

                # close parent before open adm
                login_main.withdraw()
                win = tkinter.Toplevel(login_main)
                adm.AdminGUI(win)

            elif result[3] == types.STUDENT.name:
                stdService.get_std_grades_by_user_id(result[0], login_main)
                return
            else:
                from GUI import ProfessorGUI as prof

                # close parent before open adm
                login_main.withdraw()
                win = tkinter.Toplevel(login_main)
                prof.ProfessorGUI(win, result[0])

        else:
            return "User not found"

    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 13
0
def find_student(idStd, profId):
    ret = ProfReturn()
    try:
        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute(
            "SELECT * FROM Student WHERE idStudents= %s and profIdStd = %s",
            (idStd, profId))
        std = cursor.fetchall()

        if std.__len__() > 0:
            courseList = []
            for c in std:
                courseList.append(c[2])

            param = ','.join(['%s'] * len(courseList))
            sql = """SELECT * FROM Course WHERE courseCode IN (%s)"""
            cursor.execute(
                "SELECT * FROM Course WHERE courseCode IN (%s)" % param,
                tuple(courseList))
            courses = cursor.fetchall()

            ret.std = std[0]
            ret.course = courses
            ret.msg = ""
            return ret
        else:
            ret.std = ""
            ret.course = ""
            ret.msg = "Student not found."
            return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        ret.result = ""
        ret.msg = "An error occurred while searching"
        return

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 14
0
def save_course(code, name):
    try:
        data = (code, name)
        data2 = (code, name, code)

        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT grade FROM STUDENT WHERE courseCodeStd= %s",
                       (code, ))
        student = cursor.fetchone()

        cursor.execute("SELECT * FROM COURSE WHERE courseCode= %s", (code, ))
        course = cursor.fetchone()

        if course is not None:
            if student is None:
                cursor.execute(
                    "UPDATE Course SET courseCode = %s, courseName = %s WHERE courseCode = %s",
                    data2)
                conn.conn.commit()
                return "Course updated"

            else:
                return "This course cannot be updated because is linked to a student grade."

        else:
            cursor.execute(
                "INSERT INTO Course (courseCode, courseName) VALUES(%s,%s)",
                data)
            conn.conn.commit()
            return "Course saved"

    except Error as e:
        print("Error while connecting to MySQL", e)
        return "An error occurred while saving"

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 15
0
def delete_student(id):
    try:

        conn = db.connect()
        cursor = conn.conn.cursor()
        cursor.execute("SELECT grade FROM Student WHERE idStudents= %s",
                       (id, ))
        student = cursor.fetchone()

        ret = AdmReturn()

        if student[0] is None:
            cursor.execute("DELETE FROM Student WHERE idStudents = %s", (id, ))
            # delete user
            cursor.execute("DELETE FROM User WHERE userId = %s", (id, ))
            conn.conn.commit()
            ret.result = ""
            ret.msg = "Student successfully deleted!"
        else:
            ret.result = ""
            ret.msg = "Can't be deleted! This student was already graded."

        return ret

    except Error as e:
        print("Error while connecting to MySQL", e)
        ret = AdmReturn()
        ret.result = ""
        ret.msg = "An error occurred while deleting"
        conn.conn.rollback()
        return ret

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")
Ejemplo n.º 16
0
def save_professor(idProf, nameProf, courseCodeProf, usename, password, type):
    try:

        data_prof = (idProf, nameProf, courseCodeProf, idProf)
        data_prof2 = (nameProf, courseCodeProf, idProf)

        conn = db.connect()
        cursor = conn.conn.cursor()
        # verify professor
        cursor.execute(
            "SELECT * FROM Professor WHERE idProf= %s and courseCodeProf = %s",
            (idProf, courseCodeProf))
        professor = cursor.fetchone()
        ret = AdmReturn()

        if professor is None:
            # create user
            insert_update_user(idProf, usename, password, type, False)
            # create professor
            cursor.execute(
                "INSERT INTO Professor (idProf, nameProf, courseCodeProf," +
                " userIdProf) VALUES(%s,%s,%s,%s)", data_prof)
            conn.conn.commit()

            ret.result = ""
            ret.msg = "Professor saved"
            return ret

        else:
            # verify if has graded student with that course linked to the teacher
            cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
                           (courseCodeProf, ))
            student = cursor.fetchone()

            if student is not None:
                # Verify if the course is given by the professor
                if professor[1] != courseCodeProf:
                    # update user
                    insert_update_user(idProf, usename, password, type, True)
                    # update professor
                    cursor.execute(
                        "UPDATE Professor SET nameProf = %s, " +
                        "courseCodeProf = %s" + "WHERE idProf = %s",
                        data_prof2)
                    conn.conn.commit()
                    ret.result = ""
                    ret.msg = "Professor updated"
                    return ret
                else:
                    ret.result = ""
                    ret.msg = "Can't be updated. This professor is linked with a student grade."
                    return ret
            else:
                # update user
                insert_update_user(idProf, usename, password, type, True)
                cursor.execute(
                    "UPDATE Professor SET nameProf = %s, " +
                    "courseCodeProf = %s" + "WHERE idProf = %s", data_prof2)
                conn.conn.commit()
                ret.result = ""
                ret.msg = "Professor updated"
                return ret

    except Error as e:
        conn.conn.rollback()
        print("Error while connecting to MySQL", e)
        return "An error occurred while saving"

    finally:
        if conn.conn.is_connected():
            cursor.close()
            conn.conn.close()
            print("Connection is closed")