private void fillBasicInfo(StudentScoreInfo studScore, DataRow row)
{
//1. fill student basic info
row["CN"] = studScore.CustodianName;
row["POSTALCODE"] = studScore.Address.ZipCode;
row["POSTALADDRESS"] = studScore.Address.GetFullAddress();
row["科別"] = studScore.Dept_name;
row["年級"] = studScore.GradeYear;
row["班級"] = studScore.Class;
row["座號"] = studScore.SeatNO;
row["學號"] = studScore.StudnetNumber;
row["姓名"] = studScore.StudentName;
row["年度"] = studScore.SchoolYear;
row["學期"] = studScore.Semester;
row["考試"] = studScore.ExamName;
row["導師"] = studScore.ClassTeacher;
row["考試1"] = studScore.ExamName;
row["科別1"] = studScore.Dept_name;
row["年級1"] = studScore.GradeYear;
row["班級1"] = studScore.Class;
row["座號1"] = studScore.SeatNO;
row["學號1"] = studScore.StudnetNumber;
row["姓名1"] = studScore.StudentName;
}
/// <summary>
/// 先將資料放入DataTable
/// </summary>
/// <param name="studScore"></param>
/// <param name="dt"></param>
private void FillDataToDataTable(StudentScoreInfo studScore, DataTable dt)
{
DataRow row = dt.NewRow();
this.fillBasicInfo(studScore, row);
this.fillSubjectScore(studScore, row);//填科目成績
this.fillTotalScore(studScore, row);
dt.Rows.Add(row);
}
private void fillTotalScore(StudentScoreInfo studScore, DataRow row)
{
TotaltScoreInfo totalScore = studScore.TotaltScoreInfo;
if (totalScore == null)
{
return;
}
row["班級名次"] = totalScore.ClassRank;
row["科組名次"] = String.IsNullOrEmpty(totalScore.TeamRank) ? totalScore.DeptRank : totalScore.TeamRank;
row["年級名次"] = totalScore.GradeRank;
row["班級人數"] = totalScore.ClassMatrixCount;
row["科組人數"] = String.IsNullOrEmpty(totalScore.TeamMatrixCount) ? totalScore.DeptMatrixCount : totalScore.TeamMatrixCount;
row["年級人數"] = totalScore.GradeMatrixCount;
row["附註"] = "";
row["S04_Ytdbgoc_Mid_Teachsay"] = "";
}
private void fillSubjectScore(StudentScoreInfo studScore, DataRow row)
{
// find subjects he tales
List <SubjectScoreFSceTake> subjScores = studScore.DicSubjectScoreFScAttend.Values.ToList <SubjectScoreFSceTake>();
subjScores.Sort(new SubjectScoreComparaer(
"國文"
, "英文"
, "數學"
, "理化"
, "生物"
, "社會"
, "物理"
, "化學"
, "歷史"
, "地理"
, "公民"
));
int rowNo = 1;
decimal totalScore = 0;
decimal totalCredits = 0;
int failSubjectCount = 0;
decimal failCredits = 0;
try
{
foreach (SubjectScoreFSceTake subjScoreFSceTake in subjScores)
{
//如果科目筆數 > 15 加入錯誤訊息裡 _ListErrors
if (rowNo > 15)
{
if (!_ListErrors.Any(x => x.StudentID == studScore.StudentID))
{
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.Type = "科目數大於15";
errorInfo.Detail = $"本次定期評量列印科目數大於15，列印樣板不適用";
errorInfo.StudentID = studScore.StudentID;
errorInfo.StudentNumber = studScore.StudnetNumber;
errorInfo.ListError.Add(errorInfo);
_ListErrors.Add(errorInfo);
}
continue;
}
String levelString = getLevelString(subjScoreFSceTake.Level);
row[$"科目{rowNo}"] = $"{subjScoreFSceTake.SubjectName}{levelString}";
row[$"學分{rowNo}"] = subjScoreFSceTake.Credit;
if (subjScoreFSceTake.Score == "" || subjScoreFSceTake.Score == "-1") //"" 為沒有輸入成績 && "-1"為缺考 (畫面顯示為"缺");
{
if (subjScoreFSceTake.Score == "")
{
row[$"成績{rowNo}"] = "未輸入";
}
else if (subjScoreFSceTake.Score == "-1")
{
row[$"成績{rowNo}"] = "缺考";
}
}
else
{//如果有分數 在判斷是否及格
row[$"成績{rowNo}"] = subjScoreFSceTake.IsPassing == "true" ? subjScoreFSceTake.Score : "*" + subjScoreFSceTake.Score;
}
if (studScore.DicSubjectRankInfo.ContainsKey(subjScoreFSceTake.SubjectName))
{
SubjectScoreInfos subjScoreInfo = studScore.DicSubjectRankInfo[subjScoreFSceTake.SubjectName];
//填入班級名次 如果沒有成績就填入""
if (subjScoreFSceTake.Score == "" || subjScoreFSceTake.Score == "-1")
{
row[$"班級名次{rowNo}"] = "";
}
else
{
row[$"班級名次{rowNo}"] = subjScoreInfo.ClassRank;
}
//填入科組名次 如果有沒有成績就填 ""
if (subjScoreFSceTake.Score == "" || subjScoreFSceTake.Score == "-1")
{
row[$"科組名次{rowNo}"] = "";
}
else //如果有成績就填 有類排就填類排。
{
if (subjScoreInfo.TeamOrDeptRank.ContainsKey("類別1排名"))
{
row[$"科組名次{rowNo}"] = subjScoreInfo.TeamOrDeptRank["類別1排名"];
}
else if (subjScoreInfo.TeamOrDeptRank.ContainsKey("科排名"))
{
row[$"科組名次{rowNo}"] = subjScoreInfo.TeamOrDeptRank["科排名"];
}
}
row[$"年級名次{rowNo}"] = subjScoreInfo.GradeRank;
row[$"班級平均{rowNo}"] = Math.Round(double.Parse(subjScoreInfo.Avg), 2);
row[$"及格人數{rowNo}"] = subjScoreFSceTake.PassingCount;
row[$"不及格人數{rowNo}"] = subjScoreFSceTake.NotPassingCount;
}
decimal score = (String.IsNullOrWhiteSpace(subjScoreFSceTake.Score) ? 0 : decimal.Parse(subjScoreFSceTake.Score));
decimal credit = (String.IsNullOrWhiteSpace(subjScoreFSceTake.Credit) || String.IsNullOrWhiteSpace(subjScoreFSceTake.Score)) ? 0 : decimal.Parse(subjScoreFSceTake.Credit);
if (subjScoreFSceTake.Score == "")
{
Console.WriteLine("缺考");
}
totalScore += score * credit;
totalCredits += credit;
//如果成績大是及格
if (subjScoreFSceTake.IsPassing == "false")
{
failCredits += credit;
failSubjectCount += 1;
}
rowNo++;
}
row["加權總分"] = totalScore;
row["加權平均"] = (totalCredits == 0) ? "" : Math.Round(totalScore / totalCredits, 1).ToString();
row["不及格學分數"] = failCredits;
row["不及格科目數"] = failSubjectCount;
row["修習學分數"] = totalCredits;
if (_ListErrors.Count > 0)
{
ErrorInfo err = new ErrorInfo();
// = _ListErrors;
this.onError(_ListErrors);
}
}
catch (Exception ex)
{
if (this.onError != null)
{
ErrorInfo err = new ErrorInfo();
err.Type = "Exception";
err.Detail = ex.Message;
this._ListErrors.Add(err);
this.onError(_ListErrors);
}
}
}
/// <summary>
/// 取得學生基本資訊：班級, 座號, 姓名, 學號, 班導師, 監護人, 聯絡地址
/// 如果是當學期，則從學生，班級，和教師資料表取得。
/// 否則： 班級，座號，班導師要從 學期歷程讀取，以防止列印前幾個學期時讀錯資料。
/// </summary>
private void loadStudentsInfo()
{
String strSQL = "";
if (this.isCurrentSemester)
{
strSQL = @"select
stud.id,
cls.grade_year,
cls.class_name,
dept.name dept_name,
stud.seat_no,
tea.teacher_name AS class_teacher,
stud.name,
stud.student_number,
stud.custodian_name,
stud.mailing_address
from student stud
inner join class cls on stud.ref_class_id = cls.id
left outer join dept on cls.ref_dept_id = dept.id
left outer join teacher tea on cls.ref_teacher_id = tea.id
where stud.id in ({0})
ORDER BY
cls.grade_year
, cls.display_order
, cls.class_name
, stud.seat_no
, stud.id
";
strSQL = string.Format(strSQL, String.Join(",", this.studentIDs));
}
else
{
strSQL = @"
WITH student_sp AS(
SELECT
id
,name
, student_number
, custodian_name
, mailing_address
FROM
student
WHERE id IN({0})
) ,stu_history AS(
SELECT history.id,
('0'||array_to_string(xpath('/History/@SchoolYear', history_xml), '')::text)::integer as school_year,
('0'||array_to_string(xpath('/History/@Semester', history_xml), '')::text)::integer as semester,
('0'||array_to_string(xpath('/History/@GradeYear', history_xml), '')::text)::integer as grade_year,
array_to_string(xpath('/History/@ClassName', history_xml), '')::text as class_name,
array_to_string(xpath('/History/@DeptName', history_xml), '')::text as dept_name,
array_to_string(xpath('/History/@SeatNo', history_xml), '')::text as seat_no,
array_to_string(xpath('/History/@Teacher', history_xml), '')::text as class_teacher
FROM (
SELECT id, unnest(xpath('/root/History', xmlparse(content '<root>'||sems_history||'</root>'))) as history_xml
FROM student
) as history
WHERE ('0'||array_to_string(xpath('/History/@SchoolYear', history_xml), '')::text)::integer = {1}
AND ('0'||array_to_string(xpath('/History/@Semester', history_xml), '')::text)::integer = {2}
)SELECT
his.*
,sp.name
, sp.student_number
, sp.custodian_name
, sp.mailing_address
FROM
student_sp sp
LEFT JOIN stu_history his ON his.id = sp.id" ;
strSQL = string.Format(strSQL, String.Join(",", this.studentIDs), this.SchoolYear, this.Semester);
}
DataTable dt = tool._Q.Select(strSQL);
foreach (DataRow dr in dt.Rows)
{
string studentID = "" + dr["id"];
if (!this._DicStudentScoreInfo.ContainsKey(studentID))
{
StudentScoreInfo stuScoreInfo = new StudentScoreInfo("" + dr["id"]);
//如果不是
if (!this.isCurrentSemester)
{
stuScoreInfo.SchoolYear = "" + dr["school_year"];
stuScoreInfo.Semester = "" + dr["semester"];
}
else
{
stuScoreInfo.SchoolYear = this.SchoolYear;
stuScoreInfo.Semester = this.Semester;
}
stuScoreInfo.StudentName = "" + dr["name"];
stuScoreInfo.GradeYear = "" + dr["grade_year"];
stuScoreInfo.Dept_name = "" + dr["dept_name"];
stuScoreInfo.Class = ("" + dr["class_name"]).Length == 3 ? ("" + dr["class_name"]).Substring(1, 2) : ("" + dr["class_name"]);
stuScoreInfo.StudnetNumber = "" + dr["student_number"];
stuScoreInfo.ExamName = this.Exam.Name;
stuScoreInfo.SeatNO = "" + dr["seat_no"];
stuScoreInfo.ClassTeacher = "" + dr["class_teacher"];
stuScoreInfo.CustodianName = "" + dr["custodian_name"];
stuScoreInfo.Address = new AddressInfo("" + dr["mailing_address"]);
this._DicStudentScoreInfo.Add(studentID, stuScoreInfo);
}
else
{
Console.WriteLine($"學生資料重複");
}
}
}