private void btnNxtRound_Click(object sender, EventArgs e)
{
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
//NewStartList nl;
string nxtRound;
int routeN;
if (boulder)
{
if (NextRoundForm.GetNextRoundData(this.round, this, out nxtRound, out routeN))
{
return;
}
}
else
{
if (cbRound.SelectedIndex < 0 || cbRound.SelectedItem == null)
{
MessageBox.Show("Раунд не выбран");
return;
}
routeN = -1;
nxtRound = cbRound.SelectedItem.ToString();
}
int nextID;
//bool b;
SqlCommand cmd = new SqlCommand("SELECT l.iid FROM lists l, lists l1 WHERE " +
"(l.style=l1.style) AND (l.group_id=l1.group_id) AND (l1.iid=" + listID.ToString() +
") AND (l.round = '" + nxtRound + "')", cn);
try { nextID = Convert.ToInt32(cmd.ExecuteScalar()); }
catch { nextID = 0; }
if (nextID > 0)
{
DialogResult dgr = MessageBox.Show("Такой протокол уже существует.\nЗаменить его?",
"Протокол существует", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dgr == DialogResult.No)
{
return;
}
cmd.CommandText = "DELETE FROM lists WHERE iid=" + nextID.ToString();
try { cmd.ExecuteNonQuery(); }
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
else
{
nextID = (int)StaticClass.GetNextIID("lists", cn, "iid", null);
}
List <StartListMember> starters = new List <StartListMember>();
DataTable dtTmp = new DataTable();
Random rnd = new Random();
SqlDataAdapter da = new SqlDataAdapter();
if (boulder)
{
da.SelectCommand = new SqlCommand("SELECT r.climber_id,r.pos,p.rankingBoulder AS rank, r.start FROM " +
"boulderResults r(NOLOCK) JOIN lists l(NOLOCK) ON l.iid = r.list_id JOIN Participants p(NOLOCK) ON " +
"r.climber_id = p.iid JOIN lists l2(NOLOCK) ON (l.group_id=l2.group_id) AND (l.style=l2.style) " +
"WHERE ((l.round = 'Квалификация Группа А') OR (l.round = 'Квалификация Группа Б')) AND " +
"(l2.iid = " + listID.ToString() + ") AND (r.qf = 'Q' OR r.preQf = 1) ORDER BY r.start", cn);
}
else
{
da.SelectCommand = new SqlCommand("SELECT r.climber_id,r.pos,p.rankingLead AS rank, r.start FROM " +
"routeResults r(NOLOCK) JOIN lists l(NOLOCK) ON l.iid = r.list_id JOIN Participants p(NOLOCK) ON " +
"r.climber_id = p.iid JOIN lists l2(NOLOCK) ON (l.group_id=l2.group_id) AND (l.style=l2.style) " +
"WHERE ((l.round = '1/4 финала Трасса 1') OR (l.round = '1/4 финала Трасса 2')) AND " +
"(l2.iid = " + listID.ToString() + ") AND (r.qf = 'Q' OR r.preQf = 1) ORDER BY r.start", cn);
}
da.Fill(dtTmp);
List <Starter> data = new List <Starter>();
foreach (DataRow rdd in dtTmp.Rows)
{
StartListMember stm = new StartListMember();
Starter strt = new Starter();
stm.iid = Convert.ToInt32(rdd["climber_id"]);
stm.prevPos = Convert.ToInt32(rdd["pos"]);
try { stm.ranking = Convert.ToInt32(rdd["rank"]); }
catch { stm.ranking = int.MaxValue; }
stm.rndDouble = rnd.NextDouble();
starters.Add(stm);
strt.iid = stm.iid;
strt.lateAppl = false;
strt.prevPos = stm.prevPos;
try { strt.prevStart = Convert.ToInt32(rdd["start"]); }
catch { strt.prevStart = 0; }
strt.random = stm.rndDouble;
strt.ranking = stm.ranking;
data.Add(strt);
//if (rdd["Кв."].ToString() == "Q")
//{
// StartListMember stm;
// stm.iid = Convert.ToInt32(rdd["№"]);
// stm.prevPos = Convert.ToInt32(rdd["pos"]);
// //if (rdd["Трасса 1"].ToString() == "")
// // stm.prevPos += 2000;
// //else
// // stm.prevPos += 1000;
// stm.ranking = 9999;
// stm.rndDouble = rnd.NextDouble();
// stNum.Add(stm);
//}
//else
// break;
}
cmd.Parameters.Add("@i", SqlDbType.Int);
cmd.Parameters[0].Value = listID;
//foreach (StartListMember stm in stNum)
//{
// StartListMember stmQ;
// stmQ.iid = stm.iid;
// stmQ.prevPos = stm.prevPos;
// cmd.CommandText = "SELECT rankingLead FROM Participants WHERE iid = " + stm.iid.ToString();
// try { stmQ.ranking = Convert.ToInt32(cmd.ExecuteScalar()); }
// catch { stmQ.ranking = 9999; }
// stmQ.rndDouble = stm.rndDouble;
// starters.Add(stmQ);
//}
cmd.CommandText = "SELECT g.name, g.iid FROM lists l INNER JOIN Groups g ON l.group_id=g.iid WHERE l.iid=@i";
SqlDataReader rd = cmd.ExecuteReader();
int group_id = 0;
string group_name = "";
while (rd.Read())
{
group_id = Convert.ToInt32(rd[1]);
group_name = rd[0].ToString();
break;
}
rd.Close();
//int nextID = (int)StaticMethods.GetNextIID("lists", cn);
try
{
cmd.Transaction = cn.BeginTransaction();
cmd.CommandText = "INSERT INTO lists(iid,group_id,style,round,online,routeNumber,listType) VALUES (@i,@g,@s,@r,@o,@rn,@ltp)";
cmd.Parameters[0].Value = nextID;
cmd.Parameters.Add("@g", SqlDbType.Int);
cmd.Parameters[1].Value = group_id;
cmd.Parameters.Add("@s", SqlDbType.VarChar);
if (boulder)
{
cmd.Parameters[2].Value = "Боулдеринг";
}
else
{
cmd.Parameters[2].Value = "Трудность";
}
cmd.Parameters.Add("@r", SqlDbType.VarChar);
cmd.Parameters[3].Value = nxtRound;
cmd.Parameters.Add("@o", SqlDbType.Bit);
cmd.Parameters[4].Value = StaticClass.IsListOnline(listID, cn, cmd.Transaction);
cmd.Parameters.Add("@rn", SqlDbType.Int);
cmd.Parameters[5].Value = (routeN > 0 ? routeN : (object)DBNull.Value);
cmd.Parameters.Add("@ltp", SqlDbType.VarChar, 255);
cmd.Parameters[6].Value = ((boulder && nxtRound.ToLower() != "суперфинал") ? ListTypeEnum.BoulderSimple : ListTypeEnum.LeadSimple).ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE lists SET next_round=@i WHERE iid=@g";
cmd.Parameters[1].Value = listID;
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE lists SET prev_round=@g WHERE iid=@i";
cmd.ExecuteNonQuery();
//if (boulder)
// nl = new NewStartList(false, group_name, "Боулдеринг", cbRound.SelectedItem.ToString());
//else
// nl = new NewStartList(false, group_name, "Трудность", cbRound.SelectedItem.ToString());
//nl.ShowDialog();
Sorting sorting = new Sorting(data, StartListMode.NotFirstRound);
sorting.ShowDialog();
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
if (sorting.Cancel)
//if (nl.type == StartListType.Cancel)
{
try { cmd.Transaction.Rollback(); }
catch
{
cmd.CommandText = "DELETE FROM lists WHERE iid = " + nextID.ToString();
cmd.ExecuteNonQuery();
}
return;
}
#region OLD
/* //bool b;
* do
* {
* switch (nl.type)
* {
* case StartListType.RankBased:
* starters.Sort(new RankingComparer());
* starters.Reverse();
* b = false;
* break;
* case StartListType.General:
* starters.Sort(new RandomListComparer());
* b = false;
* break;
* case StartListType.Reverse:
* starters.Sort(new NextRoundComparer());
* b = false;
* break;
* default:
* MessageBox.Show("Тип жеребьёвки неподдерживается. Выберите другой тип.");
* nl.ShowDialog();
* b = true;
* break;
* }
* } while (b);*/
#endregion
long nxtIID;
string taleName;
if (boulder)
{
taleName = "boulderResults";
}
else
{
taleName = "routeResults";
}
cmd.CommandText = "SELECT MAX(iid) FROM " + taleName;
try { nxtIID = Convert.ToInt64(cmd.ExecuteScalar()); }
catch { nxtIID = 1; }
cmd.Parameters.Clear();
cmd.Parameters.Add("@list_id", SqlDbType.Int);
cmd.Parameters[0].Value = nextID;
cmd.Parameters.Add("@iid", SqlDbType.BigInt);
cmd.Parameters.Add("@climber_id", SqlDbType.Int);
cmd.Parameters.Add("@start", SqlDbType.Int);
cmd.Parameters.Add("@pos", SqlDbType.Int);
cmd.Parameters[4].Value = int.MaxValue;
cmd.CommandText = "INSERT INTO " + taleName + "(iid, list_id, climber_id, start, pos) " +
"VALUES (@iid, @list_id, @climber_id, @start, @pos)";
int i = 0;
foreach (Starter stss in sorting.Starters)
//for (int i = 0; i < sorting.Starters.Count; i++)
{
cmd.Parameters[1].Value = (++nxtIID);
cmd.Parameters[2].Value = /*((StartListMember)starters[i]).iid*/ stss.iid;
cmd.Parameters[3].Value = ++i;
cmd.ExecuteNonQuery();
}
MessageBox.Show("Протокол создан. iid = " + nextID.ToString());
cmd.Transaction.Commit();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
try { cmd.Transaction.Rollback(); }
catch
{
cmd.CommandText = "DELETE FROM lists WHERE iid=" + nextID.ToString();
try { cmd.ExecuteNonQuery(); }
catch { }
}
//cmd.Transaction.Rollback();
return;
}
}
private void btnNxtRound_Click(object sender, EventArgs e)
{
//NewStartList nl;
if (cbRound.SelectedIndex < 0)
{
MessageBox.Show("����� �� ������");
return;
}
int nextID;
//bool b = false;
SqlCommand cmd = new SqlCommand("SELECT l.iid FROM lists l(NOLOCK), lists l1(NOLOCK) WHERE " +
"(l.style=l1.style) AND (l.group_id=l1.group_id) AND (l1.iid=" + listID.ToString() +
") AND (l.round = '" + cbRound.SelectedItem.ToString() + "')", cn);
try { nextID = Convert.ToInt32(cmd.ExecuteScalar()); }
catch { nextID = 0; }
if (nextID > 0)
{
DialogResult dgr = MessageBox.Show("����� �������� ��� ����������.\n�������� ���?",
"�������� ����������", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dgr == DialogResult.No)
{
return;
}
//b = true;
}
else
{
nextID = (int)StaticClass.GetNextIID("lists", cn, "iid", null);
}
DataTable dtTmp = StaticClass.FillResFlash(listID, cn, false, out routeNumber, true, out timeLists, out lists);
Random rnd = new Random();
cmd.CommandText = "SELECT rankingLead, lateAppl FROM Participants P(NOLOCK) WHERE iid = @iid";
cmd.Parameters.Add("@iid", SqlDbType.Int);
List <Starter> data = new List <Starter>();
foreach (DataRow rdd in dtTmp.Rows)
{
if (rdd["��."].ToString() == "Q")
{
Starter st = new Starter();
st.iid = Convert.ToInt32(rdd["�"]);
st.prevPos = Convert.ToInt32(rdd["pos"]);
st.prevStart = 1;
st.random = rnd.NextDouble();
st.ranking = int.MaxValue;
st.lateAppl = false;
cmd.Parameters[0].Value = st.iid;
try
{
SqlDataReader rdss = cmd.ExecuteReader();
while (rdss.Read())
{
try { st.lateAppl = (bool)rdss["lateAppl"]; }
catch { }
try { st.ranking = (int)rdss["rankingLead"]; }
catch { }
break;
}
rdss.Close();
}
catch { }
data.Add(st);
}
else
{
break;
}
}
//cmd.CommandText = "SELECT LQ.iid " +
// " FROM lists LQ(NOLOCK) " +
// " JOIN lists LF(NOLOCK) ON LQ.style = LF.style " +
// " AND LQ.group_id = LF.group_id " +
// " AND LQ.round = '������������ 1'" +
// " WHERE LF.iid = " + listID.ToString();
try
{
cmd.CommandText = "SELECT P.iid, P.lateAppl, P.rankingLead " +
" FROM Participants P(NOLOCK) " +
" JOIN routeResults R(NOLOCK) ON R.climber_id = P.iid " +
" JOIN lists L(NOLOCK) ON L.iid = R.list_id " +
" WHERE L.iid_parent = " + listID.ToString() +
" AND R.preQf = 1 ";
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Starter s = new Starter();
s.iid = Convert.ToInt32(rdr["iid"]);
bool exists = false;
foreach (Starter st in data)
{
if (st.iid == s.iid)
{
exists = true;
break;
}
}
if (!exists)
{
s.lateAppl = Convert.ToBoolean(rdr["lateAppl"]);
s.prevPos = 0;
s.prevStart = 0;
s.random = rnd.NextDouble();
try { s.ranking = Convert.ToInt32(rdr["rankingLead"]); }
catch { s.ranking = int.MaxValue; }
data.Add(s);
}
}
rdr.Close();
}
catch { }
//foreach(StartListMember stm in stNum)
//{
// StartListMember stmQ;
// stmQ.iid = stm.iid;
// stmQ.prevPos = stm.prevPos;
// cmd.CommandText = "SELECT rankingLead FROM Participants WHERE iid = " + stm.iid.ToString();
// try { stmQ.ranking = Convert.ToInt32(cmd.ExecuteScalar()); }
// catch { stmQ.ranking = 9999; }
// stmQ.rndDouble = stm.rndDouble;
// starters.Add(stmQ);
//}
cmd.Parameters.Clear();
cmd.Parameters.Add("@i", SqlDbType.Int);
cmd.Parameters[0].Value = listID;
cmd.CommandText = "SELECT g.name, g.iid FROM lists l INNER JOIN Groups g ON l.group_id=g.iid WHERE l.iid=@i";
SqlDataReader rd = cmd.ExecuteReader();
int group_id = 0;
string group_name = "";
while (rd.Read())
{
group_id = Convert.ToInt32(rd[1]);
group_name = rd[0].ToString();
break;
}
rd.Close();
//int nextID = (int)StaticMethods.GetNextIID("lists", cn);
ListTypeEnum newListType = ListTypeEnum.LeadSimple;
List <int> childLists = new List <int>();
try
{
//cmd.Transaction = cn.BeginTransaction();
cmd.Transaction = cn.BeginTransaction();
cmd.CommandText = "INSERT INTO lists(iid,group_id,style,round,online) VALUES (@i,@g,@s,@r,@o)";
cmd.Parameters[0].Value = nextID;
cmd.Parameters.Add("@g", SqlDbType.Int);
cmd.Parameters[1].Value = group_id;
cmd.Parameters.Add("@s", SqlDbType.VarChar);
cmd.Parameters[2].Value = "���������";
cmd.Parameters.Add("@r", SqlDbType.VarChar);
cmd.Parameters[3].Value = cbRound.SelectedItem.ToString();
cmd.Parameters.Add("@o", SqlDbType.Bit);
cmd.Parameters[4].Value = StaticClass.IsListOnline(listID, cn, cmd.Transaction);
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE lists SET next_round=@i WHERE iid=@g";
cmd.Parameters[1].Value = listID;
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE lists SET prev_round=@g WHERE iid=@i";
cmd.ExecuteNonQuery();
//nl = new NewStartList(false, group_name, "���������", cbRound.SelectedItem.ToString());
//nl.ShowDialog();
Sorting sorting = new Sorting(data, StartListMode.NotFirstRound, true);
sorting.ShowDialog();
if (sorting.Cancel)
//if (nl.type == StartListType.Cancel)
{
//cmd.Transaction.Rollback();
try { cmd.Transaction.Rollback(); }
catch
{
cmd.CommandText = "DELETE FROM lists WHERE iid = " + nextID.ToString();
try { cmd.ExecuteNonQuery(); }
catch { }
}
return;
}
cmd.Parameters.Clear();
cmd.CommandText = "UPDATE lists SET listType = @ltp WHERE iid = " + nextID.ToString();
cmd.Parameters.Add("@ltp", SqlDbType.VarChar, 255);
newListType = sorting.RoundFlash ? ListTypeEnum.LeadFlash : ListTypeEnum.LeadSimple;
cmd.Parameters[0].Value = newListType.ToString();
cmd.ExecuteNonQuery();
if (newListType == ListTypeEnum.LeadFlash)
{
cmd.CommandText = "UPDATE lists SET routeNumber = " + sorting.SettedRouteNumber.ToString() +
" WHERE iid = " + nextID.ToString();
cmd.ExecuteNonQuery();
for (int i = 1; i <= sorting.SettedRouteNumber; i++)
{
cmd.Parameters.Clear();
int curId = (int)StaticClass.GetNextIID("lists", cn, "iid", cmd.Transaction);
cmd.CommandText = "INSERT INTO lists(iid,group_id,style,round,online,listType,iid_parent) VALUES (@i,@g,@s,@r,@o,@lt,@ip)";
cmd.Parameters.Add("@i", SqlDbType.Int);
cmd.Parameters[0].Value = curId;
childLists.Add(curId);
cmd.Parameters.Add("@g", SqlDbType.Int);
cmd.Parameters[1].Value = group_id;
cmd.Parameters.Add("@s", SqlDbType.VarChar);
cmd.Parameters[2].Value = "���������";
cmd.Parameters.Add("@r", SqlDbType.VarChar);
cmd.Parameters[3].Value = cbRound.SelectedItem.ToString() + " ������ " + i.ToString();
cmd.Parameters.Add("@o", SqlDbType.Bit);
cmd.Parameters[4].Value = StaticClass.IsListOnline(listID, cn, cmd.Transaction);
cmd.Parameters.Add("@lt", SqlDbType.VarChar, 255);
cmd.Parameters[5].Value = ListTypeEnum.LeadSimple.ToString();
cmd.Parameters.Add("@ip", SqlDbType.Int);
cmd.Parameters[6].Value = nextID;
cmd.ExecuteNonQuery();
}
}
//bool b;
/*
* do
* {
* switch (nl.type)
* {
* case StartListType.General:
* starters.Sort(new RandomListComparer());
* b = false;
* break;
* case StartListType.Reverse:
* starters.Sort(new NextRoundComparer());
* b = false;
* break;
* case StartListType.Cancel:
* cmd.Transaction.Rollback();
* return;
* default:
* MessageBox.Show("��� ���������� ����������������. �������� ������ ���.");
* nl.ShowDialog();
* b = true;
* break;
* }
* } while (b);*/
//sorting.Starters
if (newListType == ListTypeEnum.LeadSimple)
{
childLists.Clear();
childLists.Add(nextID);
}
for (int i = 0; i < sorting.Count && i < childLists.Count; i++)
{
InsertToList(childLists[i], sorting[i], cn, cmd.Transaction);
}
cmd.Transaction.Commit();
MessageBox.Show("�������� ������. iid = " + nextID.ToString());
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
try { cmd.Transaction.Rollback(); }
catch { }
return;
}
}
