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) { }
}
}
}
}
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) { }
}
}
}
}
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) { }
}
}
}
}
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) { }
}
}
}
}
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);
}
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();
}
}
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
}
}
}
}
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.");
}
}
/// <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()}");
}
}
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);
}
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.");
}
}
}
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) { }
}
}
}
}
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) { }
}
}
}
}
