# EmployeeByOther.java

    package cloud.wconnect.workperformance.service.schedule.employee;

    import cloud.wconnect.commonlib.component.BeanMapper;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonErrorI18nConstant;
    import cloud.wconnect.commonlib.cons.ReportImportResultCons;
    import cloud.wconnect.commonlib.cons.SystemPlatformEnum;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.ParseReportException;
    import cloud.wconnect.commonlib.pojo.bo.SymbolBO;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.workperformance.cons.S3BucketFunctionCons;
    import cloud.wconnect.workperformance.entity.schedule.ScheduleDepartmentEmployeeImportLog;
    import cloud.wconnect.workperformance.pojo.bo.schedule.ScheduleDepartmentEmployeeBO;
    import cloud.wconnect.workperformance.service.AmazonS3Service;
    import cloud.wconnect.workperformance.service.schedule.IScheduleDepartmentEmployeeImportLogService;
    import cloud.wconnect.workperformance.service.schedule.IScheduleDepartmentEmployeeService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
    import com.google.common.collect.ImmutableMap;
    import com.google.common.collect.Lists;
    import jakarta.annotation.Resource;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.csv.CSVFormat;
    import org.apache.commons.csv.CSVRecord;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.web.multipart.MultipartFile;

    import java.io.IOException;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.util.List;
    import java.util.Map;
    import java.util.Objects;
    import java.util.stream.Collectors;

    /**
     * Employee By Other
     *
     * @author yyt
     * 2024/8/6
     */
    @Slf4j
    @Component
    @DS("work_performance")
    public class EmployeeByOther implements IEmployeeFileParse {

        @Resource
        private IScheduleDepartmentEmployeeImportLogService scheduleDepartmentEmployeeImportLogService;
        @Resource
        private IScheduleDepartmentEmployeeService scheduleDepartmentEmployeeService;
        @Autowired
        private AmazonS3Service amazonS3Service;
        @Autowired
        private BeanMapper beanMapper;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        @Value("${aws-s3.third-party.key.schedule.employee}")
        private String employeeS3key;

        @Value("${file-max-size.import-report.schedule}")
        private Long importReportFileMaxSize;

        private static final String SOURCE = SystemPlatformEnum.OTHER.getDesc();

        /**
         * 判断可导入文件条件
         *
         * @param source 平台数据源
         * @return boolean
         * @author yyt
         * @date 2023/7/7
         */
        @Override
        public boolean supports(String source) {
            return source.equals(SOURCE);
        }

        /**
         * 解析报告
         *
         * @param inputStream 文件流
         * @param propertyIds 资产 id
         * @return department employee
         * @author yyt
         * @date 2023/7/7
         */
        @Override
        public Map<Integer, List<ScheduleDepartmentEmployeeBO>> parsReport(InputStreamReader inputStream, List<Integer> propertyIds) {
            List<ScheduleDepartmentEmployeeBO> employees = Lists.newArrayList();
            try (InputStreamReader reportReader = inputStream) {
                // 解析报告
                Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(reportReader);
                for (CSVRecord record : records) {
                    if (Objects.isNull(record)) {
                        continue;
                    }

                    // 跳过第一行的标题
                    if (record.getRecordNumber() == 1) {
                        continue;
                    }

                    String departmentCode = record.get(0);
                    String departmentName = record.get(1);
                    String employeeName = record.get(2);

                    ScheduleDepartmentEmployeeBO employee = new ScheduleDepartmentEmployeeBO();
                    employee.setName(employeeName);
                    employee.setDepartmentCode(departmentCode);
                    employee.setDepartmentName(departmentName);
                    employees.add(employee);
                }
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse employee report error", e);
            }

            return ImmutableMap.of(propertyIds.get(0),
                    employees.stream().map(item -> beanMapper.map(item, ScheduleDepartmentEmployeeBO.class)).toList());
        }

        /**
         * 检查文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author yyt
         * @date 2024/8/6
         **/
        @Override
        public void checkFile(PropertyDTO propertyDTO, MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, importReportFileMaxSize);
            // 校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xls", "xlsx"));

            try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                Row titleRow = sheet.getRow(0);
                if (Objects.isNull(titleRow)) {
                    throw new BadRequestException(CommonErrorI18nConstant.IMPORT_REPORT_NOT_REQUIRED,
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            "The imported report is not the one required for the current functionality");
                }

                String departmentCodeTile = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "A"));
                String departmentNameTile = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "B"));
                String employeeNameTile = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "C"));

                for (int i = 1; i < titleRow.getLastCellNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (Objects.isNull(row)) continue;

                    Cell departmentCodeCell = CellUtils.getCellByColumnLetter(row, "A");
                    if (Objects.isNull(departmentCodeCell) || StringUtils.isBlank(departmentCodeCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(departmentCodeTile),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The department code cannot be empty");
                    } else {
                        // 最多20个字
                        if (CellUtils.convertCellStr(departmentCodeCell).length() > 20) {
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(departmentCodeTile, 20),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The department code cannot exceed 20 characters");
                        }
                    }

                    Cell departmentNameCell = CellUtils.getCellByColumnLetter(row, "B");
                    if (Objects.isNull(departmentNameCell) || StringUtils.isBlank(departmentNameCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(departmentNameTile),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The department name cannot be empty");
                    } else {
                        // 最多255个字
                        if (CellUtils.convertCellStr(departmentNameCell).length() > 255) {
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(departmentNameTile, 255),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The department name cannot exceed 255 characters");
                        }
                    }

                    Cell employeeNameCell = CellUtils.getCellByColumnLetter(row, "C");
                    if (Objects.isNull(employeeNameCell) || StringUtils.isBlank(employeeNameCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(employeeNameTile),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The employee name cannot be empty");
                    } else {
                        // 最多255个字
                        if (CellUtils.convertCellStr(employeeNameCell).length() > 255) {
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(employeeNameTile, 255),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The employee name cannot exceed 255 characters");
                        }
                    }
                }

            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse employee report error", e);
            }
        }

        /**
         * 处理导入记录
         *
         * @param tenantBO     租户
         * @param propertyDTOS 资产
         * @param importLog    导入记录
         * @param file         文件
         * @param userId       操作人
         * @return 导入记录
         * @author yyt
         * @date 2024/8/6
         **/
        @Override
        public List<ScheduleDepartmentEmployeeImportLog> handleImportLog(SymbolBO tenantBO, List<PropertyDTO> propertyDTOS, ScheduleDepartmentEmployeeImportLog importLog,
                                                                         MultipartFile file, int userId) {
            List<String> propertyAbbrList = propertyDTOS.stream().map(PropertyDTO::getAbbr).collect(Collectors.toList());
            String key = toS3(tenantBO.getSymbol(), String.join("_", propertyAbbrList), file);
            if (Objects.isNull(importLog)) {
                return scheduleDepartmentEmployeeImportLogService.addBatch(propertyDTOS.stream()
                                .map(PropertyDTO::getId).collect(Collectors.toList()), key,
                        ReportImportResultCons.IMPORT_IN_PROGRESS, userId);
            } else {
                // 更新异常为修复中
                ScheduleDepartmentEmployeeImportLog updateContent = new ScheduleDepartmentEmployeeImportLog();
                updateContent.setId(importLog.getId());
                updateContent.setImportResult(ReportImportResultCons.IMPORT_RESULT_REPAIRING);
                updateContent.setUrl(key);
                scheduleDepartmentEmployeeImportLogService.updateById(updateContent);
                importLog.setImportResult(ReportImportResultCons.IMPORT_RESULT_REPAIRING);
                return Lists.newArrayList(importLog);
            }
        }

        /**
         * 上传文件至 s3
         *
         * @param tenantSymbol   租户标识
         * @param propertySymbol 资产标识
         * @param file           文件
         * @return 文件路径
         * @author yyt
         * @date 2024/8/2
         **/
        private String toS3(String tenantSymbol, String propertySymbol, MultipartFile file) {
            String key = tenantSymbol + "/" + propertySymbol + "/" + employeeS3key +
                    UploadUtils.getRandomFileName(file.getOriginalFilename());
            try {
                // 上传文件
                amazonS3Service.uploadFile(S3BucketFunctionCons.SCHEDULE_IMPORT_RECORD, file, key);
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "Uploading to s3 failure", e);
            }
            return key;
        }

        /**
         * 处理文件
         *
         * @param tenantBO     租户
         * @param propertyDTOS 资产
         * @param file         文件
         * @param importLogs   导入记录
         * @param userId       操作人
         * @author yyt
         * @date 2024/8/6
         **/
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void handleFile(SymbolBO tenantBO, List<PropertyDTO> propertyDTOS, MultipartFile file,
                               List<ScheduleDepartmentEmployeeImportLog> importLogs, Integer userId) {
            boolean importSuccess = true;
            try (InputStream inputStream = file.getInputStream()) {
                PropertyDTO propertyDTO = propertyDTOS.get(0);
                Map<Integer, List<ScheduleDepartmentEmployeeBO>> employeesByPropertyId = parsReport(inputStream,
                        Lists.newArrayList(propertyDTO.getId()));

                scheduleDepartmentEmployeeService.handleEmployeeReportData(Lists.newArrayList(propertyDTO),
                        employeesByPropertyId, userId);
            } catch (Exception e) {
                importSuccess = false;
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse employee report error", e);
            } finally {
                if (importSuccess) {
                    importSuccess(importLogs, userId);
                } else {
                    importFail(importLogs);
                }
            }
        }

        /**
         * 解析报告
         *
         * @param inputStream 文件流
         * @param propertyIds 资产 id
         * @return department employee
         * @author yyt
         * @date 2024/5/20
         */
        private Map<Integer, List<ScheduleDepartmentEmployeeBO>> parsReport(InputStream inputStream, List<Integer> propertyIds) throws IOException {
            List<ScheduleDepartmentEmployeeBO> employees = Lists.newArrayList();
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) {
                    continue;
                }

                ScheduleDepartmentEmployeeBO employee = new ScheduleDepartmentEmployeeBO();
                employee.setName(CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "C")));
                employee.setDepartmentCode(CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "A")));
                employee.setDepartmentName(CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "B")));
                employees.add(employee);
            }

            workbook.close();

            return ImmutableMap.of(propertyIds.get(0),
                    employees.stream().map(item -> beanMapper.map(item, ScheduleDepartmentEmployeeBO.class)).toList());
        }

        /**
         * 导入成功
         *
         * @param importLogs 导入记录
         * @param userId     操作人
         * @author yyt
         * @date 2024/8/6
         **/
        private void importSuccess(List<ScheduleDepartmentEmployeeImportLog> importLogs, int userId) {
            TransactionUtils.afterCommitSyncExecute(() -> {
                // 更新导入记录
                boolean resolveExp = importLogs.stream().anyMatch(item -> item.getImportResult() == ReportImportResultCons.IMPORT_RESULT_REPAIRING);
                scheduleDepartmentEmployeeImportLogService.update(new UpdateWrapper<ScheduleDepartmentEmployeeImportLog>()
                        .lambda().set(ScheduleDepartmentEmployeeImportLog::getImportResult, ReportImportResultCons.IMPORT_RESULT_SUCCESS)
                        .set(resolveExp, ScheduleDepartmentEmployeeImportLog::getResolvedBy, userId)
                        .set(resolveExp, ScheduleDepartmentEmployeeImportLog::getResolvedTime, DateTimeUtils.nowTimestamp())
                        .in(ScheduleDepartmentEmployeeImportLog::getId,
                                importLogs.stream().map(ScheduleDepartmentEmployeeImportLog::getId).collect(Collectors.toList())));
            });
        }

        /**
         * 导入失败
         *
         * @param importLogs 导入记录
         * @author yyt
         * @date 2024/8/6
         **/
        private void importFail(List<ScheduleDepartmentEmployeeImportLog> importLogs) {
            TransactionUtils.afterCompletionRollbackSyncExecute(() -> {
                // 更新导入记录
                scheduleDepartmentEmployeeImportLogService.update(new UpdateWrapper<ScheduleDepartmentEmployeeImportLog>()
                        .lambda().set(ScheduleDepartmentEmployeeImportLog::getImportResult, ReportImportResultCons.IMPORT_RESULT_FAIL)
                        .in(ScheduleDepartmentEmployeeImportLog::getId,
                                importLogs.stream().map(ScheduleDepartmentEmployeeImportLog::getId).collect(Collectors.toList())));
            });
        }

    }