public static DataTable GetAnswerQuestionsNew([NotNull] string politicianKey,
[NotNull] string officeKey, int issueId, int commandTimeout = -1)
{
var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey);
var cmdText =
"SELECT q.QuestionId,q.Question FROM QuestionsJurisdictions qj" +
" INNER JOIN Questions2 q ON q.QuestionId = qj.QuestionId AND q.IsQuestionOmit = 0" +
" INNER JOIN IssuesQuestions iq ON iq.QuestionId = q.QuestionId AND iq.IssueId=@IssueId" +
" INNER JOIN Issues2 i ON i.IssueId = iq.IssueId AND i.IsIssueOmit = 0" +
$" WHERE {Questions.QuestionsJurisdictionsWhereClause}" +
" ORDER BY iq.QuestionOrder,q.Question";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
VoteDb.AddCommandParameter(cmd, "Level", level);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
VoteDb.AddCommandParameter(cmd, "IssueId", issueId);
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static DataTable GetIssueTopicsByPoliticianKey(string politicianKey, string officeKey = null)
{
if (IsNullOrWhiteSpace(officeKey))
{
officeKey = VotePage.GetPageCache().Politicians.GetLiveOfficeKey(politicianKey);
}
var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey);
var cmdText = "SELECT i.IssueId,i.Issue,q.QuestionId,q.Question FROM Issues2 i" +
" INNER JOIN IssuesQuestions iq ON iq.IssueId = i.IssueId" +
" INNER JOIN Questions2 q ON q.QuestionId = iq.QuestionId" +
" INNER JOIN QuestionsJurisdictions qj ON qj.QuestionId = q.QuestionId" +
" WHERE i.IssueId > 1000 AND i.IsIssueOmit = 0 AND q.IsQuestionOmit = 0" +
$" AND {Questions.QuestionsJurisdictionsWhereClause}" +
" GROUP BY i.issueId,q.QuestionId" +
" ORDER BY IssueOrder,QuestionOrder";
var cmd = VoteDb.GetCommand(cmdText);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "Level", level);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static DataTable GetAnswersNew3([NotNull] string politicianKey,
[NotNull] string officeKey, [CanBeNull] int?questionId = null, int commandTimeout = -1)
{
// If question is assigned to multiple issues, it is returned multiple times
// unless a specific question is requested
var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey);
var cmdText =
"SELECT a.Answer,CONVERT(a.QuestionId,char) AS QuestionKey,a.Sequence,a.Source," +
" a.DateStamp,a.YouTubeUrl,a.YouTubeDescription,a.YouTubeSource,a.YouTubeRunningTime," +
" a.YouTubeSourceUrl,a.YouTubeDate,a.YouTubeRefreshTime,a.YouTubeAutoDisable," +
" a.YouTubeRefreshTime,a.YouTubeAutoDisable,a.FacebookVideoUrl," +
" a.FacebookVideoDescription,a.FacebookVideoRunningTime,a.FacebookVideoDate," +
" a.FacebookVideoRefreshTime,a.FacebookVideoAutoDisable,a.PoliticianKey," +
" Convert(i.IssueId, char) AS IssueKey,(i.IssueId>1000) AS IsIssue,i.Issue,qj.IssueLevel,igi.IssueOrder," +
" q.Question,iq.QuestionOrder,ig.IssueGroupId,ig.Heading,ig.SubHeading" +
" FROM QuestionsJurisdictions qj" +
" INNER JOIN Questions2 q ON q.QuestionId = qj.QuestionId AND q.IsQuestionOmit = 0" +
" INNER JOIN IssuesQuestions iq ON iq.QuestionId = q.QuestionId" +
" INNER JOIN Issues2 i ON i.IssueId = iq.IssueId AND i.IsIssueOmit = 0" +
" INNER JOIN IssueGroupsIssues2 igi ON igi.IssueId = i.IssueId" +
" INNER JOIN IssueGroups2 ig ON ig.IssueGroupId = igi.IssueGroupId AND ig.IsEnabled=1" +
" INNER JOIN Answers2 a ON a.PoliticianKey = @PoliticianKey AND a.QuestionId = qj.QuestionId" +
" 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 = ''))" +
" WHERE" +
$" {(questionId == null ? Empty : " qj.QuestionId=@QuestionId AND")}" +
$" {Questions.QuestionsJurisdictionsWhereClause}" +
$" {(questionId == null ? Empty : " GROUP BY a.PoliticianKey, a.QuestionId, a.Sequence")}" +
" ORDER BY IsIssue,ig.IssueGroupOrder,igi.IssueOrder,i.Issue,iq.QuestionOrder,q.Question,a.DateStamp DESC, a.Sequence DESC";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
VoteDb.AddCommandParameter(cmd, "Level", level);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
if (questionId != null)
{
VoteDb.AddCommandParameter(cmd, "QuestionId", questionId.Value);
}
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static DataTable GetAnswersNew([NotNull] string politicianKey,
[NotNull] string officeKey, [CanBeNull] int?questionId = null, bool forUpdate = false, int commandTimeout = -1)
{
var stagingTest = VotePage.ShowIssues || forUpdate ? Empty : " AND i.IssueId<1000 ";
// If question is assigned to multiple issues, it is returned multiple times
var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey);
var cmdText =
"SELECT a.Answer,q.QuestionId,CONVERT(q.QuestionId,char) AS QuestionKey,a.Sequence,a.Source," +
" a.DateStamp,a.YouTubeUrl," +
" a.YouTubeDescription,a.YouTubeSource,a.YouTubeRunningTime,a.YouTubeSourceUrl,a.YouTubeDate," +
" a.YouTubeRefreshTime,a.YouTubeAutoDisable,a.YouTubeRefreshTime,a.YouTubeAutoDisable," +
" a.FacebookVideoUrl,a.FacebookVideoDescription,a.FacebookVideoRunningTime," +
" a.FacebookVideoDate,a.FacebookVideoRefreshTime,a.FacebookVideoAutoDisable," +
" @PoliticianKey AS PoliticianKey,i.IssueId,(i.IssueId>1000) AS IsIssue,Convert(i.IssueId, char) AS IssueKey,i.Issue," +
" IF(qj.IssueLevel = 'A', 1, 0) AS IsTextSourceOptional,qj.IssueLevel,i.IssueOrder,q.Question,iq.QuestionOrder" +
" FROM QuestionsJurisdictions qj" +
" INNER JOIN Questions2 q ON q.QuestionId = qj.QuestionId AND q.IsQuestionOmit = 0" +
" INNER JOIN IssuesQuestions iq ON iq.QuestionId = q.QuestionId" +
$" INNER JOIN Issues2 i ON i.IssueId = iq.IssueId AND i.IsIssueOmit = 0 {stagingTest}" +
$" {(forUpdate ? "LEFT OUTER" : "INNER")} JOIN Answers2 a ON a.PoliticianKey = @PoliticianKey AND a.QuestionId = qj.QuestionId" +
" 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 = ''))" +
" WHERE" +
$" {(questionId == null ? Empty : "qj.QuestionId=@QuestionId AND")}" +
$" {Questions.QuestionsJurisdictionsWhereClause}" +
" ORDER BY IsIssue,i.IssueOrder,i.Issue,iq.QuestionOrder,q.Question,a.DateStamp DESC, a.Sequence DESC";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
VoteDb.AddCommandParameter(cmd, "Level", level);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
if (questionId != null)
{
VoteDb.AddCommandParameter(cmd, "QuestionId", questionId.Value);
}
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
//public static DataTable GetAllDataByIssueKeyPoliticianKey(string issueKey,
// string politicianKey)
//{
// const string cmdText =
// "SELECT ig.IssueGroupKey,ig.IssueGroupOrder,ig.Heading AS IssueGroupHeading," +
// "ig.SubHeading AS IssueGroupSubHeading,i.IssueKey,igi.IssueOrder,i.IssueLevel,i.Issue,i.IsTextSourceOptional," +
// "i.StateCode AS StateCode,q.QuestionKey,q.QuestionOrder,q.Question,a.PoliticianKey,a.Source," +
// "a.DateStamp,a.Answer,a.YouTubeUrl,a.YouTubeDescription,a.YouTubeRunningTime,a.YouTubeSourceUrl," +
// "a.YouTubeSource,a.YouTubeDate,a.Sequence,a.YouTubeRefreshTime,a.YouTubeAutoDisable," +
// "a.FacebookVideoUrl,a.FacebookVideoDescription,a.FacebookVideoRunningTime," +
// "a.FacebookVideoDate,a.FacebookVideoRefreshTime,a.FacebookVideoAutoDisable FROM Issues i" +
// " INNER JOIN IssueGroupsIssues igi ON igi.IssueKey = i.IssueKey" +
// " INNER JOIN IssueGroups ig ON ig.IssueGroupKey = igi.IssueGroupKey" +
// " INNER JOIN Questions q ON q.IssueKey = i.IssueKey" +
// " LEFT JOIN Answers a on a.QuestionKey = q.QuestionKey AND a.PoliticianKey=@PoliticianKey" +
// " WHERE i.IssueKey=@IssueKey" + " AND i.IsIssueOmit=0 AND q.IsQuestionOmit=0" +
// " ORDER BY IssueGroupOrder,IssueOrder,QuestionOrder,DateStamp DESC,Sequence DESC";
// var cmd = VoteDb.GetCommand(cmdText);
// var table = new DataTable();
// using (var cn = VoteDb.GetOpenConnection())
// {
// cmd.Connection = cn;
// VoteDb.AddCommandParameter(cmd, "IssueKey", issueKey);
// VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
// DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
// adapter.Fill(table);
// return table;
// }
//}
public static DataTable GetAllDataByIssueKeyPoliticianKeyNew(int issueId,
string politicianKey, string officeKey = null)
{
// This is keyed by specificIssueId
// and so will not double-fetch questions in more than one issue
if (IsNullOrWhiteSpace(officeKey))
{
officeKey = VotePage.GetPageCache().Politicians.GetLiveOfficeKey(politicianKey);
}
var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey);
var cmdText =
"SELECT CONVERT(ig.IssueGroupId, CHAR) AS IssueGroupKey, ig.IssueGroupOrder," +
"ig.Heading AS IssueGroupHeading,ig.SubHeading AS IssueGroupSubHeading," +
"CONVERT(i.IssueId, CHAR) AS IssueKey, igi.IssueOrder,@Level AS IssueLevel,i.Issue," +
"CONVERT(q.QuestionId, CHAR) AS QuestionKey, iq.QuestionOrder,q.Question," +
"a.PoliticianKey,a.Source,a.DateStamp,a.Answer,a.YouTubeUrl,a.YouTubeDescription," +
"a.YouTubeRunningTime,a.YouTubeSourceUrl,a.YouTubeSource,a.YouTubeDate," +
"a.Sequence,a.YouTubeRefreshTime,a.YouTubeAutoDisable,a.FacebookVideoUrl," +
"a.FacebookVideoDescription,a.FacebookVideoRunningTime,a.FacebookVideoDate," +
"a.FacebookVideoRefreshTime,a.FacebookVideoAutoDisable," +
"@IsTextSourceOptional AS IsTextSourceOptional FROM Issues2 i" +
" INNER JOIN IssueGroupsIssues2 igi ON igi.IssueId = i.IssueId" +
" INNER JOIN IssueGroups2 ig ON ig.IssueGroupId = igi.IssueGroupId" +
" INNER JOIN IssuesQuestions iq ON iq.IssueId = i.IssueId" +
" INNER JOIN Questions2 q ON q.QuestionId = iq.QuestionId" +
" INNER JOIN QuestionsJurisdictions qj ON qj.QuestionId = q.QuestionId" +
" LEFT JOIN Answers2 a on a.QuestionId = q.QuestionId" +
" AND a.PoliticianKey = @PoliticianKey" +
" WHERE i.IssueId = @IssueId AND i.IsIssueOmit = 0 AND q.IsQuestionOmit = 0" +
$" AND {Questions.QuestionsJurisdictionsWhereClause}" +
" ORDER BY IssueGroupOrder,IssueOrder,QuestionOrder,DateStamp DESC, Sequence DESC";
var cmd = VoteDb.GetCommand(cmdText);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "IssueId", issueId);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
VoteDb.AddCommandParameter(cmd, "Level", level);
VoteDb.AddCommandParameter(cmd, "IsTextSourceOptional", issueId == Issues.IssueId.Biographical.ToInt());
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
//public static DataTable GetAllDataByIssueLevelStateCodePoliticianKey(string issueLevel,
// string stateCode, string politicianKey, int commandTimeout = -1)
//{
// const string cmdText =
// "SELECT ig.IssueGroupKey,ig.IssueGroupOrder,ig.Heading AS IssueGroupHeading," +
// "ig.SubHeading AS IssueGroupSubHeading,i.IssueKey,igi.IssueOrder,i.IssueLevel,i.Issue,i.IsTextSourceOptional," +
// "i.StateCode AS StateCode,q.QuestionKey,q.QuestionOrder,q.Question,a.PoliticianKey,a.Source," +
// "a.DateStamp,a.Answer,a.YouTubeUrl,a.YouTubeDescription,a.YouTubeSource,a.YouTubeRunningTime," +
// "a.YouTubeSourceUrl,a.YouTubeDate,a.Sequence,a.YouTubeRefreshTime,a.YouTubeAutoDisable," +
// "a.FacebookVideoUrl,a.FacebookVideoDescription,a.FacebookVideoRunningTime," +
// "a.FacebookVideoDate,a.FacebookVideoRefreshTime,a.FacebookVideoAutoDisable FROM Issues i" +
// " INNER JOIN IssueGroupsIssues ON IssueGroupsIssues.IssueKey = i.IssueKey" +
// " INNER JOIN IssueGroups ig ON ig.IssueGroupKey = IssueGroupsIssues.IssueGroupKey" +
// " INNER JOIN Questions q ON q.IssueKey = i.IssueKey" +
// " LEFT JOIN Answers a on a.QuestionKey = q.QuestionKey AND a.PoliticianKey=@PoliticianKey" +
// " WHERE i.StateCode=@StateCode AND i.IssueLevel=@IssueLevel" +
// " AND i.IsIssueOmit=0 AND q.IsQuestionOmit=0" +
// " ORDER BY IssueGroupOrder,IssueOrder,QuestionOrder,DateStamp DESC,Sequence DESC";
// var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
// var table = new DataTable();
// using (var cn = VoteDb.GetOpenConnection())
// {
// cmd.Connection = cn;
// VoteDb.AddCommandParameter(cmd, "IssueLevel", issueLevel);
// VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
// VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
// DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
// adapter.Fill(table);
// return table;
// }
//}
public static DataTable GetAllDataByPoliticianKeyNew(string politicianKey, string officeKey)
{
// Group to eliminate duplicates caused by questions in multiple issues
if (IsNullOrWhiteSpace(officeKey))
{
officeKey = VotePage.GetPageCache().Politicians.GetLiveOfficeKey(politicianKey);
}
var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey);
var cmdText = "SELECT CONVERT(ig.IssueGroupId, CHAR) AS IssueGroupKey," +
"ig.IssueGroupOrder,ig.Heading AS IssueGroupHeading," +
"ig.SubHeading AS IssueGroupSubHeading,CONVERT(i.IssueId, CHAR) AS IssueKey,i.IssueId," +
"igi.IssueOrder,qj.IssueLevel,i.Issue," +
"IF(qj.IssueLevel = 'A', 1, 0) AS IsTextSourceOptional,qj.StateCode AS StateCode," +
"CONVERT(q.QuestionId, CHAR) AS QuestionKey,q.QuestionId,iq.QuestionOrder,q.Question," +
"@PoliticianKey AS PoliticianKey,a.Source,a.DateStamp,a.Answer,a.YouTubeUrl,a.YouTubeDescription," +
"a.YouTubeSource,a.YouTubeRunningTime,a.YouTubeSourceUrl,a.YouTubeDate,a.Sequence," +
"a.YouTubeRefreshTime,a.YouTubeAutoDisable,a.FacebookVideoUrl," +
"a.FacebookVideoDescription,a.FacebookVideoRunningTime,a.FacebookVideoDate," +
"a.FacebookVideoRefreshTime,a.FacebookVideoAutoDisable FROM Issues2 i" +
" INNER JOIN IssueGroupsIssues2 igi ON igi.IssueId = i.IssueId" +
" INNER JOIN IssueGroups2 ig ON ig.IssueGroupId = igi.IssueGroupId" +
" INNER JOIN IssuesQuestions iq ON iq.IssueId = i.IssueId" +
" INNER JOIN Questions2 q ON q.QuestionId = iq.QuestionId" +
" INNER JOIN QuestionsJurisdictions qj ON qj.QuestionId = q.QuestionId" +
" LEFT JOIN Answers2 a on a.QuestionId = q.QuestionId" +
" AND a.PoliticianKey = @PoliticianKey" +
" WHERE i.IsIssueOmit = 0 AND q.IsQuestionOmit = 0" +
$" AND {Questions.QuestionsJurisdictionsWhereClause}" +
" GROUP BY q.QuestionId,a.Sequence" +
" ORDER BY IssueGroupOrder,IssueOrder,QuestionOrder,DateStamp DESC, Sequence DESC";
var cmd = VoteDb.GetCommand(cmdText);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "Level", level);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}