op_kqinfo.java

考勤管理系统是针对学校每个月的考勤的报表进行总结

package action;

import java.sql.SQLException;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;

import util.KqInfo;

public class Op_kqinfo extends DBOption {
	private KqInfo akqinfo = null;
	private Vector<KqInfo> kqlist = null;
	private double kqsum = 0;
	
	private String sqlStr = "";
	public String msg = "";
	
	public Op_kqinfo() {
		this.setPoolName("kaoqin");
	}
	
	/**
	 * 判断记录是否已经存在
	 * @param week 周次
	 * @param stdid 学号
	 * @return boolean 存在返回true 不存在返回false
	 */
	public boolean isExist(String week, String stdid) {
		if (week == null || week.equals("")) {
			this.msg = "请指定周次ID";
			return false;
		}
		if (stdid == null || stdid.equals("")) {
			this.msg = "请指定学生学号";
			return false;
		}
		sqlStr = "select * from Kq_info where Kq_week = " + week;
		sqlStr = sqlStr + " and Kq_std_ID = '" + stdid + "'";
		
		try {
			rs = this.query(sqlStr);
			if (rs.next()) {
				rs.close();
				freeConn();
				return true;
			} else {
				rs.close();
				this.msg = "没有您要查找的考勤记录!";
				freeConn();
				return false;
			}
		} catch (SQLException e) {
			this.msg = "查询时出错!请稍候再试!";
			Log.writeLog(e, "action.Op_kqinfo.isExist:\n\t判断第"+
					sqlStr+"记录是否存在时出错!");
			return false;
		}
	}
	
	/**
	 * 获取请求中的参数列表
	 * @param request 请求
	 * @return boolean 获取成功返回true 获取失败返回false
	 */
	public boolean getRequest(HttpServletRequest request) {
		String tempStr = "";
		int tempInt = 0;
		
		this.akqinfo = new KqInfo();
		//获取院系信息
		tempStr = request.getParameter("dep");
		if (tempStr == null || tempStr.equals("")) {
			this.msg = "院系不能为空";
			return false;
		}
		akqinfo.setKq_stdDep(tempStr);
			
		//获取周次
		tempStr = request.getParameter("week");
		if (tempStr == null || tempStr.equals("")) {
			this.msg = "请选择周次!";
			return false;
		}
		long wk = 0;
		try {
			wk = Long.parseLong(tempStr);
		} catch (NumberFormatException e) {
			this.msg = "请从正确的位置访问!";
			return false;
		}
		akqinfo.setKq_week(wk+"");
		
		//获取班级信息	
		tempStr = request.getParameter("cls");
		if (tempStr == null || tempStr.equals("") || tempStr.equals("+班级")) {
			this.msg = "请填写班级!";
			return false;
		}
		akqinfo.setKq_stdClass(tempStr);
			
		//获取学号	
		tempStr = request.getParameter("std");
		if (tempStr == null || tempStr.equals("") || tempStr.equals("+学生")) {
			this.msg = "请选择学生!";
			return false;
		}
		akqinfo.setKq_stdId(tempStr);
		
		tempStr = request.getParameter("name");
		if (tempStr == null || tempStr.equals("")) {
			this.msg = "请选择学生!";
			return false;
		}
		akqinfo.setKq_stdName(tempStr);
		//获取迟到节数	
		tempStr = request.getParameter("late");
		if (tempStr == null || tempStr.equals("")) {
			this.msg = "请填写迟到节数!";
			return false;
		}
		try {
			tempInt = Integer.valueOf(tempStr);
		} catch (NumberFormatException e) {
			this.msg = "请从正确的位置访问!";
			return false;
		}
		akqinfo.setKq_late(tempInt);
			
		//获取早退节数	
		tempStr = request.getParameter("ahead");
		if (tempStr == null || tempStr.equals("")) {
			this.msg = "请填写早退数!";
			return false;
		}
		try {
			tempInt = Integer.valueOf(tempStr);
		} catch (NumberFormatException e) {
			this.msg = "请从正确的位置访问!";
			return false;
		}
		akqinfo.setAhead(tempInt);

		//获取旷课节数
		tempStr = request.getParameter("absent");
		if (tempStr == null || tempStr.equals("")) {
			this.msg = "请填写旷课节数!";
			return false;
		}
		try {
			tempInt = Integer.valueOf(tempStr);
		} catch (NumberFormatException e) {
			this.msg = "请从正确的位置访问!";
			return false;
		}
		akqinfo.setAbsent(tempInt);

		//获取请假节数
		tempStr = request.getParameter("leave");
		if (tempStr == null || tempStr.equals("")) {
			this.msg = "请填写请假节数!";
			return false;
		}
		try {
			tempInt = Integer.valueOf(tempStr);
		} catch (NumberFormatException e) {
			this.msg = "请从正确的位置访问!";
			return false;
		}
		akqinfo.setLeave(tempInt);
		return true;
	}
	
