public static GradePeriodicalExamGPARecord GetGradePeriodicalExamGPARecord(int SchoolYear, int Semester, int Grade, int Exam)
{
string key = SchoolYear + "#" + Semester + "#" + Grade + "#" + Exam;
if (dcpegpar.ContainsKey(key))
{
return(dcpegpar[key]);
}
DataTable dt = tool._Q.Select(@"select student.id,student.english_name,student.name,student.student_number,student.seat_no,class.class_name,teacher.teacher_name,class.grade_year,course.id as course_id,course.period as period,course.credit as credit,course.subject as subject,$ischool.subject.list.group as group,$ischool.subject.list.english_name as subject_english_name,$ischool.subject.list.type as type,sc_attend.ref_student_id as student_id,sce_take.ref_sc_attend_id as sc_attend_id,sce_take.ref_exam_id as exam_id,xpath_string(sce_take.extension,'//Score') as score
from sc_attend
join sce_take on sce_take.ref_sc_attend_id=sc_attend.id
join course on course.id=sc_attend.ref_course_id
join $ischool.course.extend on $ischool.course.extend.ref_course_id=course.id
left join student on student.id=sc_attend.ref_student_id
left join class on student.ref_class_id=class.id
left join $ischool.subject.list on course.subject=$ischool.subject.list.name
left join teacher on teacher.id=class.ref_teacher_id
where class.grade_year =" + Grade + " and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sce_take.ref_exam_id = " + Exam);
Dictionary <string, List <CustomSCETakeRecord> > dscetr = new Dictionary <string, List <CustomSCETakeRecord> >();
foreach (DataRow row in dt.Rows)
{
string id = "" + row["id"];
if (!dscetr.ContainsKey(id))
{
dscetr.Add(id, new List <CustomSCETakeRecord>());
}
decimal score;
//#TODO 2014/11/26 確認為略過
if (decimal.TryParse("" + row["score"], out score))
{
dscetr[id].Add(new CustomSCETakeRecord()
{
RefStudentID = id,
Name = "" + row["name"],
EnglishName = "" + row["english_name"],
StudentNumber = "" + row["student_number"],
SeatNo = "" + row["seat_no"],
ClassName = "" + row["class_name"],
TeacherName = "" + row["teacher_name"],
GradeYear = "" + row["grade_year"],
Subject = "" + row["subject"],
Score = score,
CourseId = "" + row["course_id"],
CoursePeriod = int.Parse("" + row["period"]),
CourseCredit = int.Parse("" + row["credit"]),
SubjectEnglishName = "" + row["subject_english_name"],
CourseGroup = "" + row["group"],
CourseType = JH.IBSH.Report.PeriodicalExam.GradePeriodicalExamGPA.StringToSubjectType("" + row["type"]),
ExamId = "" + row["exam_id"]
});
}
}
Dictionary <string, decimal> StudentGPA = new Dictionary <string, decimal>();
foreach (KeyValuePair <string, List <CustomSCETakeRecord> > row in dscetr)
{
int personalCreditCount = 0;
decimal personalGPACount = 0;
foreach (CustomSCETakeRecord item in row.Value)
{
switch (item.GradeYear)
{
case "9":
case "10":
case "11":
case "12":
personalCreditCount += item.CourseCredit;
CourseGradeB.Tool.GPA _gpa = CourseGradeB.Tool.GPA.Eval(item.Score);
if (CourseGradeB.Tool.SubjectType.Honor == item.CourseType)
{
personalGPACount += item.CourseCredit * _gpa.Honors;
}
else if (CourseGradeB.Tool.SubjectType.Regular == item.CourseType)
{
personalGPACount += item.CourseCredit * _gpa.Regular;
}
break;
}
}
StudentGPA.Add(row.Key, personalCreditCount > 0 ? personalGPACount / personalCreditCount : 0);
}
dcpegpar[key] = new GradePeriodicalExamGPARecord(SchoolYear, Semester, Grade, Exam, StudentGPA);
return(dcpegpar[key]);
}
void _bgw_DoWork(object sender, DoWorkEventArgs e)
{
filter f = (filter)e.Argument;
Document document = new Document();
byte[] template3_6 = (ReportConfiguration3_6.Template != null) //3-6單頁範本
? ReportConfiguration3_6.Template.ToBinary()
: new Campus.Report.ReportTemplate(Properties.Resources._6樣版, Campus.Report.TemplateType.Word).ToBinary();
byte[] template7_8 = (ReportConfiguration7_8.Template != null) //7-8單頁範本
? ReportConfiguration7_8.Template.ToBinary()
: new Campus.Report.ReportTemplate(Properties.Resources._8樣版, Campus.Report.TemplateType.Word).ToBinary();
byte[] template9_12 = (ReportConfiguration9_12.Template != null) //9-12單頁範本
? ReportConfiguration9_12.Template.ToBinary()
: new Campus.Report.ReportTemplate(Properties.Resources._9樣版, Campus.Report.TemplateType.Word).ToBinary();
if (K12.Presentation.NLDPanels.Student.SelectedSource.Count <= 0)
{
return;
}
List <string> sids = K12.Presentation.NLDPanels.Student.SelectedSource;
int SchoolYear = f.sys.SchoolYear;
int Semester = f.sys.Semester;
int Exam = f.exam;
string ExamText = f.examText;
string gradeSection = f.gradeSection;
string sql = @"select student.id,student.english_name,student.name,student.student_number,student.seat_no,class.class_name,teacher.teacher_name,class.grade_year,course.id as course_id,course.period as period,course.credit as credit,course.subject as subject,$ischool.subject.list.group as group,$ischool.subject.list.type as type,$ischool.subject.list.english_name as subject_english_name,sc_attend.ref_student_id as student_id,sce_take.ref_sc_attend_id as sc_attend_id,sce_take.ref_exam_id as exam_id,xpath_string(sce_take.extension,'//Score') as score
from sc_attend
join sce_take on sce_take.ref_sc_attend_id=sc_attend.id
join course on course.id=sc_attend.ref_course_id
join $ischool.course.extend on $ischool.course.extend.ref_course_id=course.id
left join student on student.id=sc_attend.ref_student_id
left join class on student.ref_class_id=class.id
left join $ischool.subject.list on course.subject=$ischool.subject.list.name
left join teacher on teacher.id=class.ref_teacher_id
where sc_attend.ref_student_id in (" + string.Join(",", sids) + ") and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sce_take.ref_exam_id = " + Exam + "";
DataTable dt = tool._Q.Select(sql);
//return;
Dictionary <string, List <CustomSCETakeRecord> > dscetr = new Dictionary <string, List <CustomSCETakeRecord> >();
foreach (DataRow row in dt.Rows)
{
string id = "" + row["id"];
if (!dscetr.ContainsKey(id))
{
dscetr.Add(id, new List <CustomSCETakeRecord>());
}
decimal tmp_score;
int tmp_period, tmp_credit;
dscetr[id].Add(new CustomSCETakeRecord()
{
RefStudentID = id,
Name = "" + row["name"],
EnglishName = "" + row["english_name"],
StudentNumber = "" + row["student_number"],
SeatNo = "" + row["seat_no"],
ClassName = "" + row["class_name"],
TeacherName = "" + row["teacher_name"],
GradeYear = "" + row["grade_year"],
Subject = "" + row["subject"],
Score = decimal.TryParse("" + row["score"], out tmp_score) ? tmp_score : tmp_score,
CourseId = "" + row["course_id"],
CoursePeriod = int.TryParse("" + row["period"], out tmp_period) ? tmp_period : tmp_period,
CourseCredit = int.TryParse("" + row["credit"], out tmp_credit) ? tmp_credit : tmp_credit,
SubjectEnglishName = "" + row["subject_english_name"],
CourseGroup = "" + row["group"],
CourseType = JH.IBSH.Report.PeriodicalExam.GradePeriodicalExamGPA.StringToSubjectType("" + row["type"]),
ExamId = "" + row["exam_id"]
});
}
Dictionary <string, object> mailmerge = new Dictionary <string, object>();
List <StudentRecord> srl = K12.Data.Student.SelectByIDs(sids);
srl.Sort(delegate(StudentRecord a, StudentRecord b)
{
StudentRecord aStudent = a;
ClassRecord aClass = a.Class;
StudentRecord bStudent = b;
ClassRecord bClass = b.Class;
string aa = aClass == null ? (string.Empty).PadLeft(10, '0') : (aClass.Name).PadLeft(10, '0');
aa += aStudent == null ? (string.Empty).PadLeft(3, '0') : (aStudent.SeatNo + "").PadLeft(3, '0');
aa += aStudent == null ? (string.Empty).PadLeft(10, '0') : (aStudent.StudentNumber).PadLeft(10, '0');
string bb = bClass == null ? (string.Empty).PadLeft(10, '0') : (bClass.Name).PadLeft(10, '0');
bb += bStudent == null ? (string.Empty).PadLeft(3, '0') : (bStudent.SeatNo + "").PadLeft(3, '0');
bb += bStudent == null ? (string.Empty).PadLeft(10, '0') : (bStudent.StudentNumber).PadLeft(10, '0');
return(aa.CompareTo(bb));
});
foreach (StudentRecord sr in srl)
{
mailmerge.Clear();
// mailmerge.Add("學年", SchoolYear);
mailmerge.Add("學年", (SchoolYear + 1911) + "-" + (SchoolYear + 1912));
mailmerge.Add("學期", Semester);
mailmerge.Add("學段", ExamText);
mailmerge.Add("班級", sr.Class != null ? sr.Class.Name : "");
mailmerge.Add("級別", "");
if (sr.Class != null)
{
mailmerge["級別"] = sr.Class.GradeYear;
}
mailmerge.Add("學生系統編號", sr.ID);
mailmerge.Add("學號", sr.StudentNumber);
mailmerge.Add("座號", sr.SeatNo);
mailmerge.Add("姓名", sr.Name);
mailmerge.Add("英文名", sr.EnglishName);
mailmerge.Add("學校名稱", School.ChineseName);
mailmerge.Add("學校英文名稱", School.EnglishName);
Document each;
int subjecti = 1;
int parti = 1;
//if (dscetr.ContainsKey(sr.ID) && sr.Class != null && sr.Class.GradeYear.HasValue)
//{
#region 學生成績
switch (gradeSection)
{
case "3~6":
each = new Document(new MemoryStream(template3_6));
if (dscetr.ContainsKey(sr.ID))
{
foreach (CustomSCETakeRecord item in dscetr[sr.ID])
{
mailmerge.Add(string.Format("科目{0}", subjecti), item.Subject + " " + item.SubjectEnglishName);
mailmerge.Add(string.Format("成績{0}", subjecti), CourseGradeB.Tool.GPA.Eval(item.Score).Letter);
subjecti++;
}
}
break;
case "7~8":
each = new Document(new MemoryStream(template7_8));
if (dscetr.ContainsKey(sr.ID))
{
foreach (CustomSCETakeRecord item in dscetr[sr.ID])
{
mailmerge.Add(string.Format("科目{0}", subjecti), item.Subject + " " + item.SubjectEnglishName);
mailmerge.Add(string.Format("成績{0}", subjecti), CourseGradeB.Tool.GPA.Eval(item.Score).Letter);
subjecti++;
}
}
break;
case "9~12":
each = new Document(new MemoryStream(template9_12));
int personalCreditCount = 0;
decimal personalGPACount = 0, personalAverageCount = 0;
if (dscetr.ContainsKey(sr.ID))
{
foreach (CustomSCETakeRecord item in dscetr[sr.ID])
{
mailmerge.Add(string.Format("科目{0}", subjecti), item.Subject + " " + item.SubjectEnglishName);
mailmerge.Add(string.Format("成績{0}", subjecti), item.Score);
personalCreditCount += item.CourseCredit;
CourseGradeB.Tool.GPA _gpa = CourseGradeB.Tool.GPA.Eval(item.Score);
if (CourseGradeB.Tool.SubjectType.Honor == item.CourseType)
{
personalGPACount += item.CourseCredit * _gpa.Honors;
}
else if (CourseGradeB.Tool.SubjectType.Regular == item.CourseType)
{
personalGPACount += item.CourseCredit * _gpa.Regular;
}
personalAverageCount += item.CourseCredit * item.Score;
subjecti++;
}
if (personalCreditCount > 0)
{
mailmerge.Add("科目平均", decimal.Round(personalAverageCount / personalCreditCount, 2, MidpointRounding.AwayFromZero));
mailmerge.Add("GPA", decimal.Round(personalGPACount / personalCreditCount, 2, MidpointRounding.AwayFromZero));
}
if (sr.Class != null && sr.Class.GradeYear.HasValue)
{
GradePeriodicalExamGPARecord gpegpar = GradePeriodicalExamGPA.GetGradePeriodicalExamGPARecord(SchoolYear, Semester, sr.Class.GradeYear.Value, Exam);
List <GPADistributionPart> gpad = GradePeriodicalExamGPA.toGPADistribution(gpegpar);
gpad.Reverse();
foreach (GPADistributionPart gpadp in gpad)
{
mailmerge.Add(string.Format("GPA分段{0}", parti), decimal.Round(gpadp.GPACeiling, 2, MidpointRounding.AwayFromZero) + "~" + decimal.Round(gpadp.GPAFloor, 2, MidpointRounding.AwayFromZero));
mailmerge.Add(string.Format("GPA計數{0}", parti), gpadp.Count);
parti++;
}
}
}
break;
default:
return;
}
#endregion
for (; parti <= 5; parti++)
{
mailmerge.Add(string.Format("GPA分段{0}", parti), "");
mailmerge.Add(string.Format("GPA計數{0}", parti), "");
}
mailmerge.Add(string.Format("科目{0}", subjecti), "(以下空白)");
mailmerge.Add(string.Format("成績{0}", subjecti), "");
for (subjecti++; subjecti <= 20; subjecti++)
{
mailmerge.Add(string.Format("科目{0}", subjecti), "");
mailmerge.Add(string.Format("成績{0}", subjecti), "");
}
each.MailMerge.Execute(mailmerge.Keys.ToArray(), mailmerge.Values.ToArray());
document.Sections.Add(document.ImportNode(each.FirstSection, true));
}
document.Sections.RemoveAt(0);
e.Result = document;
}
void _bgw_DoWork(object sender, DoWorkEventArgs e)
{
filter f = (filter)e.Argument;
Document document = new Document();
Document template = (custConfigs[current].Template != null) //單頁範本
? custConfigs[current].Template.ToDocument()
: new Campus.Report.ReportTemplate(Properties.Resources.樣板, Campus.Report.TemplateType.Word).ToDocument();
if (K12.Presentation.NLDPanels.Student.SelectedSource.Count <= 0)
{
return;
}
List <string> sids = K12.Presentation.NLDPanels.Student.SelectedSource;
int SchoolYear = f.sys.SchoolYear;
int Semester = f.sys.Semester;
int Exam = f.exam;
DataTable dt = tool._Q.Select(@"select student.id,student.english_name,student.name,student.student_number,student.seat_no,class.class_name,teacher.teacher_name,class.grade_year,course.id as course_id,course.period as period,course.credit as credit,course.subject as subject,$ischool.subject.list.group as group,$ischool.subject.list.type as type,$ischool.subject.list.english_name as subject_english_name,sc_attend.ref_student_id as student_id,sce_take.ref_sc_attend_id as sc_attend_id,sce_take.ref_exam_id as exam_id,xpath_string(sce_take.extension,'//Score') as score
from sc_attend
join sce_take on sce_take.ref_sc_attend_id=sc_attend.id
join course on course.id=sc_attend.ref_course_id
join $ischool.course.extend on $ischool.course.extend.ref_course_id=course.id
left join student on student.id=sc_attend.ref_student_id
left join class on student.ref_class_id=class.id
left join $ischool.subject.list on course.subject=$ischool.subject.list.name
left join teacher on teacher.id=class.ref_teacher_id
where sc_attend.ref_student_id in (" + string.Join(",", sids) + ") and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sce_take.ref_exam_id = " + Exam + "");
Dictionary <string, List <CustomSCETakeRecord> > dscetr = new Dictionary <string, List <CustomSCETakeRecord> >();
foreach (DataRow row in dt.Rows)
{
string id = "" + row["id"];
if (!dscetr.ContainsKey(id))
{
dscetr.Add(id, new List <CustomSCETakeRecord>());
}
dscetr[id].Add(new CustomSCETakeRecord()
{
RefStudentID = id,
Name = "" + row["name"],
EnglishName = "" + row["english_name"],
StudentNumber = "" + row["student_number"],
SeatNo = "" + row["seat_no"],
ClassName = "" + row["class_name"],
TeacherName = "" + row["teacher_name"],
GradeYear = "" + row["grade_year"],
Subject = "" + row["subject"],
Score = decimal.Parse("" + row["score"]),
CourseId = "" + row["course_id"],
CoursePeriod = int.Parse("" + row["period"]),
CourseCredit = int.Parse("" + row["credit"]),
SubjectEnglishName = "" + row["subject_english_name"],
CourseGroup = "" + row["group"],
CourseType = JH.IBSH.Report.PeriodicalExam.GradePeriodicalExamGPA.StringToSubjectType("" + row["type"]),
ExamId = "" + row["exam_id"]
});
}
Dictionary <string, object> mailmerge = new Dictionary <string, object>();
foreach (KeyValuePair <string, List <CustomSCETakeRecord> > row in dscetr)
{
mailmerge.Clear();
int ClassGradeYear = int.Parse(row.Value[0].GradeYear); //should be the same
mailmerge.Add("學年", SchoolYear);
mailmerge.Add("學期", Semester);
mailmerge.Add("學段", Exam);
mailmerge.Add("班級", row.Value[0].ClassName);
mailmerge.Add("座號", row.Value[0].SeatNo);
mailmerge.Add("姓名", row.Value[0].Name);
mailmerge.Add("英文名", row.Value[0].EnglishName);
mailmerge.Add("學校名稱", School.ChineseName);
mailmerge.Add("學校英文名稱", School.EnglishName);
#region 學生成績
int subjecti = 1;
switch (ClassGradeYear)
{
case 3:
case 4:
case 5:
case 6:
case 7:
case 8:
foreach (CustomSCETakeRecord item in row.Value)
{
mailmerge.Add(string.Format("科目{0}", subjecti), item.Name + " " + item.SubjectEnglishName);
mailmerge.Add(string.Format("成績{0}", subjecti), CourseGradeB.Tool.GPA.Eval(item.Score).Letter);
subjecti++;
}
break;
case 9:
case 10:
case 11:
case 12:
int personalCreditCount = 0;
decimal personalGPACount = 0, personalAverageCount = 0;
foreach (CustomSCETakeRecord item in row.Value)
{
mailmerge.Add(string.Format("科目{0}", subjecti), item.Name + " " + item.SubjectEnglishName);
mailmerge.Add(string.Format("成績{0}", subjecti), item.Score);
personalCreditCount += item.CourseCredit;
CourseGradeB.Tool.GPA _gpa = CourseGradeB.Tool.GPA.Eval(item.Score);
if (CourseGradeB.Tool.SubjectType.Honor == item.CourseType)
{
personalGPACount += item.CourseCredit * _gpa.Honors;
}
else if (CourseGradeB.Tool.SubjectType.Regular == item.CourseType)
{
personalGPACount += item.CourseCredit * _gpa.Regular;
}
personalAverageCount += item.CourseCredit * item.Score;
subjecti++;
}
if (personalCreditCount > 0)
{
mailmerge.Add("科目平均", personalAverageCount / personalCreditCount);
mailmerge.Add("GPA", personalGPACount / personalCreditCount);
}
GradePeriodicalExamGPARecord gpegpar = GradePeriodicalExamGPA.GetGradePeriodicalExamGPARecord(SchoolYear, Semester, ClassGradeYear, Exam);
List <GPADistributionPart> gpad = GradePeriodicalExamGPA.toGPADistribution(gpegpar);
int parti = 1;
foreach (GPADistributionPart gpadp in gpad)
{
mailmerge.Add(string.Format("GPA分段{0}", parti), gpadp.GPACeiling + "~" + gpadp.GPAFloor);
mailmerge.Add(string.Format("GPA計數{0}", parti), gpadp.Count);
parti++;
}
break;
}
mailmerge.Add(string.Format("科目{0}", subjecti), "(以下空白)");
mailmerge.Add(string.Format("成績{0}", subjecti), "");
for (; subjecti <= 20; subjecti++)
{
mailmerge.Add(string.Format("科目{0}", subjecti), "");
mailmerge.Add(string.Format("成績{0}", subjecti), "");
}
#endregion
Document each = (Document)template.Clone(true);
each.MailMerge.Execute(mailmerge.Keys.ToArray(), mailmerge.Values.ToArray());
document.Sections.Add(document.ImportNode(each.FirstSection, true));
}
document.Sections.RemoveAt(0);
e.Result = document;
}
