private static string AddUrlToCsv(string csv)
{
using (var csvReader = new CsvReader(new StringReader(csv), true))
{
var headers = csvReader.GetFieldHeaders();
using (var ms = new MemoryStream())
{
var streamWriter = new StreamWriter(ms);
var csvWriter = new SimpleCsvWriter();
// write headers
foreach (var col in headers)
{
csvWriter.AddField(col);
}
csvWriter.Write(streamWriter);
while (csvReader.ReadNextRecord())
{
foreach (var col in headers)
{
if (col == "VoteUSA Url")
{
var url = Empty;
var id = csvReader["VoteUSA Id"];
if (!IsNullOrWhiteSpace(id))
{
url = GetLiveIntroPageUrl(id);
}
csvWriter.AddField(url);
}
else
{
csvWriter.AddField(csvReader[col]);
}
}
csvWriter.Write(streamWriter);
}
streamWriter.Flush();
ms.Position = 0;
csv = new StreamReader(ms).ReadToEnd();
}
}
return(csv);
}
private static void WriteColumnHeadings(SimpleCsvWriter csvWriter,
TextWriter stringWriter, IEnumerable <OutputColumnInfo> outputColumns)
{
// In case the CSV required column headings
foreach (var column in outputColumns)
{
csvWriter.AddField(column.HeadingName);
}
csvWriter.Write(stringWriter);
}
public void DoOneZipCode(string zipCode)
{
// Get all zip rows from ZipStreetsDownloaded
var isSingle = false;
var found = false;
string singleLdsStateCode = null;
var streetsTable = ZipStreetsDownloaded.GetLookupDataByZipCode(zipCode, 0);
if (streetsTable.Count > 0)
{
found = true;
// Fetch and summarize the USZD data
var ldsDictionary = new Dictionary <LdsInfo, object>();
// ReSharper disable InconsistentNaming
var zip4s = CreateZipPlus4List(streetsTable);
// ReSharper restore InconsistentNaming
var uszdTable = Uszd.GetDataByZip4List(zipCode, zip4s, 0);
foreach (var row in uszdTable)
{
var stateCode = StateCache.StateCodeFromLdsStateCode(row.LdsStateCode);
singleLdsStateCode = row.LdsStateCode;
var ldsInfo = new LdsInfo(stateCode, row.Congress, row.StateSenate,
row.StateHouse, row.County);
ldsDictionary[ldsInfo] = null;
}
if (ldsDictionary.Count == 1)
{
isSingle = true;
var singleLdsInfo = ldsDictionary.Keys.Single();
//List<string> fields = new List<string>();
var csvWriter = new SimpleCsvWriter();
//AddField(fields, zipCode);
//AddField(fields, singleLdsInfo.Congress.Substring(1));
//AddField(fields, singleLdsInfo.StateSenate);
//AddField(fields, singleLdsInfo.StateHouse);
//AddField(fields, singleLdsStateCode);
//AddField(fields, singleLdsInfo.County);
//AddField(fields, singleLdsInfo.StateCode);
//TextWriter.WriteLine(string.Join(",", fields));
csvWriter.AddField(zipCode);
csvWriter.AddField(singleLdsInfo.Congress.Substring(1));
csvWriter.AddField(singleLdsInfo.StateSenate);
csvWriter.AddField(singleLdsInfo.StateHouse);
csvWriter.AddField(singleLdsStateCode);
csvWriter.AddField(singleLdsInfo.County);
csvWriter.AddField(singleLdsInfo.StateCode);
csvWriter.Write(_TextWriter);
}
}
if (found)
{
Report("{0}: {1}", zipCode, isSingle ? "Single" : "Multiple");
}
}
private static void WriteRow(SimpleCsvWriter csvWriter,
TextWriter stringWriter, IEnumerable <OutputColumnInfo> outputColumns,
IDataRecord dataReader)
{
// Write a single row of data to the memory-resident CSV
foreach (var column in outputColumns)
{
if (column.SqlName == null) // a special (derived) column
{
switch (column.IdName)
{
case "Name":
var firstName = dataReader["FirstName"] as string;
var lastName = dataReader["LastName"] as string;
if (string.IsNullOrWhiteSpace(firstName))
{
csvWriter.AddField(string.IsNullOrWhiteSpace(lastName)
? string.Empty
: lastName);
}
else if (string.IsNullOrWhiteSpace(lastName))
{
csvWriter.AddField(lastName);
}
else
{
csvWriter.AddField(firstName + ' ' + lastName);
}
break;
case "ZipPlus4":
var zip5 = dataReader["Zip5"] as string;
var zip4 = dataReader["Zip4"] as string;
if (string.IsNullOrWhiteSpace(zip5))
{
csvWriter.AddField(string.Empty);
}
else if (string.IsNullOrWhiteSpace(zip4))
{
csvWriter.AddField(zip5);
}
else
{
csvWriter.AddField(zip5 + '-' + zip4);
}
break;
}
}
else
{
csvWriter.AddField(dataReader[column.SqlName] as string);
}
}
csvWriter.Write(stringWriter);
}
private static void Main()
{
const string dir = @"D:\Users\CurtNew\Dropbox\Documents\vote\tiger";
var shapeFile = new ShapeFile(Path.Combine(dir, @"2016\tl_2016_us_place\tl_2016_us_place.shp"));
var enumerator = shapeFile.GetShapeFileEnumerator();
var placeInfos = new List <PlaceInfo>();
while (enumerator.MoveNext())
{
var fieldValues = shapeFile.GetAttributeFieldValues(enumerator.CurrentShapeIndex);
var stateFips = fieldValues[0].Trim();
var stateCode = StateCache.StateCodeFromLdsStateCode(stateFips);
placeInfos.Add(new PlaceInfo
{
StateFips = stateFips,
PlaceFips = fieldValues[1].Trim(),
PlaceName = fieldValues[4].Trim(),
PlaceLongName = fieldValues[5].Trim(),
StateCode = stateCode,
StateName = StateCache.GetStateName(stateCode)
});
}
var textWriter = new StreamWriter(Path.Combine(dir, @"places.csv"));
var csvWriter = new SimpleCsvWriter();
csvWriter.AddField("State FIPS");
csvWriter.AddField("Place FIPS");
csvWriter.AddField("State Code");
csvWriter.AddField("State Name");
csvWriter.AddField("Place Name");
csvWriter.AddField("Place Long Name");
csvWriter.Write(textWriter);
foreach (
var district in
placeInfos.OrderBy(d => d.StateName)
.ThenBy(d => d.PlaceLongName))
{
csvWriter.AddField(district.StateFips);
csvWriter.AddField(district.PlaceFips);
csvWriter.AddField(district.StateCode);
csvWriter.AddField(district.StateName);
csvWriter.AddField(district.PlaceName);
csvWriter.AddField(district.PlaceLongName);
csvWriter.Write(textWriter);
}
textWriter.Close();
}
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)
{
var result = new AjaxResponse();
try
{
// assume success unless an exception is thrown
result.Success = true;
// copy back the groupname and uploadid
result.GroupName = Request.Form["groupname"];
result.UploadId = Request.Form["uploadid"];
// there should always be exactly one file
if (Request.Files.Count == 0)
{
throw new VoteException("The upload file is missing");
}
if (Request.Files.Count > 1)
{
throw new VoteException("Unexpected files in the upload package");
}
// Test error handling
//throw new VoteException("Some weird-ass error");
// get the file
var postedFile = Request.Files[0];
var filename = postedFile.FileName;
var saveAs = Request.Form["saveas"];
if (!string.IsNullOrWhiteSpace(saveAs))
{
filename = saveAs;
}
if (!filename.EndsWith(".csv", StringComparison.OrdinalIgnoreCase))
{
filename += ".csv";
}
var duplicate = BallotPediaCsvs.FilenameExists(filename);
if (duplicate && (Request.Form["overwrite"] != "true"))
{
throw new VoteException(
"A CSV with this filename has already been uploaded. Either rename the file or check the 'overwrite existing file' box.");
}
// save the timestamp so the time we post to all the various tables matches
// exactly...
var uploadTime = DateTime.UtcNow;
string csv;
var coded = 0;
var candidateCount = 0;
using (
var csvReader = new CsvReader(
new StreamReader(postedFile.InputStream, Encoding.UTF8), true))
{
var headers = csvReader.GetFieldHeaders();
var normalizedHeaders = NormalizeHeaders(headers);
var columnsToAdd = new List <string>();
if (!headers.Contains("VoteUSA Id"))
{
columnsToAdd.Add("VoteUSA Id");
}
if (!headers.Contains("VoteUSA Url"))
{
columnsToAdd.Add("VoteUSA Url");
}
using (var ms = new MemoryStream())
{
var streamWriter = new StreamWriter(ms);
var csvWriter = new SimpleCsvWriter();
// write headers
foreach (var col in normalizedHeaders)
{
csvWriter.AddField(col);
}
foreach (var col in columnsToAdd)
{
csvWriter.AddField(col);
}
csvWriter.Write(streamWriter);
while (csvReader.ReadNextRecord())
{
foreach (var col in headers)
{
var value = csvReader[col];
csvWriter.AddField(value);
if ((col == "VoteUSA Id") && !string.IsNullOrWhiteSpace(value))
{
coded++;
}
}
// ReSharper disable once UnusedVariable
foreach (var col in columnsToAdd)
{
csvWriter.AddField(string.Empty);
}
csvWriter.Write(streamWriter);
candidateCount++;
}
streamWriter.Flush();
ms.Position = 0;
csv = new StreamReader(ms).ReadToEnd();
}
}
if (duplicate)
{
BallotPediaCsvs.DeleteByFilename(filename);
}
BallotPediaCsvs.Insert(filename, uploadTime, csv, candidateCount, coded, false);
result.Message = "The CSV uploaded successfully.";
}
catch (Exception ex)
{
result.Success = false;
result.Message = ex.Message;
}
WriteJsonResultToResponse(result);
}
private void StartButton_Click(object sender, EventArgs e)
{
var candidates = new List <List <KeyValuePair <string, string> > >();
// read CSV into memory structure
var exactMatches = 0;
var verifiedMatches = 0;
var rejectedMatches = 0;
var noMatches = 0;
var multipleMatches = 0;
using (var csvReader = new CsvReader(File.OpenText(InputTextBox.Text), true))
{
var headers = csvReader.GetFieldHeaders();
if (!headers.Contains("FirstName"))
{
throw new VoteException("FirstName column missing");
}
if (!headers.Contains("LastName"))
{
throw new VoteException("LastName column missing");
}
if (!headers.Contains("State"))
{
throw new VoteException("State column missing");
}
while (csvReader.ReadNextRecord())
{
var data = new List <KeyValuePair <string, string> >();
candidates.Add(data);
var firstName = string.Empty;
var lastName = string.Empty;
var state = string.Empty;
var id = string.Empty;
var introPage = string.Empty;
foreach (var header in headers)
{
var value = csvReader[header];
switch (header)
{
case "FirstName":
firstName = value;
break;
case "LastName":
lastName = value;
break;
case "State":
state = value;
break;
}
data.Add(new KeyValuePair <string, string>(header, value));
}
var stateCode = StateCache.GetStateCode(state);
if (!string.IsNullOrEmpty(stateCode))
{
var table = Politicians.GetNamesDataByStateCodeLastName(stateCode,
lastName);
var matches = table
.Where(row => row.FirstName.IsEqIgnoreCase(firstName))
.ToList();
if (matches.Count == 1)
{
exactMatches++;
id = matches[0].PoliticianKey;
introPage = GetIntroPageUrl(id);
}
else
{
switch (table.Count)
{
case 1:
{
var message = string.Format("BallotPedia: {0} {1}\n" +
"Vote-USA: {2}\n\nUse?",
firstName, lastName, Politicians.GetFormattedName(table[0].PoliticianKey));
if (
MessageBox.Show(message, "No Exact Match", MessageBoxButtons.YesNo) ==
DialogResult.Yes)
{
verifiedMatches++;
id = table[0].PoliticianKey;
introPage = GetIntroPageUrl(id);
}
else
{
rejectedMatches++;
}
}
break;
case 0:
noMatches++;
break;
default:
multipleMatches++;
break;
}
}
}
data.Add(new KeyValuePair <string, string>("VoteUSA Id", id));
data.Add(new KeyValuePair <string, string>("VoteUSA Url", introPage));
}
var summary =
string.Format(
"Candidates: {0}\nExact matches: {1}\n" +
"Verified matches: {2}\nRejected: {3}\nNo matches: {4}\nMultiple matches: {5}",
candidates.Count, exactMatches, verifiedMatches, rejectedMatches,
noMatches, multipleMatches);
MessageBox.Show(summary, "Summary");
if (candidates.Count > 0)
{
var directory = Path.GetDirectoryName(InputTextBox.Text);
var filename = Path.GetFileNameWithoutExtension(InputTextBox.Text);
var extension = Path.GetExtension(InputTextBox.Text);
Debug.Assert(directory != null, "directory != null");
var outputPath = Path.Combine(directory, filename + ".coded" + extension);
var textWriter = File.CreateText(outputPath);
var csvWriter = new SimpleCsvWriter();
// write headings
foreach (var kvp in candidates[0])
{
csvWriter.AddField(kvp.Key);
}
csvWriter.Write(textWriter);
foreach (var row in candidates)
{
foreach (var kvp in row)
{
csvWriter.AddField(kvp.Value);
}
csvWriter.Write(textWriter);
}
textWriter.Close();
}
}
}
//
// Event handlers
//
private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
TextWriter writer = null;
try
{
writer = new StreamWriter(OutputFileTextBox.Text);
int count = 0;
//List<string> fields = new List<string>();
SimpleCsvWriter csvWriter = new SimpleCsvWriter();
// newTable used only to get at column info
LogAddressesGoodNewTable newTable = new LogAddressesGoodNewTable();
using (var reader = LogAddressesGood.GetAllDataReader(0))
{
while (reader.Read())
{
//fields.Clear();
//fields.Add("NULL"); // Id (will auto-increment)
//AddField(fields, reader.DateStamp.ToString("yyyy-MM-dd HH:mm:ss")); // DateStamp
//AddField(fields, ""); // Email
//AddField(fields, ""); // RawAddress
//AddField(fields, ""); // SelectedStateCode
csvWriter.AddNull(); // Id (will auto-increment)
csvWriter.AddField(reader.DateStamp.ToString("yyyy-MM-dd HH:mm:ss")); // DateStamp
csvWriter.AddField(""); // Email
csvWriter.AddField(""); // RawAddress
csvWriter.AddField(""); // SelectedStateCode
// concatenate the address fields
string address = reader.Address1.Trim();
string address2 = reader.Address2.Trim();
if (address.Length == 0)
{
address = address2;
}
else if (address2.Length > 0)
{
address = address + ", " + address2;
}
//AddField(fields, newTable.ParsedAddressColumn.Truncate(address));
//AddField(fields, newTable.ParsedCityColumn.Truncate(reader.City));
//AddField(fields, newTable.ParsedStateCodeColumn.Truncate(reader.StateCode));
//AddField(fields, newTable.ParsedZip5Column.Truncate(reader.Zip5));
//AddField(fields, newTable.ParsedZip4Column.Truncate(reader.Zip4));
csvWriter.AddField(newTable.ParsedAddressColumn.Truncate(address));
csvWriter.AddField(newTable.ParsedCityColumn.Truncate(reader.City));
csvWriter.AddField(newTable.ParsedStateCodeColumn.Truncate(reader.StateCode));
csvWriter.AddField(newTable.ParsedZip5Column.Truncate(reader.Zip5));
csvWriter.AddField(newTable.ParsedZip4Column.Truncate(reader.Zip4));
//writer.WriteLine(string.Join(",", fields));
csvWriter.Write(writer);
count++;
if ((count % 10000) == 0)
{
AppendStatusText("{0} rows written", count);
}
}
AppendStatusText("{0} rows written", count);
AppendStatusText("Finished", count);
}
}
catch (VoteException ex)
{
AppendStatusText(ex.Message);
AppendStatusText("Terminated.");
}
catch (Exception ex)
{
AppendStatusText(ex.ToString());
AppendStatusText("Terminated.");
}
finally
{
if (writer != null)
{
writer.Close();
}
}
}
private static void Main()
{
const string dir = @"D:\Users\CurtNew\Dropbox\Documents\vote\tiger";
var shapeFile = new ShapeFile(Path.Combine(dir, @"2016\tl_2016_us_cousub\tl_2016_us_cousub.shp"));
var enumerator = shapeFile.GetShapeFileEnumerator();
var districtInfos = new List <DistrictInfo>();
while (enumerator.MoveNext())
{
var fieldValues = shapeFile.GetAttributeFieldValues(enumerator.CurrentShapeIndex);
var stateFips = fieldValues[0].Trim();
var stateCode = StateCache.StateCodeFromLdsStateCode(stateFips);
var countyFips = fieldValues[1].Trim();
districtInfos.Add(new DistrictInfo
{
StateFips = stateFips,
CountyFips = countyFips,
DistrictFips = fieldValues[2].Trim(),
DistrictName = fieldValues[5].Trim(),
DistrictLongName = fieldValues[6].Trim(),
StateCode = stateCode,
StateName = StateCache.GetStateName(stateCode),
CountyName = CountyCache.GetCountyName(stateCode, countyFips)
});
}
var textWriter = new StreamWriter(Path.Combine(dir, @"districts.csv"));
var csvWriter = new SimpleCsvWriter();
csvWriter.AddField("State FIPS");
csvWriter.AddField("County FIPS");
csvWriter.AddField("District FIPS");
csvWriter.AddField("State Code");
csvWriter.AddField("State Name");
csvWriter.AddField("County Name");
csvWriter.AddField("District Name");
csvWriter.AddField("District Long Name");
csvWriter.Write(textWriter);
foreach (
var district in
districtInfos.OrderBy(d => d.StateName)
.ThenBy(d => d.CountyName)
.ThenBy(d => d.DistrictLongName))
{
csvWriter.AddField(district.StateFips);
csvWriter.AddField(district.CountyFips);
csvWriter.AddField(district.DistrictFips);
csvWriter.AddField(district.StateCode);
csvWriter.AddField(district.StateName);
csvWriter.AddField(district.CountyName);
csvWriter.AddField(district.DistrictName);
csvWriter.AddField(district.DistrictLongName);
csvWriter.Write(textWriter);
}
textWriter.Close();
}
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 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();
}
}
private int LoadState(TextReader textReader, int skipRows)
{
var rowCount = 0;
var updateErrorCount = 0;
using (var csvReader = new CsvReader(textReader, true))
{
var headers = csvReader.GetFieldHeaders();
// process skips
if (skipRows > 0)
{
AppendStatusText("Skipping {0} rows", skipRows);
while (skipRows > 0 && csvReader.ReadNextRecord())
{
skipRows--;
rowCount++;
}
}
if (ToCsv)
{
var csvWriter = new SimpleCsvWriter();
while (csvReader.ReadNextRecord())
{
var error = false;
rowCount++;
try
{
foreach (var field in headers)
{
var value = csvReader[field];
// The incoming data has a few anomolies...
value = FixupField(field, value);
//if (field == "AddressPrimaryLowNumber" ||
// field == "AddressPrimaryHighNumber")
// value = FixNumericPrimaryAddress(value);
csvWriter.AddField(value);
}
}
catch (Exception ex)
{
error = true;
AppendStatusText("Row {0} skipped due to {1}", rowCount, ex.Message);
}
if (!error)
{
csvWriter.Write(_TextWriter);
}
else
{
csvWriter.Clear();
}
if (rowCount % 50000 == 0) // report every 50000 rows
{
AppendStatusText("{0} rows written", rowCount);
}
}
}
else
{
var table = new DB.VoteZipNew.ZipStreetsDownloadedTable();
while (csvReader.ReadNextRecord())
{
var row = table.NewRow();
var error = false;
rowCount++;
try
{
foreach (var field in headers)
{
var value = csvReader[field];
// The incoming data has a few anomolies...
value = FixupField(field, value);
//if (field == "AddressPrimaryLowNumber" ||
// field == "AddressPrimaryHighNumber")
// value = FixNumericPrimaryAddress(field, value);
row[field] = value;
}
}
catch (Exception ex)
{
error = true;
AppendStatusText("Row {0} skipped due to {1}", rowCount, ex.Message);
}
if (!error)
{
table.AddRow(row);
}
if (rowCount % 1000 == 0) // flush every 1000 rows
{
UpdateZipStreetsDownloaded(table, ref updateErrorCount);
table = new DB.VoteZipNew.ZipStreetsDownloadedTable();
}
if (rowCount % 50000 == 0) // report every 50000 rows
{
AppendStatusText("{0} rows written", rowCount);
}
}
UpdateZipStreetsDownloaded(table, ref updateErrorCount);
}
}
return(rowCount);
}
private int LoadData(TextReader textReader)
{
TextWriter writer = null;
int rowCount = 0;
try
{
if (ToCsv)
{
writer = new StreamWriter(OutputFilePath);
}
using (var csvReader = new CsvReader(textReader, true))
{
int fieldCount = csvReader.FieldCount;
string[] headers = csvReader.GetFieldHeaders();
DB.VoteZipNew.ZipCitiesDownloadedTable table = null;
if (!ToCsv)
{
table = new DB.VoteZipNew.ZipCitiesDownloadedTable();
}
DB.VoteZipNew.ZipCitiesDownloadedRow row;
//List<string> fields = new List<string>();
SimpleCsvWriter csvWriter = new SimpleCsvWriter();
while (csvReader.ReadNextRecord())
{
string cityAliasName = csvReader["CityAliasName"];
string cityAliasAbbreviation = csvReader["CityAliasAbbreviation"];
string metaphoneAliasName = DoubleMetaphone.EncodePhrase(cityAliasName);
if (metaphoneAliasName.Length > DB.VoteZipNew.ZipCitiesDownloaded.MetaphoneAliasNameMaxLength)
{
metaphoneAliasName =
metaphoneAliasName.Substring(0, DB.VoteZipNew.ZipCitiesDownloaded.MetaphoneAliasNameMaxLength);
}
string metaphoneAliasAbbreviation = DoubleMetaphone.EncodePhrase(cityAliasAbbreviation);
if (metaphoneAliasAbbreviation.Length > DB.VoteZipNew.ZipCitiesDownloaded.MetaphoneAliasAbbreviationMaxLength)
{
metaphoneAliasAbbreviation =
metaphoneAliasAbbreviation.Substring(0, DB.VoteZipNew.ZipCitiesDownloaded.MetaphoneAliasAbbreviationMaxLength);
}
rowCount++;
if (ToCsv)
{
//fields.Clear();
foreach (string field in headers)
{
//AddField(fields, csvReader[field]);
csvWriter.AddField(csvReader[field]);
}
//AddField(fields, metaphoneAliasName);
//AddField(fields, metaphoneAliasAbbreviation);
csvWriter.AddField(metaphoneAliasName);
csvWriter.AddField(metaphoneAliasAbbreviation);
//writer.WriteLine(string.Join(",", fields));
csvWriter.Write(writer);
}
else
{
row = table.NewRow();
foreach (string field in headers)
{
row[field] = csvReader[field];
}
row.MetaphoneAliasName = metaphoneAliasName;
row.MetaphoneAliasAbbreviation = metaphoneAliasAbbreviation;
table.AddRow(row);
if (rowCount % 1000 == 0) // flush every 1000 rows
{
DB.VoteZipNew.ZipCitiesDownloaded.UpdateTable(table, 0);
table = new DB.VoteZipNew.ZipCitiesDownloadedTable();
}
}
}
if (!ToCsv)
{
DB.VoteZipNew.ZipCitiesDownloaded.UpdateTable(table);
}
}
}
finally
{
if (writer != null)
{
writer.Close();
}
}
return(rowCount);
}
protected void Page_Load(object sender, EventArgs e)
{
// get the data
var table = Organizations.GetOrganizationsForCsv();
var groups = table.Rows.OfType <DataRow>()
.GroupBy(r => r.OrgId());
// create the csv
string csv;
using (var ms = new MemoryStream())
{
var streamWriter = new StreamWriter(ms);
var csvWriter = new SimpleCsvWriter();
// write headers
csvWriter.AddField("Organization Name");
csvWriter.AddField("Abbreviation");
csvWriter.AddField("Type");
csvWriter.AddField("Sub Type");
csvWriter.AddField("Ideology");
csvWriter.AddField("State");
csvWriter.AddField("URL");
csvWriter.AddField("Address");
csvWriter.AddField("Contacts");
csvWriter.Write(streamWriter);
foreach (var group in groups)
{
var row = group.First();
var mailingAddress = Empty;
if (!IsNullOrWhiteSpace(row.City()) && !IsNullOrWhiteSpace(row.StateCode()))
{
var street = new List <string>();
if (!IsNullOrWhiteSpace(row.Address1()))
{
street.Add(row.Address1());
}
if (!IsNullOrWhiteSpace(row.Address2()))
{
street.Add(row.Address2());
}
mailingAddress = Join(", ", street);
if (!IsNullOrWhiteSpace(mailingAddress))
{
mailingAddress += ", ";
}
mailingAddress += row.City() + ", " + row.StateCode() + " " + row.Zip();
}
var contactList = new List <string>();
foreach (var contact in group)
{
if (!IsNullOrWhiteSpace(contact.Contact()) ||
!IsNullOrWhiteSpace(contact.Email()) ||
!IsNullOrWhiteSpace(contact.Phone()) ||
!IsNullOrWhiteSpace(contact.Title()))
{
string c;
if (!IsNullOrWhiteSpace(contact.Email()))
{
c = !IsNullOrWhiteSpace(contact.Contact())
? $"{contact.Contact()}<{contact.Email()}>"
: contact.Email();
}
else
{
c = contact.Contact();
}
if (!IsNullOrWhiteSpace(contact.Title()))
{
if (!IsNullOrWhiteSpace(c))
{
c += ", ";
}
c += contact.Title();
}
if (!IsNullOrWhiteSpace(contact.Phone()))
{
if (!IsNullOrWhiteSpace(c))
{
c += ", ";
}
c += contact.Phone();
}
contactList.Add(c);
}
}
var contacts = Join("; ", contactList);
csvWriter.AddField(row.Name());
csvWriter.AddField(row.OrgAbbreviation());
csvWriter.AddField(row.OrgType());
csvWriter.AddField(row.OrgSubType());
csvWriter.AddField(row.Ideology());
csvWriter.AddField(row.StateCode());
csvWriter.AddField(row.Url());
csvWriter.AddField(mailingAddress);
csvWriter.AddField(contacts);
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=\"organizations.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 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 electionKey = Request.QueryString["election"];
if (string.IsNullOrWhiteSpace(electionKey))
{
throw new VoteException("Election key is missing.");
}
var electionDescription = Elections.GetElectionDesc(electionKey);
if (string.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 = ElectionsPoliticians.GetVotersEdgeData(electionKey);
// 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");
csvWriter.AddField("Office");
csvWriter.AddField("District");
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("Photo Url");
csvWriter.AddField("VoteUSA Id");
csvWriter.AddField("Postal Street Address");
csvWriter.AddField("Postal City, State Zip");
csvWriter.AddField("Phone");
csvWriter.AddField("Email");
csvWriter.AddField("Date of Birth");
//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");
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("Instagram Url");
csvWriter.Write(streamWriter);
foreach (var row in table.Rows.Cast <DataRow>())
{
string jurisdiction;
var 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 = string.Empty;
break;
}
var stateCode = Elections.GetStateCodeFromKey(electionKey);
var qsc = new QueryStringCollection
{
{ "id", politicianKey }
};
var photoUri = UrlManager.GetStateUri(stateCode, "image.aspx", qsc);
var photoUrl = new UriBuilder(photoUri)
{
Host = UrlManager.GetCanonicalLiveHostName(photoUri.Host),
Port = 80
}.Uri.ToString();
var district = string.Empty;
int distictNumber;
if (int.TryParse(row.DistrictCode(), out distictNumber))
{
district = distictNumber.ToString(CultureInfo.InvariantCulture);
}
// convert to simple name if national
var partyName = Parties.GetNationalPartyDescription(row.PartyCode(),
row.PartyName());
csvWriter.AddField(jurisdiction);
csvWriter.AddField(stateCode);
csvWriter.AddField(Offices.FormatOfficeName(row));
csvWriter.AddField(district);
csvWriter.AddField(row.IsRunningMate() ? row.PoliticianKey() : string.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(photoUrl);
csvWriter.AddField(politicianKey);
csvWriter.AddField(row.PublicAddress());
csvWriter.AddField(row.PublicCityStateZip());
csvWriter.AddField(row.PublicPhone());
csvWriter.AddField(row.PublicEmail());
csvWriter.AddField(row.DateOfBirth().ToString("d"));
//csvWriter.AddField(row.GeneralStatement());
//csvWriter.AddField(row.Personal());
//csvWriter.AddField(row.Education());
//csvWriter.AddField(row.Profession());
//csvWriter.AddField(row.Military());
//csvWriter.AddField(row.Civic());
//csvWriter.AddField(row.Political());
//csvWriter.AddField(row.Religion());
//csvWriter.AddField(row.Accomplishments());
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.WebstagramWebAddress());
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=\"" + electionDescription + ".csv\"");
Response.Write("\xfeff"); // BOM
Response.Write(csv);
Response.End();
}