@Override
public void service(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
String fileName = request.getParameter("filename"); // $NON-NLS-1$
String tableName = fileName.substring(0, fileName.lastIndexOf(DSV_EXTENSION)).toUpperCase();
byte[] download;
try {
DataFinder dataFinder = new DataFinder();
dataFinder.setTableName(tableName);
download = dataFinder.getTableData().getBytes();
} catch (Exception e) {
logger.error(e.getMessage(), e);
download = (DataExportServiceHandler_ERROR_WHILE_EXPORTING_DSV + tableName).getBytes();
tableName = "error.txt"; // $NON-NLS-1$
}
String contentDisposition =
"attachment; filename=\"" + fileName + "\""; // $NON-NLS-1$ //$NON-NLS-2$
response.setContentType("text/plain"); // $NON-NLS-1$
response.setHeader("Content-Disposition", contentDisposition); // $NON-NLS-1$
response.getOutputStream().write(download);
}
/**
* 依日期查詢課程各節次出勤狀態
*
* @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");
}
/**
* 是否為一門課的任課教師
*
* @return
*/
public boolean Dtime_techid(String Dtime_oid, String techid) {
if (dataFinder.sqlGetInt(
"SELECT COUNT(*)FROM Dtime WHERE techid='" + techid + "' AND Oid=" + Dtime_oid)
> 0) {
return true;
}
return false;
}
/**
* 取授課學生 - 一科目多教師
*
* @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;
}
/**
* 學生課表資訊
*
* @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)");
}
/**
* 取得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
+ "'");
}
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();
}
/**
* 依日期查詢當日應到人數
*
* @param Dtime_oid
* @param date
* @return
*/
public int DilgLog_date_due(String Dtime_oid, String date) {
return dataFinder.sqlGetInt(
"SELECT due FROM DilgLog WHERE date='" + date + "' AND Dtime_oid=" + Dtime_oid);
}
/**
* 依課程查詢選課人數
*
* @param Dtime_oid
* @return
*/
public int Seld_count(String Dtime_oid) {
return dataFinder.sqlGetInt(
"SELECT COUNT(*)FROM Seld WHERE status IS NULL AND Dtime_oid=" + Dtime_oid);
}
/**
* 現在學年
*
* @return
*/
public String school_year() {
return dataFinder.sqlGetStr("SELECT Value FROM Parameter WHERE Name='School_year'");
}
public List dilguneed(String begin, String end) {
return dataFinder.sqlGet(
"SELECT date FROM AMS_Holiday WHERE date>='" + begin + "' AND date<='" + end + "'");
}
/**
* 取點名結束
*
* @return
*/
public String RollCall_end() {
return dataFinder.sqlGetStr(
"SELECT cdate FROM SYS_CALENDAR WHERE s_group='RollCall' AND s_name='end'");
}
/**
* 取學期結束
*
* @return
*/
public String school_term_end() {
return dataFinder.sqlGetStr(
"SELECT cdate FROM SYS_CALENDAR WHERE s_group='SchoolTerm' AND s_name='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;
}
