Excel导入导出，增删改查的实现

1.Controller

package com.nb.easy.easyframe.module.archievs.controller.app;


import com.nb.easy.easyframe.framework.common.pojo.CommonResult;
import com.nb.easy.easyframe.framework.operatelog.core.annotations.OperateLog;
import com.nb.easy.easyframe.module.archievs.controller.admin.vo.administrative.AdministrativeExcelVO;
import com.nb.easy.easyframe.module.archievs.controller.vo.administrative.AdministrativePageReqVO;
import com.nb.easy.easyframe.module.archievs.dal.dataobject.administrative.AdministrativeDO;
import com.nb.easy.easyframe.module.archievs.service.administrative.AdministrativeService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import java.util.List;

import static com.nb.easy.easyframe.framework.common.pojo.CommonResult.success;
import static com.nb.easy.easyframe.framework.operatelog.core.enums.OperateTypeEnum.EXPORT;

@Api(tags = "业务接口-文书档案")
@RestController
@Validated
@RequestMapping("/archives/administrative")
public class AdministrativeController {

    @Autowired
    private AdministrativeService administrativeService;

    /**
     * 测试
     */
    @GetMapping("/select")
    @ApiOperation("测试")
    public CommonResult selectArchives() {
        return administrativeService.selectAdministrativeArchives();
    }

    /**
     * 文书档案 id查询
     */
    @GetMapping("/selectById")
    @ApiOperation("id查询")
    public CommonResult selectAdministrativeById(@Valid @RequestParam("id") Integer id){
        return administrativeService.selectAdministrativeById(id);
    }


    /**
     * 文书档案 条件查询
     */
    @PostMapping("/selectByWapper")
    @ApiOperation("id查询")
    public CommonResult selectAdministrativeByWapper(@Valid @RequestBody AdministrativeDO archivesDO){
        return administrativeService.selectAdministrativeByWapper(archivesDO);
    }


    /**
     * 新增文书档案
     */
    @PostMapping("/create")
    @ApiOperation("新增文书档案")
    public CommonResult createArchives(@Valid @RequestBody AdministrativeDO archivesDO) {
        return administrativeService.createAdministrativeArchives(archivesDO);
    }


    /**
     * 修改文书档案
     */
    @PostMapping("/update")
    @ApiOperation("修改文书档案")
    public CommonResult updateArchives(@Valid @RequestBody AdministrativeDO archivesDO){
        return administrativeService.updateAdministrativeArchives(archivesDO);
    }


    /**
     * 逻辑删除文书档案
     */
    @DeleteMapping ("/logicDelete")
    @ApiOperation("逻辑删除文书档案")
    public CommonResult LogicDeleteArchives(@RequestParam("id") Integer id){
        return administrativeService.logicDeleteArchivesById(id);
    }


    /**
     * 分页查询
     */
    @GetMapping("/page")
    @ApiOperation("分页查询")
    public CommonResult getArchivesPage(@Valid @RequestBody AdministrativePageReqVO archivesPageReqVO){
        return administrativeService.selectPageByArchives(archivesPageReqVO);
    }


    //Excel导出
    @GetMapping("/export")
    @ApiOperation("导出文书档案")
    @OperateLog(type = EXPORT)
    public CommonResult exportAdministrative(HttpServletResponse response){
        return administrativeService.exportAdministrativeData(response);
    }


    @PostMapping("/import")
    @ApiOperation("导入用户")
    @ApiImplicitParams({@ApiImplicitParam(name = "file", value = "Excel 文件", required = true, dataTypeClass = MultipartFile.class)})
    public CommonResult importExcel(@RequestParam("file") MultipartFile file) throws Exception {

        //拿到 Excel文书数据 传给 业务层处理
        List<AdministrativeExcelVO> list =
                com.nb.easy.easyframe.framework.excel.core.util.ExcelUtils.read(file,AdministrativeExcelVO.class,1);
        //success 返回 data
        return success(administrativeService.importAdministrative(list));

    }

    @GetMapping("/selectByIds")
    @ApiOperation("id批量查询")
    public CommonResult selectAdministrativeById(@Valid String ids){
        return administrativeService.getArchivesByIds(ids);
    }

