public void UpsertGcPreference(OrgGcPreference orgGc)
{
var existing = _context.OrgGcPreferences.FirstOrDefault(u => u.EducationOrganizationId == orgGc.EducationOrganizationId);
if (existing != null)
{
existing.GcUserEmail = orgGc.GcUserEmail;
existing.AllowExternalDomains = orgGc.AllowExternalDomains;
}
else
{
_context.OrgGcPreferences.Add(orgGc);
}
_context.SaveChanges();
}
public int GenerateSyncList(int leaId, short schoolYear, int schoolId)
{
var schoolCourses = new List <GcCourse>();
var schoolCourseUsers = new List <GcCourseUser>();
_apiContext.ChangeSchoolYear(schoolYear);
var category = _apiContext.Schools.Where(s => s.SchoolId == schoolId)
.SelectMany(s => s.SchoolCategories.Select(c => c.SchoolCategoryDescriptorId)).FirstOrDefault();
//class title rules
var classRules = _context.ProvisioningRules.Include(s => s.RosterLocalCourses)
.Where(r => r.EducationOrganizationId == leaId && r.SchoolYear == schoolYear && (r.TypeId == 1 || r.TypeId == 3) &&
(r.RosterSchools.Any(s => s.SchoolId == schoolId) ||
(!r.RosterSchools.Any() && (r.SchoolCategoryTypeId == category || r.SchoolCategoryTypeId == 0)))).ToList();
var sessions = classRules.Select(t => t.SessionName).ToList();
var pref = _context.OrgGcPreferences.FirstOrDefault(p => p.EducationOrganizationId == leaId);
var domain = pref.GcUserEmail.Substring(pref.GcUserEmail.IndexOf("@", StringComparison.CurrentCultureIgnoreCase));
var classPool = _apiContext.Sections.Where(s => s.StaffSectionAssociations.Any() &&
s.SchoolId == schoolId &&
sessions.Contains(s.CourseOffering.SessionName)
).Select(c => new
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = leaId,
LocalCourseTitle = c.CourseOffering.LocalCourseTitle,
StaffEmails = c.StaffSectionAssociations.SelectMany(e => e.Staff.StaffElectronicMails.Where(a => pref.AllowExternalDomains || a.ElectronicMailAddress.EndsWith(domain)).Select(a => a.ElectronicMailAddress)).ToList(),
StudentEmails = c.StudentSectionAssociations.SelectMany(ssa => ssa.Student.StudentEducationOrganizationAssociations.SelectMany(e => e.StudentEducationOrganizationAssociationElectronicMails.Where(a => pref.AllowExternalDomains || a.ElectronicMailAddress.EndsWith(domain)).Select(a => a.ElectronicMailAddress))).ToList()
}).ToList();
foreach (var rule in classRules)
{
if (rule.GroupByTitle == true)
{
if (rule.IncludeExclude.GetValueOrDefault())
{
var i = rule.RosterLocalCourses.Count != 0 ? classPool.Where(c => c.SessionName == rule.SessionName &&
rule.RosterLocalCourses.Any(t => t.LocalCourseCode.Trim() == c.LocalCourseCode.Trim()))
.GroupBy(c => new { c.LocalCourseTitle, c.LocalCourseCode, c.SessionName }).Select(g => new { g.Key, Emails = g.SelectMany(e => e.StaffEmails), StudentEmails = g.SelectMany(e => e.StudentEmails) })
: classPool.Where(c => c.SessionName == rule.SessionName)
.GroupBy(c => new { c.LocalCourseTitle, c.LocalCourseCode, c.SessionName }).Select(g => new { g.Key, Emails = g.SelectMany(e => e.StaffEmails), StudentEmails = g.SelectMany(e => e.StudentEmails) });
foreach (var course in i)
{
schoolCourses.Add(new GcCourse
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = course.Key.LocalCourseCode,
SectionIdentifier = course.Key.LocalCourseTitle,
SessionName = course.Key.SessionName,
EducationOrganizationId = leaId,
LocalCourseTitle = course.Key.LocalCourseTitle
});
if (rule.StaffOnly == true)
{
schoolCourseUsers.AddRange(course.Emails.Distinct().Select(e => new GcCourseUser
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = course.Key.LocalCourseCode,
SectionIdentifier = course.Key.LocalCourseTitle,
SessionName = course.Key.SessionName,
EducationOrganizationId = leaId,
EmailAddress = e,
IsTeacher = true
}));
}
else
{
schoolCourseUsers.AddRange(course.Emails.Distinct().Select(e => new GcCourseUser
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = course.Key.LocalCourseCode,
SectionIdentifier = course.Key.LocalCourseTitle,
SessionName = course.Key.SessionName,
EducationOrganizationId = leaId,
EmailAddress = e,
IsTeacher = true
}));
schoolCourseUsers.AddRange(course.StudentEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = course.Key.LocalCourseCode,
SectionIdentifier = course.Key.LocalCourseTitle,
SessionName = course.Key.SessionName,
EducationOrganizationId = leaId,
EmailAddress = e,
IsTeacher = false
}));
}
}
}
else
{
var j = classPool.Where(c => c.SessionName == rule.SessionName &&
rule.RosterLocalCourses.Any(t => t.LocalCourseCode.Trim() != c.LocalCourseCode.Trim()))
.GroupBy(c => new { c.LocalCourseTitle, c.LocalCourseCode, c.SessionName })
.Select(g => new { g.Key, Emails = g.SelectMany(e => e.StaffEmails), StudentEmails = g.SelectMany(e => e.StudentEmails) });
foreach (var c in j)
{
schoolCourses.Add(new GcCourse
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = c.Key.LocalCourseCode,
SectionIdentifier = c.Key.LocalCourseTitle,
SessionName = c.Key.SessionName,
EducationOrganizationId = leaId,
LocalCourseTitle = c.Key.LocalCourseTitle
});
if (rule.StaffOnly == true)
{
schoolCourseUsers.AddRange(c.Emails.Distinct().Select(e => new GcCourseUser
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = c.Key.LocalCourseCode,
SectionIdentifier = c.Key.LocalCourseTitle,
SessionName = c.Key.SessionName,
EducationOrganizationId = leaId,
EmailAddress = e,
IsTeacher = true
}));
}
else
{
schoolCourseUsers.AddRange(c.Emails.Distinct().Select(e => new GcCourseUser
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = c.Key.LocalCourseCode,
SectionIdentifier = c.Key.LocalCourseTitle,
SessionName = c.Key.SessionName,
EducationOrganizationId = leaId,
EmailAddress = e,
IsTeacher = true
}));
schoolCourseUsers.AddRange(c.StudentEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = schoolId,
SchoolYear = schoolYear,
LocalCourseCode = c.Key.LocalCourseCode,
SectionIdentifier = c.Key.LocalCourseTitle,
SessionName = c.Key.SessionName,
EducationOrganizationId = leaId,
EmailAddress = e,
IsTeacher = false
}));
}
}
}
}
else
{
if (rule.IncludeExclude.GetValueOrDefault())
{
var i = rule.RosterLocalCourses.Count != 0 ? classPool.Where(c => c.SessionName == rule.SessionName &&
rule.RosterLocalCourses.Any(t => t.LocalCourseCode.Trim() == c.LocalCourseCode.Trim())) : classPool.Where(c => c.SessionName == rule.SessionName);
foreach (var c in i)
{
schoolCourses.Add(new GcCourse
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
LocalCourseTitle = c.LocalCourseTitle,
});
if (rule.StaffOnly == true)
{
schoolCourseUsers.AddRange(c.StaffEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
EmailAddress = e,
IsTeacher = true
}));
}
else
{
schoolCourseUsers.AddRange(c.StaffEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
EmailAddress = e,
IsTeacher = true
}));
schoolCourseUsers.AddRange(c.StudentEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
EmailAddress = e,
IsTeacher = false
}));
}
}
}
else
{
var j = classPool.Where(c => c.SessionName == rule.SessionName &&
rule.RosterLocalCourses.Any(t => t.LocalCourseCode.Trim() != c.LocalCourseCode.Trim()));
foreach (var c in j)
{
schoolCourses.Add(new GcCourse
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
LocalCourseTitle = c.LocalCourseTitle,
});
if (rule.StaffOnly == true)
{
schoolCourseUsers.AddRange(c.StaffEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
EmailAddress = e,
IsTeacher = true
}));
}
else
{
schoolCourseUsers.AddRange(c.StaffEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
EmailAddress = e,
IsTeacher = true
}));
schoolCourseUsers.AddRange(c.StudentEmails.Distinct().Select(e => new GcCourseUser
{
SchoolId = c.SchoolId,
SchoolYear = c.SchoolYear,
LocalCourseCode = c.LocalCourseCode,
SectionIdentifier = c.SectionIdentifier,
SessionName = c.SessionName,
EducationOrganizationId = c.EducationOrganizationId,
EmailAddress = e,
IsTeacher = false
}));
}
}
}
}
}
//grade rules
var gradeRules = _context.ProvisioningRules.Include(s => s.RosterGradeLevels)
.Where(r => r.EducationOrganizationId == leaId && r.SchoolYear == schoolYear && r.TypeId == 2 &&
(r.RosterSchools.Any(s => s.SchoolId == schoolId) ||
(!r.RosterSchools.Any() &&
(r.SchoolCategoryTypeId == category || r.SchoolCategoryTypeId == 0)))).ToList();
var allSchools = gradeRules.Any(r => !r.RosterGradeLevels.Any());
var gradeIds = gradeRules.SelectMany(r => r.RosterGradeLevels.Select(g => g.GradeLevelDescriptorId)).ToList();
var schoolGrades = _apiContext.SchoolGradeLevels.Where(g => g.School.LocalEducationAgencyId == leaId && g.SchoolId == schoolId && (gradeIds.Any(r => g.GradeLevelDescriptorId == r) || allSchools))
.Select(c => new GcCourse
{
SchoolId = c.SchoolId,
SchoolYear = schoolYear,
LocalCourseCode = c.GradeLevelDescriptor.Descriptor.CodeValue,
SectionIdentifier = "Grade " + c.GradeLevelDescriptor.Descriptor.CodeValue,
LocalCourseTitle = "Grade " + c.GradeLevelDescriptor.Descriptor.CodeValue,
SessionName = "SY",
EducationOrganizationId = leaId
}).ToList();
if (schoolGrades.Any())
{
schoolCourses.AddRange(schoolGrades);
}
var existing = _context.GcCourses.Where(g => g.SchoolId == schoolId && g.SchoolYear == schoolYear).ToList();
var toDelete = existing.Where(a => !schoolCourses.Any(b => a.LocalCourseCode == b.LocalCourseCode && a.SectionIdentifier == b.SectionIdentifier && a.SessionName == b.SessionName)).ToList();
if (toDelete.Any())
{
_context.GcCourses.RemoveRange(toDelete);
}
var toAdd = schoolCourses.Where(a => !existing.Any(b => a.LocalCourseCode == b.LocalCourseCode && a.SectionIdentifier == b.SectionIdentifier && a.SessionName == b.SessionName)).ToList();
if (toAdd.Any())
{
_context.GcCourses.AddRange(toAdd);
}
var existingUsers = _context.GcCourseUsers.Where(g => g.SchoolId == schoolId && g.SchoolYear == schoolYear).ToList();
var toDeleteUsers = existingUsers.Where(a => !schoolCourseUsers.Any(b => a.LocalCourseCode == b.LocalCourseCode && a.SectionIdentifier == b.SectionIdentifier && a.SessionName == b.SessionName && a.EmailAddress == b.EmailAddress)).ToList();
if (toDeleteUsers.Any())
{
_context.GcCourseUsers.RemoveRange(toDeleteUsers);
}
var toAddUsers = schoolCourseUsers.Where(a => !existingUsers.Any(b => a.LocalCourseCode == b.LocalCourseCode && a.SectionIdentifier == b.SectionIdentifier && a.SessionName == b.SessionName && a.EmailAddress == b.EmailAddress)).ToList();
if (toAddUsers.Any())
{
_context.GcCourseUsers.AddRange(toAddUsers);
}
return(_context.SaveChanges());
}
