kcim-cost/src/main/java/com/imed/costaccount/web/ExcelController.java

package com.imed.costaccount.web;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.imed.costaccount.common.exception.CostException;
import com.imed.costaccount.common.util.Result;
import com.imed.costaccount.common.util.UserContext;
import com.imed.costaccount.constants.NumberConstant;
import com.imed.costaccount.model.*;
import com.imed.costaccount.service.*;
import com.imed.costaccount.service.impl.DepartmentServiceImpl;
import com.imed.costaccount.service.impl.ProductServiceImpl;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.shiro.SecurityUtils;
import org.jetbrains.annotations.NotNull;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 相关导入导出操作
 */
@Slf4j
@Api(tags = "excel导入导出")
@RestController
@RequestMapping("/costAccount/excel")
public class ExcelController {

    private final UserService userService;

    private final DepartmentServiceImpl departmentService;

    private final ProductServiceImpl productService;

    private final AccountingService accountingService;

    private final AccountingProductService accountingProductService;

    private final ResponsibilityDepartmentService responsibilityDepartmentService;

    private final CostShareParamService costShareParamService;

    private final CostIncomeGroupService costIncomeGroupService;


    public ExcelController(UserService userService, DepartmentServiceImpl departmentService, ProductServiceImpl productService, AccountingService accountingService, AccountingProductService accountingProductService, ResponsibilityDepartmentService responsibilityDepartmentService, CostShareParamService costShareParamService, CostIncomeGroupService costIncomeGroupService) {
        this.userService = userService;
        this.departmentService = departmentService;
        this.productService = productService;
        this.accountingService = accountingService;
        this.accountingProductService = accountingProductService;
        this.responsibilityDepartmentService = responsibilityDepartmentService;
        this.costShareParamService = costShareParamService;
        this.costIncomeGroupService = costIncomeGroupService;
    }

    @ApiOperation("用户导出模板设置")
    @GetMapping("/getcurrentTemplate")
    public void getImportUserTemplate(HttpServletResponse response) throws IOException {
        User user = (User) SecurityUtils.getSubject().getPrincipal();
        // TODO: 2021/7/26 暂时没有登录
        String uuid = UUID.randomUUID().toString();
        String url = System.getProperty("java.io.tmpdir") + File.separator + uuid + File.separator + uuid + ".xls";
        FileUtil.del(FileUtil.file(url));

        ExcelWriter writer = new ExcelWriter(url);
        // 样式
        Sheet sheet = writer.getSheet();
        // 内容
        writer.merge(0, 1, 0, 4, "为了保证成功导入，请勿修改模板格式", false);
        writer.passCurrentRow();
        writer.passCurrentRow();
        writer.merge(2, 2, 0, 4, "测试医院用户导入", false);
        writer.passCurrentRow();
        writer.writeRow(Arrays.asList("院区","姓名", "工号", "密码","手机号码"));

        // 写入响应
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + uuid + ".xls");
        ServletOutputStream out = null;

