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