private IndicatorUpdate ParseAndValidate(DataRow dr, ref string error, List<KeyValuePair<string, int>> formTypes)
{
IndicatorUpdate indicator = new IndicatorUpdate();
int indicatorId = 0;
if (!string.IsNullOrEmpty(dr["Indicator Id"].ToString()))
if (!int.TryParse(dr["Indicator ID"].ToString(), out indicatorId))
error += "Indicator ID: " + TranslationLookup.GetValue("MustBeNumber") + Environment.NewLine;
else
indicator.Id = indicatorId;
if (string.IsNullOrEmpty(dr["Type ID"].ToString()))
error += "Type ID: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.EntityType = (IndicatorEntityType)Convert.ToInt32(dr["Type ID"]);
if (string.IsNullOrEmpty(dr["Sort Order"].ToString()))
error += "Sort Order: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.SortOrder = Convert.ToInt32(dr["Sort Order"]);
// Enum Drop downs
if (string.IsNullOrEmpty(dr["Aggregation Rule"].ToString()))
error += "Aggregation Rule: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.AggTypeId = (int)Enum.Parse(typeof(IndicatorAggType), dr["Aggregation Rule"].ToString());
if (string.IsNullOrEmpty(dr["Merge Rule"].ToString()))
error += "Merge Rule: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.MergeRuleId = (int)Enum.Parse(typeof(MergingRule), dr["Merge Rule"].ToString());
if (string.IsNullOrEmpty(dr["Split Rule"].ToString()))
error += "Split Rule: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.SplitRuleId = (int)Enum.Parse(typeof(RedistrictingRule), dr["Split Rule"].ToString());
if (string.IsNullOrEmpty(dr["Indicator Type"].ToString()))
error += "Indicator Type: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.DataTypeId = (int)Enum.Parse(typeof(IndicatorDataType), dr["Indicator Type"].ToString());
// Is calculated
if (indicator.DataTypeId != null && indicator.DataTypeId == (int)IndicatorDataType.Calculated)
{
indicator.IsCalculated = true;
}
else
{
indicator.IsCalculated = false;
}
// TRANSLATIONS
if (string.IsNullOrEmpty(dr["Indicator Name English"].ToString()))
error += "Indicator Name English: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.English = dr["Indicator Name English"].ToString();
if (string.IsNullOrEmpty(dr["Indicator Name French"].ToString()))
error += "Indicator Name French: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.French = dr["Indicator Name French"].ToString();
if (string.IsNullOrEmpty(dr["Indicator Name Portuguese"].ToString()))
error += "Indicator Name Portuguese: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.Portuguese = dr["Indicator Name Portuguese"].ToString();
if (string.IsNullOrEmpty(dr["Indicator Name Bahasa"].ToString()))
error += "Indicator Name Bahasa: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
indicator.Bahasa = dr["Indicator Name Bahasa"].ToString();
// Is Required
if (string.IsNullOrEmpty(dr["Is Required"].ToString()))
error += "Is Required: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
else
{
bool isRequired = true;
string val = dr["Is Required"].ToString();
if (dr["Is Required"].ToString().ToLower() == "true")
isRequired = true;
else if (dr["Is Required"].ToString().ToLower() == "false")
isRequired = false;
else
error += "Is Required: " + "Must be TRUE or FALSE " + Environment.NewLine;
indicator.IsRequired = isRequired;
}
// Form ID
if (indicator.Id > 0 && indicator.EntityType == IndicatorEntityType.Intervention)
{
indicator.FormId = 0; // not necessary for updates and interventions
}
else if (string.IsNullOrEmpty(dr["Form Name"].ToString()))
{
error += "Form Name: " + TranslationLookup.GetValue("IsRequired") + Environment.NewLine;
}
else
{
string val = dr["Form Name"].ToString();
KeyValuePair<string, int>? form = formTypes.FirstOrDefault(f => f.Key == val);
if (form.HasValue)
indicator.FormId = form.Value.Value;
else
error += "Form Name: " + "Must be a value from the drop down list " + Environment.NewLine;
}
return indicator;
}
private string GetTableName(IndicatorUpdate indicator)
{
switch (indicator.EntityType)
{
case IndicatorEntityType.DiseaseDistribution:
return "DiseaseDistributionIndicators";
case IndicatorEntityType.Intervention:
return "InterventionIndicators";
case IndicatorEntityType.Survey:
return "SurveyIndicators";
case IndicatorEntityType.Process:
return "ProcessIndicators";
default:
return "ErrorTableName";
}
}
private void DoUpdate(IndicatorUpdate ind, List<string> sqlStatements, OleDbConnection connection, string tableName)
{
string translationKey = GetTranslationKey(ind, tableName, connection);
ind.Key = translationKey;
string updateSql = "";
switch (ind.EntityType)
{
case IndicatorEntityType.DiseaseDistribution:
updateSql = string.Format("UPDATE DiseaseDistributionIndicators set AggTypeId={1}, RedistrictRuleId={2}, MergeRuleId={3}, IsRequired={4}, SortOrder={5} where displayname = '{6}' and DiseaseId={0};",
ind.FormId, ind.AggTypeId, ind.SplitRuleId, ind.MergeRuleId, ind.IsRequired ? -1 : 0, ind.SortOrder, translationKey);
break;
case IndicatorEntityType.Intervention:
updateSql = string.Format("UPDATE InterventionIndicators set AggTypeId={0}, RedistrictRuleId={1}, MergeRuleId={2}, IsRequired={3}, SortOrder={4} where displayname = '{5}';",
ind.AggTypeId, ind.SplitRuleId, ind.MergeRuleId, ind.IsRequired ? -1 : 0, ind.SortOrder, translationKey);
break;
case IndicatorEntityType.Survey:
updateSql = string.Format("UPDATE SurveyIndicators set AggTypeId={1}, RedistrictRuleId={2}, MergeRuleId={3}, IsRequired={4}, SortOrder={5} where displayname = '{6}' and SurveyTypeId={0};",
ind.FormId, ind.AggTypeId, ind.SplitRuleId, ind.MergeRuleId, ind.IsRequired ? -1 : 0, ind.SortOrder, translationKey);
break;
case IndicatorEntityType.Process:
updateSql = string.Format("UPDATE ProcessIndicators set AggTypeId={1}, RedistrictRuleId={2}, MergeRuleId={3}, IsRequired={4}, SortOrder={5} where displayname = '{6}' and ProcessTypeId={0};",
ind.FormId, ind.AggTypeId, ind.SplitRuleId, ind.MergeRuleId, ind.IsRequired ? -1 : 0, ind.SortOrder, translationKey);
break;
}
if (string.IsNullOrEmpty(updateSql))
throw new Exception("Invalid indicator type for ID# " + ind.Id);
OleDbCommand updateCmd = new OleDbCommand(updateSql, connection);
updateCmd.ExecuteNonQuery();
sqlStatements.Add(updateSql);
}
private string GetTranslationKey(IndicatorUpdate indicator, string tableName, OleDbConnection connection)
{
string translationKey = "";
OleDbCommand command = new OleDbCommand("Select DisplayName FROM " + tableName + " WHERE ID=" + indicator.Id + ";", connection);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
translationKey = reader.GetValueOrDefault<string>("DisplayName");
}
reader.Close();
}
if (string.IsNullOrEmpty(translationKey))
throw new Exception("Error could not find indicator ID " + indicator.Id);
return translationKey;
}
private void DoInsert(IndicatorUpdate ind, List<string> sqlStatements, OleDbConnection connection, string tableName)
{
string translationKey = Guid.NewGuid().ToString();
ind.Key = translationKey;
string insertSql = "";
switch (ind.EntityType)
{
case IndicatorEntityType.DiseaseDistribution:
insertSql = string.Format("insert into DiseaseDistributionIndicators (DataTypeId, DisplayName, AggTypeId, SortOrder, UpdatedById, UpdatedAt, IsDisabled, IsEditable, IsRequired, IsDisplayed, IsCalculated, CanAddValues, IsMetaData, DiseaseId, RedistrictRuleId, MergeRuleId) values ({0}, '{1}', {2}, {3}, 26, NOW(), 0, 0, {4}, 0, {5}, 0, 0, {6}, {7}, {8});",
ind.DataTypeId, ind.Key, ind.AggTypeId, ind.SortOrder, ind.IsRequired ? -1 : 0, ind.IsCalculated ? -1 : 0, ind.FormId, ind.SplitRuleId, ind.MergeRuleId);
break;
case IndicatorEntityType.Intervention:
insertSql = string.Format("insert into InterventionIndicators (DataTypeId, DisplayName, AggTypeId, SortOrder, UpdatedById, UpdatedAt, IsDisabled, IsEditable, IsRequired, IsDisplayed, IsCalculated, CanAddValues, IsMetaData, RedistrictRuleId, MergeRuleId, InterventionTypeId) values ({0}, '{1}', {2}, {3}, 26, NOW(), 0, 0, {4}, 0, {5}, 0, 0, {6}, {7}, {8});",
ind.DataTypeId, ind.Key, ind.AggTypeId, ind.SortOrder, ind.IsRequired ? -1 : 0, ind.IsCalculated ? -1 : 0, ind.SplitRuleId, ind.MergeRuleId, ind.FormId);
break;
case IndicatorEntityType.Survey:
insertSql = string.Format("insert into SurveyIndicators (DataTypeId, DisplayName, AggTypeId, SortOrder, UpdatedById, UpdatedAt, IsDisabled, IsEditable, IsRequired, IsDisplayed, IsCalculated, CanAddValues, SurveyTypeId, RedistrictRuleId, MergeRuleId) values ({0}, '{1}', {2}, {3}, 26, NOW(), 0, 0, {4}, 0, {5}, 0, {6}, {7}, {8});",
ind.DataTypeId, ind.Key, ind.AggTypeId, ind.SortOrder, ind.IsRequired ? -1 : 0, ind.IsCalculated ? -1 : 0, ind.FormId, ind.SplitRuleId, ind.MergeRuleId);
break;
case IndicatorEntityType.Process:
insertSql = string.Format("insert into ProcessIndicators (DataTypeId, DisplayName, AggTypeId, SortOrder, UpdatedById, UpdatedAt, IsDisabled, IsEditable, IsRequired, IsDisplayed, IsCalculated, CanAddValues, IsMetaData, ProcessTypeId, RedistrictRuleId, MergeRuleId) values ({0}, '{1}', {2}, {3}, 26, NOW(), 0, 0, {4}, 0, {5}, 0, 0, {6}, {7}, {8});",
ind.DataTypeId, ind.Key, ind.AggTypeId, ind.SortOrder, ind.IsRequired ? -1 : 0, ind.IsCalculated ? -1 : 0, ind.FormId, ind.SplitRuleId, ind.MergeRuleId);
break;
}
if (string.IsNullOrEmpty(insertSql))
throw new Exception("Invalid indicator type for ID# " + ind.Id);
OleDbCommand updateCmd = new OleDbCommand(insertSql, connection);
updateCmd.ExecuteNonQuery();
sqlStatements.Add(insertSql);
if(ind.EntityType == IndicatorEntityType.Intervention)
{
string joinToForm = string.Format("insert into interventiontypes_to_indicators (InterventionTypeId, IndicatorId) SELECT {0}, ID FROM interventionindicators where displayname = '{1}' AND InterventionTypeId = {0};", ind.FormId, ind.Key);
OleDbCommand joinCmd = new OleDbCommand(joinToForm, connection);
updateCmd.ExecuteNonQuery();
sqlStatements.Add(joinToForm);
}
}