def add(self, team, username):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query = "INSERT INTO team(team_name, info) values(?, ?)"
cursor.execute(query, (
team.team_name,
team.team_info,
))
teamid = cursor.lastrowid
query2 = "SELECT id FROM sport WHERE sportname = ?"
cursor.execute(query2, (team.sport_name, ))
sportid = cursor.fetchone()[0]
query3 = "INSERT INTO team_sport(team_id, sport_id) values(?, ?)"
cursor.execute(query3, (
teamid,
sportid,
))
team_obj = self.get(teamid)
cursor.execute("SELECT id FROM user WHERE username = ?", (username, ))
userId = cursor.fetchone()[0]
cursor.execute("insert into manages(user_id, team_id) values (?,?)", (
userId,
team_obj[0].team_id,
))
self.conn.commit()
cursor.close()
self.conn.close()
return team_obj
def edit(self, manager):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query = "UPDATE user SET email = %s, full_name = %s;"
cursor.execute(query, (manager.email, manager.full_name))
self.conn.commit()
cursor.close()
self.conn.close()
return
def addDrawToRecord(self, recordid):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query = "UPDATE team_records SET draw = draw + 1 WHERE id = ?"
cursor.execute(query, (recordid, ))
self.conn.commit()
cursor.close()
self.conn.close()
return
def delete(self, statid):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
result = self.get(statid)
query = "DELETE FROM soccer_team_statistics WHERE id = ?"
cursor.execute(query, (statid, ))
self.conn.commit()
cursor.close()
self.conn.close()
return result
def delete(self, user_id):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
result = self.get(user_id)
query = "DELETE FROM user WHERE id = ?"
cursor.execute(query, (user_id,))
self.conn.commit()
cursor.close()
self.conn.close()
return result
def delete(self, recordid):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
result = self.get(recordid)
query = "DELETE FROM team_records WHERE id = ?"
cursor.execute(query, (recordid, ))
self.conn.commit()
cursor.close()
self.conn.close()
return result
def delete(self, tid):
self.conn = utils.connectDB()
team_obj = self.get(tid)
cursor = self.conn.cursor()
query1 = "DELETE FROM team_sport WHERE team_id = ?"
cursor.execute(query1, (tid, ))
query3 = "DELETE FROM manages WHERE id = (SELECT id FROM manages WHERE team_id = ?)"
cursor.execute(query3, (tid, ))
query3 = "DELETE FROM team WHERE id = ?"
cursor.execute(query3, (tid, ))
self.conn.commit()
cursor.close()
self.conn.close()
return team_obj
def edit(self, TeamRecords):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query1 = "UPDATE team_records SET win = ?, loss = ?, draw = ?, year = ? WHERE id = ?"
cursor.execute(query1, (
TeamRecords.wins,
TeamRecords.loss,
TeamRecords.draw,
TeamRecords.year,
TeamRecords.records_id,
))
records_obj = self.get(TeamRecords.records_id)
self.conn.commit()
cursor.close()
self.conn.close()
return records_obj
def add(self, teamRecords):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query = "SELECT id FROM team_sport WHERE team_id = ?"
cursor.execute(query, (teamRecords.team_id, ))
team_sportid = cursor.fetchone()[0]
query1 = "INSERT INTO team_records(teamSportID, win, loss, draw, year) VALUES(?, ?, ?, ?, ?)"
cursor.execute(query1, (
team_sportid,
teamRecords.wins,
teamRecords.loss,
teamRecords.draw,
teamRecords.year,
))
record_id = cursor.lastrowid
records_obj = self.get(record_id)
self.conn.commit()
cursor.close()
self.conn.close()
return records_obj
def edit(self, soccerTeam):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query = "UPDATE soccer_team_statistics SET goals_for = ?, goals_allowed = ?, shots = ?, shots_on_goal = ?, saves = ?, passes = ?, possession = ?, fouls = ?, date = ? WHERE id = ?"
cursor.execute(query, (
soccerTeam.goals_for,
soccerTeam.goals_allowed,
soccerTeam.shots,
soccerTeam.shots_on_goal,
soccerTeam.saves,
soccerTeam.passes,
soccerTeam.possession,
soccerTeam.fouls,
soccerTeam.date,
soccerTeam.statid,
))
result = self.get(soccerTeam.statid)
self.conn.commit()
cursor.close()
self.conn.close()
return result
def add(self, soccerTeam, int):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query = "SELECT id FROM team_sport WHERE team_id = ?"
cursor.execute(query, (soccerTeam.team_id, ))
team_sportid = cursor.fetchone()[0]
query1 = "INSERT INTO soccer_team_statistics(team_sport_id, goals_for, goals_allowed, shots, shots_on_goal, saves, passes, possession, fouls, date) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
cursor.execute(query1, (
team_sportid,
soccerTeam.goals_for,
soccerTeam.goals_allowed,
soccerTeam.shots,
soccerTeam.shots_on_goal,
soccerTeam.saves,
soccerTeam.passes,
soccerTeam.possession,
soccerTeam.fouls,
soccerTeam.date,
))
statid = cursor.lastrowid
stat_obj = self.get(statid)
self.conn.commit()
date = datetime.strptime(soccerTeam.date, "%Y-%m-%d")
year = date.year
if RecordsDAO().getByTeamIDAndYear(soccerTeam.team_id, year):
pass
else:
RecordsDAO().add(TeamRecords(0, soccerTeam.team_id, 0, 0, 0, year))
recordid = RecordsDAO().getByTeamIDAndYear(soccerTeam.team_id, year)
if int == 0:
RecordsDAO().addWinToRecord(recordid)
elif int == 1:
RecordsDAO().addLossToRecord(recordid)
elif int == 2:
RecordsDAO().addDrawToRecord(recordid)
cursor.close()
self.conn.close()
return stat_obj
def edit(self, team):
self.conn = utils.connectDB()
cursor = self.conn.cursor()
query = "UPDATE team SET team_name = ?, info = ? WHERE id = ?"
cursor.execute(query, (
team.team_name,
team.team_info,
team.team_id,
))
query2 = "SELECT id FROM sport WHERE sportname = ?"
cursor.execute(query2, (team.sport_name, ))
sportid = cursor.fetchone()[0]
query3 = "UPDATE team_sport SET team_id = ?, sport_id = ? WHERE team_id = ?"
cursor.execute(query3, (
team.team_id,
sportid,
team.team_id,
))
self.conn.commit()
team_obj = self.get(team.team_id)
cursor.close()
self.conn.close()
return team_obj
def __init__(self):
self.conn = utils.connectDB()