private void GetClassStudentSemsScore() { #region SQL string sql = string.Format(@" WITH target_student AS ( SELECT id , name , seat_no , ref_class_id AS class_id FROM student WHERE ref_class_id IN( {0} ) AND status IN(1, 2) ) ,target_score AS( SELECT target_student.id AS student_id , target_student.name AS student_name , target_student.seat_no , target_student.class_id , sems_subj_score_ext.grade_year , sems_subj_score_ext.semester , sems_subj_score_ext.school_year , array_to_string(xpath('/Subject/@科目', subj_score_ele), '')::text AS 科目 , array_to_string(xpath('/Subject/@科目級別', subj_score_ele), '')::text AS 科目級別 , array_to_string(xpath('/Subject/@開課學分數', subj_score_ele), '')::text AS 學分數 , array_to_string(xpath('/Subject/@是否取得學分', subj_score_ele), '')::text AS 是否取得學分 , array_to_string(xpath('/Subject/@修課必選修', subj_score_ele), '')::text AS 必選修 , array_to_string(xpath('/Subject/@不計學分', subj_score_ele), '')::text AS 不計學分 FROM target_student LEFT OUTER JOIN ( SELECT sems_subj_score.* , unnest(xpath('/SemesterSubjectScoreInfo/Subject', xmlparse(content score_info))) as subj_score_ele FROM sems_subj_score ) as sems_subj_score_ext ON sems_subj_score_ext.ref_student_id = target_student.id ORDER BY target_student.class_id , target_student.seat_no ) SELECT * FROM target_score WHERE 不計學分 = '否' ", string.Join(",", listClassID)); #endregion DataTable dt = qh.Select(sql); foreach (DataRow row in dt.Rows) { string classID = "" + row["class_id"]; string studentID = "" + row["student_id"]; string studentName = "" + row["student_name"]; string seatNo = "" + row["seat_no"]; string gradeYear = "" + row["grade_year"]; string semester = "" + row["semester"]; string key = $"{gradeYear}_{semester}"; string subjectName = "" + row["科目"]; string level = "" + row["科目級別"]; float credit = float.Parse("" + row["學分數"] == "" ? "0" : "" + row["學分數"]); string mode = "" + row["必選修"]; bool isGetCredit = "" + row["是否取得學分"] == "是" ? true : false; #region 學生學分數整理 if (!dicCreditRecByClassStuKey.ContainsKey(classID)) { dicCreditRecByClassStuKey.Add(classID, new Dictionary <string, Dictionary <string, CreditRec> >()); } Dictionary <string, Dictionary <string, CreditRec> > dicStuCreditRec = dicCreditRecByClassStuKey[classID]; if (!dicStuCreditRec.ContainsKey(studentID)) { dicStuCreditRec.Add(studentID, new Dictionary <string, CreditRec>()); } Dictionary <string, CreditRec> dicCreditRecByKey = dicStuCreditRec[studentID]; if (!dicCreditRecByKey.ContainsKey(key)) { dicCreditRecByKey.Add(key, new CreditRec() { TotalCredit = 0, CoredCredit = 0, HaveToCredit = 0, SelectedCredit = 0 }); } CreditRec creditRec = dicCreditRecByKey[key]; creditRec.TotalCredit += credit; if (isGetCredit) { if (studentScoreRuleDict.ContainsKey(studentID)) { if (ruleCoreSubjectNameDict.ContainsKey(studentScoreRuleDict[studentID])) { if (ruleCoreSubjectNameDict[studentScoreRuleDict[studentID]].Contains($"{subjectName}_{level}")) { // 核心 creditRec.CoredCredit += credit; } } } //if (dicCoreSubjectByKey.ContainsKey($"{subjectName}_{level}")) //{ // // 核心 // creditRec.CoredCredit += credit; //} switch (mode) { case "必修": creditRec.HaveToCredit += credit; break; case "選修": creditRec.SelectedCredit += credit; break; default: break; } } #endregion #region 學生基本資料整理 if (!dicStudentRecByID.ContainsKey(studentID)) { StudentRec stuRec = new StudentRec(); stuRec.ID = studentID; stuRec.Name = studentName; stuRec.SeatNo = seatNo; stuRec.ClassID = classID; dicStudentRecByID.Add(studentID, stuRec); } #endregion } }
private Workbook FillWorkBookData() { Workbook wb = new Workbook(); wb.Copy(wbTemplate); string schoolName = School.ChineseName; string schoolYear = School.DefaultSchoolYear; int sheetIndex = 0; foreach (string classID in listClassID) { if (sheetIndex > 0) { wb.Worksheets.Add(); } int rowIndex = 0; ClassRecord classRec = Class.SelectByID(classID); Worksheet sheet = wb.Worksheets[sheetIndex]; sheet.Copy(wbTemplate.Worksheets[0]); sheet.Name = classRec.Name; sheet.Cells[rowIndex++, 0].PutValue($"{schoolName} {schoolYear}學年度 {classRec.Name} 修讀學分統計表"); rowIndex = 4; Range range = sheet.Cells.CreateRange(1, 1, 1, 30); Style style = sheet.Cells.GetCellStyle(4, 0); StyleFlag styleFlag = new StyleFlag() { All = true }; // 有班級學生資料 if (dicCreditRecByClassStuKey.ContainsKey(classID)) { foreach (string stuID in dicCreditRecByClassStuKey[classID].Keys) { // 沒有任何核心必修科目 bool nototalCoreCredit = true; if (studentScoreRuleDict.ContainsKey(stuID)) { if (ruleCoreSubjectNameDict.ContainsKey(studentScoreRuleDict[stuID])) { if (ruleCoreSubjectNameDict[studentScoreRuleDict[stuID]].Count > 0) { nototalCoreCredit = false; } } } int colIndex = 0; //修讀合計 float totalCredit = 0; // 累計實得 float totalHaveCredit = 0; float totalCoreCredit = 0; float totalHaveToCredit = 0; float totalSelectedCredit = 0; Dictionary <string, CreditRec> dicCreditRecByKey = dicCreditRecByClassStuKey[classID][stuID]; // 座號 姓名 if (dicStudentRecByID.ContainsKey(stuID)) { StudentRec stuRec = dicStudentRecByID[stuID]; sheet.Cells[rowIndex, colIndex++].PutValue(stuRec.SeatNo); sheet.Cells[rowIndex, colIndex++].PutValue(stuRec.Name); } // 成績年級學期學分數 for (int gradeYear = 1; gradeYear <= 3; gradeYear++) { for (int semester = 1; semester <= 2; semester++) { string key = $"{gradeYear}_{semester}"; if (dicCreditRecByKey.ContainsKey(key)) { CreditRec creditRec = dicCreditRecByKey[key]; sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.TotalCredit); if (nototalCoreCredit) { sheet.Cells[rowIndex, colIndex++].PutValue(""); } else { sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.CoredCredit); } sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.HaveToCredit); sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.SelectedCredit); totalCredit += creditRec.TotalCredit; totalCoreCredit += creditRec.CoredCredit; totalHaveToCredit += creditRec.HaveToCredit; totalSelectedCredit += creditRec.SelectedCredit; totalHaveCredit += (creditRec.HaveToCredit + creditRec.SelectedCredit); } else { colIndex += 4; } } } // 累計實得學分 //修讀合計 sheet.Cells[rowIndex, colIndex++].PutValue(totalCredit); // 核心必修 if (nototalCoreCredit) { sheet.Cells[rowIndex, colIndex++].PutValue(""); } else { sheet.Cells[rowIndex, colIndex++].PutValue(totalCoreCredit); } // 必修 sheet.Cells[rowIndex, colIndex++].PutValue(totalHaveToCredit); // 選修 sheet.Cells[rowIndex, colIndex++].PutValue(totalSelectedCredit); // 合計 sheet.Cells[rowIndex, colIndex++].PutValue(totalHaveCredit); Range currentRow = sheet.Cells.CreateRange(rowIndex, 0, 1, 30); currentRow.ApplyStyle(style, styleFlag); rowIndex++; } } // 沒有班級學生資料 else { sheet.Cells.Merge(4, 0, 3, 30); sheet.Cells[4, 0].PutValue("無資料"); } sheetIndex++; } return(wb); }