public List <Schedule> GetCurrentCourseSchedules(TeacherCourse aTeacherCourse)
{
try
{
int scheduleStatus = 0;
List <Schedule> schedules = new List <Schedule>();
connection.Open();
string query = "SELECT dayId,startTime,endingTime FROM t_ScheduleClass WHERE CourseId=@courseId and ScheduleStatus=@status";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@courseId", aTeacherCourse.CourseId);
command.Parameters.AddWithValue("@status", scheduleStatus);
SqlDataReader teacherCourseScheduleReader = command.ExecuteReader();
while (teacherCourseScheduleReader.Read())
{
Schedule aSchedule = new Schedule();
aSchedule.DayId = Convert.ToInt16(teacherCourseScheduleReader[0].ToString());
aSchedule.StartTime = float.Parse(teacherCourseScheduleReader[1].ToString());
aSchedule.EndingTime = float.Parse(teacherCourseScheduleReader[2].ToString());
schedules.Add(aSchedule);
}
return(schedules);
}
finally
{
connection.Close();
}
}
private void 课程查询ToolStripMenuItem_Click(object sender, EventArgs e)
{
TeacherCourse teacherCourse = new TeacherCourse();
teacherCourse.MdiParent = this;
teacherCourse.Show();
}
public ActionResult AssignTeacher(AssignTeacherViewModel model)
{
if (ModelState.IsValid)
{
try
{
TeacherCourse tc = new TeacherCourse
{
TeacherId = model.TeacherId,
CourseId = model.CourseId,
Year = DateTime.Now.Year.ToString()
};
context.TeacherCourses.Add(tc);
context.SaveChanges();
TempData["SuccessMessage"] = "Teacher has been assigned.";
}
catch (Exception ex)
{
TempData["ErrorMessage"] = ex.Message;
}
}
return(RedirectToAction("Teachers"));
}
public async Task <IActionResult> Edit(int id, [Bind("TeacherCourseId,TcrId,CrsId")] TeacherCourse teacherCourse)
{
if (id != teacherCourse.TeacherCourseId)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(teacherCourse);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!TeacherCourseExists(teacherCourse.TeacherCourseId))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
ViewData["CrsId"] = new SelectList(_context.Course, "CrsId", "CourseId", teacherCourse.CrsId);
ViewData["TcrId"] = new SelectList(_context.Teacher, "TcrId", "TeacherId", teacherCourse.TcrId);
return(View(teacherCourse));
}
public IActionResult Create([FromBody] TeacherCourse model)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
bool isInRole = _usersRepository.IsInRole(model.UserId, UserRoles.Teacher).GetAwaiter().GetResult();
if (!isInRole)
{
return(NotFound());
}
var record = _mapper.Map <Domain.Models.TeacherCourses>(model);
bool exists = _teacherCoursesRepository.Exists(record);
if (exists)
{
return(Conflict(ResponseMessage.TeacherCourseAlreadyExists));
}
_teacherCoursesRepository.Add(record);
return(Ok());
}
public IActionResult Edit([FromBody] TeacherCourse model, int id)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var record = _teacherCoursesRepository.GetById(id);
if (record == null)
{
return(NotFound());
}
record = _mapper.Map(model, record);
bool exists = _teacherCoursesRepository.Exists(record, id);
if (exists)
{
return(Conflict(ResponseMessage.TeacherCourseAlreadyExists));
}
_teacherCoursesRepository.Update(record);
return(Ok());
}
public IActionResult TeacherCreateWithCourse(TeacherCreateViewModel model)
{
if (ModelState.IsValid)
{
Teacher newTeacher = new Teacher()
{
TeacherName = model.TeacherName,
};
DbContext.Teachers.Add(newTeacher);
DbContext.SaveChanges();
foreach (var selectedCourse in model.SelectedCourse.Where(s => s.isSelect))
{
var teacherCourse = new TeacherCourse()
{
CourseId = selectedCourse.CourseId,
TeacherId = newTeacher.TeacherId
};
DbContext.teacherCourses.Add(teacherCourse);
DbContext.SaveChanges();
}
return(RedirectToAction("Index"));
}
return(View(model));
}
public static void PostOne(TeacherViewModel teacherModel)
{
using (var context = new FinalSchool())
{
var teacher = new Teacher()
{
TeacherId = teacherModel.TeacherSNN,
FName = teacherModel.FName,
Age = teacherModel.Age,
City = teacherModel.City,
Street = teacherModel.Street,
LName = teacherModel.LName,
Email = teacherModel.Email,
Gender = teacherModel.Gender,
Password = teacherModel.Password,
Phone = teacherModel.Phone,
};
context.Teachers.Add(teacher);
foreach (var item in teacherModel.CourseName)
{
var CourseId = context.Courses.FirstOrDefault(w => w.Name == item).CourseId;
var teacherCourse = new TeacherCourse()
{
TeacherId = teacher.TeacherId,
CourseId = CourseId
};
context.TeacherCourses.Add(teacherCourse);
}
context.SaveChanges();
}
}
public async Task <IHttpActionResult> PutTeacherCourse(int id, TeacherCourse teacherCourse)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != teacherCourse.TeacherCourseId)
{
return(BadRequest());
}
db.Entry(teacherCourse).State = EntityState.Modified;
try
{
await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!TeacherCourseExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(StatusCode(HttpStatusCode.NoContent));
}
private void UpdateTeacherCredit(TeacherCourse aTeacherCourse, float credit)
{
try
{
Teacher aTeacher = new Teacher();
aTeacher.TeacherId = aTeacherCourse.TeacherId;
aTeacher.ADepartment = new Department();
aTeacher.ADepartment.DepartmentId = aTeacherCourse.DepartmentId;
aTeacher = GetTeacherCreditInfo(aTeacher);
aTeacher.AssignCredit = Convert.ToDouble(aTeacher.AssignCredit) + credit;
aTeacher.RemaningCredit = Convert.ToDouble(aTeacher.RemaningCredit) - credit;
connection.Open();
string query = "UPDATE t_TeacherCredit SET AssignCredit =@assignCredit,RemainingCredit=@remainingCredit Where teacherId =@teacherId";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@assignCredit", aTeacher.AssignCredit);
command.Parameters.AddWithValue("@remainingCredit", aTeacher.RemaningCredit);
command.Parameters.AddWithValue("@teacherId", aTeacherCourse.TeacherId);
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
private bool CheckTeacherTime(TeacherCourse aTeacherCourse)
{
aTeacherGateway = new TeacherGateway();
ScheduleGateway aScheduleGateway = new ScheduleGateway();
List <int> coursesId = new List <int>();
coursesId = aTeacherGateway.GetTeacherAssignCoursesId(aTeacherCourse);
List <Schedule> schedules = new List <Schedule>();
schedules = aScheduleGateway.GetTeacherAllSchedules(coursesId);
List <Schedule> currentScheduleList = new List <Schedule>();
currentScheduleList = aScheduleGateway.GetCurrentCourseSchedules(aTeacherCourse);
foreach (Schedule schedule in currentScheduleList)
{
foreach (Schedule aSchedule in schedules)
{
if (schedule.DayId == aSchedule.DayId && ((schedule.StartTime <= aSchedule.StartTime && schedule.EndingTime >= aSchedule.StartTime) || (schedule.StartTime <= aSchedule.EndingTime & schedule.EndingTime >= aSchedule.EndingTime)))
{
return(true);
}
}
}
return(false);
}
public TeacherCourse SelectById(TeacherCourse model)
{
//修改
string sql = "select * from teacher_course where id=@id";
SQLiteParameter[] ps =
{
new SQLiteParameter("id", model.id)
};
DataTable table = SqliteHelper.Select(sql, ps);
//修改
List <TeacherCourse> list = new List <TeacherCourse>();
//遍历数据表，将数据转存到集合中
foreach (DataRow row in table.Rows)
{
//修改
list.Add(new TeacherCourse()
{
id = Convert.ToInt32(row["id"]),
teacher_id = Convert.ToInt32(row["teacher_id"]),
course_id = Convert.ToInt32(row["course_id"]),
add_time = Convert.ToDateTime(row["add_time"]),
update_time = Convert.ToDateTime(row["update_time"]),
deleted = Convert.ToInt32(row["deleted"])
});
}
//修改
TeacherCourse model2 = list[0];
return(model2);
}
//查看老师课表
public List <TeacherCourse> SelectTeacherCourse(TeacherCourse tchCourse)
{
TeacherCourse tchCourse = tchCourseDal.SelectById();
list <Course> searchedCour = new list <Course>();
searchedCour.Add(tchCourse);
return(searchedCour);
}
public async System.Threading.Tasks.Task AddTeacher_ToCourse_TestAsync()
{
var options = new DbContextOptionsBuilder <OlpContext>()
.UseInMemoryDatabase(databaseName: "Add_writes_to_database_TeacherCourses")
.Options;
// Run the test against one instance of the context
using (var context = new OlpContext(options))
{
Teacher teacher = new Domain.Teacher()
{
FirstName = "Test Name",
LastName = "Test last name",
Email = "test email",
};
var teacherService = new TeacherService(context);
await teacherService.Create(teacher);
Teacher fecthedTeacher = await teacherService.FindBy(x => x.FirstName == teacher.FirstName);
Assert.AreEqual(fecthedTeacher.FirstName, teacher.FirstName);
Course course = new Domain.Course()
{
Name = "Test course name",
Description = "test course desc",
};
var courseService = new CourseService(context);
await courseService.Create(course);
Course fecthedCourse = await courseService.FindBy(x => x.Name == course.Name);
Assert.AreEqual(fecthedCourse.Name, course.Name);
//teacher.TeacherCourses.Add(course);
TeacherCourse teacherCourse = new TeacherCourse()
{
Course = course,
Teacher = teacher
};
context.Add(teacherCourse);
context.SaveChanges();
TeacherCourse fecthedTeacherCourse = context.TeacherCourses.FirstOrDefaultAsync().Result;
Assert.AreEqual(fecthedTeacherCourse.Teacher.UserId, teacher.UserId);
fecthedCourse = await courseService.FindBy(x => x.Name == course.Name);
Assert.AreEqual(fecthedCourse.TeacherCourses.Count, 1);
//Assert.AreEqual(teacher.TeacherCourses, teacherCourse.Course);
}
}
public async Task <bool> AddTeacherToCourseAsync(TeacherCourse course)
{
context.TeacherCourses.Add(course);
if (await context.SaveChangesAsync() == 1)
{
return(true);
}
return(false);
}
// GET: TeacherCourses
public ActionResult Index()
{
String sql = "SELECT * FROM teachercourses f, Courses co, teachers s where f.courseid = co.courseid and f.teacherid = s.teacherid";
db.List(sql);
var dt = db.List(sql);
var model = new TeacherCourse().List(dt);
return(View(model));
}
public async Task <IHttpActionResult> GetTeacherCourse(int id)
{
TeacherCourse teacherCourse = await db.TeacherCourses.FindAsync(id);
if (teacherCourse == null)
{
return(NotFound());
}
return(Ok(teacherCourse));
}
public async Task <IHttpActionResult> PostTeacherCourse(TeacherCourse teacherCourse)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
db.TeacherCourses.Add(teacherCourse);
await db.SaveChangesAsync();
return(CreatedAtRoute("DefaultApi", new { id = teacherCourse.TeacherCourseId }, teacherCourse));
}
public int Update(TeacherCourse model)
{
if (SelectById(model).deleted == 1)
{
return(-2);
}
List <Object> obj = updateSQL(model);
string sql = obj[0].ToString();
List <SQLiteParameter> ps = (List <SQLiteParameter>)obj[1];
return(SqliteHelper.update(sql, ps.ToArray()));
}
public async Task <bool> RemoveTeacherFromCourse(TeacherCourse course)
{
TeacherCourse teacherCourse = context.TeacherCourses
.FirstOrDefault(c => c.CourseId == course.CourseId && c.TeacherId == course.TeacherId);
context.Remove(teacherCourse);
if (await context.SaveChangesAsync() == 1)
{
return(true);
}
return(false);
}
public List <TeacherCourse> GetTeacherCourses(int tid)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand())
{
var teacherCourses = new List <TeacherCourse>();
command.Connection = connection;
command.CommandText = "select * from courseslist(@tid)";
command.Parameters.AddWithValue("@tid", tid);
var reader = command.ExecuteReader();
while (reader.Read())
{
var teacherCourse = new TeacherCourse()
{
Teacher = new Teacher()
{
Id = tid
},
Course = new Course()
{
Id = int.Parse(reader["cid"].ToString()),
CourseName = reader["course_name"].ToString(),
CourseCode = reader["course_code"].ToString()
},
Classes = new List <Class>()
};
using (var classConnection = SqlConnectionManager.GetConnection())
using (var classCommand = new NpgsqlCommand())
{
classCommand.Connection = classConnection;
classCommand.CommandText = "select * from getClassesByTeacherAndCourse(@tid, @cid)";
classCommand.Parameters.AddWithValue("@tid", tid);
classCommand.Parameters.AddWithValue("@cid", teacherCourse.Course.Id);
var classReader = classCommand.ExecuteReader();
while (classReader.Read())
{
teacherCourse.Classes.Add(new Class()
{
Id = int.Parse(classReader["id"].ToString()),
Branch = classReader["branch"].ToString(),
Year = int.Parse(classReader["year"].ToString()),
SectionFrom = int.Parse(classReader["sec_from"].ToString()),
SectionTo = int.Parse(classReader["sec_to"].ToString())
});
}
}
teacherCourses.Add(teacherCourse);
}
return(teacherCourses);
}
}
public TeacherCourse GetTeacherCourseCreditInfoByTeacherId(int teacherId)
{
decimal totalTakenCredit = GetTotalTakenCredit(teacherId);
decimal creditToBeTaken = GetCreditToBeTaken(teacherId);
TeacherCourse teacherCourse = new TeacherCourse
{
RemainingCredit = creditToBeTaken - totalTakenCredit,
CreditToBeTaken = creditToBeTaken
};
return(teacherCourse);
}
public ActionResult Create([Bind(Include = "TeacherID,CourseID")] TeacherCourse teacherCourse)
{
if (ModelState.IsValid)
{
db.TeacherCourses.Add(teacherCourse);
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.CourseID = new SelectList(db.Courses, "CourseID", "CourseName", teacherCourse.CourseID);
ViewBag.TeacherID = new SelectList(db.Teachers, "TeacherID", "TeacherName", teacherCourse.TeacherID);
return(View(teacherCourse));
}
public async Task <IActionResult> Create([Bind("TeacherCourseId,TcrId,CrsId")] TeacherCourse teacherCourse)
{
if (ModelState.IsValid)
{
_context.Add(teacherCourse);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
ViewData["CrsId"] = new SelectList(_context.Course, "CrsId", "CourseId", teacherCourse.CrsId);
ViewData["TcrId"] = new SelectList(_context.Teacher, "TcrId", "TeacherId", teacherCourse.TcrId);
return(View(teacherCourse));
}
public async Task <IHttpActionResult> DeleteTeacherCourse(int id)
{
TeacherCourse teacherCourse = await db.TeacherCourses.FindAsync(id);
if (teacherCourse == null)
{
return(NotFound());
}
db.TeacherCourses.Remove(teacherCourse);
await db.SaveChangesAsync();
return(Ok(teacherCourse));
}
public async Task <TeacherDTO> AddLinkToCourse(int _CourseID, int _TeacherID)
{
var teacherCourse = new TeacherCourse()
{
TeacherID = _TeacherID,
CourseID = _CourseID,
Rating = 5,
};
_context.TeacherCourses.Add(teacherCourse);
await _context.SaveChangesAsync();
return(new TeacherDTO());
}
private static void AddTeacherCourses(TeachersContext db)
{
var random = new Random();
var coursesIds = new List <string>
{
"038E641D-0F1E-4FB7-AD26-02008443E9F6",
"B8F63F2F-4F35-437C-9F7F-3DB46752734D",
"ADCA34E7-A43E-4248-B2F6-3FFC0D34A687",
"B5F0105C-77DD-4273-8129-5642F2EE1E74",
"D37DCBB3-AFC9-473A-BE58-70CC8D921426"
};
var teachers = db.Teachers.ToList();
foreach (var courseId in coursesIds)
{
var teacherCourse1 = new TeacherCourse()
{
Id = Guid.NewGuid(), CourseId = Guid.Parse(courseId),
TeacherId = teachers.ElementAt(random.Next(0, 2)).Id
};
var teacherCourse2 = new TeacherCourse()
{
Id = Guid.NewGuid(),
CourseId = Guid.Parse(courseId),
TeacherId = teachers.ElementAt(random.Next(2, 4)).Id
};
var teacherCourse3 = new TeacherCourse()
{
Id = Guid.NewGuid(),
CourseId = Guid.Parse(courseId),
TeacherId = teachers.ElementAt(random.Next(4, 6)).Id
};
var teacherCourse4 = new TeacherCourse()
{
Id = Guid.NewGuid(),
CourseId = Guid.Parse(courseId),
TeacherId = teachers.ElementAt(random.Next(6, teachers.Count)).Id
};
db.TeacherCourses.Add(teacherCourse1);
db.TeacherCourses.Add(teacherCourse2);
db.TeacherCourses.Add(teacherCourse3);
db.TeacherCourses.Add(teacherCourse4);
db.SaveChanges();
}
}
public int Add(TeacherCourse model)
{
//修改
string sql = "insert into teacher_course(teacher_id,course_id,add_time) " +
"values (@teacher_id,@course_id,@add_time)";
SQLiteParameter[] ps =
{
//修改
new SQLiteParameter("@teacher_id", model.teacher_id),
new SQLiteParameter("@course_id", model.course_id),
new SQLiteParameter("@add_time", DateTime.Now)
};
return(SqliteHelper.Add(sql, ps));
}
public int Delete(TeacherCourse model)
{
if (SelectById(model).deleted == 1)
{
//如果deleted==1，已经删除，返回-2
return(-2);
}
//修改
string sql = "update teacher_course set deleted=1 where id=@id";
SQLiteParameter[] ps =
{
new SQLiteParameter("@id", model.id),
};
return(SqliteHelper.Delete(sql, ps));
}
public async Task <IActionResult> AddTeacherToCourses(List <AddTeacherToCoursesViewModel> model, long Id)
{
if (ModelState.IsValid)
{
Course course = entityRepository.GetById(Id);
if (course == null)
{
ViewBag.ErrorMessage = $"The course resource with Id = { Id } could not be found";
return(View("NotFound"));
}
bool result = false;
TeacherCourse teacherCourse = new TeacherCourse();
for (int index = 0; index < model.Count; index++)
{
Teacher teacher = teacherRepository.GetTeacherById(model[index].TeacherId);
if (model[index].IsSelected && !(await teacherRepository.TeacherTeachesCourse(teacher, course.CourseName)))
{
teacherCourse.CourseId = course.Id;
teacherCourse.TeacherId = teacher.Id;
result = await teacherRepository.AddTeacherToCourseAsync(teacherCourse);
}
else if (!model[index].IsSelected && await teacherRepository.TeacherTeachesCourse(teacher, course.CourseName))
{
var teacher_course = await teacherRepository.FindRelatedTeacherCourses(Id, model[index].TeacherId);
result = await teacherRepository.RemoveTeacherFromCourse(teacher_course);
}
else
{
continue;
}
if (result == true)
{
if (index < (model.Count - 1))
{
continue;
}
else
{
return(Json(new { success = true }));
}
}
}
}
return(PartialView(model));
}
