selectpointdboperation.java

java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经

package com.selectpoint;

import java.util.*;
import java.util.Date;
import java.sql.*;

import com.login.DatabaseConn;
import com.login.UserInforBean;

public class SelectPointDBOperation {

	private Connection conn;

	public SelectPointDBOperation() {
		try {
			conn = DatabaseConn.getConnection();
		} catch (Exception e) {

		}

	}

	// 部门考核年 月
	public Collection getDeptPoint(String year, String month, String asses)
			throws Exception {
		Collection ret = new ArrayList();
		String strSql = "";
		PreparedStatement stmt = null;
		if (month == null || "".equals(month)) {
			strSql = " select * from dept_asses where asses_person=? and year(asses_date)=? order by asses_date desc ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, asses);
			stmt.setString(2, year);
		} else {
			strSql = " select * from dept_asses where asses_person=? and year(asses_date)=? and month(asses_date)=? order by asses_date desc ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, asses);
			stmt.setString(2, year);
			stmt.setString(3, month);

		}
		ResultSet rset = stmt.executeQuery();
		//conn.commit();
		while (rset.next()) {
			String daid = rset.getString(1);// id
			String dept_name = rset.getString(2);// 部门名称
			String asses_point = rset.getString(3);// 考核分
			String ss_point = rset.getString(4);// 加减分
			String asses_person = rset.getString(5);// 考核人
			Date asses_date = rset.getDate(6);// 考核日期
			String notes = rset.getString(7);// 备注

			DeptPointBean dept = new DeptPointBean();
			dept.setDaid(daid);
			dept.setDept_name(dept_name);
			dept.setAsses_point(asses_point);
			dept.setSs_point(ss_point);
			dept.setAsses_person(asses_person);
			dept.setAsses_date(asses_date);
			dept.setNotes(notes);

			ret.add(dept);

		}

		rset.close();
		stmt.close();
		return ret;
	}

	// 人员考核年 月
	public Collection getPersonPoint(String year, String month, String asses)
			throws Exception {
		Collection ret = new ArrayList();
		String strSql = "";
		PreparedStatement stmt = null;
		if (month == null || "".equals(month)) {
			strSql = " select * from person_asses where asses_person=? and year(asses_date)=? order by asses_date desc ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, asses);
			stmt.setString(2, year);
		} else {
			strSql = " select * from person_asses where asses_person=? and year(asses_date)=? and month(asses_date)=? order by asses_date desc ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, asses);
			stmt.setString(2, year);
			stmt.setString(3, month);
		}
		ResultSet rset = stmt.executeQuery();
		//conn.commit();
		while (rset.next()) {
			String paid = rset.getString(1);// id
			String person_name = rset.getString(2);// 部门名称
			String ss_point = rset.getString(3);// 加减分
			String asses_person = rset.getString(4);// 考核人
			Date asses_date = rset.getDate(5);// 考核日期
			String notes = rset.getString(6);// 备注

			PersonPointBean person = new PersonPointBean();
			person.setPaid(paid);
			person.setPerson_name(person_name);
			person.setSs_point(ss_point);
			person.setAsses_person(asses_person);
			person.setAsses_date(asses_date);
			person.setNotes(notes);

			ret.add(person);
		}

		rset.close();
		stmt.close();
		return ret;
	}

	// 部门考核全部年 月
	public Collection getDeptPoint(String year, String month) throws Exception {
		Collection ret = new ArrayList();
		String strSql = "";
		PreparedStatement stmt = null;
		if (month == null || "".equals(month)) {
			strSql = " select * from dept_asses where year(asses_date)=? order by dept_name ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, year);
		} else {
			strSql = " select * from dept_asses where year(asses_date)=? and month(asses_date)=? order by dept_name ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, year);
			stmt.setString(2, month);
		}
		ResultSet rset = stmt.executeQuery();
		//conn.commit();
		while (rset.next()) {
			String daid = rset.getString(1);// id
			String dept_name = rset.getString(2);// 部门名称
			String asses_point = rset.getString(3);// 考核分
			String ss_point = rset.getString(4);// 加减分
			String asses_person = rset.getString(5);// 考核人
			Date asses_date = rset.getDate(6);// 考核日期
			String notes = rset.getString(7);// 备注

			DeptPointBean dept = new DeptPointBean();
			dept.setDaid(daid);
			dept.setDept_name(dept_name);
			dept.setAsses_point(asses_point);
			dept.setSs_point(ss_point);
			dept.setAsses_person(asses_person);
			dept.setAsses_date(asses_date);
			dept.setNotes(notes);

			ret.add(dept);

		}

		rset.close();
		stmt.close();
		return ret;
	}

