def get_round(r):
db = DbFunctions()
# get player info
sql = f"SELECT ID,Player,Position,Club,Nation,Age FROM all_players WHERE ROUND={r}"
player_list = db.db_control(sql, fetch="all")
for elem in player_list:
# club name shouldn't have spaces because it is used to access logo file
elem['Club'] = elem['Club'].replace(" ", "_")
return render_template('rounds.html', r=int(r), result=player_list)
def profile(name):
db = DbFunctions()
name = GenFunctions().full_name(name)
print(name)
stats = db.get_profile(name)
return render_template("player_profile.html", name=name, stats=stats)
def suggest(msg):
db = DbFunctions()
try:
sug = msg['sug']
id = msg['field']
suggest_sql = f"SELECT player,club FROM all_players " \
f"WHERE player LIKE '%{sug}%';"
suggest_result = db.db_control(suggest_sql, fetch="all")
print("emitted")
emit('sug_result', {'result': suggest_result, 'ID': id})
except:
pass
def home_search(msg):
db = DbFunctions()
print('received', msg)
received = msg['data']
# find target player
sql = f"SELECT * FROM all_players WHERE Player LIKE '%{received}%'"
search_result = db.db_control(sql, fetch="all")
search_result = json.dumps(search_result)
emit('home_result', {'data': search_result})
print(search_result)
def trade(msg):
db = DbFunctions()
# get received variables
print('received:', msg)
requester = msg['req']
trade_out = msg['out']
trade_in = msg['tin']
# check the receiver
receiver = ""
if requester == "Ben":
receiver = "Darrell"
else:
receiver = "Ben"
# alter db and add record to transaction table
update1 = "UPDATE all_players SET Owner='%s' WHERE Player='%s'" % (receiver, trade_out)
db.db_control(update1, close=False)
update2 = "UPDATE all_players SET Owner='%s' WHERE Player='%s'" % (requester, trade_in)
db.db_control(update2, close=False)
record_table = f"INSERT INTO trans_records(Player1,Player2,Type) VALUES('{trade_out}','{trade_in}','Trade')"
db.db_control(record_table, commit=True)
def home_search(msg):
db = DbFunctions()
print('received', msg)
received = msg['data']
# find target player
sql = f"SELECT * FROM all_players WHERE Player LIKE '%{received}%'"
search_result = db.db_control(sql, fetch="all")
search_result.insert(0, {'pos': msg['pos'][3:]})
search_result = json.dumps(search_result)
# send message of pos and player name to frontend
emit('builder_result', {'data': search_result})
print(search_result)
def pick(msg):
db = DbFunctions()
# get received variables
print("received:", msg)
drop = msg['drop']
add = msg['add']
position = msg['pos']
nation = msg['nat']
# drop player
drop_sql = f"SELECT ID,Position,Club,Nation FROM all_players WHERE Player='{drop}'"
dropped_player = db.db_control(drop_sql, fetch="one", close=False)
dropped_id = dropped_player['ID']
add_sql = f"UPDATE all_players SET Player='{add}',Position='{position}',Nation='{nation}' WHERE ID={dropped_id}"
print(add_sql)
db.db_control(drop_sql, close=False)
# add dropped player to db to keep his data
add_to_dropped = f"INSERT INTO dropped_players(Name,Club,Nation,Position)" \
f" VALUES('{drop}','{dropped_player['Club']}'," \
f"'{dropped_player['Nation']}','{dropped_player['Position']}')"
db.db_control(add_to_dropped, close=False)
date = time.localtime()
day = GenFunctions().modify_date(date)
transfer_order_sql = "SELECT MAX(Number) FROM trans_records" \
"WHERE Type='Add'"
transfer_order = db.db_control(transfer_order_sql, fetch="one", close=False)['MAX(Number)']
add_to_record = f"INSERT INTO trans_records " \
f"(Number,Player1,Player2,Type,Time) " \
f"VALUES('{str(int(transfer_order) + 1)}'," \
f"'{drop}','{add}','Add',{day})"
db.db_control(add_to_record, commit=True)
print(add_to_record)
def transactions_records():
db = DbFunctions()
# get all trade records
sql_trade = "SELECT Time,Number,Player1,Player2 FROM trans_records WHERE type='Trade'"
trades = db.db_control(sql_trade, fetch="all")
print(trades)
# trade list format change (player 1, player 2)
i = 0
for note in trades:
sql1 = f"SELECT Position, Club, Nation FROM all_players WHERE Player='{note['Player1']}'"
one = db.db_control(sql1, fetch="one")
print(one)
trades[i].update({"Player1": {"Name": trades[i]["Player1"], "Pos": one["Position"], "Club": one["Club"],
"Nation": one["Nation"]}})
sql2 = f"SELECT Position, Club, Nation FROM all_players WHERE Player='{note['Player2']}'"
two = db.db_control(sql2, fetch="one")
trades[i].update({"Player2": {"Name": trades[i]["Player2"], "Pos": two["Position"], "Club": two["Club"],
"Nation": two["Nation"]}})
trades[i].update({"Date": trades[i]["Time"]})
i += 1
# get all drop and add records
sql_dna = "SELECT Time,Player1,Player2 FROM trans_records WHERE type='Add'"
dnas = db.db_control(sql_dna, fetch="all")
i = 0
# alter list format
for note in dnas:
sql1 = f"SELECT Position, Club, Nation FROM dropped_players WHERE Name='{note['Player1']}'"
one = db.db_control(sql1, fetch="one", close=False)
dnas[i].update({"Player1": {"Name": dnas[i]["Player1"], "Pos": one["Position"], "Club": one["Club"],
"Nation": one["Nation"]}})
sql2 = f"SELECT Position, Club, Nation FROM all_players WHERE Player='{note['Player2']}'"
two = db.db_control(sql2, fetch="one")
dnas[i].update({"Player2": {"Name": dnas[i]["Player2"], "Pos": two["Position"], "Club": two["Club"],
"Nation": two["Nation"]}})
dnas[i].update({"Date": dnas[i]["Time"]})
i += 1
print(dnas)
return render_template('trans_record.html', trades=trades, dropnadd=dnas)
def national(nteam):
db = DbFunctions()
national_team = []
nation_dict = dict()
# read countries #TODO: from db?
with open('Official.csv', newline="") as file:
rows = csv.reader(file)
for elem in rows:
national_team.append(elem)
countries = national_team[1:13]
# get country list from csv
for team in countries:
while "" in team:
team.remove("")
nation_dict[team[0]] = team[1:]
sql_str = ""
# create get sql for searching available players
for member in nation_dict[nteam]:
sql_str += f'Nation = "{member}" OR '
sql_str = sql_str[:-4]
# classify positions
position = [['GK'], ['LB', 'CB', 'RB'], ['CAM', 'CM', 'CDM', 'LM', 'RM'], ['LW', 'RW', 'ST', 'CF']]
groups = ["Goalkeepers", "Defenders", "Midfielders", "Attackers"]
results = []
for poses in position:
sql_pos = ""
for pos in poses:
sql_pos += f'Position = "{pos}" OR '
sql_pos = sql_pos[:-4] # remove the last 'OR'
sql_find_player = f"SELECT * FROM all_players WHERE Owner = 'Darrell' AND ({sql_str}) AND ({sql_pos})"
list_of_one_cat = db.db_control(sql_find_player)
results.append(list_of_one_cat)
return render_template("national.html", results=results, groups=groups, nteam=nteam)
def add_add_squad(msg):
db = DbFunctions()
received = msg['players']
try:
received = tuple(received)
sql = f'''INSERT INTO squads (NAME,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18)
VALUES {received}'''
db.db_control(sql, commit=True)
except pymysql.err.ProgrammingError:
for i in range(len(received)):
if received[i] == 'd Outline':
received[i] = 0
received = tuple(received)
sql = f'''INSERT INTO squads (NAME,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18)
VALUES {received}'''
db.db_control(sql, commit=True)
def index():
db = DbFunctions()
player_list = db.db_control("SELECT * FROM all_players", fetch="all")
return render_template("players.html",
all_players=player_list)
def nat_team_del(msg):
db = DbFunctions()
db.del_from_nation(msg['player'])
print(msg['player'])
emit("nat_added")
def nat_team_add(msg):
db = DbFunctions()
db.add_to_nation(msg['nation'], msg['player'])
print(msg['player'], msg['nation'])