private void SaveDebate(object sender, EventArgs e)
{
List <Result> results = new List <Result>();
foreach (DataGridViewRow row in dgv_Debate.Rows)
{
results.Add((Result)row.Cells["Result"].Value);
}
if (results.Any(a => a == Result.Unspecified))
{
MessageBox.Show("Result unspecified - enter a value for each speaker");
tree_Rooms.Nodes[_currentlyLoadedDebate.ToString()].BackColor = Color.OrangeRed;
return;
}
if (results.Select(r => new KeyValuePair <Result, int>(r, results.Count(a => a.Equals(r)))).Any(s => s.Value > 1))
{
MessageBox.Show("Duplicate result is not permitted");
tree_Rooms.Nodes[_currentlyLoadedDebate.ToString()].BackColor = Color.OrangeRed;
return;
}
List <KeyValuePair <Result, int> > resultsWithSpeakerPoints = new List <KeyValuePair <Result, int> >();
foreach (DataGridViewRow row in dgv_Debate.Rows)
{
resultsWithSpeakerPoints.Add(new KeyValuePair <Result, int>((Result)row.Cells["Result"].Value, (int)row.Cells["Speaker Points"].Value));
}
int lastScore = 50;
Result lastResult = Result.Unspecified;
bool isfirstPass = true;
foreach (KeyValuePair <Result, int> result in resultsWithSpeakerPoints.OrderByDescending(r => r.Key))
{
if (result.Value <= lastScore && !isfirstPass)
{
MessageBox.Show(string.Format("{0}: {1} should be higher than {2}: {3}", result.Key, result.Value, lastResult, lastScore));
tree_Rooms.Nodes[_currentlyLoadedDebate.ToString()].BackColor = Color.OrangeRed;
return;
}
isfirstPass = false;
lastScore = result.Value;
lastResult = result.Key;
}
SqlHelper helper = new SqlHelper(_tournament.Database);
foreach (DataGridViewRow row in dgv_Debate.Rows)
{
SpeakerDraw speakerDraw = helper.GetSpeakerDraw((Guid)row.Tag).Result;
speakerDraw.Result = (Result)row.Cells["Result"].Value;
speakerDraw.SpeakerPoints = (int)row.Cells["Speaker Points"].Value;
helper.UpdateSpeakerDraw(speakerDraw);
}
tree_Rooms.Nodes[_currentlyLoadedDebate.ToString()].BackColor = Color.GreenYellow;
}
public NonQueryResult InsertSpeakerDraw(SpeakerDraw speakerDraw)
{
StringBuilder query = new StringBuilder();
query.AppendLine("INSERT INTO speaker_draw (draw_id, speaker_id, debate_id, position) ");
query.AppendFormat("VALUES('{0}', '{1}', '{2}', '{3}')", speakerDraw.DrawId, speakerDraw.SpeakerId, speakerDraw.DebateId, (int)speakerDraw.Position);
return(ExecuteNonQuery(query.ToString(), true));
}
public NonQueryResult UpdateSpeakerDraw(SpeakerDraw speakerDraw)
{
StringBuilder query = new StringBuilder();
query.AppendLine("UPDATE speaker_draw ");
query.AppendFormat("SET speaker_id = '{0}', ", speakerDraw.SpeakerId);
query.AppendFormat("debate_id = '{0}', ", speakerDraw.DebateId);
query.AppendFormat("position = {0}, ", (int)speakerDraw.Position);
query.AppendFormat("result = {0}, ", (int)speakerDraw.Result);
query.AppendFormat("speaker_points = {0} ", speakerDraw.SpeakerPoints);
query.AppendFormat("WHERE draw_id = '{0}'", speakerDraw.DrawId);
return(ExecuteNonQuery(query.ToString(), true));
}
private void MoveDraggedValue(object sender, DataGridViewCellMouseEventArgs e)
{
if (!e.Button.HasFlag(MouseButtons.Left))
{
return;
}
if (_currentlySelectedCell == null)
{
return;
}
if (_currentlySelectedCell.ColumnIndex == e.ColumnIndex && _currentlySelectedCell.RowIndex == e.RowIndex)
{
return;
}
SqlHelper helper = new SqlHelper(_tournament.Database);
bool isVenueDrag = _currentlySelectedCell.ColumnIndex == dgv_Round.Columns["Venue"].Index && dgv_Round.Rows[e.RowIndex].Tag != null;
if (isVenueDrag)
{
Guid draggedDebateId = (Guid)dgv_Round.Rows[_currentlySelectedCell.RowIndex].Tag;
Guid debateToSwitchItWithId = (Guid)dgv_Round.Rows[e.RowIndex].Tag;
Debate draggedDebate = helper.GetDebate(draggedDebateId).Result;
Guid draggedVenue = draggedDebate.VenueId;
Debate debateToSwitchItWith = helper.GetDebate(debateToSwitchItWithId).Result;
Guid switchedVenue = debateToSwitchItWith.VenueId;
draggedDebate.VenueId = switchedVenue;
debateToSwitchItWith.VenueId = draggedVenue;
helper.UpdateDebate(draggedDebate);
helper.UpdateDebate(debateToSwitchItWith);
SwitchRows(e.RowIndex, _currentlySelectedCell.RowIndex);
}
bool isAdjudicatorDrag = dgv_Round.Columns[_currentlySelectedCell.ColumnIndex].Name.StartsWith("Adj") && dgv_Round.Columns[e.ColumnIndex].Name.StartsWith("Adj");
if (isAdjudicatorDrag)
{
Guid draggedJudgeDrawId = dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[_currentlySelectedCell.ColumnIndex].Tag != null ? (Guid)dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[_currentlySelectedCell.ColumnIndex].Tag : Guid.Empty;
if (draggedJudgeDrawId.Equals(Guid.Empty))
{
_currentlySelectedCell = null;
Cursor = Cursors.Arrow;
return;
}
bool doesDraggedRowPossiblyNeedToBeRearranged = false;
for (int i = int.Parse(dgv_Round.Columns[_currentlySelectedCell.ColumnIndex].Name.Replace("Adj", string.Empty)) + 1; i <= 5; i++)
{
DataGridViewCell nextDraggedCell = dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[string.Format("Adj{0}", i)];
if (nextDraggedCell.Value != null)
{
doesDraggedRowPossiblyNeedToBeRearranged = true;
break;
}
}
JudgeDraw draggedJudgeDraw = helper.GetJudgeDraw(draggedJudgeDrawId).Result;
bool areWeSwitchingWithAnotherCell = true;
JudgeDraw judgeDrawToSwitchItWith;
int columnIndexToSwitchTo = e.ColumnIndex;
int adjudicatorNumber = int.Parse(dgv_Round.Columns[e.ColumnIndex].Name.Replace("Adj", string.Empty));
for (int i = 1; i < adjudicatorNumber; i++)
{
if (dgv_Round.Rows[e.RowIndex].Cells[string.Format("Adj{0}", i)].Value == null)
{
columnIndexToSwitchTo = dgv_Round.Columns[string.Format("Adj{0}", i)].Index;
break;
}
}
Guid judgeDrawToSwitchItWithId = dgv_Round.Rows[e.RowIndex].Cells[columnIndexToSwitchTo].Tag != null ? (Guid)dgv_Round.Rows[e.RowIndex].Cells[columnIndexToSwitchTo].Tag : Guid.Empty;
if (judgeDrawToSwitchItWithId.Equals(Guid.Empty))
{
judgeDrawToSwitchItWith = null;
areWeSwitchingWithAnotherCell = false;
}
else
{
judgeDrawToSwitchItWith = helper.GetJudgeDraw(judgeDrawToSwitchItWithId).Result;
}
bool judgeInSameDebate;
if (judgeDrawToSwitchItWith == null)
{
if (_currentlySelectedCell.RowIndex == e.RowIndex)
{
_currentlySelectedCell = null;
Cursor = Cursors.Arrow;
return;
}
else
{
judgeInSameDebate = false;
}
}
else
{
judgeInSameDebate = draggedJudgeDraw.DebateId.Equals(judgeDrawToSwitchItWith.DebateId);
}
int newJudgeNumber = 0;
if (judgeDrawToSwitchItWith != null)
{
newJudgeNumber = judgeDrawToSwitchItWith.Number;
}
else
{
newJudgeNumber = int.Parse(dgv_Round.Columns[e.ColumnIndex].Name.Replace("Adj", string.Empty));
}
int draggedJudgeDrawNumber = draggedJudgeDraw.Number;
int judgeDrawToSwitchItWithNumber = newJudgeNumber;
draggedJudgeDraw.Number = judgeDrawToSwitchItWithNumber;
if (areWeSwitchingWithAnotherCell)
{
judgeDrawToSwitchItWith.Number = draggedJudgeDrawNumber;
}
if (!judgeInSameDebate)
{
Guid draggedJudgeDrawDebateId = draggedJudgeDraw.DebateId;
Guid judgeDrawToSwitchItWithDebateId = (Guid)dgv_Round.Rows[e.RowIndex].Tag;
draggedJudgeDraw.DebateId = judgeDrawToSwitchItWithDebateId;
if (areWeSwitchingWithAnotherCell)
{
judgeDrawToSwitchItWith.DebateId = draggedJudgeDrawDebateId;
}
}
helper.UpdateJudgeDraw(draggedJudgeDraw);
if (areWeSwitchingWithAnotherCell)
{
helper.UpdateJudgeDraw(judgeDrawToSwitchItWith);
}
string draggedJudgeDrawValue = (string)dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[_currentlySelectedCell.ColumnIndex].Value;
string judgeDrawToSwitchItWithValue = (string)dgv_Round.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[_currentlySelectedCell.ColumnIndex].Value = judgeDrawToSwitchItWithValue;
Guid draggedTag = (Guid)dgv_Round[_currentlySelectedCell.ColumnIndex, _currentlySelectedCell.RowIndex].Tag;
Guid switchTag = (Guid)dgv_Round[e.ColumnIndex, e.RowIndex].Tag;
dgv_Round[_currentlySelectedCell.ColumnIndex, _currentlySelectedCell.RowIndex].Tag = switchTag;
dgv_Round[e.ColumnIndex, e.RowIndex].Tag = draggedTag;
if (doesDraggedRowPossiblyNeedToBeRearranged && !areWeSwitchingWithAnotherCell)
{
for (int i = 1; i < 5; i++)
{
DataGridViewCell thisCell = dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[string.Format("Adj{0}", i)];
DataGridViewCell nextCell = dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[string.Format("Adj{0}", i + 1)];
if (thisCell.Value == null)
{
thisCell.Value = nextCell.Value;
thisCell.Tag = nextCell.Tag;
Guid nextId = nextCell.Tag != null ? (Guid)nextCell.Tag : Guid.Empty;
if (nextId != Guid.Empty)
{
JudgeDraw nextJudgeDraw = helper.GetJudgeDraw(nextId).Result;
nextJudgeDraw.Number = i;
helper.UpdateJudgeDraw(nextJudgeDraw);
}
nextCell.Value = null;
nextCell.Tag = null;
}
}
}
dgv_Round.Rows[e.RowIndex].Cells[columnIndexToSwitchTo].Value = draggedJudgeDrawValue;
Refresh();
}
Position position;
bool isSpeakerDrag = Enum.TryParse(dgv_Round.Columns[_currentlySelectedCell.ColumnIndex].Name, out position) && Enum.TryParse(dgv_Round.Columns[_currentlySelectedCell.ColumnIndex].Name, out position);
if (isSpeakerDrag)
{
Guid draggedSpeakerDrawId = (Guid)dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[_currentlySelectedCell.ColumnIndex].Tag;
SpeakerDraw draggedSpeakerDraw = helper.GetSpeakerDraw(draggedSpeakerDrawId).Result;
Guid speakerDrawToSwitchItWithId = (Guid)dgv_Round.Rows[e.RowIndex].Cells[e.ColumnIndex].Tag;
SpeakerDraw speakerDrawToSwitchItWith = helper.GetSpeakerDraw(speakerDrawToSwitchItWithId).Result;
bool speakerInSameDebate = draggedSpeakerDraw.DebateId.Equals(speakerDrawToSwitchItWith.DebateId);
Position draggedSpeakerDrawPosition = draggedSpeakerDraw.Position;
Position speakerDrawToSwitchItWithPosition = speakerDrawToSwitchItWith.Position;
draggedSpeakerDraw.Position = speakerDrawToSwitchItWithPosition;
speakerDrawToSwitchItWith.Position = draggedSpeakerDrawPosition;
if (!speakerInSameDebate)
{
Guid draggedSpeakerDrawDebateId = draggedSpeakerDraw.DebateId;
Guid speakerDrawToSwitchItWithDebateId = speakerDrawToSwitchItWith.DebateId;
draggedSpeakerDraw.DebateId = speakerDrawToSwitchItWithDebateId;
speakerDrawToSwitchItWith.DebateId = draggedSpeakerDrawDebateId;
}
helper.UpdateSpeakerDraw(draggedSpeakerDraw);
helper.UpdateSpeakerDraw(speakerDrawToSwitchItWith);
string draggedSpeakerDrawValue = (string)dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[_currentlySelectedCell.ColumnIndex].Value;
string speakerDrawToSwitchItWithValue = (string)dgv_Round.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
dgv_Round.Rows[_currentlySelectedCell.RowIndex].Cells[_currentlySelectedCell.ColumnIndex].Value = speakerDrawToSwitchItWithValue;
dgv_Round.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = draggedSpeakerDrawValue;
}
ShowClashes(_currentlySelectedRound.RoundId);
_currentlySelectedCell = null;
Cursor = Cursors.Arrow;
}
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);
}
public static void CreateDisplay(Tournament tournament, DataContext context, Guid roundId)
{
SqlHelper helper = new SqlHelper(tournament.Database);
Round round = helper.GetRound(roundId).Result;
List <Debate> debates = helper.GetDebates(roundId).Result;
List <SpeakerDraw> speakerDraws = new List <SpeakerDraw>();
List <JudgeDraw> judgeDraws = new List <JudgeDraw>();
foreach (Debate debate in debates)
{
speakerDraws.AddRange(helper.GetSpeakerDrawsByDebate(debate.DebateId).Result);
judgeDraws.AddRange(helper.GetJudgeDrawsByDebate(debate.DebateId).Result);
}
List <Speaker> speakers = new List <Speaker>();
speakers.AddRange(context.Speakers);
speakers = speakers.OrderBy(s => s.Name).ToList();
List <Judge> judges = new List <Judge>();
judges.AddRange(context.Judges);
StringBuilder display = new StringBuilder();
display.AppendLine("<html>");
display.AppendLine("<head>");
display.AppendFormat("<title>{0} - Round {1}</title>", tournament.Name, round.RoundNumber);
display.AppendFormat("<link rel=\"stylesheet\" href=\"../lib/display.css\" type=\"text/css\"/>");
display.AppendLine();
display.AppendFormat("<script src=\"../lib/display.js\" type=\"text/javascript\"></script>");
display.AppendLine();
display.AppendLine("</head>");
display.AppendLine("<body>");
display.AppendFormat("<div class=\"header\">{0} - Round {1}</div>", tournament.Name, round.RoundNumber);
display.AppendLine();
display.AppendLine("<div class=\"blocker\"></div>");
display.AppendLine("<table border=\"1\" cellpadding=\"20\">");
display.AppendLine("<th class=\"table-header\">Speaker</th><th class=\"table-header\">Institution</th><th class=\"table-header\">Venue</th><th class=\"table-header\">Opening Government</th><th class=\"table-header\">Opening Opposition</th><th class=\"table-header\">Second Government</th><th class=\"table-header\">Second Opposition</th><th class=\"table-header\">Closing Government</th><th class=\"table-header\">Closing Opposition</th><th class=\"table-header\">Judges</th>");
foreach (Speaker speaker in speakers)
{
SpeakerDraw speakerDraw = speakerDraws.First(sd => sd.SpeakerId.Equals(speaker.SpeakerId));
Debate debate = debates.First(d => d.DebateId.Equals(speakerDraw.DebateId));
Venue venue = context.Venues.First(v => v.VenueId.Equals(debate.VenueId));
display.AppendLine("<tr class=\"display-row-entry\">");
display.AppendFormat("<td class=\"display-cell-entry\">{0}</td>", speaker.Name);
display.AppendFormat("<td class=\"display-cell-entry\">{0}</td>", context.Institutions.First(i => i.InstitutionId.Equals(speaker.InstitutionId)).Name);
display.AppendFormat("<td class=\"display-cell-entry\">{0}</td>", venue.Name);
foreach (SpeakerDraw draw in speakerDraws.Where(sd => sd.DebateId.Equals(speakerDraw.DebateId)).OrderBy(o => o.Position))
{
display.AppendFormat("<td class=\"display-cell-entry{0}\">{1}</td>", draw.DrawId.Equals(speakerDraw.DrawId) ? " selected" : string.Empty, speakers.First(s => s.SpeakerId.Equals(draw.SpeakerId)).Name);
}
string[] judgeNames = judgeDraws.Where(jd => jd.DebateId.Equals(debate.DebateId)).OrderBy(j => j.Number).Select(jd => judges.First(j => jd.JudgeId.Equals(j.JudgeId)).Name).ToArray();
display.AppendFormat("<td class=\"display-cell-entry\">{0}</td>", string.Join(",<br/>", judgeNames));
display.AppendLine("</tr>");
}
display.AppendLine("</table>");
display.AppendLine("</body>");
display.AppendLine("</html>");
string directory = Path.Combine(Path.GetDirectoryName(tournament.Location), tournament.Name, string.Format("Round {0}", round.RoundNumber));
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
string filename = Path.Combine(directory, "draw.html");
File.WriteAllText(filename, display.ToString());
}
public static void WriteTabSoFar(Tournament tournament, DataContext context, int roundNumber)
{
SqlHelper helper = new SqlHelper(tournament.Database);
List <Round> rounds = helper.GetRounds().Result.Where(r => r.RoundNumber <= roundNumber).ToList();
List <Tuple <int, List <Debate> > > debates = new List <Tuple <int, List <Debate> > >();
foreach (Round round in rounds)
{
debates.Add(Tuple.Create(round.RoundNumber, helper.GetDebates(round.RoundId).Result.ToList()));
}
List <SpeakerDraw> draws = new List <SpeakerDraw>();
foreach (Tuple <int, List <Debate> > debate in debates)
{
foreach (Debate d in debate.Item2)
{
draws.AddRange(helper.GetSpeakerDrawsByDebate(d.DebateId).Result);
}
}
List <Speaker> speakers = new List <Speaker>();
speakers.AddRange(context.Speakers);
speakers.AsParallel().ForAll(s => s.Draws = draws.Where(d => d.SpeakerId.Equals(s.SpeakerId)).ToList());
speakers = speakers.OrderByDescending(s => s.Draws.Sum(sd => sd.Result.Points())).ThenByDescending(s => s.Draws.Sum(sd => sd.SpeakerPoints)).ToList();
Excel.Application excelApplication = new Excel.Application()
{
Visible = false
};
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add();
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet)excelSheets.get_Item("Sheet1");
Excel.Range cell = sheet.get_Range("A2");
cell.Value2 = "Speaker";
cell = sheet.get_Range("B2");
cell.Value2 = "Total Points";
cell = sheet.get_Range("C2");
cell.Value2 = "Total Speaker Points";
int columns = 1;
int columnLetter = 'D';
for (int i = 1; i <= roundNumber; i++)
{
cell = sheet.get_Range(string.Format("{0}1", (char)columnLetter));
cell.Value2 = string.Concat("Round ", i);
cell = sheet.get_Range(string.Format("{0}2", (char)columnLetter++));
cell.Value2 = "Result";
cell = sheet.get_Range(string.Format("{0}2", (char)columnLetter++));
cell.Value2 = "Speaker Points";
columns += 3;
}
int letter = 'A';
int row = 3;
foreach (Speaker speaker in speakers)
{
letter = 'A';
cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
cell.Value2 = speaker.Name;
cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
cell.Value2 = speaker.Draws.Sum(x => x.Result.Points());
cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
cell.Value2 = speaker.Draws.Sum(x => x.SpeakerPoints);
foreach (Round round in rounds)
{
List <Debate> theseDebates = debates[round.RoundNumber - 1].Item2;
SpeakerDraw speakerDraw = speaker.Draws.First(sd => theseDebates.Select(d => d.DebateId).Contains(sd.DebateId));
cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
cell.Value2 = speakerDraw.Result.Points();
cell = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
cell.Value2 = speakerDraw.SpeakerPoints;
}
row++;
}
string directory = Path.Combine(Path.GetDirectoryName(tournament.Location), string.Format("Round {0}", roundNumber));
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
string filename = Path.Combine(directory, string.Format("Round {0} - tab.xlsx", roundNumber));
string endCell = string.Format("{0}{1}", (char)(letter - 1), row - 1);
cell = sheet.get_Range("A1", endCell);
cell.Columns.AutoFit();
excelApplication.DisplayAlerts = false;
excelWorkbook.SaveAs(filename);
excelWorkbook.Close();
excelApplication.DisplayAlerts = true;
excelApplication.Quit();
}
public static void CreateBallots(Tournament tournament, DataContext context, Guid roundId)
{
SqlHelper helper = new SqlHelper(tournament.Database);
Round round = helper.GetRound(roundId).Result;
string motion = round.Motion;
List <Debate> debates = helper.GetDebates(roundId).Result;
string directory = Path.Combine(Path.GetDirectoryName(tournament.Location), tournament.Name, string.Format("Round {0}", round.RoundNumber));
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
string ballotsDirectory = Path.Combine(directory, "ballots");
if (!Directory.Exists(ballotsDirectory))
{
Directory.CreateDirectory(ballotsDirectory);
}
foreach (Debate debate in debates)
{
List <SpeakerDraw> speakerDraws = helper.GetSpeakerDrawsByDebate(debate.DebateId).Result;
List <JudgeDraw> judgeDraws = helper.GetJudgeDrawsByDebate(debate.DebateId).Result;
Venue venue = context.Venues.First(v => v.VenueId.Equals(debate.VenueId));
Word._Application wordApplication = new Word.Application()
{
Visible = false
};
wordApplication.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone;
object filename = Path.Combine(Path.GetDirectoryName(tournament.Location), tournament.Name, "lib", "ballot.docx");
Word._Document wordDocument = wordApplication.Documents.Open(ref filename);
wordDocument.Activate();
Word.Range content = wordDocument.Range();
Dictionary <string, string> findReplaces = new Dictionary <string, string>();
findReplaces.Add("#t", tournament.Name);
findReplaces.Add("#v", venue.Name);
string chairJudge = judgeDraws.OrderBy(j => j.Number).Select(jd => context.Judges.First(j => j.JudgeId.Equals(jd.JudgeId))).First().Name;
findReplaces.Add("#chair", chairJudge);
findReplaces.Add("#j", string.Join(", ", judgeDraws.OrderBy(j => j.Number).Select(a => context.Judges.First(j => j.JudgeId.Equals(a.JudgeId)).Name)));
findReplaces.Add("#r", round.RoundNumber.ToString());
findReplaces.Add("#m", motion);
SpeakerDraw og = speakerDraws.First(sd => sd.Position.Equals(Position.OpeningGovernment));
Speaker ogSpeaker = context.Speakers.First(sd => og.SpeakerId.Equals(sd.SpeakerId));
findReplaces.Add("#og", context.Speakers.First(s => s.SpeakerId.Equals(og.SpeakerId)).Name);
findReplaces.Add("#iog", context.Institutions.First(i => i.InstitutionId.Equals(ogSpeaker.InstitutionId)).Name);
SpeakerDraw oo = speakerDraws.First(sd => sd.Position.Equals(Position.OpeningOpposition));
Speaker ooSpeaker = context.Speakers.First(sd => oo.SpeakerId.Equals(sd.SpeakerId));
findReplaces.Add("#oo", context.Speakers.First(s => s.SpeakerId.Equals(oo.SpeakerId)).Name);
findReplaces.Add("#ioo", context.Institutions.First(i => i.InstitutionId.Equals(ooSpeaker.InstitutionId)).Name);
SpeakerDraw sg = speakerDraws.First(sd => sd.Position.Equals(Position.SecondGovernment));
Speaker sgSpeaker = context.Speakers.First(sd => sg.SpeakerId.Equals(sg.SpeakerId));
findReplaces.Add("#sg", context.Speakers.First(s => s.SpeakerId.Equals(sg.SpeakerId)).Name);
findReplaces.Add("#isg", context.Institutions.First(i => i.InstitutionId.Equals(sgSpeaker.InstitutionId)).Name);
SpeakerDraw so = speakerDraws.First(sd => sd.Position.Equals(Position.SecondOpposition));
Speaker soSpeaker = context.Speakers.First(sd => so.SpeakerId.Equals(sd.SpeakerId));
findReplaces.Add("#so", context.Speakers.First(s => s.SpeakerId.Equals(so.SpeakerId)).Name);
findReplaces.Add("#iso", context.Institutions.First(i => i.InstitutionId.Equals(soSpeaker.InstitutionId)).Name);
SpeakerDraw cg = speakerDraws.First(sd => sd.Position.Equals(Position.ClosingGovernment));
Speaker cgSpeaker = context.Speakers.First(sd => cg.SpeakerId.Equals(sd.SpeakerId));
findReplaces.Add("#cg", context.Speakers.First(s => s.SpeakerId.Equals(cg.SpeakerId)).Name);
findReplaces.Add("#icg", context.Institutions.First(i => i.InstitutionId.Equals(cgSpeaker.InstitutionId)).Name);
SpeakerDraw co = speakerDraws.First(sd => sd.Position.Equals(Position.ClosingOpposition));
Speaker coSpeaker = context.Speakers.First(sd => co.SpeakerId.Equals(sd.SpeakerId));
findReplaces.Add("#co", context.Speakers.First(s => s.SpeakerId.Equals(co.SpeakerId)).Name);
findReplaces.Add("#ico", context.Institutions.First(i => i.InstitutionId.Equals(coSpeaker.InstitutionId)).Name);
foreach (KeyValuePair <string, string> keyValuePair in findReplaces)
{
content.Find.Execute(FindText: keyValuePair.Key, Replace: Word.WdReplace.wdReplaceAll, ReplaceWith: keyValuePair.Value);
}
object ballotFilename = Path.Combine(ballotsDirectory, string.Format("{0}.doc", venue.Name));
wordDocument.SaveAs(ref ballotFilename);
object save = Word.WdSaveOptions.wdDoNotSaveChanges;
wordDocument.Close(ref save);
wordApplication.Quit();
}
}
