graderesultimp.java

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

package com.jn0801.grademanage;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;


import oracle.jdbc.OracleTypes;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.jn0801.grademanage.dto.GradeResultBean;
import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;

/**
 * 这是用户分级结果接口的实现类
 * 
 * @author zjj
 * 
 */
public class GradeResultIMP extends HibernateDaoSupport implements
		GradeResultIFC {

	/**
	 * 保存用户分级结果
	 * 
	 * @param graderesultinfo
	 *            用户分级结果持久类
	 * @return 保存是否成功
	 */
	public boolean save(Graderesultinfo graderesultinfo) {
		boolean flog = false;
		try {
			this.getHibernateTemplate().save(graderesultinfo);
			flog = true;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return flog;
	}

	/**
	 * 查询用户分级结果
	 * 
	 * @param gradeResultBean
	 *            用户分级结果的javabean
	 * @return 用户分级结果的列表
	 */
	public List<GradeResultBean> search(GradeResultBean gradeResultBean) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		List<GradeResultBean> list = new ArrayList<GradeResultBean>();
		sql = "select * from userinfo where 1=1";
		if (gradeResultBean.getNid() != -1) {
			sql = " and nid =" + gradeResultBean.getNid();
		}
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				GradeResultBean bean = new GradeResultBean();
				bean.setNid(rs.getInt("nid"));
				bean.setUserphone(rs.getString("userphone"));
				bean.setOldsign(rs.getString("oldsign"));
				bean.setOldgrade(rs.getString("oldgrade"));
				bean.setNewsign(rs.getString("newsign"));
				bean.setNewgrade(rs.getString("newgrade"));
				bean.setOptionuser(rs.getString("optionuser"));
				bean.setOptiondate(rs.getString("optiondate"));
				list.add(bean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}

		return list;
	}

	public List<GradeResultBean> searchLog(String phone, String optionuser,
			String startdate, String enddate, HttpServletRequest request) {

		int count = 0;
		PageDAO pageDAO = new PageDAO(request);
		pageDAO.setPagesize(20);
		count = getCount(phone, optionuser, startdate, enddate);// 获取总记录数
		pageDAO.setRscount(count);
		int currentPage = pageDAO.getCurrentPage();
		int pagecount = pageDAO.getPageCount();// 获取一共有多少页
		int pageSize = pageDAO.getPagesize();// 获取每页显示多少条记录
		int maxResult = currentPage * pageSize;// 获取结束行
		String pageTool = pageDAO.pagetool(PageDAO.BbsText);// 构建工具条
		request.setAttribute("pageTool", pageTool);

		Connection con = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;
		List<GradeResultBean> list = new ArrayList<GradeResultBean>();
		StringBuffer sql = new StringBuffer();
		sql
				.append("select a.*,rownum as myrow from graderesultinfo a where 1=1");
		if (phone != null && !phone.equals("")) {
			sql.append(" and userphone like '%" + phone + "%'");
		}
		if (optionuser != null && !optionuser.equals("")) {
			sql.append(" and optionuser like '%" + optionuser + "%'");
		}
		if (startdate != null && !startdate.equals("")) {
			sql.append(" and optiondate >='" + startdate + "'");
		}
		if (enddate != null && !enddate.equals("")) {
			sql.append(" and optiondate <='" + enddate + "'");
		}
		sql.append(" and rownum <=" + maxResult);

		try {
			con = DBConnection.getConnection();
			String str = "{?= call package_test.page_info(?,?,?)}";
			cstmt = con.prepareCall(str);
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setString(2, sql.toString());
			cstmt.setInt(3, currentPage);
			cstmt.setInt(4, pageSize);
			cstmt.execute();
			rs = (ResultSet) cstmt.getObject(1);
			while (rs.next()) {
				GradeResultBean bean = new GradeResultBean();
				bean.setNid(rs.getInt("nid"));
				bean.setUserphone(rs.getString("userphone"));
				bean.setOldsign(rs.getString("oldsign"));
				bean.setOldgrade(rs.getString("oldgrade"));
				bean.setNewsign(rs.getString("newsign"));
				bean.setNewgrade(rs.getString("newgrade"));
				bean.setOptionuser(rs.getString("optionuser"));
				bean.setOptiondate(rs.getString("optiondate"));
				list.add(bean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	private int getCount(String phone, String optionuser, String startdate,
			String enddate) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int count = 0;
		StringBuffer sql = new StringBuffer();

		sql.append("select count(nid) from graderesultinfo where 1=1");
		if (phone != null && !phone.equals("")) {
			sql.append(" and userphone like '%" + phone + "%'");
		}
		if (optionuser != null && !optionuser.equals("")) {
			sql.append(" and optionuser like '%" + optionuser + "%'");
		}
		if (startdate != null && !startdate.equals("")) {
			sql.append(" and optiondate >='" + startdate + "'");
		}
		if (enddate != null && !enddate.equals("")) {
			sql.append(" and optiondate <='" + enddate + "'");
		}
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}
		return count;
	}

	/**
	 * 通过用户编号查询用户分级结果信息
	 * 
	 * @param phone
	 *            用户手机号码
	 * @return 用户分级结果的持久类
	 */
	public Graderesultinfo selectLog(String phone) {

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		Graderesultinfo graderesultinfo = new Graderesultinfo();
		sql = "select a.* from graderesultinfo a inner join (select max(nid) as nid from graderesultinfo where userphone='" + phone + "')";
        sql = sql + " b on a.nid = b.nid";
		try {
			QueryRunner queryRunner = new QueryRunner();
			con = DBConnection.getConnection();
			graderesultinfo = (Graderesultinfo)queryRunner.query(con, sql, new BeanHandler(Graderesultinfo.class));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbUtils.closeQuietly(con);
			
		}
		return graderesultinfo;
	}
}