private NonQueryResult ExecuteNonQuery(string commandText, bool enforceForeignKeys = false)
{
NonQueryResult result = new NonQueryResult()
{
CommandText = commandText,
EnforceForeignKeys = enforceForeignKeys
};
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
SQLiteCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
if (enforceForeignKeys)
{
command.CommandText = EnforceForeignKeys;
command.ExecuteNonQuery();
}
command.CommandText = commandText;
command.ExecuteNonQuery();
result.Completed = true;
}
}
catch (Exception ex)
{
result.Exception = ex;
}
return(result);
}
/// <summary>
/// Create the schema for the Institutions table - a simple lookup for institutions
/// </summary>
private bool CreateInstitutionsTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE institution (");
query.AppendLine("institution_id TEXT(36) NOT NULL,");
query.AppendLine("institution_name TEXT(100) NOT NULL,");
query.AppendLine("PRIMARY KEY (institution_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
/// <summary>
/// Creates the schema for the Venues table - a simple lookup for venues
/// </summary>
private bool CreateVenuesTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE venue (");
query.AppendLine("venue_id TEXT(36) NOT NULL,");
query.AppendLine("venue_name TEXT(100) NOT NULL,");
query.AppendLine("special_needs_venue BOOLEAN NOT NULL,");
query.AppendLine("active BOOLEAN NOT NULL,");
query.AppendLine("PRIMARY KEY (venue_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
/// <summary>
/// Creates the schema for the Rounds table - a simple lookup for rounds
/// </summary>
private bool CreateRoundsTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE round (");
query.AppendLine("round_id TEXT(36) NOT NULL,");
query.AppendLine("round_number INTEGER NOT NULL,");
query.AppendLine("motion TEXT(500) NOT NULL,");
query.AppendLine("is_random BOOLEAN NOT NULL,");
query.AppendLine("PRIMARY KEY (round_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
/// <summary>
/// Create the schema for the Debates table - a simple lookup for debates
/// </summary>
private bool CreateDebatesTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE debate (");
query.AppendLine("debate_id TEXT(36) NOT NULL,");
query.AppendLine("round_id TEXT(36) NOT NULL,");
query.AppendLine("venue_id TEXT(36) NOT NULL,");
query.AppendLine("FOREIGN KEY (round_id) REFERENCES round(round_id),");
query.AppendLine("FOREIGN KEY (venue_id) REFERENCES venue(venue_id),");
query.AppendLine("PRIMARY KEY (debate_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
/// <summary>
/// Create the schema for the Speakers table - a simple lookup for speakers
/// </summary>
private bool CreateSpeakersTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE speaker (");
query.AppendLine("speaker_id TEXT(36) NOT NULL,");
query.AppendLine("speaker_name TEXT(100) NOT NULL,");
query.AppendLine("institution_id TEXT(36) NOT NULL,");
query.AppendLine("special_needs BOOLEAN NOT NULL,");
query.AppendLine("active BOOLEAN NOT NULL,");
query.AppendLine("FOREIGN KEY (institution_id) REFERENCES institution(institution_id),");
query.AppendLine("PRIMARY KEY (speaker_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
/// <summary>
/// Create the schema for the JudgesDraw table - a simple lookup for judge draws
/// </summary>
private bool CreateJudgesDrawTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE judge_draw (");
query.AppendLine("draw_id TEXT(36) NOT NULL,");
query.AppendLine("judge_id TEXT(36) NOT NULL,");
query.AppendLine("debate_id TEXT(36) NOT NULL,");
query.AppendLine("number INTEGER NOT NULL,");
query.AppendLine("FOREIGN KEY (judge_id) REFERENCES judge(judge_id),");
query.AppendLine("FOREIGN KEY (debate_id) REFERENCES debate(debate_id),");
query.AppendLine("PRIMARY KEY (draw_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
/// <summary>
/// Creates the schema for the Judges table - a simple lookup for judges
/// </summary>
/// <returns></returns>
private bool CreateJudgesTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE judge (");
query.AppendLine("judge_id TEXT(36) NOT NULL,");
query.AppendLine("judge_name TEXT(100) NOT NULL,");
query.AppendLine("judge_level INTEGER NOT NULL,");
query.AppendLine("institution_id TEXT(36) NULL,");
query.AppendLine("active BOOLEAN NOT NULL, ");
query.AppendLine("FOREIGN KEY (institution_id) REFERENCES institution(institution_id),");
query.AppendLine("PRIMARY KEY (judge_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
/// <summary>
/// Create the schema for the SpeakersDraw table - a simple lookup for speaker draws
/// </summary>
private bool CreateSpeakerDrawTable()
{
StringBuilder query = new StringBuilder();
query.AppendLine("CREATE TABLE speaker_draw (");
query.AppendLine("draw_id TEXT(36) NOT NULL,");
query.AppendLine("speaker_id TEXT(36) NOT NULL,");
query.AppendLine("debate_id TEXT(36) NOT NULL,");
query.AppendLine("position INTEGER NOT NULL,");
query.AppendLine("result INTEGER, ");
query.AppendLine("speaker_points INTEGER, ");
query.AppendLine("FOREIGN KEY (speaker_id) REFERENCES speaker(speaker_id),");
query.AppendLine("FOREIGN KEY (debate_id) REFERENCES debate(debate_id),");
query.AppendLine("PRIMARY KEY (draw_id))");
NonQueryResult result = ExecuteNonQuery(query.ToString());
return(result.Exception == null);
}
private static Round DrawPowerpairedRound(Tournament tournament, DataContext context, string motion)
{
SqlHelper helper = new SqlHelper(tournament.Database);
Round round = new Round()
{
Motion = motion,
IsRandom = false,
RoundNumber = tournament.RoundNumber
};
NonQueryResult insert = helper.InsertRound(round);
List <Debate> debates = new List <Debate>();
List <Speaker> speakers = new List <Speaker>();
speakers.AddRange(context.Speakers.Where(a => a.Active));
speakers.AsParallel().ForAll(a => a.Draws = helper.GetSpeakerDraws(a.SpeakerId).Result);
speakers = speakers.OrderByDescending(a => a.Draws.Sum(d => d.Result.Points())).ThenByDescending(s => s.Draws.Sum(d => d.SpeakerPoints)).ToList();
int numberOfRooms = speakers.Count / 6;
int numberOfJudgesPerRoom = context.Judges.Count / numberOfRooms;
List <Judge> judges = new List <Judge>();
judges.AddRange(context.Judges.Where(j => j.Active));
judges = judges.OrderByDescending(j => (int)j.Level).ToList();
List <Venue> venues = new List <Venue>();
venues.AddRange(context.Venues);
for (int i = 1; i <= numberOfRooms; i++)
{
Venue v = venues.First();
venues.Remove(v);
Debate debate = new Debate()
{
RoundId = round.RoundId,
VenueId = v.VenueId
};
debates.Add(debate);
insert = helper.InsertDebate(debate);
List <Speaker> toInsert = speakers.Take(6).ToList();
SetSpeakersResult result = SetSpeakers(toInsert, debate.DebateId);
foreach (SpeakerDraw speakerDraw in result.SpeakerDraws)
{
helper.InsertSpeakerDraw(speakerDraw);
}
foreach (Speaker speaker in toInsert)
{
speakers.Remove(speaker);
}
Judge judge = judges.First();
JudgeDraw judgeDraw = new JudgeDraw()
{
DebateId = debate.DebateId,
Number = 1,
JudgeId = judge.JudgeId
};
judges.Remove(judge);
insert = helper.InsertJudgeDraw(judgeDraw);
}
foreach (Debate debate in debates)
{
for (int i = 2; i <= numberOfJudgesPerRoom; i++)
{
Judge j = judges.First();
JudgeDraw judgeDraw = new JudgeDraw()
{
DebateId = debate.DebateId,
Number = i,
JudgeId = j.JudgeId
};
judges.Remove(j);
insert = helper.InsertJudgeDraw(judgeDraw);
}
}
while (judges.Any())
{
Debate debate = debates.OrderBy(a => a.DebateId).First();
debates.Remove(debate);
Judge j = judges.First();
JudgeDraw judgeDraw = new JudgeDraw()
{
DebateId = debate.DebateId,
Number = numberOfJudgesPerRoom + 1,
JudgeId = j.JudgeId
};
judges.Remove(j);
insert = helper.InsertJudgeDraw(judgeDraw);
}
return(round);
}
private static Round DrawNonPowerpairedRound(Tournament tournament, DataContext context, string motion)
{
SqlHelper helper = new SqlHelper(tournament.Database);
Round round = new Round()
{
Motion = motion,
IsRandom = true,
RoundNumber = tournament.RoundNumber
};
NonQueryResult insert = helper.InsertRound(round);
Random r = new Random();
List <Debate> debates = new List <Debate>();
List <Speaker> initialSpeakers = context.Speakers.Where(s => s.Active).OrderBy(a => Guid.NewGuid()).ToList();
Queue <Speaker> speakers = new Queue <Speaker>(context.Speakers.Count(s => s.Active));
int count = context.Speakers.Count(s => s.Active);
while (initialSpeakers.Any())
{
Speaker s = initialSpeakers.First();
speakers.Enqueue(s);
initialSpeakers.Remove(s);
}
int numberOfRooms = speakers.Count / 6;
int numberOfJudgesPerRoom = context.Judges.Count(j => j.Active) / numberOfRooms;
List <Judge> judges = new List <Judge>();
judges.AddRange(context.Judges.Where(j => j.Active));
judges = judges.OrderByDescending(j => (int)j.Level).ToList();
List <Venue> venues = new List <Venue>();
venues.AddRange(context.Venues.Where(v => v.Active));
for (int i = 1; i <= numberOfRooms; i++)
{
Venue v = venues.First();
venues.Remove(v);
Debate debate = new Debate()
{
RoundId = round.RoundId,
VenueId = v.VenueId
};
debates.Add(debate);
insert = helper.InsertDebate(debate);
foreach (Position p in Enum.GetValues(typeof(Position)))
{
if (p == Position.Invalid)
{
continue;
}
Speaker s = speakers.Dequeue();
SpeakerDraw speakerDraw = new SpeakerDraw()
{
DebateId = debate.DebateId,
Position = p,
SpeakerId = s.SpeakerId
};
insert = helper.InsertSpeakerDraw(speakerDraw);
}
}
foreach (Debate debate in debates.OrderBy(a => Guid.NewGuid()))
{
Judge j = judges.First();
JudgeDraw judgeDraw = new JudgeDraw()
{
DebateId = debate.DebateId,
Number = 1,
JudgeId = j.JudgeId
};
judges.Remove(j);
insert = helper.InsertJudgeDraw(judgeDraw);
}
foreach (Debate debate in debates.OrderBy(a => Guid.NewGuid()))
{
for (int i = 2; i <= numberOfJudgesPerRoom; i++)
{
Judge j = judges.First();
JudgeDraw judgeDraw = new JudgeDraw()
{
DebateId = debate.DebateId,
Number = i,
JudgeId = j.JudgeId
};
judges.Remove(j);
insert = helper.InsertJudgeDraw(judgeDraw);
}
}
while (judges.Any())
{
Debate debate = debates.OrderBy(a => a.DebateId).First();
debates.Remove(debate);
Judge j = judges.First();
JudgeDraw judgeDraw = new JudgeDraw()
{
DebateId = debate.DebateId,
Number = numberOfJudgesPerRoom + 1,
JudgeId = j.JudgeId
};
judges.Remove(j);
insert = helper.InsertJudgeDraw(judgeDraw);
}
return(round);
}
