void BGW_DoWork(object sender, DoWorkEventArgs e)
{
//電子報表
string time = DateTime.Now.Hour.ToString().PadLeft(2) + DateTime.Now.Minute.ToString().PadLeft(2);
paperForClass = new SmartSchool.ePaper.ElectronicPaper(string.Format("日常表現記錄表(新制_{0})", time), _Schoolyear.ToString(), _Semester.ToString(), SmartSchool.ePaper.ViewerType.Class);
BGW.ReportProgress(1, "取得紙張設定");
//取得列印紙張
int sizeIndex = GetSizeIndex();
BGW.ReportProgress(4, "取得假別設定");
//取得列印假別內容
Dictionary <string, List <string> > UserType = GetUserType();
#region 取得資料
//取得使用者選擇班級
BGW.ReportProgress(8, "取得所選班級");
List <ClassRecord> allClasses = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource);
//排序(因為上面沒有照班級排序)
int displayOrder;
allClasses = allClasses.OrderBy(i => i.GradeYear).ThenBy(i => displayOrder = Int32.TryParse(i.DisplayOrder, out displayOrder)? displayOrder :0).ThenBy(i => i.Name).ToList();
#region 取得使用者所選擇的班級學生
BGW.ReportProgress(12, "取得學生清單");
string classidlist = string.Join(",", K12.Presentation.NLDPanels.Class.SelectedSource);
StringBuilder sb = new StringBuilder();
sb.Append("select student.id,student.ref_class_id from student ");
sb.Append("join class on class.id=student.ref_class_id ");
sb.Append("where student.status=1 ");
sb.Append(string.Format("and class.id in ({0})", classidlist));
List <string> StudentIDList = new List <string>();
DataTable dt = _QueryHelper.Select(sb.ToString());
foreach (DataRow row in dt.Rows)
{
StudentIDList.Add("" + row[0]);
}
BGW.ReportProgress(15, "取得學生清單");
List <StudentRecord> allStudents = Student.SelectByIDs(StudentIDList);
int maxStudents = 0;
int totalStudent = allStudents.Count;
Dictionary <string, List <StudentRecord> > classStudents = new Dictionary <string, List <StudentRecord> >();
foreach (StudentRecord each in allStudents)
{
if (!classStudents.ContainsKey(each.RefClassID))
{
classStudents.Add(each.RefClassID, new List <StudentRecord>());
}
classStudents[each.RefClassID].Add(each);
}
foreach (string each in classStudents.Keys)
{
if (classStudents[each].Count > maxStudents)
{
maxStudents = classStudents[each].Count;
}
classStudents[each].Sort(SortStudent);
}
#endregion
Dictionary <string, RewardRecord> MeritDemeritAttDic = new Dictionary <string, RewardRecord>();
Dictionary <string, RewardRecord> TotalMeritDemeritDic = new Dictionary <string, RewardRecord>();
BGW.ReportProgress(20, "取得獎勵資料");
#region 獎勵
foreach (SHMeritRecord each in SHMerit.SelectByStudentIDs(StudentIDList))
{
// 2018/1/8 羿均 新增 累計獎勵紀錄資料
RewardRecord totalMerit = new RewardRecord();
if (TotalMeritDemeritDic.ContainsKey(each.RefStudentID))
{
totalMerit = TotalMeritDemeritDic[each.RefStudentID];
}
totalMerit.MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0;
totalMerit.MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0;
totalMerit.MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0;
if (!TotalMeritDemeritDic.ContainsKey(each.RefStudentID))
{
TotalMeritDemeritDic.Add(each.RefStudentID, totalMerit);
}
if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
{
continue;
}
RewardRecord rr = new RewardRecord();
if (MeritDemeritAttDic.ContainsKey(each.RefStudentID))
{
rr = MeritDemeritAttDic[each.RefStudentID];
}
rr.MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0;
rr.MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0;
rr.MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0;
if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID))
{
MeritDemeritAttDic.Add(each.RefStudentID, rr);
}
}
#endregion
BGW.ReportProgress(25, "取得懲戒資料");
#region 懲戒
foreach (SHDemeritRecord each in SHDemerit.SelectByStudentIDs(StudentIDList))
{
// 2018/1/8 羿均 新增 累計懲戒紀錄資料
RewardRecord totalDemerit = new RewardRecord();
if (each.Cleared == "是")
{
continue;
}
if (TotalMeritDemeritDic.ContainsKey(each.RefStudentID))
{
totalDemerit = TotalMeritDemeritDic[each.RefStudentID];
}
totalDemerit.DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0;
totalDemerit.DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0;
totalDemerit.DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0;
if (!TotalMeritDemeritDic.ContainsKey(each.RefStudentID))
{
TotalMeritDemeritDic.Add(each.RefStudentID, totalDemerit);
}
if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
{
continue;
}
RewardRecord rr = new RewardRecord();
if (MeritDemeritAttDic.ContainsKey(each.RefStudentID))
{
rr = MeritDemeritAttDic[each.RefStudentID];
}
rr.DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0;
rr.DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0;
rr.DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0;
if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID))
{
MeritDemeritAttDic.Add(each.RefStudentID, rr);
}
}
#endregion
BGW.ReportProgress(30, "取得節次對照");
//取得節次對照表
Dictionary <string, string> periodDic = new Dictionary <string, string>();
foreach (PeriodMappingInfo var in PeriodMapping.SelectAll())
{
string name = var.Type;
//取得對照表並且對照出節次->類別的清單(99/11/24 by dylan)
if (!periodDic.ContainsKey(var.Name))
{
periodDic.Add(var.Name, var.Type);
}
}
BGW.ReportProgress(35, "取得缺曠資料");
#region 缺曠
foreach (SHAttendanceRecord each in SHAttendance.SelectByStudentIDs(StudentIDList))
{
if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
{
continue;
}
RewardRecord rr = new RewardRecord();
if (MeritDemeritAttDic.ContainsKey(each.RefStudentID))
{
rr = MeritDemeritAttDic[each.RefStudentID];
}
foreach (AttendancePeriod _Period in each.PeriodDetail)
{
if (periodDic.ContainsKey(_Period.Period))
{
string typename = periodDic[_Period.Period] + "_" + _Period.AbsenceType;
if (rr.Attendance.ContainsKey(typename))
{
rr.Attendance[typename]++;
}
else
{
rr.Attendance.Add(typename, 1);
}
}
}
if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID))
{
MeritDemeritAttDic.Add(each.RefStudentID, rr);
}
}
#endregion
//日常表現資料
BGW.ReportProgress(40, "取得日常表現");
Dictionary <string, SHMoralScoreRecord> SHMoralScoreDic = new Dictionary <string, SHMoralScoreRecord>();
foreach (SHMoralScoreRecord each in SHMoralScore.Select(null, StudentIDList, _Schoolyear, _Semester))
{
if (!SHMoralScoreDic.ContainsKey(each.RefStudentID))
{
SHMoralScoreDic.Add(each.RefStudentID, each);
}
}
//文字評量對照表
BGW.ReportProgress(45, "取得文字評量");
List <string> TextScoreList = new List <string>();
SmartSchool.Customization.Data.SystemInformation.getField("文字評量對照表");
System.Xml.XmlElement ElmTextScoreList = (System.Xml.XmlElement)SmartSchool.Customization.Data.SystemInformation.Fields["文字評量對照表"];
foreach (System.Xml.XmlNode Node in ElmTextScoreList.SelectNodes("Content/Morality"))
{
TextScoreList.Add(Node.Attributes["Face"].InnerText);
}
#endregion
#region 產生表格
BGW.ReportProgress(50, "產生報表樣式");
Workbook template = new Workbook();
Workbook prototype = new Workbook();
//列印尺寸
if (sizeIndex == 0)
{
template.Open(new MemoryStream(Properties.Resources.德行表現總表新制A3), FileFormatType.Excel2003);
}
else if (sizeIndex == 1)
{
template.Open(new MemoryStream(Properties.Resources.德行表現總表新制A4), FileFormatType.Excel2003);
}
else if (sizeIndex == 2)
{
template.Open(new MemoryStream(Properties.Resources.德行表現總表新制B4), FileFormatType.Excel2003);
}
prototype.Copy(template);
Worksheet templateSheet = template.Worksheets[0];
Worksheet prototypeSheet = prototype.Worksheets[0];
Range tempAbsence = templateSheet.Cells.CreateRange(9, 1, true);
Range tempScoreText = templateSheet.Cells.CreateRange(10, 1, true);
Range tempAfterOtherDiff = templateSheet.Cells.CreateRange(11, 1, true);
Range oder = templateSheet.Cells.CreateRange(12, 1, true);
Dictionary <string, int> columnIndexTable = new Dictionary <string, int>();
Dictionary <string, List <string> > periodAbsence = new Dictionary <string, List <string> >();
//紀錄獎懲的 Column Index
columnIndexTable.Add("大功", 3);
columnIndexTable.Add("小功", 4);
columnIndexTable.Add("嘉獎", 5);
columnIndexTable.Add("大過", 6);
columnIndexTable.Add("小過", 7);
columnIndexTable.Add("警告", 8);
//缺曠
int ptColIndex = 9;
foreach (string var in UserType.Keys)
{
foreach (string absence in UserType[var])
{
if (!periodAbsence.ContainsKey(var))
{
periodAbsence.Add(var, new List <string>());
}
if (!periodAbsence[var].Contains(absence))
{
periodAbsence[var].Add(absence);
}
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempAbsence);
ptColIndex += 1;
}
}
ptColIndex = 9;
foreach (string period in periodAbsence.Keys)
{
prototypeSheet.Cells.CreateRange(2, ptColIndex, 1, periodAbsence[period].Count).Merge();
prototypeSheet.Cells[2, ptColIndex].PutValue(period);
foreach (string absence in periodAbsence[period])
{
prototypeSheet.Cells[3, ptColIndex].PutValue(absence);
columnIndexTable.Add(period + "_" + absence, ptColIndex);
ptColIndex++;
}
}
if (ptColIndex > 9)
{
prototypeSheet.Cells.CreateRange(1, 9, 1, ptColIndex - 9).Merge();
prototypeSheet.Cells[1, 9].PutValue("缺曠");
}
//用來調整Column寬度的定位
int ColumnMax = ptColIndex;
//文字評量
foreach (string textscore in TextScoreList)
{
columnIndexTable.Add(textscore, ptColIndex);
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempScoreText);
prototypeSheet.Cells[4, ptColIndex].PutValue(textscore);
ptColIndex++;
}
prototypeSheet.Cells[1, ptColIndex - TextScoreList.Count].PutValue("學生綜合表現");
if ((ptColIndex - TextScoreList.Count > 0) && (TextScoreList.Count > 0))
{
prototypeSheet.Cells.CreateRange(1, ptColIndex - TextScoreList.Count, 3, TextScoreList.Count).Merge();
}
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempAfterOtherDiff);
columnIndexTable.Add("評語", ptColIndex++);
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(oder);
columnIndexTable.Add("是否留察", ptColIndex++);
//填入製表日期
prototypeSheet.Cells[0, 0].PutValue("製表日期：" + DateTime.Today.ToShortDateString());
//填入標題
prototypeSheet.Cells.CreateRange(0, 3, 1, ptColIndex - 3).Merge();
prototypeSheet.Cells[0, 3].PutValue(K12.Data.School.ChineseName + " " + _Schoolyear + " 學年度 " + ((_Semester == 1) ? "上" : "下") + " 學期 日常表現記錄表（新制）");
Range ptEachRow = prototypeSheet.Cells.CreateRange(5, 1, false);
for (int i = 5; i < maxStudents + 5; i++)
{
prototypeSheet.Cells.CreateRange(i, 1, false).Copy(ptEachRow);
}
//加上底線
prototypeSheet.Cells.CreateRange(maxStudents + 5, 0, 1, ptColIndex).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, System.Drawing.Color.Black);
for (int i = 12; i >= ptColIndex; i--)
{
prototypeSheet.Cells.DeleteColumn(i);
}
Range pt = prototypeSheet.Cells.CreateRange(0, maxStudents + 5, false);
#endregion
#region 填入表格
BGW.ReportProgress(53, "填入報表資料");
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
int index = 0;
int dataIndex = 0;
int classTotalRow = maxStudents + 5;
BGW.ReportProgress(57, "填入老師姓名");
#region 取得全校班級,與老師姓名/暱稱(2012/5/24)
Dictionary <string, string> TeacherDic = new Dictionary <string, string>();
string st = "SELECT class.id,teacher.teacher_name,teacher.nickname FROM class JOIN teacher ON class.ref_teacher_id = teacher.id";
dt = _QueryHelper.Select(st);
foreach (DataRow row in dt.Rows)
{
string classID = "" + row[0];
string teacherName = "" + row[1];
string teacherNickname = "" + row[2];
if (!TeacherDic.ContainsKey(classID))
{
if (string.IsNullOrEmpty(teacherNickname))
{
TeacherDic.Add(classID, teacherName);
}
else
{
TeacherDic.Add(classID, teacherName + "(" + teacherNickname + ")");
}
}
}
#endregion
BGW.ReportProgress(61, "學生留察資料");
#region 取得全校本學年度留查之記錄(2012/5/24)
List <string> MeritFlagIs2 = new List <string>();
st = string.Format("SELECT ref_student_id from discipline where merit_flag=2 and school_year={0} and semester={1}", _Schoolyear, _Semester);
dt = _QueryHelper.Select(st);
foreach (DataRow row in dt.Rows)
{
if (!MeritFlagIs2.Contains("" + row[0]))
{
MeritFlagIs2.Add("" + row[0]);
}
}
#endregion
BGW.ReportProgress(70, "開始列印資料");
int PeogressNow1 = totalStudent / 30;
int PeogressNow2 = 0;
int PeogressNow3 = 70;
foreach (ClassRecord aClass in allClasses)
{
//電子報表用
#region 電子報表用
Workbook Paper_wb = new Workbook();
Paper_wb.Copy(prototype);
Worksheet Paper_ws = Paper_wb.Worksheets[0];
int Paper_index = 0;
int Paper_dataIndex = 0;
int Paper_classTotalRow = maxStudents + 5;
int Paper_PeogressNow1 = totalStudent / 30;
#endregion
if (!classStudents.ContainsKey(aClass.ID))
{
continue;
}
string TeacherName = "";
if (TeacherDic.ContainsKey(aClass.ID))
{
TeacherName = TeacherDic[aClass.ID];
}
//複製完成後的樣板
ws.Cells.CreateRange(index, classTotalRow, false).Copy(pt);
Paper_ws.Cells.CreateRange(Paper_index, Paper_classTotalRow, false).Copy(pt);
//填入班級名稱
ws.Cells[index + 1, 0].PutValue("班級：" + aClass.Name);
Paper_ws.Cells[Paper_index + 1, 0].PutValue("班級：" + aClass.Name);
//填入老師名稱
ws.Cells[index + 3, 0].PutValue("教師：" + TeacherName);
Paper_ws.Cells[Paper_index + 3, 0].PutValue("教師：" + TeacherName);
dataIndex = index + 5;
Paper_dataIndex = Paper_index + 5;
foreach (StudentRecord aStudent in classStudents[aClass.ID])
{
PeogressNow2++;
if (PeogressNow2 > PeogressNow1 && PeogressNow3 < 101)
{
PeogressNow3++;
PeogressNow2 = 0;
BGW.ReportProgress(PeogressNow3, "開始列印資料");
}
ws.Cells[dataIndex, 0].PutValue(aStudent.SeatNo);
ws.Cells[dataIndex, 1].PutValue(aStudent.Name);
ws.Cells[dataIndex, 2].PutValue(aStudent.StudentNumber);
Paper_ws.Cells[Paper_dataIndex, 0].PutValue(aStudent.SeatNo);
Paper_ws.Cells[Paper_dataIndex, 1].PutValue(aStudent.Name);
Paper_ws.Cells[Paper_dataIndex, 2].PutValue(aStudent.StudentNumber);
// 2018/01/17 羿均
// MeritDemeritAttDic 為當學年度學期 缺曠獎懲資料
// TotalMeritDemeritDic 為獎懲累計資料
if (MeritDemeritAttDic.ContainsKey(aStudent.ID) || TotalMeritDemeritDic.ContainsKey(aStudent.ID))
{
RewardRecord rr = new RewardRecord();
if (MeritDemeritAttDic.ContainsKey(aStudent.ID))
{
rr = MeritDemeritAttDic[aStudent.ID];
}
if (TotalMeritDemeritDic.ContainsKey(aStudent.ID))
{
RewardRecord totalRecord = TotalMeritDemeritDic[aStudent.ID];
ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue(rr.MeritACount + " / " + totalRecord.MeritACount);
ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue(rr.MeritBCount + " / " + totalRecord.MeritBCount);
ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue(rr.MeritCCount + " / " + totalRecord.MeritCCount);
ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue(rr.DemeritACount + " / " + totalRecord.DemeritACount);
ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue(rr.DemeritBCount + " / " + totalRecord.DemeritBCount);
ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue(rr.DemeritCCount + " / " + totalRecord.DemeritCCount);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue(rr.MeritACount + " / " + totalRecord.MeritACount);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue(rr.MeritBCount + " / " + totalRecord.MeritBCount);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue(rr.MeritCCount + " / " + totalRecord.MeritCCount);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue(rr.DemeritACount + " / " + totalRecord.DemeritACount);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue(rr.DemeritBCount + " / " + totalRecord.DemeritBCount);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue(rr.DemeritCCount + " / " + totalRecord.DemeritCCount);
}
else
{
//有缺曠沒獎懲，要印獎懲0/0
//https://3.basecamp.com/4399967/buckets/15765350/todos/4520462594
ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue("0 / 0");
}
foreach (string each in rr.Attendance.Keys)
{
if (columnIndexTable.ContainsKey(each))
{
ws.Cells[dataIndex, columnIndexTable[each]].PutValue(rr.Attendance[each]);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable[each]].PutValue(rr.Attendance[each]);
}
}
}
else
{
//沒缺曠沒獎懲，要印獎懲0/0
// https://3.basecamp.com/4399967/buckets/15765350/todos/4520462594
ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue("0 / 0");
ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue("0 / 0");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue("0 / 0");
}
//文字評量部份
SHMoralScoreRecord demonScore;
if (SHMoralScoreDic.ContainsKey(aStudent.ID))
{
demonScore = SHMoralScoreDic[aStudent.ID];
//文字評量
XmlElement xml = demonScore.TextScore;
foreach (XmlElement each in xml.SelectNodes("Morality"))
{
string strFace = each.GetAttribute("Face");
if (columnIndexTable.ContainsKey(strFace))
{
int colIndex = columnIndexTable[strFace];
ws.Cells[dataIndex, colIndex].PutValue(each.InnerText);
Paper_ws.Cells[Paper_dataIndex, colIndex].PutValue(each.InnerText);
}
}
//導師評語
ws.Cells[dataIndex, columnIndexTable["評語"]].PutValue(demonScore.Comment);
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["評語"]].PutValue(demonScore.Comment);
}
//留察
if (MeritFlagIs2.Contains(aStudent.ID))
{
ws.Cells[dataIndex, columnIndexTable["是否留察"]].PutValue("是");
Paper_ws.Cells[Paper_dataIndex, columnIndexTable["是否留察"]].PutValue("是");
}
foreach (int each in columnIndexTable.Values)
{
if (each >= ColumnMax)
{
Paper_ws.AutoFitColumn(each);
}
}
dataIndex++;
Paper_dataIndex++;
}
//電子報表
MemoryStream stream = Paper_wb.SaveToStream();
paperForClass.Append(new PaperItem(PaperFormat.Office2003Xls, stream, aClass.ID));
index += classTotalRow + 2;
ws.HPageBreaks.Add(index, ptColIndex);
}
foreach (int each in columnIndexTable.Values)
{
if (each >= ColumnMax)
{
ws.AutoFitColumn(each);
}
}
#endregion
if (Carty_paper)
{
BGW.ReportProgress(90, "上傳電子報表");
SmartSchool.ePaper.DispatcherProvider.Dispatch(paperForClass);
}
BGW.ReportProgress(100, "資料列印完成");
e.Result = wb;
}
private void BGW_DoWork(object sender, DoWorkEventArgs e)
{
//取得列印紙張
int sizeIndex = GetSizeIndex();
//取得需列印的項目清單
List <String> DisplayList = GetUserType();
//取得資料
BGW.ReportProgress(10, "取得所選班級");
#region 取得使用者所選擇的班級學生
//取得所選班級紀錄
List <ClassRecord> allClasses = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource);
//從班級紀錄取得學生清單
List <StudentRecord> studentList = new List <StudentRecord>(); //學生記錄清單
List <String> StudentIDList = new List <string>(); //學生ID清單
foreach (ClassRecord classrecord in allClasses)
{
if (!_ClassNameDic.ContainsKey(classrecord.ID)) //儲存班級ID及Name方便往後查詢
{
_ClassNameDic.Add(classrecord.ID, classrecord.Name);
}
foreach (StudentRecord student in classrecord.Students) //取得班級學生
{
//只取得狀態為一般及延修的學生
if (student.Status == StudentRecord.StudentStatus.一般 || student.Status == StudentRecord.StudentStatus.延修)
{
studentList.Add(student);
StudentIDList.Add(student.ID);
}
}
}
//建立班級字典存放各班級的學生
Dictionary <String, List <StudentRecord> > classDic = new Dictionary <string, List <StudentRecord> >();
foreach (StudentRecord student in studentList)
{
if (!classDic.ContainsKey(student.RefClassID)) //若該班級ID不存在就建立key
{
classDic.Add(student.RefClassID, new List <StudentRecord>());
}
classDic[student.RefClassID].Add(student); //按對應班級ID將學生加入
}
int totalStudent = studentList.Count; //全部學生的總數,進度回報用
foreach (String classid in classDic.Keys)
{
classDic[classid].Sort(SortStudent); //按學生座號排序字典內的清單
}
#endregion
BGW.ReportProgress(20, "取得資料紀錄");
#region 取得獎懲和缺曠紀錄
//獎勵紀錄
Dictionary <string, RewardRecord> MeritDemeritAttDic = new Dictionary <string, RewardRecord>();
foreach (String id in StudentIDList) //建立清單中全部學生的獎懲紀錄字典
{
if (!MeritDemeritAttDic.ContainsKey(id))
{
MeritDemeritAttDic.Add(id, new RewardRecord());
}
}
foreach (SHMeritRecord each in SHMerit.SelectByStudentIDs(StudentIDList))
{
//if (_Semester == 1)
//{
// if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
// continue;
//}
//else
//{
// if (each.SchoolYear != _Schoolyear) continue;
//}
if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
{
continue;
}
else
{
MeritDemeritAttDic[each.RefStudentID].MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0;
MeritDemeritAttDic[each.RefStudentID].MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0;
MeritDemeritAttDic[each.RefStudentID].MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0;
}
}
//懲罰紀錄
foreach (SHDemeritRecord each in SHDemerit.SelectByStudentIDs(StudentIDList))
{
//if (_Semester == 1)
//{
// if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
// continue;
//}
//else
//{
// if (each.SchoolYear != _Schoolyear) continue;
//}
if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
{
continue;
}
else
{
if (each.Cleared == "是")
{
continue;
}
if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
{
continue;
}
else
{
MeritDemeritAttDic[each.RefStudentID].DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0;
MeritDemeritAttDic[each.RefStudentID].DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0;
MeritDemeritAttDic[each.RefStudentID].DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0;
}
}
//留查紀錄
if (each.MeritFlag == "2")
{
MeritDemeritAttDic[each.RefStudentID].Flag = true;
}
//MeritDemeritAttDic[each.RefStudentID].DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0;
//MeritDemeritAttDic[each.RefStudentID].DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0;
//MeritDemeritAttDic[each.RefStudentID].DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0;
}
//取得節次對照表
Dictionary <String, String> periodDic = new Dictionary <String, String>();
foreach (PeriodMappingInfo var in PeriodMapping.SelectAll())
{
if (!periodDic.ContainsKey(var.Name))
{
periodDic.Add(var.Name, var.Type); //key=升降旗,一,二,三,午休...etc , value=一般,集會...etc
}
}
//取得影響缺曠紀錄的假別清單
List <AbsenceMappingInfo> infoList = K12.Data.AbsenceMapping.SelectAll();
List <String> Noabsence = new List <string>();
foreach (AbsenceMappingInfo info in infoList)
{
if (!info.Noabsence) //若該假別會影響全勤就加入清單
{
if (!Noabsence.Contains(info.Name))
{
Noabsence.Add(info.Name);
}
}
}
//缺曠紀錄
foreach (SHAttendanceRecord each in SHAttendance.SelectByStudentIDs(StudentIDList))
{
if (each.SchoolYear != _Schoolyear || each.Semester != _Semester)
{
continue;
}
foreach (AttendancePeriod record in each.PeriodDetail)
{
if (periodDic.ContainsKey(record.Period)) //確認是否有此節次
{
string typename = periodDic[record.Period] + "_" + record.AbsenceType; //ex...一般_曠課,集會_曠課
if (!DisplayList.Contains(typename))
{
continue;
}
if (Noabsence.Contains(record.AbsenceType)) //如果此缺曠紀錄的假別會影響全勤,該學生的前勤紀錄則為false
{
MeritDemeritAttDic[each.RefStudentID].全勤 = false;
}
if (!MeritDemeritAttDic[each.RefStudentID].Attendance.ContainsKey(record.AbsenceType))
{
MeritDemeritAttDic[each.RefStudentID].Attendance.Add(record.AbsenceType, 0);
}
MeritDemeritAttDic[each.RefStudentID].Attendance[record.AbsenceType]++;
}
}
}
#endregion
//產生表格
BGW.ReportProgress(30, "產生表格");
#region 產生表格
Workbook template = new Workbook();
Workbook prototype = new Workbook();
//列印尺寸
if (sizeIndex == 0)
{
template.Open(new MemoryStream(Properties.Resources.班級缺曠獎懲總表A3));
}
else if (sizeIndex == 1)
{
template.Open(new MemoryStream(Properties.Resources.班級缺曠獎懲總表A4));
}
else if (sizeIndex == 2)
{
template.Open(new MemoryStream(Properties.Resources.班級缺曠獎懲總表B4));
}
prototype.Copy(template);
Worksheet prototypeSheet;
#region 範本sheet製作
//在範本sheet新增假別欄位
prototypeSheet = prototype.Worksheets[0];
_AbsenceType.Clear();
foreach (string item in DisplayList)
{
string type = item.Split('_')[1];
if (!_AbsenceType.Contains(type))
{
_AbsenceType.Add(type);
}
}
for (int i = 0; i < _AbsenceType.Count; i++) //依照勾選的顯示清單數量插入新的欄位
{
prototypeSheet.Cells.InsertColumn(_DynamicIndex + 1);
}
//刪除兩個範本格式Column
prototypeSheet.Cells.DeleteColumn(_DynamicIndex);
prototypeSheet.Cells.DeleteColumn(_DynamicIndex);
//標記新增的假別項目欄位索引
Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); //Excel欄位索引
//標記欄位索引
int index = _DynamicIndex;
columnIndexTable.Add("座號", 0);
columnIndexTable.Add("學號", 1);
columnIndexTable.Add("姓名", 2);
columnIndexTable.Add("嘉獎", 3);
columnIndexTable.Add("小功", 4);
columnIndexTable.Add("大功", 5);
columnIndexTable.Add("警告", 6);
columnIndexTable.Add("小過", 7);
columnIndexTable.Add("大過", 8);
columnIndexTable.Add("累嘉獎", 9);
columnIndexTable.Add("累小功", 10);
columnIndexTable.Add("累大功", 11);
columnIndexTable.Add("累警告", 12);
columnIndexTable.Add("累小過", 13);
columnIndexTable.Add("累大過", 14);
columnIndexTable.Add("留查", 15);
//標記動態欄位索引並列印標題
//Dictionary<String, int> mergeIndex = new Dictionary<string, int>(); //紀錄需要merge的column數量
foreach (String type in _AbsenceType)
{
if (!columnIndexTable.ContainsKey(type))
{
columnIndexTable.Add(type, index);
prototypeSheet.Cells.CreateRange(1, columnIndexTable[type], 2, 1).Merge();
prototypeSheet.Cells[1, columnIndexTable[type]].PutValue(type);
index++;
}
//columnIndexTable.Add(str, index); //標記動態欄位索引
//String[] strs = str.Split('_'); //將"一般_曠課"字串以_字元拆開
//prototypeSheet.Cells[2, columnIndexTable[str]].PutValue(strs[1]); //列印標題...ex:曠課
//if (!mergeIndex.ContainsKey(strs[0])) //若是相同title,則數量加1
//{
//mergeIndex.Add(strs[0], 0);
//}
//mergeIndex[strs[0]]++; //若是相同title,則數量加1
}
//int start = _DynamicIndex; //merge的起始值
//foreach (String s in mergeIndex.Keys)
//{
// prototypeSheet.Cells.CreateRange(1, start, 1, mergeIndex[s]).Merge();
// prototypeSheet.Cells[1, start].PutValue(s);
// start += mergeIndex[s];
//}
//全勤為最後標記
columnIndexTable.Add("全勤", index);
for (int i = 3; i <= index; i++)
{
prototypeSheet.Cells.SetColumnWidth(i, 11);
}
#endregion
#region 各班級sheet製作
int page = 1;
foreach (String id in classDic.Keys)
{
prototype.Worksheets.AddCopy(0); //複製範本sheet
prototypeSheet = prototype.Worksheets[page]; //從第二個分頁開始畫製表格,page++;
prototypeSheet.Name = GetClassName(id); //sheet.Name = 班級名稱
//每5行加一條分隔線
Range eachFiveLine = prototype.Worksheets[0].Cells.CreateRange(_StartIndex, 5, false); //從第一個sheet複製
for (int i = _StartIndex; i < classDic[id].Count + _StartIndex; i += 5) //依照該班級學生數給予適量的分隔線
{
prototypeSheet.Cells.CreateRange(i, 5, false).CopyStyle(eachFiveLine);
}
page++; //完成一個班級換下個sheet的畫製
}
prototype.Worksheets.RemoveAt(0); //都完成後刪除第一個範本sheet
#endregion
#endregion
//填入表格
BGW.ReportProgress(40, "填入表格");
#region 填入表格
_WK = new Workbook();
int sheetIndex = 0;
_WK.Copy(prototype); //複製畫製好欄位的範本
Worksheet ws;
Cells cs;
//取得功過換算比例
MeritDemeritReduceRecord mdrr = MeritDemeritReduce.Select();
int?MAB = mdrr.MeritAToMeritB;
int?MBC = mdrr.MeritBToMeritC;
int?DAB = mdrr.DemeritAToDemeritB;
int?DBC = mdrr.DemeritBToDemeritC;
float progress = 50;
float rate = (float)(100 - progress) / totalStudent; //進度百分比計算
foreach (String classid in classDic.Keys)
{
ws = _WK.Worksheets[sheetIndex];
cs = ws.Cells;
index = _StartIndex; //列印起始索引
_CountAllColumnValue = new Dictionary <int, int>(); //重制個項目的總數
foreach (StudentRecord student in classDic[classid])
{
progress += rate;
BGW.ReportProgress((int)progress, "正在填入資料...");
String id = student.ID;
int? 獎 = MeritDemeritAttDic[id].MeritCCount;
int? 小功 = MeritDemeritAttDic[id].MeritBCount;
int? 大功 = MeritDemeritAttDic[id].MeritACount;
int? 警告 = MeritDemeritAttDic[id].DemeritCCount;
int? 小過 = MeritDemeritAttDic[id].DemeritBCount;
int? 大過 = MeritDemeritAttDic[id].DemeritACount;
//將功過轉為嘉獎和警告,做功過相抵計算
獎 = 大功 * MAB * MBC + 小功 * MBC + 獎;
警告 = 大過 * DAB * DBC + 小過 * DBC + 警告;
int?[] i = 功過相抵(獎, 警告);
獎 = i[0];
警告 = i[1];
//獎勵換算
int?累嘉獎 = 獎 % MBC;
int?累小功 = (獎 / MBC) % MAB;
int?累大功 = (獎 / MBC) / MAB;
//懲戒換算
int?累警告 = 警告 % DBC;
int?累小過 = (警告 / DBC) % DAB;
int?累大過 = (警告 / DBC) / DAB;
cs[index, columnIndexTable["座號"]].PutValue(student.SeatNo);
cs[index, columnIndexTable["學號"]].PutValue(student.StudentNumber);
cs[index, columnIndexTable["姓名"]].PutValue(student.Name);
SetColumnValue(cs[index, columnIndexTable["嘉獎"]], MeritDemeritAttDic[id].MeritCCount);
SetColumnValue(cs[index, columnIndexTable["小功"]], MeritDemeritAttDic[id].MeritBCount);
SetColumnValue(cs[index, columnIndexTable["大功"]], MeritDemeritAttDic[id].MeritACount);
SetColumnValue(cs[index, columnIndexTable["警告"]], MeritDemeritAttDic[id].DemeritCCount);
SetColumnValue(cs[index, columnIndexTable["小過"]], MeritDemeritAttDic[id].DemeritBCount);
SetColumnValue(cs[index, columnIndexTable["大過"]], MeritDemeritAttDic[id].DemeritACount);
SetColumnValue(cs[index, columnIndexTable["累嘉獎"]], 累嘉獎);
SetColumnValue(cs[index, columnIndexTable["累小功"]], 累小功);
SetColumnValue(cs[index, columnIndexTable["累大功"]], 累大功);
SetColumnValue(cs[index, columnIndexTable["累警告"]], 累警告);
SetColumnValue(cs[index, columnIndexTable["累小過"]], 累小過);
SetColumnValue(cs[index, columnIndexTable["累大過"]], 累大過);
SetColumnValue(cs[index, columnIndexTable["留查"]], MeritDemeritAttDic[id].Flag ? "是" : "");
SetColumnValue(cs[index, columnIndexTable["全勤"]], MeritDemeritAttDic[id].全勤 ? "是" : "");
foreach (String type in _AbsenceType) //列印勾選的假別
{
if (MeritDemeritAttDic[id].Attendance.ContainsKey(type))
{
SetColumnValue(cs[index, columnIndexTable[type]], MeritDemeritAttDic[id].Attendance[type]);
}
}
index++; //換下一列
}
//最後總計
index = FixIndex(index);
Range endRow = cs.CreateRange(0, 1, false);
cs.CreateRange(index, 1, false).Copy(endRow);
cs[index, 0].PutValue("總計");
foreach (int cloumnIndex in _CountAllColumnValue.Keys)
{
cs[index, cloumnIndex].PutValue(_CountAllColumnValue[cloumnIndex]);
}
//列印日期及學校班級資訊
cs[0, 0].PutValue("列印日期:" + DateTime.Today.ToShortDateString());
cs.CreateRange(0, 3, 1, columnIndexTable.Last().Value - 2).Merge(); //合併標題欄位的儲存格
String title = String.Format("{0} {1} 學年度 {2} 學期 {3} 缺曠獎懲總表", K12.Data.School.ChineseName, _Schoolyear, _Semester == 1 ? "上" : "下", GetClassName(classid));
cs[0, 3].PutValue(title);
cs[0, 3].Style.Font.Size = 28; //設定標題文字大小
cs[0, 3].Style.Font.IsBold = true;
sheetIndex++; //換下一個sheet(下一個班級班)
}
//int sheet = _WK.Worksheets.Count;
//for (int i = 0; i < sheet; i++)
//{
// _WK.Worksheets[i].AutoFitColumns();
// _WK.Worksheets[i].AutoFitRows();
//}
BGW.ReportProgress(100, "已完成 班級缺曠獎懲總表");
#endregion
}