clientmanager.java

用java开发的一个企业的DRP系统源码

package com.bjsxt.drp.basedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bjsxt.drp.util.DB;
import com.bjsxt.drp.util.PageModel;
import com.bjsxt.drp.util.datadict.ClientLevel;

/**
 * 分销商管理类
 * @author Administrator
 *
 */
public class ClientManager {

	private static ClientManager instance = null;
	
	private ClientManager() {}
	
	/**
	 * 获取分销商管理者对象
	 * @return
	 */
	public static synchronized ClientManager getInstance() {
		if (instance == null) {
			instance = new ClientManager();
		}
		return instance;
	}
	
	/**
	 * 获取分销商树html字符串
	 * @return
	 */
	public String getTreeString() {
		ClientTreeReader treeReader = new ClientTreeReader();
		treeReader.read();
		return treeReader.getTreeString();
	}	
	
	/**
	 * 根据id查询区域或分销商
	 * @param id
	 * @return
	 */
	public Client findRegionOrClientById(int id) {
		StringBuffer sbfSql = new StringBuffer();
		sbfSql.append("select a.id, a.pid, a.name, a.client_id, b.id as client_level_id, b.name as client_level_name, ")
		      .append("a.bank_acct_no, a.contact_tel, a.address, a.zip_code, a.is_leaf, a.is_client ")
		      .append("from t_client a left join t_data_dict b on a.client_level=b.id ")
		      .append("where a.id=?");
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Client client = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sbfSql.toString());
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				client = new Client();
				client.setId(rs.getInt("id"));
				client.setPid(rs.getInt("pid"));
				client.setName(rs.getString("name"));
				client.setClientId(rs.getString("client_id"));
				//注意这个对象的赋值
				ClientLevel cl = new ClientLevel();
				cl.setId(rs.getString("client_level_id"));
				cl.setName(rs.getString("client_level_name"));
				client.setClientLevel(cl);
				client.setBankAcctNo(rs.getString("bank_acct_no"));
				client.setContactTel(rs.getString("contact_tel"));
				client.setAddress(rs.getString("address"));
				client.setZipCode(rs.getString("zip_code"));
				client.setIsLeaf(rs.getString("is_leaf"));
				client.setIsClient(rs.getString("is_client"));
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DB.closeRs(rs);
			DB.closeStmt(pstmt);
			DB.closeConn(conn);
		}
		return client;
	}
	
	/**
	 * 增加区域或分销商
	 * @param client
	 */
	public void addRegionOrClient(Client client) {
		StringBuffer sbfSql = new StringBuffer();
		sbfSql.append("insert into t_client(pid, name, client_id, client_level, bank_acct_no, ")
		      .append("contact_tel, address, zip_code, is_leaf, is_client) ")
		      .append("values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			//开启事务，因为对两个表进行更新操作
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sbfSql.toString());
			pstmt.setInt(1, client.getPid());
			pstmt.setString(2, client.getName());
			pstmt.setString(3, client.getClientId());
			pstmt.setString(4, client.getClientLevel().getId());
			pstmt.setString(5, client.getBankAcctNo());
			pstmt.setString(6, client.getContactTel());
			pstmt.setString(7, client.getAddress());
			pstmt.setString(8, client.getZipCode());
			pstmt.setString(9, client.getIsLeaf());
			pstmt.setString(10, client.getIsClient());
			pstmt.executeUpdate();
			//更新节点为非叶子节点
			modifyIsLeafField(conn, client.getPid());
			conn.commit();
		}catch(SQLException e) {
			try {
				//事务回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			DB.closeStmt(pstmt);
			DB.closeConn(conn);
		}
	}
	
	/**
	 * 更新节点为非叶子节点
	 * @param conn
	 * @param id
	 */
	private void modifyIsLeafField(Connection conn, int id) {
		String sql = "update t_client set is_leaf='N' where id=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DB.closeStmt(pstmt);
		}
	}

	/*
	 * 根据id删除分销商树节点
	 */
	public void deleteTreeNodeById(int id) {
		String sql = "select * from t_client where pid=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				if (rs.getString("is_leaf").equals("N")) {
					deleteTreeNodeById(rs.getInt("id"));
				}
				deleteTreeNodeById(conn, rs.getInt("id"));
			}
			deleteTreeNodeById(conn, id);			
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DB.closeRs(rs);
			DB.closeStmt(pstmt);
			DB.closeConn(conn);
		}
	}
	
	public void deleteTreeNodeById(Connection conn, int id) {
		String sql = "delete from t_client where id=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DB.closeStmt(pstmt);
		}
	}
	
