def build(self):
result = (
self.select_clause(mix=False)
+ self.from_clause(mix=False)
+ self.where_clause()
+ dba.sql_dates_condition(self.min_date, self.max_date)
)
if self.with_mix:
result += (
"\nUNION \n"
+ self.select_clause(mix=True)
+ self.from_clause(mix=True)
+ self.where_clause()
+ dba.sql_dates_condition(self.min_date, self.max_date)
)
result += self.orderby() + ";"
return result
def get_presence_results(
idents: Set[int],
sex: str,
min_date: datetime.date,
max_date: datetime.date,
with_retired=False,
) -> List[PlayerResult]:
"""[min_date, max_date) is semiclose range
:return list where date is latest for [min_date,...,max_date)
"""
present_pids = set()
present_results = []
date_now = datetime.date.today()
if not dba.initialized():
dba.open_connect()
sql = """select games.DATE_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%') """.format(sex)
sql += dba.sql_dates_condition(min_date, max_date, dator="games.DATE_G")
sql += " order by games.DATE_G desc;"
with closing(dba.get_connect().cursor()) as cursor:
for (match_dt, score_txt, fst_id, snd_id) in cursor.execute(sql):
match_date = match_dt.date() if match_dt else None
if not score_txt:
continue # may be it is scheduled match
date = match_date
if date is None:
log.error(
f"empty date in {sex} pid1{fst_id} pid2 {snd_id} score:{score_txt}"
)
continue
if with_retired and not sc.Score(score_txt).retired:
continue
if not with_retired and fst_id in idents and fst_id not in present_pids:
present_pids.add(fst_id)
present_results.append(
PlayerResult(id=fst_id, days_ago=(date_now - date).days))
if snd_id in idents and snd_id not in present_pids:
present_pids.add(snd_id)
present_results.append(
PlayerResult(id=snd_id, days_ago=(date_now - date).days))
return present_results
def _initialize_results_sex(sex,
max_rating,
max_rating_dif,
with_bo5=False,
min_date=None,
max_date=None):
tmp_recov_dct = defaultdict(list) # date -> list of (plr_id, set2win)
tmp_keep_dct = defaultdict(list) # date -> list of (plr_id, set2win)
sql = """select tours.DATE_T, games.DATE_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%')""".format(sex)
sql += dba.sql_dates_condition(min_date, max_date)
sql += " ;"
with closing(dba.get_connect().cursor()) as cursor:
for (tour_dt, match_dt, score_txt, fst_id,
snd_id) in cursor.execute(sql):
tdate = tour_dt.date() if tour_dt else None
mdate = match_dt.date() if match_dt else None
if not score_txt:
continue
scr = sc.Score(score_txt)
sets_count = scr.sets_count(full=True)
if sets_count < 2:
continue
date = mdate if mdate else tdate
if date is None:
continue
recov, keep = _get_match_data(sex, date, fst_id, snd_id, scr,
max_rating, max_rating_dif, with_bo5)
if recov is not None:
tmp_recov_dct[date].append((recov.player_id, recov.set2_win))
if keep is not None:
tmp_keep_dct[date].append((keep.player_id, keep.set2_win))
recov_dates = list(tmp_recov_dct.keys())
recov_dates.sort(reverse=True)
for date in recov_dates:
recovery_dict[sex][date] = tmp_recov_dct[date]
keep_dates = list(tmp_keep_dct.keys())
keep_dates.sort(reverse=True)
for date in keep_dates:
keep_dict[sex][date] = tmp_keep_dct[date]
def _initialize_results_sex(sex, min_date=None, max_date=None):
tmp_dct = defaultdict(list) # date -> list of match_results
sql = """select tours.DATE_T, games.DATE_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%')""".format(sex)
sql += dba.sql_dates_condition(min_date, max_date)
sql += " ;"
with closing(dba.get_connect().cursor()) as cursor:
for (tour_dt, match_dt, score_txt, fst_id,
snd_id) in cursor.execute(sql):
tdate = tour_dt.date() if tour_dt else None
mdate = match_dt.date() if match_dt else None
if not score_txt:
continue
scr = sc.Score(score_txt)
if scr.retired:
continue
sets_cnt = scr.sets_count(full=True)
if sets_cnt not in (3, 5):
continue
if sets_cnt == 3:
sets_sc = scr.sets_score()
if sets_sc != (2, 1):
continue
indec_sc = scr[sets_cnt - 1]
games_dif = indec_sc[0] - indec_sc[1]
if games_dif <= 0:
log.error(
"strange decided score {} in {} 1id {} 2id {}".format(
scr, sex, fst_id, snd_id))
continue
date = mdate if mdate else tdate
tmp_dct[date].append(MatchResult(fst_id, snd_id, games_dif))
dates = list(tmp_dct.keys())
dates.sort()
for date in dates:
results_dict[sex][date] = tmp_dct[date]
def _read_match_score(sex: str, pid1: int, pid2: int, rec_date: date):
from score import Score
min_date = rec_date - timedelta(days=1)
max_date = rec_date + timedelta(days=2)
sql = """select games.ID1_G, games.ID2_G, games.RESULT_G
from games_{0} AS games
where games.RESULT_G IS NOT NULL
and games.DATE_G IS NOT NULL
and (
(games.ID1_G = {1} and games.ID2_G = {2}) or
(games.ID1_G = {2} and games.ID2_G = {1})
)
""".format(sex, pid1, pid2)
sql += dba.sql_dates_condition(min_date, max_date, dator='games.DATE_G')
sql += ";"
with closing(dba.get_connect().cursor()) as cursor:
cursor.execute(sql)
row = cursor.fetchone()
if row:
winner_id = row[0]
loser_id = row[1]
res_score = Score(text=row[2])
return winner_id, loser_id, res_score
def _initialize_results_sex(sex,
max_rating,
max_rating_dif,
min_date=None,
max_date=None):
sql = """select tours.DATE_T, tours.NAME_T, tours.RANK_T, tours.PRIZE_T,
games.ID_R_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%') """.format(sex)
sql += dba.sql_dates_condition(min_date, max_date)
sql += " order by tours.DATE_T;"
with closing(dba.get_connect().cursor()) as cursor:
for (
tour_dt,
tour_name,
db_rank,
db_money,
rnd_id,
score_txt,
fst_id,
snd_id,
) in cursor.execute(sql):
date = tour_dt.date() if tour_dt else None
if date is None:
raise co.TennisScoreError("none date {}".format(tour_name))
if not score_txt:
continue
scr = sc.Score(score_txt)
if scr.retired:
continue
sets_count = scr.sets_count(full=True)
if sets_count != 3 or scr.best_of_five():
continue
set3_score = scr[2]
if set3_score[0] < set3_score[1]:
raise co.TennisScoreError(
"right winner unexpected {}".format(scr))
money = oncourt_db.get_money(db_money)
rank = None if db_rank is None else int(db_rank)
if rank is None:
log.error("none rank date: {} scr: {} name: {}".format(
date, scr, tour_name))
if not isinstance(rank, int):
raise co.TennisError(
"not int rank '{}' date: {} scr: {} name: {}".format(
rank, date, scr, tour_name))
rawname, level = oncourt_db.get_name_level(sex, tour_name.strip(),
rank, money, date)
if level in DISABLE_LEVELS:
continue
if level is None:
raise co.TennisError(
"none level date: {} scr: {} name: {}".format(
date, scr, tour_name))
rnd = tennis.Round.from_oncourt_id(rnd_id)
soft_level = tennis.soft_level(level, rnd)
if soft_level is None:
raise co.TennisError(
"none soft_level date: {} scr: {} name: {}".format(
date, scr, tour_name))
mdata = _get_match_data(sex, date, fst_id, snd_id, scr, max_rating,
max_rating_dif)
if mdata is not None:
past_monday = tt.past_monday_date(date)
ywn = tt.get_year_weeknum(past_monday)
data_dict[(sex, soft_level)][ywn][(mdata.set1_score,
mdata.set2_score)].hit(
mdata.decided_win)