# HG changeset patch
# User Laman
# Date 2018-06-08 00:30:35
# Node ID 0aeebf95bfe7c7ec73c8dbb502ba7951c21fe811
# Parent  19978bbb3d69e4ae03d7abd2525aa6e927d44d31

dotaz playersProgress, úprava timeToPeak

diff --git a/queries.py b/queries.py
--- a/queries.py
+++ b/queries.py
@@ -202,10 +202,25 @@ def fetchByRating(strongerThan,country="
 		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 a s vrcholem před 1.1.2017."""
+	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="""
@@ -213,13 +228,52 @@ def timeToPeak():
 			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
-			having peak_datum<date('2017-01-01')
 	"""
 
+	tournamentCounts=_tournamentsToPeak()
 	res=cursor.execute(query)
-	print("pin\tmin rating\tmax rating\tprvní turnaj\tdosažení max ratingu\troky k max ratingu")
+
+	print("pin\tmin rating\tmax rating\tprvní turnaj\tdosažení max ratingu\troky k max ratingu\tturnaje k max ratingu")
 	for rec in res:
-		print("\t".join([str(x) for x in rec]))
+		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")
 
 
 # playerHistory("Kotowski Jaroslav")
@@ -230,3 +284,4 @@ def timeToPeak():
 # tournamentAttendance(2001,2018)
 # fetchByRating(2600,"CZ")
 # timeToPeak()
+# playersProgress()