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 Set<String> TALENT_LEVELS = Set.of("卓越人才卡", "杰出人才卡", "菁英人才卡", "普通游客");
|
|
private static final String[] IMPORT_HEADERS = {
|
"姓名（必填）", "手机号码（必填）", "身份证号（必填）", "性别（必填）", "卡片等级（必填）", "人才卡号（必填）",
|
"外地/本地（必填）", "工作单位（必填）", "工作地址（必填）", "联系地址（必填）", "服务专员（必填）"
|
};
|
private record ImportTalentRow(TalentCreateRequest request, Long advisorId) {}
|
|
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, "人才卡号已存在");
|
}
|
String mobile = blankToNull(request.mobile());
|
// 一个手机号只能存在一个账号：新建持卡人前先查重（编辑手机号走 updateMobile，同样有查重）。
|
if (mobile != null) {
|
Integer mobileCount = jdbcTemplate.queryForObject(
|
"select count(*) from talent_user where deleted = 0 and mobile = ?", Integer.class, mobile);
|
if (mobileCount != null && mobileCount > 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(),
|
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 register_time desc, 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, service_staff_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 List<Map<String, Object>> availableServiceStaff() {
|
return query("""
|
select id, display_name, mobile, position_name
|
from admin_operator
|
where deleted = 0 and status = 1
|
order by display_name, id
|
""");
|
}
|
|
@Transactional
|
public Map<String, Object> updateStatus(Long id, Byte status) {
|
if (status == null || (status != 0 && status != 1)) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "状态只能为0停用或1启用");
|
}
|
int updated = jdbcTemplate.update("update talent_user set status = ? where id = ? and deleted = 0", status, id);
|
if (updated == 0) throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
|
return detail(id);
|
}
|
|
@Transactional
|
public Map<String, Object> updateServiceStaff(Long id, Long staffId) {
|
if (staffId != null) {
|
Integer staffCount = jdbcTemplate.queryForObject("""
|
select count(*) from admin_operator where id = ? and deleted = 0 and status = 1
|
""", Integer.class, staffId);
|
if (staffCount == null || staffCount == 0) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "服务人员不存在或已停用");
|
}
|
}
|
int updated = jdbcTemplate.update("""
|
update talent_user set service_staff_id = ?
|
where id = ? and deleted = 0
|
""", staffId, 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, 18 };
|
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<>();
|
List<Long> staffIds = 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 advisorName = cellText(row, 10, 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;
|
}
|
Long staffId = serviceStaffIdByName(advisorName);
|
if (staffId == null) {
|
failures.add(importFailure(rowIndex + 1, name,
|
advisorName.isBlank() ? "服务专员不能为空" : "服务专员不存在、已停用或在员工管理中姓名不唯一"));
|
continue;
|
}
|
fileMobiles.add(mobile);
|
fileIdCards.add(idCardNo);
|
fileCardNos.add(talentCardNo);
|
staffIds.add(staffId);
|
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 (int index = 0; index < validRows.size(); index++) {
|
Map<String, Object> created = create(validRows.get(index));
|
// 服务专员写进专属服务人员字段（和「持卡人管理」里选服务人员是同一个字段）。
|
updateServiceStaff(((Number) created.get("id")).longValue(), staffIds.get(index));
|
}
|
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 (mobile.isBlank()) return "手机号不能为空";
|
if (idCardNo.isBlank()) return "身份证号不能为空";
|
if (gender.isBlank()) return "性别不能为空";
|
if (level.isBlank()) return "卡片等级不能为空";
|
if (talentCardNo.isBlank()) return "人才卡号不能为空";
|
if (area.isBlank()) return "外地/本地不能为空";
|
if (workUnit.isBlank()) return "工作单位不能为空";
|
if (workAddress.isBlank()) return "工作地址不能为空";
|
if (contactAddress.isBlank()) return "联系地址不能为空";
|
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;
|
}
|
|
/**
|
* 导入时"服务专员"填的是员工姓名，对应「系统管理 → 员工管理」里的员工（admin_operator）。
|
*
|
* 以前查的是老的 talent_card_advisor 顾问表，员工管理里新建的人根本查不到，
|
* 于是导入一直报"服务专员不存在"。这里改成按员工姓名查（未删除、已启用、且姓名唯一）。
|
*/
|
Long serviceStaffIdByName(String staffName) {
|
if (staffName == null || staffName.isBlank()) return null;
|
List<Long> ids = jdbcTemplate.queryForList("""
|
select id from admin_operator
|
where deleted = 0 and status = 1 and display_name = ?
|
""", Long.class, staffName.trim());
|
return ids.size() == 1 ? ids.get(0) : 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', ' ');
|
}
|
}
|