        out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }

    @PostMapping("/importUser")
    @ApiOperation("导入用户")
    public Result importUser(@RequestParam("file") MultipartFile file) {
        InputStream in;
        try {
            in = file.getInputStream();
            ExcelReader reader = ExcelUtil.getReader(in);
            List<List<Object>> read = reader.read();
            log.info("最开始：read={}",read);
            log.info("-------------------------------假装我是个分割线------------------------------------");
            User user = (User) SecurityUtils.getSubject().getPrincipal();
            return userService.importUser(read, user);
        } catch (IOException e) {
            e.printStackTrace();
            throw new CostException(500, "导入失败");
        }
    }
    /**
     * 科室模板导出功能设置
     */
    @ApiOperation("科室导出模板设置")
    @GetMapping("/getDepartmentTemplate")
    public void getImportDepartmentTemplate(HttpServletResponse response) throws IOException {
        User user = (User) SecurityUtils.getSubject().getPrincipal();
        String uuid = UUID.randomUUID().toString();
        String url = System.getProperty("java.io.tmpdir") + File.separator + uuid + File.separator + uuid + ".xls";
        FileUtil.del(FileUtil.file(url));

        ExcelWriter writer = new ExcelWriter(url);
        // 样式
        Sheet sheet = writer.getSheet();
        // 内容
        writer.merge(0, 1, 0, 2, "为了保证成功导入，请勿修改模板格式", false);
        writer.passCurrentRow();
        writer.passCurrentRow();
        writer.merge(2, 2, 0, 2, "医院科室批量导入", false);
        writer.passCurrentRow();
        writer.setColumnWidth(0,20);
        writer.setColumnWidth(1,15);
        writer.setColumnWidth(2,15);
        writer.writeRow(Arrays.asList("院区","科室名称", "科室代码"));

        // 写入响应
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + uuid + ".xls");
        ServletOutputStream out = null;

        out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }
    /**
     * 批量导入科室信息
     */
    @PostMapping("/importDepartment")
    @ApiOperation("导入科室信息")
    public Result importDepartment(@RequestParam("file") MultipartFile file){
        InputStream in;
        try {
            in = file.getInputStream();
            ExcelReader reader = ExcelUtil.getReader(in);
            List<List<Object>> read = reader.read();
            log.info("最开始：read={}",read);
            log.info("-------------------------------------------------------------------");
            User user = (User) SecurityUtils.getSubject().getPrincipal();
            Long hospId = user.getHospId();
            return departmentService.importDepartment(read, hospId);
        }catch (IOException e){
            e.printStackTrace();;
            throw new CostException(500, "导入失败");
        }
    }
    /**
     *  收入成本项目批量导入模板
     */
    @ApiOperation("成本项目导出模板设置")
    @GetMapping("/getImportProductTemplate")
    public void getImportProductTemplate(HttpServletResponse response) throws IOException {
        User user = (User) SecurityUtils.getSubject().getPrincipal();
        String uuid = UUID.randomUUID().toString();
        String url = System.getProperty("java.io.tmpdir") + File.separator + uuid + File.separator + uuid + ".xls";
        FileUtil.del(FileUtil.file(url));

        ExcelWriter writer = new ExcelWriter(url);
        // 样式
        Sheet sheet = writer.getSheet();
        // 内容
        writer.merge(0, 1, 0, 2, "为了保证成功导入，请勿修改模板格式", false);
        writer.passCurrentRow();
        writer.passCurrentRow();
        writer.merge(2, 2, 0, 2, "医院成本收入批量导入", false);
        writer.passCurrentRow();
        writer.setColumnWidth(0,20);
        writer.setColumnWidth(1,15);
        writer.setColumnWidth(2,15);
        writer.writeRow(Arrays.asList("院区","成本项目名", "成本项目编号"));

        // 写入响应
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + uuid + ".xls");
        ServletOutputStream out = null;

        out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }
    /**
     *  收入成本项目批量导入
     */
    @PostMapping("/importProduct")
    @ApiOperation("批量导入成本项目信息")
    public Result importProduct(@RequestParam("file") MultipartFile file){
        InputStream in;
        try {
            in = file.getInputStream();
            ExcelReader reader = ExcelUtil.getReader(in);
            List<List<Object>> read = reader.read();
            log.info("最开始：read={}",read);
            log.info("-------------------------------------------------------------------");
            User user = (User) SecurityUtils.getSubject().getPrincipal();
            Long hospId = user.getHospId();
            return productService.importProduct(read, hospId);
        }catch (IOException e){
            e.printStackTrace();;
            throw new CostException(500, "导入失败");
        }
    }
    /**
     * 收入成本数据导出模板
     */
    @ApiOperation("收入数据导出模板设置")
    @GetMapping("/getImportIncomeProductAccountTemplate")
    public void getImportProductAccountTemplate(HttpServletResponse response) throws IOException {
        Long hospId = UserContext.getHospId();
        String uuid = UUID.randomUUID().toString();
        String url = System.getProperty("java.io.tmpdir") + File.separator + uuid + File.separator + uuid + ".xls";
        FileUtil.del(FileUtil.file(url));

        ExcelWriter writer = new ExcelWriter(url);
        // 样式
        Sheet sheet = writer.getSheet();
        // 内容
        writer.merge(0, 1, 0, 6, "为了保证成功导入，请勿修改模板格式", false);
        writer.passCurrentRow();
        writer.passCurrentRow();
        writer.merge(2, 2, 0, 6, "医院收入数据批量导入", false);
        writer.passCurrentRow();
        // 冻结前四行
        writer.setFreezePane(4);
        writer.writeRow(Arrays.asList("成本项目代码","成本项目名称","开单科室","开单科室代码", "执行科室","执行科室代码","金额"));
        int accountType=NumberConstant.ONE;
        int column=NumberConstant.FOUR;
        getProductByAccountType(hospId, writer,accountType,column);
        writer.setColumnWidth(0,20);
        writer.setColumnWidth(1,20);
        writer.setColumnWidth(2,20);
        writer.setColumnWidth(3,20);
        writer.setColumnWidth(4,20);
        writer.setColumnWidth(5,20);
        writer.setColumnWidth(6,20);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + uuid + ".xls");
        ServletOutputStream out = null;
        out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }
    /**
     * 成本数据导出模板
     */
    @GetMapping("/getImportCostProductAccountTemplate")
    @ApiOperation("成本数据导出模板设置")
    public void getImportCostProductAccountTemplate(HttpServletResponse response) throws IOException {
        Long hospId = UserContext.getHospId();
        String uuid = UUID.randomUUID().toString();
        String url = System.getProperty("java.io.tmpdir") + File.separator + uuid + File.separator + uuid + ".xls";
        FileUtil.del(FileUtil.file(url));

        ExcelWriter writer = new ExcelWriter(url);
        // 样式
        Sheet sheet = writer.getSheet();
        writer.merge(0, 1, 0, 6, "为了保证成功导入，请勿修改模板格式", false);
        writer.passCurrentRow();
        writer.passCurrentRow();
        writer.merge(2, 2, 0, 6, "医院成本数据批量导入", false);
        writer.passCurrentRow();
        // 所有科室里面对应存在责任中心对应的科室
        List<Department> departmentLinkedList = getDepartments(hospId);
        // 设置科室的代码集合
        List<String> departmentCodeList = departmentLinkedList.stream().map(Department::getDepartmentCode).collect(Collectors.toList());
        List<String> departmentNameList = departmentLinkedList.stream().map(Department::getDepartmentName).collect(Collectors.toList());
        departmentCodeList.add(0,null);
        departmentCodeList.add(1,null);
        writer.writeRow(departmentCodeList);
        departmentNameList.add(0,"成本项目代码");
        departmentNameList.add(1,"成本项目名称");
        writer.writeRow(departmentNameList);
        // 设置科室名称的集合
        writer.setFreezePane(5);
        int accountType=NumberConstant.TWO;
        int column=NumberConstant.FIVE;
        getProductByAccountType(hospId, writer,accountType,column);
        writer.setColumnWidth(0,15);
        writer.setColumnWidth(1,15);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + uuid + ".xls");
        ServletOutputStream out = null;
        out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }
    /**
     * 获取执行类型的成本项目
     * @param hospId 医院Id
     * @param writer 操作流
     * @param accountType 会计科目类型 1 收入  2支出(成本)
     * @param column 控制第几列
     */
    private void getProductByAccountType(Long hospId, ExcelWriter writer,Integer accountType,Integer column) {
        // 所有的成本项目
        List<Product> productList = productService.list(new QueryWrapper<Product>().lambda().eq(Product::getHospId, hospId));
        // 所有成本会计对照数据
        List<AccountingProduct> accountingProductList = accountingProductService.list(new QueryWrapper<AccountingProduct>().lambda().eq(AccountingProduct::getHospId, hospId));
        // 所有会计科目列表数据
        List<Accounting> accountingList = accountingService.list(new QueryWrapper<Accounting>().lambda().eq(Accounting::getHospId, hospId));
        List<Product> products = new ArrayList<>();
        Map<Long, List<AccountingProduct>> accountProductMap = accountingProductList.stream().collect(Collectors.groupingBy(AccountingProduct::getProductId));
        Map<Long, List<Accounting>> accountMap = accountingList.stream().collect(Collectors.groupingBy(Accounting::getId));
        productList.forEach(i->{
            Long productId = i.getId();
            List<AccountingProduct> accountingProducts = accountProductMap.get(productId);
            if (CollUtil.isNotEmpty(accountingProducts)){
                Long accountingId = accountingProducts.get(0).getAccountingId();
                List<Accounting> accountings = accountMap.get(accountingId);
                if (CollUtil.isNotEmpty(accountings) && accountType.equals(accountings.get(0).getAccountingType())){
                    products.add(i);
                }
            }
        });
        // 写入响应第一列 第二列的数据
        for (int j = 0; j<products.size(); j++){
            writer.writeCellValue(0,j+column,productList.get(j).getProductCode());
            writer.writeCellValue(1,j+column,productList.get(j).getProductName());
        }
    }
    /**
     * 成本分摊参数导出模板
     */
    @GetMapping("/getShareParamTemplate")
    @ApiOperation("成本分摊参数导出模板")
    public void getShareParamTemplate(HttpServletResponse response) throws IOException{
        Long hospId = UserContext.getHospId();
        String uuid = UUID.randomUUID().toString();
        String url = System.getProperty("java.io.tmpdir") + File.separator + uuid + File.separator + uuid + ".xls";
        FileUtil.del(FileUtil.file(url));

        ExcelWriter writer = new ExcelWriter(url);
        // 样式
        Sheet sheet = writer.getSheet();
        writer.merge(0, 1, 0, 6, "为了保证成功导入，请勿修改模板格式", false);
        writer.passCurrentRow();
        writer.passCurrentRow();
        writer.merge(2, 2, 0, 6, "医院成本分摊参数数据批量导入", false);
        writer.passCurrentRow();
        // 所有科室里面对应存在责任中心对应的科室进行显示
        List<Department> departmentLinkedList = getDepartments(hospId);
        // 设置科室的代码集合
        List<String> departmentCodeList = departmentLinkedList.stream().map(Department::getDepartmentCode).collect(Collectors.toList());
        List<String> departmentNameList = departmentLinkedList.stream().map(Department::getDepartmentName).collect(Collectors.toList());
        departmentCodeList.add(0,null);
        departmentCodeList.add(1,null);
        writer.writeRow(departmentCodeList);
        departmentNameList.add(0,"成本分摊参数代码");
        departmentNameList.add(1,"成本分摊参数名称");
        writer.writeRow(departmentNameList);
        // 设置科室名称的集合
        writer.setFreezePane(5);
        // 为第一列和第二列设置成本分摊参数的列表数据
        List<CostShareParam> costShareParamList = costShareParamService.list(new QueryWrapper<CostShareParam>().lambda().eq(CostShareParam::getHospId, hospId));
        for (int j = 0; j<costShareParamList.size(); j++){
            writer.writeCellValue(0,j+5,costShareParamList.get(j).getShareParamCode());
            writer.writeCellValue(1,j+5,costShareParamList.get(j).getShareParamName());
        }
        writer.setColumnWidth(0,15);
        writer.setColumnWidth(1,15);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + uuid + ".xls");
        ServletOutputStream out = null;
        out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }

    @NotNull
    private List<Department> getDepartments(Long hospId) {
        List<Department> departmentLinkedList = new LinkedList<>();
        List<Department> departmentList = departmentService.list(new QueryWrapper<Department>().lambda().eq(Department::getHospId, hospId).orderByDesc(Department::getCreateTime));
        List<ResponsibilityDepartment> responsibilityDepartmentList = responsibilityDepartmentService.list(new QueryWrapper<ResponsibilityDepartment>().lambda()
                .eq(ResponsibilityDepartment::getHospId, hospId));
        Map<Long, List<ResponsibilityDepartment>> responsibilityDepartmentMap = responsibilityDepartmentList.stream().collect(Collectors.groupingBy(ResponsibilityDepartment::getDepartmentId));
        departmentList.forEach(i -> {
            Long id = i.getId();
            if (!CollectionUtils.isEmpty(responsibilityDepartmentMap.get(id))) {
                // TODO 暂时先不考虑关联的责任中心是否存在
                departmentLinkedList.add(i);
            }
        });
        return departmentLinkedList;
    }
    /**
     *  导入 收入数据导入
     * @param file 导入的文件
     */
    @PostMapping("/importDataByFileType")
    @ApiOperation("批量导入指定类型数据信息")
    public Result importProductAccount(@RequestParam("file") MultipartFile file,Integer year,Integer month){
        if (Objects.isNull(file)){
            throw new CostException(500,"请选择文件");
        }
        InputStream in;
        // 导入的是收入数据
        try {
            in = file.getInputStream();
            ExcelReader reader = ExcelUtil.getReader(in);
            List<List<Object>> read = reader.read();
            log.info("最开始：read={}",read);
            log.info("-------------------------------------------------------------------");
            Long hospId = UserContext.getHospId();
            return costIncomeGroupService.importIncomeGroup(read, hospId,file,year,month);
        }catch (IOException e){
            e.printStackTrace();;
            throw new CostException(500, "导入失败");
        }
    }
}