deptuserdb.java

来自「一个用java编写的功能强大的OA系统」· Java 代码 · 共 504 行

package com.redmoon.oa.dept;

import java.io.*;
import java.sql.*;
import java.util.*;

import cn.js.fan.base.*;
import cn.js.fan.db.*;
import cn.js.fan.util.*;
import com.redmoon.oa.person.*;
import com.redmoon.oa.db.SequenceManager;


public class DeptUserDb extends ObjectDb implements Serializable {
    private String deptCode = "", userName = "";

    public DeptUserDb() {
        init();
    }

    public DeptUserDb(int id) {
        this.id = id;
        load();
        init();
    }

    public ObjectDb getObjectRaw(PrimaryKey pk) {
        return new DeptUserDb(pk.getIntValue());
    }

    public void setQueryCreate() {
        QUERY_CREATE = "insert into DEPT_USER (DEPT_CODE,USER_NAME,ORDERS,RANK,ID) values(?,?,?,?,?)";
    }

    public void setQuerySave() {
        this.QUERY_SAVE = "update DEPT_USER set USER_NAME=?,DEPT_CODE=?,ORDERS=?,RANK=? where ID=?";
    }

    public void setQueryDel() {
        QUERY_DEL = "delete from DEPT_USER where ID=?";
    }

    public void setQueryLoad() {
        this.QUERY_LOAD = "select DEPT_CODE,USER_NAME,ORDERS,RANK from DEPT_USER where ID=?";
    }

