任务
对各个电厂人员系统名单更新:和电厂人员沟通制定表格格式的标准,提供合适的Excel文件。
代码
参考正解代码如下:
package com.zknw.sysmanage;
import java.util.List;
import com.zknw.sysmanage.handler.BuMenOperationHandler;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import com.zknw.sysmanage.dao.first.*;
import com.zknw.sysmanage.model.*;
import com.zknw.util.MD5;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.*;
@SpringBootTest
@RunWith(SpringRunner.class)
/**
* 读取表格中的层级关系
* @date 2021年4月12日
*/
/**
* 导入东北新能源人员信息
*/
public class JiaGou {
@Autowired
OrganDao organDao;
@Autowired
SysUserDao sysUserDao;
@Autowired
SysProfileDao sysProfileDao;
@Autowired
DictDao dictDao;
@Autowired
DictRelationDao dictRelationDao;
@Autowired
private BuMenOperationHandler buMenOperationHandler;
@Test
public void myTest() throws Exception {
//a记录更新的人员数目 ;b代表新添人员数目
//m记录弃用人员数目
int a = 0;
int b = 0;
int m = 0;
int w = 0;
//list1用于记录保存新表格的时候自动分配的userid
List<String> list1 = new ArrayList<String>();
Workbook sheets=null;
try{
//导入文件在项目文件的import_file_templates文件夹下
File file=new File("import_file_templates/人员信息-东北新能源.xls");
sheets = WorkbookFactory.create(file);
} catch (Exception e) {
e.printStackTrace();
}
Sheet sheet = null;
if(sheets!=null){
sheet= sheets.getSheet("Sheet1");
}
if(sheet==null){
System.out.println("sheet 对象为null!!!");
return;
}
//开始遍历表格内容
for (int i = 2; i < sheet.getLastRowNum() + 1; i++) {
Row row = sheet.getRow(i);
System.out.println(row.getCell(0));
System.out.println(row.getCell(1));
System.out.println(row.getCell(2));
System.out.println(row.getCell(3));
String userName = row.getCell(0).toString();
String realName = row.getCell(1).toString();
String organName = row.getCell(2).toString();
String profession = row.getCell(3).toString();
String job = row.getCell(4).toString();
String roleNmae = row.getCell(5).toString();
//58代表一般用户
String role = "58";
if ("管理员".equals(roleNmae)) {
role = "1";
}
//直接定位到数据库的新能源公司
OrganDO organ = getOrgan(8L, organName); //部门
SysUser sysUserByUserName = sysUserDao.getSysUserByUserName(userName); //工号
//判断用户名userName即工号是否为空
//如果读取到用户的工号已存在,即不为空,开始执行更新操作
if (Objects.nonNull(sysUserByUserName)) {
Boolean flagUser = false;
Boolean flagProfile = false;
//获取数据库中已有用户ID
Integer userId = sysUserByUserName.getUserId();
//如果已有的人员部门信息与读取的人员信息的部门信息不相同
if (!Long.valueOf(sysUserByUserName.getOrgan_id()).equals(organ.getAuto_id())) {
sysUserByUserName.setOrgan_id(organ.getAuto_id().intValue());
sysUserByUserName.setOrgan_name(organ.getOrgan_name());
sysUserByUserName.setJob(job);
//进行更新
sysUserDao.updateSysUser(sysUserByUserName);
//更新以后,计数加1
a++;
}
dictRelationDao.removeByUserId(Long.valueOf(sysUserByUserName.getUserId()));
DictDO getzhuanye = getzhuanye( profession);
if (getzhuanye != null) {
DictRelationDO dictRelationDO = new DictRelationDO();
dictRelationDO.setDict_id(Long.valueOf(getzhuanye.getAuto_id()));
dictRelationDO.setObj_id(Long.valueOf(sysUserByUserName.getUserId()));
dictRelationDO.setRelation("user");
dictRelationDao.save(dictRelationDO);
}
UserProfile userProfileByUserId = sysProfileDao.getUserProfileByUserId(userId);
if (!userProfileByUserId.getRoleId().toString().equals(role)) {
userProfileByUserId.setRoleId(Integer.valueOf(role));
sysProfileDao.updateSysProfile(userProfileByUserId);
}
//把表人员的userid存进list1
list1.add(sysUserByUserName.getUserId().toString());
} else {
SysUser sysUser = new SysUser();
SysProfile sysProfile = new SysProfile();
sysUser.setUserName(userName);
sysUser.setPassWord(MD5.getMD5String("123456"));
sysUser.setUnEnabled(true);
sysUser.setOrgan_id(organ.getAuto_id().intValue());
sysUser.setOrgan_name(organ.getOrgan_name());
sysUser.setDateCreated(new Date(System.currentTimeMillis()));
sysUser.setStatus(1);
sysUserDao.insert(sysUser);
b++;
sysProfile.setUserId(sysUser.getUserId());
sysProfile.setRealName(realName);
sysProfile.setRoleId(Integer.valueOf(role));
sysProfile.setSkinId(1);
sysProfile.setMenuId(1);
//在这里设置了companyId
sysProfile.setCompanyId(8);
sysProfileDao.insert(sysProfile);
DictDO getzhuanye = getzhuanye( profession);
if (getzhuanye != null) {
DictRelationDO dictRelationDO = new DictRelationDO();
dictRelationDO.setDict_id(Long.valueOf(getzhuanye.getAuto_id()));
dictRelationDO.setObj_id(Long.valueOf(sysUser.getUserId()));
dictRelationDO.setRelation("user");
dictRelationDao.save(dictRelationDO);
}
//把表新增人员的userid存进list1
list1.add(sysUser.getUserId().toString());
}
}
//表格遍历结束,反馈更新的情况
System.out.println("更新人员数目" + a + "\n" + "新添加人员" + b);
//显示更新表中所有人员的userid
System.out.println(list1);
//根据organ_id获取所有旧的新能源人员信息
//先获取所有旧的新能源部门的organ_id
//筛选获取新能源公司人员userid,即companyid=8的人员,存进list2
List<String> list2 = new ArrayList<String>();
//获取所有新能源公司auto_id=8下的信息,找到所有一级子单位
//筛选获取新能源本部公司人员organ_id,存进list3
List<OrganDO> organList = organDao.getChildrenOrgan(8L);
List<Long> list3 = new ArrayList<Long>();
if (organList.size() > 0) {
for (OrganDO o : organList) {
list3.add(o.getAuto_id());
}
}
System.out.println("zuzhi");
System.out.println(list3);
//获取所有部门信息
List<OrganDO> organListAll = organDao.all();
//获取一级目录下的二级部门auto_id
List<Long> list4 = new ArrayList<Long>();
if (organList.size() > 0) {
for (OrganDO g : organListAll) {
for (long n : list3) {
if (g.getParent_id() == n) {
list4.add(g.getAuto_id());
} else {
continue;
}
}
}
}
System.out.println("zuzhi1");
System.out.println(list4);
//获取二级目录下的三级部门auto_id
List<Long> list5 = new ArrayList<Long>();
if (organList.size() > 0) {
for (OrganDO g : organListAll) {
for (long n : list4) {
if (g.getParent_id() == n) {
list5.add(g.getAuto_id());
} else {
continue;
}
}
}
}
System.out.println("zuzhi2");
System.out.println(list5);
//把找到的所有部门id汇总
List<Long> list6 = new ArrayList<Long>();
list6.addAll(list3);
list6.addAll(list4);
list6.addAll(list5);
System.out.println("zuzhi3");
System.out.println(list6);
//确保所有人员的companyid=8
List<SysUser> sysU1 = sysUserDao.getSysUserList();
//获取数据库sys_profile所有人员userid
List<SysProfile> sysU2 = sysProfileDao.getSysProfileList();
//list7存入所有旧部门人员的user_id
List<Integer> list7 = new ArrayList<Integer>();
for (SysUser s : sysU1) {
for (long n : list6) {
if (s.getOrgan_id() == n) {
list7.add(s.getUserId());
} else {
continue;
}
}
}
//遍历sysProfile的所有的旧的人员
for (SysProfile q : sysU2) {
//如果该人员属于list7中的部门
if (list7.contains(q.getUserId())) {
q.setCompanyId(8);
sysProfileDao.updateSysProfile(q);
} else {
continue;
}
}
//获取更新后的sys_profile所有新能源人员userid,存进list2
List<SysProfile> sysU3 = sysProfileDao.getSysProfileList();
if (sysU3 != null && sysU3.size() > 0) {
for (SysProfile s : sysU3) {
if (s.getCompanyId() != null) {
int k = s.getCompanyId();
if (k == 8) {
list2.add(s.getUserId().toString());
} else {
continue;
}
} else {
continue;
}
}
}
//显示数据库所有的新能源公司人员userid
System.out.println(list2);
//将两张表进行遍历对比
//根据userid查询用户的所有信息
for (String userId : list2) {
if (list1.contains(userId)) {
//类型转换
SysUser sysUser1 = new SysUser();
int c = Integer.parseInt(userId);
sysUser1 = sysUserDao.getSysUserById(c);
sysUser1.setStatus(1);
sysUser1.setUnEnabled(true);
sysUserDao.updateSysUser(sysUser1);
w++;
} else {
// 说明list2中没有这个userid
SysUser sysUser2 = new SysUser();
int d = Integer.parseInt(userId);
sysUser2 = sysUserDao.getSysUserById(d);
sysUser2.setUnEnabled(false);
sysUser2.setStatus(0);
sysUserDao.updateSysUser(sysUser2);
m++;
}
}
System.out.println("无效人员" + m + "\n");
System.out.println("显示人员" + w + "\n");
System.out.println("原公司总人数" + (m+w) + "\n");
System.out.println("原公司总人数" + list2.size() + "\n");
}
private OrganDO getOrgan(Long companyId, String organName) {
System.out.println(organName);
OrganDO organDO = organDao.get(companyId);
Long parentId = companyId;
String[] depart = organName.split("=");
if(depart.length>1){
System.out.println(1);
// if(organName.split("_").length>1){
// System.out.println(2);
// }
}
if (0 == depart.length) {
depart[0] = "未分配";
}
Map map = new HashMap();
String organDO2= organDO.getOrgan_name();
//设置j=1,即不对公司进行操作,将一级单位给了新能源公司,不再新建公司
for (int j=1;j<depart.length;j++) {
map.put("short_name", depart[j]);
map.put("parent_id", parentId);
List<OrganDO> organDOList = organDao.list(map);
if (organDOList.size() > 0) {
organDO = organDOList.get(0);
parentId = organDO.getAuto_id();
} else {
organDO=new OrganDO();
organDO.setParent_id(Long.valueOf(parentId));
organDO.setOrgan_name(depart[j]);
organDO.setOrder_num(0);
organDO.setShort_name(depart[j]);
organDao.save(organDO);
parentId=organDO.getAuto_id();
}
}
// 如果organ只到了company级别,则划分到未分配中;
if (organDO.getOrganiztion_id() != null && organDO.getOrganiztion_id() == 0) {
map = new HashMap();
map.put("parent_id", organDO.getAuto_id());
map.put("short_name", "未分配");
List<OrganDO> list = organDao.list(map);
if (list.size() != 0) {
organDO = list.get(0);
}
}
return organDO;
}
private DictDO getzhuanye( String profession) {
DictDO aDo=null;
long id=383;
Long parentId = id;
String[] depart = profession.split("=");
Map map = new HashMap();
for (String str : depart) {
map.put("name", str);
map.put("parent_id", parentId);
List<DictDO> list = dictDao.list(map);
if (list.size() > 0) {
aDo = list.get(0);
parentId = aDo.getAuto_id();
} else {
break;
}
}
// 如果organ只到了company级别,则划分到未分配中;
return aDo;
}
}