jsp

来源：互联网发布：淘宝户外小菜刀编辑：程序博客网时间：2024/04/20 03:20

源文件
    <%@page contentType="text/html;charset=GBK"%><%@page import="java.util.*,com.jspsoft.database.*,com.jspsoft.util.*"%><%
String out_excel=request.getParameter("out_excel");
boolean isExcel=out_excel!=null&&out_excel.equals("true");
if(isExcel){
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition","attachment;filename=report.xls");
}else response.setContentType("text/html;charset=GBK");
%><%!
public String getDateList(String datestr){
String ret=StringEx.replace(datestr,new String[]{"2008-"," ","-",",,"},new String[]{"","、","/",","});

while(ret.indexOf("、、")>-1)ret=StringEx.replace(ret,"、、","、");
if(ret.startsWith("、")){
  if(ret.length()>1)ret=ret.substring(1);
  else ret="";
}
if(ret.endsWith("、")){
  if(ret.length()>1)ret=ret.substring(0,ret.length()-1);
  else ret="";
}
Vector tmp=new Vector();
String[] xx=StringEx.split(ret,"、");
if(xx!=null&&xx.length>0){
  for(int i=0;i<xx.length;i++){
   if(!tmp.contains(xx[i]))tmp.addElement(xx[i]);
  }
  ret="";
  for(int i=0;i<tmp.size();i++){
   ret+=(i==0?"":"、")+(String)tmp.elementAt(i);
  }
}else return "";
return ret;
}
%>
<html><head><meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>课程缺勤汇总表</title></head>
<script language="javascript" src="/kaoqin/system.js"></script>
<script language="javascript">
function refreshReport(out_excel){
var url1=replaceURLParameter(location.href,"college_code",$("objClass").value);
url1=replaceURLParameter(url1,"out_excel",out_excel&&out_excel==true?"true":"");
location=url1;
}
</script>
<style>
* {font-size:12px;}
body{text-align:center;}
</style>
<%

com.kaoqin.card.CardRecord.dealSynchronization("netsys","kaoqin",-1);

String college_code=request.getParameter("college_code");
if(college_code==null||college_code.equals(""))college_code="";
%>
</head>
<body>
<span style="font-size:16px;font-weight:bold;"><%=com.kaoqin.Kaoqin.getCurrentYear()%>年度第<%=com.kaoqin.Kaoqin.getCurrentTerm()%>学期<%
if(!college_code.equals("")){
out.print("<span style='color:red;font-size:16px;font-weight:bold;'>"+DataRow.getFieldValue("","kq_college","college_code='"+college_code+"'","college_name")+"</span>");
}
%>学生缺勤汇总表</span><br><br>
<%if(!isExcel){%>
<table style="font-size:13px;">
<tr><td>选择学院：</td><td><select id=objClass onChange="refreshReport(false);" style="width:160px;"><option value="" style="font-size:14px;">请选择学院</option><%=com.jspsoft.autoform.util.AutoFormTool.getSelectOptions(DataView.executeQuery("","select * from kq_college"),"college_code","college_name",college_code)%></select></td></tr>
</table><br/>
<%
}
String[] warn_color=new String[]{"red","white","yellow"};

String grade1="white";
String grade2="white";
String grade3="white";

