public SetForm()
{
InitializeComponent();
_A = new AccessHelper();
colTarget.Items.Add("事病假");
colTarget.Items.Add("曠課");
colTarget.Items.Add("遲到");
//取得全部假別
foreach (AbsenceMappingInfo info in AbsenceMapping.SelectAll())
{
colSource.Items.Add(info.Name);
}
//排序
_auList = _A.Select <AbsenceUDT>();
_auList.Sort(delegate(AbsenceUDT x, AbsenceUDT y)
{
string xx = x.Target.PadLeft(20, '0');
xx += x.Source.PadLeft(20, '0');
string yy = y.Target.PadLeft(20, '0');
yy += y.Source.PadLeft(20, '0');
return(xx.CompareTo(yy));
});
foreach (AbsenceUDT au in _auList)
{
DataGridViewRow row = new DataGridViewRow();
row.CreateCells(dgv, au.Target, au.Source);
dgv.Rows.Add(row);
}
}
/// <summary>
/// 取得缺曠對照 List,一般_曠課..
/// </summary>
/// <returns></returns>
public static List <string> GetATMappingKey()
{
List <string> retVal = new List <string>();
List <string> key1List = new List <string>();
List <string> Key2List = new List <string>();
foreach (PeriodMappingInfo data in PeriodMapping.SelectAll())
{
if (!key1List.Contains(data.Type))
{
key1List.Add(data.Type);
}
}
foreach (AbsenceMappingInfo data in AbsenceMapping.SelectAll())
{
if (!Key2List.Contains(data.Name))
{
Key2List.Add(data.Name);
}
}
// 一般_曠課
foreach (string key1 in key1List)
{
foreach (string key2 in Key2List)
{
retVal.Add(key1 + "_" + key2);
}
}
return(retVal);
}
/// <summary>
/// 取得缺曠對照 List,一般_曠課..
/// </summary>
/// <returns></returns>
public static List <string> GetATMappingKey()
{
List <string> retVal = new List <string>();
List <string> key1List = new List <string>();
List <string> Key2List = new List <string>();
foreach (PeriodMappingInfo data in PeriodMapping.SelectAll())
{
if (!key1List.Contains(data.Type))
{
key1List.Add(data.Type);
}
}
foreach (AbsenceMappingInfo data in AbsenceMapping.SelectAll())
{
if (!Key2List.Contains(data.Name))
{
Key2List.Add(data.Name);
}
//2017/1/3 穎驊新增，因應文華ePost 缺曠格式與舊常春藤不一樣，
//雖然本次和恩正討論後，用不到了，但日後有可能有用，先留著。
//if (!retVal.Contains(data.Name))
//{
// retVal.Add(data.Name);
//}
}
// 一般_曠課
foreach (string key1 in key1List)
{
foreach (string key2 in Key2List)
{
retVal.Add(key1 + "_" + key2);
}
}
return(retVal);
}
private void AttendanceReport_Load(object sender, EventArgs e)
{
this.MaximumSize = this.MinimumSize = this.Size;
// 預設學年度、學期
iptSchoolYear.Value = int.Parse(K12.Data.School.DefaultSchoolYear);
iptSemester.Value = int.Parse(K12.Data.School.DefaultSemester);
// 載入代碼對照
Dictionary <string, List <ConfigDataItem> > datas = _cd.GetConfigDataItemDict();
Dictionary <string, string> dataDict = GetDefaultItemDict();
List <string> dataList1 = GetDefaultPeridList();
Dictionary <string, string> tmpDict1 = new Dictionary <string, string>();
Dictionary <string, string> tmpDict2 = GetDefaultPeridDict();
// 載入假別
List <AbsenceMappingInfo> AbsenceMappingList = AbsenceMapping.SelectAll();
List <string> aList = new List <string>();
aList.Add("");
foreach (AbsenceMappingInfo rec in AbsenceMappingList)
{
aList.Add(rec.Name);
if (!tmpDict1.ContainsKey(rec.Name))
{
tmpDict1.Add(rec.Name, rec.Name);
}
}
colType.Items.AddRange(aList.ToArray());
// 載入節次對照
List <PeriodMappingInfo> PeriodMappingList = PeriodMapping.SelectAll();
// 節次>類別
List <string> pList = new List <string>();
pList.Add("");
foreach (PeriodMappingInfo rec in PeriodMappingList)
{
pList.Add(rec.Name);
}
colPerType.Items.AddRange(pList.ToArray());
colType.DropDownStyle = ComboBoxStyle.DropDownList;
colPerType.DropDownStyle = ComboBoxStyle.DropDownList;
#region 讀取設定值
// 有儲存過 假別
if (datas.ContainsKey(_ConfigName1))
{
List <string> defList = new List <string>();
int rowIdx = 0;
foreach (ConfigDataItem cdi in datas[_ConfigName1])
{
defList.Add(cdi.Name);
rowIdx = dgData.Rows.Add();
dgData.Rows[rowIdx].Cells[colName.Index].Value = cdi.Name;
dgData.Rows[rowIdx].Cells[colValue.Index].Value = cdi.Value;
dgData.Rows[rowIdx].Cells[colType.Index].Value = cdi.TargetName;
}
// 檢查加入預設是否完整
foreach (string key in dataDict.Keys)
{
if (!defList.Contains(key))
{
rowIdx = dgData.Rows.Add();
dgData.Rows[rowIdx].Cells[colName.Index].Value = key;
dgData.Rows[rowIdx].Cells[colValue.Index].Value = dataDict[key];
if (tmpDict1.ContainsKey(key))
{
dgData.Rows[rowIdx].Cells[colType.Index].Value = tmpDict1[key];
}
else
{
dgData.Rows[rowIdx].Cells[colType.Index].Value = "";
}
}
}
}
else
{
// 完全沒使用過
foreach (string key in dataDict.Keys)
{
int rowIdx = dgData.Rows.Add();
dgData.Rows[rowIdx].Cells[colName.Index].Value = key;
dgData.Rows[rowIdx].Cells[colValue.Index].Value = dataDict[key];
if (tmpDict1.ContainsKey(key))
{
dgData.Rows[rowIdx].Cells[colType.Index].Value = tmpDict1[key];
}
else
{
dgData.Rows[rowIdx].Cells[colType.Index].Value = "";
}
}
}
// 有儲存過 節次
if (datas.ContainsKey(_ConfigName2))
{
List <string> defList = new List <string>();
int rowIdx = 0;
foreach (ConfigDataItem cdi in datas[_ConfigName2])
{
defList.Add(cdi.Name);
rowIdx = dgPerdata.Rows.Add();
dgPerdata.Rows[rowIdx].Cells[colPerName.Index].Value = cdi.Name;
dgPerdata.Rows[rowIdx].Cells[colPerType.Index].Value = cdi.TargetName;
}
// 檢查加入預設是否完整
foreach (string key in dataList1)
{
if (!defList.Contains(key))
{
rowIdx = dgPerdata.Rows.Add();
dgPerdata.Rows[rowIdx].Cells[colPerName.Index].Value = key;
dgPerdata.Rows[rowIdx].Cells[colPerType.Index].Value = "";
if (tmpDict2.ContainsKey(key))
{
dgPerdata.Rows[rowIdx].Cells[colPerType.Index].Value = tmpDict2[key];
}
}
}
}
else
{
// 完全沒使用過
foreach (string key in dataList1)
{
int rowIdx = dgPerdata.Rows.Add();
dgPerdata.Rows[rowIdx].Cells[colPerName.Index].Value = key;
dgPerdata.Rows[rowIdx].Cells[colPerType.Index].Value = "";
if (tmpDict2.ContainsKey(key))
{
dgPerdata.Rows[rowIdx].Cells[colPerType.Index].Value = tmpDict2[key];
}
}
}
Dictionary <string, string> ds = _cd.GetKeyValueItem(_ConfigName3);
if (ds.ContainsKey("學年度"))
{
if (ds["學年度"] != "")
{
iptSchoolYear.Value = int.Parse(ds["學年度"]);
}
}
if (ds.ContainsKey("學期"))
{
if (ds["學期"] != "")
{
iptSemester.Value = int.Parse(ds["學期"]);
}
}
#endregion
}
public void Export()
{
//取得班級ID
List <string> ClassIDList = K12.Presentation.NLDPanels.Class.SelectedSource;
List <string> 會影響全勤的假別 = new List <string>();
foreach (AbsenceMappingInfo each in AbsenceMapping.SelectAll())
{
if (!each.Noabsence)
{
會影響全勤的假別.Add(each.Name);
}
}
//取得班級學生
List <StudentRecord> StudentList = Student.SelectByClassIDs(ClassIDList);
//所有學生
Dictionary <string, StudentRecord> StudentDic = new Dictionary <string, StudentRecord>();
//不全勤學生
Dictionary <string, StudentRecord> noOK_StudentList = new Dictionary <string, StudentRecord>();
//全勤的學生
Dictionary <string, StudentRecord> OK_StudentList = new Dictionary <string, StudentRecord>();
foreach (StudentRecord each in StudentList)
{
if (each.Status == StudentRecord.StudentStatus.一般 || each.Status == StudentRecord.StudentStatus.延修)
{
if (!StudentDic.ContainsKey(each.ID))
{
StudentDic.Add(each.ID, each);
}
}
}
//取得學生所有的缺曠資料
//如果沒有缺曠記錄的
//就算是全勤
List <AttendanceRecord> TestList = Attendance.SelectByStudentIDs(StudentDic.Keys);
List <AttendanceRecord> AttendanceList = new List <AttendanceRecord>();
if (checkBoxX1.Checked)
{
//相同學年度/學期
foreach (AttendanceRecord attendnace in TestList)
{
int schoolYear = int.Parse(cboSchoolYear.SelectedItem.ToString());
int semester = int.Parse(cboSemester.SelectedItem.ToString());
if (attendnace.SchoolYear == schoolYear && attendnace.Semester == semester)
{
AttendanceList.Add(attendnace);
}
}
}
else
{
AttendanceList.AddRange(TestList);
}
foreach (AttendanceRecord each in AttendanceList)
{
foreach (K12.Data.AttendancePeriod period in each.PeriodDetail)
{
if (會影響全勤的假別.Contains(period.AbsenceType))
{
if (!noOK_StudentList.ContainsKey(period.RefStudentID))
{
noOK_StudentList.Add(period.RefStudentID, StudentDic[period.RefStudentID]);
}
}
}
}
foreach (StudentRecord each in StudentDic.Values)
{
if (!noOK_StudentList.ContainsKey(each.ID))
{
if (!OK_StudentList.ContainsKey(each.ID))
{
OK_StudentList.Add(each.ID, each);
}
}
}
List <StudentRecord> StudentRecordList = new List <StudentRecord>();
foreach (StudentRecord each in OK_StudentList.Values)
{
StudentRecordList.Add(each);
}
StudentRecordList = SortClassIndex.K12Data_StudentRecord(StudentRecordList);
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
string schoolName = School.ChineseName;
Cell A1 = sheet.Cells["A1"];
A1.Style.Borders.SetColor(Color.Black);
string A1Name = schoolName + " ";
if (checkBoxX1.Checked)
{
A1Name += "(" + cboSchoolYear.SelectedItem.ToString() + "/" + cboSemester.SelectedItem.ToString() + ") ";
}
A1Name += "全勤學生名單";
sheet.Name = "全勤學生名單";
A1.PutValue(A1Name);
A1.Style.HorizontalAlignment = TextAlignmentType.Center;
sheet.Cells.Merge(0, 0, 1, 5);
FormatCell(sheet.Cells["A2"], "編號");
FormatCell(sheet.Cells["B2"], "班級");
FormatCell(sheet.Cells["C2"], "座號");
FormatCell(sheet.Cells["D2"], "姓名");
FormatCell(sheet.Cells["E2"], "學號");
int index = 1;
foreach (StudentRecord e in StudentRecordList)
{
int rowIndex = index + 2;
FormatCell(sheet.Cells["A" + rowIndex], index.ToString());
FormatCell(sheet.Cells["B" + rowIndex], string.IsNullOrEmpty(e.RefClassID) ? "" : e.Class.Name);
FormatCell(sheet.Cells["C" + rowIndex], e.SeatNo.HasValue ? e.SeatNo.Value.ToString() : "");
FormatCell(sheet.Cells["D" + rowIndex], e.Name);
FormatCell(sheet.Cells["E" + rowIndex], e.StudentNumber);
index++;
}
string path = Path.Combine(Application.StartupPath, "Reports");
path = Path.Combine(path, book.Worksheets[0].Name + ".xls");
int i = 1;
while (true)
{
string newPath = Path.GetDirectoryName(path) + "\\" + Path.GetFileNameWithoutExtension(path) + (i++) + Path.GetExtension(path);
if (!File.Exists(newPath))
{
path = newPath;
break;
}
}
try
{
book.Save(path);
}
catch (IOException)
{
try
{
FileInfo file = new FileInfo(path);
string nameTempalte = file.FullName.Replace(file.Extension, "") + "{0}.xls";
int count = 1;
string fileName = string.Format(nameTempalte, count);
while (File.Exists(fileName))
{
fileName = string.Format(nameTempalte, count++);
}
book.Save(fileName);
path = fileName;
}
catch (Exception ex)
{
MsgBox.Show("檔案儲存失敗:" + ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
catch (Exception ex)
{
MsgBox.Show("檔案儲存失敗:" + ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
try
{
Process.Start(path);
}
catch (Exception ex)
{
MsgBox.Show("檔案開啟失敗:" + ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}