# - Wetterturnier specific modules from pywetterturnier import utils, database # - Evaluating input arguments inputs = utils.inputcheck('ComputePoints') # - Read configuration file config = utils.readconfig('config.conf',inputs) # - Initializing class and open database connection db = database.database(config) # - Loading tdate (day since 1970-01-01) for the tournament. # Normaly Friday-Tornament (tdate is then Friday) while # the bet-dates are for Saturday and Sunday. if config['input_tdate'] == None: tdates = [db.current_tournament()] print ' * Current tournament is %s' % utils.tdate2string( tdates[0] ) else: tdates = [config['input_tdate']] # - If input_user was given as string we have to find the # corresponding userID first! if type(config['input_user']) == type(str()): config['input_user'] = db.get_user_id( config['input_user'] ) if not config['input_user']: utils.exit('SORRY could not convert your input -u/--user to corresponding userID. Check name.') # - Loading all parameters params = db.get_parameter_names(False) # ----------------------------------------------------------------
# Final variable. If set to False we would not allow to merge. # If 'True' merging would be allowed. merging_allowed = True cur = db.cursor() for tdate in tdates: # Initialize counts = [None] * len(users) for u in range(0, len(users)): cur.execute(sql % (userids[u], tdate)) counts[u] = cur.fetchone()[0] # If no bets for this tournament at all: skip if np.max(counts) == 0: continue # Show frontend print " %10s %6d " % (utils.tdate2string(tdate), tdate), for u in range(0, len(users)): print " %10d " % counts[u], if len(np.where(np.array(counts) > 0)[0]) == 1: print " merge ok" else: merging_allowed = False print " [ERROR] MERGING NOT ALLOWED (OVERLAP)" if not merging_allowed: print "\n\n !!!! MERGING NOT ALLOWED !!!!!\n\n" db.close() sys.exit(9) # ---------------------------------------------------------------- # Else merge user
# - Wetterturnier specific modules from pywetterturnier import utils, database # - Evaluating input arguments inputs = utils.inputcheck('ComputePoints') # - Read configuration file config = utils.readconfig('config.conf', inputs) # - Initializing class and open database connection db = database.database(config) # - Loading tdate (day since 1970-01-01) for the tournament. # Normaly Friday-Tornament (tdate is then Friday) while # the bet-dates are for Saturday and Sunday. if config['input_tdate'] == None: tdates = [db.current_tournament()] print(' * Current tournament is %s' % utils.tdate2string(tdates[0])) else: tdates = [config['input_tdate']] # - If input_user was given as string we have to find the # corresponding userID first! if type(config['input_user']) == type(str()): config['input_user'] = db.get_user_id(config['input_user']) if not config['input_user']: utils.exit( 'SORRY could not convert your input -u/--user to corresponding userID. Check name.' ) # - Loading all parameters params = db.get_parameter_names(False)
inputs = utils.inputcheck('PlotStats') # - Read configuration file config = utils.readconfig('config.conf',inputs) # - Initializing class and open database connection db = database.database(config) # ---------------------------------------------------------------- # - Loading all different cities (active cities) cities = db.get_cities() #TODO: only accepted input should be city! # - If input city set, then drop all other cities. if not config['input_city'] == None: tmp = [] for i in cities: if i['name'] == config['input_city']: tmp.append( i ) cities = tmp if config['input_tdate'] == None: tdate = db.current_tournament() print(' * Current tournament is %s' % utils.tdate2string( tdate )) else: tdate = config['input_tdate'] # - Calling the function now plot(db, cities, tdate) db.commit() db.close()
def print_moses(db, config, cities, tdates): path = "moses/input/" #fixed strings and headers: file_head = "Berliner Wetterprognoseturnier %s\n\nEingetroffene Werte und abgegebene Prognosen:\n" table_head = "Name N Sd dd ff fx Wv Wn PPP TTm TTn TTd RR" day_heads = ["Samstag:", "Sonntag:"] ranking_str = "Wertung der Prognose vom %s:\n" ranking_head = "Pl. Name Punkte Tag1 Tag2\n_________________________________________________" info_str = "\nDie durchschnittliche Punktzahl beträgt: %5.1f Punkte.\nWertung für nicht teilnehmende Mitspieler: %5.1f Punkte." season_str = "Die aktuelle Jahreszeitenwertung (%s):\n(basierend auf den Prognosen vom %s bis zum %s)" season_head = "Platz Name Punkte\n___________________________________________________" weeks_str = "Das aktuelle Gesamtranking:\n(basierend auf den Prognosen vom %s bis zum %s)" weeks_head = "Platz Name Punkte Durchschnitt Teiln.\n_____________________________________________________________________________" footer = "_____________________________________\nBerliner Wetterprognoseturnier\nhttp://www.wetterturnier.de/\[email protected]" params = db.get_parameter_names(sort=True) printf = lambda *args: print(*args, file=f) def print_rows(args, file): row_format = "{name:<25.25s} {n:>1} {sd:>3} {dd:>3} {ff:>2} {fx:>2} {wv:>2} {wn:>2} {ppp:>6.6} {tn:>5.5} {tx:>5.5} {td:>5.5} {rr:>5.5}" for i in range(1, 8): if type(args[i]) != str: args[i] = int(args[i]) print(row_format.format(name=args[0], n=args[1], sd=args[2], dd=args[3], ff=args[4], fx=args[5], wv=args[6], wn=args[7], ppp=args[8], tn=args[9], tx=args[10], td=args[11], rr=args[12]), file=f) for city in cities: cityID = city['ID'] if config['input_alldates']: tdates = db.all_tournament_dates(city['ID']) current = db.current_tournament() if current in tdates: tdates.remove(current) tdates = [i for i in tdates if i > 12027] for tdate in tdates: missing_bets = db.find_missing_bets(cityID, tdate) missing_obs = db.find_missing_obs(cityID, tdate) #if *missing_bets returns False, it's a bool, missing obs returns either True or False if type(missing_bets) != bool or missing_obs: print("To many missing obs or parameters!") continue stations = db.get_stations_for_city(cityID, active=False, tdate=tdate) #print output to file, first get prober filename filename = path + utils.tdate2string( tdate, moses=True) + "." + city['name'].lower()[0] + "pw" f = open(filename, 'w') tdate_str = utils.tdate2string(tdate) printf(file_head % tdate_str) users = db.get_participants_in_city(cityID, tdate, sort=True, what="user_login") for day in range(1, 3): printf(day_heads[day - 1]) printf(table_head) printf(78 * "_") for station in stations: obs = [station.name] for param in params: paramID = db.get_parameter_id(param) value = db.get_obs_data(cityID, paramID, tdate, day, station.wmo) if value is False or value is None: obs.append("n") else: obs.append(float(value) / 10) print_rows(obs, f) #printf(78*"_") printf("") for userID in users: bet = [ db.get_username_by_id(userID, which="user_login").replace( "GRP_", "") ] for param in params: paramID = db.get_parameter_id(param) value = db.get_bet_data("user", userID, cityID, paramID, tdate, day) if value is False or value is None: bet.append("n") else: bet.append(float(value) / 10) print_rows(bet, f) if day == 1: printf("\f") else: printf("\n") #weekend ranking printf(ranking_str % tdate_str) printf(ranking_head) sleepyID = db.get_user_id("Sleepy") sql = "SELECT bs.rank, wu.user_login, bs.points, bs.points_d1, bs.points_d2, REPLACE(wu.user_login, 'GRP_', '') " sql += "FROM wp_wetterturnier_betstat bs JOIN wp_users wu ON userID = wu.ID WHERE tdate=%d AND cityID=%d AND userID != %d ORDER BY bs.rank" cur = db.cursor() cur.execute(sql % (tdate, cityID, sleepyID)) data = cur.fetchall() for i in data: printf("{:2d}. {:<25.25s} {:5.1f} ({:5.1f}/{:5.1f})".format( i[0], i[1].replace("GRP_", ""), i[2], i[3], i[4])) sql = "SELECT mean FROM wp_wetterturnier_tdatestats WHERE tdate = %d AND cityID = %d" cur.execute(sql % (tdate, cityID)) mean = cur.fetchone()[0] do, fr = db.get_user_id("Donnerstag"), db.get_user_id("Freitag") sleepy = db.get_sleepy_points(cityID, tdate, [do, fr]) printf(info_str % (mean, sleepy)) printf("\n") #TODO: season ranking (fake?) #TODO: 15 weeks ranking (only parts?) printf(footer)
def print_moses(db, config, cities, tdates): path = "moses/input/" table_head = "Spielername N SD DD FF FX Wv Wn PPP TX TN TD RR" day_heads = [ " Samstag", " Sonntag" ] params = db.get_parameter_names(sort=True) def print_rows(args, file): row_format = "{name:<21.21s} {n:>5} {sd:>3} {dd:>4} {ff:>2} {fx:>2} {wv:>2} {wn:>1} {ppp:>7.7} {tn:>6.6} {tx:>5.5} {td:>5.5} {rr:>5.5}" for i in range(1, 8): if type(args[i]) != str: args[i] = int(args[i]) print(row_format.format(name=args[0], n=args[1], sd=args[2], dd=args[3], ff=args[4], fx=args[5], wv=args[6], wn=args[7], ppp=args[8], tn=args[9], tx=args[10], td=args[11], rr=args[12]), file=f) for city in cities: cityID = city['ID'] if config['input_alldates']: tdates = db.all_tournament_dates(city['ID']) current = db.current_tournament() if current in tdates: tdates.remove(current) tdates = [i for i in tdates if i > 12027] for tdate in tdates: missing_bets = db.find_missing_bets(cityID, tdate) missing_obs = db.find_missing_obs(cityID, tdate) #if *missing_bets returns False, it's a bool, missing obs returns either True or False if type(missing_bets) != bool or missing_obs: print("To many missing obs or parameters!") continue stations = db.get_stations_for_city(cityID, active=False, tdate=tdate) #print output to file, first get prober filename filename = path + utils.tdate2string( tdate, moses=True) + "." + city['name'].lower()[0] + "pw" f = open(filename, 'w') users = db.get_participants_in_city(cityID, tdate, sort=True, what="user_login") print(users) for day in range(1, 3): print(day_heads[day - 1], file=f) print("", file=f) for i in range(4): print("", file=f) print(table_head, file=f) print(80 * "-", file=f) for station in stations: obs = [station.name] for param in params: paramID = db.get_parameter_id(param) value = db.get_obs_data(cityID, paramID, tdate, day, station.wmo) if value is False or value is None: obs.append("n") else: obs.append(float(value) / 10) print_rows(obs, f) print(80 * "-", file=f) for userID in users: bet = [ db.get_username_by_id(userID, which="user_login").replace( "GRP_", "") ] for param in params: paramID = db.get_parameter_id(param) value = db.get_bet_data("user", userID, cityID, paramID, tdate, day) if value is False or value is None: bet.append("n") else: bet.append(float(value) / 10) print_rows(bet, f) if day == 1: print("\f", file=f) else: print("", file=f)
def compute_stats(self, cityID, measures, userID=False, tdate=False, day=0, last_tdate=None, referenz=True, mitteltips=True, aliases=None, pout=25, pmid=100, midyear=2010, span=None, dates=None, verbose=False): """ Computes all kinds of statistics e.g. the points for the eternal list plus min/max/median/mean of points for each user, city, tdate and so on. """ res = { } #results will be saved here with measures as keys, tdate as subkeys import numpy as np cur = self.db.cursor() day_strs = ["", "_d1", "_d2"] day_str = day_strs[0] if day != 0: day_str = day_strs[day] if last_tdate: last_tdate_str = " AND tdate<=" + str(last_tdate) else: last_tdate_str = "" sql = "SELECT points" + day_str + " FROM %swetterturnier_betstat WHERE " if tdate or (not tdate and not userID and cityID): # We don't want Sleepy in our tdatestats! #exclude = [self.get_user_id("Sleepy")] exclude = [] if not referenz: groupID = self.get_group_id("Referenztipps") for j in self.get_participants_in_group(groupID, cityID, tdate, playing=False): exclude.append(j) if not mitteltips: #include no groups sql2 = "SELECT ID FROM %susers WHERE user_login LIKE \"%s\"" cur.execute(sql2 % (self.prefix, "GRP_%")) data2 = cur.fetchall() for j in data2: exclude.append(int(j[0])) if tdate: #tdatestats #only include users who really played on tdate (no sleepy points!) #played = self.sql_tuple( self.get_participants_in_city( cityID, tdate ) ) sql += "cityID=%d AND tdate=%d AND userID NOT IN%s" + last_tdate_str cur.execute(sql % (self.prefix, cityID, tdate, self.sql_tuple(exclude))) elif cityID: #citystats sql2 = "SELECT part FROM %swetterturnier_tdatestats WHERE cityID=%d" + last_tdate_str cur.execute(sql2 % (self.prefix, cityID)) data = cur.fetchall() for i in measures: parts = [] for j in data: parts.append(int(j[0])) if i == "mean_part": res[i] = np.mean(parts) elif i == "max_part": res[i] = np.max(parts) elif i == "min_part": res[i] = np.min(parts) elif i == "tdates": res[i] = len(parts) sql += "cityID=%d AND userID NOT IN%s" + last_tdate_str cur.execute(sql % (self.prefix, cityID, self.sql_tuple(exclude))) elif userID: #userstats userIDs = [userID] #if we are using an alias dict we merge all aliases of a user with his/her other identities if aliases: username = self.get_username_by_id(userID) if username in aliases.keys() or username in sum( aliases.values(), []): if verbose: print(username) for j in aliases.keys(): if username == j: for k in aliases[j]: userIDs.append(self.get_user_id(k)) break else: if username in aliases[j]: userIDs.append(self.get_user_id(j)) for k in aliases[j]: userID = self.get_user_id(k) if userID not in userIDs: userIDs.append(userID) if verbose: print userIDs sql += "userID IN%s" if cityID != 0: sql += " AND cityID=%d" + last_tdate_str cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID)) else: sql += last_tdate_str cur.execute(sql % (self.prefix, self.sql_tuple(userIDs))) else: utils.exit("Wrong usage of compute_stats!") data = cur.fetchall() points = [] for i in data: #sleepy has NULL points on d1 and d2, skip him! if i[0] == None: continue else: points.append(float(i[0])) if not points: points.append(0) if cityID == 0: """ first calculate sd_ind for all tournaments played. Pretend as cities were one so if a user participated in multiple cities on the same day it counts like he played multiple tournaments & for each of these tournaments we look up the sd_upp and calculate a mean (sd_ind) """ sql = "SELECT cityID, tdate, points FROM %swetterturnier_betstat WHERE userID IN%s AND tdate <= %d" cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), last_tdate)) points = {} for j in cur.fetchall(): if j[0] not in points: points[j[0]] = {} points[j[0]][j[1]] = j[2] sd_upp = [] for cityID in points.keys(): for tdate in points[cityID].keys(): sd_upp.append( self.get_stats(cityID, measure="sd_upp", tdate=tdate)) if not sd_upp: return {"points_adj": 0} #remove None values sd_upp = [x for x in sd_upp if x] sd_ind = np.nanmean(sd_upp) res["sd_ind"] = sd_ind #now we get the individual points for each tournament played points_adj = [] parts = {} for cityID in points.keys(): parts[cityID] = 0 for cityID in points.keys(): for tdate in points[cityID].keys(): median = self.get_stats(cityID, measure="median", tdate=tdate) points_adj.append(points[cityID][tdate] - median) parts[cityID] += 1 #get mean participations for every city a user ever played parts_all = np.sum(parts.values()) #actually parts_mean, we will use this variable in calculation parts = parts_all / len(parts.values()) res["part"] = parts_all sql = "SELECT points FROM wp_wetterturnier_betstat WHERE userID IN%s" cur.execute(sql % self.sql_tuple(userIDs)) points_all = [j[0] for j in cur.fetchall() if j[0]] if points_all: res["max"] = np.max(points_all) res["mean"] = np.mean(points_all) #norm by sd_ind, scale by mean participations in all cities if parts < pout: f = 0 elif parts < pmid: f = np.sqrt(float(parts) / pmid) else: f = 1 #average points above median res["points_med"] = np.mean(points_adj) #final adjusted points res["points_adj"] = f * (res["points_med"] / sd_ind) * 1000 if np.isnan(res["points_adj"]): res["points_adj"] = 0 sql = """ SELECT rank, count(rank) AS count FROM %swetterturnier_betstat WHERE userID IN%s AND rank <= 3 %s GROUP BY rank ORDER BY rank """ cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), last_tdate_str)) ranks = {1: "0", 2: "0", 3: "0"} for j in cur.fetchall(): ranks[j[0]] = str(j[1]) res["ranks_weekend"] = ",".join(ranks.values()) return res elif cityID == 6: #weighted variant sum(p_adj_i * part_i) / sum(part_i) #which cities did the user play? would be better to call userstat but then we need to get rid of zero/null rows... sql = "SELECT cityID FROM wp_wetterturnier_betstat WHERE userID IN%s" cur.execute(sql % self.sql_tuple(userIDs)) cityIDs = [] for i in cur.fetchall(): if i[0] not in cityIDs: cityIDs.append(i[0]) #get points_adj from userstats points_adj, parts, sd_ind = ([] for _ in range(3)) for cityID in cityIDs: measures = self.get_stats( cityID=cityID, userID=userIDs[0], measures=["points_adj", "part", "sd_ind"]) if measures: points_adj.append(measures[0]) parts.append(measures[1]) sd_ind.append(measures[2]) if not points_adj or not parts or not sd_ind or None in points_adj: res["points_adj"] = 0 res["part"] = 0 res["sd_ind"] = 0 return res else: sql = "SELECT points FROM wp_wetterturnier_betstat WHERE userID IN%s" cur.execute(sql % self.sql_tuple(userIDs)) points_all = [j[0] for j in cur.fetchall() if j[0]] if points_all: res["max"] = np.max(points_all) res["mean"] = np.mean(points_all) sql = """ SELECT rank, count(rank) AS count FROM %swetterturnier_betstat WHERE userID IN%s AND rank <= 3 %s GROUP BY rank ORDER BY rank """ cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), last_tdate_str)) ranks = {1: "0", 2: "0", 3: "0"} for j in cur.fetchall(): ranks[j[0]] = str(j[1]) res["ranks_weekend"] = ",".join(ranks.values()) parts_all = np.sum(parts) res["part"] = parts_all res["points_adj"] = np.dot(points_adj, parts) / parts_all res["sd_ind"] = np.dot(sd_ind, parts) / parts_all return res for i in measures: i += day_str if i == "points" + day_str: res[i] = sum(points) elif "sd_ind" in i and "_d" not in i: #get tdates where the user participated sql = "SELECT tdate FROM %swetterturnier_betstat WHERE userID IN%s AND cityID=%d" if "1" in i or "2" in i or "X" in i: spanstr = i[-1] if midyear: middle_tdate = str( utils.string2tdate(str(midyear) + "-01-01")) #print "Middle of tournament (tdate): %s" % middle_tdate if "1" in i: sql += " AND tdate<=" + middle_tdate elif "2" in i: sql += " AND tdate>" + middle_tdate elif span: if verbose: print span sql += " AND tdate BETWEEN " + str( span[0]) + " AND " + str(span[1]) cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID)) tdates = [j[0] for j in cur.fetchall()] sql = "SELECT sd_upp from %swetterturnier_tdatestats WHERE cityID=%d AND tdate IN%s" cur.execute(sql % (self.prefix, cityID, self.sql_tuple(tdates))) #print sql % (self.prefix, cityID, self.sql_tuple(tdates) ) sd_upp = [j[0] for j in cur.fetchall()] res[i] = np.mean(sd_upp) #print(res[i]) if res[i] == None or np.isnan(res[i]): res[i] = 0 elif "points_adj" in i and "_d" not in i: if verbose: print self.get_username_by_id(userIDs[0]) print i, "\n" #skip Sleepy #if self.get_user_id("Sleepy") in userIDs: continue parts = len(points) if not parts: continue """ find all dates where the user actually played for each date calculate: (points-points) / (ymax-median*) sum up and divide by number of tdates * daily median and median fitted by PlotStats should be calculated earlier in ComputeStats with other citystats! """ tdates = {} sql = "SELECT tdate, points FROM %swetterturnier_betstat WHERE userID IN%s AND cityID=%d" if "1" in i or "2" in i or "X" in i: if midyear: middle_tdate = str( utils.string2tdate(str(midyear) + "-01-01")) if verbose: print "Middle of tournament (tdate): %s" % middle_tdate if "1" in i: sql += " AND tdate<=" + middle_tdate elif "2" in i: sql += " AND tdate>" + middle_tdate elif span: if verbose: print span sql += " AND tdate BETWEEN " + str( span[0]) + " AND " + str(span[1]) #print sql cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID)) data = cur.fetchall() for j in data: tdates[j[0]] = {} tdates[j[0]]["points"] = j[1] #get the actual median for each tdate sql = "SELECT tdate, median from %swetterturnier_tdatestats WHERE cityID=%d AND tdate IN%s" cur.execute(sql % (self.prefix, cityID, self.sql_tuple(tdates.keys()))) data = cur.fetchall() for j in data: tdates[j[0]]["median"] = j[1] if "1" in i: timestr = "1" elif "2" in i: timestr = "2" elif "X" in i: timestr = "X" else: timestr = "" sd_ind = self.get_stats(cityID, measure="sd_ind" + timestr, userID=userIDs[0]) if sd_ind in [0, None] or np.isnan(sd_ind): continue if verbose: print "tdate points median sd points_adj" points_adj = [] for t in sorted(tdates.keys()): if {"points", "median"} <= set(tdates[t]): perc = tdates[t]["points"] - tdates[t]["median"] if verbose: print utils.tdate2string(t), str( tdates[t]["points"]).ljust(6), str( tdates[t]["median"]).ljust(6), str( round(sd_ind, 2)).ljust(7), str(round(perc, 2)).ljust(10) else: continue points_adj.append(perc) if not points_adj: points_adj.append(0) if verbose: print "" #if someone has less than "pout" parts, just kick him out! if parts < pout: f = 0 #in between pout and pmid the points get adjusted by the sqrt() function elif parts < pmid: f = np.sqrt(float(parts) / pmid) else: #if parts >= pmid f = 1 #average points above median res["points_med"] = np.mean(points_adj) #final adjusted points res["points_adj"] = f * (res["points_med"] / sd_ind) * 1000 if np.isnan(res["points_adj"]): res["points_adj"] = 0 elif i == "mean" + day_str: res[i] = round(np.mean(points), 1) elif i == "median" + day_str: res[i] = np.median(points) elif i == "Qlow" + day_str: res[i] = np.percentile(points, 25, interpolation="midpoint") elif i == "Qupp" + day_str: res[i] = np.percentile(points, 75, interpolation="midpoint") elif i == "max" + day_str: res[i] = max(points) elif i == "min" + day_str: res[i] = min(points) elif i == "sd" + day_str: #standard deviation sd = np.std(points) if np.isnan(sd): res[i] = 0 else: res[i] = sd elif i == "part": #important for part count, otherwise could be 1 if a player/date actually has 0 part if len(points) == 1 and points[0] == 0: res[i] = 0 else: res[i] = len(points) elif i == "sd_upp" + day_str: median = self.get_stats(tdate=tdate, cityID=cityID, measure="median" + day_str) if not median: median = res["median" + day_str] if not median: continue sql = "SELECT points" + day_str + " from %swetterturnier_betstat WHERE tdate=%d AND cityID=%d AND points" + day_str + " > %f" #print sql % (self.prefix, tdate, cityID, median) cur.execute(sql % (self.prefix, tdate, cityID, median)) sd = np.mean([j[0] - median for j in cur.fetchall()]) if np.isnan(sd): res[i] = 0 else: res[i] = sd elif i == "ranks_weekend": sql = """ SELECT rank, count(rank) AS count FROM %swetterturnier_betstat WHERE cityID = %d AND userID IN%s AND rank <= 3 %s GROUP BY rank ORDER BY rank """ cur.execute( sql % (self.prefix, cityID, self.sql_tuple(userIDs), last_tdate_str)) ranks = {1: "0", 2: "0", 3: "0"} for j in cur.fetchall(): ranks[j[0]] = str(j[1]) res[i] = ",".join(ranks.values()) elif i == "ranks_season": pass else: continue if not res: utils.exit("No results!") return res
inputs = utils.inputcheck('ComputePersistenzen') # - Read configuration file config = utils.readconfig('config.conf', inputs) # - Initializing class and open database connection db = database.database(config) # - Loading tdate (day since 1970-01-01) for the tournament. # Normaly Friday-Tornament (tdate is then Friday) while # the bet-dates are for Saturday and Sunday if there was # no input tournament date -t/--tdate. if config['input_tdate'] == None: tdates = [db.current_tournament()] else: tdates = [config['input_tdate']] print "Current tournament is %s" % utils.tdate2string(tdates[0]) # - Loading all different cities (active cities) cities = db.get_cities() # - If input city set, then drop all other cities. if not config['input_city'] == None: tmp = [] for elem in cities: if elem['name'] == config['input_city']: tmp.append(elem) cities = tmp # - Reading parameter list params = db.get_parameter_names(False) for i, j in zip(["Donnerstag", "Freitag"], [1, 0]): print i, j
# Final variable. If set to False we would not allow to merge. # If 'True' merging would be allowed. merging_allowed = True cur = db.cursor() for tdate in tdates: # Initialize counts = [None] * len(users) for u in range(0, len(users)): cur.execute(sql % (userids[u], tdate)) counts[u] = cur.fetchone()[0] # If no bets for this tournament at all: skip if np.max(counts) == 0: continue # Show frontend print(" %10s %6d " % (utils.tdate2string(tdate), tdate), end=' ') for u in range(0, len(users)): print(" %10d " % counts[u], end=' ') if len(np.where(np.array(counts) > 0)[0]) == 1: print(" merge ok") else: merging_allowed = False print(" [ERROR] MERGING NOT ALLOWED (OVERLAP)") if not merging_allowed: print("\n\n !!!! MERGING NOT ALLOWED !!!!!\n\n") db.close() sys.exit(9) # ---------------------------------------------------------------- # Else merge user
def print_moses(db, config, cities, tdates): path = "moses/input/" file_head = "Berliner Wetterprognoseturnier %s\n\nEingetroffene Werte und abgegebene Prognosen:\n" table_head = "Name N Sd dd ff fx Wv Wn PPP TTm TTn TTd RR" day_heads = ["Samstag:", "Sonntag:"] ranking_str = "Wertung der Prognose vom %s:\n" ranking_head = "Pl. Name Punkte Tag1 Tag2\n_________________________________________________" info_str = "\nDie durchschnittliche Punktzahl beträgt: %5.1f Punkte.\nWertung für nicht teilnehmende Mitspieler: %5.1f Punkte." params = db.get_parameter_names(sort=True) def print_rows(args, file): row_format = "{name:<21.21s} {n:>5} {sd:>3} {dd:>3} {ff:>2} {fx:>2} {wv:>2} {wn:>2} {ppp:>6.6} {tn:>5.5} {tx:>5.5} {td:>5.5} {rr:>5.5}" for i in range(1, 8): if type(args[i]) != str: args[i] = int(args[i]) print(row_format.format(name=args[0], n=args[1], sd=args[2], dd=args[3], ff=args[4], fx=args[5], wv=args[6], wn=args[7], ppp=args[8], tn=args[9], tx=args[10], td=args[11], rr=args[12]), file=f) for city in cities: cityID = city['ID'] if config['input_alldates']: tdates = db.all_tournament_dates(city['ID']) current = db.current_tournament() if current in tdates: tdates.remove(current) tdates = [i for i in tdates if i > 12027] for tdate in tdates: missing_bets = db.find_missing_bets(cityID, tdate) missing_obs = db.find_missing_obs(cityID, tdate) #if *missing_bets returns False, it's a bool, missing obs returns either True or False if type(missing_bets) != bool or missing_obs: print("To many missing obs or parameters!") continue stations = db.get_stations_for_city(cityID, active=False, tdate=tdate) #print output to file, first get prober filename filename = path + utils.tdate2string( tdate, moses=True) + "." + city['name'].lower()[0] + "pw" f = open(filename, 'w') tdate_str = utils.tdate2string(tdate) print(file_head % tdate_str, file=f) users = db.get_participants_in_city(cityID, tdate, sort=True) for day in range(1, 3): print(day_heads[day - 1], file=f) print(table_head, file=f) print(80 * "_", file=f) for station in stations: obs = [station.name] for param in params: paramID = db.get_parameter_id(param) value = db.get_obs_data(cityID, paramID, tdate, day, station.wmo) if value is False or value is None: obs.append("n") else: obs.append(float(value) / 10) print_rows(obs, f) #print(78*"_", file=f) print("", file=f) for userID in users: bet = [db.get_username_by_id(userID, which="display_name")] for param in params: paramID = db.get_parameter_id(param) value = db.get_bet_data("user", userID, cityID, paramID, tdate, day) if value is False or value is None: bet.append("n") else: bet.append(float(value) / 10) print_rows(bet, f) if day == 1: print("\f", file=f) else: print("\n", file=f) print(ranking_str % tdate_str, file=f) print(ranking_head, file=f) #TODO: ranking sleepyID = db.get_user_id("Sleepy") sql = "SELECT bs.rank, wu.display_name, bs.points, bs.points_d1, bs.points_d2 FROM wp_wetterturnier_betstat bs JOIN wp_users wu ON userID = wu.ID WHERE tdate=%d AND cityID=%d AND userID != %d ORDER BY bs.rank" cur = db.cursor() cur.execute(sql % (tdate, cityID, sleepyID)) data = cur.fetchall() for i in data: print("{:2d}. {:26s}{:5.1f} ({:5.1f}/{:5.1f})".format( i[0], i[1], i[2], i[3], i[4]), file=f) sql = "SELECT mean FROM wp_wetterturnier_tdatestats WHERE tdate = %d AND cityID = %d" cur.execute(sql % (tdate, cityID)) mean = cur.fetchone()[0] do, fr = db.get_user_id("Donnerstag"), db.get_user_id("Freitag") sleepy = db.get_sleepy_points(cityID, tdate, [do, fr]) print(sleepy) print(info_str % (mean, sleepy), file=f)
def CSP(db,config,cities,tdates): import sys, os import numpy as np from pywetterturnier import utils print('\n * Compute sum points to fill betstat table') # ---------------------------------------------------------------- # - Now going over the cities and compute the points. # ---------------------------------------------------------------- for city in cities: # ------------------------------------------------------------- # - If aldates, take all tdates from database # ------------------------------------------------------------- if config['input_alldates']: tdates = db.all_tournament_dates( city['ID'] ) # ------------------------------------------------------------- # - Looping trough dates # ------------------------------------------------------------- for tdate in tdates: # ---------------------------------------------------------------- # - Check if we are allowed to perform the computation of the # mean bets on this date # ---------------------------------------------------------------- check = utils.datelock(config,tdate) if check: print(' Date is \'locked\' (datelock). Dont execute, skip.') continue # ---------------------------------------------------------- # - Which judgingclass do we have to take? # It is possible that the scoring system changed. # ---------------------------------------------------------- # Take the latest judgingclass changed in 2002-12-06 if tdate < 12027: if config['input_ignore']: print('[!] Judginglcass not defined - but started in ignore mode. Skip.') continue else: utils.exit('I dont know which judgingclass I should use for this date. Stop.') print(' For %s tournament is %s' % (city['name'], utils.tdate2string( tdate ))) # - If config['input_user'] is an integer value this # is a userID. Compute the sum points for this user # only. This is for some replay purposes. if type(config['input_user']) == type(int()): extra = ' AND userID = %d ' % config['input_user'] else: extra = '' sqlP = 'SELECT userID, cityID, tdate, ' + \ 'round(sum(points),1) AS points, max(placed) AS submitted ' + \ 'FROM %swetterturnier_bets ' + \ 'WHERE cityID = %d AND tdate = %d %s ' + \ 'GROUP BY userID, cityID, tdate ' sqlP = sqlP % (db.prefix,city['ID'], tdate, extra) sqlX = 'SELECT userID, cityID, tdate, sum(points) AS points ' + \ 'FROM %swetterturnier_bets ' + \ 'WHERE cityID = %d AND tdate = %d ' + \ 'AND betdate = %d %s ' + \ 'GROUP BY userID, cityID, tdate' sql1 = sqlX % (db.prefix,city['ID'], tdate, tdate+1, extra) sql2 = sqlX % (db.prefix,city['ID'], tdate, tdate+2, extra) sql_full = 'SELECT p.points AS points, d1.points AS points_d1, d2.points AS points_d2, ' + \ 'p.userID, p.cityID, p.tdate ' + \ 'FROM ('+sqlP+') AS p ' + \ 'LEFT OUTER JOIN ' + \ '('+sql1+') AS d1 ON p.userID=d1.userID AND p.tdate=d1.tdate ' + \ 'AND p.cityID = d1.cityID ' + \ 'LEFT OUTER JOIN ' + \ '('+sql2+') AS d2 ON p.userID=d2.userID AND p.tdate=d2.tdate ' + \ 'AND p.cityID = d2.cityID ' + \ '' print(' - Reading data from database') cur = db.cursor() cur.execute( sql_full ) desc = cur.description data = cur.fetchall() # Now compute if len(data) == 0: print(' - Sorry, got no data to compute sum points') else: # Else: we have data, update database print(' - Upserting database (%d lines)' % len(data)) # Require the index of the "points" variable points_idx = None for di in range(0,len(desc)): if str(desc[di][0]) == 'points': points_idx = di break if points_idx is None: sys.exit("ERROR: could not find variable \"points\" in data. Stop.") # - Prepare the data sql = 'INSERT INTO '+db.prefix+'wetterturnier_betstat ' + \ 'SET rank=%s, points=%s, points_d1=%s, points_d2=%s, ' + \ 'userID=%s, cityID=%s, tdate=%s ON DUPLICATE KEY UPDATE points=VALUES(points), ' + \ 'points_d1 = VALUES(points_d1), points_d2 = VALUES(points_d2)' #for d in data: # if not int(d[0]) == 1130: continue # print d # Compute rank points = [] for pi in range(0,len(data)): points.append(data[pi][points_idx]) print(points) try: points = np.sort(points)[::-1] except: print("ERROR: error while computing ranks") continue #sys.exit(0) rank = [] data = list(data) for pi in range(0,len(data)): this = data[pi][points_idx] # if points is empty # Append empty rank (IN FRONT) and skip if not this: data[pi] = (None,) + data[pi] continue # else search rank # and append rank IN FRONT (check sql command) rank = np.where(points == this)[0] if not len(rank): data[pi] = (None,) + data[pi] else: data[pi] = (rank[0] + 1,) + data[pi] cur.executemany( sql , data ) db.commit()
def CSP(db,config,cities,tdates): import sys, os import numpy as np from pywetterturnier import utils print '\n * Compute sum points to fill betstat table' # ---------------------------------------------------------------- # - Now going over the cities and compute the points. # ---------------------------------------------------------------- for city in cities: # ------------------------------------------------------------- # - If aldates, take all tdates from database # ------------------------------------------------------------- if config['input_alldates']: tdates = db.all_tournament_dates( city['ID'] ) # ------------------------------------------------------------- # - Looping trough dates # ------------------------------------------------------------- for tdate in tdates: print ' For %s tournament is %s' % (city['name'], utils.tdate2string( tdate )) # - If config['input_user'] is an integer value this # is a userID. Compute the sum points for this user # only. This is for some replay purposes. if type(config['input_user']) == type(int()): extra = ' AND userID = %d ' % config['input_user'] else: extra = '' sqlP = 'SELECT userID, cityID, tdate, ' + \ 'round(sum(points),1) AS points, max(placed) AS submitted ' + \ 'FROM %swetterturnier_bets ' + \ 'WHERE cityID = %d AND tdate = %d %s ' + \ 'GROUP BY userID, cityID, tdate ' sqlP = sqlP % (db.prefix,city['ID'], tdate, extra) sqlX = 'SELECT userID, cityID, tdate, sum(points) AS points ' + \ 'FROM %swetterturnier_bets ' + \ 'WHERE cityID = %d AND tdate = %d ' + \ 'AND betdate = %d %s ' + \ 'GROUP BY userID, cityID, tdate' sql1 = sqlX % (db.prefix,city['ID'], tdate, tdate+1, extra) sql2 = sqlX % (db.prefix,city['ID'], tdate, tdate+2, extra) sql_full = 'SELECT p.points AS points, d1.points AS points_d1, d2.points AS points_d2, ' + \ 'p.userID, p.cityID, p.tdate ' + \ 'FROM ('+sqlP+') AS p ' + \ 'LEFT OUTER JOIN ' + \ '('+sql1+') AS d1 ON p.userID=d1.userID AND p.tdate=d1.tdate ' + \ 'AND p.cityID = d1.cityID ' + \ 'LEFT OUTER JOIN ' + \ '('+sql2+') AS d2 ON p.userID=d2.userID AND p.tdate=d2.tdate ' + \ 'AND p.cityID = d2.cityID ' + \ '' print ' - Reading data from database' cur = db.cursor() cur.execute( sql_full ) desc = cur.description data = cur.fetchall() # Now compute if len(data) == 0: print ' - Sorry, got no data to compute sum points' else: # Else: we have data, update database print ' - Upserting database (%d lines)' % len(data) # Require the index of the "points" variable points_idx = None for di in range(0,len(desc)): if str(desc[di][0]) == 'points': points_idx = di break if points_idx is None: sys.exit("ERROR: could not find variable \"points\" in data. Stop.") # - Prepare the data sql = 'UPDATE IGNORE '+db.prefix+'wetterturnier_betstat ' + \ 'SET rank=%s, points=%s, points_d1=%s, points_d2=%s ' + \ 'WHERE userID=%s AND cityID=%s AND tdate=%s' #for d in data: # if not int(d[0]) == 1130: continue # print d # Compute rank points = [] for pi in range(0,len(data)): points.append(data[pi][points_idx]) points = np.sort(points)[::-1] rank = [] data = list(data) for pi in range(0,len(data)): this = data[pi][points_idx] # if points is empty # Append empty rank (IN FRONT) and skip if not this: data[pi] = (None,) + data[pi] continue # else search rank # and append rank IN FRONT (check sql command) rank = np.where(points == this)[0] if not len(rank): data[pi] = (None,) + data[pi] else: data[pi] = (rank[0] + 1,) + data[pi] cur.executemany( sql , data ) db.commit()