// 填寫 課程成績EXCEL
private void FillCourseScoreExcelColunm(Workbook wb)
{
int wscount = 1;
// 一種ESL樣板 開一個 Worksheet
foreach (string assessmentSetupID in _courseTermScoreDict.Keys)
{
Worksheet ws_total = wb.Worksheets[wb.Worksheets.Add()];
// 複製樣板
ws_total.Copy(wb.Worksheets["樣板一"]);
// 一種樣板 一個sheet 名稱
string wsName = wscount + ".ESL樣板_" + _assessmentSetupIDNamePairDict[assessmentSetupID] + "_課程";
wscount++;
// excel sheet Name 最多只能 31 個字
wsName = wsName.Length > 26 ? wsName.Substring(0, 22) + "..." : wsName;
ws_total.Name = wsName + "(總表)";
#region 填表頭
// 填表頭
// 標題
ws_total.Cells["H1"].Value = school_year + "學年度 第" + semester + "學期 學生成績一覽表";
// 列印時間
ws_total.Cells["L1"].Value = "列印日期:" + DateTime.Now.Date.ToShortDateString();
#endregion
// 把範例樣板的成績結構刪除
ws_total.Cells.ClearRange(1, 12, 2, 19);
ws_total.Cells.ClearRange(2, 0, 2, 19);
// 最後 term 的位置
int termCol = 0;
// 最後 semesterScore 的位置
int semesterScoreCol = 0;
// 本樣板中有敘獎的學生人數
int totalAwardsCount = 0;
int col = 11;
// 填入每一個 樣板的 成績結構
foreach (Term term in _assessmentSetupDataTableDict[assessmentSetupID])
{
foreach (Subject subject in term.SubjectList)
{
foreach (Assessment assessment in subject.AssessmentList)
{
Cell cell = ws_total.Cells[1, col];
cell.Copy(wb.Worksheets["樣板一"].Cells["M2"]);
cell.Value = "(" + subject.Name + ")\n" + assessment.Name;
col++;
}
}
// 最後補上 term
Cell cell_term = ws_total.Cells[1, col];
cell_term.Copy(wb.Worksheets["樣板一"].Cells["M2"]);
cell_term.Value = term.Name;
termCol = col;
col++;
}
semesterScoreCol = termCol + 1;
// 最後補上 semesterScore
Cell cell_semesterScore = ws_total.Cells[1, col];
cell_semesterScore.Copy(wb.Worksheets["樣板一"].Cells["M2"]);
cell_semesterScore.Value = "Semester Score";
totalAwardsCount = 0;
// 依據 表頭的名稱 填入分數 (總表)
foreach (string courseID in _courseTermScoreDict[assessmentSetupID].Keys)
{
// 取幾名
int rankedNumber = 0;
// 總表 取全部學生
rankedNumber = _courseScattendDict[courseID].Count;
// 依照設定看要取幾名
for (int i = 0; i < rankedNumber; i++)
{
ESLCourseRecord courseRecord = new ESLCourseRecord();
courseRecord = _eslCourseList.First(x => x.ESLID == courseID);
// 學生 系統 ID
string ref_studentID;
// 分數
if (_courseScattendDict[courseID].Count >= i + 1)
{
ref_studentID = _courseScattendDict[courseID][i].RefStudentID;
if (!_studentRecordDict.ContainsKey(ref_studentID))
{
continue;
}
// 自樣板 把資料第一Row 的格式都Copy
ws_total.Cells.CopyRows(wb.Worksheets["樣板一"].Cells, 2, totalAwardsCount + 2, 1);
Cell cell = ws_total.Cells[totalAwardsCount + 2, semesterScoreCol];
//cell.Copy(wb.Worksheets["樣板一"].Cells["T2"]);
// 先清空值， 預設是沒有分數
cell.Value = "N/A";
if (_courseScattendDict[courseID][i].Score != null)
{
cell.Value = _courseScattendDict[courseID][i].Score;
}
// 初始欄位置
int initialCol = 11;
// 填入每一個 樣板的 成績
foreach (Term term in _assessmentSetupDataTableDict[assessmentSetupID])
{
string termName = term.Name;
int assessmentTotal = 0;
foreach (Subject subject in term.SubjectList)
{
foreach (Assessment assessment in subject.AssessmentList)
{
assessmentTotal++;
}
}
// 填 assesssment 成績
for (int assesssmentCol = initialCol; assesssmentCol < initialCol + assessmentTotal; assesssmentCol++)
{
string subjectAssesssmentName = "" + ws_total.Cells[1, assesssmentCol].Value;
Cell assesssmentCell = ws_total.Cells[totalAwardsCount + 2, assesssmentCol];
assesssmentCell.Value = "N/A";
foreach (ESLScore eslScore in _courseAssessmentScoreDict[assessmentSetupID][courseID])
{
string key = "(" + eslScore.Subject + ")\n" + eslScore.Assessment;
if (eslScore.RefStudentID == ref_studentID && eslScore.Term == termName && key == subjectAssesssmentName)
{
assesssmentCell.Value = eslScore.Value;
}
}
}
// 填 term 成績
Cell termCell = ws_total.Cells[totalAwardsCount + 2, initialCol + assessmentTotal];
termCell.Value = "N/A";
foreach (ESLScore eslScore in _courseTermScoreDict[assessmentSetupID][courseID])
{
if (eslScore.RefStudentID == ref_studentID && eslScore.Term == termName)
{
termCell.Value = eslScore.Value;
}
}
initialCol = initialCol + assessmentTotal + 1;
}
}
else
{
// 人數 不足， 跳離迴圈
break;
}
// 學號 (Student Number)
ws_total.Cells[totalAwardsCount + 2, 0].Value = _studentRecordDict.ContainsKey(ref_studentID) ? _studentRecordDict[ref_studentID].StudentNumber : "";
// 英文姓名 (English Name)
ws_total.Cells[totalAwardsCount + 2, 1].Value = _studentRecordDict.ContainsKey(ref_studentID) ? _studentRecordDict[ref_studentID].EnglishName : "";
// 中文姓名 (Chinese Name)
ws_total.Cells[totalAwardsCount + 2, 2].Value = _studentRecordDict.ContainsKey(ref_studentID) ? _studentRecordDict[ref_studentID].ChineseName : "";
// 性別 (Gender)
ws_total.Cells[totalAwardsCount + 2, 3].Value = _studentRecordDict.ContainsKey(ref_studentID) ? _studentRecordDict[ref_studentID].Gender : "";
// 原班級 (Home Room)
ws_total.Cells[totalAwardsCount + 2, 4].Value = _studentRecordDict.ContainsKey(ref_studentID) ? _studentRecordDict[ref_studentID].HomeRoom : "";
// 課程難度
ws_total.Cells[totalAwardsCount + 2, 5].Value = courseRecord.ESLDifficulty;
// 課程名稱
ws_total.Cells[totalAwardsCount + 2, 7].Value = courseRecord.ESLName;
// 教師一
ws_total.Cells[totalAwardsCount + 2, 8].Value = courseRecord.ESLTeachers.Count > 0 ? courseRecord.ESLTeachers.Find(t => t.Sequence == 1).TeacherName : "";
// 教師二
ws_total.Cells[totalAwardsCount + 2, 9].Value = courseRecord.ESLTeachers.Count > 1 ? courseRecord.ESLTeachers.Find(t => t.Sequence == 2).TeacherName : "";
// 教師三
ws_total.Cells[totalAwardsCount + 2, 10].Value = courseRecord.ESLTeachers.Count > 2 ? courseRecord.ESLTeachers.Find(t => t.Sequence == 3).TeacherName : "";
// 穎驊注解，另外 在樣板中 還有 Level 、 Group ， 目前 2019/1/3 系統中沒有這兩個欄位，
// 目前預計是等 寒假，在補齊課程欄位
totalAwardsCount++;
}
}
//把多餘的右半邊CELL欄位 砍掉 (總表)
ws_total.Cells.ClearRange(1, semesterScoreCol + 1, totalAwardsCount + 2, 50);
ws_total.AutoFitColumns();
ws_total.FirstVisibleColumn = 0;// 將打開的介面 調到最左， 要不然就會看到 右邊一片空白。
}
}
public static List <ESLCourseRecord> ToESLCourseRecords(List <K12.Data.CourseRecord> courseList)
{
List <ESLCourseRecord> eslCourseList = new List <ESLCourseRecord>();
string courseIDs = string.Join(",", courseList.Select(x => x.ID).ToList());
string selectSQL = @"
SELECT
id
, difficulty
FROM
course
WHERE
id in
(
" + courseIDs + @"
)
";
QueryHelper queryHelper = new QueryHelper();
DataTable dt = queryHelper.Select(selectSQL);
Dictionary <string, string> courseDic = new Dictionary <string, string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!courseDic.ContainsKey("" + dt.Rows[i]["id"]))
{
courseDic.Add("" + dt.Rows[i]["id"], null);
}
courseDic["" + dt.Rows[i]["id"]] = "" + dt.Rows[i]["difficulty"];
}
foreach (CourseRecord courseRecord in courseList)
{
ESLCourseRecord eslCourse = new ESLCourseRecord();
//Teachers
eslCourse.ESLTeachers = courseRecord.Teachers;
//MajorTeacherNickname
eslCourse.ESLMajorTeacherNickname = courseRecord.MajorTeacherNickname;
//MajorTeacherName
eslCourse.ESLMajorTeacherName = courseRecord.MajorTeacherName;
//MajorTeacherID
eslCourse.ESLMajorTeacherID = courseRecord.MajorTeacherID;
//AssessmentSetup
eslCourse.ESLAssessmentSetup = courseRecord.AssessmentSetup;
//Class
eslCourse.ESLClass = courseRecord.Class;
//RefClassID
eslCourse.ESLRefClassID = courseRecord.RefClassID;
//Credit
eslCourse.ESLCredit = courseRecord.Credit;
//RefAssessmentSetupID
eslCourse.ESLRefAssessmentSetupID = courseRecord.RefAssessmentSetupID;
//Subject
eslCourse.ESLSubject = courseRecord.Subject;
//ID
eslCourse.ESLID = courseRecord.ID;
//Name
eslCourse.ESLName = courseRecord.Name;
//SchoolYear
eslCourse.ESLSchoolYear = courseRecord.SchoolYear;
//Semester
eslCourse.ESLSemester = courseRecord.Semester;
//Period
eslCourse.ESLPeriod = courseRecord.Period;
//Diffiiculty
eslCourse.ESLDifficulty = courseDic[courseRecord.ID];
eslCourseList.Add(eslCourse);
}
return(eslCourseList);
}
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 課程成績 列印完成。");
}
