Ejemplo n.º 1
0
 public List <DemographicsCountReportQuery> GetDistrictAncestryRaceCounts(int?districtEdOrgId, string fourDigitOdsDbYear)
 {
     // todo: dependency-inject these data contexts
     using (var rawOdsContext = new RawOdsDbContext(fourDigitOdsDbYear))
     {
         var conn = rawOdsContext.Database.Connection;
         try
         {
             conn.Open();
             var ancestryQueryCmd = conn.CreateCommand();
             ancestryQueryCmd.CommandType = System.Data.CommandType.StoredProcedure;
             ancestryQueryCmd.CommandText = DemographicsCountReportQuery.DistrictAncestryRaceCountsQuery;
             ancestryQueryCmd.Parameters.Add(new SqlParameter("@distid", System.Data.SqlDbType.Int));
             ancestryQueryCmd.Parameters["@distid"].Value = districtEdOrgId.HasValue ? (object)districtEdOrgId.Value : (object)DBNull.Value;
             var reportData = new List <DemographicsCountReportQuery>();
             using (var reader = ancestryQueryCmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     int?theEdOrgValue = null;
                     var edOrgIdObj    = reader[DemographicsCountReportQuery.EdOrgIdColumnName];
                     if (!(edOrgIdObj is DBNull))
                     {
                         theEdOrgValue = System.Convert.ToInt32(reader[DemographicsCountReportQuery.EdOrgIdColumnName]);
                     }
                     reportData.Add(new DemographicsCountReportQuery
                     {
                         OrgType            = (OrgType)(System.Convert.ToInt32(reader[DemographicsCountReportQuery.OrgTypeColumnName])),
                         EdOrgId            = theEdOrgValue,
                         LEASchool          = reader[DemographicsCountReportQuery.LEASchoolColumnName].ToString(),
                         EnrollmentCount    = System.Convert.ToInt32(reader[DemographicsCountReportQuery.DistinctEnrollmentCountColumnName]),
                         DemographicsCount  = System.Convert.ToInt32(reader[DemographicsCountReportQuery.DistinctDemographicsCountColumnName]),
                         AncestryGivenCount = System.Convert.ToInt32(reader[DemographicsCountReportQuery.AncestryGivenCountColumnName]),
                         RaceGivenCount     = System.Convert.ToInt32(reader[DemographicsCountReportQuery.RaceGivenCountColumnName])
                     });
                 }
             }
             return(reportData);
         }
         catch (Exception ex)
         {
             LoggingService.LogErrorMessage($"While compiling report on ancestry/ethnic origin supplied counts: {ex.ChainInnerExceptionMessages()}");
             throw;
         }
         finally
         {
             if (conn != null && conn.State != System.Data.ConnectionState.Closed)
             {
                 try
                 {
                     conn.Close();
                 }
                 catch (Exception) { }
             }
         }
     }
 }
Ejemplo n.º 2
0
 public List <MultipleEnrollmentsCountReportQuery> GetMultipleEnrollmentCounts(int?districtEdOrgId, string fourDigitOdsDbYear)
 {
     using (var rawOdsContext = new RawOdsDbContext(fourDigitOdsDbYear))
     {
         var conn = rawOdsContext.Database.Connection;
         try
         {
             conn.Open();
             var multipleEnrolledQueryCmd = conn.CreateCommand();
             multipleEnrolledQueryCmd.CommandType = System.Data.CommandType.StoredProcedure;
             multipleEnrolledQueryCmd.CommandText = MultipleEnrollmentsCountReportQuery.MultipleEnrollmentsCountQuery;
             multipleEnrolledQueryCmd.Parameters.Add(new SqlParameter("@distid", System.Data.SqlDbType.Int));
             multipleEnrolledQueryCmd.Parameters["@distid"].Value = districtEdOrgId.HasValue ? (object)districtEdOrgId.Value : (object)DBNull.Value;
             var reportData = new List <MultipleEnrollmentsCountReportQuery>();
             using (var reader = multipleEnrolledQueryCmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     int?theEdOrgValue = null;
                     var edOrgIdObj    = reader[MultipleEnrollmentsCountReportQuery.EdOrgIdColumnName];
                     if (!(edOrgIdObj is DBNull))
                     {
                         theEdOrgValue = System.Convert.ToInt32(reader[MultipleEnrollmentsCountReportQuery.EdOrgIdColumnName]);
                     }
                     reportData.Add(new MultipleEnrollmentsCountReportQuery
                     {
                         OrgType                       = (OrgType)(System.Convert.ToInt32(reader[MultipleEnrollmentsCountReportQuery.OrgTypeColumnName])),
                         EdOrgId                       = theEdOrgValue,
                         LEASchool                     = reader[MultipleEnrollmentsCountReportQuery.SchoolNameColumnName].ToString(),
                         TotalEnrollmentCount          = System.Convert.ToInt32(reader[MultipleEnrollmentsCountReportQuery.TotalEnrollmentCountColumnName]),
                         DistinctEnrollmentCount       = System.Convert.ToInt32(reader[MultipleEnrollmentsCountReportQuery.DistinctEnrollmentCountColumnName]),
                         EnrolledInOtherSchoolsCount   = System.Convert.ToInt32(reader[MultipleEnrollmentsCountReportQuery.EnrolledInOtherSchoolsCountColumnName]),
                         EnrolledInOtherDistrictsCount = System.Convert.ToInt32(reader[MultipleEnrollmentsCountReportQuery.EnrolledInOtherDistrictsCountColumnName])
                     });
                 }
             }
             return(reportData);
         }
         catch (Exception ex)
         {
             LoggingService.LogErrorMessage($"While compiling report on multiple enrollments: {ex.ChainInnerExceptionMessages()}");
             throw;
         }
         finally
         {
             if (conn != null && conn.State != System.Data.ConnectionState.Closed)
             {
                 try
                 {
                     conn.Close();
                 }
                 catch (Exception) { }
             }
         }
     }
 }
Ejemplo n.º 3
0
        public List <StudentDrillDownQuery> GetMultipleEnrollmentStudentDrillDown(
            OrgType orgType,
            int?schoolEdOrgId,
            int?districtEdOrgId,
            int?columnIndex,
            string fourDigitOdsDbYear)
        {
            var returnedList = new List <StudentDrillDownQuery>();

            using (var rawOdsContext = new RawOdsDbContext(fourDigitOdsDbYear))
            {
                var conn = rawOdsContext.Database.Connection;
                try
                {
                    conn.Open();
                    var queryCmd = conn.CreateCommand();
                    queryCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    queryCmd.CommandText = MultipleEnrollmentsCountReportQuery.MultipleEnrollmentsStudentDetailsQuery;

                    queryCmd.Parameters.Add(new SqlParameter("@schoolid", System.Data.SqlDbType.Int));
                    queryCmd.Parameters["@schoolid"].Value = schoolEdOrgId.HasValue &&
                                                             orgType == OrgType.School ? schoolEdOrgId.Value : (object)DBNull.Value;

                    queryCmd.Parameters.Add(new SqlParameter("@distid", System.Data.SqlDbType.Int));
                    queryCmd.Parameters["@distid"].Value = districtEdOrgId.HasValue &&
                                                           (orgType == OrgType.District || orgType == OrgType.School) ? districtEdOrgId.Value : (object)DBNull.Value;

                    queryCmd.Parameters.Add(new SqlParameter("@columnIndex", System.Data.SqlDbType.Int));
                    queryCmd.Parameters["@columnIndex"].Value = columnIndex ?? (object)DBNull.Value;

                    using (var reader = queryCmd.ExecuteReader())
                    {
                        returnedList = ReadStudentDrillDownDataReader(reader);
                    }

                    return(returnedList);
                }
                catch (Exception ex)
                {
                    LoggingService.LogErrorMessage($"While providing student details on multiple enrollments: {ex.ChainInnerExceptionMessages()}");
                    throw;
                }
                finally
                {
                    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch (Exception) { }
                    }
                }
            }
        }
