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