	/**
	 * 添加一条数据
	 * @param request 添加请求
	 * @return boolean 添加成功返回true 不成功返回false
	 */
	public boolean insertOne(HttpServletRequest request) {
		if (!getRequest(request)) {
			return false;
		}
		if (isExist(akqinfo.getKq_week(), akqinfo.getKq_stdId())) {
			this.msg = "本周"+akqinfo.getKq_stdId()+"同学已经有缺勤记录!";
			return false;
		}
		sqlStr = "insert into kq_info (Kq_week, Kq_Std_ID, " +
				"Kq_Std_name,Kq_Std_class, Kq_Std_dep, Kq_late, " +
				"Kq_ahead, Kq_absent, Kq_leave)	values(" +
				akqinfo.getKq_week()+",'"+akqinfo.getKq_stdId()+
				"','"+akqinfo.getKq_stdName()+
				"','"+akqinfo.getKq_stdClass()+
				"','"+akqinfo.getKq_stdDep()+
				"',"+akqinfo.getKq_late()+
				","+akqinfo.getAhead()+
				","+akqinfo.getAbsent()+
				","+akqinfo.getLeave()+")";
		//System.out.println(sqlStr);
		try {
			this.update(sqlStr);
			this.freeConn();
			return true;
		} catch (SQLException e) {
			this.msg = "添加记录失败!请稍候再试!";
			Log.writeLog(e, "action.Op_kqinfo.insertOne:\n\t"+msg);
			this.freeConn();
			return false;
		}
	}
	
	/**
	 * 获取指定的学号同学的所有缺勤记录
	 * @param std
	 * @return
	 */
	public Vector getStdKq(String std) {
		sqlStr = "select * from akq where 1=1 ";
		if (std==null) std = "";
		if (!std.equals("")) {
			sqlStr = sqlStr + " and akq_stdId = '" + std + "'";
		}
		//System.out.println(sqlStr);
		this.kqsum = 0;
		try {
			rs = this.query(sqlStr);
			this.kqlist = new Vector<KqInfo>();
			while(rs.next()) {
				this.akqinfo = new KqInfo();
				akqinfo.setKq_Id(rs.getLong("akq_Id"));
				akqinfo.setKq_year(rs.getString("akq_year"));
				akqinfo.setKq_term(rs.getString("akq_term"));
				akqinfo.setKq_weekID(rs.getLong("akq_weekId"));
				akqinfo.setKq_week(rs.getString("akq_week"));
				akqinfo.setKq_stdId(rs.getString("akq_stdId"));
				akqinfo.setKq_stdName(rs.getString("akq_stdName"));
				akqinfo.setKq_stdClass(rs.getString("akq_stdCls"));
				akqinfo.setKq_stdDep(rs.getString("akq_stdDep"));
				akqinfo.setKq_late(rs.getInt("akq_late"));
				akqinfo.setAhead(rs.getInt("akq_ahead"));
				akqinfo.setAbsent(rs.getInt("akq_absent"));
				akqinfo.setLeave(rs.getInt("akq_leave"));
				this.kqsum = kqsum + akqinfo.getAbsent() + (akqinfo.getKq_late()+
						akqinfo.getAhead()+akqinfo.getLeave())/2.0;
				kqlist.addElement(akqinfo);
				System.out.println(kqsum);
			}
			if (kqlist.size()==0) {
				this.msg = std+"同学 没有缺勤记录!";
				this.freeConn();
				return null;
			}
			this.freeConn();
			return kqlist;
		} catch (SQLException e) {
			this.msg = "查询时出错!请稍候再试!";
			Log.writeLog(e, "action.Op_kqinfo.getStdKq:\n\t" +
					"查询学生"+std+"考勤制度情况时出错!");
			this.freeConn();
			return null;
		}
	}
	
