public static void InsertSCAttendList(List <SCAttendInfo> dataList)
{
try
{
List <string> cmdList = new List <string>();
foreach (SCAttendInfo si in dataList)
{
string pSocre = "null", mScore = "null", dScore = "null";
if (si.PassingStandard.HasValue)
{
pSocre = si.PassingStandard.Value.ToString();
}
if (si.MakeupStandard.HasValue)
{
mScore = si.MakeupStandard.Value.ToString();
}
if (si.DesignateFinalScore.HasValue)
{
dScore = si.DesignateFinalScore.Value.ToString();
}
string query = "INSERT INTO sc_attend(" +
"ref_student_id" +
",ref_course_id" +
",passing_standard" +
",makeup_standard" +
",designate_final_score" +
",remark" +
",subject_code)" +
" VALUES(" + si.StudentID + "" +
"," + si.CourseID + "" +
"," + pSocre + "" +
"," + mScore + "" +
"," + dScore + "" +
",'" + si.Remark + "'" +
",'" + si.SubjectCode + "');";
cmdList.Add(query);
}
K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
uh.Execute(cmdList);
}
catch (Exception ex)
{
throw;
}
}
private void DeleteSportsRegistration(List <string> studentIDList)
{
// 刪除人員
try
{
if (studentIDList.Count > 0)
{
string qry = $"DELETE FROM $ischool.sports.sports_registration WHERE ref_event_id = {_EventID} AND ref_student_id IN ({string.Join(",", studentIDList.ToArray())});";
K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
uh.Execute(qry);
}
}
catch (Exception ex)
{
FISCA.Presentation.Controls.MsgBox.Show($"刪除限制人員發生錯誤：{ex.Message}");
}
}
private void DeleteItem(List <string> uidList)
{
try
{
if (uidList.Count > 0)
{
K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
string delStr = "DELETE FROM $ischool.sports.sports_chief WHERE uid in(" + string.Join(",", uidList.ToArray()) + ");";
uh.Execute(delStr);
}
}
catch (Exception ex)
{
FISCA.Presentation.Controls.MsgBox.Show("刪除資料失敗，" + ex.Message);
}
}
private void btnCreate_Click(object sender, EventArgs e)
{
ControlEnable(false);
try
{
List <GPlanInfo108> insertDataList = new List <GPlanInfo108>();
List <GPlanInfo108> updateDataList = new List <GPlanInfo108>();
foreach (GPlanInfo108 data in _GPlanInfo108List)
{
if (data.Status == "新增")
{
insertDataList.Add(data);
}
if (data.Status == "更新")
{
updateDataList.Add(data);
}
}
// 新增資料
List <string> insertSQLList = new List <string>();
if (insertDataList.Count > 0)
{
K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
try
{
foreach (GPlanInfo108 data in insertDataList)
{
string sql = "" +
" INSERT INTO graduation_plan(" +
" name " +
" ,content " +
" ,moe_group_code ) " +
" VALUES( " +
" '" + data.GDCName + "' " +
" ,'" + data.MOEXml.ToString() + "' " +
" ,'" + data.GDCCode + "' " +
" ); ";
insertSQLList.Add(sql);
}
uh.Execute(insertSQLList);
MsgBox.Show("新增" + insertSQLList.Count + "筆課程規劃表");
}
catch (Exception ex)
{
MsgBox.Show("新增資料發生錯誤：" + ex.Message);
}
}
// 更新資料
List <string> updateSQLList = new List <string>();
if (updateDataList.Count > 0)
{
K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
try
{
foreach (GPlanInfo108 data in updateDataList)
{
XElement GPlanXml = new XElement("GraduationPlan");
foreach (chkSubjectInfo subj in data.chkSubjectInfoList)
{
if (subj.ProcessStatus == "更新" || subj.ProcessStatus == "新增")
{
foreach (XElement elm in subj.MOEXml)
{
GPlanXml.Add(elm);
}
}
if (subj.ProcessStatus == "略過")
{
foreach (XElement elm in subj.GPlanXml)
{
GPlanXml.Add(elm);
}
}
}
// 依課程代碼,科目名稱排序
List <XElement> orderList = (from elmData in GPlanXml.Elements("Subject") orderby elmData.Attribute("課程代碼").Value ascending, elmData.Attribute("SubjectName").Value select elmData).ToList();
// List<XElement> orderList = GPlanXml.Elements("Subject").OrderBy(x => x.Attribute("課程代碼").Value).ToList();
// 重整 idx
int rowIdx = 0;
string tmpCode = "";
foreach (XElement elm in orderList)
{
string codeA = elm.Attribute("課程代碼").Value + elm.Attribute("SubjectName").Value;
if (codeA != tmpCode)
{
rowIdx++;
tmpCode = codeA;
}
if (elm.Element("Grouping") != null)
{
elm.Element("Grouping").SetAttributeValue("RowIndex", rowIdx);
}
}
// 重新排列科目級別
Dictionary <string, int> tmpSubjLevelDict = new Dictionary <string, int>();
foreach (XElement elm in GPlanXml.Elements("Subject"))
{
string subj = elm.Attribute("SubjectName").Value;
if (!tmpSubjLevelDict.ContainsKey(subj))
{
tmpSubjLevelDict.Add(subj, 0);
}
tmpSubjLevelDict[subj] += 1;
elm.SetAttributeValue("FullName", Utility.SubjFullName(subj, tmpSubjLevelDict[subj]));
elm.SetAttributeValue("Level", tmpSubjLevelDict[subj]);
}
// 重新整理開始級別
Dictionary <string, string> tmpStartLevel = new Dictionary <string, string>();
foreach (XElement elm in GPlanXml.Elements("Subject"))
{
string subjName = elm.Attribute("SubjectName").Value;
string RowIndex = elm.Element("Grouping").Attribute("RowIndex").Value;
if (!tmpStartLevel.ContainsKey(subjName))
{
tmpStartLevel.Add(subjName, RowIndex);
}
else
{
if (tmpStartLevel[subjName] != RowIndex)
{
// 設定開始級別是目前級別
elm.Element("Grouping").SetAttributeValue("startLevel", elm.Attribute("Level").Value);
tmpStartLevel[subjName] = RowIndex;
}
}
}
GPlanXml.ReplaceAll(orderList);
if (data.GDCCode.Length > 3)
{
GPlanXml.SetAttributeValue("EntryYear", data.GDCCode.Substring(0, 3));
}
data.RefGPContent = GPlanXml.ToString();
if (!string.IsNullOrEmpty(data.RefGPID))
{
string sql = "UPDATE graduation_plan SET content = '" + data.RefGPContent + "' WHERE id = " + data.RefGPID + ";";
updateSQLList.Add(sql);
}
}
// 更新資料
uh.Execute(updateSQLList);
MsgBox.Show("更新" + updateSQLList.Count + "筆課程規劃表");
}
catch (Exception ex)
{
MsgBox.Show("更新資料發生錯誤" + ex.Message);
}
}
if (insertSQLList.Count > 0 || updateSQLList.Count > 0)
{
ControlEnable(false);
_bgWorker.RunWorkerAsync();
}
}
catch (Exception ex)
{
MsgBox.Show("儲存發生錯誤：" + ex.Message);
}
ControlEnable(true);
}
public static void UpdateSCAttendList(List <SCAttendInfo> dataList)
{
try
{
List <string> cmdList = new List <string>();
List <string> tmpList = new List <string>();
foreach (SCAttendInfo si in dataList)
{
string pSocre = "null", mScore = "null", dScore = "null";
if (si.PassingStandard.HasValue)
{
pSocre = si.PassingStandard.Value.ToString();
}
if (si.MakeupStandard.HasValue)
{
mScore = si.MakeupStandard.Value.ToString();
}
if (si.DesignateFinalScore.HasValue)
{
dScore = si.DesignateFinalScore.Value.ToString();
}
tmpList.Clear();
if (si.IsPassingStandardCheck)
{
tmpList.Add("passing_standard=" + pSocre);
}
if (si.IsMakeupStandardCheck)
{
tmpList.Add("makeup_standard = " + mScore);
}
if (si.IsDesignateFinalScoreCheck)
{
tmpList.Add("designate_final_score = " + dScore);
}
if (si.IsSubjectCodeCheck)
{
tmpList.Add("subject_code = '" + si.SubjectCode + "'");
}
if (si.IsRemarkCheck)
{
tmpList.Add("remark = '" + si.Remark + "'");
}
if (!string.IsNullOrEmpty(si.ID) && tmpList.Count > 0)
{
string query = "UPDATE sc_attend SET " + string.Join(",", tmpList.ToArray()) + " WHERE ID = " + si.ID + ";";
cmdList.Add(query);
}
}
K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
uh.Execute(cmdList);
}
catch (Exception ex)
{
throw;
}
}