X-Git-Url: https://git.sesse.net/?a=blobdiff_plain;f=update_sheets.js;h=5794b6f608dd3e8a29e0b8ff61c6fdbe824a87c9;hb=4d2991a8d05b23222912fb904b436af5f4d740c2;hp=641c94db10e41597d28eb5af48f65e4f05ed2f36;hpb=342bae451d406b44d8fb47ceca5213f308d5e797;p=ultimatescore

diff --git a/update_sheets.js b/update_sheets.js
index 641c94d..5794b6f 100644
--- a/update_sheets.js
+++ b/update_sheets.js
@@ -65,39 +65,279 @@ function possibly_update_oauth_key(cb) {
 	}
 }
 
-function publish_group_rank(group_name)
+function publish_group_rank(response, group_name)
 {
-	get_group(group_name, function(response, group_name) {
-		let updates = [];
-		let cols = ultimateconfig['score_sheet_cols'][group_name];
+	let updates = [];
+	let cols = ultimateconfig['score_sheet_cols'][group_name];
+
+	let teams = parse_teams_from_spreadsheet(response);
+	let games = parse_games_from_spreadsheet(response, group_name, false);
+	apply_games_to_teams(games, teams);
+
+	// Write the points total to the unsorted columns.
+	for (let i = 0; i < teams.length; ++i) {
+		let row = ultimateconfig['point_total_start_row'] + i;
+		updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
+	}
+
+	let tiebreakers = [];
+	teams = rank(games, teams, 1, tiebreakers);
+
+	// Write the ranking table, from scratch.
+	for (let i = 0; i < teams.length; ++i) {
+		let row = ultimateconfig['ranking_list_start_row'] + i;
+		updates.push({ "range": cols[0] + row, "values": [ [ teams[i].rank ] ] });
+		updates.push({ "range": cols[1] + row, "values": [ [ teams[i].mediumname ] ] });
+		updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
+	}
+
+	let tb_str = "";
+	if (tiebreakers.length != 0) {
+		tb_str = tiebreakers.join("\n");
+	}
+	updates.push({ "range": cols[0] + ultimateconfig['ranking_list_explain_row'], "values": [ [ tb_str ] ]});
+
+	let json = {
+		"valueInputOption": "USER_ENTERED",
+		"data": updates 
+	};
+	possibly_update_oauth_key(function() {
+		post_json('https://sheets.googleapis.com/v4/spreadsheets/' + ultimateconfig['score_sheet_id'] + '/values:batchUpdate?key=' + ultimateconfig['api_key'], json, function(response) {}, current_oauth_access_token);
+	});
+}
+
+function montecarlo(responses) {
+	let pseudo_group_names = ['X', 'Y', 'Z'];
+	let real_group_names = ['A', 'B', 'C'];
+	let teams = [], games = [], teams_to_idx = [];
 
-		let teams = parse_teams_from_spreadsheet(response);
-		let games = parse_games_from_spreadsheet(response, group_name, false);
-		apply_games_to_teams(games, teams);
+	let third_groups = [];
+	let busted_thirds = false;
 
-		// Write the points total to the unsorted columns.
-		for (let i = 0; i < teams.length; ++i) {
-			let row = ultimateconfig['point_total_start_row'] + i;
-			updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
+	for (const response of responses) {
+		let teams_group = parse_teams_from_spreadsheet(response);
+		let games_group = parse_games_from_spreadsheet(response, 'irrelevant group name', true);
+		apply_games_to_teams(games_group, teams_group);
+
+		teams.push(teams_group);
+		games.push(games_group);
+		teams_to_idx.push(make_teams_to_idx(teams_group));
+	}
+
+	for (let simulation_idx = 0; simulation_idx < 100; ++simulation_idx) {  // 100 seems to be enough.
+		let thirds = [];
+		for (let group_idx = 0; group_idx < responses.length; ++group_idx) {
+			// Fill in random results. We deliberately use a uniform [-13,+13]
+			// model here, since we are interested in the extremal results.
+			// Of course, not all real games go to 13, but the risk of that
+			// influencing the tiebreakers is very slim.
+			let games_copy = [];
+			for (const game of games[group_idx]) {
+				games_copy.push(Object.assign({}, game));
+			}
+			let teams_copy = [];
+			for (const team of teams[group_idx]) {
+				teams_copy.push(Object.assign({}, team));
+			}
+
+			for (let i = 0; i < games_copy.length; ++i) {
+				let idx1 = teams_to_idx[group_idx][games_copy[i].name1];
+				let idx2 = teams_to_idx[group_idx][games_copy[i].name2];
+				if (idx1 === undefined || idx2 === undefined) continue;
+				if (games_copy[i].score1 === undefined || games_copy[i].score2 === undefined ||
+				    isNaN(games_copy[i].score1) || isNaN(games_copy[i].score2) ||
+				    games_copy[i].score1 == games_copy[i].score2) {
+					// These were skipped by apply_games_to_teams() above.
+					let score1 = 0, score2 = 0;
+					let r = Math.floor(Math.random() * 26);
+					if (r < 13) {
+						score1 = 13;
+						score2 = r;
+						teams_copy[idx1].pts += 2;
+					} else {
+						score1 = r - 13;
+						score2 = 13;
+						teams_copy[idx2].pts += 2;
+					}
+					games_copy[i].score1 = score1;
+					games_copy[i].score2 = score2;
+					++teams_copy[idx1].nplayed;
+					++teams_copy[idx2].nplayed;
+					teams_copy[idx1].goals += score1;
+					teams_copy[idx2].goals += score2;
+					teams_copy[idx1].gd += score1;
+					teams_copy[idx2].gd += score2;
+					teams_copy[idx1].gd -= score2;
+					teams_copy[idx2].gd -= score1;
+				} else {
+					continue;
+				}
+			}
+			
+			// Now rank according to the simulation.
+			let tiebreakers = [];
+			teams_copy = rank(games_copy, teams_copy, 1, tiebreakers);
+
+			// See if we have conflicting information with other simulations.
+			if (simulation_idx == 0) {
+				for (let i = 0; i < teams[group_idx].length; ++i) {
+					let idx = teams_to_idx[group_idx][teams_copy[i].name];
+					teams[group_idx][idx].simulated_rank = teams_copy[i].rank;
+				}
+			} else {
+				for (let i = 0; i < teams[group_idx].length; ++i) {
+					let idx = teams_to_idx[group_idx][teams_copy[i].name];
+					if (teams[group_idx][idx].simulated_rank !== teams_copy[i].rank) {
+						teams[group_idx][idx].simulated_rank = null;
+					}
+				}
+			}
+
+			if (!busted_thirds) {
+				let any_third_found = false;
+				for (let i = 0; i < teams[group_idx].length; ++i) {
+					// Store the third.
+					if (i == 2 || teams_copy[i].rank == 3) {
+						if (any_third_found) {
+							busted_thirds = true;
+						} else {
+							teams_copy[i].group_idx = group_idx;
+							thirds.push(teams_copy[i]);
+							any_third_found = true;
+						}
+					}
+				}
+			}
 		}
 
-		let tiebreakers = [];
-		teams = rank(games, teams, 1, tiebreakers);
+		// Also rank thirds.
+		if (!busted_thirds) {
+			let tiebreakers = [];
+			let ranked = rank_thirds([], thirds, 1, tiebreakers);
+			if (simulation_idx == 0) {
+				third_groups = ranked;
+			} else {
+				for (let i = 0; i < responses.length; ++i) {
+					if (third_groups[i].group_idx !== ranked[i].group_idx) {
+						third_groups[i].group_idx = null;
+					}
+				}
+			}
+		}
+	}
 
-		// Write the ranking table, from scratch.
-		for (let i = 0; i < teams.length; ++i) {
-			let row = ultimateconfig['ranking_list_start_row'] + i;
-			updates.push({ "range": cols[0] + row, "values": [ [ teams[i].rank ] ] });
-			updates.push({ "range": cols[1] + row, "values": [ [ teams[i].mediumname ] ] });
-			updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
+	let replacements = [];
+	for (let group_idx = 0; group_idx < responses.length; ++group_idx) {
+		if (third_groups[group_idx].group_idx !== null) {
+			replacements.push([ pseudo_group_names[group_idx], real_group_names[third_groups[group_idx].group_idx] ]);
 		}
+	}
 
-		let tb_str = "";
-		if (tiebreakers.length != 0) {
-			tb_str = tiebreakers.join("\n");
+	for (let group_idx = 0; group_idx < responses.length; ++group_idx) {
+		for (let i = 0; i < teams[group_idx].length; ++i) {
+			if (teams[group_idx][i].simulated_rank !== null) {
+				replacements.push([ real_group_names[group_idx] + teams[group_idx][i].simulated_rank, teams[group_idx][i].shortname ]);
+			}
 		}
-		updates.push({ "range": cols[0] + ultimateconfig['ranking_list_explain_row'], "values": [ [ tb_str ] ]});
+	}
+
+	return replacements;
+}
 
+function names_for_team(team, expansions) {
+	if (expansions.hasOwnProperty(team)) {
+		return expansions[team];
+	}
+	let longteam = team.replace("W ", "Win. ").replace("L ", "Los. ");
+	return [ longteam, longteam, team ];
+}
+
+function expand_mediumname_if_single_team(team, expansions) {
+	if (expansions.hasOwnProperty(team)) {
+		return expansions[team][1];
+	}
+	return team;
+}
+
+function do_replacements(str, replacements) {
+	for (const r of replacements) {
+		str = str.replace(r[0], r[1]);
+	}
+	return str;
+}
+
+function fill_playoff(replacements, teams) {
+	let team_expansions = {};
+	for (const group of teams) {
+		for (const team of group) {
+			team_expansions[team.name] = team_expansions[team.mediumname] = team_expansions[team.shortname] =
+				[ team.name, team.mediumname, team.shortname ];
+		}
+	}
+
+	let games = ultimateconfig['playoff_games'];
+	get_results('Results', function(response) {
+		let updates = [];
+		let game_num = 0;
+		for (const game of games) {
+			let team1 = do_replacements(game[0], replacements);
+			let team2 = do_replacements(game[1], replacements);
+			let team1_mediumname = expand_mediumname_if_single_team(team1, team_expansions);
+			let team2_mediumname = expand_mediumname_if_single_team(team2, team_expansions);
+			let row = ultimateconfig['playoff_games_start_row'] + game[3];
+			let cols = ultimateconfig['playoff_games_cols'][game[2]];
+			let cell_team1 = "Results!" + String.fromCharCode(cols[0] + 65) + row;
+			let cell_score1 = "Results!" + String.fromCharCode(cols[1] + 65) + row;
+			let cell_score2 = "Results!" + String.fromCharCode(cols[2] + 65) + row;
+			let cell_team2 = "Results!" + String.fromCharCode(cols[3] + 65) + row;
+			updates.push({ "range": cell_team1, "values": [ [ team1_mediumname ] ] });
+			updates.push({ "range": cell_team2, "values": [ [ team2_mediumname ] ] });
+
+			let score1 = response['values'][row - 1][cols[1]];
+			let score2 = response['values'][row - 1][cols[2]];
+			let game_name = game[4];
+			let game_name2 = game_name.replace("Semi", "semi");
+
+			if (parseInt(score1) >= 0 && parseInt(score2) >= 0 && score1 != score2) {
+				if (parseInt(score1) > parseInt(score2)) {
+					replacements.unshift(["W " + game_name, team1]);
+					replacements.unshift(["L " + game_name, team2]);
+					replacements.unshift(["W " + game_name2, team1]);
+					replacements.unshift(["L " + game_name2, team2]);
+				} else {
+					replacements.unshift(["W " + game_name, team2]);
+					replacements.unshift(["L " + game_name, team1]);
+					replacements.unshift(["W " + game_name2, team2]);
+					replacements.unshift(["L " + game_name2, team1]);
+				}
+			} else if (game[5]) {
+				// No score yet, so write the name of the game (e.g. “L-semi 1”)
+				// where the score would normally be, to mark what this game is called.
+				// This is useful with the limited space on the tablet.
+				score1 = score2 = "";
+				updates.push({ "range": cell_score1, "values": [ [ game[4] ] ] });
+			}
+
+			if (game[2] == 0) {  // Stream field.
+				// Game.
+				updates.push({
+					"range": "Playoffs!A" + (game_num + 32) + ":J" + (game_num + 32),
+					"values": [ [ team1, team2, score1, score2, "", "", "", 7, response['values'][row - 1][1].replace(".",":"), game[6] ] ]
+				});
+
+				// Team codes.
+				updates.push({
+					"range": "Playoffs!A" + (2 * game_num + 3) + ":C" + (2 * game_num + 3),
+					"values": [ names_for_team(team1, team_expansions) ]
+				});
+				updates.push({
+					"range": "Playoffs!A" + (2 * game_num + 4) + ":C" + (2 * game_num + 4),
+					"values": [ names_for_team(team2, team_expansions) ]
+				});
+
+				++game_num;
+			}
+		}
 		let json = {
 			"valueInputOption": "USER_ENTERED",
 			"data": updates 
@@ -105,14 +345,35 @@ function publish_group_rank(group_name)
 		possibly_update_oauth_key(function() {
 			post_json('https://sheets.googleapis.com/v4/spreadsheets/' + ultimateconfig['score_sheet_id'] + '/values:batchUpdate?key=' + ultimateconfig['api_key'], json, function(response) {}, current_oauth_access_token);
 		});
-
 	});
 }
 
+function get_results(sheet_name, cb)
+{
+	let req = new XMLHttpRequest();
+	req.onload = function(e) {
+		cb(JSON.parse(req.responseText), sheet_name);
+	};
+	req.open('GET', 'https://sheets.googleapis.com/v4/spreadsheets/' + ultimateconfig['score_sheet_id'] + '/values/\'' + sheet_name + '\'!A1:Q50?key=' + ultimateconfig['api_key']);
+	req.send();
+}
+
 function publish_group_ranks() {
-	publish_group_rank('Group A');
-	publish_group_rank('Group B');
-	publish_group_rank('Group C');
+	get_group('Group A', function(response_a) {
+		get_group('Group B', function(response_b) {
+			get_group('Group C', function(response_c) {
+				publish_group_rank(response_a, 'Group A');
+				publish_group_rank(response_b, 'Group B');
+				publish_group_rank(response_c, 'Group C');
+
+				let replacements = montecarlo([response_a, response_b, response_c]);
+				let team_a = parse_teams_from_spreadsheet(response_a);
+				let team_b = parse_teams_from_spreadsheet(response_b);
+				let team_c = parse_teams_from_spreadsheet(response_c);
+				fill_playoff(replacements, [team_a, team_b, team_c]);
+			});
+		});
+	});
 }
 
 function get_ranked(response, group_name) {