clubmanageimp.java

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

		Statement stmt = null;
		try {
			conn = DBConnection.getConnection();
			conn.setAutoCommit(false);
			
			stmt=conn.createStatement();
			stmt.execute(sql);
			flag = true;
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO 自动生成 catch 块
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			try {
				conn.setAutoCommit(true);
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		
		return flag;
		
	}
	
	/**
	 * 注销俱乐部
	 * 返回 true 和false
	 * 
	 */
	public boolean islock(int cardid){
		
		boolean  flag= false;
		Connection conn= null;
		Statement stmt = null;
		StringBuffer sql = new StringBuffer();
		sql.append("update membercardinfo set ");
		sql.append("islock = 1 where cardid = "+cardid);
 
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			
			if(stmt.executeUpdate(sql.toString())>0){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		} 
		return flag;
	}
	/**
	 * 添加俱乐部会员
	 * 
	 */
	public boolean insertMembercar(MembercardBean bean){
		boolean flag= false;
		
		StringBuffer sql = new StringBuffer();
		if(bean != null)
		{
			sql.append("insert into membercardinfo(");
			sql.append("cardid,islock,mdate,memberphone,servetype,usergrade,userid)values(" );
			sql.append("seq_membercardinfo.Nextval,");
			sql.append(0+",");
			
			sql.append("'"+bean.getMdate()+"',");
			
			sql.append("'"+bean.getMemberphone()+"',");
			
			sql.append("'"+bean.getServetype()+"',");
			
			sql.append("'"+bean.getUsergrade()+"',");
			
			sql.append(bean.getUserid()+")"); 
			 
			
		}
		
		Connection conn =null;
		Statement stmt = null;
		try {
			conn= DBConnection.getConnection();
			stmt = conn.createStatement();
			if(stmt.executeUpdate(sql.toString())>0){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		
		return flag;
	}
	/**
	 * 修改俱乐部会员信息
	 */
	
	public boolean updateMembercar(MembercardBean bean){
		
		boolean  flag= false;
		Connection conn= null;
		Statement stmt = null;
		StringBuffer sql = new StringBuffer();
		sql.append("update membercardinfo set ");

		sql.append("islock = "+bean.getIslock()+",");

		sql.append("mdate = '"+bean.getMdate()+"',");
		
		sql.append("userid = "+bean.getUserid()+",");
		sql.append("memberphone = '"+bean.getMemberphone()+"',");
		sql.append("servetype = '"+bean.getServetype()+"',");
		sql.append("usergrade = '"+bean.getUsergrade()+"'");
		sql.append(" where cardid="+bean.getCardid());
		 
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			
			if(stmt.executeUpdate(sql.toString())>0){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}

		return flag;
	}
	/**
	 * 
	 * 查询俱乐部会员信息
	 * 
	 * 这是根据条件查询
	 */
	public MembercardBean serchmembercard(int userid,int cardid,String phone){
		MembercardBean bean = null;
		
		String sql="select * from membercardinfo where 1 = 1";
		
		if(userid != -1){
			sql = sql+" and userid=" + userid;
		}
		if(cardid != -1)
		{
			sql = sql + " and cardid=" + cardid;
		}
		if(phone != null && !phone.equals(""))
		{
			sql = sql + " and memberphone='" + phone + "'";
		}
//		System.out.println(sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
	
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new MembercardBean();
				bean.setCardid(rs.getInt("cardid"));
				bean.setIslock(rs.getInt("islock"));
				bean.setMdate(rs.getString("mdate"));
				bean.setMemberphone(rs.getString("memberphone"));
				bean.setUserid(rs.getInt("userid")); 
				bean.setServetype(rs.getString("servetype"));
				bean.setUsergrade(rs.getString("usergrade"));
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} finally{
			
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		} 
		return bean; 
	}
	
	/**
	 * 查询会员信息返回list
	 */
	
	public List<MembercardBean> listmembercard(String clubname,HttpServletRequest request) {

		List<MembercardBean> list = new ArrayList<MembercardBean>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		
		//开始分页
		PageDAO pagedao = new PageDAO(request);
		pagedao.setPagesize(8);
		// 获取每页多少条
		int pagesize = pagedao.getPagesize();
		//获取总记录数
		sql = "select count(1) from membercardinfo where 1=1  and islock=0 ";
		if (clubname != null && !clubname.trim().equals(""))
			sql = sql + " and memberphone like '%" + clubname + "%'";
		
		//总记录数
		int rscount = 0;

		try {
			
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				rscount = rs.getInt(1);
			}
			
			//设置总记录数
			pagedao.setRscount(rscount);
			//获取总页数
			int pagecount = pagedao.getPageCount();
			// 获取当前页
			int currentpage = pagedao.getCurrentPage();
			//构建工具栏
			String pagetool = pagedao.pagetool(PageDAO.BbsText);
			request.setAttribute("pagetool", pagetool);
			//计算起始行和结束行
			int pastart = (currentpage-1)*pagesize+1; 
			int paend = currentpage*pagesize;
			
			sql = "select * from ("; 
			sql = sql + "select s.*,rownum as myrow from membercardinfo s where 1=1 and islock=0 ";
			
			if (clubname != null && !clubname.trim().equals(""))
				sql = sql + " and memberphone like '%" + clubname + "%'";
			
			sql = sql + " and rownum <= " + paend;
			sql = sql + " order by cardid desc )";
			sql = sql + " where myrow >= " + pastart;
			//+" and islock =0 "
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {

				MembercardBean bean = new MembercardBean();
				bean.setCardid(rs.getInt("cardid"));
				bean.setIslock(rs.getInt("islock"));
				bean.setMdate(rs.getString("mdate"));
				bean.setMemberphone(rs.getString("memberphone"));
				bean.setUserid(rs.getInt("userid"));  
				bean.setServetype(rs.getString("servetype"));
				bean.setUsergrade(rs.getString("usergrade"));
				
				list.add(bean);
			}
		} 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 phone
	 *            用户手机号码
	 * @return 是否存在
	 */
	public boolean isphone(String phone) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		int count = 0;
		sql = "select count(userid) from membercardinfo where memberphone='" + 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;
		}
	}
}