intergralimp.java

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

package com.jn0801.intergral;

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.common.UserInfo;
import com.jn0801.grademanage.dto.UserBean;
import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;


/**
 * 这是一个积分处理接口的实现类
 * @author zjj
 *
 */
public class IntergralIMP extends HibernateDaoSupport implements IntergralIFC {
	/**
	 * 根据日期查询用户的累计积分
	 * @param maxscore	积分上限
	 * @param minscore	积分下限
	 * @param usergrade	会员等级
	 * @param rulebrand	品牌
	 * @param request	请求
	 * @return	积分表信息的集合
	 */
	public List<UserInfo> addIntergral(int maxscore,int minscore,String usergrade,String rulebrand,HttpServletRequest request) {
		int count = 0;
		PageDAO pageDAO = new PageDAO(request);
		pageDAO.setPagesize(20);
		count = getCount(maxscore,minscore,usergrade,rulebrand);// 获取总记录数
		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<UserInfo> list = new ArrayList<UserInfo>();

		sql = "select a.*,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.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()) {
				UserInfo userInfo = new UserInfo();
				userInfo.setUserid(rs.getLong("userid"));
				userInfo.setPhone(rs.getString("phone"));
				userInfo.setIntegralsign(rs.getString("integralsign"));
				userInfo.setUsername(rs.getString("username"));
				userInfo.setPapertype(rs.getString("papertype"));
				userInfo.setPaperid(rs.getString("paperid"));
				userInfo.setServetype(rs.getString("servetype"));
				userInfo.setCity(rs.getString("city"));
				userInfo.setBusinessroom(rs.getString("businessroom"));
				userInfo.setOptiontype(rs.getString("optiontype"));
				userInfo.setMainoption(rs.getString("mainoption"));
				userInfo.setRegtime(rs.getString("regtime"));
				userInfo.setOldoption(rs.getString("oldoption"));
				userInfo.setIsgroupclient(rs.getLong("isgroupclient"));
				userInfo.setUsergrade(rs.getString("usergrade"));
				userInfo.setIsmember(rs.getLong("ismember"));
				userInfo.setGetmembertime(rs.getString("getmembertime"));
				userInfo.setClientmanagerid(rs.getLong("clientmanagerid"));
				userInfo.setAddintegral(rs.getLong("addintegral"));
				userInfo.setIntegrallosttime(rs.getString("integrallosttime"));
				userInfo.setIslock(rs.getLong("islock"));
				userInfo.setPostaddress(rs.getString("postaddress"));
				userInfo.setTel(rs.getString("tel"));
				userInfo.setPostalcode(rs.getLong("postalcode"));
				userInfo.setBootstrap(rs.getString("bootstrap"));
				list.add(userInfo);
			}

		} 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;
	}

	
	/**
	 * 查询累计积分超过6个月的用户编号
	 * 
	 * @return 用户编号的集合
	 */
	public List getUserid() {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		List list = new ArrayList();
		sql = "select userid from intergralinfo group by userid having count(userid)>=6";
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				list.add(rs.getInt("userid"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * 根据条件查询记录数
	 * @param maxscore	积分上限
	 * @param minscore	积分下限
	 * @param usergrade	会员等级
	 * @return	用户信息的记录数
	 */
	public int getCount(int maxscore,int minscore,String usergrade,String rulebrand) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int count = 0;
		String sql = "";
		sql = "select count(a.userid) 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 + ") b " +
		"on a.userid = b.userid and a.usergrade<>'"+ usergrade +"' and a.servetype='" + rulebrand + "'";
		
		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;
	}
	

}