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)