Ejemplo n.º 4
0
 public List <ResidentsEnrolledElsewhereReportQuery> GetResidentsEnrolledElsewhereReport(int?districtEdOrgId, string fourDigitOdsDbYear)
 {
     using (var rawOdsContext = new RawOdsDbContext(fourDigitOdsDbYear))
     {
         var conn = rawOdsContext.Database.Connection;
         try
         {
             conn.Open();
             var residentsElseWhereQueryCmd = conn.CreateCommand();
             residentsElseWhereQueryCmd.CommandType = System.Data.CommandType.StoredProcedure;
             residentsElseWhereQueryCmd.CommandText = ResidentsEnrolledElsewhereReportQuery.ResidentsEnrolledElsewhereQuery;
             residentsElseWhereQueryCmd.Parameters.Add(new SqlParameter("@distid", System.Data.SqlDbType.Int));
             residentsElseWhereQueryCmd.Parameters["@distid"].Value = districtEdOrgId.HasValue ? (object)districtEdOrgId.Value : (object)DBNull.Value;
             var reportData = new List <ResidentsEnrolledElsewhereReportQuery>();
             using (var reader = residentsElseWhereQueryCmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     int?theEdOrgValue = null;
                     var edOrgIdObj    = reader[ResidentsEnrolledElsewhereReportQuery.EdOrgIdColumnName];
                     if (!(edOrgIdObj is DBNull))
                     {
                         theEdOrgValue = System.Convert.ToInt32(reader[ResidentsEnrolledElsewhereReportQuery.EdOrgIdColumnName]);
                     }
                     reportData.Add(new ResidentsEnrolledElsewhereReportQuery
                     {
                         OrgType   = (OrgType)(System.Convert.ToInt32(reader[ResidentsEnrolledElsewhereReportQuery.OrgTypeColumnName])),
                         EdOrgId   = theEdOrgValue,
                         EdOrgName = reader[ResidentsEnrolledElsewhereReportQuery.EdOrgNameColumnName].ToString(),
                         DistrictOfEnrollmentId   = System.Convert.ToInt32(reader[ResidentsEnrolledElsewhereReportQuery.DistrictOfEnrollmentIdColumnName]),
                         DistrictOfEnrollmentName = reader[ResidentsEnrolledElsewhereReportQuery.DistrictOfEnrollmentNameColumnName].ToString(),
                         ResidentsEnrolled        = System.Convert.ToInt32(reader[ResidentsEnrolledElsewhereReportQuery.ResidentsEnrolledColumnName])
                     });
                 }
             }
             return(reportData);
         }
         catch (Exception ex)
         {
             LoggingService.LogErrorMessage($"While compiling report on residents enrolled elsewhere: {ex.ChainInnerExceptionMessages()}");
             throw;
         }
         finally
         {
             if (conn != null && conn.State != System.Data.ConnectionState.Closed)
             {
                 try
                 {
                     conn.Close();
                 }
                 catch (Exception) { }
             }
         }
     }
 }
Ejemplo n.º 5
0
        public SingleEdOrgByIdQuery GetSingleEdOrg(int edOrgId, int schoolYearId)
        {
            SingleEdOrgByIdQuery result = null;

            var schoolYear = SchoolYearService.GetSchoolYearById(schoolYearId);

            using (var odsRawDbContext = new RawOdsDbContext(schoolYear.EndYear))
            {
                var conn = odsRawDbContext.Database.Connection;
                try
                {
                    conn.Open();
                    var edOrgQueryCmd = conn.CreateCommand();
                    edOrgQueryCmd.CommandType = System.Data.CommandType.Text;
                    edOrgQueryCmd.CommandText = SingleEdOrgByIdQuery.EdOrgQuery;
                    edOrgQueryCmd.Parameters.Add(new SqlParameter("@edOrgId", System.Data.SqlDbType.Int));
                    edOrgQueryCmd.Parameters["@edOrgId"].Value = edOrgId;

                    using (var reader = edOrgQueryCmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            result = new SingleEdOrgByIdQuery
                            {
                                Id = int.Parse(reader[SingleEdOrgByIdQuery.IdColumnName].ToString()),
                                ShortOrganizationName = reader[SingleEdOrgByIdQuery.OrganizationShortNameColumnName].ToString(),
                                OrganizationName      = reader[SingleEdOrgByIdQuery.OrganizationNameColumnName].ToString(),
                                StateOrganizationId   = reader[SingleEdOrgByIdQuery.StateOrganizationIdColumnName].ToString()
                            };
                        }
                    }
                }
                catch (Exception ex)
                {
                    LoggingService.LogErrorMessage(
                        $"While reading Ed Org description (ID# {edOrgId}, school year {schoolYear.ToString()}): {ex.ChainInnerExceptionMessages()}");
                }
                finally
                {
                    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
            }

            return(result);
        }
