private void btnSave_Click(object sender, EventArgs e)
{
_new.Clear();
if (ValidateData())
{
List <GpaRef> insert = new List <GpaRef>();
foreach (DataGridViewRow row in dgv.Rows)
{
if (row.IsNewRow)
{
continue;
}
string schoolYear = row.Cells[colSchoolYear.Index].Value + "";
string semester = row.Cells[colSemester.Index].Value + "";
string grade = row.Cells[colGrade.Index].Value + "";
string max = row.Cells[colMax.Index].Value + "";
string avg = row.Cells[colAvg.Index].Value + "";
decimal d;
GpaRef gr = new GpaRef();
gr.SchoolYear = int.Parse(schoolYear);
gr.Semester = int.Parse(semester);
gr.Grade = int.Parse(grade);
gr.Max = decimal.TryParse(max, out d) ? d : 0;
gr.Avg = decimal.TryParse(avg, out d) ? d : 0;
insert.Add(gr);
string key1 = schoolYear + "學年度" + semester + "學期" + grade + "年級最高GPA";
string key2 = schoolYear + "學年度" + semester + "學期" + grade + "年級平均GPA";
if (!_new.ContainsKey(key1))
{
_new.Add(key1, max);
}
if (!_new.ContainsKey(key2))
{
_new.Add(key2, avg);
}
}
_A.DeletedValues(_Records);
_A.InsertValues(insert);
FiscaLogWriter();
MessageBox.Show("儲存完成");
LoadData();
}
else
{
MessageBox.Show("資料有誤,請確認後再儲存");
}
}
/// <summary>
/// 自動更新GPA統計資料
/// </summary>
/// <param name="schoolYear"></param>
/// <param name="semester"></param>
/// <returns></returns>
public static bool UpdateGPAref(int schoolYear, int semester)
{
DataTable dt;
bool approve = false;
List <string> update_log = new List <string>();
List <string> insert_log = new List <string>();
//更新的學年度學期必須大於已存在record的最大值
try
{
string prepare_sql = "select max(school_year) as school_year,max(semester) as semester from $ischool.gparef where school_year = (select max(school_year) from $ischool.gparef)";
dt = Instance._Q.Select(prepare_sql);
if (dt.Rows.Count == 0)
{
approve = true;
}
if (dt.Rows.Count > 0)
{
int i;
int sy = int.TryParse(dt.Rows[0]["school_year"] + "", out i) ? i : 0;
int sm = int.TryParse(dt.Rows[0]["semester"] + "", out i) ? i : 0;
if (schoolYear > sy)
{
approve = true;
}
else if (schoolYear == sy)
{
if (semester >= sm)
{
approve = true;
}
}
}
}
catch
{
approve = false;
}
if (approve)
{
FISCA.Presentation.MotherForm.SetStatusBarMessage("GPA統計更新中...");
for (int grade = 9; grade <= 12; grade++)
{
//Get old record
List <GpaRef> list = Instance._A.Select <GpaRef>("grade=" + grade + " and school_year=" + schoolYear + " and semester=" + semester);
GpaRef gparef;
//if old record didn't exist ,insert new record
decimal old_max = 0;
decimal old_avg = 0;
if (list.Count > 0)
{
gparef = list[0];
old_max = gparef.Max;
old_avg = gparef.Avg;
}
else
{
gparef = new GpaRef();
gparef.Grade = grade;
gparef.SchoolYear = schoolYear;
gparef.Semester = semester;
}
//get each student CumulateGPA
string sql = "select sems_subj_score.ref_student_id,sems_subj_score.school_year,sems_subj_score.semester,xpath_string('<root>'||sems_subj_score.score_info||'</root>','//CumulateGPA') as gpa from sems_subj_score ";
sql += "join student on sems_subj_score.ref_student_id=student.id ";
sql += "join class on student.ref_class_id=class.id ";
sql += "where class.grade_year=" + grade + " and sems_subj_score.school_year=" + schoolYear + " and sems_subj_score.semester=" + semester + " and student.status in (1,2)";
dt = Instance._Q.Select(sql);
int count = 0;
decimal max = 0;
decimal total = 0;
foreach (DataRow row in dt.Rows)
{
decimal d;
decimal gpa = decimal.TryParse(row["gpa"] + "", out d) ? d : 0;
if (gpa > max)
{
max = gpa;
}
total += gpa;
count++;
}
decimal avg = 0;
if (count > 0)
{
avg = Math.Round(total / count, 2, MidpointRounding.AwayFromZero);
}
gparef.Max = max;
gparef.Avg = avg;
string text = gparef.SchoolYear + "學年度" + gparef.Semester + "學期" + gparef.Grade + "年級";
if (string.IsNullOrWhiteSpace(gparef.UID))
{
insert_log.Add(text + " 最高GPA:" + gparef.Max + " 平均GPA:" + gparef.Avg);
}
else
{
update_log.Add(text + "\r\n最高GPA由 " + old_max + " 改為 " + gparef.Max + "\r\n平均GPA由 " + old_avg + " 改為 " + gparef.Avg);
}
gparef.Save();
}
FISCA.Presentation.MotherForm.SetStatusBarMessage("GPA統計更新完成");
}
if (insert_log.Count > 0)
{
FISCA.LogAgent.ApplicationLog.Log("GPA統計", "自動新增", string.Join("\r\n", insert_log));
}
if (update_log.Count > 0)
{
FISCA.LogAgent.ApplicationLog.Log("GPA統計", "自動修改", string.Join("\r\n", update_log));
}
return(approve);
}
