package com.mingrisoft.dao;

import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.mingrisoft.actionForm.QueryResultIfForm;
import com.mingrisoft.actionForm.StaticForm;
import com.mingrisoft.actionForm.StuResultForm;
import com.mingrisoft.core.ConnDB;

public class StuResultDAO {
	private ConnDB conn=new ConnDB();
    //查询方法
    public List query(String stuId) {
    	List stuResultList = new ArrayList();
        StuResultForm stuResultForm1 = null;
        String sql="";
        if(stuId.equals("")){
            sql = "SELECT * FROM tb_stuResult ORDER BY joinTime DESC";
        }else{
        	sql = "SELECT * FROM tb_stuResult WHERE stuId='" +stuId+ "'";
        }
        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                stuResultForm1 = new StuResultForm();
                stuResultForm1.setID(rs.getInt(1));
                stuResultForm1.setStuId(rs.getString(2));
                stuResultForm1.setWhichLesson(rs.getString(3));
                stuResultForm1.setResSingle(rs.getInt(4));
                stuResultForm1.setResMore(rs.getInt(5));
                stuResultForm1.setResTotal(rs.getInt(6));
                stuResultForm1.setJoinTime(java.text.DateFormat.getDateTimeInstance().parse(rs.getString(7)));               
                stuResultList.add(stuResultForm1);
            }
        } catch (Exception ex) {
        	System.out.println("查询学生成绩(全部和按准考证精确查询)时产生的错误："+ex.getMessage());
        }
        return stuResultList;
    }
    public List query(QueryResultIfForm q){
    	List stuResultList = new ArrayList();
        StuResultForm stuResultForm1 = null;
        String sql="SELECT * FROM tb_stuResult WHERE "+q.getQueryIf()+" like '%"+q.getKey()+"%'";
        System.out.println("SQL："+sql);
        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                stuResultForm1 = new StuResultForm();
                stuResultForm1.setID(rs.getInt(1));
                stuResultForm1.setStuId(rs.getString(2));
                stuResultForm1.setWhichLesson(rs.getString(3));
                stuResultForm1.setResSingle(rs.getInt(4));
                stuResultForm1.setResMore(rs.getInt(5));
                stuResultForm1.setResTotal(rs.getInt(6));
                stuResultForm1.setJoinTime(java.text.DateFormat.getDateTimeInstance().parse(rs.getString(7)));               
                stuResultList.add(stuResultForm1);
            }
        } catch (Exception ex) {
        	System.out.println("带条件查询学生成绩时产生的错误："+ex.getMessage());
        }
        return stuResultList;
    }
    
    //考试成绩统计查询
    @SuppressWarnings("unchecked")
	public List getStatic() {
    	List staticList = new ArrayList();
    	StaticForm staticForm1 = null;
        String sql="";
        DecimalFormat   fnum  =   new  DecimalFormat("##0.00");    
        sql = "select count(*) num,sum(jige) ji,sum(youxiu) youxiu,whichLesson " +
        		"from (select *, (case when (resSingle+resMore)> 60 then 1 else 0 end)  as jige," 
        +
        		"(case when (resSingle+resMore)> 80 then 1 else 0 end)  " +
        		"as youxiu from tb_StuResult) a group by whichLesson";
        
        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
            	staticForm1 = new StaticForm();
            	float jige=rs.getFloat(2)/rs.getFloat(1);
            	float youxiu=rs.getFloat(3)/rs.getFloat(1);
            	staticForm1.setExamNum(rs.getInt(1));
            	staticForm1.setJige(fnum.format(jige));
            	staticForm1.setYouxiu(fnum.format(youxiu));
            	staticForm1.setCourseName(rs.getString(4));
            	staticList.add(staticForm1);
            }
        } catch (Exception ex) {
        	System.out.println("查询成绩统计时时产生的错误："+ex.getMessage());
        }
        return staticList;
    }

}
