public static string GetCountyDescription(string stateCode, ref string countyCode,
string localKey)
{
var cc = countyCode;
countyCode = Empty;
if (!IsNullOrWhiteSpace(cc))
{
return(GetCountyName(stateCode, cc));
}
var counties = LocalIdsCodes.FindCountiesWithNames(stateCode, localKey);
switch (counties.Length)
{
case 0:
return("?");
case 1:
countyCode = counties[0].Value;
return(counties[0].Text);
default:
countyCode = counties[0].Value;
return("Multiple Counties");
}
}
protected void ButtonRemoveMultiCountyDistricts_OnClick(object sender, EventArgs e)
{
switch (RemoveMultiCountyDistrictsReloading.Value)
{
case "reloading":
{
RemoveMultiCountyDistrictsReloading.Value = Empty;
_RemoveMultiCountyDistrictsTabInfo.ClearValidationErrors();
var items =
FormatMultiCountyLocalsList(LocalDistricts.GetLocalsForRemove(StateCode, CountyCode));
PopulateDistrictsToRemoveDropdown(items);
_RemoveMultiCountyDistrictsTabInfo.LoadControls();
FeedbackRemoveMultiCountyDistricts.AddInfo("Remove Multi-County Districts settings loaded.");
}
break;
case "":
{
try
{
// normal update
_RemoveMultiCountyDistrictsTabInfo.ClearValidationErrors();
var localKey = ControlRemoveMultiCountyDistrictsLocalKey.GetValue();
if (IsNullOrWhiteSpace(localKey))
{
throw new VoteException("No district was selected");
}
var lic = LocalIdsCodes.GetDataByStateCodeLocalKey(StateCode, localKey);
if (lic.Count != 1)
{
throw new VoteException("Missing LocalIdsCodes row");
}
if (lic[0].LocalType != LocalIdsCodes.LocalTypeVote)
{
throw new VoteException("Invalid LocalType");
}
TigerPlacesCounties.DeleteByStateCodeCountyCodeTigerCodeTigerType(StateCode,
CountyCode, lic[0].LocalId, lic[0].LocalType);
_RemoveMultiCountyDistrictsTabInfo.ClearValidationErrors();
var items =
FormatMultiCountyLocalsList(LocalDistricts.GetLocalsForRemove(StateCode, CountyCode));
PopulateDistrictsToRemoveDropdown(items);
_RemoveMultiCountyDistrictsTabInfo.LoadControls();
NavigateJurisdictionUpdatePanel.Update();
NavigateJurisdiction.Initialize();
FeedbackRemoveMultiCountyDistricts.AddInfo("Multi-County District removed.");
}
catch (Exception ex)
{
FeedbackRemoveMultiCountyDistricts.HandleException(ex);
}
}
break;
default:
throw new VoteException($"Unknown reloading option: '{RemoveMultiCountyDistrictsReloading.Value}'");
}
}
private void LoadTigerControlsForTigerSettings()
{
var tigerData =
LocalIdsCodes.GetLocalData(StateCode, LocalKey).Rows.Cast <DataRow>().ToList();
ControlTigerSettingsEntireCounty.SetValue(
tigerData.Any(d => d.LocalType() == LocalIdsCodes.LocalTypeVote).ToString());
PopulateTigerDistrictsForTigerSettings(tigerData);
}
// ReSharper disable VirtualMemberNeverOverriden.Global
protected void FormatMultiCountiesMessage(HtmlContainerControl control)
{
var allCounties =
LocalIdsCodes.FormatMultiCountyNames(StateCode, LocalKey, true);
if (!IsNullOrWhiteSpace(allCounties))
{
control.RemoveCssClass("hidden");
control.InnerText = $"Parts of this local district are in {allCounties}";
}
}
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
// ReSharper disable UnusedMethodReturnValue.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
// ReSharper disable UnassignedField.Global
#endregion ReSharper disable
public static void AddLinks(Control parent, string stateCode, string countyCode,
Func<string, string, string, string, string, Control> getAnchor)
{
var localsDictionary = LocalDistricts.GetNamesDictionary(stateCode, countyCode)
.OrderBy(kvp => kvp.Value, new AlphanumericComparer()).ToArray();
var otherCounties = LocalIdsCodes.FormatOtherCountyNamesDictionary(stateCode,
countyCode, localsDictionary.Select(l => l.Key));
foreach (var local in localsDictionary)
getAnchor(stateCode, countyCode, local.Key, local.Value, otherCounties[local.Key])
.AddTo(parent);
}
protected int FormatOtherCountiesMessage(HtmlContainerControl control)
{
var otherCounties =
LocalIdsCodes.FormatOtherCountyNames(StateCode, CountyCode, LocalKey, out var countyCount, true);
if (!IsNullOrWhiteSpace(otherCounties))
{
control.RemoveCssClass("hidden");
control.InnerText = $"Parts of this local district are also in {otherCounties}";
}
return(countyCount);
}
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
// ReSharper disable UnusedMethodReturnValue.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
// ReSharper disable UnassignedField.Global
#endregion ReSharper disable
public static void GetReport(Control container, string countyElectionKey, bool openAll = false)
{
var stateCode = Elections.GetStateCodeFromKey(countyElectionKey);
var countyCode = Elections.GetCountyCodeFromKey(countyElectionKey);
var stateElectionKey = Elections.GetStateElectionKeyFromKey(countyElectionKey);
// We get a dictionary of locals with elections that match the stateElectionKey
// Key: localKey; Value: local electionKey
// Locals without an election will not be in the dictionary
// We can't forget the Ballot Measures...
var localKeys = LocalDistricts.GetLocalKeysForCounty(stateCode, countyCode);
var localElectionDictionary = ElectionsOffices.GetLocalElections(stateElectionKey,
countyCode, localKeys);
var localReferendumDictionary = Referendums.GetLocalElections(stateElectionKey, countyCode,
localKeys);
// merge them into the first dictionary
foreach (var kvp in localReferendumDictionary)
if (!localElectionDictionary.ContainsKey(kvp.Key))
localElectionDictionary.Add(kvp.Key, kvp.Value);
if (localElectionDictionary.Count == 0) return;
// We also get a dictionary of all local names for the county
var localNamesDictionary = LocalDistricts.GetNamesDictionary(stateCode, countyCode);
new HtmlDiv {InnerText = "Local District Elections"}.AddTo(container,
"accordion-header");
var content = new HtmlDiv().AddTo(container, "local-anchors accordion-content");
// For reporting we filter only locals with elections and sort by name,
var locals =
localNamesDictionary.Where(kvp => localElectionDictionary.ContainsKey(kvp.Key))
.OrderBy(kvp => kvp.Value, new AlphanumericComparer())
.ToList();
// get a dictionary of other county names for multiple counties
var otherCountyNames =
LocalIdsCodes.FormatOtherCountyNamesDictionary(stateCode, countyCode,
locals.Select(l => l.Key));
foreach (var kvp in locals)
{
var localKey = kvp.Key;
var localName = kvp.Value;
var otherCounties = otherCountyNames[localKey];
if (!IsNullOrWhiteSpace(otherCounties))
localName += $" (also in {otherCounties})";
var localElectionKey = localElectionDictionary[localKey];
CreatePublicElectionAnchor(localElectionKey, localName, openAll)
.AddTo(content, "local-anchor");
}
}
protected override bool Update(object newValue)
{
// everything has been validated already
var changed = false;
var stateCode = Page.StateCode;
var countyCode = Page.CountyCode;
foreach (var s in _Submitted)
{
if (s.New)
{
CountySupervisors.Insert(stateCode, s.Id, s.Name, countyCode, s.InShapefile);
if (s.Create)
{
// also create local district
var localKey = LocalDistricts.GetAvailableLocalKey(stateCode);
LocalDistricts.Insert(stateCode, localKey, s.Name, string.Empty, string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
string.Empty, false);
LocalIdsCodes.Insert(stateCode, LocalIdsCodes.LocalTypeCountySupervisors,
s.Id, localKey);
changed = true;
}
}
else if (s.Delete)
{
// can only delete if no local district entry
changed |=
CountySupervisors.DeleteByStateCodeCountySupervisorsCode(stateCode, s.Id) !=
0;
}
else
{
// existing item, but may update IsInShapefile or Name
changed |=
CountySupervisors.UpdateNameByStateCodeCountySupervisorsCode(s.Name,
stateCode, s.Id) != 0;
changed |=
CountySupervisors.UpdateIsInShapefileByStateCodeCountySupervisorsCode(
s.InShapefile, stateCode, s.Id) != 0;
}
}
LoadControl();
return(changed);
}
public static string FindCountyCode()
{
// reworked to eliminate ViewState references
var stateCode = FindStateCode();
if (IsNullOrWhiteSpace(stateCode))
{
return(Empty);
}
var countyCode = QueryCounty;
if (IsNullOrWhiteSpace(countyCode))
{
if (IsMasterUser || IsAdminUser)
{
countyCode = UserCountyCode;
if (IsNullOrWhiteSpace(countyCode))
{
var localKey = FindLocalKey();
if (!IsNullOrEmpty(localKey))
{
// if there is a local key and there is no county in the query string,
// we must look up a county. If there's more than 1, we just use the first we find.
var counties = LocalIdsCodes.FindCounties(stateCode, localKey);
countyCode = counties.Length == 0 ? Empty : counties[0];
}
else
{
countyCode = Elections.GetCountyCodeFromKey(QueryElection);
if (IsNullOrWhiteSpace(countyCode))
{
countyCode = Offices.GetCountyCodeFromKey(QueryOffice);
}
}
}
}
}
return(!IsNullOrWhiteSpace(countyCode) &&
CountyCache.CountyExists(stateCode, countyCode)
? countyCode
: Empty);
}
// ReSharper restore UnusedMember.Global
// ReSharper restore VirtualMemberNeverOverriden.Global
// ReSharper restore MemberCanBePrivate.Global
#endregion ReSharper restore
#endregion Protected
#region Event handlers and overrides
protected override void OnPreLoad(EventArgs e)
{
base.OnPreLoad(e);
if (AdminPageLevel == AdminPageLevel.Local)
{
var licTable = LocalIdsCodes.GetDataByStateCodeLocalKey(StateCode, LocalKey);
if (licTable.Count == 1)
{
var lic = licTable[0];
switch (lic.LocalType)
{
case LocalIdsCodes.LocalTypeTiger:
// Allow council for both places and cosubs - 04/10/19
IsTigerPlace =
TigerPlacesCounties.CountByStateCodeTigerTypeTigerCode(StateCode,
TigerPlacesCounties.TigerTypePlace, lic.LocalId) +
TigerPlacesCounties.CountByStateCodeTigerTypeTigerCode(StateCode,
TigerPlacesCounties.TigerTypeCousub, lic.LocalId) > 0;
if (IsTigerPlace)
{
TigerPlaceCode = lic.LocalId;
TigerPlaceName =
TigerPlaces.GetNameByStateCodeTigerCode(StateCode, TigerPlaceCode);
}
break;
case LocalIdsCodes.LocalTypeElementary:
case LocalIdsCodes.LocalTypeSecondary:
case LocalIdsCodes.LocalTypeUnified:
IsTigerSchool = true;
TigerSchoolType = lic.LocalType;
TigerSchoolCode = lic.LocalId;
TigerSchoolName =
TigerSchools.GetNameByStateCodeTigerCodeTigerType(StateCode, TigerSchoolCode,
TigerSchoolType);
break;
}
}
}
}
private static void GetLocalElections(Control container, string countyElectionKey, Control multiCountySection = null)
{
var stateCode = Elections.GetStateCodeFromKey(countyElectionKey);
var countyCode = Elections.GetCountyCodeFromKey(countyElectionKey);
var stateElectionKey = Elections.GetStateElectionKeyFromKey(countyElectionKey);
// We get a dictionary of locals with elections that match the stateElectionKey
// Key: localKey; Value: local electionKey
// Locals without an election will not be in the dictionary
var localElectionDictionary =
ElectionsOffices.GetLocalElections(stateElectionKey, countyCode);
// We can't forget the Ballot Measures...
var localReferendumDictionary =
Referendums.GetLocalElections(stateElectionKey, countyCode);
// merge them into the first dictionary
foreach (var kvp in localReferendumDictionary)
{
if (!localElectionDictionary.ContainsKey(kvp.Key))
{
localElectionDictionary.Add(kvp.Key, kvp.Value);
}
}
if (localElectionDictionary.Count == 0)
{
return;
}
// We also get a dictionary of all local names for the county
var localNamesDictionary = LocalDistricts.GetNamesDictionary(stateCode, countyCode);
// For reporting we filter only locals with elections and sort by name,
var locals = localNamesDictionary
.Where(kvp => localElectionDictionary.ContainsKey(kvp.Key))
.OrderBy(kvp => kvp.Value, new AlphanumericComparer()).ToList();
// Get all counties for the locals NB: the counties are pre-sorted by county name
var countiesForLocals =
LocalIdsCodes.FindCountiesWithNames(stateCode, locals.Select(kvp => kvp.Key));
foreach (var kvp in locals)
{
var localKey = kvp.Key;
var localName = kvp.Value;
var localElectionKey = localElectionDictionary[localKey];
var countiesForLocal = countiesForLocals[localKey];
var electionReport = new ElectionReportResponsive();
var localReport = electionReport.GenerateReport(localElectionKey, true);
// this will be either a state or county report (for local reports, GenerateReport
// is called directly)
//for either county or state, inject the name of the local district into the office
// if this is an office-title oe a referendum header
for (var inx = 0; inx < localReport.Controls.Count; inx += 2)
{
var header = localReport.Controls[inx] as HtmlContainerControl;
if (header?.HasClass("office-title") == true || header?.HasClass("referendum-header") == true)
{
header.Controls.AddAt(0,
new HtmlSpan {
InnerText = $"{localName}, "
}.AddCssClasses("local-name"));
}
}
//switch (reportLevel)
//{
//case ReportLevel.StateLevel:
// {
while (localReport.Controls.Count > 0)
{
if (countiesForLocal.Length > 1 && multiCountySection != null)
{
// if the county is the first in the counties, inject a multi-county message
// and move the pair (header and content) to the multi-counties section.
// Otherwise, discard it (it's a duplicate)
if (countyCode == countiesForLocal[0].Value)
{
//var countyMessage = "Parts of this district are in" +
// $" {LocalIdsCodes.FormatCountyNames(countiesForLocal, true)}";
//var header = localReport.Controls[0] as HtmlContainerControl;
//header?.Controls.Add(
// new HtmlP { InnerText = countyMessage }.AddCssClasses("county-message"));
//Debug.Assert(multiCountySection != null, "multiCountySection != null");
multiCountySection.Controls.Add(localReport.Controls[0]);
multiCountySection.Controls.Add(localReport.Controls[0]);
}
else
{
localReport.Controls.RemoveAt(0);
localReport.Controls.RemoveAt(0);
}
}
else
{
// move the pair (header and content) to the outer content
container.Controls.Add(localReport.Controls[0]);
container.Controls.Add(localReport.Controls[0]);
}
}
// }
// break;
//case ReportLevel.CountyLevel:
// {
//var first = true;
//while (localReport.Controls.Count > 0)
//{
// // for multi-county districts, inject an "other counties" message into the
// // first office header if multi county
// var countyMessage = Empty;
// if (first && countiesForLocal.Length > 1)
// countyMessage = "Parts of this local district are also in" +
// $" {LocalIdsCodes.FormatCountyNames(countiesForLocal, true, countyCode)}";
// var header = localReport.Controls[0] as HtmlContainerControl;
// if (!IsNullOrWhiteSpace(countyMessage))
// header?.Controls.Add(
// new HtmlP { InnerText = countyMessage }.AddCssClasses("county-message"));
// first = false;
// // move the pair (header and content) to the outer content
// container.Controls.Add(localReport.Controls[0]);
// container.Controls.Add(localReport.Controls[0]);
//}
//}
//break;
//}
}
}
public void Generate(ElectionReportResponsive electionReport, string electionKey,
bool isForAllStatesReport = false)
{
ElectionReport = electionReport;
ElectionKey = electionKey;
StateCode = Elections.GetStateCodeFromKey(electionKey);
CountyCode = Elections.GetCountyCodeFromKey(electionKey);
LocalKey = Elections.GetLocalKeyFromKey(electionKey);
var counties = LocalIdsCodes.FindCountiesWithNames(StateCode, LocalKey);
var isMultiCountyLocal = counties.Length > 1;
IsForAllStatesReport = isForAllStatesReport;
var offices = GetOffices()
.Where(g => g.First().ElectionsPoliticianKey() != null)
.ToList();
if (offices.Count <= 0)
{
return;
}
if (Elections.IsPrimaryElection(electionKey))
{
offices = FilterUncontestedOffices(offices)
.ToList();
}
Control container;
// only create a category wrapper if more than one office, or isMultiCountyLocal
if (isMultiCountyLocal || offices.Count > 1 || isForAllStatesReport)
{
// ReSharper disable once PossibleNullReferenceException
var title = new PlaceHolder();
// ReSharper disable once PossibleNullReferenceException
(new HtmlSpan().AddTo(title) as HtmlGenericControl).InnerText = GetCategoryTitle();
if (isMultiCountyLocal)
{
var allCounties =
LocalIdsCodes.FormatAllCountyNames(StateCode, LocalKey, true);
// ReSharper disable once PossibleNullReferenceException
(new HtmlP().AddTo(title) as HtmlGenericControl)
.InnerText = $"Parts of this local district are in {allCounties}";
}
// ReSharper disable once PossibleNullReferenceException
new HtmlDiv().AddTo(ElectionReport.ReportContainer, "category-title accordion-header")
.Controls.Add(title);
container = new HtmlDiv().AddTo(ElectionReport.ReportContainer,
"category-content accordion-content");
}
else
{
container = ElectionReport.ReportContainer;
}
foreach (var office in offices)
{
var politicians = office.ToList();
var officeInfo = politicians[0];
var officeContent = new HtmlDiv();
CreateOfficeTitle(container, officeContent, politicians,
GetOfficeTitle(officeInfo));
officeContent.AddTo(container, "office-content accordion-content");
//var isRunningMateOffice = officeInfo.IsRunningMateOffice() &&
// !Elections.IsPrimaryElection(electionKey);
var isRunningMateOffice = Offices.IsRunningMateOfficeInElection(electionKey,
officeInfo.OfficeKey());
ReportOffice(officeContent, isRunningMateOffice, politicians,
ElectionReport._DataManager);
}
}
protected void ButtonTigerSettings_OnClick(object sender, EventArgs e)
{
switch (TigerSettingsReloading.Value)
{
case "reloading":
{
TigerSettingsReloading.Value = Empty;
_TigerSettingsTabInfo.ClearValidationErrors();
LoadTigerControlsForTigerSettings();
FeedbackTigerSettings.AddInfo("Tiger settings loaded.");
}
break;
case "":
{
try
{
// normal update
_TigerSettingsTabInfo.ClearValidationErrors();
var tigerData =
LocalIdsCodes.GetLocalData(StateCode, LocalKey).Rows.Cast <DataRow>().ToList();
PopulateTigerDistrictsForTigerSettings(tigerData, true);
// phase 2 checks
if (TigerChecks(out var localType, out var localIds,
ControlTigerSettingsEntireCounty, ControlTigerSettingsTigerDistrict,
ControlTigerSettingsTigerPlace, ControlTigerSettingsSchoolDistrict,
ControlTigerSettingsCouncilDistrict, ControlTigerSettingsSupervisorsDistrict,
FeedbackTigerSettings))
{
return;
}
// delete TigerPlacesCounties row if type Vote -- these are not intrinsically tied
// to county(s)
var localIdsCodes = LocalIdsCodes.GetDataByStateCodeLocalKey(StateCode,
LocalKey);
Debug.Assert(localIdsCodes.Count == 1);
if (localIdsCodes[0].LocalType == LocalIdsCodes.LocalTypeVote)
{
TigerPlacesCounties.DeleteByStateCodeCountyCodeTigerCodeTigerType(StateCode,
CountyCode, localIdsCodes[0].LocalId, LocalIdsCodes.LocalTypeVote);
}
// delete existing
LocalIdsCodes.DeleteByStateCodeLocalKey(StateCode, LocalKey);
// add new
foreach (var localId in localIds)
{
LocalIdsCodes.Insert(StateCode, localType, localId, LocalKey);
if (localType == LocalIdsCodes.LocalTypeVote)
{
TigerPlacesCounties.Insert(StateCode, CountyCode, localId,
TigerPlacesCounties.TigerTypeVote);
}
}
LoadTigerControlsForTigerSettings();
FeedbackTigerSettings.AddInfo("Tiger Settings changed.");
}
catch (Exception ex)
{
FeedbackTigerSettings.HandleException(ex);
}
}
break;
default:
throw new VoteException($"Unknown reloading option: '{TigerSettingsReloading.Value}'");
}
}
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)
{
Server.ScriptTimeout = 1800;
var electionKey = Request.QueryString["election"];
var csvType = Request.QueryString["type"];
var includeCandidates = csvType != "BM";
var includeCandidateInfo = csvType == "NA" || csvType == "WA";
var includeAnswers = csvType == "OA" || csvType == "WA";
var includeBallotMeasures = csvType == "BM";
if (IsNullOrWhiteSpace(electionKey))
{
throw new VoteException("Election key is missing.");
}
var electionDescription = Elections.GetElectionDesc(electionKey);
if (IsNullOrWhiteSpace(electionDescription))
{
throw new VoteException("Election key is invalid.");
}
// make sure it's a valid filename
var invalidFileChars = Path.GetInvalidFileNameChars();
electionDescription = Regex.Replace(electionDescription, ".",
match => new[] { ' ', ',', '"', '\'' }.Contains(match.Value[0]) ||
invalidFileChars.Contains(match.Value[0])
? "_"
: match.Value);
electionDescription = Regex.Replace(electionDescription, "__+", "_");
// get the data
var table = includeBallotMeasures
? Referendums.GetElectionCsvReferendumData(electionKey)
: ElectionsPoliticians.GetElectionCsvCandidateData(electionKey);
// if we're including answers, get the answers data for each office in the election
var qas = new List <QuestionAndAnswer>();
var columns = new List <dynamic>();
if (includeAnswers)
{
var answers = new List <DataRow>();
// get a list of all offices in the election
var allOfficeKeys = table.Rows.OfType <DataRow>().Select(r => r.OfficeKey()).Distinct()
.ToList();
// collect all the answers
foreach (var officeKey in allOfficeKeys)
{
var oldAnswerCutoff =
ElectionsOffices.GetOldAnswerCutoffDate(electionKey, officeKey);
// the GroupBy is to eliminate duplicate answers if a question is in more than one issue
answers.AddRange(ElectionsPoliticians.GetCompareCandidateIssuesNew(electionKey, officeKey)
.Rows.OfType <DataRow>()
.GroupBy(r => new
{
PoliticianKey = r.PoliticianKey(),
QuestionKey = r.QuestionKey(),
Sequence = r.Sequence()
})
.Select(g => g.First()));
// convert the answers to QuestionAndAnswer format
foreach (var p in answers.GroupBy(r => r.PoliticianKey()))
{
qas.AddRange(ResponsiveIssuesReport.SplitOutVideos(ResponsiveIssuesReport.GetQuestionAndAnswerList(p,
Politicians.GetData(p.Key)[0], false)).Where(qa => qa.ResponseDate > oldAnswerCutoff));
}
}
// analyze qas to determine which topic columns we need to include
columns.AddRange(qas
.GroupBy(qa =>
new { qa.QuestionKey, IsYouTube = !IsNullOrWhiteSpace(qa.YouTubeUrl) }).Select(
g => new
{
g.Key.QuestionKey,
g.Key.IsYouTube,
Topic = $"{g.First().Question}{(g.Key.IsYouTube ? " Video" : Empty)}"
}).OrderBy(q => q.Topic));
}
// create the csv
string csv;
using (var ms = new MemoryStream())
{
var streamWriter = new StreamWriter(ms);
var csvWriter = new SimpleCsvWriter();
// write headers
csvWriter.AddField("Jurisdiction");
csvWriter.AddField("State Code");
if (csvType != "OK")
{
csvWriter.AddField("County");
csvWriter.AddField("City or District");
csvWriter.AddField("Election Name");
csvWriter.AddField("Election Date");
csvWriter.AddField("VoteUSA Election Id");
}
if (includeCandidates && csvType != "OK")
{
csvWriter.AddField("Office");
csvWriter.AddField("Office Class");
csvWriter.AddField("District");
csvWriter.AddField("VoteUSA Office Id");
csvWriter.AddField("Running Mate?");
csvWriter.AddField("Candidate");
csvWriter.AddField("First Name");
csvWriter.AddField("Middle Name");
csvWriter.AddField("Nickname");
csvWriter.AddField("Last Name");
csvWriter.AddField("Suffix");
csvWriter.AddField("Party");
csvWriter.AddField("VoteUSA Id");
}
if (csvType == "OK")
{
csvWriter.AddField("County Code");
csvWriter.AddField("County");
csvWriter.AddField("Local Key");
csvWriter.AddField("Local Name");
csvWriter.AddField("Election Key");
csvWriter.AddField("Office Key");
csvWriter.AddField("Office");
csvWriter.AddField("Politician Key");
csvWriter.AddField("Politician Password");
csvWriter.AddField("Candidate");
csvWriter.AddField("Party Code");
csvWriter.AddField("Ad Enabled");
csvWriter.AddField("YouTube Video Url");
csvWriter.AddField("YouTube Channel or Playlist Url");
csvWriter.AddField("Compare Candidates Url");
csvWriter.AddField("Type");
csvWriter.AddField("Date");
csvWriter.AddField("Amount");
csvWriter.AddField("Email");
csvWriter.AddField("Banner Ad Url");
}
if (includeCandidateInfo)
{
csvWriter.AddField("Intro Url");
csvWriter.AddField("Photo100 Url");
csvWriter.AddField("Photo200 Url");
csvWriter.AddField("Photo300 Url");
csvWriter.AddField("Postal Street Address");
csvWriter.AddField("Postal City, State Zip");
csvWriter.AddField("Phone");
csvWriter.AddField("Email");
csvWriter.AddField("Date of Birth");
}
if (!includeAnswers && !includeBallotMeasures && csvType != "OK")
{
csvWriter.AddField("General Philosophy");
csvWriter.AddField("Personal and Family");
csvWriter.AddField("Education");
csvWriter.AddField("Profession");
csvWriter.AddField("Military");
csvWriter.AddField("Civic");
csvWriter.AddField("Political Experience");
csvWriter.AddField("Religious Affiliation");
csvWriter.AddField("Accomplishment and Awards");
}
if (includeCandidateInfo)
{
csvWriter.AddField("Website Url");
csvWriter.AddField("Facebook Url");
csvWriter.AddField("YouTube Url");
csvWriter.AddField("Flickr Url");
csvWriter.AddField("Twitter Url");
csvWriter.AddField("RSS Feed Url");
csvWriter.AddField("Wikipedia Url");
csvWriter.AddField("BallotPedia Url");
csvWriter.AddField("Vimeo Url");
csvWriter.AddField("Google+ Url");
csvWriter.AddField("LinkedIn Url");
csvWriter.AddField("Pinterest Url");
csvWriter.AddField("Blogger Url");
csvWriter.AddField("Podcast Url");
csvWriter.AddField("Instagram Url");
csvWriter.AddField("GoFundMe Url");
csvWriter.AddField("Crowdpac Url");
}
if (includeAnswers)
{
foreach (var column in columns)
{
csvWriter.AddField(column.Topic);
}
}
if (includeBallotMeasures)
{
csvWriter.AddField("Ballot Measure Title");
csvWriter.AddField("Ballot Measure Description");
csvWriter.AddField("Ballot Measure Detail");
csvWriter.AddField("Ballot Measure Detail URL");
csvWriter.AddField("Ballot Measure Full Text");
csvWriter.AddField("Ballot Measure Full Text URL");
csvWriter.AddField("Ballot Measure Passed");
}
csvWriter.Write(streamWriter);
var stateCode = Elections.GetStateCodeFromKey(electionKey);
// do a first pass to get counties for all locals
var allLocals = new List <string>();
foreach (var row in table.Rows.Cast <DataRow>())
{
if (!IsNullOrWhiteSpace(row.LocalKey()))
{
allLocals.Add(row.LocalKey());
}
}
var countiesForLocals =
LocalIdsCodes.FindCountiesWithNames(stateCode, allLocals.Distinct());
var rows = table.Rows.Cast <DataRow>();
if (csvType == "OK")
{
rows = rows.OrderBy(r => r.OfficeLevel())
//.ThenBy(r => r.DistrictCode())
//.ThenBy(r => r.OfficeOrderWithinLevel())
//.ThenBy(r => r.OfficeLine1())
//.ThenBy(r => r.OfficeLine2())
.ThenBy(r => Offices.FormatOfficeName(r),
MixedNumericComparer.Instance)
.ThenBy(r => r.OrderOnBallot())
.ThenBy(r => r.PoliticianKey(), StringComparer.OrdinalIgnoreCase)
.ThenBy(r => r.IsRunningMate());
}
foreach (var row in rows)
{
string jurisdiction;
var politicianKey = Empty;
if (includeBallotMeasures)
{
if (!IsNullOrWhiteSpace(row.LocalKey()))
{
jurisdiction = "Local";
}
else if (!IsNullOrWhiteSpace(row.CountyCode()))
{
jurisdiction = "County";
}
else
{
jurisdiction = "State";
}
}
else
{
politicianKey = row.IsRunningMate()
? row.RunningMateKey()
: row.PoliticianKey();
switch (Offices.GetElectoralClass(row.OfficeClass()))
{
case ElectoralClass.USPresident:
case ElectoralClass.USSenate:
case ElectoralClass.USHouse:
jurisdiction = "Federal";
break;
case ElectoralClass.USGovernors:
case ElectoralClass.State:
jurisdiction = "State";
break;
case ElectoralClass.County:
jurisdiction = "County";
break;
case ElectoralClass.Local:
jurisdiction = "Local";
break;
default:
jurisdiction = Empty;
break;
}
}
var photo100Url = Empty;
if (includeCandidateInfo)
{
var qsc100 = new QueryStringCollection
{
{
"id", politicianKey
},
{
"Col", "Headshot100"
}
};
var photo100Uri = UrlManager.GetSiteUri("image.aspx", qsc100);
photo100Url = new UriBuilder(photo100Uri)
{
Scheme = Uri.UriSchemeHttps,
Host = UrlManager.GetCanonicalLiveHostName(photo100Uri.Host),
Port = 443
}.Uri.ToString();
}
var photo200Url = Empty;
if (includeCandidateInfo)
{
var qsc200 = new QueryStringCollection
{
{ "id", politicianKey }, { "Col", "Profile200" }
};
var photo200Uri = UrlManager.GetSiteUri("image.aspx", qsc200);
photo200Url = new UriBuilder(photo200Uri)
{
Scheme = Uri.UriSchemeHttps,
Host = UrlManager.GetCanonicalLiveHostName(photo200Uri.Host),
Port = 443
}.Uri.ToString();
}
var photo300Url = Empty;
if (includeCandidateInfo)
{
var qsc300 = new QueryStringCollection
{
{ "id", politicianKey }, { "Col", "Profile300" }
};
var photo300Uri = UrlManager.GetSiteUri("image.aspx", qsc300);
photo300Url = new UriBuilder(photo300Uri)
{
Scheme = Uri.UriSchemeHttps,
Host = UrlManager.GetCanonicalLiveHostName(photo300Uri.Host),
Port = 443
}.Uri.ToString();
}
var introUrl = Empty;
if (includeCandidateInfo)
{
var introUri = UrlManager.GetIntroPageUri(politicianKey);
introUrl = new UriBuilder(introUri)
{
Scheme = Uri.UriSchemeHttps,
Host = UrlManager.GetCanonicalLiveHostName(introUri.Host),
Port = 443
}.Uri.ToString();
}
var district = Empty;
if (includeCandidates)
{
if (int.TryParse(row.DistrictCode(), out var districtNumber))
{
district = districtNumber.ToString(CultureInfo.InvariantCulture);
}
}
// convert to simple name if national
var partyName = Empty;
if (includeCandidates)
{
partyName = Parties.GetNationalPartyDescription(row.PartyCode(),
row.PartyName());
}
var county = IsNullOrWhiteSpace(row.County()) ? Empty : row.County();
var local = Empty;
if (!IsNullOrWhiteSpace(row.LocalKey()))
{
local = row.LocalDistrict();
county = Join(", ", countiesForLocals[row.LocalKey()].Select(c => c.Text));
}
csvWriter.AddField(jurisdiction);
csvWriter.AddField(stateCode);
if (csvType != "OK")
{
csvWriter.AddField(county);
csvWriter.AddField(local);
csvWriter.AddField(row.ElectionDescription());
csvWriter.AddField(row.ElectionDate().ToString("d"));
csvWriter.AddField(row.ElectionKey());
}
if (includeCandidates && csvType != "OK")
{
csvWriter.AddField(Offices.FormatOfficeName(row));
csvWriter.AddField(Offices.GetOfficeClassShortDescriptionExtended(row));
csvWriter.AddField(district);
csvWriter.AddField(row.OfficeKey());
csvWriter.AddField(row.IsRunningMate() ? row.PoliticianKey() : Empty);
csvWriter.AddField(Politicians.FormatName(row));
csvWriter.AddField(row.FirstName());
csvWriter.AddField(row.MiddleName());
csvWriter.AddField(row.Nickname());
csvWriter.AddField(row.LastName());
csvWriter.AddField(row.Suffix());
csvWriter.AddField(partyName);
csvWriter.AddField(politicianKey);
}
if (csvType == "OK")
{
var youTubeVideoUrl = Empty;
var youTubeAdWebAddress = row.AdUrl();
if (!IsNullOrWhiteSpace(youTubeAdWebAddress))
{
youTubeVideoUrl = youTubeAdWebAddress.IsValidYouTubeVideoUrl()
? youTubeAdWebAddress
: "channel or playlist";
}
var adEnabled = Empty;
if (!IsNullOrWhiteSpace(row.AdType()))
{
adEnabled = row.AdEnabled() ? "E" : "D";
}
var compareUrl =
UrlManager.GetCompareCandidatesPageUri(row.ElectionKey(), row.OfficeKey()) +
$"&ad={politicianKey}";
csvWriter.AddField(row.CountyCode());
csvWriter.AddField(row.County().SafeString());
csvWriter.AddField(row.LocalKey());
csvWriter.AddField(row.LocalDistrict().SafeString());
csvWriter.AddField(row.ElectionKey());
csvWriter.AddField(row.OfficeKey());
csvWriter.AddField(Offices.FormatOfficeName(row.OfficeLine1(),
row.OfficeLine2(), row.OfficeKey()));
csvWriter.AddField(politicianKey);
csvWriter.AddField(row.Password());
csvWriter.AddField(Politicians.FormatName(row));
csvWriter.AddField(row.PartyCode().SafeString());
csvWriter.AddField(adEnabled);
csvWriter.AddField(youTubeVideoUrl);
csvWriter.AddField(row.YouTubeWebAddress());
csvWriter.AddField($"=HYPERLINK(\"{compareUrl}\",\"{compareUrl}\")");
csvWriter.AddField(Empty);
csvWriter.AddField(Empty);
csvWriter.AddField(Empty);
csvWriter.AddField(Empty);
csvWriter.AddField(Empty);
}
if (includeCandidateInfo)
{
csvWriter.AddField(introUrl);
csvWriter.AddField(photo100Url);
csvWriter.AddField(photo200Url);
csvWriter.AddField(photo300Url);
csvWriter.AddField(row.PublicAddress());
csvWriter.AddField(row.PublicCityStateZip());
csvWriter.AddField(row.PublicPhone());
csvWriter.AddField(row.PublicEmail());
csvWriter.AddField(row.DateOfBirth().ToString("d"));
}
if (!includeAnswers && !includeBallotMeasures && csvType != "OK")
{
csvWriter.AddField(row.GeneralStatement().SafeString());
csvWriter.AddField(row.Personal().SafeString());
csvWriter.AddField(row.Education().SafeString());
csvWriter.AddField(row.Profession().SafeString());
csvWriter.AddField(row.Military().SafeString());
csvWriter.AddField(row.Civic().SafeString());
csvWriter.AddField(row.Political().SafeString());
csvWriter.AddField(row.Religion().SafeString());
csvWriter.AddField(row.Accomplishments().SafeString());
}
if (includeCandidateInfo)
{
csvWriter.AddField(row.PublicWebAddress());
csvWriter.AddField(row.FacebookWebAddress());
csvWriter.AddField(row.YouTubeWebAddress());
csvWriter.AddField(row.FlickrWebAddress());
csvWriter.AddField(row.TwitterWebAddress());
csvWriter.AddField(row.RssFeedWebAddress());
csvWriter.AddField(row.WikipediaWebAddress());
csvWriter.AddField(row.BallotPediaWebAddress());
csvWriter.AddField(row.VimeoWebAddress());
csvWriter.AddField(row.GooglePlusWebAddress());
csvWriter.AddField(row.LinkedInWebAddress());
csvWriter.AddField(row.PinterestWebAddress());
csvWriter.AddField(row.BloggerWebAddress());
csvWriter.AddField(row.PodcastWebAddress());
csvWriter.AddField(row.WebstagramWebAddress());
csvWriter.AddField(row.GoFundMeWebAddress());
csvWriter.AddField(row.CrowdpacWebAddress());
}
if (includeAnswers)
{
var data = qas.Where(qa => qa.PoliticianKey == politicianKey)
.OrderByDescending(qa => qa.ResponseDate)
.ToList();
foreach (var column in columns)
{
var response = data.FirstOrDefault(d => d.QuestionKey == column.QuestionKey &&
d.HasVideo == column.IsYouTube);
var field = Empty;
if (response != null)
{
if (response.HasVideo)
{
field = response.YouTubeUrl;
}
else
{
field = $"{response.Answer}" +
$"\n\n{(IsNullOrWhiteSpace(response.AnswerSource) ? Empty : $" Source: {response.AnswerSource}")}" +
$" ({response.AnswerDate:M/d/yyyy})";
}
}
csvWriter.AddField(field);
}
}
if (includeBallotMeasures)
{
csvWriter.AddField(row.ReferendumTitle());
csvWriter.AddField(row.ReferendumDescription());
csvWriter.AddField(row.ReferendumDetail());
csvWriter.AddField(row.ReferendumDetailUrl());
csvWriter.AddField(row.ReferendumFullText());
csvWriter.AddField(row.ReferendumFullTextUrl());
csvWriter.AddField(row.IsPassed() ? "Y" : Empty);
}
csvWriter.Write(streamWriter);
}
streamWriter.Flush();
ms.Position = 0;
csv = new StreamReader(ms).ReadToEnd();
}
// download
var filename = electionDescription;
switch (csvType)
{
case "NA":
filename += " without topics";
break;
case "OA":
filename += " - topics only";
break;
case "WA":
filename += " - all data including topics";
break;
case "OK":
filename += " - names and keys only";
break;
case "BM":
filename += " - Ballot Measures";
break;
}
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition",
$"attachment;filename=\"{filename}.csv\"");
Response.Write("\xfeff"); // BOM
Response.Write(csv);
Response.End();
}
protected void ButtonDeleteDistricts_OnClick(object sender, EventArgs e)
{
switch (DeleteDistrictsReloading.Value)
{
case "reloading":
{
DeleteDistrictOverride.AddCssClasses("hidden");
ControlDeleteDistrictOverride.Checked = false;
_DeleteDistrictsTabInfo.ClearValidationErrors();
DeleteDistrictsReloading.Value = Empty;
PopulateLocalDistrictDropdown();
_DeleteDistrictsTabInfo.LoadControls();
FeedbackDeleteDistricts.AddInfo("Delete Districts information loaded.");
}
break;
case "":
{
// normal update
DeleteDistrictOverride.AddCssClasses("hidden");
_DeleteDistrictsTabInfo.ClearValidationErrors();
var localKey = ControlDeleteDistrictsLocalKey.GetValue();
var localDistrict = LocalDistricts.GetLocalDistrictByStateCodeLocalKey(StateCode,
localKey);
var referenceList = new List <string>();
if (!ControlDeleteDistrictOverride.Checked)
{
// check for references in other counties
var otherCounties = new List <string>();
foreach (var oc in
LocalIdsCodes.GetOtherCountyReferences(StateCode, CountyCode, localKey)
.Rows.OfType <DataRow>())
{
otherCounties.Add(oc.County());
}
// check for meaningful usages
FormatReferences(referenceList, "Elections",
Elections.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "ElectionsOffices",
ElectionsOffices.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "ElectionsPoliticians",
ElectionsPoliticians.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "QuestionsJurisdictions",
QuestionsJurisdictions.CountByIssueLevelStateCodeCountyOrLocal(
Issues.IssueLevelLocal, StateCode, localKey));
FormatReferences(referenceList, "Offices",
Offices.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "OfficesOfficials",
OfficesOfficials.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "Referendums",
Referendums.CountByStateCodeLocalKey(StateCode, localKey));
if (referenceList.Count > 0 || otherCounties.Count > 0)
{
DeleteDistrictOverride.RemoveCssClass("hidden");
if (otherCounties.Count > 0)
{
FeedbackDeleteDistricts.PostValidationError(ControlDeleteDistrictsLocalKey,
"The district is referenced in the following other counties. If you delete this" +
" district it will be removed from the other counties also. Check the box to" +
" override.");
foreach (var oc in otherCounties)
{
FeedbackDeleteDistricts.AddError(oc);
}
}
if (referenceList.Count > 0)
{
FeedbackDeleteDistricts.PostValidationError(ControlDeleteDistrictsLocalKey,
"Cannot delete because the LocalKey is referenced in the following tables. Check the box to override.");
foreach (var @ref in referenceList)
{
FeedbackDeleteDistricts.AddError(@ref);
}
}
return;
}
}
// delete
ControlDeleteDistrictOverride.Checked = false;
DeleteAllDistictReferences(StateCode, localKey);
LocalDistricts.DeleteByStateCodeLocalKey(StateCode, localKey);
// delete TigerPlacesCounties rows if type Vote -- these are not intrinsically tied
// to county(s)
var localIdsCodes = LocalIdsCodes.GetDataByStateCodeLocalKey(StateCode, localKey);
Debug.Assert(localIdsCodes.Count == 1);
if (localIdsCodes[0].LocalType == LocalIdsCodes.LocalTypeVote)
{
TigerPlacesCounties.DeleteByStateCodeTigerTypeTigerCode(StateCode,
LocalIdsCodes.LocalTypeVote, localIdsCodes[0].LocalId);
}
LocalIdsCodes.DeleteByStateCodeLocalKey(StateCode, localKey);
_DeleteDistrictsTabInfo.ClearValidationErrors();
PopulateLocalDistrictDropdown();
_DeleteDistrictsTabInfo.LoadControls();
NavigateJurisdictionUpdatePanel.Update();
NavigateJurisdiction.Initialize();
FeedbackDeleteDistricts.AddInfo(
$"Local District {localDistrict} ({localKey}) deleted.");
}
break;
default:
throw new VoteException(
$"Unknown reloading option: '{DeleteDistrictsReloading.Value}'");
}
}
private bool TigerChecks(out string localType, out List <string> localIds,
HtmlInputCheckBox controlEntireCounty, DropDownList controlTigerDistrict,
DropDownList controlTigerPlace, DropDownList controlSchoolDistrict,
DropDownList controlCityCouncilDistrict, DropDownList controlCountySupervisorsDistrict,
FeedbackContainerControl feedback)
{
localType = Empty;
localIds = new List <string>();
if (controlEntireCounty.Checked)
{
localType = LocalIdsCodes.LocalTypeVote;
localIds.Add(LocalIdsCodes.GetNextVoteIdForState(StateCode));
}
else
{
var tigerDistrict = controlTigerDistrict.GetValue();
var tigerPlace = controlTigerPlace.GetValue();
var schoolDistrict = controlSchoolDistrict.GetValue();
var cityCouncilDistrict = controlCityCouncilDistrict.GetValue();
var countySupervisorsDistrict = controlCountySupervisorsDistrict.GetValue();
var selections = 0;
if (!IsNullOrWhiteSpace(tigerPlace))
{
selections++;
localType = LocalIdsCodes.LocalTypeTiger;
localIds.Add(tigerPlace);
}
if (!IsNullOrWhiteSpace(tigerDistrict))
{
selections++;
localType = LocalIdsCodes.LocalTypeTiger;
localIds.Add(tigerDistrict);
}
if (!IsNullOrWhiteSpace(schoolDistrict))
{
selections++;
localType = schoolDistrict.Substring(0, 1);
localIds.Add(schoolDistrict.Substring(1));
}
if (!IsNullOrWhiteSpace(cityCouncilDistrict))
{
selections++;
localType = LocalIdsCodes.LocalTypeCityCouncil;
localIds.Add(cityCouncilDistrict);
}
if (!IsNullOrWhiteSpace(countySupervisorsDistrict))
{
selections++;
localType = LocalIdsCodes.LocalTypeCountySupervisors;
localIds.Add(countySupervisorsDistrict);
}
if (selections == 0)
{
feedback.PostValidationError(
new[]
{
controlTigerDistrict, controlTigerPlace, controlSchoolDistrict,
controlCityCouncilDistrict, controlCountySupervisorsDistrict
},
"At least one item must be selected");
return(true);
}
if (selections > 1)
{
feedback.PostValidationError(
new[]
{
controlTigerDistrict, controlTigerPlace, controlSchoolDistrict,
controlCityCouncilDistrict, controlCountySupervisorsDistrict
},
"Conflicting selections");
return(true);
}
}
return(false);
}
public static DataTable GetLoggedEmails(string[] contactTypes, string jurisdictionLevel,
string[] stateCodes, string[] countyCodes, string[] localKeysOrCodes,
DateTime beginTime, DateTime endTime, bool reportSuccess, bool reportFailure,
bool reportFlagged, bool reportUnflagged, int maximumResults, string[] froms, string[] tos,
string[] users, string[] electionKeys, string[] officeKeys, string[] candidateKeys,
string[] politicianKeys, int[] batchIds, int commandTimeout = -1)
{
const string cmdTemplate =
"SELECT le.`Subject`,le.Contact,le.CountyCode,le.ElectionKey,le.Id," +
"le.LocalKey,le.LogBatchId,le.OfficeKey,le.PoliticianKey,le.ErrorMessage," +
"le.SentTime,le.StateCode,le.ToEmail,le.WasSent,le.ForwardedCount," +
"le.IsFlagged,lb.ContactType,lb.FromEmail,lb.UserName,l.LocalDistrict," +
"e.ElectionDesc,o.OfficeLine1,o.OfficeLine2,p.Fname,p.Mname," +
"p.Lname,p.Nickname,p.Suffix,pt.PartyCode FROM votelog.LogEmail le" +
" INNER JOIN votelog.LogEmailBatches lb ON lb.Id=le.LogBatchId" +
" INNER JOIN States s ON s.StateCode=le.StateCode" +
" LEFT OUTER JOIN Elections e ON e.ElectionKey=le.ElectionKey" +
" LEFT OUTER JOIN Offices o ON o.OfficeKey=le.OfficeKey" +
" LEFT OUTER JOIN Politicians p ON p.PoliticianKey=le.PoliticianKey" +
" LEFT OUTER JOIN Parties pt ON pt.PartyKey=p.PartyKey" +
" LEFT OUTER JOIN LocalDistricts l ON l.StateCode=le.StateCode AND " +
" le.LocalKey!='' AND l.LocalKey=le.LocalKey" +
" {0} {1}";
// Build the where clause
var outerAnds = new List <string>();
var outerOrs = new List <string>();
var middleAnds = new List <string>();
var middleOrs = new List <string>();
var innerAnds = new List <string>();
var allContacts = contactTypes.Length == 1 && contactTypes[0] == "all";
var allStates = stateCodes.Length == 1 && stateCodes[0] == "all";
var allCounties = countyCodes.Length == 1 && countyCodes[0] == "all";
var allLocals = localKeysOrCodes.Length == 1 && localKeysOrCodes[0] == "all";
var allElections = electionKeys.Length == 1 && electionKeys[0] == "all";
var allOffices = officeKeys.Length == 1 && officeKeys[0] == "all";
var allCandidates = candidateKeys.Length == 1 && candidateKeys[0] == "all";
// success clause (applies to everything)
if (reportSuccess != reportFailure)
{
outerAnds.Add("le.WasSent=" + (reportSuccess ? "1" : "0"));
}
// flagged clause (applies to everything)
if (reportFlagged != reportUnflagged)
{
outerAnds.Add("le.IsFlagged=" + (reportSuccess ? "1" : "0"));
}
// tos (applies to everything)
if (tos.Length > 0)
{
outerAnds.Add(tos.SqlIn("le.ToEmail"));
}
// batchIds
if (batchIds.Length > 0)
{
outerOrs.Add(batchIds.SqlIn("lb.Id"));
}
// dates
if (beginTime != DateTime.MinValue)
{
middleAnds.Add("le.SentTime>=@BeginTime");
}
if (endTime != DateTime.MaxValue)
{
middleAnds.Add("le.SentTime<@EndTime");
}
// froms (a single address in the db)
if (froms.Length > 0)
{
middleAnds.Add(froms.SqlIn("lb.FromEmail"));
}
// users
if (users.Length > 0)
{
middleAnds.Add(users.SqlIn("lb.UserName"));
}
// politicianKeys
if (politicianKeys.Length > 0)
{
middleOrs.Add(politicianKeys.SqlIn("le.PoliticianKey"));
}
// anaylyze states, counties, locals, elections, offices and
// candidates based on specificity
if (contactTypes.Length > 0 && (allElections || electionKeys.Length == 0))
{
switch (jurisdictionLevel)
{
case "states":
if (stateCodes.Length == 0) // force no results from this section
{
innerAnds.Add("le.StateCode=''");
}
else if (!allStates)
{
innerAnds.Add(stateCodes.SqlIn("le.StateCode"));
}
break;
case "counties":
if (stateCodes.Length == 0 || countyCodes.Length == 0) // force no results from this section
{
innerAnds.Add("le.StateCode=''");
}
else if (allCounties)
{
if (!allStates)
{
innerAnds.Add(stateCodes.SqlIn("le.StateCode"));
}
innerAnds.Add("le.CountyCode<>''");
}
else if (countyCodes.Length > 0)
{
Debug.Assert(stateCodes.Length == 1, "Expecting a single state");
innerAnds.Add("le.StateCode=" + stateCodes[0].SqlLit());
innerAnds.Add(countyCodes.SqlIn("le.CountyCode"));
}
break;
case "locals":
if (stateCodes.Length == 0 || countyCodes.Length == 0 ||
localKeysOrCodes.Length == 0) // force no results from this section
{
innerAnds.Add("le.StateCode=''");
}
else if (allCounties)
{
// implies allLocals
if (!allStates)
{
innerAnds.Add(stateCodes.SqlIn("le.StateCode"));
}
innerAnds.Add("le.LocalKey<>''");
}
else
{
// selected counties implies single state
innerAnds.Add("le.StateCode=" + stateCodes[0].SqlLit());
if (allLocals)
{
// we need to do a pre-query to get all the locals in the selected counties
var localKeys = LocalDistricts.GetLocalKeysForCounties(stateCodes[0],
countyCodes);
innerAnds.Add(localKeys.SqlIn("le.LocalKey"));
}
else
{
innerAnds.Add(localKeysOrCodes.SqlIn("le.LocalKey"));
}
}
break;
}
}
else if (electionKeys.Length == 0) // exclude election-coded emails
{
innerAnds.Add("le.ElectionKey=''");
}
else if (allOffices)
{
innerAnds.Add(electionKeys.SqlIn("le.ElectionKey"));
}
else
{
Debug.Assert(electionKeys.Length == 1, "Expecting a single electionKey");
innerAnds.Add("le.ElectionKey=" + electionKeys[0].SqlLit());
if (officeKeys.Length == 0)
{
innerAnds.Add("le.OfficeKey=''");
}
else if (allCandidates)
{
innerAnds.Add(officeKeys.SqlIn("le.OfficeKey"));
}
else
{
Debug.Assert(officeKeys.Length == 1, "Expecting a single officeKey");
innerAnds.Add("le.OfficeKey=" + officeKeys[0].SqlLit());
innerAnds.Add(candidateKeys.Length == 0
? "le.PoliticianKey=''"
: candidateKeys.SqlIn("le.PoliticianKey"));
}
}
if (!allContacts)
{
innerAnds.Add(contactTypes.SqlIn("lb.ContactType"));
}
if (innerAnds.Count > 0) // combine into an OR
{
middleOrs.Add(Join(" AND ", innerAnds));
}
if (middleOrs.Count > 0) // combine into an AND
{
middleAnds.Add("(" + Join(" OR ", middleOrs) + ")");
}
if (middleAnds.Count > 0) // combine into an OR
{
outerOrs.Add(Join(" AND ", middleAnds));
}
if (outerOrs.Count > 0) // combine into an AND
{
outerAnds.Add("(" + Join(" OR ", outerOrs) + ")");
}
var whereClause = outerAnds.Count > 0
? "WHERE " + Join(" AND ", outerAnds)
: Empty;
var limitClause = maximumResults > 0
? "LIMIT " + maximumResults
: Empty;
var cmdText = Format(cmdTemplate, whereClause, limitClause);
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
if (beginTime != DateTime.MinValue)
{
VoteLogDb.AddCommandParameter(cmd, "BeginTime", beginTime);
}
if (endTime != DateTime.MaxValue)
{
VoteLogDb.AddCommandParameter(cmd, "EndTime", endTime);
}
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
}
// create a dictionary of counties for any local keys
var countyDict =
table.Rows.OfType <DataRow>()
.Where(r => !IsNullOrWhiteSpace(r.LocalKey()))
.Select(r => new {
StateCode = r.StateCode(),
LocalKey = r.LocalKey()
})
.Distinct()
.ToDictionary(o => o,
o =>
{
var codes = LocalIdsCodes.FindCounties(o.StateCode, o.LocalKey);
switch (codes.Length)
{
case 0:
return(Empty);
case 1:
return(codes[0]);
default:
return("999");
}
});
// now apply the counties to the table
foreach (var r in
table.Rows.OfType <DataRow>().Where(r => !IsNullOrWhiteSpace(r.LocalKey())))
{
r["CountyCode"] =
countyDict[new { StateCode = r.StateCode(), LocalKey = r.LocalKey() }];
}
return(table);
}