Ejemplo n.º 6
0
        public void RefreshEdOrgCache(SchoolYear schoolYear)
        {
            string fourDigitOdsDbYear     = schoolYear.EndYear;
            var    edOrgsExtractedFromODS = new List <EdOrg>();

            using (var odsRawDbContext = new RawOdsDbContext(fourDigitOdsDbYear))
            {
                var conn = odsRawDbContext.Database.Connection;
                try
                {
                    conn.Open();
                    var edOrgQueryCmd = conn.CreateCommand();
                    edOrgQueryCmd.CommandType = System.Data.CommandType.Text;
                    edOrgQueryCmd.CommandText = EdOrgQuery.AllEdOrgQuery;
                    edOrgsExtractedFromODS.AddRange(ReadEdOrgs(edOrgQueryCmd, schoolYear.Id).ToList());
                }
                catch (Exception ex)
                {
                    LoggingService.LogErrorMessage($"While trying to add all ODS Ed Org descriptions to the Validation Portal Database Cache: school year {fourDigitOdsDbYear}, error: {ex.ChainInnerExceptionMessages()}");
                }
                finally
                {
                    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch (Exception) { }   // todo: never this
                    }
                }
            }

            using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
            {
                foreach (var singleEdOrg in edOrgsExtractedFromODS)
                {
                    validationPortalDataContext.EdOrgs.AddOrUpdate(singleEdOrg);
                }

                LoggingService.LogDebugMessage($"EdOrgCache: saving changes");
                validationPortalDataContext.SaveChanges();
            }
        }
Ejemplo n.º 7
0
        public List <StudentDrillDownQuery> GetResidentsEnrolledElsewhereStudentDrillDown(int?districtEdOrgId, string fourDigitOdsDbYear)
        {
            var returnedList = new List <StudentDrillDownQuery>();

            using (var rawOdsContext = new RawOdsDbContext(fourDigitOdsDbYear))
            {
                var conn = rawOdsContext.Database.Connection;
                try
                {
                    conn.Open();
                    var residentsElseWhereQueryCmd = conn.CreateCommand();
                    residentsElseWhereQueryCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    residentsElseWhereQueryCmd.CommandText = ResidentsEnrolledElsewhereReportQuery.ResidentsEnrolledElsewhereStudentDetailsQuery;
                    residentsElseWhereQueryCmd.Parameters.Add(new SqlParameter("@distid", System.Data.SqlDbType.Int));
                    residentsElseWhereQueryCmd.Parameters["@distid"].Value = districtEdOrgId.HasValue ? (object)districtEdOrgId.Value : (object)DBNull.Value;
                    using (var reader = residentsElseWhereQueryCmd.ExecuteReader())
                    {
                        returnedList = ReadStudentDrillDownDataReader(reader);
                    }
                    return(returnedList);
                }
                catch (Exception ex)
                {
                    LoggingService.LogErrorMessage($"While providing student details on residents enrolled elsewhere: {ex.ChainInnerExceptionMessages()}");
                    throw;
                }
                finally
                {
                    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch (Exception) { }   // todo: remove all these
                    }
                }
            }
        }
Ejemplo n.º 8
0
        public EdOrg GetEdOrgById(int edOrgId, int schoolYearId)
        {
            using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
            {
                var schoolYear = SchoolYearService.GetSchoolYearById(schoolYearId);

                LoggingService.LogDebugMessage($"EdOrg cache: {validationPortalDataContext.EdOrgs.Count()} currently in ValidationPortal database");

                if (!validationPortalDataContext.EdOrgs.Any())
                {
                    LoggingService.LogDebugMessage($"Refreshing EdOrg cache");
                    RefreshEdOrgCache(schoolYear);
                }

                var result = validationPortalDataContext.EdOrgs.FirstOrDefault(eo => eo.Id == edOrgId);
                if (result != null)
                {
                    return(result);
                }

                using (var _odsRawDbContext = new RawOdsDbContext(schoolYear.EndYear))
                {
                    var conn = _odsRawDbContext.Database.Connection;
                    try
                    {
                        conn.Open();
                        var edOrgQueryCmd = conn.CreateCommand();
                        edOrgQueryCmd.CommandType = System.Data.CommandType.Text;
                        edOrgQueryCmd.CommandText = EdOrgQuery.SingleEdOrgsQuery;
                        edOrgQueryCmd.Parameters.Add(new SqlParameter("@lea_id", System.Data.SqlDbType.Int));
                        edOrgQueryCmd.Parameters["@lea_id"].Value = edOrgId;
                        result = ReadEdOrgs(edOrgQueryCmd, schoolYearId).FirstOrDefault();
                    }
                    catch (Exception ex)
                    {
                        LoggingService.LogErrorMessage(
                            $"While reading Ed Org description (ID# {edOrgId}, school year {schoolYear.ToString()}): {ex.ChainInnerExceptionMessages()}");
                    }
                    finally
                    {
                        if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                        {
                            try
                            {
                                conn.Close();
                            }
                            catch (Exception)
                            {
                            }
                        }
                    }
                }

                if (result != null)
                {
                    validationPortalDataContext.EdOrgs.AddOrUpdate(result);
                    validationPortalDataContext.SaveChanges();
                    return(result);
                }

                throw new ApplicationException(
                          $"The Ed Org with ID# {edOrgId}, school year {schoolYear.ToString()}, was not found.");
            }
        }
