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 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"); }