private void renewScoreBoard()
{
System.Diagnostics.Debug.WriteLine("renew score board");
if (i >= gamesAssigned.Count)
{
curGameLabel.Text = "已无可评分项目1" + i + ">=" + gamesAssigned.Count;
button1.Enabled = false;
return;
}
string curGameId = gamesAssigned[i];
string compQuery = String.Format("SELECT gameType,gender,ageGroup" +
" FROM competitions,games" +
" WHERE competitions.compId=games.compId" +
" AND games.gameId = '{0}'", gamesAssigned[i]);
NpgsqlCommand cmd = new NpgsqlCommand(compQuery);
cmd.Connection = npgSqlCon1;
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
curGameLabel.Text = "已无可评分项目2 " + i + ">=" + gamesAssigned.Count;
button1.Enabled = false;
reader.Close();
return;
}
reader.Read();
curGameLabel.Text = "当前项目:" + reader.GetString(0) + " 性别:" + (reader.GetValue(1)?.ToString() == "True" ? "男" : "女") +
" 年龄组:" + ProgramCore.ageRange(reader.GetValue(2)?.ToString());
reader.Close();
DataTable dt = new DataTable();
dt.Columns.Add("运动员号", typeof(string));
dt.Columns.Add("成绩", typeof(int));
curGameId = gamesAssigned[i];
string query = "SELECT athleteNo FROM participates WHERE tournamentId='" + curGameId + "'";
cmd = new NpgsqlCommand(query);
cmd.Connection = npgSqlCon1;
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
reader.Close();
i++;
renewScoreBoard();
return;
}
while (reader.Read())
{
DataRow row = dt.NewRow();
row["运动员号"] = reader.GetValue(0).ToString();
row["成绩"] = 0;
dt.Rows.Add(row);
}
reader.Close();
scoreBoard.DataSource = dt;
}
private void refreshSchedule_Click(object sender, EventArgs e)
{
string query = "SELECT gameType,gender,ageGroup,time,gameId" +
" FROM games,competitions" +
" WHERE games.compId = competitions.compId" +
" AND stage=true" +
" ORDER BY time ASC";
NpgsqlCommand cmd = new NpgsqlCommand(query);
cmd.Connection = npgsqlcon;
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Columns.Add("场次编号", typeof(string));
dt.Columns.Add("场次", typeof(string));
dt.Columns.Add("项目", typeof(string));
dt.Columns.Add("性别", typeof(string));
dt.Columns.Add("年龄组", typeof(string));
for (int i = 0; i < 55; i++)
{
occupied[i] = 0;
}
while (reader.Read())
{
DataRow row = dt.NewRow();
object[] objs = new object[5];
reader.GetValues(objs);
row["场次编号"] = objs[4]?.ToString();
row["项目"] = objs[0]?.ToString();
row["性别"] = (objs[1]?.ToString() == "True" ? "男" : "女");
row["年龄组"] = ProgramCore.ageRange(objs[2]?.ToString());
row["场次"] = objs[3].ToString();
occupied[int.Parse(objs[3].ToString())] = 1;
dt.Rows.Add(row);
}
scheduleGridView.DataSource = dt;
}
reader.Close();
}
//refresh results
private void tempButton_Click(object sender, EventArgs e)
{
// get rank from participates
//personal overall rank
string query = " WITH t AS(SELECT athleteNo, avg(finalScore) as score" +
" FROM participates,games" +
" WHERE tournamentId=gameId" +
" AND stage=" + !showsFinals.Checked +
" GROUP BY athleteNo)" +
" SELECT athlete.name,teamName,t.score" +
" FROM t,athlete,teams,games" +
" WHERE t.athleteNo = athlete.athleteNo" +
" AND athlete.identityNo = teams.memberIdentityNo";
NpgsqlCommand cmd = new NpgsqlCommand(query);
cmd.Connection = npgSqlCon;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Columns.Add("排名", typeof(int));
dt.Columns.Add("名字", typeof(string));
dt.Columns.Add("队伍", typeof(string));
dt.Columns.Add("积分", typeof(string));
int rank = 1;
DataRow row;
while (reader.Read())
{
row = dt.NewRow();
object[] objs = new object[3];
reader.GetValues(objs);
row["排名"] = rank++;
row["名字"] = objs[0]?.ToString();
row["积分"] = objs[2]?.ToString();
row["队伍"] = objs[1]?.ToString();
dt.Rows.Add(row);
}
personalRank.DataSource = dt;
}
reader.Close();
//team games rank
query = "WITH t AS(SELECT teamAccNo, gameId, compId, sum(finalScore) teamTotalScore" +
" FROM teams, athlete, participates, games" +
" WHERE teams.memberIdentityNo= athlete.identityNo" +
" AND athlete.athleteNo= participates.athleteNo" +
" AND participates.tournamentId= games.gameId" +
" AND games.stage=" + !showsFinals.Checked +
" GROUP BY teamAccNo, gameId)," +
" tName AS(SELECT accNo, teamName FROM teams GROUP BY accNo, teamName)" +
" SELECT teamName, gameType, gender, ageGroup, teamTotalScore" +
" FROM tName, t, competitions" +
" WHERE tName.accNo = t.teamAccNo" +
" AND t.compId = competitions.compId" +
" ORDER BY teamTotalScore DESC";
cmd = new NpgsqlCommand(query);
cmd.Connection = npgSqlCon;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Columns.Add("队伍", typeof(string));
dt.Columns.Add("项目", typeof(string));
dt.Columns.Add("性别", typeof(string));
dt.Columns.Add("年龄组", typeof(string));
dt.Columns.Add("积分", typeof(string));
while (reader.Read())
{
DataRow row = dt.NewRow();
object[] objs = new object[5];
reader.GetValues(objs);
row["队伍"] = objs[0]?.ToString();
row["项目"] = objs[1]?.ToString();
row["性别"] = ((objs[2]?.ToString() == "True")?"男":"女");
row["年龄组"] = ProgramCore.ageRange(objs[3]?.ToString());
row["积分"] = objs[4]?.ToString();
dt.Rows.Add(row);
}
teamGamesRank.DataSource = dt;
}
reader.Close();
//team overall rank
string query1 = " SELECT teamName,sum(finalScore) as s,teamAccNo" +
" FROM teams, athlete, participates,games" +
" WHERE teams.memberIdentityNo = athlete.identityNo" +
" AND athlete.athleteNo = participates.athleteNo" +
" AND tournamentId=gameId" +
" AND games.stage=" + !showsFinals.Checked +
" GROUP BY teamAccNo,teamName" +
" ORDER BY s DESC";
NpgsqlCommand cmd1 = new NpgsqlCommand(query1);
cmd1.Connection = npgSqlCon;
reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Columns.Add("排名", typeof(int));
dt.Columns.Add("队伍名字", typeof(string));
dt.Columns.Add("总积分", typeof(string));
int rank = 1;
while (reader.Read())
{
object[] objs = new object[2];
reader.GetValues(objs);
DataRow row;
row = dt.NewRow();
row["排名"] = rank++;
row["队伍名字"] = objs[0]?.ToString();
row["总积分"] = objs[1]?.ToString();
dt.Rows.Add(row);
}
teamOverallRank.DataSource = dt;
}
reader.Close();
}
private void refresh_Click(object sender, EventArgs e)
{
//赛程
string query = "SELECT gameType,gender,ageGroup,time,gameId" +
" FROM games,competitions" +
" WHERE games.compId = competitions.compId" +
" AND games.stage =" + !showFinals.Checked;
NpgsqlCommand cmd = new NpgsqlCommand(query);
cmd.Connection = npgSqlCon;
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Columns.Add("项目", typeof(string));
dt.Columns.Add("性别", typeof(string));
dt.Columns.Add("年龄组", typeof(string));
dt.Columns.Add("场次", typeof(int));
dt.Columns.Add("x", typeof(string));
while (reader.Read())
{
DataRow row = dt.NewRow();
object[] objs = new object[5];
reader.GetValues(objs);
row["项目"] = objs[0]?.ToString();
row["性别"] = (objs[1]?.ToString() == "true" ? "男" : "女");
row["年龄组"] = ProgramCore.ageRange(objs[2]?.ToString());
row["场次"] = objs[3].ToString();
row["x"] = objs[4].ToString();
dt.Rows.Add(row);
}
reader.Close();
finalsSchedule.DataSource = dt;
}
reader.Close();
//得出决赛运动员参赛信息
query = "SELECT athlete.name,teams.teamName, gameType, competitions.gender,ageGroup,time,gameId" +
" FROM athlete,teams,participates,games,competitions" +
" WHERE athlete.athleteNo = participates.athleteNo" +
" AND athlete.teamAccNo = teams.accNo" +
" AND participates.tournamentId = games.gameId" +
" AND games.compId = competitions.compId" +
" AND games.stage =" + !showFinals.Checked +
" ORDER BY time ASC," +
" teamName DESC";
cmd = new NpgsqlCommand(query);
cmd.Connection = npgSqlCon;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("队伍", typeof(string));
dt.Columns.Add("项目", typeof(string));
dt.Columns.Add("性别", typeof(string));
dt.Columns.Add("年龄组", typeof(string));
dt.Columns.Add("场次", typeof(string));
dt.Columns.Add("x", typeof(string));
while (reader.Read())
{
DataRow row = dt.NewRow();
object[] objs = new object[7];
reader.GetValues(objs);
row["姓名"] = objs[0]?.ToString();
row["队伍"] = objs[1]?.ToString();
row["项目"] = objs[2]?.ToString();
row["性别"] = (objs[3]?.ToString() == "true" ? "男" : "女");
row["年龄组"] = ProgramCore.ageRange(objs[4]?.ToString());
row["场次"] = objs[5]?.ToString();
row["x"] = objs[6]?.ToString();
dt.Rows.Add(row);
}
finalsParticipation.DataSource = dt;
}
else
{
noticeLab.Text = "比赛信息暂未公布";
}
reader.Close();
}