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 RunValidation(SubmissionCycle submissionCycle, long ruleValidationId) { var schoolYear = _schoolYearService.GetSchoolYearById(submissionCycle.SchoolYearId.Value); string fourDigitOdsDbYear = schoolYear.EndYear; // todo: dependency inject the initialization of RawOdsDbContext with a year. introduce a factory for most simple implementation using (var odsRawDbContext = new RawOdsDbContext(fourDigitOdsDbYear)) { using (var validationDbContext = DbContextFactory.Create()) { var newReportSummary = validationDbContext .ValidationReportSummaries .Include(x => x.SchoolYear) .FirstOrDefault(x => x.ValidationReportSummaryId == ruleValidationId && x.SchoolYearId == schoolYear.Id); var newRuleValidationExecution = odsRawDbContext.RuleValidations.FirstOrDefault(x => x.RuleValidationId == newReportSummary.RuleValidationId); var collectionId = newRuleValidationExecution.CollectionId; #region 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}."); #endregion Now, store each Ruleset ID and Rule ID that the engine will run. Save it in the Engine database. #region 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}."); #endregion The ValidationReportDetails is one-for-one with the ValidationReportSummary - it should be refactored away. It contains the error/warning details. #region Execute each individual rule. List <RulesEngineExecutionException> rulesEngineExecutionExceptions = new List <RulesEngineExecutionException>(); for (var i = 0; i < rules.Length; i++) { var rule = rules[i]; try { // 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}."); _loggingService.LogDebugMessage($"Executing Rule SQL {rule.Sql}."); 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 = 60; var result = odsRawDbContext.Database.ExecuteSqlCommand(rule.ExecSql, detailParams.ToArray()); _loggingService.LogDebugMessage($"Executing Rule {rule.RuleId} rows affected = {result}."); #region 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(); #endregion Record the results of this rule in the Validation Portal database, accompanied by more detailed information. } 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() }); } } #endregion Execute each individual rule. _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."); } } }