?? query_mark_mn_print.jsp
字號:
<%@ page contentType="application/msword; 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 =request.getParameter("filenamemn");// WebHitChart.generateMnBarChart(Long.parseLong(mn_testID), session, new PrintWriter(out));
String graphURLmn = request.getContextPath()+"/chart/temp/"+filenamemn; //+ "/servlet/DisplayChart?filename=" + filenamemn;
String filenamestu = request.getParameter("filenamestu");//WebHitChart.generateStuHisXYChart(studentID, Long.parseLong(mn_testID),session, new PrintWriter(out));
String graphURLstu = request.getContextPath()+"/chart/temp/"+filenamestu; //+ "/servlet/DisplayChart?filename=" + 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 +
"<br>sql totalmark="+rs.getInt("totalmark"));
}
if(Student.total_mark==imark){
Student.posAll=tempPos;
if(mnTest.testPeople>100)
Student.posPercent=100*tempPos/mnTest.testPeople;
else
Student.posPercent=tempPos;
}
else if(tmark>imark){//分數不同
tempPos++;
tmark=imark;
}
}
rs.close();
stmt.close();
Student.avgTop10=df.format(f10/i10);
Student.avgTop30=df.format(f30/i30);
if(debug){
out.print("<br>Query student's position: sql="+sql);
}
}
catch(SQLException se){
out.print("<br> Query student's position sql="+sql);
out.print("<br>"+se.toString());
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -