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