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.");
}
}
public void UpdateFocusedEdOrg(string newFocusedEdOrgId)
{
LoggingService.LogDebugMessage($"Attempting to update focused Ed Org ID to {newFocusedEdOrgId}.");
try
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
var desiredEdOrgId = int.Parse(newFocusedEdOrgId);
var sessionObj = GetSession();
if (sessionObj?.UserIdentity.AuthorizedEdOrgs.FirstOrDefault(eo => eo.Id == desiredEdOrgId) == null)
{
return; // todo: why silently fail?
}
validationPortalDataContext.AppUserSessions
.First(x => x.Id == sessionObj.Id)
.FocusedEdOrgId = desiredEdOrgId;
validationPortalDataContext.SaveChanges();
}
}
catch (Exception ex)
{
LoggingService.LogErrorMessage($"An error occurred when updating focused Ed Org ID to {newFocusedEdOrgId}: {ex.ChainInnerExceptionMessages()}");
}
}
public List <EdOrg> GetAllEdOrgs()
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
return(validationPortalDataContext.EdOrgs.ToList());
}
}
public IEnumerable <SubmissionCycle> GetSubmissionCyclesOpenToday()
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
var submissionCycles = validationPortalDataContext.SubmissionCycles.ToList();
var submissionCyclesOpenToday = submissionCycles
.Where(s =>
s.StartDate.Date <= DateTime.Now.Date &&
s.EndDate.Date >= DateTime.Now.Date)
.ToList();
foreach (var submissionCycle in submissionCyclesOpenToday)
{
var schoolYear =
validationPortalDataContext.SchoolYears.FirstOrDefault(
x => x.Id == submissionCycle.SchoolYearId);
if (schoolYear != null)
{
submissionCycle.SchoolYearDisplay = schoolYear.ToString();
}
}
return(submissionCyclesOpenToday);
}
}
public void UpdateFocusedSchoolYear(int newFocusedSchoolYearId)
{
LoggingService.LogDebugMessage($"Attempting to update focused School Year ID to {newFocusedSchoolYearId.ToString()}.");
try
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
var sessionObj = GetSession();
// todo: null ref
int?validSchoolYearId = validationPortalDataContext.SchoolYears
.FirstOrDefault(sy => sy.Enabled && sy.Id == newFocusedSchoolYearId)?.Id;
if (sessionObj == null || !validSchoolYearId.HasValue)
{
LoggingService.LogErrorMessage("Unable to set an invalid school year");
return; // todo: another silent fail!
}
validationPortalDataContext.AppUserSessions
.First(x => x.Id == sessionObj.Id)
.FocusedSchoolYearId = validSchoolYearId.Value;
validationPortalDataContext.SaveChanges();
}
}
catch (Exception ex)
{
LoggingService.LogErrorMessage($"An error occurred when updating focused School Year ID to {newFocusedSchoolYearId.ToString()}: {ex.ChainInnerExceptionMessages()}");
}
}
public SubmissionCycle GetSubmissionCycle(int id)
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
return(validationPortalDataContext.SubmissionCycles
.FirstOrDefault(submissionCycle => submissionCycle.Id == id));
}
}
public void SaveReportDefinition(DynamicReportDefinition reportDefinition)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
validationPortalContext.DynamicReportDefinitions.AddOrUpdate(reportDefinition);
validationPortalContext.SaveChanges();
}
}
public SubmissionCycle SchoolYearCollectionAlreadyExists(SubmissionCycle submissionCycle)
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
var duplicateCycle = validationPortalDataContext.SubmissionCycles
.FirstOrDefault(x => x.SchoolYearId == submissionCycle.SchoolYearId && x.CollectionId == submissionCycle.CollectionId);
return(duplicateCycle);
}
}
public bool AddSubmissionCycle(SubmissionCycle submissionCycle)
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
validationPortalDataContext.SubmissionCycles.Add(submissionCycle);
validationPortalDataContext.SaveChanges();
return(true); // why have a return type at all? todo
}
}
public IEnumerable <ValidationRulesView> GetRulesViews(int schoolYearId)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
return(validationPortalContext.ValidationRulesViews
.Include(x => x.RulesFields)
.Where(x => x.SchoolYearId == schoolYearId)
.ToList());
}
}
public DynamicReportDefinition GetReportDefinition(int id)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
return(validationPortalContext.DynamicReportDefinitions
.Include(x => x.RulesView)
.Include(x => x.Fields)
.Include(x => x.Fields.Select(y => y.Field))
.Include(x => x.SchoolYear)
.Single(x => x.Id == id));
}
}
public IEnumerable <DynamicReportDefinition> GetReportDefinitions()
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
return(validationPortalContext.DynamicReportDefinitions
.Include(x => x.Fields)
.Include(x => x.Fields.Select(y => y.Field))
.Include(x => x.SchoolYear)
.Include(x => x.RulesView)
.ToList());
}
}
public void DeleteViewsAndRulesForSchoolYear(int schoolYearId)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
var existingViews = validationPortalContext.ValidationRulesViews
.Include(x => x.RulesFields)
.Where(x => x.SchoolYearId == schoolYearId);
validationPortalContext.ValidationRulesViews.RemoveRange(existingViews);
validationPortalContext.SaveChanges();
}
}
public IList <SubmissionCycle> GetSubmissionCyclesByCollectionId(string collectionId)
{
if (collectionId == null)
{
return(null);
}
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
return(validationPortalDataContext.SubmissionCycles
.Where(submissionCycle => submissionCycle.CollectionId == collectionId)
.ToList());
}
}
public void DeleteSubmissionCycle(int id)
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
var submissionCycle = validationPortalDataContext.SubmissionCycles.FirstOrDefault(a => a.Id == id);
if (submissionCycle == null)
{
throw new Exception(
$"Could not delete a collection cycle because collection cycle with ID {id} was not found");
}
validationPortalDataContext.SubmissionCycles.Remove(submissionCycle);
validationPortalDataContext.SaveChanges();
}
}
public void DisableReportDefinition(int id)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
var report = validationPortalContext.DynamicReportDefinitions
.SingleOrDefault(x => x.Id == id);
if (report == null)
{
throw new InvalidOperationException($"Unable to find a report definition with id {id}");
}
report.Enabled = false;
validationPortalContext.SaveChanges();
}
}
public IList <SubmissionCycle> GetSubmissionCycles()
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
foreach (var submissionCycle in validationPortalDataContext.SubmissionCycles)
{
var schoolYear = validationPortalDataContext.SchoolYears.FirstOrDefault(x => x.Id == submissionCycle.SchoolYearId);
if (schoolYear != null)
{
submissionCycle.SchoolYearDisplay = schoolYear.ToString();
}
}
return(validationPortalDataContext.SubmissionCycles.ToList());
}
}
public bool AddSubmissionCycle(string collectionId, DateTime startDate, DateTime endDate)
{
if (collectionId == null)
{
// todo: better null/invalid argument handling
return(false);
}
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
var newSubmissionCycle = new SubmissionCycle(collectionId, startDate, endDate);
validationPortalDataContext.SubmissionCycles.Add(newSubmissionCycle);
validationPortalDataContext.SaveChanges();
return(true);
}
}
public void SaveSubmissionCycle(SubmissionCycle submissionCycle)
{
try
{
using (var validationPortalDataContext = ValidationPortalDataContextFactory.Create())
{
if (submissionCycle == null)
{
throw new Exception("Attempted to save a null SubmissionCycle.");
}
if (submissionCycle.Id == 0)
{
validationPortalDataContext.SubmissionCycles.Add(submissionCycle);
validationPortalDataContext.SaveChanges();
}
else
{
var existingSubmissionCycle = validationPortalDataContext.SubmissionCycles
.FirstOrDefault(s => s.Id == submissionCycle.Id);
if (existingSubmissionCycle == null)
{
throw new Exception($"SubmissionCycle with id {submissionCycle.Id} does not exist.");
}
existingSubmissionCycle.CollectionId = submissionCycle.CollectionId;
existingSubmissionCycle.StartDate = submissionCycle.StartDate;
existingSubmissionCycle.EndDate = submissionCycle.EndDate;
existingSubmissionCycle.SchoolYearId = submissionCycle.SchoolYearId;
validationPortalDataContext.SaveChanges();
}
}
}
catch (Exception ex)
{
string strMessage = string.Empty;
if (submissionCycle != null)
{
strMessage = $" with id = { submissionCycle.Id }, StartDate = { submissionCycle.StartDate }, EndDate = { submissionCycle.EndDate}, SchoolYearID = { submissionCycle.SchoolYearId }";
}
LoggingService.LogErrorMessage($"An error occurred while saving announcement {strMessage}: {ex.ChainInnerExceptionMessages()}");
throw new Exception("An error occurred while saving announcement.");
}
}
public void DeleteReportDefinition(int id)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
var report = validationPortalContext.DynamicReportDefinitions
.Include(x => x.Fields)
.SingleOrDefault(x => x.Id == id);
if (report == null)
{
throw new InvalidOperationException($"Unable to find a report definition with id {id}");
}
validationPortalContext.DynamicReportFields.RemoveRange(report.Fields);
validationPortalContext.DynamicReportDefinitions.Remove(report);
validationPortalContext.SaveChanges();
}
}
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 void UpdateReportDefinition(DynamicReportDefinition newReportDefinition)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
var existingReportDefinition = validationPortalContext.DynamicReportDefinitions
.Include(x => x.Fields)
.Single(x => x.Id == newReportDefinition.Id);
existingReportDefinition.Name = newReportDefinition.Name?.Trim();
existingReportDefinition.Description = newReportDefinition.Description?.Trim();
existingReportDefinition.IsOrgLevelReport = newReportDefinition.IsOrgLevelReport;
foreach (var newField in newReportDefinition.Fields)
{
var existingField = existingReportDefinition.Fields.Single(x => x.Id == newField.Id);
existingField.Enabled = newField.Enabled;
existingField.Description = newField.Description?.Trim();
}
validationPortalContext.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();
}
}