/// <summary>
/// 依試別取得所有課程成績
/// </summary>
/// <param name="targetTermName"></param>
private void LoadCourses(string targetTermName)
{
_worker.ReportProgress(0, "取得課程資料...");
List <string> targetCourseList = new List <string>();
// 在所有選擇課程中 其樣板 為目前選擇樣板 才加入查詢成績
foreach (string courseID in _CourseIDList)
{
if (_ESLCourseIDExamTermIDDict[courseID] == _targetTemplateID)
{
targetCourseList.Add(courseID);
}
}
string targetCourseIDs = string.Join(",", targetCourseList);
#region 建立應有成績名單
_scoreDict.Clear(); // 每次重抓都把成績Dict 清乾淨
int scaCount = 0;
// 以之前抓到的學生修課名單，建立應該要有的成績資料
foreach (K12.Data.SCAttendRecord scaRecord in _scaList)
{
// 假如本筆修課紀錄 的課程 非設定為本次篩選ESL樣板 跳過
if (_ESLCourseIDExamTermIDDict[scaRecord.RefCourseID] != _targetTemplateID)
{
continue;
}
// 2018/11/14 穎驊修正，若學生有修課紀錄， 但是目前 狀態 為非一般，則不顯示。
if (scaRecord.Student.Status != StudentRecord.StudentStatus.一般)
{
continue;
}
_worker.ReportProgress(10 + 50 * (scaCount++ / _scaList.Count), "取得修課學生資料...");
// 目標樣板設定
List <Term> termList = _ESLTemplateDict[_targetTemplateID].TermList;
if (!_scoreDict.ContainsKey(scaRecord.RefCourseID))
{
_scoreDict.Add(scaRecord.RefCourseID, new Dictionary <string, List <ESLScore> >());
foreach (Term term in termList)
{
if (term.Name == _targetTermName)
{
foreach (Subject subject in term.SubjectList)
{
foreach (Assessment assessment in subject.AssessmentList)
{
// 取得授課教師
CourseTeacherRecord teacher = scaRecord.Course.Teachers.Find(t => t.Sequence == int.Parse(assessment.TeacherSequence));
ESLScore scoreItem = new ESLScore();
scoreItem.Term = term.Name;
scoreItem.Subject = subject.Name;
scoreItem.Assessment = assessment.Name;
scoreItem.RefCourseID = scaRecord.RefCourseID;
scoreItem.RefStudentID = scaRecord.RefStudentID;
scoreItem.RefScAttendID = scaRecord.ID; // 參考修課紀錄ID(依據ESL2019寒假優化，成績參考修課紀錄ID，將廢除RefCourseID、RefStudentID)
scoreItem.RefTeacherID = teacher != null ? teacher.TeacherID : ""; // 教師ID
scoreItem.RefCourseName = scaRecord.Course.Name;
scoreItem.RefTeacherName = teacher != null ? teacher.TeacherName : "";; // 教師名稱
scoreItem.RefStudentName = scaRecord.Student.Name;
scoreItem.HasValue = false; // 一開始都先當 教師沒有輸入成績，等到取得成績後 再回填scoreItem
if (!_scoreDict[scaRecord.RefCourseID].ContainsKey(subject.Name))
{
_scoreDict[scaRecord.RefCourseID].Add(subject.Name, new List <ESLScore>());
_scoreDict[scaRecord.RefCourseID][subject.Name].Add(scoreItem);
}
else
{
_scoreDict[scaRecord.RefCourseID][subject.Name].Add(scoreItem);
}
}
}
}
}
}
else
{
foreach (Term term in termList)
{
if (term.Name == _targetTermName)
{
foreach (Subject subject in term.SubjectList)
{
foreach (Assessment assessment in subject.AssessmentList)
{
// 取得授課教師
CourseTeacherRecord teacher = scaRecord.Course.Teachers.Find(t => t.Sequence == int.Parse(assessment.TeacherSequence));
ESLScore scoreItem = new ESLScore();
scoreItem.Term = term.Name;
scoreItem.Subject = subject.Name;
scoreItem.Assessment = assessment.Name;
scoreItem.RefCourseID = scaRecord.RefCourseID;
scoreItem.RefStudentID = scaRecord.RefStudentID;
scoreItem.RefScAttendID = scaRecord.ID; // 參考修課紀錄ID(依據ESL2019寒假優化，成績參考修課紀錄ID，將廢除RefCourseID、RefStudentID)
scoreItem.RefTeacherID = teacher != null ? teacher.TeacherID : ""; // 教師ID
scoreItem.RefCourseName = scaRecord.Course.Name;
scoreItem.RefTeacherName = teacher != null ? teacher.TeacherName : "";; // 教師名稱
scoreItem.RefStudentName = scaRecord.Student.Name;
scoreItem.HasValue = false; // 一開始都先當 教師沒有輸入成績，等到取得成績後 再回填scoreItem
if (!_scoreDict[scaRecord.RefCourseID].ContainsKey(subject.Name))
{
_scoreDict[scaRecord.RefCourseID].Add(subject.Name, new List <ESLScore>());
_scoreDict[scaRecord.RefCourseID][subject.Name].Add(scoreItem);
}
else
{
_scoreDict[scaRecord.RefCourseID][subject.Name].Add(scoreItem);
}
}
}
}
}
}
}
#endregion
#region 取得 本試別 ESL 課程成績資料 (參考修課紀錄ID(依據ESL2019寒假優化，成績參考修課紀錄ID，將廢除RefCourseID、RefStudentID))
string query = @"
SELECT
$esl.gradebook_assessment_score.uid
,$esl.gradebook_assessment_score.term
,$esl.gradebook_assessment_score.subject
,$esl.gradebook_assessment_score.assessment
,$esl.gradebook_assessment_score.custom_assessment
,$esl.gradebook_assessment_score.value
,$esl.gradebook_assessment_score.ref_sc_attend_id
,$esl.gradebook_assessment_score.ref_teacher_id
,course.id AS ref_course_id
,student.id AS ref_student_id
,course.course_name
,teacher.teacher_name
,student.name AS student_name
FROM $esl.gradebook_assessment_score
LEFT JOIN sc_attend ON $esl.gradebook_assessment_score.ref_sc_attend_id = sc_attend.id
LEFT JOIN course ON sc_attend.ref_course_id = course.id
LEFT JOIN student ON sc_attend.ref_student_id= student.id
LEFT JOIN teacher ON $esl.gradebook_assessment_score.ref_teacher_id= teacher.id
WHERE
assessment IS NOT NULL
AND term = '" + targetTermName + @"'
AND sc_attend.ref_course_id IN( " + targetCourseIDs + ")";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
//整理目前的ESL 課程資料
_worker.ReportProgress(60, "取得學生成績資料...");
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
// 濾掉有 custom_assessment 項目的成績，不用SQL AND custom_assessment!='' 的原因是因為有的時候custom_assessment 會NULL
if ("" + dr["custom_assessment"] != "")
{
continue;
}
foreach (string courseID in _scoreDict.Keys)
{
if (courseID == "" + dr["ref_course_id"])
{
foreach (string subjectName in _scoreDict[courseID].Keys)
{
if (subjectName == "" + dr["subject"])
{
foreach (ESLScore scoreItem in _scoreDict[courseID][subjectName])
{
if (scoreItem.Assessment == "" + dr["assessment"] && scoreItem.RefStudentID == "" + dr["ref_student_id"])
{
scoreItem.ID = "" + dr["uid"]; // 填入 uid 之後可以做為更新使用
scoreItem.Value = "" + dr["value"]; // 填分數
scoreItem.HasValue = true; // 若以上條件可找到配對，則本課程、本term、subject、assessment 的教師 有輸入成績。
}
}
}
}
}
}
}
}
#endregion
int scoreCount = 0;
// 填 DataGridView
_courseDataGridViewRowList = new List <CourseDataGridViewRow>();
foreach (string courseID in _scoreDict.Keys)
{
_worker.ReportProgress(60 + 30 * (scoreCount++ / _scoreDict.Keys.Count), "取得學生成績資料...");
// 假如該課程 為採用目前所選 樣板
if (_ESLCourseIDExamTermIDDict[courseID] == _targetTemplateID)
{
CourseDataGridViewRow row = new CourseDataGridViewRow(_ESLCourseIDNameDict[courseID], _scoreDict[courseID], _ESLTemplateDict[_targetTemplateID], _targetTermName);
row.Tag = courseID; // 用課程ID 當作 Tag
_courseDataGridViewRowList.Add(row);
}
}
}
/// <summary>
/// 依試別取得所有課程成績
/// </summary>
/// <param name="targetTermName"></param>
private void LoadCourses(string targetTermName)
{
_downloadWorker.ReportProgress(0, "取得課程資料...");
#region 建立該學生應有成績
_scoreDict.Clear(); // 每次重抓都把成績Dict 清乾淨
// 目標樣板設定
List <Term> termList = _ESLTemplateDict[_targetTemplateID].TermList;
if (!_scoreDict.ContainsKey(_scAttendRecord.RefCourseID))
{
_scoreDict.Add(_scAttendRecord.RefCourseID, new List <ESLScore>());
foreach (Term term in termList)
{
if (term.Name == _targetTermName)
{
foreach (Subject subject in term.SubjectList)
{
foreach (Assessment assessment in subject.AssessmentList)
{
// 取得授課教師
CourseTeacherRecord teacher = _scAttendRecord.Course.Teachers.Find(t => t.Sequence == int.Parse(assessment.TeacherSequence));
ESLScore scoreItem = new ESLScore();
scoreItem.Term = term.Name;
scoreItem.Subject = subject.Name;
scoreItem.Assessment = assessment.Name;
scoreItem.RefCourseID = _scAttendRecord.RefCourseID;
scoreItem.RefStudentID = _scAttendRecord.RefStudentID;
scoreItem.RefScAttendID = _scAttendRecord.ID; // 參考修課紀錄ID(依據ESL2019寒假優化，成績參考修課紀錄ID，將廢除RefCourseID、RefStudentID)
scoreItem.RefTeacherID = teacher != null ? teacher.TeacherID : ""; // 教師ID
scoreItem.RefCourseName = _scAttendRecord.Course.Name;
scoreItem.RefTeacherName = teacher != null ? teacher.TeacherName : "";; // 教師名稱
scoreItem.RefStudentName = _scAttendRecord.Student.Name;
scoreItem.HasValue = false; // 一開始都先當 教師沒有輸入成績，等到取得成績後 再回填scoreItem
_scoreDict[_scAttendRecord.RefCourseID].Add(scoreItem);
}
}
}
}
}
else
{
foreach (Term term in termList)
{
if (term.Name == _targetTermName)
{
foreach (Subject subject in term.SubjectList)
{
foreach (Assessment assessment in subject.AssessmentList)
{
// 取得授課教師
CourseTeacherRecord teacher = _scAttendRecord.Course.Teachers.Find(t => t.Sequence == int.Parse(assessment.TeacherSequence));
ESLScore scoreItem = new ESLScore();
scoreItem.Term = term.Name;
scoreItem.Subject = subject.Name;
scoreItem.Assessment = assessment.Name;
scoreItem.RefCourseID = _scAttendRecord.RefCourseID;
scoreItem.RefStudentID = _scAttendRecord.RefStudentID;
scoreItem.RefScAttendID = _scAttendRecord.ID; // 參考修課紀錄ID(依據ESL2019寒假優化，成績參考修課紀錄ID，將廢除RefCourseID、RefStudentID)
scoreItem.RefTeacherID = teacher != null ? teacher.TeacherID : ""; // 教師ID
scoreItem.RefCourseName = _scAttendRecord.Course.Name;
scoreItem.RefTeacherName = teacher != null ? teacher.TeacherName : "";; // 教師名稱
scoreItem.RefStudentName = _scAttendRecord.Student.Name;
scoreItem.HasValue = false; // 一開始都先當 教師沒有輸入成績，等到取得成績後 再回填scoreItem
_scoreDict[_scAttendRecord.RefCourseID].Add(scoreItem);
}
}
}
}
}
#endregion
#region 取得 本試別 ESL 課程成績資料 (參考修課紀錄ID(依據ESL2019寒假優化，成績參考修課紀錄ID，將廢除RefCourseID、RefStudentID))
string query = @"
SELECT
$esl.gradebook_assessment_score.uid
,$esl.gradebook_assessment_score.term
,$esl.gradebook_assessment_score.subject
,$esl.gradebook_assessment_score.assessment
,$esl.gradebook_assessment_score.custom_assessment
,$esl.gradebook_assessment_score.value
,$esl.gradebook_assessment_score.ratio
,$esl.gradebook_assessment_score.ref_sc_attend_id
,$esl.gradebook_assessment_score.ref_teacher_id
,course.id AS ref_course_id
,student.id AS ref_student_id
,course.course_name
,teacher.teacher_name
,student.name AS student_name
FROM $esl.gradebook_assessment_score
LEFT JOIN sc_attend ON $esl.gradebook_assessment_score.ref_sc_attend_id = sc_attend.id
LEFT JOIN course ON sc_attend.ref_course_id = course.id
LEFT JOIN student ON sc_attend.ref_student_id= student.id
LEFT JOIN teacher ON $esl.gradebook_assessment_score.ref_teacher_id= teacher.id
WHERE
assessment IS NOT NULL
AND term = '" + targetTermName + @"'
AND sc_attend.ref_course_id IN( " + _targetCourseID + ")";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
//整理目前的ESL 課程資料
_downloadWorker.ReportProgress(60, "取得學生成績資料...");
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
// 濾掉有 custom_assessment 項目的成績，不用SQL AND custom_assessment!='' 的原因是因為有的時候custom_assessment 會NULL
if ("" + dr["custom_assessment"] != "")
{
continue;
}
foreach (string courseID in _scoreDict.Keys)
{
if (courseID == "" + dr["ref_course_id"])
{
foreach (ESLScore scoreItem in _scoreDict[courseID])
{
if (scoreItem.Subject == "" + dr["subject"] && scoreItem.Assessment == "" + dr["assessment"] && scoreItem.RefStudentID == "" + dr["ref_student_id"])
{
scoreItem.ID = "" + dr["uid"]; // 填入 uid 之後可以做為更新使用
scoreItem.Value = "" + dr["value"]; // 填分數
if ("" + dr["ratio"] != "")
{
scoreItem.Ratio = int.Parse("" + dr["ratio"]); // 填比例
}
scoreItem.HasValue = true; // 若以上條件可找到配對，則本課程、本term、subject、assessment 的教師 有輸入成績。
}
}
}
}
}
}
#endregion
}
