public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
int RowCount = 0;
List <DAO.UDT_CounselUserDefDataDef> CounselUserDefDataList = _UDTTransfer.GetCounselUserDefineDataByStudentIDList(e.List);
foreach (DAO.UDT_CounselUserDefDataDef udd in CounselUserDefDataList)
{
RowData row = new RowData();
row.ID = udd.StudentID.ToString();
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "欄位名稱": row.Add(field, udd.FieldName); break;
case "值": row.Add(field, udd.Value); break;
case "狀態":
row.Add(field, udd.StudentStatus);
break;
}
}
}
RowCount++;
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
int RowCount = 0;
foreach (DAL.UserDefData udd in UDTTransfer.GetDataFromUDT(e.List))
{
RowData row = new RowData();
row.ID = udd.RefID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "欄位名稱": row.Add(field, udd.FieldName); break;
case "值": row.Add(field, udd.Value); break;
}
}
}
RowCount++;
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
int RowCount = 0;
List<DAO.UDT_CounselUserDefDataDef> CounselUserDefDataList = _UDTTransfer.GetCounselUserDefineDataByStudentIDList(e.List);
foreach (DAO.UDT_CounselUserDefDataDef udd in CounselUserDefDataList)
{
RowData row = new RowData();
row.ID = udd.StudentID.ToString ();
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "欄位名稱": row.Add(field, udd.FieldName); break;
case "值": row.Add(field, udd.Value); break;
case "狀態":
row.Add(field, udd.StudentStatus);
break;
}
}
}
RowCount++;
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
int RowCount = 0;
foreach (DAL.UserDefData udd in UDTTransfer.GetDataFromUDT(e.List))
{
RowData row = new RowData();
row.ID = udd.RefID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "欄位名稱": row.Add(field, udd.FieldName); break;
case "值": row.Add(field, udd.Value); break;
}
}
}
RowCount++;
e.Items.Add(row);
}
};
}
//覆寫
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
List <School> GraduateList = tool._A.Select <School>(Program.MainPanel.SelectedSource);
wizard.ExportableFields.AddRange("學校系統編號", "學校名稱", "DSNS", "群組", "註解");
wizard.ExportPackage += (sender, e) =>
{
for (int i = 0; i < GraduateList.Count; i++)
{
RowData row = new RowData();
row.ID = GraduateList[i].UID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學校系統編號": row.Add(field, "" + GraduateList[i].UID); break;
case "學校名稱": row.Add(field, "" + GraduateList[i].Title); break;
case "DSNS": row.Add(field, "" + GraduateList[i].DSNS); break;
case "群組": row.Add(field, "" + GraduateList[i].Group); break;
case "註解": row.Add(field, "" + GraduateList[i].Comment); break;
}
}
}
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 學生類別組合
Dictionary<string, StudentTagEntity> StudTagDict = new Dictionary<string, StudentTagEntity>();
// 取得學生類別
foreach (StudentTagRecord studTagRec in StudentTag.SelectByStudentIDs(e.List))
{
if (StudTagDict.ContainsKey(studTagRec.RefStudentID))
StudTagDict[studTagRec.RefStudentID].AddPrefixName(studTagRec.Prefix, studTagRec.Name);
else
{
StudentTagEntity stn = new StudentTagEntity();
stn.StudentID = studTagRec.RefStudentID;
stn.AddPrefixName(studTagRec.Prefix, studTagRec.Name);
StudTagDict.Add(studTagRec.RefStudentID, stn);
}
}
// 讀取組合後的學生類別
foreach (StudentTagEntity ste in StudTagDict.Values)
{
foreach (KeyValuePair<string, List<string>> data in ste.GetPrefixNameDic())
{
// 當群組空白
string key = string.Empty;
if (data.Key != " ")
key = data.Key;
// 類別名稱
foreach (string str in data.Value)
{
RowData row = new RowData();
foreach (string field in e.ExportFields)
{
row.ID = ste.StudentID;
if (field == "群組")
row.Add(field, key);
if (field == "類別名稱")
row.Add(field, str);
}
e.Items.Add(row);
}
}
}
PermRecLogProcess prlp = new PermRecLogProcess();
prlp.SaveLog("學生.匯出類別", "匯出", "共匯出" + StudTagDict.Values.Count + "筆學生類別資料.");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 班級類別組合
Dictionary<string, CourseTagEntity> courTagDict = new Dictionary<string, CourseTagEntity>();
// 取得班級類別
foreach (CourseTagRecord courTagRec in CourseTag.SelectByCourseIDs(e.List))
{
if (courTagDict.ContainsKey(courTagRec.RefCourseID))
courTagDict[courTagRec.RefCourseID].AddPrefixName(courTagRec.Prefix, courTagRec.Name);
else
{
CourseTagEntity cou = new CourseTagEntity();
cou.ClassID = courTagRec.RefCourseID;
cou.AddPrefixName(courTagRec.Prefix, courTagRec.Name);
courTagDict.Add(courTagRec.RefCourseID, cou);
}
}
// 讀取組合後的學生類別
foreach (CourseTagEntity cour in courTagDict.Values)
{
foreach (KeyValuePair<string, List<string>> data in cour.GetPrefixNameDic())
{
// 當群組空白
string key = string.Empty;
if (data.Key != " ")
key = data.Key;
// 類別名稱
foreach (string str in data.Value)
{
RowData row = new RowData();
foreach (string field in e.ExportFields)
{
row.ID = cour.ClassID;
if (field == "群組")
row.Add(field, key);
if (field == "類別名稱")
row.Add(field, str);
}
e.Items.Add(row);
}
}
}
PermRecLogProcess prlp = new PermRecLogProcess();
prlp.SaveLog("課程.匯出類別", "匯出", "共匯出" + courTagDict.Values.Count + "筆課程類別資料.");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 教師類別組合
Dictionary<string, TeacherTagEntity> teachTagDict = new Dictionary<string, TeacherTagEntity>();
// 取得教師類別
foreach (TeacherTagRecord teachTagRec in TeacherTag.SelectByTeacherIDs(e.List))
{
if (teachTagDict.ContainsKey(teachTagRec.RefTeacherID))
teachTagDict[teachTagRec.RefTeacherID].AddPrefixName(teachTagRec.Prefix, teachTagRec.Name);
else
{
TeacherTagEntity stn = new TeacherTagEntity();
stn.TeacherID = teachTagRec.RefTeacherID;
stn.AddPrefixName(teachTagRec.Prefix, teachTagRec.Name);
teachTagDict.Add(teachTagRec.RefTeacherID, stn);
}
}
// 讀取組合後的教師類別
foreach (TeacherTagEntity teach in teachTagDict.Values)
{
foreach (KeyValuePair<string, List<string>> data in teach.GetPrefixNameDic())
{
// 當群組空白
string key = string.Empty;
if (data.Key != " ")
key = data.Key;
// 類別名稱
foreach (string str in data.Value)
{
RowData row = new RowData();
foreach (string field in e.ExportFields)
{
row.ID = teach.TeacherID;
if (field == "群組")
row.Add(field, key);
if (field == "類別名稱")
row.Add(field, str);
}
e.Items.Add(row);
}
}
}
PermRecLogProcess prlp = new PermRecLogProcess();
prlp.SaveLog("教師.匯出類別", "匯出", "共匯出" + teachTagDict.Values.Count + "筆教師類別資料.");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
int RowCount = 0;
Dictionary <string, DAO.UDT_HighConcern> HighConcernList = DAO.UDTTransfer.GetHighConcernDictByStudentIDList(e.List);
foreach (DAO.UDT_HighConcern udd in HighConcernList.Values)
{
RowData row = new RowData();
row.ID = udd.RefStudentID;
// 檢查是否匯出
bool chkExportData = false;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "高關懷特殊身分註記":
if (udd.HighConcern)
{
chkExportData = true;
row.Add(field, "是");
}
break;
case "減免人數": row.Add(field, udd.NumberReduce.ToString()); break;
case "文號":
row.Add(field, udd.DocNo);
break;
case "相關證明文件網址":
row.Add(field, udd.EDoc);
break;
}
}
}
if (chkExportData)
{
RowCount++;
e.Items.Add(row);
}
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 取得畢業相關資訊
List <JHLeaveInfoRecord> JHLeaveInfoRecordList = JHLeaveIfno.SelectByStudentIDs(e.List);
foreach (JHLeaveInfoRecord lir in JHLeaveInfoRecordList)
{
RowData row = new RowData();
row.ID = lir.RefStudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "畢業學年度":
if (lir.SchoolYear.HasValue)
{
row.Add(field, lir.SchoolYear.Value.ToString());
}
break;
case "畢業資格":
row.Add(field, lir.Reason);
break;
case "畢業證書字號":
row.Add(field, lir.DiplomaNumber);
break;
case "畢業相關訊息":
row.Add(field, lir.Memo);
break;
}
}
}
e.Items.Add(row);
}
PermRecLogProcess prlp = new PermRecLogProcess();
prlp.SaveLog("學生.匯出畢業資訊", "匯出", "共匯出" + K12.Presentation.NLDPanels.Student.SelectedSource.Count + "筆學生類別資料.");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(DAL.DALTransfer.GetTeacherTagPrefixList(Teacher.Instance.SelectedKeys));
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
List <DAL.TeacherTagEntity> TeacherTagList = DAL.DALTransfer.GetTeacherTagList(e.List);
foreach (DAL.TeacherTagEntity ste in TeacherTagList)
{
RowData row = new RowData();
row.ID = ste.TeacherID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
if (ste.PrefixNameDic.ContainsKey(field))
{
string str = "";
foreach (string strItem in ste.PrefixNameDic[field])
{
str += strItem + "、";
}
str = str.Substring(0, str.Length - 1);
row.Add(field, str);
}
}
}
e.Items.Add(row);
}
PermRecLogProcess prlp = new PermRecLogProcess();
prlp.SaveLog("教師.匯出類別", "匯出", "共匯出" + TeacherTagList.Count + "筆教師類別資料.");
};
}
//覆寫
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange("英文別名", "居留證號", "入學日期", "畢業日期");
wizard.ExportPackage += (sender, e) =>
{
List <StudentRecord_Ext> records = tool._A.Select <StudentRecord_Ext>(string.Format("ref_student_id in ('{0}')", string.Join("','", e.List)));
Dictionary <string, StudentRecord_Ext> recordsDic = new Dictionary <string, StudentRecord_Ext>();
foreach (StudentRecord_Ext each in records)
{
if (!recordsDic.ContainsKey(each.RefStudentID))
{
recordsDic.Add(each.RefStudentID, each);
}
}
List <StudentRecord> StudList = K12.Data.Student.SelectByIDs(e.List);
StudList.Sort(SortStudent);
foreach (StudentRecord stud in StudList)
{
RowData row = new RowData();
row.ID = stud.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "英文別名": row.Add(field, "" + recordsDic[stud.ID].Nickname); break;
case "居留證號": row.Add(field, "" + recordsDic[stud.ID].PassportNumber); break;
case "入學日期": row.Add(field, recordsDic[stud.ID].EntranceDate.HasValue ? recordsDic[stud.ID].EntranceDate.Value.ToShortDateString() : ""); break;
case "畢業日期": row.Add(field, recordsDic[stud.ID].LeavingDate.HasValue ? recordsDic[stud.ID].LeavingDate.Value.ToShortDateString() : ""); break;
}
}
}
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
SmartSchool.API.PlugIn.VirtualCheckBox filterRepeat = new SmartSchool.API.PlugIn.VirtualCheckBox("自動略過重讀成績", true);
wizard.Options.Add(filterRepeat);
// 2022-01 Cynthia 移除"體育", "國防通識", "健康與護理",
wizard.ExportableFields.AddRange("學年度", "成績年級", "學業", "實習科目", "專業科目", "德行");
AccessHelper _AccessHelper = new AccessHelper();
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
List <StudentRecord> students = _AccessHelper.StudentHelper.GetStudents(e.List);
_AccessHelper.StudentHelper.FillSchoolYearEntryScore(filterRepeat.Checked, students);
foreach (StudentRecord stu in students)
{
Dictionary <int, List <SchoolYearEntryScoreInfo> > schoolYearEntryScoreList = new Dictionary <int, List <SchoolYearEntryScoreInfo> >();
foreach (SchoolYearEntryScoreInfo var in stu.SchoolYearEntryScoreList)
{
if (!schoolYearEntryScoreList.ContainsKey(var.SchoolYear))
{
schoolYearEntryScoreList.Add(var.SchoolYear, new List <SchoolYearEntryScoreInfo>());
}
schoolYearEntryScoreList[var.SchoolYear].Add(var);
}
foreach (int sy in schoolYearEntryScoreList.Keys)
{
RowData row = new RowData();
row.ID = stu.StudentID;
row.Add("學年度", "" + sy);
foreach (SchoolYearEntryScoreInfo var in schoolYearEntryScoreList[sy])
{
if (!row.ContainsKey("成績年級"))
{
row.Add("成績年級", "" + var.GradeYear);
}
if (e.ExportFields.Contains(var.Entry))
{
row.Add(var.Entry, "" + var.Score);
}
}
e.Items.Add(row);
}
}
};
}
// 實作匯出
public override void InitializeExport(ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 透過學生編號取得UDT兄弟姊妹資訊
AccessHelper accessHelper = new AccessHelper();
string qry = "ref_student_id IN(" + string.Join(",", e.List.ToArray()) + ")";
List <SiblingRecord> SiblingRecordList = accessHelper.Select <SiblingRecord>(qry);
// 填入資料
foreach (SiblingRecord sr in SiblingRecordList)
{
// 填入資料
RowData row = new RowData();
row.ID = sr.StudnetID.ToString();
foreach (string field in e.ExportFields)
{
// 檢查需要匯出欄位
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "兄弟姊妹姓名":
row.Add(field, sr.SiblingName);
break;
case "稱謂":
row.Add(field, sr.SiblingTitle);
break;
case "生日":
row.Add(field, sr.Birthday.ToShortDateString());
break;
case "學校名稱":
row.Add(field, sr.SchoolName);
break;
case "班級名稱":
row.Add(field, sr.ClassName);
break;
case "備註":
row.Add(field, sr.Remark);
break;
}
}
}
e.Items.Add(row);
}
};
}
void ExportGradScore_ExportPackage(object sender, ExportPackageEventArgs e)
{
foreach (XmlElement studentElement in SmartSchool.Feature.QueryStudent.GetDetailList(new string[] { "ID", "GradScore" }, e.List.ToArray()).GetContent().GetElements("Student"))
{
RowData row = new RowData();
row.ID = studentElement.GetAttribute("ID");
foreach (XmlElement scoreElement in studentElement.SelectNodes("GradScore/GradScore/EntryScore"))
{
string entry = scoreElement.GetAttribute("Entry") + "畢業成績";
if (ExportableFields.Contains(entry))
{
row.Add(entry, scoreElement.GetAttribute("Score"));
}
}
e.Items.Add(row);
}
}
//覆寫
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
List <string> FieldList = new List <string>();
FieldList.Add("學年度"); //目前欄位
FieldList.Add("學期"); //目前欄位
FieldList.Add("社團名稱"); //目前欄位
FieldList.Add("學期成績"); //目前欄位
FieldList.Add("幹部名稱"); //目前欄位
FieldList.Add("社團評等"); //目前欄位
wizard.ExportableFields.AddRange(FieldList);
wizard.ExportPackage += (sender, e) =>
{
//取得學生清單
AccessHelper helper = new AccessHelper();
string strCondition = string.Join("','", e.List);
List <ResultScoreRecord> records = helper.Select <ResultScoreRecord>("ref_student_id in ('" + strCondition + "')");
for (int i = 0; i < records.Count; i++)
{
RowData row = new RowData();
row.ID = records[i].RefStudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row.Add(field, "" + records[i].SchoolYear); break;
case "學期": row.Add(field, "" + records[i].Semester); break;
case "社團名稱": row.Add(field, records[i].ClubName); break;
case "學期成績": row.Add(field, records[i].ResultScore.HasValue ? records[i].ResultScore.Value.ToString() : ""); break;
case "幹部名稱": row.Add(field, records[i].CadreName); break;
case "社團評等": row.Add(field, records[i].ClubLevel); break;
}
}
}
e.Items.Add(row);
}
};
}
//覆寫
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange("學年度", "學期", "年級", "當時班級", "當時座號", "當時班導師姓名", "上課天數");
wizard.ExportPackage += (sender, e) =>
{
//取得學生清單
List <JHSemesterHistoryRecord> records = JHSemesterHistory.SelectByStudentIDs(e.List);
foreach (JHSemesterHistoryRecord record in records)
{
foreach (K12.Data.SemesterHistoryItem Item in record.SemesterHistoryItems)
{
RowData row = new RowData();
row.ID = record.RefStudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row.Add(field, "" + Item.SchoolYear); break;
case "學期": row.Add(field, "" + Item.Semester); break;
case "年級": row.Add(field, "" + Item.GradeYear); break;
case "當時班級": row.Add(field, "" + Item.ClassName); break;
case "當時座號": row.Add(field, "" + Item.SeatNo); break;
case "當時班導師姓名": row.Add(field, "" + Item.Teacher); break;
case "上課天數": row.Add(field, "" + Item.SchoolDayCount); break;
}
}
}
e.Items.Add(row);
}
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
SmartSchool.API.PlugIn.VirtualCheckBox filterRepeat = new SmartSchool.API.PlugIn.VirtualCheckBox("自動略過重讀成績", true);
wizard.Options.Add(filterRepeat);
wizard.ExportableFields.AddRange("學年度", "成績年級", "科目", "學年成績", "結算成績", "補考成績", "重修成績");
AccessHelper _AccessHelper = new AccessHelper();
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
List <StudentRecord> students = _AccessHelper.StudentHelper.GetStudents(e.List);
_AccessHelper.StudentHelper.FillSchoolYearSubjectScore(filterRepeat.Checked, students);
foreach (StudentRecord stu in students)
{
foreach (SchoolYearSubjectScoreInfo var in stu.SchoolYearSubjectScoreList)
{
RowData row = new RowData();
row.ID = stu.StudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row.Add(field, "" + var.SchoolYear); break;
case "成績年級": row.Add(field, "" + var.GradeYear); break;
case "科目": row.Add(field, var.Subject); break;
case "學年成績": row.Add(field, "" + var.Score); break;
case "結算成績": row.Add(field, var.Detail.GetAttribute(field) == "" ? ("" + var.Score) : var.Detail.GetAttribute(field)); break;
case "補考成績": row.Add(field, var.Detail.GetAttribute(field)); break;
case "重修成績": row.Add(field, var.Detail.GetAttribute(field)); break;
}
}
}
e.Items.Add(row);
}
}
};
}
/// <inheritdoc />
public int Read(BinaryReader reader)
{
var byteCount = 0;
var header = reader.ReadBytes(TableHead.Length);
byteCount += TableHead.Length;
if (!CompareBytes(header, GetHashedBytes(TableHead, TableIdx)))
{
throw new ConfigException("Invalid Table Header");
}
byteCount += ConfigStructureUtils.ReadString(reader, out var tableName);
TableName = tableName;
ColumnCount = reader.ReadUInt16();
byteCount += sizeof(ushort);
for (var i = 0; i < ColumnCount; ++i)
{
byteCount += ConfigStructureUtils.ReadString(reader, out var colName);
ColumnName.Add(colName);
var colSep = reader.ReadByte();
if (colSep != ColSep)
{
throw new ConfigException("Invalid Column Separator");
}
byteCount += 1;
}
RowCount = reader.ReadUInt32();
byteCount += sizeof(uint);
for (var i = 0; i < RowCount; ++i)
{
var rowItem = new RowItem(ColumnCount);
byteCount += rowItem.Read(reader);
RowData.Add(rowItem);
var sep = reader.ReadBytes(RowSep.Length);
byteCount += RowSep.Length;
if (!CompareBytes(sep, GetHashedBytes(RowSep, i)))
{
throw new ConfigException("Invalid Row Separator");
}
}
return(byteCount);
}
public void Save()
{
var tableData = Model.Table.Data;
tableData.Columns.Clear();
tableData.ClearRows();
CopyColumns(_columns, tableData.Columns);
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
var data = new RowData();
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
var val = dataGridView1[j, i].Value;
data.Add(val != null ? val.ToString() : "");
}
tableData.AddRow(data);
}
}
/// ------------------------------------------------------------------------------------
public void SaveIdForIndex(int index, string newId)
{
if (newId == null || newId.Trim() == string.Empty)
{
return;
}
newId = GetFieldNameToSerialize(newId);
if (index == RowData.Count)
{
RowData.Add(new FieldInstance(newId));
}
else if (RowData[index].FieldId != newId)
{
_fieldGatherer.SuspendProcessing();
_file.RenameId(RowData[index].FieldId, newId);
RowData[index].FieldId = newId;
_file.Save();
_fieldGatherer.GatherFieldsForFileNow(_file.PathToAnnotatedFile);
_fieldGatherer.ResumeProcessing(true);
}
}
private List <List <string> > ReadRows(ref System.IO.StreamReader file, string FirstLine)
{
List <List <string> > Rows = new List <List <string> >();
List <string> RowData = new List <string>();
string line = string.Empty;
while ((line = file.ReadLine()) != null)
{
string[] elements;
if ((line.Equals(FirstLine)))
{
continue;
}
if ((line.Equals(Environment.NewLine)) || (line.Equals(string.Empty)))
{
Rows.Add(RowData);
RowData = new List <string>();
}
else if (line.Contains('='))
{
elements = line.Split('=');
RowData.Add(elements[1].Trim());
}
else
{
// Do nothing
}
}
if (file.EndOfStream)
{
Rows.Add(RowData);
}
file.BaseStream.Position = 0;
file.DiscardBufferedData();
return(Rows);
}
private void _BKWValidate_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bkw = (BackgroundWorker)sender;
List<string> selectedFields = (List<string>)((object[])e.Argument)[0];
Dictionary<string, int> importFields = (Dictionary<string, int>)((object[])e.Argument)[1];
Workbook wb = (Workbook)((object[])e.Argument)[2];
wb.Worksheets[0].Comments.Clear();
wb.Worksheets[0].Hyperlinks.Clear();
int errorSheetIndex = wb.Worksheets.Add();
{
int errc = 0;
#region 命名
for (; ; errc++)
{
bool pass = true;
string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
foreach (Aspose.Cells.Worksheet var in wb.Worksheets)
{
if (n == var.Name)
{
pass = false;
break;
}
}
if (pass) break;
}
#endregion
wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
}
string errorSheetName = wb.Worksheets[errorSheetIndex].Name;
Worksheet errorSheet = wb.Worksheets[errorSheetIndex];
errorSheet.Cells[0, 0].PutValue("行號");
errorSheet.Cells[0, 1].PutValue("種類");
errorSheet.Cells[0, 2].PutValue("說明");
int errorSheetRowIndex = 1;
Style errorStyle = wb.Styles[wb.Styles.Add()];
Style warningStyle = wb.Styles[wb.Styles.Add()];
Style passStyle = wb.Styles[wb.Styles.Add()];
Style errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
//errorStyle.Pattern = BackgroundType.Solid;
//errorStyle.ForegroundColor = Color.Red;
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
//warningStyle.Pattern = BackgroundType.Solid;
//warningStyle.ForegroundColor = Color.Yellow;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
//passStyle.Pattern = BackgroundType.Solid;
//passStyle.ForegroundColor = Color.Green;
passStyle.Font.Color = Color.Green;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
int errorCount = 0;
int warningCount = 0;
Dictionary<RowData, int> rowDataIndex = new Dictionary<RowData, int>();
Dictionary<int, string> fieldIndex = new Dictionary<int, string>();
foreach (string field in selectedFields)
{
fieldIndex.Add(importFields[field], field);
}
double progress = 0.0;
// 讀取驗證資料工作表使用
Dictionary<string, int> checkSameDataDic = new Dictionary<string, int>();
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (!checkSameDataDic.ContainsKey(chkStr))
checkSameDataDic.Add(chkStr, 0);
}
#region 產生RowData資料
if (importFields.ContainsKey("學生系統編號"))
{
#region 用編號驗證資料
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
// , "學號", "班級", "座號", "科別", "姓名"
string id = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["學生系統編號"]].StringValue);
if (id != "")
{
string rowError = "";
// 檢查重複資料
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (checkSameDataDic.ContainsKey(chkStr))
{
checkSameDataDic[chkStr]++;
if (checkSameDataDic[chkStr] > 1)
rowError =chkStr+"，有重複資料!";
}
#region 驗明正身
StudentRecord stu = Student.SelectByID(id);
if (stu != null)
{
if (importFields.ContainsKey("學號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["學號"]].StringValue) != stu.StudentNumber)
{
//rowError = "學號與系統內學生資料不同!!";
rowError += (rowError == "" ? "" : "、\n") + "系統內學生學號為\"" + stu.StudentNumber + "\"";
}
if (importFields.ContainsKey("姓名") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue) != stu.Name)
{
//rowError = "姓名與系統內學生資料不同!!";
rowError += (rowError == "" ? "" : "、\n") + "系統內學生姓名為\"" + stu.Name + "\"";
}
}
else
{
rowError += "學生不存在!!";
}
#endregion
if (rowError == "")
{
if (!(stu.Status == _StudStatus))
{
#region 警告非在校生
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
//errorSheet.Cells[errorSheetRowIndex, 2].PutValue("學生不是在校生。");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("學生不在所選擇狀態內。");
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
warningCount++;
#endregion
}
RowData rowdata = new RowData();
rowdata.ID = id;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(學生系統編號)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
else if (importFields.ContainsKey("學號"))
{
#region 用學號驗證資料
Dictionary<string, List<StudentRecord>> studentNumberStudents = new Dictionary<string, List<StudentRecord>>();
#region 整理學號對應學生清單(如索引欄不試系統編號時用)
foreach (StudentRecord stu in Student.SelectAll())
{
if (stu.Status == _StudStatus)
{
if (!studentNumberStudents.ContainsKey(stu.StudentNumber))
studentNumberStudents.Add(stu.StudentNumber, new List<StudentRecord>(new StudentRecord[] { stu }));
else
studentNumberStudents[stu.StudentNumber].Add(stu);
}
}
#endregion
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string num = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["學號"]].StringValue);
//wb.Worksheets[0].Cells.
if (num != "")
{
string rowError = "";
// 檢查重複資料
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (checkSameDataDic.ContainsKey(chkStr))
{
checkSameDataDic[chkStr]++;
if (checkSameDataDic[chkStr] > 1)
rowError = chkStr+"，有重複資料!";
}
#region 驗明正身
StudentRecord stu = null;
if (studentNumberStudents.ContainsKey(num))
{
if (studentNumberStudents[num].Count > 1)
{
#region 必需要其他欄位做索引
bool err = true;
foreach (string validateKey in new string[] { "班級", "科別", "座號", "姓名" })
{
if (importFields.ContainsKey(validateKey))
{
err = false;
foreach (StudentRecord var in studentNumberStudents[num])
{
bool pass = true;
//if (importFields.ContainsKey("班級") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級"]].StringValue) != (var.Class != null ? var.Class.Name : ""))
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生班級為\"" + (var.Class != null ? var.Class.Name : "") + "\"";
// pass &= false;
//}
//if (importFields.ContainsKey("座號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["座號"]].StringValue) != "" + var.SeatNo)
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生座號為\"" + var.SeatNo + "\"";
// pass &= false;
//}
if (importFields.ContainsKey("姓名") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue) != var.Name)
{
if (studentNumberStudents[num].Count == 1)
rowError += (rowError == "" ? "" : "、\n") + "系統內學生姓名為\"" + var.Name + "\"";
pass &= false;
}
if (pass)
{
stu = var;
break;
}
}
}
break;
}
if (err)
{
rowError = "系統內發現多名相同學號學生且皆為在校學生，\n需要其他學生欄位進行識別。";
}
#endregion
}
if (studentNumberStudents[num].Count == 1)
{
StudentRecord var = studentNumberStudents[num][0];
bool pass = true;
//if (importFields.ContainsKey("班級") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級"]].StringValue) != (var.Class != null ? var.Class.Name : ""))
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生班級為\"" + (var.Class != null ? var.Class.Name : "") + "\"";
// pass &= false;
//}
//if (importFields.ContainsKey("座號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["座號"]].StringValue) != "" + var.SeatNo)
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生座號為\"" + var.SeatNo + "\"";
// pass &= false;
//}
if (importFields.ContainsKey("姓名") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue) != var.Name)
{
if (studentNumberStudents[num].Count == 1)
rowError += (rowError == "" ? "" : "、\n") + "系統內學生姓名為\"" + var.Name + "\"";
pass &= false;
}
if (pass)
{
stu = var;
}
}
}
else
{
rowError += "學生不存在所選擇狀態內!!";
}
if (rowError == "" && stu == null)
rowError = "學生資料有誤!!";
#endregion
if (rowError == "")
{
RowData rowdata = new RowData();
rowdata.ID = stu.ID;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(學號)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
#endregion
#region 驗證資料
List<string> list = new List<string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
list.Add(row.ID);
}
//_Process.StartValidate(list);
if (ValidateStart != null)
{
SmartSchool.API.PlugIn.Import.ValidateStartEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateStartEventArgs();
args.List = list.ToArray();
ValidateStart(this, args);
}
double totleCount = (double)rowDataIndex.Count;
double count = 0.0;
foreach (RowData row in rowDataIndex.Keys)
{
#region 驗證
string rowError = "";
Dictionary<string, string> errorFields, warningFields;
//RowDataValidatedEventArgs args = _Process.ValidateRow(row, selectedFields);
SmartSchool.API.PlugIn.Import.ValidateRowEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateRowEventArgs();
args.Data = row;
args.SelectFields.AddRange(selectedFields);
if (ValidateRow != null)
{
ValidateRow(this, args);
}
errorFields = args.ErrorFields;
warningFields = args.WarningFields;
rowError += args.ErrorMessage;
if (rowError == "" && errorFields.Count == 0 && warningFields.Count == 0)
{
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = passStyle;
}
else
{
string message = "";
bool hasError = false;
if (rowError != "" || errorFields.Count != 0)
{
errorCount++;
hasError = true;
message = rowError;
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue(hasError ? "錯誤" : "警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
message = "";
if (warningFields.Count != 0)
{
if (!hasError)
warningCount++;
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
if (!hasError)
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = hasError ? errorStyle : warningStyle;
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { errorCount, warningCount });
}
//_Process.FinishValidate();
if (ValidateComplete != null)
ValidateComplete(this, new EventArgs());
#endregion
List<RowData> rows = new List<RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { errorCount, warningCount });
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
e.Result = new object[] { wb, errorCount == 0, rows, selectedFields };
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
SmartSchool.API.PlugIn.VirtualCheckBox filterRepeat = new SmartSchool.API.PlugIn.VirtualCheckBox("自動略過重讀成績", true);
wizard.Options.Add(filterRepeat);
// 2018/8 穎驊註解，經過討論後， 先暫時將 ischool類別2 排名的概念拿掉，因為目前的結構 無法區隔類別1、類別2，待日後設計完整
//wizard.ExportableFields.AddRange("學年度", "學期", "成績年級", "學業", "體育", "國防通識", "健康與護理", "實習科目", "專業科目", "學業(原始)", "體育(原始)", "國防通識(原始)", "健康與護理(原始)", "實習科目(原始)", "專業科目(原始)", "德行", "學業成績班排名", "學業成績班排名母數", "學業成績科排名", "學業成績科排名母數", "學業成績校排名", "學業成績校排名母數", "學業成績排名類別1", "學業成績類1排名", "學業成績類1排名母數", "學業成績排名類別2", "學業成績類2排名", "學業成績類2排名母數");
wizard.ExportableFields.AddRange("學年度", "學期", "成績年級", "學業", "體育", "國防通識", "健康與護理", "實習科目", "專業科目", "學業(原始)", "體育(原始)", "國防通識(原始)", "健康與護理(原始)", "實習科目(原始)", "專業科目(原始)", "德行", "學業成績班排名", "學業成績班排名母數", "學業成績科排名", "學業成績科排名母數", "學業成績校排名", "學業成績校排名母數", "學業成績排名類別1", "學業成績類1排名", "學業成績類1排名母數");
AccessHelper _AccessHelper = new AccessHelper();
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
List <StudentRecord> students = _AccessHelper.StudentHelper.GetStudents(e.List);
Dictionary <string, List <StudSemsEntryRating> > StudSemsEntryRatingDict = new Dictionary <string, List <StudSemsEntryRating> >();
StudSemsEntryRatingDict = Utility.GetStudSemsEntryRatingByStudentID(e.List);
_AccessHelper.StudentHelper.FillSemesterEntryScore(filterRepeat.Checked, students);
foreach (StudentRecord stu in students)
{
Dictionary <int, Dictionary <int, List <SemesterEntryScoreInfo> > > semesterEntryScoreList = new Dictionary <int, Dictionary <int, List <SemesterEntryScoreInfo> > >();
foreach (SemesterEntryScoreInfo var in stu.SemesterEntryScoreList)
{
if (!semesterEntryScoreList.ContainsKey(var.SchoolYear))
{
semesterEntryScoreList.Add(var.SchoolYear, new Dictionary <int, List <SemesterEntryScoreInfo> >());
}
if (!semesterEntryScoreList[var.SchoolYear].ContainsKey(var.Semester))
{
semesterEntryScoreList[var.SchoolYear].Add(var.Semester, new List <SemesterEntryScoreInfo>());
}
semesterEntryScoreList[var.SchoolYear][var.Semester].Add(var);
}
foreach (int sy in semesterEntryScoreList.Keys)
{
foreach (int se in semesterEntryScoreList[sy].Keys)
{
RowData row = new RowData();
row.ID = stu.StudentID;
row.Add("學年度", "" + sy);
row.Add("學期", "" + se);
foreach (SemesterEntryScoreInfo var in semesterEntryScoreList[sy][se])
{
if (!row.ContainsKey("成績年級"))
{
row.Add("成績年級", "" + var.GradeYear);
}
if (e.ExportFields.Contains(var.Entry))
{
row.Add(var.Entry, "" + var.Score);
}
}
//處理學業成績排名資料
if (StudSemsEntryRatingDict.ContainsKey(stu.StudentID))
{
foreach (var record in StudSemsEntryRatingDict[stu.StudentID])
{
if (record.SchoolYear == "" + sy && record.Semester == "" + se)
{
row.Add("學業成績班排名", "" + record.ClassRank);
row.Add("學業成績班排名母數", "" + record.ClassCount);
row.Add("學業成績科排名", "" + record.DeptRank);
row.Add("學業成績科排名母數", "" + record.DeptCount);
row.Add("學業成績校排名", "" + record.YearRank);
row.Add("學業成績校排名母數", "" + record.YearCount);
row.Add("學業成績排名類別1", "" + record.Group1);
row.Add("學業成績類1排名", "" + record.Group1Rank);
row.Add("學業成績類1排名母數", "" + record.Group1Count);
// 2018/8 穎驊註解，經過討論後， 先暫時將 ischool類別2 排名的概念拿掉，因為目前的結構 無法區隔類別1、類別2，待日後設計完整
//row.Add("學業成績排名類別2", "" + record.Group2);
//row.Add("學業成績類2排名", "" + record.Group2Rank);
//row.Add("學業成績類2排名母數", "" + record.Group2Count);
}
}
}
e.Items.Add(row);
}
}
}
};
}
//覆寫
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
List <string> FieldList = new List <string>();
FieldList.Add("學年度"); //目前欄位
FieldList.Add("學期"); //目前欄位
FieldList.Add("社團名稱"); //目前欄位
FieldList.Add("代碼");
FieldList.Add("場地");
FieldList.Add("類型");
FieldList.Add("評等");
FieldList.Add("老師1");
FieldList.Add("老師2");
FieldList.Add("老師3");
FieldList.Add("簡介");
FieldList.Add("限制:性別");
FieldList.Add("限制:科別");
FieldList.Add("限制:一年級人數");
FieldList.Add("限制:二年級人數");
FieldList.Add("限制:三年級人數");
FieldList.Add("限制:人數上限");
//取得教師
TeacherDic = GetTeacher();
wizard.ExportableFields.AddRange(FieldList);
wizard.ExportPackage += (sender, e) =>
{
//取得學生清單
AccessHelper helper = new AccessHelper();
string strCondition = string.Join("','", e.List);
List <CLUBRecord> records = helper.Select <CLUBRecord>("uid in ('" + strCondition + "')");
for (int i = 0; i < records.Count; i++)
{
RowData row = new RowData();
row.ID = records[i].UID;
string teacher1 = "";
string teacher2 = "";
string teacher3 = "";
if (TeacherDic.ContainsKey(records[i].RefTeacherID))
{
teacher1 = TeacherDic[records[i].RefTeacherID];
}
if (TeacherDic.ContainsKey(records[i].RefTeacherID2))
{
teacher2 = TeacherDic[records[i].RefTeacherID2];
}
if (TeacherDic.ContainsKey(records[i].RefTeacherID3))
{
teacher3 = TeacherDic[records[i].RefTeacherID3];
}
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row.Add(field, "" + records[i].SchoolYear); break;
case "學期": row.Add(field, "" + records[i].Semester); break;
case "社團名稱": row.Add(field, records[i].ClubName); break;
case "代碼": row.Add(field, records[i].ClubNumber); break;
case "場地": row.Add(field, records[i].Location); break;
case "類型": row.Add(field, records[i].ClubCategory); break;
case "評等": row.Add(field, records[i].Level); break;
case "老師1": row.Add(field, teacher1); break;
case "老師2": row.Add(field, teacher2); break;
case "老師3": row.Add(field, teacher3); break;
case "簡介": row.Add(field, records[i].About); break;
case "限制:性別": row.Add(field, records[i].GenderRestrict); break;
case "限制:科別": row.Add(field, GetRestrict(records[i].DeptRestrict)); break;
case "限制:一年級人數": row.Add(field, records[i].Grade1Limit.HasValue ? "" + records[i].Grade1Limit.Value : ""); break;
case "限制:二年級人數": row.Add(field, records[i].Grade2Limit.HasValue ? "" + records[i].Grade2Limit.Value : ""); break;
case "限制:三年級人數": row.Add(field, records[i].Grade3Limit.HasValue ? "" + records[i].Grade3Limit.Value : ""); break;
case "限制:人數上限": row.Add(field, records[i].Limit.HasValue ? "" + records[i].Limit.Value : ""); break;
}
}
}
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 依學生ID取得晤談紀錄
List<DAO.UDT_CounselStudentInterviewRecordDef> _StudentInterviewRecordList = _UDTTransfer.GetCounselStudentInterviewRecordByStudentIDList(e.List);
// 取得教師ID與名稱對照
Dictionary<int, string> TeacherNameDict = Utility.GetTeacherIDNameDict();
Dictionary<string, string> item_AttendessDict = new Dictionary<string, string>();
Dictionary<string, string> item_CounselTypeDict = new Dictionary<string, string>();
Dictionary<string, string> item_CounselTypeKindDict = new Dictionary<string, string>();
// 學生ID List
List<int> studIdList = (from data in _StudentInterviewRecordList select data.StudentID).ToList();
// 取得學生名稱log 用
Dictionary<string, string> StudentNameDict = new Dictionary<string, string>();
foreach (KeyValuePair<int, string> data in Utility.GetConvertStringDict1fromDB(studIdList))
StudentNameDict.Add(data.Key.ToString(), data.Value);
// 記錄匯出 log 細項用
Dictionary<string, StringBuilder> logData = new Dictionary<string, StringBuilder>();
// 匯出總筆數
int totalCount = 0;
foreach (DAO.UDT_CounselStudentInterviewRecordDef csir in _StudentInterviewRecordList)
{
// 取得 XML 解析後
item_AttendessDict = Utility.GetConvertCounselXMLVal_Attendees(csir.Attendees);
item_CounselTypeDict = Utility.GetConvertCounselXMLVal_CounselType(csir.CounselType);
item_CounselTypeKindDict = Utility.GetConvertCounselXMLVal_CounselTypeKind(csir.CounselTypeKind);
RowData row = new RowData();
row.ID = csir.StudentID.ToString();
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
// 參與人員
if (item_AttendessDict.ContainsKey(field))
row.Add(field, item_AttendessDict[field]);
// 輔導方式
if (item_CounselTypeDict.ContainsKey(field))
row.Add(field, item_CounselTypeDict[field]);
// 輔導歸類
if (item_CounselTypeKindDict.ContainsKey(field))
row.Add(field, item_CounselTypeKindDict[field]);
switch (field)
{
case "晤談編號":
row.Add(field, csir.InterviewNo);
break;
case "晤談老師":
// 需轉換
if(TeacherNameDict.ContainsKey(csir.TeacherID))
row.Add(field, TeacherNameDict[csir.TeacherID]);
break;
case "晤談方式":
row.Add(field, csir.InterviewType);
break;
case "晤談對象":
row.Add(field, csir.IntervieweeType);
break;
case "日期":
if(csir.InterviewDate.HasValue )
row.Add(field, csir.InterviewDate.Value.ToShortDateString());
break;
case "時間":
row.Add(field, csir.InterviewTime);
break;
case "地點":
row.Add(field, csir.Place);
break;
case "晤談事由":
row.Add(field, csir.Cause);
break;
case "參與人員":
// 解析 XML
row.Add(field,Utility.AttendeesXMLToString(csir.Attendees));
break;
case "輔導方式":
// 解析 XML
row.Add(field, Utility.CounselTypeXMLToString(csir.CounselType));
break;
case "輔導歸類":
// 解析 XML
row.Add(field, Utility.CounselTypeKindXMLToString(csir.CounselTypeKind));
break;
case "內容要點":
row.Add(field, csir.ContentDigest);
break;
case "記錄者":
row.Add(field, csir.AuthorID);
break;
case "記錄者姓名":
row.Add(field, csir.AuthorName);
break;
case "狀態":
row.Add(field, csir.StudentStatus);
break;
}
}
}
e.Items.Add(row);
}
// 處理 log 細項
foreach (RowData rd in e.Items)
{
// 收集 log
StringBuilder sb = new StringBuilder();
if (StudentNameDict.ContainsKey(rd.ID))
sb.AppendLine(StudentNameDict[rd.ID]);
sb.AppendLine(this.Text);
foreach (KeyValuePair<string, string> data in rd)
sb.AppendLine(data.Key + "：" + data.Value);
// 加入 log
if (logData.ContainsKey(rd.ID))
{
sb.AppendLine();
logData[rd.ID].AppendLine(sb.ToString());
}
else
logData.Add(rd.ID, sb);
}
// 寫入 log
DAO.LogTransfer log = new DAO.LogTransfer();
foreach (KeyValuePair<string, StringBuilder> data in logData)
log.SaveLog("輔導系統." + this.Text, "匯出", "student", data.Key, data.Value);
StringBuilder sbT = new StringBuilder();
sbT.AppendLine(this.Text);
sbT.AppendLine("總共匯出" + logData.Keys.Count + "位學生 , 共" + totalCount + "筆");
sbT.AppendLine("匯出學生名單..");
foreach (KeyValuePair<string, string> data in StudentNameDict)
if (logData.ContainsKey(data.Key))
sbT.AppendLine(data.Value);
log.SaveLog("輔導系統." + this.Text, "匯出", "student", "", sbT);
};
}
private void _BKWValidate_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bkw = (BackgroundWorker)sender;
List<string> selectedFields = (List<string>)((object[])e.Argument)[0];
Dictionary<string, int> importFields = (Dictionary<string, int>)((object[])e.Argument)[1];
Workbook wb = (Workbook)((object[])e.Argument)[2];
wb.Worksheets[0].Comments.Clear();
wb.Worksheets[0].Hyperlinks.Clear();
int errorSheetIndex = wb.Worksheets.Add();
{
int errc = 0;
#region 命名
for (; ; errc++)
{
bool pass = true;
string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
foreach (Aspose.Cells.Worksheet var in wb.Worksheets)
{
if (n == var.Name)
{
pass = false;
break;
}
}
if (pass) break;
}
#endregion
wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
}
string errorSheetName = wb.Worksheets[errorSheetIndex].Name;
Worksheet errorSheet = wb.Worksheets[errorSheetIndex];
errorSheet.Cells[0, 0].PutValue("行號");
errorSheet.Cells[0, 1].PutValue("種類");
errorSheet.Cells[0, 2].PutValue("說明");
int errorSheetRowIndex = 1;
Style errorStyle = wb.Styles[wb.Styles.Add()];
Style warningStyle = wb.Styles[wb.Styles.Add()];
Style passStyle = wb.Styles[wb.Styles.Add()];
Style errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
//errorStyle.Pattern = BackgroundType.Solid;
//errorStyle.ForegroundColor = Color.Red;
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
//warningStyle.Pattern = BackgroundType.Solid;
//warningStyle.ForegroundColor = Color.Yellow;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
//passStyle.Pattern = BackgroundType.Solid;
//passStyle.ForegroundColor = Color.Green;
passStyle.Font.Color = Color.Green;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
int errorCount = 0;
int warningCount = 0;
Dictionary<RowData, int> rowDataIndex = new Dictionary<RowData, int>();
Dictionary<int, string> fieldIndex = new Dictionary<int, string>();
foreach (string field in selectedFields)
{
fieldIndex.Add(importFields[field], field);
}
double progress = 0.0;
// 讀取驗證資料工作表使用
Dictionary<string, int> checkSameDataDic = new Dictionary<string, int>();
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (!checkSameDataDic.ContainsKey(chkStr))
checkSameDataDic.Add(chkStr, 0);
}
#region 產生RowData資料
if (importFields.ContainsKey("姓名") && importFields.ContainsKey("暱稱"))
{
#region 用「姓名+暱稱」驗證資料
List<K12.Data.TeacherRecord> stus = K12.Data.Teacher.SelectAll();
K12.Data.TeacherRecord stu = new K12.Data.TeacherRecord();
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
// , "學號", "班級", "座號", "科別", "姓名"
string name = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue);
string nickName = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["暱稱"]].StringValue);
if ((name+nickName) != "")
{
string rowError = "";
// 檢查重複資料
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (checkSameDataDic.ContainsKey(chkStr))
{
checkSameDataDic[chkStr]++;
if (checkSameDataDic[chkStr] > 1)
rowError = "有重複資料!";
}
#region 驗明正身
if (stus != null)
{
if (stus.Where(x => (x.Name.Trim() == name.Trim() && x.Nickname.Trim() == nickName.Trim())).Count() == 0)
rowError = "教師不存在於系統中，請先新增教師資料!!";
}
else
rowError = "教師不存在於系統中，請先新增教師資料!!";
#endregion
if (rowError == "")
{
stu = stus.Where(x => (x.Name.Trim() == name.Trim() && x.Nickname.Trim() == nickName.Trim())).ElementAt(0);
if (!(stu.Status == _StudStatus))
{
#region 警告非在職教師
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
//errorSheet.Cells[errorSheetRowIndex, 2].PutValue("學生不是在校生。");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("教師不在所選擇狀態內。");
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
warningCount++;
#endregion
}
RowData rowdata = new RowData();
rowdata.ID = stu.ID;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(姓名+暱稱)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
#endregion
#region 驗證資料
List<string> list = new List<string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
list.Add(row.ID);
}
//_Process.StartValidate(list);
if (ValidateStart != null)
{
SmartSchool.API.PlugIn.Import.ValidateStartEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateStartEventArgs();
args.List = list.ToArray();
ValidateStart(this, args);
}
double totleCount = (double)rowDataIndex.Count;
double count = 0.0;
foreach (RowData row in rowDataIndex.Keys)
{
#region 驗證
string rowError = "";
Dictionary<string, string> errorFields, warningFields;
//RowDataValidatedEventArgs args = _Process.ValidateRow(row, selectedFields);
SmartSchool.API.PlugIn.Import.ValidateRowEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateRowEventArgs();
args.Data = row;
args.SelectFields.AddRange(selectedFields);
if (ValidateRow != null)
{
ValidateRow(this, args);
}
errorFields = args.ErrorFields;
warningFields = args.WarningFields;
rowError += args.ErrorMessage;
if (rowError == "" && errorFields.Count == 0 && warningFields.Count == 0)
{
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = passStyle;
}
else
{
string message = "";
bool hasError = false;
if (rowError != "" || errorFields.Count != 0)
{
errorCount++;
hasError = true;
message = rowError;
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue(hasError ? "錯誤" : "警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
message = "";
if (warningFields.Count != 0)
{
if (!hasError)
warningCount++;
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
if (!hasError)
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = hasError ? errorStyle : warningStyle;
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { errorCount, warningCount });
}
//_Process.FinishValidate();
if (ValidateComplete != null)
ValidateComplete(this, new EventArgs());
#endregion
List<RowData> rows = new List<RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { errorCount, warningCount });
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
e.Result = new object[] { wb, errorCount == 0, rows, selectedFields };
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
List <string> FieldsList = GetList();
wizard.ExportableFields.AddRange(FieldsList.ToArray());
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
#region 收集資料
//取得所選社團
List <string> SelectCLUBIDList = e.List;
List <ClubCadresObj> _startList = new List <ClubCadresObj>();
List <CLUBRecord> clubList = helper.Select <CLUBRecord>(SelectCLUBIDList);
CLUBDic.Clear();
foreach (CLUBRecord each in clubList)
{
if (!CLUBDic.ContainsKey(each.UID))
{
CLUBDic.Add(each.UID, each);
}
#region 處理學生擔任之幹部
if (!string.IsNullOrEmpty(each.President))
{
ClubCadresObj obj = new ClubCadresObj();
obj._Club = each;
obj.ref_student_id = each.President;
obj.CadreName = "社長";
_startList.Add(obj);
}
if (!string.IsNullOrEmpty(each.VicePresident))
{
ClubCadresObj obj = new ClubCadresObj();
obj._Club = each;
obj.ref_student_id = each.VicePresident;
obj.CadreName = "副社長";
_startList.Add(obj);
}
#endregion
}
//取得社團學生的幹部記錄
List <CadresRecord> newList = helper.Select <CadresRecord>(string.Format("ref_club_id in ('{0}')", string.Join("','", SelectCLUBIDList)));
foreach (CadresRecord each in newList)
{
if (CLUBDic.ContainsKey(each.RefClubID))
{
ClubCadresObj obj = new ClubCadresObj();
obj._Club = CLUBDic[each.RefClubID];
obj.CadreName = each.CadreName;
obj.ref_student_id = each.RefStudentID;
_startList.Add(obj);
}
}
List <string> StudentIDList = new List <string>();
foreach (ClubCadresObj rsr in _startList)
{
if (!StudentIDList.Contains(rsr.ref_student_id))
{
StudentIDList.Add(rsr.ref_student_id);
}
}
#endregion
#region 取得學生基本資料
StudentDic.Clear();
List <StudentRecord> StudentRecordList = Student.SelectByIDs(StudentIDList);
foreach (StudentRecord each in StudentRecordList)
{
if (!StudentDic.ContainsKey(each.ID))
{
StudentDic.Add(each.ID, each);
}
}
foreach (ClubCadresObj Result in _startList)
{
if (StudentDic.ContainsKey(Result.ref_student_id))
{
Result._Student = StudentDic[Result.ref_student_id];
}
}
#endregion
_startList.Sort(SortResult);
foreach (ClubCadresObj Result in _startList)
{
StudentRecord sr = Result._Student;
if (sr == null)
{
continue;
}
//社團代碼
string CLUBCode = Result._Club.ClubNumber;
RowData row = new RowData();
row.ID = Result.ref_student_id;
foreach (string field in e.ExportFields)
{
#region row
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row.Add(field, "" + Result._Club.SchoolYear); break;
case "學期": row.Add(field, "" + Result._Club.Semester); break;
case "社團名稱": row.Add(field, "" + Result._Club.ClubName); break;
case "代碼": row.Add(field, CLUBCode); break;
case "班級": row.Add(field, string.IsNullOrEmpty(sr.RefClassID) ? "" : sr.Class.Name); break;
case "座號": row.Add(field, sr.SeatNo.HasValue ? sr.SeatNo.Value.ToString() : ""); break;
case "學號": row.Add(field, sr.StudentNumber); break;
case "姓名": row.Add(field, sr.Name); break;
case "幹部名稱": row.Add(field, Result.CadreName); break;
}
}
#endregion
}
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
AccessHelper helper = new AccessHelper();
Dictionary<string, CLUBRecord> AllClubDic = new Dictionary<string, CLUBRecord>();
Dictionary<string, List<string>> SemesterClub = new Dictionary<string, List<string>>();
//社團基本資料
List<CLUBRecord> ClubList = helper.Select<CLUBRecord>();
foreach (CLUBRecord each in ClubList)
{
if (!AllClubDic.ContainsKey(each.UID))
{
AllClubDic.Add(each.UID, each);
}
string SchoolYearSemester = each.SchoolYear.ToString().PadLeft(3, ' ') + "学年度 第" + each.Semester.ToString() + "学期";
if (!SemesterClub.ContainsKey(SchoolYearSemester))
{
//學年度學期名稱/當學期的社團課程ID清單
SemesterClub.Add(SchoolYearSemester, new List<string>());
}
SemesterClub[SchoolYearSemester].Add(each.UID);
}
var FiltedSemester = School.DefaultSchoolYear.ToString().PadLeft(3, ' ') + "学年度 第" + School.DefaultSemester + "学期";
List<string> list = new List<string>();
foreach (string item in SemesterClub.Keys)
{
list.Add(item);
}
list.Sort();
if (list.Count > 0 && !list.Contains(FiltedSemester))
FiltedSemester = list[0];
foreach (string item in list)
{
SmartSchool.API.PlugIn.VirtualRadioButton radioSem = new VirtualRadioButton(item);
wizard.Options.Add(radioSem);
if (item == FiltedSemester)
radioSem.Checked = true;
radioSem.CheckedChanged += delegate(object sender1, EventArgs e1)
{
var target = sender1 as SmartSchool.API.PlugIn.VirtualRadioButton;
if (target.Checked)
FiltedSemester = target.Text;
};
//MenuButton mb = e.VirtualButtons[item];
//mb.AutoCheckOnClick = true;
//mb.AutoCollapseOnClick = true;
//mb.Checked = (item == FiltedSemester);
//mb.Tag = item;
//mb.CheckedChanged += delegate(object sender1, EventArgs e1)
//{
// MenuButton mb1 = sender1 as MenuButton;
// SetClubList(mb1.Text);
// FiltedSemester = FilterMenu.Text = mb1.Text;
// mb1.Checked = true;
//};
}
wizard.ExportableFields.AddRange(new string[]{
"班级",
"学号",
"姓名",
"学籍号",
"性别",
"第一阶段",
"第二阶段"
});
wizard.ExportPackage += (sender, e) =>
{
Dictionary<string, CLUBRecord> ClubRefIDList = new Dictionary<string, CLUBRecord>();
//List<CLUBRecord> CLUBRecordList = helper.Select<CLUBRecord>(string.Format("uid in ('{0}')", string.Join("','", e.List)));
//foreach (CLUBRecord record in CLUBRecordList)
//{
// if (!ClubRefIDList.ContainsKey(record.UID))
// {
// ClubRefIDList.Add(record.UID, record);
// }
//}
foreach (CLUBRecord each in ClubList)
{
string SchoolYearSemester = each.SchoolYear.ToString().PadLeft(3, ' ') + "学年度 第" + each.Semester.ToString() + "学期";
if (FiltedSemester == SchoolYearSemester)
{
ClubRefIDList.Add(each.UID, each);
}
}
string ClubIdString = string.Join("','", ClubRefIDList.Keys);
List<SCJoin> Scjoin = helper.Select<SCJoin>(string.Format("ref_club_id in ('{0}') and ref_student_id in ('{1}')", ClubIdString, string.Join("','", e.List)));
Dictionary<string, List<SCJoin>> StudentScjoinDic = new Dictionary<string, List<SCJoin>>();
foreach (SCJoin join in Scjoin)
{
if (!StudentScjoinDic.ContainsKey(join.RefStudentID))
{
StudentScjoinDic.Add(join.RefStudentID, new List<SCJoin>());
}
StudentScjoinDic[join.RefStudentID].Add(join);
}
var studentList = K12.Data.Student.SelectByIDs(e.List);
foreach (var stuRec in studentList)
{
RowData row = new RowData();
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "班级":
row.Add(field, stuRec.Class == null ? "" : stuRec.Class.Name);
break;
case "学号":
row.Add(field, "" + stuRec.SeatNo);
break;
case "姓名":
row.Add(field, "" + stuRec.Name);
break;
case "学籍号":
row.Add(field, "" + stuRec.StudentNumber);
break;
case "性别":
row.Add(field, "" + stuRec.Gender);
break;
case "第一阶段":
if (StudentScjoinDic.ContainsKey(stuRec.ID))
{
foreach (var item in StudentScjoinDic[stuRec.ID])
{
if (item.Phase == 1)
{
if (row.ContainsKey(field))
row[field] += "," + ClubRefIDList[item.RefClubID].ClubName;
else
row.Add(field, ClubRefIDList[item.RefClubID].ClubName);
}
}
}
break;
case "第二阶段":
if (StudentScjoinDic.ContainsKey(stuRec.ID))
{
foreach (var item in StudentScjoinDic[stuRec.ID])
{
if (item.Phase == 1 && ClubRefIDList[item.RefClubID].FullPhase.HasValue && ClubRefIDList[item.RefClubID].FullPhase.Value == true)
{
if (row.ContainsKey(field))
row[field] += ",--";
else
row.Add(field, "--");
}
if (item.Phase == 2)
{
if (row.ContainsKey(field))
row[field] += "," + ClubRefIDList[item.RefClubID].ClubName;
else
row.Add(field, ClubRefIDList[item.RefClubID].ClubName);
}
}
}
break;
}
}
}
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
SmartSchool.API.PlugIn.VirtualCheckBox filterRepeat = new SmartSchool.API.PlugIn.VirtualCheckBox("自動略過重讀成績", true);
wizard.Options.Add(filterRepeat);
wizard.ExportableFields.AddRange(
"科目"
, "科目級別"
, "學年度"
, "學期"
, "英文名稱"
, "學分數"
, "分項類別"
, "成績年級"
, "必選修"
, "校部訂"
, "科目成績"
, "原始成績"
, "補考成績"
, "重修成績"
, "手動調整成績"
, "學年調整成績"
, "取得學分"
, "不計學分"
, "不需評分"
, "註記"
, "畢業採計-學分數"
, "畢業採計-分項類別"
, "畢業採計-必選修"
, "畢業採計-校部訂"
, "畢業採計-不計學分"
, "畢業採計-說明"
, "是否補修成績"
, "補修學年度"
, "補修學期"
, "重修學年度"
, "重修學期"
, "修課及格標準"
, "修課補考標準"
, "修課備註"
, "修課直接指定總成績"
, "免修"
, "抵免"
);
filterRepeat.CheckedChanged += delegate
{
if (filterRepeat.Checked)
{
foreach (var item in new string[] { "畢業採計-學分數"
, "畢業採計-分項類別"
, "畢業採計-必選修"
, "畢業採計-校部訂"
, "畢業採計-不計學分"
, "畢業採計-說明" }
)
{
if (!wizard.ExportableFields.Contains(item))
{
wizard.ExportableFields.Add(item);
}
}
}
else
{
foreach (var item in new string[] { "畢業採計-學分數"
, "畢業採計-分項類別"
, "畢業採計-必選修"
, "畢業採計-校部訂"
, "畢業採計-不計學分"
, "畢業採計-說明" }
)
{
if (wizard.ExportableFields.Contains(item))
{
wizard.ExportableFields.Remove(item);
}
}
}
};
AccessHelper _AccessHelper = new AccessHelper();
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
List <StudentRecord> students = _AccessHelper.StudentHelper.GetStudents(e.List);
if (filterRepeat.Checked)
{
var gCheck = false;
foreach (var item in new string[] { "畢業採計-學分數"
, "畢業採計-分項類別"
, "畢業採計-必選修"
, "畢業採計-校部訂"
, "畢業採計-不計學分"
, "畢業採計-說明" }
)
{
if (e.ExportFields.Contains(item))
{
gCheck = true;
break;
}
}
if (gCheck)
{
new WearyDogComputer().FillStudentGradCheck(_AccessHelper, students);
}
//_AccessHelper.StudentHelper.FillSemesterSubjectScore(filterRepeat.Checked, students);
else
{
_AccessHelper.StudentHelper.FillSemesterSubjectScore(filterRepeat.Checked, students);
}
}
else
{
_AccessHelper.StudentHelper.FillSemesterSubjectScore(false, students);
}
//if (e.ExportFields.Contains("計算規則-及格標準"))
// _AccessHelper.StudentHelper.FillField("及格標準", students);
//if (e.ExportFields.Contains("計算規則-補考標準"))
// _AccessHelper.StudentHelper.FillField("補考標準", students);
//// 取得學生課程代碼相關對照
//CourseCodeTransfer cct = new CourseCodeTransfer();
//// 取得學生課程代碼
//Dictionary<string, StudentCourseCodeInfo> studCodeInfoDict = cct.GetStundetCourseCodeDict(e.List);
string subjectCode = "";
foreach (StudentRecord stu in students)
{
foreach (SemesterSubjectScoreInfo var in stu.SemesterSubjectScoreList)
{
subjectCode = "";
//if (studCodeInfoDict.ContainsKey(stu.StudentID))
//{
// StudentCourseCodeInfo item = studCodeInfoDict[stu.StudentID];
// string req = var.Require ? "必修" : "選修";
// subjectCode = item.GetCourseCode(var.Subject, var.Detail.GetAttribute("修課校部訂"), req);
//}
RowData row = new RowData();
row.ID = stu.StudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "科目": row.Add(field, var.Subject); break;
case "科目級別": row.Add(field, var.Level); break;
case "學年度": row.Add(field, "" + var.SchoolYear); break;
case "學期": row.Add(field, "" + var.Semester); break;
case "英文名稱": row.Add(field, var.Detail.GetAttribute("英文名稱")); break;
case "學分數": row.Add(field, "" + var.CreditDec()); break;
case "分項類別": row.Add(field, var.Detail.GetAttribute("開課分項類別")); break;
case "成績年級": row.Add(field, "" + var.GradeYear); break;
case "必選修": row.Add(field, var.Require ? "必修" : "選修"); break;
case "校部訂": row.Add(field, var.Detail.GetAttribute("修課校部訂")); break;
case "科目成績": row.Add(field, "" + var.Score); break;
case "原始成績": row.Add(field, var.Detail.GetAttribute("原始成績")); break;
case "補考成績": row.Add(field, var.Detail.GetAttribute("補考成績")); break;
case "重修成績": row.Add(field, var.Detail.GetAttribute("重修成績")); break;
case "手動調整成績": row.Add(field, var.Detail.GetAttribute("擇優採計成績")); break;
case "學年調整成績": row.Add(field, var.Detail.GetAttribute("學年調整成績")); break;
case "取得學分": row.Add(field, var.Pass ? "是" : "否"); break;
case "不計學分": row.Add(field, var.Detail.GetAttribute("不計學分") == "是" ? "是" : ""); break;
case "不需評分": row.Add(field, var.Detail.GetAttribute("不需評分") == "是" ? "是" : ""); break;
case "註記": row.Add(field, var.Detail.HasAttribute("註記") ? var.Detail.GetAttribute("註記") : ""); break;
//case "計算規則-及格標準":
// if (stu.Fields.ContainsKey("及格標準") && stu.Fields["及格標準"] is Dictionary<int, decimal>)
// {
// Dictionary<int, decimal> applyLimit = (Dictionary<int, decimal>)stu.Fields["及格標準"];
// if (applyLimit.ContainsKey(var.GradeYear))
// {
// row.Add(field, "" + applyLimit[var.GradeYear]);
// }
// else
// {
// row.Add(field, "無法判斷");
// }
// }
// else
// {
// row.Add(field, "沒有成績計算規則");
// }
// break;
//case "計算規則-補考標準":
// if (stu.Fields.ContainsKey("補考標準") && stu.Fields["補考標準"] is Dictionary<int, decimal>)
// {
// Dictionary<int, decimal> reexamLimit = (Dictionary<int, decimal>)stu.Fields["補考標準"];
// if (reexamLimit.ContainsKey(var.GradeYear))
// {
// row.Add(field, "" + reexamLimit[var.GradeYear]);
// }
// else
// {
// row.Add(field, "無法判斷");
// }
// }
// else
// {
// row.Add(field, "沒有成績計算規則");
// }
// break;
case "畢業採計-學分數":
case "畢業採計-分項類別":
case "畢業採計-必選修":
case "畢業採計-校部訂":
case "畢業採計-不計學分":
if (var.Detail.GetAttribute(field) == "")
{
row.Add(field, "--");
}
else
{
row.Add(field, var.Detail.GetAttribute(field));
}
break;
case "畢業採計-說明":
row.Add(field, var.Detail.GetAttribute(field));
break;
case "是否補修成績": row.Add(field, var.Detail.GetAttribute("是否補修成績") == "是" ? "是" : ""); break;
case "重修學年度": row.Add(field, var.Detail.GetAttribute("重修學年度")); break;
case "重修學期": row.Add(field, var.Detail.GetAttribute("重修學期")); break;
case "補修學年度": row.Add(field, var.Detail.GetAttribute("補修學年度")); break;
case "補修學期": row.Add(field, var.Detail.GetAttribute("補修學期")); break;
case "修課及格標準": row.Add(field, var.Detail.GetAttribute("修課及格標準")); break;
case "修課補考標準": row.Add(field, var.Detail.GetAttribute("修課補考標準")); break;
// case "修課科目代碼": row.Add(field, var.Detail.GetAttribute("修課科目代碼")); break;
case "修課備註": row.Add(field, var.Detail.GetAttribute("修課備註")); break;
case "修課直接指定總成績": row.Add(field, var.Detail.GetAttribute("修課直接指定總成績")); break;
//case "應修學期": row.Add(field, var.Detail.GetAttribute("應修學期")); break;
case "免修": row.Add(field, var.Detail.GetAttribute("免修") == "是" ? "是" : ""); break;
case "抵免": row.Add(field, var.Detail.GetAttribute("抵免") == "是" ? "是" : ""); break;
}
}
}
e.Items.Add(row);
}
}
};
}
private void ExportUpdateRecord_ExportPackage(object sender, ExportPackageEventArgs e)
{
for (int i = 0; i < e.List.Count; i++)
{
foreach (SHUpdateRecordRecord var in SHUpdateRecord.SelectByStudentID(e.List[i]))
{
if (var.UpdateType != Type)
{
continue;
}
RowData row = new RowData();
row.ID = e.List[i];
foreach (string field in e.ExportFields)
{
if (ExportableFields.Contains(field))
{
switch (field)
{
case "班別": row.Add(field, var.ClassType); break;
case "特殊身份代碼": row.Add(field, var.SpecialStatus); break;
case "異動科別": row.Add(field, var.Department); break;
case "年級": row.Add(field, var.GradeYear); break;
case "異動學號": row.Add(field, var.StudentNumber); break;
case "異動姓名": row.Add(field, var.StudentName); break;
case "身分證號": row.Add(field, var.IDNumber); break;
case "註1": row.Add(field, var.IDNumberComment); break;
case "性別": row.Add(field, var.Gender); break;
case "生日": row.Add(field, var.Birthdate); break;
case "異動種類": row.Add(field, var.UpdateType); break;
case "異動代碼": row.Add(field, var.UpdateCode); break;
case "異動日期": row.Add(field, var.UpdateDate); break;
case "原因及事項": row.Add(field, var.UpdateDescription); break;
case "新學號": row.Add(field, var.NewStudentNumber); break;
case "更正後資料": row.Add(field, var.NewData); break;
case "轉入前學生資料-科別": row.Add(field, var.PreviousDepartment); break;
case "轉入前學生資料-年級": row.Add(field, var.PreviousGradeYear); break;
case "轉入前學生資料-學校": row.Add(field, var.PreviousSchool); break;
case "轉入前學生資料-(備查日期)": row.Add(field, var.PreviousSchoolLastADDate); break;
case "轉入前學生資料-(備查文號)": row.Add(field, var.PreviousSchoolLastADNumber); break;
case "轉入前學生資料-學號": row.Add(field, var.PreviousStudentNumber); break;
case "入學資格-畢業國中": row.Add(field, var.GraduateSchool); break;
case "入學資格-畢業國中所在地代碼": row.Add(field, var.GraduateSchoolLocationCode); break;
case "入學資格-畢業國中年度": row.Add(field, var.GraduateSchoolYear); break;
case "入學資格-註2": row.Add(field, var.GraduateComment); break;
case "最後異動代碼": row.Add(field, var.LastUpdateCode); break;
case "畢(結)業證書字號": row.Add(field, var.GraduateCertificateNumber); break;
case "舊班別": row.Add(field, var.OldClassType); break;
case "舊科別代碼": row.Add(field, var.OldDepartmentCode); break;
case "備查日期": row.Add(field, var.LastADDate); break;
case "備查文號": row.Add(field, var.LastADNumber); break;
case "核准日期": row.Add(field, var.ADDate); break;
case "核准文號": row.Add(field, var.ADNumber); break;
case "備註": row.Add(field, var.Comment); break;
}
}
}
e.Items.Add(row);
}
}
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
// 2018.09.05 [ischoolKingdom] Vicky依據 [02-01][03] 匯入評量成績 項目，移除文字描述。
// 2018.09.05 [ischoolKingdom] Vicky依據 [12-01][01] 多學期成績排名 項目，增加 "領域", "科目", "節數(權重)", "學分" 。
// 2018.09.07 [ischoolKingdom] Vicky依據新需求調整項目位置。
wizard.ExportableFields.AddRange("學年度", "學期", "課程名稱", "領域", "科目", "節數(權重)", "學分", "評量名稱", "分數評量", "努力程度");
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
//學生資訊
List <JHStudentRecord> students = JHStudent.SelectByIDs(e.List);
//學生修課資訊
Dictionary <string, List <JHSCAttendRecord> > scattends = new Dictionary <string, List <JHSchool.Data.JHSCAttendRecord> >();
//學生修習的課程
Dictionary <string, JHCourseRecord> courses = new Dictionary <string, JHSchool.Data.JHCourseRecord>();
//評量成績 key: SCAttendID
Dictionary <string, List <KH.JHSCETakeRecord> > sces = new Dictionary <string, List <KH.JHSCETakeRecord> >();
//試別資訊
Dictionary <string, JHExamRecord> exams = new Dictionary <string, JHSchool.Data.JHExamRecord>();
#region 取得修課記錄
foreach (JHSCAttendRecord record in JHSCAttend.SelectByStudentIDAndCourseID(e.List, new string[] { }))
{
if (!scattends.ContainsKey(record.RefStudentID))
{
scattends.Add(record.RefStudentID, new List <JHSchool.Data.JHSCAttendRecord>());
}
scattends[record.RefStudentID].Add(record);
if (!courses.ContainsKey(record.RefCourseID))
{
courses.Add(record.RefCourseID, null);
}
}
#endregion
#region 取得課程資訊
foreach (JHCourseRecord record in JHCourse.SelectByIDs(new List <string>(courses.Keys)))
{
if (courses.ContainsKey(record.ID))
{
courses[record.ID] = record;
}
}
#endregion
#region 取得試別資訊
foreach (JHExamRecord exam in JHExam.SelectAll())
{
if (!exams.ContainsKey(exam.ID))
{
exams.Add(exam.ID, exam);
}
}
#endregion
#region 取得評量成績
foreach (KH.JHSCETakeRecord record in JHSCETake.SelectByStudentAndCourse(new List <string>(scattends.Keys), new List <string>(courses.Keys)).AsKHJHSCETakeRecords())
{
if (!sces.ContainsKey(record.RefSCAttendID))
{
sces.Add(record.RefSCAttendID, new List <KH.JHSCETakeRecord>());
}
sces[record.RefSCAttendID].Add(record);
}
#endregion
#region 產生 Row Data
foreach (JHStudentRecord stu in students)
{
if (!scattends.ContainsKey(stu.ID))
{
//學生沒有任何修課記錄。
continue;
}
foreach (JHSCAttendRecord record in scattends[stu.ID])
{
if (!sces.ContainsKey(record.ID))
{
continue;
}
sces[record.ID].Sort(delegate(KH.JHSCETakeRecord x, KH.JHSCETakeRecord y)
{
return(x.RefExamID.CompareTo(y.RefExamID));
});
foreach (KH.JHSCETakeRecord sce in sces[record.ID])
{
string examName = sce.RefExamID;
if (exams.ContainsKey(sce.RefExamID))
{
examName = exams[sce.RefExamID].Name;
}
RowData row = new RowData();
row.ID = stu.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row.Add(field, "" + courses[record.RefCourseID].SchoolYear); break;
case "學期": row.Add(field, "" + courses[record.RefCourseID].Semester); break;
case "課程名稱": row.Add(field, courses[record.RefCourseID].Name); break;
// 2018.09.05 [ischoolKingdom] Vicky依據 [02-01][03] 匯入評量成績 項目，移除文字描述。
// 2018.09.05 [ischoolKingdom] Vicky依據 [12-01][01] 多學期成績排名 項目，增加 "領域", "科目", "節數(權重)", "學分" 項目資料。
//case "文字描述": row.Add(field, sce.Text); break;
case "領域": row.Add(field, courses[record.RefCourseID].Domain); break;
case "科目": row.Add(field, courses[record.RefCourseID].Subject); break;
case "節數(權重)": row.Add(field, "" + courses[record.RefCourseID].Period); break;
case "學分": row.Add(field, "" + courses[record.RefCourseID].Credit); break;
case "評量名稱": row.Add(field, examName); break;
case "分數評量": row.Add(field, "" + sce.Score); break;
case "努力程度": row.Add(field, "" + sce.Effort); break;
}
}
}
e.Items.Add(row);
}
//2016/7/26 穎驊新增，因應高雄國中希望能加入匯出匯入"平時成績"的功能，因此在原本的匯出評量成績報表Excel中增加平時成績的項目，
// 基本邏輯跟 SCEtake 的定期評量一樣，另外意外發現平時成績、努力程度、Text在 JHSCAttendRecord裏頭就有了，不必在另外下SQL 找UDT 在用XElement 去解析Xml 檔填成績
string examName2 = "平時評量";
RowData row2 = new RowData();
row2.ID = stu.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row2.Add(field, "" + courses[record.RefCourseID].SchoolYear); break;
case "學期": row2.Add(field, "" + courses[record.RefCourseID].Semester); break;
case "課程名稱": row2.Add(field, courses[record.RefCourseID].Name); break;
// 2018.09.05 [ischoolKingdom] Vicky依據 [02-01][03] 匯入評量成績 項目，移除文字描述。
// 2018.09.05 [ischoolKingdom] Vicky依據 [12-01][01] 多學期成績排名 項目，增加 "領域", "科目", "節數(權重)", "學分" 項目資料。
// case "文字描述": row2.Add(field, record.Text); break;
case "領域": row2.Add(field, courses[record.RefCourseID].Domain); break;
case "科目": row2.Add(field, courses[record.RefCourseID].Subject); break;
case "節數(權重)": row2.Add(field, "" + courses[record.RefCourseID].Period); break;
case "學分": row2.Add(field, "" + courses[record.RefCourseID].Credit); break;
case "評量名稱": row2.Add(field, examName2); break;
case "分數評量": row2.Add(field, "" + record.OrdinarilyScore); break;
case "努力程度": row2.Add(field, "" + record.OrdinarilyEffort); break;
}
}
}
e.Items.Add(row2);
}
}
#endregion
FISCA.LogAgent.ApplicationLog.Log("成績系統.匯入匯出", "匯出評量成績", "總共匯出" + e.Items.Count + "筆評量成績。");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
List<string> SelectedFields = new List<string>() { "學生系統編號", "學號", "班級", "座號", "姓名" };
List<string> SelectableFields = new List<string>(){"領域","科目","學年度", "學期","權數","節數" , "成績" , "GPA" ,"Level"};
wizard.SelectedFields.AddRange(SelectedFields);
wizard.ExportableFields.AddRange(SelectableFields);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
try
{
List<string> StudentIDs = e.List;
string strScoreSQL = "select ref_student_id,school_year,semester,score_info from sems_subj_score where ref_student_id in (" + string.Join(",", StudentIDs.ToArray()) + ")";
QueryHelper QueryHelper = Utility.QueryHelper;
//學生系統編號 學號 班級 座號 姓名 領域 科目 學年度 學期 權數 節數 成績 GPA Level
DataTable tblScore = QueryHelper.Select(strScoreSQL);
foreach (DataRow row in tblScore.Rows)
{
string StudentID = row.Field<string>("ref_student_id");
string SchoolYear = row.Field<string>("school_year");
string Semester = row.Field<string>("semester");
string ScoreInfo = "<root>" + row.Field<string>("score_info") + "</root>";
XElement elmScoreInfo = XElement.Load(new StringReader(ScoreInfo));
foreach (XElement elmSubject in elmScoreInfo
.Element("SemesterSubjectScoreInfo")
.Elements("Subject"))
{
RowData vRow = new RowData();
//<Subject GPA=""4.5"" Level=""11"" 努力程度="""" 成績=""100"" 文字描述="""" 權數=""1"" 科目=""物理"" 節數=""1"" 註記="""" 領域=""""/>
// "學年度", "學期","領域","科目","權數","節數" , "成績" , "GPA" ,"Level"
vRow.ID = StudentID;
string Subject = elmSubject.AttributeText("科目");
string Score = elmSubject.AttributeText("成績");
string Period = elmSubject.AttributeText("節數");
string Domain = elmSubject.AttributeText("領域");
string Grade = Score;
vRow.Add("學年度", SchoolYear);
vRow.Add("學期", Semester);
vRow.Add("領域", Domain);
vRow.Add("科目", Subject);
vRow.Add("權數", elmSubject.AttributeText("權數"));
vRow.Add("節數", elmSubject.AttributeText("節數"));
vRow.Add("成績", elmSubject.AttributeText("成績"));
vRow.Add("GPA", elmSubject.AttributeText("GPA"));
vRow.Add("Level", elmSubject.AttributeText("Level"));
e.Items.Add(vRow);
}
}
}
catch (Exception ve)
{
MessageBox.Show(ve.Message);
}
};
}
private void _BKWValidate_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bkw = (BackgroundWorker)sender;
List<string> selectedFields = (List<string>)((object[])e.Argument)[0];
Dictionary<string, int> importFields = (Dictionary<string, int>)((object[])e.Argument)[1];
Workbook wb = (Workbook)((object[])e.Argument)[2];
wb.Worksheets[0].Comments.Clear();
wb.Worksheets[0].Hyperlinks.Clear();
int errorSheetIndex = wb.Worksheets.Add();
{
int errc = 0;
#region 命名
for (; ; errc++)
{
bool pass = true;
string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
foreach (Aspose.Cells.Worksheet var in wb.Worksheets)
{
if (n == var.Name)
{
pass = false;
break;
}
}
if (pass) break;
}
#endregion
wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
}
string errorSheetName = wb.Worksheets[errorSheetIndex].Name;
Worksheet errorSheet = wb.Worksheets[errorSheetIndex];
errorSheet.Cells[0, 0].PutValue("行號");
errorSheet.Cells[0, 1].PutValue("種類");
errorSheet.Cells[0, 2].PutValue("說明");
int errorSheetRowIndex = 1;
Style errorStyle = wb.Styles[wb.Styles.Add()];
Style warningStyle = wb.Styles[wb.Styles.Add()];
Style passStyle = wb.Styles[wb.Styles.Add()];
Style errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
//errorStyle.Pattern = BackgroundType.Solid;
//errorStyle.ForegroundColor = Color.Red;
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
//warningStyle.Pattern = BackgroundType.Solid;
//warningStyle.ForegroundColor = Color.Yellow;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
//passStyle.Pattern = BackgroundType.Solid;
//passStyle.ForegroundColor = Color.Green;
passStyle.Font.Color = Color.Green;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
int errorCount = 0;
int warningCount = 0;
Dictionary<RowData, int> rowDataIndex = new Dictionary<RowData, int>();
Dictionary<int, string> fieldIndex = new Dictionary<int, string>();
foreach (string field in selectedFields)
{
fieldIndex.Add(importFields[field], field);
}
double progress = 0.0;
#region 產生RowData資料
if (importFields.ContainsKey("班級系統編號"))
{
#region 用編號驗證資料
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string id = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級系統編號"]].StringValue);
if (id != "")
{
string rowError = "";
#region 驗明正身
//取得班級
ClassRecord stu = Class.SelectByID(id);
if (stu != null)
{
if (importFields.ContainsKey("班級名稱") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級名稱"]].StringValue) != stu.Name)
{
//rowError = "[班級名稱]與系統內[名稱]資料不同!!";
rowError += (rowError == "" ? "" : "、\n") + "系統內班級名稱為\"" + stu.Name + "\"";
}
}
else
{
rowError = "班級不存在!!";
}
#endregion
if (rowError == "")
{
RowData rowdata = new RowData();
rowdata.ID = id;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else //如果ID是空的
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(班級系統編號)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
else if (importFields.ContainsKey("班級名稱"))
{
#region 用教師姓名+暱稱驗證資料
Dictionary<string, List<ClassRecord>> studentNumberStudents = new Dictionary<string, List<ClassRecord>>();
#region 整理學號對應學生清單(如索引欄不試系統編號時用)
foreach (ClassRecord teach in Class.SelectAll())
{
string Number = teach.Name;
if (!studentNumberStudents.ContainsKey(Number))
studentNumberStudents.Add(Number, new List<ClassRecord>(new ClassRecord[] { teach }));
else
studentNumberStudents[Number].Add(teach);
}
#endregion
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string num = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級名稱"]].StringValue);
//wb.Worksheets[0].Cells.
if (num != "")
{
string rowError = "";
#region 驗明正身
ClassRecord stu = null;
if (studentNumberStudents.ContainsKey(num))
{
if (studentNumberStudents[num].Count > 1)
{
}
if (studentNumberStudents[num].Count == 1)
{
ClassRecord var = studentNumberStudents[num][0];
bool pass = true;
//if (importFields.ContainsKey("班級") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級"]].StringValue) != (var.Class != null ? var.Class.Name : ""))
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生班級為\"" + (var.Class != null ? var.Class.Name : "") + "\"";
// pass &= false;
//}
//if (importFields.ContainsKey("座號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["座號"]].StringValue) != "" + var.SeatNo)
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生座號為\"" + var.SeatNo + "\"";
// pass &= false;
//}
if (importFields.ContainsKey("班級名稱") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級名稱"]].StringValue) != var.Name)
{
if (studentNumberStudents[num].Count == 1)
rowError += (rowError == "" ? "" : "、\n") + "系統內班級名稱為\"" + var.Name + "\"";
pass &= false;
}
if (pass)
{
stu = var;
}
}
}
else
{
rowError = "班級不存在!!";
}
if (rowError == "" && stu == null)
rowError = "班級資料有誤!!";
#endregion
if (rowError == "")
{
RowData rowdata = new RowData();
rowdata.ID = stu.ID;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("班級名稱不得空白!");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
#endregion
#region 驗證是否有班級,重覆標記相同類別
Dictionary<string, List<string>> Dic2 = new Dictionary<string, List<string>>();
foreach (RowData row in rowDataIndex.Keys)
{
string id = row.ID;
foreach (string each in row.Values)
{
id += each;
}
if (!Dic2.ContainsKey(id))
{
Dic2.Add(id, new List<string>());
}
Dic2[id].Add(row.ID);
}
List<string> list2 = new List<string>();
foreach (string each in Dic2.Keys)
{
if (Dic2[each].Count > 1) //大於1,則此Row資料重覆
{
list2.Add(Dic2[each][0]);
}
}
//錯誤資料ID
foreach (string each in list2)
{
errorSheet.Cells[errorSheetRowIndex, 0].PutValue("(無法標記行號)");
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("班級不可重覆設定相同類別!!系統編號:" + each);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[errorSheetRowIndex, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
errorCount++;
}
#endregion
#region 驗證資料
List<string> list = new List<string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
list.Add(row.ID);
}
//_Process.StartValidate(list);
if (ValidateStart != null)
{
SmartSchool.API.PlugIn.Import.ValidateStartEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateStartEventArgs();
args.List = list.ToArray();
ValidateStart(this, args);
}
double totleCount = (double)rowDataIndex.Count;
double count = 0.0;
foreach (RowData row in rowDataIndex.Keys)
{
#region 驗證
string rowError = "";
Dictionary<string, string> errorFields, warningFields;
//RowDataValidatedEventArgs args = _Process.ValidateRow(row, selectedFields);
SmartSchool.API.PlugIn.Import.ValidateRowEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateRowEventArgs();
args.Data = row;
args.SelectFields.AddRange(selectedFields);
if (ValidateRow != null)
{
ValidateRow(this, args);
}
errorFields = args.ErrorFields;
warningFields = args.WarningFields;
rowError += args.ErrorMessage;
if (rowError == "" && errorFields.Count == 0 && warningFields.Count == 0)
{
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = passStyle;
}
else
{
string message = "";
bool hasError = false;
if (rowError != "" || errorFields.Count != 0)
{
errorCount++;
hasError = true;
message = rowError;
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue(hasError ? "錯誤" : "警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
message = "";
if (warningFields.Count != 0)
{
if (!hasError)
warningCount++;
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
if (!hasError)
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = hasError ? errorStyle : warningStyle;
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { errorCount, warningCount });
}
//_Process.FinishValidate();
if (ValidateComplete != null)
ValidateComplete(this, new EventArgs());
#endregion
List<RowData> rows = new List<RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { errorCount, warningCount });
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
e.Result = new object[] { wb, errorCount == 0, rows, selectedFields };
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
List <string> FieldsList = GetList();
wizard.ExportableFields.AddRange(FieldsList.ToArray());
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
#region 收集資料
//取得所選社團
List <string> SelectCLUBIDList = e.List;
//取得參與學生的社團學期成績
List <SCJoin> SCJoinList = tool._A.Select <SCJoin>("ref_club_id in ('" + string.Join("','", SelectCLUBIDList) + "')");
List <string> SCJoinIDList = new List <string>();
foreach (SCJoin each in SCJoinList)
{
if (!SCJoinIDList.Contains(each.UID))
{
SCJoinIDList.Add(each.UID);
}
}
List <ResultScoreRecord> ResultScoreList = tool._A.Select <ResultScoreRecord>("ref_scjoin_id in ('" + string.Join("','", SCJoinIDList) + "')");
List <string> StudentIDList = new List <string>();
foreach (ResultScoreRecord rsr in ResultScoreList)
{
if (!StudentIDList.Contains(rsr.RefStudentID))
{
StudentIDList.Add(rsr.RefStudentID);
}
}
#endregion
#region 取得學生基本資料
StudentDic.Clear();
if (StudentIDList.Count > 0)
{
DataTable dt = tool._Q.Select(string.Format("select student.id,student.seat_no,student.student_number,student.name,class.class_name,class.id as class_id,class.grade_year from student join class on student.ref_class_id=class.id where student.id in('{0}')", string.Join("','", StudentIDList)));
foreach (DataRow each in dt.Rows)
{
demoStudent ds = new demoStudent(each);
if (!StudentDic.ContainsKey(ds.ref_student_id))
{
StudentDic.Add(ds.ref_student_id, ds);
}
}
}
#endregion
ResultScoreList.Sort(SortResult);
foreach (ResultScoreRecord Result in ResultScoreList)
{
if (StudentDic.ContainsKey(Result.RefStudentID))
{
demoStudent sr = StudentDic[Result.RefStudentID];
#region 其它
string 取得學分 = "否";
if (Result.ResultScore.HasValue)
{
if (Result.ResultScore.Value >= 60)
{
取得學分 = "是";
}
}
string 科目級別 = "";
string 成績年級 = "";
if (!string.IsNullOrEmpty(sr.class_id))
{
int a;
if (int.TryParse(sr.grade_year, out a))
{
科目級別 = GetSchoolYearByGradeYear(a, Result.Semester);
成績年級 = sr.grade_year;
}
}
#endregion
RowData row = new RowData();
row.ID = Result.UID;
foreach (string field in e.ExportFields)
{
#region row
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學生系統編號": row.Add(field, sr.ref_student_id); break;
case "學號": row.Add(field, sr.student_number); break;
case "班級": row.Add(field, sr.class_name); break;
case "座號": row.Add(field, sr.seat_no); break;
case "姓名": row.Add(field, sr.name); break;
case "科目": row.Add(field, "聯課活動"); break;
case "科目級別": row.Add(field, 科目級別); break;
case "學年度": row.Add(field, "" + Result.SchoolYear); break;
case "學期": row.Add(field, "" + Result.Semester); break;
case "學分數": row.Add(field, "0"); break;
case "必選修": row.Add(field, "必修"); break;
case "分項類別": row.Add(field, "學業"); break;
case "成績年級": row.Add(field, 成績年級); break;
case "校部訂": row.Add(field, "部訂"); break;
case "科目成績": row.Add(field, Result.ResultScore.HasValue ? Result.ResultScore.Value.ToString() : ""); break;
case "原始成績": row.Add(field, Result.ResultScore.HasValue ? Result.ResultScore.Value.ToString() : ""); break;
case "取得學分": row.Add(field, 取得學分); break;
}
}
#endregion
}
e.Items.Add(row);
}
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
//SmartSchool.API.PlugIn.VirtualCheckBox filterRepeat = new SmartSchool.API.PlugIn.VirtualCheckBox("自動略過重讀成績", true);
//wizard.Options.Add(filterRepeat);
wizard.ExportableFields.AddRange("領域", "分數評量");
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
#region ExportPackage
List <JHStudentRecord> students = JHStudent.SelectByIDs(e.List);
GradScore.Instance.SyncDataBackground(e.List);
foreach (JHStudentRecord stu in students)
{
GradScoreRecord record = GradScore.Instance.Items[stu.ID];
if (record == null)
{
continue;
}
foreach (GradDomainScore domain in record.Domains.Values)
{
RowData row = new RowData();
row.ID = stu.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "領域": row.Add(field, "" + domain.Domain); break;
case "分數評量": row.Add(field, "" + domain.Score); break;
}
}
}
e.Items.Add(row);
}
foreach (string item in new string[] { "學習領域", "課程學習" })
{
RowData row = new RowData();
row.ID = stu.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "領域": row.Add(field, item); break;
case "分數評量":
if (item == "學習領域")
{
row.Add("分數評量", "" + record.LearnDomainScore);
}
else if (item == "課程學習")
{
row.Add("分數評量", "" + record.CourseLearnScore);
}
break;
}
}
}
e.Items.Add(row);
}
}
#endregion
FISCA.LogAgent.ApplicationLog.Log("成績系統.匯入匯出", "匯出畢業成績", "總共匯出" + e.Items.Count + "筆畢業成績。");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender,SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 依學生ID取得優先關懷
List<DAO.UDT_CounselCareRecordDef> _CounselCareRecordList = _UDTTransfer.GetCareRecordsByStudentIDList(e.List);
// 學生ID List
List<int> studIdList = (from data in _CounselCareRecordList select data.StudentID).ToList();
// 取得學生名稱log 用
Dictionary<string, string> StudentNameDict = new Dictionary<string, string>();
foreach (KeyValuePair<int, string> data in Utility.GetConvertStringDict1fromDB(studIdList))
StudentNameDict.Add(data.Key.ToString(), data.Value);
// 記錄匯出 log 細項用
Dictionary<string, StringBuilder> logData = new Dictionary<string, StringBuilder>();
// 匯出總筆數
int totalCount = 0;
foreach (DAO.UDT_CounselCareRecordDef ccrd in _CounselCareRecordList)
{
RowData row = new RowData();
totalCount++;
row.ID = ccrd.StudentID.ToString();
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch(field)
{
case "代號":
row.Add(field, ccrd.CodeName);
break;
case "立案日期":
if(ccrd.FileDate.HasValue)
row.Add(field, ccrd.FileDate.Value.ToShortDateString());
break;
case "個案類別":
row.Add(field, ccrd.CaseCategory);
break;
case "個案類別備註":
row.Add(field, ccrd.CaseCategoryRemark);
break;
case "個案來源":
row.Add(field, ccrd.CaseOrigin);
break;
case "個案來源備註":
row.Add(field, ccrd.CaseOriginRemark);
break;
case "優勢能力及財力":
row.Add(field, ccrd.Superiority);
break;
case "弱勢能力及財力":
row.Add(field, ccrd.Weakness);
break;
case "輔導人員輔導目標":
row.Add(field, ccrd.CounselGoal);
break;
case "校外協輔機構":
row.Add(field, ccrd.OtherInstitute);
break;
case "輔導人員輔導方式":
row.Add(field, ccrd.CounselType);
break;
case "協同輔導人員協助導師事項":
row.Add(field, ccrd.AssistedMatter);
break;
case "記錄者":
row.Add(field, ccrd.AuthorID);
break;
case "記錄者姓名":
row.Add(field, ccrd.AuthorName);
break;
case "狀態":
row.Add(field, ccrd.StudentStatus);
break;
}
}
}
e.Items.Add(row);
}
// 處理 log 細項
foreach (RowData rd in e.Items)
{
// 收集 log
StringBuilder sb = new StringBuilder();
if (StudentNameDict.ContainsKey(rd.ID))
sb.AppendLine(StudentNameDict[rd.ID]);
sb.AppendLine(this.Text);
foreach (KeyValuePair<string, string> data in rd)
sb.AppendLine(data.Key + "：" + data.Value);
// 加入 log
if (logData.ContainsKey(rd.ID))
{
sb.AppendLine();
logData[rd.ID].AppendLine(sb.ToString());
}
else
logData.Add(rd.ID, sb);
}
// 寫入 log
DAO.LogTransfer log = new DAO.LogTransfer();
foreach (KeyValuePair<string, StringBuilder> data in logData)
log.SaveLog("輔導系統." + this.Text, "匯出", "student", data.Key, data.Value);
StringBuilder sbT= new StringBuilder ();
sbT.AppendLine(this.Text);
sbT.AppendLine("總共匯出"+logData.Keys.Count+"位學生 , 共"+totalCount+"筆");
sbT.AppendLine("匯出學生名單..");
foreach (KeyValuePair<string, string> data in StudentNameDict)
if (logData.ContainsKey(data.Key))
sbT.AppendLine(data.Value);
log.SaveLog("輔導系統." + this.Text, "匯出","student","",sbT);
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(ExportItemList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
Dictionary <string, List <DAL.StudUpdateRecordEntity> > StudUpdateRecordEntityListDic = DAL.DALTransfer2.GetStudListUpdateRecordEntityListByUpdateType(e.List, JHPermrec.UpdateRecord.DAL.DALTransfer2.UpdateType.中輟);
int ExportCount = 0;
foreach (KeyValuePair <string, List <DAL.StudUpdateRecordEntity> > sureKey in StudUpdateRecordEntityListDic)
{
foreach (DAL.StudUpdateRecordEntity sure in sureKey.Value)
{
RowData row = new RowData();
row.ID = sure.StudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度":
if (sure.SchoolYear > 0)
{
row.Add(field, "" + sure.SchoolYear);
}
break;
case "學期":
if (sure.Semester > 0)
{
row.Add(field, "" + sure.Semester);
}
break;
case "異動年級": row.Add(field, "" + sure.GetGradeYear()); break;
case "異動日期":
if (sure.GetUpdateDate().HasValue)
{
row.Add(field, sure.GetUpdateDate().Value.ToShortDateString());
}
break;
case "原因及事項": row.Add(field, sure.GetUpdateDescription()); break;
case "備註": row.Add(field, sure.GetComment()); break;
case "異動班級": row.Add(field, sure.GetClassName()); break;
case "異動姓名": row.Add(field, sure.GetName()); break;
case "異動身分證號": row.Add(field, sure.GetIDNumber()); break;
case "異動學號": row.Add(field, sure.GetStudentNumber()); break;
case "異動性別": row.Add(field, sure.GetGender()); break;
case "異動生日":
if (sure.GetBirthday().HasValue)
{
row.Add(field, sure.GetBirthday().Value.ToShortDateString());
}
break;
case "異動地址": row.Add(field, sure.GetAddress()); break;
case "核准日期":
if (sure.GetADDate().HasValue)
{
row.Add(field, sure.GetADDate().Value.ToShortDateString());
}
break;
case "核准文號": row.Add(field, sure.GetADNumber()); break;
case "學籍核准日期":
if (sure.GetLastADDate().HasValue)
{
row.Add(field, sure.GetLastADDate().Value.ToShortDateString());
}
break;
case "學籍核准文號": row.Add(field, sure.GetLastADNumber()); break;
case "異動座號": row.Add(field, sure.GetSeatNo()); break;
case "異動類別": row.Add(field, "中輟"); break;
}
}
}
ExportCount++;
e.Items.Add(row);
}
}
JHSchool.PermRecLogProcess prlp = new JHSchool.PermRecLogProcess();
prlp.SaveLog("學生.匯出異動", "匯出中輟異動", "共匯出中輟異動" + ExportCount + "筆資料.");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
SmartSchool.API.PlugIn.VirtualCheckBox filterRepeat = new SmartSchool.API.PlugIn.VirtualCheckBox("自動略過重讀成績", true);
wizard.Options.Add(filterRepeat);
//2017/6/16 穎驊新增，因應[02-02][06] 計算學期科目成績新增清空原成績模式 項目， 新增 "刪除"欄位，使使用者能匯入 刪除成績資料
wizard.ExportableFields.AddRange("領域", "學年度", "學期", "權數", "節數", "成績", "原始成績", "補考成績", "努力程度", "文字描述", "註記", "刪除");
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
#region ExportPackage
List <JHStudentRecord> students = JHStudent.SelectByIDs(e.List);
Dictionary <string, List <JHSemesterScoreRecord> > semsDict = new Dictionary <string, List <JHSemesterScoreRecord> >();
foreach (JHSemesterScoreRecord record in JHSemesterScore.SelectByStudentIDs(e.List))
{
if (!semsDict.ContainsKey(record.RefStudentID))
{
semsDict.Add(record.RefStudentID, new List <JHSemesterScoreRecord>());
}
semsDict[record.RefStudentID].Add(record);
}
foreach (JHStudentRecord stu in students)
{
if (!semsDict.ContainsKey(stu.ID))
{
continue;
}
foreach (JHSemesterScoreRecord record in semsDict[stu.ID])
{
foreach (K12.Data.DomainScore domain in record.Domains.Values)
{
RowData row = new RowData();
row.ID = stu.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "領域": row.Add(field, "" + domain.Domain); break;
case "學年度": row.Add(field, "" + record.SchoolYear); break;
case "學期": row.Add(field, "" + record.Semester); break;
case "權數": row.Add(field, "" + domain.Credit); break;
case "節數": row.Add(field, "" + domain.Period); break;
case "成績": row.Add(field, "" + domain.Score); break;
case "原始成績": row.Add(field, "" + domain.ScoreOrigin); break;
case "補考成績": row.Add(field, "" + domain.ScoreMakeup); break;
case "努力程度": row.Add(field, "" + domain.Effort); break;
case "文字描述": row.Add(field, domain.Text); break;
case "註記": row.Add(field, domain.Comment); break;
case "刪除": row.Add(field, ""); break;
}
}
}
e.Items.Add(row);
}
}
}
#endregion
FISCA.LogAgent.ApplicationLog.Log("成績系統.匯入匯出", "匯出學期領域成績", "總共匯出" + e.Items.Count + "筆學期領域成績。");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange("學年度", "學期", "課程名稱", "評量名稱", "定期分數", "平時分數", "文字描述");
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
//學生資訊
List <JHStudentRecord> students = JHStudent.SelectByIDs(e.List);
//學生修課資訊
Dictionary <string, List <JHSCAttendRecord> > scattends = new Dictionary <string, List <JHSchool.Data.JHSCAttendRecord> >();
//學生修習的課程
Dictionary <string, JHCourseRecord> courses = new Dictionary <string, JHSchool.Data.JHCourseRecord>();
//評量成績 key: SCAttendID
Dictionary <string, List <HC.JHSCETakeRecord> > sces = new Dictionary <string, List <HC.JHSCETakeRecord> >();
//試別資訊
Dictionary <string, JHExamRecord> exams = new Dictionary <string, JHSchool.Data.JHExamRecord>();
#region 取得修課記錄
foreach (JHSCAttendRecord record in JHSCAttend.SelectByStudentIDAndCourseID(e.List, new string[] { }))
{
if (!scattends.ContainsKey(record.RefStudentID))
{
scattends.Add(record.RefStudentID, new List <JHSchool.Data.JHSCAttendRecord>());
}
scattends[record.RefStudentID].Add(record);
if (!courses.ContainsKey(record.RefCourseID))
{
courses.Add(record.RefCourseID, null);
}
}
#endregion
#region 取得課程資訊
foreach (JHCourseRecord record in JHCourse.SelectByIDs(new List <string>(courses.Keys)))
{
if (courses.ContainsKey(record.ID))
{
courses[record.ID] = record;
}
}
#endregion
#region 取得試別資訊
foreach (JHExamRecord exam in JHExam.SelectAll())
{
if (!exams.ContainsKey(exam.ID))
{
exams.Add(exam.ID, exam);
}
}
#endregion
#region 取得評量成績
foreach (HC.JHSCETakeRecord record in JHSCETake.SelectByStudentAndCourse(new List <string>(scattends.Keys), new List <string>(courses.Keys)).AsHCJHSCETakeRecords())
{
if (!sces.ContainsKey(record.RefSCAttendID))
{
sces.Add(record.RefSCAttendID, new List <HC.JHSCETakeRecord>());
}
sces[record.RefSCAttendID].Add(record);
}
#endregion
#region 產生 Row Data
foreach (JHStudentRecord stu in students)
{
if (!scattends.ContainsKey(stu.ID))
{
continue;
}
foreach (JHSCAttendRecord record in scattends[stu.ID])
{
if (!sces.ContainsKey(record.ID))
{
continue;
}
sces[record.ID].Sort(delegate(HC.JHSCETakeRecord x, HC.JHSCETakeRecord y)
{
return(x.RefExamID.CompareTo(y.RefExamID));
});
foreach (HC.JHSCETakeRecord sce in sces[record.ID])
{
string examName = sce.RefExamID;
if (exams.ContainsKey(sce.RefExamID))
{
examName = exams[sce.RefExamID].Name;
}
RowData row = new RowData();
row.ID = stu.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度": row.Add(field, "" + courses[record.RefCourseID].SchoolYear); break;
case "學期": row.Add(field, "" + courses[record.RefCourseID].Semester); break;
case "課程名稱": row.Add(field, courses[record.RefCourseID].Name); break;
case "評量名稱": row.Add(field, examName); break;
case "定期分數": row.Add(field, "" + sce.Score); break;
case "平時分數": row.Add(field, "" + sce.AssignmentScore); break;
case "文字描述": row.Add(field, sce.Text); break;
}
}
}
e.Items.Add(row);
}
}
}
#endregion
FISCA.LogAgent.ApplicationLog.Log("成績系統.匯入匯出", "匯出評量成績", "總共匯出" + e.Items.Count + "筆評量成績。");
};
}
//覆寫
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
List<string> FieldList = new List<string>();
FieldList.Add("学年度"); //目前字段
FieldList.Add("学期"); //目前字段
FieldList.Add("课程名称"); //目前字段
FieldList.Add("代码");
FieldList.Add("场地");
FieldList.Add("类型");
FieldList.Add("长短课程");
FieldList.Add("课程领域");
FieldList.Add("课程属性");
FieldList.Add("上课形式");
FieldList.Add("老师1");
FieldList.Add("老师2");
FieldList.Add("老师3");
FieldList.Add("总课时数");
FieldList.Add("简介");
FieldList.Add("限制:一年级选课人数限制");
FieldList.Add("限制:二年级选课人数限制");
FieldList.Add("限制:三年级选课人数限制");
FieldList.Add("限制:四年级选课人数限制");
FieldList.Add("限制:五年级选课人数限制");
FieldList.Add("限制:一年级选课人数男生限制");
FieldList.Add("限制:二年级选课人数男生限制");
FieldList.Add("限制:三年级选课人数男生限制");
FieldList.Add("限制:四年级选课人数男生限制");
FieldList.Add("限制:五年级选课人数男生限制");
FieldList.Add("限制:一年级选课人数女生限制");
FieldList.Add("限制:二年级选课人数女生限制");
FieldList.Add("限制:三年级选课人数女生限制");
FieldList.Add("限制:四年级选课人数女生限制");
FieldList.Add("限制:五年级选课人数女生限制");
//取得教師
TeacherDic = GetTeacher();
wizard.ExportableFields.AddRange(FieldList);
wizard.ExportPackage += (sender, e) =>
{
//取得學生清單
AccessHelper helper = new AccessHelper();
string strCondition = string.Join("','", e.List);
List<CLUBRecord> records = helper.Select<CLUBRecord>("uid in ('" + strCondition + "')");
for (int i = 0; i < records.Count; i++)
{
RowData row = new RowData();
row.ID = records[i].UID;
string teacher1 = "";
string teacher2 = "";
string teacher3 = "";
if (TeacherDic.ContainsKey(records[i].RefTeacherID))
teacher1 = TeacherDic[records[i].RefTeacherID];
if (TeacherDic.ContainsKey(records[i].RefTeacherID2))
teacher2 = TeacherDic[records[i].RefTeacherID2];
if (TeacherDic.ContainsKey(records[i].RefTeacherID3))
teacher3 = TeacherDic[records[i].RefTeacherID3];
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "学年度": row.Add(field, "" + records[i].SchoolYear); break;
case "学期": row.Add(field, "" + records[i].Semester); break;
case "课程名称": row.Add(field, records[i].ClubName); break;
case "代码": row.Add(field, records[i].ClubNumber); break;
case "场地": row.Add(field, records[i].Location); break;
case "类型": row.Add(field, records[i].ClubCategory); break;
case "长短课程": row.Add(field, records[i].FullPhase.HasValue && records[i].FullPhase.Value ? "长课程" : ""); break;
case "课程领域": row.Add(field, records[i].Domain); break;
case "课程属性": row.Add(field, records[i].Type); break;
case "上课形式": row.Add(field, records[i].Formal); break;
case "老师1": row.Add(field, teacher1); break;
case "老师2": row.Add(field, teacher2); break;
case "老师3": row.Add(field, teacher3); break;
case "总课时数": row.Add(field, records[i].TotalNumberHours.HasValue ? records[i].TotalNumberHours.Value.ToString() : ""); break;
case "简介": row.Add(field, records[i].About); break;
case "限制:一年级选课人数限制": row.Add(field, records[i].Grade1Limit.HasValue ? "" + records[i].Grade1Limit.Value : ""); break;
case "限制:二年级选课人数限制": row.Add(field, records[i].Grade2Limit.HasValue ? "" + records[i].Grade2Limit.Value : ""); break;
case "限制:三年级选课人数限制": row.Add(field, records[i].Grade3Limit.HasValue ? "" + records[i].Grade3Limit.Value : ""); break;
case "限制:四年级选课人数限制": row.Add(field, records[i].Grade4Limit.HasValue ? "" + records[i].Grade4Limit.Value : ""); break;
case "限制:五年级选课人数限制": row.Add(field, records[i].Grade5Limit.HasValue ? "" + records[i].Grade5Limit.Value : ""); break;
case "限制:一年级选课人数男生限制": row.Add(field, records[i].Grade1BoyLimit.HasValue ? "" + records[i].Grade1BoyLimit.Value : ""); break;
case "限制:二年级选课人数男生限制": row.Add(field, records[i].Grade2BoyLimit.HasValue ? "" + records[i].Grade2BoyLimit.Value : ""); break;
case "限制:三年级选课人数男生限制": row.Add(field, records[i].Grade3BoyLimit.HasValue ? "" + records[i].Grade3BoyLimit.Value : ""); break;
case "限制:四年级选课人数男生限制": row.Add(field, records[i].Grade4BoyLimit.HasValue ? "" + records[i].Grade4BoyLimit.Value : ""); break;
case "限制:五年级选课人数男生限制": row.Add(field, records[i].Grade5BoyLimit.HasValue ? "" + records[i].Grade5BoyLimit.Value : ""); break;
case "限制:一年级选课人数女生限制": row.Add(field, records[i].Grade1GirlLimit.HasValue ? "" + records[i].Grade1GirlLimit.Value : ""); break;
case "限制:二年级选课人数女生限制": row.Add(field, records[i].Grade2GirlLimit.HasValue ? "" + records[i].Grade2GirlLimit.Value : ""); break;
case "限制:三年级选课人数女生限制": row.Add(field, records[i].Grade3GirlLimit.HasValue ? "" + records[i].Grade3GirlLimit.Value : ""); break;
case "限制:四年级选课人数女生限制": row.Add(field, records[i].Grade4GirlLimit.HasValue ? "" + records[i].Grade4GirlLimit.Value : ""); break;
case "限制:五年级选课人数女生限制": row.Add(field, records[i].Grade5GirlLimit.HasValue ? "" + records[i].Grade5GirlLimit.Value : ""); break;
}
}
}
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange("姓名", "學號", "班級", "座號", "必選修", "校部訂", "學生狀態");
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
//課程資訊
List <SHCourseRecord> courses = SHCourse.SelectByIDs(e.List);
//學生修課資訊
Dictionary <string, List <SHSCAttendRecord> > scattends = new Dictionary <string, List <SHSCAttendRecord> >();
//課程修課學生
Dictionary <string, SHStudentRecord> students = new Dictionary <string, SHStudentRecord>();
#region 取得修課記錄
foreach (SHSCAttendRecord record in SHSCAttend.SelectByStudentIDAndCourseID(new string[] { }, e.List))
{
if (!scattends.ContainsKey(record.RefCourseID))
{
scattends.Add(record.RefCourseID, new List <SHSCAttendRecord>());
}
scattends[record.RefCourseID].Add(record);
if (!students.ContainsKey(record.RefStudentID))
{
students.Add(record.RefStudentID, null);
}
}
#endregion
#region 取得學生資訊
SHStudent.RemoveAll();
foreach (SHStudentRecord record in SHStudent.SelectByIDs(new List <string>(students.Keys)))
{
if (students.ContainsKey(record.ID))
{
students[record.ID] = record;
}
}
#endregion
#region 產生 Row Data
foreach (SHCourseRecord course in courses)
{
//Debug
if (!scattends.ContainsKey(course.ID))
{
continue;
}
foreach (SHSCAttendRecord record in scattends[course.ID])
{
RowData row = new RowData();
row.ID = course.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "姓名": row.Add(field, students[record.RefStudentID].Name); break;
case "學號": row.Add(field, students[record.RefStudentID].StudentNumber); break;
case "班級": row.Add(field, (students[record.RefStudentID].Class != null ? students[record.RefStudentID].Class.Name : "")); break;
case "座號": row.Add(field, "" + students[record.RefStudentID].SeatNo); break;
case "必選修":
if (record.Required)
{
row.Add(field, "" + "必修");
}
else
{
row.Add(field, "" + "選修");
}
break;
case "校部訂":
row.Add(field, "" + record.RequiredBy);
break;
case "學生狀態": row.Add(field, "" + students[record.RefStudentID].Status.ToString()); break;
}
}
}
e.Items.Add(row);
}
}
#endregion
ApplicationLog.Log("成績系統.匯入匯出", "匯出課程修課學生", "總共匯出" + e.Items.Count + "筆課程修課學生。");
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(_ExportList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 取得異動代碼
XElement _UpdateCode = DAL.DALTransfer.GetUpdateCodeList();
List<string> UpdateCodeList = (from elm in _UpdateCode.Elements("異動") where elm.Element("分類").Value == "學籍異動" select elm.Element("代號").Value).ToList();
// 取得學生學籍異動
List<SHUpdateRecordRecord> updateRecList = (from data in SHUpdateRecord.SelectByStudentIDs(e.List) where UpdateCodeList.Contains(data.UpdateCode) select data).ToList();
Dictionary<string, string> StudentStatusDict = DAL.FDQuery.GetAllStudentStatus1Dict();
foreach (SHUpdateRecordRecord rec in updateRecList)
{
RowData row = new RowData();
row.ID = rec.StudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度":
if (rec.SchoolYear.HasValue)
row.Add(field, rec.SchoolYear.Value.ToString()); break;
case "學期":
if (rec.Semester.HasValue)
row.Add(field, rec.Semester.Value.ToString()); break;
case "異動年級": row.Add(field, rec.GradeYear); break;
case "異動代碼": row.Add(field, rec.UpdateCode); break;
case "原因及事項": row.Add(field, rec.UpdateDescription); break;
case "異動日期": row.Add(field, rec.UpdateDate); break;
case "備註": row.Add(field, rec.Comment); break;
case "班別": row.Add(field, rec.ClassType); break;
case "科別": row.Add(field, rec.Department); break;
case "特殊身分代碼": row.Add(field, rec.SpecialStatus); break;
case "應畢業學年度": row.Add(field, rec.ExpectGraduateSchoolYear); break;
case "異動姓名": row.Add(field, rec.StudentName); break;
case "異動學號": row.Add(field, rec.StudentNumber); break;
case "異動身分證字號": row.Add(field, rec.IDNumber); break;
case "更正後資料": row.Add(field, rec.NewData); break;
case "異動生日": row.Add(field, rec.Birthdate); break;
case "異動身分證註記": row.Add(field, rec.IDNumberComment); break;
case "更正後身分證註記": row.Add(field, rec.Comment2); break;
case "舊科別代碼": row.Add(field, rec.OldDepartmentCode); break;
case "舊班別": row.Add(field, rec.OldClassType); break;
case "異動性別": row.Add(field, rec.Gender); break;
case "備查日期": row.Add(field, rec.LastADDate); break;
case "備查文號": row.Add(field, rec.LastADNumber); break;
case "核准日期": row.Add(field, rec.ADDate); break;
case "核准文號": row.Add(field, rec.ADNumber); break;
case "狀態":
if (StudentStatusDict.ContainsKey(rec.StudentID))
row.Add(field, StudentStatusDict[rec.StudentID]); break;
}
}
}
e.Items.Add(row);
}
};
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
wizard.ExportableFields.AddRange(_ExportList);
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
// 取得異動代碼
XElement _UpdateCode = DAL.DALTransfer.GetUpdateCodeList();
List <string> UpdateCodeList = (from elm in _UpdateCode.Elements("異動") where elm.Element("分類").Value == "新生異動" select elm.Element("代號").Value).ToList();
// 取得學生新生異動
List <SHUpdateRecordRecord> updateRecList = (from data in SHUpdateRecord.SelectByStudentIDs(e.List) where UpdateCodeList.Contains(data.UpdateCode) select data).ToList();
Dictionary <string, string> StudentStatusDict = DAL.FDQuery.GetAllStudentStatus1Dict();
foreach (SHUpdateRecordRecord rec in updateRecList)
{
RowData row = new RowData();
row.ID = rec.StudentID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "學年度":
if (rec.SchoolYear.HasValue)
{
row.Add(field, rec.SchoolYear.Value.ToString());
}
break;
case "學期":
if (rec.Semester.HasValue)
{
row.Add(field, rec.Semester.Value.ToString());
}
break;
case "年級": row.Add(field, rec.GradeYear); break;
case "異動代碼": row.Add(field, rec.UpdateCode); break;
case "原因及事項": row.Add(field, rec.UpdateDescription); break;
case "異動日期": row.Add(field, rec.UpdateDate); break;
case "備註": row.Add(field, rec.Comment); break;
case "班別": row.Add(field, rec.ClassType); break;
case "科別": row.Add(field, rec.Department); break;
case "特殊身分代碼": row.Add(field, rec.SpecialStatus); break;
case "入學資格證明文件": row.Add(field, rec.GraduateDocument); break;
case "異動姓名": row.Add(field, rec.StudentName); break;
case "異動學號": row.Add(field, rec.StudentNumber); break;
case "異動身分證字號": row.Add(field, rec.IDNumber); break;
case "異動生日": row.Add(field, rec.Birthdate); break;
case "異動身分證註記": row.Add(field, rec.IDNumberComment); break;
case "異動性別": row.Add(field, rec.Gender); break;
case "畢業國中": row.Add(field, rec.GraduateSchool); break;
case "畢業國中所在地代碼": row.Add(field, rec.GraduateSchoolLocationCode); break;
case "畢業國中學校代碼": row.Add(field, rec.GraduateSchoolCode); break;
case "國中畢業學年度": row.Add(field, rec.GraduateSchoolYear); break;
case "畢業國中入學資格註記": row.Add(field, rec.GraduateComment); break;
case "核准日期": row.Add(field, rec.ADDate); break;
case "核准文號": row.Add(field, rec.ADNumber); break;
case "狀態":
if (StudentStatusDict.ContainsKey(rec.StudentID))
{
row.Add(field, StudentStatusDict[rec.StudentID]);
}
break;
}
}
}
e.Items.Add(row);
}
};
}
private void _BKWValidate_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bkw = (BackgroundWorker)sender;
List<string> selectedFields = (List<string>)((object[])e.Argument)[0];
Dictionary<string, int> importFields = (Dictionary<string, int>)((object[])e.Argument)[1];
Workbook wb = (Workbook)((object[])e.Argument)[2];
Style passStyle = wb.Styles[wb.Styles.Add()];
passStyle.Font.Color = Color.Green;
Dictionary<RowData, int> rowDataIndex = new Dictionary<RowData, int>();
double progress = 0.0;
#region 產生RowData資料
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
RowData rowdata = new RowData();
foreach (string fieldName in importFields.Keys)
{
int index = importFields[fieldName];
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldName, wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldName, GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
wb.Worksheets[0].Cells[i, 0].Style = passStyle;
rowDataIndex.Add(rowdata, i);
}
if (RowsLoad != null)
{
var args = new RowDataLoadEventArgs();
args.RowDatas.AddRange(rowDataIndex.Keys);
RowsLoad(this, args);
}
#endregion
#region 識別RowData資料
foreach (RowData row in rowDataIndex.Keys)
{
IdentifyRowEventArgs args = new IdentifyRowEventArgs();
args.RowData = row;
if (IdentifyRow != null)
IdentifyRow(this, args);
if (!string.IsNullOrEmpty(args.ErrorMessage))
AddError(row, args.ErrorMessage);
if (!string.IsNullOrEmpty(args.WarningMessage))
AddWarning(row, args.WarningMessage);
}
#endregion
#region 驗證資料
List<string> list = new List<string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
list.Add(row.ID);
}
if (ValidateStart != null)
{
ValidateStartEventArgs args = new ValidateStartEventArgs();
args.List = list.ToArray();
ValidateStart(this, args);
}
double totleCount = (double)rowDataIndex.Count;
double count = 0.0;
foreach (RowData row in rowDataIndex.Keys)
{
#region 驗證
string rowError = "";
Dictionary<string, string> errorFields, warningFields;
ValidateRowEventArgs args = new ValidateRowEventArgs();
args.Data = row;
args.SelectFields.AddRange(selectedFields);
if (ValidateRow != null)
{
ValidateRow(this, args);
}
errorFields = args.ErrorFields;
warningFields = args.WarningFields;
rowError = args.ErrorMessage;
if (rowError != "" || errorFields.Count != 0)
{
string message = "";
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
AddError(row, message);
}
if (warningFields.Count != 0)
{
string message = "";
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
AddWarning(row, message);
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
//填入驗證結果
FillReport(wb, rowDataIndex);
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { GetErrorCount(rowDataIndex), GetWarningCount(rowDataIndex) });
}
if (ValidateComplete != null)
ValidateComplete(this, new EventArgs());
#endregion
List<RowData> rows = new List<RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { GetErrorCount(rowDataIndex), GetWarningCount(rowDataIndex) });
//填入驗證結果
FillReport(wb, rowDataIndex);
e.Result = new object[] { wb, GetErrorCount(rowDataIndex) == 0, rows, selectedFields };
}