	/**
	 * 统计
	 * @param request
	 * @return
	 */
	public Vector queryStat(HttpServletRequest request) {
		String dep = request.getParameter("q_dep");
		if (dep == null) dep = "";
		String cls = request.getParameter("q_class");
		if (cls == null) cls = "";
		String year = request.getParameter("q_year");
		if (year == null) year = "";
		String term = request.getParameter("q_term");
		if (term == null) term = "";
		String moon = request.getParameter("q_month");
		if (moon == null) moon = "";
		String week = request.getParameter("q_week");
		if (week == null) week = "";
		String plow = request.getParameter("q_low");
		if (plow == null) plow = "";
		String ptop = request.getParameter("q_top");
		if (ptop == null) ptop = "";
		
		if (!year.equals("") && !term.equals("") && !moon.equals("") && !week.equals("")) {
			//以周统计
			sqlStr = "select * from statByWeek where 1=1 ";
			sqlStr = sqlStr + " and akq_year = '" + year + "'";
			sqlStr = sqlStr + " and akq_term = '" + term + "'";
			sqlStr = sqlStr + " and akq_month = " + moon;
			sqlStr = sqlStr + " and akq_week = " + week;
		} else if (!year.equals("") && !term.equals("") && !moon.equals("")) {
			//以学月统计
			sqlStr = "select * from statByMonth where 1=1 ";
			sqlStr = sqlStr + " and akq_year = '" + year + "'";
			sqlStr = sqlStr + " and akq_term = '" + term + "'";
			sqlStr = sqlStr + " and akq_month = " + moon;
		} else if (!year.equals("") && !term.equals("")) {
			//以学期统计
			sqlStr = "select * from statByTerm where 1=1 ";
			sqlStr = sqlStr + " and akq_year = '" + year + "'";
			sqlStr = sqlStr + " and akq_term = '" + term + "'";
		} else if (!year.equals("")) {
			//以学年统计
			sqlStr = "select * from statByYear where 1=1 ";
			sqlStr = sqlStr + " and akq_year = '" + year + "'";
		} else {
			//不以时间分类
			sqlStr = "select * from statKq where 1=1 ";
		}
		
		if (!dep.equals("")) //以院系统计
			sqlStr = sqlStr + " and akq_stdDep = '" + dep + "'";
		if (!cls.endsWith("")) //以班级统计
			sqlStr = sqlStr + " and akq_stdCls = '" + cls + "'";
		if (!plow.equals("")) //处分的下限
			sqlStr = sqlStr + " and kq_sum >= " + plow;
		if (!ptop.equals("")) //处分的上限
			sqlStr = sqlStr + " and kq_sum < " + ptop;
		sqlStr = sqlStr + " order by akq_StdId";
		
		//System.out.println(sqlStr);
		try {
			rs = this.query(sqlStr);
			this.kqlist = new Vector<KqInfo>();
			while(rs.next()) {
				this.akqinfo = new KqInfo();
				akqinfo.setKq_stdId(rs.getString("akq_StdId"));
				akqinfo.setKq_stdName(rs.getString("akq_StdName"));
				akqinfo.setKq_stdClass(rs.getString("akq_StdCls"));
				akqinfo.setKq_stdDep(rs.getString("akq_StdDep"));
				akqinfo.setKq_year(year);
				akqinfo.setKq_term(term);
				akqinfo.setKq_month(moon);
				akqinfo.setKq_week(week);
				akqinfo.setKq_sum(rs.getFloat("kq_sum"));
				kqlist.addElement(akqinfo);
			}
			rs.close();
			freeConn();
			if (kqlist.size() == 0) {
				this.msg = "没有找到您统计的相关缺勤计录!";
				return null;
			}
			return kqlist;
		} catch (SQLException e) {
			freeConn();
			this.msg = "统计时出错!请稍候再试!";
			Log.writeLog(e, "action.Op_kqinfo.queryStat:\n\t" +
					"统计" + sqlStr + "时出错!");
			return null;
		}
	}
	
