package com.vci.server.framework.systemConfig.stafforgmanage.dept;
|
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Timestamp;
|
import java.util.List;
|
import org.apache.commons.lang3.StringUtils;
|
import org.hibernate.HibernateException;
|
|
import com.vci.common.objects.UserEntity;
|
import com.vci.server.base.persistence.dao.BaseService;
|
import com.vci.server.base.persistence.dao.HibernateCallback;
|
import com.vci.server.base.persistence.dao.HibernateCallbackExt;
|
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
|
import com.vci.server.base.persistence.dao.HibernateTemplate;
|
import com.vci.server.base.persistence.dao.JDBCCallback;
|
import com.vci.server.base.persistence.dao.JDBCRunType;
|
import com.vci.server.base.persistence.dao.JDBCTemplate;
|
|
public class DepartmentService extends BaseService {
|
public DepartmentService() {}
|
public DepartmentService(UserEntity userEntity) {
|
super(userEntity);
|
}
|
|
public void saveDepartment(final Department dept) {
|
new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
dept.setUserEntity(userEntity);
|
impl.save(dept);
|
return dept;
|
}
|
});
|
}
|
|
public void batchSaveDepart(Department[] depts) throws HibernateException, SQLException {
|
String sql = "INSERT INTO PLDEPT VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
|
Timestamp currentTime = new Timestamp(System.currentTimeMillis());
|
PreparedStatement pst = null;
|
int index = 1;
|
int batchSize = 200;
|
for (int i = 0; i < depts.length; i++) {
|
index = 1;
|
if(pst == null){
|
pst = HibernateSessionFactory.getSessionConnection().prepareStatement(sql);
|
}
|
pst.setString(index++, depts[i].getId());
|
pst.setString(index++, depts[i].getName());
|
pst.setString(index++, depts[i].getNum());
|
pst.setString(index++, depts[i].getCode());
|
pst.setInt(index++, 0);
|
pst.setString(index++, depts[i].getParentId());
|
pst.setString(index++, depts[i].getDesc());
|
pst.setTimestamp(index++, currentTime);
|
pst.setString(index++, userEntity.getUserName());
|
pst.setTimestamp(index++, currentTime);
|
pst.setString(index++, userEntity.getUserName());
|
pst.setString(index++, "");
|
pst.addBatch();
|
|
// 是否达到批量执行阀值
|
if((i+1) % batchSize == 0){
|
// 达到则执行
|
pst.executeBatch();
|
}
|
}
|
|
pst.executeBatch();
|
|
closePreparedStatement(pst);
|
}
|
|
private void closePreparedStatement(PreparedStatement pst) throws SQLException{
|
if(pst != null){
|
pst.clearBatch();
|
pst.clearParameters();
|
pst.close();
|
pst = null;
|
}
|
}
|
|
@SuppressWarnings("rawtypes")
|
public List deleteDepartment(final String id) {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String sql = " select * from PLDEPT d start with d.pluid = ? connect by prior d.pluid = d.plparentuid " ;
|
Object[] values = new Object[1];
|
values[0] = id;
|
List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
|
for(int i=0;i<list.size();i++) {
|
Department object = (Department)list.get(i);
|
String id = object.getId();
|
Department entity = impl.loadById(id);
|
entity.setUserEntity(userEntity);
|
impl.delete(entity);
|
}
|
return list;
|
}
|
});
|
}
|
|
public boolean deleteDepartmentByHQL(final String id) {
|
return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
String hql = "delete Department dept where dept.id=:id";
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
impl.deleteQueryObject(hql, "id", id, userEntity);
|
return true;
|
}
|
});
|
}
|
|
/**
|
* 更新部门同步状态
|
* @param ids
|
* @return
|
*/
|
public boolean updateByDeptCode(final String[] ids) {
|
return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
String hql1 = "update Department dept set dept.status = 2 where dept.num in(";
|
int len1 = ids.length;
|
for (int i = 0; i < len1; i++) {
|
hql1 += "?";
|
if (i != len1 - 1) {
|
hql1 += ",";
|
}
|
}
|
hql1 += ")";
|
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
impl.deleteQueryObject(hql1, ids, userEntity);
|
return true;
|
}
|
});
|
}
|
|
public boolean updateDeptParentId(final String id, final String parentId) {
|
return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
String hql1 = "update Department dept set dept.parentId = :parentId where dept.id = :id";
|
String[] props = new String[2];
|
props[0] = "parentId";
|
props[1] = "id";
|
|
String[] values = new String[2];
|
values[0] = parentId;
|
values[1] = id;
|
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
impl.deleteQueryObject(hql1, props, values, userEntity);
|
return true;
|
}
|
});
|
}
|
|
public boolean deleteDepartmentByHQL(final String[] ids) {
|
return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
String hql1 = "delete Department dept where dept.parentId in(";
|
int len1 = ids.length;
|
for (int i = 0; i < len1; i++) {
|
hql1 += "?";
|
if (i != len1 - 1) {
|
hql1 += ",";
|
}
|
}
|
hql1 += ")";
|
|
String hql = "delete Department dept where dept.id in(";
|
int len = ids.length;
|
for (int i = 0; i < len; i++) {
|
hql += "?";
|
if (i != len - 1) {
|
hql += ",";
|
}
|
}
|
hql += ")";
|
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
impl.deleteQueryObject(hql1, ids, userEntity);
|
impl.deleteQueryObject(hql, ids, userEntity);
|
return true;
|
}
|
});
|
}
|
|
public boolean updateDepartment(final Department dept) {
|
return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
Department curDept = impl.getById(dept.getId());
|
if (curDept == null) {
|
dept.setUserEntity(userEntity);
|
impl.saveOrUpdate(dept);
|
} else {
|
curDept.setName(dept.getName());
|
curDept.setNum(dept.getNum());
|
curDept.setCode(dept.getCode());
|
curDept.setStatus(curDept.getStatus());
|
curDept.setDesc(dept.getDesc());
|
curDept.setSpecialties(dept.getSpecialties());
|
curDept.setUpdateTime(dept.getUpdateTime());
|
curDept.setUpdateUser(dept.getUpdateUser());
|
curDept.setGrantor(dept.getGrantor());
|
curDept.setUserEntity(userEntity);
|
impl.saveOrUpdate(curDept);
|
}
|
|
return true;
|
}
|
});
|
}
|
|
public Department selectDepartmentById(final String id) {
|
return (Department)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
return impl.loadById(id);
|
}
|
});
|
}
|
public Department fetchManageDept(final String rmTypeId) {
|
return (Department)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String hsql = " select * from pldept d where d.PLUID in " +
|
" (select ud.PlDEPTID from plmanagedept ud where ud.PLCLASSIFYID = '"+rmTypeId+"')";
|
Object[] values = new Object[0];
|
List<Department> list = impl.findEntites(hsql, values, "d", Department.class);
|
if (list.size()> 0){
|
return list.get(0);
|
}else{
|
return null;
|
}
|
|
}
|
});
|
}
|
public Department fetchManageOfMaterialsDept(final String rmTypeId) {
|
return (Department)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String hsql = " select * from pldept d where d.PLUID in " +
|
" (select ud.PLmaterialsDeptId from plmanagedept ud where ud.PLCLASSIFYID = '"+rmTypeId+"')";
|
Object[] values = new Object[0];
|
List<Department> list = impl.findEntites(hsql, values, "d", Department.class);
|
if (list.size()> 0){
|
return list.get(0);
|
}else{
|
return null;
|
}
|
|
}
|
});
|
}
|
public Department fetchDeptByUserId(final String userId) {
|
return (Department)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String hsql = " select * from pldept d where d.PLUID in " +
|
" (select ud.pldeptuid from pluserdept ud where ud.pluseruid = '"+userId+"')";
|
Object[] values = new Object[0];
|
List<Department> list = impl.findEntites(hsql, values, "d", Department.class);
|
if (list.size()> 0){
|
return list.get(0);
|
}else{
|
return null;
|
}
|
|
}
|
});
|
}
|
|
public Department selectDepartmentByName(final String name) {
|
return (Department)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String hsql = "from Department dept where dept.name = :name";
|
return impl.findEntity(hsql, "name", name);
|
}
|
});
|
}
|
|
public Department fetchDeptByParentIdAndName(final String parentId ,final String name) {
|
return (Department)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String hsql = "from Department dept where dept.name = '"+name+"' ";
|
if (!"".equals(parentId)){
|
hsql += "and dept.parentId = '"+parentId+"'";
|
}else{
|
hsql += "and dept.parentId is null";
|
}
|
|
|
return impl.findEntity(hsql);
|
}
|
});
|
}
|
|
@SuppressWarnings("rawtypes")
|
public List getDepartmentList() {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
return impl.loadAll();
|
}
|
});
|
}
|
|
@SuppressWarnings("rawtypes")
|
public List getDepartmentListByFilter(final boolean root, final String prtoid) {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
List res = null;
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String hsql = "from Department dept where ";
|
if(root){
|
hsql += " dept.parentId is null";
|
res = impl.findEntities(hsql);
|
}else{
|
if(prtoid != null && !prtoid.equals("")){
|
hsql += " dept.parentId = ?";
|
Object[] values = { prtoid };
|
res = impl.findEntites(hsql, values);
|
}else if ("".equals(prtoid)){
|
hsql += " dept.parentId is null";
|
res = impl.findEntities(hsql);
|
}
|
}
|
return res;
|
}
|
});
|
}
|
|
@SuppressWarnings("rawtypes")
|
public List getDepartmentPageList(final int pageNo, final int pageSize) {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
return impl.findList(pageNo, pageSize);
|
}
|
});
|
}
|
@SuppressWarnings("rawtypes")
|
public List getDepartmentListById(final String id) {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
if("root".equals(id)) {
|
return impl.loadAll();
|
}
|
String sql = " select * from PLDEPT d start with d.pluid = ? connect by prior d.pluid = d.plparentuid " ;
|
|
Object[] values = new Object[1];
|
values[0] = id;
|
List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
|
return list;
|
|
}
|
});
|
}
|
@SuppressWarnings("rawtypes")
|
public List getDepartmentListBySonId(final String id) {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
if("root".equals(id)) {
|
return impl.loadAll();
|
}
|
String sql = " select * from PLDEPT d start with d.pluid = ? connect by prior d.plparentuid=d.pluid " ;
|
Object[] values = new Object[1];
|
values[0] = id;
|
List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
|
return list;
|
|
}
|
});
|
}
|
@SuppressWarnings("rawtypes")
|
public List getDepartmentListByType(final String type) {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String sql = " select * from PLDEPT d start with d.PLUSERTYPE = ? connect by prior d.PLOID = d.PLPARENTOID " ;
|
int a = Integer.parseInt(type);
|
String type1 = "";
|
if (a<2){
|
a = a + 1;
|
}
|
type1 = "" + a + "";
|
Object[] values = { type1 };
|
List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
|
return list;
|
|
}
|
});
|
}
|
@SuppressWarnings("rawtypes")
|
public List getDepartmentInfoRootByType(final boolean root, final String type) {
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
List res = null;
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
String hsql = "from Department dept where 1=1 ";
|
if(root){
|
hsql += " and dept.parentId is null and dept.userType = ? ";
|
int a = Integer.parseInt(type);
|
String type1 = "";
|
if (a<2){
|
a = a + 1;
|
}
|
type1 = "" + a + "";
|
Object[] values = { type1 };
|
res = impl.findEntites(hsql, values);
|
}
|
return res;
|
}
|
});
|
}
|
|
public boolean saveRight(final String deptId , final String[] userIds ){
|
return (Boolean)new HibernateTemplate().runExt(new HibernateCallbackExt() {
|
public Object execute(Connection conn) throws HibernateException,
|
SQLException {
|
String sql = "delete pluserdept ud where ud.pldeptuid = ? ";
|
PreparedStatement pstmt = null;
|
PreparedStatement pstmt1 = null;
|
try {
|
pstmt = conn.prepareStatement(sql);
|
pstmt.setString(1, deptId);
|
pstmt.execute();
|
} catch (SQLException e1) {
|
e1.printStackTrace();
|
} finally {
|
if (pstmt != null){
|
pstmt.close();
|
}
|
}
|
try {
|
for (int i = 0 ; i < userIds.length ; i ++){
|
String hql = "delete pluserdept ud where ud.pluseruid = ?";
|
pstmt1 = conn.prepareStatement(hql);
|
pstmt1.setString(1, userIds[i]);
|
pstmt1.execute();
|
}
|
}catch (SQLException e1) {
|
e1.printStackTrace();
|
} finally {
|
if (pstmt1 != null){
|
pstmt1.close();
|
}
|
}
|
try {
|
for (int i = 0 ; i < userIds.length ; i ++){
|
String hql = "insert into pluserdept values (? , ?)";
|
pstmt1 = conn.prepareStatement(hql);
|
pstmt1.setString(1, userIds[i]);
|
pstmt1.setString(2, deptId);
|
pstmt1.execute();
|
}
|
}catch (SQLException e1) {
|
e1.printStackTrace();
|
} finally {
|
if (pstmt1 != null){
|
pstmt1.close();
|
}
|
}
|
return true;
|
}
|
});
|
}
|
|
|
@SuppressWarnings("unchecked")
|
public List<Department> fetchDeptByUserNames(final String[] userNames) {
|
return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
|
String names = "";
|
int k = 0;
|
for(String name : userNames) {
|
names += "'" + name + "'";
|
if(k++ < userNames.length - 1) {
|
names += ",";
|
}
|
|
}
|
|
String sql = " select p.* from pldept p start with p.pluid in( " +
|
" select up.pldeptuid from pluserdept up where up.pluseruid in( " +
|
" select u.pluid from pluser u where u.plusername in("+ names +") "+
|
" )" +
|
" )" +
|
" connect by prior p.plparentuid = p.pluid " ;
|
|
Object[] values = new Object[0];
|
List<Department> list = impl.findEntites(sql, values, "Department", Department.class);
|
return list;
|
}
|
});
|
}
|
|
@SuppressWarnings("unchecked")
|
public List<Department> fetchDeptByNum(final String num) {
|
return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
|
String sql = "select p.* from pldept p where p.plnum = ?";
|
|
Object[] values = new Object[1];
|
values[0]= num;
|
List<Department> list = impl.findEntites(sql, values, "Department", Department.class);
|
return list;
|
}
|
});
|
}
|
|
@SuppressWarnings("unchecked")
|
public List<Department> fetchDepartmentInfoByIds(final String otherFiterString){
|
return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
StringBuffer sql=new StringBuffer();
|
sql.append(" select p.* from pldept p where 1=1 ");
|
sql.append(otherFiterString);
|
Object[] values = new Object[0];
|
List<Department> list = impl.findEntites(sql.toString(), values, "Department", Department.class);
|
return list;
|
}
|
});
|
}
|
|
public List fetchChildrenDeptByParentOid(final String prtoid,
|
final boolean iscontains, final String otherFiterString){
|
return (List)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
StringBuffer sb=new StringBuffer();
|
sb.append(" select * from PLDEPT ");
|
Object[] values = new Object[]{};
|
if(iscontains){
|
sb.append(" start with " + (StringUtils.isBlank(prtoid)?" (plparentuid is null or plparentuid = '') ":("pluid = '"+prtoid+"'")));
|
sb.append(" connect by prior pluid= plparentuid ");
|
}else{
|
sb.append(" and ( plparentuid='"+prtoid+"' or pluid='"+prtoid+"' )");
|
}
|
if(!"".equals(otherFiterString)){
|
sb.append( otherFiterString);
|
}
|
List list = impl.findEntites(sb.toString(), values, "PLDEPT", Department.class);
|
return list;
|
}
|
});
|
|
}
|
|
@SuppressWarnings("unchecked")
|
public List<Department> gridDeptDataGrids(final String filter, int pageNo, int pageSize){
|
final int V_PAGE_ROWNUM_START = (pageNo - 1) * pageSize;
|
final int V_PAGE_ROWNUM_END = (pageNo ) * pageSize;
|
return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
|
public Object execute() throws HibernateException {
|
DepartmentDAOImpl impl = new DepartmentDAOImpl();
|
StringBuffer sb=new StringBuffer();
|
sb.append(" select * from ");
|
sb.append(" ( ");
|
sb.append(" select pldept.*,rownum RN from pldept ");
|
sb.append(" where 1=1 ");
|
sb.append( filter );
|
sb.append(" ) ");
|
sb.append(" WHERE RN >");
|
sb.append(V_PAGE_ROWNUM_START);
|
sb.append(" AND RN <= ");
|
sb.append(V_PAGE_ROWNUM_END);
|
Object[] values = new Object[0];
|
List<Department> list = impl.findEntites(sb.toString(), values, "u", Department.class);
|
return list;}
|
});
|
}
|
|
public int gridDeptDataGridsCount(final String filter){
|
StringBuffer sb=new StringBuffer();
|
sb.append(" select count(*) from pldept ");
|
sb.append(" where 1=1 ");
|
sb.append( filter );
|
int res=0;
|
res = (Integer)new JDBCTemplate().run(new JDBCCallback(
|
new HibernateTemplate().getSessionConnection(),
|
sb.toString(), JDBCRunType.SQL, 0, false, false,new Object[]{}) {
|
@Override
|
public Object execute(ResultSet rst) throws SQLException {
|
int ress = 0;
|
while(rst.next()){
|
ress = rst.getInt(1);
|
}
|
return ress;
|
}
|
});
|
return res;
|
}
|
}
|
