from egd import connection


cursor=connection.cursor()


def playerYear(name,year):
	query="""
		select name, datum, win_count, game_count, rating_after, rating_after-rating_before
			from players natural join players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
			where name=? and datum>=date(?) and datum<date(?)
			order by datum asc
	"""
	date1="{0}-01-01".format(year)
	date2="{0}-01-01".format(year+1)

	res=cursor.execute(query,(name,date1,date2))
	print("jméno\tdatum\tpočet výher\tpočet partií\trating po turnaji\tzměna")
	for rec in res:
		print("\t".join([str(x) for x in rec]))


def yearByCountry(year):
	date1="{0}-01-01".format(year)
	date2="{0}-01-01".format(year+1)
	query="""
		select country_code,count(pin) from (
			select pin,count(*),pp.country_code from
				players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
				where datum>=date(?) and datum<date(?)
				group by pin
			)
			group by country_code
			order by country_code asc;
		"""
	res=cursor.execute(query,(date1,date2))
	print("kód země\tpočet aktivních hráčů")
	for rec in res:
		print("\t".join([str(x) for x in rec]))


def populationStrength():
	pass


def populationAge():
	pass


# playerYear("Kotowski Jaroslav",2017)
# yearByCountry(2016)