private void dataGridViewX1_UserDeletedRow(object sender, DataGridViewRowEventArgs e) { if (e.Row.Tag != null) { ResultScoreRecord rsr = (ResultScoreRecord)e.Row.Tag; if (!deleteList.Contains(rsr)) { deleteList.Add(rsr); } } }
private int SortResultScore(ResultScoreRecord r1, ResultScoreRecord r2) { string school_1 = r1.SchoolYear.ToString().PadLeft(3, '0'); school_1 += r1.Semester.ToString().PadLeft(1, '0'); string school_2 = r2.SchoolYear.ToString().PadLeft(3, '0'); school_2 += r2.Semester.ToString().PadLeft(1, '0'); return(school_1.CompareTo(school_2)); }
/// <summary> /// 排序學期資料 /// </summary> private int SortRSRList(ResultScoreRecord a1, ResultScoreRecord b2) { string aa1 = a1.SchoolYear.ToString().PadLeft(3, '0'); aa1 += a1.Semester.ToString().PadLeft(2, '0'); string bb2 = b2.SchoolYear.ToString().PadLeft(3, '0'); bb2 += b2.Semester.ToString().PadLeft(2, '0'); return(aa1.CompareTo(bb2)); }
public void RunInsert(SCJoin scj) { if (tool._StudentDic.ContainsKey(scj.RefStudentID)) { //社團 CLUBRecord cr = tool._ClubDic[scj.RefClubID]; //學生 StudentRecord sr = tool._StudentDic[scj.RefStudentID]; ResultScoreRecord rsr = new ResultScoreRecord(); rsr.SchoolYear = cr.SchoolYear; rsr.Semester = cr.Semester; rsr.RefClubID = cr.UID; //社團ID rsr.RefStudentID = sr.ID; //學生ID rsr.RefSCJoinID = scj.UID; //參與記錄ID rsr.ClubName = cr.ClubName; rsr.ClubLevel = cr.Level; //社團評等 if (!string.IsNullOrEmpty(scj.Score)) { rsr.ResultScore = tool.GetDecimalValue(scj); //成績 } #region 幹部 if (CadreDic.ContainsKey(cr.UID)) { if (CadreDic[cr.UID]._Cadre1.ContainsKey(sr.ID)) { rsr.CadreName = CadreDic[cr.UID]._Cadre1[sr.ID]; } else { rsr.CadreName = ""; } } #endregion InsertScoreList.Add(rsr); } }
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> /// <param name="e"></param> protected override void OnSaveButtonClick(EventArgs e) { //資料檢查 if (!CheckCell()) { MsgBox.Show("請修正資料再儲存!!"); return; } List <ResultScoreRecord> InsertList = new List <ResultScoreRecord>(); List <ResultScoreRecord> updateList = new List <ResultScoreRecord>(); //沒有Tag就是一筆新記錄 foreach (DataGridViewRow row in dataGridViewX1.Rows) { if (row.IsNewRow) { continue; } if (row.Tag == null) { ResultScoreRecord rsr = new ResultScoreRecord(); rsr.SchoolYear = int.Parse("" + row.Cells[0].Value); rsr.Semester = int.Parse("" + row.Cells[1].Value); //rsr.RefClubID = cr.UID; //社團ID rsr.RefStudentID = _Studentecord.ID; //學生ID //rsr.RefSCJoinID = scj.UID; //參與記錄ID rsr.ClubName = "" + row.Cells[2].Value; decimal xy; if (decimal.TryParse("" + row.Cells[3].Value, out xy)) { rsr.ResultScore = xy; //成績 } rsr.CadreName = "" + row.Cells[4].Value; //幹部 InsertList.Add(rsr); } else { ResultScoreRecord rsr = (ResultScoreRecord)row.Tag; rsr.SchoolYear = int.Parse("" + row.Cells[0].Value); rsr.Semester = int.Parse("" + row.Cells[1].Value); rsr.ClubName = "" + row.Cells[2].Value; decimal xy; if (!string.IsNullOrEmpty("" + row.Cells[3].Value)) { if (decimal.TryParse("" + row.Cells[3].Value, out xy)) { rsr.ResultScore = xy; //成績 } else { rsr.ResultScore = null; } } else { rsr.ResultScore = null; } rsr.CadreName = "" + row.Cells[4].Value; //幹部 rsr.ClubLevel = "" + row.Cells[5].Value; //社團評等 updateList.Add(rsr); } } if (InsertList.Count != 0) { _AccessHelper.InsertValues(InsertList); } if (updateList.Count != 0) { _AccessHelper.UpdateValues(updateList); } if (deleteList.Count != 0) { _AccessHelper.DeletedValues(deleteList); } SaveButtonVisible = false; CancelButtonVisible = false; Changed(); }