# HG changeset patch
# User Laman
# Date 2018-04-01 13:08:32
# Node ID 4ebd2c7c365734961f962c725375c7a3d503b626
# Parent  e9a044cb8ce8a838d6dc3cc5397bda62e4877123

yearByCountry rozšířeno na yearsByCountry

diff --git a/queries.py b/queries.py
--- a/queries.py
+++ b/queries.py
@@ -1,7 +1,11 @@
+import sys
+
 from egd import connection
 
 
 cursor=connection.cursor()
+year1=2001
+year2=2018
 
 
 def yearStart(year):
@@ -25,27 +29,50 @@ def playerYear(name,year):
 		print("\t".join([str(x) for x in rec]))
 
 
-def yearByCountry(year):
+def _yearByCountry(year):
 	"""Počty hráčů s turnajem odehraným v daném roce, dělené podle země původu. Při změně příslušnosti se hráč započítá oběma zemím."""
 	date1=yearStart(year)
 	date2=yearStart(year+1)
-	# co hráči, kteří změní zemi?
 	query="""
-		select p_country_code,count(pin) from (
+		select p_country_code,count(pin) as player_count from (
 			select pin,count(*),p_country_code from
 				players_performance_tournaments
 				where datum>=date(?) and datum<date(?)
 				group by pin,p_country_code
 			)
 			group by p_country_code
-			order by p_country_code asc;
+			order by p_country_code asc
 		"""
 	res=cursor.execute(query,(date1,date2))
+
+	return res
+
+
+def yearByCountry(year):
 	print("kód země\tpočet aktivních hráčů")
-	for rec in res:
+	for rec in _yearByCountry(year):
 		print("\t".join([str(x) for x in rec]))
 
 
+def yearsByCountry():
+	res=dict()
+	for year in range(year1,year2):
+		records=_yearByCountry(year)
+		for rec in records:
+			(countryCode,playerCount)=rec
+			if countryCode not in res:
+				res[countryCode]=dict()
+			res[countryCode][year]=playerCount
+		print("\r",year,end="",file=sys.stderr)
+	print("\r",end="",file=sys.stderr)
+
+	print("Počty aktivních hráčů podle let")
+	print("kód země\t"+"\t".join(str(y) for y in range(year1,year2)))
+	for countryCode in sorted(res.keys()):
+		print(countryCode,end="\t")
+		print("\t".join(str(res[countryCode].get(y,0)) for y in range(year1,year2)))
+
+
 def timeRank(year,country):
 	"""Výpis hráčů dané země aktivních v daném roce."""
 	date1=yearStart(year)
@@ -56,7 +83,8 @@ def timeRank(year,country):
 				(select pin,min(datum) as min_datum,max(datum) as max_datum from players_performance_tournaments where datum<date(?) group by pin having max_datum>=date(?)) as subq
 				join players_performance_tournaments as ppt on subq.pin=ppt.pin and subq.max_datum=ppt.datum natural join players
 			where ppt.p_country_code=?
-			order by rating desc"""
+			order by rating desc
+	"""
 
 	res=cursor.execute(query,(date2,date2,date1,country))
 	print("pin\tcelé roky hraní\tprvní turnaj\trating")
@@ -95,7 +123,8 @@ def populationAge(year,country):
 					where ppt2.datum>=date(?) and ppt2.datum<date(?) and ppt1.p_country_code=?
 					group by ppt1.pin
 			)
-			group by playing_years"""
+			group by playing_years
+	"""
 
 	res=cursor.execute(query,(date2,date1,date2,country))
 	print("celé roky hraní\tpočet hráčů")
@@ -121,3 +150,4 @@ def tournamentAttendance(year):
 # populationStrength(2017,"CZ")
 # populationAge(2017,"CZ")
 # tournamentAttendance(2010)
+yearsByCountry()