/// <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 }