private void AnalyzeDeletions(string address)
{
var found = false;
found |= AnalyzeCount(States.CountByEmail(address), address, "States.Email");
found |= AnalyzeCount(States.CountByAltEmail(address), address, "States.AltEmail");
found |= AnalyzeCount(Counties.CountByEmail(address), address, "Counties.Email");
found |= AnalyzeCount(Counties.CountByAltEmail(address), address,
"Counties.AltEmail");
found |= AnalyzeCount(LocalDistricts.CountByEmail(address), address,
"LocalDistricts.Email");
found |= AnalyzeCount(LocalDistricts.CountByAltEmail(address), address,
"LocalDistricts.AltEmail");
found |= AnalyzeCount(Politicians.CountByEmail(address), address,
"Politicians.Email");
found |= AnalyzeCount(Politicians.CountByCampaignEmail(address), address,
"Politicians.CampaignEmail");
found |= AnalyzeCount(Politicians.CountByEmailVoteUSA(address), address,
"Politicians.EmailVoteUSA");
found |= AnalyzeCount(Politicians.CountByStateEmail(address), address,
"Politicians.StateEmail");
found |= AnalyzeCount(Addresses.EmailExists(address) ? 1 : 0, address,
"Addresses.Email");
found |= AnalyzeCount(PartiesEmails.PartyEmailExists(address) ? 1 : 0, address,
"PartiesEmails.PartyEmail");
found |= AnalyzeCount(OrganizationContacts.CountByEmail(address), address,
"OrganizationContacts.Email");
if (!found)
{
_Messages.Add($"<p class=\"error\">{address} not found</p>");
}
}
private void UnsubscribeOrganizationContact(string email)
{
var table = OrganizationContacts.GetDataByEmail(email);
if (table.Count == 0)
{
Message.InnerText = "The requested email address was not found";
return;
}
foreach (var row in table)
{
row.Email = Empty;
}
OrganizationContacts.UpdateTable(table);
Message.InnerText =
$"Email {email} has been unsubscribed from all future organization emails.";
}
private void DoDeletions(string address)
{
var found = false;
found |= DeletionCount(States.UpdateEmailByEmail(Empty, address), address,
"States.Email");
found |= DeletionCount(States.UpdateAltEmailByAltEmail(Empty, address),
address, "States.AltEmail");
found |= DeletionCount(Counties.UpdateEmailByEmail(Empty, address), address,
"Counties.Email");
found |= DeletionCount(Counties.UpdateAltEmailByAltEmail(Empty, address),
address, "Counties.AltEmail");
found |= DeletionCount(LocalDistricts.UpdateEmailByEmail(Empty, address),
address, "LocalDistricts.Email");
found |= DeletionCount(LocalDistricts.UpdateAltEmailByAltEmail(Empty, address),
address, "LocalDistricts.AltEmail");
found |= DeletionCount(Politicians.UpdateEmailByEmail(null, address), address,
"Politicians.Email");
found |= DeletionCount(
Politicians.UpdateCampaignEmailByCampaignEmail(Empty, address), address,
"Politicians.CampaignEmail");
found |= DeletionCount(
Politicians.UpdateEmailVoteUSAByEmailVoteUSA(Empty, address), address,
"Politicians.EmailVoteUSA");
found |= DeletionCount(
Politicians.UpdateStateEmailByStateEmail(Empty, address), address,
"Politicians.StateEmail");
if (Addresses.EmailExists(address))
{
Addresses.DeleteByEmail(address);
found |= DeletionCount(1, address, "Addresses");
}
found |= DeletionCount(PartiesEmails.DeleteByPartyEmail(address), address,
"PartiesEmails.PartyEmail");
found |= DeletionCount(
OrganizationContacts.UpdateEmailByEmail(Empty, address), address,
"OrganizationContacts.Email");
if (!found)
{
_Messages.Add($"<p class=\"error\">{address} not found</p>");
}
}
public static string LookupUpEmailSourceCode(string sourceCode)
{
var sourceType = sourceCode[0];
var subType = sourceCode[1];
var hyphenPos = sourceCode.IndexOf('-');
var stateCode = Empty;
if (hyphenPos <= 0 || hyphenPos > 2 || sourceCode.Length < hyphenPos + 2)
{
return(null);
}
switch (sourceType)
{
case 'S':
case 'C':
case 'L':
if (sourceCode.Length < 5)
{
return(null);
}
if (subType != 'P' && subType != 'A')
{
return(null);
}
stateCode = sourceCode.Substring(hyphenPos + 1, 2);
hyphenPos = 4;
if (!StateCache.IsValidStateCode(stateCode))
{
return(null);
}
break;
case 'P':
if (subType != 'M' && subType != 'C' && subType != 'S' && subType != 'V')
{
return(null);
}
break;
case 'A':
case 'Z':
case 'O':
break;
default:
return(null);
}
var idString = sourceCode.Substring(hyphenPos + 1);
if (!int.TryParse(idString, out var id) && sourceType != 'S')
{
return(null);
}
switch (sourceType)
{
case 'S':
switch (subType)
{
case 'P':
return(States.GetContactEmail(stateCode));
case 'A':
return(States.GetAltEmail(stateCode));
}
break;
case 'C':
switch (subType)
{
case 'P':
return(Counties.GetContactEmail(stateCode, idString));
case 'A':
return(Counties.GetAltEmail(stateCode, idString));
}
break;
case 'L':
switch (subType)
{
case 'P':
return(LocalDistricts.GetContactEmail(stateCode, idString));
case 'A':
return(LocalDistricts.GetAltEmail(stateCode, idString));
}
break;
case 'P':
switch (subType)
{
case 'M':
return(Politicians.GetEmailById(id));
case 'C':
return(Politicians.GetCampaignEmailById(id));
case 'S':
return(Politicians.GetStateEmailById(id));
case 'V':
return(Politicians.GetEmailVoteUSAById(id));
}
break;
case 'A':
return(Addresses.GetEmailById(id));
case 'Z':
return(PartiesEmails.GetPartyEmailById(id));
case 'O':
return(OrganizationContacts.GetEmailByContactId(id));
}
return(null);
}
private static void Main()
{
try
{
var fileToRead =
new FileInfo(
@"D:\Users\Curt\Dropbox\Documents\Vote\Mantis\772\PACs Master List revised.v2.xlsx");
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);
var dataTable = result.Tables[0];
MessageBox.Show(Join("\n",
dataTable.Columns.OfType <DataColumn>().Select(c => c.ColumnName)));
MessageBox.Show(dataTable.Rows.Count.ToString());
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Type"] as string)
.Where(t => !IsNullOrWhiteSpace(t)).Distinct()), "Type");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Ideology"] as string)
.Where(t => !IsNullOrWhiteSpace(t)).Distinct()), "Ideology");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Status"] as string)
.Where(t => !IsNullOrWhiteSpace(t)).Distinct()), "Status");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["State"] as string)
.Where(s => !IsNullOrWhiteSpace(s) && !StateCache.IsValidStateCode(s)).Distinct()), "Invalid States");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Notes"] as string)
.Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Notes");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Mission URL"] as string)
.Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Mission URL");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Email1"] as string)
.Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Email1");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Email2"] as string)
.Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Email2");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Email1"] as string)
.Where(m => !IsNullOrWhiteSpace(m) && m != "#N/A" && !Validation.IsValidEmailAddress(m)).Distinct()), "Invalid Email1s");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Email2"] as string)
.Where(m => !IsNullOrWhiteSpace(m) && m != "#N/A" && !Validation.IsValidEmailAddress(m)).Distinct()), "Invalid Email2s");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["PAC URL"] as string)
.Where(m => !IsNullOrWhiteSpace(m) && !VotePage.IsValidUrl(m)).Distinct()), "Invalid URLs");
MessageBox.Show(Join("\n",
dataTable.Rows.OfType <DataRow>().Select(r => r["Mission URL"] as string)
.SelectMany(s => s.SafeString().Split('\n'))
.Where(m => !IsNullOrWhiteSpace(m) && !VotePage.IsValidUrl(m)).Distinct()), "Invalid Mission URLs");
var orgTypeDictionary = OrganizationTypes.GetAllData()
.ToDictionary(r => r.OrgType, r => r.OrgTypeId);
var orgSubTypeDictionary = OrganizationSubTypes.GetAllData()
.ToDictionary(r => new { r.OrgTypeId, r.OrgSubType }, r => r.OrgSubTypeId);
var ideologyDictionary = OrganizationIdeologies.GetAllData()
.ToDictionary(r => r.Ideology, r => r.IdeologyId);
ideologyDictionary.Add(Empty, 0);
string Field(DataRow row, string fieldname)
{
return((row[fieldname] as string).SafeString().Trim());
}
foreach (var row in dataTable.Rows.OfType <DataRow>())
{
var orgTypeId = orgTypeDictionary["PAC"];
string orgSubType;
switch (Field(row, "Type"))
{
case "Independent Expenditures PAC":
orgSubType = "Independent Expenditures";
break;
default:
orgSubType = "Standard";
break;
}
var orgSubTypeId =
orgSubTypeDictionary[new { OrgTypeId = orgTypeId, OrgSubType = orgSubType }];
var orgId = Organizations.Insert(orgTypeId, orgSubTypeId, ideologyDictionary[Field(row, "Ideology")],
DateTime.UtcNow, Field(row, "Name"), Field(row, "Abbreviated Name"), Empty, Empty,
Field(row, "City"), Field(row, "State"), Empty, Field(row, "PAC URL"), Empty, Empty,
Empty, null, Empty, Empty, null, Empty);
if (Field(row, "Contact 1") != Empty || Field(row, "Title 1") != Empty ||
Field(row, "Email1") != Empty || Field(row, "Phone1") != Empty)
{
OrganizationContacts.Insert(orgId, Field(row, "Contact 1"),
Field(row, "Email1"), Field(row, "Phone1"), Field(row, "Title 1"), 10);
}
if (Field(row, "Contact 2") != Empty || Field(row, "Title 2") != Empty ||
Field(row, "Email2") != Empty || Field(row, "Phone2") != Empty)
{
OrganizationContacts.Insert(orgId, Field(row, "Contact 2"),
Field(row, "Email2"), Field(row, "Phone2"), Field(row, "Title 2"), 10);
}
var mOrder = 0;
foreach (var missionUrl in Field(row, "Mission URL").Split('\n')
.Select(s => s.Trim()).Where(s => s != Empty))
{
mOrder += 10;
OrganizationMissionUrls.Insert(orgId, missionUrl, mOrder);
}
var notes = Field(row, "Notes");
if (notes != Empty)
{
OrganizationNotes.Insert(orgId, DateTime.UtcNow, notes);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
