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