conditionblogicclass.java

关于学校管理

package student.manage.condition;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import student.manage.javaConnction.connectionDB;
import student.manage.mark.MarkBean;

public class ConditionBlogicClass {

	connectionDB db = new connectionDB();

	Statement state = null;

	ResultSet result = null;

	int insertNum = 0;

	public Statement getConn() {

		state = db.getConnectionDB();
		return state;
	}

	public int getStudentId(MarkBean markBean) {

		String sql = "select COUNT(*) from RIKO_MARK where GRADE_ID = '" + markBean.getGRADE_ID() +"'";
		System.out.println(sql);
		int k = 0;
		ArrayList list = new ArrayList();
		try {

			ResultSet rs = this.getConn().executeQuery(sql);
			while (rs.next()) {
				k = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return k;
	}
	
	public ArrayList getMark(MarkBean markBean) {
		ArrayList list = new ArrayList();
		int k = this.getStudentId(markBean);
		String MARK = "";
		String sql = "";
			BigDecimal markBig2 = new BigDecimal(markBean.getMarkConditionStart());
			BigDecimal markBig3 = new BigDecimal(markBean.getMarkConditionEnd());
			BigDecimal studentSize = new BigDecimal(k);
			if(markBean.getCOURSE_ID().equals("01")) {
				MARK = "YUWEN";
			} else if(markBean.getCOURSE_ID().equals("02")){
				MARK = "SHUXUE";
			}else if(markBean.getCOURSE_ID().equals("03")){
				MARK = "YINGYU";
			}else if(markBean.getCOURSE_ID().equals("04")){
				MARK = "ZHENGZHI";
			}else if(markBean.getCOURSE_ID().equals("05")){
				MARK = "LISHI";
			}else if(markBean.getCOURSE_ID() .equals( "06")){
				MARK = "WULI";
			}else if(markBean.getCOURSE_ID() .equals("07")){
				MARK = "HUAXUE";
			}else if(markBean.getCOURSE_ID() .equals("08")){
				MARK = "DILI";
			}else if(markBean.getCOURSE_ID() .equals( "09")){
				MARK = "SHENGWU";
			}
			if(markBean.getMarkConditionStart().equals(0) || markBean.getMarkConditionStart().equals(1)) {
				 sql = "select A.ID from (select * from RIKO_MARK where "+MARK+" >=(select min(T."+MARK+") from (Select  TOP "+Math.round(studentSize.multiply(markBig3.divide(new BigDecimal(100))).doubleValue())+" ("+MARK+") from RIKO_MARK t  where GRADE_ID = '" + markBean.getGRADE_ID() + "' order by "+MARK+" desc) AS T ) and GRADE_ID = '" + markBean.getGRADE_ID() + "' and "+MARK+" <=(select min(T."+MARK+") from (Select  TOP "+1+" ("+MARK+")  from RIKO_MARK t  where GRADE_ID = '" + markBean.getGRADE_ID() + "' ORDER BY "+MARK+" DESC) AS T )and GRADE_ID = '" + markBean.getGRADE_ID() + "') AS A ";
			} else {
				 sql = "select A.ID from (select * from RIKO_MARK where "+MARK+" >=(select min(T."+MARK+") from (Select  TOP "+Math.round(studentSize.multiply(markBig3.divide(new BigDecimal(100))).doubleValue())+" ("+MARK+") from RIKO_MARK t  where GRADE_ID = '" + markBean.getGRADE_ID() + "' order by "+MARK+" desc) AS T ) and GRADE_ID = '" + markBean.getGRADE_ID() + "' and "+MARK+" <=(select min(T."+MARK+") from (Select  TOP "+Math.round(studentSize.multiply(markBig2.divide(new BigDecimal(100))).doubleValue())+" ("+MARK+")  from RIKO_MARK t  where GRADE_ID = '" + markBean.getGRADE_ID() + "' ORDER BY "+MARK+" DESC) AS T ) and GRADE_ID = '" + markBean.getGRADE_ID() + "') AS A ";
			}
			
			
			//String sql = "select TOP " + Math.round(studentSize.multiply(markBig2.divide(new BigDecimal(100))).doubleValue()) +" ("+MARK+") ,ID from RIKO_MARK where GRADE_ID = '" + markBean.getGRADE_ID() + "' order by "+MARK+" desc";
			System.out.println(sql);
			try {
				System.out.println("1111");
				ResultSet rs = this.getConn().executeQuery(sql);
				while (rs.next()) {
					MarkBean markBean1 = new MarkBean();
					markBean1.setID(rs.getInt(1));
					list.add(markBean1);
					//BigDecimal markBig = new BigDecimal(rs.getInt("MARK"));
					//int secondMark = markBig.multiply(markBig2).divide(new BigDecimal(100)).intValue();
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		return list;
	}
	
	public int updateSecondMark(MarkBean markBean) {
		
		int isnotOK = 0;
		ArrayList list = this.getMark(markBean);
		for(int i = 0 ; i<list.size() ; i++) {
			String sql = "";
			if(markBean.getCOURSE_ID() .equals("01")) {
				
				sql = "update  RIKO_MARK set YUWEN_SECOND_MARK = " + markBean.getSECOND_MARK() + " , YUWEN_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			} else if (markBean.getCOURSE_ID() .equals("02")){
				
				sql = "update  RIKO_MARK set SHUXUE_SECOND_MARK = " + markBean.getSECOND_MARK() + " , SHUXUE_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}else if (markBean.getCOURSE_ID() .equals("03")){
				
				sql = "update  RIKO_MARK set YINGYU_SECOND_MARK = " + markBean.getSECOND_MARK() + " , YINGYU_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}else if (markBean.getCOURSE_ID().equals("04")){
				
				sql = "update  RIKO_MARK set ZHENGZHI_SECOND_MARK = " + markBean.getSECOND_MARK() + " , ZHENGZHI_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}else if (markBean.getCOURSE_ID() .equals( "05")){
				sql = "update  RIKO_MARK set LISHI_SECOND_MARK = " + markBean.getSECOND_MARK() + " ,  LISHI_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}else if (markBean.getCOURSE_ID() .equals("06")){
				sql = "update  RIKO_MARK set WULI_SECOND_MARK = " + markBean.getSECOND_MARK() + " , WULI_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}else if (markBean.getCOURSE_ID() .equals( "07")){
				sql = "update  RIKO_MARK set HUAXUE_SECOND_MARK = " + markBean.getSECOND_MARK() + " ,  HUAXUE_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}else if (markBean.getCOURSE_ID() .equals("08")){
				sql = "update  RIKO_MARK set DILI_SECOND_MARK = " + markBean.getSECOND_MARK() + " ,  DILI_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}else if (markBean.getCOURSE_ID().equals("09")){
				sql = "update  RIKO_MARK set SHENGWU_SECOND_MARK = " + markBean.getSECOND_MARK() + " ,  SHENGWU_B= '"+ markBean.getSECOND_MARK_B()+"' where ID = '" +((MarkBean)list.get(i)).getID() + "'";
			}
			
			System.out.println(sql);
			try {

				   isnotOK = this.getConn().executeUpdate(sql);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isnotOK;
	}
}