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);
}
public static List <BillingItem> GetBillingSummary(string userName,
DateTime lowDate, DateTime highDate, int commandTimeout = -1)
{
var cmdText = SelectBillingCommandText + " WHERE UserName=@UserName" +
" AND DateStamp>=@LowDate" + " AND DateStamp<=@HighDate";
var cmd = VoteLogDb.GetCommand(cmdText, commandTimeout);
VoteLogDb.AddCommandParameter(cmd, "UserName", userName);
VoteLogDb.AddCommandParameter(cmd, "LowDate", lowDate);
VoteLogDb.AddCommandParameter(cmd, "HighDate", highDate);
var table = FillTable(cmd, LogDataChangeTable.ColumnSet.Billing);
var summary = table.GroupBy(
row => new { row.UserName, row.TableName, row.DateStamp })
.Select(g =>
{
var first = g.First();
var type = "Change";
foreach (var row in g)
{
switch (row.ColumnName)
{
case "*INSERT":
type = "Add";
break;
case "*DELETE":
type = "Delete";
break;
}
}
return(new BillingItem
{
ActivityName = first.TableName,
ActivityType = type,
UserName = first.UserName,
DateStamp = first.DateStamp.Date,
Count = g.Count()
});
})
.Union(LogLogins.GetDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "Login",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogPoliticianAnswers.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "Answers",
ActivityType = "Change",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogPoliticianAdds.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "Politicians",
ActivityType = "Add",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogPoliticianChanges.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "Politicians",
ActivityType = "Change",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogElectionPoliticianAddsDeletes.GetBillingDataByUserNameDateStampRange(userName,
lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "ElectionsPoliticians",
ActivityType = "Change",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogElectionOfficeChanges.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "ElectionsOffices",
ActivityType = "Change",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogOfficeChanges.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "Offices",
ActivityType = "Change",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogOfficeOfficialAddsDeletes.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "OfficesOfficials",
ActivityType = "Add",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogOfficeOfficialChanges.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "OfficesOfficials",
ActivityType = "Change",
UserName = row.UserName,
DateStamp = row.DateStamp
}))
.Union(LogPoliticiansImagesOriginal.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "PoliticiansImagesBlobs",
ActivityType = "Change",
UserName = row.UserName,
DateStamp = row.ProfileOriginalDate
}))
.Union(LogAdminData.GetBillingDataByUserNameDateStampRange(userName, lowDate,
highDate)
.Select(
row =>
new BillingItem
{
ActivityName = "PartiesEmails",
ActivityType = "Add",
UserName = row.UserName,
DateStamp = row.DateStamp
}));
return(summary.OrderBy(i => i.UserName)
.ThenBy(i => i.DateStamp)
.ThenBy(i => i.ActivityName)
.ToList());
}
