/// <summary>
/// 由班級系統編號
/// 取得學生清單
/// </summary>
private Dictionary <string, StudRecord> GetClassStudent(List <string> classIDList)
{
//功過換算表
reduceRecord = K12.Data.MeritDemeritReduce.Select();
//由班級ID取得學生系統編號與資料
DataTable dt = tool._Q.Select(string.Format(@"select class.id as class_id,class.class_name,
student.id as student_id,student.name as student_name,student.student_number,
student.seat_no,class.display_order,class.grade_year,teacher.id as teacher_id
from class
join student on student.ref_class_id=class.id
left join teacher on class.ref_teacher_id=teacher.id
where class.id in ({0}) and student.status in (1 , 2)
order by class.grade_year,class.display_order,class.class_name,student.seat_no,student.name", string.Join(",", classIDList)));
Dictionary <string, StudRecord> dic = new Dictionary <string, StudRecord>();
foreach (DataRow row in dt.Rows)
{
string student_id = "" + row["student_id"];
if (!dic.ContainsKey(student_id))
{
dic.Add(student_id, new StudRecord(row, _SchoolYear, _Semester, reduceRecord));
}
}
return(dic);
}
public void Dispose()
{
_PersonalMeritRecordInfo.GetEnumerator().Dispose();
_PersonalDemeritRecordInfo.GetEnumerator().Dispose();
_MeritDemeritReduceRecord = null;
GC.SuppressFinalize(this);
}
public MeritDeMeritInfo(List <SHStudentRecord> pStudents)
{
_PersonalMeritRecordInfo = new Dictionary <string, List <MeritRecord> >();
_PersonalDemeritRecordInfo = new Dictionary <string, List <DemeritRecord> >();
_MeritDemeritReduceRecord = MeritDemeritReduce.Select();
_DemeritAToDemeritB = _MeritDemeritReduceRecord.DemeritAToDemeritB.HasValue ? _MeritDemeritReduceRecord.DemeritAToDemeritB.Value : 0;
_DemeritBToDemeritC = _MeritDemeritReduceRecord.DemeritBToDemeritC.HasValue ? _MeritDemeritReduceRecord.DemeritBToDemeritC.Value : 0;
_MeritAToMeritB = _MeritDemeritReduceRecord.MeritAToMeritB.HasValue ? _MeritDemeritReduceRecord.MeritAToMeritB.Value : 0;
_MeritBToMeritC = _MeritDemeritReduceRecord.MeritBToMeritC.HasValue ? _MeritDemeritReduceRecord.MeritBToMeritC.Value : 0;
ProduceData(pStudents);
}
public StudRecord(DataRow row, string school_year, string semester, MeritDemeritReduceRecord reduceRecord)
{
_reduceRecord = reduceRecord;
if (_reduceRecord.MeritAToMeritB.HasValue)
{
MeritAtoB = _reduceRecord.MeritAToMeritB.Value;
}
if (_reduceRecord.MeritBToMeritC.HasValue)
{
MeritBtoC = _reduceRecord.MeritBToMeritC.Value;
}
if (_reduceRecord.DemeritAToDemeritB.HasValue)
{
DemeritAtoB = _reduceRecord.DemeritAToDemeritB.Value;
}
if (_reduceRecord.DemeritBToDemeritC.HasValue)
{
DemeritBtoC = _reduceRecord.DemeritBToDemeritC.Value;
}
int.TryParse(school_year, out _school_year);
int.TryParse(semester, out _semester);
StudentID = "" + row["student_id"];
StudentName = "" + row["student_name"];
SeatNo = "" + row["seat_no"];
StudentNumber = "" + row["student_number"];
ClassID = "" + row["class_id"];
ClassName = "" + row["class_name"];
ClassNumber = "" + row["display_order"];
GradeYear = "" + row["grade_year"];
TeacherID = "" + row["teacher_id"];
AttendanceList = new List <AttendanceRecord>();
MeritList = new List <MeritRecord>();
DemeritList = new List <DemeritRecord>();
}
private void DataBuilding(object sender, DoWorkEventArgs e)
{
_BW.ReportProgress(0);
//班級資料
List <ClassRecord> classRecords = K12.Data.Class.SelectByIDs(_Classes);
Dictionary <string, ClassRecord> classCatch = new Dictionary <string, ClassRecord>();
Dictionary <string, StudentObj> Students = new Dictionary <string, StudentObj>();
//建立班級快取並展開學生資料
_BW.ReportProgress(10);
foreach (ClassRecord record in classRecords)
{
string id = record.ID;
if (!classCatch.ContainsKey(id))
{
classCatch.Add(id, record);
}
foreach (StudentRecord student in record.Students)
{
if (student.Status == StudentRecord.StudentStatus.一般 || student.Status == StudentRecord.StudentStatus.延修)
{
string sid = student.ID;
if (!Students.ContainsKey(sid))
{
Students.Add(sid, new StudentObj());
}
Students[sid].Id = student.ID;
Students[sid].Name = student.Name;
Students[sid].SeatNo = student.SeatNo.HasValue ? student.SeatNo.ToString() : "";
Students[sid].StudentNo = student.StudentNumber;
if (classCatch.ContainsKey(student.RefClassID))
{
Students[sid].ClassName = classCatch[student.RefClassID].Name;
Students[sid].Grade = classCatch[student.RefClassID].GradeYear.HasValue ? classCatch[student.RefClassID].GradeYear.Value.ToString() : "";
Students[sid].DisplayOrder = classCatch[student.RefClassID].DisplayOrder;
}
}
}
}
//獎懲紀錄
_BW.ReportProgress(20);
List <DisciplineRecord> disciplineRecords = Discipline.SelectByStudentIDs(Students.Keys);
foreach (DisciplineRecord dr in disciplineRecords)
{
if (dr.SchoolYear != _SchoolYear)
{
continue;
}
if (_Semester == 1)
{
if (dr.Semester != 1)
{
continue;
}
}
string id = dr.RefStudentID;
Students[id].MeritA += dr.MeritA.HasValue ? dr.MeritA.Value : 0;
Students[id].MeritB += dr.MeritB.HasValue ? dr.MeritB.Value : 0;
Students[id].MeritC += dr.MeritC.HasValue ? dr.MeritC.Value : 0;
if (dr.Cleared != "是")
{
Students[id].DemeritA += dr.DemeritA.HasValue ? dr.DemeritA.Value : 0;
Students[id].DemeritB += dr.DemeritB.HasValue ? dr.DemeritB.Value : 0;
Students[id].DemeritC += dr.DemeritC.HasValue ? dr.DemeritC.Value : 0;
}
}
//功過換算
_BW.ReportProgress(30);
MeritDemeritReduceRecord mdrr = MeritDemeritReduce.Select();
int MAB = mdrr.MeritAToMeritB.HasValue ? mdrr.MeritAToMeritB.Value : 0;
int MBC = mdrr.MeritBToMeritC.HasValue ? mdrr.MeritBToMeritC.Value : 0;
int DAB = mdrr.DemeritAToDemeritB.HasValue ? mdrr.DemeritAToDemeritB.Value : 0;
int DBC = mdrr.DemeritBToDemeritC.HasValue ? mdrr.DemeritBToDemeritC.Value : 0;
foreach (StudentObj obj in Students.Values)
{
int merit = ((obj.MeritA * MAB) + obj.MeritB) * MBC + obj.MeritC;
int demerit = ((obj.DemeritA * DAB) + obj.DemeritB) * DBC + obj.DemeritC;
int total = merit - demerit;
if (total < 0)
{
total *= -1;
obj.Result = total;
}
}
//資料排序
_BW.ReportProgress(40);
List <StudentObj> PrintList = Students.Values.ToList();
PrintList.Sort(SortObj);
//預備填入資料
_BW.ReportProgress(50);
Workbook wb = new Workbook(new MemoryStream(Properties.Resources.Template));
Cells cs = wb.Worksheets[0].Cells;
cs[0, 0].PutValue(_SchoolName + " (" + _SchoolYear + "/" + _Semester + ") " + _ReportName);
//開始填入
decimal per = (decimal)50 / PrintList.Count;
int count = 0;
int row = 2;
foreach (StudentObj obj in PrintList)
{
if (obj.Result >= _StandardValue)
{
cs[row, 0].PutValue(obj.ClassName);
cs[row, 1].PutValue(obj.SeatNo);
cs[row, 2].PutValue(obj.Name);
cs[row, 3].PutValue(obj.StudentNo);
cs[row, 4].PutValue(obj.DemeritA);
cs[row, 5].PutValue(obj.DemeritB);
cs[row, 6].PutValue(obj.DemeritC);
cs[row, 7].PutValue(obj.MeritA);
cs[row, 8].PutValue(obj.MeritB);
cs[row, 9].PutValue(obj.MeritC);
cs[row, 10].PutValue(obj.Result);
row++;
}
_BW.ReportProgress((int)per * count + 50);
count++;
}
_BW.ReportProgress(100);
e.Result = wb;
}
public Report()
{
InitializeComponent();
Column2Prepare();
報名資格 = new Dictionary <string, int>();
報名費減免身分 = new Dictionary <string, int>();
弱勢身分 = new Dictionary <string, int>();
特種生加分類別 = new Dictionary <string, int>();
其他比序項目_全民英檢 = new Dictionary <string, int>();
//其他比序項目_多益測驗 = new Dictionary<string, int>();
// 2018/05/15 穎驊 新增 統計資料截止時間以利產生正確的範圍時間內資料
// 預設為顯示今天
dateTimeInput1.Value = DateTime.Now;
報名資格.Add("國民中學非應屆畢業生", 0);
報名資格.Add("同等學歷", 2);
報名費減免身分.Add("低收入戶", 1);
報名費減免身分.Add("失業子女", 2);
報名費減免身分.Add("中低收入戶", 3);
弱勢身分.Add("低收入戶", 1);
弱勢身分.Add("失業子女", 2);
弱勢身分.Add("中低收入戶", 3);
弱勢身分.Add("特殊境遇家庭", 4);
特種生加分類別.Add("原住民-未取得原住民文化及語言能力證明", 1);
特種生加分類別.Add("原住民-取得原住民文化及語言能力證明", 2);
特種生加分類別.Add("境外-返國(來臺)就讀未滿一學年", 3);
特種生加分類別.Add("境外-返國(來臺)就讀一學年以上未滿二學年", 4);
特種生加分類別.Add("境外-返國(來臺)就讀二學年以上未滿三學年", 5);
特種生加分類別.Add("派外-返國(來臺)就讀未滿一學年", 6);
特種生加分類別.Add("派外-返國(來臺)就讀一學年以上未滿二學年", 7);
特種生加分類別.Add("派外-返國(來臺)就讀二學年以上未滿三學年", 8);
特種生加分類別.Add("蒙藏生", 9);
特種生加分類別.Add("身障生", 10);
特種生加分類別.Add("僑生", 11);
特種生加分類別.Add("退伍軍人-在營服役期間五年以上，退伍後未滿一年", 12);
特種生加分類別.Add("退伍軍人-在營服役期間五年以上，退伍後一年以上未滿二年", 13);
特種生加分類別.Add("退伍軍人-在營服役期間五年以上，退伍後二年以上未滿三年", 14);
特種生加分類別.Add("退伍軍人-在營服役期間五年以上，退伍後三年以上未滿五年", 15);
特種生加分類別.Add("退伍軍人-在營服役期間四年以上未滿五年，退伍後未滿一年", 16);
特種生加分類別.Add("退伍軍人-在營服役期間四年以上未滿五年，退伍後一年以上未滿二年", 17);
特種生加分類別.Add("退伍軍人-在營服役期間四年以上未滿五年，退伍後二年以上未滿三年", 18);
特種生加分類別.Add("退伍軍人-在營服役期間四年以上未滿五年，退伍後三年以上未滿五年", 19);
特種生加分類別.Add("退伍軍人-在營服役期間三年以上未滿四年，退伍後未滿一年", 20);
特種生加分類別.Add("退伍軍人-在營服役期間三年以上未滿四年，退伍後一年以上未滿二年", 21);
特種生加分類別.Add("退伍軍人-在營服役期間三年以上未滿四年，退伍後二年以上未滿三年", 22);
特種生加分類別.Add("退伍軍人-在營服役期間三年以上未滿四年，退伍後三年以上未滿五年", 23);
特種生加分類別.Add("退伍軍人-在營服役期間未滿三年，已達義務役法定役期，且退伍後未滿三年", 24);
特種生加分類別.Add("退伍軍人-因作戰或因公成殘領有撫卹證明，於免役、除役後未滿五年", 25);
特種生加分類別.Add("退伍軍人-因病成殘領有撫卹證明，於免役、除役後未滿五年", 26);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 初級 初試及格", 1);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 初級 複試及格", 2);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 中級 初試及格", 3);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 中級 複試及格", 4);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 中高級 初試及格", 5);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 中高級 複試及格", 6);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 高級 初試及格", 7);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 高級 複試及格", 8);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 優級 初試及格", 9);
其他比序項目_全民英檢.Add("全民英語能力分級檢定測驗 GEPT 優級 複試及格", 10);
//其他比序項目_多益測驗.Add("多益測驗 (TOEIC) 聽力 110 以上 閱讀 115 以上", 1);
//其他比序項目_多益測驗.Add("多益測驗 (TOEIC) 聽力 275 以上 閱讀 275 以上", 2);
//其他比序項目_多益測驗.Add("多益測驗 (TOEIC) 聽力 400 以上 閱讀 385 以上", 3);
_SchoolName = K12.Data.School.ChineseName;
_BW = new BackgroundWorker();
_BW.WorkerReportsProgress = true;
_BW.DoWork += new DoWorkEventHandler(DataBuilding);
_BW.RunWorkerCompleted += new RunWorkerCompletedEventHandler(ReportBuilding);
_BW.ProgressChanged += new ProgressChangedEventHandler(BW_Progress);
//取得功過換算比例
MeritDemeritReduceRecord mdrr = MeritDemeritReduce.Select();
MAB = mdrr.MeritAToMeritB.HasValue ? mdrr.MeritAToMeritB.Value : 0;
MBC = mdrr.MeritBToMeritC.HasValue ? mdrr.MeritBToMeritC.Value : 0;
DAB = mdrr.DemeritAToDemeritB.HasValue ? mdrr.DemeritAToDemeritB.Value : 0;
DBC = mdrr.DemeritBToDemeritC.HasValue ? mdrr.DemeritBToDemeritC.Value : 0;
}
public Dictionary <string, int> GetMerit(List <一名學生> StudentList)
{
//取得學生ID
StudentDic = GetStudentIDList(StudentList);
//取得獎懲資料
List <MeritRecord> MeritList = Merit.SelectByStudentIDs(StudentDic.Keys);
List <DemeritRecord> DemeritList = Demerit.SelectByStudentIDs(StudentDic.Keys);
//取得功過相抵換算值
_Reduce = K12.Data.MeritDemeritReduce.Select();
foreach (MeritRecord mr in MeritList)
{
if (StudentDic.ContainsKey(mr.RefStudentID))
{
int studINT = 0;
int a = mr.MeritA.HasValue ? mr.MeritA.Value : 0;
int b = mr.MeritB.HasValue ? mr.MeritB.Value : 0;
int c = mr.MeritC.HasValue ? mr.MeritC.Value : 0;
if (_Reduce.MeritAToMeritB.HasValue)
{
int aa = a * _Reduce.MeritAToMeritB.Value;
studINT += aa * _Reduce.MeritBToMeritC.Value;
}
if (_Reduce.MeritBToMeritC.HasValue)
{
studINT += b * _Reduce.MeritBToMeritC.Value;
}
studINT += c;
StudentDic[mr.RefStudentID] += studINT;
}
}
foreach (DemeritRecord mr in DemeritList)
{
if (mr.Cleared == "是") //銷過資料則離開
{
continue;
}
if (mr.MeritFlag == "2") //留查資料則離開
{
continue;
}
if (StudentDic.ContainsKey(mr.RefStudentID))
{
int studINT = 0;
int a = mr.DemeritA.HasValue ? mr.DemeritA.Value : 0;
int b = mr.DemeritB.HasValue ? mr.DemeritB.Value : 0;
int c = mr.DemeritC.HasValue ? mr.DemeritC.Value : 0;
if (_Reduce.MeritAToMeritB.HasValue)
{
int aa = a * _Reduce.MeritAToMeritB.Value;
studINT += aa * _Reduce.MeritBToMeritC.Value;
}
if (_Reduce.MeritBToMeritC.HasValue)
{
studINT += b * _Reduce.MeritBToMeritC.Value;
}
studINT += c;
StudentDic[mr.RefStudentID] -= studINT;
}
}
return(StudentDic);
}
private void DataBuilding(object sender, DoWorkEventArgs e)
{
//取得班級物件對照
_BW.ReportProgress(0);
List <ClassRecord> classes = K12.Data.Class.SelectAll();
_ClassCatch = new Dictionary <string, ClassRecord>();
foreach (ClassRecord record in classes)
{
if (!_ClassCatch.ContainsKey(record.ID))
{
_ClassCatch.Add(record.ID, record);
}
}
//查詢指定學年度的獎懲紀錄
_BW.ReportProgress(10);
Dictionary <string, StudentObj> StudentDic = new Dictionary <string, StudentObj>();
DataTable dt = _Q.Select("SELECT school_year,ref_student_id,merit_flag,xpath_string(detail,'/Discipline/Merit/@A') as merita,xpath_string(detail,'/Discipline/Merit/@B') as meritb,xpath_string(detail,'/Discipline/Merit/@C') as meritc,xpath_string(detail,'/Discipline/Demerit/@A') as demerita,xpath_string(detail,'/Discipline/Demerit/@B') as demeritb,xpath_string(detail,'/Discipline/Demerit/@C') as demeritc,xpath_string(detail,'/Discipline/Demerit/@Cleared') as cleared FROM discipline WHERE school_year=" + _SchoolYear);
foreach (DataRow drow in dt.Rows)
{
string sid = drow["ref_student_id"].ToString();
if (!StudentDic.ContainsKey(sid))
{
StudentDic.Add(sid, new StudentObj(sid));
}
int x = 0;
int merita = int.TryParse(drow["merita"].ToString(), out x) ? x : 0;
int meritb = int.TryParse(drow["meritb"].ToString(), out x) ? x : 0;
int meritc = int.TryParse(drow["meritc"].ToString(), out x) ? x : 0;
int demerita = int.TryParse(drow["demerita"].ToString(), out x) ? x : 0;
int demeritb = int.TryParse(drow["demeritb"].ToString(), out x) ? x : 0;
int demeritc = int.TryParse(drow["demeritc"].ToString(), out x) ? x : 0;
string cleared = drow["cleared"].ToString();
string flag = drow["merit_flag"].ToString();
StudentDic[sid].MeritA += merita;
StudentDic[sid].MeritB += meritb;
StudentDic[sid].MeritC += meritc;
if (cleared != "是")
{
StudentDic[sid].DemeritA += demerita;
StudentDic[sid].DemeritB += demeritb;
StudentDic[sid].DemeritC += demeritc;
}
if (flag == "2")
{
StudentDic[sid].留察 = true;
}
}
//功過換算
_BW.ReportProgress(20);
MeritDemeritReduceRecord mdrr = MeritDemeritReduce.Select();
int MAB = mdrr.MeritAToMeritB.HasValue ? mdrr.MeritAToMeritB.Value : 0;
int MBC = mdrr.MeritBToMeritC.HasValue ? mdrr.MeritBToMeritC.Value : 0;
int DAB = mdrr.DemeritAToDemeritB.HasValue ? mdrr.DemeritAToDemeritB.Value : 0;
int DBC = mdrr.DemeritBToDemeritC.HasValue ? mdrr.DemeritBToDemeritC.Value : 0;
foreach (StudentObj obj in StudentDic.Values)
{
int merit = ((obj.MeritA * MAB) + obj.MeritB) * MBC + obj.MeritC;
int demerit = ((obj.DemeritA * DAB) + obj.DemeritB) * DBC + obj.DemeritC;
int total = merit - demerit;
if (total > 0)
{
obj.MC = total % MBC;
obj.MB = (total / MBC) % MAB;
obj.MA = (total / MBC) / MAB;
}
else if (total < 0)
{
total *= -1;
obj.DC = total % DBC;
obj.DB = (total / DBC) % DAB;
obj.DA = (total / DBC) / DAB;
}
}
//取得累積大過滿2次以上的學生
_BW.ReportProgress(30);
Dictionary <string, StudentObj> NewStudentDic = new Dictionary <string, StudentObj>();
foreach (StudentObj obj in StudentDic.Values)
{
if (obj.DA >= _StandardValue)
{
NewStudentDic.Add(obj.Id, obj);
}
}
//取得學生資料
_BW.ReportProgress(40);
List <string> ids = NewStudentDic.Keys.ToList();
List <StudentRecord> studentRecords = K12.Data.Student.SelectByIDs(ids);
foreach (StudentRecord record in studentRecords)
{
if (NewStudentDic.ContainsKey(record.ID))
{
NewStudentDic[record.ID].StudentNo = record.StudentNumber;
NewStudentDic[record.ID].Name = record.Name;
NewStudentDic[record.ID].SeatNo = record.SeatNo.HasValue ? record.SeatNo.Value.ToString() : "";
string classId = record.RefClassID;
NewStudentDic[record.ID].ClassName = _ClassCatch.ContainsKey(classId) ? _ClassCatch[classId].Name : "";
NewStudentDic[record.ID].Grade = _ClassCatch.ContainsKey(classId) ? _ClassCatch[classId].GradeYear.ToString() : "";
NewStudentDic[record.ID].DisplayOrder = _ClassCatch.ContainsKey(classId) ? _ClassCatch[classId].DisplayOrder : "";
NewStudentDic[record.ID].Status = record.Status.ToString();
}
}
//列印
_BW.ReportProgress(50);
Workbook wb = new Workbook(new MemoryStream(Properties.Resources.Template));
Worksheet ws = wb.Worksheets[0];
Cells cs = ws.Cells;
//需動態新增的框線
_BW.ReportProgress(60);
Range eachLine = cs.CreateRange(3, 1, false);
//Report Title
_BW.ReportProgress(70);
cs[0, 0].PutValue(_SchoolName + " " + _ReportName);
//資料排序
_BW.ReportProgress(80);
List <StudentObj> DataList = NewStudentDic.Values.ToList();
DataList.Sort(DataListSort);
int row = 2;
foreach (StudentObj obj in DataList)
{
if (obj.Status == "一般" || obj.Status == "延修")
{
cs.CreateRange(row + 1, 1, false).CopyStyle(eachLine);
cs[row, 0].PutValue(obj.StudentNo);
cs[row, 1].PutValue(obj.ClassName);
cs[row, 2].PutValue(obj.SeatNo);
cs[row, 3].PutValue(obj.Name);
cs[row, 4].PutValue(obj.留察 ? "Y" : "");
cs[row, 5].PutValue(obj.MA == 0 ? "" : obj.MA.ToString());
cs[row, 6].PutValue(obj.MB == 0 ? "" : obj.MB.ToString());
cs[row, 7].PutValue(obj.MC == 0 ? "" : obj.MC.ToString());
cs[row, 8].PutValue(obj.DA == 0 ? "" : obj.DA.ToString());
cs[row, 9].PutValue(obj.DB == 0 ? "" : obj.DB.ToString());
cs[row, 10].PutValue(obj.DC == 0 ? "" : obj.DC.ToString());
row++;
}
}
e.Result = wb;
_BW.ReportProgress(100);
}
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
}