private List<DataBindedSheet> GetDataBindedSheets(StudentRecord Student, int SchoolYear, int Semester)
{
// 非停修生修課記錄：學年度 學期 課號 課程識別碼 課程名稱
string SQL = string.Format(@"Select subject.uid as subject_id, course.school_year, course.semester, subject.new_subject_code, subject.subject_code, subject.name as subject_name From $ischool.emba.scattend_ext as se join course on course.id=se.ref_course_id
join student on student.id=se.ref_student_id
join $ischool.emba.course_ext as ce on ce.ref_course_id=course.id
join $ischool.emba.subject as subject on subject.uid=ce.ref_subject_id where student.id={0} and course.school_year={1} and course.semester={2} order by ce.serial_no", Student.ID, SchoolYear, Semester);
DataTable dataTable = Query.Select(SQL);
Dictionary<string, dynamic> dicSCAttends = new Dictionary<string, dynamic>();
foreach(DataRow row in dataTable.Rows)
{
dynamic o = new ExpandoObject();
o.SchoolYear = SchoolYear;
o.Semester = Semester;
o.NewSubjectCode = row["new_subject_code"] + "";
o.SubjectCode = row["subject_code"] + "";
o.SubjectName = row["subject_name"] + "";
o.SubjectID = row["subject_id"] + "";
dicSCAttends.Add(SchoolYear + "-" + Semester + "-" + row["subject_id"] + "", o);
}
UDT.StudentBrief2 StudentBrief2 = new UDT.StudentBrief2();
if (this.dicStudentBrief2.ContainsKey(Student.ID))
StudentBrief2 = this.dicStudentBrief2[Student.ID];
UDT.DepartmentGroup DepartmentGroup = new UDT.DepartmentGroup();
if (this.dicStudentBrief2.ContainsKey(Student.ID))
{
if (this.dicDepartmentGroups.ContainsKey(StudentBrief2.DepartmentGroupID.ToString()))
DepartmentGroup = this.dicDepartmentGroups[StudentBrief2.DepartmentGroupID.ToString()];
}
Dictionary<int, UDT.GraduationSubjectList> dicGraduationSubjectLists = new Dictionary<int, UDT.GraduationSubjectList>();
if (this.dicStudentBrief2.ContainsKey(Student.ID))
{
if (this.dicGraduationSubjectLists.ContainsKey(StudentBrief2.GraduationRequirementID))
dicGraduationSubjectLists = this.dicGraduationSubjectLists[StudentBrief2.GraduationRequirementID];
}
Dictionary<string, UDT.GraduationSubjectGroupRequirement> dicGraduationSubjectGroups = new Dictionary<string, UDT.GraduationSubjectGroupRequirement>();
if (this.dicStudentBrief2.ContainsKey(Student.ID))
{
if (this.dicGraduationSubjectGroupRequirements.ContainsKey(StudentBrief2.GraduationRequirementID))
dicGraduationSubjectGroups = this.dicGraduationSubjectGroupRequirements[StudentBrief2.GraduationRequirementID];
}
//GraduationSubjectList
Workbook wb = new Workbook();
MemoryStream ms = new MemoryStream(Properties.Resources.EMBA_歷年成績表_樣版);
wb.Open(ms);
List<DataBindedSheet> DataBindedSheets = new List<DataBindedSheet>();
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["PageHeader"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(Student.StudentNumber.ToDataTable("學號", "學號"));
DataBindedSheet.DataTables.Add(Student.Name.ToDataTable("姓名", "姓名"));
DataBindedSheet.DataTables.Add(Student.Gender.ToDataTable("性別", "性別"));
if (!Student.Birthday.HasValue)
{
DataBindedSheet.DataTables.Add("".ToDataTable("出生日期", "出生日期"));
}
else
{
DateTime birthday;
if (DateTime.TryParse(Student.Birthday.Value + "", out birthday))
DataBindedSheet.DataTables.Add(birthday.ToString("yyyy/MM/dd").ToDataTable("出生日期", "出生日期"));
else
DataBindedSheet.DataTables.Add("".ToDataTable("出生日期", "出生日期"));
}
DataBindedSheet.DataTables.Add(StudentBrief2.EnrollYear.ToDataTable("入學年度", "入學年度"));
DataBindedSheet.DataTables.Add(DepartmentGroup.Name.ToDataTable("系所組別", "系所組別"));
DataBindedSheets.Add(DataBindedSheet);
Dictionary<string, KeyValuePair> dicSubjectGroupGredits = new Dictionary<string, KeyValuePair>();
if (this.dicSubjectSemesterScores.ContainsKey(Student.ID))
{
List<UDT.SubjectSemesterScore> SubjectSemesterScores = this.dicSubjectSemesterScores[Student.ID];
SubjectSemesterScores.ForEach((x) =>
{
if (string.IsNullOrEmpty(x.NewSubjectCode))
{
if (this.dicSubjects.ContainsKey(x.SubjectID.ToString()))
x.NewSubjectCode = this.dicSubjects[x.SubjectID.ToString()].NewSubjectCode;
}
});
SubjectSemesterScores = SubjectSemesterScores.OrderBy(x => x.SchoolYear.HasValue ? x.SchoolYear.Value : 0).ThenBy(x => x.Semester.HasValue ? x.Semester.Value : 0).ThenBy(x => x.NewSubjectCode).ToList();
int credit_total = 0;
Dictionary<int, List<UDT.SubjectSemesterScore>> dicDuplicateSubjectSemesterScores = new Dictionary<int, List<UDT.SubjectSemesterScore>>();
List<string> present_subject_ids = new List<string>();
foreach (UDT.SubjectSemesterScore SubjectSemesterScore in SubjectSemesterScores)
{
// 是否重覆修課
if (!dicDuplicateSubjectSemesterScores.ContainsKey(SubjectSemesterScore.SubjectID))
dicDuplicateSubjectSemesterScores.Add(SubjectSemesterScore.SubjectID, new List<UDT.SubjectSemesterScore>());
dicDuplicateSubjectSemesterScores[SubjectSemesterScore.SubjectID].Add(SubjectSemesterScore);
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["DataSection"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add((SubjectSemesterScore.SchoolYear.HasValue ? SubjectSemesterScore.SchoolYear.Value + "" : "").ToDataTable("學年度", "學年度"));
DataBindedSheet.DataTables.Add((SubjectSemesterScore.Semester.HasValue ? SubjectSemesterScore.Semester.Value + "" : "").ToDataTable("學期", "學期"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.NewSubjectCode.ToDataTable("課號", "課號"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.SubjectCode.ToDataTable("課程識別碼", "課程識別碼"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.SubjectName.ToDataTable("課程名稱", "課程名稱"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.Score.ToDataTable("成績", "成績"));
if (dicGraduationSubjectLists.ContainsKey(SubjectSemesterScore.SubjectID))
{
string SubjectGroup = dicGraduationSubjectLists[SubjectSemesterScore.SubjectID].SubjectGroup;
DataBindedSheet.DataTables.Add(SubjectGroup.ToDataTable("群組別", "群組別"));
if (!dicSubjectGroupGredits.ContainsKey(SubjectGroup))
dicSubjectGroupGredits.Add(SubjectGroup, new KeyValuePair());
if (dicGraduationSubjectGroups.ContainsKey(SubjectGroup))
dicSubjectGroupGredits[SubjectGroup].Key = dicGraduationSubjectGroups[SubjectGroup].LowestCredit;
if (SubjectSemesterScore.IsPass)
{
if (dicSubjectGroupGredits[SubjectGroup].Value == null)
dicSubjectGroupGredits[SubjectGroup].Value = SubjectSemesterScore.Credit;
else
dicSubjectGroupGredits[SubjectGroup].Value += SubjectSemesterScore.Credit;
}
if (this.dicSubjects.ContainsKey(SubjectSemesterScore.SubjectID.ToString()))
DataBindedSheet.DataTables.Add(this.dicSubjects[SubjectSemesterScore.SubjectID.ToString()].Credit.ToDataTable("學分數", "學分數"));
else
DataBindedSheet.DataTables.Add(SubjectSemesterScore.Credit.ToDataTable("學分數", "學分數"));
}
else
{
DataBindedSheet.DataTables.Add("".ToDataTable("群組別", "群組別"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.Credit.ToDataTable("學分數", "學分數"));
}
if (!string.IsNullOrEmpty(SubjectSemesterScore.OffsetCourse) || SubjectSemesterScore.IsPass)
{
DataBindedSheet.DataTables.Add("已取得學分".ToDataTable("備註", "備註"));
}
else
DataBindedSheet.DataTables.Add("未取得學分".ToDataTable("備註", "備註"));
if (SubjectSemesterScore.IsPass && string.IsNullOrEmpty(SubjectSemesterScore.OffsetCourse))
credit_total += SubjectSemesterScore.Credit;
DataBindedSheets.Add(DataBindedSheet);
present_subject_ids.Add(string.Format("{0}-{1}-{2}", (SubjectSemesterScore.SchoolYear.HasValue ? SubjectSemesterScore.SchoolYear.Value + "" : ""), (SubjectSemesterScore.Semester.HasValue ? SubjectSemesterScore.Semester.Value + "" : ""), SubjectSemesterScore.SubjectID));
}
foreach(string key in dicSCAttends.Keys)
{
if (present_subject_ids.Contains(key))
continue;
dynamic o = dicSCAttends[key];
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["DataSection"];
DataBindedSheet.DataTables = new List<DataTable>();
int intSchoolYear = 0;
int intSemester = 0;
int.TryParse(o.SchoolYear + "", out intSchoolYear);
int.TryParse(o.Semester + "", out intSemester);
string NewSubjectCode = o.NewSubjectCode + "";
string SubjectCode = o.SubjectCode + "";
string SubjectName = o.SubjectName + "";
DataBindedSheet.DataTables.Add(intSchoolYear.ToDataTable("學年度", "學年度"));
DataBindedSheet.DataTables.Add(intSemester.ToDataTable("學期", "學期"));
DataBindedSheet.DataTables.Add(NewSubjectCode.ToDataTable("課號", "課號"));
DataBindedSheet.DataTables.Add(SubjectCode.ToDataTable("課程識別碼", "課程識別碼"));
DataBindedSheet.DataTables.Add(SubjectName.ToDataTable("課程名稱", "課程名稱"));
DataBindedSheet.DataTables.Add("".ToDataTable("成績", "成績"));
DataBindedSheet.DataTables.Add("".ToDataTable("學分數", "學分數"));
int SubjectID = 0;
int.TryParse(o.SubjectID + "", out SubjectID);
if (dicGraduationSubjectLists.ContainsKey(SubjectID))
{
string SubjectGroup = dicGraduationSubjectLists[SubjectID].SubjectGroup;
DataBindedSheet.DataTables.Add(SubjectGroup.ToDataTable("群組別", "群組別"));
if (!dicSubjectGroupGredits.ContainsKey(SubjectGroup))
dicSubjectGroupGredits.Add(SubjectGroup, new KeyValuePair());
if (dicGraduationSubjectGroups.ContainsKey(SubjectGroup))
dicSubjectGroupGredits[SubjectGroup].Key = dicGraduationSubjectGroups[SubjectGroup].LowestCredit;
}
else
{
DataBindedSheet.DataTables.Add("".ToDataTable("群組別", "群組別"));
}
DataBindedSheet.DataTables.Add("本學期修課".ToDataTable("備註", "備註"));
DataBindedSheets.Add(DataBindedSheet);
}
int offset_credit = SubjectSemesterScores.Where(x => !string.IsNullOrWhiteSpace(x.OffsetCourse)).Sum(x => x.Credit);
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["PageFooter-Header"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(credit_total.ToDataTable("修習及格學分", "修習及格學分"));
DataBindedSheet.DataTables.Add(offset_credit.ToDataTable("抵免學分", "抵免學分"));
DataBindedSheet.DataTables.Add((credit_total + offset_credit).ToDataTable("實得總學分", "實得總學分"));
DataBindedSheets.Add(DataBindedSheet);
List<UDT.GraduationSubjectGroupRequirement> GraduationSubjectGroupRequirements = dicGraduationSubjectGroups.Values.ToList();
GraduationSubjectGroupRequirements.Sort(
delegate(UDT.GraduationSubjectGroupRequirement x, UDT.GraduationSubjectGroupRequirement y)
{
int index_x = SubjectGroups_Sort.IndexOf(x.SubjectGroup);
int index_y = SubjectGroups_Sort.IndexOf(y.SubjectGroup);
if (index_x < 0)
index_x = int.MaxValue;
if (index_y < 0)
index_y = int.MaxValue;
return index_x.CompareTo(index_y);
});
foreach (UDT.GraduationSubjectGroupRequirement GraduationSubjectGroupRequirement in GraduationSubjectGroupRequirements)
{
string SubjectGroup = GraduationSubjectGroupRequirement.SubjectGroup;
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["PageFooter-DataSection"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(SubjectGroup.ToDataTable("群組別", "群組別"));
int gCredit = 0;
if (dicSubjectGroupGredits.ContainsKey(SubjectGroup))
if (dicSubjectGroupGredits[SubjectGroup].Value.HasValue)
gCredit = dicSubjectGroupGredits[SubjectGroup].Value.Value;
DataBindedSheet.DataTables.Add((gCredit + "").ToDataTable("已取得學分數", "已取得學分數"));
int sCredit = GraduationSubjectGroupRequirement.LowestCredit;
if (dicSubjectGroupGredits.ContainsKey(SubjectGroup))
{
if (sCredit > gCredit)
DataBindedSheet.DataTables.Add((sCredit - gCredit).ToDataTable("不足學分數", "不足學分數"));
else
DataBindedSheet.DataTables.Add("0".ToDataTable("不足學分數", "不足學分數"));
}
else
{
DataBindedSheet.DataTables.Add(sCredit.ToDataTable("不足學分數", "不足學分數"));
}
DataBindedSheet.DataTables.Add(sCredit.ToDataTable("應修學分數", "應修學分數"));
DataBindedSheets.Add(DataBindedSheet);
}
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["PageFooter-Footer"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheets.Add(DataBindedSheet);
// 重覆修課
List<UDT.SubjectSemesterScore> DuplicateSubjectSemesterScores = new List<UDT.SubjectSemesterScore>();
foreach (int SubjectID in dicDuplicateSubjectSemesterScores.Keys)
{
if (dicDuplicateSubjectSemesterScores[SubjectID].Count > 1)
DuplicateSubjectSemesterScores.AddRange(dicDuplicateSubjectSemesterScores[SubjectID]);
}
if (DuplicateSubjectSemesterScores.Count > 1)
{
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["Duplicate-Header"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheets.Add(DataBindedSheet);
foreach (UDT.SubjectSemesterScore SubjectSemesterScore in DuplicateSubjectSemesterScores)
{
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["Duplicate-DataSection"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add((SubjectSemesterScore.SchoolYear.HasValue ? SubjectSemesterScore.SchoolYear.Value + "" : "").ToDataTable("學年度", "學年度"));
DataBindedSheet.DataTables.Add((SubjectSemesterScore.Semester.HasValue ? SubjectSemesterScore.Semester.Value + "" : "").ToDataTable("學期", "學期"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.NewSubjectCode.ToDataTable("課號", "課號"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.SubjectCode.ToDataTable("課程識別碼", "課程識別碼"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.SubjectName.ToDataTable("課程名稱", "課程名稱"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.Score.ToDataTable("成績", "成績"));
//DataBindedSheet.DataTables.Add(SubjectSemesterScore.Credit.ToDataTable("學分數", "學分數"));
if (dicGraduationSubjectLists.ContainsKey(SubjectSemesterScore.SubjectID))
{
string SubjectGroup = dicGraduationSubjectLists[SubjectSemesterScore.SubjectID].SubjectGroup;
DataBindedSheet.DataTables.Add(SubjectGroup.ToDataTable("群組別", "群組別"));
if (!dicSubjectGroupGredits.ContainsKey(SubjectGroup))
dicSubjectGroupGredits.Add(SubjectGroup, new KeyValuePair());
if (dicGraduationSubjectGroups.ContainsKey(SubjectGroup))
dicSubjectGroupGredits[SubjectGroup].Key = dicGraduationSubjectGroups[SubjectGroup].LowestCredit;
if (SubjectSemesterScore.IsPass)
{
if (dicSubjectGroupGredits[SubjectGroup].Value == null)
dicSubjectGroupGredits[SubjectGroup].Value = SubjectSemesterScore.Credit;
else
dicSubjectGroupGredits[SubjectGroup].Value += SubjectSemesterScore.Credit;
}
if (this.dicSubjects.ContainsKey(SubjectSemesterScore.SubjectID.ToString()))
DataBindedSheet.DataTables.Add(this.dicSubjects[SubjectSemesterScore.SubjectID.ToString()].Credit.ToDataTable("學分數", "學分數"));
else
DataBindedSheet.DataTables.Add(SubjectSemesterScore.Credit.ToDataTable("學分數", "學分數"));
}
else
{
DataBindedSheet.DataTables.Add("".ToDataTable("群組別", "群組別"));
DataBindedSheet.DataTables.Add(SubjectSemesterScore.Credit.ToDataTable("學分數", "學分數"));
}
if (!string.IsNullOrEmpty(SubjectSemesterScore.OffsetCourse) || SubjectSemesterScore.IsPass)
{
DataBindedSheet.DataTables.Add("已取得學分".ToDataTable("備註", "備註"));
}
else
DataBindedSheet.DataTables.Add("未取得學分".ToDataTable("備註", "備註"));
if (SubjectSemesterScore.IsPass && string.IsNullOrEmpty(SubjectSemesterScore.OffsetCourse))
credit_total += SubjectSemesterScore.Credit;
DataBindedSheets.Add(DataBindedSheet);
}
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["Duplicate-Footer"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheets.Add(DataBindedSheet);
}
}
return DataBindedSheets;
}
// 基本資料
private List<DataBindedSheet> Get基本資料(List<XElement> xStatistics, Workbook wb, int DisplayOrder)
{
List<DataBindedSheet> DataBindedSheets = new List<DataBindedSheet>();
// 基本資料-標題
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["基本資料-標題"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add((ToChineseNo(DisplayOrder) + "、").ToDataTable("項次", "項次"));
DataBindedSheets.Add(DataBindedSheet);
// 基本資料-資料
foreach (XElement xElement in xStatistics)
{
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["基本資料-資料"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add((xElement.Attribute("No").Value + ".").ToDataTable("項次", "項次"));
IEnumerable<XElement> Options = xElement.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
string question_content = string.Empty;
foreach (XElement xOption in Options)
{
// 選項做答人數
DataBindedSheet.DataTables.Add(xOption.Attribute("AnswerCount").Value.ToDataTable("做答人數-" + xOption.Attribute("No").Value, "做答人數-" + xOption.Attribute("No").Value));
question_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value);
}
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value + question_content).ToDataTable("題目", "題目"));
}
// 基本資料-空白列
foreach(Worksheet workSheet in wb.Worksheets)
{
if (workSheet.Name == "基本資料-空白列")
{
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = workSheet;
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheets.Add(DataBindedSheet);
}
}
return DataBindedSheets;
}
// 報表標題
private List<DataBindedSheet> Get報表標題(XElement xStatistics, Workbook wb)
{
string CSAttendCount = xStatistics.Attribute("CSAttendCount").Value; // 修課人數
string FeedBackCount = xStatistics.Attribute("FeedBackCount").Value; // 填答人數
string TeacherName = HttpUtility.HtmlDecode(xStatistics.Attribute("TeacherName").Value); // 授課教師
string CourseName = HttpUtility.HtmlDecode(xStatistics.Attribute("CourseName").Value); // 開課
string SubjectName = HttpUtility.HtmlDecode(xStatistics.Attribute("SubjectName").Value); // 課程
string ClassName = xStatistics.Attribute("ClassName").Value; // 班次
string SubjectCode = xStatistics.Attribute("SubjectCode").Value; // 課程識別碼
string NewSubjectCode = xStatistics.Attribute("NewSubjectCode").Value; // 課號
string SchoolYear = xStatistics.Attribute("SchoolYear").Value; // 學年度
string Semester = xStatistics.Attribute("Semester").Value; // 學期
string CourseID = xStatistics.Attribute("CourseID").Value; // 開課系統編號
string TeacherID = xStatistics.Attribute("TeacherID").Value; // 授課教師系統編號
string SurveyDate = xStatistics.Attribute("SurveyDate").Value; // 問卷調查日期
string SurveyID = xStatistics.Attribute("SurveyID").Value; // 問卷系統編號
List<DataBindedSheet> DataBindedSheets = new List<DataBindedSheet>();
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["報表標題"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(SchoolYear.ToDataTable("學年度", "學年度"));
DataBindedSheet.DataTables.Add(Semester.ToDataTable("學期", "學期"));
DataBindedSheet.DataTables.Add(SubjectName.ToDataTable("科目名稱", "科目名稱"));
DataBindedSheet.DataTables.Add(NewSubjectCode.ToDataTable("課號", "課號"));
DataBindedSheet.DataTables.Add(ClassName.ToDataTable("班次", "班次"));
DataBindedSheet.DataTables.Add(TeacherName.ToDataTable("任課教師", "任課教師"));
DataBindedSheet.DataTables.Add(CSAttendCount.ToDataTable("修課人數", "修課人數"));
DataBindedSheet.DataTables.Add(FeedBackCount.ToDataTable("填答人數", "填答人數"));
DataBindedSheet.DataTables.Add(SurveyDate.ToDataTable("問卷調查日期", "問卷調查日期"));
DataBindedSheets.Add(DataBindedSheet);
return DataBindedSheets;
}
// 報表標題
private DataBindedSheet GetReportHeader(XElement xStatistics, Workbook wb)
{
string CSAttendCount = xStatistics.Attribute("CSAttendCount").Value; // 修課人數
string FeedBackCount = xStatistics.Attribute("FeedBackCount").Value; // 填答人數
string TeacherName = HttpUtility.HtmlDecode(xStatistics.Attribute("TeacherName").Value); // 授課教師
string CourseName = HttpUtility.HtmlDecode(xStatistics.Attribute("CourseName").Value); // 開課
string SubjectName = HttpUtility.HtmlDecode(xStatistics.Attribute("SubjectName").Value); // 課程
string ClassName = xStatistics.Attribute("ClassName").Value; // 班次
string SubjectCode = xStatistics.Attribute("SubjectCode").Value; // 課程識別碼
string NewSubjectCode = xStatistics.Attribute("NewSubjectCode").Value; // 課號
string SchoolYear = xStatistics.Attribute("SchoolYear").Value; // 學年度
string Semester = xStatistics.Attribute("Semester").Value; // 學期
string CourseID = xStatistics.Attribute("CourseID").Value; // 開課系統編號
string TeacherID = xStatistics.Attribute("TeacherID").Value; // 授課教師系統編號
string SurveyDate = xStatistics.Attribute("SurveyDate").Value; // 問卷調查日期
string SurveyID = xStatistics.Attribute("SurveyID").Value; // 問卷系統編號
string SubjectID = xStatistics.Attribute("SubjectID").Value;
this.SubjectID = SubjectID;
this.CourseID = CourseID;
this.TeacherID = TeacherID;
this.SurveyID = SurveyID;
this.SubjectCode = SubjectCode;
this.NewSubjectCode = NewSubjectCode;
this.SubjectName = SubjectName;
this.CourseName = CourseName;
this.TeacherName = TeacherName;
this.SchoolYear = SchoolYear;
this.Semester = Semester;
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["報表標題"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(SchoolYear.ToDataTable("學年度", "學年度"));
DataBindedSheet.DataTables.Add(DataItems.SemesterItem.GetSemesterByCode(xStatistics.Attribute("Semester").Value).Name.ToDataTable("學期", "學期"));
DataBindedSheet.DataTables.Add(SubjectName.ToDataTable("科目名稱", "科目名稱"));
DataBindedSheet.DataTables.Add(NewSubjectCode.ToDataTable("課號", "課號"));
DataBindedSheet.DataTables.Add(ClassName.ToDataTable("班次", "班次"));
DataBindedSheet.DataTables.Add(TeacherName.ToDataTable("任課教師", "任課教師"));
DataBindedSheet.DataTables.Add(CSAttendCount.ToDataTable("修課人數", "修課人數"));
DataBindedSheet.DataTables.Add(FeedBackCount.ToDataTable("填答人數", "填答人數"));
DataBindedSheet.DataTables.Add(SurveyDate.ToDataTable("問卷調查日期", "問卷調查日期"));
this.WorksheetName = string.Format("{0}-{1}-{2}-{3}", this.SchoolYear, this.Semester, this.CourseName, this.TeacherName);
return DataBindedSheet;
}
private List<DataBindedSheet> GetDataBindedSheets(StudentRecord Student, UDT.TeacherStatistics Statistic, Dictionary<string, UDT.Hierarchy> dicQuestionHierarchies, Dictionary<string, Dictionary<string, Color>> dicQuestionBackgroundColor, Dictionary<string, Dictionary<string, Color>> dicEvaluationBackgroundColor)
{
Dictionary<UDT.Hierarchy, List<XElement>> dicHierarchyQuestions = new Dictionary<UDT.Hierarchy, List<XElement>>();
XDocument xDocument = XDocument.Parse(Statistic.StatisticsList, LoadOptions.None);
XElement xStatistics = xDocument.Element("Statistics");
#region 表頭
string CSAttendCount = xStatistics.Attribute("CSAttendCount").Value; // 修課人數
string FeedBackCount = xStatistics.Attribute("FeedBackCount").Value; // 填答人數
string TeacherName = HttpUtility.HtmlDecode(xStatistics.Attribute("TeacherName").Value); // 授課教師
string CourseName = HttpUtility.HtmlDecode(xStatistics.Attribute("CourseName").Value); // 開課
string SubjectName = HttpUtility.HtmlDecode(xStatistics.Attribute("SubjectName").Value); // 課程
string ClassName = xStatistics.Attribute("ClassName").Value; // 班次
string SubjectCode = xStatistics.Attribute("SubjectCode").Value; // 課程識別碼
string NewSubjectCode = xStatistics.Attribute("NewSubjectCode").Value; // 課號
string SchoolYear = xStatistics.Attribute("SchoolYear").Value; // 學年度
string Semester = xStatistics.Attribute("Semester").Value; // 學期
string CourseID = xStatistics.Attribute("CourseID").Value; // 開課系統編號
string TeacherID = xStatistics.Attribute("TeacherID").Value; // 授課教師系統編號
string SurveyDate = xStatistics.Attribute("SurveyDate").Value; // 問卷調查日期
string SurveyID = xStatistics.Attribute("SurveyID").Value; // 問卷系統編號
// 開啟意見調查表樣版檔
List<UDT.ReportTemplate> templates = Access.Select<UDT.ReportTemplate>(string.Format("ref_survey_id = {0}", SurveyID));
byte[] _buffer = Convert.FromBase64String(templates.ElementAt(0).Template);
MemoryStream ms = new MemoryStream(_buffer);
Workbook wb = new Workbook();
wb.Open(ms);
List<string> workSheetNames = new List<string>();
wb.Worksheets.Cast<Worksheet>().ToList().ForEach(x=>workSheetNames.Add(x.Name));
List<DataBindedSheet> DataBindedSheets = new List<DataBindedSheet>();
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["報表標題"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(SchoolYear.ToDataTable("學年度", "學年度"));
DataBindedSheet.DataTables.Add(Semester.ToDataTable("學期", "學期"));
DataBindedSheet.DataTables.Add(SubjectName.ToDataTable("科目名稱", "科目名稱"));
DataBindedSheet.DataTables.Add(NewSubjectCode.ToDataTable("課號", "課號"));
DataBindedSheet.DataTables.Add(ClassName.ToDataTable("班次", "班次"));
DataBindedSheet.DataTables.Add(TeacherName.ToDataTable("任課教師", "任課教師"));
DataBindedSheet.DataTables.Add(CSAttendCount.ToDataTable("修課人數", "修課人數"));
DataBindedSheet.DataTables.Add(FeedBackCount.ToDataTable("填答人數", "填答人數"));
DataBindedSheet.DataTables.Add(SurveyDate.ToDataTable("問卷調查日期", "問卷調查日期"));
DataBindedSheets.Add(DataBindedSheet);
#endregion
#region 群組化題目並排序
// 將所有題目加入「標題」群組
foreach (XElement xElement in xDocument.Descendants("Question"))
{
string question_id = xElement.Attribute("ID").Value;
if (dicQuestionHierarchies.ContainsKey(question_id))
{
UDT.Hierarchy Hierarchy = dicQuestionHierarchies[question_id];
if (!dicHierarchyQuestions.ContainsKey(Hierarchy))
dicHierarchyQuestions.Add(Hierarchy, new List<XElement>());
dicHierarchyQuestions[Hierarchy].Add(xElement);
}
}
// 依照標題之顯示順序及題號排序
Dictionary<UDT.Hierarchy, List<XElement>> dicOrderedHierarchyQuestions = new Dictionary<UDT.Hierarchy, List<XElement>>();
foreach (KeyValuePair<UDT.Hierarchy, List<XElement>> kv in dicHierarchyQuestions.OrderBy(x => x.Key.DisplayOrder))
{
if (!dicOrderedHierarchyQuestions.ContainsKey(kv.Key))
dicOrderedHierarchyQuestions.Add(kv.Key, new List<XElement>());
dicOrderedHierarchyQuestions[kv.Key].AddRange(kv.Value.OrderBy(x => int.Parse(x.Attribute("No").Value)));
}
#endregion
string report_name = SchoolYear + "-" + Semester + "-" + CourseName + "-" + TeacherName;
Dictionary<ReportHelper.CellObject, ReportHelper.CellStyle> dicCellStyles = new Dictionary<CellObject, CellStyle>();
#region 報表資料的部份
// 先產生資料容器
DataTable EnrollRecordTable = new DataTable("NoneSelfAssessmentContent");
this.MakeTableColumn(EnrollRecordTable);
// 接著將資料倒進容器中
int i = 0;
foreach (UDT.Hierarchy Hierarchy in dicOrderedHierarchyQuestions.Keys)
{
//// 群組首
//this.MakeGroupData(EnrollRecordTable, Hierarchy, report_name, dicCellStyles);
//// 題目及答案
//this.MakeDetailOneData(EnrollRecordTable, Hierarchy, dicOrderedHierarchyQuestions[Hierarchy], report_name, dicCellStyles);
//this.MakeDetailTwoData(EnrollRecordTable, Hierarchy, dicOrderedHierarchyQuestions[Hierarchy], xDocument.Descendants("StatisticsGroup"), report_name, dicCellStyles, dicQuestionBackgroundColor, dicEvaluationBackgroundColor, SurveyID);
//this.MakeDetailThreeData(EnrollRecordTable, Hierarchy, dicOrderedHierarchyQuestions[Hierarchy], report_name, dicCellStyles);
//this.MakeDetailFourData(EnrollRecordTable, Hierarchy, dicOrderedHierarchyQuestions[Hierarchy], report_name, dicCellStyles);
if (!workSheetNames.Contains(Hierarchy.Title))
continue;
// 群組標題
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-標題"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(i.ToDataTable("項次", "項次"));
DataBindedSheets.Add(DataBindedSheet);
// 群組資料
foreach (XElement xElement in dicOrderedHierarchyQuestions[Hierarchy])
{
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-資料"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add((xElement.Attribute("No").Value + ".").ToDataTable("項次", "項次"));
// 非基本資料的題目
if (Hierarchy.Title != "基本資料")
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("題目", "題目"));
IEnumerable<XElement> Options = xElement.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
string question_content = string.Empty;
foreach (XElement xOption in Options)
{
// 選項做答人數
DataBindedSheet.DataTables.Add(xOption.Attribute("AnswerCount").Value.ToDataTable("做答人數-" + xOption.Attribute("No").Value, "做答人數-" + xOption.Attribute("No").Value));
question_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value);
}
// 基本資料的題目
if (Hierarchy.Title == "基本資料")
{
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value + question_content).ToDataTable("題目", "題目"));
}
// 評鑑值
DataBindedSheet.DataTables.Add(xElement.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
}
// 群組空白列
if (workSheetNames.Contains(Hierarchy.Title + "-空白列"))
{
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-空白列"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheets.Add(DataBindedSheet);
}
// 平均評鑑值
foreach (XElement xElement in xDocument.Descendants("StatisticsGroup"))
{
DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets["平均評鑑值"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("群組名稱", "群組名稱"));
DataBindedSheet.DataTables.Add(xElement.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
DataBindedSheets.Add(DataBindedSheet);
// 評鑑值背景色
if (dicEvaluationBackgroundColor.ContainsKey(SurveyID))
{
if (dicEvaluationBackgroundColor[SurveyID].ContainsKey(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)))
{
//CellObject co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", "平均評鑑值");
//cs.SetFontBackGroundColor(dicEvaluationBackgroundColor[SurveyID][HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)]);
//dicCellStyles.Add(co, cs);
}
}
}
// 個案題
}
#endregion
return DataBindedSheets;
}
// 群組標題
private DataBindedSheet GetGroupHeader(int DisplayOrder, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-標題"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add((ToChineseNo(DisplayOrder)).ToDataTable("項次", "項次"));
return DataBindedSheet;
}
// 選擇題-題目
private DataBindedSheet GetQuestion(XElement xElement, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-選擇題-題目"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add((xElement.Attribute("No").Value + ".").ToDataTable("項次", "項次"));
// 題目
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("題目", "題目"));
// 題目高度
CellObject co = new CellObject(0, 0, "題目", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
// 題目背景色
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
goto Outline;
}
}
this.dicCellStyles.Add(co, cs);
Outline:
IEnumerable<XElement> Options = xElement.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
string question_content = string.Empty;
int i = 0;
foreach (XElement xOption in Options)
{
i = i + 1;
// 選項做答人數
DataBindedSheet.DataTables.Add(xOption.Attribute("AnswerCount").Value.ToDataTable("做答人數-" + xOption.Attribute("No").Value, "做答人數-" + xOption.Attribute("No").Value));
question_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value);
// 做答背景色
co = new CellObject(0, 0, "做答人數-" + xOption.Attribute("No").Value, DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
}
}
}
for (int j = i; j < 6; j++)
{
// 補選項6背景色
DataBindedSheet.DataTables.Add("".ToDataTable("做答人數-" + (j + 1), "做答人數-" + (j + 1)));
co = new CellObject(0, 0, "做答人數-" + (j+1), DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
}
}
}
// 題目+選項
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value + question_content).ToDataTable("題目+選項", "題目+選項"));
// 評鑑值
DataBindedSheet.DataTables.Add(xElement.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
// 評鑑值背景色
co = new CellObject(0, 0, "評鑑值", DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
}
}
return DataBindedSheet;
}
// 問答題-題目
private DataBindedSheet GetEssayQuestion(XElement xElement, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-問答題-題目"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add((xElement.Attribute("No").Value + ".").ToDataTable("項次", "項次"));
// 題目
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("題目", "題目"));
// 題目高度
CellObject co = new CellObject(0, 0, "題目", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
return DataBindedSheet;
}
// 問答題-做答
private DataBindedSheet GetEssayAnswer(string DisplayOrder, XElement xEssay, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-問答題-做答"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add(DisplayOrder.ToDataTable("項次", "項次"));
// 做答
DataBindedSheet.DataTables.Add(xEssay.Value.ToDataTable("內容", "內容"));
// 題目高度
CellObject co = new CellObject(0, 0, "內容", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
return DataBindedSheet;
}
// 個案題-做答
private DataBindedSheet GetCaseAnswer(string DisplayOrder, XElement xCase, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-個案題-做答"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add(DisplayOrder.ToDataTable("項次", "項次"));
// 題目
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xCase.Attribute("Content").Value).ToDataTable("題目", "題目"));
// 題目高度
CellObject co = new CellObject(0, 0, "題目", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
// 評鑑值
DataBindedSheet.DataTables.Add(xCase.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
IEnumerable<XElement> Options = xCase.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
string question_content = string.Empty;
foreach (XElement xOption in Options)
{
// 選項做答人數
DataBindedSheet.DataTables.Add(xOption.Attribute("AnswerCount").Value.ToDataTable("做答人數-" + xOption.Attribute("No").Value, "做答人數-" + xOption.Attribute("No").Value));
question_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value);
}
// 題目+選項
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xCase.Attribute("Content").Value + question_content).ToDataTable("題目+選項", "題目+選項"));
// 題目高度
co = new CellObject(0, 0, "題目+選項", DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
return DataBindedSheet;
}
// 平均評鑑值
private DataBindedSheet GetAvgScore(XElement xElement, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-平均評鑑值"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("群組名稱", "群組名稱"));
DataBindedSheet.DataTables.Add(xElement.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
// 評鑑值背景色
if (this._DicEvaluationBackgroundColor.ContainsKey(this.SurveyID))
{
if (this._DicEvaluationBackgroundColor[this.SurveyID].ContainsKey(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)))
{
CellObject co = new CellObject(0, 0, "評鑑值", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetFontBackGroundColor(this._DicEvaluationBackgroundColor[this.SurveyID][HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)]);
dicCellStyles.Add(co, cs);
}
}
return DataBindedSheet;
}
/// <summary>
/// CourseID-TeacherID, Workbook
/// </summary>
/// <returns>Dictionary<string, Workbook></returns>
public Workbook Produce()
{
List<string> Worksheets = new List<string>();
this._Template.Worksheets.Cast<Worksheet>().ToList().ForEach(x => Worksheets.Add(x.Name));
XDocument xDocument = XDocument.Parse(_Statistics.StatisticsList, LoadOptions.None);
XElement xStatistics = xDocument.Element("Statistics");
string SurveyID = xStatistics.Attribute("SurveyID").Value;
Workbook wb = this._Template;
if (!Worksheets.Contains("報表標題"))
throw new Exception("教學意見調查表樣版必須有「報表標題」工作表。");
// 報表標題：樣版一定要有「報表標題」工作表，且以其為排版依據
this._DataBindedSheets.Add(this.GetReportHeader(xStatistics, wb));
// 依照題目群組順序產生題目
Dictionary<UDT.Hierarchy, List<XElement>> dicOrderedHierarchyQuestions = this.GetHierarchyQuestions(_Statistics);
int i = 1;
foreach (UDT.Hierarchy Hierarchy in dicOrderedHierarchyQuestions.Keys)
{
// 樣版中沒有此題目群組就跳過
if (!Worksheets.Contains(Hierarchy.Title + "-標題"))
continue;
// 1、題目群組標題
this._DataBindedSheets.Add(this.GetGroupHeader(i++, Hierarchy, wb));
// 2、題目
List<XElement> CaseElements = new List<XElement>();
foreach (XElement xElement in dicOrderedHierarchyQuestions[Hierarchy])
{
if (xElement.Attribute("Type").Value != "問答題" && xElement.Attribute("IsCase").Value != "是")
{
if (Worksheets.Contains(Hierarchy.Title + "-選擇題-題目"))
this._DataBindedSheets.Add(this.GetQuestion(xElement, Hierarchy, wb));
}
if (xElement.Attribute("IsCase").Value == "是")
{
CaseElements.Add(xElement);
}
if (xElement.Attribute("Type").Value == "問答題")
{
// 樣版中有問答題
if (Worksheets.Contains(Hierarchy.Title + "-問答題-題目"))
{
this._DataBindedSheets.Add(this.GetEssayQuestion(xElement, Hierarchy, wb));
if (Worksheets.Contains(Hierarchy.Title + "-問答題-做答"))
{
IEnumerable<XElement> xAnswers = xElement.Descendants("Answer");
int z = 1;
foreach (XElement xAnswer in xAnswers)
{
if (string.IsNullOrWhiteSpace(xAnswer.Value))
continue;
string DisplayOrder = xElement.Attribute("No").Value + "-" + z++;
this._DataBindedSheets.Add(this.GetEssayAnswer(DisplayOrder, xAnswer, Hierarchy, wb));
}
}
if (Worksheets.Contains(Hierarchy.Title + "-問答題-空白列"))
{
DataBindedSheet dataBindedSheet_問答題_空白列 = new DataBindedSheet();
dataBindedSheet_問答題_空白列.Worksheet = wb.Worksheets[Hierarchy.Title + "-問答題-空白列"];
dataBindedSheet_問答題_空白列.DataTables = new List<DataTable>();
this._DataBindedSheets.Add(dataBindedSheet_問答題_空白列);
}
}
}
}
// 「平均平鑑值」與「個案」只能放在題目最下方顯示
// 平均平鑑值
if (Worksheets.Contains(Hierarchy.Title + "-平均評鑑值") && xDocument.Descendants("StatisticsGroup").Count() > 0)
{
foreach (XElement xElement in xDocument.Descendants("StatisticsGroup"))
this._DataBindedSheets.Add(this.GetAvgScore(xElement, Hierarchy, wb));
if (Worksheets.Contains(Hierarchy.Title + "-平均評鑑值-空白列"))
{
DataBindedSheet dataBindedSheet_平均評鑑值_空白列 = new DataBindedSheet();
dataBindedSheet_平均評鑑值_空白列.Worksheet = wb.Worksheets[Hierarchy.Title + "-平均評鑑值-空白列"];
dataBindedSheet_平均評鑑值_空白列.DataTables = new List<DataTable>();
this._DataBindedSheets.Add(dataBindedSheet_平均評鑑值_空白列);
}
}
// 個案
if (CaseElements.Count > 0)
{
if (Worksheets.Contains(Hierarchy.Title + "-個案題-題目"))
{
foreach (XElement xElement in CaseElements)
{
string DisplayOrder = xElement.Attribute("No").Value;
this._DataBindedSheets.Add(this.GetCaseQuestion(DisplayOrder, xElement, Hierarchy, wb));
if (Worksheets.Contains(Hierarchy.Title + "-個案題-做答"))
{
// 個案做答
IEnumerable<XElement> xCases = xElement.Descendants("Case");
int z = 1;
foreach (XElement xCase in xCases)
{
DisplayOrder = xElement.Attribute("No").Value + "-" + z++;
this._DataBindedSheets.Add(this.GetCaseAnswer(DisplayOrder, xCase, Hierarchy, wb));
}
}
}
}
}
// 3、群組空白列
if (Worksheets.Contains(Hierarchy.Title + "-空白列"))
{
DataBindedSheet dataBindedSheet_Group_空白列 = new DataBindedSheet();
dataBindedSheet_Group_空白列.Worksheet = wb.Worksheets[Hierarchy.Title + "-空白列"];
dataBindedSheet_Group_空白列.DataTables = new List<DataTable>();
this._DataBindedSheets.Add(dataBindedSheet_Group_空白列);
}
}
Workbook report = this.GenerateWorkbook();
// 將「List<DataBindedSheet>」轉換為 Workbook
//Dictionary<string, Workbook> dicCourseTeacherStatistics = new Dictionary<string, Workbook>();
return report;
}
