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);
}
}
}
}
/// <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>
/// 將資料解析為 doc merge 格式
/// </summary>
private DataTable FillMergeFiledData()
{
string schoolName = School.ChineseName;
DataTable dt = CreateMergeFieldTable();
foreach (string id in dicStudentByID.Keys)
{
DataRow row = dt.NewRow();
row["year"] = DateTime.Now.Year - 1911;
row["month"] = DateTime.Now.Month;
row["day"] = DateTime.Now.Day;
row["school_name"] = schoolName;
row["class_name"] = dicStudentByID[id].ClassName;
row["seat_no"] = dicStudentByID[id].SeatNo;
row["student_name"] = dicStudentByID[id].Name;
// 領域及格數
int passCount = 0;
// 領域加權平均清單
List <decimal> listDomainAvgScore = new List <decimal>();
// 領域
int d = 1;
foreach (string domain in dicStudentByID[id].listDomainFromStu)
{
// 領域名稱
row[$"{d}_domain"] = domain;
// 領域個學期分數與權重清單
List <ScoreRec> listScore = new List <ScoreRec>();
// 學年度學期
int s = 1;
foreach (string key in dicStudentByID[id].dicSchoolYear.Keys)
{
SchoolYearSemester ss = dicStudentByID[id].dicSchoolYear[key];
if (d == 1)
{
row[$"{s}_school_year"] = $"{ss.SchoolYear}學年度";
row[$"{s}_semester"] = $"第{ss.Semester}學期";
}
// 成績
if (dicStudentByID[id].dicScoreByDomainBySchoolYear.ContainsKey(key))
{
if (dicStudentByID[id].dicScoreByDomainBySchoolYear[key].ContainsKey(domain))
{
string score = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Score;
string originScore = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].OriginScore;
string power = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Power;
row[$"{d}_domain_{s}_score"] = score == originScore ? score : $"*{score}";
row[$"{d}_d_{s}_p"] = power;
ScoreRec sr = new ScoreRec();
sr.Score = score;
sr.Power = power;
listScore.Add(sr);
}
}
s++;
}
// 領域平均
if (listScore.Count > 0)
{
decimal totalScore = 0;
int domainScoreCount = 0;
foreach (ScoreRec sr in listScore)
{
totalScore += DecimalParser(sr.Score);
domainScoreCount++;
}
if (totalScore != 0)
{
decimal avgScore = Math.Round(totalScore / domainScoreCount, 2, MidpointRounding.AwayFromZero);
row[$"{d}_domain_avg"] = avgScore;
listDomainAvgScore.Add(avgScore);
if (avgScore >= 60)
{
passCount++;
}
}
}
// 及格數
row["pass_domain_count"] = passCount;
d++;
}
// 學期各領域平均
// 個學期領域平均的算術平均
decimal allTotalScore = 0;
int scoreCount = 0;
int sIndex = 1;
foreach (string key in dicStudentByID[id].dicSchoolYear.Keys)
{
// 學年度學期各領域成績資料
List <ScoreRec> listScore = new List <ScoreRec>();
foreach (string domain in dicStudentByID[id].listDomainFromStu)
{
if (dicStudentByID[id].dicScoreByDomainBySchoolYear.ContainsKey(key))
{
if (dicStudentByID[id].dicScoreByDomainBySchoolYear[key].ContainsKey(domain))
{
string score = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Score;
string power = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Power;
ScoreRec sr = new ScoreRec();
sr.Score = score;
sr.Power = power;
listScore.Add(sr);
}
}
}
if (listScore.Count > 0)
{
decimal totalScore = 0;
decimal totalPower = 0;
foreach (ScoreRec sr in listScore)
{
totalScore += DecimalParser(sr.Score) * DecimalParser(sr.Power);
totalPower += DecimalParser(sr.Power);
}
// 沒有權重就不幫你算
if (totalPower != 0)
{
decimal score = Math.Round(totalScore / totalPower, 2, MidpointRounding.AwayFromZero);
row[$"{sIndex}_all_domain_avg"] = score;
allTotalScore += score;
scoreCount++;
}
}
sIndex++;
}
// 總平均(算術平均)
if (listDomainAvgScore.Count > 0)
{
//foreach (double score in listDomainAvgScore)
//{
// totalScore += score;
//}
decimal avgScore = Math.Round(allTotalScore / scoreCount, 2, MidpointRounding.AwayFromZero);
row["all_domain_avg"] = avgScore;
}
dt.Rows.Add(row);
}
return(dt);
}
/// <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);
}
});
}
}
