private List <ColumnInfo> GetTextColumns(string tableName, List <string> keyColumns)
{
var result = new List <ColumnInfo>();
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
DataTable table = new DataTable("ColumnList");
var command = new MySqlCommand("SHOW COLUMNS FROM " + tableName, cn);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
int maxSize;
Type type = SqlTypeToDotNetType(row["Type"] as string, out maxSize);
string name = row["Field"] as string;
if (type == typeof(string) && !keyColumns.Contains(name))
{
result.Add(new ColumnInfo()
{
Name = name,
Size = maxSize
});
}
}
}
return(result);
}
private void FixButton_Click(object sender, EventArgs e)
{
AppendStatusText(string.Empty);
foreach (UpdateInfo updateInfo in Updates)
{
// This could be better done with a single update statement
string sqlText =
"SELECT " + updateInfo.ColumnName +
" FROM " + updateInfo.TableName +
" WHERE " + updateInfo.ColumnName + "=@OriginalKey";
using (var cn = VoteDb.GetOpenConnection())
{
DataTable table = new DataTable("Updates");
var command = VoteDb.GetCommand(sqlText, cn);
VoteDb.AddCommandParameter(command, "OriginalKey", OriginalKeyTextBox.Text);
var adapter = VoteDb.GetDataAdapter(command);
adapter.Fill(table);
AppendStatusText("Table {0}, Column {1}, Found {2}",
updateInfo.TableName, updateInfo.ColumnName, table.Rows.Count);
if (table.Rows.Count > 0)
{
var builder = VoteDb.GetCommandBuilder(adapter);
foreach (DataRow row in table.Rows)
{
row[updateInfo.ColumnName] = NewKeyTextBox.Text;
}
adapter.Update(table);
}
}
}
}
public static DataTable GetPoliticianLoginsByDateStampRange(DateTime lowDate,
DateTime?highDate = null, int commandTimeout = -1)
{
lowDate = lowDate.Date;
if (highDate == null)
{
highDate = lowDate;
}
highDate = highDate.Value.AddDays(1).Date;
const string cmdText = "SELECT l.DateStamp,p.StateCode,p.PoliticianKey,p.FName AS FirstName," +
"p.MName as MiddleName,p.LName AS LastName,p.Nickname,p.Suffix,p.LiveOfficeStatus," +
"o.OfficeKey,o.OfficeLine1,o.OfficeLine2,e.ElectionDesc FROM votelog.LogLogins l" +
" INNER JOIN vote.Politicians p ON p.PoliticianKey=l.UserPoliticianKey" +
" LEFT OUTER JOIN vote.Offices o ON o.OfficeKey=p.LiveOfficeKey" +
" LEFT OUTER JOIN vote.Elections e ON e.ElectionKey=p.LiveElectionKey" +
" WHERE l.UserSecurity='POLITICIAN'" +
" AND l.DateStamp>=@LowDate AND l.DateStamp<@HighDate" +
" GROUP BY DATE(l.DateStamp),p.PoliticianKey" +
" ORDER BY DATE(l.DateStamp),p.StateCode,p.LName,p.FName,p.MName,p.Suffix";
using (var cn = VoteDb.GetOpenConnection())
{
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "LowDate", lowDate);
VoteDb.AddCommandParameter(cmd, "HighDate", highDate);
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public void UpdateValue(TableInfo tableInfo, ColumnInfo columnInfo,
DataRow row, string newValue)
{
try
{
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
string template = "UPDATE {0} SET {1} = @NewValue WHERE {2}";
string where = string.Join(" AND ",
tableInfo.PrimaryKeyColumns.Select(col => col + "=@" + col));
string sqlText = string.Format(template, tableInfo.Name,
columnInfo.Name, where);
var command = new MySqlCommand(sqlText, cn);
VoteDb.AddCommandParameter(command, "NewValue", newValue);
foreach (string keyColumn in tableInfo.PrimaryKeyColumns)
{
VoteDb.AddCommandParameter(command, keyColumn, row[keyColumn]);
}
VoteDb.ExecuteNonQuery(command);
}
}
catch (Exception ex)
{
AppendStatusText("An error occurred updating value: {0}",
ex.Message);
}
}
private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
var tableInfoList = e.Argument as List <TableInfo>;
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);
}
AppendStatusText("Looking for entities in table {0}", tableInfo.Name);
foreach (DataRow row in table.Rows)
{
HandleRow(row, tableInfo);
}
}
AppendStatusText("Process is complete.");
}
private void CheckZipStreetsDownloadedAddressPrimaryLowNumberNonAlpha()
{
string sqlText = "SELECT AddressPrimaryLowNumber FROM ZipStreetsDownloaded";
int rowCount = 0;
Dictionary <string, object> ofInterest = new Dictionary <string, object>();
using (DbConnection cn = VoteDb.GetOpenConnection())
{
DbCommand command = VoteDb.GetCommand(sqlText, cn, 0);
using (DbDataReader reader = command.ExecuteReader())
{
int addressPrimaryLowNumberOrd = reader.GetOrdinal("AddressPrimaryLowNumber");
while (reader.Read())
{
string addressPrimaryLowNumber = reader.GetString(addressPrimaryLowNumberOrd);
if (addressPrimaryLowNumber.Length != 0)
{
if (!HouseRegex.Match(addressPrimaryLowNumber).Success)
{
ofInterest[addressPrimaryLowNumber] = null;
}
}
rowCount++;
}
}
}
}
private void CheckZipStreetsDownloadedAllNumeric()
{
string sqlText = "SELECT StName FROM ZipStreetsDownloaded";
int rowCount = 0;
Dictionary <string, object> ofInterest = new Dictionary <string, object>();
using (DbConnection cn = VoteDb.GetOpenConnection())
{
DbCommand command = VoteDb.GetCommand(sqlText, cn, 0);
using (DbDataReader reader = command.ExecuteReader())
{
int stNameOrd = reader.GetOrdinal("StName");
while (reader.Read())
{
string streetName = reader.GetString(stNameOrd);
if (streetName.IsDigits())
{
ofInterest[streetName] = null;
}
rowCount++;
}
}
}
}
private void CheckZipStreetsDownloadedStreetNameNonAlpha()
{
string sqlText = "SELECT StName FROM ZipStreetsDownloaded";
int rowCount = 0;
Dictionary <string, object> nonAlpha = new Dictionary <string, object>();
using (DbConnection cn = VoteDb.GetOpenConnection())
{
DbCommand command = VoteDb.GetCommand(sqlText, cn, 0);
using (DbDataReader reader = command.ExecuteReader())
{
int stNameOrd = reader.GetOrdinal("StName");
while (reader.Read())
{
string streetName = reader.GetString(stNameOrd);
if (!AllAlphaRegex.Match(streetName).Success)
{
nonAlpha[streetName] = null;
}
rowCount++;
}
}
}
}
private void CheckZipStreetsDownloadedStreetNameSpaces()
{
string sqlText = "SELECT StName FROM ZipStreetsDownloaded";
int rowCount = 0;
Dictionary <string, object> manySpaces = new Dictionary <string, object>();
int maxSpaces = 0;
using (DbConnection cn = VoteDb.GetOpenConnection())
{
DbCommand command = VoteDb.GetCommand(sqlText, cn, 0);
using (DbDataReader reader = command.ExecuteReader())
{
int stNameOrd = reader.GetOrdinal("StName");
while (reader.Read())
{
string streetName = reader.GetString(stNameOrd);
Match match = CountSpacesRegex.Match(streetName);
int spaces = match.Groups["spaces"].Captures.Count;
maxSpaces = Math.Max(maxSpaces, spaces);
if (spaces > 3)
{
manySpaces[streetName] = null;
}
rowCount++;
}
}
}
}
public void GetUpdateInfo()
{
Updates = new List <UpdateInfo>();
foreach (string tableName in TableNames)
{
using (var cn = VoteDb.GetOpenConnection())
{
DataTable table = new DataTable("ColumnList");
var command = VoteDb.GetCommand("SHOW COLUMNS FROM " + tableName, cn);
var adapter = VoteDb.GetDataAdapter(command);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string columnName = row[0] as string;
if (PoliticianKeyColumns.Contains(columnName))
{
Updates.Add(
new UpdateInfo()
{
TableName = tableName,
ColumnName = columnName
});
}
}
}
}
AppendStatusText("Found {0} updateable columns", Updates.Count);
}
protected void Page_Load(object sender, EventArgs e)
{
using (VoteDb.GetOpenConnection())
{
// This tests the health of the DB.
// It will throw an exception if the DB is unavailable.
}
}
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("");
}
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.");
}
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 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]);
}
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++;
}
}
}
}
private List <string> GetPrimaryKeyColumns(string tableName)
{
var result = new List <string>();
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
DataTable table = new DataTable("KeyList");
var command = new MySqlCommand("SHOW KEYS IN " + tableName, cn);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
if ((row["Key_name"] as string) == "PRIMARY")
{
result.Add(row["Column_name"] as string);
}
}
}
return(result);
}
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);
}
private List <TableInfo> GetTableInfo()
{
var result = new List <TableInfo>();
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
DataTable table = new DataTable("TableList");
var command = new MySqlCommand("SHOW FULL TABLES", cn);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
if ((row["Table_type"] as string) != "VIEW") // only real tables
{
string tableName = row["Tables_in_vote"] as string;
var keyColumns = GetPrimaryKeyColumns(tableName);
if (keyColumns.Count > 0) // must have a primary key
{
var textColumns = GetTextColumns(tableName, keyColumns);
if (textColumns.Count > 0)
{
result.Add(new TableInfo()
{
Name = tableName,
PrimaryKeyColumns = keyColumns,
TextColumns = textColumns,
Enabled = true
});
}
}
}
}
}
return(result);
}
private void UpdateValue(TableInfo tableInfo, ColumnInfo columnInfo,
DataRow row, string value)
{
if (columnInfo.Size > 0 && value.Length > columnInfo.Size)
{
MessageBox.Show(string.Format("The new text length exceeds the maximum for this column of {0}",
columnInfo.Size), "Cannot Update New Value", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
try
{
string template = "UPDATE {0} SET {1} = @NewValue WHERE {2}";
string where = string.Join(" AND ",
tableInfo.PrimaryKeyColumns.Select(col => col + "=@" + col));
string sqlText = string.Format(template, tableInfo.Name,
columnInfo.Name, where);
var command = new MySqlCommand(sqlText, cn);
VoteDb.AddCommandParameter(command, "NewValue", value);
foreach (string keyColumn in tableInfo.PrimaryKeyColumns)
{
VoteDb.AddCommandParameter(command, keyColumn, row[keyColumn]);
}
VoteDb.ExecuteNonQuery(command);
}
catch (Exception ex)
{
AppendStatusText("An error occurred updating value: {0}",
ex.Message);
}
}
}
}
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.");
}
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);
}
