def upsert_stats(self, cityID, stats, userID=False, tdate=False, day=0):
"""Insert stats dict into database"""
cur = self.db.cursor()
mstr = ""
values, sql_vals = [], ""
update = " ON DUPLICATE KEY UPDATE "
for i in stats.keys():
mstr += "%s, " % i
values.append(stats[i])
sql_vals = sql_vals + i + "=VALUES(" + i + "), "
sql_vals, mstr = sql_vals[:-2], mstr[:-2]
if tdate:
sql = "INSERT INTO %swetterturnier_tdatestats (cityID, tdate, %s) VALUES %s" + update + sql_vals
cur.execute(
sql % (self.prefix, mstr,
str(self.sql_tuple(sum([[cityID], [tdate], values], [])))))
elif userID:
sql = "INSERT INTO %swetterturnier_userstats (userID, cityID, %s) VALUES %s" + update + sql_vals
cur.execute(
sql % (self.prefix, mstr,
str(self.sql_tuple(sum([[userID], [cityID], values], [])))))
elif cityID:
sql = "INSERT INTO %swetterturnier_citystats (cityID, %s) VALUES %s" + update + sql_vals
cur.execute(sql % (self.prefix, mstr,
str(self.sql_tuple(sum([[cityID], values], [])))))
else:
utils.exit("Wrong usage of upsert_stats in database.py!")
def input_to_list(x, msg):
"""
Inputs to this script can be lists of numeric values.
Therefore we have to convert them into numpy arrays which
will be done by this script. If input is NONE, an empty
array will be returned.
Inputs: x required string (e.g., '13.3' or '13.3,14.4')
msg required short message in case the function calls exit.
Output: numpy.array object containing either nothing (empty) or
the values from input x as float numbers.
"""
if not x: return np.asarray([])
tmp = x.split(",")
res = []
for elem in tmp:
try:
res.append(float(elem))
except:
utils.exit("Problems: %s have to be numeric!" % msg)
return np.asarray(res)
is_latest_tournament = False
if config['input_tdate'] == None:
config['input_tdate'] = current_tdate
print ' * Using latest tournament date: %d' % config['input_tdate']
is_latest_tournament = True
else:
#utils.exit('Sorry, need explicit -t/--tdate input for this script')
print ' * Using input tdate: %d' % 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.'
)
# - Compute the Points for all the dudes first
import subprocess as sub
if not config['input_alldates']:
if is_latest_tournament:
scripts = [
'ComputePetrus.py', 'ComputeMoses.py',
'ComputePersistenzen.py', 'ComputeMeanBets.py',
'ComputePoints.py', 'ComputeSleepy.py'
]
if today == current_tdate:
print "Today is a tournament day!"
else:
scripts = [
# - 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)
# ----------------------------------------------------------------
# - Because of the observations we have to compute the
# points city by city. Loading city data here first.
# ----------------------------------------------------------------
# - 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 )
# - 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)
# ----------------------------------------------------------------
# - Because of the observations we have to compute the
# points city by city. Loading city data here first.
# ----------------------------------------------------------------
# - 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 i in cities:
print(" Run computation for city {0:s} ({1:d})".format(city,cityID))
# Run the scripts in the correct order
for script in scripts:
# - Create next python command. Append ALL input arguments
# we hade in this script plus the tdate (if not set in input
# arguments).
cmd = ['python',script]
# Append tournamentdate and city to the arglist
cmd.append('-t'); cmd.append("{0:d}".format(tdate))
cmd.append('-c'); cmd.append(city)
p1 = sub.Popen(cmd,stderr=sub.PIPE)
err = p1.communicate()
if not p1.returncode == 0:
for line in err: print('%s\n' % line)
utils.exit('ERROR WHILE RUNNING %s AS SUBPROCESS FOR DATE %d CITY %s' % (script,tdate,city))
# update database ans set these jobs to 'done'
cur = db.cursor()
now = dt.now().strftime("%Y-%m-%d %H:%M:%S")
sql = "UPDATE {0:s}{1:s} SET done = '{2:s}' WHERE cityID = {3:d} AND tdate = {4:d}".format(
config['mysql_prefix'],'wetterturnier_rerunrequest',now,cityID,tdate)
cur = db.cursor()
cur.execute(sql)
db.commit()
db.close()
else:
tdates = [config['input_tdate']]
# - 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 i in cities:
if i['name'] == config['input_city']: tmp.append(i)
cities = tmp
# - If moses directory does not exist:
if not os.path.isdir(config['data_moses']):
utils.exit("Cannot find directory \"{0:s}\" which should contain ".format(config["data_moses"]) + \
"the necessary coefficient-files for Moses! Stop!")
# - Reading parameter list
params = db.get_parameter_names(active=True, sort=True)
# ----------------------------------------------------------------
# - Prepare the Moses
# ----------------------------------------------------------------
username = '******'
#db.create_user( username )
moses_userID = db.get_user_id(username)
# ----------------------------------------------------------------
# - A small helper function to find the correct coefficient
# file. Klaus sends them once a week, normally on
# Tuesday. The name of the file contains a date
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
tmp = []
for elem in cities:
if elem['name'] == config['input_city']: tmp.append(elem)
cities = tmp
# - Loading all active users from the database
active_groups = db.get_active_groups()
# - Checking input user if set
# If input_user was given as string we have to find the
# corresponding userID first!
if type(config['input_user']) == type(int()):
config['input_user'] = db.get_username_by_id(config['input_user'])
if not config['input_user']:
utils.exit(
'SORRY could not convert your input -u/--user to corresponding username. Check name.'
)
# - Check if input_user is an active group.
if not config['input_user'] == None:
if not config['input_user'] in active_groups:
print ' Some help for you: currently active groups:'
for grp in active_groups:
print ' - %s' % grp
utils.exit(
'Sorry, but %s is not a name of an active group. Stop.' %
config['input_user'])
else:
active_groups = [config['input_user']]
# - Create new user
'/archivefiles_processed_%s.txt' % city):
aid = open(
config['rawdir'] +
'/archivefiles_processed_%s.txt' % city, 'r')
aco = aid.readlines()
aid.close()
skip = False
for aline in aco:
if file in aline:
print ' - ALLREADY PROCESSED. Skip'
skip = True
if skip: continue
print '* Loading: %s' % (file)
if counter >= 5: utils.exit('dev stop, counter >= 5 here')
# - Import first file
obj = importbets.importbets(config, file_forced)
obj.loadfilecontent(file)
obj.identify_city()
# - Getting data from raw files
obj.takedata(obj.raw_lines)
obj.extract_sum_points()
obj.extract_obs(1)
obj.extract_obs(2)
"observed wind speed, single value, or comma separated list.")
# - Quiet mode
parser.add_option(
"-q",
"--quiet",
action="store_true",
dest="quiet",
default=False,
help=
"Optional, logical. If set, output will be more quiet. Default False.")
inputs, args = parser.parse_args()
# - Missing input -o
if not inputs.param:
print parser.usage
utils.exit("Missing required input -p/--param.")
if not inputs.obs:
print parser.usage
utils.exit("Missing required input -o/--obs.")
if not inputs.values:
print parser.usage
utils.exit("Missing required input -v/--values.")
# ----------------------------------------------------------------
# ----------------------------------------------------------------
# - Reading config file
# ----------------------------------------------------------------
config = utils.readconfig('config.conf')
# ----------------------------------------------------------------
# - Checking if parameter is allowed
# - Only start if called as main routine
if __name__ == '__main__':
import subprocess as sub
import getopt
import sys
# - Wetterturnier specific modules
from pywetterturnier import utils
from pywetterturnier import importbets
try:
opts, args = getopt.getopt(sys.argv[1:],'-o',['--obs'])
except getopt.GetoptError as err:
print(err)
utils.exit('Wrong input options set')
obsonly = False
for o, a in opts:
if o in ['-o','--obs']:
obsonly = True
# - Reading configuration file first
config = utils.readconfig()
# - The url needed to download the data
url_template = 'http://wetterturnier.de/wertungen/uebersicht_%s.php?p=ue0&stadt=%s'
# ---------------------------------------------------------------
# - No tags? No downloading shit.
if config['migrate_citytags'] == None:
print ' * No citytags from config file: nothing will be done.'
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()
from pywetterturnier import database
# - Reading configuration file first
config = utils.readconfig()
# - Migrate mitspieler.txt file with
# passwords ans shit?
if config['migrate_mitspieler']:
db = database.database(config)
print ' * Migrating mitspieler list'
try:
fid = open(config['migrate_mitspielerfile'], 'r')
except:
utils.exit('Cannot read file %s' %
config['migrate_mitspielerfile'])
# - Write changed usernames to file
# A local file to store all users where
# the username changed because of special characters.
try:
ufile = open('changed_usernames.txt', 'w+')
except:
utils.exit(
'Problems creating file changed_usernames.txt write mode')
# - Else loop trough file and convert usernames and
# passwords to prepare for database
res = []
for line in fid.readlines():
line = line.strip()
