📄 query_mark_mn_detail.jsp
字号:
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" %>
<%@ page import="java.sql.*"%>
<%@ page import="java.text.*"%>
<%@ page import="jinghua.*"%>
<%@ page import="java.util.*"%>
<%@ page import = "jinghua.chart.servlet.WebHitChart" %>
<%@ page import = "jinghua.chart.servlet.WebHitDataSet" %>
<%@ page import = "java.io.PrintWriter" %>
<jsp:useBean id="DBCon" class="jinghua.JinghuaConn" scope="session">
</jsp:useBean>
<%!Connection con;
//查询参加某次模拟考试的所有学生的ID
String colwidth="";
boolean debug=false;
String studentID="";
String gradenote="";
String gradename="";
int maxHisSubNum =0;
String mn_testID="";
Statement stmt=null;
ResultSet rs=null;
String sql="";
String test_subs="";
int sub_num=0;
int have_test=0;
int i=0;
int j=0;
long i10=0;
long i30=0;
float f10=0;
float f30=0;
DecimalFormat df=new DecimalFormat("####.00");
class mn_test{
String id;
String name;
String gradenote;
String subs;
String totalMark;
int sub_num;
int testPeople;//全部参加人数
//参加完所有测试的学生的ID，用","分隔
int flag;//1:complete;2:uncomplete;3:do not any test;
public String toString(){
String temp="";
temp="<br> id="+id+"<br> name="+name;
return temp;
}
};
class student{
String id;
String name;
String jh_area;
String school;
String jh_area_sch;
String grade;
String test_date;
int total_mark;//总分
String avgTop10;//top 10%的总分
String avgTop30;
int posAll;//全部排名
int posPercent;//100人排名
public String toString(){
String temp="";
temp="<br> id="+id+"<br> name="+name+"<br> grade="+grade+"<br>jh_area="+jh_area+
"<br>school="+school+"<br>jh_area_sch="+jh_area_sch+"<br>test_date="+test_date;
return temp;
}
};
class History{
long id;
String stu_id;
String mn_test_id;
int sub_num;
int testPeople;
String tested_id;
int totalmark;
String position;
String top10avg;
String top30avg;
String testdate;
String [] sub_names;
String [] sub_marks;
String debug;
public String toString(){
String temp="";
temp="<br> id="+id+"<br> stu_id="+stu_id+"<br> mn_test_id="+mn_test_id+
"<br>totalmark="+totalmark+"<br>position="+position+
"<br>top10avg="+top10avg+"<br>top30avg="+top30avg;
return temp;
}
};
class Subject{
int sub_id;
String name;
String totalMark;
String realMark;
String avgArea;
String avgTotal;
String avgTop10;
String avgTop30;
String posAll;//全部排名
String posPercent;//100人排名
int testnum=0;//试题数目
String [] arrAnswer;
String [] arrMark;
String [] arrSubAnswer;
String [] arrTF;
String [] arrDif;
String [] arrSubMark;
String [] arrRate;//准确率
String [] arrID;//question id数组
public String toString(){
String temp="";
temp="<br> sub_id="+sub_id+"<br> name="+name+"<br> totalMark="+totalMark+
"<br>avgArea="+avgArea+"<br>avgTotal="+avgTotal+"<br>avgTop10="+avgTop10+
"<br>avgTop30="+avgTop30+ "<br>posAll="+posAll+ "<br>posPercent="+posPercent;
return temp;
}
};
mn_test mnTest;
student Student;
Vector subs;
Vector hisRS;
%>
<%
try{
studentID=request.getParameter("stuID");
mn_testID=request.getParameter("mn_testID");
//Tools.showalert(studentID,out);
}catch(Exception e){
Tools.showalert("您还没有登录，或连接超时，请重新登录!",out);
Tools.showJS("window.open('../login.jsp','_self')",out);
}
try{
con=DBCon.getConnection();
String filenamemn = WebHitChart.generateMnBarChartSize(Long.parseLong(mn_testID), 350,250,session, new PrintWriter(out));
//String graphURLmn = request.getContextPath() + "/servlet/DisplayChart?filename=" + filenamemn;
String graphURLmn = request.getContextPath()+"/chart/temp/"+filenamemn;
String filenamestu = WebHitChart.generateStuHisXYChartSize(studentID, Long.parseLong(mn_testID),350,250,session, new PrintWriter(out));
//String graphURLstu = request.getContextPath() + "/servlet/DisplayChart?filename=" + filenamestu;
String graphURLstu = request.getContextPath()+"/chart/temp/"+filenamestu;
//out.print("<br>graphURLmn="+graphURLmn);
//out.print("<br>graphURLstu="+graphURLstu);
//获取学生的年级信息
sql=" SELECT `grade`.`Name`, `grade`.`note` FROM"+
" `grade` INNER JOIN `UserInfo` ON (`grade`.`note` = `UserInfo`.`gradenote`)"+
" WHERE (`UserInfo`.`vcUserNo` = '"+studentID+"')";
try{
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next()){
gradenote=rs.getString("note");
gradename=rs.getString("Name");
rs.close();
stmt.close();
}
if(debug){
out.print("<br>stuID="+studentID+"<br>gradenote="+gradenote);
}
}
catch(SQLException se){
out.print("<br> query student error sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> query student error sql="+sql);
out.print("<br>"+e.toString());
}
//获取模拟考试的科目信息
sql="select * from mn_test where id="+mn_testID;
try{
mnTest=new mn_test();
Student=new student();
subs=new Vector();
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next()){
mnTest.subs=rs.getString("subs");
if(mnTest.subs==null)
mnTest.subs="-1";
else if(mnTest.subs.equals(""))
mnTest.subs="-1";
mnTest.id=mn_testID;
mnTest.name=rs.getString("name");
mnTest.sub_num=rs.getInt("sub_num");
mnTest.gradenote=gradenote;
mnTest.testPeople=rs.getInt("testpeople");
}
rs.close();
stmt.close();
if(debug){
out.print("<br>mn_test_subs="+mnTest.subs+"<br>sub_num="+mnTest.sub_num);
}
}
catch(SQLException se){
out.print("<br> query mn_test error sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> query mn_test error sql="+sql);
out.print("<br>"+e.toString());
}
//
//如果完成了模拟考试，
have_test=0;
sql="select count(*) as have_test from mn_history where stu_id='"+studentID+"' and mn_test_id="+mn_testID;
try{
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next()){
have_test=rs.getInt("have_test");
}
rs.close();
stmt.close();
if(debug){
out.print("<br>query mn_history sql="+sql);
}
}
catch(SQLException se){
out.print("<br>query mn_history sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br>query mn_history sql="+sql);
out.print("<br>"+e.toString());
}
//查询该学生是否完成该模拟考试中的所有测试
%>
<%if(mnTest.sub_num==0){
Tools.showalert("模拟考试还没有进行！",out);
Tools.showJS("window.close()",out);
}else if (have_test<1){
Tools.showalert("您还没有全部参加完这次模拟考试的各科测试，不能统计分数！",out);
Tools.showJS("window.close()",out);
}else{
//查询模拟考试的总分
sql="select FORMAT(sum(mark),0) as totalmark from test,mn_scores "+
"where test.id=mn_scores.test_id and mn_test_id="+mn_testID+" and stu_id='"+
studentID+"'";
try{
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next()){
mnTest.totalMark=rs.getString("totalmark");
}
rs.close();
stmt.close();
if(debug){
out.print("<br>mn_test totalMark sql="+sql);
out.print("<br>mn_test totalMark="+mnTest.totalMark);
}
}
catch(SQLException se){
out.print("<br> query totalpeople error sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> query totalpeople error sql="+sql);
out.print("<br>"+e.toString());
}
//查询学生信息，生成成绩单，考试日期取为所有试卷最后做的时间
sql="SELECT `UserInfo`.`vcName`, `UserInfo`.`vcUserNo`, "+
" DATE_FORMAT(`mn_scores`.`test_date`,\"%Y-%m-%e\") as test_date, `UserInfo`.`vcSchool`, `UserInfo`.`jh_area`,CONCAT(`UserInfo`.`jh_area`,'<br>(',`UserInfo`.`vcSchool`,')') AS jh_area_sch "+
"FROM `UserInfo` LEFT OUTER JOIN `grade` ON (`UserInfo`.`gradenote` = `grade`.`note`) "+
"LEFT OUTER JOIN `mn_scores` ON (`UserInfo`.`vcUserNo` = `mn_scores`.`stu_id`) "+
"WHERE UserInfo.vcUserNo='"+studentID+"' and mn_scores.mn_test_id="+mn_testID+
" and mn_scores.stu_id='"+studentID+"'"+
" ORDER BY test_date DESC"+
" LIMIT 1";
try{
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next()){
Student.grade=gradename;
Student.id=rs.getString("vcUserNo");
Student.jh_area=rs.getString("jh_area");
Student.jh_area_sch=rs.getString("jh_area_sch");
Student.name=rs.getString("vcName");
Student.school=rs.getString("vcSchool");
Student.test_date=rs.getString("test_date");
}
rs.close();
stmt.close();
if(debug){
out.print("<br> query student info sql="+sql);
//out.print("<br>student info="+Student.toString());
}
}
catch(SQLException se){
out.print("<br> query student info error sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> query student info error sql="+sql);
out.print("<br>"+e.toString());
}
//成绩和排名
//总分
try{
//个人总分
sql=" SELECT mn_history.stu_id,mn_history.mn_test_id, FORMAT(SUM(mn_scores.score),0) AS totalmark"+
" FROM `mn_history` LEFT OUTER JOIN `mn_scores` ON (`mn_history`.`mn_test_id` = `mn_scores`.`mn_test_id`) "+
" AND (`mn_history`.`stu_id` = `mn_scores`.`stu_id`) "+
" WHERE mn_history.mn_test_id="+mnTest.id+" and mn_history.stu_id ='"+Student.id +"'"+
" GROUP BY mn_history.stu_id,mn_history.mn_test_id"+
" ORDER BY totalmark desc";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(debug)
out.println("<br><br><br> person totalmark sql="+sql);
if(rs.next()){
Student.total_mark=rs.getInt("totalmark");
}
else{
Student.total_mark=0;
}
rs.close();
stmt.close();
//排名
i10=Math.round(mnTest.testPeople *0.1);//top10
i30=Math.round(mnTest.testPeople *0.3);
if(i10==0) i10=1;
if(i30==0) i30=1;
f10=0;
f30=0;
sql=" SELECT mn_history.stu_id,mn_history.mn_test_id, FORMAT(SUM(mn_scores.score),0) AS totalmark"+
" FROM mn_history LEFT OUTER JOIN mn_scores ON(mn_history.stu_id=mn_scores.stu_id) "+
" AND (`mn_history`.`mn_test_id` = `mn_scores`.`mn_test_id`)"+
" WHERE mn_history.mn_test_id="+mnTest.id+
" GROUP BY mn_history.stu_id,mn_history.mn_test_id"+
" ORDER BY totalmark desc";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
i=0;
int imark=0;
int tempPos=1;
float fmark=0;
int tmark=Integer.MAX_VALUE;
while(rs.next()){
i++;
imark=rs.getInt("totalmark");
fmark=rs.getFloat("totalmark");
if(i<=i10)
f10+=fmark;
if(i<=i30)
f30+=fmark;
//查找分数的排名
if(debug){
out.print("<br><br>student totalmark="+Student.total_mark +
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -