managerassigndaoimpl.java

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

package com.jn0801.managerassign;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.jn0801.common.UserInfo;
import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;

public class ManagerAssignDaoImpl extends HibernateDaoSupport implements
		ManagerAssignDao {

	/**
	 * 根据条件返回大客户列表
	 * 
	 * @param phone
	 *            号码
	 * @param mainoption
	 *            主套餐
	 * @param optiontype
	 *            套餐类型
	 * @param integralStart
	 *            起始积分
	 * @param integralEnd
	 *            结束积分
	 * @param feeStart
	 *            起始话费
	 * @param feeEnd
	 *            结束话费
	 * @param request
	 * @return
	 */
	public List listUserInfo(String phone, String mainoption,
			String optiontype, int integralStart, int integralEnd,
			int feeStart, int feeEnd, int clientmanagerid,
			HttpServletRequest request) {

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		List<Map<String, Object>> userInfoList = new ArrayList<Map<String, Object>>();
		String sql = "";

		try {
			connection = DBConnection.getConnection();
			statement = connection.createStatement();
			PageDAO pageDAO = new PageDAO(request);
			sql = "select count(1) as rscount "
					+ "from "
					+ "(select u.userid,to_char(avg(p.money),999999.99) fee "
					+ "from userinfo u,phonefeeinfo p "
					+ "where u.userid=p.userid and mdate>to_char(add_months(sysdate,-4),'yyyy-mm') and u.ismember=1 "
					+ "group by u.userid) t,userinfo u "
					+ "where 1=1 and t.userid=u.userid";

			if (!"".equals(phone) && phone != null) {
				sql = sql + " and u.phone like '%" + phone + "%'";
			}
			if (!"".equals(mainoption) && mainoption != null) {
				sql = sql + " and u.mainoption='" + mainoption + "'";
			}
			if (!"".equals(optiontype) && optiontype != null) {
				sql = sql + " and u.optiontype='" + optiontype + "'";
			}
			if (integralStart != -1) {
				sql = sql + " and u.addintegral>=" + integralStart;
			}
			if (integralEnd != -1) {
				sql = sql + " and u.addintegral<=" + integralEnd;
			}
			if (feeStart != -1) {
				sql = sql + " and t.fee>=" + feeStart;
			}
			if (feeEnd != -1) {
				sql = sql + " and t.fee<=" + feeEnd;
			}
			if (clientmanagerid != -1) {
				sql = sql + " and u.clientmanagerid=" + clientmanagerid;
			}
			int rscount = pageDAO.getRsCountForSQL(sql);
			int pagesize = 10;
			pageDAO.setPagesize(pagesize);
			pageDAO.setRscount(rscount);
			pageDAO.getPageCount();
			int currentpage = pageDAO.getCurrentPage();
			String pagetool = pageDAO.pagetool(PageDAO.BbsText);
			request.setAttribute("pagetool", pagetool);

			sql = "select * from(select u.userid,u.phone,u.username,u.city,u.area,u.mainoption,u.optiontype,t.fee,u.clientmanagerid,u.addintegral,rownum rn "
					+ "from "
					+ "(select u.userid,to_char(avg(p.money),999999.99) fee "
					+ "from userinfo u,phonefeeinfo p "
					+ "where u.userid=p.userid and mdate>to_char(add_months(sysdate,-4),'yyyy-mm') and u.ismember=1 "
					+ "group by u.userid) t,userinfo u "
					+ "where 1=1 and t.userid=u.userid";

			if (!"".equals(phone) && phone != null) {
				sql = sql + " and u.phone like '%" + phone + "%'";
			}
			if (!"".equals(mainoption) && mainoption != null) {
				sql = sql + " and u.mainoption='" + mainoption + "'";
			}
			if (!"".equals(optiontype) && optiontype != null) {
				sql = sql + " and u.optiontype='" + optiontype + "'";
			}
			if (integralStart != -1) {
				sql = sql + " and u.addintegral>=" + integralStart;
			}
			if (integralEnd != -1) {
				sql = sql + " and u.addintegral<=" + integralEnd;
			}
			if (feeStart != -1) {
				sql = sql + " and t.fee>=" + feeStart;
			}
			if (feeEnd != -1) {
				sql = sql + " and t.fee<=" + feeEnd;
			}
			if (clientmanagerid != -1) {
				sql = sql + " and u.clientmanagerid=" + clientmanagerid;
			}
			sql = sql + " and rownum <=" + currentpage * pagesize + ") t ";
			sql = sql + " where t.rn >=" + ((currentpage - 1) * pagesize + 1);

			System.out.println(sql);

			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("userid", resultSet.getObject(1));
				map.put("phone", resultSet.getObject(2));
				map.put("username", resultSet.getObject(3));
				map.put("city", resultSet.getObject(4));
				map.put("area", resultSet.getObject(5));
				map.put("mainoption", resultSet.getObject(6));
				map.put("optiontype", resultSet.getObject(7));
				map.put("fee", resultSet.getObject(8));
				map.put("clientmanagerid", resultSet.getObject(9));
				map.put("addintegral", resultSet.getObject(10));
				userInfoList.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, null, statement, connection);
		}
		return userInfoList;
	}

	/**
	 * 返回客户经理信息
	 * 
	 * @param request
	 * @return
	 */
	public List listSystemUserInfo(HttpServletRequest request) {

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		List<Map<String, Object>> systemUserInfoList = new ArrayList<Map<String, Object>>();
		String sql = "";

		try {
			connection = DBConnection.getConnection();
			statement = connection.createStatement();
			PageDAO pageDAO = new PageDAO(request);

			sql = "select count(1) from systemuserinfo";
			int rscount = pageDAO.getRsCountForSQL(sql);
			pageDAO.setRscount(rscount);
			int pagesize = 10;
			pageDAO.setPagesize(pagesize);
			int currentpage = pageDAO.getCurrentpage();
			pageDAO.getPageCount();

			sql = "select * from (select nid,username,rownum rn from systemuserinfo where rownum<="
					+ currentpage
					* pagesize
					+ ") t"
					+ " where t.rn>="
					+ ((currentpage - 1) * pagesize + 1);

			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("nid", resultSet.getObject(1));
				map.put("username", resultSet.getObject(2));
				systemUserInfoList.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, null, statement, connection);
		}
		return systemUserInfoList;
	}

	/**
	 * 返回业务类型信息
	 * 
	 * @return
	 */
	public List listOperationType() {
		List operationTypeList = new ArrayList();
		String hql = "select operationType from operationtype operationType where operationType.npid not in(0)";
		operationTypeList = this.getHibernateTemplate().find(hql);
		return operationTypeList;
	}

	/**
	 * 对在线用户分配客户经理
	 * 
	 * @param string
	 * @param managerid
	 */
	public void assignManager(long userid, long managerid) {
		UserInfo userInfo = (UserInfo) this.getHibernateTemplate().get(
				UserInfo.class, userid);
		userInfo.setClientmanagerid(managerid);
		this.getHibernateTemplate().update(userInfo);
	}

}