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; } }