Ejemplo n.º 9
0
        /// <summary>
        /// For a single rule, on a single execution of the Rules Engine, records the resulting errors and warnings accompanied by enhanced information.
        /// </summary>
        /// <param name="rule">The single rule whose corresponding errors and warnings will be recorded in the Validation Portal's Database.</param>
        /// <param name="rawOdsContext">A connection to the Ed Fi ODS database from which details about the error and the entity the error relates to.
        /// Used as a source to fill in details about Individual errors and warnings.</param>
        /// <param name="rulesExecutionId">The single execution of the Rules Engine which serves as the scope of the paticular errors and warnings that will be
        /// transferred to the Validation Portal database. This ID is used by the Rules engine database.</param>
        /// <param name="reportDetailId">The single execution of the Rules Engine which serves as the scope of the paticular errors and warnings that will be
        /// transferred to the Validation Portal database. This ID is used by the Validation Portal database.</param>
        private void PopulateErrorDetailsFromViews(Rule rule, RawOdsDbContext rawOdsContext, long rulesExecutionId, int reportDetailId)
        {
            LoggingService.LogDebugMessage($"Preparing to populate the error information for rule {rule.RuleId} to the Validation Portal database.");
            try
            {
                var errorSummaries = new List <ValidationErrorSummary>();

                // Retrieve what the Rules Engine recorded - errors or warnings for this particular rule, on this particular execution.
                var queryResults = rawOdsContext.RuleValidationDetails.Where(rvd => rvd.RuleValidationId == rulesExecutionId && rvd.RuleId == rule.RuleId);
                LoggingService.LogDebugMessage($"Successfully retrieved results for rule {rule.RuleId} from the Ed Fi ODS database Rules Validation tables. Retrieving additional information.");
                var conn = rawOdsContext.Database.Connection;
                try
                {
                    conn.Open();
                    var studentQueryCmd = conn.CreateCommand();
                    studentQueryCmd.CommandType = System.Data.CommandType.Text;
                    studentQueryCmd.CommandText = StudentDataFromId.StudentDataQueryFromId;
                    studentQueryCmd.Parameters.Add(new SqlParameter("@student_unique_id", System.Data.SqlDbType.NVarChar, 32));

                    foreach (var queryResult in queryResults.ToArray())
                    {
                        studentQueryCmd.Parameters["@student_unique_id"].Value = queryResult.Id.ToString("D13");
                        var singleStudentData = new List <StudentDataFromId>();
                        using (var reader = studentQueryCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var entryDateValue        = Convert.IsDBNull(reader[StudentDataFromId.EntryDateColumnName]) ? (DateTime?)null : Convert.ToDateTime(reader[StudentDataFromId.EntryDateColumnName]);
                                var exitWithdrawDateValue = Convert.IsDBNull(reader[StudentDataFromId.ExitWithdrawDateColumnName]) ? (DateTime?)null : Convert.ToDateTime(reader[StudentDataFromId.ExitWithdrawDateColumnName]);
                                singleStudentData.Add(new StudentDataFromId
                                {
                                    EntryDate         = entryDateValue,
                                    ExitWithdrawDate  = exitWithdrawDateValue,
                                    FirstName         = reader[StudentDataFromId.FirstNameColumnName].ToString(),
                                    GradeLevel        = reader[StudentDataFromId.GradeLevelColumnName].ToString(),
                                    LastSurname       = reader[StudentDataFromId.LastSurnameColumnName].ToString(),
                                    MiddleName        = reader[StudentDataFromId.MiddleNameColumnName].ToString(),
                                    NameOfInstitution = reader[StudentDataFromId.NameOfInstitutionColumnName].ToString(),
                                    SchoolId          = reader[StudentDataFromId.SchoolIdColumnName].ToString(),
                                });
                            }
                        }

                        LoggingService.LogDebugMessage($"Additional info for one student error record retrieved from the ODS Rules Engine database table {rule.RuleId}.");

                        // Record the error (warning) with additional details taken from the ODS database.
                        errorSummaries.Add(new ValidationErrorSummary
                        {
                            StudentUniqueId           = queryResult.Id.ToString(), // .ToString("D13"),
                            StudentFullName           = StudentDataFromId.GetStudentFullName(singleStudentData),
                            SeverityId                = queryResult.IsError ? (int)ErrorSeverity.Error : (int)ErrorSeverity.Warning,
                            Component                 = rule.Components[0],
                            ErrorCode                 = rule.RuleId,
                            ErrorText                 = queryResult.Message,
                            ValidationReportDetailsId = reportDetailId,
                            ErrorEnrollmentDetails    = new HashSet <ValidationErrorEnrollmentDetail>(
                                singleStudentData.Select(
                                    ssd => new ValidationErrorEnrollmentDetail
                            {
                                School        = ssd.NameOfInstitution,
                                SchoolId      = ssd.SchoolId,
                                Grade         = ssd.GradeLevel,
                                DateEnrolled  = ssd.EntryDate,
                                DateWithdrawn = ssd.ExitWithdrawDate
                            }))
                        });

                        LoggingService.LogDebugMessage("A record was added to the Validation Portal, but not yet committed.");
                    }
                }
                catch (Exception ex)
                {
                    LoggingService.LogErrorMessage($"While reading student data to add to error/warning information during an execution of the validation engine, and error occurred: {ex.ChainInnerExceptionMessages()}");
                }
                finally
                {
                    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }

                using (var validationDbContext = DbContextFactory.Create())
                {
                    validationDbContext.ValidationErrorSummaries.AddRange(errorSummaries);
                    validationDbContext.SaveChanges();
                    LoggingService.LogDebugMessage(
                        $"Successfully committed all additional error information for students found with issues referring to rule {rule.RuleId}.");
                }
            }
            catch (Exception ex)
            {
                LoggingService.LogErrorMessage($"Error when compiling details of validation error {rule.RuleId ?? string.Empty}: {ex.ChainInnerExceptionMessages()}");
            }
        }
