clientimpt.java

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

	PageDAO pagedao = new PageDAO(request);
	pagedao.setPagesize(20);
	int pagesize = pagedao.getPagesize();
	
	
	String sql1 = "select count(userid) from userinfo where 1=1 ";
	if(phone!=null && !phone.equals(""))
		sql1 = sql1 + " and phone = '" + phone + "'";
	if(area!=null && !area.equals(""))
		sql1 = sql1 + " and area like '%" + area + "%'";
	if(city!=null)
		sql1 = sql1 + " and city like '%" + city + "%'";
	if(start!=null && !start.equals("")&&end!=null && !end.equals(""))
		sql1 = sql1 + "and userid in (select n from(select a.userid as n,sum(b.money)as m from userinfo a left join phonefeeinfo b on a.userid=b.userid where mdate>='"+start+"'and mdate<='"+end+"'"+ "group by a.userid )where m>1000)";

	conn  = DBConnection.getConnection();
	try {
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql1);
		if(rs.next())
		{
			rscount=rs.getInt(1);
		}
	} catch (SQLException e1) {
		// TODO 自动生成 catch 块
		e1.printStackTrace();
	}
	System.out.println("总记录数"+rscount);
//	 获取总记录数
	pagedao.setRscount(rscount);
	// 获取总页数
	int pagecount = pagedao.getPageCount();
	// 获取当前页
	int currentpage = pagedao.getCurrentPage();

	// 构建工具栏
	String pagetool = pagedao.pagetool(PageDAO.BbsText);

	request.setAttribute("pagetool", pagetool);

	
	
	int prend=0;
	int prstart=0;
	prend=currentpage*pagesize;
	prstart=(currentpage - 1) * pagesize+1;
	String sql = "select * from(select t.* ,rownum as myrow from userinfo t where 1=1 ";
	if(phone!=null && !phone.equals(""))
		sql = sql + " and phone = '" + phone + "'";
	if(area!=null && !area.equals(""))
		sql = sql + " and area like '%" + area + "%'";
	if(city!=null)
		sql = sql + " and city like '%" + city + "%'";
	if(start!=null && !start.equals("")&&end!=null && !end.equals(""))
		sql = sql + "and userid in (select n from(select a.userid as n,sum(b.money)as m from userinfo a left join phonefeeinfo b on a.userid=b.userid where mdate>='"+start+"'and mdate<='"+end+"'"+ "group by a.userid )where m>1000)";
	if(prend!=-1)
	{
		sql = sql + " and rownum<="+prend+" order by t.userid desc"+")";
	}
	if(prstart!=-1)
	{
		sql = sql + " where myrow>="+prstart;
	}

	try {
		conn  = DBConnection.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			UserInfo userinfo = new UserInfo();
			userinfo.setUserid(rs.getLong(1));
			userinfo.setPhone(rs.getString(2));
			userinfo.setIntegralsign(rs.getString(3));
			userinfo.setUsername(rs.getString(4));
			userinfo.setPapertype(rs.getString(5));
			userinfo.setPaperid(rs.getString(6));
			userinfo.setServetype(rs.getString(7));
			userinfo.setArea(rs.getString(8));
			userinfo.setCity(rs.getString(9));
			userinfo.setBusinessroom(rs.getString(10));
			userinfo.setPostaddress(rs.getString(11));
			userinfo.setTel(rs.getString(12));
			userinfo.setPostalcode(rs.getLong(13));
			userinfo.setOptiontype(rs.getString(14));
			userinfo.setMainoption(rs.getString(15));
			userinfo.setRegtime(rs.getString(16));
			userinfo.setOldoption(rs.getString(17));
			userinfo.setIsgroupclient(rs.getLong(18));
			userinfo.setUsergrade(rs.getString(19));
			userinfo.setBootstrap(rs.getString(20)); 
			userinfo.setIsmember(rs.getLong(21));
			userinfo.setGetmembertime(rs.getString(22));
			userinfo.setClientmanagerid(rs.getLong(23));
			userinfo.setAddintegral(rs.getLong(24));
			userinfo.setIntegrallosttime(rs.getString(25));
			userinfo.setIslock(rs.getLong(26));
			list.add(userinfo);
			
		}

	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		try {
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	return list;
}

