public List <SelectListItem> GetSchoolYearsSelectList(SubmissionCycle submissionCycle = null) { var schoolYearsEnumerable = SchoolYearService.GetSubmittableSchoolYearsDictionary().OrderByDescending(x => x.Value); List <SelectListItem> schoolYears = schoolYearsEnumerable.Select(kvPair => new SelectListItem { Value = kvPair.Key.ToString(), Text = kvPair.Value, Selected = (submissionCycle != null) && (kvPair.Key == submissionCycle.SchoolYearId) }).ToList(); if (submissionCycle != null) { schoolYears[0].Selected = true; } return(schoolYears); }
public void DeleteOldValidationRuns(SubmissionCycle submissionCycle, int edOrgId) { if (submissionCycle?.SchoolYearId == null) { throw new ArgumentException("Submission cycle is null, or SchoolYearId is null", nameof(submissionCycle)); } using (var validationDbContext = DbContextFactory.Create()) { var reportSummaries = validationDbContext.ValidationReportSummaries .Where(x => x.Collection == submissionCycle.CollectionId && x.SchoolYearId == submissionCycle.SchoolYearId && x.EdOrgId == edOrgId); if (reportSummaries.Count() > 1) { reportSummaries = reportSummaries.OrderByDescending(x => x.RequestedWhen).Skip(1); foreach (var reportSummary in reportSummaries) { var reportDetails = validationDbContext.ValidationReportDetails.Where( x => x.ValidationReportSummaryId == reportSummary.ValidationReportSummaryId); validationDbContext.ValidationReportDetails.RemoveRange(reportDetails); validationDbContext.ValidationReportSummaries.Remove(reportSummary); var schoolYear = SchoolYearService.GetSchoolYearById(reportSummary.SchoolYearId); string fourDigitOdsDbYear = schoolYear.EndYear; using (var odsRawDbContext = OdsDbContextFactory.CreateWithParameter(fourDigitOdsDbYear)) { var ruleValidations = odsRawDbContext.RuleValidations.Where( x => x.RuleValidationId == reportSummary.RuleValidationId); odsRawDbContext.RuleValidations.RemoveRange(ruleValidations); odsRawDbContext.SaveChanges(); } } validationDbContext.SaveChanges(); } } }
public Task RunValidationAsync(SubmissionCycle submissionCycle, long ruleValidationId) { if (submissionCycle?.SchoolYearId == null) { throw new ArgumentException("Submission cycle is null, or SchoolYearId is null", nameof(submissionCycle)); } var schoolYear = SchoolYearService.GetSchoolYearById(submissionCycle.SchoolYearId.Value); string fourDigitOdsDbYear = schoolYear.EndYear; LoggingService.LogInfoMessage($"===== Starting validation run for year {fourDigitOdsDbYear}, ruleValidationId {ruleValidationId}"); return(Task.Factory .StartNew(() => RunValidation(submissionCycle, ruleValidationId)) .ContinueWith(task => { LoggingService.LogInfoMessage($"===== Completed validation run for year {fourDigitOdsDbYear}, ruleValidationId {ruleValidationId}"); if (task.Exception != null) { LoggingService.LogErrorMessage(task.Exception.Flatten().ChainInnerExceptionMessages()); } })); }
public ValidationReportSummary SetupValidationRun(SubmissionCycle submissionCycle, string collectionId) { if (submissionCycle?.SchoolYearId == null) { throw new ArgumentException("Submission cycle is null or contains null SchoolYearId", nameof(submissionCycle)); } var schoolYear = SchoolYearService.GetSchoolYearById(submissionCycle.SchoolYearId.Value); string fourDigitOdsDbYear = schoolYear.EndYear; ValidationReportSummary newReportSummary; using (var odsRawDbContext = OdsDbContextFactory.CreateWithParameter(fourDigitOdsDbYear)) { LoggingService.LogDebugMessage( $"Connecting to the Ed Fi ODS {fourDigitOdsDbYear} to run the Rules Engine. Submitting the RulesValidation run ID."); // Add a new execution of the Validation Engine to the ODS database, (required by the Engine) and get an ID back representing this execution. var newRuleValidationExecution = new RuleValidation { CollectionId = collectionId }; odsRawDbContext.RuleValidations.Add(newRuleValidationExecution); odsRawDbContext.SaveChanges(); LoggingService.LogDebugMessage( $"Successfully submitted RuleValidationId {newRuleValidationExecution.RuleValidationId.ToString()} to the Rules Engine database table."); // Add a new execution of the Validation Engine to the Validation database, (required by the Portal) and get an ID back representing this execution. /* todo: using this (Id, SchoolYearId) as a PK - this isn't reliable because it comes from the ods's id. * we can stomp other execution runs from other districts etc. the ID is the identity column in another database. * it doesn't know about what we're doing ... change the ID to the ods's execution id and set up our own identity column * that's independent (and change all references to this "id" */ newReportSummary = new ValidationReportSummary { Collection = collectionId, CompletedWhen = null, ErrorCount = null, WarningCount = null, TotalCount = 0, RuleValidationId = newRuleValidationExecution.RuleValidationId, EdOrgId = AppUserService.GetSession().FocusedEdOrgId, SchoolYearId = schoolYear.Id, InitiatedBy = AppUserService.GetUser().FullName, RequestedWhen = DateTime.UtcNow, Status = "In Progress - Starting" }; LoggingService.LogDebugMessage( $"Successfully submitted Validation Report Summary ID {newReportSummary.ValidationReportSummaryId} " + $"to the Validation Portal database for Rules Validation Run {newRuleValidationExecution.RuleValidationId.ToString()}."); } using (var validationDbContext = DbContextFactory.Create()) { validationDbContext.ValidationReportSummaries.Add(newReportSummary); validationDbContext.SaveChanges(); } return(newReportSummary); }
public void RunValidation(SubmissionCycle submissionCycle, long ruleValidationId) { if (submissionCycle?.SchoolYearId == null) { throw new ArgumentException("Submission cycle is null, or SchoolYearId is null", nameof(submissionCycle)); } var schoolYear = SchoolYearService.GetSchoolYearById(submissionCycle.SchoolYearId.Value); var fourDigitOdsDbYear = schoolYear.EndYear; using (var odsRawDbContext = OdsDbContextFactory.CreateWithParameter(fourDigitOdsDbYear)) { using (var validationDbContext = DbContextFactory.Create()) { var newReportSummary = validationDbContext .ValidationReportSummaries .Include(x => x.SchoolYear) .FirstOrDefault(x => x.ValidationReportSummaryId == ruleValidationId && x.SchoolYearId == schoolYear.Id); if (newReportSummary == null) { throw new InvalidOperationException($"Unable to find report summary {ruleValidationId} for year {schoolYear.Id}"); } string savedSqlPath = string.Empty; if (EngineConfig.SaveGeneratedRulesSqlToFiles) { savedSqlPath = Path.Combine( Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "ValidationRulesSql", ruleValidationId.ToString()); LoggingService.LogInfoMessage("Saved SQL Directory is " + savedSqlPath); if (Directory.Exists(savedSqlPath)) { LoggingService.LogInfoMessage("Clearing contents of " + savedSqlPath); Directory.Delete(savedSqlPath, true); } Directory.CreateDirectory(savedSqlPath); } var newRuleValidationExecution = odsRawDbContext.RuleValidations .FirstOrDefault(x => x.RuleValidationId == newReportSummary.RuleValidationId); if (newRuleValidationExecution == null) { throw new InvalidOperationException($"Unable to find execution {ruleValidationId}"); } var collectionId = newRuleValidationExecution.CollectionId; // todo: check collectionId for null/empty and why is it a string!? // Now, store each Ruleset ID and Rule ID that the engine will run. Save it in the Engine database. LoggingService.LogDebugMessage($"Getting the rules to run for the chosen collection {collectionId}."); var rules = EngineObjectModel.GetRules(collectionId).ToArray(); var ruleComponents = rules.SelectMany( r => r.Components.Distinct().Select( c => new { r.RulesetId, r.RuleId, Component = c })); foreach (var singleRuleNeedingToBeValidated in ruleComponents) { odsRawDbContext.RuleValidationRuleComponents.Add( new RuleValidationRuleComponent { RuleValidationId = newRuleValidationExecution.RuleValidationId, RulesetId = singleRuleNeedingToBeValidated.RulesetId, RuleId = singleRuleNeedingToBeValidated.RuleId, Component = singleRuleNeedingToBeValidated.Component }); } odsRawDbContext.SaveChanges(); LoggingService.LogDebugMessage($"Saved the rules to run for the chosen collection {collectionId}."); // The ValidationReportDetails is one-for-one with the ValidationReportSummary - it should be refactored away. It contains the error/warning details. LoggingService.LogDebugMessage( $"Adding additional Validation Report details to the Validation Portal database for EdOrgID {newReportSummary.EdOrgId}."); var newReportDetails = new ValidationReportDetails { CollectionName = collectionId, SchoolYearId = newReportSummary.SchoolYear.Id, DistrictName = $"{EdOrgService.GetEdOrgById(newReportSummary.EdOrgId, newReportSummary.SchoolYear.Id).OrganizationName} ({newReportSummary.EdOrgId.ToString()})", ValidationReportSummaryId = newReportSummary.ValidationReportSummaryId }; validationDbContext.ValidationReportDetails.Add(newReportDetails); try { validationDbContext.SaveChanges(); } catch (Exception ex) { LoggingService.LogErrorMessage(ex.ChainInnerExceptionMessages()); } LoggingService.LogDebugMessage( $"Successfully added additional Validation Report details to the Validation Portal database for EdOrgID {newReportSummary.EdOrgId}."); // Execute each individual rule. List <RulesEngineExecutionException> rulesEngineExecutionExceptions = new List <RulesEngineExecutionException>(); for (var i = 0; i < rules.Length; i++) { var rule = rules[i]; try { //Execute the SQL files in here? We have the RuleSetName and the Rule Id //e.g. RuleSetId = MultipleEnrollment // RuleId = 10.10.6175 var toExecute = ManualRuleExecutionService.GetManualSqlFile(rule.RulesetId, rule.RuleId).Result; // By default, rules are run against ALL districts in the Ed Fi ODS. This line filters for multi-district/multi-tenant ODS's. rule.AddDistrictWhereFilter(newReportSummary.EdOrgId); LoggingService.LogDebugMessage($"Executing Rule {rule.RuleId}."); var detailParams = new List <SqlParameter> { new SqlParameter( "@RuleValidationId", newRuleValidationExecution.RuleValidationId) }; detailParams.AddRange( EngineObjectModel.GetParameters(collectionId) .Select(x => new SqlParameter(x.ParameterName, x.Value))); odsRawDbContext.Database.CommandTimeout = EngineConfig.RulesExecutionTimeout; if (toExecute.Count > 0) { foreach (var sql in toExecute) { detailParams.Add( new SqlParameter( "@DistrictId", newReportSummary.EdOrgId)); LoggingService.LogDebugMessage($"Executing Rule SQL {sql}."); var resultManualSql = odsRawDbContext.Database.ExecuteSqlCommand(sql, detailParams.ToArray <object>()); LoggingService.LogDebugMessage($"Executing Rule {rule.RuleId} rows affected = {resultManualSql}."); } } else { if (EngineConfig.SaveGeneratedRulesSqlToFiles && !string.IsNullOrWhiteSpace(savedSqlPath)) { var rulesetPath = Path.Combine(savedSqlPath, rule.RulesetId); if (!Directory.Exists(rulesetPath)) { Directory.CreateDirectory(rulesetPath); } var rulePath = Path.Combine(rulesetPath, rule.RuleId + ".sql"); File.WriteAllText(rulePath, rule.Sql); } LoggingService.LogDebugMessage($"Executing Rule SQL {rule.ExecSql}."); var result = odsRawDbContext.Database.ExecuteSqlCommand(rule.ExecSql, detailParams.ToArray <object>()); LoggingService.LogDebugMessage($"Executing Rule {rule.RuleId} rows affected = {result}."); } // Record the results of this rule in the Validation Portal database, accompanied by more detailed information. PopulateErrorDetailsFromViews( rule, odsRawDbContext, newRuleValidationExecution.RuleValidationId, newReportDetails.Id); newReportSummary.Status = $"In Progress - {(int)((float)i / rules.Length * 100)}% complete"; validationDbContext.SaveChanges(); } catch (Exception ex) { rulesEngineExecutionExceptions.Add( new RulesEngineExecutionException { RuleId = rule.RuleId, Sql = rule.Sql, ExecSql = rule.ExecSql, DataSourceName = $"Database Server: {odsRawDbContext.Database.Connection.DataSource}{Environment.NewLine} " + $"Database: {odsRawDbContext.Database.Connection.Database}", ChainedErrorMessages = ex.ChainInnerExceptionMessages() }); } } LoggingService.LogDebugMessage("Counting errors and warnings."); newReportSummary.CompletedWhen = DateTime.UtcNow; newReportSummary.ErrorCount = odsRawDbContext.RuleValidationDetails.Count( rvd => rvd.RuleValidation.RuleValidationId == newRuleValidationExecution.RuleValidationId && rvd.IsError); newReportSummary.WarningCount = odsRawDbContext.RuleValidationDetails.Count( rvd => rvd.RuleValidation.RuleValidationId == newRuleValidationExecution.RuleValidationId && !rvd.IsError); var hasExecutionErrors = rulesEngineExecutionExceptions.Count > 0; newReportSummary.Status = hasExecutionErrors ? $"Completed - {rulesEngineExecutionExceptions.Count} rules did not execute, ask an administrator to check the log for errors, Report Summary Number {newReportSummary.ValidationReportSummaryId.ToString()}" : "Completed"; LoggingService.LogDebugMessage($"Saving status {newReportSummary.Status}."); // Log Execution Errors LoggingService.LogErrorMessage( GetLogExecutionErrorsMessage(rulesEngineExecutionExceptions, newReportSummary.ValidationReportSummaryId)); newReportDetails.CompletedWhen = newReportDetails.CompletedWhen ?? DateTime.UtcNow; validationDbContext.SaveChanges(); LoggingService.LogDebugMessage("Saved status."); } } }
public void UpdateViewsAndRulesForSchoolYear(int schoolYearId) { DeleteViewsAndRulesForSchoolYear(schoolYearId); var viewsForYear = new List <ValidationRulesView>(); var schoolYear = SchoolYearService.GetSchoolYearById(schoolYearId); using (var schoolYearContext = SchoolYearDbContextFactory.CreateWithParameter(schoolYear.EndYear)) { var connection = schoolYearContext.Database.Connection; connection.Open(); var queryCommand = connection.CreateCommand(); queryCommand.CommandType = System.Data.CommandType.Text; queryCommand.CommandText = @"select OBJECT_SCHEMA_NAME(t.object_id) [Schema], t.name [Name] FROM sys.tables as t where OBJECT_SCHEMA_NAME(t.object_id)=@schemaName"; queryCommand.Parameters.Add(new SqlParameter("@schemaName", "rules")); using (var reader = queryCommand.ExecuteReader()) { while (reader.Read()) { // check for exclusions here and just continue var tableName = reader["Name"].ToString(); if (!RulesEngineConfigurationValues.RulesTableExclusions.Contains( tableName, StringComparer.OrdinalIgnoreCase)) { var rulesView = new ValidationRulesView { Enabled = true, Schema = reader["Schema"].ToString(), Name = tableName, SchoolYearId = schoolYearId }; viewsForYear.Add(rulesView); } } } } using (var validationPortalContext = ValidationPortalDataContextFactory.Create()) { foreach (var view in viewsForYear) { var fieldNames = GetFieldsForView(schoolYear, view.Schema, view.Name); foreach (var fieldName in fieldNames) { var rulesField = new ValidationRulesField { Enabled = true, Name = fieldName }; validationPortalContext.ValidationRulesFields.Add(rulesField); view.RulesFields.Add(rulesField); } } validationPortalContext.ValidationRulesViews.AddRange(viewsForYear); validationPortalContext.SaveChanges(); } }