private void Save_Click(object sender, EventArgs e)
{
if (MessageBox.Show("確定發佈？若您選擇「確定」，「選課結果」將加入至「學生修課」。若不確定，請按「取消」。", "警告", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.Cancel)
return;
this.circularProgress.IsRunning = true;
this.circularProgress.Visible = true;
int item = 2;
try
{
Task task = Task.Factory.StartNew(() =>
{
List<UDT.CSAttendSnapshot> CSAttendSnapshots = Access.Select<UDT.CSAttendSnapshot>(string.Format("school_year = {0} and semester = {1} and item = {2}", this.SchoolYear, this.Semester, item));
if (CSAttendSnapshots.Count == 0)
throw new Exception("無資料可發佈。");
List<UDT.SCAttendExt> SCAttendExts = new List<UDT.SCAttendExt>();
DataTable dataTable = Query.Select(string.Format("select se.uid, se.ref_student_id, se.ref_course_id, se.report_group, se.is_cancel, se.seat_x, se.seat_y from $ischool.emba.scattend_ext as se join course on course.id=se.ref_course_id where course.school_year={0} and course.semester={1}", this.SchoolYear, this.Semester));
List<string> oSCAttendExts = new List<string>();
foreach (DataRow row in dataTable.Rows)
{
string key = row["ref_student_id"] + "-" + row["ref_course_id"];
oSCAttendExts.Add(key);
}
Dictionary<string, UDT.CSAttendSnapshot> dicCSAttendSnapshots = new Dictionary<string, UDT.CSAttendSnapshot>();
foreach(UDT.CSAttendSnapshot CSAttendSnapshot in CSAttendSnapshots)
{
string key = CSAttendSnapshot.StudentID + "-" + CSAttendSnapshot.CourseID;
if (!oSCAttendExts.Contains(key))
{
UDT.SCAttendExt SCAttendExt = new UDT.SCAttendExt();
SCAttendExt.CourseID = CSAttendSnapshot.CourseID;
SCAttendExt.StudentID = CSAttendSnapshot.StudentID;
SCAttendExts.Add(SCAttendExt);
}
}
SCAttendExts.SaveAll();
});
task.ContinueWith((x) =>
{
this.circularProgress.IsRunning = false;
this.circularProgress.Visible = false;
if (x.Exception != null)
MessageBox.Show(x.Exception.InnerException.Message);
else
MessageBox.Show("發佈完成。");
}, System.Threading.CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext());
}
catch (Exception ex)
{
this.circularProgress.IsRunning = false;
this.circularProgress.Visible = false;
MessageBox.Show(ex.Message);
return;
}
}
//1. 分類出加選和退選的紀錄，
//2. 加選的加入 SCAttend，退選的從 SCAttend 刪除
//3. 更新 ConfirmDate 為今天。
private void btnApproved_Click(object sender, EventArgs e)
{
//student_id, add_or_drop, recs
Dictionary<string, Dictionary<string, List<UDT.AddDropCourse>>> recPerStud = new Dictionary<string,Dictionary<string,List<UDT.AddDropCourse>>>();
//foreach (DataGridViewRow row in this.dataGridViewX1.Rows)
foreach (DataGridViewRow row in this.dataGridViewX1.SelectedRows) //2012.5.8 改成選取的紀錄才核准
{
UDT.AddDropCourse ad = (UDT.AddDropCourse)row.Tag;
if (!recPerStud.ContainsKey(ad.StudentID.ToString()))
recPerStud.Add(ad.StudentID.ToString(), new Dictionary<string,List<UDT.AddDropCourse>>());
string add_or_drop = row.Cells["colAddOrDrop"].Value.ToString();
if (!recPerStud[ad.StudentID.ToString()].ContainsKey(add_or_drop))
recPerStud[ad.StudentID.ToString()].Add(add_or_drop, new List<UDT.AddDropCourse>());
recPerStud[ad.StudentID.ToString()][add_or_drop].Add(ad);
}
if (recPerStud.Keys.Count > 0)
{
this.progressBarX1.Maximum = recPerStud.Keys.Count;
this.progressBarX1.Value = 0;
this.progressBarX1.Visible = true ;
}
else {
this.progressBarX1.Visible = false ;
return ;
}
int index = 0;
/* for each student */
foreach (string studID in recPerStud.Keys)
{
index += 1;
this.progressBarX1.Value = index;
Application.DoEvents();
string studName = this.dicStudent[studID];
StringBuilder sb = new StringBuilder(string.Format("核准 {0} 的加退選紀錄如下：", studName));
//取得該生的修課紀錄
//List<SCAttendRecord> recs = SCAttend.SelectByStudentID(studID);
List<UDT.SCAttendExt> recs = (new AccessHelper()).Select<UDT.SCAttendExt>("ref_student_id=" + studID);
Dictionary<string, UDT.SCAttendExt> dicRecs = new Dictionary<string, UDT.SCAttendExt>();
foreach (UDT.SCAttendExt rec in recs)
{
if (!dicRecs.ContainsKey(rec.StudentID.ToString() + "_" + rec.CourseID.ToString()))
dicRecs.Add(rec.StudentID.ToString() + "_" + rec.CourseID.ToString(), rec);
}
// * 1. 處理加選，先濾掉已經存在修課紀錄的加選紀錄 * /
if (recPerStud[studID].ContainsKey("加"))
{
sb.Append(" 加選： ( ");
List<ActiveRecord> addRecs = new List<ActiveRecord>();
foreach (UDT.AddDropCourse ad in recPerStud[studID]["加"])
{
if (!dicRecs.ContainsKey(ad.StudentID.ToString() + "_" + ad.CourseID.ToString()))
{
UDT.SCAttendExt scatt = new UDT.SCAttendExt();
scatt.StudentID = ad.StudentID;
scatt.CourseID = ad.CourseID;
addRecs.Add(scatt);
//
string courseName = this.dicCourses[ad.CourseID];
sb.Append(courseName);
sb.Append(",");
}
}
sb.Append(" ) ,\n ");
if (addRecs.Count > 0)
(new AccessHelper()).InsertValues(addRecs); //加選
}
// * 2. 處理退選 * /
if (recPerStud[studID].ContainsKey("退"))
{
sb.Append(" 退選 : ( ");
List<ActiveRecord> delRecs = new List<ActiveRecord>();
foreach (UDT.AddDropCourse ad in recPerStud[studID]["退"])
{
if (dicRecs.ContainsKey(ad.StudentID.ToString() + "_" + ad.CourseID.ToString()))
{
delRecs.Add(dicRecs[ad.StudentID.ToString() + "_" + ad.CourseID.ToString()]);
//add log
string courseName = this.dicCourses[ad.CourseID];
sb.Append(courseName);
sb.Append(", ");
}
}
if (delRecs.Count > 0)
(new AccessHelper()).DeletedValues(delRecs);
//SCAttend.Delete(delRecs); //退選
sb.Append(" )");
}
FISCA.LogAgent.ApplicationLog.Log("核准加退選.學生", "新增", "student", studID, sb.ToString());
}// END FOR
// * 3. 更新加退選紀錄的 confirm_date * /
List<ActiveRecord> updRecs = new List<ActiveRecord>();
foreach (DataGridViewRow row in this.dataGridViewX1.SelectedRows)
{
UDT.AddDropCourse ad = (UDT.AddDropCourse)row.Tag;
ad.ConfirmDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
updRecs.Add(ad);
}
(new AccessHelper()).SaveAll(updRecs);
Util.ShowMsg("核准完成！", "核准加退選");
UDT.SCAttendExt.RaiseAfterUpdateEvent(this, null);
this.loadData();
}
private void ImportSCAttendRecords()
{
this.addMsg(" ==== 開始匯入修課學生 ===");
/* 取得目前所有的修課紀錄 */
List<UDT.SCAttendExt> attRecs = (new AccessHelper()).Select<UDT.SCAttendExt>();
Dictionary<string, Dictionary<string, UDT.SCAttendExt>> dicAttRecs = new Dictionary<string, Dictionary<string, UDT.SCAttendExt>>();
foreach (UDT.SCAttendExt att in attRecs)
{
if (!dicAttRecs.ContainsKey(att.CourseID.ToString()))
dicAttRecs.Add(att.CourseID.ToString(), new Dictionary<string, UDT.SCAttendExt>());
dicAttRecs[att.CourseID.ToString()].Add(att.StudentID.ToString(), att);
}
/* 取得所有課程 ，以便從課程代碼 及班及名稱，找出 課程系統編號 */
this.GetAllCourses();
List<UDT.CourseExt> allCourses = (new AccessHelper()).Select<UDT.CourseExt>();
Dictionary<string, UDT.CourseExt> dicAllCourses = new Dictionary<string, UDT.CourseExt>();
foreach (UDT.CourseExt course in allCourses)
{
if (this.dicCourses.ContainsKey(course.CourseID.ToString()))
{
string key = string.Format("{0}_{1}", course.SubjectCode, course.ClassName);
dicAllCourses.Add(key, course);
}
}
/* 取得所有學生資料，以便從學號找出學生編號 */
List<K12.Data.StudentRecord> allStudents = K12.Data.Student.SelectAll();
Dictionary<string, K12.Data.StudentRecord> dicAllStudents = new Dictionary<string, K12.Data.StudentRecord>();
foreach (K12.Data.StudentRecord stud in allStudents)
{
if (!string.IsNullOrWhiteSpace(stud.StudentNumber))
dicAllStudents.Add(stud.StudentNumber, stud);
}
/* 讀取 Excel 資料 */
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(this.textBoxX1.Text);
Worksheet ws = wb.Worksheets[0]; //修課紀錄
int rowIndex = 1;
while (ws.Cells[rowIndex, 3].Value != null)
{
string studNo = GetCellValue(ws.Cells[rowIndex, 3].Value);
if (!dicAllStudents.ContainsKey(studNo))
{
this.addMsg(string.Format("找不到學生，學號：{0}, rowNo: {1} ", studNo, rowIndex.ToString()));
}
else
{
string studID = dicAllStudents[studNo].ID ;
string courseCode = GetCellValue(ws.Cells[rowIndex, 7].Value);
string classCode = GetCellValue(ws.Cells[rowIndex, 8].Value);
if (classCode.Length > 2)
classCode = classCode.Substring(1, 2);
string key = string.Format("{0}_{1}", courseCode, classCode);
if (!dicAllCourses.ContainsKey(key))
{
this.addMsg(string.Format("找不到課程，課號：{0}, 班及：{1}, rowNo: {2} ", courseCode, classCode, rowIndex.ToString()));
}
else
{
string courseID = dicAllCourses[key].CourseID.ToString();
//判斷該生是否已經修課，若是，則 skip ，否則新增 !
if (dicAttRecs.ContainsKey(courseID) && dicAttRecs[courseID].ContainsKey(studID))
{
//do nothing
string msg = string.Format("學號：{0} 已修課程： 課號 = {1}, 班號 = {2}, rowindex ={3}，故忽略不匯入！", studNo, courseCode, classCode, rowIndex.ToString());
this.addMsg(msg);
}
else
{
// 新增修課紀錄
UDT.SCAttendExt attRec = new UDT.SCAttendExt();
attRec.StudentID = int.Parse(studID);
attRec.CourseID = int.Parse(courseID);
List<ActiveRecord> recs = new List<ActiveRecord>();
recs.Add(attRec);
(new AccessHelper()).InsertValues( recs);
}
}
}
rowIndex += 1;
this.lblStatus.Text = rowIndex.ToString();
Application.DoEvents();
}
}