    @GetMapping("/selectByNos")
    @ApiOperation("档号批量查询")
    public CommonResult getArchivesByArchivesNo(@Valid String archivesNos){
        return administrativeService.getArchivesByArchivesNo(archivesNos);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

2.Service

package com.nb.easy.easyframe.module.archievs.service.administrative;

import com.nb.easy.easyframe.framework.common.pojo.CommonResult;
import com.nb.easy.easyframe.module.archievs.controller.admin.vo.administrative.AdministrativeExcelVO;
import com.nb.easy.easyframe.module.archievs.controller.vo.administrative.AdministrativePageReqVO;
import com.nb.easy.easyframe.module.archievs.dal.dataobject.administrative.AdministrativeDO;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.util.List;

/**
 * 

 * 文书档案表 服务类
 * 
 *
 * @author durunwu
 * @since 2022-07-06
 */
public interface AdministrativeService {


    /**
     * 测试
     */
    CommonResult selectAdministrativeArchives();

    /**
     * 文书档案 id查询
     */
    CommonResult selectAdministrativeById(Integer id);

    /**
     * 文书档案 条件查询
     */
    CommonResult selectAdministrativeByWapper(AdministrativeDO aDo);

    /**
     * 新增文书档案
     */
    CommonResult createAdministrativeArchives(@Valid AdministrativeDO archivesDO);

    /**
     * 修改文书档案
     */
    CommonResult updateAdministrativeArchives(@Valid AdministrativeDO archives);

    /**
     * 文书档案逻辑删除
     */
    CommonResult logicDeleteArchivesById(@Valid Integer id);

    /**
     * 文书档案分页查询
     */
    CommonResult selectPageByArchives(AdministrativePageReqVO archivesPageReqVO);

    //Excel导出
    CommonResult exportAdministrativeData(HttpServletResponse response);

    //Excel导入文书档案
    CommonResult importAdministrative(List<AdministrativeExcelVO> list);

    //id 批量查询
    CommonResult getArchivesByIds(String ids);

    //档号批量查询
    CommonResult getArchivesByArchivesNo(String archivesNos);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

3.ServiceImpl

package com.nb.easy.easyframe.module.archievs.service.administrative;


import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.nb.easy.easyframe.framework.common.pojo.CommonResult;
import com.nb.easy.easyframe.framework.common.pojo.PageResult;
import com.nb.easy.easyframe.module.archievs.controller.admin.vo.administrative.AdministrativeExcelVO;
import com.nb.easy.easyframe.module.archievs.controller.vo.administrative.AdministrativePageReqVO;
import com.nb.easy.easyframe.module.archievs.controller.vo.administrative.AmsShelvesArchivesVO;
import com.nb.easy.easyframe.module.archievs.dal.dataobject.administrative.AdministrativeDO;
import com.nb.easy.easyframe.module.archievs.dal.dataobject.archivesStorage.ArchivesStoragePageVo;
import com.nb.easy.easyframe.module.archievs.dal.mapper.administrative.AdministrativeMapperX;
import com.nb.easy.easyframe.module.archievs.dal.mapper.administrative.AmsShelvesMapperX;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.nb.easy.easyframe.framework.common.exception.util.ServiceExceptionUtil.exception;
import static com.nb.easy.easyframe.module.system.enums.ErrorCodeConstants.USER_IMPORT_LIST_IS_EMPTY;
import static com.nb.easy.easyframe.module.system.enums.ErrorCodeConstants.USER_USERNAME_EXISTS;

/**
 * 

 * 文书档案表 服务类
 * 
 *
 * @author durunwu
 * @since 2022-07-06
 */
@Service
public class AdministrativeServiceImpl implements AdministrativeService {

    @Resource
    private AdministrativeMapperX archivesMapper;

    @Resource
    private AmsShelvesMapperX shelvesMapper;

    private CommonResult commonResult = new CommonResult();

    /**
     * 测试
     * 获取全部文书档案信息
     */
    @Override
    public CommonResult selectAdministrativeArchives() {
        //查询文书表全部数据
        List<AdministrativeDO> amsAdministrativeArchivesDOList = archivesMapper.selectList();
        return commonResult.setMsg("查询成功").setData(amsAdministrativeArchivesDOList);
    }

    /**
     * 文书档案 id查询
     */
    @Override
    public CommonResult selectAdministrativeById(Integer id){
        if (id == 0){
            return commonResult.setMsg("id 不能为0");
        }
        AdministrativeDO aDo = archivesMapper.selectById(id);
        if (aDo.getDeleted() == '1') {
            //若 deleted = '1' 则不执行任何操作
            return commonResult.setMsg("档案已删除");
        }
        return commonResult.setMsg("文书档案查询成功").setData(aDo);
    }

    /**
     * 文书档案 条件查询
     */
    @Override
    public CommonResult selectAdministrativeByWapper(AdministrativeDO aDo){

        if (aDo == null){
            //查询条件为空返回全部数据
            selectAdministrativeArchives();
            return commonResult.setMsg("内容不能为空");
        }
        if (aDo.getDeleted() == '1') {
            //若 deleted = '1' 则不执行任何操作
            return commonResult.setMsg("档案已删除");
        }
        QueryWrapper<AdministrativeDO> queryWrapper = new QueryWrapper<>();
        if (aDo.getArchivesNo() != null) queryWrapper.eq("archives_no",aDo.getArchivesNo());
        if (aDo.getDataBarcode() != null) queryWrapper.eq("data_barcode",aDo.getDataBarcode());
        if (aDo.getAddrBarcode() != null) queryWrapper.eq("addr_barcode",aDo.getAddrBarcode());
        if (aDo.getBoxNo() != null) queryWrapper.eq("box_no",aDo.getBoxNo());
        if (aDo.getPartNo() != null) queryWrapper.eq("part_no",aDo.getPartNo());
        if (aDo.getDocNo() != null) queryWrapper.eq("doc_no",aDo.getDocNo());
        if (aDo.getDocTitle() != null) queryWrapper.eq("doc_title",aDo.getDocTitle());
        if (aDo.getContractSignatory() != null) queryWrapper.eq("contract_signatory",aDo.getContractSignatory());
        if (aDo.getOrganization() != null) queryWrapper.eq("organization",aDo.getOrganization());
        if (aDo.getRemarks() != null) queryWrapper.eq("remarks",aDo.getRemarks());
        if (aDo.getPersonResponsible() != null) queryWrapper.eq("person_responsible",aDo.getPersonResponsible());
        if (aDo.getRetentionPeriod() != null) queryWrapper.eq("retention_period",aDo.getRetentionPeriod());
        if (aDo.getPageNo() != null) queryWrapper.eq("page_no",aDo.getPageNo());
        if (aDo.getArchivesType() != null) queryWrapper.eq("archives_type",aDo.getArchivesType());
        if (aDo.getSignDate() != null) queryWrapper.eq("sign_date",aDo.getSignDate());
        if (aDo.getFilingYear() != null) queryWrapper.eq("filing_year",aDo.getFilingYear());
        if (aDo.getRetentionedPeriod() != null) queryWrapper.eq("retentioned_period",aDo.getRetentionedPeriod());
        if (aDo.getAmount() != null) queryWrapper.eq("amount",aDo.getAmount());
        if (aDo.getHoldOrg() != null) queryWrapper.eq("hold_org",aDo.getHoldOrg());
        if (aDo.getContent() != null) queryWrapper.eq("content",aDo.getContent());
        if (aDo.getArchivesStatus() != null) queryWrapper.eq("archives_status",aDo.getArchivesStatus());
        if (aDo.getAccessionDate() != null) queryWrapper.eq("accession_date",aDo.getAccessionDate());
        if (aDo.getUpdateDate() != null) queryWrapper.eq("update_date",aDo.getUpdateDate());
        if (aDo.getDeliveryDate() != null) queryWrapper.eq("delivery_date",aDo.getDeliveryDate());
        AdministrativeDO administrativeDO = archivesMapper.selectOne(queryWrapper);
        return commonResult.setMsg("文件档案条件查询成功").setData(administrativeDO);
    }


    //***已测通
    //文书档案新增
    @Override
    public CommonResult createAdministrativeArchives(AdministrativeDO archivesDO) {
        //id自增，不需要传，前端传值框处没有id

        //传入的文书档案实体类必须存在档号
        if ("".equals(archivesDO.getArchivesNo()) || archivesDO.getArchivesNo() == null || archivesDO.getArchivesNo().length() == 0){
            //StringUtils.hasLength(archivesDO.getArchivesNo())
            //StringUtils.hasLength()  str==null && str.length==0
            return commonResult.setMsg("档号未填写,无法添加");
        }

        //查询数据库没有该档号的档案，则进行添加操作
        //获取当前时间，赋值给入库时间accession_date
        //2022-08-01 09:04:16
        SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        Date date = null;
        try {
            date = df.parse(df.format(new Date()));
        } catch (ParseException e) {
            //打印堆栈跟踪，交给系统处理  printStackTrace(System.err);
            e.printStackTrace();
        }

        //根据传入的档号去数据库比对是否存在该档号的档案
        AdministrativeDO aDo = archivesMapper.selectOne(new QueryWrapper<AdministrativeDO>()
                .eq("archives_no", archivesDO.getArchivesNo()));
        //数据库存在该档号档案
        if (aDo != null){
            if (aDo.getDeleted() == '0') {
                //若 deleted = '1' 则不执行任何操作
                return commonResult.setMsg("档案已存在").setData(archivesDO.getArchivesNo());
            }
            //档案已删除，可添加
            try{
                if (aDo.getDeleted() == '1'){
                    archivesDO.setAccessionDate(date);
                    //执行添加操作
                    int i = archivesMapper.insert(archivesDO);
                    if (i == 0){
                        return commonResult.setMsg("档案添加失败").setData(archivesDO.getArchivesNo());
                    }
                    return commonResult.setMsg("档案添加成功").setData(archivesDO);
                }
            }catch (Exception e){//异常是因为id不能传值却传值了
                e.printStackTrace();
            }
        }
        //若数据库查询不到该数据则添加
        archivesDO.setAccessionDate(date);
        //执行添加操作
        int i = archivesMapper.insert(archivesDO);
        if (i == 0){
            return commonResult.setMsg("档案添加失败").setData(archivesDO.getArchivesNo());
        }
        //添加成功返回id
        return commonResult.setMsg("档案添加成功").setData(archivesDO.getArchivesNo());
    }


    //***已测通
    //文书档案修改
    @Override
    public CommonResult updateAdministrativeArchives(AdministrativeDO archivesDO) {

        if (archivesDO.getId() == null || archivesDO.getArchivesNo() == null){
            return commonResult.setMsg("档案Id或档号未填写");
        }
        //若该档案存在就执行修改操作
        AdministrativeDO aDo = archivesMapper.selectOne(new QueryWrapper<AdministrativeDO>()
                .eq("id", archivesDO.getId())
                .eq("archives_no", archivesDO.getArchivesNo()));
        //如果查询档案存在，则进行修改操作
        if (aDo != null) {
            if (aDo.getDeleted() == '1') {
                //若 deleted = '1' 则不执行任何操作，返回档案ID
                return commonResult.setMsg("档案已删除");
            }
            SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
            Date date = null;
            try {
                date = df.parse(df.format(new Date()));
            } catch (ParseException e) {
                //打印堆栈跟踪，交给系统处理 System.err
                e.printStackTrace();
            }
            //配置跟新时间
            archivesDO.setUpdateDate(date);
            //执行修改操作
            int update = archivesMapper.update(archivesDO,
                    new QueryWrapper<AdministrativeDO>().eq("id", archivesDO.getId()));
            if (update == 1){
                return commonResult.setMsg("档案修改成功").setData(archivesDO.getId());
            }
        }
        return commonResult.setMsg("档案修改失败").setData(archivesDO.getId());
    }


    //***已测通
    /**
     * 文书档案逻辑删除
     * 默认为 0
     * 删除为 1
     */
    @Override
    public CommonResult logicDeleteArchivesById(Integer id) {

        if (id == 0){
            return commonResult.setMsg("id 不能为0");
        }
        AdministrativeDO aDO = (AdministrativeDO)selectAdministrativeById(id).getData();
        //检测档案是否存在
        if (aDO == null) {
            return commonResult.setMsg("档案不存在").setData(id);
        }
        //若 deleted = '0' 则执行逻辑删除
        if (aDO.getDeleted() == '1') {
            //若 deleted = '1' 则不执行任何操作，返回档案ID
            return commonResult.setMsg("档案已删除");
        }

        SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        Date date = null;
        try {
            date = df.parse(df.format(new Date()));
        } catch (ParseException e) {
            //打印堆栈跟踪，交给系统处理 System.err
            e.printStackTrace();
        }
        //配置跟新时间
        aDO.setUpdateDate(date);
        //执行逻辑删除操作
        // MybatisPlus框架根据实体类@TableLogic注解 在进行删除时自动 update set isDeleted=1
        // 此处不需要手动 set为1，且手动无效
        archivesMapper.deleteById(id);
        return commonResult.setMsg("档案逻辑删除成功").setData(id);
    }


    //分页查询
    @Override
    public CommonResult selectPageByArchives(AdministrativePageReqVO archivesPageReqVO) {

        if (archivesPageReqVO != null) {
            //执行查询
            PageResult<List<ArchivesStoragePageVo>> pageResult =
                    archivesMapper.selectAdministrativeByDeleted(archivesPageReqVO);
            return commonResult.setMsg("分页获取成功").setData(pageResult);
        }
        return commonResult.setMsg("分页获取失败").setData(null);
    }


    //***已测通
    //@GetMapping("/export")
    //导出 Excel
    @Override
    public CommonResult exportAdministrativeData(HttpServletResponse response) {

        //TODO : 配置下载信息
        //设置Content类型为 excel
        response.setContentType("application/vnd.ms-excel");
        //设置编码
        response.setCharacterEncoding("utf-8");
        //设置导出文件名
        String fileName = null;

        try {
            //使用google浏览器下载无误 文书档案.xls，使用postman测试文件名会乱码
            fileName = URLEncoder.encode("文书档案", "utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        //设置头信息及文件后缀名，并让此操作以下载方式打开
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");

        //查询数据库 文书档案表ams_administrative_archives的全部数据
        List<AdministrativeDO> administrativeDOList = archivesMapper.selectList(null);

        //校验
        if (administrativeDOList.size()==0){return null;}

        // TODO: 2022/7/29  拿出 administrativeDOList中所有的archives_id,以及archives_type作为查询条件
        List<Integer> ids=new ArrayList<>();
        for (AdministrativeDO administrativeDO : administrativeDOList) {
            ids.add(administrativeDO.getId());
        }
        //根据条件查询上架表ams_shelves_archives的全部数据
        List<AmsShelvesArchivesVO> amsShelvesArchivesVOList = shelvesMapper.selectList(new QueryWrapper<AmsShelvesArchivesVO>()
                        .in("archives_id",ids)
                        .eq("archives_type",administrativeDOList.get(0).getArchivesType()));//get(0)
        List<AdministrativeExcelVO> excelVOList = new ArrayList<>();

        //把 文书表和上架表的部分数据添加到 excel 实体类中
        for (AdministrativeDO administrativeDO : administrativeDOList) {
            AdministrativeExcelVO excelVO = new AdministrativeExcelVO();
            //BeanUtils.copyProperties(a,b) 把 a中对应的字段拷贝到 b中
            BeanUtils.copyProperties(administrativeDO, excelVO);
            for (AmsShelvesArchivesVO archivesVO : amsShelvesArchivesVOList) {
                //根据文书表和上架表的关联字段二次插入数据
                if (administrativeDO.getId() == archivesVO.getArchivesId()) {
                    BeanUtils.copyProperties(archivesVO, excelVO);
                }
            }
            //把 excel 实体类添加到 excel的 list中，等待写出
            excelVOList.add(excelVO);
        }

        //写出文书档案
        try {
            EasyExcel.write(response.getOutputStream(), AdministrativeExcelVO.class)
                    .sheet("文书档案")
                    .doWrite(excelVOList);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return commonResult.setMsg("文书档案Excel导出成功");
    }


    //***已测通
    //Excel导入
    @Override
    @Transactional(rollbackFor = Exception.class) // 添加事务，异常则回滚所有导入
    public CommonResult importAdministrative(List<AdministrativeExcelVO> importAdministratives) {

        //判断传入的参数是否为空，为空则抛出异常码 new ErrorCode(1002003004, "导入用户数据不能为空！");
        if (CollUtil.isEmpty(importAdministratives)) {
            throw exception(USER_IMPORT_LIST_IS_EMPTY);
        }

        /**
         * 策略：
         * 1.先插入文书档案表的信息
         * 2.从文书档案获取关联字段
         * 3.根据关联字段再插入档案上架表信息
         */

        importAdministratives.forEach(administrativeExcelVO -> {

            /**
             * 此处为啥不使用 api简便操作呢？
             * 1.因为 BeanUtils.copyProperties() 会自动拷贝字段同名的数据，但是表中存在不同名的字段需要迁移和一些非空字段的赋值
             * 2.在 get、set传值的同时也能校验数据的类型是否一致
             */

            //每次读取的内容 administrativeExcelVO
            AdministrativeDO administrativeDO = new AdministrativeDO();

            //把 AdministrativeExcelVO实体类是数据迁移到 对应的实体类进行添加
            //文书档案表 ams_administrative_archives
            administrativeDO
                    .setAddrBarcode(administrativeExcelVO.getAddrBarcode()).setDataBarcode(administrativeExcelVO.getDataBarcode())
                    .setBoxNo(administrativeExcelVO.getBoxNo()).setPartNo(administrativeExcelVO.getPartNo())
                    .setDocNo(administrativeExcelVO.getDocNo()).setPersonResponsible(administrativeExcelVO.getPersonResponsible())
                    .setDocTitle(administrativeExcelVO.getDocTitle()).setOrganization(administrativeExcelVO.getOrganization())
                    .setRetentionPeriod(administrativeExcelVO.getRetentionPeriod()).setSignDate(administrativeExcelVO.getSignDate())
                    .setPartNo(administrativeExcelVO.getPartNo()).setFilingYear(administrativeExcelVO.getFilingYear())
                    .setArchivesType(administrativeExcelVO.getArchivesType()).setRemarks(administrativeExcelVO.getRemarks())
                    .setArchivesNo(administrativeExcelVO.getArchivesNo()).setContractSignatory(administrativeExcelVO.getContractSignatory())
                    .setAmount(administrativeExcelVO.getAmount()).setHoldOrg(administrativeExcelVO.getHoldOrg())
                    .setContent(administrativeExcelVO.getContent());

            //插入数据到 ams_administrative_archives
            //这里直接调用上面已经写好的 文书档案新增方法，方法中已经校验就不再外部校验
            CommonResult result = createAdministrativeArchives(administrativeDO);
            System.out.println(result.getMsg());

            //获取 id跟档号
            AdministrativeDO aDo = archivesMapper.selectOne(new QueryWrapper<AdministrativeDO>()
                    .eq("addr_barcode", administrativeExcelVO.getAddrBarcode())
                    .eq("data_barcode", administrativeExcelVO.getDataBarcode())
                    .eq("box_no", administrativeExcelVO.getBoxNo()));

            //档案位置表  ams_shelves_archives
            AmsShelvesArchivesVO amsShelvesArchivesVO = new AmsShelvesArchivesVO();

            //ams_administrative_archives表的字段id == ams_shelves_archives表的 archives_id
            //ams_administrative_archives表id自增，
            amsShelvesArchivesVO.setArchivesId(aDo.getId());
            amsShelvesArchivesVO.setArchivesNo(administrativeExcelVO.getArchivesNo());
            amsShelvesArchivesVO.setArchivesType(administrativeExcelVO.getArchivesType());
            amsShelvesArchivesVO.setAreaNo(administrativeExcelVO.getAreaNo());
            amsShelvesArchivesVO.setColumnNo(administrativeExcelVO.getColumnNo());
            amsShelvesArchivesVO.setRowNo(administrativeExcelVO.getRowNo());
            amsShelvesArchivesVO.setSectionNo(administrativeExcelVO.getSectionNo());
            amsShelvesArchivesVO.setLayerNo(administrativeExcelVO.getLayerNo());

            //添加查询条件
            AmsShelvesArchivesVO archivesVO = shelvesMapper.selectOne(new QueryWrapper<AmsShelvesArchivesVO>()
                    .eq("archives_id", aDo.getId())
                    .eq("archives_no", amsShelvesArchivesVO.getArchivesNo())
                    .eq("archives_type", administrativeDO.getArchivesType()));
            if (archivesVO != null) {//ams_shelves_archives 已存在该数据
                throw exception(USER_USERNAME_EXISTS);
            }
            插入数据到 ams_shelves_archives  插入数据为 区号、列号、排号、节数、层数
            shelvesMapper.insert(amsShelvesArchivesVO);
        });
        return commonResult.setMsg("文书档案Excel导入成功");
    }


    /**
     * 批量查询
     * 根据文书档案——id（单值/多值）查询单条档案记录
     */
    @Override
    public CommonResult<List<AdministrativeDO>> getArchivesByIds(String ids) {//id1,id2,id3...
        //判断是否为空串或长度是否等于0
        if ("".equals(ids) || ids.length() == 0) {
            return commonResult.setMsg("id不能为空");
        }
        String[] split = ids.split(",");
        List<AdministrativeDO> id = archivesMapper.selectList(new QueryWrapper<AdministrativeDO>().in("id", split));
        return commonResult.setData(id).setMsg("批量查询成功");
    }


    /**
     * 根据文书档案——档号（单值/多值）查询单条档案记录
     */
    @Override
    public CommonResult getArchivesByArchivesNo(String archivesNos) {

        if ("".equals(archivesNos) || archivesNos.length() == 0) {
            return commonResult.setMsg("id不能为空");
        }
        //判断是否为空串或长度是否等于0
        String[] split = archivesNos.split(",");
        List<AdministrativeDO> aNo = archivesMapper.selectList(new QueryWrapper<AdministrativeDO>().in("archives_no", split));
        return commonResult.setData(aNo).setMsg("批量查询成功");
    }


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466

4.Mapper

package com.nb.easy.easyframe.module.archievs.dal.mapper.administrative;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.nb.easy.easyframe.framework.common.pojo.PageResult;
import com.nb.easy.easyframe.framework.mybatis.core.mapper.BaseMapperX;
import com.nb.easy.easyframe.framework.mybatis.core.query.QueryWrapperX;
import com.nb.easy.easyframe.module.archievs.controller.vo.administrative.AdministrativePageReqVO;
import com.nb.easy.easyframe.module.archievs.dal.dataobject.administrative.AdministrativeDO;
import com.nb.easy.easyframe.module.archievs.dal.dataobject.archivesStorage.ArchivesStoragePageVo;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;


/**
 * 

 * 文书档案表 Mapper 接口
 * 
 *
 * @author Zhangzx
 * @since 2022-07-06
 */
@Mapper
public interface AdministrativeMapperX extends BaseMapperX<AdministrativeDO> {

    /**
     * 分页查询 文书档案列表（根据deleted状态）
     */
    default PageResult<List<ArchivesStoragePageVo>> selectAdministrativeByDeleted(AdministrativePageReqVO administrativeDO){
        QueryWrapperX queryWrapperX = new QueryWrapperX<>();

        if (administrativeDO.getDeleted()==null || administrativeDO.getDeleted().equals("")){
            queryWrapperX.eq("deleted","0");
        }else {
            queryWrapperX.eq("delete",administrativeDO.getDeleted());
        }

        queryWrapperX.eqIfPresent("id",administrativeDO.getId());
        queryWrapperX.eqIfPresent("archives_no",administrativeDO.getArchivesNo());

        return selectPage(administrativeDO,queryWrapperX);

    }

    //
    default AdministrativeDO selectByArchivesNo(String archivesNo) {
         return selectOne(new LambdaQueryWrapper<AdministrativeDO>().eq(AdministrativeDO::getArchivesNo, archivesNo));
    }




}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

package com.nb.easy.easyframe.module.archievs.dal.mapper.administrative;

import com.nb.easy.easyframe.framework.mybatis.core.mapper.BaseMapperX;
import com.nb.easy.easyframe.module.archievs.controller.vo.administrative.AmsShelvesArchivesVO;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface AmsShelvesMapperX extends BaseMapperX<AmsShelvesArchivesVO> {
}

1
2
3
4
5
6
7
8
9
10

5.Do

package com.nb.easy.easyframe.module.archievs.dal.dataobject.administrative;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;


/**
 * 

 * 文书档案表
 * 
 *
 * @author durunwu
 * @since 2022-07-06
 */
@TableName("ams_administrative_archives")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class AdministrativeDO implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 档案ID
     */
    @TableId("id")
    @ApiModelProperty(value = "ID",required = true)
    private Integer id;

    /**
     * 档号
     */
    @ApiModelProperty(value = "档号",required = true)
    private String archivesNo;

    /**
     * 资料条码
     */
    @ApiModelProperty(value = "资料条码")
    private String dataBarcode;

    /**
     * 地址条码
     */
    @ApiModelProperty(value = "地址条码")
    private String addrBarcode;

    /**
     * 盒号
     */
    @ApiModelProperty(value = "盒号")
    private String boxNo;

    /**
     * 件号
     */
    @ApiModelProperty(value = "件号")
    private String partNo;

    /**
     * 文件/合同编号
     */
    @ApiModelProperty(value = "文件/合同编号")
    private String docNo;

    /**
     * 文件题名/名称
     */
    @ApiModelProperty(value = "文件题名/名称")
    private String docTitle;

    /**
     * 合同签订方
     */
    @ApiModelProperty(value = "合同签订方")
    private String contractSignatory;

    /**
     * 机构（问题）
     */
    @ApiModelProperty(value = "机构（问题）")
    private String organization;

    /**
     * 备注
     */
    @ApiModelProperty(value = "备注")
    private String remarks;

    /**
     * 责任人
     */
    @ApiModelProperty(value = "责任人")
    private String personResponsible;

    /**
     * 保管期限
     */
    @ApiModelProperty(value = "保管期限")
    private Integer retentionPeriod;

    /**
     * 页数
     */
    @ApiModelProperty(value = "页数")
    private Integer pageNo;

    /**
     * 档案类型
     */
    @ApiModelProperty(value = "档案类型")
    private String archivesType;

    /**
     * 签订日期
     */
    @ApiModelProperty(value = "签订日期")
    private Date signDate;

    /**
     *  归档年度
     */
    @ApiModelProperty(value = "归档年度")
    private String filingYear;

    /**
     * 已保管期限
     */
    @ApiModelProperty(value = "已保管期限")
    private String retentionedPeriod;

    /**
     * 金额
     */
    @ApiModelProperty(value = "金额")
    private String amount;

    /**
     * 承办部门
     */
    @ApiModelProperty(value = "承办部门")
    private String holdOrg;

    /**
     * 主要内容
     */
    @ApiModelProperty(value = "主要内容")
    private String content;

    /**
     * 档案状态
     */
    @ApiModelProperty(value = "档案状态")
    private String archivesStatus;

    /**
     * 入库时间
     */
    @ApiModelProperty(value = "入库时间")
    private Date accessionDate;

    /**
     * 更新时间
     */
    @ApiModelProperty(value = "更新时间")
    private Date updateDate;

    /**
     * 出库时间
     */
    @ApiModelProperty(value = "出库时间")
    private Date deliveryDate;

    /**
     * 逻辑删除的字段,1表示删除,0表示未删除
     */
    @TableLogic
    @ApiModelProperty(value = "逻辑删除标志")
    private Character deleted;


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191

6.ExcelVo

package com.nb.easy.easyframe.module.archievs.controller.admin.vo.administrative;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.util.Date;

@ApiModel("文书档案 Excel VO")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false) // 设置 chain = false，避免用户导入有问题
public class AdministrativeExcelVO {

    //文书档案表 ams_administrative_archives

    /**
     * 地址条码
     * @ExcelProperty 配置 execl表头名称
     */
    @ExcelProperty("地址条码")
    private String addrBarcode;

    /**
     * 资料条码
     */
    @ExcelProperty("资料条码")
    private String dataBarcode;

    /**
     * 盒号
     */
    @ExcelProperty("盒号")
    private String boxNo;

    /**
     * 件号
     */
    @ExcelProperty("件号")
    private String partNo;

    /**
     * 文件编号
     */
    @ExcelProperty("文件编号")
    private String docNo;

    /**
     * 责任者
     */
    @ExcelProperty("责任者")
    private String personResponsible;

    /**
     * 文件题名/名称
     */
    @ExcelProperty("文件题名/名称")
    private String docTitle;

    /**
     * 机构（问题）
     */
    @ExcelProperty("机构（问题）")
    private String organization;

    /**
     * 保管期限
     */
    @ExcelProperty("保管期限")
    private Integer retentionPeriod;

    /**
     * 成文/签订日期
     */
    @ExcelProperty("成文/签订日期")
    private Date signDate;

    /**
     * 页数
     */
    @ExcelProperty("页数")
    private Integer pageNo;

    /**
     * 归档年度
     */
    @ExcelProperty("归档年度")
    private String filingYear;

    /**
     * 档案类别
     */
    @ExcelProperty("档案类别")
    private String archivesType;

    /**
     *  备注
     */
    @ExcelProperty("备注")
    private String remarks;

    /**
     * 档号
     */
    @ExcelProperty(value = "档号")
    private String archivesNo;

    /**
     * 合同签订方
     */
    @ExcelProperty("合同签订方")
    private String contractSignatory;

    /**
     * 金额
     */
    @ExcelProperty("金额")
    private String amount;

    /**
     * 承办部门
     */
    @ExcelProperty("承办部门")
    private String holdOrg;

    /**
     * 主要内容
     */
    @ExcelProperty("主要内容")
    private String content;


    //档案位置表  ams_shelves_archives 关联字段 档案id

    /**
     * 区号
     */
    @ExcelProperty("区号")
    private String areaNo;

    /**
     * 列号
     */
    @ExcelProperty("列号")
    private String columnNo;

    /**
     * 排号
     */
    @ExcelProperty("排号")
    private String rowNo;

    /**
     * 节号
     */
    @ExcelProperty("节号")
    private String sectionNo;

    /**
     * 层号
     */
    @ExcelProperty("层号")
    private String layerNo;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172