^\.

all.hst

tlist.html

egd.db

__pycache__

def fetchByRating(strongerThan,country=""):

	"""Hráči, kteří dosáhli rating strongerThan nebo vyšší, nepovinně filtrovaní podle země, seřazení od nejlepšího dosaženého ratingu."""

	query="""

		select pin,name,max(rating_after) as max_rating

			from players_performance natural join players

			where rating_after>=? and (?='' or country_code=?)

			group by pin

			order by max_rating desc"""

	res=cursor.execute(query,(strongerThan,country,country))

	print("pin\tjméno\tmax rating")

	for rec in res:

		print("\t".join([str(x) for x in rec]))

def _tournamentsToPeak():

	"""Vrací slovník {pin: počet_turnajů_k_dosažení_max_ratingu}"""

	query="""select pin,rating_after,max_rating from players_performance_tournaments join (select pin as sub_pin,max(rating_after) as max_rating from players_performance group by pin) on pin=sub_pin order by datum asc"""

	res=cursor.execute(query)

	players=dict()

	closed=set()

	for (pin,rating,maxRating) in res:

		tc=players.get(pin,0)

		if pin not in closed: players[pin]=tc+1

		if rating==maxRating: closed.add(pin)

	return players

def timeToPeak():

	"""Jakého hráči dosáhli maximálního ratingu a jak dlouho jim to trvalo.

	Uvažuje hráče začínající s ratingem<=600."""

	# join on pin, max_rating může duplikovat řádky, když měl hráč stejný max_rating vícekrát.

	# proto ten ošklivý vnější group by :-ú

	query="""

		select subq.pin,min(min_rating),min(max_rating),min(_min_datum) as min_datum,min(datum) as peak_datum,(julianday(min(datum))-julianday(min(_min_datum)))/365.25 as time_to_peak

			from players_performance_tournaments as ppt

				join (select pin,min(rating_before) as min_rating,max(rating_after) as max_rating,min(datum) as _min_datum from players_performance_tournaments group by pin having min_rating<=600) as subq on ppt.pin=subq.pin and ppt.rating_after=max_rating

			group by ppt.pin

	"""

	tournamentCounts=_tournamentsToPeak()

	res=cursor.execute(query)

	for rec in res:

		pin=rec[0]

		print("\t".join([str(x) for x in rec]+[str(tournamentCounts[pin])]))

def playersProgress():

	"""Všem hráčům vypíše rating po jednotlivých letech. Zobrazuje rating na konci roku, zaokrouhlený dolů na stovky. Nula značí rok bez záznamu v databázi."""

	y1=1996

	y2=2018

	class Player:

		def __init__(self):

			self.rating=dict()

			self.country=""

			self.tournamentCount=0

	query="""

		select pin,p_country_code as country_code,t_count,cast(strftime('%Y',datum) as int) as year,rating_after,datum

			from players_performance_tournaments as ppt

				join (select pin as sub_pin,count(tournament_code) as t_count from players_performance group by pin) on ppt.pin=sub_pin

			order by datum desc"""

	res=cursor.execute(query)

	players=dict()

	for (i,rec) in enumerate(res):

		(pin,country,tournamentCount,year,rating,_)=rec

		if pin in players:

			p=players[pin]

		else:

			p=Player()

			p.country=country

			p.tournamentCount=tournamentCount

			players[pin]=p

		if year not in p.rating: p.rating[year]=round(rating//100*100)

		if i%1000==0: print(i,"/ ?","\r",end="",file=sys.stderr)

	print(" "*20,file=sys.stderr)

	print("pin\tkód země\tpočet turnajů\t"+"\t".join(str(y) for y in range(y1,y2)))

	for (pin,p) in players.items():

		print(pin,p.country,p.tournamentCount,*(p.rating.get(y,0) for y in range(y1,y2)),sep="\t")

def lifeSpans():

	"""Životnosti hráčů. Datum prvního a posledního turnaje, jejich rozdíl v letech, počet odehraných turnajů."""

	res=cursor.execute(query)

	for rec in res:

		print("\t".join([str(x) for x in rec]))

# playerHistory("Kotowski Jaroslav")

# timeRank(2017,"CZ")

# yearsByCountry(2001,2018)

# populationStrength(2001,2018,"RU")

# populationAge(2001,2018,"RU")

# tournamentAttendance(2001,2018)

# fetchByRating(2600,"CZ")

# timeToPeak()

# playersProgress()