/**
* 取得1位學生的各種缺課
*
* @param student_no
* @return
*/
public Map StudentDilg(String student_no) {
return dataFinder.sqlGetMap(
"SELECT (SELECT COUNT(*)FROM Dilg, Dilg_apply WHERE Dilg_apply.result='1' AND Dilg.abs='1' AND "
+ "Dilg.Dilg_app_oid=Dilg_apply.Oid AND Dilg.student_no=s.student_no)as abs1, (SELECT COUNT(*)FROM Dilg LEFT OUTER JOIN Dilg_apply ON Dilg.Dilg_app_oid=Dilg_apply.Oid "
+ "WHERE Dilg.abs!='5' AND Dilg.abs='2' AND(Dilg_apply.result='2'||Dilg_apply.result IS NULL) AND Dilg.student_no=s.student_no)as abs2, "
+ "(SELECT COUNT(*)FROM Dilg, Dilg_apply WHERE Dilg_apply.result='1' AND Dilg.abs='3' AND Dilg.Dilg_app_oid=Dilg_apply.Oid AND Dilg.student_no=s.student_no)as abs3, "
+ "(SELECT COUNT(*)FROM Dilg, Dilg_apply WHERE Dilg_apply.result='1' AND Dilg.abs='4' AND Dilg.Dilg_app_oid=Dilg_apply.Oid AND Dilg.student_no=s.student_no)as abs4, "
+ "(SELECT COUNT(*)FROM Dilg WHERE Dilg.abs='5' AND Dilg.student_no=s.student_no)as abs5, (SELECT COUNT(*)FROM Dilg, Dilg_apply WHERE Dilg_apply.result='1' AND Dilg.abs='6' "
+ "AND Dilg.Dilg_app_oid=Dilg_apply.Oid AND Dilg.student_no=s.student_no)as abs6, (SELECT COUNT(*)FROM Dilg, Dilg_apply WHERE Dilg_apply.result='1' AND Dilg.abs='7' AND "
+ "Dilg.Dilg_app_oid=Dilg_apply.Oid AND Dilg.student_no=s.student_no)as abs7, (SELECT COUNT(*)FROM Dilg, Dilg_apply WHERE Dilg_apply.result='1' AND Dilg.abs='8' AND "
+ "Dilg.Dilg_app_oid=Dilg_apply.Oid AND Dilg.student_no=s.student_no)as abs8, (SELECT COUNT(*)FROM Dilg, Dilg_apply WHERE Dilg_apply.result='1' AND Dilg.abs='9' AND "
+ "Dilg.Dilg_app_oid=Dilg_apply.Oid AND Dilg.student_no=s.student_no)as abs9 FROM stmd s WHERE s.student_no='"
+ student_no
+ "'");
}
/**
* 取授課學生 - 單一科目學生
*
* @param Oid
* @param date
* @param week
* @return
*/
public List Dilg_student_date(String Oid, String date, String week) {
List list =
dataFinder.sqlGet(
"SELECT j.total_score, s.status, st.student_no, st.student_name,(SELECT COUNT(*)FROM Dilg di, Dilg_rules dr WHERE di.abs=dr.id AND "
+ "dr.exam='1'AND di.student_no=st.student_no AND di.Dtime_oid=d.Oid) as dilg_period, s.score1, s.score2, s.score FROM "
+ "Dtime d, stmd st LEFT OUTER JOIN Just j ON st.student_no=j.student_no, Seld s WHERE st.student_no=s.student_no AND s.Dtime_oid=d.Oid AND d.Oid="
+ Oid
+ " ORDER BY st.student_no");
Object dc[] = Dtime_class(Oid, week);
List dg;
Map m;
for (int i = 0; i < list.size(); i++) {
dg = new ArrayList();
for (int j = 0; j < dc.length; j++) {
m = new HashMap();
m.put("cls", dc[j]);
try {
// m1=dataFinder.sqlGetMap("SELECT abs, earlier FROM Dilg WHERE
// student_no='"+((Map)list.get(i)).get("student_no")+"' AND cls='"+dc[j]+"' AND
// date='"+date+"'");
m.putAll(
dataFinder.sqlGetMap(
"SELECT abs, earlier FROM Dilg WHERE student_no='"
+ ((Map) list.get(i)).get("student_no")
+ "' AND cls='"
+ dc[j]
+ "' AND date='"
+ date
+ "'"));
} catch (Exception e) {
m.put("abs", "");
m.put("earlier", "");
}
dg.add(m);
}
((Map) list.get(i)).put("dilgs", dg);
}
return list;
}
