public async Task <bool> DeleteStartListMember(int raceId, int skierId, int runNo)
{
return(await Task.Run(() =>
{
var startlistAdo = new AdoStartListDao(connectionFactory);
var raceDataAdo = new AdoRaceDataDao(connectionFactory);
var racedata = raceDataAdo.FindAllBySkierId(skierId).FirstOrDefault(rd => rd.RaceId == raceId);
if (!raceDataAdo.Delete(racedata))
{
return false;
}
var race = new Race
{
Id = raceId
};
var startList = new StartListMember
{
Race = race,
SkierId = skierId,
RunNo = runNo
};
return startlistAdo.Delete(startList);
}));
}
public bool Delete(StartListMember startListMember)
{
return(template.Execute(
@"delete from startlist where raceId=@raceId and skierId=@skierId and runNo=@runNo",
new QueryParameter("@raceId", startListMember.Race.Id),
new QueryParameter("@skierId", startListMember.SkierId),
new QueryParameter("@runNo", startListMember.RunNo)) == 1);
}
public bool Update(StartListMember startListMember)
{
return(template.Execute(
@"update startlist set startpos=@startpos where raceId=@raceId and skierId=@skierId and runNo=@runNo",
new QueryParameter("@raceId", startListMember.Race.Id),
new QueryParameter("@skierId", startListMember.SkierId),
new QueryParameter("@runNo", startListMember.RunNo),
new QueryParameter("@startpos", startListMember.StartPos)) == 1);
}
public int Insert(StartListMember startListMember)
{
return(template.Execute(
@"insert into startlist(raceId, skierId, runNo, startpos) values (@raceId, @skierId, @runNo,@startpos); SELECT last_insert_rowid();",
new QueryParameter("@raceId", startListMember.Race.Id),
new QueryParameter("@skierId", startListMember.SkierId),
new QueryParameter("@runNo", startListMember.RunNo),
new QueryParameter("@startpos", startListMember.StartPos)));
}
public async Task <bool> IsStartListMemberInStartList(int raceId, int skierId, int runNo)
{
return(await Task.Run(() => {
var startListAdo = new AdoStartListDao(connectionFactory);
StartListMember startListMemberMember = startListAdo.FindByIds(raceId, skierId, runNo);
if (startListMemberMember != null)
{
return true;
}
return false;
}));
}
public void InsertTest()
{
var startlist = new StartListMember
{
Race = new Race {
Id = 14
},
SkierId = 44,
StartPos = 2,
RunNo = 1
};
Assert.True(startlistDao.Insert(startlist) > 0);
}
private void GenerateStartLists()
{
int i;
IList <Race> races = new List <Race>(adoRaceDao.FindAll());
foreach (var race in races)
{
i = 1;
while (i < GetNumberOfStarters() + 1)
{
StartListMember startListMember = new StartListMember {
Race = race
};
startListMember.SkierId = GetNewRandomSkierIdForRace(race);
startListMember.StartPos = i;
StartLists.Add(startListMember);
i++;
}
}
}
public async Task <bool> UpdateStartListMemberStartPosition(int raceId, int skierId, int runNo, int startPosition)
{
return(await Task.Run(() =>
{
var startlistAdo = new AdoStartListDao(connectionFactory);
var race = new Race
{
Id = raceId
};
var startList = new StartListMember
{
Race = race,
SkierId = skierId,
RunNo = runNo,
StartPos = startPosition
};
var result = startlistAdo.Update(startList);
return result;
}));
}
public async Task <bool> InsertStartListMember(int raceId, int skierId, int runNo, int startPosition)
{
return(await Task.Run(() =>
{
var startListAdo = new AdoStartListDao(connectionFactory);
var raceDataAdo = new AdoRaceDataDao(connectionFactory);
var oldRaceData = raceDataAdo.FindAllBySkierId(skierId).FirstOrDefault(rd => rd.RaceId == raceId);
if (oldRaceData != null)
{
raceDataAdo.Delete(oldRaceData);
}
var newRaceData = new RaceData()
{
RaceId = raceId,
SkierId = skierId,
Blocked = true,
Disqualified = false,
Finished = false,
Running = false
};
if (raceDataAdo.Insert(newRaceData) == 0)
{
return false;
}
var race = new Race
{
Id = raceId
};
var startListMember = new StartListMember
{
Race = race,
SkierId = skierId,
RunNo = runNo,
StartPos = startPosition
};
return startListAdo.Insert(startListMember) != 0;
}));
}
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;
}
}
public static StartListSkierOutDto FromSkierRaceDataAndStartListMember(Skier skier, RaceData raceData, StartListMember startListMember)
{
return(new StartListSkierOutDto()
{
Blocked = raceData.Blocked,
Running = raceData.Running,
Disqualified = raceData.Disqualified,
Finished = raceData.Finished,
Startposition = startListMember.StartPos,
SkierId = skier.Id,
FirstName = skier.FirstName,
LastName = skier.LastName,
Nation = skier.Nation,
ProfileImage = skier.ProfileImage
});
}
