kcim-cost/src/main/java/com/kcim/service/impl/KpiComputeImportServiceImpl.java

package com.kcim.service.impl;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.kcim.common.constants.Constant;
import com.kcim.common.constants.NumberConstant;
import com.kcim.common.exception.CostException;
import com.kcim.common.file.MinioConfig;
import com.kcim.common.file.MinioFileUtil;
import com.kcim.common.util.DateUtils;
import com.kcim.common.util.PageUtils;
import com.kcim.common.util.UserContext;
import com.kcim.dao.model.KpiComputeImport;
import com.kcim.dao.repository.KpiComputeImportRepository;
import com.kcim.service.CenterService;
import com.kcim.service.KpiComputeImportService;
import com.kcim.vo.CommonTitleVo;
import com.kcim.vo.DictDataVo;
import com.kcim.vo.UserInfoVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.ibatis.jdbc.SqlRunner;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: CostAccount
 * @description:
 * @author: Wang.YS
 * @create: 2024-06-03 13:58
 **/
@Service("kpiComputeImportService")
@Slf4j
public class KpiComputeImportServiceImpl implements KpiComputeImportService {
    private final KpiComputeImportRepository repository;

    private final MinioConfig minioConfig;

    private final MinioFileUtil minioFileUtil;

    private final CenterService centerService;

    @Value("${spring.datasource.driver-class-name}")
    private String driver;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    public KpiComputeImportServiceImpl(KpiComputeImportRepository repository, MinioConfig minioConfig, MinioFileUtil minioFileUtil, CenterService centerService) {
        this.repository = repository;
        this.minioConfig = minioConfig;
        this.minioFileUtil = minioFileUtil;
        this.centerService = centerService;
    }


