/// <summary>
/// 返回学生所有数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IEnumerable <StudentFullClassScore> GetStudentJsonResult(int page, int limit, out int count)
{
//var students = _msSqlContext.StudentsInfos
// .Join(_msSqlContext.LastClassScores, stu => stu.Id, score => score.StudentsInfo, (stu, score) =>
// new StudentFullClassScore()
// {
// Id = stu.Id, Name = stu.Name, Age = stu.Age, Gender = stu.Gender, Phone = stu.Phone,
// Grade = stu.Grade, ClassName = stu.ClassName, Remark = stu.Remark, Geography = score.Geography,
// Chinese = score.Chinese, Physics = score.Physics, Biology = score.Biology,
// English = score.English,
// Math = score.Math, History = score.History, Politics = score.Politics,
// Chemistry = score.Chemistry
// }).SelectMany(combination=>combination.Id.DefaultIfEmpty(), (score, c) => new{}).ToList();
var students = (from stu in _msSqlContext.StudentsInfos
join score in _msSqlContext.LastClassScores
on stu.Id equals score.StudentsInfoId into t
from score in t.DefaultIfEmpty()
select new StudentFullClassScore()
{
StudentInfoId = stu.Id,
Name = stu.Name,
Grade = stu.Grade.ToString(),
ClassName = stu.ClassName,
Remark = stu.Remark,
id = score.id,
ClassInfoId = score.ClassInfoId,
Geography = score.Geography,
Chinese = score.Chinese,
Physics = score.Physics,
Biology = score.Biology,
English = score.English,
Math = score.Math,
History = score.History,
Politics = score.Politics,
Chemistry = score.Chemistry,
TotalScore = (score.Chinese + score.Math + score.English + score.Geography + score.Biology +
score.Chemistry + score.History + score.Politics + score.Physics)
}).OrderBy(key => key.id).Skip(limit * (page - 1)).Take(limit).ToList();
count = _msSqlContext.LastClassScores.Count();
_loggerHelper.WriteInfoLog(students);
return(students);
}
public void UpdateScore(string studentInfoId, string classInfoId, int newScore, string course)
{
SqlParameter[] sql = new []
{
new SqlParameter("course", course),
new SqlParameter("stuInfo", studentInfoId),
new SqlParameter("classInfo", classInfoId),
new SqlParameter("newScore", newScore)
{
Direction = ParameterDirection.Output
},
};
_loggerHelper.WriteInfoLog(studentInfoId + "," + classInfoId + "," + newScore + "," + course);
//没搞明白为什么ExecuteSqlInterpolated可以执行却不能变更,暂时先用ExecuteSqlRaw
var update = _msSqlContext.Database
.ExecuteSqlRaw($"update LastClassScores set {course} = {newScore} where ClassInfoId = '{classInfoId}' and StudentsInfoId = '{studentInfoId}'");
_msSqlContext.SaveChanges();
_loggerHelper.WriteInfoLog(update);
}
/// <summary>
/// 获取学生查询数据
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="name"></param>
/// <returns></returns>
public string SutudentSearch(int page, int limit, string searchName, int grade, string className)
{
//if (!string.IsNullOrEmpty(searchName) && !string.IsNullOrEmpty(grade.ToString()) && !string.IsNullOrEmpty(className))
//{
_loggerHelper.WriteInfoLog(searchName + grade + className);
var data = _queryDbData.GetStudentJsonResult(page, limit, searchName, grade, className);
StuAndSocreToJsonResult <StudentFullClassScore> result = new StuAndSocreToJsonResult <StudentFullClassScore>();
result.count = data.Count();
result.code = 0;
result.msg = "";
result.data = data;
var json = JsonConvert.SerializeObject(result);
return(json);
}
