//public static QuestionsData GetQuestionsData()
//{
// var result = new QuestionsData();
// // get the Issues
// const string issuesCmdText =
// "SELECT i.IssueId,i.Issue,iq.QuestionId FROM Issues2 i" +
// " LEFT OUTER JOIN IssuesQuestions iq ON iq.IssueId=i.IssueId" +
// " LEFT OUTER JOIN Questions2 q ON q.QuestionId=iq.QuestionId" +
// " ORDER BY i.Issue,i.IssueId,iq.QuestionOrder";
// var issuesCmd = VoteDb.GetCommand(issuesCmdText);
// using (var cn = VoteDb.GetOpenConnection())
// {
// issuesCmd.Connection = cn;
// var table = new DataTable();
// DbDataAdapter adapter = new MySqlDataAdapter(issuesCmd as MySqlCommand);
// adapter.Fill(table);
// // result.Issues = table.Rows.OfType<DataRow>().GroupBy(r => r.IssueId()).Select(g =>
// // new IssuesDataIssue
// // {
// // IssueId = g.First().IssueId(),
// // IssueGroupId = g.First().IssueGroupId(),
// // IsEnabled = !g.First().IsIssueOmit(),
// // Issue = g.First().Issue(),
// // QuestionIds = g.Where(q => q.QuestionIdOrNull() != null)
// // .Select(q => q.QuestionId()).ToArray()
// // }).ToArray();
// }
// // // get the IssueGroups
// // const string issueGroupsCmdText = "SELECT IssueGroupId,Heading FROM IssueGroups2";
// // var issueGroupsCmd = VoteDb.GetCommand(issueGroupsCmdText);
// // using (var cn = VoteDb.GetOpenConnection())
// // {
// // issueGroupsCmd.Connection = cn;
// // var table = new DataTable();
// // DbDataAdapter adapter = new MySqlDataAdapter(issueGroupsCmd as MySqlCommand);
// // adapter.Fill(table);
// // result.IssueGroups = table.Rows.OfType<DataRow>().Select(r =>
// // new IssuesDataIssueGroup
// // {
// // IssueGroupId = r.IssueGroupId(),
// // Heading = r.Heading()
// // }).ToArray();
// // }
// // // get the questions
// // const string questionsCmdText = "SELECT QuestionId,Question,IsQuestionOmit FROM Questions2" +
// // " ORDER BY Question";
// // var questionsCmd = VoteDb.GetCommand(questionsCmdText);
// // using (var cn = VoteDb.GetOpenConnection())
// // {
// // questionsCmd.Connection = cn;
// // var table = new DataTable();
// // DbDataAdapter adapter = new MySqlDataAdapter(questionsCmd as MySqlCommand);
// // adapter.Fill(table);
// // result.Questions = table.Rows.OfType<DataRow>().Select(r =>
// // new IssuesDataQuestion
// // {
// // QuestionId = r.QuestionId(),
// // IsEnabled = !r.IsQuestionOmit(),
// // Question = r.Question()
// // }).ToArray();
// // }
// return result;
//}
public static void SaveTopicsData(QuestionsData data)
{
var questionsTable = GetAllData();
var questionsJurisdictionsTable = QuestionsJurisdictions.GetAllData();
var issuesQuestionsTable = IssuesQuestions.GetAllData();
// collect all QuestionsJurisdictions data
var questionsJurisdictionsData = data.Questions.SelectMany(q => q.Jurisdictions, (q, j) => new
{
q.QuestionId,
j.IssueLevel,
j.StateCode,
j.CountyOrLocal
}).ToArray();
// this dictionary is used for adding IssuesQuestions entries for new questions
var iqMaxOrderByIssue = issuesQuestionsTable.GroupBy(iq => iq.IssueId)
.Select(g => new { IssueId = g.Key, MaxOrder = g.Max(iq => iq.QuestionOrder) })
.ToDictionary(i => i.IssueId, i => i.MaxOrder);
// delete any missing Questions rows
foreach (var qRow in questionsTable)
{
if (data.Questions.All(i => qRow.QuestionId != i.QuestionId))
{
qRow.Delete();
}
}
// delete any missing QuestionsJurisdictions rows
foreach (var qjRow in questionsJurisdictionsTable)
{
var val = new
{
qjRow.QuestionId,
qjRow.IssueLevel,
qjRow.StateCode,
qjRow.CountyOrLocal
};
if (questionsJurisdictionsData.All(i => i.QuestionId != val.QuestionId || i.IssueLevel != val.IssueLevel || i.StateCode != val.StateCode || i.CountyOrLocal != val.CountyOrLocal))
{
qjRow.Delete();
}
}
// delete any missing IssuesQuestions rows
foreach (var iqRow in issuesQuestionsTable)
{
var question =
data.Questions.FirstOrDefault(i => i.QuestionId == iqRow.QuestionId);
if (question == null)
{
iqRow.Delete();
}
}
// update or add remaining Questions rows -- if new add an IssuesQuestions row too
//var qOrder = 0;
foreach (var q in data.Questions)
{
var qRow = questionsTable.Where(r => r.RowState != DataRowState.Deleted)
.FirstOrDefault(r => r.QuestionId == q.QuestionId);
if (qRow == null)
{
// new question -- insert directly because of auto increment column
Insert(q.QuestionId, q.Question, !q.IsEnabled);
// find the Issue with this QuestionId and add a row to IssuesQuestions
foreach (var i in data.Issues)
{
if (i.QuestionIds.Contains(q.QuestionId))
{
var maxOrder = iqMaxOrderByIssue[i.IssueId] + 10;
iqMaxOrderByIssue[i.IssueId] = maxOrder;
issuesQuestionsTable.AddRow(i.IssueId, q.QuestionId, maxOrder);
}
}
}
else
{
if (qRow.Question != q.Question)
{
qRow.Question = q.Question;
}
if (qRow.IsQuestionOmit != !q.IsEnabled)
{
qRow.IsQuestionOmit = !q.IsEnabled;
}
}
}
// add new QuestionsJurisdictions rows
foreach (var qj in questionsJurisdictionsData)
{
if (questionsJurisdictionsTable.Where(r => r.RowState != DataRowState.Deleted).All(
r => qj.QuestionId != r.QuestionId || qj.IssueLevel != r.IssueLevel || qj.StateCode != r.StateCode || qj.CountyOrLocal != r.CountyOrLocal))
{
questionsJurisdictionsTable.AddRow(qj.QuestionId, qj.IssueLevel, qj.StateCode,
qj.CountyOrLocal);
}
}
UpdateTable(questionsTable);
QuestionsJurisdictions.UpdateTable(questionsJurisdictionsTable);
IssuesQuestions.UpdateTable(issuesQuestionsTable);
}
public static void SaveIssuesData(IssuesDataIssue[] data)
{
var issuesTable = GetAllData();
var issueGroupsIssuesTable = IssueGroupsIssues2.GetAllData();
var issuesQuestionsTable = IssuesQuestions.GetAllData();
// this dictionary is used for adding IssueGroupsIssues entries for new issues
var igiMaxOrderByIssueGroup = issueGroupsIssuesTable.GroupBy(igi => igi.IssueGroupId)
.Select(g => new { IssueGroupId = g.Key, MaxOrder = g.Max(igi => igi.IssueOrder) })
.ToDictionary(ig => ig.IssueGroupId, ig => ig.MaxOrder);
// delete any missing Issues rows
foreach (var iRow in issuesTable)
{
if (data.All(i => iRow.IssueId != i.IssueId))
{
iRow.Delete();
}
}
// delete any missing IssueGroupsIssues rows
foreach (var igiRow in issueGroupsIssuesTable)
{
var issue =
data.FirstOrDefault(i => i.IssueId == igiRow.IssueId);
if (issue == null)
{
igiRow.Delete();
}
}
// delete any missing IssuesQuestions rows
foreach (var iqRow in issuesQuestionsTable)
{
var issue =
data.FirstOrDefault(i => i.IssueId == iqRow.IssueId);
if (issue == null || issue.QuestionIds.All(iq => iq != iqRow.QuestionId))
{
iqRow.Delete();
}
}
// update or add remaining entries
var iOrder = 0;
foreach (var i in data)
{
iOrder += 10;
var iRow = issuesTable.Where(r => r.RowState != DataRowState.Deleted)
.FirstOrDefault(r => r.IssueId == i.IssueId);
// Issues
if (iRow == null)
{
// insert directly because of auto increment column
Insert(i.IssueId, iOrder, i.Issue, !i.IsEnabled);
// new issues are added to IssueGroupsIssues at the end of the sequence by issue group
var maxOrder = igiMaxOrderByIssueGroup[i.IssueGroupId] + 10;
igiMaxOrderByIssueGroup[i.IssueGroupId] = maxOrder;
var igiRow = issueGroupsIssuesTable.NewRow(i.IssueGroupId, i.IssueId, maxOrder);
issueGroupsIssuesTable.AddRow(igiRow);
}
else
{
if (iRow.IssueOrder != iOrder)
{
iRow.IssueOrder = iOrder;
}
if (iRow.Issue != i.Issue)
{
iRow.Issue = i.Issue;
}
if (iRow.IsIssueOmit != !i.IsEnabled)
{
iRow.IsIssueOmit = !i.IsEnabled;
}
}
var iqOrder = 0;
foreach (var iq in i.QuestionIds)
{
iqOrder += 10;
var iqRow = issuesQuestionsTable.Where(r => r.RowState != DataRowState.Deleted)
.FirstOrDefault(r => r.IssueId == i.IssueId && r.QuestionId == iq);
if (iqRow == null)
{
iqRow = issuesQuestionsTable.NewRow(i.IssueId, iq, iqOrder);
issuesQuestionsTable.AddRow(iqRow);
}
else
if (iqRow.QuestionOrder != iqOrder)
{
iqRow.QuestionOrder = iqOrder;
}
}
}
UpdateTable(issuesTable);
IssueGroupsIssues2.UpdateTable(issueGroupsIssuesTable);
IssuesQuestions.UpdateTable(issuesQuestionsTable);
}
