private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
AppendStatusText("Started");
if (!int.TryParse(MaxAttemptsTextBox.Text.Trim(), out var maxAttempts))
{
AppendStatusText("Invalid Max Attempts");
return;
}
var table = GetData(maxAttempts);
var coded = 0;
var notCoded = 0;
foreach (var row in table)
{
var coding = GoogleMapsLookup.Lookup(row.Address, row.City, row.StateCode,
row.Zip5);
if (coding.Status == "OK")
{
row.Address = $"{coding.StreetNumber.SafeString()} {coding.Street.SafeString()}".Trim();
row.City = coding.City.SafeString();
row.StateCode = coding.State.SafeString();
row.Zip5 = coding.Zip5.SafeString();
row.Zip4 = coding.Zip4.SafeString();
row.Latitude = coding.Latitude;
row.Longitude = coding.Longitude;
coded++;
}
else
{
AppendStatusText($"Could not code {row.Address}, {row.City}, {row.StateCode}, {row.Zip4}:" +
$" {coding.Status}");
switch (coding.Status)
{
case "ambiguous":
case "ZERO_RESULTS":
row.Latitude = 0.0;
row.Longitude = 0.0;
break;
}
notCoded++;
}
}
Addresses.UpdateTable(table);
AppendStatusText($"Task complete: coded={coded}, not coded={notCoded}");
AppendStatusText(Empty);
}
catch (VoteException ex)
{
AppendStatusText(ex.Message);
}
catch (Exception ex)
{
AppendStatusText(ex.ToString());
}
}
private void Process()
{
var rowNumber = 1;
var donationsAdded = 0;
var addressesAdded = 0;
var addressesModified = 0;
var now = DateTime.UtcNow;
void PostError(string msg)
{
AppendStatusText($"Row {rowNumber}: {msg}");
}
var fileToRead = new FileInfo(SpreadsheetTextBox.Text);
using (var stream = fileToRead.OpenRead())
{
var reader = ExcelReaderFactory.CreateReader(stream);
using (reader)
{
var config = new ExcelDataSetConfiguration
{
ConfigureDataTable = tableReader => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var result = reader.AsDataSet(config);
if (result.Tables.Count != 1)
{
throw new Exception($"Cannot handle an Excel File with {result.Tables.Count} sheets");
}
var dataTable = result.Tables[0];
foreach (var row in dataTable.Rows.OfType <DataRow>())
{
rowNumber++;
var date = row.ExcelDate();
var fullName = row.ExcelFullName().SafeString().Trim();
var address = row.ExcelAddress().SafeString().Trim();
var cityStateZip = row.ExcelCityStateZip().SafeString().Trim();
var email = row.ExcelEmail().Trim().SafeString();
var donation = row.ExcelDonation();
if (date == null || date > DateTime.Now || date < DateTime.Now.Subtract(new TimeSpan(730, 0, 0, 0) /* 2 years */))
{
PostError("Missing or invalid Date ({date}");
continue;
}
if (IsNullOrWhiteSpace(fullName))
{
PostError("Missing Full Name");
continue;
}
if (IsNullOrWhiteSpace(address))
{
PostError("Missing Address");
continue;
}
if (IsNullOrWhiteSpace(cityStateZip))
{
PostError("Missing City State Zip");
continue;
}
if (!Validation.IsValidEmailAddress(email))
{
PostError("Missing or invalid Email ({email}");
continue;
}
if (donation == null || donation <= 0)
{
PostError("Missing or invalid Donation");
continue;
}
// decompose name
var pos = fullName.LastIndexOf(' ');
if (pos < 1)
{
PostError($"Cannot decompose name: {fullName}");
continue;
}
var firstName = fullName.Substring(0, pos).Trim();
var lastName = fullName.Substring(pos + 1).Trim();
if (IsNullOrWhiteSpace(firstName) || IsNullOrWhiteSpace(lastName))
{
PostError($"Cannot decompose name: {fullName}");
continue;
}
// decompose cityStateZip
var match = Regex.Match(cityStateZip, @"^\s*(?<city>[^,]+),\s*(?<state>[^\d]+)(?<zip5>\d{5})?(?:-(?<zip4>\d{4}))?\s*$", RegexOptions.IgnoreCase);
if (!match.Success)
{
PostError($"Cannot decompose cityStateZip: {cityStateZip}");
continue;
}
var city = match.Groups["city"].Value.Trim();
var state = match.Groups["state"].Value.Trim();
var zip5 = match.Groups["zip5"].Value.Trim();
var zip4 = match.Groups["zip4"].Value.Trim();
var stateCode = StateCache.GetStateCode(state);
if (IsNullOrWhiteSpace(stateCode))
{
PostError($"Invalid State: {state}");
continue;
}
//if (Donations.EmailDateExists(email, date.Value))
//{
// PostError("A donation for this email, date and time already exists" +
// $" ({email}, {date.Value:G}, {donation:C})");
// continue;
//}
if (UpdateDatabaseCheckBox.Checked)
{
Donations.Insert(email, date.Value, firstName, lastName, fullName,
address, city, stateCode, zip5, zip4, Empty, Convert.ToDecimal(donation.Value), false, null);
}
donationsAdded++;
// Get the encoding
var googleResult = GoogleMapsLookup.Lookup(address, city, stateCode, zip5);
TigerLookupData tigerResult = null;
if (googleResult.Status == "OK")
{
tigerResult =
TigerLookup.Lookup(googleResult.Latitude, googleResult.Longitude);
}
var table = Addresses.GetDataByEmail(email);
if (table.Count == 0) // Insert
{
if (UpdateDatabaseCheckBox.Checked)
{
Addresses.Insert(firstName, lastName, address, city, stateCode, zip5, zip4,
email, Empty, now, "DONR", false, false, false, VotePage.DefaultDbDate,
Empty, tigerResult?.Congress.SafeString(),
tigerResult?.Upper.SafeString(), tigerResult?.Lower.SafeString(),
tigerResult?.County.SafeString(), tigerResult?.District.SafeString(),
tigerResult?.Place.SafeString(), tigerResult?.Elementary.SafeString(),
tigerResult?.Secondary.SafeString(), tigerResult?.Unified.SafeString(),
tigerResult?.CityCouncil.SafeString(),
tigerResult?.CountySupervisors.SafeString(),
tigerResult?.SchoolDistrictDistrict.SafeString(), googleResult.Latitude,
googleResult.Longitude, googleResult.Status == "OK" ? now : VotePage.DefaultDbDate,
0, VotePage.DefaultDbDate, true);
}
addressesAdded++;
}
else if (googleResult.Status == "OK") // update all matching Addresses
{
foreach (var r in table)
{
if (!IsNullOrWhiteSpace(firstName))
{
r.FirstName = firstName;
}
if (!IsNullOrWhiteSpace(lastName))
{
r.LastName = lastName;
}
r.IsDonor = true;
if (tigerResult != null)
{
r.Address = address;
r.City = city;
r.StateCode = stateCode;
r.Zip5 = zip5;
r.Zip4 = zip4;
r.CongressionalDistrict = tigerResult.Congress;
r.StateSenateDistrict = tigerResult.Upper;
r.StateHouseDistrict = tigerResult.Lower;
r.County = tigerResult.County;
r.District = tigerResult.District;
r.Place = tigerResult.Place;
r.Latitude = googleResult.Latitude;
r.Longitude = googleResult.Longitude;
r.DistrictLookupDate = now;
}
addressesModified++;
}
if (UpdateDatabaseCheckBox.Checked)
{
Addresses.UpdateTable(table);
}
}
}
AppendStatusText($"{rowNumber - 1} rows processed, {donationsAdded} donations added, {addressesAdded} addresses added, {addressesModified} addresses modified");
}
}
}
private void PostCsv()
{
var now = DateTime.UtcNow;
var messages = new List <string>();
var good = 0;
var bad = 0;
try
{
if (Request.Files.Count != 1 || Request.Files[0].ContentLength == 0)
{
throw new VoteException("Could not find CSV file");
}
using (var parser =
new TextFieldParser(Request.Files[0].InputStream, Encoding.UTF8))
{
parser.TextFieldType = FieldType.Delimited;
parser.HasFieldsEnclosedInQuotes = true;
parser.SetDelimiters(",");
// look for the field names -- they aren't the first row so find a row whose
// first column is "FirstName"
string[] columnNames = null;
while (!parser.EndOfData)
{
var row = parser.ReadFields();
// ReSharper disable once PossibleNullReferenceException
//if (row.Length > 0 && row[0] == "FirstName")
if (row.Length > 0 && new[] { "FirstName", "First Name" }.Contains(row[0]))
{
columnNames = row;
break;
}
}
if (columnNames == null)
{
throw new VoteException(
"Could not find column names row (\"FirstName\" in first column");
}
var maxColumnInx = -1;
var firstNameInx = GetColumnIndex(columnNames, new[] { "FirstName", "First Name" },
ref maxColumnInx);
var lastNameInx = GetColumnIndex(columnNames, new[] { "LastName", "Last Name" },
ref maxColumnInx);
var address1Inx = GetColumnIndex(columnNames, "Address1", ref maxColumnInx);
var address2Inx = GetColumnIndex(columnNames, "Address2", ref maxColumnInx);
var cityInx = GetColumnIndex(columnNames, "City", ref maxColumnInx);
var stateProvinceInx = GetColumnIndex(columnNames,
new[] { "StateProvince", "State/Province" }, ref maxColumnInx);
var zipCodeInx = GetColumnIndex(columnNames, new[] { "ZipCode", "Zip Code" },
ref maxColumnInx);
var emailInx = GetColumnIndex(columnNames, "Email", ref maxColumnInx);
var phoneInx = GetColumnIndex(columnNames, "Phone", ref maxColumnInx);
var transactionDateInx = GetColumnIndex(columnNames,
new[] { "TransactionDate", "Transaction Date" }, ref maxColumnInx);
var totalChangedInx = GetColumnIndex(columnNames, new[] { "TotalCharged", "Total" },
ref maxColumnInx);
while (!parser.EndOfData)
{
var row = parser.ReadFields();
try
{
// ReSharper disable once PossibleNullReferenceException
if (row.Length <= maxColumnInx)
{
throw new VoteException(
$"Contains only {row.Length} columns, {maxColumnInx + 1} required.");
}
var email = row[emailInx];
if (IsNullOrWhiteSpace(email))
{
throw new VoteException("Email is missing");
}
if (!Validation.IsValidEmailAddress(email))
{
throw new VoteException($"Invalid Email: {email}");
}
var firstName = row[firstNameInx];
var lastName = row[lastNameInx];
var fullName = firstName + " " + lastName;
if (IsNullOrWhiteSpace(fullName))
{
throw new VoteException("FirstName or LastName is missing");
}
var address = row[address1Inx];
var address2 = row[address2Inx];
if (!IsNullOrWhiteSpace(address) &&
!IsNullOrWhiteSpace(address2))
{
address += ", " + address2;
}
var city = row[cityInx];
if (IsNullOrWhiteSpace(city))
{
throw new VoteException("City is missing");
}
var stateCode = row[stateProvinceInx];
if (IsNullOrWhiteSpace(stateCode))
{
throw new VoteException("StateProvince is missing");
}
if (!StateCache.IsValidStateCode(stateCode))
{
// might be state name
stateCode = StateCache.GetStateCode(stateCode);
if (!StateCache.IsValidStateCode(stateCode))
{
throw new VoteException("Invaid state or state code");
}
}
var zipCode = row[zipCodeInx];
if (IsNullOrWhiteSpace(zipCode))
{
NonFatalError(messages, parser.LineNumber, $"ZipCode is missing ({email})");
}
var zipMatch = Regex.Match(zipCode, @"(?<zip5>\d{5})(?:\D?(?<zip4>\d{4}))?");
var zip5 = Empty;
var zip4 = Empty;
if (!zipMatch.Success)
{
NonFatalError(messages, parser.LineNumber,
$"Could not parse ZipCode: {zipCode} ({email})");
}
else
{
zip5 = zipMatch.Groups["zip5"].Value;
zip4 = zipMatch.Groups["zip4"].Value;
}
var phone = row[phoneInx].NormalizePhoneNumber();
if (!DateTime.TryParse(row[transactionDateInx], out var transactionDate))
{
throw new VoteException(
$"Could not parse TransactionDate: {row[transactionDateInx]}");
}
if (!decimal.TryParse(row[totalChangedInx], out var totalCharged))
{
throw new VoteException(
$"Could not parse TotalCharged: {row[totalChangedInx]}");
}
if (totalCharged == 0)
{
continue;
}
//if (Donations.EmailDateExists(email, transactionDate))
// throw new VoteException(
// "A donation for this email, date and time already exists" +
// $" ({email}, {transactionDate:G}, {totalCharged:C})");
Donations.Insert(email, transactionDate, firstName, lastName, fullName,
address, city, stateCode, zip5, zip4, phone, totalCharged, false, null);
// Get the encoding
var googleResult = GoogleMapsLookup.Lookup(address, city, stateCode, zip5);
TigerLookupData tigerResult = null;
if (googleResult.Status == "OK")
{
tigerResult =
TigerLookup.Lookup(googleResult.Latitude, googleResult.Longitude);
}
var table = Addresses.GetDataByEmail(email);
if (table.Count == 0) // Insert
{
Addresses.Insert(firstName, lastName, address, city, stateCode, zip5, zip4,
email, phone, now, "DONR", false, false, false, DefaultDbDate,
Empty, tigerResult?.Congress.SafeString(),
tigerResult?.Upper.SafeString(), tigerResult?.Lower.SafeString(),
tigerResult?.County.SafeString(), tigerResult?.District.SafeString(),
tigerResult?.Place.SafeString(), tigerResult?.Elementary.SafeString(),
tigerResult?.Secondary.SafeString(), tigerResult?.Unified.SafeString(),
tigerResult?.CityCouncil.SafeString(),
tigerResult?.CountySupervisors.SafeString(),
tigerResult?.SchoolDistrictDistrict.SafeString(), googleResult.Latitude,
googleResult.Longitude, googleResult.Status == "OK" ? now : DefaultDbDate,
0, DefaultDbDate, true);
}
else if (googleResult.Status == "OK") // update all matching Addresses
{
foreach (var r in table)
{
if (!IsNullOrWhiteSpace(firstName))
{
r.FirstName = firstName;
}
if (!IsNullOrWhiteSpace(lastName))
{
r.LastName = lastName;
}
if (!IsNullOrWhiteSpace(phone))
{
r.Phone = phone;
}
r.IsDonor = true;
if (tigerResult != null)
{
r.Address = address;
r.City = city;
r.StateCode = stateCode;
r.Zip5 = zip5;
r.Zip4 = zip4;
r.CongressionalDistrict = tigerResult.Congress;
r.StateSenateDistrict = tigerResult.Upper;
r.StateHouseDistrict = tigerResult.Lower;
r.County = tigerResult.County;
r.District = tigerResult.District;
r.Place = tigerResult.Place;
r.Latitude = googleResult.Latitude;
r.Longitude = googleResult.Longitude;
r.DistrictLookupDate = now;
}
}
Addresses.UpdateTable(table);
}
messages.Add(
$"<p>{totalCharged:C} donation added for {fullName}<{email}> on {transactionDate:G}</p>");
good++;
}
catch (Exception ex)
{
messages.Add(
$"<p class=\"error\">Error on row {parser.LineNumber}: {ex.Message}</p>");
bad++;
}
}
}
}
catch (Exception ex)
{
messages.Add($"<p class=\"error\">Error: {ex.Message}</p>");
bad++;
}
messages.Add($"<p>{good} donations added. {bad} errors.</p>");
SummaryPlaceHolder.Controls.Add(
new LiteralControl(Join(Empty, messages)));
SummaryContainer.RemoveCssClass("hidden");
}
//private static void NonFatalError(ICollection<string> messages, long lineNumber,
// string message)
//{
// messages.Add(
// $"<p class=\"error\">Non-fatal error on row {lineNumber}: {message}</p>");
//}
private void PostCsv()
{
var now = DateTime.UtcNow;
var messages = new List <string>();
var good = 0;
var bad = 0;
try
{
if (Request.Files.Count != 1 || Request.Files[0].ContentLength == 0)
{
throw new VoteException("Could not find CSV file");
}
using (var parser =
new TextFieldParser(Request.Files[0].InputStream, Encoding.UTF8))
{
parser.TextFieldType = FieldType.Delimited;
parser.HasFieldsEnclosedInQuotes = true;
parser.SetDelimiters(",");
// look for the field names -- they might not be in the first row so find a row
// whose first column is "Date"
string[] columnNames = null;
while (!parser.EndOfData)
{
var row = parser.ReadFields();
// ReSharper disable once PossibleNullReferenceException
if (row.Length > 0 && row[0] == "Date")
{
columnNames = row;
break;
}
}
if (columnNames == null)
{
throw new VoteException(
"Could not find column names row (\"FirstName\" in first column");
}
var maxColumnInx = -1;
var nameInx = GetColumnIndex(columnNames, "Name", ref maxColumnInx);
var address1Inx = GetColumnIndex(columnNames, "Address Line 1", ref maxColumnInx);
var address2Inx = GetColumnIndex(columnNames, "Address Line 2/District/Neighborhood", ref maxColumnInx);
var cityInx = GetColumnIndex(columnNames, "Town/City", ref maxColumnInx);
var stateProvinceInx = GetColumnIndex(columnNames,
"State/Province/Region/County/Territory/Prefecture/Republic", ref maxColumnInx);
var zipCodeInx = GetColumnIndex(columnNames, "Zip/Postal Code",
ref maxColumnInx);
var emailInx = GetColumnIndex(columnNames, "From Email Address", ref maxColumnInx);
var transactionDateInx = GetColumnIndex(columnNames, "Date", ref maxColumnInx);
var totalChangedInx = GetColumnIndex(columnNames, "Gross", ref maxColumnInx);
var transactionIdInx = GetColumnIndex(columnNames, "Transaction ID", ref maxColumnInx);
var typeInx = GetColumnIndex(columnNames, "Type", ref maxColumnInx);
while (!parser.EndOfData)
{
var row = parser.ReadFields();
if (row[typeInx] != "Donation Payment")
{
continue;
}
try
{
// ReSharper disable once PossibleNullReferenceException
if (row.Length <= maxColumnInx)
{
throw new VoteException(
$"Contains only {row.Length} columns, {maxColumnInx + 1} required.");
}
var email = row[emailInx];
if (IsNullOrWhiteSpace(email))
{
throw new VoteException("Email is missing");
}
if (!Validation.IsValidEmailAddress(email))
{
throw new VoteException($"Invalid Email: {email}");
}
// we try to split the single name into first and last
var name = row[nameInx];
var nameParts = name.Split(new [] { ' ' },
StringSplitOptions.RemoveEmptyEntries);
var firstName = Empty;
var lastName = Empty;
switch (nameParts.Length)
{
case 0:
break;
case 1:
lastName = nameParts[0];
break;
case 2:
firstName = nameParts[0];
lastName = nameParts[1];
break;
default:
if (Validation.IsValidNameSuffix(nameParts.Last()))
{
firstName = Join(" ", nameParts.Take(nameParts.Length - 2));
lastName = Join(" ", nameParts.Skip(nameParts.Length - 2));
}
else
{
firstName = Join(" ", nameParts.Take(nameParts.Length - 1));
lastName = nameParts.Last();
}
break;
}
// var firstName = row[firstNameInx];
// var lastName = row[lastNameInx];
// var fullName = firstName + " " + lastName;
// if (IsNullOrWhiteSpace(fullName))
// throw new VoteException("FirstName or LastName is missing");
var address = row[address1Inx];
var address2 = row[address2Inx];
if (!IsNullOrWhiteSpace(address) &&
!IsNullOrWhiteSpace(address2))
{
address += ", " + address2;
}
var city = row[cityInx];
var stateCode = row[stateProvinceInx];
if (!StateCache.IsValidStateCode(stateCode))
{
// might be state name
stateCode = StateCache.GetStateCode(stateCode).SafeString();
}
var zipCode = row[zipCodeInx];
var zipMatch = Regex.Match(zipCode, @"(?<zip5>\d{5})(?:\D?(?<zip4>\d{4}))?");
var zip5 = Empty;
var zip4 = Empty;
if (zipMatch.Success)
{
zip5 = zipMatch.Groups["zip5"].Value;
zip4 = zipMatch.Groups["zip4"].Value;
}
var transactionId = row[transactionIdInx].Trim();
if (IsNullOrWhiteSpace(transactionId))
{
throw new VoteException(
$"Transaction ID is missing: {row[transactionDateInx]}");
}
// var phone = row[phoneInx].NormalizePhoneNumber();
if (!DateTime.TryParse(row[transactionDateInx], out var transactionDate))
{
throw new VoteException(
$"Could not parse TransactionDate: {row[transactionDateInx]}");
}
if (!decimal.TryParse(row[totalChangedInx], out var totalCharged))
{
throw new VoteException(
$"Could not parse TotalCharged: {row[totalChangedInx]}");
}
if (totalCharged == 0)
{
continue;
}
if (Donations.PayPalTransactionIdExists(transactionId))
{
throw new VoteException(
"A donation for this PayPal Transaction Id already exists" +
$" ({transactionId}, {email}, {transactionDate:G}, {totalCharged:C})");
}
Donations.Insert(email, transactionDate, firstName, lastName, name,
address, city, stateCode, zip5, zip4, Empty, totalCharged, false, transactionId);
// Get the encoding
var googleResult = IsNullOrWhiteSpace(stateCode)
? null
: GoogleMapsLookup.Lookup(address, city, stateCode, zip5);
TigerLookupData tigerResult = null;
if (googleResult?.Status == "OK")
{
tigerResult =
TigerLookup.Lookup(googleResult.Latitude, googleResult.Longitude);
}
var table = Addresses.GetDataByEmail(email);
if (table.Count == 0) // Insert
{
Addresses.Insert(firstName, lastName, address, city, stateCode, zip5, zip4,
email, Empty, now, "DONR", false, false, false, DefaultDbDate,
Empty, tigerResult?.Congress ?? Empty,
tigerResult?.Upper ?? Empty, tigerResult?.Lower ?? Empty,
tigerResult?.County ?? Empty, tigerResult?.District ?? Empty,
tigerResult?.Place ?? Empty, tigerResult?.Elementary ?? Empty,
tigerResult?.Secondary ?? Empty, tigerResult?.Unified ?? Empty,
tigerResult?.CityCouncil ?? Empty,
tigerResult?.CountySupervisors ?? Empty,
tigerResult?.SchoolDistrictDistrict ?? Empty,
googleResult?.Latitude ?? 0, googleResult?.Longitude ?? 0,
googleResult?.Status == "OK" ? now : DefaultDbDate,
0, DefaultDbDate, true);
}
else if (googleResult?.Status == "OK") // update all matching Addresses
{
foreach (var r in table)
{
if (!IsNullOrWhiteSpace(firstName))
{
r.FirstName = firstName;
}
if (!IsNullOrWhiteSpace(lastName))
{
r.LastName = lastName;
}
//if (!IsNullOrWhiteSpace(phone)) r.Phone = phone;
r.IsDonor = true;
if (tigerResult != null)
{
r.Address = address;
r.City = city;
r.StateCode = stateCode;
r.Zip5 = zip5;
r.Zip4 = zip4;
r.CongressionalDistrict = tigerResult.Congress;
r.StateSenateDistrict = tigerResult.Upper;
r.StateHouseDistrict = tigerResult.Lower;
r.County = tigerResult.County;
r.District = tigerResult.District;
r.Place = tigerResult.Place;
r.Latitude = googleResult.Latitude;
r.Longitude = googleResult.Longitude;
r.DistrictLookupDate = now;
}
}
Addresses.UpdateTable(table);
}
messages.Add(
$"<p>{totalCharged:C} donation added for {name}<{email}> on {transactionDate.Date.ToShortDateString()}</p>");
good++;
}
catch (Exception ex)
{
messages.Add(
$"<p class=\"error\">Error on row {parser.LineNumber}: {ex.Message}</p>");
bad++;
}
}
}
}
catch (Exception ex)
{
messages.Add($"<p class=\"error\">Error: {ex.Message}</p>");
bad++;
}
messages.Add($"<p>{good} donations added. {bad} errors.</p>");
SummaryPlaceHolder.Controls.Add(
new LiteralControl(Join(Empty, messages)));
SummaryContainer.RemoveCssClass("hidden");
}
