?? query_mark_mn_print.jsp
字號:
}
catch(Exception e){
out.print("<br> Query student's total position sql="+sql);
out.print("<br>"+e.toString());
}
//各科目的成績單
sql=" SELECT test.paper as testpaper,test.testnum as testnum,mn_scores.test_rs,`subject`.`id` AS `subid`,`subject`.`Name` AS `subname`, FORMAT(`test`.`mark`,0) AS `totalmark`, FORMAT(`mn_scores`.`score`,0) AS `score` "+
" FROM `mn_scores` LEFT OUTER JOIN `subject` ON (`mn_scores`.`sub_id` = `subject`.`ID`)"+
" LEFT JOIN `test` ON (`mn_scores`.`test_id` = `test`.`id`)"+
" WHERE (`mn_test_id` = "+mnTest.id+") AND (`stu_id` = '"+Student.id+"')"+
" ORDER BY `subid`";
try{
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
String temp=null;
String [] arrtemp=null;
String [] tests=null;
while(rs.next()){
Subject s=new Subject();
s.sub_id=rs.getInt("subid");
s.name=rs.getString("subname");
s.totalMark=rs.getString("totalmark");
s.realMark=rs.getString("score");
s.testnum=rs.getInt("testnum");
//處理試題信息
temp=rs.getString("testpaper");
tests=temp.split("#");
StringBuffer Answer=new StringBuffer();
StringBuffer Mark=new StringBuffer();
StringBuffer Dif=new StringBuffer();
StringBuffer testIDs=new StringBuffer();
StringBuffer Rate=new StringBuffer();
for(i=0;i<s.testnum;i++){
//取回試題號
arrtemp=tests[i].split("@");
testIDs.append(arrtemp[0]+",");//題號
Rate.append(T_Question.getCorrectRate(con,Integer.parseInt(arrtemp[0]))+",");
Answer.append(arrtemp[1]+",");//答案
Mark.append(arrtemp[2]+",");
Dif.append(arrtemp[3]+",");
}
s.arrAnswer=Answer.toString().split(",");
s.arrDif=Dif.toString().split(",");
s.arrMark=Mark.toString().split(",");
s.arrID=testIDs.toString().split(",");
s.arrRate=Rate.toString().split(",");
Answer=null;
Mark=null;
Dif=null;
testIDs=null;
//處理科目考試結果
temp=rs.getString("test_rs");
arrtemp=temp.split("#");
//s.arrAnswer=arrtemp[0].split(",");
//s.arrMark=arrtemp[1].split(",");
s.arrSubAnswer=arrtemp[0].split(",");
s.arrTF=arrtemp[1].split(",");
//s.arrDif=arrtemp[4].split(",");
s.arrSubMark=arrtemp[2].split(",");
subs.addElement(s);
}
rs.close();
stmt.close();
if(debug){
out.print("<br>subject info sql="+sql);
out.print("<br>subject info="+subs.toString());
}
}
catch(SQLException se){
out.print("<br> subject info error sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> subject info error sql="+sql);
out.print("<br>"+e.toString());
}
//科目的平均
try{
//分校平均
sql="SELECT `UserInfo`.`jh_area`, `mn_history`.`mn_test_id`, `mn_scores`.`sub_id`,"+
" FORMAT(AVG(`mn_scores`.`score`),2) AS `score_avg`"+
" 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`)"+
" LEFT OUTER JOIN `UserInfo` ON (`mn_history`.`stu_id` = `UserInfo`.`vcUserNo`) "+
" WHERE"+
" (`UserInfo`.`jh_area` = '"+Student.jh_area+"') AND "+
" (`mn_history`.`mn_test_id` = "+mnTest.id+") "+
" GROUP BY `UserInfo`.`jh_area`, `mn_history`.`mn_test_id`, `mn_scores`.`sub_id`"+
" ORDER BY `mn_scores`.`sub_id`";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
i=0;
Subject s=null;
while(rs.next()){
s=(Subject)subs.elementAt(i);
s.avgArea=rs.getString("score_avg");
i++;
}
rs.close();
stmt.close();
if(debug){
out.print("<br>subject area_avg sql="+sql);
out.print("<br>subject info="+subs.toString());
}
//全體平均
sql="SELECT `mn_scores`.`mn_test_id`, `mn_scores`.`sub_id`,"+
" FORMAT(AVG(`mn_scores`.`score`),2) AS `score_avg`"+
" 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_scores`.`mn_test_id`, `mn_scores`.`sub_id`"+
" ORDER BY `mn_scores`.`sub_id`";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
i=0;
s=null;
while(rs.next()){
s=(Subject)subs.elementAt(i);
s.avgTotal=rs.getString("score_avg");
i++;
}
rs.close();
stmt.close();
if(debug){
out.print("<br>subject total sql="+sql);
out.print("<br>subject info="+subs.toString());
}
}
catch(SQLException se){
out.print("<br> subject total error sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> subject total error sql="+sql);
out.print("<br>"+e.toString());
}
//10% top 30% top
//
DecimalFormat df=new DecimalFormat("####.##");
for(j=0;j<subs.size();j++){
//每一科目
Subject s=(Subject)subs.elementAt(j);
try{
sql=" SELECT mn_history.mn_test_id,mn_history.stu_id, mn_scores.score, mn_scores.sub_id "+
" 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+
" AND sub_id="+s.sub_id+
" ORDER BY mn_scores.score DESC";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
//top10
i10=Math.round(mnTest.testPeople *0.1);
i30=Math.round(mnTest.testPeople *0.3);
if(i10==0) i10=1;
if(i30==0) i30=1;
f10=0;
f30=0;
if(debug){
out.print("<br><br><br>Begin:j="+j+"<br>sub_name="+s.name +
"<br>f10="+f10+"<br>i30="+i30+"<br>f30="+f30);
}
i=0;
int tempPos=1;
float fmark=Float.MAX_VALUE;
float tempf=0;
float realf=Float.parseFloat(s.realMark);
if(debug){
out.print("<br>top10-top30:"+
"<br>i10="+i10+
"<br>i30="+i30);
}
while(rs.next()){
tempf=rs.getFloat("score");
if(debug){
out.print("<br>sub_real_mark="+realf+
"<br>rs:score="+tempf);
}
i++;
if(i<=i10)
f10+=tempf;
if(i<=i30)
f30+=tempf;
if(tempf==realf){
s.posAll=String.valueOf(tempPos);
if(mnTest.testPeople<=100)
s.posPercent=s.posAll;
else
s.posPercent=String.valueOf(100*tempPos/mnTest.testPeople);
}
else if(fmark>tempf){
tempPos++;
fmark=tempf;
}
}
if(debug){
out.print("<br>end:i10="+i10+"<br>f10="+f10+"<br>i30="+i30+
"<br>f30="+f30+"<br>posall="+s.posAll);
}
rs.close();
stmt.close();
df=new DecimalFormat("####.00");
s.avgTop10=df.format(f10/i10);
s.avgTop30=df.format(f30/i30);
if(debug){
out.print("<br>avgTop10="+s.avgTop10+"<br>avgTop30="+s.avgTop30);
out.print("<br>subject top10-30 sql="+sql);
}
}
catch(SQLException se){
out.print("<br> subject info error sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> subject info error sql="+sql);
out.print("<br>"+e.toString());
}
}//end for
//更新歷史記錄
//先將這次查詢的結果存入mn_history
try{
sql=" select * from mn_history where mn_test_id="+mnTest.id+
" and stu_id='"+Student.id+"'";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next()){
rs.close();
stmt.close();
StringBuffer sb=new StringBuffer();
for(i=0;i<subs.size();i++){
Subject s=(Subject)subs.elementAt(i);
sb.append(s.name+":"+s.realMark+"#");
}
sql=" update mn_history set totalmark="+Student.total_mark+","+
" submark='"+sb.toString()+"',test_date='"+Student.test_date+"',"+
" sub_num="+mnTest.sub_num+","+
" totalpeople="+mnTest.testPeople+","+
" position="+Student.posAll+","+
" top10avg="+Student.avgTop10+","+
" top30avg="+Student.avgTop30+
" where stu_id='"+Student.id+"' and mn_test_id="+mnTest.id;
stmt=con.createStatement();
stmt.executeUpdate(sql);
if(debug) out.println("<br>update mn_history sql="+sql);
stmt.close();
}
else{
//將新記錄插入數據庫
rs.close();
stmt.close();
StringBuffer sb=new StringBuffer();
for(i=0;i<subs.size();i++){
Subject s=(Subject)subs.elementAt(i);
sb.append(s.name+":"+s.realMark+"#");
}
sql=" insert into mn_history(stu_id,mn_test_id,totalmark,submark,test_date,sub_num,"+
" position,top10avg,top30avg,testpeople) "+
" values('"+Student.id+"',"+mnTest.id+","+Student.total_mark+",'"+
sb.toString()+"','"+Student.test_date+"',"+mnTest.sub_num+","+
Student.posAll+","+Student.avgTop10+","+Student.avgTop30+","+mnTest.testPeople+")";
stmt=con.createStatement();
stmt.executeUpdate(sql);
stmt.close();
}
}catch(SQLException se){
out.print("<br> insert into mn_history sql="+sql);
out.print("<br>"+se.toString());
}
catch(Exception e){
out.print("<br> insert into mn_history sql="+sql);
out.print("<br>"+e.toString());
}
//從mn_history中選取記錄 stu_id,mn_test_id,totalmark,submark,test_date
try{
sql="SELECT MAX(`mn_history`.`sub_num`) AS `maxsub` FROM `mn_history`"+
" WHERE (stu_id = '"+Student.id+"')";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next())
maxHisSubNum=rs.getInt("maxsub");
else
maxHisSubNum=0;
rs.close();
stmt.close();
sql=" SELECT `mn_test`.`sub_num`,`mn_test`.`testpeople`,`mn_history`.`totalpeople`, "+
" `mn_history`.`mn_test_id`, `mn_history`.`totalmark`, `mn_history`.`position`,"+
" `mn_history`.`top10avg`, `mn_history`.`top30avg`, `mn_history`.`submark`,"+
" DATE_FORMAT(`mn_history`.`test_date`,\"%Y-%m-%e\") AS `test_date`, "+
" `mn_history`.`stu_id` "+
" FROM `mn_history` LEFT OUTER JOIN `mn_test` ON (`mn_history`.`mn_test_id` = `mn_test`.`id`)"+
" WHERE (`mn_history`.`stu_id` = '"+Student.id+"')"+" AND (`mn_test`.`grade_note` = '"+gradenote +"')"+
" ORDER BY `test_date` DESC";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
hisRS=new Vector();
if(debug)
out.print("<br> query mn_history sql="+sql);
String submarks;
while(rs.next()){
//構造一條歷史記錄/
History h=new History();
h.sub_num=rs.getInt("sub_num");
h.testdate=rs.getString("test_date");
h.mn_test_id=rs.getString("mn_test_id");
h.stu_id=rs.getString("stu_id");
h.totalmark=rs.getInt("totalmark");
submarks=rs.getString("submark");
h.sub_marks=submarks.split("#");
if(rs.getInt("testpeople")==rs.getInt("totalpeople")){
//模擬考試的總人數沒有變化
h.top10avg=rs.getString("top10avg");
h.top30avg=rs.getString("top30avg");
h.position=rs.getString("position");
h.testPeople=rs.getInt("totalpeople");
}
else{
//歷史排名 //重新求排名
h.testPeople=rs.getInt("testpeople");
i10=Math.round(h.testPeople *0.1);//top10
i30=Math.round(h.testPeople *0.3);
if(i10==0) i10=1;
if(i30==0) i30=1;
f10=0;
f30=0;
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.stu_id=mn_scores.stu_id) "+
" AND (`mn_history`.`mn_test_id` = `mn_scores`.`mn_test_id`)"+
" WHERE mn_history.mn_test_id="+h.mn_test_id+
" GROUP BY mn_history.stu_id,mn_history.mn_test_id"+
" ORDER BY totalmark desc";
Statement tempstmt=con.createStatement();
ResultSet temprs=tempstmt.executeQuery(sql);
i=0;
int imark=0;
int tempPos=1;
float fmark=0;
int tmark=Integer.MAX_VALUE;
while(temprs.next()){
i++;
imark=temprs.getInt("totalmark");
fmark=temprs.getFloat("totalmark");
if(i<=i10)
f10+=fmark;
if(i<=i30)
f30+=fmark;
//查找分數的排名
if(debug){
out.print("<br><br>history student totalmark="+Student.total_mark +
"<br>sql totalmark="+temprs.getInt("totalmark"));
}
if(h.totalmark==imark){
h.position=String.valueOf(tempPos);
//Student.posAll=tempPos;
}
else if(tmark>imark){//分數不同
tempPos++;
tmark=imark;
}
}//end while
temprs.close();
tempstmt.close();
df=new DecimalFormat("####.00");
h.top10avg=df.format(f10/i10);
h.top30avg=df.format(f30/i30);
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -