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();
}