    public void load() {
        ResultSet rs = null;
        Conn conn = new Conn(connname);
        try {
            PreparedStatement ps = conn.prepareStatement(this.QUERY_LOAD);
            ps.setInt(1, id);
            rs = conn.executePreQuery();
            if (rs != null && rs.next()) {
                deptCode = rs.getString(1);
                userName = StrUtil.getNullStr(rs.getString(2));
                orders = rs.getInt(3);
                rank = rs.getString(4);
                loaded = true;
            }
        } catch (SQLException e) {
            logger.error("load:" + e.getMessage());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {}
                rs = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
    }

    public boolean create(String deptCode, String userName, String rank) throws ErrMsgException {
        boolean re = false;
        orders = getMaxOrders(deptCode) + 1;
        Conn conn = new Conn(connname);
        try {
            PreparedStatement ps = conn.prepareStatement(this.QUERY_CREATE);
            ps.setString(1, deptCode);
            ps.setString(2, userName);
            ps.setInt(3, orders);
            ps.setString(4, rank);
            id = (int)SequenceManager.nextID(SequenceManager.OA_DEPT_USER);
            ps.setInt(5, id);
            re = conn.executePreUpdate()==1;
        } catch (SQLException e) {
            logger.error("create:" + e.getMessage());
            throw new ErrMsgException("数据库操作错误，可能是编码重复造成的！");
        }
        finally {
            if (conn!=null) {
                conn.close();
                conn = null;
            }
        }
        return re;
    }

    public String getDeptCode() {
        return deptCode;
    }

    public void setDeptCode(String deptCode) {
        this.deptCode = deptCode;
    }

    public String getUserName() {
        return userName;
    }

    public int getId() {
        return id;
    }

    public int getOrders() {
        return orders;
    }

    public String getRank() {
        return rank;
    }


    public void setUserName(String n) {
        this.userName = n;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setOrders(int orders) {
        this.orders = orders;
    }

    public void setRank(String rank) {
        this.rank = rank;
    }

    private int id;

    public synchronized boolean save() {
        int r = 0;
        boolean re = false;
        Conn conn = new Conn(connname);
        try {
            PreparedStatement ps = conn.prepareStatement(QUERY_SAVE);
            ps.setString(1, userName);
            ps.setString(2, deptCode);
            ps.setInt(3, orders);
            ps.setString(4, rank);
            ps.setInt(5, id);
            r = conn.executePreUpdate();
            try {
                if (r == 1) {
                    re = true;
                    DeptUserCache dcm = new DeptUserCache();
                    dcm.refreshSave(id);
                }
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        } catch (SQLException e) {
            logger.error("修改出错！");
        }
        finally {
            if (conn!=null) {
                conn.close();
                conn = null;
            }
        }
        return re;
    }

    public boolean del() {
        Conn conn = new Conn(connname);
        int r = 0;
        boolean re = false;
        try {
            PreparedStatement ps = conn.prepareStatement(QUERY_DEL);
            ps.setInt(1, id);
            r = conn.executePreUpdate();
            if (r == 1) {
                re = true;
                DeptUserCache dcm = new DeptUserCache();
                dcm.refreshDel(id);
            }
            if (ps!=null) {
                ps.close();
                ps = null;
            }
            
            String sql = "select ID from DEPT_USER where DEPT_CODE=? and ORDERS>?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, deptCode);
            ps.setInt(2, orders);
            ResultSet rs = conn.executePreQuery();
            while (rs.next()) {
                DeptUserDb du = getDeptUserDb(rs.getInt(1));
                du.setOrders(du.getOrders() - 1);
                du.save();
            }
        } catch (SQLException e) {
            logger.error("del:" + e.getMessage());
        }
        finally {
            if (conn!=null) {
                conn.close();
                conn = null;
            }
        }
        return re;
    }

    public boolean isUserOfDept(String userName, String deptCode) {
        boolean re = false;
        Conn conn = new Conn(connname);
        try {
            String sql = "select ID from DEPT_USER where DEPT_CODE=? and USER_NAME=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, deptCode);
            ps.setString(2, userName);
            ResultSet rs = conn.executePreQuery();
            while (rs.next()) {
                re = true;
            }
        } catch (SQLException e) {
            logger.error("isUserOfDept:" + e.getMessage());
        }
        finally {
            if (conn!=null) {
                conn.close();
                conn = null;
            }
        }
        return re;
    }

    public DeptUserDb getDeptUserDb(int id) {
        DeptUserCache dcm = new DeptUserCache();
        return dcm.getDeptUserDb(id);
    }

    public String getDeptName() {
        DeptDb dd = new DeptDb();
        dd = dd.getDeptDb(deptCode);
        return dd.getName();
    }

    public void setQueryList() {
        this.QUERY_LIST = "select ID from DEPT_USER where DEPT_CODE=? order by orders asc";
    }

    
    public Vector getDeptsOfUser(String userName) {
        ResultSet rs = null;
        String sql =
                "select DEPT_CODE from DEPT_USER where USER_NAME=? order by ORDERS";
        Conn conn = new Conn(connname);
        Vector result = new Vector();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, userName);
            rs = conn.executePreQuery();
            if (rs == null) {
                return null;
            } else {
                DeptMgr dm = new DeptMgr();
                while (rs.next()) {
                    result.addElement(dm.getDeptDb(rs.getString(1)));
                }
            }
        } catch (SQLException e) {
            logger.error("list:" + e.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
        return result;
    }

    
    public Vector list2DWR(String deptCode) {
        return list(deptCode);
    }

    
    public Vector list(String deptCode) {
        ResultSet rs = null;
        Conn conn = new Conn(connname);
        Vector result = new Vector();
        try {
            PreparedStatement ps = conn.prepareStatement(this.QUERY_LIST);
            ps.setString(1, deptCode);
            rs = conn.executePreQuery();
            if (rs == null) {
                return null;
            } else {
                while (rs.next()) {
                    result.addElement(getDeptUserDb(rs.getInt(1)));
                }
            }
        } catch (SQLException e) {
            logger.error("list:" + e.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
        return result;
    }

    public boolean delUser(String userName) {
        String sql = "select ID from DEPT_USER where USER_NAME=?";
        ResultSet rs = null;
        boolean re = false;
        Conn conn = new Conn(connname);
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, userName);
            rs = conn.executePreQuery();
            if (rs == null) {
                return false;
            } else {
                while (rs.next()) {
                    getDeptUserDb(rs.getInt(1)).del();
                    re = true;
                }
            }
        } catch (SQLException e) {
            logger.error("list:" + e.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
        return re;
    }

    public void setPrimaryKey() {
        primaryKey = new PrimaryKey("id", PrimaryKey.TYPE_INT);
    }

    public ObjectDb getObjectDb(Object objKey) {
        return getDeptUserDb(((Integer)objKey).intValue());
    }

    public int getObjectCount(String sql) {
        return getDeptUserCount(sql);
    }

    public int getDeptUserCount(String sql) {
        DeptUserCache uc = new DeptUserCache();
        return uc.getDeptUserCount(sql);
    }

    public String getUserRealName() {
        UserDb ud = new UserDb();
        if (userName==null || userName.equals(""))
            return "";
        ud = ud.getUserDb(userName);
        if (ud!=null &&  ud.isLoaded())
            return ud.getRealName();
        else
            return "";
    }

    public int getMaxOrders(String deptCode) {
        String SQL_GETMAXORDERS =
            "select max(orders) from DEPT_USER where DEPT_CODE=?";
        Conn conn = new Conn(connname);
        ResultSet rs = null;
        int maxorders = -1;
        try {
            
            PreparedStatement pstmt = conn.prepareStatement(SQL_GETMAXORDERS);
            pstmt.setString(1, deptCode);
            rs = conn.executePreQuery();
            if (rs != null) {
                if (rs.next()) {
                    maxorders = rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            logger.error("getMaxOrders:" + e.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
        return maxorders;
    }

    public boolean move(String direction) throws ErrMsgException {
        
        if (direction.equals("up")) {
            if (orders==0)
                throw new ErrMsgException("该项已处在首位！");
        }
        if (direction.equals("down")) {
            if (orders==getMaxOrders(deptCode))
                throw new ErrMsgException("该项已处于最后一位！");
        }

        DeptUserDb bjob = getBrother(direction);
        if (bjob == null) {
            throw new ErrMsgException("该项不能被移动！");
        }

        Conn conn = new Conn(connname);
        boolean re = false;
        try {
            if (direction.equals("up")) {
                if (orders == 0)
                    return true;
                String sql = "update DEPT_USER set orders=orders+1 where id=?";
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setInt(1, bjob.getId());
                conn.executePreUpdate();
                sql = "update DEPT_USER set orders=orders-1 where id=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, id);
                conn.executePreUpdate();
                re = true;
            } else {
                int maxorders = getMaxOrders(deptCode);
                if (orders == maxorders) {
                    return true;
                } else {
                    String sql = "update DEPT_USER set orders=orders-1 where id=?";
                    PreparedStatement ps = conn.prepareStatement(sql);
                    ps.setInt(1, bjob.getId());
                    conn.executePreUpdate();
                    if (ps!=null) {
                        ps.close();
                        ps = null;
                    }
                    sql = "update DEPT_USER set orders=orders+1 where id=?";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1, id);
                    conn.executePreUpdate();
                }
                re = true;
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
        if (re) {
            DeptUserCache jc = new DeptUserCache();
            jc.refreshMove(id, bjob.getId());
        }
        return re;
    }

    public DeptUserDb getBrother(String direction) {
        String sql;
        Conn conn = new Conn(connname);
        DeptUserDb bleaf = null;
        try {
            if (direction.equals("down")) {
                sql = "select ID from DEPT_USER where DEPT_CODE=" +
                      StrUtil.sqlstr(deptCode) +
                      " and ORDERS=" + (orders + 1);
            } else {
                sql = "select ID from DEPT_USER where DEPT_CODE=" +
                      StrUtil.sqlstr(deptCode) +
                      " and ORDERS=" + (orders - 1);
            }

            ResultSet rs = conn.executeQuery(sql);
            if (rs.next()) {
                bleaf = getDeptUserDb(rs.getInt(1));
            }
        } catch (SQLException e) {
            logger.error("getBrother:" + e.getMessage());
        }
        finally {
            if (conn!=null) {
                conn.close();
                conn = null;
            }
        }
        return bleaf;
    }

    private int orders; 

    private String rank;
}