/** * 依日期查詢課程各節次出勤狀態 * * @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; }