	public boolean getRequestForModi(HttpServletRequest request) {
		int tempInt = 0;
		this.akqinfo = new KqInfo();
		String reqPar = "";
		
		reqPar = request.getParameter("week");
		if (reqPar == null || reqPar.equals("")) {
			this.msg = "获取周信息出错";
			return false;
		}
		long kqid =0;
		try {
			kqid = Long.parseLong(reqPar);
		} catch (NumberFormatException e) {
			this.msg = "请从正确位置访问本页!";
			return false;
		}
		this.akqinfo.setKq_weekID(kqid);
		
		reqPar = request.getParameter("sid");
		if (reqPar == null || reqPar.equals("")) {
			this.msg = "获取学号信息出错";
			return false;
		}
		this.akqinfo.setKq_stdId(reqPar);
		
		reqPar = request.getParameter("late");
		try {
			tempInt = Integer.parseInt(reqPar);
		} catch (NumberFormatException e) {
			this.msg = "请输入正确格式的迟到数";
			return false;
		}
		this.akqinfo.setKq_late(tempInt);
		
		reqPar = request.getParameter("absent");
		try {
			tempInt = Integer.parseInt(reqPar);
		} catch (NumberFormatException e) {
			this.msg = "请输入正确格式的旷课数";
			return false;
		}
		this.akqinfo.setAbsent(tempInt);
		
		reqPar = request.getParameter("ahead");
		try {
			tempInt = Integer.parseInt(reqPar);
		} catch (NumberFormatException e) {
			this.msg = "请输入正确格式的早退数";
			return false;
		}
		this.akqinfo.setAhead(tempInt);
		
		reqPar = request.getParameter("leave");
		try {
			tempInt = Integer.parseInt(reqPar);
		} catch (NumberFormatException e) {
			this.msg = "请输入正确格式的请假数";
			return false;
		}
		this.akqinfo.setLeave(tempInt);
		
		return true;
	}
	
	public boolean modifyInfo(HttpServletRequest request) {
		if (getRequestForModi(request)) {
			if (isExist(akqinfo.getKq_weekID()+"", akqinfo.getKq_stdId())) {
				//要修改的记录存在
				sqlStr = "SET NOCOUNT ON update Kq_info set " +
						" Kq_late=" + akqinfo.getKq_late() +
						", Kq_ahead=" + akqinfo.getAhead() +
						", Kq_leave=" + akqinfo.getLeave() + 
						", Kq_absent=" + akqinfo.getAbsent();
				
				try {
					this.update(sqlStr);
					freeConn();
					return true;
				} catch (SQLException e) {
					this.msg = "插入数据时出错";
					Log.writeLog(e, "Op_KqInfo.saveInfo()_01 : " + msg);
					freeConn();
					return false;
				}
			} else {
				if (msg.equals(""))
					this.msg = "要修改的记录不存在";
				return false;
			}
		} else {
			return false;
		}
	}
	
	public KqInfo getAKqInfo(String week, String sid) {
		sqlStr = "select * from akq ";
		sqlStr = sqlStr + "where " +
				" akq_weekID = " + week +
				" and akq_stdId ='" + sid + "'";
		
		//System.out.println("Op_KqInfo.getAKqinfo()_01 : sqlStr = " + sqlStr);
		
		try {
			this.rs = this.query(sqlStr);
			
			if(rs.next()) {
				this.akqinfo = new KqInfo();
				this.akqinfo.setKq_Id(rs.getLong("akq_Id"));
				this.akqinfo.setKq_stdId(rs.getString("akq_stdId"));
				this.akqinfo.setKq_year(rs.getString("akq_year"));
				this.akqinfo.setKq_term(rs.getString("akq_term"));
				this.akqinfo.setKq_month(rs.getString("akq_month"));
				this.akqinfo.setKq_weekID(rs.getLong("akq_weekId"));
				this.akqinfo.setKq_week(rs.getString("akq_week"));
				this.akqinfo.setKq_late(rs.getInt("akq_late"));
				this.akqinfo.setAhead(rs.getInt("akq_ahead"));
				this.akqinfo.setAbsent(rs.getInt("akq_absent"));
				this.akqinfo.setLeave(rs.getInt("akq_leave"));
			}
			rs.close();
			freeConn();
			return akqinfo;
		} catch (SQLException e) {
			this.msg = "取得指定学生的考勤信息时出错!";
			freeConn();
			Log.writeLog(e, "Op_KqInfo.getPersonKq()_02 : " + msg);
			return null;
		}
	}
	
	public boolean delKqInfo(String week, String sid) {
		if (isExist(week, sid)) {
			sqlStr = "delete from Kq_info where " +
					" Kq_week=" + week + 
					" and Kq_Std_ID='" + sid + "'";
			//System.out.println("Op_KqInfo.delKqInfo()_01 : sqlStr = " + sqlStr);
			
			try {
				this.update(sqlStr);
				freeConn();
				return true;
			} catch (SQLException e) {
				this.msg = "删除记录时出错";
				freeConn();
				Log.writeLog(e, "Op_kqinfo.delKqInfo() _02 " + msg);
				return false;
			}
		} else {
			if (msg.equals("")) 
				this.msg = "要删除的记录不存在";
			return false;
		}
	}
	
	public double getSum() {
		return this.kqsum;
	}
	
	
}