protected void Page_Load(object sender, EventArgs e) { SimpleCsvWriter csvWriter; StreamWriter streamWriter; void FillDataRows(IList <int> candidates, IList <int> offices, int ballotMeasures, string description = null) { if (_HasElections) { csvWriter.AddField(description.SafeString()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.President].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.President].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.USSenate].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.USSenate].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.USHouse].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.USHouse].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.Governor].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.Governor].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.StateExecutive].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.StateExecutive].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.StateSenate].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.StateSenate].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.StateHouse].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.StateHouse].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.County].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.County].BlankIfZero()); } if (_HasOffices) { csvWriter.AddField(offices[(int)OfficeHeading.Local].BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(candidates[(int)OfficeHeading.Local].BlankIfZero()); } if (_HasBallotMeasures) { csvWriter.AddField(ballotMeasures.BlankIfZero()); } var allOffices = 0; var allCandidates = 0; for (var o = OfficeHeading.President; o < OfficeHeading.Count; o++) { allOffices += offices[(int)o]; allCandidates += candidates[(int)o]; } if (_HasOffices) { csvWriter.AddField(allOffices.BlankIfZero()); } if (_HasCandidates) { csvWriter.AddField(allCandidates.BlankIfZero()); } csvWriter.Write(streamWriter); } bool IsBallotMeasure(DataRow r) { return(r.ReferendumKey() != null); } if (!DateTime.TryParse(Request.QueryString["from"], out var fromDate)) { fromDate = DateTime.MinValue; } if (!DateTime.TryParse(Request.QueryString["to"], out var toDate)) { toDate = DateTime.MaxValue; } var reportType = Request.QueryString["type"].SafeString(); _HasCandidates = reportType.Contains("C"); _HasOffices = reportType.Contains("O"); _HasBallotMeasures = reportType.Contains("B"); _HasElections = Request.QueryString["detail"] == "E"; for (var i = 0; i < (int)OfficeHeading.Count; i++) { _ElectionOffices[i] = new List <string>(); } // get data table from database var table = ElectionsPoliticians.GetOfficesAndCandidatesReportData(fromDate, toDate, 0) .Rows.OfType <DataRow>().ToList(); // create dummy entries so months with no elections will show var lowDate = fromDate == DateTime.MinValue ? table.Min(r => r.ElectionDate()) : fromDate; var highDate = toDate == DateTime.MaxValue ? table.Max(r => r.ElectionDate()) : toDate; var monthList = new List <DateTime>(); while (lowDate <= highDate) { monthList.Add(lowDate); lowDate = lowDate.AddMonths(1); } var dummies = monthList.Select(m => new { row = (DataRow)null, MonthYear = m.ToString("yyyyMM"), MonthYearDesc = m.ToString("MMM yyyy"), Year = m.ToString("yyyy"), OfficeHeading = OfficeHeading.Other }).ToList(); var data = table .Select(r => new { row = r, MonthYear = r.ElectionDate().ToString("yyyyMM"), MonthYearDesc = r.ElectionDate().ToString("MMM yyyy"), Year = r.ElectionDate().ToString("yyyy"), OfficeHeading = GetOfficeHeading(r.OfficeClass(), r.AlternateOfficeClass()) }) .Union(dummies) .GroupBy(r => r.row == null ? r.MonthYear : r.row.ElectionKeyState()) .GroupBy(el => el.First().MonthYear) .OrderBy(g => g.Key) .GroupBy(m => m.First().First().Year) .ToList(); using (var ms = new MemoryStream()) { streamWriter = new StreamWriter(ms); csvWriter = new SimpleCsvWriter(); // write CSV headings csvWriter.AddField("Date"); if (_HasElections) { csvWriter.AddField("Election Title"); } if (_HasOffices) { csvWriter.AddField("President Offices"); } if (_HasCandidates) { csvWriter.AddField("President Candidates"); } if (_HasOffices) { csvWriter.AddField("US Senate Offices"); } if (_HasCandidates) { csvWriter.AddField("US Senate Candidates"); } if (_HasOffices) { csvWriter.AddField("US House Offices"); } if (_HasCandidates) { csvWriter.AddField("US House Candidates"); } if (_HasOffices) { csvWriter.AddField("Governor Offices"); } if (_HasCandidates) { csvWriter.AddField("Governor Candidates"); } if (_HasOffices) { csvWriter.AddField("State Executive Offices"); } if (_HasCandidates) { csvWriter.AddField("State Executive Candidates"); } if (_HasOffices) { csvWriter.AddField("State Senate Offices"); } if (_HasCandidates) { csvWriter.AddField("State Senate Candidates"); } if (_HasOffices) { csvWriter.AddField("State House Offices"); } if (_HasCandidates) { csvWriter.AddField("State House Candidates"); } if (_HasOffices) { csvWriter.AddField("Countywide Offices"); } if (_HasCandidates) { csvWriter.AddField("Countywide Candidates"); } if (_HasOffices) { csvWriter.AddField("Local Offices"); } if (_HasCandidates) { csvWriter.AddField("Local Candidates"); } if (_HasBallotMeasures) { csvWriter.AddField("Ballot Measures"); } if (_HasOffices) { csvWriter.AddField("TOTAL OFFICES"); } if (_HasCandidates) { csvWriter.AddField("TOTAL CANDIDATES"); } csvWriter.Write(streamWriter); foreach (var year in data) { foreach (var monthYear in year) { foreach (var election in monthYear) { for (var i = 0; i < _ElectionOfficeCount.Length; i++) { _ElectionOffices[i].Clear(); } foreach (var candidate in election) { if (candidate.row != null) { if (IsBallotMeasure(candidate.row)) { _ElectionBallotMeasures++; } else { _ElectionCandidates[(int)candidate.OfficeHeading]++; _ElectionOffices[(int)candidate.OfficeHeading].Add(candidate.row.OfficeKey()); } } } for (var i = 0; i < _ElectionOfficeCount.Length; i++) { _ElectionOfficeCount[i] = _ElectionOffices[i].Distinct().Count(); } if (_HasElections) { var row = election.First().row; if (row != null) { csvWriter.AddField(row.ElectionDate().ToString("M/d/yyyy")); var desc = $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetAdminFolderPageUrl("election", "election", row.ElectionKeyState(), "complete", "1"))}\",\"{row.ElectionDescription()}\")"; FillDataRows(_ElectionCandidates, _ElectionOfficeCount, _ElectionBallotMeasures, desc); } } RollCounters(_ElectionCandidates, _MonthCandidates); RollCounters(_ElectionOfficeCount, _MonthOfficeCount); RollBallotMeasures(ref _ElectionBallotMeasures, ref _MonthBallotMeasures); } csvWriter.AddField($"Total {monthYear.First().First().MonthYearDesc}"); FillDataRows(_MonthCandidates, _MonthOfficeCount, _MonthBallotMeasures); RollCounters(_MonthCandidates, _YearCandidates); RollCounters(_MonthOfficeCount, _YearOfficeCount); RollBallotMeasures(ref _MonthBallotMeasures, ref _YearBallotMeasures); } csvWriter.Write(streamWriter); csvWriter.AddField($"TOTAL YEAR {year.First().First().First().Year}"); FillDataRows(_YearCandidates, _YearOfficeCount, _YearBallotMeasures); RollCounters(_YearCandidates, _ReportCandidates); RollCounters(_YearOfficeCount, _ReportOfficeCount); RollBallotMeasures(ref _YearBallotMeasures, ref _ReportBallotMeasures); csvWriter.Write(streamWriter); } csvWriter.AddField("REPORT TOTAL"); FillDataRows(_ReportCandidates, _ReportOfficeCount, _ReportBallotMeasures); streamWriter.Flush(); ms.Position = 0; var csv = new StreamReader(ms).ReadToEnd(); var reportDesc = String.Empty; if (_HasOffices) { if (_HasCandidates) { reportDesc += "Offices & Candidates"; } else { reportDesc += "Offices"; } } else if (_HasCandidates) { reportDesc += "Candidates"; } else if (_HasBallotMeasures) { reportDesc += "Ballot Measures"; } reportDesc += " in Elections"; if (fromDate != DateTime.MinValue) { if (toDate != DateTime.MaxValue) { reportDesc += $" {fromDate:M-d-yyyy} to {toDate:M-d-yyyy}"; } else { reportDesc += $" after {fromDate:M-d-yyyy}"; } } else if (toDate != DateTime.MaxValue) { reportDesc += $" before {toDate:M-d-yyyy}"; } if (_HasElections) { reportDesc += " with election detail"; } // download Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", $"attachment;filename=\"{reportDesc}.csv\""); Response.Write("\xfeff"); // BOM Response.Write(csv); Response.End(); } }