package com.talentcard.server.business.portal;
|
|
import java.awt.image.BufferedImage;
|
import java.io.ByteArrayOutputStream;
|
import java.net.URI;
|
import java.sql.Timestamp;
|
import java.time.Instant;
|
import java.time.LocalDate;
|
import java.util.Arrays;
|
import java.util.Base64;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Locale;
|
import java.util.Map;
|
import java.util.Set;
|
import java.util.stream.Collectors;
|
|
import javax.imageio.ImageIO;
|
|
import com.google.zxing.BarcodeFormat;
|
import com.google.zxing.client.j2se.MatrixToImageWriter;
|
import com.google.zxing.common.BitMatrix;
|
import com.google.zxing.qrcode.QRCodeWriter;
|
import com.talentcard.server.business.DatabaseRows;
|
import com.talentcard.server.common.BusinessException;
|
import com.talentcard.server.common.ErrorCode;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
@Service
|
public class TalentCardPortalService {
|
|
private static final int DYNAMIC_CODE_SECONDS = 60;
|
|
private final JdbcTemplate jdbcTemplate;
|
private final Set<String> allowedExternalWebHosts;
|
|
public TalentCardPortalService(
|
JdbcTemplate jdbcTemplate,
|
@Value("${app.external-web.allowed-hosts:}") String allowedExternalWebHosts
|
) {
|
this.jdbcTemplate = jdbcTemplate;
|
this.allowedExternalWebHosts = Arrays.stream(allowedExternalWebHosts.split(","))
|
.map(String::trim)
|
.filter(value -> !value.isEmpty())
|
.map(value -> value.toLowerCase(Locale.ROOT))
|
.collect(Collectors.toUnmodifiableSet());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> profile(Long talentId) {
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("hasTalentCard", false);
|
result.put("applicationOpen", true);
|
result.put("applicationMessage", "请前往东区政务服务中心人才综合服务专窗办理人才卡申请。");
|
result.put("applicationPhone", "0812-3509008");
|
result.put("cardTypes", List.of(
|
Map.of("level", "卓越人才卡", "theme", "EXCELLENCE"),
|
Map.of("level", "杰出人才卡", "theme", "OUTSTANDING"),
|
Map.of("level", "菁英人才卡", "theme", "ELITE")
|
));
|
Map<String, Object> familyCardAccess = familyCardAccess(talentId);
|
result.put("familyCardAccess", familyCardAccess);
|
if (talentId == null) return result;
|
|
List<Map<String, Object>> cards = query("""
|
select c.id as card_id, c.card_no, c.card_level, c.valid_start, c.valid_end, c.status as card_status,
|
c.is_family_card_enabled, c.family_card_limit,
|
t.id as talent_id, t.name, t.gender, t.work_unit, t.status as talent_status
|
from talent_card c
|
join talent_user t on t.id = c.talent_id and t.deleted = 0
|
where c.deleted = 0 and c.talent_id = ? and c.card_type = 'PERSONAL'
|
order by c.id desc
|
limit 1
|
""", talentId);
|
if (cards.isEmpty()) return result;
|
|
Map<String, Object> row = cards.get(0);
|
String status = cardStatus(row);
|
String level = value(row.get("cardLevel"), "人才卡");
|
Map<String, Object> card = new LinkedHashMap<>();
|
card.put("id", row.get("cardId"));
|
card.put("level", level);
|
card.put("theme", cardTheme(level));
|
card.put("name", maskName((String) row.get("name")));
|
card.put("gender", genderText(row.get("gender")));
|
card.put("cardNo", maskCardNo((String) row.get("cardNo")));
|
card.put("workUnit", maskWorkUnit((String) row.get("workUnit")));
|
card.put("status", status);
|
card.put("statusText", cardStatusText(status));
|
card.put("canShowCode", "ACTIVE".equals(status));
|
card.put("validEnd", row.get("validEnd"));
|
card.put("familyCardEnabled", Boolean.TRUE.equals(familyCardAccess.get("enabled")));
|
card.put("familyCardLimit", ((Number) row.getOrDefault("familyCardLimit", 0)).intValue());
|
result.put("hasTalentCard", true);
|
result.put("card", card);
|
advisor(level).ifPresent(advisor -> {
|
String consultUrl = value(advisor.get("consultUrl"), "");
|
advisor.put("consultUrl", isAllowedExternalWebUrl(consultUrl) ? consultUrl : "");
|
result.put("advisor", advisor);
|
});
|
return result;
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> familyCardAccess(Long talentId) {
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("enabled", false);
|
result.put("effectiveScope", "仅限卓越人才卡用户");
|
result.put("cardLevel", "");
|
result.put("familyCardLimit", 0);
|
if (talentId == null) {
|
result.put("unavailableReason", "暂未获取到人才卡信息");
|
return result;
|
}
|
List<Map<String, Object>> cards = query("""
|
select c.card_level, c.status as card_status, c.valid_end, c.is_family_card_enabled, c.family_card_limit,
|
t.status as talent_status
|
from talent_card c
|
join talent_user t on t.id = c.talent_id and t.deleted = 0
|
where c.deleted = 0 and c.talent_id = ? and c.card_type = 'PERSONAL'
|
order by c.id desc
|
limit 1
|
""", talentId);
|
if (cards.isEmpty()) {
|
result.put("unavailableReason", "暂未开通人才卡，暂不能使用家庭附属卡服务");
|
return result;
|
}
|
Map<String, Object> card = cards.get(0);
|
String level = value(card.get("cardLevel"), "人才卡");
|
result.put("cardLevel", level);
|
result.put("familyCardLimit", ((Number) card.getOrDefault("familyCardLimit", 0)).intValue());
|
if (!isExcellenceCardLevel(level)) {
|
result.put("unavailableReason", "家庭附属卡仅向卓越人才卡用户开放");
|
return result;
|
}
|
if (!"ACTIVE".equals(cardStatus(card))) {
|
result.put("unavailableReason", cardStatusText(cardStatus(card)) + "，暂不能使用家庭附属卡服务");
|
return result;
|
}
|
if (((Number) card.getOrDefault("isFamilyCardEnabled", 0)).intValue() != 1) {
|
result.put("unavailableReason", "当前人才卡暂未开通家庭附属卡服务");
|
return result;
|
}
|
if (((Number) card.getOrDefault("familyCardLimit", 0)).intValue() < 1) {
|
result.put("unavailableReason", "当前人才卡暂未配置家庭附属卡名额");
|
return result;
|
}
|
result.put("enabled", true);
|
result.put("unavailableReason", "");
|
return result;
|
}
|
|
@Transactional
|
public Map<String, Object> issueDynamicCode(Long talentId) {
|
return issueDynamicCode(talentId, null);
|
}
|
|
@Transactional
|
public Map<String, Object> issueDynamicCode(Long talentId, TalentDynamicCodeRequest request) {
|
Map<String, Object> card = activeCard(talentId);
|
String scene = scene(request == null ? null : request.scene());
|
Long expectedMerchantId = request == null ? null : request.merchantId();
|
if (expectedMerchantId != null && expectedMerchantId > 0) ensureMerchant(expectedMerchantId);
|
if (expectedMerchantId != null && expectedMerchantId <= 0) expectedMerchantId = null;
|
jdbcTemplate.update("update talent_dynamic_code set status = 2 where card_id = ? and status = 0", card.get("cardId"));
|
Instant expiresAt = Instant.now().plusSeconds(DYNAMIC_CODE_SECONDS);
|
String token = nextToken();
|
jdbcTemplate.update("""
|
insert into talent_dynamic_code(code_token, card_id, talent_id, expected_merchant_id, scene, expires_at, status)
|
values (?, ?, ?, ?, ?, ?, 0)
|
""", token, card.get("cardId"), talentId, expectedMerchantId, scene, Timestamp.from(expiresAt));
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("imagePath", "/api/app/talent-cards/dynamic-codes/" + token + "/image");
|
result.put("expiresAt", expiresAt.toEpochMilli());
|
result.put("refreshAfterSeconds", DYNAMIC_CODE_SECONDS);
|
result.put("scene", scene);
|
return result;
|
}
|
|
@Transactional(readOnly = true)
|
public byte[] dynamicCodeImage(String token) {
|
List<Map<String, Object>> rows = query("""
|
select id from talent_dynamic_code
|
where code_token = ? and status = 0 and expires_at > now()
|
""", token);
|
if (rows.isEmpty()) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "动态人才码已失效");
|
}
|
try {
|
BitMatrix matrix = new QRCodeWriter().encode(token, BarcodeFormat.QR_CODE, 360, 360);
|
BufferedImage image = MatrixToImageWriter.toBufferedImage(matrix);
|
ByteArrayOutputStream output = new ByteArrayOutputStream();
|
ImageIO.write(image, "PNG", output);
|
return output.toByteArray();
|
} catch (Exception exception) {
|
throw new BusinessException(ErrorCode.INTERNAL_ERROR, "动态人才码生成失败");
|
}
|
}
|
|
@Transactional
|
public Map<String, Object> verifyDynamicCode(String token, Long merchantId) {
|
if (merchantId == null) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "核销商户不能为空");
|
}
|
List<Map<String, Object>> rows = query("""
|
select d.id, d.card_id, d.talent_id, d.expected_merchant_id, d.scene, c.card_no, c.card_level, c.status as card_status, c.valid_end
|
from talent_dynamic_code d
|
join talent_card c on c.id = d.card_id and c.deleted = 0
|
where d.code_token = ? and d.status = 0 and d.expires_at > now()
|
""", token);
|
if (rows.isEmpty() || !"ACTIVE".equals(cardStatus(rows.get(0)))) {
|
throw new BusinessException(ErrorCode.CONFLICT, "动态人才码已失效或不可核销");
|
}
|
Map<String, Object> code = rows.get(0);
|
Long expectedMerchantId = code.get("expectedMerchantId") instanceof Number number ? number.longValue() : null;
|
if (expectedMerchantId != null && !expectedMerchantId.equals(merchantId)) {
|
throw new BusinessException(ErrorCode.CONFLICT, "该人才码不可在当前商户核验");
|
}
|
int updated = jdbcTemplate.update("""
|
update talent_dynamic_code
|
set status = 1, merchant_id = ?, used_at = now()
|
where id = ? and status = 0 and expires_at > now()
|
""", merchantId, code.get("id"));
|
if (updated == 0) {
|
throw new BusinessException(ErrorCode.CONFLICT, "动态人才码已被使用");
|
}
|
return Map.of(
|
"verified", true,
|
"cardNo", maskCardNo((String) code.get("cardNo")),
|
"cardLevel", value(code.get("cardLevel"), "人才卡")
|
);
|
}
|
|
private void ensureMerchant(Long merchantId) {
|
Integer count = jdbcTemplate.queryForObject("select count(*) from merchant where id = ? and deleted = 0 and status = 1", Integer.class, merchantId);
|
if (count == null || count == 0) throw new BusinessException(ErrorCode.NOT_FOUND, "亮码商户不存在或不可用");
|
}
|
|
private String scene(String value) {
|
String scene = value == null ? "general" : value.trim();
|
return switch (scene) {
|
case "merchant", "parking", "children", "general" -> scene;
|
default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的亮码场景");
|
};
|
}
|
|
private Map<String, Object> activeCard(Long talentId) {
|
if (talentId == null) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "人才信息不能为空");
|
}
|
List<Map<String, Object>> cards = query("""
|
select c.id as card_id, c.card_no, c.card_level, c.valid_end, c.status as card_status, t.status as talent_status
|
from talent_card c
|
join talent_user t on t.id = c.talent_id and t.deleted = 0
|
where c.deleted = 0 and c.talent_id = ? and c.card_type = 'PERSONAL'
|
order by c.id desc
|
limit 1
|
""", talentId);
|
if (cards.isEmpty()) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "未找到人才卡");
|
}
|
Map<String, Object> card = cards.get(0);
|
if (!"ACTIVE".equals(cardStatus(card))) {
|
throw new BusinessException(ErrorCode.CONFLICT, cardStatusText(cardStatus(card)) + "，无法生成动态人才码");
|
}
|
return card;
|
}
|
|
private java.util.Optional<Map<String, Object>> advisor(String cardLevel) {
|
List<Map<String, Object>> advisors = query("""
|
select advisor_name, department, mobile, consult_url
|
from talent_card_advisor
|
where card_level = ? and status = 1
|
limit 1
|
""", cardLevel);
|
if (advisors.isEmpty()) return java.util.Optional.empty();
|
Map<String, Object> row = advisors.get(0);
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("name", row.get("advisorName"));
|
result.put("department", row.get("department"));
|
result.put("mobile", row.get("mobile"));
|
result.put("consultUrl", row.get("consultUrl"));
|
return java.util.Optional.of(result);
|
}
|
|
private boolean isExcellenceCardLevel(String level) {
|
if (level == null) return false;
|
String normalized = level.trim().toUpperCase(Locale.ROOT);
|
return "EXCELLENCE".equals(normalized) || level.contains("卓越") || level.contains("鍗撹秺");
|
}
|
|
private String cardStatus(Map<String, Object> card) {
|
if (((Number) card.getOrDefault("talentStatus", 1)).intValue() != 1) return "FROZEN";
|
if (((Number) card.getOrDefault("cardStatus", 0)).intValue() == 0) return "FROZEN";
|
if (((Number) card.getOrDefault("cardStatus", 0)).intValue() == 2) return "EXPIRED";
|
Object validEnd = card.get("validEnd");
|
if (validEnd instanceof LocalDate date && date.isBefore(LocalDate.now())) return "EXPIRED";
|
return "ACTIVE";
|
}
|
|
private String cardStatusText(String status) {
|
return switch (status) {
|
case "FROZEN" -> "人才卡已冻结";
|
case "EXPIRED" -> "人才卡已过期";
|
default -> "人才卡有效";
|
};
|
}
|
|
private String cardTheme(String level) {
|
if (level.contains("杰出")) return "OUTSTANDING";
|
if (level.contains("菁英") || level.contains("精英")) return "ELITE";
|
return "EXCELLENCE";
|
}
|
|
private String maskName(String name) {
|
if (name == null || name.isBlank()) return "*";
|
return name.length() == 1 ? name + "*" : name.substring(0, 1) + "*".repeat(name.length() - 1);
|
}
|
|
private String maskCardNo(String cardNo) {
|
if (cardNo == null || cardNo.isBlank()) return "-";
|
return cardNo.length() <= 6 ? cardNo : cardNo.substring(0, 2) + "****" + cardNo.substring(cardNo.length() - 4);
|
}
|
|
private String maskWorkUnit(String workUnit) {
|
if (workUnit == null || workUnit.isBlank()) return "未填写";
|
return workUnit.length() <= 4 ? workUnit.substring(0, 1) + "***" : workUnit.substring(0, 2) + "****" + workUnit.substring(workUnit.length() - 2);
|
}
|
|
private String genderText(Object gender) {
|
if (gender instanceof Number value) return value.intValue() == 1 ? "男" : value.intValue() == 2 ? "女" : "-";
|
return "-";
|
}
|
|
private String value(Object source, String fallback) {
|
return source == null || String.valueOf(source).isBlank() ? fallback : String.valueOf(source);
|
}
|
|
private String nextToken() {
|
byte[] bytes = new byte[24];
|
java.security.SecureRandom random = new java.security.SecureRandom();
|
random.nextBytes(bytes);
|
return "TC1" + Base64.getUrlEncoder().withoutPadding().encodeToString(bytes);
|
}
|
|
private boolean isAllowedExternalWebUrl(String value) {
|
if (value.isBlank() || allowedExternalWebHosts.isEmpty()) return false;
|
try {
|
URI uri = URI.create(value);
|
String host = uri.getHost();
|
return "https".equalsIgnoreCase(uri.getScheme()) && host != null && uri.getUserInfo() == null
|
&& allowedExternalWebHosts.contains(host.toLowerCase(Locale.ROOT));
|
} catch (IllegalArgumentException ignored) {
|
return false;
|
}
|
}
|
|
private List<Map<String, Object>> query(String sql, Object... args) {
|
return DatabaseRows.normalize(jdbcTemplate.queryForList(sql, args));
|
}
|
}
|
