# -------------------------------------------------------------
if config['input_alldates']:
tdates = db.all_tournament_dates(city['ID'])
# -------------------------------------------------------------
# - Looping trough dates
# -------------------------------------------------------------
for tdate in tdates:
print(' * Current tournament is %s' % utils.tdate2string(tdate))
# ----------------------------------------------------------------
# - Check if we are allowed to perform the computation of the
# mean bets on this date
# ----------------------------------------------------------------
check = utils.datelock(config, tdate)
if not config['input_force'] and 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
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()