    /**
     * 查询导入列表
     *
     * @param current     当前页
     * @param pageSize    页容量
     * @param computeDate 核算年月
     * @param tableName   表名
     * @return 导入列表
     */
    @Override
    public Object getList(Integer current, Integer pageSize, String computeDate, String tableName) {
        List<UserInfoVO> centerEmployee = centerService.getCenterUserInfo(null);
        Map<Long, String> userName = centerEmployee.stream().collect(Collectors.toMap(UserInfoVO::getId, UserInfoVO::getName, (a, b) -> b));
        DictDataVo dict = centerService.getDict(Constant.SPECIAL_SHEET_MAP);
        if (Objects.isNull(dict)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        List<DictDataVo> dataVoList = dict.getDataVoList();
        if (CollectionUtils.isEmpty(dataVoList)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        Map<String, String> dictionaryCodeNameMap = dataVoList.stream().collect(Collectors.toMap(DictDataVo::getCode, DictDataVo::getName, (a, b) -> b));
        Page<KpiComputeImport> page = repository.getPage(current, pageSize, computeDate, tableName);
        List<KpiComputeImport> list = page.getRecords();
        if (CollectionUtils.isEmpty(list)) {
            return new PageUtils(new ArrayList<>(), NumberConstant.ZERO, pageSize, current);
        }
        for (KpiComputeImport computeImport : list) {
            computeImport.setCreateUserName(userName.get(Long.valueOf(computeImport.getCreateUser())));
            computeImport.setTableNameDisplay(dictionaryCodeNameMap.get(computeImport.getTableName()));
        }
        return new PageUtils(list, Math.toIntExact(page.getTotal()), pageSize, current);
    }

    /**
     * @return
     */
    @Override
    public Object getTableList() {
        DictDataVo dict = centerService.getDict(Constant.SPECIAL_SHEET_MAP);
        if (Objects.isNull(dict)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        List<DictDataVo> dataVoList = dict.getDataVoList();
        if (CollectionUtils.isEmpty(dataVoList)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        Map<String, String> dictionaryCodeNameMap = dataVoList.stream().collect(Collectors.toMap(DictDataVo::getCode, DictDataVo::getName, (a, b) -> b));
        List<CommonTitleVo> list = new ArrayList<>();
        if (!CollectionUtils.isEmpty(dictionaryCodeNameMap)) {
            list = dictionaryCodeNameMap.keySet().stream().map(s -> new CommonTitleVo(s, dictionaryCodeNameMap.get(s))).collect(Collectors.toList());
        }
        return list;
    }

    /**
     * 导入数据
     *
     * @param computeDate 核算年月
     * @param tableName   表名
     * @param file        导入文件
     */
    @Override
    @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED)
    public void importData(String computeDate, String tableName, MultipartFile file) {
        //excel 数据导入
        String fileName = file.getOriginalFilename();
        //导入文件上传
        String uploadFileUrl = uploadFile(file, fileName);
        //导入记录添加
        KpiComputeImport kpiComputeImport = new KpiComputeImport();
        kpiComputeImport.setComputeDate(computeDate);
        kpiComputeImport.setImportStatus(NumberConstant.ZERO);
        kpiComputeImport.setCreateTime(new Date());
        kpiComputeImport.setCreateUser(String.valueOf(UserContext.getCurrentUser().getId()));
        kpiComputeImport.setUrl(uploadFileUrl);
        kpiComputeImport.setTableName(tableName);
        kpiComputeImport.setFileName(fileName);
        kpiComputeImport.setHospId(UserContext.getCurrentLoginHospId());
        kpiComputeImport.setDelFlag(NumberConstant.ZERO);
        repository.save(kpiComputeImport);
        insertExcelData(kpiComputeImport.getId(), tableName, file, fileName, computeDate);
    }

    private void insertExcelData(Integer id, String tableName, MultipartFile file, String fileName, String computeDate) {
        try {
            assert fileName != null;
            File file1 = new File(fileName);
            FileUtils.copyInputStreamToFile(file.getInputStream(), file1);
            String excelSql = readExcelSql(file1, tableName, id);
            SqlRunner sqlRunner = new SqlRunner(getConnection());
            sqlRunner.insert(excelSql);

            //更新新增人和时间
            String updateSql = "update " + "`" + tableName + "`" + "set" +
                    "`create_user` = '" + UserContext.getCurrentUser().getId() + "' , `create_time` = '" + DateUtils.formatDate2String(new Date()) +
                    "' where `compute_date` = '" + computeDate + "' and `hosp_id` = " + UserContext.getCurrentLoginHospId() +
                    ";";
            sqlRunner.run(updateSql);
        } catch (IOException | SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }


    }

    private String uploadFile(MultipartFile file, String fileName) {
        DateTime date = DateUtil.date();
        int month = DateUtil.month(date) + 1;
        int year = DateUtil.year(date);
        int day = DateUtil.dayOfMonth(date);
        Long hospId = UserContext.getCurrentLoginHospId();
        String fileUrl = "";
        try {
            String format = DateUtil.format(date, DatePattern.PURE_DATETIME_PATTERN);
            String originalFilename = format + fileName;
            String dataDirectory = minioConfig.getBucketName();
            String uploadFileName = "upload" + "/" + hospId + "/" + year + "/" + month + "/" + day + "/" + originalFilename;
            InputStream inputStream = file.getInputStream();
            minioFileUtil.putObject(dataDirectory, uploadFileName, inputStream);
            fileUrl = minioFileUtil.getObjectUrl(dataDirectory, uploadFileName);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return fileUrl;
    }

    /**
     * 作废导入数据
     *
     * @param id 记录id
     */
    @Override
    @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED)
    public void removeImport(Integer id) {
        KpiComputeImport kpiComputeImport = repository.getById(id);
        if (Objects.nonNull(kpiComputeImport)) {
            //更新表信息
//            String computeDate = kpiComputeImport.getComputeDate();
            String tableName = kpiComputeImport.getTableName();
            Long hospId = kpiComputeImport.getHospId();
            // TODO: 2023/7/24  导入的数据可能不是这个月的，用记录id 进行删除，有问题再看
            StringBuilder delSql = new StringBuilder().append("update " + "`").append(tableName).append("`").append("set")
                    .append("`del_flag` = 1 , `update_user` = '").append(UserContext.getCurrentUser().getId())
                    .append("' , `update_time` = '").append(DateUtils.formatDate2String(new Date())).append("' where  `hosp_id` = ").append(hospId)
                    .append(" and `import_id` = ").append(id).append(";");
            try {
                log.info("执行的sql语句：" + delSql);
                SqlRunner sqlRunner = new SqlRunner(getConnection());
                sqlRunner.run(delSql.toString());
            } catch (ClassNotFoundException | SQLException e) {
                throw new RuntimeException("删除导入数据错误" + e);
            }
            //更新 记录表信息
            kpiComputeImport.setImportStatus(NumberConstant.ONE);
            kpiComputeImport.setUpdateUser(String.valueOf(UserContext.getCurrentUser().getId()));
            kpiComputeImport.setUpdateTime(new Date());
            repository.updateById(kpiComputeImport);
        }
    }

    /**
     * 复原导入数据
     *
     * @param id 记录id
     */
    @Override
    @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED)
    public void recoverImport(Integer id) {
        KpiComputeImport kpiComputeImport = repository.getById(id);
        if (Objects.nonNull(kpiComputeImport)) {
            //更新表信息
            String computeDate = kpiComputeImport.getComputeDate();
            String tableName = kpiComputeImport.getTableName();
            Long hospId = kpiComputeImport.getHospId();
            StringBuilder delSql = new StringBuilder().append("update " + "`").append(tableName).append("`").append("set")
                    .append("`del_flag` = 0 , `update_user` = '").append(UserContext.getCurrentUser().getId())
                    .append("' , `update_time` = '").append(DateUtils.formatDate2String(new Date())).append("' where `hosp_id` = ").append(hospId)
                    .append(" and `import_id` = ").append(id).append(";");
            try {
                log.info("执行的sql语句：" + delSql);
                SqlRunner sqlRunner = new SqlRunner(getConnection());
                sqlRunner.run(delSql.toString());
            } catch (ClassNotFoundException | SQLException e) {
                throw new RuntimeException("复原数据错误" + e);
            }
            //更新 记录表信息
            kpiComputeImport.setImportStatus(NumberConstant.ZERO);
            kpiComputeImport.setUpdateUser(String.valueOf(UserContext.getCurrentUser().getId()));
            kpiComputeImport.setUpdateTime(new Date());
            repository.updateById(kpiComputeImport);
        }
    }

    public static String readExcelSql(File file, String tableName, Integer id) throws IOException {
        // 获取excel工作簿对象
        String sql = null;
        String fileName = file.getName();
        String substring = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(substring)) {
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
            for (Sheet sheet : workbook) {
                List<String> headerList = new ArrayList<>();
                List<Map<String, Object>> dataList = new ArrayList<>();
                System.out.println(sheet.getSheetName());
                int i = 0;
                for (Row row : sheet) {
                    if (i == 1) {
                        i++;
                        continue;
                    }
                    if (i == 0) {
                        if (i == 0) {
                            for (Cell cell : row) {
                                headerList.add(cell.getStringCellValue());
                            }
//                            if(headerList.size() > 0) {
//                                String createTableSql = createTable(headerList, sheetName);
//                                System.out.println("生成的创建表语句："+ createTableSql);
//                            }
                        }
                    } else {
                        Map<String, Object> map = new HashMap<>();
                        int j = 0;
                        for (Cell cell : row) {
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            map.put(headerList.get(j), cell.getStringCellValue());
                            j++;
                        }

                        dataList.add(map);
                    }
                    i++;
                }
                //组装记录表和hospId
                setCommonData(id, dataList);
                sql = insertFromMap(dataList, tableName);

            }

        } else if (".xlsx".equals(substring)) {
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
            for (Sheet sheet : workbook) {
                List<String> headerList = new ArrayList<>();
                List<Map<String, Object>> dataList = new ArrayList<>();
                System.out.println(sheet.getSheetName());
                String sheetName = sheet.getSheetName();
                int i = 0;
                for (Row row : sheet) {
                    if (i == 1) {
                        i++;
                        continue;
                    }
                    if (i == 0) {
                        if (i == 0) {
                            for (Cell cell : row) {
                                headerList.add(cell.getStringCellValue());
                            }
//                            if(headerList.size() > 0) {
//                                String createTableSql = createTable(headerList, sheetName);
//                                System.out.println("生成的创建表语句："+ createTableSql);
//                            }
                        }
                    } else {
                        Map<String, Object> map = new HashMap<>();
                        int j = 0;
                        for (Cell cell : row) {
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            map.put(headerList.get(j), cell.getStringCellValue());
                            j++;
                        }
                        dataList.add(map);
                    }
                    i++;
                }
                setCommonData(id, dataList);
                sql = insertFromMap(dataList, tableName);
            }
        }
        return sql;
    }

    private static void setCommonData(Integer id, List<Map<String, Object>> dataList) {
        if (!CollectionUtils.isEmpty(dataList)) {
            dataList.forEach(stringObjectMap -> {
                stringObjectMap.put("hosp_id", UserContext.getCurrentLoginHospId());
                stringObjectMap.put("import_id", id);
            });
        }
    }

    /**
     * 生成创建表结构
     *
     * @param headerList 表头
     * @param sheetName  sheet名
     * @return
     */
    public static String createTable(List<String> headerList, String sheetName) {
        StringBuffer createTableSql = new StringBuffer();
        if (headerList.size() > 0) {
            createTableSql.append("SET NAMES utf8mb4;\n");
            createTableSql.append("SET FOREIGN_KEY_CHECKS = 0;\n");
            createTableSql.append("DROP TABLE IF EXISTS `" + sheetName + "`;\n");
            createTableSql.append("CREATE TABLE `" + sheetName + "`");
            createTableSql.append("(\n");
            createTableSql.append("`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',\n");
            int k = 0;
            for (String key : headerList) {
                createTableSql.append("`" + key + "` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n");
                k++;
                if (k == headerList.size()) {
                    createTableSql.append("PRIMARY KEY (`id`) USING BTREE)\n");
                    createTableSql.append("ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '' ROW_FORMAT = Dynamic;\n");
                    createTableSql.append("SET FOREIGN_KEY_CHECKS = 1;");
                }
            }
        }
        return createTableSql.toString();
    }


    /**
     * map对象生成insert插入语句
     *
     * @param dataList
     * @param tableName
     */
    public static String insertFromMap(List<Map<String, Object>> dataList, String tableName) {
        String sql = null;
        if (dataList.size() > 0) {
            int i = 0;
            StringBuilder strKey = new StringBuilder();
            //插入sql语句
            StringBuilder insertSql = new StringBuilder().append("INSERT INTO " + "`").append(tableName).append("`");
            StringBuilder value = new StringBuilder();
            for (Map<String, Object> map : dataList) {
                //存入key的字符串数组
                //存入value的字符串数组
                ArrayList<Object> arrValue = new ArrayList<>();
                //拼接sql
                ArrayList<Object> arrKey = new ArrayList<>(map.keySet());
                for (String keys : map.keySet()) {
                    arrValue.add(map.get(keys));
                }
                if (i == 0) {
                    //遍历存的key字符串数组拼接sql
                    for (int j = 0; j < arrKey.size(); j++) {
                        strKey.append("`").append(arrKey.get(j)).append("`");
                        if (j != arrKey.size() - 1) {//拼上","最后一个不拼
                            strKey.append(",");
                        }
                    }
                }
                i++;
                StringBuilder strVal = new StringBuilder();
                //遍历存的value字符串数组拼接sql
                for (int j = 0; j < arrValue.size(); j++) {
                    if (null != arrValue.get(j) && !"".equals(arrValue.get(j))) {
                        strVal.append("'").append(arrValue.get(j)).append("'");//拼接单引号
                    } else if ("".equals(arrValue.get(j))) {
                        strVal.append((String) null);
                    } else {
                        strVal.append(arrValue.get(j));
                    }
                    if (j != arrValue.size() - 1) {//拼上","最后一个不拼
                        strVal.append(",");
                    }
                }
                String stringEntryVal = strVal.toString();
                value.append("(").append(stringEntryVal).append(")");
                if (i < dataList.size()) {
                    value.append(",");
                }
            }
            insertSql.append("(").append(strKey).append(")");
            insertSql.append(" VALUES ");
            insertSql.append(value).append(";");
            System.out.println("生成插入数据sql:" + insertSql.toString());
            sql = insertSql.toString();
        }
        return sql;
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        return DriverManager.getConnection(url, username, password);
    }
}