大型企业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 = nul