companyimp.java

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

					+ "'");
		}

		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}
		return count;
	}

	/**
	 * 查找特定条件的记录数(生成编号的特殊查询)
	 * 
	 * @param companyBean
	 * @return
	 */
	public int getCountforcallingtype(CompanyBean companyBean) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int count = 0;
		StringBuffer sql = new StringBuffer();

		sql.append("select count(*) from companytable where 1=1");

		if (companyBean.getCompanystate() != null
				&& !companyBean.getCompanystate().equals("录入")) {
			sql.append(" and companystate in ('潜在','正式')");
		}
		if (companyBean.getServicemanager() != null
				&& !companyBean.getServicemanager().equals("")) {

			sql
					.append(" and servicemanager="
							+ companyBean.getServicemanager());
		}
		if (companyBean.getCallingtype() != null
				&& !companyBean.getCallingtype().equals("")) {
			sql.append(" and callingtype=" + companyBean.getCallingtype());
		}

		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}
		return count;
	}

	/**
	 * 检查是否有名字重复
	 * 
	 * @param name
	 * @return
	 */

	public List listsearchbyname(final String name) {

		final String hql = "Select a From CompanyBean a where companyname=:name";

		List list = this.getHibernateTemplate().executeFind(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {

						List mylist =null;

						Query query = session.createQuery(hql);
						query.setString("name", name);
						
						mylist = query.list();
						
						return mylist;
					}
				});
		return list;
	}

	/**
	 * 获取客户经理的集团客户信息列表
	 * 
	 * @param request
	 * @return
	 */
	public List getcmadetail() {
		List<Map> list = new ArrayList<Map>();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = sql + "Select cc.loginname,";
		sql = sql + "  sum(case when 状态='正式' then 个数 else 0 end) as i,";
		sql = sql + "   sum(case when 状态='潜在' then 个数 else 0 end) as j";

		sql = sql
				+ " from (select a.loginname, a.username,count(b.companyid) as 个数,decode(b.companystate,'正式',b.companystate,'潜在')";
		sql = sql + " as 状态 from systemuserinfo a,companytable b where ";
		sql = sql
				+ "a.loginname = b.servicemanager(+) and a.loginname>=10001 and a.loginname<=20000";
		sql = sql
				+ "group by a.loginname,a.username,decode(b.companystate,'正式',b.companystate,'潜在'))";
		sql = sql
				+ " cc Group By cc.loginname,cc.username order by cc.username desc";

		System.out.println(sql);
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("loginname", rs.getInt("loginname"));
				map.put("i", rs.getString("i"));
				map.put("j", rs.getString("j"));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}

		return list;
	}

	/**
	 * 获取未分配的集团用户数
	 * 
	 * @return
	 */
	public Object getNoCount() {
		String hql = "Select count(company.nid) From CompanyBean company where company.servicemanager = '0'";
		List list = null;
		list = this.getHibernateTemplate().find(hql);
		Object size = list.get(0);

		return size;
	}

	/**
	 * 查找最大ID的记录
	 * 
	 * @return
	 */
	public List<CompanyBean> listSearchMaxID(CompanyBean companyBean) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List<CompanyBean> list = new ArrayList<CompanyBean>();

		String sql = "select a.* from companytable a inner join ";
		sql = sql + "(select max(nid) as maxid from companytable";
		if (companyBean.getCallingtype() != null
				&& !companyBean.getCallingtype().equals("")) {
			sql = sql + " where callingtype=" + companyBean.getCallingtype();
		}
		sql = sql + " order by nid) b on ";
		sql = sql + "a.nid=b.maxid";

		try {
			conn = DBConnection.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				CompanyBean company = new CompanyBean();
				company.setCompanyid(rs.getString("companyid"));
				company.setCompanyname(rs.getString("companyname"));
				company.setCallingtype(rs.getString("callingtype"));
				company.setCompanylevel(rs.getString("companylevel"));
				company.setAddress(rs.getString("address"));
				company.setPhone(rs.getString("phone"));
				company.setDeputy(rs.getString("deputy"));
				company.setCompanystate(rs.getString("companystate"));
				company.setIslogout(rs.getString("islogout"));
				company.setUltimouser(rs.getString("ultimouser"));
				company.setUltimoearning(rs.getString("ultimoearning"));
				company.setServicemanager(rs.getString("servicemanager"));
				company.setTurntime(rs.getString("turntime"));
				list.add(company);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, st, conn);
		}

		return list;
	}

	/**
	 * 分配集团客户
	 * 
	 * @param servicemanager
	 * @param nid
	 * @return
	 */
	public boolean updatecompany(String servicemanager, Long nid) {

		final String hql = "Update CompanyBean  set servicemanager="
				+ servicemanager + " where nid=" + nid;
		boolean flag = false;
		try {
			this.getHibernateTemplate().execute(new HibernateCallback() {

				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					boolean tag = false;
					Query query = session.createQuery(hql);
					int i = query.executeUpdate();
					if (i == 1) {
						tag = true;
					}

					return tag;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}

		return flag;
	}

	/**
	 * 更新注销标志
	 * 
	 * @param nid
	 */
	public boolean updatelogout(long nid) {
		boolean flag = true;
		final String hql = "Update CompanyBean set islogout=1 where nid=" + nid;

		this.getHibernateTemplate().execute(new HibernateCallback() {
			boolean tag = false;

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				Query query = session.createQuery(hql);
				int i = query.executeUpdate();

				if (i == 1) {
					tag = true;
				} else {
					tag = false;
				}
				return tag;
			}

		});
		return flag;
	}

	
	/**
	 * 用于系统用户和客户经理的集团列表的查询
	 * @param companyBean
	 * @param request
	 * @return
	 */
	public List<CompanyBean> listcompanyforall(CompanyBean companyBean, HttpServletRequest request) {
		int count = 0;
		PageDAO pageDAO = new PageDAO(request);
		pageDAO.setPagesize(20);
		count = getCountforall(companyBean);// 获取总记录数
		pageDAO.setRscount(count);
		int currentPage = pageDAO.getCurrentPage();
		int pagecount = pageDAO.getPageCount();// 获取一共有多少页
		int pageSize = pageDAO.getPagesize();// 获取每页显示多少条记录
		int maxResult = currentPage * pageSize;// 获取结束行
		int startResult = (currentPage - 1) * pageSize + 1;// 获取起始行
		String pageTool = pageDAO.pagetool(PageDAO.BbsText);// 构建工具条
		request.setAttribute("pageTool", pageTool);

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<CompanyBean> list = new ArrayList<CompanyBean>();
		StringBuffer sql = new StringBuffer();
		
		sql.append("select * from (");
		sql.append("select a.*,b.sname as typename,rownum as myrow from companytable a inner join typecode b");
		sql.append(" on a.callingtype = b.scode where 1=1");
		sql.append(" and a.islogout=0");

		if (companyBean.getCompanystate() != null
				&& !companyBean.getCompanystate().equals("")) {
				sql.append(" and companystate in ("+companyBean.getCompanystate()+")");
		}
		if (companyBean.getNid() != null && companyBean.getNid() != -1) {
			sql.append(" and nid=" + companyBean.getNid());
		}
		if (companyBean.getServicemanager() != null
				&& !companyBean.getServicemanager().equals("")) {
			
				sql.append(" and servicemanager="
						+ companyBean.getServicemanager());
			

		}
		sql.append(" and rownum<=" + maxResult);
		sql.append(") where myrow>=" + startResult);

		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());

			while (rs.next()) {
				CompanyBean company = new CompanyBean();
				company.setNid(rs.getLong("nid"));
				company.setCompanyid(rs.getString("companyid"));
				company.setCompanyname(rs.getString("companyname"));
				company.setCallingtype(rs.getString("typename"));
				company.setCompanylevel(rs.getString("companylevel"));
				company.setAddress(rs.getString("address"));
				company.setPhone(rs.getString("phone"));
				company.setDeputy(rs.getString("deputy"));
				company.setCompanystate(rs.getString("companystate"));
				company.setIslogout(rs.getString("islogout"));
				company.setUltimouser(rs.getString("ultimouser"));
				company.setUltimoearning(rs.getString("ultimoearning"));
				company.setServicemanager(rs.getString("servicemanager"));
				company.setTurntime(rs.getString("turntime"));
				list.add(company);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}

		return list;
	}
	/**
	 * 用于系统用户和客户经理的集团列表的查询获取记录数
	 * @param companyBean
	 * @param request
	 * @return
	 */
	public int getCountforall(CompanyBean companyBean) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int count = 0;
		StringBuffer sql = new StringBuffer();

		sql.append("select count(*) from companytable where 1=1 and islogout=0");

		if (companyBean.getCompanystate() != null
				&& !companyBean.getServicemanager().equals("")) {
			sql.append(" and companystate in (" + companyBean.getCompanystate()
					+ ")");
		}
		if (companyBean.getServicemanager() != null
				&& !companyBean.getServicemanager().equals("")) {

			sql.append(" and servicemanager="
							+ companyBean.getServicemanager());
		}
		if (companyBean.getCompanyid() != null
				&& !companyBean.getCompanyid().equals("")) {
			sql.append(" and companyid='" + companyBean.getCompanyid() + "'");
		}
		if (companyBean.getCompanyname() != null
				&& !companyBean.getCompanyname().equals("")) {
			sql.append(" and companyname like'%" + companyBean.getCompanyname()
					+ "%'");
		}
		if (companyBean.getCompanylevel() != null
				&& !companyBean.getCompanylevel().equals("")) {
			sql.append(" and companylevel='" + companyBean.getCompanylevel()
					+ "'");
		}
		System.out.println(sql+"......记录");

		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, stmt, con);
		}
		return count;
	}



	/**
	 * 更新集团客户状态
	 * 
	 * @param nid
	 */
	public boolean updatecompanystate(String state, String turntime ,long nid) {
		boolean  flag= false;
		Connection conn= null;
		Statement stmt = null;
	    String sql = "Update companytable set companystate='"+state+"',turntime='"+turntime+"' where nid=" + nid;
	    try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			
			if(stmt.executeUpdate(sql)>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;   
		
	}
	
	
	

}