private void btnRun_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(cbxTotal.Text)) { FISCA.Presentation.Controls.MsgBox.Show("沒有合併評量名稱"); return; } if (string.IsNullOrEmpty(cbxSScore .Text)) { FISCA.Presentation.Controls.MsgBox.Show("沒有定期評量名稱"); return; } if (string.IsNullOrEmpty(cbxAScore.Text)) { FISCA.Presentation.Controls.MsgBox.Show("沒有平時評量名稱"); return; } btnRun.Enabled = false; List<string> StudenIDList = K12.Presentation.NLDPanels.Student.SelectedSource; int SchoolYear=iptSchoolYear.Value; int Semester=iptSemester.Value; // 處理小考名稱與小考成績 string oldASExamID = ""; string oldSSExamID = ""; string newTSExamID = ""; if (_ExamDict.ContainsKey(cbxAScore.Text)) oldASExamID = _ExamDict[cbxAScore.Text]; if (_ExamDict.ContainsKey(cbxSScore.Text)) oldSSExamID = _ExamDict[cbxSScore.Text]; if (_ExamDict.ContainsKey(cbxTotal.Text)) newTSExamID = _ExamDict[cbxTotal.Text]; // 修改課程上小考項目與學生修課上的小考成績 EditCourseSCAttendExtesions(oldASExamID, newTSExamID, StudenIDList, SchoolYear, Semester); List<string> delSCTakeIDss = new List<string>(); List<string> delSCTakeIDas = new List<string>(); // Score Dictionary<string, string> SSDict = new Dictionary<string, string>(); // A Score Dictionary<string, string> SADict = new Dictionary<string, string>(); if (_SCAttendIDList.Count == 0) { FISCA.Presentation.Controls.MsgBox.Show("沒有修課"); btnRun.Enabled = true; return; } string queryKey = string.Join(",", _SCAttendIDList.ToArray()); // 讀取舊定期成績 string querySS = @"select id,ref_sc_attend_id,extension from sce_take where ref_sc_attend_id in("+queryKey+") and ref_exam_id="+oldSSExamID; QueryHelper qhss = new QueryHelper(); DataTable dtss = qhss.Select(querySS); foreach (DataRow drss in dtss.Rows) { delSCTakeIDss.Add(drss["id"].ToString()); string scid = drss["ref_sc_attend_id"].ToString(); string ext = drss["extension"].ToString(); string score=""; if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("讀取定期成績發生錯誤" + ex.Message); } if (elm != null) { if (elm.Element("Score") != null) score = elm.Element("Score").Value; } } SSDict.Add(scid, score); } // 讀取舊平時成績 string queryAS = @"select id,ref_sc_attend_id,extension from sce_take where ref_sc_attend_id in(" + queryKey + ") and ref_exam_id=" + oldASExamID; QueryHelper qhas = new QueryHelper(); DataTable dtas = qhas.Select(queryAS); foreach (DataRow dras in dtas.Rows) { delSCTakeIDas.Add(dras["id"].ToString()); string scid = dras["ref_sc_attend_id"].ToString(); string ext = dras["extension"].ToString(); string ascore = ""; if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("讀取平時成績發生錯誤:" + ex.Message); } if (elm != null) { if (elm.Element("AssignmentScore") != null) ascore = elm.Element("AssignmentScore").Value; } } SADict.Add(scid, ascore); } // 檢查該修課是否已存在 Dictionary<string, string> chkSCDict = new Dictionary<string, string>(); string chkStr = "select ref_sc_attend_id,extension from sce_take where ref_sc_attend_id in(" + queryKey + ") and ref_exam_id=" + newTSExamID; QueryHelper qhchk = new QueryHelper(); DataTable qhchkDt = qhchk.Select(chkStr); foreach (DataRow dr in qhchkDt.Rows) { string ssid = dr[0].ToString(); if (!chkSCDict.ContainsKey(ssid)) chkSCDict.Add(ssid, dr[1].ToString()); } // 寫入定期/平時成績 List<string> sbInsert = new List<string>(); List<string> sbUpdate = new List<string>(); foreach (string scID in _SCAttendIDList) { string ss = "", sa = ""; // 定期 if (SSDict.ContainsKey(scID)) ss = SSDict[scID]; // 平時 if (SADict.ContainsKey(scID)) sa = SADict[scID]; if (ss == "" && sa == "") continue; XElement elm = new XElement("Extension"); elm.SetElementValue("Score", ss); elm.SetElementValue("AssignmentScore", sa); elm.SetElementValue("Text", ""); string str = ""; // 已存在以定期為主,只修改平時成績 if (chkSCDict.ContainsKey(scID)) { XElement elmss = null; string sse = chkSCDict[scID]; try { elmss = XElement.Parse(sse); } catch (Exception ex) { } if (elmss == null) elmss = elm; else { // 只修改平時 elmss.SetElementValue("AssignmentScore", sa); } str = @"update sce_take set extension='" + elmss.ToString() + "' where ref_sc_attend_id=" + scID + " and ref_exam_id=" + newTSExamID; sbUpdate.Add(str); } else { str = @"insert into sce_take(ref_sc_attend_id,ref_exam_id,score,extension) values(" + scID + "," + newTSExamID + ",0,'" + elm.ToString() + "');"; sbInsert.Add(str); } } try { if (sbInsert.Count > 0) { UpdateHelper uhIns = new UpdateHelper(); foreach (string str in sbInsert) uhIns.Execute(str); } if (sbUpdate.Count > 0) { UpdateHelper uhUpdate = new UpdateHelper(); foreach (string str in sbUpdate) uhUpdate.Execute(str); } } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("資料新增錯誤:" + ex.Message); } FISCA.Presentation.Controls.MsgBox.Show("資料轉換完成."); btnRun.Enabled = true; }
private void btnRun_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(cbxTotal.Text)) { FISCA.Presentation.Controls.MsgBox.Show("沒有合併評量名稱"); return; } if (string.IsNullOrEmpty(cbxSScore.Text)) { FISCA.Presentation.Controls.MsgBox.Show("沒有定期評量名稱"); return; } if (string.IsNullOrEmpty(cbxAScore.Text)) { FISCA.Presentation.Controls.MsgBox.Show("沒有平時評量名稱"); return; } btnRun.Enabled = false; List <string> StudenIDList = K12.Presentation.NLDPanels.Student.SelectedSource; int SchoolYear = iptSchoolYear.Value; int Semester = iptSemester.Value; // 處理小考名稱與小考成績 string oldASExamID = ""; string oldSSExamID = ""; string newTSExamID = ""; if (_ExamDict.ContainsKey(cbxAScore.Text)) { oldASExamID = _ExamDict[cbxAScore.Text]; } if (_ExamDict.ContainsKey(cbxSScore.Text)) { oldSSExamID = _ExamDict[cbxSScore.Text]; } if (_ExamDict.ContainsKey(cbxTotal.Text)) { newTSExamID = _ExamDict[cbxTotal.Text]; } // 修改課程上小考項目與學生修課上的小考成績 EditCourseSCAttendExtesions(oldASExamID, newTSExamID, StudenIDList, SchoolYear, Semester); List <string> delSCTakeIDss = new List <string>(); List <string> delSCTakeIDas = new List <string>(); // Score Dictionary <string, string> SSDict = new Dictionary <string, string>(); // A Score Dictionary <string, string> SADict = new Dictionary <string, string>(); if (_SCAttendIDList.Count == 0) { FISCA.Presentation.Controls.MsgBox.Show("沒有修課"); btnRun.Enabled = true; return; } string queryKey = string.Join(",", _SCAttendIDList.ToArray()); // 讀取舊定期成績 string querySS = @"select id,ref_sc_attend_id,extension from sce_take where ref_sc_attend_id in(" + queryKey + ") and ref_exam_id=" + oldSSExamID; QueryHelper qhss = new QueryHelper(); DataTable dtss = qhss.Select(querySS); foreach (DataRow drss in dtss.Rows) { delSCTakeIDss.Add(drss["id"].ToString()); string scid = drss["ref_sc_attend_id"].ToString(); string ext = drss["extension"].ToString(); string score = ""; if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("讀取定期成績發生錯誤" + ex.Message); } if (elm != null) { if (elm.Element("Score") != null) { score = elm.Element("Score").Value; } } } SSDict.Add(scid, score); } // 讀取舊平時成績 string queryAS = @"select id,ref_sc_attend_id,extension from sce_take where ref_sc_attend_id in(" + queryKey + ") and ref_exam_id=" + oldASExamID; QueryHelper qhas = new QueryHelper(); DataTable dtas = qhas.Select(queryAS); foreach (DataRow dras in dtas.Rows) { delSCTakeIDas.Add(dras["id"].ToString()); string scid = dras["ref_sc_attend_id"].ToString(); string ext = dras["extension"].ToString(); string ascore = ""; if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("讀取平時成績發生錯誤:" + ex.Message); } if (elm != null) { if (elm.Element("AssignmentScore") != null) { ascore = elm.Element("AssignmentScore").Value; } } } SADict.Add(scid, ascore); } // 檢查該修課是否已存在 Dictionary <string, string> chkSCDict = new Dictionary <string, string>(); string chkStr = "select ref_sc_attend_id,extension from sce_take where ref_sc_attend_id in(" + queryKey + ") and ref_exam_id=" + newTSExamID; QueryHelper qhchk = new QueryHelper(); DataTable qhchkDt = qhchk.Select(chkStr); foreach (DataRow dr in qhchkDt.Rows) { string ssid = dr[0].ToString(); if (!chkSCDict.ContainsKey(ssid)) { chkSCDict.Add(ssid, dr[1].ToString()); } } // 寫入定期/平時成績 List <string> sbInsert = new List <string>(); List <string> sbUpdate = new List <string>(); foreach (string scID in _SCAttendIDList) { string ss = "", sa = ""; // 定期 if (SSDict.ContainsKey(scID)) { ss = SSDict[scID]; } // 平時 if (SADict.ContainsKey(scID)) { sa = SADict[scID]; } if (ss == "" && sa == "") { continue; } XElement elm = new XElement("Extension"); elm.SetElementValue("Score", ss); elm.SetElementValue("AssignmentScore", sa); elm.SetElementValue("Text", ""); string str = ""; // 已存在以定期為主,只修改平時成績 if (chkSCDict.ContainsKey(scID)) { XElement elmss = null; string sse = chkSCDict[scID]; try { elmss = XElement.Parse(sse); } catch (Exception ex) { } if (elmss == null) { elmss = elm; } else { // 只修改平時 elmss.SetElementValue("AssignmentScore", sa); } str = @"update sce_take set extension='" + elmss.ToString() + "' where ref_sc_attend_id=" + scID + " and ref_exam_id=" + newTSExamID; sbUpdate.Add(str); } else { str = @"insert into sce_take(ref_sc_attend_id,ref_exam_id,score,extension) values(" + scID + "," + newTSExamID + ",0,'" + elm.ToString() + "');"; sbInsert.Add(str); } } try { if (sbInsert.Count > 0) { UpdateHelper uhIns = new UpdateHelper(); foreach (string str in sbInsert) { uhIns.Execute(str); } } if (sbUpdate.Count > 0) { UpdateHelper uhUpdate = new UpdateHelper(); foreach (string str in sbUpdate) { uhUpdate.Execute(str); } } } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("資料新增錯誤:" + ex.Message); } FISCA.Presentation.Controls.MsgBox.Show("資料轉換完成."); btnRun.Enabled = true; }
/// <summary> /// 修改課程與修課小考 Extesions /// </summary> /// <param name="newExamID"></param> private void EditCourseSCAttendExtesions(string oldExamID,string newExamID,List<string> StudIDList,int SchoolYear,int Semester) { List<string> sb1 = new List<string>(); List<string> sb2 = new List<string>(); string idList=string.Join(",",StudIDList.ToArray()); // 修改課程 string query1 = @"select distinct course.id as course_id,course.extensions as course_extensions from course inner join sc_attend on course.id=sc_attend.ref_course_id where course.ref_exam_template_id is not null and course.ref_exam_template_id not in(5,6) and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sc_attend.ref_student_id in(" + idList + ")"; QueryHelper qh1 = new QueryHelper(); DataTable dt1 = qh1.Select(query1); foreach (DataRow dr1 in dt1.Rows) { string id = dr1["course_id"].ToString(); string ext = dr1["course_extensions"].ToString(); if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("處理課程小考發生錯誤:"+ex.Message); } if (elm != null) { foreach (XElement elms1 in elm.Elements("Extension")) { if (elms1.Attribute("Name") != null && elms1.Attribute("Name").Value == "GradeItem") { foreach (XElement elms2 in elms1.Elements("GradeItem")) { foreach (XElement elms3 in elms2.Elements("Item")) { // 更換 ExamID if (elms3.Attribute("ExamID").Value == oldExamID) { elms3.SetAttributeValue("ExamID", newExamID); } } } } } string updateStr = @"update course set extensions='" + elm.ToString() + "' where id=" + id + ";"; sb1.Add(updateStr); } } } if (sb1.Count > 0) { UpdateHelper uh1 = new UpdateHelper(); foreach (string str in sb1) { uh1.Execute(str); } } _SCAttendIDList.Clear(); // 修改修課 string query2 = @"select sc_attend.id as sc_attend_id, sc_attend.extensions as sc_attend_extensions from course inner join sc_attend on course.id=sc_attend.ref_course_id where course.ref_exam_template_id is not null and course.ref_exam_template_id not in(5,6) and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sc_attend.ref_student_id in(" + idList + ")"; QueryHelper qh2 = new QueryHelper(); DataTable dt2 = qh1.Select(query2); foreach (DataRow dr2 in dt2.Rows) { string id = dr2["sc_attend_id"].ToString(); _SCAttendIDList.Add(id); string ext = dr2["sc_attend_extensions"].ToString(); if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("處理修課小考發生錯誤:" + ex.Message); } if (elm != null) { foreach (XElement elms1 in elm.Elements("Extension")) { if (elms1.Attribute("Name") != null && elms1.Attribute("Name").Value == "GradeBook") { foreach (XElement elms2 in elms1.Elements("Exam")) { // 更換 ExamID if (elms2.Attribute("ExamID").Value == newExamID && elms2.Attribute("Score").Value=="") { elms2.Remove(); } } } } foreach (XElement elms1 in elm.Elements("Extension")) { if (elms1.Attribute("Name") != null && elms1.Attribute("Name").Value == "GradeBook") { foreach (XElement elms2 in elms1.Elements("Exam")) { // 更換 ExamID if (elms2.Attribute("ExamID").Value == oldExamID) { elms2.SetAttributeValue("ExamID", newExamID); } } } } string updateStr = @"update sc_attend set extensions='" + elm.ToString() + "' where id=" + id + ";"; sb2.Add(updateStr); } } } if (sb2.Count > 0) { UpdateHelper uh2 = new UpdateHelper(); foreach(string str in sb2) uh2.Execute(str); } }
/// <summary> /// 修改課程與修課小考 Extesions /// </summary> /// <param name="newExamID"></param> private void EditCourseSCAttendExtesions(string oldExamID, string newExamID, List <string> StudIDList, int SchoolYear, int Semester) { List <string> sb1 = new List <string>(); List <string> sb2 = new List <string>(); string idList = string.Join(",", StudIDList.ToArray()); // 修改課程 string query1 = @"select distinct course.id as course_id,course.extensions as course_extensions from course inner join sc_attend on course.id=sc_attend.ref_course_id where course.ref_exam_template_id is not null and course.ref_exam_template_id not in(5,6) and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sc_attend.ref_student_id in(" + idList + ")"; QueryHelper qh1 = new QueryHelper(); DataTable dt1 = qh1.Select(query1); foreach (DataRow dr1 in dt1.Rows) { string id = dr1["course_id"].ToString(); string ext = dr1["course_extensions"].ToString(); if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("處理課程小考發生錯誤:" + ex.Message); } if (elm != null) { foreach (XElement elms1 in elm.Elements("Extension")) { if (elms1.Attribute("Name") != null && elms1.Attribute("Name").Value == "GradeItem") { foreach (XElement elms2 in elms1.Elements("GradeItem")) { foreach (XElement elms3 in elms2.Elements("Item")) { // 更換 ExamID if (elms3.Attribute("ExamID").Value == oldExamID) { elms3.SetAttributeValue("ExamID", newExamID); } } } } } string updateStr = @"update course set extensions='" + elm.ToString() + "' where id=" + id + ";"; sb1.Add(updateStr); } } } if (sb1.Count > 0) { UpdateHelper uh1 = new UpdateHelper(); foreach (string str in sb1) { uh1.Execute(str); } } _SCAttendIDList.Clear(); // 修改修課 string query2 = @"select sc_attend.id as sc_attend_id, sc_attend.extensions as sc_attend_extensions from course inner join sc_attend on course.id=sc_attend.ref_course_id where course.ref_exam_template_id is not null and course.ref_exam_template_id not in(5,6) and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sc_attend.ref_student_id in(" + idList + ")"; QueryHelper qh2 = new QueryHelper(); DataTable dt2 = qh1.Select(query2); foreach (DataRow dr2 in dt2.Rows) { string id = dr2["sc_attend_id"].ToString(); _SCAttendIDList.Add(id); string ext = dr2["sc_attend_extensions"].ToString(); if (!string.IsNullOrEmpty(ext)) { XElement elm = null; try { elm = XElement.Parse(ext); } catch (Exception ex) { FISCA.Presentation.Controls.MsgBox.Show("處理修課小考發生錯誤:" + ex.Message); } if (elm != null) { foreach (XElement elms1 in elm.Elements("Extension")) { if (elms1.Attribute("Name") != null && elms1.Attribute("Name").Value == "GradeBook") { foreach (XElement elms2 in elms1.Elements("Exam")) { // 更換 ExamID if (elms2.Attribute("ExamID").Value == newExamID && elms2.Attribute("Score").Value == "") { elms2.Remove(); } } } } foreach (XElement elms1 in elm.Elements("Extension")) { if (elms1.Attribute("Name") != null && elms1.Attribute("Name").Value == "GradeBook") { foreach (XElement elms2 in elms1.Elements("Exam")) { // 更換 ExamID if (elms2.Attribute("ExamID").Value == oldExamID) { elms2.SetAttributeValue("ExamID", newExamID); } } } } string updateStr = @"update sc_attend set extensions='" + elm.ToString() + "' where id=" + id + ";"; sb2.Add(updateStr); } } } if (sb2.Count > 0) { UpdateHelper uh2 = new UpdateHelper(); foreach (string str in sb2) { uh2.Execute(str); } } }