gradedealimp.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.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.jn0801.clubnetinfo.ClubManageImp;
import com.jn0801.clubnetinfo.ClubManageInf;
import com.jn0801.clubnetinfo.MembercardBean;
import com.jn0801.common.UserInfo;
import com.jn0801.grademanage.dto.UserBean;
import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;

/**
 * 这是用户分级处理接口的实现类
 * 
 * @author zjj
 * 
 */
public class GradeDealIMP extends HibernateDaoSupport implements GradeDealIFC {
   
	/**
	 * 修改用户等级
	 * 
	 * @param userInfo
	 *            用户信息表
	 * @return 修改是否成功
	 */
	public boolean update(UserInfo userInfo) {
		boolean flog = false;
		try {
			this.getHibernateTemplate().update(userInfo);
			flog = true;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return flog;
	}

	/**
	 * 查询在线用户信息
	 * 
	 * @param userBean
	 *            用户信息的javabean
	 * @return 用户信息列表
	 */
	public List<UserBean> searchuser(UserBean userBean,
			HttpServletRequest request) {
		int count = 0;
		PageDAO pageDAO = new PageDAO(request);
		pageDAO.setPagesize(20);
		count = getCount(userBean);// 获取总记录数
		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;
		String sql = "";
		List<UserBean> list = new ArrayList<UserBean>();
		list.clear();
		sql = "select a.*,rownum as myrow from userinfo a where rownum<=" + maxResult;
		if (userBean.getUserid() != -1) {
			sql = sql + " and userid=" + userBean.getUserid();
		}
		if (userBean.getPhone() != null && !userBean.getPhone().equals("")) {
			sql = sql + " and phone like '%" + userBean.getPhone() + "%'";
		}
		if (userBean.getServetype() != null
				&& !userBean.getServetype().equals("")) {
			sql = sql + " and servetype='" + userBean.getServetype() + "'";
		}
		if (userBean.getUsergrade() != null
				&& !userBean.getUsergrade().equals("")) {
			sql = sql + " and usergrade='" + userBean.getUsergrade() + "'";
		}
		sql = sql + " order by userid asc";
		try {
			con = DBConnection.getConnection();
			String str = "{?= call package_test.page_info(?,?,?)}";
			cstmt = con.prepareCall(str);

			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setString(2, sql);
			cstmt.setInt(3, currentPage);
			cstmt.setInt(4, pageSize);
			cstmt.execute();
			rs = (ResultSet) cstmt.getObject(1);
			while (rs.next()) {
				UserBean bean = new UserBean();
				bean.setUserid(rs.getLong("userid"));
				bean.setPhone(rs.getString("phone"));
				bean.setIntegralsign(rs.getString("integralsign"));
				bean.setUsername(rs.getString("username"));
				bean.setPapertype(rs.getString("papertype"));
				bean.setPaperid(rs.getString("paperid"));
				bean.setServetype(rs.getString("servetype"));
				bean.setArea(rs.getString("area"));
				bean.setCity(rs.getString("city"));
				bean.setBusinessroom(rs.getString("businessroom"));
				bean.setOptiontype(rs.getString("optiontype"));
				bean.setMainoption(rs.getString("mainoption"));
				bean.setRegtime(rs.getString("regtime"));
				bean.setOldoption(rs.getString("oldoption"));
				bean.setIsgroupclient(rs.getLong("isgroupclient"));
				bean.setUsergrade(rs.getString("usergrade"));
				bean.setIsmember(rs.getLong("ismember"));
				bean.setGetmembertime(rs.getString("getmembertime"));
				bean.setClientmanagerid(rs.getLong("clientmanagerid"));
				bean.setAddintegral(rs.getLong("addintegral"));
				bean.setIntegrallosttime(rs.getString("integrallosttime"));
				bean.setIslock(rs.getLong("islock"));
				bean.setPostaddress(rs.getString("postaddress"));
				bean.setTel(rs.getString("tel"));
				bean.setPostalcode(rs.getLong("postalcode"));
				bean.setBootstrap(rs.getString("bootstrap"));
				list.add(bean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (cstmt != null) {
					cstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 根据条件查询用户表的总记录数
	 * 
	 * @param userBean
	 *            用户信息的javabean
	 * @return 总记录数
	 */
	public int getCount(UserBean userBean) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int count = 0;
		String sql = "";
		sql = "select count(userid) from userinfo where 1=1";
		if (userBean.getPhone() != null && !userBean.getPhone().equals("")) {
			sql = sql + " and phone like '%" + userBean.getPhone() + "%'";
		}
		if (userBean.getServetype() != null
				&& !userBean.getServetype().equals("")) {
			sql = sql + " and servetype='" + userBean.getServetype() + "'";
		}
		if (userBean.getUsergrade() != null
				&& !userBean.getUsergrade().equals("")) {
			sql = sql + " and usergrade='" + userBean.getUsergrade() + "'";
		}
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			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 boolean isExist(String phone) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		int count = 0;
		sql = "select count(userid) from userinfo where phone='" + phone + "'";
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}

		if (count == 0) {
			return false;
		} else {
			return true;
		}

	}

	/**
	 * 查询用户详细信息
	 * 
	 * @param phone
	 *            用户手机号码
	 * @param userid
	 *            用户编号
	 * @return 用户详细信息
	 */
	public UserInfo selectuser(String phone, Long userid, String usergrade) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		UserInfo bean = new UserInfo();
		sql = "select * from userinfo where 1=1";
		if (phone != null && !phone.equals("")) {
			sql = sql + " and phone='" + phone + "'";
		}
		if (userid != -1) {
			sql = sql + " and userid=" + userid;
		}
		if (usergrade != null && !usergrade.equals("")) {
			sql = sql + " and usergrade<>'" + usergrade  + "'";
		}
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				bean.setUserid(rs.getLong("userid"));
				bean.setPhone(rs.getString("phone"));
				bean.setBootstrap(rs.getString("bootstrap"));
				bean.setIntegralsign(rs.getString("integralsign"));
				bean.setUsername(rs.getString("username"));
				bean.setPapertype(rs.getString("papertype"));
				bean.setPaperid(rs.getString("paperid"));
				bean.setServetype(rs.getString("servetype"));
				bean.setArea(rs.getString("area"));
				bean.setCity(rs.getString("city"));
				bean.setBusinessroom(rs.getString("businessroom"));
				bean.setOptiontype(rs.getString("optiontype"));
				bean.setMainoption(rs.getString("mainoption"));
				bean.setRegtime(rs.getString("regtime"));
				bean.setOldoption(rs.getString("oldoption"));
				bean.setIsgroupclient(rs.getLong("isgroupclient"));				
				bean.setPostaddress(rs.getString("postaddress"));
				bean.setTel(rs.getString("tel"));
				bean.setPostalcode(rs.getLong("postalcode"));
				bean.setUsergrade(rs.getString("usergrade"));
				bean.setIsmember(rs.getLong("ismember"));
				bean.setGetmembertime(rs.getString("getmembertime"));
				bean.setClientmanagerid(rs.getLong("clientmanagerid"));
				bean.setAddintegral(rs.getLong("addintegral"));
				bean.setIntegrallosttime(rs.getString("integrallosttime"));
				bean.setIslock(rs.getLong("islock"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}

		return bean;
	}

	/**
	 * 带事务修改用户等级和插入分级结果
	 * @param userInfo	用户信息表的持久类
	 * @param graderesultinfo	分级结果表的持久类
	 * @return	是否成功
	 */
	public boolean updateandinsert(UserInfo userInfo, Graderesultinfo graderesultinfo,MembercardBean membercardBean) {
		boolean flog = false;
		try {
			this.getHibernateTemplate().update(userInfo);
			this.getHibernateTemplate().save(graderesultinfo);
			ClubManageInf clubManageImp = new ClubManageImp();
			clubManageImp.updateMembercar(membercardBean);
			flog = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flog;
	}
	
	

	/**
	 * 更新会员等级
	 * @param userInfo	用户信息的实体Bean
	 * @param memberCardBean	会员卡的实体Bean
	 * @return	更新是否成功
	 */
	public boolean updatemember(UserInfo userInfo,MembercardBean memberCardBean)
	{
		boolean flog = false;
		try {
			this.getHibernateTemplate().update(userInfo);
			ClubManageInf clubManageImp = new ClubManageImp();
			clubManageImp.updateMembercar(memberCardBean);
			flog = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return flog;
	}
	
	
	
	/**
	 * 根据积分动态分级用户等级
	 * @param maxscore	积分上限
	 * @param minscore	积分下限
	 * @param usergrade	用户等级
	 * @param rulebrand	用户品牌
	 * @param currentPage	当前页
	 * @param pageSize	每页显示条数
	 */
	public void dynamicGradeDealByIntergral(int maxscore,int minscore,String usergrade,String rulebrand,int currentPage,int pageSize)
	{
		Connection con = null;
		CallableStatement cstmt = null;
		String sql = "";
		int maxResult = currentPage*pageSize;
		sql = "select a.userid,rownum as myrow from userinfo a inner join " +
		"(select userid from intergralinfo where userid in " +
		"(select userid from intergralinfo group by userid having count(userid)>=6) " +
		"and recordtime>=to_char(add_months(sysdate,-6),'yyyy-MM') and recordtime<=to_char(add_months(sysdate,-1),'yyyy-MM') " +
		"group by userid having min(baseintegral-consumeintergral+onlineintergral+honorintergral+adjustintergral)>=" + minscore +
		" and min(baseintegral-consumeintergral+onlineintergral+honorintergral+adjustintergral)<" + maxscore + " order by userid) b " +
		"on a.userid = b.userid and a.usergrade<>'"+ usergrade +"' and a.servetype='" + rulebrand + "' and rownum<=" + maxResult;
		try {
			con = DBConnection.getConnection();
			String str = "{call package_test.gradedeal(?,?,?,?)}";
			cstmt = con.prepareCall(str);
			
			cstmt.setString(1, sql);
			cstmt.setInt(2, currentPage);
			cstmt.setInt(3, pageSize);
			cstmt.setString(4, usergrade);
			cstmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if (cstmt != null) {
					cstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	
	
	/**
	 * 根据话费动态分级用户等级
	 * @param maxARPU	每月消费上限
	 * @param minARPU	每月消费下限
	 * @param maxphonefee	预存话费上限
	 * @param minphonefee	预存话费下限
	 * @param usergrade		用户等级
	 * @param rulebrand		用户品牌
	 * @param currentPage	当前页
	 * @param pageSize		每页显示条数
	 */
	public void dynamicGradeDealByFee(int maxARPU,int minARPU,int maxphonefee,int minphonefee,String usergrade,String rulebrand,int currentPage,int pageSize)
	{
		Connection con = null;
		CallableStatement cstmt = null;
		String sql = "";
		int maxResult = currentPage*pageSize;
		sql = "select a.userid,rownum as myrow from userinfo a inner join (select userid,avg(money)" +
		" from phonefeeinfo where userid in (select userid from " +
		"(select userid,mdate,money,prepay from phonefeeinfo where userid in " +
		"(select userid from phonefeeinfo group by userid having count(mdate)>=6) " +
		"and mdate>=to_char(add_months(sysdate,-6),'yyyy-MM') " +
		"and mdate<=to_char(add_months(sysdate,-1),'yyyy-MM')) " +
		"where prepay>=" + minphonefee + "and prepay<" + maxphonefee + ") group by userid " +
		"having avg(money)>=" + minARPU + " and avg(money)<" + maxARPU + " order by userid) b on a.userid = b.userid and a.usergrade<> '"
		+ usergrade + "' and servetype='" + rulebrand + "' and rownum <=" + maxResult;
		
		try {
			con = DBConnection.getConnection();
			String str = "{call package_test.gradedeal(?,?,?,?)}";
			cstmt = con.prepareCall(str);
			
			cstmt.setString(1, sql);
			cstmt.setInt(2, currentPage);
			cstmt.setInt(3, pageSize);
			cstmt.setString(4, usergrade);
			cstmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if (cstmt != null) {
					cstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}



	/**
	 * 更新普通用户等级
	 * @param userInfo	用户信息的实体Bean
	 * @param graderesultinfo	用户分级结果的实体Bean
	 * @param membercardBean	会员卡的实体Bean
	 * @return
	 */
	public boolean updatecomman(UserInfo userInfo, Graderesultinfo graderesultinfo, MembercardBean membercardBean) {
		
		boolean flog = false;
		try {
			this.getHibernateTemplate().update(userInfo);
			this.getHibernateTemplate().save(graderesultinfo);
			ClubManageInf clubManageImp = new ClubManageImp();
			clubManageImp.insertMembercar(membercardBean);
			flog = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flog;
	}

}