clientmanager.java

java大型企业DRP系统源码带sql数据库

package com.bjsxt.drp.basedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.bjsxt.drp.util.DB;
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);
		}
	}		
}