private void Worker_DoWork(object sender, DoWorkEventArgs e)
{
_worker.ReportProgress(0, "開始列印...");
#region 取得目前 系統的 學年度 學期
school_year = K12.Data.School.DefaultSchoolYear;
semester = K12.Data.School.DefaultSemester;
#endregion
#region 取得課程 設定樣板、 基本資料整理
_courseList = new List <K12.Data.CourseRecord>();
_eslCourseList = new List <ESLCourseRecord>();
_courseList = K12.Data.Course.SelectByIDs(_courseIDList);
_eslCourseList = ESLCourseRecord.ToESLCourseRecords(_courseList);
_refAssessmentSetupIDList = new List <string>();
foreach (K12.Data.CourseRecord courseRecord in _courseList)
{
if (!_refAssessmentSetupIDList.Contains("'" + courseRecord.RefAssessmentSetupID + "'"))
{
_refAssessmentSetupIDList.Add("'" + courseRecord.RefAssessmentSetupID + "'");
}
if (!_courseIDPairDict.ContainsKey(courseRecord.ID))
{
_courseIDPairDict.Add(courseRecord.ID, courseRecord.RefAssessmentSetupID);
}
if (!_assessmentSetupIDNamePairDict.ContainsKey(courseRecord.RefAssessmentSetupID))
{
_assessmentSetupIDNamePairDict.Add(courseRecord.RefAssessmentSetupID, courseRecord.AssessmentSetup.Name);
}
}
string assessmentSetupIDs = string.Join(",", _refAssessmentSetupIDList);
#endregion
#region 建立課程成績 Dict、取得各課程的學生人數(sc_attend)
List <K12.Data.SCAttendRecord> scList = K12.Data.SCAttend.SelectByCourseIDs(_courseIDList);
List <string> studentIDList = new List <string>();
foreach (K12.Data.SCAttendRecord scr in scList)
{
if (scr.Student.Status != K12.Data.StudentRecord.StudentStatus.一般)
{
continue;
}
studentIDList.Add(scr.Student.ID);
// 建立課程修課名單，以對照出修課人數 對照畫面設定 決定取幾名 ，
if (!_courseScattendDict.ContainsKey(scr.Course.ID))
{
_courseScattendDict.Add(scr.Course.ID, new List <K12.Data.SCAttendRecord>());
_courseScattendDict[scr.Course.ID].Add(scr);
}
else
{
_courseScattendDict[scr.Course.ID].Add(scr);
}
}
#endregion
// 建立 解讀 description XML
CreateFieldTemplate();
#region 取得、整理ESL成績
_worker.ReportProgress(20, "取得ESL課程成績");
List <String> _courseIDListBatch = new List <string>();
QueryHelper qh = new QueryHelper();
DataTable dt = new DataTable();
// 2019/4/09 穎驊優化， 原本的取法，可能會因為筆數過多 造成伺服器資源過載，在此處做分批處理優化(10筆課程一次查詢)
for (int i = 0; i < _courseIDList.Count; i++)
{
if (_courseIDListBatch.Count <= 9 && i + 1 != _courseIDList.Count)
{
_courseIDListBatch.Add(_courseIDList[i]);
}
else
{
_courseIDListBatch.Add(_courseIDList[i]);
string course_ids = string.Join("','", _courseIDListBatch);
string sql = @"
SELECT
course.ref_exam_template_id
,course.course_name AS english_class
,course.id AS course_id
,student.student_number AS student_number
,student.name AS student_chinese_name
,student.english_name AS student_english_name
,student.gender AS gender
,class.class_name AS home_room
,student.ref_class_id AS ref_class_id
,student.id AS student_id
,teacher.teacher_name
,sc_attend.id AS sc_attend_id
,$esl.gradebook_assessment_score.ref_teacher_id
,sc_attend.ref_course_id
,sc_attend.ref_student_id
,$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
FROM student
LEFT JOIN sc_attend ON sc_attend.ref_student_id =student.id
LEFT JOIN $esl.gradebook_assessment_score 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 class ON student.ref_class_id = class.id
LEFT JOIN teacher ON $esl.gradebook_assessment_score.ref_teacher_id = teacher.id
WHERE sc_attend.ref_course_id IN ('" + course_ids + @"')
ORDER BY $esl.gradebook_assessment_score.last_update";
;
_courseIDListBatch.Clear();
try
{
DataTable dt_batch = new DataTable();
dt_batch = qh.Select(sql);
dt.Merge(dt_batch);
}
catch (Exception ex)
{
throw ex;
}
}
}
foreach (DataRow row in dt.Rows)
{
string termWord = "" + row["term"];
string subjectWord = "" + row["subject"];
string assessmentWord = "" + row["assessment"];
// 課程ID
string course_id = "" + row["ref_course_id"];
// 評分樣版 ID
string assessmentSetupID = "" + row["ref_exam_template_id"];
string targetTerm = _assessmentSetupExamTermDict["" + row["ref_exam_template_id"]].ContainsKey(_examID) ? _assessmentSetupExamTermDict["" + row["ref_exam_template_id"]][_examID] : "";
// 有教師自訂的子項目成績就跳掉 不處理
if ("" + row["custom_assessment"] != "")
{
continue;
}
// 選擇目標課程的 ESL 成績 其Term 與選擇對應試別 對不起來 也不處理
// 但如果 選擇 課程總成績 ， 則所有 成績都要紀錄
if (targetTerm != termWord && !_isSemesterCourseScore)
{
continue;
}
//// 指標型成績
//if (_indicatorList.Contains("" + row["ref_course_id"] + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + assessmentWord.Trim().Replace(' ', '_').Replace('"', '_')))
//{
// //獎項只排 分數型成績、指標不納入
// continue;
//}
//// 評語型成績
//else if (_commentList.Contains("" + row["ref_course_id"] + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + assessmentWord.Trim().Replace(' ', '_').Replace('"', '_')))
//{
// //獎項只排 分數型成績、評語不納入
// continue;
//}
// 上列狀況排除 剩下的都是分數型成績
#region 分數整理
ESLScore eslScore = new ESLScore();
eslScore.RefStudentID = "" + row["student_id"];
eslScore.RefStudentName = "" + row["student_chinese_name"];
eslScore.Term = termWord;
eslScore.Subject = subjectWord;
eslScore.Assessment = assessmentWord;
if ("" + row["value"] != "")
{
eslScore.Value = "" + row["value"];
}
// 項目都有，為assessment 成績
if (termWord != "" && "" + subjectWord != "" && "" + assessmentWord != "")
{
_courseAssessmentScoreDict[assessmentSetupID][course_id].Add(eslScore);
}
// 沒有assessment，為subject 成績
if (termWord != "" && "" + subjectWord != "" && "" + assessmentWord == "")
{
// 敘獎 沒有看為subject 成績，暫不處理
}
// 沒有assessment、subject，為term 成績
if (termWord != "" && "" + subjectWord == "" && "" + assessmentWord == "")
{
_courseTermScoreDict[assessmentSetupID][course_id].Add(eslScore);
}
#endregion
#region 學生基本資料整理
if (!_studentRecordDict.ContainsKey("" + row["ref_student_id"]))
{
ESLAwardStudentRecord studentRecord = new ESLAwardStudentRecord();
// 學號 (Student Number)
studentRecord.StudentNumber = "" + row["student_number"];
// 英文姓名 (English Name)
studentRecord.EnglishName = "" + row["student_english_name"];
// 中文姓名 (Chinese Name)
studentRecord.ChineseName = "" + row["student_chinese_name"];
// 性別 (Gender)
studentRecord.Gender = "" + row["gender"] != "" ? "" + row["gender"] == "1" ? "男" : "女" : "";
// 原班級 (Home Room)
studentRecord.HomeRoom = "" + row["home_room"];
// 以ref_student_id 當作Key值 加入 對照字典， 之後填資料可以使用
_studentRecordDict.Add("" + row["ref_student_id"], studentRecord);
}
#endregion
// 穎驊注解，另外 在樣板中 還有 Level 、 Group ， 目前 2019/1/3 系統中沒有這兩個欄位，
// 目前預計是等 寒假，在補齊欄位
}
#endregion
_worker.ReportProgress(60, "排序中...");
#region 排序
// 每一個課程的 Term 成績List 排序 (學號)
foreach (string assessmentSetupID in _courseTermScoreDict.Keys)
{
foreach (string coursrID in _courseTermScoreDict[assessmentSetupID].Keys)
{
try
{
_courseTermScoreDict[assessmentSetupID][coursrID].Sort((x, y) => { return(_studentRecordDict[x.RefStudentID].StudentNumber.CompareTo(_studentRecordDict[y.RefStudentID].StudentNumber)); });
}
catch
{
}
}
}
// 學期 課程成績排序 (學號)
foreach (string coursrID in _courseScattendDict.Keys)
{
try
{
_courseScattendDict[coursrID].Sort((x, y) => { return(_studentRecordDict[x.RefStudentID].StudentNumber.CompareTo(_studentRecordDict[y.RefStudentID].StudentNumber)); });
}
catch
{
}
}
#endregion
_worker.ReportProgress(80, "填寫報表...");
// 取得 系統預設的樣板
Workbook wb = new Workbook(new MemoryStream(Properties.Resources.ESL課程班級取前N名_樣板_));
#region 填表
FillCourseScoreExcelColunm(wb);
#endregion
// 把當作樣板的 第一張 移掉
wb.Worksheets.RemoveAt(0);
e.Result = wb;
_worker.ReportProgress(100, "ESL 課程成績 列印完成。");
}
