private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
Workbook wb = new Workbook();
wb.Copy((Workbook)((Control)sender).Tag);
Completed(_Title + "_驗證", wb);
}
private void wizardPage3_AfterPageDisplayed(object sender, WizardPageChangeEventArgs e)
{
this.progressBarX1.Value = 0;
lblWarningCount.Text = lblErrCount.Text = "0";
this.wizardPage3.FinishButtonEnabled = eWizardButtonState.False;
linkLabel1.Visible = false;
labelX2.Text = "資料驗證中";
linkLabel3.Tag = null;
linkLabel3.Visible = false;
Application.DoEvents();
_BKWValidate = new BackgroundWorker();
_BKWValidate.WorkerReportsProgress = true;
_BKWValidate.WorkerSupportsCancellation = true;
_BKWValidate.DoWork += new DoWorkEventHandler(_BKWValidate_DoWork);
_BKWValidate.ProgressChanged += new ProgressChangedEventHandler(_BKWValidate_ProgressChanged);
_BKWValidate.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_BKWValidate_RunWorkerCompleted);
List <string> fields = new List <string>();
string fileName = txtFile.Text;
fields.AddRange(_RequiredFields);
foreach (ListViewItem item in listView1.Items)
{
if (item.Checked)
{
fields.Add(item.Text);
}
}
_ErrorRows = new Dictionary <RowData, string>();
_WarningRows = new Dictionary <RowData, string>();
Workbook wb = new Workbook();
wb.Copy(_WorkBook);
_BKWValidate.RunWorkerAsync(new object[] { fields, _ImportFields, wb });
}
public ExportStudentClub(string sy, string s)
{
//儲存資料
Workbook wb = new Workbook();
Exception exc = null;
BackgroundWorker bkw = new BackgroundWorker()
{
WorkerReportsProgress = true
};
bkw.ProgressChanged += delegate(object sender, ProgressChangedEventArgs e)
{
MotherForm.SetStatusBarMessage("匯出選社結果", e.ProgressPercentage);
};
bkw.DoWork += delegate
{
try
{
bkw.ReportProgress(1);
List <CLUBRecord> _clubList = this.access.Select <CLUBRecord>();
foreach (CLUBRecord club in _clubList)
{
dicClubNameByID.Add(club.UID, club.ClubName);
}
//建立Excel範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.匯出選社結果_範本), FileFormatType.Excel97To2003);
wb.Copy(template);
//取得Sheet
Worksheet ws = wb.Worksheets[0];
#region SQL
string selectSQL = string.Format(@"
SELECT
student.id
, class.class_name
, class.grade_year
, seat_no,name
, student_number
, student.ref_class_id
, clubrecord.ref_club_id
, clubrecord.lock
, clubrecord.club_name
, clubrecord.school_year
, clubrecord.semester
, volunteer.content
, $k12.config.universal.content as wish_limit
FROM
student
LEFT OUTER JOIN class on class.id = student.ref_class_id
LEFT OUTER JOIN (
SELECT
scjoin.*
, clubrecord.club_name
, clubrecord.school_year
, clubrecord.semester
FROM
$k12.clubrecord.universal AS clubrecord
LEFT OUTER JOIN $k12.scjoin.universal AS scjoin
ON clubrecord.uid = scjoin.ref_club_id::bigint
WHERE
clubrecord.school_year = {0}
AND clubrecord.semester = {1}
) AS clubrecord
ON clubrecord.ref_student_id::bigint = student.id
LEFT OUTER JOIN $k12.volunteer.universal AS volunteer
ON volunteer.ref_student_id::bigint = student.id
AND volunteer.school_year = {0}
AND volunteer.semester = {1}
LEFT OUTER JOIN $k12.config.universal ON config_name = '學生選填志願數'
WHERE
student.status in (1, 2)
ORDER BY
class.grade_year
, class.display_order
, class.class_name
, student.seat_no
, student.id"
, sy, s);
#endregion
// 取得學生社團資料
DataTable dt = this.qh.Select(selectSQL);
bkw.ReportProgress(10);
int index = 1;
foreach (DataRow dr in dt.Rows)
{
bkw.ReportProgress(10 + 90 * index / dt.Rows.Count);
int wishLimit = (dr["wish_limit"] == null ? 5 : int.Parse("" + dr["wish_limit"]));
if (index == 1)
{
int countLimit = 6 + wishLimit;
for (int i = 6; i < countLimit; i++)
{
ws.Cells.CopyColumn(ws.Cells, 4, i);
ws.Cells[0, i].PutValue("志願" + (i - 5));
}
}
else
{
ws.Cells.CopyRow(ws.Cells, 1, index);
int countLimit = 6 + wishLimit;
for (int i = 0; i < countLimit; i++)
{
ws.Cells[index, i].PutValue(null);
}
}
ws.Cells[index, 0].PutValue("" + dr["class_name"]);
ws.Cells[index, 1].PutValue("" + dr["seat_no"]);
ws.Cells[index, 2].PutValue("" + dr["name"]);
ws.Cells[index, 3].PutValue("" + dr["student_number"]);
ws.Cells[index, 4].PutValue("" + dr["club_name"]);
ws.Cells[index, 5].PutValue(("" + dr["lock"]) == "true" ? "是" : "");
// 學生志願序
if (dr["content"] != null && "" + dr["content"] != "")
{
XDocument content = XDocument.Parse("" + dr["content"]);
List <XElement> clubList = content.Element("xml").Elements("Club").ToList();
int count = 6;
int countLimit = count + wishLimit - 1;
foreach (XElement club in clubList)
{
if (!dicClubNameByID.ContainsKey(club.Attribute("Ref_Club_ID").Value))
{
continue;
}
ws.Cells[index, count].PutValue(dicClubNameByID[club.Attribute("Ref_Club_ID").Value]);
count++;
if (count > countLimit)
{
break;
}
}
}
index++;
}
}
catch (Exception ex)
{
exc = ex;
}
};
bkw.RunWorkerCompleted += delegate
{
if (exc == null)
{
MotherForm.SetStatusBarMessage("匯出選社結果完成");
#region Excel 存檔
{
SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
SaveFileDialog1.Filter = "Excel (*.xls)|*.xls|所有檔案 (*.*)|*.*";
SaveFileDialog1.FileName = "匯出選社結果";
try
{
if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
{
wb.Save(SaveFileDialog1.FileName, SaveFormat.Excel97To2003);
Process.Start(SaveFileDialog1.FileName);
MotherForm.SetStatusBarMessage("匯出選社結果,列印完成!!");
}
else
{
FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存");
return;
}
}
catch
{
FISCA.Presentation.Controls.MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!");
MotherForm.SetStatusBarMessage("檔案儲存錯誤,請檢查檔案是否開啟中!!");
}
}
#endregion
}
else
{
throw new Exception("匯出選社結果 發生錯誤", exc);
}
};
bkw.RunWorkerAsync();
}
void _BGWSemesterMoralScoresCalculate_DoWork(object sender, DoWorkEventArgs e)
{
object[] args = (object[])e.Argument;
int schoolyear = (int)args[0];
int semester = (int)args[1];
bool over100 = (bool)args[2];
int sizeIndex = (int)args[3];
Dictionary <string, List <string> > userType = (Dictionary <string, List <string> >)args[4];
_BGWSemesterMoralScoresCalculate.ReportProgress(1);
#region 取得資料
AccessHelper dataSeed = new AccessHelper();
List <ClassRecord> allClasses = dataSeed.ClassHelper.GetSelectedClass();
List <StudentRecord> allStudents = new List <StudentRecord>();
Dictionary <string, List <StudentRecord> > classStudents = new Dictionary <string, List <StudentRecord> >();
AngelDemonComputer computer = new AngelDemonComputer();
int maxStudents = 0;
int totalStudent = 0;
int currentStudent = 1;
foreach (ClassRecord aClass in allClasses)
{
List <StudentRecord> studnetList = aClass.Students;
if (studnetList.Count > maxStudents)
{
maxStudents = studnetList.Count;
}
allStudents.AddRange(studnetList);
//computer.FillDemonScore(dataSeed, schoolyear, semester, studnetList);
classStudents.Add(aClass.ClassID, studnetList);
totalStudent += studnetList.Count;
}
computer.FillDemonScore(dataSeed, schoolyear, semester, allStudents);
if (semester == 2)
{
dataSeed.StudentHelper.FillSemesterEntryScore(true, allStudents);
dataSeed.StudentHelper.FillSchoolYearEntryScore(true, allStudents);
dataSeed.StudentHelper.FillSemesterHistory(allStudents);
}
SystemInformation.getField("DiffItem");
SystemInformation.getField("Degree");
Dictionary <string, decimal> degreeList = (Dictionary <string, decimal>)SystemInformation.Fields["Degree"];
#endregion
#region 產生表格
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 tempInfoAndReward = templateSheet.Cells.CreateRange(0, 17, true);
Range tempAbsence = templateSheet.Cells.CreateRange(17, 2, true);
Range tempBeforeOtherDiff = templateSheet.Cells.CreateRange(19, 3, true);
Range tempOtherDiff = templateSheet.Cells.CreateRange(22, 1, true);
Range tempAfterOtherDiff = templateSheet.Cells.CreateRange(25, 2, true);
Dictionary <string, int> columnIndexTable = new Dictionary <string, int>();
Dictionary <string, List <string> > periodAbsence = new Dictionary <string, List <string> >();
//紀錄獎懲的 Column Index
columnIndexTable.Add("大功", 4);
columnIndexTable.Add("小功", 6);
columnIndexTable.Add("嘉獎", 8);
columnIndexTable.Add("大過", 10);
columnIndexTable.Add("小過", 12);
columnIndexTable.Add("警告", 14);
columnIndexTable.Add("獎懲小計", 16);
//缺曠加減分
int ptColIndex = 17;
foreach (SmartSchool.Evaluation.AngelDemonComputer.UsefulPeriodAbsence var in computer.UsefulPeriodAbsences)
{
if (!periodAbsence.ContainsKey(var.Period))
{
periodAbsence.Add(var.Period, new List <string>());
}
if (!periodAbsence[var.Period].Contains(var.Absence))
{
periodAbsence[var.Period].Add(var.Absence);
}
prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAbsence);
ptColIndex += 2;
}
//foreach (string period in userType.Keys)
//{
// if (!periodAbsence.ContainsKey(period))
// {
// periodAbsence.Add(period, new List<string>());
// foreach (string absence in userType[period])
// {
// if (!periodAbsence[period].Contains(absence))
// {
// periodAbsence[period].Add(absence);
// prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAbsence);
// ptColIndex += 2;
// }
// }
// }
//}
ptColIndex = 17;
foreach (string period in periodAbsence.Keys)
{
prototypeSheet.Cells.CreateRange(2, ptColIndex, 1, periodAbsence[period].Count * 2).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 += 2;
}
}
prototypeSheet.Cells.CreateRange(1, 17, 1, ptColIndex - 15).Merge();
prototypeSheet.Cells[1, 17].PutValue("缺曠加減分");
columnIndexTable.Add("全勤", ptColIndex);
columnIndexTable.Add("缺曠小計", ptColIndex + 1);
prototypeSheet.Cells.CreateRange(ptColIndex, 3, true).Copy(tempBeforeOtherDiff);
ptColIndex += 3;
//導師加減分
columnIndexTable.Add("導師加減分", ptColIndex - 1);
//其他加減分項目
foreach (string var in (List <string>)SystemInformation.Fields["DiffItem"])
{
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff);
prototypeSheet.Cells[1, ptColIndex].PutValue(var);
columnIndexTable.Add(var, ptColIndex);
ptColIndex++;
}
if (semester == 2)
{
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff);
prototypeSheet.Cells[1, ptColIndex].PutValue("上學期德行成績");
columnIndexTable.Add("上學期成績", ptColIndex++);
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff);
prototypeSheet.Cells[1, ptColIndex].PutValue("學期德行成績");
columnIndexTable.Add("學期成績", ptColIndex++);
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff);
prototypeSheet.Cells[1, ptColIndex].PutValue("等第");
columnIndexTable.Add("等第", ptColIndex++);
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff);
prototypeSheet.Cells[1, ptColIndex].PutValue("學年德行成績");
columnIndexTable.Add("學年成績", ptColIndex++);
}
else
{
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff);
prototypeSheet.Cells[1, ptColIndex].PutValue("學期德行成績");
columnIndexTable.Add("學期成績", ptColIndex++);
prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff);
prototypeSheet.Cells[1, ptColIndex].PutValue("等第");
columnIndexTable.Add("等第", ptColIndex++);
}
prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAfterOtherDiff);
columnIndexTable.Add("評語", ptColIndex++);
columnIndexTable.Add("更改等第", ptColIndex++);
//加上底線
prototypeSheet.Cells.CreateRange(maxStudents + 5, 0, 1, ptColIndex).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, System.Drawing.Color.Black);
//填入製表日期
prototypeSheet.Cells[0, 0].PutValue("製表日期：" + DateTime.Today.ToShortDateString());
//填入標題
prototypeSheet.Cells.CreateRange(0, 4, 1, ptColIndex - 4).Merge();
prototypeSheet.Cells[0, 4].PutValue(SystemInformation.SchoolChineseName + " " + 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);
}
Range pt = prototypeSheet.Cells.CreateRange(0, maxStudents + 5, false);
#endregion
#region 填入表格
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
int index = 0;
int dataIndex = 0;
int classTotalRow = maxStudents + 5;
foreach (ClassRecord aClass in allClasses)
{
//複製完成後的樣板
ws.Cells.CreateRange(index, classTotalRow, false).Copy(pt);
//填入班級名稱
ws.Cells[index + 1, 0].PutValue(aClass.ClassName);
Dictionary <string, int> degreeCount = new Dictionary <string, int>();
foreach (string key in degreeList.Keys)
{
degreeCount.Add(key, 0);
}
dataIndex = index + 5;
foreach (StudentRecord aStudent in classStudents[aClass.ClassID])
{
ws.Cells[dataIndex, 0].PutValue(aStudent.SeatNo);
ws.Cells[dataIndex, 1].PutValue(aStudent.StudentName);
ws.Cells[dataIndex, 2].PutValue(aStudent.StudentNumber);
decimal score = 0;
decimal rewardScore = 0;
decimal absenceScore = 0;
decimal diffScore = 0;
XmlElement demonScore = (XmlElement)aStudent.Fields["DemonScore"];
score = decimal.Parse(demonScore.GetAttribute("Score"));
foreach (XmlElement var in demonScore.SelectNodes("SubScore"))
{
if (var.GetAttribute("Type") == "基分")
{
ws.Cells[dataIndex, 3].PutValue(var.GetAttribute("Score"));
}
else if (var.GetAttribute("Type") == "獎懲")
{
int colIndex = columnIndexTable[var.GetAttribute("Name")];
if (decimal.Parse(var.GetAttribute("Count")) != 0)
{
ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Count"));
}
if (decimal.Parse(var.GetAttribute("Score")) != 0)
{
ws.Cells[dataIndex, colIndex + 1].PutValue(var.GetAttribute("Score"));
}
rewardScore += decimal.Parse(var.GetAttribute("Score"));
}
else if (var.GetAttribute("Type") == "缺曠")
{
string pa = var.GetAttribute("PeriodType") + "_" + var.GetAttribute("Absence");
if (columnIndexTable.ContainsKey(pa))
{
int colIndex = columnIndexTable[pa];
if (decimal.Parse(var.GetAttribute("Count")) != 0)
{
ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Count"));
}
if (decimal.Parse(var.GetAttribute("Score")) != 0)
{
ws.Cells[dataIndex, colIndex + 1].PutValue(var.GetAttribute("Score"));
}
}
absenceScore += decimal.Parse(
var.GetAttribute("Score"));
}
else if (var.GetAttribute("Type") == "加減分")
{
int colIndex = columnIndexTable[var.GetAttribute("DiffItem")];
if (decimal.Parse(var.GetAttribute("Score")) != 0)
{
ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Score"));
}
diffScore += decimal.Parse(var.GetAttribute("Score"));
}
else if (var.GetAttribute("Type") == "全勤")
{
int colIndex = columnIndexTable["全勤"];
if (decimal.Parse(var.GetAttribute("Score")) != 0)
{
ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Score"));
}
absenceScore += decimal.Parse(var.GetAttribute("Score"));
}
}
//填入獎懲小計
if (rewardScore != 0)
{
ws.Cells[dataIndex, columnIndexTable["獎懲小計"]].PutValue(rewardScore.ToString());
}
//填入缺曠小計
if (absenceScore != 0)
{
ws.Cells[dataIndex, columnIndexTable["缺曠小計"]].PutValue(absenceScore.ToString());
}
//填入學業成績試算
if (!over100 && score > 100)
{
score = 100;
}
ws.Cells[dataIndex, columnIndexTable["學期成績"]].PutValue(score.ToString());
//填入上學期&學年成績
if (semester == 2)
{
int gradeYear = -1;
foreach (SemesterHistory sh in aStudent.SemesterHistoryList)
{
if (sh.SchoolYear == schoolyear && sh.Semester == semester)
{
gradeYear = sh.GradeYear;
break;
}
}
//沒有學期歷程就用當下的學期
if (gradeYear == -1 && schoolyear == SystemInformation.SchoolYear)
{
int.TryParse(aStudent.RefClass.GradeYear, out gradeYear);
}
//填入上學期成績
foreach (SemesterEntryScoreInfo semesterEntryScore in aStudent.SemesterEntryScoreList)
{
if (semesterEntryScore.Entry == "德行" && semesterEntryScore.GradeYear == gradeYear && semesterEntryScore.Semester == 1)
{
if (!over100 && semesterEntryScore.Score > 100)
{
ws.Cells[dataIndex, columnIndexTable["上學期成績"]].PutValue("100");
}
else
{
ws.Cells[dataIndex, columnIndexTable["上學期成績"]].PutValue(semesterEntryScore.Score.ToString());
}
break;
}
}
//填入學年成績
foreach (SchoolYearEntryScoreInfo schoolyearEntryScore in aStudent.SchoolYearEntryScoreList)
{
if (schoolyearEntryScore.Entry == "德行" && schoolyearEntryScore.GradeYear == gradeYear)
{
if (!over100 && schoolyearEntryScore.Score > 100)
{
ws.Cells[dataIndex, columnIndexTable["學年成績"]].PutValue("100");
}
else
{
ws.Cells[dataIndex, columnIndexTable["學年成績"]].PutValue(schoolyearEntryScore.Score.ToString());
}
break;
}
}
}
//填入等第
string degree = computer.ParseLevel(score);
ws.Cells[dataIndex, columnIndexTable["等第"]].PutValue(degree);
//計算等第出現次數
if (degreeCount.ContainsKey(degree))
{
degreeCount[degree]++;
}
//評語
if (demonScore.SelectSingleNode("Others/@Comment") != null)
{
ws.Cells[dataIndex, columnIndexTable["評語"]].PutValue(demonScore.SelectSingleNode("Others/@Comment").InnerText);
}
dataIndex++;
//回報進度
_BGWSemesterMoralScoresCalculate.ReportProgress((int)(currentStudent++ *100.0 / totalStudent));
}
ws.Cells.CreateRange(index + classTotalRow + 1, 0, 1, ptColIndex).Merge();
StringBuilder degreeSumString = new StringBuilder("");
degreeSumString.Append("德行等第統計 ");
foreach (string key in degreeCount.Keys)
{
degreeSumString.Append(key + "等： " + degreeCount[key].ToString() + " ");
}
ws.Cells[index + classTotalRow + 1, 0].Style.Font.Size = 12;
ws.Cells.CreateRange(index + classTotalRow + 1, 1, false).RowHeight = 20;
ws.Cells[index + classTotalRow + 1, 0].PutValue(degreeSumString.ToString());
ws.Cells[index + classTotalRow + 1, 0].Style.HorizontalAlignment = TextAlignmentType.Left;
index += classTotalRow + 3;
ws.HPageBreaks.Add(index, ptColIndex);
}
#endregion
e.Result = wb;
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
Workbook wb = new Workbook();
wb.Copy((Workbook)((Control)sender).Tag);
Completed(_Title + "_驗證", wb);
}
void BGW_DoWork(object sender, DoWorkEventArgs e)
{
//取得相關的學生資料
//1.基本資料
//2.社團結算成績
GetPoint = new ClubTraMag();
//取得範本
#region 建立範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.社團成績單_範本), FileFormatType.Excel97To2003);
//Style sy = template.Worksheets[0].Cells[3, 0].Style;
//每一張
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
#region 建立標頭Column
評量比例 評 = new 評量比例();
ColumnNameList = new List <string>();
ColumnNameList.Add("班級名稱");
ColumnNameList.Add("座號");
ColumnNameList.Add("姓名");
ColumnNameList.Add("學號");
ColumnNameList.Add("性別");
foreach (string each in 評.ColumnDic.Keys)
{
ColumnNameList.Add(each + "(" + 評.ProportionDic[each] + "%)");
}
ColumnNameList.Add("學期成績");
ColumnNameList.Add("社團幹部");
int ColumnNameIndex = 0;
//Jean 更新Aspose
Style style = prototype.CreateStyle();
style.IsTextWrapped = true;
foreach (string each in ColumnNameList)
{
ptws.Cells[2, ColumnNameIndex].SetStyle(style);
ptws.Cells[2, ColumnNameIndex].PutValue(each);
if (ColumnNameIndex >= 5)
{
ptws.Cells.SetColumnWidth(ColumnNameIndex, 8);
tool.SetCellBro(ptws, 2, ColumnNameIndex, 1, 1);
}
ColumnNameIndex++;
}
#endregion
Range ptHeader = ptws.Cells.CreateRange(0, 3, false);
Range ptEachRow = ptws.Cells.CreateRange(3, 1, false);
//建立Excel檔案
Workbook wb = new Workbook();
wb.Copy(prototype);
//取得第一張
Worksheet ws = wb.Worksheets[0];
int dataIndex = 0;
int CountPage = 1;
int DetalIndex = 5;
#endregion
#region 填資料
foreach (string clubID in GetPoint.TraDic.Keys)
{
//每一個社團
ws.Cells.CreateRange(dataIndex, 3, false).CopyStyle(ptHeader);
ws.Cells.CreateRange(dataIndex, 3, false).CopyValue(ptHeader);
CLUBRecord cr = GetPoint.CLUBDic[clubID];
//第一行 - 建立標頭內容
ws.Cells.Merge(dataIndex, 0, 1, ColumnNameList.Count);
string TitleName = string.Format("{0}學年度 第{1}學期 {2} 社團成績單", cr.SchoolYear.ToString(), cr.Semester.ToString(), cr.ClubName);
ws.Cells[dataIndex, 0].PutValue(TitleName);
dataIndex++;
//第二行 - 代碼
ws.Cells.Merge(dataIndex, 0, 1, 3);
ws.Cells[dataIndex, 0].PutValue("代碼：" + cr.ClubNumber);
//第二行 - 老師
//教師 ColumnDic大小後的一格
ws.Cells.Merge(dataIndex, ColumnNameList.Count - 3, 1, 3);
string TeacherString = "教師：" + GetTeacherName(cr);
ws.Cells[dataIndex, ColumnNameList.Count - 3].PutValue(TeacherString);
//SetCellBro(ws, dataIndex, 0, 1, ColumnNameList.Count);
dataIndex += 2;
GetPoint.TraDic[clubID].Sort(SortTraDic);
foreach (ClubTraObj each in GetPoint.TraDic[clubID])
{
ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow);
ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow);
//基本資料
tool.SetCellBro(ws, dataIndex, 0, 1, 1);
ws.Cells[dataIndex, 0].PutValue(each.student.Class != null ? each.student.Class.Name : "");
tool.SetCellBro(ws, dataIndex, 1, 1, 1);
ws.Cells[dataIndex, 1].PutValue(each.student.SeatNo.HasValue ? each.student.SeatNo.Value.ToString() : "");
tool.SetCellBro(ws, dataIndex, 2, 1, 1);
ws.Cells[dataIndex, 2].PutValue(each.student.Name);
tool.SetCellBro(ws, dataIndex, 3, 1, 1);
ws.Cells[dataIndex, 3].PutValue(each.student.StudentNumber);
tool.SetCellBro(ws, dataIndex, 4, 1, 1);
ws.Cells[dataIndex, 4].PutValue(each.student.Gender);
if (!string.IsNullOrEmpty(each.SCJ.Score))
{
XmlElement xml = DSXmlHelper.LoadXml(each.SCJ.Score);
foreach (XmlElement each1 in xml.SelectNodes("Item"))
{
string name = each1.GetAttribute("Name");
if (評.ColumnDic.ContainsKey(name))
{
tool.SetCellBro(ws, dataIndex, DetalIndex + 評.ColumnDic[name], 1, 1);
ws.Cells[dataIndex, DetalIndex + 評.ColumnDic[name]].PutValue(each1.GetAttribute("Score"));
}
}
}
else
{
for (int x = 4; x < ColumnNameList.Count; x++)
{
tool.SetCellBro(ws, dataIndex, x, 1, 1);
}
}
//學期成績
if (each.RSR != null)
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 2, 8);
tool.SetCellBro(ws, dataIndex, ColumnNameList.Count - 2, 1, 1);
string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : "";
ws.Cells[dataIndex, ColumnNameList.Count - 2].PutValue(Score);
//ws.Cells[dataIndex, ColumnNameList.Count - 1].Style = sy;
}
//社團幹部
if (each.RSR != null)
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 8);
tool.SetCellBro(ws, dataIndex, ColumnNameList.Count - 1, 1, 1);
string CardreName = each.RSR.CadreName;
ws.Cells[dataIndex, ColumnNameList.Count - 1].PutValue(CardreName);
//ws.Cells[dataIndex, ColumnNameList.Count - 1].Style = sy;
}
dataIndex++;
}
//頁數
string DateName = "日期：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm") + " 頁數:" + CountPage.ToString();
CountPage++;
ws.Cells.Merge(dataIndex, 0, 1, 5);
//SetCellBro(ws, dataIndex, 0, 1, ColumnNameList.Count);
ws.Cells[dataIndex, 0].PutValue(DateName);
//ws.Cells[dataIndex, 0].Style = sy;
ws.HPageBreaks.Add(dataIndex + 1, ColumnNameList.Count);
dataIndex++;
}
#endregion
e.Result = wb;
}
void _BGWAbsenceDetail_DoWork(object sender, DoWorkEventArgs e)
{
string reportName = "學生個人缺曠明細";
#region 快取相關資料
//選擇的學生
List <StudentRecord> selectedStudents = Student.SelectByIDs(K12.Presentation.NLDPanels.Student.SelectedSource);
selectedStudents.Sort(new Comparison <StudentRecord>(CommonMethods.ClassSeatNoComparer));
//紀錄所有學生ID
List <string> allStudentID = new List <string>();
//對照表
Dictionary <string, string> absenceList = new Dictionary <string, string>();
Dictionary <string, string> periodList = new Dictionary <string, string>();
//根據節次類型統計每一種缺曠別的次數
Dictionary <string, Dictionary <string, Dictionary <string, int> > > periodStatisticsByType = new Dictionary <string, Dictionary <string, Dictionary <string, int> > >();
//每一位學生的缺曠明細
Dictionary <string, Dictionary <string, Dictionary <string, string> > > studentAbsenceDetail = new Dictionary <string, Dictionary <string, Dictionary <string, string> > >();
//紀錄每一個節次在報表中的 column index
Dictionary <string, int> columnTable = new Dictionary <string, int>();
List <AttendanceRecord> AttendanceList;
//取得所有學生ID
foreach (StudentRecord var in selectedStudents)
{
allStudentID.Add(var.ID);
}
//取得 Absence List
List <AbsenceMappingInfo> Absencelist = K12.Data.AbsenceMapping.SelectAll();
foreach (AbsenceMappingInfo var in Absencelist)
{
if (!absenceList.ContainsKey(var.Name))
{
absenceList.Add(var.Name, var.Abbreviation);
}
}
//取得 Period List
List <PeriodMappingInfo> PerioList = K12.Data.PeriodMapping.SelectAll();
foreach (PeriodMappingInfo var in PerioList)
{
if (!periodList.ContainsKey(var.Name))
{
periodList.Add(var.Name, var.Type);
}
}
//產生 DSRequest，取得缺曠明細
if (form.SelectDayOrSchoolYear)
{
#region 依日期
//取得學生,開始 & 結束之間的缺曠資料
AttendanceList = Attendance.SelectByDate(Student.SelectByIDs(allStudentID), form.dateTimeInput1.Value, form.dateTimeInput2.Value);
#endregion
}
else
{
if (form.checkBoxX1Bool)
{
#region 全部學期列印
AttendanceList = Attendance.SelectByStudentIDs(allStudentID);
#endregion
}
else
{
#region 指定學期列印
AttendanceList = Attendance.SelectBySchoolYearAndSemester(Student.SelectByIDs(allStudentID), int.Parse(form.SchoolYear), int.Parse(form.Semester));
#endregion
}
}
foreach (AttendanceRecord var in AttendanceList)
{
string studentID = var.RefStudentID;
string schoolYear = var.SchoolYear.ToString();
string semester = var.Semester.ToString();
string occurDate = var.OccurDate.ToShortDateString();
string sso = schoolYear + "_" + semester + "_" + occurDate;
//累計資料
if (!periodStatisticsByType.ContainsKey(studentID))
{
periodStatisticsByType.Add(studentID, new Dictionary <string, Dictionary <string, int> >());
}
foreach (string value in periodList.Values)
{
if (!periodStatisticsByType[studentID].ContainsKey(value))
{
periodStatisticsByType[studentID].Add(value, new Dictionary <string, int>());
}
foreach (string absence in absenceList.Keys)
{
if (!periodStatisticsByType[studentID][value].ContainsKey(absence))
{
periodStatisticsByType[studentID][value].Add(absence, 0);
}
}
}
//每一位學生缺曠資料
if (!studentAbsenceDetail.ContainsKey(studentID))
{
studentAbsenceDetail.Add(studentID, new Dictionary <string, Dictionary <string, string> >());
}
if (!studentAbsenceDetail[studentID].ContainsKey(sso))
{
studentAbsenceDetail[studentID].Add(sso, new Dictionary <string, string>());
}
foreach (AttendancePeriod ap in var.PeriodDetail)
{
string absenceType = ap.AbsenceType;
string inner = ap.Period;
if (!periodList.ContainsKey(inner))
{
continue;
}
string periodType = periodList[inner];
if (!studentAbsenceDetail[studentID][sso].ContainsKey(inner) && absenceList.ContainsKey(absenceType))
{
studentAbsenceDetail[studentID][sso].Add(inner, absenceList[absenceType]);
}
if (periodStatisticsByType[studentID][periodType].ContainsKey(absenceType))
{
periodStatisticsByType[studentID][periodType][absenceType]++;
}
}
}
#endregion
if (AttendanceList.Count == 0)
{
e.Cancel = true;
return;
}
#region 產生範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.學生缺曠明細), FileFormatType.Excel2003);
Range tempEachColumn = template.Worksheets[0].Cells.CreateRange(4, 1, true);
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
int startPage = 1;
int pageNumber = 1;
int colIndex = 4;
int startPeriodIndex = colIndex;
int endPeriodIndex;
//產生每一個節次的欄位
foreach (string periodName in periodList.Keys)
{
ptws.Cells.CreateRange(colIndex, 1, true).Copy(tempEachColumn);
ptws.Cells[3, colIndex].PutValue(periodName);
columnTable.Add(periodName, colIndex);
colIndex++;
}
endPeriodIndex = colIndex;
ptws.Cells.CreateRange(2, startPeriodIndex, 1, endPeriodIndex - startPeriodIndex).Merge();
ptws.Cells[2, startPeriodIndex].PutValue("節次");
//合併標題列
ptws.Cells.CreateRange(0, 0, 1, endPeriodIndex).Merge();
ptws.Cells.CreateRange(1, 0, 1, endPeriodIndex).Merge();
Range ptHeader = ptws.Cells.CreateRange(0, 4, false);
Range ptEachRow = ptws.Cells.CreateRange(4, 1, false);
//current++;
#endregion
#region 產生報表
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
int index = 0;
int dataIndex = 0;
int studentCount = 1;
foreach (StudentRecord studentInfo in selectedStudents)
{
string TitleName1 = School.ChineseName + " 個人缺曠明細";
string TitleName2 = "班級：" + ((studentInfo.Class == null ? " " : studentInfo.Class.Name) + " 座號：" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名：" + studentInfo.Name + " 學號：" + studentInfo.StudentNumber);
//回報進度
_BGWAbsenceDetail.ReportProgress((int)(((double)studentCount++ *100.0) / (double)selectedStudents.Count));
if (!studentAbsenceDetail.ContainsKey(studentInfo.ID))
{
continue;
}
//如果不是第一頁，就在上一頁的資料列下邊加黑線
if (index != 0)
{
ws.Cells.CreateRange(index - 1, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
//複製 Header
ws.Cells.CreateRange(index, 4, false).Copy(ptHeader);
//填寫基本資料
ws.Cells[index, 0].PutValue(School.ChineseName + " 個人缺曠明細");
ws.Cells[index + 1, 0].PutValue("班級：" + ((studentInfo.Class == null ? " " : studentInfo.Class.Name) + " 座號：" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名：" + studentInfo.Name + " 學號：" + studentInfo.StudentNumber));
dataIndex = index + 4;
int recordCount = 0;
//學生Row筆數超過40筆,則添加換行符號,與標頭
int CountRows = 0;
Dictionary <string, Dictionary <string, string> > absenceDetail = studentAbsenceDetail[studentInfo.ID];
//取總頁數 , 資料數除以38列(70/38=2)
int TotlePage = absenceDetail.Count / 40;
//目前頁數
int pageCount = 1;
//如果還有餘數則+1
if (absenceDetail.Count % 40 != 0)
{
TotlePage++;
}
//填寫基本資料
ws.Cells[index, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")");
pageCount++;
ws.Cells[index + 1, 0].PutValue(TitleName2);
foreach (string sso in absenceDetail.Keys)
{
CountRows++;
string[] ssoSplit = sso.Split(new string[] { "_" }, StringSplitOptions.RemoveEmptyEntries);
//複製每一個 row
ws.Cells.CreateRange(dataIndex, 1, false).Copy(ptEachRow);
//填寫學生缺曠資料
ws.Cells[dataIndex, 0].PutValue(ssoSplit[0]);
ws.Cells[dataIndex, 1].PutValue(ssoSplit[1]);
ws.Cells[dataIndex, 2].PutValue(ssoSplit[2]);
ws.Cells[dataIndex, 3].PutValue(CommonMethods.GetChineseDayOfWeek(DateTime.Parse(ssoSplit[2])));
Dictionary <string, string> record = absenceDetail[sso];
foreach (string periodName in record.Keys)
{
ws.Cells[dataIndex, columnTable[periodName]].PutValue(record[periodName]);
}
dataIndex++;
recordCount++;
if (CountRows == 40 && pageCount <= TotlePage)
{
CountRows = 0;
//分頁
ws.HPageBreaks.Add(dataIndex, endPeriodIndex);
//複製 Header
ws.Cells.CreateRange(dataIndex, 4, false).Copy(ptHeader);
//填寫基本資料
ws.Cells[dataIndex, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")");
pageCount++; //下一頁使用
ws.Cells[dataIndex + 1, 0].PutValue(TitleName2);
dataIndex += 4;
}
}
//缺曠統計資訊
Range absenceStatisticsRange = ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex);
absenceStatisticsRange.Merge();
absenceStatisticsRange.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black);
absenceStatisticsRange.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black);
absenceStatisticsRange.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
absenceStatisticsRange.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
absenceStatisticsRange.RowHeight = 20.0;
ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.Font.Size = 10;
ws.Cells[dataIndex, 0].PutValue("缺曠總計");
dataIndex++;
int typeNumber = dataIndex;
foreach (string periodType in periodStatisticsByType[studentInfo.ID].Keys)
{
Dictionary <string, int> byType = periodStatisticsByType[studentInfo.ID][periodType];
int printable = 0;
foreach (string type in byType.Keys)
{
printable += byType[type];
}
if (printable == 0)
{
continue;
}
ws.Cells.CreateRange(dataIndex, 0, 1, 2).Merge();
ws.Cells.CreateRange(dataIndex, 2, 1, endPeriodIndex - 2).Merge();
ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center;
ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex).RowHeight = 27.0;
ws.Cells.CreateRange(dataIndex, 0, 1, 2).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
ws.Cells.CreateRange(dataIndex, 0, 1, 2).SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
ws.Cells[dataIndex, 0].Style.Font.Size = 10;
ws.Cells[dataIndex, 0].PutValue(periodType);
StringBuilder text = new StringBuilder("");
foreach (string type in byType.Keys)
{
if (byType[type] > 0)
{
if (text.ToString() != "")
{
text.Append(" ");
}
text.Append(type + "：" + byType[type]);
}
}
ws.Cells[dataIndex, 2].Style.Font.Size = 10;
ws.Cells[dataIndex, 2].Style.ShrinkToFit = true;
ws.Cells[dataIndex, 2].PutValue(text.ToString());
ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
dataIndex++;
}
typeNumber = dataIndex - typeNumber;
//資料列上邊加上黑線
//ws.Cells.CreateRange(index + 3, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
//表格最右邊加上黑線
//ws.Cells.CreateRange(index + 2, endPeriodIndex - 1, recordCount + typeNumber + 3, 1).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
index = dataIndex;
//每500頁,增加一個WorkSheet,並於下標顯示(1~500)(501~xxx)
if (pageNumber < 500)
{
ws.HPageBreaks.Add(index, endPeriodIndex);
pageNumber++;
}
else
{
ws.Name = startPage + " ~ " + (pageNumber + startPage - 1);
ws = wb.Worksheets[wb.Worksheets.Add()];
ws.Copy(prototype.Worksheets[0]);
startPage += pageNumber;
pageNumber = 1;
index = 0;
}
}
if (dataIndex > 0)
{
//最後一頁的資料列下邊加上黑線
ws.Cells.CreateRange(dataIndex - 1, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
ws.Name = startPage + " ~ " + (pageNumber + startPage - 2);
}
else
{
wb = new Workbook();
}
#endregion
string path = Path.Combine(Application.StartupPath, "Reports");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = Path.Combine(path, reportName + ".xlt");
e.Result = new object[] { reportName, path, wb };
}
void _BWResitScoreImport_DoWork(object sender, DoWorkEventArgs e)
{
object[] objectValue = (object[])e.Argument;
int schoolyear = (int)objectValue[0];
int semester = (int)objectValue[1];
_BWResitScoreImport.ReportProgress(0);
#region 取得所有學生以及補考資訊
AccessHelper helper = new AccessHelper();
List <StudentRecord> allStudents = new List <StudentRecord>();
List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass();
WearyDogComputer computer = new WearyDogComputer();
double currentClass = 1;
double totalClasses = allClasses.Count;
List <StudentRecord> loadCourseStudents = new List <StudentRecord>();
foreach (ClassRecord aClass in allClasses)
{
List <StudentRecord> classStudents = aClass.Students;
computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents);
helper.StudentHelper.FillField("及格標準", classStudents);
allStudents.AddRange(classStudents);
foreach (StudentRecord aStudent in classStudents)
{
foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList)
{
if (info.SchoolYear == schoolyear && info.Semester == semester)
{
if (!info.Pass && info.Detail.HasAttribute("達補考標準") && info.Detail.GetAttribute("達補考標準") == "是")
{
if (!loadCourseStudents.Contains(aStudent))
{
loadCourseStudents.Add(aStudent);
}
}
}
}
}
_BWResitScoreImport.ReportProgress((int)(currentClass++ *90.0 / totalClasses));
}
MultiThreadWorker <StudentRecord> multiThreadWorker = new MultiThreadWorker <StudentRecord>();
multiThreadWorker.MaxThreads = 3;
multiThreadWorker.PackageSize = 80;
multiThreadWorker.PackageWorker += new EventHandler <PackageWorkEventArgs <StudentRecord> >(multiThreadWorker_PackageWorker);
multiThreadWorker.Run(loadCourseStudents, new object[] { schoolyear, semester, helper });
double currentStudent = 1;
double totalStudents = allStudents.Count;
#endregion
#region 產生表格並填入資料
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.補考成績匯入), FileFormatType.Excel2003);
Workbook wb = new Workbook();
wb.Copy(template);
Worksheet ws = wb.Worksheets[0];
Dictionary <string, int> columnIndexTable = new Dictionary <string, int>();
int headerIndex = 0;
string headerString = template.Worksheets[0].Cells[0, headerIndex].StringValue;
while (!string.IsNullOrEmpty(headerString))
{
columnIndexTable.Add(headerString, headerIndex);
headerString = template.Worksheets[0].Cells[0, ++headerIndex].StringValue;
}
foreach (StudentRecord aStudent in loadCourseStudents)
{
foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList)
{
if (info.SchoolYear == schoolyear && info.Semester == semester)
{
if (!info.Pass && info.Detail.HasAttribute("達補考標準") && info.Detail.GetAttribute("達補考標準") == "是")
{
#region 搜尋授課教師
foreach (StudentAttendCourseRecord attendCourse in aStudent.AttendCourseList)
{
if (attendCourse.SchoolYear == schoolyear && attendCourse.Semester == semester && attendCourse.Subject == info.Subject && attendCourse.SubjectLevel == info.Level)
{
List <TeacherRecord> teachers = helper.TeacherHelper.GetLectureTeacher(attendCourse);
if (teachers.Count > 0)
{
info.Detail.SetAttribute("授課教師", teachers[0].TeacherName);
}
}
}
#endregion
machine.AddSubject(aStudent, info);
}
}
}
_BWResitScoreImport.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents));
}
machine.Sort();
int index = 1;
foreach (Dictionary <string, string> info in machine.GetSubjects())
{
foreach (string key in info.Keys)
{
ws.Cells[index, columnIndexTable[key]].PutValue(info[key]);
}
index++;
}
#endregion
e.Result = wb;
}
private void printBtn_Click(object sender, EventArgs e)
{
#region SQL
string sql = "";
if (unitCbx.Text == "--全部--")
{
sql = string.Format(@"
WITH data_row AS(
SELECT
unit.name AS unit_name
, room.name AS room_name
, room.uid AS room_id
, app.uid
, app_detail.start_time
, app_detail.end_time
FROM
$ischool.booking.meetingroom_unit AS unit
LEFT OUTER JOIN $ischool.booking.meetingroom AS room
ON unit.uid = room.ref_unit_id
LEFT OUTER JOIN $ischool.booking.meetingroom_application AS app
ON room.uid = app.ref_meetingroom_id
LEFT OUTER JOIN $ischool.booking.meetingroom_application_detail AS app_detail
ON app.uid = app_detail.ref_application_id
WHERE
room.uid IS NOT NULL
AND app.uid IS NOT NULL
AND app.is_canceled = false
AND app.is_approved = true
AND app_detail.start_time >= '{0}'
AND app_detail.start_time <= '{1}'
)
SELECT
unit_name
, room_name
, count(*) AS 使用次數
, SUM(end_time - start_time) AS 使用時數
FROM
data_row
GROUP BY
unit_name
,room_name
", startTime.Value.ToShortDateString(), endTime.Value.ToShortDateString());
}
else
{
string unitID = unitDic[unitCbx.Text];
sql = string.Format(@"
WITH data_row AS(
SELECT
unit.name AS unit_name
, room.name AS room_name
, room.uid AS room_id
, app.uid
, app_detail.start_time
, app_detail.end_time
FROM
$ischool.booking.meetingroom_unit AS unit
LEFT OUTER JOIN $ischool.booking.meetingroom AS room
ON unit.uid = room.ref_unit_id
LEFT OUTER JOIN $ischool.booking.meetingroom_application AS app
ON room.uid = app.ref_meetingroom_id
LEFT OUTER JOIN $ischool.booking.meetingroom_application_detail AS app_detail
ON app.uid = app_detail.ref_application_id
WHERE
room.uid IS NOT NULL
AND app.uid IS NOT NULL
AND app.is_canceled = false
AND app.is_approved = true
AND unit.uid = {2}
AND app_detail.start_time >= '{0}'
AND app_detail.start_time <= '{1}'
)
SELECT
unit_name
, room_name
, count(*) AS 使用次數
, SUM(end_time - start_time) AS 使用時數
FROM
data_row
GROUP BY
unit_name
,room_name
", startTime.Value.ToShortDateString(), endTime.Value.ToShortDateString(), unitID);
}
#endregion
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(sql);
Workbook book = new Workbook();
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.統計會議室報表樣板), FileFormatType.Excel2003);
book.Copy(template);
Worksheet sheet = book.Worksheets[0];
Style style = sheet.Cells[2, 0].Style;
#region 寫入資料
int rowIndex = 3;
sheet.Cells[0, 1].PutValue(startTime.Value.ToShortDateString() + " ~ " + endTime.Value.ToShortDateString());
foreach (DataRow row in dt.Rows)
{
int colIndex = 0;
sheet.Cells[rowIndex, colIndex].PutValue("" + row["unit_name"]);
sheet.Cells[rowIndex, colIndex++].Style = style;
sheet.Cells[rowIndex, colIndex].PutValue("" + row["room_name"]);
sheet.Cells[rowIndex, colIndex++].Style = style;
sheet.Cells[rowIndex, colIndex].PutValue("" + row["使用次數"]);
sheet.Cells[rowIndex, colIndex++].Style = style;
string[] times = ("" + row["使用時數"]).Split(':');
sheet.Cells[rowIndex, colIndex].PutValue(times[0] /*DateTime.Parse("" + row["使用時數"]).ToString("hh")*/);
sheet.Cells[rowIndex, colIndex++].Style = style;
}
#endregion
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "Excel (*.xls)|*.xls|所有檔案 (*.*)|*.*";
string fileName = "會議室統計報表";
saveFile.FileName = fileName;
try
{
if (saveFile.ShowDialog() == DialogResult.OK)
{
book.Save(saveFile.FileName);
MsgBox.Show("會議室統計報表列印完成!");
this.Close();
Process.Start(saveFile.FileName);
}
else
{
FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存");
return;
}
}
catch
{
MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!");
}
}
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
}
private void btnPrint_Click(object sender, EventArgs e)
{
DataTable dt;
if (cbxUnit.Text == "--全部--")
{
dt = DAO.StatisticalReportDAO.GetReportData("--全部--", dtStar.Value.ToShortDateString(), dtEnd.Value.ToShortDateString());
}
else
{
string unitID = dicUnitNameID[cbxUnit.Text];
dt = DAO.StatisticalReportDAO.GetReportData(unitID, dtStar.Value.ToShortDateString(), dtEnd.Value.ToShortDateString());
}
// 列印樣板設定
Workbook book = new Workbook();
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.統計設備報表樣板), FileFormatType.Excel2003);
book.Copy(template);
Worksheet sheet = book.Worksheets[0];
Style style = sheet.Cells[2, 0].Style;
#region 寫入資料
int rowIndex = 3;
sheet.Cells[0, 1].PutValue(dtStar.Value.ToShortDateString() + " ~ " + dtEnd.Value.ToShortDateString());
foreach (DataRow row in dt.Rows)
{
int colIndex = 0;
sheet.Cells[rowIndex, colIndex].PutValue("" + row["unit_name"]);
sheet.Cells[rowIndex, colIndex++].Style = style;
sheet.Cells[rowIndex, colIndex].PutValue("" + row["equip_name"]);
sheet.Cells[rowIndex, colIndex++].Style = style;
sheet.Cells[rowIndex, colIndex].PutValue("" + row["使用次數"]);
sheet.Cells[rowIndex, colIndex++].Style = style;
sheet.Cells[rowIndex, colIndex].PutValue(DateTime.Parse("" + row["使用時數"]).ToString("hh"));
sheet.Cells[rowIndex, colIndex++].Style = style;
}
#endregion
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "Excel (*.xls)|*.xls|所有檔案 (*.*)|*.*";
string fileName = "設備使用狀況統計報表";
saveFile.FileName = fileName;
try
{
if (saveFile.ShowDialog() == DialogResult.OK)
{
book.Save(saveFile.FileName);
MsgBox.Show("設備使用狀況統計報表列印完成!");
this.Close();
Process.Start(saveFile.FileName);
}
else
{
FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存");
return;
}
}
catch
{
MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!");
}
}
void BGW_DoWork(object sender, DoWorkEventArgs e)
{
//建立社團比對學生ID的清單
//以每個社團為一個單位
//進行學生資料列印
SDL = new SCJoinDataLoad();
//建立Excel範本
Workbook template = new Workbook();
//Jean Open
template.Open(new MemoryStream(Properties.Resources.社團點名單_範本), FileFormatType.Excel97To2003);
//每一張
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
//範圍
Range ptHeader = ptws.Cells.CreateRange(0, 4, false);
Range ptEachRow = ptws.Cells.CreateRange(4, 1, false);
//儲存資料
Workbook wb = new Workbook();
wb.Copy(prototype);
//取得Sheet
Worksheet ws = wb.Worksheets[0];
int index = 0;
int dataIndex = 0;
//每一個社團
foreach (string club in SDL.ClubByStudentList.Keys)
{
//社團資訊收集
CLUBRecord cr = SDL.CLUBRecordDic[club];
//社團標頭
string TitleName1 = string.Format("{0}學年度／第{1}學期 社團點名單", cr.SchoolYear.ToString(), cr.Semester.ToString());
string TitleName2 = cr.ClubName + " (類型：" + cr.ClubCategory + ")";
ws.Cells.CreateRange(dataIndex, 4, false).CopyStyle(ptHeader);
ws.Cells.CreateRange(dataIndex, 4, false).CopyValue(ptHeader);
ws.Cells[dataIndex, 0].PutValue(TitleName1);
dataIndex += 1;
ws.Cells[dataIndex, 0].PutValue(TitleName2);
dataIndex += 1;
ws.Cells[dataIndex, 0].PutValue("代碼：" + cr.ClubNumber);
ws.Cells[dataIndex, 1].PutValue("場地：" + cr.Location);
ws.Cells[dataIndex, 3].PutValue("人數：" + SDL.ClubByStudentList[club].Count);
//社團老師
string TeacherNameA = GetTeacherName(cr.RefTeacherID);
string TeacherNameB = GetTeacherName(cr.RefTeacherID2);
string TeacherNameC = GetTeacherName(cr.RefTeacherID3);
string TeacherName = "老師：" + TeacherNameA + " " + TeacherNameB + " " + TeacherNameC;
ws.Cells[dataIndex, 4].PutValue(TeacherName);
dataIndex += 2;
foreach (StudentRecord stud in SDL.ClubByStudentList[club])
{
ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow);
ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow);
string classname = string.IsNullOrEmpty(stud.RefClassID) ? "" : stud.Class.Name;
ws.Cells[dataIndex, 0].PutValue(classname);
ws.Cells[dataIndex, 1].PutValue(stud.SeatNo.HasValue ? stud.SeatNo.Value.ToString() : "");
ws.Cells[dataIndex, 2].PutValue(stud.Name);
ws.Cells[dataIndex, 3].PutValue(stud.StudentNumber);
ws.Cells[dataIndex, 4].PutValue(stud.Gender);
dataIndex += 1;
}
ws.Cells.CreateRange(dataIndex - 1, 0, 1, 6).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
ws.HPageBreaks.Add(dataIndex, 6);
}
e.Result = wb;
}
void _BGWDisciplineDetail_DoWork(object sender, DoWorkEventArgs e)
{
string reportName = "學生獎勵懲戒記錄明細";
#region 快取相關資料
//選擇的學生
List <StudentRecord> selectedStudents = Student.SelectByIDs(K12.Presentation.NLDPanels.Student.SelectedSource);
selectedStudents.Sort(new Comparison <StudentRecord>(CommonMethods.ClassSeatNoComparer));
//紀錄所有學生ID
List <string> allStudentID = new List <string>();
//每一位學生的獎懲明細(學生id<字串組合,物件>)
Dictionary <string, Dictionary <string, DisciplineRecord> > studentDisciplineDetail = new Dictionary <string, Dictionary <string, DisciplineRecord> >();
//每一位學生的獎懲累計資料(學生id,特殊物件)
Dictionary <string, DisciplineStatistics> studentDisciplineStatistics = new Dictionary <string, DisciplineStatistics>();
//紀錄每一種獎懲在報表中的 column index
Dictionary <string, int> columnTable = new Dictionary <string, int>();
List <DisciplineRecord> DisciplineList = new List <DisciplineRecord>();
//取得所有學生ID
foreach (StudentRecord var in selectedStudents)
{
allStudentID.Add(var.ID);
}
//初始化
string[] columnString;
if (form.checkBoxX2Bool) //使用者已勾選"排除懲戒已銷過資料"
{
columnString = new string[] { "大功", "小功", "嘉獎", "大過", "小過", "警告", "留察", "登錄日期", "事由" }
}
;
else
{
columnString = new string[] { "大功", "小功", "嘉獎", "大過", "小過", "警告", "留察", "銷過", "銷過日期", "登錄日期", "事由" }
};
int i = 4;
foreach (string s in columnString)
{
columnTable.Add(s, i++);
}
#region 取得獎勵懲戒明細
if (form.SelectDayOrSchoolYear) //依日期
{
if (form.SetupTime) //依發生日期
{
DisciplineList = Discipline.SelectByOccurDate(allStudentID, form.dateTimeInput1.Value, form.dateTimeInput2.Value);
}
else //依登錄日期
{
DisciplineList = Discipline.SelectByRegisterDate(allStudentID, form.dateTimeInput1.Value, form.dateTimeInput2.Value);
}
}
else //依學期
{
if (form.checkBoxX1Bool) //全部學期列印
{
#region 全部學期列印
DisciplineList = Discipline.SelectByStudentIDs(allStudentID);
#endregion
}
else //指定學期列印
{
#region 指定學期列印
foreach (DisciplineRecord each in Discipline.SelectByStudentIDs(allStudentID))
{
if (each.SchoolYear.ToString() == form.cbSchoolYear.Text && each.Semester.ToString() == form.cbSemester.Text)
{
DisciplineList.Add(each);
}
}
#endregion
}
}
#endregion
if (form.checkBoxX2Bool) //使用者已勾選"排除懲戒已銷過資料"
{
IsOrRemoveData(DisciplineList);
}
if (DisciplineList.Count == 0)
{
e.Cancel = true;
return;
}
foreach (DisciplineRecord each in DisciplineList)
{
string studentID = each.RefStudentID;
string schoolYear = each.SchoolYear.ToString();
string semester = each.Semester.ToString();
string occurDate = each.OccurDate.ToShortDateString();
string reason = each.Reason;
string disciplineID = each.ID;
string sso = schoolYear + "_" + semester + "_" + occurDate + "_" + disciplineID;
//初始化累計資料
if (!studentDisciplineStatistics.ContainsKey(studentID))
{
studentDisciplineStatistics.Add(studentID, new DisciplineStatistics(studentID));
}
//每一位學生獎勵懲戒資料
if (!studentDisciplineDetail.ContainsKey(studentID))
{
studentDisciplineDetail.Add(studentID, new Dictionary <string, DisciplineRecord>());
}
if (!studentDisciplineDetail[studentID].ContainsKey(sso))
{
studentDisciplineDetail[studentID].Add(sso, each);
}
if (each.MeritFlag == "1")
{
studentDisciplineStatistics[studentID].大功 += each.MeritA.HasValue ? each.MeritA.Value : 0;
studentDisciplineStatistics[studentID].小功 += each.MeritB.HasValue ? each.MeritB.Value : 0;
studentDisciplineStatistics[studentID].獎 += each.MeritC.HasValue ? each.MeritC.Value : 0;
}
else if (each.MeritFlag == "0")
{
if (each.Cleared != "是")
{
studentDisciplineStatistics[studentID].大過 += each.DemeritA.HasValue ? each.DemeritA.Value : 0;
studentDisciplineStatistics[studentID].小過 += each.DemeritB.HasValue ? each.DemeritB.Value : 0;
studentDisciplineStatistics[studentID].警告 += each.DemeritC.HasValue ? each.DemeritC.Value : 0;
}
}
else if (each.MeritFlag == "2") //留察
{
}
}
#endregion
#region 產生範本
Workbook template = new Workbook();
if (form.checkBoxX2Bool)
{
template.Open(new MemoryStream(K12.學生獎懲明細.Properties.Resources.學生獎懲明細_2), FileFormatType.Excel2003);
}
else
{
template.Open(new MemoryStream(K12.學生獎懲明細.Properties.Resources.學生獎懲明細), FileFormatType.Excel2003);
}
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
int startPage = 1;
int pageNumber = 1;
int columnNumber = 15;
if (form.checkBoxX2Bool)
{
columnNumber = 13;
}
//合併標題列
ptws.Cells.CreateRange(0, 0, 1, columnNumber).Merge();
ptws.Cells.CreateRange(1, 0, 1, columnNumber).Merge();
Range ptHeader = ptws.Cells.CreateRange(0, 4, false);
Range ptEachRow = ptws.Cells.CreateRange(4, 1, false);
#endregion
#region 產生報表
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
int index = 0;
int dataIndex = 0;
int studentCount = 1;
foreach (StudentRecord studentInfo in selectedStudents)
{
string TitleName1 = School.ChineseName + " 個人獎勵懲戒明細";
string TitleName2 = "班級：" + ((studentInfo.Class == null ? " " : studentInfo.Class.Name) + " 座號：" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名：" + studentInfo.Name + " 學號：" + studentInfo.StudentNumber);
//回報進度
_BGWDisciplineDetail.ReportProgress((int)(((double)studentCount++ *100.0) / (double)selectedStudents.Count));
if (!studentDisciplineDetail.ContainsKey(studentInfo.ID))
{
continue;
}
//如果不是第一頁，就在上一頁的資料列下邊加黑線
if (index != 0)
{
ws.Cells.CreateRange(index - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
//複製 Header
ws.Cells.CreateRange(index, 4, false).Copy(ptHeader);
dataIndex = index + 4;
int recordCount = 0;
//學生Row筆數超過40筆,則添加換行符號,與標頭
int CountRows = 0;
Dictionary <string, DisciplineRecord> disciplineDetail = studentDisciplineDetail[studentInfo.ID];
//取總頁數 , 資料數除以38列(70/38=2)
int TotlePage = disciplineDetail.Count / 40;
//目前頁數
int pageCount = 1;
//如果還有餘數則+1
if (disciplineDetail.Count % 40 != 0)
{
TotlePage++;
}
//填寫基本資料
ws.Cells[index, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")");
pageCount++;
ws.Cells[index + 1, 0].PutValue(TitleName2);
foreach (string sso in disciplineDetail.Keys)
{
CountRows++;
string[] ssoSplit = sso.Split(new string[] { "_" }, StringSplitOptions.RemoveEmptyEntries);
//複製每一個 row
ws.Cells.CreateRange(dataIndex, 1, false).Copy(ptEachRow);
//填寫學生獎懲資料
ws.Cells[dataIndex, 0].PutValue(ssoSplit[0]);
ws.Cells[dataIndex, 1].PutValue(ssoSplit[1]);
ws.Cells[dataIndex, 2].PutValue(ssoSplit[2]);
ws.Cells[dataIndex, 3].PutValue(CommonMethods.GetChineseDayOfWeek(DateTime.Parse(ssoSplit[2])));
DisciplineRecord record = disciplineDetail[sso];
if (record.MeritFlag == "1")
{
ws.Cells[dataIndex, columnTable["大功"]].PutValue(record.MeritA);
ws.Cells[dataIndex, columnTable["小功"]].PutValue(record.MeritB);
ws.Cells[dataIndex, columnTable["嘉獎"]].PutValue(record.MeritC);
}
else if (record.MeritFlag == "0")
{
if (record.Cleared == "是")
{
ws.Cells[dataIndex, columnTable["大過"]].PutValue(record.DemeritA);
ws.Cells[dataIndex, columnTable["小過"]].PutValue(record.DemeritB);
ws.Cells[dataIndex, columnTable["警告"]].PutValue(record.DemeritC);
if (!form.checkBoxX2Bool)
{
ws.Cells[dataIndex, columnTable["銷過"]].PutValue(record.Cleared);
ws.Cells[dataIndex, columnTable["銷過日期"]].PutValue(record.ClearDate.HasValue ? record.ClearDate.Value.ToShortDateString() : "");
}
}
else
{
ws.Cells[dataIndex, columnTable["大過"]].PutValue(record.DemeritA);
ws.Cells[dataIndex, columnTable["小過"]].PutValue(record.DemeritB);
ws.Cells[dataIndex, columnTable["警告"]].PutValue(record.DemeritC);
}
}
else if (record.MeritFlag == "2")
{
ws.Cells[dataIndex, columnTable["留察"]].PutValue("是");
}
ws.Cells[dataIndex, columnTable["事由"]].PutValue(record.Reason);
ws.Cells[dataIndex, columnTable["登錄日期"]].PutValue(record.RegisterDate.HasValue ? record.RegisterDate.Value.ToShortDateString() : "");
dataIndex++;
recordCount++;
if (CountRows == 40 && pageCount <= TotlePage)
{
CountRows = 0;
//分頁
ws.HPageBreaks.Add(dataIndex, columnNumber);
//複製 Header
ws.Cells.CreateRange(dataIndex, 4, false).Copy(ptHeader);
//填寫基本資料
ws.Cells[dataIndex, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")");
pageCount++; //下一頁使用
ws.Cells[dataIndex + 1, 0].PutValue(TitleName2);
dataIndex += 4;
}
}
//獎懲統計資訊
Range disciplineStatisticsRange = ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber);
disciplineStatisticsRange.Copy(ptEachRow);
disciplineStatisticsRange.Merge();
disciplineStatisticsRange.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black);
disciplineStatisticsRange.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black);
disciplineStatisticsRange.RowHeight = 20.0;
ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.Font.Size = 10;
ws.Cells[dataIndex, 0].PutValue("獎勵懲戒總計");
dataIndex++;
//獎懲統計內容
ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Copy(ptEachRow);
ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).RowHeight = 27.0;
ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Merge();
ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.Font.Size = 10;
ws.Cells[dataIndex, 0].Style.ShrinkToFit = true;
StringBuilder text = new StringBuilder("");
DisciplineStatistics disciplineStatistics = studentDisciplineStatistics[studentInfo.ID];
List <string> list = new List <string>();
if (disciplineStatistics.大功 > 0)
{
list.Add("大功：" + disciplineStatistics.大功);
}
if (disciplineStatistics.小功 > 0)
{
list.Add("小功：" + disciplineStatistics.小功);
}
if (disciplineStatistics.獎 > 0)
{
list.Add("嘉獎：" + disciplineStatistics.獎);
}
if (disciplineStatistics.大過 > 0)
{
list.Add("大過：" + disciplineStatistics.大過);
}
if (disciplineStatistics.小過 > 0)
{
list.Add("小過：" + disciplineStatistics.小過);
}
if (disciplineStatistics.警告 > 0)
{
list.Add("警告：" + disciplineStatistics.警告);
}
text.Append(string.Join(" ", list.ToArray()));
ws.Cells[dataIndex, 0].PutValue(text.ToString());
ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
dataIndex++;
//資料列上邊加上黑線
//ws.Cells.CreateRange(index + 3, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
//表格最右邊加上黑線
//ws.Cells.CreateRange(index + 2, columnNumber - 1, recordCount + 4, 1).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
index = dataIndex;
//每500頁,增加一個WorkSheet,並於下標顯示(1~500)(501~xxx)
if (pageNumber < 500)
{
ws.HPageBreaks.Add(index, columnNumber);
pageNumber++;
}
else
{
ws.Name = startPage + " ~ " + (pageNumber + startPage - 1);
ws = wb.Worksheets[wb.Worksheets.Add()];
ws.Copy(prototype.Worksheets[0]);
startPage += pageNumber;
pageNumber = 1;
index = 0;
}
}
if (dataIndex > 0)
{
//最後一頁的資料列下邊加上黑線
ws.Cells.CreateRange(dataIndex - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
ws.Name = startPage + " ~ " + (pageNumber + startPage - 2);
}
else
{
wb = new Workbook();
}
#endregion
string path = Path.Combine(Application.StartupPath, "Reports");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = Path.Combine(path, reportName + ".xlt");
e.Result = new object[] { reportName, path, wb };
}
void BGW_DoWork(object sender, DoWorkEventArgs e)
{
ClassClubTraMag mag = new ClassClubTraMag(_SchoolYear, _Semester, PrintLost);
//依據使用者所選擇的學年期
//取得相關學生之社團結算成績
//列印不及格學生清單時,排除所有學期成績60分(含)以上之學生
//取得範本
#region 建立範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.班級社團成績單_範本), FileFormatType.Excel97To2003);
if (PrintLost) //不及格確認單
{
PriontName = "班級社團成績不及格(確認單)";
}
else
{
PriontName = "班級社團成績單";
}
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
#region 建立標頭Column
評量比例 評 = new 評量比例();
if (評._wp == null)
{
e.Cancel = true;
return;
}
ColumnNameList = new List <string>();
ColumnNameList.Add("座號");
ColumnNameList.Add("姓名");
ColumnNameList.Add("學號");
ColumnNameList.Add("性別");
ColumnNameList.Add("社團");
foreach (string each in 評.ColumnDic.Keys)
{
ColumnNameList.Add(each + "(" + 評.ProportionDic[each] + "%)");
}
ColumnNameList.Add("學期成績");
if (PrintLost) //不及格確認單
{
ColumnNameList.Add("簽名");
}
int ColumnNameIndex = 0;
//Jean Aspose更新
Style style = prototype.CreateStyle();
style.IsTextWrapped = true;
foreach (string each in ColumnNameList)
{
ptws.Cells[2, ColumnNameIndex].SetStyle(style);
ptws.Cells[2, ColumnNameIndex].PutValue(each);
if (ColumnNameIndex >= 5)
{
ptws.Cells.SetColumnWidth(ColumnNameIndex, 10);
tool.SetCellBro(ptws, 2, ColumnNameIndex, 1, 1);
}
ColumnNameIndex++;
}
#endregion
Range ptHeader = ptws.Cells.CreateRange(0, 3, false);
Range ptEachRow = ptws.Cells.CreateRange(3, 1, false);
//建立Excel檔案
Workbook wb = new Workbook();
wb.Copy(prototype);
//取得第一張
Worksheet ws = wb.Worksheets[0];
int dataIndex = 0;
int CountPage = 1;
int DetalIndex = 5;
#endregion
#region 填資料
foreach (string classID in mag.TraDic.Keys)
{
if (mag.TraDic[classID].Count == 0)
{
continue;
}
ws.Cells.CreateRange(dataIndex, 3, false).CopyStyle(ptHeader);
ws.Cells.CreateRange(dataIndex, 3, false).CopyValue(ptHeader);
ClassRecord cr = mag.ClassDic[classID];
ws.Cells.Merge(dataIndex, 0, 1, ColumnNameList.Count);
string TitleName = string.Format("{0}學年度 第{1}學期 {2}", _SchoolYear.ToString(), _Semester.ToString(), PriontName);
ws.Cells[dataIndex, 0].PutValue(TitleName);
dataIndex++;
//班級
ws.Cells.Merge(dataIndex, 0, 1, 3);
ws.Cells[dataIndex, 0].PutValue(string.Format("班級：{0}", cr.Name));
ws.Cells.Merge(dataIndex, 4, 1, 3);
//教師
if (!string.IsNullOrEmpty(cr.RefTeacherID))
{
#region 教師
if (mag.TeacherDic.ContainsKey(cr.RefTeacherID))
{
TeacherRecord tr = mag.TeacherDic[cr.RefTeacherID];
//是否有暱稱
if (!string.IsNullOrEmpty(tr.Nickname))
{
string TeacherString = "班導師：" + tr.Name + "(" + tr.Nickname + ")";
ws.Cells[dataIndex, 4].PutValue(TeacherString);
}
else
{
string TeacherString = "班導師：" + mag.TeacherDic[cr.RefTeacherID].Name;
ws.Cells[dataIndex, 4].PutValue(TeacherString);
}
}
#endregion
}
//頁數
ws.Cells.Merge(dataIndex, ColumnNameList.Count - 3, 1, 3);
ws.Cells[dataIndex, ColumnNameList.Count - 3].PutValue("日期：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm") + " 頁數:" + CountPage.ToString());
dataIndex += 2;
mag.TraDic[classID].Sort(SortTraDic);
foreach (ClassClubTraObj each in mag.TraDic[classID])
{
ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow);
ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow);
ws.Cells[dataIndex, 0].PutValue(each.studentRecord.SeatNo.HasValue ? each.studentRecord.SeatNo.Value.ToString() : "");
ws.Cells[dataIndex, 1].PutValue(each.studentRecord.Name);
ws.Cells[dataIndex, 2].PutValue(each.studentRecord.StudentNumber);
ws.Cells[dataIndex, 3].PutValue(each.studentRecord.Gender);
//社團
if (each.club != null)
{
ws.Cells[dataIndex, 4].PutValue(each.club.ClubName);
}
if (each.SCJoin != null)
{
if (!string.IsNullOrEmpty(each.SCJoin.Score))
{
int x = 0;
XmlElement xml = DSXmlHelper.LoadXml(each.SCJoin.Score);
foreach (XmlElement each1 in xml.SelectNodes("Item"))
{
x++;
string name = each1.GetAttribute("Name");
if (評.ColumnDic.ContainsKey(name))
{
ws.Cells[dataIndex, DetalIndex + 評.ColumnDic[name]].PutValue(each1.GetAttribute("Score"));
}
}
}
}
for (int x = 3; x < ColumnNameList.Count; x++)
{
tool.SetCellBro(ws, dataIndex, x, 1, 1);
}
//學期成績
if (PrintLost) //不及格確認單
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 14);
if (each.RSR != null)
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 2, 8);
string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : "";
ws.Cells[dataIndex, ColumnNameList.Count - 2].PutValue(Score);
}
else
{
}
}
else
{
if (each.RSR != null) //有學期成績
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 8);
string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : "";
ws.Cells[dataIndex, ColumnNameList.Count - 1].PutValue(Score);
}
else
{
}
}
dataIndex++;
}
CountPage++; //每班增加1頁
ws.HPageBreaks.Add(dataIndex, ColumnNameList.Count);
}
#endregion
e.Result = wb;
}
void _BGWResitList_DoWork(object sender, DoWorkEventArgs e)
{
object[] objectValue = (object[])e.Argument;
int schoolyear = (int)objectValue[0];
int semester = (int)objectValue[1];
_BGWResitList.ReportProgress(0);
#region 取得所有學生以及補考資訊
AccessHelper helper = new AccessHelper();
List <StudentRecord> allStudents = new List <StudentRecord>();
List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass();
WearyDogComputer computer = new WearyDogComputer();
double currentClass = 1;
double totalClasses = allClasses.Count;
foreach (ClassRecord aClass in allClasses)
{
List <StudentRecord> classStudents = aClass.Students;
computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents);
allStudents.AddRange(classStudents);
_BGWResitList.ReportProgress((int)(currentClass++ *90.0 / totalClasses));
}
double currentStudent = 1;
double totalStudents = allStudents.Count;
#endregion
#region 產生表格並填入資料
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.補考名單_依學生), FileFormatType.Excel2003);
Workbook wb = new Workbook();
wb.Copy(template);
Worksheet ws = wb.Worksheets[0];
Range eachRow = template.Worksheets[0].Cells.CreateRange(2, 1, false);
ws.Cells[0, 0].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 第 " + semester + " 學期 學生補考名單");
int index = 2;
foreach (StudentRecord aStudent in allStudents)
{
string className = aStudent.RefClass.ClassName;
string seatNo = aStudent.SeatNo;
string studentNumber = aStudent.StudentNumber;
string studentName = aStudent.StudentName;
aStudent.SemesterSubjectScoreList.Sort(SortBySemesterSubjectScore);
foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList)
{
if (info.SchoolYear == schoolyear && info.Semester == semester && !info.Pass)
{
if (info.Detail.GetAttribute("達補考標準") == "是")
{
string subject = info.Subject;
string levelString = "";
int level;
if (int.TryParse(info.Level, out level))
{
levelString = GetNumber(level);
}
string credit = info.CreditDec().ToString();
string score = info.Detail.GetAttribute("原始成績");
string limit = info.Detail.GetAttribute("補考標準");
ws.Cells.CreateRange(index, 1, false).Copy(eachRow);
ws.Cells[index, 0].PutValue(className);
ws.Cells[index, 1].PutValue(seatNo);
ws.Cells[index, 2].PutValue(studentNumber);
ws.Cells[index, 3].PutValue(studentName);
ws.Cells[index, 4].PutValue(subject + (string.IsNullOrEmpty(levelString) ? "" : " " + levelString));
ws.Cells[index, 5].PutValue(credit);
ws.Cells[index, 6].PutValue(score);
ws.Cells[index, 7].PutValue(limit);
index++;
}
}
}
_BGWResitList.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents));
}
#endregion
e.Result = wb;
}
void _BWClassSemesterScore_DoWork(object sender, DoWorkEventArgs e)
{
object[] objectValue = (object[])e.Argument;
int schoolyear = (int)objectValue[0];
int semester = (int)objectValue[1];
bool over100 = (bool)objectValue[2];
int papersize = (int)objectValue[3];
bool UseSourceScore = (bool)objectValue[4];
_BWClassSemesterScore.ReportProgress(0);
Workbook template = new Workbook();
if (papersize == 0)
{
template.Open(new MemoryStream(Properties.Resources.班級學生學期成績一覽表B4));
}
else if (papersize == 1)
{
template.Open(new MemoryStream(Properties.Resources.班級學生學期成績一覽表A3));
}
Worksheet tempws = template.Worksheets[template.Worksheets.Add()];
// 表頭 0~5 垂直複製
Range tempHeader = template.Worksheets[0].Cells.CreateRange(0, 5, false);
// 學業
Range tempEachEntry = template.Worksheets[0].Cells.CreateRange(1, 33, 4, 2);
// 學分數
Range tempCreditHeader = template.Worksheets[0].Cells.CreateRange(1, 35, 4, 4);
Workbook wb = new Workbook();
wb.Copy(template);
Worksheet ws = wb.Worksheets[0];
ScoreAverageMachine machine = new ScoreAverageMachine();
AccessHelper helper = new AccessHelper();
List <StudentRecord> allClassStudent = new List <StudentRecord>();
foreach (ClassRecord aClass in helper.ClassHelper.GetSelectedClass())
{
allClassStudent.AddRange(aClass.Students);
}
double currentStudent = 1;
double totalStudent = allClassStudent.Count;
int rowIndex = 0;
int headerColIndex = 0;
foreach (ClassRecord aClass in helper.ClassHelper.GetSelectedClass())
{
List <StudentRecord> allStudent = aClass.Students;
int headerIndex = rowIndex;
int count = 0;
if (rowIndex > 0)
{
ws.Cells.CreateRange(headerIndex - 2, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
ws.Cells.CreateRange(headerIndex - 1, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
helper.StudentHelper.FillSchoolYearSubjectScore(true, allStudent);
helper.StudentHelper.FillSemesterSubjectScore(true, allStudent);
helper.StudentHelper.FillSemesterEntryScore(true, allStudent);
helper.StudentHelper.FillField("SemesterEntryClassRating", allStudent); //學期分項班排名。
List <string> subjectHeader = new List <string>();
Dictionary <string, string> subjectCreditHeader = new Dictionary <string, string>();
Dictionary <string, string> subjectReqHeader = new Dictionary <string, string>();
List <string> entryHeader = new List <string>();
Dictionary <string, int> columnIndexTable = new Dictionary <string, int>();
foreach (StudentRecord student in allStudent)
{
foreach (SemesterSubjectScoreInfo info in student.SemesterSubjectScoreList)
{
// 不計學分、不須評分跳過 || info.Detail.GetAttribute("不需評分") == "是"
if (info.Detail.GetAttribute("不計學分") == "是")
{
continue;
}
if (info.SchoolYear == schoolyear && info.Semester == semester)
{
int level;
string levelString = "";
if (int.TryParse(info.Level, out level))
{
levelString = GetNumber(level);
}
//
//string header = info.Subject + levelString + "_" + (info.Require ? "必 " : "選 ") + "_" + info.Credit;
//string creditHeader = (info.Require ? "必 " : "選 ") + info.Credit;
string header = info.Subject + levelString + "_" + info.CreditDec();
string creditHeader = info.CreditDec().ToString();
string reqHeader = info.Require ? "必 " : "選 ";
if (!subjectHeader.Contains(header))
{
subjectHeader.Add(header);
subjectCreditHeader.Add(header, creditHeader);
subjectReqHeader.Add(header, reqHeader);
}
}
}
foreach (SemesterEntryScoreInfo info in student.SemesterEntryScoreList)
{
if (info.SchoolYear == schoolyear && info.Semester == semester)
{
string header = info.Entry; // 先過濾原始
// 使用原始成績
if (UseSourceScore && header == "學業(原始)")
{
header = "學業平均(原始)";
}
if (UseSourceScore && header == "學業")
{
continue;
}
if (UseSourceScore == false && header == "學業(原始)")
{
continue;
}
if (UseSourceScore == false && header == "學業")
{
header = "學業平均(擇優)";
}
if (!entryHeader.Contains(header))
{
entryHeader.Add(header);
}
}
}
}
entryHeader.Add("班級排名");
entryHeader.Sort(SortByEntryName);
subjectHeader.Sort(SortBySubjectName);
string sString = "";
if (UseSourceScore)
{
sString = "(原始)";
}
else
{
sString = "(擇優)";
}
ws.Cells.CreateRange(rowIndex, 4, false).Copy(tempHeader);
ws.Cells[rowIndex, 0].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 第 " + semester + " 學期 班級學生成績一覽表 班級： " + aClass.ClassName + " " + sString);
headerColIndex = 3;
foreach (string subject in subjectHeader)
{
columnIndexTable.Add(subject, headerColIndex);
machine.AddItem(subject);
string sl = subject.Split('_')[0];
ws.Cells[rowIndex + 1, headerColIndex].PutValue(sl);
ws.Cells[rowIndex + 2, headerColIndex].PutValue(subjectReqHeader[subject]);
ws.Cells[rowIndex + 3, headerColIndex].PutValue(subjectCreditHeader[subject]);
headerColIndex++;
}
headerColIndex = 33;
foreach (string entry in entryHeader)
{
columnIndexTable.Add(entry, headerColIndex);
machine.AddItem(entry);
ws.Cells.CreateRange(rowIndex + 1, headerColIndex, 4, 2).Copy(tempEachEntry);
ws.Cells[rowIndex + 1, headerColIndex].PutValue(entry);
headerColIndex++;
}
ws.Cells.CreateRange(rowIndex + 1, headerColIndex, 4, 4).Copy(tempCreditHeader);
columnIndexTable.Add("應得學分", headerColIndex);
columnIndexTable.Add("實得學分", headerColIndex + 1);
columnIndexTable.Add("應得學分累計", headerColIndex + 2);
columnIndexTable.Add("實得學分累計", headerColIndex + 3);
tempws.Cells.CreateRange(0, 1, false).Copy(ws.Cells.CreateRange(rowIndex + 4, 1, false));
Range eachStudent = tempws.Cells.CreateRange(0, 1, false);
rowIndex += 4;
int defSS = schoolyear * 10 + semester;
foreach (StudentRecord student in allStudent)
{
count++;
ws.Cells.CreateRange(rowIndex, 1, false).Copy(eachStudent);
ws.Cells[rowIndex, 0].PutValue(student.StudentNumber);
ws.Cells[rowIndex, 1].PutValue(student.SeatNo);
ws.Cells[rowIndex, 2].PutValue(student.StudentName);
decimal shouldGetCredit = 0;
decimal gotCredit = 0;
decimal shouldGetTotalCredit = 0;
decimal gotTotalCredit = 0;
foreach (SemesterSubjectScoreInfo info in student.SemesterSubjectScoreList)
{
// 不計學分、不須評分跳過 || info.Detail.GetAttribute("不需評分") == "是"
if (info.Detail.GetAttribute("不計學分") == "是")
{
continue;
}
if (info.SchoolYear == schoolyear && info.Semester == semester)
{
shouldGetCredit += info.CreditDec();
if (info.Pass)
{
gotCredit += info.CreditDec();
}
int level;
string levelString = "";
if (int.TryParse(info.Level, out level))
{
levelString = GetNumber(level);
}
//之前
//string key = info.Subject + levelString + "_" + (info.Require ? "必 " : "選 ") + "_" + info.Credit;
string key = info.Subject + levelString + "_" + info.CreditDec();
if (columnIndexTable.ContainsKey(key))
{
// 判斷使用原始/擇優
decimal iScore = 0;
if (UseSourceScore)
{
decimal.TryParse(info.Detail.GetAttribute("原始成績"), out iScore);
}
else
{
iScore = info.Score; // 擇優
}
//ws.Cells[rowIndex, columnIndexTable[key]].PutValue((info.Pass ? "" : "*") + info.Score);
//machine.AddScore(key, info.Score);
ws.Cells[rowIndex, columnIndexTable[key]].PutValue((info.Pass ? "" : "*") + iScore);
machine.AddScore(key, iScore);
}
}
// 累計應得、累計實得，調整判斷學年度學期
int iss = info.SchoolYear * 10 + info.Semester;
if (iss <= defSS)
{
shouldGetTotalCredit += info.CreditDec();
if (info.Pass)
{
gotTotalCredit += info.CreditDec();
}
}
}
foreach (SemesterEntryScoreInfo info in student.SemesterEntryScoreList)
{
if (info.SchoolYear == schoolyear && info.Semester == semester)
{
if (UseSourceScore && info.Entry == "學業(原始)")
{
decimal score = info.Score;
if (!over100 && score > 100)
{
score = 100;
}
ws.Cells[rowIndex, columnIndexTable["學業平均(原始)"]].PutValue(score);
machine.AddScore("學業平均(原始)", score);
}
else if (UseSourceScore == false && info.Entry == "學業")
{
decimal score = info.Score;
if (!over100 && score > 100)
{
score = 100;
}
ws.Cells[rowIndex, columnIndexTable["學業平均(擇優)"]].PutValue(score);
machine.AddScore("學業平均(擇優)", score);
}
else
{
if (columnIndexTable.ContainsKey(info.Entry))
{
decimal score = info.Score;
if (!over100 && score > 100)
{
score = 100;
}
ws.Cells[rowIndex, columnIndexTable[info.Entry]].PutValue(score);
machine.AddScore(info.Entry, score);
}
}
}
}
ws.Cells[rowIndex, columnIndexTable["應得學分"]].PutValue(shouldGetCredit.ToString());
ws.Cells[rowIndex, columnIndexTable["實得學分"]].PutValue(gotCredit.ToString());
ws.Cells[rowIndex, columnIndexTable["應得學分累計"]].PutValue(shouldGetTotalCredit.ToString());
ws.Cells[rowIndex, columnIndexTable["實得學分累計"]].PutValue(gotTotalCredit.ToString());
SemesterEntryRating rating = new SemesterEntryRating(student);
ws.Cells[rowIndex, columnIndexTable["班級排名"]].PutValue(rating.GetPlace(schoolyear, semester));
if (count % 5 == 0)
{
ws.Cells.CreateRange(rowIndex, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
rowIndex++;
_BWClassSemesterScore.ReportProgress((int)(currentStudent++ *100.0 / totalStudent));
}
ws.Cells.CreateRange(headerIndex, 0, 4, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
ws.Cells.CreateRange(rowIndex, 1, false).Copy(eachStudent);
//for (int i = 0; i < headerColIndex + 4; i++)
//{
// ws.Cells[rowIndex, i].PutValue("");
//}
ws.Cells[rowIndex, 0].PutValue("平均");
foreach (string name in machine.GetAllItemName())
{
ws.Cells[rowIndex, columnIndexTable[name]].PutValue(machine.GetAverage(name).ToString());
}
machine.Clear();
rowIndex++;
ws.HPageBreaks.Add(rowIndex, headerColIndex + 4);
}
ws.Cells.CreateRange(rowIndex - 2, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
ws.Cells.CreateRange(rowIndex - 1, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
e.Result = wb;
}
void _BGWDisciplineDetail_DoWork(object sender, DoWorkEventArgs e)
{
string reportName = "學生獎勵明細";
#region 快取相關資料
//選擇的學生
List <SHStudentRecord> selectedStudents = SHStudent.SelectByIDs(K12.Presentation.NLDPanels.Student.SelectedSource);
selectedStudents.Sort(new Comparison <SHStudentRecord>(CommonMethods.SHClassSeatNoComparer));
//紀錄所有學生ID
List <string> allStudentID = new List <string>();
//每一位學生的獎勵明細
Dictionary <string, Dictionary <string, Dictionary <string, string> > > studentDisciplineDetail = new Dictionary <string, Dictionary <string, Dictionary <string, string> > >();
//每一位學生的獎勵累計資料
Dictionary <string, Dictionary <string, int> > studentDisciplineStatistics = new Dictionary <string, Dictionary <string, int> >();
//紀錄每一種獎勵在報表中的 column index
Dictionary <string, int> columnTable = new Dictionary <string, int>();
//取得所有學生ID
foreach (SHStudentRecord var in selectedStudents)
{
allStudentID.Add(var.ID);
}
//對照表
Dictionary <string, string> meritTable = new Dictionary <string, string>();
meritTable.Add("A", "大功");
meritTable.Add("B", "小功");
meritTable.Add("C", "嘉獎");
//初始化
string[] columnString = new string[] { "嘉獎", "小功", "大功", "事由", "備註" };
int i = 4;
foreach (string s in columnString)
{
columnTable.Add(s, i++);
}
//產生 DSRequest，取得缺曠明細
DSResponse dsrsp;
if (form.SelectDayOrSchoolYear) //依日期
{
if (form.SetupTime) //依發生日期
{
#region 依發生日期
DSXmlHelper helper = new DSXmlHelper("Request");
helper.AddElement("Field");
helper.AddElement("Field", "All");
helper.AddElement("Condition");
foreach (string var in allStudentID)
{
helper.AddElement("Condition", "RefStudentID", var);
}
helper.AddElement("Condition", "StartDate", form.StartDay);
helper.AddElement("Condition", "EndDate", form.EndDay);
helper.AddElement("Order");
helper.AddElement("Order", "OccurDate", "asc");
dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper));
#endregion
}
else //依登錄日期
{
#region 依登錄日期
DSXmlHelper helper = new DSXmlHelper("Request");
helper.AddElement("Field");
helper.AddElement("Field", "All");
helper.AddElement("Condition");
foreach (string var in allStudentID)
{
helper.AddElement("Condition", "RefStudentID", var);
}
helper.AddElement("Condition", "StartRegisterDate", form.StartDay);
helper.AddElement("Condition", "EndRegisterDate", form.EndDay);
helper.AddElement("Order");
helper.AddElement("Order", "OccurDate", "asc");
dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper));
#endregion
}
}
else //依學期
{
if (form.checkBoxX1Bool) //全部學期列印
{
#region 全部學期列印
DSXmlHelper helper = new DSXmlHelper("Request");
helper.AddElement("Field");
helper.AddElement("Field", "All");
helper.AddElement("Condition");
foreach (string var in allStudentID)
{
helper.AddElement("Condition", "RefStudentID", var);
}
helper.AddElement("Order");
helper.AddElement("Order", "OccurDate", "asc");
dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper));
#endregion
}
else //指定學期列印
{
#region 指定學期列印
DSXmlHelper helper = new DSXmlHelper("Request");
helper.AddElement("Field");
helper.AddElement("Field", "All");
helper.AddElement("Condition");
foreach (string var in allStudentID)
{
helper.AddElement("Condition", "RefStudentID", var);
}
helper.AddElement("Condition", "SchoolYear", form.SchoolYear);
helper.AddElement("Condition", "Semester", form.Semester);
helper.AddElement("Order");
helper.AddElement("Order", "OccurDate", "asc");
dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper));
#endregion
}
}
if (dsrsp == null)
{
MsgBox.Show("未取得獎勵資料");
return;
}
foreach (XmlElement var in dsrsp.GetContent().GetElements("Discipline"))
{
if (var.SelectSingleNode("MeritFlag").InnerText == "1")
{
string studentID = var.SelectSingleNode("RefStudentID").InnerText;
string schoolYear = var.SelectSingleNode("SchoolYear").InnerText;
string semester = var.SelectSingleNode("Semester").InnerText;
string occurDate = DateTime.Parse(var.SelectSingleNode("OccurDate").InnerText).ToShortDateString();
string reason = var.SelectSingleNode("Reason").InnerText;
string remark = var.SelectSingleNode("Remark").InnerText; //2019/12/31 - 新增
string disciplineID = var.GetAttribute("ID");
string sso = schoolYear + "_" + semester + "_" + occurDate + "_" + disciplineID;
//初始化累計資料
if (!studentDisciplineStatistics.ContainsKey(studentID))
{
studentDisciplineStatistics.Add(studentID, new Dictionary <string, int>());
}
//每一位學生獎勵資料
if (!studentDisciplineDetail.ContainsKey(studentID))
{
studentDisciplineDetail.Add(studentID, new Dictionary <string, Dictionary <string, string> >());
}
if (!studentDisciplineDetail[studentID].ContainsKey(sso))
{
studentDisciplineDetail[studentID].Add(sso, new Dictionary <string, string>());
}
//加入事由
if (!studentDisciplineDetail[studentID][sso].ContainsKey("事由"))
{
studentDisciplineDetail[studentID][sso].Add("事由", reason);
}
//加入事由
if (!studentDisciplineDetail[studentID][sso].ContainsKey("備註"))
{
studentDisciplineDetail[studentID][sso].Add("備註", remark);
}
XmlElement discipline = (XmlElement)var.SelectSingleNode("Detail/Discipline/Merit");
foreach (XmlAttribute attr in discipline.Attributes)
{
if (meritTable.ContainsKey(attr.Name))
{
string name = meritTable[attr.Name];
if (!studentDisciplineStatistics[studentID].ContainsKey(name))
{
studentDisciplineStatistics[studentID].Add(name, 0);
}
int v;
if (int.TryParse(attr.InnerText, out v))
{
studentDisciplineStatistics[studentID][name] += v;
}
if (!studentDisciplineDetail[studentID][sso].ContainsKey(name))
{
studentDisciplineDetail[studentID][sso].Add(name, attr.InnerText);
}
}
}
}
}
#endregion
#region 產生範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.學生獎勵記錄明細), FileFormatType.Excel2003);
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
int startPage = 1;
int pageNumber = 1;
int columnNumber = 9;
//合併標題列
ptws.Cells.CreateRange(0, 0, 1, columnNumber).Merge();
ptws.Cells.CreateRange(1, 0, 1, columnNumber).Merge();
Range ptHeader = ptws.Cells.CreateRange(0, 4, false);
Range ptEachRow = ptws.Cells.CreateRange(4, 1, false);
#endregion
#region 產生報表
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
int index = 0;
int dataIndex = 0;
int studentCount = 1;
foreach (SHStudentRecord studentInfo in selectedStudents)
{
//回報進度
_BGWDisciplineDetail.ReportProgress((int)(((double)studentCount++ *100.0) / (double)selectedStudents.Count));
if (!studentDisciplineDetail.ContainsKey(studentInfo.ID))
{
continue;
}
//如果不是第一頁，就在上一頁的資料列下邊加黑線
if (index != 0)
{
ws.Cells.CreateRange(index - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
//複製 Header
ws.Cells.CreateRange(index, 4, false).Copy(ptHeader);
//填寫基本資料
ws.Cells[index, 0].PutValue(School.ChineseName + " 個人獎勵明細");
ws.Cells[index + 1, 0].PutValue("班級：" + ((studentInfo.Class == null) ? " " : studentInfo.Class.Name) + " 座號：" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名：" + studentInfo.Name + " 學號：" + studentInfo.StudentNumber);
dataIndex = index + 4;
int recordCount = 0;
Dictionary <string, Dictionary <string, string> > disciplineDetail = studentDisciplineDetail[studentInfo.ID];
foreach (string sso in disciplineDetail.Keys)
{
string[] ssoSplit = sso.Split(new string[] { "_" }, StringSplitOptions.RemoveEmptyEntries);
//複製每一個 row
ws.Cells.CreateRange(dataIndex, 1, false).Copy(ptEachRow);
//填寫學生獎勵資料
ws.Cells[dataIndex, 0].PutValue(ssoSplit[0]);
ws.Cells[dataIndex, 1].PutValue(ssoSplit[1]);
ws.Cells[dataIndex, 2].PutValue(ssoSplit[2]);
ws.Cells[dataIndex, 3].PutValue(CommonMethods.GetChineseDayOfWeek(DateTime.Parse(ssoSplit[2])));
Dictionary <string, string> record = disciplineDetail[sso];
foreach (string name in record.Keys)
{
if (meritTable.ContainsValue(name))
{
int v;
if (int.TryParse(record[name], out v))
{
if (v > 0)
{
ws.Cells[dataIndex, columnTable[name]].PutValue(record[name]);
}
}
}
else
{
if (columnTable.ContainsKey(name))
{
ws.Cells[dataIndex, columnTable[name]].PutValue(record[name]);
}
}
}
dataIndex++;
recordCount++;
}
//獎懲統計資訊
Range disciplineStatisticsRange = ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber);
disciplineStatisticsRange.Copy(ptEachRow);
disciplineStatisticsRange.Merge();
disciplineStatisticsRange.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black);
disciplineStatisticsRange.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black);
disciplineStatisticsRange.RowHeight = 20.0;
ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.Font.Size = 10;
ws.Cells[dataIndex, 0].PutValue("獎勵總計");
dataIndex++;
//獎懲統計內容
ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Copy(ptEachRow);
ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).RowHeight = 27.0;
ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Merge();
ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center;
ws.Cells[dataIndex, 0].Style.Font.Size = 10;
ws.Cells[dataIndex, 0].Style.ShrinkToFit = true;
StringBuilder text = new StringBuilder("");
Dictionary <string, int> disciplineStatistics = studentDisciplineStatistics[studentInfo.ID];
foreach (string type in disciplineStatistics.Keys)
{
if (disciplineStatistics[type] > 0)
{
if (text.ToString() != "")
{
text.Append(" ");
}
text.Append(type + "：" + disciplineStatistics[type]);
}
}
ws.Cells[dataIndex, 0].PutValue(text.ToString());
dataIndex++;
//資料列上邊加上黑線
ws.Cells.CreateRange(index + 3, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
//表格最右邊加上黑線
ws.Cells.CreateRange(index + 2, columnNumber - 1, recordCount + 4, 1).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
index = dataIndex;
//設定分頁
if (pageNumber < 500)
{
ws.HPageBreaks.Add(index, columnNumber);
pageNumber++;
}
else
{
ws.Name = startPage + " ~ " + (pageNumber + startPage - 1);
ws = wb.Worksheets[wb.Worksheets.Add()];
ws.Copy(prototype.Worksheets[0]);
startPage += pageNumber;
pageNumber = 1;
index = 0;
}
}
if (dataIndex > 0)
{
//最後一頁的資料列下邊加上黑線
ws.Cells.CreateRange(dataIndex - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
ws.Name = startPage + " ~ " + (pageNumber + startPage - 2);
}
else
{
wb = new Workbook();
}
#endregion
string path = Path.Combine(Application.StartupPath, "Reports");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = Path.Combine(path, reportName + ".xlt");
e.Result = new object[] { reportName, path, wb };
}
void _BGWAbsenceWeekListByAbsence_DoWork(object sender, DoWorkEventArgs e)
{
string reportName = "缺曠週報表";
object[] args = e.Argument as object[];
Dictionary <string, List <string> > config = args[0] as Dictionary <string, List <string> >;
DateTime startDate = (DateTime)args[1];
DateTime endDate = (DateTime)args[2];
int size = (int)args[3];
bool CheckClass = (bool)args[4];
bool CheckWeek = (bool)args[5];
DateTime firstDate = startDate;
#region 快取學生缺曠紀錄資料
List <ClassRecord> selectedClass = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource);
selectedClass.Sort(new Comparison <ClassRecord>(CommonMethods.ClassComparer));
Dictionary <string, List <StudentRecord> > classStudentList = new Dictionary <string, List <StudentRecord> >();
List <string> allStudentID = new List <string>();
//紀錄每一個 Column 的 Index
Dictionary <string, int> columnTable = new Dictionary <string, int>();
//紀錄每一個學生的缺曠紀錄
Dictionary <string, Dictionary <string, Dictionary <string, int> > > studentAbsenceList = new Dictionary <string, Dictionary <string, Dictionary <string, int> > >();
//紀錄每一個學生本週累計的缺曠紀錄
Dictionary <string, Dictionary <string, int> > studentWeekAbsenceList = new Dictionary <string, Dictionary <string, int> >();
//紀錄每一個學生學期累計的缺曠紀錄
Dictionary <string, Dictionary <string, int> > studentSemesterAbsenceList = new Dictionary <string, Dictionary <string, int> >();
//節次對照表
Dictionary <string, string> periodList = new Dictionary <string, string>();
int allStudentNumber = 0;
//計算學生總數，取得所有學生ID
foreach (ClassRecord aClass in selectedClass)
{
List <StudentRecord> classStudent = new List <StudentRecord>(); //取得一般生
foreach (StudentRecord each in aClass.Students)
{
if (each.Status == StudentRecord.StudentStatus.一般)
{
classStudent.Add(each);
}
}
classStudent.Sort(new Comparison <StudentRecord>(CommonMethods.ClassSeatNoComparer));
foreach (StudentRecord aStudent in classStudent)
{
allStudentID.Add(aStudent.ID);
}
if (!classStudentList.ContainsKey(aClass.ID))
{
classStudentList.Add(aClass.ID, classStudent);
}
allStudentNumber += classStudent.Count;
}
//取得 Period List
List <K12.Data.PeriodMappingInfo> PeriodInfoList = K12.Data.PeriodMapping.SelectAll();
foreach (PeriodMappingInfo var in PeriodInfoList)
{
string name = var.Name;
string type = var.Type;
if (!periodList.ContainsKey(name))
{
periodList.Add(name, type);
}
}
//產生 DSRequest
DSXmlHelper helper = new DSXmlHelper("Request");
helper.AddElement("Field");
helper.AddElement("Field", "All");
helper.AddElement("Condition");
foreach (string var in allStudentID)
{
helper.AddElement("Condition", "RefStudentID", var);
}
helper.AddElement("Condition", "StartDate", startDate.ToShortDateString());
if (CheckWeek) //new,True就是取得至星期日內
{
helper.AddElement("Condition", "EndDate", endDate.ToShortDateString());
}
else //new,false就是取得到星期五的缺曠內容
{
helper.AddElement("Condition", "EndDate", endDate.AddDays(-2).ToShortDateString());
}
helper.AddElement("Order");
helper.AddElement("Order", "OccurDate", "desc");
DSResponse dsrsp = Get.GetAttendance(new DSRequest(helper));
foreach (XmlElement var in dsrsp.GetContent().GetElements("Attendance"))
{
string studentID = var.SelectSingleNode("RefStudentID").InnerText;
string occurDate = DateTime.Parse(var.SelectSingleNode("OccurDate").InnerText).ToShortDateString();
if (!studentAbsenceList.ContainsKey(studentID))
{
studentAbsenceList.Add(studentID, new Dictionary <string, Dictionary <string, int> >());
}
if (!studentAbsenceList[studentID].ContainsKey(occurDate))
{
studentAbsenceList[studentID].Add(occurDate, new Dictionary <string, int>());
}
if (!studentWeekAbsenceList.ContainsKey(studentID))
{
studentWeekAbsenceList.Add(studentID, new Dictionary <string, int>());
}
foreach (XmlElement period in var.SelectNodes("Detail/Attendance/Period"))
{
string type = periodList.ContainsKey(period.InnerText) ? periodList[period.InnerText] : "";
string absence = period.GetAttribute("AbsenceType");
if (!studentAbsenceList[studentID][occurDate].ContainsKey(type + "_" + absence))
{
studentAbsenceList[studentID][occurDate].Add(type + "_" + absence, 0);
}
studentAbsenceList[studentID][occurDate][type + "_" + absence]++;
if (!studentWeekAbsenceList[studentID].ContainsKey(type + "_" + absence))
{
studentWeekAbsenceList[studentID].Add(type + "_" + absence, 0);
}
studentWeekAbsenceList[studentID][type + "_" + absence]++;
}
}
//產生 DSRequest，本學期累計
helper = new DSXmlHelper("Request");
helper.AddElement("Field");
helper.AddElement("Field", "All");
helper.AddElement("Condition");
foreach (string var in allStudentID)
{
helper.AddElement("Condition", "RefStudentID", var);
}
helper.AddElement("Condition", "SchoolYear", K12.Data.School.DefaultSchoolYear);
helper.AddElement("Condition", "Semester", K12.Data.School.DefaultSemester);
if (CheckWeek) //new,True就是取得至星期日內
{
helper.AddElement("Condition", "EndDate", endDate.ToShortDateString());
}
else
{
helper.AddElement("Condition", "EndDate", endDate.AddDays(-2).ToShortDateString());
}
helper.AddElement("Order");
helper.AddElement("Order", "OccurDate", "desc");
dsrsp = Get.GetAttendance(new DSRequest(helper));
foreach (XmlElement var in dsrsp.GetContent().GetElements("Attendance"))
{
string studentID = var.SelectSingleNode("RefStudentID").InnerText;
if (!studentSemesterAbsenceList.ContainsKey(studentID))
{
studentSemesterAbsenceList.Add(studentID, new Dictionary <string, int>());
}
foreach (XmlElement period in var.SelectNodes("Detail/Attendance/Period"))
{
string type = periodList.ContainsKey(period.InnerText) ? periodList[period.InnerText] : "";
string absence = period.GetAttribute("AbsenceType");
if (!studentSemesterAbsenceList[studentID].ContainsKey(type + "_" + absence))
{
studentSemesterAbsenceList[studentID].Add(type + "_" + absence, 0);
}
studentSemesterAbsenceList[studentID][type + "_" + absence]++;
}
}
#endregion
//取得學生英文別名
//Dictionary<string, string> StudentEXTDic = tool.GetStudentEXT(allStudentID);
//計算使用者自訂項目
int allAbsenceNumber = 7;
foreach (string type in config.Keys)
{
allAbsenceNumber += config[type].Count;
}
int current = 1;
int all = allAbsenceNumber + allStudentNumber;
#region 動態產生範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.缺曠週報表_依假別), FileFormatType.Excel2003);
Range tempStudent = template.Worksheets[0].Cells.CreateRange(0, 4, true);
Range tempEachColumn = template.Worksheets[0].Cells.CreateRange(4, 1, true);
Workbook prototype = new Workbook();
prototype.Copy(template);
prototype.Worksheets[0].Cells.CreateRange(0, 4, true).Copy(tempStudent);
int titleRow = 2;
int dayNumber;
if (CheckWeek)
{
dayNumber = 7;
}
else
{
dayNumber = 5;
}
int colIndex = 4;
int dayStartIndex = colIndex;
int dayEndIndex;
int dayColumnNumber;
//根據使用者設定的缺曠別，產生 Column
List <string> perList = new List <string>();
foreach (string type in config.Keys)
{
if (config[type].Count == 0)
{
continue;
}
int typeStartIndex = colIndex;
foreach (string var in config[type])
{
string SaveVar = type + "_" + var;
prototype.Worksheets[0].Cells.CreateRange(colIndex, 1, true).Copy(tempEachColumn);
prototype.Worksheets[0].Cells[titleRow + 2, colIndex].PutValue(var);
Cell _c = prototype.Worksheets[0].Cells[titleRow + 2, colIndex];
_c.Style.Rotation = -90; //將文字改為垂直
columnTable.Add(SaveVar, colIndex - 4);
if (!perList.Contains(SaveVar))
{
perList.Add(type + "_" + var);
}
colIndex++;
_BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all));
}
int typeEndIndex = colIndex;
Range typeRange = prototype.Worksheets[0].Cells.CreateRange(titleRow, typeStartIndex, titleRow + 2, typeEndIndex - typeStartIndex);
typeRange.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.Black);
typeRange.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
prototype.Worksheets[0].Cells.CreateRange(titleRow + 1, typeStartIndex, 1, typeEndIndex - typeStartIndex).Merge();
prototype.Worksheets[0].Cells[titleRow + 1, typeStartIndex].PutValue(type);
}
dayEndIndex = colIndex;
dayColumnNumber = dayEndIndex - dayStartIndex;
if (dayColumnNumber == 0)
{
dayColumnNumber = 1;
}
prototype.Worksheets[0].Cells.CreateRange(titleRow, dayStartIndex, 1, dayColumnNumber).Merge();
Range dayRange = prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true);
//prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue(firstDate.ToShortDateString() + " (" + CommonMethods.GetChineseDayOfWeek(firstDate) + ")");
//columnTable.Add(firstDate.ToShortDateString(), dayStartIndex);
//以一個日期為單位進行 Column 複製
//for (int i = 1; i < dayNumber; i++)
//{
// firstDate = firstDate.AddDays(1);
// dayStartIndex += dayColumnNumber;
// prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true).Copy(dayRange);
// prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue(firstDate.ToShortDateString() + " (" + CommonMethods.GetChineseDayOfWeek(firstDate) + ")");
// columnTable.Add(firstDate.ToShortDateString(), dayStartIndex);
// _BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ * 100.0) / (double)all));
//}
//dayStartIndex += dayColumnNumber;
//prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true).Copy(dayRange);
prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue("本週合計(Week)");
columnTable.Add("本週合計(Week)", dayStartIndex);
_BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all));
dayStartIndex += dayColumnNumber;
prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true).Copy(dayRange);
//2011/3/10 - 調整顯示字樣
prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue("本學期累計(Semester)");
columnTable.Add("本學期累計(Semester)", dayStartIndex);
_BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all));
dayStartIndex += dayColumnNumber;
//合併標題列
prototype.Worksheets[0].Cells.CreateRange(0, 0, 1, dayStartIndex).Merge();
prototype.Worksheets[0].Cells.CreateRange(1, 0, 1, dayStartIndex).Merge();
Range prototypeRow = prototype.Worksheets[0].Cells.CreateRange(5, 1, false);
Range prototypeHeader = prototype.Worksheets[0].Cells.CreateRange(0, 5, false);
current++;
#endregion
#region 產生報表
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
#region 判斷紙張大小
if (size == 0)
{
ws.PageSetup.PaperSize = PaperSizeType.PaperA3;
ws.PageSetup.Zoom = 90;
}
else if (size == 1)
{
ws.PageSetup.PaperSize = PaperSizeType.PaperA4;
ws.PageSetup.Zoom = 65;
}
else if (size == 2)
{
ws.PageSetup.PaperSize = PaperSizeType.PaperB4;
ws.PageSetup.Zoom = 80;
}
#endregion
int index = 0;
int dataIndex = 0;
List <string> list = new List <string>();
#region 檢查是否有資料
if (CheckClass) //如果需要過慮資料
{
foreach (ClassRecord CheckClassData in selectedClass)
{
List <StudentRecord> classStudent = classStudentList[CheckClassData.ID];
bool jumpNext = false;
foreach (StudentRecord CheckStudentData in classStudent)
{
if (studentWeekAbsenceList.ContainsKey(CheckStudentData.ID)) //如果studentWeekAbsenceList內包含了該學生ID
{
foreach (string ajn in studentWeekAbsenceList[CheckStudentData.ID].Keys) //取得該學生假別
{
if (perList.Contains(ajn)) //如果假別的確包含於清單中
{
if (!list.Contains(CheckClassData.ID)) //如果清單中不包含就加入
{
list.Add(CheckClassData.ID);
jumpNext = true;
}
}
if (jumpNext)
{
break;
}
}
}
if (jumpNext)
{
break;
}
}
}
}
else //如果不需要過慮資料
{
foreach (ClassRecord CheckClassData in selectedClass)
{
list.Add(CheckClassData.ID);
}
}
#endregion
foreach (ClassRecord classInfo in selectedClass)
{
if (list.Contains(classInfo.ID))
{
List <StudentRecord> classStudent = classStudentList[classInfo.ID];
//如果不是第一頁，就在上一頁的資料列下邊加黑線
if (index != 0)
{
ws.Cells.CreateRange(index - 1, 0, 1, dayStartIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
//複製 Header
ws.Cells.CreateRange(index, 5, false).Copy(prototypeHeader);
//填寫基本資料
string TeacherName = "";
if (classInfo.Teacher != null)
{
TeacherName = classInfo.Teacher.Name + " 老師";
}
ws.Cells[index, 0].PutValue("SchoolYear:" + tool.GetSchoolChange(K12.Data.School.DefaultSchoolYear) + " Semester:" + K12.Data.School.DefaultSemester + " " + School.ChineseName + " 學生缺曠課表");
if (CheckWeek) //new,True就是取得至星期日內
{
ws.Cells[index + 1, 0].PutValue("班導師： " + TeacherName + " 缺曠統計區間： " + startDate.ToShortDateString() + " ~ " + endDate.ToShortDateString() + " 列印日期：" + DateTime.Today.ToShortDateString());
}
else
{
ws.Cells[index + 1, 0].PutValue("班導師： " + TeacherName + " 缺曠統計區間： " + startDate.ToShortDateString() + " ~ " + endDate.AddDays(-2).ToShortDateString() + " 列印日期：" + DateTime.Today.ToShortDateString());
}
dataIndex = index + 5;
int studentCount = 0;
while (studentCount < classStudent.Count)
{
//複製每一個 row
ws.Cells.CreateRange(dataIndex, 1, false).Copy(prototypeRow);
if (studentCount % 5 == 0 && studentCount != 0)
{
Range eachFiveRow = ws.Cells.CreateRange(dataIndex, 0, 1, dayStartIndex);
eachFiveRow.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black);
}
//填寫學生缺曠資料
StudentRecord student = classStudent[studentCount];
string studentID = student.ID;
ws.Cells[dataIndex, 0].PutValue(student.Class != null ? student.Class.Name : "");
ws.Cells[dataIndex, 1].PutValue(student.SeatNo);
ws.Cells[dataIndex, 2].PutValue(student.Name);
ws.Cells[dataIndex, 3].PutValue(student.EnglishName);
//英文別名
//if (StudentEXTDic.ContainsKey(student.ID))
//{
// ws.Cells[dataIndex, 4].PutValue(StudentEXTDic[student.ID]);
//}
int startCol;
//if (studentAbsenceList.ContainsKey(studentID))
//{
// foreach (string date in studentAbsenceList[studentID].Keys)
// {
// Dictionary<string, int> dateAbsence = studentAbsenceList[studentID][date];
// startCol = columnTable[date];
// foreach (string var in dateAbsence.Keys)
// {
// if (columnTable.ContainsKey(var))
// {
// ws.Cells[dataIndex, startCol + columnTable[var]].PutValue(dateAbsence[var]);
// }
// }
// }
//}
if (studentWeekAbsenceList.ContainsKey(studentID))
{
startCol = columnTable["本週合計(Week)"];
Dictionary <string, int> studentWeek = studentWeekAbsenceList[studentID];
foreach (string var in studentWeek.Keys)
{
if (columnTable.ContainsKey(var))
{
ws.Cells[dataIndex, startCol + columnTable[var]].PutValue(studentWeekAbsenceList[studentID][var]);
}
}
}
if (studentSemesterAbsenceList.ContainsKey(studentID))
{
startCol = columnTable["本學期累計(Semester)"];
Dictionary <string, int> studentSemester = studentSemesterAbsenceList[studentID];
foreach (string var in studentSemester.Keys)
{
if (columnTable.ContainsKey(var))
{
ws.Cells[dataIndex, startCol + columnTable[var]].PutValue(studentSemester[var]);
}
}
}
studentCount++;
dataIndex++;
_BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all));
}
//資料列上邊各加上黑線
ws.Cells.CreateRange(index + 4, 0, 1, dayStartIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
index = dataIndex;
//設定分頁
ws.HPageBreaks.Add(index, dayStartIndex);
}
}
//最後一頁的資料列下邊加上黑線
if (dataIndex != 0)
{
ws.Cells.CreateRange(dataIndex - 1, 0, 1, dayStartIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
#endregion
string path = Path.Combine(Application.StartupPath, "Reports");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = Path.Combine(path, reportName + ".xlt");
e.Result = new object[] { reportName, path, wb };
}
void _BGWTotalDisciplineAndAbsence_DoWork(object sender, DoWorkEventArgs e)
{
string reportName = "歷年功過及出席統計";
_BGWTotalDisciplineAndAbsence.ReportProgress(0);
#region 取得資料
AccessHelper helper = new AccessHelper();
List <StudentRecord> students = helper.StudentHelper.GetSelectedStudent();
helper.StudentHelper.FillSemesterEntryScore(true, students);
helper.StudentHelper.FillReward(students);
helper.StudentHelper.FillAttendance(students);
StatisticsCollection stat = new StatisticsCollection();
Dictionary <string, List <string> > detailList = new Dictionary <string, List <string> >();
Dictionary <string, int> rewardDict = new Dictionary <string, int>();
foreach (StudentRecord each in students)
{
StatisticsData data = new StatisticsData();
List <string> list = new List <string>();
//獎懲
foreach (RewardInfo info in each.RewardList)
{
int schoolyear = info.SchoolYear;
int semester = info.Semester;
if (info.UltimateAdmonition == true)
{
data.AddItem(schoolyear, semester, "留校察看", 1);
list.Add(CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " 留校察看");
continue;
}
rewardDict.Clear();
data.AddItem(schoolyear, semester, "大功", (decimal)info.AwardA);
data.AddItem(schoolyear, semester, "小功", (decimal)info.AwardB);
data.AddItem(schoolyear, semester, "嘉獎", (decimal)info.AwardC);
rewardDict.Add("大功", info.AwardA);
rewardDict.Add("小功", info.AwardB);
rewardDict.Add("嘉獎", info.AwardC);
if (_print_cleared == true || info.Cleared == false)
{
data.AddItem(schoolyear, semester, "大過", (decimal)info.FaultA);
data.AddItem(schoolyear, semester, "小過", (decimal)info.FaultB);
data.AddItem(schoolyear, semester, "警告", (decimal)info.FaultC);
rewardDict.Add("大過", info.FaultA);
rewardDict.Add("小過", info.FaultB);
rewardDict.Add("警告", info.FaultC);
}
string rewardStat = "";
foreach (string var in new string[] { "大功", "小功", "嘉獎", "大過", "小過", "警告" })
{
if (rewardDict.ContainsKey(var) && rewardDict[var] > 0)
{
if (!string.IsNullOrEmpty(rewardStat))
{
rewardStat += ", ";
}
rewardStat += var + rewardDict[var] + "次";
}
}
//兩個條件可加入明細清單
//1.如果勾選消過也列印
//2.資料未消過
if (_print_cleared == true)
{
string all = "";
if (info.Cleared)
{
all = CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " " + rewardStat + " (已銷過)";
}
else
{
all = CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " " + rewardStat;
}
list.Add(all);
}
else if (info.Cleared == false)
{
string all = CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " " + rewardStat;
list.Add(all);
}
#region 註解掉了
//if (((info.OccurReason + " " + rewardStat) as string).Length >= 20)
//{
// List<string> mini = new List<string>();
// string reason = info.OccurReason;
// while (reason.Length >= 20)
// {
// mini.Add(reason.Substring(0, 19));
// reason = reason.Substring(19);
// }
// if (((reason + " " + rewardStat) as string).Length >= 20)
// {
// mini.Add(reason);
// mini.Add(rewardStat);
// }
// else
// mini.Add(reason + " " + rewardStat);
// //mini.Add(CDATE(info.OccurDate.ToShortDateString()) + " ");
// for (int i = 0; i < mini.Count; i++)
// {
// if (i == 0)
// list.Add(CDATE(info.OccurDate.ToShortDateString()) + " " + mini[i]);
// else
// list.Add(" " + mini[i]);
// }
//}
//else
// list.Add(all);
#endregion
}
detailList.Add(each.StudentID, list);
//缺曠
foreach (AttendanceInfo info in each.AttendanceList)
{
int schoolyear = info.SchoolYear;
int semester = info.Semester;
if (PrintDic.ContainsKey(info.Period))
{
data.AddItem(schoolyear, semester, PrintDic[info.Period] + "_" + info.Absence, 1);
}
}
//德行成績
foreach (SemesterEntryScoreInfo info in each.SemesterEntryScoreList)
{
if (info.Entry == "德行")
{
int schoolyear = info.SchoolYear;
int semester = info.Semester;
data.AddItem(schoolyear, semester, "德行成績", info.Score);
}
}
data.MendSemester();
stat.Add(each.StudentID, data);
}
#endregion
#region 產生範本
int AsbCount = 0;
foreach (string each in _print_types.Keys)
{
AsbCount += _print_types[each].Count;
}
totalRow = detailRow + 11 + AsbCount;
foreach (StudentRecord each in students)
{
if (detailRow < detailList[each.StudentID].Count / 2 + 1)
{
detailRow = detailList[each.StudentID].Count / 2 + 1;
totalRow = detailRow + 11 + AsbCount;
}
}
Workbook pt1 = GetTemplate(6);
Workbook pt2 = GetTemplate(8);
Workbook pt3 = GetTemplate(10);
Dictionary <int, Workbook> pts = new Dictionary <int, Workbook>();
pts.Add(6, pt1);
pts.Add(8, pt2);
pts.Add(10, pt3);
Range ptr1 = pt1.Worksheets[0].Cells.CreateRange(0, totalRow, false);
Range ptr2 = pt2.Worksheets[0].Cells.CreateRange(0, totalRow, false);
Range ptr3 = pt3.Worksheets[0].Cells.CreateRange(0, totalRow, false);
Dictionary <int, Range> ptrs = new Dictionary <int, Range>();
ptrs.Add(6, ptr1);
ptrs.Add(8, ptr2);
ptrs.Add(10, ptr3);
#endregion
#region 產生報表
Workbook wb = new Workbook();
wb.Copy(pt1);
wb.Worksheets[0].Copy(pt1.Worksheets[0]);
int wsCount = wb.Worksheets.Count;
for (int i = wsCount - 1; i > 0; i--)
{
wb.Worksheets.RemoveAt(i);
}
wb.Worksheets[0].Name = "一般(6學期)";
Dictionary <int, int> sheetIndex = new Dictionary <int, int>();
Dictionary <int, Worksheet> sheets = new Dictionary <int, Worksheet>();
sheets.Add(6, wb.Worksheets[0]);
sheetIndex.Add(6, 0);
Worksheet ws = wb.Worksheets[0];
int index = 0;
int cur = 6;
int pages = 500;
//
int start = 1;
int limit = pages;
int studentCount = 0;
//學生數
int allStudentCount = students.Count;
foreach (StudentRecord each in students)
{
//取得學生統計
StatisticsData data = stat[each.StudentID];
#region 判斷學期數
if (data.GetSemesterNumber() > 6 && data.GetSemesterNumber() <= 8)
{
//學期數大於6小於8
//特殊報表
if (!sheets.ContainsKey(8))
{
int new_ws_index = wb.Worksheets.Add();
wb.Worksheets[new_ws_index].Copy(pt2.Worksheets[0]);
wb.Worksheets[new_ws_index].Name = "特殊(8學期)";
sheets.Add(8, wb.Worksheets[new_ws_index]);
sheetIndex.Add(8, 0);
}
//變更預設報表位置
ws = sheets[8];
//取得報表定位
index = sheetIndex[8];
//取得報表定位?
cur = 8;
studentCount++;
}
else if (data.GetSemesterNumber() == 6)
{
ws = sheets[6];
index = sheetIndex[6];
cur = 6;
studentCount++;
}
else
{
//學期數大於8小於10
//特殊1報表
if (!sheets.ContainsKey(10))
{
int new_ws_index = wb.Worksheets.Add();
wb.Worksheets[new_ws_index].Copy(pt3.Worksheets[0]);
wb.Worksheets[new_ws_index].Name = "特殊(其它)";
sheets.Add(10, wb.Worksheets[new_ws_index]);
sheetIndex.Add(10, 0);
}
ws = sheets[10];
index = sheetIndex[10];
cur = 10;
studentCount++;
}
#endregion
//取得畫面範圍 Range(0~TotalRow)
ws.Cells.CreateRange(index, totalRow, false).Copy(ptrs[cur]);
//填入標題
ws.Cells[index + 1, 0].PutValue(string.Format("班級：{0} 學號：{1} 姓名：{2}", (each.RefClass != null) ? each.RefClass.ClassName : "", each.StudentNumber, each.StudentName));
//第一行位置(標題+2)
int firstRow = index + 2;
//第一列位置
int col = 2;
//處理每個學期的統計
foreach (string semsString in data.Semesters)
{
//填入學年度/學期
ws.Cells[firstRow, col].PutValue(DisplaySemester(semsString));
//取得學期統計資料
Dictionary <string, decimal> semsDict = data.GetItem(semsString);
foreach (string item in semsDict.Keys)
{
if (rowIndexTable.ContainsKey(item))
{
ws.Cells[index + rowIndexTable[item], col].PutValue((semsDict[item] <= 0) ? "" : semsDict[item].ToString());
}
}
col++;
}
if (rowIndexTable.ContainsKey("明細"))
{
//資料一律由第二列進行列印
int detailColIndex = 2;
//
int detailIndex = index + rowIndexTable["明細"];
//明細定位點
int detailCount = 0;
//取得學生明細清單
foreach (string var in detailList[each.StudentID])
{
//明細定位點+1
detailCount++;
//定位點大於明細畫面範圍時
//將換為第二區進行列印
if (detailCount > detailRow)
{
detailColIndex += (cur / 2);
detailIndex = index + rowIndexTable["明細"];
detailCount = 0;
}
ws.Cells[detailIndex++, detailColIndex].PutValue(var);
}
}
//?
index += totalRow;
sheetIndex[cur] = index;
//增加列印分割線
ws.HPageBreaks.Add(index, 0);
//當學生大於極限值 , 並且列印完所有學生
if (studentCount >= limit && studentCount < allStudentCount)
{
//取得工作表名稱
//string orig_name = ws.Name;
//
//ws.Name = ws.Name + " (" + start + " ~ " + studentCount + ")";
ws = wb.Worksheets[wb.Worksheets.Add()];
ws.Copy(pts[cur].Worksheets[0]);
//ws.Name = orig_name;
start += pages;
limit += pages;
index = 0;
sheetIndex[cur] = index;
sheets[cur] = ws;
}
//回報進度
_BGWTotalDisciplineAndAbsence.ReportProgress((int)(((double)studentCount * 100.0) / (double)allStudentCount));
}
//if (cur == 6)
//ws.Name = ws.Name + " (" + start + " ~ " + studentCount + ")";
#endregion
string path = Path.Combine(Application.StartupPath, "Reports");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = Path.Combine(path, reportName + ".xlt");
e.Result = new object[] { reportName, path, wb };
}
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 Workbook GetTemplate(int sems_num)
{
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.歷年功過及出席統計), FileFormatType.Excel2003);
Worksheet tempWorksheet = template.Worksheets["" + sems_num];
Range tempHeader = tempWorksheet.Cells.CreateRange(0, 2, false);
Range tempRow = tempWorksheet.Cells.CreateRange(2, 1, false);
Range tempStatistics = tempWorksheet.Cells.CreateRange(3, 1, false);
Range tempDetail = tempWorksheet.Cells.CreateRange(5, 1, false);
Workbook prototype = new Workbook();
prototype.Copy(template);
int count = prototype.Worksheets.Count;
for (int i = count - 1; i > 0; i--)
{
prototype.Worksheets.RemoveAt(i);
}
prototype.Worksheets[0].Copy(tempWorksheet);
prototype.Worksheets[0].Cells.CreateRange(0, 2, false).Copy(tempHeader);
int index = 2;
rowIndexTable.Clear();
//標題
prototype.Worksheets[0].Cells[0, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolChineseName + "學生個人歷年功過及出席統計表");
//學期
prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempRow);
prototype.Worksheets[0].Cells[index, 0].PutValue("學年度學期");
index++;
//獎懲部分
foreach (string var in new string[] { "大功", "小功", "嘉獎" })
{
prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempStatistics);
prototype.Worksheets[0].Cells[index, 1].PutValue(var);
rowIndexTable.Add(var, index);
index++;
}
foreach (string var in new string[] { "大過", "小過", "警告", "留校察看" })
{
prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempStatistics);
prototype.Worksheets[0].Cells[index, 1].PutValue(var);
rowIndexTable.Add(var, index);
index++;
}
prototype.Worksheets[0].Cells.CreateRange(index - 7, 0, 3, 1).Merge();
prototype.Worksheets[0].Cells[index - 7, 0].PutValue("獎勵");
prototype.Worksheets[0].Cells.CreateRange(index - 4, 0, 4, 1).Merge();
prototype.Worksheets[0].Cells[index - 4, 0].PutValue("懲戒");
//缺曠部分
foreach (string period in _print_types.Keys)
{
foreach (string absence in _print_types[period])
{
prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempStatistics);
prototype.Worksheets[0].Cells[index, 1].PutValue(absence);
rowIndexTable.Add(period + "_" + absence, index);
index++;
}
prototype.Worksheets[0].Cells.CreateRange(index - _print_types[period].Count, 0, _print_types[period].Count, 1).Merge();
prototype.Worksheets[0].Cells[index - _print_types[period].Count, 0].PutValue(period);
}
//德行成績部分
prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempRow);
prototype.Worksheets[0].Cells[index, 0].PutValue("德行成績");
rowIndexTable.Add("德行成績", index);
index++;
//獎懲明細部分
////檢查每個學生獎懲明細是否會超過表格大小
//foreach (string var in disciplineDetail.Keys)
//{
// if (disciplineDetail[var].Count > (totalRows - index) * 2)
// {
// totalRows += (disciplineDetail[var].Count - (totalRows - index) * 2) / 2;
// totalRows += (disciplineDetail[var].Count - (totalRows - index) * 2) % 2;
// }
//}
rowIndexTable.Add("明細", index);
//if ((index + detailRow) > totalRow)
//{
// detailRow = totalRow - index;
//}
for (int j = index; j < index + detailRow; j++)
{
prototype.Worksheets[0].Cells.CreateRange(j, 1, false).Copy(tempDetail);
}
prototype.Worksheets[0].Cells.CreateRange(index, 0, detailRow, 1).Merge();
prototype.Worksheets[0].Cells[index, 0].PutValue("獎懲明細");
//加上報表底線
prototype.Worksheets[0].Cells.CreateRange(index + detailRow - 1, 0, 1, sems_num + 2).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
return(prototype);
}
private void exportBtn_Click(object sender, EventArgs e)
{
// 建立範本
Workbook template = new Workbook(new MemoryStream(Properties.Resources.班級課堂點名明細樣板));
// 複製範本
Workbook book = new Workbook();
book.Copy(template);
Worksheet sheet = book.Worksheets[0];
sheet.Name = classLb.Text;
// 填入資料
int row = 1;
Style style = sheet.Cells.GetCellStyle(0, 0);
foreach (DataGridViewRow datarow in dataGridViewX1.Rows)
{
int col = 0;
sheet.Cells[row, col].PutValue(datarow.Cells["teacher"].Value);
sheet.Cells[row, col++].SetStyle(style);
//sheet.Cells[row, col].PutValue(datarow.Cells["course"].Value);
//sheet.Cells[row, col++].SetStyle(style);
sheet.Cells[row, col].PutValue(datarow.Cells["className"].Value);
sheet.Cells[row, col++].SetStyle(style);
sheet.Cells[row, col].PutValue(datarow.Cells["seatNo"].Value);
sheet.Cells[row, col++].SetStyle(style);
sheet.Cells[row, col].PutValue(datarow.Cells["stuNumber"].Value);
sheet.Cells[row, col++].SetStyle(style);
sheet.Cells[row, col].PutValue(datarow.Cells["name"].Value);
sheet.Cells[row, col++].SetStyle(style);
sheet.Cells[row, col].PutValue(datarow.Cells["rollCallLog"].Value);
sheet.Cells[row, col++].SetStyle(style);
sheet.Cells[row, col].PutValue(datarow.Cells["attendance"].Value);
sheet.Cells[row, col++].SetStyle(style);
sheet.Cells[row, col].PutValue(datarow.Cells["rollCallTime"].Value);
sheet.Cells[row, col++].SetStyle(style);
row++;
}
string date = DateTime.Parse(dateTimeLb.Text).ToString("yyyy_MM_dd");
char[] ca = periodLb.Text.ToCharArray();
string period = "";
int i = 0;
foreach (char c in ca)
{
if (c == '/')
{
ca[i] = '_';
}
period += ca[i];
i++;
}
// 存檔
SaveFileDialog SaveFileDialog = new SaveFileDialog();
SaveFileDialog.Filter = "Excel (*.xlsx)|*.xlsx|所有檔案 (*.*)|*.*";
SaveFileDialog.FileName = string.Format("{0}_{1}_{2}_課堂點名", date, classLb.Text, period);
try
{
if (SaveFileDialog.ShowDialog() == DialogResult.OK)
{
book.Save(SaveFileDialog.FileName);
Process.Start(SaveFileDialog.FileName);
MotherForm.SetStatusBarMessage("課堂點名明細,列印完成!!");
}
else
{
FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存");
return;
}
}
catch
{
FISCA.Presentation.Controls.MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!");
MotherForm.SetStatusBarMessage("檔案儲存錯誤,請檢查檔案是否開啟中!!");
}
}
private Workbook FillWorkBookData()
{
Workbook wb = new Workbook();
wb.Copy(wbTemplate);
string schoolName = School.ChineseName;
string schoolYear = School.DefaultSchoolYear;
int sheetIndex = 0;
foreach (string classID in listClassID)
{
if (sheetIndex > 0)
{
wb.Worksheets.Add();
}
int rowIndex = 0;
ClassRecord classRec = Class.SelectByID(classID);
Worksheet sheet = wb.Worksheets[sheetIndex];
sheet.Copy(wbTemplate.Worksheets[0]);
sheet.Name = classRec.Name;
sheet.Cells[rowIndex++, 0].PutValue($"{schoolName} {schoolYear}學年度 {classRec.Name} 修讀學分統計表");
rowIndex = 4;
Range range = sheet.Cells.CreateRange(1, 1, 1, 30);
Style style = sheet.Cells.GetCellStyle(4, 0);
StyleFlag styleFlag = new StyleFlag()
{
All = true
};
// 有班級學生資料
if (dicCreditRecByClassStuKey.ContainsKey(classID))
{
foreach (string stuID in dicCreditRecByClassStuKey[classID].Keys)
{
// 沒有任何核心必修科目
bool nototalCoreCredit = true;
if (studentScoreRuleDict.ContainsKey(stuID))
{
if (ruleCoreSubjectNameDict.ContainsKey(studentScoreRuleDict[stuID]))
{
if (ruleCoreSubjectNameDict[studentScoreRuleDict[stuID]].Count > 0)
{
nototalCoreCredit = false;
}
}
}
int colIndex = 0;
//修讀合計
float totalCredit = 0;
// 累計實得
float totalHaveCredit = 0;
float totalCoreCredit = 0;
float totalHaveToCredit = 0;
float totalSelectedCredit = 0;
Dictionary <string, CreditRec> dicCreditRecByKey = dicCreditRecByClassStuKey[classID][stuID];
// 座號 姓名
if (dicStudentRecByID.ContainsKey(stuID))
{
StudentRec stuRec = dicStudentRecByID[stuID];
sheet.Cells[rowIndex, colIndex++].PutValue(stuRec.SeatNo);
sheet.Cells[rowIndex, colIndex++].PutValue(stuRec.Name);
}
// 成績年級學期學分數
for (int gradeYear = 1; gradeYear <= 3; gradeYear++)
{
for (int semester = 1; semester <= 2; semester++)
{
string key = $"{gradeYear}_{semester}";
if (dicCreditRecByKey.ContainsKey(key))
{
CreditRec creditRec = dicCreditRecByKey[key];
sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.TotalCredit);
if (nototalCoreCredit)
{
sheet.Cells[rowIndex, colIndex++].PutValue("");
}
else
{
sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.CoredCredit);
}
sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.HaveToCredit);
sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.SelectedCredit);
totalCredit += creditRec.TotalCredit;
totalCoreCredit += creditRec.CoredCredit;
totalHaveToCredit += creditRec.HaveToCredit;
totalSelectedCredit += creditRec.SelectedCredit;
totalHaveCredit += (creditRec.HaveToCredit + creditRec.SelectedCredit);
}
else
{
colIndex += 4;
}
}
}
// 累計實得學分
//修讀合計
sheet.Cells[rowIndex, colIndex++].PutValue(totalCredit);
// 核心必修
if (nototalCoreCredit)
{
sheet.Cells[rowIndex, colIndex++].PutValue("");
}
else
{
sheet.Cells[rowIndex, colIndex++].PutValue(totalCoreCredit);
}
// 必修
sheet.Cells[rowIndex, colIndex++].PutValue(totalHaveToCredit);
// 選修
sheet.Cells[rowIndex, colIndex++].PutValue(totalSelectedCredit);
// 合計
sheet.Cells[rowIndex, colIndex++].PutValue(totalHaveCredit);
Range currentRow = sheet.Cells.CreateRange(rowIndex, 0, 1, 30);
currentRow.ApplyStyle(style, styleFlag);
rowIndex++;
}
}
// 沒有班級學生資料
else
{
sheet.Cells.Merge(4, 0, 3, 30);
sheet.Cells[4, 0].PutValue("無資料");
}
sheetIndex++;
}
return(wb);
}
private void wizardPage3_AfterPageDisplayed(object sender, WizardPageChangeEventArgs e)
{
this.progressBarX1.Value = 0;
// 讀取畫面上選取教師狀態
switch (cboStudStatus.Text)
{
case "一般":
_StudStatus = K12.Data.TeacherRecord.TeacherStatus.一般;
break;
//case "休學":
// _StudStatus = K12.Data.StudentRecord.StudentStatus.休學;
// break;
//case "輟學":
// _StudStatus = K12.Data.StudentRecord.StudentStatus.輟學;
// break;
//case "畢業或離校":
// _StudStatus = K12.Data.StudentRecord.StudentStatus.畢業或離校;
// break;
case "刪除":
_StudStatus = K12.Data.TeacherRecord.TeacherStatus.刪除;
break;
}
lblWarningCount.Text = lblErrCount.Text = "0";
this.wizardPage3.FinishButtonEnabled = eWizardButtonState.False;
linkLabel1.Visible = false;
labelX2.Text = "資料驗證中";
linkLabel3.Tag = null;
linkLabel3.Visible = false;
Application.DoEvents();
_BKWValidate = new BackgroundWorker();
_BKWValidate.WorkerReportsProgress = true;
_BKWValidate.WorkerSupportsCancellation = true;
_BKWValidate.DoWork += new DoWorkEventHandler(_BKWValidate_DoWork);
_BKWValidate.ProgressChanged += new ProgressChangedEventHandler(_BKWValidate_ProgressChanged);
_BKWValidate.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_BKWValidate_RunWorkerCompleted);
List<string> fields = new List<string>();
string fileName = txtFile.Text;
fields.AddRange(_RequiredFields);
foreach (ListViewItem item in listView1.Items)
{
if (item.Checked)
fields.Add(item.Text);
}
_ErrorRows = new Dictionary<RowData, Dictionary<string, string>>();
_WarningRows = new Dictionary<RowData, Dictionary<string, string>>();
Workbook wb = new Workbook();
wb.Copy(_WorkBook);
_BKWValidate.RunWorkerAsync(new object[] { fields, _ImportFields, wb });
}
private void _BWRepeatScoreImport_DoWork(object sender, DoWorkEventArgs e)
{
object[] obj = (object[])e.Argument;
int schoolyear = (int)obj[0];
int semester = (int)obj[1];
bool printAll = (bool)obj[2];
_BWRepeatScoreImport.ReportProgress(0);
#region 取得所有學生以及補考資訊
AccessHelper helper = new AccessHelper();
List <SmartSchool.Customization.Data.StudentRecord> allStudents = new List <SmartSchool.Customization.Data.StudentRecord>();
List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass();
double currentClass = 1;
double totalClasses = allClasses.Count;
foreach (ClassRecord aClass in allClasses)
{
List <SmartSchool.Customization.Data.StudentRecord> classStudents = aClass.Students;
helper.StudentHelper.FillSemesterSubjectScore(true, classStudents);
helper.StudentHelper.FillField("及格標準", classStudents);
allStudents.AddRange(classStudents);
_BWRepeatScoreImport.ReportProgress((int)(currentClass++ *90.0 / totalClasses));
}
double currentStudent = 1;
double totalStudents = allStudents.Count;
#endregion
#region 產生表格並填入資料
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.重修成績匯入), FileFormatType.Excel2003);
Workbook wb = new Workbook();
wb.Copy(template);
Worksheet ws = wb.Worksheets[0];
Dictionary <string, int> columnIndexTable = new Dictionary <string, int>();
int headerIndex = 0;
string headerString = template.Worksheets[0].Cells[0, headerIndex].StringValue;
while (!string.IsNullOrEmpty(headerString))
{
columnIndexTable.Add(headerString, headerIndex);
headerString = template.Worksheets[0].Cells[0, ++headerIndex].StringValue;
}
foreach (SmartSchool.Customization.Data.StudentRecord aStudent in allStudents)
{
foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList)
{
if ((info.SchoolYear == schoolyear && info.Semester == semester) || printAll)
{
if (info.Pass && machine.IsExistInList(aStudent.StudentNumber, aStudent.StudentName, info.Subject, info.Level))
{
machine.RemoveSubject(aStudent.StudentName, info.Subject, info.Level);
}
else if (!info.Pass)
{
machine.AddSubject(aStudent, info);
}
}
}
_BWRepeatScoreImport.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents));
}
machine.Sort();
int index = 1;
foreach (Dictionary <string, string> info in machine.GetSubjects())
{
foreach (string key in info.Keys)
{
ws.Cells[index, columnIndexTable[key]].PutValue(info[key]);
}
index++;
}
#endregion
e.Result = wb;
}
void _BGWResitList_DoWork(object sender, DoWorkEventArgs e)
{
object[] objectValue = (object[])e.Argument;
int schoolyear = (int)objectValue[0];
int semester = (int)objectValue[1];
_BGWResitList.ReportProgress(0);
#region 取得所有學生以及補考資訊
AccessHelper helper = new AccessHelper();
List <StudentRecord> allStudents = new List <StudentRecord>();
List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass();
WearyDogComputer computer = new WearyDogComputer();
double currentClass = 1;
double totalClasses = allClasses.Count;
foreach (ClassRecord aClass in allClasses)
{
List <StudentRecord> classStudents = aClass.Students;
computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents);
allStudents.AddRange(classStudents);
_BGWResitList.ReportProgress((int)(currentClass++ *90.0 / totalClasses));
}
double currentStudent = 1;
double totalStudents = allStudents.Count;
#endregion
#region 產生表格並填入資料
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.補考名單_依科目), FileFormatType.Excel2003);
Workbook wb = new Workbook();
wb.Copy(template);
Worksheet ws = wb.Worksheets[0];
ws.Name = "未達補考標準名單";
Range eachSubject = template.Worksheets[0].Cells.CreateRange(0, 4, false);
Range eachRow = template.Worksheets[0].Cells.CreateRange(4, 1, false);
int index = 0;
Dictionary <string, Dictionary <string, string> > subjectInfo = new Dictionary <string, Dictionary <string, string> >();
Dictionary <string, List <Dictionary <string, string> > > subjectStudentList = new Dictionary <string, List <Dictionary <string, string> > >();
foreach (StudentRecord aStudent in allStudents)
{
string className = aStudent.RefClass.ClassName;
string seatNo = aStudent.SeatNo;
string studentName = aStudent.StudentName;
string studentNumber = aStudent.StudentNumber;
//aStudent.SemesterSubjectScoreList.Sort(SortBySemesterSubjectScore);
foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList)
{
if (info.SchoolYear == schoolyear && info.Semester == semester && !info.Pass)
{
if (info.Detail.GetAttribute("達補考標準") == "否")
{
string sl = info.Subject + "_" + info.Level + "_" + info.CreditDec();
if (!subjectInfo.ContainsKey(sl))
{
subjectInfo.Add(sl, new Dictionary <string, string>());
subjectInfo[sl].Add("科目", info.Subject);
subjectInfo[sl].Add("級別", info.Level);
subjectInfo[sl].Add("學分", info.CreditDec().ToString());
}
if (!subjectStudentList.ContainsKey(sl))
{
subjectStudentList.Add(sl, new List <Dictionary <string, string> >());
}
Dictionary <string, string> data = new Dictionary <string, string>();
data.Add("班級", className);
data.Add("座號", seatNo);
data.Add("姓名", studentName);
data.Add("學號", studentNumber);
data.Add("必選修", info.Require ? "必修" : "選修");
data.Add("校部訂", info.Detail.HasAttribute("修課校部訂") ? info.Detail.GetAttribute("修課校部訂") : "");
data.Add("補考標準", info.Detail.HasAttribute("補考標準") ? info.Detail.GetAttribute("補考標準") : "");
data.Add("原始成績", info.Detail.HasAttribute("原始成績") ? info.Detail.GetAttribute("原始成績") : "");
subjectStudentList[sl].Add(data);
}
}
}
_BGWResitList.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents));
}
List <string> sortedList = new List <string>();
sortedList.AddRange(subjectStudentList.Keys);
sortedList.Sort(SortBySemesterSubjectScore);
foreach (string key in sortedList)
{
int level;
string levelString = "";
if (!string.IsNullOrEmpty(subjectInfo[key]["級別"]) && int.TryParse(subjectInfo[key]["級別"], out level))
{
levelString = GetNumber(level);
}
string sl = subjectInfo[key]["科目"] + levelString;
ws.Cells.CreateRange(index, 4, false).Copy(eachSubject);
ws.Cells[index, 0].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 第 " + semester + " 學期 學生未達補考標準名單");
ws.Cells[index + 1, 2].PutValue(sl);
ws.Cells[index + 1, 7].PutValue(subjectInfo[key]["學分"]);
index += 4;
foreach (Dictionary <string, string> dict in subjectStudentList[key])
{
ws.Cells.CreateRange(index, 1, false).Copy(eachRow);
ws.Cells[index, 0].PutValue(dict["班級"]);
ws.Cells[index, 1].PutValue(dict["座號"]);
ws.Cells[index, 2].PutValue(dict["姓名"]);
ws.Cells[index, 3].PutValue(dict["學號"]);
ws.Cells[index, 4].PutValue(dict["必選修"]);
ws.Cells[index, 5].PutValue(dict["校部訂"]);
ws.Cells[index, 6].PutValue(dict["補考標準"]);
ws.Cells[index, 7].PutValue(dict["原始成績"]);
index++;
}
index++;
}
#endregion
e.Result = wb;
}
private Workbook FillWorkBookData(ParameterRec data)
{
Workbook prototype = new Workbook();
prototype.Copy(wbTemplate);
Worksheet ps = prototype.Worksheets[0];
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
Range colRange = ps.Cells.CreateRange(2, 1, 1, 1);
Range rowRange = ps.Cells.CreateRange(3, 1, false);
int rowIndex = 0;
int colIndex = 0;
int domainCount = listDomainFromData.Count;
string title = cbxIsSchoolYear.Checked ? $"{data.SchoolYear}學年度 領域不及格人數統計表"
: $"{data.SchoolYear}學年度 {data.Semester}學期 領域不及格人數統計表";
ws.Cells.Merge(rowIndex, 0, 1, domainCount * 2);
ws.Cells[rowIndex++, 0].PutValue(title);
// 各學習領域學生成績評量情形
{
ws.Cells.Merge(1, 1, 1, domainCount);
Range range = ws.Cells.CreateRange(1, 1, 1, domainCount);
range.SetOutlineBorders(CellBorderType.Thin, Color.Black);
range.PutValue("各學習領域學生成績評量情形", false, false);
Cell cell = ws.Cells.GetCell(1, 1);
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
cell.SetStyle(style);
}
// 學生成績評量不及格領域數情形
{
ws.Cells.Merge(1, domainCount + 1, 1, domainCount);
Range range = ws.Cells.CreateRange(1, domainCount + 1, 1, domainCount);
range.SetOutlineBorders(CellBorderType.Thin, Color.Black);
range.PutValue("學生成績評量不及格領域數情形", false, false);
Cell cell = ws.Cells.GetCell(1, domainCount + 1);
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
cell.SetStyle(style);
}
rowIndex++;
colIndex = 1;
// 領域
foreach (string domain in listDomainFromData)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
range.ColumnWidth = colRange.ColumnWidth;
ws.Cells[rowIndex, colIndex++].PutValue(domain);
}
// 不及格數
for (int i = 1; i <= listDomainFromData.Count; i++)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
range.ColumnWidth = colRange.ColumnWidth;
ws.Cells[rowIndex, colIndex++].PutValue($"{i}個學習領域不及格人數");
}
rowIndex++;
// 年級
foreach (string gradeYear in listGradeYear)
{
colIndex = 0;
if (rowIndex > 3)
{
ws.Cells.InsertRow(rowIndex);
}
ws.Cells.CreateRange(rowIndex, 1, false).CopyStyle(rowRange);
ws.Cells[rowIndex, colIndex++].PutValue($"{gradeYear}年級");
// 領域不及格人數
foreach (string domain in listDomainFromData)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
if (dicUnPassCountByDomainGradeYear[gradeYear].ContainsKey(domain))
{
int unPassCount = dicUnPassCountByDomainGradeYear[gradeYear][domain];
ws.Cells[rowIndex, colIndex++].PutValue(unPassCount);
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(0);
}
}
// 不及格領域人數
for (int i = 1; i <= listDomainFromData.Count; i++)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
if (dicUnPassCountByUnPassGradeYear.ContainsKey(gradeYear))
{
if (dicUnPassCountByUnPassGradeYear[gradeYear].ContainsKey(i))
{
int unPassCount = dicUnPassCountByUnPassGradeYear[gradeYear][i];
ws.Cells[rowIndex, colIndex++].PutValue(unPassCount);
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(0);
}
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(0);
}
}
rowIndex++;
}
colIndex = 1;
// 總計 領域
foreach (string domain in listDomainFromData)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
ws.Cells[rowIndex, colIndex++].PutValue(dicTotalCountByDomain[domain]);
}
// 總計 不及格領域數
foreach (int i in dicTotalCountByUnPass.Keys)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
ws.Cells[rowIndex, colIndex++].PutValue(dicTotalCountByUnPass[i]);
}
rowIndex++;
int year = DateTime.Now.Year - 1911;
int month = DateTime.Now.Month;
int day = DateTime.Now.Day;
ws.Cells[rowIndex, 0].PutValue($"列印日期: {year}年{month}月{day}日");
return(wb);
}
