Beispiel #1
0
        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();
        }
Beispiel #3
0
        //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();
        }