Untitled

import pymysql as pm
import re
conn = pm.connect(host='localhost', user='root', port='', database='WSHS Swimming Carnival')
cursor = conn.cursor()


def login():
    print("Please enter your Username and Password")
    while True:
        userName = input("Username:")
        userPassword = input("Password:")
        sql = "SELECT UserID FROM UserInformation WHERE UserName = '"+userName+"' AND UserPassword = '"+userPassword+"'"
        cursor.execute(sql)
        conn.commit()
        for row in cursor.fetchall():
            if row[0] > 0:
                showInfo(int(row[0]))
                break
            else:
                print("Invalid Username or Password")

def raceDataEntry():
    cursor.execute("SELECT DISTINCT RaceID FROM StudentRaces WHERE TimeTaken IS NULL")
    undocumentedRaces = [row[0] for row in cursor.fetchall()]

    print(undocumentedRaces)

    print("You are about to input race times into the system")
    print("These are the races yet to be documented:")
    count = 0
    for i in undocumentedRaces:
        cursor.execute(f"SELECT RaceName, RaceID FROM RaceInformation WHERE RaceID = '{i}'")
        for row in cursor.fetchall():
            count += 1
            print(str(count) + ") " + str(row[0]))
            print(f"Race id: {row[1]}")


    while True:
        try:
            userChoice = int(input("Which race do you want to input data for? Please input the race id."))
            if userChoice in undocumentedRaces:
                break
            else:
                print("Invalid Answer")
        except:
            print("Invalid Answer")
    getStudentNames(userChoice)

def getStudentNames(raceID):
    sql = "SELECT StudentID FROM StudentRaces WHERE RaceID = '"+str(raceID)+"'"
    cursor.execute(sql)
    conn.commit()
    studentNameList = cursor.fetchall()
    print("This is a list of all students who participated in this race.")
    for row in studentNameList:
        sql = "SELECT FirstName, LastName FROM StudentData WHERE StudentID = '"+str(row[0])+"'"
        cursor.execute(sql)
        conn.commit()
        for row in cursor.fetchall():
            print(row)
    finalDataEntry(raceID)

def finalDataEntry(raceID):
    cursor.execute("SELECT ui.UserID, sd.FirstName, sd.LastName FROM StudentData sd JOIN UserInformation ui ON sd.UserID = ui.UserID JOIN StudentRaces sr ON sd.StudentID = sr.StudentID WHERE sr.RaceID = %s", raceID)
    participants = cursor.fetchall()
    for participant in participants:
        userID, firstName, lastName = participant
        while True:
            race_time_str = input(f"Enter {firstName} {lastName}'s race time (mm:ss.msms): ")
            if re.match(r'^\d{2}:\d{2}\.\d{2}$', race_time_str):
                break
            else:
                print("Invalid format. Please use the format mm:ss.ll (e.g. 01:23.45).")
        raceTime = int(race_time_str[:2]) * 60 + int(race_time_str[3:5]) + float(race_time_str[6:]) / 100
        cursor.execute("UPDATE StudentRaces SET TimeTaken = %s WHERE StudentID = (SELECT StudentID FROM StudentData WHERE UserID = %s) AND RaceID = %s", (raceTime, userID, raceID))
        conn.commit()
    updateHousePoints(raceID, 1)

def updateHousePoints(raceID, editTF):
    cursor.execute(f"SELECT CurrentRecord FROM RaceInformation WHERE RaceID = {raceID}")
    current_record = cursor.fetchone()[0]

    cursor.execute("SELECT StudentRaces.StudentID, StudentData.StudentHouse, StudentRaces.TimeTaken "
                   f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID "
                   f"WHERE StudentRaces.RaceID = {raceID}")
    results = cursor.fetchall()

    sorted_results = sorted(results, key=lambda x: x[2])

    print(sorted_results)

    for i in range(len(sorted_results)):
        student_id, house, time_taken = sorted_results[i]
        if i == 0:
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 20 WHERE HouseName = '{house}'")
        elif i == 1:
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 15 WHERE HouseName = '{house}'")
        elif i == 2:
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 10 WHERE HouseName = '{house}'")
        elif time_taken != '00:00.00':
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 3 WHERE HouseName = '{house}'")

    best_time = sorted_results[0][2]
    if best_time < current_record:
        cursor.execute(f"UPDATE RaceInformation SET CurrentRecord = '{best_time}' WHERE RaceID = {raceID}")
        cursor.execute(f"SELECT StudentName FROM StudentData WHERE StudentID = '{sorted_results[0]}")
        print(f"'{cursor.fetchall()}' has set a new record!")


    conn.commit()
    if editTF != 0:
        createFinal(raceID)

