public EdOrg GetEdOrgById(int edOrgId, int schoolYearId)
{
LoggingService.LogDebugMessage($"GetEdOrgById: '{edOrgId}', year {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 odsDbContext = SchoolYearDbContextFactory.CreateWithParameter(schoolYear.EndYear))
{
var conn = odsDbContext.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}): {ex.ChainInnerExceptionMessages()}");
}
finally
{
if (conn != null && conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
if (result != null)
{
validationPortalDataContext.EdOrgs.AddOrUpdate(result);
validationPortalDataContext.SaveChanges();
return(result);
}
throw new ApplicationException(
$"The Ed Org with ID# {edOrgId}, school year {schoolYear}, was not found.");
}
}
protected IEnumerable <string> GetFieldsForView(SchoolYear schoolYear, string schema, string name)
{
var fieldsForView = new List <string>();
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 COLUMN_NAME FROM information_schema.columns WHERE TABLE_SCHEMA=@schemaName and table_name = @viewName";
queryCommand.Parameters.Add(new SqlParameter("@schemaName", schema));
queryCommand.Parameters.Add(new SqlParameter("@viewName", name));
using (var reader = queryCommand.ExecuteReader())
{
while (reader.Read())
{
fieldsForView.Add(reader["COLUMN_NAME"].ToString());
}
}
}
return(fieldsForView);
}
public IList <dynamic> GetReportData(DynamicReportRequest request, int?districtId)
{
var reportDefinition = GetReportDefinition(request.ReportDefinitionId);
// todo: implement user ordering, this at least mimics the admin definition screen
var selectedFields = reportDefinition.Fields
.Where(x => request.SelectedFields.Contains(x.Id.ToString()))
.OrderBy(x => x.Field.Id)
.ToList();
var viewName = $"[{reportDefinition.RulesView.Schema}].[{reportDefinition.RulesView.Name}]";
var fieldNames = string.Join(", ", selectedFields.Select(x => $"[{x.Field.Name}]"));
var report = new List <dynamic>();
using (var schoolYearContext = SchoolYearDbContextFactory.CreateWithParameter(reportDefinition.SchoolYear.EndYear))
{
var connection = schoolYearContext.Database.Connection;
connection.Open();
var queryCommand = connection.CreateCommand();
queryCommand.CommandTimeout = 500;
queryCommand.CommandType = System.Data.CommandType.Text;
queryCommand.CommandText = $"SELECT {fieldNames} FROM {viewName}";
if (!reportDefinition.IsOrgLevelReport || districtId.HasValue)
// && selectedFields.Any(x => x.Field.Name.Equals("DistrictId", StringComparison.InvariantCultureIgnoreCase)))
{
queryCommand.CommandText += $" where [DistrictId] = {districtId}";
}
LoggingService.LogInfoMessage($"Executing dynamic report SQL: {queryCommand.CommandText}");
using (var reader = queryCommand.ExecuteReader())
{
while (reader.Read())
{
var dynamicObject = new ExpandoObject() as IDictionary <string, object>;
foreach (var field in selectedFields)
{
var fieldDescription = !string.IsNullOrWhiteSpace(field.Description) ? field.Description : field.Field.Name;
dynamicObject.Add(fieldDescription, reader[field.Field.Name].ToString());
}
report.Add(dynamicObject);
}
}
}
return(report);
}
public SingleEdOrgByIdQuery GetSingleEdOrg(int edOrgId, int schoolYearId)
{
SingleEdOrgByIdQuery result = null;
var schoolYear = SchoolYearService.GetSchoolYearById(schoolYearId);
using (var odsDbContext = SchoolYearDbContextFactory.CreateWithParameter(schoolYear.EndYear))
{
var conn = odsDbContext.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}): {ex.ChainInnerExceptionMessages()}");
}
finally
{
if (conn != null && conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
return(result);
}
public void RefreshEdOrgCache(SchoolYear schoolYear)
{
string fourDigitOdsDbYear = schoolYear.EndYear;
var edOrgsExtractedFromODS = new List <EdOrg>();
using (var odsDbContext = SchoolYearDbContextFactory.CreateWithParameter(fourDigitOdsDbYear))
{
var conn = odsDbContext.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)
{
conn.Close();
}
}
}
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
foreach (var singleEdOrg in edOrgsExtractedFromODS)
{
validationPortalDataContext.EdOrgs.AddOrUpdate(singleEdOrg);
}
LoggingService.LogDebugMessage("EdOrgCache: saving changes");
validationPortalDataContext.SaveChanges();
}
}
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();
}
}
