public static Answers2Table GetActiveDataByPoliticianKeyNew([NotNull] string politicianKey,
int commandTimeout = -1)
{
// Group to eliminate duplicates caused by questions in multiple issues
const string cmdText =
"SELECT a.PoliticianKey,a.QuestionId,a.Sequence," +
"a.Answer,a.Source,a.DateStamp,a.UserName," +
"a.YouTubeUrl,a.YouTubeSource,a.YouTubeSourceUrl,a.YouTubeDescription," +
"a.YouTubeRunningTime,a.YouTubeSourceUrl,a.YouTubeRunningTime,a.YouTubeDate," +
"a.YouTubeRefreshTime,a.YouTubeAutoDisable,a.FacebookVideoUrl," +
"a.FacebookVideoDescription,a.FacebookVideoRunningTime,a.FacebookVideoDate," +
"a.FacebookVideoRefreshTime,a.FacebookVideoAutoDisable FROM Answers2 a" +
" INNER JOIN IssuesQuestions iq ON iq.QuestionId=a.QuestionId" +
" INNER JOIN Issues2 i ON i.IssueId=iq.IssueId AND i.IsIssueOmit=0" +
" INNER JOIN Questions2 q ON q.QuestionId=a.QuestionId AND q.IsQuestionOmit=0" +
" WHERE PoliticianKey=@PoliticianKey AND" +
" (TRIM(a.Answer) <> '' OR" +
" TRIM(a.YouTubeUrl)<>'' AND NOT a.YouTubeUrl IS NULL AND (a.YouTubeAutoDisable IS NULL OR a.YouTubeAutoDisable='') OR" +
" TRIM(a.FacebookVideoUrl)<>'' AND NOT a.FacebookVideoUrl IS NULL AND (a.FacebookVideoAutoDisable IS NULL OR a.FacebookVideoAutoDisable='')" +
") GROUP BY a.PoliticianKey,a.QuestionId,a.Sequence";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
return(Answers2.FillTable(cmd, Answers2Table.ColumnSet.All));
}
public static void ConsolidateTopics(int toId, int fromId)
{
var politicians = Answers.GetAnswersForConsolidation(toId, fromId).Rows.OfType <DataRow>()
.GroupBy(r => r.PoliticianKey(), StringComparer.OrdinalIgnoreCase);
foreach (var politician in politicians)
{
// delete old
Answers2.DeleteByPoliticianKeyQuestionId(politician.Key, toId);
Answers2.DeleteByPoliticianKeyQuestionId(politician.Key, fromId);
// insert new
var answers = politician.OrderBy(r =>
IsNullOrWhiteSpace(r.YouTubeUrl()) ? r.AnswerDate() : r.YouTubeDate());
var sequence = 0;
foreach (var a in answers)
{
Answers2.Insert(politician.Key, toId, sequence++, a.Answer(), a.Source(), a.DateStamp(),
a.UserName(), a.YouTubeUrl(), a.YouTubeDescription(), a.YouTubeRunningTime(),
a.YouTubeSource(), a.YouTubeSourceUrl(), a.YouTubeDate(), a.YouTubeRefreshTime(),
a.YouTubeAutoDisable(), a.FacebookVideoUrl(), a.FacebookVideoDescription(),
a.FacebookVideoRunningTime(), a.FacebookVideoDate(), a.FacebookVideoRefreshTime(),
a.FacebookVideoAutoDisable());
}
}
// delete the topic
Questions2.DeleteByQuestionId(fromId);
IssuesQuestions.DeleteByQuestionId(fromId);
}
private static void DeleteAllPoliticianReferences(string politicianKey)
{
Answers2.DeleteByPoliticianKey(politicianKey);
ElectionsIncumbentsRemoved.DeleteByPoliticianKey(politicianKey);
ElectionsIncumbentsRemoved.UpdateRunningMateKeyByRunningMateKey(Empty, politicianKey);
ElectionsPoliticians.DeleteByPoliticianKey(politicianKey);
ElectionsPoliticians.UpdateRunningMateKeyByRunningMateKey(Empty, politicianKey);
OfficesOfficials.DeleteByPoliticianKey(politicianKey);
OfficesOfficials.UpdateRunningMateKeyByRunningMateKey(Empty, politicianKey);
PoliticiansImagesBlobs.DeleteByPoliticianKey(politicianKey);
PoliticiansImagesData.DeleteByPoliticianKey(politicianKey);
TempEmail.DeleteByPoliticianKey(politicianKey);
}
public static Answers2Table GetDataForYouTubeRefreshNew2(int maxRows,
int commandTimeout = -1)
{
// We now do the oldest maxRows only
var cmdText =
"SELECT PoliticianKey,QuestionId,Sequence,Answer," +
"Source,DateStamp,UserName,YouTubeUrl,YouTubeDescription,YouTubeRunningTime,YouTubeSource," +
"YouTubeSourceUrl,YouTubeDate,YouTubeRefreshTime,YouTubeAutoDisable,FacebookVideoUrl," +
"FacebookVideoDescription,FacebookVideoRunningTime,FacebookVideoDate," +
"FacebookVideoRefreshTime,FacebookVideoAutoDisable" +
$" FROM Answers2 WHERE YouTubeUrl!='' AND NOT YouTubeUrl IS NULL LIMIT {maxRows}";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(Answers2.FillTable(cmd, Answers2Table.ColumnSet.All));
}
protected void ButtonAddCandidates_OnClick(object sender, EventArgs e)
{
switch (AddCandidatesReloading.Value)
{
case "reloading":
{
DeletePoliticianOverrideContainer.AddCssClasses("hidden");
DeletePoliticianOverride.Checked = false;
AddCandidatesReloading.Value = Empty;
_ManagePoliticiansPanel.LoadControls();
_ManagePoliticiansPanel.ClearAddNewCandidate(true);
break;
}
case "deleting":
{
AddCandidatesReloading.Value = Empty;
DeletePoliticianOverrideContainer.AddCssClasses("hidden");
var keyToDelete = AddCandidatesKeyToDelete.GetValue();
// check for meaningful usages
var referenceList = new List <string>();
FormatReferences(referenceList, "Answers", Answers2.CountByPoliticianKey(keyToDelete));
FormatReferences(referenceList, "ElectionsPoliticians",
ElectionsPoliticians.CountByPoliticianKey(keyToDelete));
FormatReferences(referenceList, "ElectionsPoliticians:RunningMateKey",
ElectionsPoliticians.CountByRunningMateKey(keyToDelete));
FormatReferences(referenceList, "OfficesOfficials",
OfficesOfficials.CountByPoliticianKey(keyToDelete));
FormatReferences(referenceList, "OfficesOfficials:RunningMateKey",
OfficesOfficials.CountByRunningMateKey(keyToDelete));
if (referenceList.Count > 0 && !DeletePoliticianOverride.Checked)
{
FeedbackAddCandidates.AddError(
"Cannot delete because the PoliticianKey is referenced in the following tables. Check the box to override.");
DeletePoliticianOverrideContainer.RemoveCssClass("hidden");
foreach (var @ref in referenceList)
{
FeedbackAddCandidates.AddError(@ref);
}
return;
}
// delete
DeletePoliticianOverride.Checked = false;
var name = PageCache.Politicians.GetPoliticianName(keyToDelete);
if (referenceList.Count > 0)
{
DeleteAllPoliticianReferences(keyToDelete);
}
Politicians.DeleteByPoliticianKey(keyToDelete);
FeedbackAddCandidates.AddInfo($"Politician {name} ({keyToDelete}) deleted.");
AddCandidatesKeyToDelete.Value = Empty;
//_DeleteDistrictsTabInfo.ClearValidationErrors();
//PopulateLocalDistrictDropdown();
//_DeleteDistrictsTabInfo.LoadControls();
break;
}
case "":
{
//// normal update
//_ManagePoliticiansPanel.Update();
//_ManagePoliticiansPanel.ClearAddNewCandidate();
break;
}
default:
throw new VoteException($"Unknown reloading option: '{AddCandidatesReloading.Value}'");
}
}
private void DoConsolidation()
{
try
{
var selectedIndex = ConsolidateSelectedIndex.Value;
string selectedKey;
string unselectedKey;
switch (selectedIndex)
{
case "1":
selectedKey = ConsolidateKey1.Value;
unselectedKey = ConsolidateKey2.Value;
break;
case "2":
selectedKey = ConsolidateKey2.Value;
unselectedKey = ConsolidateKey1.Value;
break;
default: throw new VoteException("Index not 1 or 2");
}
// Politicians
var selectedPolitician = Politicians.GetData(selectedKey);
var unselectedPolitician = Politicians.GetData(unselectedKey);
if (selectedPolitician.Count != 1)
{
throw new VoteException("Politician " + selectedPolitician + " not found");
}
if (unselectedPolitician.Count != 1)
{
throw new VoteException("Politician " + unselectedKey + " not found");
}
var selectedData = UpdatePoliticians(selectedIndex, selectedPolitician,
unselectedPolitician);
// PoliticiansImagesData and PoliticiansImagesBlobs
var selectedImagesData = PoliticiansImagesData.GetData(selectedKey);
var unselectedImagesData = PoliticiansImagesData.GetData(unselectedKey);
var selectedImagesBlobs = PoliticiansImagesBlobs.GetData(selectedKey);
var unselectedImagesBlobs = PoliticiansImagesBlobs.GetData(unselectedKey);
UpdateImages(selectedIndex, selectedData, selectedKey, selectedImagesData,
selectedImagesBlobs, unselectedImagesData, unselectedImagesBlobs);
// Answers
//AnswersTable selectedAnswers = null;
//AnswersTable unselectedAnswers = null;
var selectedAnswers2 = Answers.GetActiveDataByPoliticianKeyNew(selectedKey);
var unselectedAnswers2 = Answers2.GetDataByPoliticianKey(unselectedKey);
UpdateAnswers2(selectedKey, selectedAnswers2, unselectedAnswers2);
// ElectionsIncumbentsRemoved
var selectedIncumbentsRemoved =
ElectionsIncumbentsRemoved.GetDataByPoliticianKey(selectedKey);
var unselectedIncumbentsRemoved =
ElectionsIncumbentsRemoved.GetDataByPoliticianKey(unselectedKey);
UpdateIncumbentsRemoved(selectedKey, unselectedIncumbentsRemoved,
selectedIncumbentsRemoved);
// ElectionsPoliticians
var selectedElectionsPoliticians =
ElectionsPoliticians.GetDataByPoliticianKey(selectedKey);
var unselectedElectionsPoliticians =
ElectionsPoliticians.GetDataByPoliticianKey(unselectedKey);
UpdateElectionsPoliticians(selectedKey, unselectedElectionsPoliticians,
selectedElectionsPoliticians);
// OfficesOfficials
var selectedOfficesOfficials = OfficesOfficials.GetDataByPoliticianKey(selectedKey);
var unselectedOfficesOfficials =
OfficesOfficials.GetDataByPoliticianKey(unselectedKey);
UpdateOfficesOfficials(selectedKey, unselectedOfficesOfficials,
selectedOfficesOfficials);
// Update everything as one transaction, politicians last
PoliticiansImagesData.UpdateTable(selectedImagesData);
PoliticiansImagesData.UpdateTable(unselectedImagesData);
PoliticiansImagesBlobs.UpdateTable(selectedImagesBlobs);
PoliticiansImagesBlobs.UpdateTable(unselectedImagesBlobs);
Answers2.UpdateTable(selectedAnswers2);
Answers2.UpdateTable(unselectedAnswers2);
ElectionsIncumbentsRemoved.UpdateTable(unselectedIncumbentsRemoved);
ElectionsPoliticians.UpdateTable(unselectedElectionsPoliticians);
OfficesOfficials.UpdateTable(unselectedOfficesOfficials);
Politicians.UpdateTable(selectedPolitician);
Politicians.UpdateTable(unselectedPolitician);
// Log
LogDataChange.LogUpdate("*ConsolidatePoliticians", "*Various", unselectedKey,
selectedKey, VotePage.UserName, SecurePage.UserSecurityClass, DateTime.UtcNow,
selectedKey);
// After the main update, refresh the LiveOfficeKey, LiveOfficeStatus and LiveElectionKey
var view = PoliticiansLiveOfficeKeyView.GetData(selectedKey);
if (view.Count == 1)
{
var keyAndStatus =
PoliticianOfficeStatus.FromLiveOfficeKeyAndStatus(
view[0].LiveOfficeKeyAndStatus);
selectedPolitician[0].LiveOfficeKey = keyAndStatus.OfficeKey;
selectedPolitician[0].LiveOfficeStatus = keyAndStatus.PoliticianStatus.ToString();
selectedPolitician[0].LiveElectionKey = keyAndStatus.ElectionKey;
Politicians.UpdateTable(selectedPolitician);
}
ConsolidateReloaded.Value = "ok";
}
catch (Exception ex)
{
FeedbackConsolidate.AddError("There was an unexpected error: " + ex.Message);
}
}
public static void UpdatePoliticianAnswerNew(string politicianKey, string questionKey,
int sequence, string newValue, string source, DateTime dateStamp, string youTubeUrl,
string youTubeDescription, TimeSpan youTubeRunningTime, string youTubeSource,
string youTubeSourceUrl, DateTime youTubeDate, string facebookVideoUrl,
string facebookVideoDescription, TimeSpan facebookVideoRunningTime,
DateTime facebookVideoDate)
{
var questionId = int.Parse(questionKey);
if (IsNullOrWhiteSpace(newValue) && IsNullOrWhiteSpace(youTubeUrl) &&
IsNullOrWhiteSpace(facebookVideoUrl))
// Just delete and be done with it
{
Answers2.DeleteByPoliticianKeyQuestionIdSequence(politicianKey, questionId,
sequence);
return;
}
var table = Answers2.GetDataByPoliticianKeyQuestionIdSequence(politicianKey,
questionId, sequence);
Answers2Row row;
if (table.Count == 0)
{
row = table.NewRow();
row.Sequence = sequence;
}
else
{
row = table[0];
}
row.PoliticianKey = politicianKey;
row.QuestionId = questionId;
row.Answer = newValue;
row.Source = source;
row.DateStamp = dateStamp;
row.UserName = UserName;
row.YouTubeUrl = youTubeUrl;
row.YouTubeDescription = youTubeDescription;
row.YouTubeRunningTime = youTubeRunningTime;
row.YouTubeSource = youTubeSource;
row.YouTubeSourceUrl = youTubeSourceUrl;
row.YouTubeDate = youTubeDate;
row.YouTubeRefreshTime = IsNullOrWhiteSpace(youTubeUrl)
? DefaultDbDate
: DateTime.UtcNow;
row.YouTubeAutoDisable = null;
row.FacebookVideoUrl = facebookVideoUrl;
row.FacebookVideoDescription = facebookVideoDescription;
row.FacebookVideoRunningTime = facebookVideoRunningTime;
row.FacebookVideoDate = facebookVideoDate;
row.FacebookVideoRefreshTime = IsNullOrWhiteSpace(facebookVideoUrl)
? DefaultDbDate
: DateTime.UtcNow;
row.FacebookVideoAutoDisable = null;
if (table.Count == 0)
{
table.AddRow(row);
}
Answers2.UpdateTable(table);
}
private void RemapExistingAnswers()
{
var oldAnswers = Answers.GetAllData();
// Code all answers with new Question.
var codedAnswers = oldAnswers
.Where(r =>
_QuestionDictionary.ContainsKey(r.QuestionKey)).Select(r => new
{
QuestionKey = r.QuestionKey.ToUpperInvariant(),
QuestionId = _QuestionDictionary[r.QuestionKey],
PoliticianKey = r.PoliticianKey.ToUpperInvariant(),
r.Sequence,
r.Answer,
r.YouTubeUrl,
r.DateStamp
}).OrderByDescending(r => r.DateStamp).ToList();
// find merged answers with multiple references to the same video
var duplicateVideoAnswers = codedAnswers.Where(a => !IsNullOrWhiteSpace(a.YouTubeUrl))
.GroupBy(r => new
{
r.QuestionId,
r.PoliticianKey,
YouTubeId = r.YouTubeUrl.GetYouTubeVideoId()
}).Where(g => g.Count() > 1).ToList();
// the old answers to have their YouTubeUrls cleared on final output --
// only keep the most recent
var duplicateVideoDictionary = duplicateVideoAnswers
// queue all but the most recent for deletion
.SelectMany(g => g.OrderByDescending(g2 => g2.DateStamp).Skip(1)).ToDictionary(a =>
new
{
PoliticianKey = a.PoliticianKey.ToUpperInvariant(),
a.QuestionKey,
a.Sequence
});
foreach (var kvp in duplicateVideoDictionary)
{
_DuplicateVideoDictionary.Add(kvp.Key, null);
}
// find questions with exact duplicate long text answers
var groupedTextAnswers = codedAnswers
.Where(a =>
!IsNullOrWhiteSpace(a.Answer.Trim()) &&
a.Answer.Trim().Length >= AllOptions.LongAnswerMinimumLength).GroupBy(r =>
new
{
QuestionKey = r.QuestionKey.ToUpperInvariant(),
QuestionId = _QuestionDictionary[r.QuestionKey],
PoliticianKey = r.PoliticianKey.ToUpperInvariant(),
Answer = r.Answer.Trim()
}).ToList();
var duplicateTextAnswers = groupedTextAnswers.Where(g => g.Count() > 1).ToList();
// the old answers to have their Answers cleared on final output
var duplicateTextDictionary = duplicateTextAnswers
// queue all but the most recent for deletion
.SelectMany(g => g.OrderByDescending(g2 => g2.DateStamp).Skip(1)).ToDictionary(a =>
new
{
PoliticianKey = a.PoliticianKey.ToUpperInvariant(),
QuestionKey = a.QuestionKey.ToUpperInvariant(),
a.Sequence
});
foreach (var kvp in duplicateTextDictionary)
{
_DuplicateTextDictionary.Add(kvp.Key, null);
}
var oldFilteredAnswers = oldAnswers
.Where(a =>
{
if (!_QuestionDictionary.ContainsKey(a.QuestionKey) ||
a.QuestionKey.StartsWith("ALL", StringComparison.Ordinal))
{
return(false);
}
var youTubeUrl = _DuplicateVideoDictionary.ContainsKey(new
{
PoliticianKey = a.PoliticianKey.ToUpperInvariant(),
OldQuestionKey = a.QuestionKey.ToUpperInvariant(),
a.Sequence
})
? Empty
: a.YouTubeUrl;
var answer = a.Answer.Trim().Length >= AllOptions.LongAnswerMinimumLength &&
_DuplicateTextDictionary.ContainsKey(new
{
PoliticianKey = a.PoliticianKey.ToUpperInvariant(),
OldQuestionKey = a.QuestionKey.ToUpperInvariant(),
a.Sequence
})
? Empty
: a.Answer;
return(!IsNullOrWhiteSpace(youTubeUrl) || !IsNullOrWhiteSpace(answer));
}).ToArray();
AppendStatusText($"{oldAnswers.Count} existing answers read");
AppendStatusText($"{oldFilteredAnswers.Length} answers mapped to new topics");
var oldMappedAnswers = oldFilteredAnswers
.Select(a => new { TopicId = _QuestionDictionary[a.QuestionKey()], AnswerRow = a })
.GroupBy(a => new { a.TopicId, PoliticianKey = a.AnswerRow.PoliticianKey.ToUpperInvariant() }).ToList();
// delete old answers
DeleteNonPermanentAnswers();
// write new
var count = 0;
foreach (var g in oldMappedAnswers)
{
var sequence = 0;
foreach (var a in g.OrderBy(r => r.AnswerRow.DateStamp))
{
var row = a.AnswerRow;
var answer = PrependOldQuestionToAnswerIfNecessary(row.QuestionKey, row);
Answers2.Insert(row.PoliticianKey, g.Key.TopicId, sequence++, answer, row.Source,
row.DateStamp, row.UserName, row.YouTubeUrl, row.YouTubeDescription,
row.YouTubeRunningTime, row.YouTubeSource, row.YouTubeSourceUrl, row.YouTubeDate,
row.YouTubeRefreshTime, row.YouTubeAutoDisable, row.FacebookVideoUrl,
row.FacebookVideoDescription, row.FacebookVideoRunningTime, row.FacebookVideoDate,
row.FacebookVideoRefreshTime, row.FacebookVideoAutoDisable);
count++;
if (count % 1000 == 0)
{
AppendStatusText($"{count} Answers2 rows written");
}
}
}
AppendStatusText($"{count} Answers2 rows written");
}
public static void RefreshYouTubeAnswersNew()
{
string message;
//var expiration = new TimeSpan(3, 0, 0, 0); // 3 days
// fudge is to account for small differences in run time
//var fudge = new TimeSpan(1, 0, 0); // 1 hour
var now = DateTime.UtcNow;
try
{
VotePage.LogInfo("RefreshYouTubeAnswersNew", "Started");
var answersUpdated = 0;
//var table = Answers.GetDataForYouTubeRefreshNew(now - expiration + fudge);
var table = Answers.GetDataForYouTubeRefreshNew2(450);
foreach (var row in table)
{
row.YouTubeRefreshTime = now;
var youTubeId = row.YouTubeUrl.GetYouTubeVideoId();
if (IsNullOrWhiteSpace(youTubeId))
{
if (row.YouTubeAutoDisable != YouTubeVideoInfo.InvalidVideoUrlMessage)
{
row.YouTubeAutoDisable = YouTubeVideoInfo.InvalidVideoUrlMessage;
answersUpdated++;
}
continue;
}
var videoInfo = GetVideoInfo(youTubeId, false, 1);
if (!videoInfo.IsValid)
{
if (row.YouTubeAutoDisable != YouTubeVideoInfo.VideoIdNotFoundMessage)
{
row.YouTubeAutoDisable = YouTubeVideoInfo.VideoIdNotFoundMessage;
answersUpdated++;
}
continue;
}
if (!videoInfo.IsPublic)
{
if (row.YouTubeAutoDisable != YouTubeVideoInfo.VideoNotPublicMessage)
{
row.YouTubeAutoDisable = YouTubeVideoInfo.VideoNotPublicMessage;
answersUpdated++;
}
continue;
}
var description = videoInfo.Description.Trim();
if (IsNullOrWhiteSpace(description) ||
description.Length > VideoInfo.MaxVideoDescriptionLength)
{
description = videoInfo.Title.Trim();
}
if (description != row.YouTubeDescription ||
videoInfo.Duration != row.YouTubeRunningTime ||
row.YouTubeAutoDisable != null ||
row.YouTubeSource == YouTubeVideoInfo.VideoUploadedByCandidateMessage &&
videoInfo.PublishedAt != row.YouTubeDate)
{
row.YouTubeDescription = description;
row.YouTubeRunningTime = videoInfo.Duration;
row.YouTubeAutoDisable = null;
if (row.YouTubeSource == YouTubeVideoInfo.VideoUploadedByCandidateMessage)
{
row.YouTubeDate = videoInfo.PublishedAt;
}
answersUpdated++;
}
}
Answers2.UpdateTable(table);
message =
$"{table.Count} Expired YouTube answers found, {answersUpdated} YouTube Answers updated";
}
catch (Exception ex)
{
VotePage.LogException("RefreshYouTubeAnswersNew", ex);
message = $"Exception: {ex.Message} [see exception log for details]";
}
VotePage.LogInfo("RefreshYouTubeAnswersNew", message);
}
