private void UpdateDatabase()
{
// For now we just use a single Issue Group "Issues"
// Create it, initially disabled
DeleteNonPermanentIssueGroups();
IssueGroups2.Insert(issuesIssueGroupId, 10, false, "Issues", Empty);
AppendStatusText("Global Issue Group created");
// Add the Issues
DeleteNonPermanentIssues();
foreach (var kvp in _IssueDictionary)
{
Issues2.Insert(kvp.Value.IssueId, kvp.Value.IssueId, kvp.Value.Issue, false);
}
AppendStatusText($"{_IssueDictionary.Count} Issues created");
// Add the IssueGroupsIssues
DeleteNonPermanentIssueGroupsIssues();
foreach (var kvp in _IssueDictionary)
{
IssueGroupsIssues2.Insert(issuesIssueGroupId, kvp.Value.IssueId, kvp.Value.IssueId);
}
AppendStatusText($"{_IssueDictionary.Count} IssueGroupsIssues created");
// Add the Questions
DeleteNonPermanentQuestions();
foreach (var kvp in _TopicDictionary)
{
Questions2.Insert(kvp.Value.TopicId, kvp.Value.Topic, false);
}
AppendStatusText($"{_TopicDictionary.Count} Questions created");
// Add the QuestionJurisdictions
// All are federal for now
DeleteNonPermanentQuestionsJurisdictions();
foreach (var kvp in _TopicDictionary)
{
QuestionsJurisdictions.Insert(kvp.Value.TopicId, "B", Empty, Empty);
}
AppendStatusText($"{_TopicDictionary.Count} QuestionsJurisdictions created");
// Add the IssuesQuestions
DeleteNonPermanentIssuesQuestions();
var iqCount = 0;
foreach (var kvp in _IssueDictionary)
{
var value = kvp.Value;
foreach (var topicId in value.TopicIds)
{
IssuesQuestions.Insert(value.IssueId, topicId, topicId);
iqCount++;
}
}
AppendStatusText($"{iqCount} IssuesQuestions created");
// remap existing answers
RemapExistingAnswers();
}
private static void DeleteAllDistictReferences(string stateCode, string localKey)
{
Elections.DeleteByStateCodeLocalKey(stateCode, localKey);
ElectionsIncumbentsRemoved.DeleteByStateCodeLocalKey(stateCode, localKey);
ElectionsOffices.DeleteByStateCodeLocalKey(stateCode, localKey);
ElectionsPoliticians.DeleteByStateCodeLocalKey(stateCode, localKey);
QuestionsJurisdictions.DeleteByIssueLevelStateCodeCountyOrLocal(Issues.IssueLevelLocal,
stateCode, localKey);
Offices.DeleteByStateCodeLocalKey(stateCode, localKey);
//OfficesAllIdentified.DeleteByStateCodeLocalKey(stateCode, localKey);
OfficesOfficials.DeleteByStateCodeLocalKey(stateCode, localKey);
Referendums.DeleteByStateCodeLocalKey(stateCode, localKey);
}
//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);
}
protected void ButtonDeleteDistricts_OnClick(object sender, EventArgs e)
{
switch (DeleteDistrictsReloading.Value)
{
case "reloading":
{
DeleteDistrictOverride.AddCssClasses("hidden");
ControlDeleteDistrictOverride.Checked = false;
_DeleteDistrictsTabInfo.ClearValidationErrors();
DeleteDistrictsReloading.Value = Empty;
PopulateLocalDistrictDropdown();
_DeleteDistrictsTabInfo.LoadControls();
FeedbackDeleteDistricts.AddInfo("Delete Districts information loaded.");
}
break;
case "":
{
// normal update
DeleteDistrictOverride.AddCssClasses("hidden");
_DeleteDistrictsTabInfo.ClearValidationErrors();
var localKey = ControlDeleteDistrictsLocalKey.GetValue();
var localDistrict = LocalDistricts.GetLocalDistrictByStateCodeLocalKey(StateCode,
localKey);
var referenceList = new List <string>();
if (!ControlDeleteDistrictOverride.Checked)
{
// check for references in other counties
var otherCounties = new List <string>();
foreach (var oc in
LocalIdsCodes.GetOtherCountyReferences(StateCode, CountyCode, localKey)
.Rows.OfType <DataRow>())
{
otherCounties.Add(oc.County());
}
// check for meaningful usages
FormatReferences(referenceList, "Elections",
Elections.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "ElectionsOffices",
ElectionsOffices.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "ElectionsPoliticians",
ElectionsPoliticians.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "QuestionsJurisdictions",
QuestionsJurisdictions.CountByIssueLevelStateCodeCountyOrLocal(
Issues.IssueLevelLocal, StateCode, localKey));
FormatReferences(referenceList, "Offices",
Offices.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "OfficesOfficials",
OfficesOfficials.CountByStateCodeLocalKey(StateCode, localKey));
FormatReferences(referenceList, "Referendums",
Referendums.CountByStateCodeLocalKey(StateCode, localKey));
if (referenceList.Count > 0 || otherCounties.Count > 0)
{
DeleteDistrictOverride.RemoveCssClass("hidden");
if (otherCounties.Count > 0)
{
FeedbackDeleteDistricts.PostValidationError(ControlDeleteDistrictsLocalKey,
"The district is referenced in the following other counties. If you delete this" +
" district it will be removed from the other counties also. Check the box to" +
" override.");
foreach (var oc in otherCounties)
{
FeedbackDeleteDistricts.AddError(oc);
}
}
if (referenceList.Count > 0)
{
FeedbackDeleteDistricts.PostValidationError(ControlDeleteDistrictsLocalKey,
"Cannot delete because the LocalKey is referenced in the following tables. Check the box to override.");
foreach (var @ref in referenceList)
{
FeedbackDeleteDistricts.AddError(@ref);
}
}
return;
}
}
// delete
ControlDeleteDistrictOverride.Checked = false;
DeleteAllDistictReferences(StateCode, localKey);
LocalDistricts.DeleteByStateCodeLocalKey(StateCode, localKey);
// delete TigerPlacesCounties rows if type Vote -- these are not intrinsically tied
// to county(s)
var localIdsCodes = LocalIdsCodes.GetDataByStateCodeLocalKey(StateCode, localKey);
Debug.Assert(localIdsCodes.Count == 1);
if (localIdsCodes[0].LocalType == LocalIdsCodes.LocalTypeVote)
{
TigerPlacesCounties.DeleteByStateCodeTigerTypeTigerCode(StateCode,
LocalIdsCodes.LocalTypeVote, localIdsCodes[0].LocalId);
}
LocalIdsCodes.DeleteByStateCodeLocalKey(StateCode, localKey);
_DeleteDistrictsTabInfo.ClearValidationErrors();
PopulateLocalDistrictDropdown();
_DeleteDistrictsTabInfo.LoadControls();
NavigateJurisdictionUpdatePanel.Update();
NavigateJurisdiction.Initialize();
FeedbackDeleteDistricts.AddInfo(
$"Local District {localDistrict} ({localKey}) deleted.");
}
break;
default:
throw new VoteException(
$"Unknown reloading option: '{DeleteDistrictsReloading.Value}'");
}
}
