public ImportHCScore()
{
OpenFileDialog ope = new OpenFileDialog();
ope.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
Workbook wb = new Workbook();
if (ope.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
else
{
wb = new Workbook(ope.FileName);
}
Worksheet ws = wb.Worksheets[0];
Cells cells = ws.Cells;
foreach (var row in cells.Rows)
{
if (row.Index > 1 && !row.IsBlank)
{
//LA CET 成績
for (int i = 16; i <= 22; i++)
{
ESLScore score = new ESLScore();
score.RefCourseID = "" + cells[row.Index, 3].Value;
score.RefStudentID = "" + cells[row.Index, 7].Value;
score.RefTeacherID = "" + cells[row.Index, 13].Value;
score.Term = "Mid-Term";
score.Subject = "Language Arts (CET)";
score.Assessment = "" + cells[1, i].Value;
score.Value = "" + cells[row.Index, i].Value;
this.insertESLscoreList.Add(score);
}
//LA CET 成績
for (int i = 23; i <= 29; i++)
{
ESLScore score = new ESLScore();
score.RefCourseID = "" + cells[row.Index, 3].Value;
score.RefStudentID = "" + cells[row.Index, 7].Value;
score.RefTeacherID = "" + cells[row.Index, 14].Value;
score.Term = "Mid-Term";
score.Subject = "Language Arts (FET)";
score.Assessment = "" + cells[1, i].Value;
score.Value = "" + cells[row.Index, i].Value;
this.insertESLscoreList.Add(score);
}
//SC
for (int i = 30; i <= 35; i++)
{
ESLScore score = new ESLScore();
score.RefCourseID = "" + cells[row.Index, 5].Value;
score.RefStudentID = "" + cells[row.Index, 7].Value;
score.RefTeacherID = "" + cells[row.Index, 15].Value;
score.Term = "Mid-Term";
score.Subject = "Science";
score.Assessment = "" + cells[1, i].Value;
score.Value = "" + cells[row.Index, i].Value;
this.insertESLscoreList.Add(score);
}
}
}
//拚SQL
// 兜資料
List <string> dataList = new List <string>();
foreach (ESLScore score in insertESLscoreList)
{
string data = string.Format(@"
SELECT
'{0}'::BIGINT AS ref_student_id
,'{1}'::BIGINT AS ref_course_id
,'{2}'::BIGINT AS ref_teacher_id
,'{3}'::TEXT AS term
,{4} AS subject
,'{5}'::TEXT AS assessment
,'{6}'::TEXT AS value
,{7}::INTEGER AS uid
,'INSERT'::TEXT AS action
", score.RefStudentID, score.RefCourseID, score.RefTeacherID, score.Term, score.Subject != null ? "'" + score.Subject + "' ::TEXT" : "NULL", score.Assessment, score.Value, 0); // insert 給 uid = 0
dataList.Add(data);
}
string Data = string.Join(" UNION ALL", dataList);
string sql = string.Format(@"
WITH score_data_row AS(
{0}
)
INSERT INTO $esl.gradebook_assessment_score(
ref_student_id
,ref_course_id
,ref_teacher_id
,term
,subject
,assessment
,value
)
SELECT
score_data_row.ref_student_id::BIGINT AS ref_student_id
,score_data_row.ref_course_id::BIGINT AS ref_course_id
,score_data_row.ref_teacher_id::BIGINT AS ref_teacher_id
,score_data_row.term::TEXT AS term
,score_data_row.subject::TEXT AS subject
,score_data_row.assessment::TEXT AS subject
,score_data_row.value::TEXT AS value
FROM
score_data_row
WHERE action ='INSERT'", Data);
}
private void Worker_DoWork(object sender, DoWorkEventArgs e)
{
_worker.ReportProgress(0, "取得課程資料...");
List <string> _TargetCourseTermList = new List <string>(); //本次須計算的目標課程Term <TermName>
string courseIDs = string.Join(",", _courseIDList);
#region 取得ESL 課程資料
// 2018/06/12 抓取課程且其有ESL 樣板設定規則的，才做後續整理， 在table exam_template 欄位 description 不為空代表其為ESL 的樣板
string query = @"
SELECT
course.id
,course.course_name
,exam_template.description
FROM course
LEFT JOIN exam_template ON course.ref_exam_template_id =exam_template.id
WHERE course.id IN( " + courseIDs + ") AND exam_template.description IS NOT NULL ";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
_courseIDList.Clear(); // 清空
//整理目前的ESL 課程資料
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
ESLCourse record = new ESLCourse();
_courseIDList.Add("" + dr[0]); // 加入真正的 是ESL 課程ID
record.ID = "" + dr[0]; //課程ID
record.CourseName = "" + dr[1]; //課程名稱
record.Description = "" + dr[2]; // ESL 評分樣版設定
_ESLCourseList.Add(record);
}
}
#endregion
_worker.ReportProgress(10, "取得解析ESL課程樣板...");
#region 解析ESL 課程 計算規則
// 解析計算規則
foreach (ESLCourse course in _ESLCourseList)
{
string xmlStr = "<root>" + course.Description + "</root>";
XElement elmRoot = XElement.Parse(xmlStr);
//解析讀下來的 descriptiony 資料
if (elmRoot != null)
{
if (elmRoot.Element("ESLTemplate") != null)
{
foreach (XElement ele_term in elmRoot.Element("ESLTemplate").Elements("Term"))
{
Term t = new Term();
t.Name = ele_term.Attribute("Name").Value;
t.Weight = ele_term.Attribute("Weight").Value;
t.InputStartTime = ele_term.Attribute("InputStartTime").Value;
t.InputEndTime = ele_term.Attribute("InputEndTime").Value;
t.Ref_exam_id = ele_term.Attribute("Ref_exam_id").Value;
if (target_exam_id == t.Ref_exam_id && !_TargetCourseTermList.Contains("'" + t.Name + "'"))
{
_TargetCourseTermList.Add("'" + t.Name + "'");
}
t.SubjectList = new List <Subject>();
foreach (XElement ele_subject in ele_term.Elements("Subject"))
{
Subject s = new Subject();
s.Name = ele_subject.Attribute("Name").Value;
s.Weight = ele_subject.Attribute("Weight").Value;
s.AssessmentList = new List <Assessment>();
foreach (XElement ele_assessment in ele_subject.Elements("Assessment"))
{
Assessment a = new Assessment();
a.Name = ele_assessment.Attribute("Name").Value;
a.Weight = ele_assessment.Attribute("Weight").Value;
a.TeacherSequence = ele_assessment.Attribute("TeacherSequence").Value;
a.Type = ele_assessment.Attribute("Type").Value;
a.AllowCustomAssessment = ele_assessment.Attribute("AllowCustomAssessment").Value;
if (a.Type == "Comment") // 假如是 評語類別，多讀一項 輸入限制屬性
{
a.InputLimit = ele_assessment.Attribute("InputLimit").Value;
}
a.IndicatorsList = new List <Indicators>();
if (ele_assessment.Element("Indicators") != null)
{
foreach (XElement ele_Indicator in ele_assessment.Element("Indicators").Elements("Indicator"))
{
Indicators i = new Indicators();
i.Name = ele_Indicator.Attribute("Name").Value;
i.Description = ele_Indicator.Attribute("Description").Value;
a.IndicatorsList.Add(i);
}
}
s.AssessmentList.Add(a);
}
t.SubjectList.Add(s);
}
if (!_scoreTemplateDict.ContainsKey(course.ID))
{
_scoreTemplateDict.Add(course.ID, new List <Term>());
_scoreTemplateDict[course.ID].Add(t);
}
else
{
_scoreTemplateDict[course.ID].Add(t);
}
}
}
}
}
#endregion
_worker.ReportProgress(20, "取得ESL課程修課學生...");
#region 取得ESL 課程 修課學生 修課紀錄
_scatList = K12.Data.SCAttend.SelectByCourseIDs(_courseIDList);
// 將修課紀錄 以stidentID 整理成字典
foreach (K12.Data.SCAttendRecord scattendRecord in _scatList)
{
if (!_scatDict.ContainsKey(scattendRecord.RefStudentID))
{
_scatDict.Add(scattendRecord.RefStudentID, new List <SCAttendRecord>());
_scatDict[scattendRecord.RefStudentID].Add(scattendRecord);
}
else
{
_scatDict[scattendRecord.RefStudentID].Add(scattendRecord);
}
}
#endregion
_worker.ReportProgress(30, "取得ESL課程修課學生成績...");
#region 取得學生ESL 成績(assessment)
// 學生ID清單
string studentIDs = string.Join(",", _scatDict.Keys);
// Term 名稱 清單
string termNames = string.Join(",", _TargetCourseTermList);
//抓取目前所選取ESL 課程、評量，且其修課學生的 成績
//assessment 欄位 為空，還有濾掉 custom_assessment 成績 代表此成績 是 Subject 或是 Term 成績
query = @"SELECT *
FROM $esl.gradebook_assessment_score
WHERE ref_course_id IN( " + courseIDs + ") " +
"AND ref_student_id IN(" + studentIDs + ")" +
"AND term IN(" + termNames + ")" +
"AND assessment != ''" +
"AND custom_assessment = ''";
dt = qh.Select(query);
// 整理 既有的成績資料 (Assessment)
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (!_scoreAssessmentOriDict.ContainsKey("" + dr["ref_student_id"]))
{
ESLScore score = new ESLScore();
score.ID = "" + dr["uid"];
score.RefCourseID = "" + dr["ref_course_id"];
score.RefStudentID = "" + dr["ref_student_id"];
score.RefTeacherID = "" + dr["ref_teacher_id"];
score.Term = "" + dr["term"];
score.Subject = "" + dr["subject"];
score.Assessment = "" + dr["assessment"];
score.Custom_Assessment = "" + dr["custom_assessment"];
score.Value = "" + dr["value"];
_scoreAssessmentOriDict.Add("" + dr["ref_student_id"], new List <ESLScore>());
_scoreAssessmentOriDict["" + dr["ref_student_id"]].Add(score);
}
else
{
ESLScore score = new ESLScore();
score.ID = "" + dr["uid"];
score.RefCourseID = "" + dr["ref_course_id"];
score.RefStudentID = "" + dr["ref_student_id"];
score.RefTeacherID = "" + dr["ref_teacher_id"];
score.Term = "" + dr["term"];
score.Subject = "" + dr["subject"];
score.Assessment = "" + dr["assessment"];
score.Custom_Assessment = "" + dr["custom_assessment"];
score.Value = "" + dr["value"];
_scoreAssessmentOriDict["" + dr["ref_student_id"]].Add(score);
}
}
}
#endregion
#region 取得學生 ESL 成績(term、subject) 作為最後對照是否更新使用
//抓取目前所選取ESL 課程、評量，且其修課學生的 term、subject 成績
query = @"SELECT *
FROM $esl.gradebook_assessment_score
WHERE ref_course_id IN( " + courseIDs + ") " +
"AND ref_student_id IN(" + studentIDs + ")" +
"AND term IN(" + termNames + ")" +
"AND assessment IS NULL " +
"AND custom_assessment IS NULL ";
dt = qh.Select(query);
// 整理 既有的成績資料
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (!_scoreTermSubjectOriDict.ContainsKey("" + dr["ref_student_id"]))
{
ESLScore score = new ESLScore();
score.ID = "" + dr["uid"];
score.RefCourseID = "" + dr["ref_course_id"];
score.RefStudentID = "" + dr["ref_student_id"];
score.RefTeacherID = "" + dr["ref_teacher_id"];
score.Term = "" + dr["term"];
score.Subject = "" + dr["subject"];
score.Assessment = "" + dr["assessment"];
score.Custom_Assessment = "" + dr["custom_assessment"];
score.Value = "" + dr["value"];
_scoreTermSubjectOriDict.Add("" + dr["ref_student_id"], new List <ESLScore>());
_scoreTermSubjectOriDict["" + dr["ref_student_id"]].Add(score);
}
else
{
ESLScore score = new ESLScore();
score.ID = "" + dr["uid"];
score.RefCourseID = "" + dr["ref_course_id"];
score.RefStudentID = "" + dr["ref_student_id"];
score.RefTeacherID = "" + dr["ref_teacher_id"];
score.Term = "" + dr["term"];
score.Subject = "" + dr["subject"];
score.Assessment = "" + dr["assessment"];
score.Custom_Assessment = "" + dr["custom_assessment"];
score.Value = "" + dr["value"];
_scoreTermSubjectOriDict["" + dr["ref_student_id"]].Add(score);
}
}
}
#endregion
_worker.ReportProgress(40, "取得系統課程評量成績...");
// 取得舊有評量成績
List <SCETakeRecord> scetakeList_Old = SCETake.SelectByCourseAndExam(_courseIDList, target_exam_id);
// 以 studentID 為 key 整理 學生評量成績
foreach (SCETakeRecord scetRecord in scetakeList_Old)
{
if (!_scetDict.ContainsKey(scetRecord.RefStudentID))
{
_scetDict.Add(scetRecord.RefStudentID, new List <SCETakeRecord>());
_scetDict[scetRecord.RefStudentID].Add(scetRecord);
}
else
{
_scetDict[scetRecord.RefStudentID].Add(scetRecord);
}
}
#region 換算ESL 成績項目 每一個的權重
foreach (string courseID in _scoreTemplateDict.Keys)
{
string key_subject = "";
string key_assessment = "";
foreach (Term t in _scoreTemplateDict[courseID])
{
t.SubjectTotalWeight = 0;
foreach (Subject s in t.SubjectList)
{
s.AssessmentTotalWeight = 0;
foreach (Assessment a in s.AssessmentList)
{
if (a.Type == "Score") // 只取分數型成績
{
key_assessment = courseID + "_" + t.Name + "_" + s.Name + "_" + a.Name;
decimal ratio_assessment = decimal.Parse(a.Weight);
_scoreRatioDict.Add(key_assessment, ratio_assessment);
}
}
key_subject = courseID + "_" + t.Name + "_" + s.Name;
decimal ratio = decimal.Parse(s.Weight);
_scoreRatioDict.Add(key_subject, ratio);
}
}
}
#endregion
_worker.ReportProgress(50, "計算ESL結構成績...");
#region ESL 成績計算
//計算成績 依照比例 等量換算成分數 儲存 (Subject 成績)
foreach (string studentId in _scoreAssessmentOriDict.Keys)
{
string key_score_assessment = "";
string key_subject = "";
string key_term = "";
decimal subject_score_partial;
foreach (ESLScore score in _scoreAssessmentOriDict[studentId])
{
key_score_assessment = score.RefCourseID + "_" + score.Term + "_" + score.Subject + "_" + score.Assessment; // 查分數比例的KEY 這些就夠
key_subject = score.RefCourseID + "_" + score.RefStudentID + "_" + score.Term + "_" + score.Subject; // 寫給學生的Subject 成績 還必須要有 studentID 才有獨立性
key_term = score.RefCourseID + "_" + score.RefStudentID + "_" + score.Term; // 寫給學生的Term 成績 還必須要有 studentID 才有獨立性
if (_scoreRatioDict.ContainsKey(key_score_assessment))
{
decimal assementScore;
if (decimal.TryParse(score.Value, out assementScore))
{
subject_score_partial = Math.Round(assementScore * _scoreRatioDict[key_score_assessment], 2, MidpointRounding.ToEven); // 四捨五入到第二位
// 處理 subject分母
if (!_scoreRatioTotalDict.ContainsKey(key_subject))
{
_scoreRatioTotalDict.Add(key_subject, _scoreRatioDict[key_score_assessment]);
}
else
{
_scoreRatioTotalDict[key_subject] += _scoreRatioDict[key_score_assessment];
}
if (!_subjectScoreDict.ContainsKey(key_subject))
{
ESLScore subjectScore = new ESLScore();
subjectScore.RefCourseID = score.RefCourseID;
subjectScore.RefStudentID = score.RefStudentID;
subjectScore.RefTeacherID = score.RefTeacherID;
subjectScore.Term = score.Term;
subjectScore.Subject = score.Subject;
subjectScore.Score = subject_score_partial;
_subjectScoreDict.Add(key_subject, subjectScore);
}
else
{
_subjectScoreDict[key_subject].Score += subject_score_partial;
}
}
else
{
//assementScore = 0; // 轉失敗(可能沒有輸入)，當0 分
}
}
}
}
// 計算Subject成績後，現在將各自加權後的成績除以各自的的總權重
foreach (KeyValuePair <string, ESLScore> score in _subjectScoreDict)
{
string ratioTotalKey = score.Value.RefCourseID + "_" + score.Value.RefStudentID + "_" + score.Value.Term + "_" + score.Value.Subject;
_subjectScoreDict[score.Key].Score = Math.Round(_subjectScoreDict[score.Key].Score / _scoreRatioTotalDict[ratioTotalKey], 2, MidpointRounding.ToEven);
}
//計算成績 依照比例 等量換算成分數 儲存 (term 成績)
foreach (ESLScore subjectScore in _subjectScoreDict.Values)
{
string key_score_subject = subjectScore.RefCourseID + "_" + subjectScore.Term + "_" + subjectScore.Subject; // 查分數比例的KEY 這些就夠
string key_term = "";
decimal term_score_partial;
if (_scoreRatioDict.ContainsKey(key_score_subject))
{
key_term = subjectScore.RefCourseID + "_" + subjectScore.RefStudentID + "_" + subjectScore.Term; // 寫給學生的Term 成績 還必須要有 studentID 才有獨立性
term_score_partial = Math.Round(subjectScore.Score * _scoreRatioDict[key_score_subject], 2, MidpointRounding.ToEven); // 四捨五入到第二位
// 處理 term 分母
if (!_scoreRatioTotalDict.ContainsKey(key_term))
{
_scoreRatioTotalDict.Add(key_term, _scoreRatioDict[key_score_subject]);
}
else
{
_scoreRatioTotalDict[key_term] += _scoreRatioDict[key_score_subject];
}
if (!_termScoreDict.ContainsKey(key_term))
{
ESLScore termScore = new ESLScore();
termScore.RefCourseID = subjectScore.RefCourseID;
termScore.RefStudentID = subjectScore.RefStudentID;
termScore.RefTeacherID = subjectScore.RefTeacherID;
termScore.Term = subjectScore.Term;
termScore.Score = term_score_partial;
_termScoreDict.Add(key_term, termScore);
}
else
{
_termScoreDict[key_term].Score += term_score_partial;
}
}
}
// 計算Term成績後，現在將各自加權後的成績除以各自的的總權重
foreach (KeyValuePair <string, ESLScore> score in _termScoreDict)
{
string ratioTotalKey = score.Value.RefCourseID + "_" + score.Value.RefStudentID + "_" + score.Value.Term;
_termScoreDict[score.Key].Score = Math.Round(_termScoreDict[score.Key].Score / _scoreRatioTotalDict[ratioTotalKey], 2, MidpointRounding.ToEven);
}
// 以 studentID 為 key 整理 學生subject成績 至_scorefinalDict
foreach (ESLScore score in _subjectScoreDict.Values)
{
if (!_scorefinalDict.ContainsKey(score.RefStudentID))
{
_scorefinalDict.Add(score.RefStudentID, new List <ESLScore>());
_scorefinalDict[score.RefStudentID].Add(score);
}
else
{
_scorefinalDict[score.RefStudentID].Add(score);
}
}
// 以 studentID 為 key 整理 學生term 成績 至 _scorefinalDict
foreach (ESLScore score in _termScoreDict.Values)
{
if (!_scorefinalDict.ContainsKey(score.RefStudentID))
{
_scorefinalDict.Add(score.RefStudentID, new List <ESLScore>());
_scorefinalDict[score.RefStudentID].Add(score);
}
else
{
_scorefinalDict[score.RefStudentID].Add(score);
}
}
#endregion
List <ESLScore> updateESLscoreList = new List <ESLScore>(); // 最後要update ESLscoreList
List <ESLScore> insertESLscoreList = new List <ESLScore>(); // 最後要indert ESLscoreList
foreach (string studentID in _scorefinalDict.Keys)
{
foreach (ESLScore scoreFinal in _scorefinalDict[studentID])
{
string scoreKey = scoreFinal.RefCourseID + "_" + scoreFinal.Subject + "_" + scoreFinal.Term;
if (_scoreTermSubjectOriDict.ContainsKey(studentID))
{
foreach (ESLScore scoreOri in _scoreTermSubjectOriDict[studentID])
{
// update 分數
if (scoreKey == scoreOri.RefCourseID + "_" + scoreOri.Subject + "_" + scoreOri.Term)
{
scoreOri.Score = scoreFinal.Score;
updateESLscoreList.Add(scoreOri);
}
}
// 假若就分數沒有任何一項對的起來 就是 insert 分數
if (!_scoreTermSubjectOriDict[studentID].Any(s => s.RefCourseID + "_" + s.Subject + "_" + s.Term == scoreKey))
{
insertESLscoreList.Add(scoreFinal);
}
}
// insert 分數
else
{
insertESLscoreList.Add(scoreFinal);
}
}
}
////2018/6/14 先暫時這樣整理，之後會想要用 scoreUpsertDict ，資料會比較齊
//foreach (ESLScore score in _subjectScoreDict.Values)
//{
// _eslscoreList.Add(score);
//}
//foreach (ESLScore score in _termScoreDict.Values)
//{
// _eslscoreList.Add(score);
//}
_worker.ReportProgress(80, "轉換ESL成績 為評量成績");
#region 換算Exam 成績
List <SCETakeRecord> updateList = new List <SCETakeRecord>();
List <SCETakeRecord> insertList = new List <SCETakeRecord>();
// 更新舊評量分數
foreach (string studentID in _scetDict.Keys)
{
foreach (K12.Data.SCETakeRecord scet in _scetDict[studentID])
{
foreach (ESLScore score in _termScoreDict.Values)
{
if (scet.RefCourseID == score.RefCourseID && scet.RefStudentID == score.RefStudentID && GetScore(scet) != "" + score.Score)
{
SetScore(scet, "" + score.Score);
updateList.Add(scet);
}
}
}
}
// 新增 評量分數
foreach (SCAttendRecord sca in _scatList)
{
if (_scetDict.ContainsKey(sca.RefStudentID))
{
// 有學生修課紀錄，卻在該試別 沒有舊評量成績 就是本次要新增的項目
if (!_scetDict[sca.RefStudentID].Any(s => s.RefSCAttendID == sca.ID))
{
foreach (ESLScore score in _termScoreDict.Values)
{
if (sca.RefCourseID == score.RefCourseID && sca.RefStudentID == score.RefStudentID)
{
SCETakeRecord sce = new SCETakeRecord();
sce.RefSCAttendID = sca.ID;
sce.RefExamID = target_exam_id;
sce.RefStudentID = sca.RefStudentID;
sce.RefCourseID = sca.RefCourseID;
SetScore(sce, "" + score.Score);
insertList.Add(sce);
}
}
}
}
else
{
foreach (ESLScore score in _termScoreDict.Values)
{
if (sca.RefCourseID == score.RefCourseID && sca.RefStudentID == score.RefStudentID)
{
SCETakeRecord sce = new SCETakeRecord();
sce.RefSCAttendID = sca.ID;
sce.RefExamID = target_exam_id;
sce.RefStudentID = sca.RefStudentID;
sce.RefCourseID = sca.RefCourseID;
SetScore(sce, "" + score.Score);
insertList.Add(sce);
}
}
}
}
K12.Data.SCETake.Update(updateList);
K12.Data.SCETake.Insert(insertList);
#endregion
//拚SQL
// 兜資料
List <string> dataList = new List <string>();
// 沒有新增任何成績資料，代表所選ESL 課程都沒有成績，不需執行SQL
if (updateESLscoreList.Count + insertESLscoreList.Count == 0)
{
return;
}
foreach (ESLScore score in updateESLscoreList)
{
string data = string.Format(@"
SELECT
'{0}'::BIGINT AS ref_student_id
,'{1}'::BIGINT AS ref_course_id
,'{2}'::BIGINT AS ref_teacher_id
,'{3}'::TEXT AS term
,'{4}'::TEXT AS subject
,'{5}'::TEXT AS value
,'{6}'::INTEGER AS uid
,'UPDATE'::TEXT AS action
", score.RefStudentID, score.RefCourseID, score.RefTeacherID, score.Term, score.Subject, score.Score, score.ID);
dataList.Add(data);
}
foreach (ESLScore score in insertESLscoreList)
{
string data = string.Format(@"
SELECT
'{0}'::BIGINT AS ref_student_id
,'{1}'::BIGINT AS ref_course_id
,'{2}'::BIGINT AS ref_teacher_id
,'{3}'::TEXT AS term
,'{4}'::TEXT AS subject
,'{5}'::TEXT AS value
,{6}::INTEGER AS uid
,'INSERT'::TEXT AS action
", score.RefStudentID, score.RefCourseID, score.RefTeacherID, score.Term, score.Subject, score.Score, 0); // insert 給 uid = 0
dataList.Add(data);
}
string Data = string.Join(" UNION ALL", dataList);
string sql = string.Format(@"
WITH score_data_row AS(
{0}
),update_score AS(
Update $esl.gradebook_assessment_score
SET
ref_student_id = score_data_row.ref_student_id
,ref_course_id = score_data_row.ref_course_id
,ref_teacher_id = score_data_row.ref_teacher_id
,term = score_data_row.term
,subject = score_data_row.subject
,value = score_data_row.value
FROM
score_data_row
WHERE $esl.gradebook_assessment_score.uid = score_data_row.uid
AND score_data_row.action ='UPDATE'
RETURNING $esl.gradebook_assessment_score.*
)
INSERT INTO $esl.gradebook_assessment_score(
ref_student_id
,ref_course_id
,ref_teacher_id
,term
,subject
,value
)
SELECT
score_data_row.ref_student_id::BIGINT AS ref_student_id
,score_data_row.ref_course_id::BIGINT AS ref_course_id
,score_data_row.ref_teacher_id::BIGINT AS ref_teacher_id
,score_data_row.term::TEXT AS term
,score_data_row.subject::TEXT AS subject
,score_data_row.value::TEXT AS value
FROM
score_data_row
WHERE action ='INSERT'", Data);
UpdateHelper uh = new UpdateHelper();
_worker.ReportProgress(90, "上傳成績...");
//執行sql
uh.Execute(sql);
_worker.ReportProgress(100, "ESL 評量成績計算完成。");
}
public void CalculateESLTermScore()
{
string courseIDs = string.Join(",", _courseIDList);
// 2018/06/12 抓取課程且其有ESL 樣板設定規則的，才做後續整理， 在table exam_template 欄位 description 不為空代表其為ESL 的樣板
string query = @"
SELECT
course.id
,course.course_name
,exam_template.description
FROM course
LEFT JOIN exam_template ON course.ref_exam_template_id =exam_template.id
WHERE course.id IN( " + courseIDs + ") AND exam_template.description IS NOT NULL ";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
_courseIDList.Clear(); // 清空
//整理目前的ESL 課程資料
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
ESLCourse record = new ESLCourse();
_courseIDList.Add("" + dr[0]); // 加入真正的 是ESL 課程ID
record.ID = "" + dr[0]; //課程ID
record.CourseName = "" + dr[1]; //課程名稱
record.Description = "" + dr[2]; // ESL 評分樣版設定
_ESLCourseList.Add(record);
}
}
// 解析計算規則
foreach (ESLCourse course in _ESLCourseList)
{
string xmlStr = "<root>" + course.Description + "</root>";
XElement elmRoot = XElement.Parse(xmlStr);
//解析讀下來的 descriptiony 資料
if (elmRoot != null)
{
if (elmRoot.Element("ESLTemplate") != null)
{
foreach (XElement ele_term in elmRoot.Element("ESLTemplate").Elements("Term"))
{
Term t = new Term();
t.Name = ele_term.Attribute("Name").Value;
t.Weight = ele_term.Attribute("Weight").Value;
t.InputStartTime = ele_term.Attribute("InputStartTime").Value;
t.InputEndTime = ele_term.Attribute("InputEndTime").Value;
t.Ref_exam_id = ele_term.Attribute("Ref_exam_id").Value;
if (target_exam_id == t.Ref_exam_id)
{
_TargetCourseExam.Add(course.ID + "_" + t.Name);
}
t.SubjectList = new List <Subject>();
foreach (XElement ele_subject in ele_term.Elements("Subject"))
{
Subject s = new Subject();
s.Name = ele_subject.Attribute("Name").Value;
s.Weight = ele_subject.Attribute("Weight").Value;
s.AssessmentList = new List <Assessment>();
foreach (XElement ele_assessment in ele_subject.Elements("Assessment"))
{
Assessment a = new Assessment();
a.Name = ele_assessment.Attribute("Name").Value;
a.Weight = ele_assessment.Attribute("Weight").Value;
a.TeacherSequence = ele_assessment.Attribute("TeacherSequence").Value;
a.Type = ele_assessment.Attribute("Type").Value;
a.AllowCustomAssessment = ele_assessment.Attribute("AllowCustomAssessment").Value;
if (a.Type == "Comment") // 假如是 評語類別，多讀一項 輸入限制屬性
{
a.InputLimit = ele_assessment.Attribute("InputLimit").Value;
}
a.IndicatorsList = new List <Indicators>();
if (ele_assessment.Element("Indicators") != null)
{
foreach (XElement ele_Indicator in ele_assessment.Element("Indicators").Elements("Indicator"))
{
Indicators i = new Indicators();
i.Name = ele_Indicator.Attribute("Name").Value;
i.Description = ele_Indicator.Attribute("Description").Value;
a.IndicatorsList.Add(i);
}
}
s.AssessmentList.Add(a);
}
t.SubjectList.Add(s);
}
if (!_scoreTemplateDict.ContainsKey(course.ID))
{
_scoreTemplateDict.Add(course.ID, new List <Term>());
_scoreTemplateDict[course.ID].Add(t);
}
else
{
_scoreTemplateDict[course.ID].Add(t);
}
}
}
}
}
string eslCourseIDs = string.Join(",", _courseIDList); // 把真正是ESL 課程的ID 列出來
query = "SELECT* FROM $esl.gradebook_assessment_score WHERE ref_course_id in( " + eslCourseIDs + ")"; // 抓取這些ESL 的所有成績資料
dt = qh.Select(query);
// 整理 既有的成績資料 (Assessment)
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
// 算是整理成績資料最重要的一行， assessment 欄位 為空，還有濾掉 custom_assessment 成績 代表此成績 是 Subject 或是 Term 成績
if ("" + dr["assessment"] == "" || "" + dr["custom_assessment"] != "")
{
continue;
}
// 假如本成績資料 非在本次所選的評量，也將其拿掉
if (!_TargetCourseExam.Contains("" + dr["ref_course_id"] + "_" + "" + dr["term"]))
{
continue;
}
if (!_scoreDict.ContainsKey("" + dr["ref_course_id"]))
{
ESLScore score = new ESLScore();
score.ID = "" + dr["uid"];
score.RefCourseID = "" + dr["ref_course_id"];
score.RefStudentID = "" + dr["ref_student_id"];
score.RefTeacherID = "" + dr["ref_teacher_id"];
score.Term = "" + dr["term"];
score.Subject = "" + dr["subject"];
score.Assessment = "" + dr["assessment"];
score.Custom_Assessment = "" + dr["custom_assessment"];
score.Value = "" + dr["value"];
_scoreDict.Add("" + dr["ref_course_id"], new List <ESLScore>());
_scoreDict["" + dr["ref_course_id"]].Add(score);
}
else
{
ESLScore score = new ESLScore();
score.ID = "" + dr["uid"];
score.RefCourseID = "" + dr["ref_course_id"];
score.RefStudentID = "" + dr["ref_student_id"];
score.RefTeacherID = "" + dr["ref_teacher_id"];
score.Term = "" + dr["term"];
score.Subject = "" + dr["subject"];
score.Assessment = "" + dr["assessment"];
score.Custom_Assessment = "" + dr["custom_assessment"];
score.Value = "" + dr["value"];
_scoreDict["" + dr["ref_course_id"]].Add(score);
}
}
}
//計算成績 比例加減
foreach (KeyValuePair <string, List <Term> > p in _scoreTemplateDict)
{
foreach (Term t in p.Value)
{
t.SubjectTotalWeight = 0;
foreach (Subject s in t.SubjectList)
{
//t.SubjectTotalWeight += decimal.Parse(s.Weight); // 加總比例 之後才可以換算分別比例
s.AssessmentTotalWeight = 0;
foreach (Assessment a in s.AssessmentList)
{
if (a.Type == "Score") // 只取分數型成績
{
//s.AssessmentTotalWeight += decimal.Parse(a.Weight); // 加總比例 之後才可以換算分別比例
}
}
}
}
}
foreach (KeyValuePair <string, List <Term> > p in _scoreTemplateDict)
{
string key_subject = "";
string key_assessment = "";
foreach (Term t in p.Value)
{
foreach (Subject s in t.SubjectList)
{
foreach (Assessment a in s.AssessmentList)
{
if (a.Type == "Score") // 只取分數型成績
{
key_assessment = p.Key + "_" + t.Name + "_" + s.Name + "_" + a.Name;
decimal ratio_assessment = decimal.Parse(a.Weight);
_scoreRatioDict.Add(key_assessment, ratio_assessment);
}
}
key_subject = p.Key + "_" + t.Name + "_" + s.Name;
decimal ratio = decimal.Parse(s.Weight);
_scoreRatioDict.Add(key_subject, ratio);
}
}
}
//計算成績 依照比例 等量換算成分數 儲存 在 scoreUpsertDict (Subject 成績)
foreach (KeyValuePair <string, List <ESLScore> > p in _scoreDict)
{
string key_score_assessment = "";
string key_subject = "";
string key_term = "";
decimal subject_score_partial;
foreach (ESLScore score in p.Value)
{
key_score_assessment = p.Key + "_" + score.Term + "_" + score.Subject + "_" + score.Assessment; // 查分數比例的KEY 這些就夠
key_subject = p.Key + "_" + score.RefStudentID + "_" + score.Term + "_" + score.Subject; // 寫給學生的Subject 成績 還必須要有 studentID 才有獨立性
key_term = p.Key + "_" + score.RefStudentID + "_" + score.Term; // 寫給學生的Term 成績 還必須要有 studentID 才有獨立性
if (_scoreRatioDict.ContainsKey(key_score_assessment))
{
decimal assementScore;
if (decimal.TryParse(score.Value, out assementScore))
{
subject_score_partial = Math.Round(assementScore * _scoreRatioDict[key_score_assessment], 2, MidpointRounding.ToEven); // 四捨五入到第二位
// 處理 subject分母
if (!_scoreRatioTotalDict.ContainsKey(key_subject))
{
_scoreRatioTotalDict.Add(key_subject, _scoreRatioDict[key_score_assessment]);
}
else
{
_scoreRatioTotalDict[key_subject] += _scoreRatioDict[key_score_assessment];
}
if (!_subjectScoreDict.ContainsKey(key_subject))
{
ESLScore subjectScore = new ESLScore();
subjectScore.RefCourseID = score.RefCourseID;
subjectScore.RefStudentID = score.RefStudentID;
subjectScore.RefTeacherID = score.RefTeacherID;
subjectScore.Term = score.Term;
subjectScore.Subject = score.Subject;
subjectScore.Score = subject_score_partial;
_subjectScoreDict.Add(key_subject, subjectScore);
}
else
{
_subjectScoreDict[key_subject].Score += subject_score_partial;
}
}
else
{
//assementScore = 0; // 轉失敗(可能沒有輸入)，當0 分
}
}
}
}
// 計算Subject成績後，現在將各自加權後的成績除以各自的的總權重
foreach (KeyValuePair <string, ESLScore> score in _subjectScoreDict)
{
string ratioTotalKey = score.Value.RefCourseID + "_" + score.Value.RefStudentID + "_" + score.Value.Term + "_" + score.Value.Subject;
_subjectScoreDict[score.Key].Score = Math.Round(_subjectScoreDict[score.Key].Score / _scoreRatioTotalDict[ratioTotalKey], 2, MidpointRounding.ToEven);
}
//計算成績 依照比例 等量換算成分數 儲存 在 scoreUpsertDict (term 成績)
foreach (ESLScore subjectScore in _subjectScoreDict.Values)
{
string key_score_subject = subjectScore.RefCourseID + "_" + subjectScore.Term + "_" + subjectScore.Subject; // 查分數比例的KEY 這些就夠
string key_term = "";
decimal term_score_partial;
if (_scoreRatioDict.ContainsKey(key_score_subject))
{
key_term = subjectScore.RefCourseID + "_" + subjectScore.RefStudentID + "_" + subjectScore.Term; // 寫給學生的Term 成績 還必須要有 studentID 才有獨立性
term_score_partial = Math.Round(subjectScore.Score * _scoreRatioDict[key_score_subject], 2, MidpointRounding.ToEven); // 四捨五入到第二位
// 處理 term 分母
if (!_scoreRatioTotalDict.ContainsKey(key_term))
{
_scoreRatioTotalDict.Add(key_term, _scoreRatioDict[key_score_subject]);
}
else
{
_scoreRatioTotalDict[key_term] += _scoreRatioDict[key_score_subject];
}
if (!_termScoreDict.ContainsKey(key_term))
{
ESLScore termScore = new ESLScore();
termScore.RefCourseID = subjectScore.RefCourseID;
termScore.RefStudentID = subjectScore.RefStudentID;
termScore.RefTeacherID = subjectScore.RefTeacherID;
termScore.Term = subjectScore.Term;
termScore.Score = term_score_partial;
_termScoreDict.Add(key_term, termScore);
}
else
{
_termScoreDict[key_term].Score += term_score_partial;
}
}
}
// 計算Term成績後，現在將各自加權後的成績除以各自的的總權重
foreach (KeyValuePair <string, ESLScore> score in _termScoreDict)
{
string ratioTotalKey = score.Value.RefCourseID + "_" + score.Value.RefStudentID + "_" + score.Value.Term;
_termScoreDict[score.Key].Score = Math.Round(_termScoreDict[score.Key].Score / _scoreRatioTotalDict[ratioTotalKey], 2, MidpointRounding.ToEven);
}
//2018/6/14 先暫時這樣整理，之後會想要用 scoreUpsertDict ，資料會比較齊
foreach (ESLScore score in _subjectScoreDict.Values)
{
_eslscoreList.Add(score);
}
foreach (ESLScore score in _termScoreDict.Values)
{
_eslscoreList.Add(score);
}
// 取的學生修課紀錄
List <K12.Data.SCAttendRecord> scaList = SCAttend.SelectByCourseIDs(_courseIDList);
// 取得舊有評量成績
List <SCETakeRecord> scetakeList_Old = SCETake.SelectByCourseAndExam(_courseIDList, target_exam_id);
List <SCETakeRecord> updateList = new List <SCETakeRecord>();
List <SCETakeRecord> insertList = new List <SCETakeRecord>();
// 更新舊評量分數
foreach (SCETakeRecord scet in scetakeList_Old)
{
foreach (ESLScore score in _termScoreDict.Values)
{
if (scet.RefCourseID == score.RefCourseID && scet.RefStudentID == score.RefStudentID && GetScore(scet) != "" + score.Score)
{
SetScore(scet, "" + score.Score);
updateList.Add(scet);
}
}
}
// 新增 評量分數
foreach (SCAttendRecord sca in scaList)
{
// 沒在舊評量成績 就是本次要新增的項目
if (!scetakeList_Old.Any(s => s.RefSCAttendID == sca.ID))
{
foreach (ESLScore score in _termScoreDict.Values)
{
if (sca.RefCourseID == score.RefCourseID && sca.RefStudentID == score.RefStudentID)
{
SCETakeRecord sce = new SCETakeRecord();
sce.RefSCAttendID = sca.ID;
sce.RefExamID = target_exam_id;
sce.RefStudentID = sca.RefStudentID;
sce.RefCourseID = sca.RefCourseID;
SetScore(sce, "" + score.Score);
insertList.Add(sce);
}
}
}
}
K12.Data.SCETake.Update(updateList);
K12.Data.SCETake.Insert(insertList);
//拚SQL
// 兜資料
List <string> dataList = new List <string>();
// 沒有新增任何成績資料，代表所選ESL 課程都沒有成績，不需執行SQL
if (_eslscoreList.Count == 0)
{
return;
}
foreach (ESLScore score in _eslscoreList)
{
string data = string.Format(@"
SELECT
'{0}'::BIGINT AS ref_student_id
,'{1}'::BIGINT AS ref_course_id
,'{2}'::BIGINT AS ref_teacher_id
,'{3}'::TEXT AS term
,'{4}'::TEXT AS subject
,'{5}'::TEXT AS value
", score.RefStudentID, score.RefCourseID, score.RefTeacherID, score.Term, score.Subject, score.Score);
dataList.Add(data);
}
string Data = string.Join(" UNION ALL", dataList);
string sql = string.Format(@"
WITH score_data_row AS(
{0}
),delete_score AS(
DELETE
FROM
$esl.gradebook_assessment_score
WHERE assessment IS NULL
)
INSERT INTO $esl.gradebook_assessment_score(
ref_student_id
,ref_course_id
,ref_teacher_id
,term
,subject
,value
)
SELECT
score_data_row.ref_student_id::BIGINT AS ref_student_id
,score_data_row.ref_course_id::BIGINT AS ref_course_id
,score_data_row.ref_teacher_id::BIGINT AS ref_teacher_id
,score_data_row.term::TEXT AS term
,score_data_row.subject::TEXT AS subject
,score_data_row.value::TEXT AS value
FROM
score_data_row" , Data);
UpdateHelper uh = new UpdateHelper();
//執行sql
uh.Execute(sql);
MsgBox.Show("計算完成!");
}
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 課程成績 列印完成。");
}
