java/talent-card.git

package com.talentcard.server.business.talent;
 
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.Arrays;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.Set;
import java.time.LocalDate;
 
import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
@Service
public class TalentService {
    private static final String[] IMPORT_HEADERS = {
            "姓名", "手机号", "身份证号", "性别", "卡片等级", "人才卡号", "外地/本地", "工作单位", "工作地址", "联系地址"
    };
    private static final Set<String> TALENT_LEVELS = Set.of("卓越人才卡", "杰出人才卡", "普通人才卡", "普通游客");
 
    private final JdbcTemplate jdbcTemplate;
 
    public TalentService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
 
    @Transactional
    public Map<String, Object> create(TalentCreateRequest request) {
        String talentLevel = blankToNull(request.talentLevel());
        String cardNo = request.talentCardNo().trim().toUpperCase();
        Integer cardCount = jdbcTemplate.queryForObject("select count(*) from talent_card where card_no = ?", Integer.class, cardNo);
        if (cardCount != null && cardCount > 0) {
            throw new BusinessException(ErrorCode.CONFLICT, "人才卡号已存在");
        }
        jdbcTemplate.update("""
                insert into talent_user(openid, name, mobile, id_card_no, gender, work_unit, talent_level,
                                        source_area_type, work_address, contact_address)
                values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                blankToNull(request.openid()),
                request.name(),
                blankToNull(request.mobile()),
                blankToNull(request.idCardNo()),
                request.gender(),
                blankToNull(request.workUnit()),
                talentLevel,
                defaultValue(request.sourceAreaType(), "LOCAL"),
                blankToNull(request.workAddress()),
                blankToNull(request.contactAddress()));
        Long id = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
        jdbcTemplate.update("""
                insert into talent_card(talent_id, card_no, card_type, card_level, valid_start, valid_end, issue_time, status)
                values (?, ?, 'PERSONAL', ?, curdate(), date_add(curdate(), interval 1 year), now(), 1)
                """, id, cardNo, talentLevel);
        jdbcTemplate.update("update talent_user set talent_card_no = ? where id = ?", cardNo, id);
        return detail(id);
    }
 
    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> page(
            String keyword,
            String mobile,
            String cardNo,
            String level,
            Integer status,
            LocalDate registerStart,
            LocalDate registerEnd,
            int page,
            int size
    ) {
        if (registerStart != null && registerEnd != null && registerStart.isAfter(registerEnd)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "注册开始日期不能晚于结束日期");
        }
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        StringBuilder sql = new StringBuilder("""
                select id, name, mobile, id_card_no, talent_level, talent_card_no, source_area_type,
                       status, register_time,
                       (select count(*) from benefit_order o where o.talent_id = talent_user.id and o.deleted = 0) as order_count
                from talent_user
                where deleted = 0
                """);
        StringBuilder countSql = new StringBuilder("select count(*) from talent_user where deleted = 0");
        List<Object> args = new ArrayList<>();
        if (keyword != null && !keyword.isBlank()) {
            sql.append(" and name like ?");
            countSql.append(" and name like ?");
            args.add("%" + keyword.trim() + "%");
        }
        if (mobile != null && !mobile.isBlank()) {
            sql.append(" and mobile like ?");
            countSql.append(" and mobile like ?");
            args.add("%" + mobile.trim() + "%");
        }
        if (cardNo != null && !cardNo.isBlank()) {
            sql.append(" and talent_card_no like ?");
            countSql.append(" and talent_card_no like ?");
            args.add("%" + cardNo.trim() + "%");
        }
        if (level != null && !level.isBlank()) {
            sql.append(" and talent_level = ?");
            countSql.append(" and talent_level = ?");
            args.add(level);
        }
        if (status != null) {
            sql.append(" and status = ?");
            countSql.append(" and status = ?");
            args.add(status);
        }
        if (registerStart != null) {
            sql.append(" and register_time >= ?");
            countSql.append(" and register_time >= ?");
            args.add(registerStart.atStartOfDay());
        }
        if (registerEnd != null) {
            sql.append(" and register_time < ?");
            countSql.append(" and register_time < ?");
            args.add(registerEnd.plusDays(1).atStartOfDay());
        }
        sql.append(" order by id desc limit ? offset ?");
        Object[] pageArgs = Arrays.copyOf(args.toArray(), args.size() + 2);
        pageArgs[args.size()] = safeSize;
        pageArgs[args.size() + 1] = (safePage - 1) * safeSize;
        List<Map<String, Object>> records = query(sql.toString(), pageArgs);
        Long total = jdbcTemplate.queryForObject(countSql.toString(), Long.class, args.toArray());
        return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
    }
 
    @Transactional(readOnly = true)
    public Map<String, Object> detail(Long id) {
        List<Map<String, Object>> rows = query("""
                select id, openid, name, mobile, id_card_no, gender, work_unit, talent_level,
                       source_area_type, talent_card_no, register_time, status, work_address, contact_address, advisor_id
                from talent_user
                where deleted = 0 and id = ?
                """, id);
        if (rows.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
        }
        return rows.get(0);
    }
 
    @Transactional(readOnly = true)
    public List<Map<String, Object>> availableAdvisors() {
        return query("""
                select id, advisor_name, department, mobile, card_level
                from talent_card_advisor
                where status = 1
                order by card_level, id
                """);
    }
 
    @Transactional
    public Map<String, Object> updateMobile(Long id, String mobile) {
        String normalizedMobile = mobile.trim();
        Integer duplicateCount = jdbcTemplate.queryForObject("""
                select count(*) from talent_user
                where deleted = 0 and mobile = ? and id <> ?
                """, Integer.class, normalizedMobile, id);
        if (duplicateCount != null && duplicateCount > 0) {
            throw new BusinessException(ErrorCode.CONFLICT, "手机号已被其他持卡人使用");
        }
        int updated = jdbcTemplate.update("""
                update talent_user set mobile = ?
                where id = ? and deleted = 0
                """, normalizedMobile, id);
        if (updated == 0) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
        }
        return detail(id);
    }
 
    @Transactional
    public Map<String, Object> updateCertification(Long id, TalentCertificationUpdateRequest request) {
        String idCardNo = blankToNull(request.idCardNo());
        if (idCardNo != null) {
            idCardNo = idCardNo.toUpperCase();
            Integer duplicateCount = jdbcTemplate.queryForObject("""
                    select count(*) from talent_user
                    where deleted = 0 and id_card_no = ? and id <> ?
                    """, Integer.class, idCardNo, id);
            if (duplicateCount != null && duplicateCount > 0) {
                throw new BusinessException(ErrorCode.CONFLICT, "身份证号已被其他持卡人使用");
            }
        }
        int updated = jdbcTemplate.update("""
                update talent_user
                set name = ?, id_card_no = ?, gender = ?, work_unit = ?, work_address = ?, contact_address = ?
                where id = ? and deleted = 0
                """,
                request.name().trim(),
                idCardNo,
                request.gender(),
                blankToNull(request.workUnit()),
                blankToNull(request.workAddress()),
                blankToNull(request.contactAddress()),
                id);
        if (updated == 0) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
        }
        return detail(id);
    }
 
    @Transactional
    public Map<String, Object> updateCardLevel(Long id, String talentLevel) {
        Integer cardCount = jdbcTemplate.queryForObject("""
                select count(*) from talent_card
                where deleted = 0 and talent_id = ? and card_type = 'PERSONAL'
                """, Integer.class, id);
        if (cardCount == null || cardCount == 0) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "该持卡人没有有效的主人才卡");
        }
        int updated = jdbcTemplate.update("""
                update talent_user set talent_level = ?
                where id = ? and deleted = 0
                """, talentLevel, id);
        if (updated == 0) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
        }
        jdbcTemplate.update("""
                update talent_card set card_level = ?
                where deleted = 0 and talent_id = ? and card_type = 'PERSONAL'
                """, talentLevel, id);
        return detail(id);
    }
 
    @Transactional
    public Map<String, Object> updateAdvisor(Long id, Long advisorId) {
        if (advisorId != null) {
            Integer advisorCount = jdbcTemplate.queryForObject("""
                    select count(*) from talent_card_advisor where id = ? and status = 1
                    """, Integer.class, advisorId);
            if (advisorCount == null || advisorCount == 0) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, "服务专员不存在或已停用");
            }
        }
        int updated = jdbcTemplate.update("""
                update talent_user set advisor_id = ?
                where id = ? and deleted = 0
                """, advisorId, id);
        if (updated == 0) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
        }
        return detail(id);
    }
 
    @Transactional(readOnly = true)
    public byte[] exportExcel(String keyword, String mobile, String cardNo, String level, Integer status,
            LocalDate registerStart, LocalDate registerEnd) {
        List<Object[]> rows = new ArrayList<>();
        int pageNumber = 1;
        long rowNumber = 1;
        while (true) {
            PageResult<Map<String, Object>> result = page(keyword, mobile, cardNo, level, status,
                    registerStart, registerEnd, pageNumber, 100);
            for (Map<String, Object> row : result.records()) {
                rows.add(new Object[] {
                        rowNumber++,
                        row.get("name"),
                        maskMobile(row.get("mobile")),
                        row.get("talentCardNo"),
                        row.get("talentLevel"),
                        "OUTSIDE".equals(row.get("sourceAreaType")) ? "外地" : "本地",
                        row.get("orderCount"),
                        formatDateTime(row.get("registerTime")),
                        Integer.valueOf(0).equals(row.get("status")) ? "停用" : "正常"
                });
            }
            if ((long) pageNumber * result.size() >= result.total() || result.records().isEmpty()) {
                break;
            }
            pageNumber++;
        }
 
        String[] headers = { "编号", "持卡人名称", "持卡人手机号", "人才卡号", "卡片等级", "外地/本地", "订单数", "注册时间", "状态" };
        try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream output = new ByteArrayOutputStream()) {
            Sheet sheet = workbook.createSheet("持卡人列表");
            sheet.createFreezePane(0, 1);
            CellStyle headerStyle = createHeaderStyle(workbook);
            Row headerRow = sheet.createRow(0);
            for (int index = 0; index < headers.length; index++) {
                headerRow.createCell(index).setCellValue(headers[index]);
                headerRow.getCell(index).setCellStyle(headerStyle);
            }
            for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
                Row excelRow = sheet.createRow(rowIndex + 1);
                Object[] values = rows.get(rowIndex);
                for (int columnIndex = 0; columnIndex < values.length; columnIndex++) {
                    excelRow.createCell(columnIndex).setCellValue(values[columnIndex] == null ? "" : String.valueOf(values[columnIndex]));
                }
            }
            int[] widths = { 10, 20, 18, 20, 16, 12, 10, 22, 10 };
            for (int index = 0; index < widths.length; index++) {
                sheet.setColumnWidth(index, widths[index] * 256);
            }
            workbook.write(output);
            return output.toByteArray();
        } catch (IOException exception) {
            throw new IllegalStateException("生成持卡人 Excel 失败", exception);
        }
    }
 
    public byte[] importTemplate() {
        try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream output = new ByteArrayOutputStream()) {
            Sheet sheet = workbook.createSheet("持卡人导入模板");
            CellStyle headerStyle = createHeaderStyle(workbook);
            Row headerRow = sheet.createRow(0);
            for (int index = 0; index < IMPORT_HEADERS.length; index++) {
                headerRow.createCell(index).setCellValue(IMPORT_HEADERS[index]);
                headerRow.getCell(index).setCellStyle(headerStyle);
            }
            int[] widths = { 16, 18, 24, 10, 18, 20, 14, 24, 28, 28 };
            for (int index = 0; index < widths.length; index++) {
                sheet.setColumnWidth(index, widths[index] * 256);
            }
            sheet.createFreezePane(0, 1);
            workbook.write(output);
            return output.toByteArray();
        } catch (IOException exception) {
            throw new IllegalStateException("生成持卡人导入模板失败", exception);
        }
    }
 
    @Transactional
    public Map<String, Object> importExcel(InputStream inputStream) {
        List<Map<String, Object>> failures = new ArrayList<>();
        List<TalentCreateRequest> validRows = new ArrayList<>();
        Set<String> fileMobiles = new HashSet<>();
        Set<String> fileIdCards = new HashSet<>();
        Set<String> fileCardNos = new HashSet<>();
        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getNumberOfSheets() == 0 ? null : workbook.getSheetAt(0);
            if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 中没有可导入的数据");
            }
            validateImportHeaders(sheet.getRow(0));
            DataFormatter formatter = new DataFormatter();
            int dataRows = 0;
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null || isEmptyRow(row, formatter)) {
                    continue;
                }
                dataRows++;
                if (dataRows > 1000) {
                    throw new BusinessException(ErrorCode.BAD_REQUEST, "单次最多导入1000条持卡人数据");
                }
                String name = cellText(row, 0, formatter);
                String mobile = cellText(row, 1, formatter);
                String idCardNo = cellText(row, 2, formatter).toUpperCase();
                String gender = cellText(row, 3, formatter);
                String level = cellText(row, 4, formatter);
                String talentCardNo = cellText(row, 5, formatter).toUpperCase();
                String area = cellText(row, 6, formatter);
                String workUnit = cellText(row, 7, formatter);
                String workAddress = cellText(row, 8, formatter);
                String contactAddress = cellText(row, 9, formatter);
                String error = validateImportRow(name, mobile, idCardNo, gender, level, talentCardNo, area, workUnit, workAddress,
                        contactAddress, fileMobiles, fileIdCards, fileCardNos);
                if (error != null) {
                    failures.add(importFailure(rowIndex + 1, name, error));
                    continue;
                }
                fileMobiles.add(mobile);
                fileIdCards.add(idCardNo);
                fileCardNos.add(talentCardNo);
                validRows.add(new TalentCreateRequest(null, name, mobile, idCardNo, genderValue(gender), workUnit, level, talentCardNo,
                        "外地".equals(area) ? "OUTSIDE" : "LOCAL", workAddress, contactAddress));
            }
            if (dataRows == 0) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 中没有可导入的数据");
            }
        } catch (BusinessException exception) {
            throw exception;
        } catch (IOException | RuntimeException exception) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 文件无法解析，请使用下载的导入模板");
        }
 
        for (TalentCreateRequest request : validRows) {
            create(request);
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("successCount", validRows.size());
        result.put("failureCount", failures.size());
        result.put("failures", failures);
        return result;
    }
 
    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> cards(Long id, int page, int size) {
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        List<Map<String, Object>> records = query("""
                select id, talent_id, card_no, card_type, card_level, valid_start, valid_end,
                       issue_time, status, expire_time, is_family_card_enabled, family_card_limit
                from talent_card
                where deleted = 0 and talent_id = ?
                order by id desc
                limit ? offset ?
                """, id, safeSize, (safePage - 1) * safeSize);
        Long total = jdbcTemplate.queryForObject("""
                select count(*) from talent_card where deleted = 0 and talent_id = ?
                """, Long.class, id);
        return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
    }
 
    private List<Map<String, Object>> query(String sql, Object... args) {
        return DatabaseRows.normalize(jdbcTemplate.queryForList(sql, args));
    }
 
    private String defaultValue(String value, String fallback) {
        return value == null || value.isBlank() ? fallback : value;
    }
 
    private String blankToNull(String value) {
        return value == null || value.isBlank() ? null : value;
    }
 
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }
 
    private void validateImportHeaders(Row row) {
        DataFormatter formatter = new DataFormatter();
        if (row == null) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 表头缺失，请使用下载的导入模板");
        }
        for (int index = 0; index < IMPORT_HEADERS.length; index++) {
            if (!IMPORT_HEADERS[index].equals(cellText(row, index, formatter))) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 表头不正确，请使用下载的导入模板");
            }
        }
    }
 
    private boolean isEmptyRow(Row row, DataFormatter formatter) {
        for (int index = 0; index < IMPORT_HEADERS.length; index++) {
            if (!cellText(row, index, formatter).isBlank()) {
                return false;
            }
        }
        return true;
    }
 
    private String cellText(Row row, int index, DataFormatter formatter) {
        return row.getCell(index) == null ? "" : formatter.formatCellValue(row.getCell(index)).trim();
    }
 
    private String validateImportRow(String name, String mobile, String idCardNo, String gender, String level,
            String talentCardNo, String area, String workUnit, String workAddress, String contactAddress,
            Set<String> fileMobiles, Set<String> fileIdCards, Set<String> fileCardNos) {
        if (name.isBlank()) return "姓名不能为空";
        if (name.length() > 64) return "姓名不能超过64个字符";
        if (!mobile.matches("^1\\d{10}$")) return "手机号须为11位数字";
        if (!idCardNo.matches("^[0-9A-Z]{18}$")) return "身份证号须为18位数字或大写英文字母";
        if (!gender.isBlank() && !Set.of("男", "女").contains(gender)) return "性别只能填写男或女";
        if (!TALENT_LEVELS.contains(level)) return "卡片等级须填写卓越人才卡、杰出人才卡、普通人才卡或普通游客";
        if (!talentCardNo.matches("^[0-9A-Z-]{1,64}$")) return "人才卡号只能包含大写字母、数字和短横线";
        if (!area.isBlank() && !Set.of("本地", "外地").contains(area)) return "外地/本地只能填写本地或外地";
        if (workUnit.length() > 128) return "工作单位不能超过128个字符";
        if (workAddress.length() > 255) return "工作地址不能超过255个字符";
        if (contactAddress.length() > 255) return "联系地址不能超过255个字符";
        if (fileMobiles.contains(mobile)) return "Excel 内手机号重复";
        if (fileIdCards.contains(idCardNo)) return "Excel 内身份证号重复";
        if (fileCardNos.contains(talentCardNo)) return "Excel 内人才卡号重复";
        Integer mobileCount = jdbcTemplate.queryForObject("select count(*) from talent_user where deleted = 0 and mobile = ?", Integer.class, mobile);
        if (mobileCount != null && mobileCount > 0) return "手机号已存在";
        Integer idCardCount = jdbcTemplate.queryForObject("select count(*) from talent_user where deleted = 0 and id_card_no = ?", Integer.class, idCardNo);
        if (idCardCount != null && idCardCount > 0) return "身份证号已存在";
        Integer cardCount = jdbcTemplate.queryForObject("select count(*) from talent_card where card_no = ?", Integer.class, talentCardNo);
        if (cardCount != null && cardCount > 0) return "人才卡号已存在";
        return null;
    }
 
    private Byte genderValue(String gender) {
        if ("男".equals(gender)) return 1;
        if ("女".equals(gender)) return 2;
        return null;
    }
 
    private Map<String, Object> importFailure(int rowNumber, String name, String reason) {
        Map<String, Object> failure = new LinkedHashMap<>();
        failure.put("rowNumber", rowNumber);
        failure.put("name", name);
        failure.put("reason", reason);
        return failure;
    }
 
    private String maskMobile(Object value) {
        String mobile = value == null ? "" : String.valueOf(value);
        return mobile.length() == 11 ? mobile.substring(0, 3) + "****" + mobile.substring(7) : mobile;
    }
 
    private String formatDateTime(Object value) {
        return value == null ? "" : String.valueOf(value).replace('T', ' ');
    }
}