def createFinal(raceID):
    cursor.execute(f"SELECT FinalOrSemi FROM RaceInformation WHERE RaceID = '{raceID}'")
    for row in cursor.fetchall():
        if row[0] == 0:
            print("A finals race will now be created.")
        else:
            print("This race has now concluded. You will now be redirected to the login page.")
            login()
            return
    cursor.execute("SELECT StudentRaces.StudentID, StudentRaces.TimeTaken "
                   f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID "
                   f"WHERE StudentRaces.RaceID = {raceID}")
    results = cursor.fetchall()

    sorted_results = sorted(results, key=lambda x: x[1])

    print(sorted_results)
    maxEight = 0
    finals = raceID + 1
    finals = str(finals)
    for i in range(len(sorted_results)):
        if maxEight == 8:
            break
        maxEight += 1
        student_id, time_taken = sorted_results[i]
        cursor.execute(f"INSERT INTO StudentRaces (StudentID, RaceID) VALUES ({student_id}, {finals})")
        conn.commit()

def showInfo(currentUser):
    sql = "SELECT UserRole FROM UserInformation WHERE UserID = '"+str(currentUser)+"'"
    cursor.execute(sql)
    conn.commit()
    for row in cursor.fetchall():
        if row[0] == "Teacher":
            teacherPage()
        else:
            studentPage(currentUser)

def studentPage(studentUserID):
    print("You are logged in as a student.")
    sql = "SELECT FirstName FROM StudentData, UserInformation WHERE StudentData.UserID = '"+str(studentUserID)+"' AND UserInformation.UserID = StudentData.UserID"
    cursor.execute(sql)
    conn.commit()
    for row in cursor.fetchall():
        print("Hello "+str(row[0]))
        showScore()

def teacherPage():
    print("You are logged in as a teacher.")
    showScore()
    while True:
        teacherInput = input("Do you want to input new race times or edit existing ones? Type nothing to be logged out.")
        print("1) Input New"
              "2) Edit Existing")
        if teacherInput == "1":
            raceDataEntry()
        elif teacherInput == "2":
            editTimes()
        elif teacherInput == "":
            print("You will now be logged out.")
            break
        else:
            print("Invalid input")
    print("You will now be logged out.")

def editTimes():
    cursor.execute("SELECT DISTINCT RaceID FROM StudentRaces WHERE TimeTaken IS NOT NULL")
    documentedRaces = [row[0] for row in cursor.fetchall()]

    print(documentedRaces)

    print("These are the races that have been documented:")
    count = 0
    for i in documentedRaces:
        cursor.execute(f"SELECT RaceName, RaceID FROM RaceInformation WHERE RaceID = '{i}'")
        for row in cursor.fetchall():
            count += 1
            print(str(count) + ") " + str(row[0]))
            print(f"Race id: {row[1]}")


    while True:
        try:
            userChoice = int(input("Which race do you want to change data for? Please input the race id."))
            if userChoice in documentedRaces:
                break
            else:
                print("Invalid Answer")
        except:
            print("Invalid Answer")
    newTimes(userChoice)

def newTimes(raceID):
    cursor.execute("SELECT StudentData.FirstName, StudentData.LastName, StudentData.StudentHouse, StudentRaces.TimeTaken, StudentRaces.StudentID "
                   f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID "
                   f"WHERE StudentRaces.RaceID = {raceID}")
    studentIDs = [row[4] for row in cursor.fetchall()]
    print("These are the times the students got:")
    counter = 0
    for row in cursor.fetchall():
        counter += 1
        print(str(counter) +f") {row[0]} {row[1]}: {row[3]}. Student ID = {row[4]}")

    while True:
        try:
            userChoice = int(input("Please select the ID of the student."))
            if userChoice not in studentIDs:
                print("Invalid Input")
            else:
                break
        except:
            print("Invalid Input")

    cursor.execute("SELECT StudentRaces.StudentID, StudentData.StudentHouse, StudentRaces.TimeTaken "
                   f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID "
                   f"WHERE StudentRaces.RaceID = {raceID}")
    results = cursor.fetchall()

    sorted_results = sorted(results, key=lambda x: x[2])

    print(sorted_results)

    for i in range(len(sorted_results)):
        student_id, house, time_taken = sorted_results[i]
        if i == 0:
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 20 WHERE HouseName = '{house}'")
        elif i == 1:
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 15 WHERE HouseName = '{house}'")
        elif i == 2:
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 10 WHERE HouseName = '{house}'")
        elif time_taken != '00:00.00':
            cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 3 WHERE HouseName = '{house}'")
    while True:
        race_time_str = input(f"Enter the student's new race time (mm:ss.msms): ")
        if re.match(r'^\d{2}:\d{2}\.\d{2}$', race_time_str):
            break
        else:
            print("Invalid format. Please use the format mm:ss.ll (e.g. 01:23.45).")
    raceTime = int(race_time_str[:2]) * 60 + int(race_time_str[3:5]) + float(race_time_str[6:]) / 100
    cursor.execute(f"UPDATE StudentRaces SET TimeTaken = {raceTime} WHERE StudentID = '{userChoice}'")
    conn.commit()

    updateHousePoints(raceID, 0)


def showScore():
    print("Current Standings:")
    sql = "SELECT HouseName, HousePoints From EventData"
    cursor.execute(sql)
    conn.commit()
    for row in cursor.fetchall():
        print(row)