private int SortStudentSCJoin(OnlineSCJoin sj1, OnlineSCJoin sj2) { string student1 = sj1.ClassName.PadLeft(5, '0'); student1 += sj1.SeatNo.PadLeft(3, '0'); string student2 = sj2.ClassName.PadLeft(5, '0'); student2 += sj2.SeatNo.PadLeft(3, '0'); return(student1.CompareTo(student2)); }
/// <summary> /// 取得學生社團參與記錄 /// </summary> public static Dictionary <string, OnlineSCJoin> GetSCJoinStudent(Connection me, string SchoolYear, string Semester) { Dictionary <string, OnlineSCJoin> dic = new Dictionary <string, OnlineSCJoin>(); Envelope rsp = me.SendRequest("_.GetStudentSCJoin", new Envelope(GetRequest(SchoolYear, Semester))); XElement clubs = XElement.Parse(rsp.Body.XmlString); foreach (XElement club in clubs.Elements("Student")) { OnlineSCJoin ol = new OnlineSCJoin(club); if (!dic.ContainsKey(ol.StudentId)) { dic.Add(ol.StudentId, ol); } } return(dic); }
public ExportAcrossClubStudent(string sy, string s) { Workbook wb = new Workbook(); Exception exc = null; BackgroundWorker bgw = new BackgroundWorker() { WorkerReportsProgress = true }; bgw.ProgressChanged += delegate(object sender, ProgressChangedEventArgs e) { MotherForm.SetStatusBarMessage("匯出選社結果(跨部別)", e.ProgressPercentage); }; bgw.DoWork += delegate { try { #region 跨部別 { bgw.ReportProgress(1); AccessHelper access = new AccessHelper(); loginSchoolList = access.Select <LoginSchool>(); //DIC Dictionary <string, OnlineVolunteer> volunteerDic = new Dictionary <string, OnlineVolunteer>(); Dictionary <string, OnlineSCJoin> scjoinDic = new Dictionary <string, OnlineSCJoin>(); Dictionary <string, string> clubNameDic = new Dictionary <string, string>(); //-- wb.Open(new MemoryStream(Properties.Resources.匯出選社結果_跨部別__範本), FileFormatType.Excel2007Xlsx); Worksheet[] ws = new Worksheet[2]; int sheet = 0; int p = 10; foreach (LoginSchool school in loginSchoolList) { p = p + 90 / loginSchoolList.Count; bgw.ReportProgress(p); ws[sheet] = wb.Worksheets[sheet]; Connection connection = new Connection(); connection.Connect(school.School_Name, "ClubAcrossdivisions", FISCA.Authentication.DSAServices.PassportToken); ws[sheet].Name = school.GetFullName(); // 取得社團清單 Envelope rsp = connection.SendRequest("_.GetClubList", new Envelope(RunService.GetRequest(sy, s))); XDocument clubDoc = XDocument.Parse(rsp.Body.XmlString); List <XElement> clubList = clubDoc.Element("Response").Elements("K12.clubrecord.universal").ToList(); foreach (XElement club in clubList) { clubNameDic.Add(club.Element("Uid").Value, club.Element("ClubName").Value); } // 取得學生志願序 volunteerDic.Clear(); rsp = connection.SendRequest("_.GetStudentVolunteer", new Envelope(RunService.GetRequest(sy, s))); XDocument volunteerDoc = XDocument.Parse(rsp.Body.XmlString); List <XElement> volunteers = volunteerDoc.Element("Response").Elements("K12.volunteer.universal").ToList(); foreach (XElement volunteer in volunteers) { OnlineVolunteer vol = new OnlineVolunteer(volunteer); volunteerDic.Add(vol.RefStudentId, vol); } // 取得學生入選紀錄 scjoinDic.Clear(); rsp = connection.SendRequest("_.GetStudentSCJoin", new Envelope(RunService.GetRequest(sy, s))); XDocument scjDoc = XDocument.Parse(rsp.Body.XmlString); List <XElement> scjoins = scjDoc.Element("Response").Elements("Student").ToList(); foreach (XElement scjoin in scjoins) { OnlineSCJoin scj = new OnlineSCJoin(scjoin); scjoinDic.Add(scj.StudentId, scj); } // 讀取志願數上限設定 List <ConfigRecord> crList = access.Select <K12.Club.Volunteer.ConfigRecord>(); for (int i = 6; i < int.Parse(crList[0].Content) + 6; i++) { ws[sheet].Cells.CopyColumn(ws[sheet].Cells, 4, i); ws[sheet].Cells[0, i].PutValue("志願" + (i - 5)); } // 取得所有學生 rsp = connection.SendRequest("_.GetAllStudent", new Envelope(RunService.GetRequest(sy, s))); XDocument allStudentDoc = XDocument.Parse(rsp.Body.XmlString); List <XElement> students = allStudentDoc.Element("Response").Elements("Student").ToList(); int index = 1; foreach (XElement student in students) { if (scjoinDic.ContainsKey(student.Element("Id").Value)) { ws[sheet].Cells.CopyRow(ws[sheet].Cells, 1, index); ws[sheet].Cells[index, 0].PutValue(student.Element("ClassName").Value); ws[sheet].Cells[index, 1].PutValue(student.Element("SeatNo").Value); ws[sheet].Cells[index, 2].PutValue(student.Element("Name").Value); ws[sheet].Cells[index, 3].PutValue(student.Element("StudentNumber").Value); ws[sheet].Cells[index, 4].PutValue(scjoinDic[student.Element("Id").Value].ClubName); ws[sheet].Cells[index, 5].PutValue(scjoinDic[student.Element("Id").Value].IsLock == true ? "是" : ""); List <XElement> club = XDocument.Parse(volunteerDic[student.Element("Id").Value].Content).Element("xml").Elements("Club").ToList(); int i = 1; // 取得志願數設定 int clubCount = int.Parse("" + crList[0].Content); foreach (XElement clubID in club) { ws[sheet].Cells[index, 5 + i].PutValue(clubNameDic[clubID.Attribute("Ref_Club_ID").Value]); if (i == clubCount) { break; } i++; } index++; } } sheet++; } } #endregion #region { // bgw.ReportProgress(51); // AccessHelper access = new AccessHelper(); // List<CLUBRecord> _clubList = access.Select<CLUBRecord>(/*"school_year = "+School.DefaultSchoolYear+" AND semester = "+ School.DefaultSemester*/).ToList(); // foreach (CLUBRecord club in _clubList) // { // clubDic.Add(club.UID, club.ClubName); // } // //取得Sheet // Worksheet ws = wb.Worksheets[0]; // ws.Name = School.ChineseName; // QueryHelper qh = new QueryHelper(); // #region SQL // string selectSQL = string.Format(@" //SELECT // student.id // , class.class_name // , class.grade_year // , seat_no,name // , student_number // , student.ref_class_id // , scjoin.ref_club_id // , scjoin.lock // , clubrecord.club_name // , clubrecord.school_year // , clubrecord.semester // , volunteer.content // , $k12.config.universal.content as wish_limit //FROM // student // LEFT OUTER JOIN class on class.id = student.ref_class_id // LEFT OUTER JOIN $k12.scjoin.universal AS scjoin // ON scjoin.ref_student_id::bigint = student.id // LEFT OUTER JOIN $k12.clubrecord.universal AS clubrecord // ON clubrecord.uid = scjoin.ref_club_id::bigint // LEFT OUTER JOIN $k12.volunteer.universal AS volunteer // ON volunteer.ref_student_id::bigint = student.id // AND volunteer.school_year = clubrecord.school_year // AND volunteer.semester = clubrecord.semester // LEFT OUTER JOIN $k12.config.universal ON config_name = '學生選填志願數' //WHERE // student.status in (1, 2) // AND clubrecord.school_year = {0} // AND clubrecord.semester = {1} //ORDER BY // class.grade_year // , class.display_order // , class.class_name // , student.seat_no // , student.id" // , sy, s); // #endregion // // 取得學生社團資料 // DataTable dt = qh.Select(selectSQL); // bgw.ReportProgress(55); // int index = 1; // foreach (DataRow dr in dt.Rows) // { // bgw.ReportProgress(55 + 45 * index / dt.Rows.Count); // int wishLimit = (dr["wish_limit"] == null ? 5 : int.Parse("" + dr["wish_limit"])); // if (index == 1) // { // int count = 6; // int countLimit = count + wishLimit; // for (int i = count; i < countLimit; i++) // { // ws.Cells.CopyColumn(ws.Cells, 4, i); // ws.Cells[0, i].PutValue("志願" + (i - 5)); // } // } // else // ws.Cells.CopyRow(ws.Cells, 1, index); // ws.Cells[index, 0].PutValue("" + dr["class_name"]); // ws.Cells[index, 1].PutValue("" + dr["seat_no"]); // ws.Cells[index, 2].PutValue("" + dr["name"]); // ws.Cells[index, 3].PutValue("" + dr["student_number"]); // ws.Cells[index, 4].PutValue("" + dr["club_name"]); // ws.Cells[index, 5].PutValue(("" + dr["lock"]) == "true" ? "是" : ""); // // 學生志願序 // if (dr["content"] != null && "" + dr["content"] != "") // { // XDocument content = XDocument.Parse("" + dr["content"]); // List<XElement> clubList = content.Element("xml").Elements("Club").ToList(); // int count = 6; // int countLimit = count + wishLimit - 1; // foreach (XElement club in clubList) // { // ws.Cells[index, count].PutValue(clubDic[club.Attribute("Ref_Club_ID").Value]); // count++; // if (count > countLimit) // break; // } // } // index++; // } } #endregion } catch (Exception ex) { exc = ex; } }; bgw.RunWorkerCompleted += delegate { if (exc == null) { #region Excel 存檔 { SaveFileDialog SaveFileDialog1 = new SaveFileDialog(); SaveFileDialog1.Filter = "Excel (*.xls)|*.xls|所有檔案 (*.*)|*.*"; SaveFileDialog1.FileName = "匯出選社結果(跨部別)"; try { if (SaveFileDialog1.ShowDialog() == DialogResult.OK) { wb.Save(SaveFileDialog1.FileName); Process.Start(SaveFileDialog1.FileName); MotherForm.SetStatusBarMessage("匯出選社結果(跨部別),列印完成!!"); } else { FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存"); return; } } catch { FISCA.Presentation.Controls.MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!"); MotherForm.SetStatusBarMessage("檔案儲存錯誤,請檢查檔案是否開啟中!!"); } } #endregion } else { throw new Exception("匯出選社結果 發生錯誤", exc); } }; bgw.RunWorkerAsync(); }