public static int UpdatePoliticianElectionsAllRows(string newValue)
{
const string cmdText = "UPDATE Sitemap SET PoliticianElections=@newValue";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static int UpdateMinimumCandidatesAllRows(int newValue)
{
const string cmdText = "UPDATE Sitemap SET MinimumCandidates=@newValue";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static int UpdateMustHaveStatementAllRows(bool newValue)
{
const string cmdText = "UPDATE Sitemap SET MustHaveStatement=@newValue";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static SitemapTable GetDomainDataSorted(int commandTimeout = -1)
{
const string cmdText = "SELECT DomainDataCode,DomainName FROM Sitemap" +
" ORDER BY DomainDataCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, SitemapTable.ColumnSet.Domain));
}
private void ProcessOneTable(TableWithEmails table)
{
// Create a list of all necessary columns
List <string> columns = new List <string>();
foreach (string column in table.KeyColumns)
{
if (!columns.Contains(column))
{
columns.Add(column);
}
}
foreach (string column in table.EmailColumns)
{
if (!columns.Contains(column))
{
columns.Add(column);
}
}
// Build the select statement
StringBuilder sbSelect = new StringBuilder();
sbSelect.Append("SELECT ");
sbSelect.Append(string.Join(",", columns));
sbSelect.Append(" FROM ");
sbSelect.Append(table.TableName);
// Create the DbDataReader
DbConnection cn = VoteDb.GetOpenConnection();
DbCommand cmd = VoteDb.GetCommand(sbSelect.ToString(), cn, 0);
DbDataReader reader = cmd.ExecuteReader();
// Read and process each row
AppendStatusText("Processing table {0}, examining columns {1}", table.TableName,
string.Join(", ", table.EmailColumns));
int rows = 0;
using (reader)
while (reader.Read())
{
rows++;
// Check each email column
foreach (string emailColumn in table.EmailColumns)
{
string email = reader[emailColumn].ToStringOrNull();
if (!string.IsNullOrWhiteSpace(email) && !Validation.IsValidEmailAddress(email))
{
IEnumerable <string> keyDesc =
table.KeyColumns
.Select(col => col + " = " + reader[col].ToString());
AppendStatusText("{0} = {1} [{2}]", emailColumn, email, string.Join(", ", keyDesc));
}
}
}
AppendStatusText("Finished table {0}, rows = {1}", table.TableName, rows);
AppendStatusText("");
}
public static InstructionalVideosTable GetAdminData(int commandTimeout = -1)
{
const string cmdText =
"SELECT Id,Title,Description,EmbedCode,Url,AdminOrder,VolunteersOrder" +
" FROM InstructionalVideos WHERE AdminOrder!=0 ORDER BY AdminOrder";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, InstructionalVideosTable.ColumnSet.All));
}
public static int UpdateColumnAllRows(Column column, object newValue)
{
var cmdText = "UPDATE Sitemap SET {0}=@newValue";
cmdText = string.Format(cmdText, GetColumnName(column));
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static PartiesTable GetMajorPartyDataByStateCode(string stateCode,
int commandTimeout = -1)
{
const string cmdText =
"SELECT PartyKey,PartyCode,StateCode,PartyOrder,PartyName,PartyURL,PartyAddressLine1,PartyAddressLine2,PartyCityStateZip,IsPartyMajor FROM Parties WHERE StateCode=@StateCode AND IsPartyMajor=1 AND LENGTH(PartyCode)=1 ORDER BY PartyOrder";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
return(FillTable(cmd, PartiesTable.ColumnSet.All));
}
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
// ReSharper disable UnusedMethodReturnValue.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
public static EmailQueueTable GetPendingEmail(int maxEmails, int commandTimeout = -1)
{
var cmdText = SelectAllCommandText + " WHERE SentTime IS NULL";
if (maxEmails > 0)
{
cmdText = VoteDb.InjectSqlLimit(cmdText, maxEmails);
}
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, EmailQueueTable.ColumnSet.All));
}
public static AddressesTable GetData(int limit, int commandTimeout = -1)
{
var cmdText = "SELECT Id,FirstName,LastName,Address,City,StateCode,Zip5,Zip4,Email,Phone," +
"DateStamp,SourceCode,OptOut,SendSampleBallots,SentBallotChoices,EmailAttachedDate," +
"EmailAttachedSource,CongressionalDistrict,StateSenateDistrict,StateHouseDistrict," +
"County,District,Place,Latitude,Longitude,DistrictLookupDate,CommentCount,LastCommentDate," +
"IsDonor" +
$" FROM Addresses WHERE Address!='' AND Latitude IS Null LIMIT {limit}";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(Addresses.FillTable(cmd, AddressesTable.ColumnSet.All));
}
private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
var tableInfoList = e.Argument as List <TableInfo>;
foreach (var tableInfo in tableInfoList.Where(info => info.Enabled))
{
int rows = 0;
AppendStatusText("Reading table {0} with {1} text columns", tableInfo.Name,
tableInfo.TextColumns.Count);
DataTable table = new DataTable("ColumnList");
// get all text and key fields
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
List <string> allColumns = new List <string>();
allColumns.AddRange(tableInfo.PrimaryKeyColumns);
allColumns.AddRange(tableInfo.TextColumns.Select(info => info.Name));
string columns = string.Join(",", allColumns);
var command = VoteDb.GetCommand(
string.Format("SELECT {0} FROM {1}", columns, tableInfo.Name), cn, 0)
as MySqlCommand;
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(table);
}
Dictionary <string, ColumnStatistics> statistics =
new Dictionary <string, ColumnStatistics>();
foreach (var columnInfo in tableInfo.TextColumns)
{
statistics.Add(columnInfo.Name, new ColumnStatistics());
}
foreach (DataRow row in table.Rows)
{
HandleRow(row, tableInfo, statistics);
rows++;
}
foreach (var columnInfo in tableInfo.TextColumns)
{
AppendStatusText("Column {0}: {1} with unnormalized line breaks, {2} with redundant spaces",
columnInfo.Name, statistics[columnInfo.Name].HasUnnormalizedLineBreaks,
statistics[columnInfo.Name].HasRedundantSpaces);
if (RepairLineBreaks || RepairRedundantSpaces)
{
AppendStatusText("Column {0}: {1} unnormalized line breaks repaired, {2} redundant spaces repaired",
columnInfo.Name, statistics[columnInfo.Name].HasUnnormalizedLineBreaks,
statistics[columnInfo.Name].HasRedundantSpaces);
}
}
AppendStatusText("{0} rows examined", rows);
}
AppendStatusText("Process is complete.");
}
public static EmailQueueViewTable GetPendingEmail(
int maxEmails, int commandTimeout = -1)
{
var cmdText = SelectAllCommandText + " WHERE SentTime IS NULL" +
" AND IsClosed=0" + " AND TRIM(Subject)<>''" + " AND TRIM(Template)<>''";
if (maxEmails > 0)
{
cmdText = VoteDb.InjectSqlLimit(cmdText, maxEmails);
}
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, EmailQueueViewTable.ColumnSet.All));
}
public static OfficesAllIdentifiedTable GetDataByStateCode(string stateCode,
string countyCode = "", string localCode = "",
int commandTimeout = -1)
{
const string cmdText = "SELECT StateCode,CountyCode,LocalCode,OfficeLevel," +
"IsOfficesAllIdentified FROM OfficesAllIdentified" +
" WHERE StateCode=@StateCode" +
" AND CountyCode=@CountyCode AND LocalCode=@LocalCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalCode", localCode);
return(FillTable(cmd, OfficesAllIdentifiedTable.ColumnSet.All));
}
private static DataTable GetAddresses()
{
const string cmdText = "SELECT Id,Address,City,StateCode FROM Addresses" +
" WHERE NOT Latitude IS NULL AND Latitude!=0 AND" +
" NOT Longitude IS NULL AND Longitude!=0 AND" +
" Address != '' AND City != '' AND StateCode != ''";
var cmd = VoteDb.GetCommand(cmdText, -1);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
var adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static int UpdateIsOfficesAllIdentified(bool newValue,
string stateCode, int officeLevel, string countyCode = "",
string localCode = "")
{
const string cmdText = "UPDATE OfficesAllIdentified" +
" SET IsOfficesAllIdentified=@newValue WHERE StateCode=@StateCode" +
" AND OfficeLevel=@OfficeLevel AND CountyCode=@CountyCode" +
" AND LocalCode=@LocalCode";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "OfficeLevel", officeLevel);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalCode", localCode);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static DataTable GetData()
{
const string cmdText = "SELECT ep.ElectionKey,o.OfficeLevel,e.ElectionDesc" +
" FROM ElectionsPoliticians ep" +
" INNER JOIN Elections e ON e.ElectionKey=ep.ElectionKeyState" +
" INNER JOIN Offices o ON o.OfficeKey = ep.OfficeKey" +
" WHERE e.ElectionType = 'P'";
var cmd = VoteDb.GetCommand(cmdText);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
private void CheckZipStreetsDownloadedAddressPrimaryLowNumberBadLength()
{
string sqlText = "SELECT UpdateKey,AddressPrimaryLowNumber,AddressPrimaryHighNumber FROM ZipStreetsDownloaded";
int rowCount = 0;
Dictionary <string, object> badLength = new Dictionary <string, object>();
using (DbConnection cn = VoteDb.GetOpenConnection())
{
DbCommand command = VoteDb.GetCommand(sqlText, cn, 0);
using (DbDataReader reader = command.ExecuteReader())
{
int updateKeyOrd = reader.GetOrdinal("UpdateKey");
int addressPrimaryLowNumberOrd = reader.GetOrdinal("AddressPrimaryLowNumber");
int addressPrimaryHighNumberOrd = reader.GetOrdinal("AddressPrimaryHighNumber");
while (reader.Read())
{
string updateKey = reader.GetString(updateKeyOrd);
string addressPrimaryLowNumber = reader.GetString(addressPrimaryLowNumberOrd);
string addressPrimaryHighNumber = reader.GetString(addressPrimaryHighNumberOrd);
if (AllNumericRegex.Match(addressPrimaryLowNumber).Success&&
addressPrimaryLowNumber.Length != 10)
{
badLength[updateKey] = null;
}
if (AllNumericRegex.Match(addressPrimaryHighNumber).Success&&
addressPrimaryHighNumber.Length != 10)
{
badLength[updateKey] = null;
}
rowCount++;
}
}
}
string badKeys = string.Join(Environment.NewLine,
badLength.Select(kvp => kvp.Key));
}
public static DataRow GetLoggedEmailDetail(int id, int commandTimeout = -1)
{
const string cmdText =
"SELECT le.Id,le.Body,lb.CcEmails,lb.BccEmails,lb.Description," +
"lb.SelectionCriteria FROM votelog.LogEmail le " +
" INNER JOIN votelog.LogEmailBatches lb ON lb.Id=le.LogBatchId" +
" WHERE le.Id=@Id";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteLogDb.AddCommandParameter(cmd, "Id", id);
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
}
return(table.Rows.Count == 0 ? null : table.Rows[0]);
}
public static AnswersViewTable GetDataForConsolidation(string politicianKey1,
string politicianKey2, int commandTimeout = -1)
{
const string cmdText =
"SELECT a.PoliticianKey,a.Answer,a.IssueKey,a.QuestionKey,a.Sequence," +
"a.Source,a.DateStamp,a.YouTubeUrl,a.YouTubeDescription,a.YouTubeRunningTime,a.YouTubeSourceUrl," +
"a.YouTubeSource,a.YouTubeDate,i.Issue,i.IssueLevel,a.YouTubeRefreshTime,a.YouTubeAutoDisable," +
"i.IssueOrder,q.Question,q.QuestionOrder FROM Answers a" +
" INNER JOIN Issues i ON i.IssueKey=a.IssueKey AND i.IsIssueOmit=0" +
" INNER JOIN Questions q ON q.QuestionKey=a.QuestionKey AND q.IsQuestionOmit=0" +
" WHERE PoliticianKey IN (@PoliticianKey1,@PoliticianKey2) AND" +
" (TRIM(a.Answer) <> '' OR" +
" TRIM(a.YouTubeUrl)<>'' AND NOT a.YouTubeUrl IS NULL AND (a.YouTubeAutoDisable IS NULL OR a.YouTubeAutoDisable='')" +
")" +
" ORDER BY IssueLevel,IssueOrder,Issue,QuestionOrder,Question,DateStamp DESC,PoliticianKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "PoliticianKey1", politicianKey1);
VoteDb.AddCommandParameter(cmd, "PoliticianKey2", politicianKey2);
return(FillTable(cmd, AnswersViewTable.ColumnSet.All));
}
private void CheckZipStreetsDownloadedOddEven()
{
string sqlText = "SELECT AddressPrimaryLowNumber,AddressPrimaryHighNumber,AddressPrimaryEvenOdd FROM ZipStreetsDownloaded";
int rowCount = 0;
List <OddEvenInfo> ofInterest = new List <OddEvenInfo>();
using (DbConnection cn = VoteDb.GetOpenConnection())
{
DbCommand command = VoteDb.GetCommand(sqlText, cn, 0);
using (DbDataReader reader = command.ExecuteReader())
{
int lowOrd = reader.GetOrdinal("AddressPrimaryLowNumber");
int highOrd = reader.GetOrdinal("AddressPrimaryHighNumber");
int evenOddOrd = reader.GetOrdinal("AddressPrimaryEvenOdd");
while (reader.Read())
{
string low = reader.GetString(lowOrd);
string high = reader.GetString(highOrd);
string evenOdd = reader.GetString(evenOddOrd);
if (low.Length != 0 && !low.IsDigits() &&
evenOdd != "B" &&
low != high &&
ContainsLetter(low))
{
ofInterest.Add(new OddEvenInfo()
{
Low = low,
High = high,
EvenOdd = evenOdd
});
}
rowCount++;
}
}
}
}
public static AnswersViewTable GetAllDataByIssueKeyPoliticianKey(
string issueKey, string politicianKey, int commandTimeout = -1)
{
const string cmdText =
"SELECT ig.IssueGroupKey,ig.IssueGroupOrder,ig.Heading AS IssueGroupHeading," +
"ig.SubHeading AS IssueGroupSubHeading,i.IssueKey,i.IssueOrder,i.IssueLevel,i.Issue,i.IsTextSourceOptional," +
"i.StateCode AS StateCode,q.QuestionKey,q.QuestionOrder,q.Question,a.PoliticianKey,a.Source," +
"a.DateStamp,a.Answer,a.YouTubeUrl,a.YouTubeDescription,a.YouTubeRunningTime,a.YouTubeSourceUrl," +
"a.YouTubeSource,a.YouTubeDate,a.Sequence,a.YouTubeRefreshTime,a.YouTubeAutoDisable FROM Issues i" +
" INNER JOIN IssueGroupsIssues ON IssueGroupsIssues.IssueKey = i.IssueKey" +
" INNER JOIN IssueGroups ig ON ig.IssueGroupKey = IssueGroupsIssues.IssueGroupKey" +
" INNER JOIN Questions q ON q.IssueKey = i.IssueKey" +
" LEFT JOIN Answers a on a.QuestionKey = q.QuestionKey AND a.PoliticianKey=@PoliticianKey" +
" WHERE i.IssueKey=@IssueKey" +
" AND i.IsIssueOmit=0 AND q.IsQuestionOmit=0" +
" ORDER BY IssueGroupOrder,IssueOrder,QuestionOrder,DateStamp DESC,Sequence DESC";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "IssueKey", issueKey);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
return(FillTable(cmd, AnswersViewTable.ColumnSet.All));
}
public void GetTableInfo()
{
TableNames = new List <String>();
using (var cn = VoteDb.GetOpenConnection())
{
DataTable table = new DataTable("TableList");
var command = VoteDb.GetCommand("SHOW FULL TABLES", cn);
var adapter = VoteDb.GetDataAdapter(command);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string tableName = row[0] as string;
if ((row[1] as string) != "VIEW" && !ExcludeTables.Contains(tableName))
{
TableNames.Add(tableName);
}
}
}
AppendStatusText("Found {0} tables", TableNames.Count);
}
public static DataTable GetLoggedEmails(string[] contactTypes, string jurisdictionLevel,
string[] stateCodes, string[] countyCodes, string[] localCodes,
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.LocalCode,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" +
" 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" +
" INNER JOIN LocalDistricts l ON l.LocalCode=le.LocalCode" + " {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 = localCodes.Length == 1 && localCodes[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<>''");
innerAnds.Add("le.LocalCode=''");
}
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"));
innerAnds.Add("le.LocalCode=''");
}
break;
case "locals":
if (stateCodes.Length == 0 || countyCodes.Length == 0 ||
localCodes.Length == 0) // force no results from this section
{
innerAnds.Add("le.StateCode=''");
}
else if (allLocals)
{
if (!allStates)
{
innerAnds.Add(stateCodes.SqlIn("le.StateCode"));
}
if (!allCounties)
{
innerAnds.Add(countyCodes.SqlIn("le.CountyCode"));
}
innerAnds.Add("le.LocalCode<>''");
}
else if (countyCodes.Length > 0)
{
Debug.Assert(stateCodes.Length == 1, "Expecting a single state");
Debug.Assert(countyCodes.Length == 1, "Expecting a single county");
innerAnds.Add("le.StateCode=" + stateCodes[0].SqlLit());
innerAnds.Add("le.CountyCode=" + countyCodes[0].SqlLit());
innerAnds.Add(localCodes.SqlIn("le.LocalCode"));
}
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(string.Join(" AND ", innerAnds));
}
if (middleOrs.Count > 0) // combine into an AND
{
middleAnds.Add("(" + string.Join(" OR ", middleOrs) + ")");
}
if (middleAnds.Count > 0) // combine into an OR
{
outerOrs.Add(string.Join(" AND ", middleAnds));
}
if (outerOrs.Count > 0) // combine into an AND
{
outerAnds.Add("(" + string.Join(" OR ", outerOrs) + ")");
}
var whereClause = outerAnds.Count > 0
? "WHERE " + string.Join(" AND ", outerAnds)
: string.Empty;
var limitClause = maximumResults > 0
? "LIMIT " + maximumResults
: string.Empty;
var cmdText = string.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);
}
return(table);
}
private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
var tableInfoList = e.Argument as List <TableInfo>;
char badCharacter = BadCharacter;
foreach (var control in SearchCharacterGroupBox.Controls)
{
var radioButton = control as RadioButton;
if (radioButton != null && radioButton.Checked)
{
string tag = radioButton.Tag as string;
if (tag != null && tag.Length > 0)
{
badCharacter = tag[0];
break;
}
}
}
using (var editForm = new EditForm(this))
foreach (var tableInfo in tableInfoList.Where(info => info.Enabled))
{
AppendStatusText("Reading table {0}", tableInfo.Name);
DataTable table = new DataTable("ColumnList");
// get all text and key fields
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
List <string> allColumns = new List <string>();
allColumns.AddRange(tableInfo.PrimaryKeyColumns);
allColumns.AddRange(tableInfo.TextColumns.Select(info => info.Name));
string columns = string.Join(",", allColumns);
var command = VoteDb.GetCommand(
string.Format("SELECT {0} FROM {1}", columns, tableInfo.Name), cn, 0)
as MySqlCommand;
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(table);
}
bool hasPoliticianKey =
tableInfo.TextColumns
.FirstOrDefault(info => info.Name == "PoliticianKey") != null ||
tableInfo.PrimaryKeyColumns
.FirstOrDefault(name => name == "PoliticianKey") != null;
AppendStatusText("Looking for invalid characters in table {0}", tableInfo.Name);
foreach (DataRow row in table.Rows)
{
//if (
// row["PoliticianKey"].ToString() != "MARomneyMitt" &&
// row["PoliticianKey"].ToString() != "WIRyanPaul" &&
// row["PoliticianKey"].ToString() != "ILObamaBarack" &&
// row["PoliticianKey"].ToString() != "DEBidenJosephRJr")
// continue;
if (!hasPoliticianKey ||
row["PoliticianKey"].ToString().IsGeIgnoreCase(StartAtTextBox.Text))
{
HandleRow(row, editForm, tableInfo, badCharacter);
if (editForm.SkippingTable)
{
AppendStatusText("Skipping rest of table {0}", tableInfo.Name);
editForm.SkippingTable = false;
break;
}
}
if (editForm.Exiting)
{
AppendStatusText("Exiting");
break;
}
}
}
AppendStatusText("Process is complete.");
}