public List<Schedule> GetScheduleList(string year, string quarter, ref List<string> errors)
{
var conn = new SqlConnection(ConnectionString);
var scheduleList = new List<Schedule>();
try
{
var adapter = new SqlDataAdapter(GetScheduleListProcedure, conn);
if (year.Length > 0)
{
adapter.SelectCommand.Parameters.Add(new SqlParameter("@year", SqlDbType.Int));
adapter.SelectCommand.Parameters["@year"].Value = year;
}
if (quarter.Length > 0)
{
adapter.SelectCommand.Parameters.Add(new SqlParameter("@quarter", SqlDbType.VarChar, 25));
adapter.SelectCommand.Parameters["@quarter"].Value = quarter;
}
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
var dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count == 0)
{
return null;
}
for (var i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
var schedule = new Schedule
{
ScheduleId = Convert.ToInt32(dataSet.Tables[0].Rows[i]["schedule_id"].ToString()),
Year = dataSet.Tables[0].Rows[i]["year"].ToString(),
Quarter = dataSet.Tables[0].Rows[i]["quarter"].ToString(),
Session = dataSet.Tables[0].Rows[i]["session"].ToString(),
Course = new Course
{
CourseId = dataSet.Tables[0].Rows[i]["course_id"].ToString(),
Title = dataSet.Tables[0].Rows[i]["course_title"].ToString(),
Description = dataSet.Tables[0].Rows[i]["course_description"].ToString(),
}
};
scheduleList.Add(schedule);
}
}
catch (Exception e)
{
errors.Add("Error: " + e);
}
finally
{
conn.Dispose();
}
return scheduleList;
}
public void DeleteFromSchedule(Schedule schedule, ref List<string> errors)
{
var conn = new SqlConnection(ConnectionString);
try
{
var adapter = new SqlDataAdapter(DeleteFromScheduleProcedure, conn)
{
SelectCommand =
{
CommandType = CommandType.StoredProcedure
}
};
adapter.SelectCommand.Parameters.Add(new SqlParameter("@schedule_id", SqlDbType.VarChar, 10));
adapter.SelectCommand.Parameters["@schedule_id"].Value = schedule.ScheduleId;
var dataSet = new DataSet();
adapter.Fill(dataSet);
}
catch (Exception e)
{
errors.Add("Error: " + e);
}
finally
{
conn.Dispose();
}
}
public void AssignGradeErrorTest2()
{
//// Arrange
var errors = new List<string>();
Mock<IInstructorRepository> mockRepository = new Mock<IInstructorRepository>();
InstructorService iserv = new InstructorService(mockRepository.Object);
Schedule s = new Schedule { ScheduleId = 99, Year = "1000", Quarter = "Winter", Session = "2" };
iserv.AssignGrade(s, string.Empty, 1, string.Empty, ref errors);
Assert.AreEqual(1, errors.Count);
}
public void AddCourseToScheduleErrorTest3()
{
//// Arranage
var errors = new List<string>();
var mockRepository = new Mock<IScheduleRepository>();
var scheduleService = new ScheduleService(mockRepository.Object);
var schedule = new Schedule { };
//// Act
scheduleService.AddCourseToSchedule(schedule, ref errors);
//// Assert cant be null course object
Assert.AreEqual(1, errors.Count);
}
public void DeleteFromSchTest2()
{
//// Arrange
var errors = new List<String>();
var mockRepository = new Mock<IScheduleRepository>();
var scheduleService = new ScheduleService(mockRepository.Object);
var schedule = new Schedule { Quarter = string.Empty, Year = string.Empty };
//// Act
scheduleService.DeleteFromSchedule(schedule, ref errors);
//// Assert
Assert.AreEqual(5, errors.Count);
}
public string AddCourseToSchedule(Schedule schedule)
{
var errors = new List<string>();
var repository = new ScheduleRepository(this.entities);
var service = new ScheduleService(repository);
service.AddCourseToSchedule(schedule, ref errors);
if (errors.Count == 0)
{
return "ok";
}
return "error";
}
public string AssignGrade(Schedule schedule, string studentId, int instructorId, string grade)
{
var errors = new List<string>();
var repository = new InstructorRepository(this.entities);
var service = new InstructorService(repository);
service.AssignGrade(schedule, studentId, instructorId, grade, ref errors);
if (errors.Count == 0)
{
return "ok";
}
return "error";
}
public void AssignGrade()
{
//// Arrange
var errors = new List<string>();
Mock<IInstructorRepository> mockRepository = new Mock<IInstructorRepository>();
InstructorService iserv = new InstructorService(mockRepository.Object);
Schedule s = new Schedule { ScheduleId = 99, Year = "1000", Quarter = "Winter", Session = "2" };
mockRepository.Setup(x => x.AssignGradeToStudent(s, "1", 1, "A+", ref errors));
iserv.AssignGrade(s, "1", 1, "A+", ref errors);
mockRepository.Verify(x => x.AssignGradeToStudent(s, "1", 1, "A+", ref errors), Times.Once());
}
public void AddCourseToSchedule(Schedule schedule, ref List<string> errors)
{
if (schedule == null)
{
errors.Add("Invalid schedule");
throw new ArgumentException();
}
if (schedule.Day.DayId <= 0)
{
errors.Add("Invalid Day");
throw new ArgumentException();
}
if (schedule.Time.TimeId <= 0)
{
errors.Add("Invalid Time");
throw new ArgumentException();
}
if (schedule.Course.CourseId <= 0)
{
errors.Add("Invalid Course");
throw new ArgumentException();
}
if (schedule.Instructor.InstructorId <= 0)
{
errors.Add("Invalid Instructor");
throw new ArgumentException();
}
if (string.IsNullOrEmpty(schedule.Year))
{
errors.Add("Invalid year");
throw new ArgumentException();
}
if (string.IsNullOrEmpty(schedule.Quarter))
{
errors.Add("Invalid quarter");
throw new ArgumentException();
}
this.repository.AddCourseToSchedule(schedule, ref errors);
}
public void AddCourseScheduleTest()
{
//// Arranage
var errors = new List<string>();
Mock<IScheduleRepository> mockRepository = new Mock<IScheduleRepository>();
ScheduleService iserv = new ScheduleService(mockRepository.Object);
Schedule s = new Schedule { ScheduleId = 99, Year = "1000", Quarter = "Winter", Session = "2" };
mockRepository.Setup(x => x.AddCourseToSchedule(s, ref errors));
//// Act
////iserv.AddCourseToSchedule(s, ref errors);
//// Assert
////mockRepository.Verify(x => x.AddCourseToSchedule(s, ref errors), Times.Once());
}
public void AssignGradeToStudent(Schedule schedule, string studentId, int instructorId, string grade, ref List<string> errors)
{
var db_enrollment = new enrollment();
int scheduleId;
try
{
scheduleId = this.context.course_schedule.Where(
y => y.quarter == schedule.Quarter && y.year == int.Parse(schedule.Year) &&
y.instructor_id == instructorId).Select(x => x.schedule_id).First();
db_enrollment = this.context.enrollments.Where(x => x.student_id == studentId && x.schedule_id == scheduleId).First();
db_enrollment.grade = grade;
this.context.SaveChanges();
}
catch (Exception e)
{
errors.Add("Error occured in InstructorRepository.AssignGradeToStudent: " + e);
}
}
public void AddCourseToSchedule(Schedule schedule, ref List<string> errors)
{
course_schedule db_Schedule = new course_schedule();
try
{
db_Schedule.course_id = schedule.Course.CourseId;
db_Schedule.instructor_id = schedule.Instructor.InstructorId;
db_Schedule.schedule_day_id = schedule.Day.DayId;
db_Schedule.schedule_time_id = schedule.Time.TimeId;
db_Schedule.year = int.Parse(schedule.Year);
db_Schedule.quarter = schedule.Quarter;
db_Schedule.session = schedule.Session;
this.context.course_schedule.Add(db_Schedule);
this.context.SaveChanges();
}
catch (Exception e)
{
errors.Add("Error occured in ScheduleRepository.AddCourseToSchedule: " + e);
}
}
public void InsertToSchedule(Schedule schedule, ref List<string> errors)
{
if (schedule == null)
{
errors.Add("Schedule cannot be null");
throw new ArgumentException();
}
if (string.IsNullOrEmpty(schedule.Year))
{
errors.Add("Invalid year");
throw new ArgumentException();
}
if (string.IsNullOrEmpty(schedule.Quarter))
{
errors.Add("Invalid quarter");
throw new ArgumentException();
}
this.repository.InsertToSchedule(schedule, ref errors);
}
public void AssignGrade(Schedule schedule, string studentId, int instructorId, string grade, ref List<string> errors)
{
Regex notRealGrade = new Regex(@"[A-D][+-]?|F");
if (schedule == null)
{
errors.Add("Invalid instructorId");
throw new ArgumentException();
}
if (instructorId <= 0)
{
errors.Add("Invalid instructorId");
throw new ArgumentException();
}
if (string.IsNullOrEmpty(grade))
{
errors.Add("Must assign a grade");
throw new ArgumentException();
}
if (!notRealGrade.IsMatch(grade))
{
errors.Add("Invalid grade");
throw new ArgumentException();
}
if (schedule.ScheduleId <= 0)
{
errors.Add("Invalid schedule");
throw new ArgumentException();
}
this.repository.AssignGradeToStudent(schedule, studentId, instructorId, grade, ref errors);
}
public void UpdateCourseFromSchedule(Schedule schedule, ref List<string> errors)
{
course_schedule db_Schedule = new course_schedule();
try
{
db_Schedule = this.context.course_schedule.Find(schedule.ScheduleId);
db_Schedule.instructor_id = schedule.Instructor.InstructorId;
db_Schedule.schedule_day_id = schedule.Day.DayId;
db_Schedule.schedule_time_id = schedule.Time.TimeId;
db_Schedule.session = schedule.Session;
this.context.SaveChanges();
}
catch (Exception e)
{
errors.Add("Error occured in ScheduleRepository.AddCourseToSchedule: " + e);
}
}
public TaTutor GetTaTutorInfo(string ta_tutor_id, ref List<string> errors)
{
TaTutor tatutor = null;
var connection = new SqlConnection(ConnectionString);
try
{
var adapter = new SqlDataAdapter(GetTaScheduleByCourseProcedure, connection)
{
SelectCommand =
{
CommandType = CommandType.StoredProcedure
}
};
var dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count == 0)
{
return null;
}
tatutor = new TaTutor
{
TaTutorId = dataSet.Tables[0].Rows[0]["id"].ToString(),
FirstName = dataSet.Tables[0].Rows[0]["first"].ToString(),
LastName = dataSet.Tables[0].Rows[0]["last"].ToString(),
TaType = (TaType)
Enum.Parse(
typeof(TaType),
dataSet.Tables[0].Rows[0]["ta_type_id"].ToString())
};
if (dataSet.Tables[1].Rows.Count > 0)
{
tatutor.TaTutorSchedule = new List<Schedule>();
for (int i = 0; i < dataSet.Tables[1].Rows.Count; i++)
{
var schedule = new Schedule
{
ScheduleId = Convert.ToInt32(dataSet.Tables[1].Rows[i]["schedule_id"].ToString()),
Year = dataSet.Tables[1].Rows[i]["year"].ToString(),
Quarter = dataSet.Tables[1].Rows[i]["quarter"].ToString(),
Session = dataSet.Tables[1].Rows[i]["session"].ToString(),
Course = new Course
{
CourseId = dataSet.Tables[1].Rows[i]["course_id"].ToString(),
Title = dataSet.Tables[1].Rows[i]["course_title"].ToString(),
Description = dataSet.Tables[1].Rows[i]["course_description"].ToString(),
CourseLevel =
(CourseLevel)
Enum.Parse(
typeof(CourseLevel),
dataSet.Tables[1].Rows[i]["course_level"].ToString())
},
};
tatutor.TaTutorSchedule.Add(schedule);
}
}
}
catch (Exception e)
{
errors.Add("Errors: " + e);
}
finally
{
connection.Dispose();
}
return tatutor;
}
public Student FindStudentById(int student_id, ref List<string> errors)
{
student db_student;
Student pocoStudent = new Student();
try
{
db_student = this.context.students.Find(student_id);
pocoStudent.StudentId = db_student.student_id;
pocoStudent.FirstName = db_student.first_name;
pocoStudent.LastName = db_student.last_name;
pocoStudent.Password = db_student.password;
pocoStudent.Email = db_student.email;
pocoStudent.Enrolled = new List<Schedule>();
foreach (enrollment enrolledCourse in db_student.enrollments)
{
var pocoSchedule = new Schedule();
var db_Schedule = new course_schedule();
db_Schedule.schedule_id = enrolledCourse.schedule_id;
db_Schedule = this.context.course_schedule.Find(db_Schedule);
pocoSchedule.ScheduleId = db_Schedule.schedule_id;
pocoSchedule.Quarter = db_Schedule.quarter;
pocoSchedule.Year = db_Schedule.year.ToString();
pocoSchedule.Session = db_Schedule.session;
pocoStudent.Enrolled.Add(pocoSchedule);
}
}
catch (Exception e)
{
errors.Add("Error occured in StudentRepository.FindStudentById: " + e);
}
return pocoStudent;
}
public Student GetStudentDetail(string id, ref List<string> errors)
{
var conn = new SqlConnection(ConnectionString);
Student student = null;
try
{
var adapter = new SqlDataAdapter(GetStudentInfoProcedure, conn)
{
SelectCommand = { CommandType = CommandType.StoredProcedure }
};
adapter.SelectCommand.Parameters.Add(new SqlParameter("@student_id", SqlDbType.VarChar, 20));
adapter.SelectCommand.Parameters["@student_id"].Value = id;
var dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count == 0)
{
return null;
}
student = new Student
{
StudentId = dataSet.Tables[0].Rows[0]["student_id"].ToString(),
FirstName = dataSet.Tables[0].Rows[0]["first_name"].ToString(),
LastName = dataSet.Tables[0].Rows[0]["last_name"].ToString(),
Email = dataSet.Tables[0].Rows[0]["email"].ToString(),
Password = dataSet.Tables[0].Rows[0]["password"].ToString()
};
if (dataSet.Tables[1] != null)
{
student.Enrolled = new List<Schedule>();
for (var i = 0; i < dataSet.Tables[1].Rows.Count; i++)
{
var schedule = new Schedule();
var course = new Course
{
CourseId = int.Parse(dataSet.Tables[1].Rows[i]["course_id"].ToString()),
Title = dataSet.Tables[1].Rows[i]["course_title"].ToString(),
Description =
dataSet.Tables[1].Rows[i]["course_description"].ToString()
};
schedule.Course = course;
schedule.Quarter = dataSet.Tables[1].Rows[i]["quarter"].ToString();
schedule.Year = dataSet.Tables[1].Rows[i]["year"].ToString();
schedule.Session = dataSet.Tables[1].Rows[i]["session"].ToString();
schedule.ScheduleId = Convert.ToInt32(dataSet.Tables[1].Rows[i]["schedule_id"].ToString());
student.Enrolled.Add(schedule);
}
}
}
catch (Exception e)
{
errors.Add("Error occured in StudentRepository.GetStudentDetail: " + e);
}
finally
{
conn.Dispose();
}
return student;
}
public List<Schedule> getSchedule(ref List<string> errors)
{
var conn = new SqlConnection(ConnectionString);
var instructorList = new List<Schedule>();
try
{
var adapter = new SqlDataAdapter(GetScheduleListProcedure, conn)
{
SelectCommand =
{
CommandType = CommandType.StoredProcedure
}
};
var dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count == 0)
{
return null;
}
for (var i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
var instructor = new Schedule
{
ScheduleId = dataSet.Tables[0].Rows[i]["schedule_id"].ToString(),
Year = dataSet.Tables[0].Rows[i]["year"].ToString(),
Quarter = dataSet.Tables[0].Rows[i]["quarter"].ToString(),
Session = dataSet.Tables[0].Rows[i]["session"].ToString(),
courseId = dataSet.Tables[0].Rows[i]["course_id"].ToString(),
courseTitle = dataSet.Tables[0].Rows[i]["course_title"].ToString(),
courseDescription = dataSet.Tables[0].Rows[i]["course_description"].ToString(),
};
instructorList.Add(instructor);
}
}
catch (Exception e)
{
errors.Add("Error: " + e);
}
finally
{
conn.Dispose();
}
return instructorList;
}
public List<Schedule> GetInstructorSchedule(int instructorId, ref List<string> errors)
{
IEnumerable<course_schedule> db_List;
List<Schedule> pocoList = new List<Schedule>();
try
{
db_List = this.context.course_schedule.Include("schedule_day").Include("schedule_time").Include("instructor").Where(x => x.instructor_id == instructorId);
foreach (course_schedule c in db_List)
{
var day = new schedule_day();
var time = new schedule_time();
var instructor = new instructor();
var schedule = new Schedule
{
ScheduleId = c.schedule_id,
Year = c.year.ToString(),
Quarter = c.quarter,
Session = c.session,
Instructor = new Instructor
{
InstructorId = c.instructor.instructor_id,
FirstName = c.instructor.first_name,
LastName = c.instructor.last_name,
},
Day = new ScheduleDay
{
DayId = c.schedule_day.schedule_day_id,
Day = c.schedule_day.schedule_day1
},
Time = new ScheduleTime
{
TimeId = c.schedule_time.schedule_time_id,
Time = c.schedule_time.schedule_time1
},
Course = new Course
{
CourseId = c.course.course_id,
Title = c.course.course_title,
Description = c.course.course_description,
Level = c.course.course_level,
}
};
pocoList.Add(schedule);
}
}
catch (Exception e)
{
errors.Add("Error occured in ScheduleRepository.GetDays: " + e);
}
return pocoList;
}
public Schedule GetScheduleById(int scheduleId, ref List<string> errors)
{
Schedule pocoSchedule = new Schedule();
course_schedule db_Schedule;
try
{
db_Schedule = this.context.course_schedule.Find(scheduleId);
var day = new schedule_day();
var time = new schedule_time();
var instructor = new instructor();
day = this.context.schedule_day.Find((int)db_Schedule.schedule_day_id);
time = this.context.schedule_time.Find((int)db_Schedule.schedule_time_id);
instructor = this.context.instructors.Find((int)db_Schedule.instructor_id);
pocoSchedule.ScheduleId = db_Schedule.schedule_id;
pocoSchedule.Year = db_Schedule.year.ToString();
pocoSchedule.Quarter = db_Schedule.quarter;
pocoSchedule.Session = db_Schedule.session;
pocoSchedule.Instructor = new Instructor
{
InstructorId = instructor.instructor_id,
FirstName = instructor.first_name,
LastName = instructor.last_name,
};
pocoSchedule.Day = new ScheduleDay
{
DayId = day.schedule_day_id,
Day = day.schedule_day1
};
pocoSchedule.Time = new ScheduleTime
{
TimeId = time.schedule_time_id,
Time = time.schedule_time1
};
pocoSchedule.Course = new Course
{
CourseId = db_Schedule.course.course_id,
Title = db_Schedule.course.course_title,
Description = db_Schedule.course.course_description,
Level = db_Schedule.course.course_level,
};
}
catch (Exception e)
{
errors.Add("Error occured in ScheduleRepository.GetScheduleList: " + e);
}
return pocoSchedule;
}
public List<Schedule> GetScheduleList(string year, string quarter, ref List<string> errors)
{
var scheduleList = new List<Schedule>();
List<Schedule> pocoScheduleList = new List<Schedule>();
IEnumerable<course_schedule> db_ScheduleList;
try
{
var myYear = int.Parse(year);
db_ScheduleList = this.context.course_schedule.Include("schedule_day").Include("schedule_time").Include("instructor").Where(x => x.quarter == quarter && x.year == myYear);
foreach (course_schedule c in db_ScheduleList)
{
var day = new schedule_day();
var time = new schedule_time();
var instructor = new instructor();
var schedule = new Schedule
{
ScheduleId = c.schedule_id,
Year = c.year.ToString(),
Quarter = c.quarter,
Session = c.session,
Instructor = new Instructor
{
InstructorId = c.instructor.instructor_id,
FirstName = c.instructor.first_name,
LastName = c.instructor.last_name,
},
Day = new ScheduleDay
{
DayId = c.schedule_day.schedule_day_id,
Day = c.schedule_day.schedule_day1
},
Time = new ScheduleTime
{
TimeId = c.schedule_time.schedule_time_id,
Time = c.schedule_time.schedule_time1
},
Course = new Course
{
CourseId = c.course.course_id,
Title = c.course.course_title,
Description = c.course.course_description
}
};
scheduleList.Add(schedule);
}
}
catch (Exception e)
{
errors.Add("Error occured in ScheduleRepository.GetScheduleList: " + e);
}
return scheduleList;
}
public void UpdateSchedule(Schedule schedule, ref List<string> errors)
{
var conn = new SqlConnection(ConnectionString);
try
{
var adapter = new SqlDataAdapter(DeleteFromScheduleProcedure, conn)
{
SelectCommand =
{
CommandType = CommandType.StoredProcedure
}
};
adapter.SelectCommand.Parameters.Add(new SqlParameter("@schedule_id", SqlDbType.VarChar, 10));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@course_id", SqlDbType.VarChar, 10));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@year", SqlDbType.Int));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@quarter", SqlDbType.VarChar, 25));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@session", SqlDbType.VarChar, 3));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@day_id", SqlDbType.VarChar, 2));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@time_id", SqlDbType.VarChar, 2));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@instructor_id", SqlDbType.VarChar, 10));
adapter.SelectCommand.Parameters["@schedule_id"].Value = schedule.ScheduleId;
adapter.SelectCommand.Parameters["@course_id"].Value = schedule.Course.CourseId;
adapter.SelectCommand.Parameters["@year"].Value = schedule.Year;
adapter.SelectCommand.Parameters["@quarter"].Value = schedule.Quarter;
adapter.SelectCommand.Parameters["@session"].Value = schedule.Session;
adapter.SelectCommand.Parameters["@day_id"].Value = schedule.DayId;
adapter.SelectCommand.Parameters["@time_id"].Value = schedule.TimeId;
adapter.SelectCommand.Parameters["@instructor_id"].Value = schedule.Instructor.InstructorId;
var dataSet = new DataSet();
adapter.Fill(dataSet);
}
catch (Exception e)
{
errors.Add("Error: " + e);
}
finally
{
conn.Dispose();
}
}
