public async Task <IActionResult> ScoreSummaryByDepartment(Department department)
{
if (!ModelState.IsValid)
{
return(BadRequest("Body JSON content invalid"));
}
var counselor = await unitOfWork.CounselorRepository.FirstOrDefaultAsync(c => c.Department == department);
if (counselor == null)
{
return(NotFound());
}
return(Json(await ScoreSummaryByDepartmentViewModel.GetAsync(unitOfWork, counselor)));
}
public async Task <IActionResult> CreateExcelOfAllDepartments()
{
string sWebRootFolder = Startup.Environment.WebRootPath;
string sFileName = @"excel/" + "ScoreSummaryOfAllDepartments.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
if (!file.Exists)
{
var datatable = unitOfWork.DbContext.Counselors.Select(m => ScoreSummaryByDepartmentViewModel.GetAsync(unitOfWork, m)).ToList();
using (ExcelPackage package = new ExcelPackage(file))
{
// 添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("1");
//添加头
worksheet.Cells[1, 1].Value = "院系";
worksheet.Cells[1, 2].Value = "辅导员";
worksheet.Cells[1, 3].Value = "最高分";
worksheet.Cells[1, 4].Value = "平均分";
worksheet.Cells[1, 5].Value = ">=90";
worksheet.Cells[1, 6].Value = ">=75";
worksheet.Cells[1, 7].Value = ">=60";
worksheet.Cells[1, 8].Value = "不及格";
worksheet.Cells[1, 9].Value = "未测试";
//添加值
int number = 2;
foreach (var scoreSummarytask in datatable)
{
var scoreSummary = await scoreSummarytask;
worksheet.Cells[number, 1].Value = scoreSummary.DepartmentID;
worksheet.Cells[number, 2].Value = scoreSummary.CounselorName;
worksheet.Cells[number, 3].Value = scoreSummary.MaxScore;
worksheet.Cells[number, 4].Value = scoreSummary.AverageScore;
worksheet.Cells[number, 5].Value = scoreSummary.ScoreBandCount.HigherThan90;
worksheet.Cells[number, 6].Value = scoreSummary.ScoreBandCount.HigherThan75;
worksheet.Cells[number, 7].Value = scoreSummary.ScoreBandCount.HigherThan60;
worksheet.Cells[number, 8].Value = scoreSummary.ScoreBandCount.Failed;
worksheet.Cells[number, 9].Value = scoreSummary.ScoreBandCount.NotTested;
number++;
}
number++;
//最后空一行添加去全校概况
worksheet.Cells[number, 2].Value = "最高分";
worksheet.Cells[number, 3].Value = "平均分";
worksheet.Cells[number, 4].Value = ">=90";
worksheet.Cells[number, 5].Value = ">=75";
worksheet.Cells[number, 6].Value = ">=60";
worksheet.Cells[number, 7].Value = "不及格";
worksheet.Cells[number, 8].Value = "未测试";
worksheet.Cells[number, 9].Value = "更新时间";
number++;
var model = await ScoreSummaryOfSchoolViewModel.GetAsync(unitOfWork);
worksheet.Cells[number, 1].Value = "全校分数概况";
worksheet.Cells[number, 2].Value = model.MaxScore;
worksheet.Cells[number, 3].Value = model.AverageScore;
worksheet.Cells[number, 4].Value = model.ScoreBandCount.HigherThan90;
worksheet.Cells[number, 5].Value = model.ScoreBandCount.HigherThan75;
worksheet.Cells[number, 6].Value = model.ScoreBandCount.HigherThan60;
worksheet.Cells[number, 7].Value = model.ScoreBandCount.Failed;
worksheet.Cells[number, 8].Value = model.ScoreBandCount.NotTested;
worksheet.Cells[number, 9].Value = model.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss");
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
worksheet.View.ShowGridLines = false; //去掉sheet的网格线
worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightYellow); //设置背景色
package.Save();
}
using (ExcelPackage package = new ExcelPackage(file))
{
// 添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
var rowCnt = worksheet.Dimension.End.Row;
var colCnt = worksheet.Dimension.End.Column;
for (int i = 1; i <= rowCnt; i++)
{
if (i % 2 == 0)
{
for (int j = 1; j <= colCnt; j++)
{
worksheet.Cells[i, j].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[i, j].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
}
}
}
package.Save();
}
}
return(null);
}
public async Task <IActionResult> CountScore([FromBody] List <SubmittedAnswerViewModel> submittedAnswers)
{
var timeElapsed = DateTime.Now - this.Session().TestBeginTime;
if (timeElapsed >= unitOfWork.Configuration.TestTime + TimeSpan.FromMinutes(3) || timeElapsed <= TimeSpan.FromMinutes(3))
{
return(BadRequest("Test time not in the proper range!"));
}
var size = unitOfWork.Configuration.QuestionCount.Choice + unitOfWork.Configuration.QuestionCount.TrueFalse;
if (!ModelState.IsValid || submittedAnswers.Count != size)
{
return(BadRequest("Body JSON content invalid or does not fit the size: " + size));
}
if (this.Session().TestState == TestState.Tested)
{
return(Forbid());
}
string studentID = this.Session().ID;
if (studentID == null)
{
return(BadRequest("Student ID not set in the session, please login again"));
}
if (await unitOfWork.Cache.Database.StringGetAsync("CountingScore:" + studentID) == "true")
{
return(BadRequest("Score has already been counting"));
}
await unitOfWork.Cache.Database.StringSetAsync("CountingScore:" + studentID, "true");
try
{
var seed = this.Session().SeedID;
if (seed == null)
{
await unitOfWork.Cache.Database.KeyDeleteAsync("CountingScore:" + studentID);
return(BadRequest("Question seed not created"));
}
#region calculate score and update student data
var studentDictionary = unitOfWork.Cache.StudentEntities(studentID.ToDepartment());
var student = await studentDictionary.GetAsync(studentID);
student.Score = 0;
student.QuestionSeedID = (int)seed;
student.DateTimeFinished = DateTime.Now;
student.Choices = submittedAnswers.Select(a => (byte)a.Answer).ToArray();
if (this.Session().TestBeginTime == null)
{
await unitOfWork.Cache.Database.KeyDeleteAsync("CountingScore:" + studentID);
return(BadRequest("Test begin time not set in the session"));
}
student.TimeConsumed = student.DateTimeFinished - this.Session().TestBeginTime;
var correctAnswers = await questionSeedService.GetAnswersBySeedID((int)seed);
var model = new ResultViewModel
{
Details = new List <ResultDetailViewModel>(capacity: size),
TimeFinished = (DateTime)student.DateTimeFinished,
TimeConsumed = (TimeSpan)student.TimeConsumed
};
for (int i = 0; i < submittedAnswers.Count; ++i)
{
var submit = submittedAnswers[i];
var correct = correctAnswers[i];
if (submit.ID != correct.ID)
{
await unitOfWork.Cache.Database.KeyDeleteAsync("CountingScore:" + studentID);
return(BadRequest("Encounter improper ID in your answer set: " + submit.ID + " at " + i));
}
student.Score += submit.Answer == correct.Answer ? correct.Points : 0;
model.Details.Add(new ResultDetailViewModel {
ID = correct.ID, Correct = correct.Answer, Submit = submit.Answer
});
}
model.Score = (int)student.Score;
#endregion
#region save data
var summary = await ScoreSummaryByDepartmentViewModel.GetAsync(unitOfWork, student.Counselor);
await summary.UpdateAsync(unitOfWork, student); // 更新院系概况，放在前面防止重复计算
await unitOfWork.Cache.StudentEntities(student.Department).SetAsync(studentID, student); // 更新Student
await unitOfWork.Cache.StudentViewModels(student.Department).SetAsync(studentID, (StudentViewModel)student);
await unitOfWork.Cache.Database.ListRightPushAsync("StudentIDsToUpdate", studentID); // 学生ID放入待更新列表
await unitOfWork.Cache.Results().SetAsync(studentID, model); // result存入缓存
new ExcelExportService(unitOfWork).UpdateExcelByStudent(student);
#endregion
this.Session().TestState = TestState.Tested;
#region logout
HttpContext.Session.Clear(); // 注销账户
await HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme);
await unitOfWork.Cache.Database.KeyDeleteAsync("CountingScore:" + studentID); // 表示没有正在计算
#endregion
return(Json(model));
}
catch (Exception ex)
{
this.Session().TestState = TestState.Testing;
await unitOfWork.Cache.Database.KeyDeleteAsync("CountingScore:" + studentID);
Console.WriteLine(ex.ToString());
return(BadRequest(ex.ToString()));
}
}