Ejemplo n.º 1
0
        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();
                }
            }
        }
Ejemplo n.º 2
0
        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());
                }
            }));
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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();
            }
        }