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);
}
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);
}
}
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));
}
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.
}
}
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));
}
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));
}
public static LogNotesTable GetLatestDataByUserStateCode(
string userStateCode, int commandTimeout = -1)
{
var cmdText = SelectAllCommandText + " WHERE UserStateCode=@UserStateCode" +
" ORDER BY DateStamp DESC";
cmdText = VoteDb.InjectSqlLimit(cmdText, 1);
var cmd = VoteLogDb.GetCommand(cmdText, commandTimeout);
VoteLogDb.AddCommandParameter(cmd, "UserStateCode", userStateCode);
return(FillTable(cmd, LogNotesTable.ColumnSet.All));
}
public static async void Timer_Voters2(object sender, ElapsedEventArgs e)
{
if (Interlocked.Exchange(ref VoteLock, 1) == 0)
{
try
{
var voters = await WebUtil.GetVotersAsync();
var old = await VoteDb.GetVoters(500);
var votersParsed = voters.Select(x => x.Id).ToList();
votersParsed.Reverse();
List <ulong> add = NewEntries(old, votersParsed);
if (add.Count > 500)
{
var ch = await Program.GetClient().GetUser(AppSettings.OwnerId).CreateDMChannelAsync();
string er = "```\n";
foreach (var id in voters.Take(10))
{
er += $"{id.Id}\n";
}
er += "...\n";
foreach (var id in voters.Skip(add.Count - 10))
{
er += $"{id.Id}\n";
}
er += "```";
await ch.SendMessageAsync($"Found {add.Count} new voters.\n {er}");
return;
}
await VoteDb.AddVoters(add);
await SendRewards(add);
if (add.Count > 0)
{
Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} Shipped {add.Count} lootboxes.");
}
}
catch (Exception ex)
{
SentrySdk.CaptureException(ex);
}
finally
{
VoteLock = 0;
}
}
}
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 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));
}
// 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 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 LogPoliticiansImagesHeadshotTable GetLatestData(
// string politicianKey)
//{
// return GetLatestData(politicianKey, -1);
//}
public static LogPoliticiansImagesHeadshotTable GetLatestData(
string politicianKey, int commandTimeout)
{
var cmdText =
"SELECT PrimaryKey,PoliticianKey,HeadshotOriginal,HeadshotDate,UserSecurity,UserName" +
" FROM LogPoliticiansImagesHeadshot" +
" WHERE PoliticianKey=@PoliticianKey" + " ORDER BY HeadshotDate DESC";
cmdText = VoteDb.InjectSqlLimit(cmdText, 1);
var cmd = VoteLogDb.GetCommand(cmdText, commandTimeout);
VoteLogDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
return(FillTable(cmd, LogPoliticiansImagesHeadshotTable.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));
}
public static LogPoliticiansImagesOriginalTable GetTwoLatestImageDateAndUsers(
string politicianKey)
{
var cmdText =
"SELECT ProfileOriginalDate,UserName FROM LogPoliticiansImagesOriginal" +
" WHERE PoliticianKey=@PoliticianKey ORDER BY ProfileOriginalDate DESC";
cmdText = VoteDb.InjectSqlLimit(cmdText, 2);
var cmd = VoteLogDb.GetCommand(cmdText, -1);
VoteLogDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
var table = FillTable(cmd, LogPoliticiansImagesOriginalTable.ColumnSet.DateAndUser);
return(table);
}
public static string GetFirstStateByZipCode(string zipCode)
{
var cmdText = "SELECT State FROM ZipCitiesDownloaded WHERE ZipCode=@ZipCode";
cmdText = VoteDb.InjectSqlLimit(cmdText, 1);
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, -1);
VoteZipNewLocalDb.AddCommandParameter(cmd, "ZipCode", zipCode);
var result = VoteZipNewLocalDb.ExecuteScalar(cmd);
if ((result == null) || (result == DBNull.Value))
{
return(null);
}
return(result as string);
}
public static List <LoggedImageInfo> GetTwoLatestProfileImageInfos(string politicianKey)
{
var cmdText = "SELECT DateStamp,UserName FROM LogDataChange" +
" WHERE TableName='PoliticiansImagesBlobs' AND ColumnName='ProfileOriginal'" +
" AND KeyValues=@PoliticianKey ORDER BY DateStamp DESC";
cmdText = VoteDb.InjectSqlLimit(cmdText, 2);
var cmd = VoteLogDb.GetCommand(cmdText, -1);
VoteLogDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
var list = FillTable(cmd, LogDataChangeTable.ColumnSet.DateAndUser).Select(row =>
new LoggedImageInfo {
DateStamp = row.DateStamp, UserName = row.UserName
}).ToList();
return(list.OrderByDescending(o => o.DateStamp).Take(2).ToList());
}
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));
}
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 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));
}
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);
}