	// 人员考核全部年 月
	public Collection getPersonPoint(String year, String month)
			throws Exception {
		Collection ret = new ArrayList();
		String strSql = "";
		PreparedStatement stmt = null;
		if (month == null || "".equals(month)) {
			strSql = " select * from person_asses where year(asses_date)=? order by asses_date desc ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, year);
		} else {
			strSql = " select * from person_asses where year(asses_date)=? and month(asses_date)=? order by asses_date desc ";
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, year);
			stmt.setString(2, month);
		}
		ResultSet rset = stmt.executeQuery();
		//conn.commit();
		while (rset.next()) {
			String paid = rset.getString(1);// id
			String person_name = rset.getString(2);// 部门名称
			String ss_point = rset.getString(3);// 加减分
			String asses_person = rset.getString(4);// 考核人
			Date asses_date = rset.getDate(5);// 考核日期
			String notes = rset.getString(6);// 备注

			PersonPointBean person = new PersonPointBean();
			person.setPaid(paid);
			person.setPerson_name(person_name);
			person.setSs_point(ss_point);
			person.setAsses_person(asses_person);
			person.setAsses_date(asses_date);
			person.setNotes(notes);

			ret.add(person);
		}

		rset.close();
		stmt.close();
		return ret;
	}
	
	// 部门考核全部年 月
	public Collection getDeptMonthPoint(String year, String month) throws Exception {
		Collection ret = new ArrayList();
		String strSql = "";
		PreparedStatement stmt = null;
		strSql = " select * from dept_asses where year(asses_date)=? and month(asses_date)=? order by dept_name ";
		stmt = conn.prepareStatement(strSql);
		stmt.setString(1, year);
		stmt.setString(2, month);
		ResultSet rset = stmt.executeQuery();
		//conn.commit();
		while (rset.next()) {
			String daid = rset.getString(1);// id
			String dept_name = rset.getString(2);// 部门名称
			String asses_point = rset.getString(3);// 考核分
			String ss_point = rset.getString(4);// 加减分
			String asses_person = rset.getString(5);// 考核人
			Date asses_date = rset.getDate(6);// 考核日期
			String notes = rset.getString(7);// 备注

			DeptPointBean dept = new DeptPointBean();
			dept.setDaid(daid);
			dept.setDept_name(dept_name);
			dept.setAsses_point(asses_point);
			dept.setSs_point(ss_point);
			dept.setAsses_person(asses_person);
			dept.setAsses_date(asses_date);
			dept.setNotes(notes);

			ret.add(dept);

		}

		rset.close();
		stmt.close();
		conn.close();
		return ret;
	}

	
	public ArrayList getPointPhoto(String year, String month) throws Exception {
		ArrayList ret = new ArrayList();
		PreparedStatement stmt = null;
		ResultSet rset = null;
		String strSql = " select count(*) from dept_asses where year(asses_date)=? and month(asses_date)=? and ss_point=? ";
		for(int i=-10;i<=10;i++){
			
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, year);
			stmt.setString(2, month);
			stmt.setString(3, String.valueOf(i));
		    rset = stmt.executeQuery();
			if (rset.next()) {
				String timce = rset.getString(1);// id
				ret.add(timce);
			}
		}
		rset.close();
		stmt.close();
		conn.close();
		return ret;
	}

	public void closeConn() throws Exception {
		conn.close();
	}
}