public static CSV Enrollment(int termId)
{
CSV csv = new CSV();
using (WebhostEntities db = new WebhostEntities())
{
Term term = db.Terms.Where(t => t.id == termId).Single();
List <Section> currentSections = term.Sections.Where(sec => sec.Course.goesToSchoology).ToList();
foreach (Section section in currentSections)
{
//Department Head
/*Dictionary<String, String> dept = new Dictionary<string, string>()
* {
* {"Course Code", Convert.ToString(section.CourseIndex)},
* {"Section Code", Convert.ToString(section.id)},
* {"User Unique ID", Convert.ToString(section.Course.Department.DeptHeadId)},
* {"Enrollment Type", enrl_teacher}
* };
*
* csv.Add(dept);*/
foreach (Faculty teacher in section.Teachers)
{
Dictionary <String, String> enr = new Dictionary <string, string>();
enr.Add("Course Code", Convert.ToString(section.CourseIndex));
enr.Add("Section Code", Convert.ToString(section.id));
enr.Add("User Unique ID", Convert.ToString(teacher.ID));
enr.Add("Enrollment Type", enrl_teacher);
enr.Add("Class Name", String.Format("[{0}] {1}", section.Block.LongName, section.Course.Name));
enr.Add("Name", String.Format("{0} {1}", teacher.FirstName, teacher.LastName));
csv.Add(enr);
}
foreach (Student student in section.Students)
{
Dictionary <String, String> enr = new Dictionary <string, string>();
enr.Add("Course Code", Convert.ToString(section.CourseIndex));
enr.Add("Section Code", Convert.ToString(section.id));
enr.Add("User Unique ID", Convert.ToString(student.ID));
enr.Add("Enrollment Type", enrl_student);
enr.Add("Class Name", String.Format("[{0}] {1}", section.Block.LongName, section.Course.Name));
enr.Add("Name", String.Format("{0} {1}", student.FirstName, student.LastName));
csv.Add(enr);
}
}
}
return(csv);
}
public static CSV AdvisorList(int termId)
{
/*
* Brad
* Erika
* Holly
* Sarah
* Eric
* Anne
* Jill
* emily cornell
*/
List <String> adv_admins = new List <String>(new string[] { "bbates", "erogers", "hmacy", "sdoenmez", "smcfall", "amackey", "jhutchins", "ecornell", "rbeauzay" });
CSV csv = new CSV();
using (WebhostEntities db = new WebhostEntities())
{
foreach (Student student in db.Students.Where(st => st.isActive).ToList())
{
Dictionary <String, String> row = new Dictionary <string, string>();
row.Add("StudentID", Convert.ToString(student.ID));
row.Add("AdvisorID", Convert.ToString(student.AdvisorID));
csv.Add(row);
foreach (String username in adv_admins)
{
Faculty teacher = db.Faculties.Where(t => t.UserName.Equals(username)).ToList().Single();
Dictionary <String, String> adm = new Dictionary <string, string>();
adm.Add("StudentID", Convert.ToString(student.ID));
adm.Add("AdvisorID", Convert.ToString(teacher.ID));
csv.Add(adm);
}
foreach (Section sec in student.Sections.Where(sec => sec.Terms.Where(t => t.id == termId).Count() > 0).ToList())
{
if (sec.Teachers.Count > 0 && (sec.Course.Name.Contains("Tutorial") || sec.Course.Name.Contains("Evening Assisted")))
{
Dictionary <String, String> tut = new Dictionary <string, string>();
tut.Add("StudentID", Convert.ToString(student.ID));
tut.Add("AdvisorID", Convert.ToString(sec.Teachers.FirstOrDefault().ID));
csv.Add(tut);
}
}
}
return(csv);
}
}
/// <summary>
/// courseHeaders = { "Course Name", "Department Name", "Course Code", "Section Name", "Section Code" }
/// </summary>
/// <param name="termId"></param>
/// <returns></returns>
public static CSV FullYearCourses(int termId)
{
CSV csv = new CSV();
using (WebhostEntities db = new WebhostEntities())
{
Term term = db.Terms.Where(t => t.id == termId).Single();
List <Section> currentSections = term.Sections.Where(sec => sec.Course.LengthInTerms == 3 && sec.Course.goesToSchoology).ToList();
foreach (Section sec in currentSections)
{
String TeachersNames = "";
bool first = true;
foreach (Faculty faculty in sec.Teachers.ToList())
{
TeachersNames += String.Format("{0}{1} {2}", first ? "" : ", ", faculty.FirstName, faculty.LastName);
first = false;
}
Dictionary <string, string> secdata = new Dictionary <string, string>();
secdata.Add("Course Name", sec.Course.Name);
secdata.Add("Department Name", sec.Course.Department.Name);
secdata.Add("Course Code", Convert.ToString(sec.CourseIndex));
secdata.Add("Section Name", String.Format("[{0}] {1}", sec.Block.LongName, TeachersNames));
secdata.Add("Section Code", Convert.ToString(sec.id));
csv.Add(secdata);
}
}
return(csv);
}
/// <summary>
/// Get a CSV containing all the entries of this CSV which do not correspond to entries in the Other CSV.
/// </summary>
/// <param name="Other"></param>
/// <returns></returns>
public CSV NotIn(CSV Other)
{
CSV newCSV = new CSV();
List <String> CommonKeys = new List <string>();
foreach (String key in AllKeys)
{
if (Other.AllKeys.Contains(key))
{
CommonKeys.Add(key);
}
}
foreach (Dictionary <String, String> row in _Data)
{
Dictionary <String, String> strippedRow = new Dictionary <string, string>();
foreach (String key in CommonKeys)
{
strippedRow.Add(key, row[key]);
}
if (!Other.Contains(strippedRow))
{
newCSV.Add(row);
}
}
return(newCSV);
}
/// <summary>
/// userColumns = { "First Name", "Last Name", "Username", "User Unique ID", "Grad Year", "Role" };
/// </summary>
/// <param name="academicYear"></param>
/// <returns></returns>
public static CSV Users(int academicYear)
{
CSV csv = new CSV();
using (WebhostEntities db = new WebhostEntities())
{
List <Student> activeStudents = db.Students.Where(st => st.Sections.Where(sec => sec.Course.AcademicYearID == academicYear).Count() > 0).ToList();
List <Faculty> activeFaculty = db.Faculties.Where(fac => fac.Sections.Where(sec => sec.Course.AcademicYearID == academicYear).Count() > 0).ToList();
foreach (Faculty faculty in activeFaculty)
{
Dictionary <String, String> tdata = new Dictionary <string, string>();
tdata.Add("First Name", faculty.FirstName);
tdata.Add("Last Name", faculty.LastName);
tdata.Add("User name", faculty.UserName);
tdata.Add("E-Mail", string.Format("{0}@dublinschool.org", faculty.UserName));
tdata.Add("User Unique ID", Convert.ToString(faculty.ID));
tdata.Add("Role", Convert.ToString(faculty.SchoologyRoleId));
/*if (faculty.ID != 67)
* tdata.Add("Role", usr_teacher);
* else
* tdata.Add("Role", usr_admin);
*/
csv.Add(tdata);
}
foreach (Student student in activeStudents)
{
Dictionary <String, String> tdata = new Dictionary <string, string>();
tdata.Add("First Name", student.FirstName);
tdata.Add("Last Name", student.LastName);
tdata.Add("User name", student.UserName);
tdata.Add("E-Mail", string.Format("{0}@dublinschool.org", student.UserName));
tdata.Add("User Unique ID", Convert.ToString(student.ID));
tdata.Add("Grad Year", Convert.ToString(student.GraduationYear));
tdata.Add("Role", usr_student);
csv.Add(tdata);
}
}
return(csv);
}
/// <summary>
/// Get a CSV file containing completed credit counts for each student in the given list.
/// Rows => Students
/// Columns => Credits
/// </summary>
/// <param name="studentIds">Default value is all active students.</param>
/// <returns></returns>
public static CSV GetStudentCredits(List <int> studentIds = null)
{
CSV output = new CSV();
using (WebhostEntities db = new WebhostEntities())
{
List <Student> students = new List <Student>();
if (studentIds == null)
{
students = db.Students.Where(s => s.isActive).OrderBy(s => s.GraduationYear).ThenBy(s => s.LastName).ThenBy(s => s.FirstName).ToList();
}
else
{
students = db.Students.Where(s => studentIds.Contains(s.ID)).OrderBy(s => s.GraduationYear).ThenBy(s => s.LastName).ThenBy(s => s.FirstName).ToList();
}
foreach (Student student in students)
{
Dictionary <String, float> credits = new Dictionary <string, float>();
foreach (GradeTableEntry credType in db.GradeTableEntries.Where(t => t.GradeTable.Name.Equals("Credit Types")).ToList())
{
if (!credits.ContainsKey(credType.Name))
{
credits.Add(credType.Name, 0f);
}
}
foreach (Credit credit in student.Credits.ToList())
{
credits[credit.CreditType.Name] += credit.CreditValue.Value / 9f;
}
Dictionary <String, String> row = new Dictionary <string, string>()
{
{ "First Name", student.FirstName },
{ "Last Name", student.LastName }
};
foreach (String credType in credits.Keys)
{
row.Add(credType, credits[credType].ToString());
}
output.Add(row);
}
}
return(output);
}
/// <summary>
/// Assigns Default Credit Values for all classes for all students. These credits may be modified later and will be passed over by this method if it is run again.
/// </summary>
/// <returns>CSV object containing all new credits which have been created.</returns>
public static CSV AssignDefaultCredits()
{
CSV output = new CSV();
using (WebhostEntities db = new WebhostEntities())
{
int newCreditId = db.Credits.Count() > 0? db.Credits.OrderBy(c => c.id).ToList().Last().id + 1 : 0;
foreach (AcademicYear acadYear in db.AcademicYears.ToList())
{
GradeTable CreditTypes;
GradeTable CreditValues;
try
{
CreditTypes = acadYear.GradeTables.Where(t => t.Name.Equals("Credit Types")).Single();
CreditValues = acadYear.GradeTables.Where(t => t.Name.Equals("Credit Values")).Single();
}
catch
{
continue;
}
foreach (Course course in acadYear.Courses.Where(c => c.Sections.Count > 0).ToList())
{
if (CreditTypes.GradeTableEntries.Where(t => course.Department.Name.Contains(t.Name)).Count() <= 0)
{
WebhostEventLog.Syslog.LogInformation("Skipping nonsense credit for {0}", course.Name);
continue;
}
foreach (Section section in course.Sections.Where(s => s.Students.Count > 0).ToList())
{
GradeTableEntry creditType = CreditTypes.GradeTableEntries.Where(t => course.Department.Name.Contains(t.Name)).Single();
GradeTableEntry creditValue = CreditValues.GradeTableEntries.Where(v => v.Value == course.LengthInTerms * 3).Single();
foreach (Student student in section.Students.ToList())
{
// if this student already has credit for this section, then don't do anything! it may be that the credit has been altered.
if (student.Credits.Where(c => c.Sections.Contains(section)).Count() <= 0)
{
WebhostEventLog.Syslog.LogInformation("Giving {0} {1} credit for [{2}] {3}", student.FirstName, student.LastName, section.Block.LongName, section.Course.Name);
Credit credit = new Credit()
{
id = newCreditId++,
StudentId = student.ID,
CreditTypeId = creditType.id,
CreditValueId = creditValue.id,
Student = student,
CreditType = creditType,
CreditValue = creditValue,
Notes = "This is a default credit."
};
credit.Sections.Add(section);
db.Credits.Add(credit);
Dictionary <String, String> row = new Dictionary <string, string>()
{
{ "Student", String.Format("{0} {1}", student.FirstName, student.LastName) },
{ "Credit Type", creditType.Name },
{ "Credit Value", creditValue.Name },
{ "Class", section.Course.Name },
{ "Year", acadYear.id.ToString() }
};
output.Add(row);
}
else
{
WebhostEventLog.Syslog.LogInformation("{0} {1} already has credit for {2}. Nothing to do.", student.FirstName, student.LastName, section.Course.Name);
}
}
}
}
}
db.SaveChanges();
}
return(output);
}
/// <summary>
/// Import Credits from Blackbaud.
/// Use the Query named for this in Blackbaud.
/// Returns a CSV contaning everything that was ignored or errored.
/// </summary>
/// <param name="creditCSV"></param>
/// <returns></returns>
public static CSV ImportCredits(CSV creditCSV)
{
CSV output = new CSV();
using (WebhostEntities db = new WebhostEntities())
{
int newCreditId = db.Credits.Count() > 0 ? db.Credits.OrderBy(c => c.id).Select(c => c.id).ToList().Last() + 1 : 0;
int year = DateRange.GetCurrentAcademicYear();
foreach (Dictionary <String, String> row in creditCSV.Data)
{
String note = String.Format("Imported {5} Credit for {0} taken {1} {2} - {3} {4}", row[Credit_CourseName], row[Credit_Term], row[Credit_AcademicYear], row[Credit_FirstName], row[Credit_LastName], row[Credit_Department]);
int studentId = Convert.ToInt32(row[Credit_StudentId]);
if (db.Students.Where(s => s.ID == studentId).Count() <= 0)
{
output.Add(new Dictionary <string, string>()
{
{ "Student", String.Format("{0} {1}", row[Credit_FirstName], row[Credit_LastName]) }, { "Error", "Not in Webhost" }
});
continue;
}
Student student = db.Students.Where(s => s.ID == studentId).Single();
if (student.Credits.Where(c => c.Notes.Equals(note)).Count() > 0)
{
output.Add(new Dictionary <string, string>()
{
{ "Student", String.Format("{0} {1}", row[Credit_FirstName], row[Credit_LastName]) }, { "Error", String.Format("{3} Credit already exists for {0} taken {1} {2}", row[Credit_CourseName], row[Credit_Term], row[Credit_AcademicYear], row[Credit_Department]) }
});
continue; // Don't re-import the same credit more than once!
}
GradeTable CreditTypes;
GradeTable CreditValues;
try
{
CreditTypes = db.GradeTables.Where(t => t.Name.Equals("Credit Types") && t.AcademicYearID == year).Single();
CreditValues = db.GradeTables.Where(t => t.Name.Equals("Credit Values") && t.AcademicYearID == year).Single();
}
catch
{
continue;
}
String dept = row[Credit_Department];
if (CreditTypes.GradeTableEntries.Where(t => dept.Contains(t.Name)).Count() <= 0)
{
output.Add(new Dictionary <string, string>()
{
{ "Student", String.Format("{0} {1}", row[Credit_FirstName], row[Credit_LastName]) }, { "Error", String.Format("{3} No credit given for {0} taken {1} {2}", row[Credit_CourseName], row[Credit_Term], row[Credit_AcademicYear], row[Credit_Department]) }
});
continue;
}
Credit credit = new Credit()
{
StudentId = studentId,
Notes = note,
CreditTypeId = CreditTypes.GradeTableEntries.Where(t => dept.Contains(t.Name)).Single().id,
CreditValueId = CreditValues.GradeTableEntries.Where(v => v.Value == 3).Single().id,
id = newCreditId++
};
db.Credits.Add(credit);
}
db.SaveChanges();
}
return(output);
}