Ejemplo n.º 10
0
        public ValidationReportSummary SetupValidationRun(SubmissionCycle submissionCycle, string collectionId)
        {
            var    schoolYear         = _schoolYearService.GetSchoolYearById(submissionCycle.SchoolYearId.Value);
            string fourDigitOdsDbYear = schoolYear.EndYear;

            ValidationReportSummary newReportSummary = null;

            using (var odsRawDbContext = new RawOdsDbContext(fourDigitOdsDbYear))
            {
                using (var validationDbContext = DbContextFactory.Create())
                {
                    _loggingService.LogDebugMessage(
                        $"Connecting to the Ed Fi ODS {fourDigitOdsDbYear} to run the Rules Engine. Submitting the RulesValidation run ID.");

                    // Run the rules - This code is adapted from an example in the Rule Engine project.

                    #region 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.");

                    #endregion Add a new execution of the Validation Engine to the ODS database, (required by the Engine) and get an ID back representing this execution.

                    #region 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"
                    };

                    validationDbContext.ValidationReportSummaries.Add(newReportSummary);
                    validationDbContext.SaveChanges();
                    _loggingService.LogDebugMessage(
                        $"Successfully submitted Validation Report Summary ID {newReportSummary.ValidationReportSummaryId} to the Validation Portal database for Rules Validation Run {newRuleValidationExecution.RuleValidationId.ToString()}.");

                    #endregion Add a new execution of the Validation Engine to the Validation database, (required by the Portal) and get an ID back representing this execution.
                }
            }

            return(newReportSummary);
        }
Ejemplo n.º 11
0
        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.");
                }
            }
        }
