private void ParseData(DataTable dt)
{
foreach (DataRow row in dt.Rows)
{
string classID = "" + row["class_id"];
string studentID = "" + row["id"];
string domain = "" + row["領域"];
//if (domain != "")
if (listDomainName.Contains(domain) && domain != "特殊需求") //如果領域存在領域資料管理，且不是特殊需求才列印，沒有則忽略-Cynthia 2021.08
{
// 成績資料
if (!dicClassStuDomainScore.ContainsKey(classID))
{
dicClassStuDomainScore.Add(classID, new Dictionary <string, Dictionary <string, ScoreRec> >());
}
if (!dicClassStuDomainScore[classID].ContainsKey(studentID))
{
dicClassStuDomainScore[classID].Add(studentID, new Dictionary <string, ScoreRec>());
}
ScoreRec sr = new ScoreRec();
sr.Score = "" + row["成績"];
sr.OriginScore = "" + row["原始成績"];
sr.Power = "" + row["權數"];
dicClassStuDomainScore[classID][studentID].Add(domain, sr);
// 班級資料
if (!dicClassNameByID.ContainsKey(classID))
{
dicClassNameByID.Add(classID, "" + row["class_name"]);
}
// 學生資料
if (!dicStuRecByID.ContainsKey(studentID))
{
StudentRec stuRec = new StudentRec();
stuRec.SeatNo = "" + row["seat_no"];
stuRec.Name = "" + row["name"];
dicStuRecByID.Add(studentID, stuRec);
}
// 班級領域資料
if (!dicDomainNameByClassID.ContainsKey(classID))
{
dicDomainNameByClassID.Add(classID, new List <string>());
}
if (!dicDomainNameByClassID[classID].Contains(domain))
{
dicDomainNameByClassID[classID].Add(domain);
}
}
}
}
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);
}
private void _bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
// 取得班級學生領域成績
GetStuDomainScore();
_bgWorker.ReportProgress(20);
// 取得班級領域清單
GetClassDomainList();
_bgWorker.ReportProgress(40);
// 班級領域清單排序
foreach (string classID in dicDomainByClassID.Keys)
{
List <string> listDomain = dicDomainByClassID[classID];
// 資料排序
listDomain.Sort(delegate(string a, string b)
{
int aIndex = listDomainFromConfig.FindIndex(name => name == a);
int bIndex = listDomainFromConfig.FindIndex(name => name == b);
if (aIndex > bIndex)
{
return(1);
}
else
{
return(-1);
}
});
}
_bgWorker.ReportProgress(60);
Workbook wb = new Workbook();
int sheetIndex = 0;
// 總表功能
Worksheet wst = wb.Worksheets[0];
wst.Name = "總表";
wst.Copy(wbTemplate.Worksheets["總表樣板"]);
// 需要補考
List <string> domainNameList = new List <string>();
Dictionary <string, int> domainIdxDict = new Dictionary <string, int>();
List <string> tmpNameList = new List <string>();
int colIdx = 4;
foreach (string classID in listClassID)
{
if (dicDomainByClassID.ContainsKey(classID))
{
foreach (string dName in dicDomainByClassID[classID])
{
if (!tmpNameList.Contains(dName))
{
tmpNameList.Add(dName);
}
}
}
}
if (tmpNameList.Contains("語文"))
{
domainNameList.Add("語文");
}
// 依對照表順序
foreach (string name in listDomainFromConfig)
{
if (tmpNameList.Contains(name))
{
domainNameList.Add(name);
}
}
// 填入欄位
foreach (string dName in domainNameList)
{
wst.Cells[0, colIdx].PutValue(dName);
domainIdxDict.Add(dName + "補考", colIdx);
colIdx++;
}
foreach (string dName in domainNameList)
{
wst.Cells[0, colIdx].PutValue(dName);
domainIdxDict.Add(dName + "成績", colIdx);
colIdx++;
}
int rowIdx = 1;
// 產生資料至總表
foreach (string classID in listClassID)
{
List <string> listDomain = new List <string>();
if (dicDomainByClassID.ContainsKey(classID))
{
listDomain = dicDomainByClassID[classID];
if (dicStuRecByClassID.ContainsKey(classID))
{
foreach (string stuID in dicStuRecByClassID[classID].Keys)
{
StudentRec stuRec = dicStuRecByClassID[classID][stuID];
bool checkReExam = false;
// 檢查是否需要補考
foreach (string domain in listDomain)
{
if (stuRec.dicScoreRecByDomain.ContainsKey(domain))
{
if (stuRec.dicScoreRecByDomain[domain].isPass == false)
{
checkReExam = true;
}
}
}
// 需要補考才產生在總表
if (checkReExam)
{
wst.Cells[rowIdx, 0].PutValue(stuRec.GradeYear);
wst.Cells[rowIdx, 1].PutValue(stuRec.ClassName);
wst.Cells[rowIdx, 2].PutValue(stuRec.SeatNo);
wst.Cells[rowIdx, 3].PutValue(stuRec.StudentName);
foreach (string domain in listDomain)
{
if (stuRec.dicScoreRecByDomain.ContainsKey(domain))
{
if (stuRec.dicScoreRecByDomain[domain].isPass)
{
//
}
else
{
if (domainIdxDict.ContainsKey(domain + "補考"))
{
wst.Cells[rowIdx, domainIdxDict[domain + "補考"]].PutValue("補考");
}
}
if (domainIdxDict.ContainsKey(domain + "成績"))
{
decimal score;
if (decimal.TryParse(stuRec.dicScoreRecByDomain[domain].OriginScore, out score))
{
wst.Cells[rowIdx, domainIdxDict[domain + "成績"]].PutValue(score);
}
}
}
}
rowIdx++;
}
}
}
}
}
wst.AutoFitColumns();
sheetIndex = 1;
// 各班分列
foreach (string classID in listClassID)
{
string className = K12.Data.Class.SelectByID(classID).Name;
List <string> listDomain = new List <string>();
if (dicDomainByClassID.ContainsKey(classID))
{
listDomain = dicDomainByClassID[classID];
}
if (sheetIndex > 0)
{
wb.Worksheets.Add();
}
Worksheet ws = wb.Worksheets[sheetIndex++];
ws.Name = className;
ws.Copy(wbTemplate.Worksheets["班級樣板"]);
Range colRange = wbTemplate.Worksheets["班級樣板"].Cells.CreateRange(2, 2, 1, 1);
int rowIndex = 0;
int colIndex = 0;
int domainCount = listDomain.Count;
string title = $"{iptSchoolYear.Value}學年度 第{iptSemester.Value}學期 {className}領域補考名單";
ws.Cells.Merge(rowIndex, colIndex, 1, (domainCount * 2 + 1) < 6 ? 6 : domainCount * 2 + 1);
ws.Cells[rowIndex, colIndex].PutValue(title);
if (dicStuRecByClassID.ContainsKey(classID))
{
rowIndex++;
colIndex = 2;
// 班級領域清單
foreach (string domain in listDomain)
{
ws.Cells[rowIndex, colIndex++].PutValue(domain);
}
rowIndex++;
foreach (string stuID in dicStuRecByClassID[classID].Keys)
{
colIndex = 0;
StudentRec stuRec = dicStuRecByClassID[classID][stuID];
Dictionary <string, ScoreRec> dicScoreRecByDomain = stuRec.dicScoreRecByDomain;
// 座號
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
ws.Cells[rowIndex, colIndex++].PutValue(stuRec.SeatNo);
}
// 姓名
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
ws.Cells[rowIndex, colIndex++].PutValue(stuRec.StudentName);
}
foreach (string domain in listDomain)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
if (dicScoreRecByDomain.ContainsKey(domain))
{
string score = dicScoreRecByDomain[domain].OriginScore;
string data = dicScoreRecByDomain[domain].isPass ? $"{score}" : $"補考/ {score}";
ws.Cells[rowIndex, colIndex].PutValue(data);
}
colIndex++;
}
rowIndex++;
}
}
else
{
ws.Cells[2, 0].PutValue("此班級無學生。");
ws.Cells.Merge(2, 0, 1, 3);
}
}
e.Result = wb;
}
/// <summary>
/// 取得班級學生領域成績
/// </summary>
private void GetStuDomainScore()
{
dicStuRecByClassID = new Dictionary <string, Dictionary <string, StudentRec> >();
#region SQL
string sql = $@"
WITH target_student AS(
SELECT
*
FROM
student
WHERE
ref_class_id IN ({string.Join(",", listClassID)})
AND status IN (1, 2)
)
SELECT
target_student.id
, target_student.name
, target_student.seat_no
, class.id AS class_id
, class.class_name
, class.grade_year
, array_to_string(xpath('/Domain/@原始成績', subj_score_ele), '')::text AS 原始成績
, array_to_string(xpath('/Domain/@成績', subj_score_ele), '')::text AS 成績
, array_to_string(xpath('/Domain/@領域', subj_score_ele), '')::text AS 領域
, array_to_string(xpath('/Domain/@權數', subj_score_ele), '')::text AS 權數
FROM
target_student
LEFT OUTER JOIN (
SELECT
sems_subj_score.*
, unnest(xpath('/root/Domains/Domain', xmlparse(content '<root>' || score_info || '</root>'))) as subj_score_ele
FROM
sems_subj_score
INNER JOIN target_student
ON target_student.id = sems_subj_score.ref_student_id
WHERE
sems_subj_score.school_year = {iptSchoolYear.Value}
AND sems_subj_score.semester = {iptSemester.Value}
) as sems_subj_score_ext
ON sems_subj_score_ext.ref_student_id = target_student.id
LEFT OUTER JOIN class
ON class.id = target_student.ref_class_id
ORDER BY
class.display_order
, target_student.seat_no
";
#endregion
DataTable dt = qh.Select(sql);
foreach (DataRow row in dt.Rows)
{
string classID = "" + row["class_id"];
string stuID = "" + row["id"];
// 學生資料整理
if (!dicStuRecByClassID.ContainsKey(classID))
{
dicStuRecByClassID.Add(classID, new Dictionary <string, StudentRec>());
}
Dictionary <string, StudentRec> dicStuRecByID = dicStuRecByClassID[classID];
if (!dicStuRecByID.ContainsKey(stuID))
{
StudentRec stuRec = new StudentRec();
stuRec.GradeYear = "" + row["grade_year"];
stuRec.ClassName = "" + row["class_name"];
stuRec.SeatNo = "" + row["seat_no"];
stuRec.StudentName = "" + row["name"];
stuRec.dicScoreRecByDomain = new Dictionary <string, ScoreRec>();
dicStuRecByID.Add(stuID, stuRec);
}
string domain = "" + row["領域"];
if (!dicStuRecByID[stuID].dicScoreRecByDomain.ContainsKey(domain))
{
ScoreRec scoreRec = new ScoreRec();
scoreRec.Domain = domain;
scoreRec.OriginScore = "" + row["原始成績"];
scoreRec.isPass = FloatParse("" + row["原始成績"]) >= _passScore;
dicStuRecByID[stuID].dicScoreRecByDomain.Add(domain, scoreRec);
}
}
}
/// <summary>
/// 資料解析
/// </summary>
private void ParseData()
{
foreach (DataRow row in dtRsp.Rows)
{
string stuID = "" + row["id"];
// 新增學生物件
if (!dicStudentByID.ContainsKey(stuID))
{
StudentRec stuRec = new StudentRec();
stuRec.ID = stuID;
stuRec.Name = "" + row["name"];
stuRec.SeatNo = "" + row["seat_no"];
stuRec.ClassName = "" + row["class_name"];
dicStudentByID.Add(stuID, stuRec);
}
// 資料整理
{
StudentRec stuRec = dicStudentByID[stuID];
string schoolYear = "" + row["school_year"];
string semester = "" + row["semester"];
string domain = "" + row["領域"];
string ssKey = schoolYear + semester;
// 沒有領域就忽略
//if (domain != "")
if (listDomainName.Contains(domain) && domain != "特殊需求") //如果領域存在領域資料管理，且不是特殊需求才列印，沒有則忽略-Cynthia 2021.08
{
// 成績
if (!stuRec.dicScoreByDomainBySchoolYear.ContainsKey(ssKey))
{
stuRec.dicScoreByDomainBySchoolYear.Add(ssKey, new Dictionary <string, ScoreRec>());
}
ScoreRec sr = new ScoreRec();
sr.Score = "" + row["成績"];
sr.Power = "" + row["權數"];
sr.OriginScore = "" + row["原始成績"];
stuRec.dicScoreByDomainBySchoolYear[ssKey].Add(domain, sr);
// 領域
if (!stuRec.listDomainFromStu.Contains(domain))
{
stuRec.listDomainFromStu.Add(domain);
}
// 學年度學期
if (!stuRec.dicSchoolYear.Keys.Contains(ssKey))
{
SchoolYearSemester ss = new SchoolYearSemester();
ss.SchoolYear = schoolYear;
ss.Semester = semester;
stuRec.dicSchoolYear.Add(ssKey, ss);
}
}
}
}
// 領域根據對照表做排序
foreach (string id in dicStudentByID.Keys)
{
dicStudentByID[id].listDomainFromStu.Sort(delegate(string a, string b) {
int aIndex = listDomainName.FindIndex(name => name == a);
int bIndex = listDomainName.FindIndex(name => name == b);
if (aIndex > bIndex)
{
return(1);
}
else
{
return(-1);
}
});
}
}
private DataTable FillMergeFiledData(ParameterRec data)
{
DataTable dt = CreateMergeFiledTable();
foreach (string classID in dicClassNameByID.Keys)
{
DataRow row = dt.NewRow();
row["school_name"] = School.ChineseName;
row["class_name"] = dicClassNameByID[classID];
// 班級領域清單
List <string> listDomain = dicDomainNameByClassID[classID];
// 根據領域對照表做排序
listDomain.Sort(delegate(string a, string b)
{
int aIndex = listDomainName.FindIndex(domain => domain == a);
int bIndex = listDomainName.FindIndex(domain => domain == b);
if (aIndex > bIndex)
{
return(1);
}
else if (aIndex == bIndex)
{
return(0);
}
else
{
return(-1);
}
});
// 目前樣板最多支援8個領域 多的不顯示
if (listDomain.Count > 8)
{
listDomain.RemoveRange(8, listDomain.Count - 8);
}
// 領域
{
int d = 1;
foreach (string domain in listDomain)
{
row[$"domain_{d}"] = domain;
d++;
}
}
// 學生
Dictionary <string, Dictionary <string, ScoreRec> > dicStuDomainScore = dicClassStuDomainScore[classID];
int s = 1;
foreach (string stuID in dicStuDomainScore.Keys)
{
StudentRec stuRec = dicStuRecByID[stuID];
row[$"seat_no_{s}"] = stuRec.SeatNo;
row[$"name_{s}"] = stuRec.Name;
int d = 1;
int sc = 0;
int passCount = 0;
decimal totalPower = 0;
decimal totalScore = 0;
// 領域成績
foreach (string domain in listDomain)
{
if (dicStuDomainScore[stuID].ContainsKey(domain))
{
string score = dicStuDomainScore[stuID][domain].Score;
string originScore = dicStuDomainScore[stuID][domain].OriginScore;
string power = dicStuDomainScore[stuID][domain].Power;
row[$"stu_{s}_domain_{d}"] = score == originScore ? score : $"{score}";
//totalScore += FloatParser(score) * FloatParser(power);
//totalPower += FloatParser(power);
// 最後平均使用算術平均不使用加權平均
totalScore += DecimalParser(score);
totalPower += 1;
if (DecimalParser(score) >= 60)
{
passCount++;
}
sc++;
}
d++;
}
if (sc > 0)
{
// 沒有權重就不幫你算
if (totalPower > 0)
{
// 成績計算規則
ScoreCalculator defaultScoreCalculator = new ScoreCalculator(null);
ScoreCalculator studentCalculator = defaultScoreCalculator;
if (calcIDCache.ContainsKey(stuID) && calcCache.ContainsKey(calcIDCache[stuID]))
{
studentCalculator = calcCache[calcIDCache[stuID]];
}
//// 平均成績
//row[$"stu_{s}_avg_score"] = studentCalculator.ParseGraduateScore(totalScore / totalPower); // Math.Round(totalScore / totalPower, 2);
List <decimal> scoreList = new List <decimal>();
if (StudentSemsDomainScoreSumDict.ContainsKey(stuID) && StudentSemsCreditSumDict.ContainsKey(stuID))
{
foreach (string sms in StudentSemsDomainScoreSumDict[stuID].Keys)
{
// 各學期做加權平均
if (StudentSemsCreditSumDict[stuID].ContainsKey(sms))
{
decimal ss = StudentSemsDomainScoreSumDict[stuID][sms];
decimal cc = StudentSemsCreditSumDict[stuID][sms];
if (cc > 0)
{
// 使用領域計算規則進位
scoreList.Add(studentCalculator.ParseDomainScore(ss / cc));
}
}
}
}
// 最後再做算術平均在四捨五入
row[$"stu_{s}_avg_score"] = studentCalculator.ParseGraduateScore(scoreList.Average()); // Math.Round(totalScore / totalPower, 2);
}
// 領域及格數
row[$"stu_{s}_pass_count"] = passCount;
}
s++;
}
dt.Rows.Add(row);
}
return(dt);
}
private void ParseData(DataTable dt, string range)
{
// 學生資料整理
foreach (DataRow row in dt.Rows)
{
string stuID = "" + row["ref_student_id"];
string domain = "" + row["領域"];
string gradeYear = "" + row["grade_year"];
if (domain != "")
{
if (!dicStuRecByID.ContainsKey(stuID))
{
dicStuRecByID.Add(stuID, new StudentRec());
}
StudentRec stuRec = dicStuRecByID[stuID];
stuRec.ID = stuID;
stuRec.GradeYear = gradeYear;
DomainRec domainRec = new DomainRec()
{
Name = domain,
OriginScore = "" + row["原始成績"],
ReTestScore = "" + row["補考成績"],
Score = "" + row["成績"]
};
if (stuRec.dicScoreByDomain.ContainsKey(domain))
{
DomainRec d1 = stuRec.dicScoreByDomain[domain];
DomainRec d2 = domainRec;
DomainRec result = new DomainRec
{
Name = domain,
OriginScore = Calc(d1.OriginScore, d2.OriginScore),
ReTestScore = Calc(d1.ReTestScore, d2.ReTestScore),
Score = Calc(d1.Score, d2.Score)
};
stuRec.dicScoreByDomain[domain] = result;
}
else
{
stuRec.dicScoreByDomain.Add(domain, domainRec);
}
// 領域清單
if (!listDomainFromData.Contains(domain))
{
listDomainFromData.Add(domain);
}
// 年級清單
if (!listGradeYear.Contains(gradeYear))
{
listGradeYear.Add(gradeYear);
}
}
}
// 領域資料排序與刪除
listDomainFromData = DomainListParse(listDomainFromData);
// 統計「學生領域不及格數」、「年級領域不及格數」(區分補考前後)
foreach (string stuID in dicStuRecByID.Keys)
{
StudentRec stuRec = dicStuRecByID[stuID];
string gradeYear = stuRec.GradeYear;
foreach (string domain in listDomainFromData)
{
if (stuRec.dicScoreByDomain.ContainsKey(domain))
{
DomainRec domainRec = stuRec.dicScoreByDomain[domain];
#region 學生領域不及格數
{
if (domainRec.OriginScore != "")
{
stuRec.OriginScoreUnPassCount += FloatParse(domainRec.OriginScore) < 60 ? 1 : 0;
}
if (domainRec.ReTestScore != "")
{
stuRec.ReTestScoreUnPassCount += FloatParse(domainRec.ReTestScore) < 60 ? 1 : 0;
}
if (domainRec.Score != "")
{
stuRec.ScoreUnPassCount += FloatParse(domainRec.Score) < 60 ? 1 : 0;
}
}
#endregion
#region 年級領域不及格數
{
string score;
if (range == "補考前")
{
score = domainRec.OriginScore;
}
else
{
score = domainRec.Score;
}
if (score != "")
{
// 年級
if (!dicUnPassCountByDomainGradeYear.ContainsKey(gradeYear))
{
dicUnPassCountByDomainGradeYear.Add(stuRec.GradeYear, new Dictionary <string, int>());
}
// 領域
Dictionary <string, int> dicUnPassCountByDomain = dicUnPassCountByDomainGradeYear[gradeYear];
if (!dicUnPassCountByDomain.ContainsKey(domain))
{
dicUnPassCountByDomain.Add(domain, 0);
}
dicUnPassCountByDomain[domain] += FloatParse(score) < 60 ? 1 : 0;
}
}
#endregion
}
}
}
// 領域不及格數人數統計 (區分補考前後)
foreach (string stuID in dicStuRecByID.Keys)
{
StudentRec stuRec = dicStuRecByID[stuID];
int unPassCount = 0;
if (range == "補考前")
{
unPassCount = stuRec.OriginScoreUnPassCount;
}
else
{
unPassCount = stuRec.ScoreUnPassCount;
}
if (unPassCount > 0)
{
if (!dicUnPassCountByUnPassGradeYear.ContainsKey(stuRec.GradeYear))
{
dicUnPassCountByUnPassGradeYear.Add(stuRec.GradeYear, new Dictionary <int, int>());
}
Dictionary <int, int> dicUnPassCountByUnPass = dicUnPassCountByUnPassGradeYear[stuRec.GradeYear];
if (!dicUnPassCountByUnPass.ContainsKey(unPassCount))
{
dicUnPassCountByUnPass.Add(unPassCount, 0);
}
dicUnPassCountByUnPass[unPassCount] += 1;
}
}
// 總計 領域不及格數 (區分補考前後)
foreach (string domain in listDomainFromData)
{
if (!dicTotalCountByDomain.ContainsKey(domain))
{
dicTotalCountByDomain.Add(domain, 0);
}
}
foreach (string gradeyear in dicUnPassCountByDomainGradeYear.Keys)
{
foreach (string domain in dicUnPassCountByDomainGradeYear[gradeyear].Keys)
{
dicTotalCountByDomain[domain] += dicUnPassCountByDomainGradeYear[gradeyear][domain];
}
}
// 總計 不及格領域數總人數 (區分補考前後)
for (int i = 1; i <= listDomainFromData.Count; i++)
{
dicTotalCountByUnPass.Add(i, 0);
}
foreach (string gradeyear in dicUnPassCountByUnPassGradeYear.Keys)
{
foreach (int unpass in dicUnPassCountByUnPassGradeYear[gradeyear].Keys)
{
dicTotalCountByUnPass[unpass] += dicUnPassCountByUnPassGradeYear[gradeyear][unpass];
}
}
// 年級清單排序
listGradeYear.Sort();
}
