/// <summary> /// 取得學生體適能資料。 /// </summary> public static void FillFitness(this List <StudentExcess> students) { //資料表:ischool_student_fitness if (students == null || students.Count <= 0) { return; } string sql = @"select ref_student_id,school_year, height_degree,weight_degree, sit_and_reach_degree,standing_long_jump_degree,sit_up_degree,cardiorespiratory_degree from $ischool_student_fitness where ref_student_id in({0}) order by school_year"; sql = string.Format(sql, students.ToPrimaryKeyStringList()); DataTable table = Utility.Q.Select(sql); // Dictionary<string, StudentExcess> StudentLookup = students.ToDictionary(x => x.StudentID); Dictionary <string, StudentExcess> StudentLookup = new Dictionary <string, StudentExcess>(); foreach (StudentExcess data in students) { if (!StudentLookup.ContainsKey(data.StudentID)) { StudentLookup.Add(data.StudentID, data); } } foreach (DataRow row in table.Rows) { string id = row["ref_student_id"] + ""; int school_year = int.Parse(row["school_year"] + ""); string height = row["height_degree"] + ""; string weight = row["weight_degree"] + ""; string sit_and_reach = row["sit_and_reach_degree"] + ""; string standing_long_jump = row["standing_long_jump_degree"] + ""; string sit_up = row["sit_up_degree"] + ""; string cardiorespiratory = row["cardiorespiratory_degree"] + ""; if (!StudentLookup.ContainsKey(id)) { continue; } StudentExcess stu = StudentLookup[id]; FitnessDegree fitness = new FitnessDegree(); //fitness.Height = height; //fitness.Weight = weight; fitness.SitAndReach = sit_and_reach; fitness.StandingLongJump = standing_long_jump; fitness.SitUp = sit_up; fitness.Cardiorespiratory = cardiorespiratory; fitness.Collect(); stu.Fitness[school_year] = fitness; } }
/// <summary> /// 取得學生服務學習時數。 /// </summary> public static void FillServiceLearning(this List <StudentExcess> students) { //資料表:K12.Service.Learning.Record if (students == null || students.Count <= 0) { return; } string sql = @"select ref_student_id, school_year, semester, sum(hours) hours from $k12.service.learning.record where ref_student_id in ({0}) and not school_year is null and not semester is null group by ref_student_id,school_year,semester order by ref_student_id, school_year, semester"; sql = string.Format(sql, students.ToPrimaryKeyStringList()); DataTable table = Utility.Q.Select(sql); //服務學期資料 Lookup //Dictionary<string, StudentExcess> StudentLookup = students.ToDictionary(x => x.StudentID); Dictionary <string, StudentExcess> StudentLookup = new Dictionary <string, StudentExcess>(); foreach (StudentExcess data in students) { if (!StudentLookup.ContainsKey(data.StudentID)) { StudentLookup.Add(data.StudentID, data); } } foreach (DataRow row in table.Rows) { string id = row["ref_student_id"] + ""; int syear = int.Parse(row["school_year"] + ""); int sems = int.Parse(row["semester"] + ""); decimal hours = decimal.Parse(row["hours"] + ""); if (!StudentLookup.ContainsKey(id)) { continue; } StudentExcess se = StudentLookup[id]; SemesterData sd = new SemesterData(0, syear, sems); se.ServiceLearning[sd] = hours; } }
public static void FillDomainScore(this List <StudentExcess> students) { //資料表:ischool_student_fitness if (students == null || students.Count <= 0) { return; } string sql = @"select ref_student_id,school_year,semester,score_info from sems_subj_score where ref_student_id in({0}) order by school_year, semester"; sql = string.Format(sql, students.ToPrimaryKeyStringList()); DataTable table = Utility.Q.Select(sql); // Dictionary<string, StudentExcess> StudentLookup = students.ToDictionary(x => x.StudentID); Dictionary <string, StudentExcess> StudentLookup = new Dictionary <string, StudentExcess>(); foreach (StudentExcess data in students) { if (!StudentLookup.ContainsKey(data.StudentID)) { StudentLookup.Add(data.StudentID, data); } } foreach (DataRow row in table.Rows) { string id = row["ref_student_id"] + ""; int school_year = int.Parse(row["school_year"] + ""); int semester = int.Parse(row["semester"] + ""); string score_info = row["score_info"] + ""; if (!StudentLookup.ContainsKey(id)) { continue; } StudentExcess stu = StudentLookup[id]; SemesterData sd = new SemesterData(0, school_year, semester); DomainScore ds = ParseDomainScore(score_info); stu.DomainScore[sd] = ds; } }
/// <summary> /// 取得學生幹部資料計數。 /// </summary> public static void FillCadre(this List <StudentExcess> students) { //資料表:K12.Service.Learning.Record if (students == null || students.Count <= 0) { return; } string sql = @"select ref_student_id,school_year,semester,count(id) as count from discipline where ref_student_id in({0}) and reason like '%[幹部]%' group by ref_student_id,school_year,semester"; sql = string.Format(sql, students.ToPrimaryKeyStringList()); DataTable table = Utility.Q.Select(sql); // Dictionary<string, StudentExcess> StudentLookup = students.ToDictionary(x => x.StudentID); Dictionary <string, StudentExcess> StudentLookup = new Dictionary <string, StudentExcess>(); foreach (StudentExcess data in students) { if (!StudentLookup.ContainsKey(data.StudentID)) { StudentLookup.Add(data.StudentID, data); } } foreach (DataRow row in table.Rows) { string id = row["ref_student_id"].ToString(); int sy = int.Parse(row["school_year"].ToString()); int ss = int.Parse(row["semester"].ToString()); int count = int.Parse(row["count"].ToString()); if (!StudentLookup.ContainsKey(id)) { continue; } StudentExcess stu = StudentLookup[id]; SemesterData sd = new SemesterData(0, sy, ss); stu.Cadre[sd] = count; } }
/// <summary> /// 取得學生年級。 /// </summary> public static void GetGradeYear(this List <StudentExcess> students) { //資料表:K12.Service.Learning.Record if (students == null || students.Count <= 0) { return; } string sql = @"SELECT student.id, class.grade_year FROM student LEFT JOIN class ON student.ref_class_id = class.id WHERE student.id in ({0}) "; sql = string.Format(sql, students.ToPrimaryKeyStringList()); DataTable table = Utility.Q.Select(sql); Dictionary <string, StudentExcess> StudentLookup = new Dictionary <string, StudentExcess>(); foreach (StudentExcess data in students) { if (!StudentLookup.ContainsKey(data.StudentID)) { StudentLookup.Add(data.StudentID, data); } } foreach (DataRow row in table.Rows) { string id = row["id"] + ""; string gyear = row["grade_year"] + ""; if (!StudentLookup.ContainsKey(id)) { continue; } StudentExcess se = StudentLookup[id]; se.GradeYear = gyear; } }