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