Ejemplo n.º 12
0
        public List <ChangeOfEnrollmentReportQuery> GetChangeOfEnrollmentReport(int districtEdOrgId, string fourDigitOdsDbYear)
        {
            using (var rawOdsContext = new RawOdsDbContext(fourDigitOdsDbYear))
            {
                var conn = rawOdsContext.Database.Connection;
                try
                {
                    conn.Open();
                    var chgOfEnrollmentQueryCmd = conn.CreateCommand();
                    chgOfEnrollmentQueryCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    chgOfEnrollmentQueryCmd.CommandText = ChangeOfEnrollmentReportQuery.ChangeOfEnrollmentQuery;
                    chgOfEnrollmentQueryCmd.Parameters.Add(new SqlParameter("@distid", System.Data.SqlDbType.Int));
                    chgOfEnrollmentQueryCmd.Parameters["@distid"].Value = districtEdOrgId;
                    var reportData = new List <ChangeOfEnrollmentReportQuery>();
                    using (var reader = chgOfEnrollmentQueryCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            reportData.Add(new ChangeOfEnrollmentReportQuery
                            {
                                IsCurrentDistrict          = System.Convert.ToBoolean(reader[ChangeOfEnrollmentReportQuery.IsCurrentDistrictColumnName]),
                                CurrentDistEdOrgId         = System.Convert.ToInt32(reader[ChangeOfEnrollmentReportQuery.CurrentDistEdOrgIdColumnName]),
                                CurrentDistrictName        = reader[ChangeOfEnrollmentReportQuery.CurrentDistrictNameColumnName].ToString(),
                                CurrentSchoolEdOrgId       = System.Convert.ToInt32(reader[ChangeOfEnrollmentReportQuery.CurrentSchoolEdOrgIdColumnName]),
                                CurrentSchoolName          = reader[ChangeOfEnrollmentReportQuery.CurrentSchoolNameColumnName].ToString(),
                                CurrentEdOrgEnrollmentDate = (reader[ChangeOfEnrollmentReportQuery.CurrentEdOrgEnrollmentDateColumnName] is DBNull)
                                    ? (DateTime?)null
                                    : System.Convert.ToDateTime(reader[ChangeOfEnrollmentReportQuery.CurrentEdOrgEnrollmentDateColumnName]),
                                CurrentEdOrgExitDate = (reader[ChangeOfEnrollmentReportQuery.CurrentEdOrgExitDateColumnName] is DBNull)
                                    ? (DateTime?)null
                                    : System.Convert.ToDateTime(reader[ChangeOfEnrollmentReportQuery.CurrentEdOrgExitDateColumnName]),
                                CurrentGrade            = reader[ChangeOfEnrollmentReportQuery.CurrentGradeColumnName].ToString(),
                                PastDistEdOrgId         = System.Convert.ToInt32(reader[ChangeOfEnrollmentReportQuery.PastDistEdOrgIdColumnName]),
                                PastDistrictName        = reader[ChangeOfEnrollmentReportQuery.PastDistrictNameColumnName].ToString(),
                                PastSchoolEdOrgId       = System.Convert.ToInt32(reader[ChangeOfEnrollmentReportQuery.PastSchoolEdOrgIdColumnName]),
                                PastSchoolName          = reader[ChangeOfEnrollmentReportQuery.PastSchoolNameColumnName].ToString(),
                                PastEdOrgEnrollmentDate = (reader[ChangeOfEnrollmentReportQuery.PastEdOrgEnrollmentDateColumnName] is DBNull)
                                    ? (DateTime?)null
                                    : System.Convert.ToDateTime(reader[ChangeOfEnrollmentReportQuery.PastEdOrgEnrollmentDateColumnName]),
                                PastEdOrgExitDate = (reader[ChangeOfEnrollmentReportQuery.PastEdOrgExitDateColumnName] is DBNull)
                                    ? (DateTime?)null
                                    : System.Convert.ToDateTime(reader[ChangeOfEnrollmentReportQuery.PastEdOrgExitDateColumnName]),
                                PastGrade         = reader[ChangeOfEnrollmentReportQuery.PastGradeColumnName].ToString(),
                                StudentID         = reader[ChangeOfEnrollmentReportQuery.StudentIDColumnName].ToString(),
                                StudentLastName   = reader[ChangeOfEnrollmentReportQuery.StudentLastNameColumnName].ToString(),
                                StudentFirstName  = reader[ChangeOfEnrollmentReportQuery.StudentFirstNameColumnName].ToString(),
                                StudentMiddleName = reader[ChangeOfEnrollmentReportQuery.StudentMiddleNameColumnName].ToString(),
                                StudentBirthDate  = (reader[ChangeOfEnrollmentReportQuery.StudentBirthDateColumnName] is DBNull)
                                    ? (DateTime?)null
                                    : System.Convert.ToDateTime(reader[ChangeOfEnrollmentReportQuery.StudentBirthDateColumnName]),
                            });
                        }
                    }

                    return(reportData);
                }
                catch (Exception ex)
                {
                    LoggingService.LogErrorMessage($"While compiling report on changes of enrollment: {ex.ChainInnerExceptionMessages()}");
                    throw;
                }
                finally
                {
                    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch (Exception) { }
                    }
                }
            }
        }
