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