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);
}
