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();
list.AddRange(LogPoliticiansImagesOriginal.GetTwoLatestImageDateAndUsers(
politicianKey)
.Select(
row =>
new LoggedImageInfo
{
DateStamp = row.ProfileOriginalDate,
UserName = row.UserName
}));
return(list.OrderByDescending(o => o.DateStamp)
.Take(2)
.ToList());
}
public static DateTime GetSecondLatestProfileImageDate(string politicianKey,
out string userName)
{
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 { row.DateStamp, row.UserName })
.ToList();
list.AddRange(LogPoliticiansImagesOriginal.GetTwoLatestImageDateAndUsers(
politicianKey)
.Select(row => new { DateStamp = row.ProfileOriginalDate, row.UserName }));
if (list.Count < 2)
{
userName = string.Empty;
return(VoteDb.DateTimeMin);
}
var item = list.OrderByDescending(o => o.DateStamp)
.Skip(1)
.First();
userName = item.UserName;
return(item.DateStamp);
}
// 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 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 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 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);
}