if(!college_code.equals("")){
%>
<table cellspacing="1" bgcolor="#333333" cellpadding="6" border="<%=isExcel?1:0%>" style="text-align:center;" align=center>
<tr align="center" bgcolor="#f4f4f4" style="color:#000;font-weight:bold;height:23;">
    <td rowspan="2" width="80">班级</td>
    <td rowspan="2" width="80">学号</td>
    <td rowspan="2" width="80">姓名</td>
    <td colspan="5" width="548">缺   课</td>
    <td rowspan="2" width="200">消缺课准假日期</td>
    <td rowspan="2" width="50">可消课时累计</td>
    <td rowspan="2" width="50">累计缺勤</td>
</tr>
<tr align="center" bgcolor="#f4f4f4" style="color:#000;font-weight:bold;height:23;">
<td width="200">课程名称</td>
    <td width="50">总课时</td>
    <td width="50">缺课时</td>
    <td width="200">日期</td>
    <td width="50">累计</td>
</tr>
<%
DataView data1=new DataView();
DataView data2=new DataView();
DataView data3=new DataView();

data1.setSQL("","select * from kq_result_stat where cur_term ='"+com.kaoqin.Kaoqin.getCurrentTerm()+"' and cur_year='"+com.kaoqin.Kaoqin.getCurrentYear()+"' and plan_weeks<="+com.kaoqin.Kaoqin.getCurrentTermWeek()+" and college_code like '"+college_code+"'");
if(data1.readData()){if(data1.getRecordCount()<1){return;}}

DBOperation.executeUpdate(DBBuffer.MEM_DB,"drop table sp_report_record");
DBOperation.executeUpdate(DBBuffer.MEM_DB,"drop table sp_report");

DBBuffer.BufferDataView(data1,"sp_report_record","");
data2.setSQL("","select * from kq_result_stat where 1>2");
DBBuffer.BufferDataView(data2,"sp_report","");

data1.setSQL(DBBuffer.MEM_DB,"select class_code,student_code,course_code,class_name,student_name,course_name,total_hour,college_code,cur_year,cur_term from sp_report_record group by class_code,student_code,course_code,class_name,student_name,course_name,total_hour,college_code,cur_year,cur_term");
if(data1.readData()){

  int count_cut_hour=0,count_leave_hour=0;
  String remark_cut_hour="",remark_leave_remark="",filter1="";

  while(data1.next()){
   filter1="";
   count_cut_hour=0;
   count_leave_hour=0;
   remark_leave_remark="";
   remark_cut_hour="";

   DataRow dr=new DataRow("sp_report");
   for(int i=0;i<data1.getFldCount();i++){
    filter1+=(i==0?"":" and ")+data1.getFieldName(i)+"='"+data1.getString(i)+"'";
    dr.setField(data1.getFieldName(i).toLowerCase(),data1.getString(i));
   }
   data2.setSQL(DBBuffer.MEM_DB,"select leave_remark,cut_remark,sum(cut_hour) as cut_hour,sum(leave_hour) as leave_hour from sp_report_record where "+filter1+" group by cut_remark,leave_remark");
   if(data2.readData()){
    while(data2.next()){
     count_cut_hour  +=data2.getInt("cut_hour");
     count_leave_hour +=data2.getInt("leave_hour");
     remark_cut_hour  +=" "+data2.getString("cut_remark");
     remark_leave_remark +=" "+data2.getString("leave_remark");
    }
   }
   dr.setField("_hsqldb_auto_id_",""+data1.getRowIndex());
   dr.setField("cut_hour",""+count_cut_hour);
   dr.setField("cut_remark",""+remark_cut_hour);
   dr.setField("leave_hour",""+count_leave_hour);
   dr.setField("leave_remark",remark_leave_remark);
   //System.out.println(dr.insert(false));
   DBOperation.executeUpdate(DBBuffer.MEM_DB,dr.insert(false));
  }
}
DBOperation.executeUpdate(DBBuffer.MEM_DB,"drop table sp_report_record");

data1.setSQL(DBBuffer.MEM_DB,"select class_code,class_name,count(class_code) as rcount from sp_report group by class_code,class_name order by class_code");

if(data1.readData()){
  while(data1.next()){
   out.print("<tr align=center><td valign=top bgcolor="+grade1);
   if(data1.getInt("rcount")>1)out.print(" rowspan="+data1.getString("rcount"));
   out.print(">"+data1.getString("class_name")+"<br>"+data1.getString("class_code")+"</td>");

   data2.setSQL(DBBuffer.MEM_DB,"select student_code,student_name,count(student_code) as rcount,sum(cut_hour) as total_cut,sum(total_hour) as student_total_hour from sp_report where class_code='"+data1.getString("class_code")+"' group by student_code,student_name order by student_code");
   if(data2.readData()){
    while(data2.next()){
     out.print("<td align=center valign=top bgcolor="+grade2);
     if(data2.getInt("rcount")>1)out.print(" rowspan="+data2.getString("rcount"));
     out.print(">"+data2.getString("student_code")+"</td>");

     out.print("<td align=center valign=top bgcolor="+grade2);
     if(data2.getInt("rcount")>1)out.print(" rowspan="+data2.getString("rcount"));
     out.print(">"+data2.getString("student_name")+"</td>");

     data3.setSQL(DBBuffer.MEM_DB,"select * from sp_report where class_code='"+data1.getString("class_code")+"' and student_code='"+data2.getString("student_code")+"'");
     if(data3.readData()){
      String leave_remark="";
      int total_leave_hour=0;

      while(data3.next()){
       leave_remark+=" "+data3.getString("leave_remark");
       total_leave_hour+=data3.getInt("leave_hour",0);
      }
      leave_remark=getDateList(leave_remark);

      data3.first();
      while(data3.next()){
       out.print("<td bgcolor="+grade3+" align=left>"+data3.getString("course_name")+"<br>("+data3.getString("course_code")+")</td>");
       out.print("<td align=center bgcolor="+grade3+">"+data3.getString("total_hour")+"</td>");
       out.print("<td align=center bgcolor="+((data3.getFloat("cut_hour")/data3.getFloat("total_hour")<0.3F)?"white":"yellow")+">"+data3.getString("cut_hour")+"</td>");
       out.print("<td align=left bgcolor="+grade3+">"+getDateList(data3.getString("cut_remark"))+"</td>");
       if(data3.isFirstRow()){
        out.print("<td align=center valign=top bgcolor="+grade2);
        if(data2.getInt("rcount")>1)out.print(" rowspan="+data2.getString("rcount"));
        out.print(">"+data2.getString("total_cut")+"</td>");

        out.print("<td align=left valign=top bgcolor="+grade2);
        if(data2.getInt("rcount")>1)out.print(" rowspan="+data2.getString("rcount"));
        out.print(">"+leave_remark+"</td>");

        out.print("<td align=center valign=top bgcolor="+grade2);
        if(data2.getInt("rcount")>1)out.print(" rowspan="+data2.getString("rcount"));
        out.print(">"+total_leave_hour+"</td>");

        int abc=data2.getInt("total_cut")-total_leave_hour;//data3.getInt("leave_hour");
        out.print("<td align=center valign=top bgcolor="+(abc>-1?(((float)abc/(float)data2.getInt("student_total_hour")<0.3F)?"white":"yellow"):"red"));
        if(data2.getInt("rcount")>1)out.print(" rowspan="+data2.getString("rcount"));
        out.print(">"+abc+"</td>");
       }
       out.print("</tr>");
      }
     }
    }
   }
  }
}
data1.close();data1=null;
data2.close();data2=null;
data3.close();data3=null;
DBOperation.executeUpdate(DBBuffer.MEM_DB,"drop table sp_report");
%>
</table>
<br><br>
红色背景：表示考勤数据错误     黄色背景：学生缺勤已超过1/3课时
<br><br>
<%}%>
</body>
</html>