phoneinfoimg.java

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

package com.jn0801.phonefeeinfo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
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.tools.DBConnection;
import com.jn0801.tools.PageDAO;
/**
 * 用户费用清单实现类
 * @author student
 *
 */
public class PhoneinfoIMG extends HibernateDaoSupport implements PhoneinfoIFC {

	/**
	 * 根据用户ID查询用户费用清单
	 * @param userid
	 * @return 用户费用清单
	 */
	public phoneinfo selectphoneinfo(Long userid , String mdate) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		phoneinfo phonebean = new phoneinfo();
		sql = "select * from phonefeeinfo where userid="+userid+"and mdate='" + mdate + "'";
			con = DBConnection.getConnection();
			try {
				stmt = con.createStatement();
				rs = stmt.executeQuery(sql);
				while(rs.next()){
					phonebean.setPhonefeeinfoid(rs.getLong("phonefeeinfoid"));
					phonebean.setUserid(rs.getLong("userid"));
					phonebean.setMdate(rs.getString("mdate"));
					phonebean.setMoney(rs.getLong("money"));
					phonebean.setPrepay(rs.getLong("prepay"));		
				}
										
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}finally{
				DBConnection.close(rs, null, stmt, con);			
			}

			return phonebean;
		}

	
	

	/**
	 * 根据预存话费和ARPU值查询用户信息
	 * @param maxARPU	ARPU上限
	 * @param minARPU	ARPU下限
	 * @param maxphonefee	预存话费上限
	 * @param minphonefee	预存话费下限
	 * @param usergrade		会员等级
	 * @param rulebrand		品牌
	 * @param request		请求
	 * @return	用户信息的集合
	 */
	public List<UserInfo> searchphoneinfo(int maxARPU,int minARPU,int maxphonefee,int minphonefee,String usergrade,String rulebrand,HttpServletRequest request) {
		int count = 0;
		PageDAO pageDAO = new PageDAO(request);
		pageDAO.setPagesize(20);
		count = getCount(maxARPU,minARPU,maxphonefee,minphonefee,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,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.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;
		
	}
	
	
	
	

	/**
	 * 根据预存话费和ARPU值查询用户记录数
	 * @param maxARPU	ARPU上限
	 * @param minARPU	ARPU下限
	 * @param maxphonefee	预存话费上限
	 * @param minphonefee	预存话费下限
	 * @param usergrade		会员等级
	 * @param rulebrand		品牌
	 * @param request		请求
	 * @return	用户信息的记录数
	 */
	public int getCount(int maxARPU,int minARPU,int maxphonefee,int minphonefee,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,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 + ") 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;
	}
	
	
	

}