private void ClubResultsInput_Load(object sender, EventArgs e) { //取得學生社團參與記錄 BGW_FormLoad.DoWork += new DoWorkEventHandler(BGW_FormLoad_DoWork); BGW_FormLoad.RunWorkerCompleted += new RunWorkerCompletedEventHandler(BGW_FormLoad_RunWorkerCompleted); //儲存資料(更新社團參與記錄) Save_BGW.DoWork += new DoWorkEventHandler(Save_BGW_DoWork); Save_BGW.RunWorkerCompleted += new RunWorkerCompletedEventHandler(Save_BGW_RunWorkerCompleted); //dataGridViewX1.DataError += new DataGridViewDataErrorEventHandler(dataGridViewX1_DataError); _ChangeListener.StatusChanged += new EventHandler <Campus.Windows.ChangeEventArgs>(_ChangeListener_StatusChanged); _ChangeListener.Add(new Campus.Windows.DataGridViewSource(dataGridViewX1)); if (ClubAdmin.Instance.SelectedSource.Count == 0) { return; } btnReport.Enabled = false; btnSave.Enabled = false; this.Text = "成績輸入(資料讀取中..)"; //取得成績計算比例原則 GetPoint = new 成績取得器(); GetPoint.SetWeightProportion(); AddColumns(); BGW_FormLoad.RunWorkerAsync(); }
/// <summary> /// 取得該筆Log調整內容 /// </summary> public string GetLogString(成績取得器 GetPoint) { StringBuilder sb = new StringBuilder(); string SeatNo = ""; string CLUBName = ""; if (_stud.SeatNo.HasValue) { SeatNo = _stud.SeatNo.Value.ToString(); } if (GetPoint._ClubDic.ContainsKey(_sch.RefClubID)) { CLUBName = GetPoint._ClubDic[_sch.RefClubID].ClubName; } sb.AppendLine(string.Format("社團「{0}」班級「{1}」座號「{2}」學生「{3}」", CLUBName, _class.Name, SeatNo, _stud.Name)); foreach (string each in _OldItemDic.Keys) { if (_OldItemDic[each] != _NewItemDic[each]) { sb.AppendLine(string.Format("「{0}」由「{1}」修改為「{2}」", each, _OldItemDic[each], _NewItemDic[each])); } } return(sb.ToString()); }
public ClassClubTraMag(int SchoolYear, int Semester, bool PrintLost) { //學年度:學期 _SchoolYear = SchoolYear; _Semester = Semester; _PrintLost = PrintLost; List <string> ClassIDList = GetClass(); List <string> StudentIDList = GetStudent(ClassIDList); TeacherDic = GetTeacher(); 成績取得器 GetPoint = new 成績取得器(); GetPoint.GetSCJoinByStudentIDList(StudentIDList); //建立資料模型 TraDic = GetTraDic(GetPoint); List <ClassRecord> ClassSortList = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource); ClassSortList.Sort(GetClassSort); Dictionary <string, List <ClassClubTraObj> > tDic = new Dictionary <string, List <ClassClubTraObj> >(); foreach (ClassRecord cr in ClassSortList) { tDic.Add(cr.ID, TraDic[cr.ID]); } TraDic = tDic; }
//void dataGridViewX1_DataError(object sender, DataGridViewDataErrorEventArgs e) //{ // MsgBox.Show("輸入資料錯誤!!"); // e.Cancel = false; //} void BGW_FormLoad_DoWork(object sender, DoWorkEventArgs e) { StringBuilder sb_3 = new StringBuilder(); GetPoint = new 成績取得器(); GetPoint.SetWeightProportion(); GetPoint.GetSCJoinByClubIDList(ClubAdmin.Instance.SelectedSource); #region 社團老師資訊 List <string> teacherIDList = new List <string>(); foreach (CLUBRecord club in GetPoint._ClubDic.Values) { if (!string.IsNullOrEmpty(club.RefTeacherID)) { if (!teacherIDList.Contains(club.RefTeacherID)) { teacherIDList.Add(club.RefTeacherID); } } } List <TeacherRecord> TeacherList = Teacher.SelectByIDs(teacherIDList); Dictionary <string, TeacherRecord> ClubTeacherDic = new Dictionary <string, TeacherRecord>(); foreach (TeacherRecord each in TeacherList) { if (!ClubTeacherDic.ContainsKey(each.ID)) { ClubTeacherDic.Add(each.ID, each); } } #endregion #region 取得班級資料 //從學生Record內取得班級ID,再取得班級Record ClassDic = GetClassDic(); #endregion RowList.Clear(); _logDic = new Dictionary <string, Log_Result>(); //取得社團參與記錄 foreach (List <SCJoin> each in GetPoint._SCJoinDic.Values) { if (each.Count == 1) { #region 只有一筆資料 SCJoin sch = each[0]; SCJoinRow scjRow = new SCJoinRow(); scjRow.SCJ = sch; //學生 if (GetPoint._StudentDic.ContainsKey(sch.RefStudentID)) { scjRow.student = GetPoint._StudentDic[sch.RefStudentID]; //社團 if (GetPoint._ClubDic.ContainsKey(sch.RefClubID)) { scjRow.club = GetPoint._ClubDic[sch.RefClubID]; if (ClubTeacherDic.ContainsKey(GetPoint._ClubDic[sch.RefClubID].RefTeacherID)) { scjRow.teacher = ClubTeacherDic[GetPoint._ClubDic[sch.RefClubID].RefTeacherID]; } } if (GetPoint._RSRDic.ContainsKey(sch.UID)) { scjRow.RSR = GetPoint._RSRDic[sch.UID]; } RowList.Add(scjRow); } #endregion } else if (each.Count >= 1) { #region 兩筆資料 //錯誤訊息 StudentRecord sr = Student.SelectByID(each[0].RefStudentID); sb_3.AppendLine("學生[" + sr.Name + "]有2筆以上社團記錄"); SCJoin sch = each[0]; SCJoinRow scjRow = new SCJoinRow(); scjRow.SCJ = sch; //學生 if (GetPoint._StudentDic.ContainsKey(sch.RefStudentID)) { scjRow.student = GetPoint._StudentDic[sch.RefStudentID]; //社團 if (GetPoint._ClubDic.ContainsKey(sch.RefClubID)) { scjRow.club = GetPoint._ClubDic[sch.RefClubID]; if (ClubTeacherDic.ContainsKey(GetPoint._ClubDic[sch.RefClubID].RefTeacherID)) { scjRow.teacher = ClubTeacherDic[GetPoint._ClubDic[sch.RefClubID].RefTeacherID]; } } if (GetPoint._RSRDic.ContainsKey(sch.UID)) { scjRow.RSR = GetPoint._RSRDic[sch.UID]; } RowList.Add(scjRow); } #endregion } else { //沒有記錄繼續 } } if (!string.IsNullOrEmpty(sb_3.ToString())) { MsgBox.Show(sb_3.ToString()); } }
void BGW_DoWork(object sender, DoWorkEventArgs e) { //取得目前選擇課程 tool = new 成績取得器(); tool.GetSCJoinByClubIDList(ClubAdmin.Instance.SelectedSource); //取得運算比例 tool.SetWeightProportion(); //社團ID : 社團幹部obj CadreDic = new Dictionary <string, 社團幹部obj>(); #region 處理幹部記錄 foreach (CLUBRecord each in tool._ClubDic.Values) { if (!CadreDic.ContainsKey(each.UID)) { CadreDic.Add(each.UID, new 社團幹部obj(each)); } } string qr = string.Join("','", tool._ClubDic.Keys); List <CadresRecord> list = _AccessHelper.Select <CadresRecord>("ref_club_id in ('" + qr + "')"); foreach (CadresRecord cr in list) { if (!CadreDic[cr.RefClubID]._Cadre1.ContainsKey(cr.RefStudentID)) { CadreDic[cr.RefClubID]._Cadre1.Add(cr.RefStudentID, cr.CadreName); } else { CadreDic[cr.RefClubID]._Cadre1[cr.RefStudentID] += "," + cr.CadreName; } } #endregion #region 結算回學務幹部 List <string> dataList = new List <string>(); foreach (社團幹部obj cadre in CadreDic.Values) { string clubID = cadre._Club.UID; string clubName = cadre._Club.ClubName; int _schoolYear = cadre._Club.SchoolYear; int _semester = cadre._Club.Semester; if (cadre._Cadre1.Count == 0) //如果沒有社團幹部資料 { string data = string.Format(@" SELECT {0}::TEXT AS ref_student_id , '{1}'::TEXT AS schoolyear , '{2}'::TEXT AS semester , {3}::TEXT AS cadrename , '社團幹部'::TEXT AS referencetype , '{4}'::TEXT AS text -- 放社團名稱 ", "null", _schoolYear, _semester, "null", clubName); dataList.Add(data); } if (cadre._Cadre1.Count > 0) { foreach (string _cadre in cadre._Cadre1.Keys) // StudentID,CadreName { string studentID = _cadre; string cadreName = cadre._Cadre1[_cadre]; string data = string.Format(@" SELECT '{0}'::TEXT AS ref_student_id , '{1}'::TEXT AS schoolyear , '{2}'::TEXT AS semester , '{3}'::TEXT AS cadrename , '社團幹部'::TEXT AS referencetype , '{4}'::TEXT AS text -- 放社團名稱 ", studentID, _schoolYear, _semester, cadreName, clubName); dataList.Add(data); } } } string dataRow = string.Join(" UNION ALL", dataList); #region SQL // 判斷條件: 學生ID、學年度、學期、社團名稱 string sql = string.Format(@" WITH data_row AS( {0} ) ,run_update AS( UPDATE $behavior.thecadre SET cadrename = data_row.cadrename FROM data_row WHERE data_row.schoolyear = $behavior.thecadre .schoolyear AND data_row.semester = $behavior.thecadre .semester AND data_row.text = $behavior.thecadre .text AND data_row.referencetype = $behavior.thecadre .referencetype AND data_row.ref_student_id = $behavior.thecadre .studentid AND data_row.ref_student_id IS NOT NULL RETURNING $behavior.thecadre.* ) , run_insert AS( INSERT INTO $behavior.thecadre( studentid , schoolyear , semester , referencetype , cadrename , text ) SELECT data_row.ref_student_id , data_row.schoolyear , data_row.semester , data_row.referencetype , data_row.cadrename , data_row.text FROM data_row LEFT OUTER JOIN $behavior.thecadre AS cadre ON cadre.schoolyear = data_row.schoolyear AND cadre.semester = data_row.semester AND cadre.text = data_row.text AND cadre.referencetype = data_row.referencetype AND cadre.studentid = data_row.ref_student_id WHERE cadre.uid IS NULL AND data_row.ref_student_id IS NOT NULL RETURNING $behavior.thecadre.* ) ,delete_data AS( SELECT uid FROM( SELECT cadre.uid , cadre.studentid , row_number() OVER ( PARTITION BY cadre.studentid, cadre.text ) as row_index FROM data_row LEFT OUTER JOIN $behavior.thecadre AS cadre ON cadre.schoolyear = data_row.schoolyear AND cadre.semester = data_row.semester AND cadre.text = data_row.text AND cadre.referencetype = data_row.referencetype AND cadre.studentid = data_row.ref_student_id ) AS target_club WHERE target_club.studentid NOT IN (SELECT ref_student_id FROM data_row) OR row_index >= 2 ) DELETE FROM $behavior.thecadre WHERE uid IN (SELECT * FROM delete_data) ", dataRow); #endregion UpdateHelper up = new UpdateHelper(); up.Execute(sql); #endregion List <string> list_2 = new List <string>(); foreach (List <SCJoin> each in tool._SCJoinDic.Values) { foreach (SCJoin scj in each) { list_2.Add(scj.UID); } } //string uq = string.Join("','", list_2); string studentIDs = string.Join("','", tool._StudentDic.Keys); List <string> clubNameList = new List <string>(); string schoolYear = "", semester = ""; foreach (string clubID in tool._ClubDic.Keys) { string clubName = tool._ClubDic[clubID].ClubName; schoolYear = "" + tool._ClubDic[clubID].SchoolYear; semester = "" + tool._ClubDic[clubID].Semester; clubNameList.Add("'" + clubName + "'"); } string clubNames = string.Join(",", clubNameList); // 201/03/22 羿均 更新: 修改讀取學期結算成績的KEY值為 studentID、clubName、schoolYear、semester string condition = string.Format("ref_student_id IN ('{0}') AND school_year = {1} AND semester = {2} AND club_name IN ({3}) ", studentIDs, schoolYear, semester, clubNames); List <ResultScoreRecord> ResultList = _AccessHelper.Select <ResultScoreRecord>(condition); Dictionary <string, Dictionary <string, ResultScoreRecord> > ResultScoreDic = new Dictionary <string, Dictionary <string, ResultScoreRecord> >(); foreach (ResultScoreRecord rsr in ResultList) { //學生有社團成績 if (!ResultScoreDic.ContainsKey(rsr.RefStudentID)) { ResultScoreDic.Add(rsr.RefStudentID, new Dictionary <string, ResultScoreRecord>()); ResultScoreDic[rsr.RefStudentID].Add(rsr.RefClubID, rsr); } else { if (!ResultScoreDic[rsr.RefStudentID].ContainsKey(rsr.RefClubID)) { ResultScoreDic[rsr.RefStudentID].Add(rsr.RefClubID, rsr); } } } UPDateScoreList = new List <ResultScoreRecord>(); InsertScoreList = new List <ResultScoreRecord>(); //DeleteScoreList = new List<ResultScoreRecord>(); //_AccessHelper.DeletedValues(ResultList); foreach (List <SCJoin> scjList in tool._SCJoinDic.Values) { foreach (SCJoin scj in scjList) { //有學生紀錄 if (ResultScoreDic.ContainsKey(scj.RefStudentID)) { //相同社團 if (ResultScoreDic[scj.RefStudentID].ContainsKey(scj.RefClubID)) { #region 如果有原資料 if (tool._StudentDic.ContainsKey(scj.RefStudentID)) { //社團 CLUBRecord cr = tool._ClubDic[scj.RefClubID]; //學生 StudentRecord sr = tool._StudentDic[scj.RefStudentID]; //原有社團成績記錄 ResultScoreRecord update_rsr = ResultScoreDic[scj.RefStudentID][scj.RefClubID]; update_rsr.SchoolYear = cr.SchoolYear; update_rsr.Semester = cr.Semester; update_rsr.RefClubID = cr.UID; //社團ID update_rsr.RefStudentID = sr.ID; //學生ID update_rsr.RefSCJoinID = scj.UID; //參與記錄ID update_rsr.ClubName = cr.ClubName; update_rsr.ClubLevel = cr.Level; //社團評等 #region 成績 if (!string.IsNullOrEmpty(scj.Score)) { update_rsr.ResultScore = tool.GetDecimalValue(scj); //成績 } else { //當成績已被清空,結算內容也被清空 update_rsr.ResultScore = null; } #endregion #region 幹部 if (CadreDic.ContainsKey(cr.UID)) { if (CadreDic[cr.UID]._Cadre1.ContainsKey(sr.ID)) { update_rsr.CadreName = CadreDic[cr.UID]._Cadre1[sr.ID]; } else { update_rsr.CadreName = ""; } } else { update_rsr.CadreName = ""; } #endregion UPDateScoreList.Add(update_rsr); } } else { //學生有社團紀錄,但社團不相同 RunInsert(scj); } #endregion } else { //完全沒有成績記錄 RunInsert(scj); } } } try { _AccessHelper.InsertValues(InsertScoreList); _AccessHelper.UpdateValues(UPDateScoreList); } catch (Exception ex) { MsgBox.Show("新增社團成績發生錯誤!!\n" + ex.Message); e.Cancel = true; return; } #region 社團成績Log處理 StringBuilder _sbLog = new StringBuilder(); _sbLog.AppendLine("已進行社團結算:"); if (InsertScoreList.Count > 0) { _sbLog.AppendLine("共新增「" + InsertScoreList.Count + "」筆成績記錄"); } if (UPDateScoreList.Count > 0) { _sbLog.AppendLine("共更新「" + UPDateScoreList.Count + "」筆成績記錄"); } _sbLog.AppendLine(""); _sbLog.AppendLine("簡要明細如下:"); if (InsertScoreList.Count > 0) { foreach (ResultScoreRecord each in InsertScoreList) { if (tool._StudentDic.ContainsKey(each.RefStudentID)) { if (string.IsNullOrEmpty(each.CadreName)) { StudentRecord sr = tool._StudentDic[each.RefStudentID]; string de = each.ResultScore.HasValue ? each.ResultScore.Value.ToString() : ""; _sbLog.AppendLine(string.Format("學生新增「{0}」社團成績「{1}」", sr.Name, de)); } else { StudentRecord sr = tool._StudentDic[each.RefStudentID]; string de = each.ResultScore.HasValue ? each.ResultScore.Value.ToString() : ""; _sbLog.AppendLine(string.Format("學生新增「{0}」社團成績「{1}」幹部記錄「{2}」", sr.Name, de, each.CadreName)); } } } } if (UPDateScoreList.Count > 0) { _sbLog.AppendLine(""); foreach (ResultScoreRecord each in UPDateScoreList) { if (tool._StudentDic.ContainsKey(each.RefStudentID)) { if (string.IsNullOrEmpty(each.CadreName)) { StudentRecord sr = tool._StudentDic[each.RefStudentID]; string de = each.ResultScore.HasValue ? each.ResultScore.Value.ToString() : ""; _sbLog.AppendLine(string.Format("學生更新「{0}」社團成績「{1}」", sr.Name, de)); } else { StudentRecord sr = tool._StudentDic[each.RefStudentID]; string de = each.ResultScore.HasValue ? each.ResultScore.Value.ToString() : ""; _sbLog.AppendLine(string.Format("學生更新「{0}」社團成績「{1}」幹部記錄「{2}」", sr.Name, de, each.CadreName)); } } } } if (InsertScoreList.Count + UPDateScoreList.Count > 0) { try { FISCA.LogAgent.ApplicationLog.Log("社團", "成績結算", _sbLog.ToString()); } catch (Exception ex) { MsgBox.Show("上傳Log記錄發生錯誤!!\n" + ex.Message); e.Cancel = true; return; } } #endregion }
/// <summary> /// 取得資料模型 /// </summary> private Dictionary <string, List <ClassClubTraObj> > GetTraDic(成績取得器 Point) { 成績取得器 _GetPoint = Point; Dictionary <string, List <ClassClubTraObj> > dic = new Dictionary <string, List <ClassClubTraObj> >(); foreach (ClassRecord each in ClassDic.Values) { if (!dic.ContainsKey(each.ID)) { dic.Add(each.ID, new List <ClassClubTraObj>()); } } //建立資料內容 foreach (StudentRecord student in _GetPoint._StudentDic.Values) { if (string.IsNullOrEmpty(student.RefClassID)) { continue; } if (!ClassDic.ContainsKey(student.RefClassID)) { continue; } ClassRecord cr = ClassDic[student.RefClassID]; ClassClubTraObj obj = new ClassClubTraObj(); obj.studentRecord = student; //學生 obj.classRecord = cr; //班級 //如果有社團記錄 if (_GetPoint._SCJoinDic.ContainsKey(student.ID)) { List <SCJoin> scjList = _GetPoint._SCJoinDic[student.ID]; foreach (SCJoin each in scjList) { if (_GetPoint._ClubDic.ContainsKey(each.RefClubID)) { CLUBRecord cc = _GetPoint._ClubDic[each.RefClubID]; if (cc.SchoolYear == _SchoolYear && cc.Semester == _Semester && cc.UID == each.RefClubID) { obj.club = _GetPoint._ClubDic[each.RefClubID]; obj.SCJoin = each; //社團記錄 if (_GetPoint._RSRDic.ContainsKey(each.UID)) { obj.RSR = _GetPoint._RSRDic[each.UID]; //社團記錄 } } } } } if (obj.RSR == null) { if (_GetPoint._RSRDic_s.ContainsKey(obj.studentRecord.ID)) { foreach (ResultScoreRecord each in _GetPoint._RSRDic_s[obj.studentRecord.ID]) { if (each.SchoolYear == _SchoolYear && each.Semester == _Semester) { obj.RSR = each; } } } } if (dic.ContainsKey(cr.ID)) { //只列印不及格學生 if (_PrintLost) { if (obj.RSR != null) //有結算記錄(不管是否有社團記錄) { if (obj.RSR.ResultScore.HasValue) { if (obj.RSR.ResultScore.Value >= 60) //當成績低於60分才加入清單內 { continue; } } } dic[cr.ID].Add(obj); } else { //列印所有學生 dic[cr.ID].Add(obj); } } } return(dic); }