//	public void deleteTreeNodeById(int id) {
//		String sql = "delete from t_client where id=? or pid=?";
//		Connection conn = null;
//		PreparedStatement pstmt = null;
//		try {
//			conn = DB.getConn();
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setInt(1, id);
//			pstmt.setInt(2, id);
//			pstmt.executeUpdate();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}finally {
//			DB.closeStmt(pstmt);
//			DB.closeConn(conn);
//		}
//	}
	
	/**
	 * 修改区域数据
	 * @param id
	 * @param name
	 */
	public void modifyRegion(int id, String name) {
		String sql = "update t_client set name=? where id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closeStmt(pstmt);
			DB.closeConn(conn);
		}
	}	
	
	/**
	 * 修改分销商数据
	 * @param client
	 */
	public void modifyClient(Client client) {
		String sql = "update t_client set name=?, client_id=?, client_level=?, bank_acct_no=?, contact_tel=?, address=?, zip_code=? " +
				"where id=?";	
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, client.getName());
			pstmt.setString(2, client.getClientId());
			pstmt.setString(3, client.getClientLevel().getId());
			pstmt.setString(4, client.getBankAcctNo());
			pstmt.setString(5, client.getContactTel());
			pstmt.setString(6, client.getAddress());
			pstmt.setString(7, client.getZipCode());
			pstmt.setInt(8, client.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DB.closeStmt(pstmt);
			DB.closeConn(conn);
		}
	}	
	
	/**
	 * 查询所有的分销删，对应页面aim_client_select.html
	 * @param pageNo
	 * @param pageSize
	 * @param queryStr
	 * @return
	 */
	public PageModel findAllAimClients(int pageNo, int pageSize,
			String queryStr) {
		String sql = "select id, name, level_id, level_name from v_aim_client " +
				"where (id like '" + queryStr + "%' or name like '" + queryStr + "%') " +
		         " order by id " +
				"limit " +
				(pageNo-1) * pageSize + 
				", " +
				pageSize;
		System.out.println(sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		PageModel pageModel = null;
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			List aimClientList = new ArrayList();
			while(rs.next()) {
				AimClient aimClient = new AimClient();
				aimClient.setId(rs.getString(1));
				aimClient.setName(rs.getString(2));
				aimClient.setLevelId(rs.getString(3));
				aimClient.setLevelName(rs.getString(4));
				aimClientList.add(aimClient); 
			} 
			pageModel = new PageModel();
			pageModel.setPageNo(pageNo);
			pageModel.setPageSize(pageSize);
			pageModel.setList(aimClientList);
			pageModel.setTotalRecords(getTotalAimRecords(conn, queryStr));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
			DB.closeConn(conn);
		}
		return pageModel;			
	}

	 private int getTotalAimRecords(Connection conn, String queryStr) {
			String sql = "select count(*) from v_aim_client " +
			"where id like '" + queryStr + "%' or name like '" + queryStr + "%' ";
		int totalRecords = 0;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				totalRecords = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
		}
		return totalRecords;	 
	}		 
		/**
		 * 查询分销商 对应页面client_select.html
		 * @param pageNo
		 * @param pageSize
		 * @param queryStr
		 * @return
		 */
		public PageModel findAllClients(int pageNo, int pageSize, String queryStr) {
			StringBuffer sbfSql = new StringBuffer();
			sbfSql.append("select a.id, a.pid, a.name, a.client_id, b.id as client_level_id, b.name as client_level_name, ")
			.append("a.bank_acct_no, a.contact_tel, a.address, a.zip_code, a.is_leaf, a.is_client ")
			.append("from t_client a, t_data_dict b where a.client_level=b.id and a.is_client='Y' ")
			.append("and (a.client_id like '" + queryStr + "%' or ")
			.append("a.name like '" + queryStr + "%')  order by a.id ")
			.append("limit ")
			.append((pageNo - 1) * pageSize)
			.append(", ")
			.append(pageSize);
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			PageModel pageModel = null;
			try {
				conn = DB.getConn();
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sbfSql.toString());
				List clientList = new ArrayList();
				while(rs.next()) {
					Client client = new Client();
					client.setId(rs.getInt(1));
					client.setPid(rs.getInt(2));
					client.setName(rs.getString(3));
					client.setClientId(rs.getString(4));
					ClientLevel cl = new ClientLevel();
					cl.setId(rs.getString(5));
					cl.setName(rs.getString(6));
					client.setClientLevel(cl);
					client.setBankAcctNo(rs.getString(7));
					client.setContactTel(rs.getString(8));
					client.setAddress(rs.getString(9));
					client.setZipCode(rs.getString(10));
					client.setIsLeaf(rs.getString(11));
					client.setIsClient(rs.getString(12));
					clientList.add(client);
				} 
				pageModel = new PageModel();
				pageModel.setPageNo(pageNo);
				pageModel.setPageSize(pageSize);
				pageModel.setList(clientList);
				pageModel.setTotalRecords(getTotalClientRecords(conn, queryStr));
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DB.closeRs(rs);
				DB.closeStmt(stmt);
				DB.closeConn(conn);
			}
			return pageModel;	
	}
		
		private int getTotalClientRecords(Connection conn, String queryStr) {
			String sql = "select count(*) from t_client where is_client='Y' and " +
					"(client_id like '" + queryStr + "%' or name like '" + queryStr + "%')";
			int totalRecords = 0;
			Statement stmt = null;
			ResultSet rs = null;
			try {
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				if (rs.next()) {
					totalRecords = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DB.closeRs(rs);
				DB.closeStmt(stmt);
			}
			return totalRecords;
		}	 
}