/// <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); }