/**
* 依日期查詢課程各節次出勤狀態
*
* @param Dtime_oid
* @param date
* @return
*/
public List Dilg_info(String Dtime_oid, String date) {
return dataFinder.sqlGet(
"SELECT cls, COUNT(*) as cnt FROM Dilg WHERE date='"
+ date
+ "' AND Dtime_oid="
+ Dtime_oid
+ " GROUP BY cls");
}
/**
* 學生課表資訊
*
* @param stdNo
* @param term
* @return
*/
public List getCsTable(String stdNo, String term) {
return dataFinder.sqlGet(
"SELECT d.Oid as dOid, d.thour, d.credit, "
+ "d.Oid as dtOid, d.techid, e.cname, c.cscode, c.chi_name,dc.* FROM stmd st, "
+ "Seld s, (Dtime d LEFT OUTER JOIN empl e ON d.techid=e.idno), Dtime_class dc, "
+ "Csno c WHERE st.student_no=s.student_no AND s.Dtime_oid=d.Oid AND "
+ "c.cscode=d.cscode AND d.Oid=dc.Dtime_oid AND d.Sterm='"
+ term
+ "' AND "
+ "s.student_no='"
+ stdNo
+ "' ORDER BY CAST(dc.begin AS UNSIGNED)");
}
/**
* 取授課學生 - 一科目多教師
*
* @param Oid
* @param date
* @param week
* @param id
* @return
*/
public List Dilg_student_date_mulTeacher(String Oid, String date, String week, String id) {
List<Map> 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
+ " AND s.Dtime_teacher=(SELECT Oid FROM Dtime_teacher WHERE "
+ "Dtime_oid="
+ Oid
+ " AND teach_id='"
+ id
+ "')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 {
m.put(
"abs",
dataFinder.sqlGetStr(
"SELECT abs FROM Dilg WHERE student_no='"
+ list.get(i).get("student_no")
+ "' AND cls='"
+ dc[j]
+ "' AND date='"
+ date
+ "'"));
} catch (Exception e) {
m.put("abs", "");
}
dg.add(m);
}
list.get(i).put("dilgs", dg);
}
return list;
}
public Object[] Dtime_class(String Dtime_oid, String week) {
List<Map> list =
dataFinder.sqlGet(
"SELECT begin, end FROM Dtime_class WHERE week='"
+ week
+ "' AND Dtime_oid="
+ Dtime_oid);
int begin, end;
ArrayList str = new ArrayList();
for (int i = 0; i < list.size(); i++) {
begin = Integer.parseInt(list.get(i).get("begin").toString());
end = Integer.parseInt(list.get(i).get("end").toString());
for (int j = begin; j <= end; j++) {
str.add(j);
}
}
return str.toArray();
}
public List dilguneed(String begin, String end) {
return dataFinder.sqlGet(
"SELECT date FROM AMS_Holiday WHERE date>='" + begin + "' AND date<='" + end + "'");
}
/**
* 出席率圖表
*
* @param techid
* @param begin
* @param end
* @param term
* @return
*/
public Map Dilg_pro_techid(String techid, Date begin, String term) {
SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd");
Calendar c = Calendar.getInstance();
Calendar c1 = Calendar.getInstance();
Calendar c2 = Calendar.getInstance();
c.setTime(begin);
c1.setTime(begin);
List<Map> list =
dataFinder.sqlGet(
"SELECT ((SELeCT COUNT(*)FROM stmd, Seld WHERE stmd.student_no=Seld.student_no AND Seld.Dtime_oid=d.Oid)*d.thour) as cnt, d.Oid, cl.ClassName, cs.chi_name FROM Dtime d, "
+ "Csno cs, Class cl WHERE d.cscode=cs.cscode AND d.depart_class=cl.ClassNo AND d.techid='"
+ techid
+ "' AND d.Sterm='"
+ term
+ "'");
float cnt;
float wdilg;
StringBuilder sb = new StringBuilder("['週次',");
for (int i = 0; i < list.size(); i++) {
sb.append(
"'"
+ ((Map) list.get(i)).get("ClassName")
+ "-"
+ ((Map) list.get(i)).get("chi_name")
+ "',");
}
sb.delete(sb.length() - 1, sb.length());
sb.append("],");
Map map = new HashMap();
map.put("dtimes", sb);
String sba[] = new String[19];
for (int i = 1; i < 19; i++) {
c1.add(Calendar.DAY_OF_YEAR, 7);
c2.setTime(c1.getTime());
c2.add(Calendar.DAY_OF_YEAR, -1);
sb = new StringBuilder("['第" + i + "週',");
for (int k = 0; k < list.size(); k++) {
try {
cnt = Float.parseFloat(list.get(k).get("cnt").toString());
wdilg =
Float.parseFloat(
dataFinder.sqlGetStr(
"SELECT COUNT(*)FROM Dilg WHERE date>='"
+ sf.format(c.getTime())
+ "'AND date<='"
+ sf.format(c2.getTime())
+ "' AND Dtime_oid="
+ list.get(k).get("Oid")));
sb.append(100 - ((wdilg / cnt) * 100) + ",");
} catch (Exception e) {
sb.append(100 + ",");
}
}
sb.delete(sb.length() - 1, sb.length());
sb.append("]");
// System.out.println(sb);
if (i < 18) {
sb.append(",");
}
sba[i] = sb.toString();
map.put("dilgs", sba);
c.add(Calendar.DAY_OF_YEAR, 7);
}
return map;
}