Ejemplo n.º 13
0
 public List <StudentProgramsCountReportQuery> GetStudentProgramsCounts(int?districtEdOrgId, string fourDigitOdsDbYear)
 {
     using (var rawOdsContext = new RawOdsDbContext(fourDigitOdsDbYear))
     {
         var conn = rawOdsContext.Database.Connection;
         try
         {
             conn.Open();
             var studentProgsCmd = conn.CreateCommand();
             studentProgsCmd.CommandType = System.Data.CommandType.StoredProcedure;
             studentProgsCmd.CommandText = StudentProgramsCountReportQuery.StudentProgramsCountQuery;
             studentProgsCmd.Parameters.Add(new SqlParameter("@distid", System.Data.SqlDbType.Int));
             studentProgsCmd.Parameters["@distid"].Value = districtEdOrgId.HasValue ? (object)districtEdOrgId.Value : (object)DBNull.Value;
             var reportData = new List <StudentProgramsCountReportQuery>();
             using (var reader = studentProgsCmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     int?theEdOrgValue = null;
                     var edOrgIdObj    = reader[DemographicsCountReportQuery.EdOrgIdColumnName];
                     if (!(edOrgIdObj is DBNull))
                     {
                         theEdOrgValue = System.Convert.ToInt32(reader[DemographicsCountReportQuery.EdOrgIdColumnName]);
                     }
                     reportData.Add(new StudentProgramsCountReportQuery
                     {
                         OrgType                       = (OrgType)(System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.OrgTypeColumnName])),
                         EdOrgId                       = theEdOrgValue,
                         LEASchool                     = reader[StudentProgramsCountReportQuery.LEASchoolColumnName].ToString(),
                         DemographicsCount             = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.DistinctEnrollmentCountColumnName]),
                         DistinctEnrollmentCount       = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.DistinctEnrollmentCountColumnName]),
                         DistinctDemographicsCount     = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.DistinctDemographicsCountColumnName]),
                         ADParentCount                 = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.ADParentCountColumnName]),
                         IndianNativeCount             = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.IndianNativeCountColumnName]),
                         MigrantCount                  = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.MigrantCountColumnName]),
                         HomelessCount                 = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.HomelessCountColumnName]),
                         ImmigrantCount                = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.ImmigrantCountColumnName]),
                         EnglishLearnerIdentifiedCount = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.EnglishLearnerIdentifiedCountColumnName]),
                         EnglishLearnerServedCount     = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.EnglishLearnerServedCountColumnName]),
                         RecentEnglishCount            = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.RecentEnglishCountColumnName]),
                         SLIFECount                    = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.SLIFECountColumnName]),
                         IndependentStudyCount         = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.IndependentStudyCountColumnName]),
                         Section504Count               = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.Section504CountColumnName]),
                         Title1PartACount              = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.Title1PartACountColumnName]),
                         FreeReducedCount              = System.Convert.ToInt32(reader[StudentProgramsCountReportQuery.FreeReducedColumnName])
                     });
                 }
             }
             return(reportData);
         }
         catch (Exception ex)
         {
             LoggingService.LogErrorMessage($"While compiling report on student characteristics and programs: {ex.ChainInnerExceptionMessages()}");
             throw;
         }
         finally
         {
             if (conn != null && conn.State != System.Data.ConnectionState.Closed)
             {
                 try
                 {
                     conn.Close();
                 }
                 catch (Exception) { }
             }
         }
     }
 }