// 填寫 課程成績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 課程成績 列印完成。"); }