/**
 * 
 * 添加客户资料
 * 
 * 
 * @param UserInfo
 *            用户资料
 * @return boolean
 */
public boolean adduserinfo(UserInfo userinfo) {
	// TODO 自动生成方法存根
	boolean flag = false;
	try {
		
		this.getHibernateTemplate().save(userinfo);
		flag = true;
	} catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		flag = false;
	}
	System.out.println(flag);
	return flag;
}
/**
 * 
 * 更新客户资料
 * 
 * 
 * @param UserInfo
 *            用户资料
 * @return boolean
 */
public boolean updateuserinfo(UserInfo userinfo) {

	boolean flag = false;
	try {
		this.getHibernateTemplate().update(userinfo);
		flag = true;
	} catch (Exception e) {

		e.printStackTrace();

	}
	System.out.println("!!!!!!!!"+flag);
	return flag;
}


public List<SystemuserBean> getuser(int nid) {
	
	List<SystemuserBean> list = new ArrayList<SystemuserBean>();
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	
	
	String sql = "select * from systemuserinfo where 1=1 ";
	if(nid!=-1)
		sql = sql + " and roleid = " + nid ;

	try {
		conn  = DBConnection.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			SystemuserBean userinfo = new SystemuserBean();
			userinfo.setNid(rs.getInt("nid"));
			userinfo.setPassword(rs.getString("password"));
			userinfo.setUsername(rs.getString("username"));
			userinfo.setRealname(rs.getInt("realname"));
			userinfo.setRoleid(rs.getInt("roleid"));
			list.add(userinfo);
		}

	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		try {
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	return list;
}


/**
 * 
 * 获取系统用户
 * 
 * 
 * @param npid
 *            系统用户编号
 * @return 系统用户的集合
 */
public List<DataDictionary> getdatadictionary(int npid) {
	
	List<DataDictionary> list = new ArrayList<DataDictionary>();
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	
	
	String sql = "select * from datadictionary where 1=1 ";
	if(npid!=-1)
		sql = sql + " and npid = " + npid ;

	try {
		conn  = DBConnection.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			DataDictionary userinfo = new DataDictionary();
			userinfo.setNid(rs.getLong(1));
			userinfo.setSname(rs.getString(2));
			userinfo.setNpid(rs.getLong(3));
			list.add(userinfo);
			
		}

	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		try {
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	return list;
}

/**
 * 
 * 获取服务类型
 * 
 * 
 * @param nid
 *            服务类型编号编号
 * @return 服务类型的集合
 */
public List<DataDictionary> getdatadictionary1(int nid) {
	
	List<DataDictionary> list = new ArrayList<DataDictionary>();
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	
	
	String sql = "select * from datadictionary where 1=1 ";
	if(nid!=-1)
		sql = sql + " and nid = " + nid ;

	try {
		conn  = DBConnection.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			DataDictionary userinfo = new DataDictionary();
			userinfo.setNid(rs.getLong(1));
			userinfo.setSname(rs.getString(2));
			userinfo.setNpid(rs.getLong(3));
			list.add(userinfo);
			
		}

	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		try {
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	return list;
}

/**
 * 
 * 查询我的客户
 * 
 * 
 * @param clientmanagerid
 *            所属客户经理编号
 * @return 客户的集合
 */
public List<UserInfo> listmyuser(int clientmanagerid,HttpServletRequest request) {
	List<UserInfo> list = new ArrayList<UserInfo>();
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	
	int rscount=0;
	
	PageDAO pagedao = new PageDAO(request);
	pagedao.setPagesize(20);
	int pagesize = pagedao.getPagesize();
	
	
	String sql1 = "select count(userid) from userinfo where 1=1 ";
	if(clientmanagerid!=-1)
		sql1 = sql1 + " and clientmanagerid =" + clientmanagerid;
	System.out.println(sql1);
	conn  = DBConnection.getConnection();
	try {
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql1);
		if(rs.next())
		{
			rscount=rs.getInt(1);
		}
	} catch (SQLException e1) {
		// TODO 自动生成 catch 块
		e1.printStackTrace();
	}
	System.out.println("总记录数"+rscount);
//	 获取总记录数
	pagedao.setRscount(rscount);
	// 获取总页数
	int pagecount = pagedao.getPageCount();
	// 获取当前页
	int currentpage = pagedao.getCurrentPage();

	// 构建工具栏
	String pagetool = pagedao.pagetool(PageDAO.BbsText);

	request.setAttribute("pagetool", pagetool);

	
	
	int prend=0;
	int prstart=0;
	prend=currentpage*pagesize;
	prstart=(currentpage - 1) * pagesize+1;
	String sql = "select * from(select t.* ,rownum as myrow from userinfo t where 1=1 ";
	if(clientmanagerid!=-1)
		sql = sql + " and clientmanagerid =" + clientmanagerid;
	if(prend!=-1)
	{
		sql = sql + " and rownum<="+prend+" order by t.userid desc"+")";
	}
	if(prstart!=-1)
	{
		sql = sql + " where myrow>="+prstart;
	}

	System.out.println(sql);
	try {
		conn  = DBConnection.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			UserInfo userinfo = new UserInfo();
			userinfo.setUserid(rs.getLong(1));
			userinfo.setPhone(rs.getString(2));
			userinfo.setIntegralsign(rs.getString(3));
			userinfo.setUsername(rs.getString(4));
			userinfo.setPapertype(rs.getString(5));
			userinfo.setPaperid(rs.getString(6));
			userinfo.setServetype(rs.getString(7));
			userinfo.setArea(rs.getString(8));
			userinfo.setCity(rs.getString(9));
			userinfo.setBusinessroom(rs.getString(10));
			userinfo.setPostaddress(rs.getString(11));
			userinfo.setTel(rs.getString(12));
			userinfo.setPostalcode(rs.getLong(13));
			userinfo.setOptiontype(rs.getString(14));
			userinfo.setMainoption(rs.getString(15));
			userinfo.setRegtime(rs.getString(16));
			userinfo.setOldoption(rs.getString(17));
			userinfo.setIsgroupclient(rs.getLong(18));
			userinfo.setUsergrade(rs.getString(19));
			userinfo.setBootstrap(rs.getString(20)); 
			userinfo.setIsmember(rs.getLong(21));
			userinfo.setGetmembertime(rs.getString(22));
			userinfo.setClientmanagerid(rs.getLong(23));
			userinfo.setAddintegral(rs.getLong(24));
			userinfo.setIntegrallosttime(rs.getString(25));
			userinfo.setIslock(rs.getLong(26));
			list.add(userinfo);
			
		}

	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		try {
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	return list;
}


/**
 * 
 * 查询系统用户
 * 
 * 
 * @param nid
 *            系统用户的编号
 * @return 系统用户的集合
 */
public List<SystemuserBean> getuser1(int nid) {
	
	List<SystemuserBean> list = new ArrayList<SystemuserBean>();
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	
	
	String sql = "select * from systemuserinfo where 1=1 ";
	if(nid!=-1)
		sql = sql + " and nid = " + nid ;

	try {
		conn  = DBConnection.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			SystemuserBean userinfo = new SystemuserBean();
			userinfo.setNid(rs.getInt("nid"));
			userinfo.setPassword(rs.getString("password"));
			userinfo.setUsername(rs.getString("username"));
			userinfo.setRealname(rs.getInt("realname"));
			userinfo.setRoleid(rs.getInt("roleid"));
			list.add(userinfo);
			
		}

	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		try {
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	return list;
}


}