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