电厂任务记录

---

任务

对各个电厂人员系统名单更新：和电厂人员沟通制定表格格式的标准，提供合适的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;
    }
}

---