public static TeamResult selectResultsType(SqlConnection cn, int list_id, string competitionTitle)
{
SelectTeamType tm = new SelectTeamType(cn, list_id, competitionTitle);
tm.ShowDialog();
return(tm.tRes);
}
public TeamResults(SqlConnection baseCon, int list_id, string competitionTitle)
: base(baseCon, competitionTitle)
{
//try
//{
// if (cn.State != ConnectionState.Open)
// cn.Open();
//}
//catch { }
InitializeComponent();
this.Text = "Командные результаты";
//this.cn = cn;
this.list_id = list_id;
TeamResult tmResType = SelectTeamType.selectResultsType(this.cn, list_id, competitionTitle);
if (tmResType.type == TeamResultsType.Cancel)
{
return;
}
List <team> teams = new List <team>();
List <group> groups = new List <group>();
if (tmResType.lead)
{
styles.Add("Трудность");
}
if (tmResType.speed)
{
styles.Add("Скорость");
}
if (tmResType.boulder)
{
styles.Add("Боулдеринг");
}
if (styles.Count == 0)
{
return;
}
SqlCommand cmd = new SqlCommand("SELECT iid,name FROM teams(NOLOCK) ORDER BY name", this.cn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
team t;
t.iid = Convert.ToInt32(rdr[0]);
t.name = rdr[1].ToString();
teams.Add(t);
}
rdr.Close();
cmd.CommandText = "DELETE FROM teamResults WHERE list_id = " + list_id.ToString();
try { cmd.ExecuteNonQuery(); }
catch
{
cmd.CommandText = "CREATE TABLE [dbo].[teamResults] (" +
" [list_id] [int] NOT NULL ," +
" [team_id] [int] NOT NULL ," +
" [pos] [int] NULL ," +
" CONSTRAINT [PK_teamResults] PRIMARY KEY CLUSTERED " +
" (" +
" [list_id],"+
" [team_id]"+
" ) ON [PRIMARY] ," +
" CONSTRAINT [FK_teamResults_lists] FOREIGN KEY " +
" (" +
" [list_id]"+
" ) REFERENCES [dbo].[lists] (" +
" [iid]"+
" ) ON DELETE CASCADE ON UPDATE CASCADE ," +
" CONSTRAINT [FK_teamResults_Teams] FOREIGN KEY " +
" (" +
" [team_id]"+
" ) REFERENCES [dbo].[Teams] (" +
" [iid]"+
" ) ON DELETE CASCADE ON UPDATE CASCADE " +
")";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT iid,genderFemale FROM groups(NOLOCK) ORDER BY name";
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
group gr;
gr.iid = Convert.ToInt32(rdr[0]);
gr.female = Convert.ToBoolean(rdr[1]);
groups.Add(gr);
}
rdr.Close();
cmd.Parameters.Clear();
cmd.Parameters.Add("@q2nd", SqlDbType.Float);
cmd.Parameters[0].Value = tmResType.Qf2nd;
cmd.Parameters.Add("@teamID", SqlDbType.Int);
cmd.Parameters.Add("@style", SqlDbType.VarChar, 255);
foreach (team tm in teams)
{
teamRes tRes = new teamRes();
tRes.res = 0;
tRes.styleResults = new List <styleRes>();
tRes.pos = tm.iid;
tRes.name = tm.name;
tRes.iid = tm.iid;
cmd.Parameters[1].Value = tm.iid;
foreach (string stle in styles)
{
styleRes stRes = new styleRes();
stRes.style = stle;//и g.iid и tm.iid
stRes.res = 0;
cmd.Parameters[2].Value = stle;
stRes.dt = new DataTable();
if (tmResType.type == TeamResultsType.Group)
{
if (cmd.Parameters.Count < 4)
{
cmd.Parameters.Add("@groupID", SqlDbType.Int);
}
foreach (group g in groups)
{
cmd.Parameters[3].Value = g.iid;
if (g.female)
{
cmd.CommandText =
"SELECT TOP " + tmResType.nFemale.ToString() + " p.iid AS [№], " +
"p.surname+' '+p.name AS [Фамилия, Имя], t.name AS [Команда]," +
" (CASE p.team_id WHEN @teamID THEN 1.0 ELSE @q2nd END) * gr.pts AS [Балл] " +
" FROM lists l(NOLOCK)" +
" JOIN generalResults gr(NOLOCK) ON l.iid = gr.list_id" +
" JOIN Participants p(NOLOCK) ON gr.climber_id = p.iid" +
" LEFT JOIN teamsLink tl(nolock) on tl.climber_id = p.iid" +
" and 1 = CASE p.team_id WHEN @teamID THEN 0 ELSE 1 END" +
" JOIN Teams t(NOLOCK) ON t.iid = ISNULL(tl.team_id, p.team_id) " +
" WHERE l.style = @style" +
" AND t.iid = @teamID" +
" AND l.group_id = @groupID" +
" AND gr.pts > 0" +
" ORDER BY gr.pts DESC";
}
else
{
cmd.CommandText =
"SELECT TOP " + tmResType.nMale.ToString() + " p.iid AS [№], " +
" p.surname+' '+p.name AS [Фамилия, Имя], t.name AS [Команда]," +
" (CASE p.team_id WHEN @teamID THEN 1.0 ELSE @q2nd END) * gr.pts AS [Балл] " +
" FROM lists l(NOLOCK)" +
" JOIN generalResults gr(NOLOCK) ON l.iid=gr.list_id" +
" JOIN Participants p(NOLOCK) ON gr.climber_id=p.iid" +
" LEFT JOIN teamsLink tl(nolock) on tl.climber_id = p.iid" +
" and 1 = CASE p.team_id WHEN @teamID THEN 0 ELSE 1 END" +
" JOIN Teams t(NOLOCK) ON t.iid = ISNULL(tl.team_id, p.team_id)" +
" WHERE l.style = @style" +
" AND t.iid = @teamID" +
" AND l.group_id = @groupID" +
" AND gr.pts > 0" +
" ORDER BY gr.pts DESC";
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(stRes.dt);
}
}
else
{
cmd.CommandText =
"SELECT TOP " + tmResType.nMale.ToString() + " p.iid AS [№], " +
" p.surname+' '+p.name AS [Фамилия, Имя], t.name AS [Команда]," +
" (CASE p.team_id WHEN @teamID THEN 1.0 ELSE @q2nd END) * gr.pts AS [Балл] " +
" FROM lists l" +
" JOIN generalResults gr(NOLOCK) ON l.iid = gr.list_id" +
" JOIN Participants p(NOLOCK) ON gr.climber_id = p.iid" +
" LEFT JOIN teamsLink tl(nolock) ON tl.climber_id = p.iid" +
" AND 1 = CASE p.team_id WHEN @teamID THEN 0 ELSE 1 END" +
" JOIN Teams t(NOLOCK) ON t.iid = ISNULL(tl.team_id, p.team_id) " +
" WHERE l.style = @style" +
" AND t.iid = @teamID" +
" AND gr.pts > 0" +
" ORDER BY gr.pts DESC";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(stRes.dt);
}
foreach (DataRow dr in stRes.dt.Rows)
{
stRes.res += Math.Round(Convert.ToDouble(dr["Балл"]), 2);
}
tRes.styleResults.Add(stRes);
}
foreach (styleRes str in tRes.styleResults)
{
tRes.res += str.res;
}
generalResults.Add(tRes);
}
TeamComparer tc = new TeamComparer();
generalResults.Sort(tc);
dtRes.Columns.Add("Фамилия, Имя", typeof(string));
dtRes.Columns.Add("Балл", typeof(double));
dtRes.Columns.Add("Команда", typeof(string));
foreach (teamRes tr in generalResults)
{
foreach (styleRes sr in tr.styleResults)
{
foreach (DataRow dr in sr.dt.Rows)
{
object[] obj = new object[3];
obj[0] = dr["Фамилия, Имя"].ToString();
int i = (int)(Convert.ToDouble(dr["Балл"]) * 100);
double d = (double)i / 100.0;
obj[1] = d;
obj[2] = tr.name;
dtRes.Rows.Add(obj);
}
}
}
dg.DataSource = dtRes;
if (generalResults.Count < 1)
{
return;
}
teamRes ttRes = (teamRes)generalResults[0];
double ptsCur = ttRes.res;
ttRes.pos = 1;
generalResults[0] = ttRes;
int curPos = 1;
for (int i = 1; i < generalResults.Count; i++)
{
ttRes = (teamRes)generalResults[i];
if (ttRes.res != ptsCur)
{
ttRes.pos = (i + 1);
curPos = ttRes.pos;
ptsCur = ttRes.res;
}
else
{
ttRes.pos = curPos;
}
generalResults[i] = ttRes;
}
cmd.CommandText = "INSERT INTO teamResults(list_id, team_id, pos) VALUES (" + list_id.ToString() + ", @t, @p)";
cmd.Parameters.Clear();
cmd.Parameters.Add("@t", SqlDbType.Int);
cmd.Parameters.Add("@p", SqlDbType.Int);
foreach (teamRes t in generalResults)
{
cmd.Parameters[0].Value = t.iid;
cmd.Parameters[1].Value = t.pos;
cmd.ExecuteNonQuery();
}
}