protected static HtmlAnchor CreateAdminPoliticianAnchor(DataRow politician,
string anchorText, string target = "politician")
{
var href = SecurePoliticianPage.GetUpdateIntroPageUrl(politician.PoliticianKey());
if (VotePage.IsPublicPage)
{
href = UrlManager.GetAdminUri(href).ToString();
}
var a = new HtmlAnchor
{
HRef = href,
Target = target,
Title = "Edit Links, Picture, Bio & Reasons",
InnerHtml = anchorText
};
return(a);
}
protected void Page_Load(object sender, EventArgs e)
{
var stateCode = Request.QueryString["state"];
if (IsNullOrWhiteSpace(stateCode))
{
throw new VoteException("State code is missing.");
}
if (!StateCache.IsValidStateCode(stateCode))
{
throw new VoteException("State code is invalid.");
}
var includeMissing = GetQueryString("m") == "1";
var includeWith = GetQueryString("w") == "1";
var fileDesc = includeMissing
? includeWith ? ".all" : ".missing-incumbents"
: ".with-incumbents";
// get the data
var table = Counties.GetOfficesForCsv(stateCode);
// create the csv
string csv;
using (var ms = new MemoryStream())
{
var streamWriter = new StreamWriter(ms);
var csvWriter = new SimpleCsvWriter();
// write headers
csvWriter.AddField("State Code");
csvWriter.AddField("County Name");
csvWriter.AddField("County Code");
csvWriter.AddField("Office Name");
csvWriter.AddField("Office Key");
csvWriter.AddField("Incumbent Name");
csvWriter.AddField("Politician Key");
csvWriter.AddField("Election Key");
csvWriter.Write(streamWriter);
foreach (var row in table.Rows.OfType <DataRow>()
.Where(r => !IsNullOrWhiteSpace(r.ElectionKey()) &&
(includeMissing && IsNullOrWhiteSpace(r.PoliticianKey()) ||
includeWith && !IsNullOrWhiteSpace(r.PoliticianKey()))))
{
csvWriter.AddField(row.StateCode());
csvWriter.AddField(row.County());
csvWriter.AddField(row.CountyCode());
csvWriter.AddField(Offices.FormatOfficeName(row));
csvWriter.AddField(row.OfficeKey() ?? Empty);
csvWriter.AddField(Politicians.FormatName(row));
csvWriter.AddField(row.PoliticianKey() ?? Empty);
csvWriter.AddField(row.ElectionKey() ?? Empty);
csvWriter.AddField(
row.ElectionKey() == null ?
Empty
: $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetAdminFolderPageUrl("election", "election", row.ElectionKey()))}\",\"Election Report\")");
csvWriter.AddField(
row.ElectionKey() == null || row.OfficeKey() == null ?
Empty
: $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetOfficeWinnerPageUrl(row.ElectionKey(), row.OfficeKey()))}\",\"Identify Winners\")");
csvWriter.Write(streamWriter);
}
streamWriter.Flush();
ms.Position = 0;
csv = new StreamReader(ms).ReadToEnd();
}
// download
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition",
$"attachment;filename=\"county-offices-{stateCode}{fileDesc}.csv\"");
Response.Write("\xfeff"); // BOM
Response.Write(csv);
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
var stateCode = Request.QueryString["state"];
if (IsNullOrWhiteSpace(stateCode))
{
throw new VoteException("State code is missing.");
}
if (!StateCache.IsValidStateCode(stateCode))
{
throw new VoteException("State code is invalid.");
}
var includeMissing = GetQueryString("m") == "1";
var includeWith = GetQueryString("w") == "1";
var fileDesc = includeMissing
? (includeWith ? ".all" : ".missing-incumbents")
: ".with-incumbents";
// get the data
var rows = LocalDistricts.GetOfficesForCsv(stateCode).Rows.OfType <DataRow>()
.Where(r => !IsNullOrWhiteSpace(r.ElectionKey()) &&
(includeMissing && IsNullOrWhiteSpace(r.PoliticianKey()) ||
includeWith && !IsNullOrWhiteSpace(r.PoliticianKey())))
.ToList();
// apply counties to the data
var countiesForLocals =
LocalIdsCodes.FindCountiesWithNames(stateCode, rows.Select(r => r.LocalKey()).Distinct());
var data = rows.SelectMany(r => countiesForLocals[r.LocalKey()].Select(c =>
new
{
Row = r,
CountyCode = c.Value,
County = c.Text,
AlsoIn = countiesForLocals[r.LocalKey()]
.Where(o => o.Text != c.Text).Select(o => o.Text).ToArray()
}))
.OrderBy(r => r.County)
.ThenBy(r => r.Row.LocalDistrict())
.ThenBy(r => r.Row.OfficeLine1())
.ThenBy(r => r.Row.OfficeLine2())
.ToArray();
// create the csv
string csv;
using (var ms = new MemoryStream())
{
var streamWriter = new StreamWriter(ms);
var csvWriter = new SimpleCsvWriter();
// write headers
csvWriter.AddField("State Code");
csvWriter.AddField("County Name");
csvWriter.AddField("County Code");
csvWriter.AddField("Also In");
csvWriter.AddField("Local Name");
csvWriter.AddField("Local Key");
csvWriter.AddField("Office Name");
csvWriter.AddField("Office Key");
csvWriter.AddField("Incumbent Name");
csvWriter.AddField("Politician Key");
csvWriter.AddField("Election Key");
csvWriter.Write(streamWriter);
foreach (var row in data)
{
csvWriter.AddField(row.Row.StateCode());
csvWriter.AddField(row.County);
csvWriter.AddField(row.CountyCode);
csvWriter.AddField(Join(", ", row.AlsoIn));
csvWriter.AddField(row.Row.LocalDistrict());
csvWriter.AddField(row.Row.LocalKey());
csvWriter.AddField(Offices.FormatOfficeName(row.Row));
csvWriter.AddField(row.Row.OfficeKey() ?? Empty);
csvWriter.AddField(Politicians.FormatName(row.Row));
csvWriter.AddField(row.Row.PoliticianKey() ?? Empty);
csvWriter.AddField(row.Row.ElectionKey() ?? Empty);
csvWriter.AddField(
row.Row.ElectionKey() == null ?
Empty
: $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetAdminFolderPageUrl("election", "election", row.Row.ElectionKey()))}\",\"Election Report\")");
csvWriter.AddField(
row.Row.ElectionKey() == null || row.Row.OfficeKey() == null ?
Empty
: $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetOfficeWinnerPageUrl(row.Row.ElectionKey(), row.Row.OfficeKey()))}\",\"Identify Winners\")");
csvWriter.Write(streamWriter);
}
streamWriter.Flush();
ms.Position = 0;
csv = new StreamReader(ms).ReadToEnd();
}
// download
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition",
$"attachment;filename=\"local-offices-{stateCode}{fileDesc}.csv\"");
Response.Write("\xfeff"); // BOM
Response.Write(csv);
Response.End();
}
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();
}
}
