package com.talentcard.server.business.portal;
|
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import com.talentcard.server.business.BusinessIdGenerator;
|
import com.talentcard.server.business.DatabaseRows;
|
import com.talentcard.server.request.DependentCardApplyRequest;
|
import com.talentcard.server.request.DependentCardUnbindRequest;
|
import com.talentcard.server.common.BusinessException;
|
import com.talentcard.server.response.DependentCardApplyDetailResponse;
|
import com.talentcard.server.response.DependentCardApplyItemResponse;
|
import com.talentcard.server.common.ErrorCode;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
@Service
|
public class FamilyCardPortalService {
|
private final JdbcTemplate jdbc;
|
public FamilyCardPortalService(JdbcTemplate jdbc) { this.jdbc = jdbc; }
|
|
@Transactional(readOnly = true)
|
public List<Map<String, Object>> list(Long talentId) {
|
ensureTalent(talentId);
|
return jdbc.queryForList("""
|
select f.id, f.application_no, f.family_name, f.family_mobile, f.relationship, f.status as application_status,
|
f.apply_time, f.audit_time, f.audit_remark, f.family_card_id, c.status as card_status,
|
c.valid_end
|
from family_card_application f
|
left join talent_card c on c.id = f.family_card_id and c.deleted = 0
|
where f.deleted = 0 and f.talent_id = ? and f.status <> '已解绑'
|
order by f.apply_time desc, f.id desc
|
""", talentId).stream().map(DatabaseRows::normalize).map(this::view).toList();
|
}
|
|
@Transactional(readOnly = true)
|
public List<DependentCardApplyItemResponse> listByTalentCardId(Long talentCardId) {
|
Long talentId = talentIdByCardId(talentCardId);
|
return jdbc.queryForList("""
|
select f.id, f.family_name, f.family_mobile, f.relationship, f.status, f.audit_remark
|
from family_card_application f
|
where f.deleted = 0 and f.talent_id = ? and f.status <> '已解绑'
|
order by f.apply_time desc, f.id desc
|
""", talentId).stream().map(DatabaseRows::normalize).map(this::dependentCardItem).toList();
|
}
|
|
@Transactional
|
public void createByTalentCardId(DependentCardApplyRequest request) {
|
Long talentId = talentIdByCardId(request.talentCardId());
|
create(new FamilyCardApplicationRequest(
|
talentId, request.dependentName(), request.dependentIdCardNo(),
|
request.dependentMobile(), request.relation()
|
));
|
}
|
|
@Transactional
|
public DependentCardApplyItemResponse unbindByTalentCardId(Long id, DependentCardUnbindRequest request) {
|
Long talentId = talentIdByCardId(request.talentCardId());
|
unbind(id, new FamilyCardUnbindRequest(talentId));
|
List<Map<String, Object>> rows = jdbc.queryForList("""
|
select f.id, f.family_name, f.family_mobile, f.relationship, f.status, f.audit_remark
|
from family_card_application f
|
where f.id = ? and f.talent_id = ? and f.deleted = 0
|
limit 1
|
""", id, talentId).stream().map(DatabaseRows::normalize).toList();
|
if (rows.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡申请不存在");
|
return dependentCardItem(rows.get(0));
|
}
|
@Transactional
|
public Map<String, Object> create(FamilyCardApplicationRequest request) {
|
Map<String, Object> owner = ownerCard(request.talentId());
|
if (!"卓越人才卡".equals(string(owner.get("cardLevel")))) throw new BusinessException(ErrorCode.CONFLICT, "家庭附属卡仅向卓越人才卡用户开放");
|
if (!List.of("配偶", "父母", "子女", "其他").contains(request.relationship().trim())) throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的家属关系");
|
if (!validIdCard(request.idCard().trim())) throw new BusinessException(ErrorCode.BAD_REQUEST, "身份证号格式不正确");
|
Integer submitted = jdbc.queryForObject("select count(*) from family_card_application where deleted = 0 and talent_id = ? and status in ('待审核', '已通过', '已驳回')", Integer.class, request.talentId());
|
if (submitted != null && submitted >= 3) throw new BusinessException(ErrorCode.CONFLICT, "同一张主卡最多提交3个家属附属卡申请");
|
Integer occupied = jdbc.queryForObject("select count(*) from family_card_application where deleted = 0 and talent_id = ? and status in ('待审核', '已通过')", Integer.class, request.talentId());
|
if (occupied != null && occupied >= 2) throw new BusinessException(ErrorCode.CONFLICT, "该主卡家属附属卡名额已满，最多绑定2个");
|
Integer duplicate = jdbc.queryForObject("""
|
select count(*) from family_card_application
|
where deleted = 0 and talent_id = ? and id_card_no = ? and status in ('待审核', '已通过')
|
""", Integer.class, request.talentId(), request.idCard().trim().toUpperCase());
|
if (duplicate != null && duplicate > 0) throw new BusinessException(ErrorCode.CONFLICT, "该家属已有待审核或已生效申请");
|
String applicationNo = BusinessIdGenerator.next("FC");
|
jdbc.update("""
|
insert into family_card_application(application_no, talent_id, talent_name, talent_mobile, talent_card_no, card_level,
|
family_name, family_mobile, relationship, id_card_no, status)
|
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '待审核')
|
""", applicationNo, request.talentId(), owner.get("talentName"), owner.get("talentMobile"), owner.get("cardNo"), owner.get("cardLevel"),
|
request.name().trim(), request.mobile().trim(), request.relationship().trim(), request.idCard().trim().toUpperCase());
|
Map<String, Object> result = jdbc.queryForList("select * from family_card_application where application_no = ?", applicationNo).stream()
|
.map(DatabaseRows::normalize).map(this::view).findFirst().orElseThrow();
|
result.put("applicationId", result.get("id"));
|
result.put("estimatedProcessingTime", "预计 3 个工作日内完成审核");
|
return result;
|
}
|
|
@Transactional
|
public Map<String, Object> unbind(Long applicationId, FamilyCardUnbindRequest request) {
|
return unbindMany(new FamilyCardBatchUnbindRequest(request.talentId(), List.of(applicationId)));
|
}
|
|
@Transactional
|
public Map<String, Object> unbindMany(FamilyCardBatchUnbindRequest request) {
|
List<Long> applicationIds = request.applicationIds().stream().distinct().toList();
|
List<Map<String, Object>> members = applicationIds.stream().map(applicationId -> {
|
List<Map<String, Object>> rows = jdbc.queryForList("""
|
select f.*, f.status as application_status, c.status as card_status
|
from family_card_application f left join talent_card c on c.id = f.family_card_id and c.deleted = 0
|
where f.deleted = 0 and f.id = ? and f.talent_id = ?
|
""", applicationId, request.talentId()).stream().map(DatabaseRows::normalize).toList();
|
if (rows.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡不存在或无权操作");
|
Map<String, Object> current = rows.get(0);
|
if (!canUnbind(current)) throw new BusinessException(ErrorCode.CONFLICT, unbindReason(current));
|
return current;
|
}).toList();
|
for (Map<String, Object> member : members) {
|
Long cardId = number(member.get("familyCardId"));
|
jdbc.update("update talent_card set status = 0 where id = ? and deleted = 0", cardId);
|
jdbc.update("""
|
update family_card_application set status = '已解绑', audit_remark = '用户主动解除绑定', updated_at = now()
|
where id = ? and talent_id = ? and deleted = 0
|
""", number(member.get("id")), request.talentId());
|
}
|
return Map.of("applicationIds", applicationIds, "unbound", true);
|
}
|
|
|
@Transactional(readOnly = true)
|
public DependentCardApplyDetailResponse detail(Long id, Long talentCardId) {
|
Long talentId = talentIdByCardId(talentCardId);
|
if (id == null || id <= 0) throw new BusinessException(ErrorCode.BAD_REQUEST, "申请ID不能为空");
|
List<Map<String, Object>> rows = jdbc.queryForList("""
|
select f.id, f.application_no, f.family_name, f.id_card_no, f.family_mobile, f.relationship, f.status,
|
f.audit_remark, f.apply_time
|
from family_card_application f
|
where f.id = ? and f.talent_id = ? and f.deleted = 0
|
limit 1
|
""", id, talentId).stream().map(DatabaseRows::normalize).toList();
|
if (rows.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡申请不存在");
|
return dependentCardDetail(rows.get(0));
|
}
|
|
@Transactional
|
public DependentCardApplyItemResponse resubmit(Long id, DependentCardApplyRequest request) {
|
Long talentId = talentIdByCardId(request.talentCardId());
|
if (id == null || id <= 0) throw new BusinessException(ErrorCode.BAD_REQUEST, "申请ID不能为空");
|
List<Map<String, Object>> current = jdbc.queryForList("""
|
select f.status
|
from family_card_application f
|
where f.id = ? and f.talent_id = ? and f.deleted = 0
|
limit 1
|
""", id, talentId).stream().map(DatabaseRows::normalize).toList();
|
if (current.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡申请不存在");
|
if (!"已驳回".equals(string(current.get(0).get("status")))) throw new BusinessException(ErrorCode.CONFLICT, "仅已驳回的申请可以修改重新提交");
|
int updated = jdbc.update("""
|
update family_card_application
|
set family_name = ?, id_card_no = ?, family_mobile = ?, relationship = ?,
|
status = '待审核', audit_by = null, audit_time = null, audit_remark = null, updated_at = now()
|
where id = ? and talent_id = ? and status = '已驳回' and deleted = 0
|
""", request.dependentName().trim(), request.dependentIdCardNo().trim().toUpperCase(),
|
request.dependentMobile().trim(), request.relation().trim(), id, talentId);
|
if (updated != 1) throw new BusinessException(ErrorCode.CONFLICT, "申请状态已变化，请刷新后重试");
|
List<Map<String, Object>> rows = jdbc.queryForList("""
|
select f.id, f.family_name, f.family_mobile, f.relationship, f.status, f.audit_remark
|
from family_card_application f
|
where f.id = ? and f.talent_id = ? and f.deleted = 0
|
limit 1
|
""", id, talentId).stream().map(DatabaseRows::normalize).toList();
|
if (rows.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡申请不存在");
|
return dependentCardItem(rows.get(0));
|
}
|
|
private DependentCardApplyDetailResponse dependentCardDetail(Map<String, Object> row) {
|
Integer status = switch (string(row.get("status"))) {
|
case "待审核" -> 0;
|
case "已通过" -> 1;
|
case "已驳回" -> 2;
|
case "已解绑" -> 3;
|
default -> -1;
|
};
|
String statusText = switch (status) {
|
case 0 -> "待审核";
|
case 1 -> "已通过";
|
case 2 -> "已驳回";
|
case 3 -> "已解绑";
|
default -> "未知";
|
};
|
return new DependentCardApplyDetailResponse(
|
number(row.get("id")),
|
string(row.get("applicationNo")),
|
string(row.get("familyName")),
|
string(row.get("idCardNo")),
|
string(row.get("familyMobile")),
|
string(row.get("relationship")),
|
status,
|
statusText,
|
string(row.get("auditRemark")),
|
toLocalDateTime(row.get("applyTime"))
|
);
|
}
|
|
private java.time.LocalDateTime toLocalDateTime(Object value) {
|
if (value instanceof java.time.LocalDateTime dateTime) return dateTime;
|
if (value instanceof java.sql.Timestamp timestamp) return timestamp.toLocalDateTime();
|
return null;
|
}
|
private Long talentIdByCardId(Long talentCardId) {
|
if (talentCardId == null || talentCardId <= 0) throw new BusinessException(ErrorCode.NOT_FOUND, "主卡不存在或已失效");
|
List<Map<String, Object>> rows = jdbc.queryForList("""
|
select talent_id from talent_card
|
where id = ? and deleted = 0 and card_type = 'PERSONAL' and status = 1
|
limit 1
|
""", talentCardId).stream().map(DatabaseRows::normalize).toList();
|
if (rows.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "主卡不存在或已失效");
|
return number(rows.get(0).get("talentId"));
|
}
|
|
private DependentCardApplyItemResponse dependentCardItem(Map<String, Object> row) {
|
Integer status = switch (string(row.get("status"))) {
|
case "待审核" -> 0;
|
case "已通过" -> 1;
|
case "已驳回" -> 2;
|
case "已解绑" -> 3;
|
default -> -1;
|
};
|
String statusText = switch (status) {
|
case 0 -> "待审核";
|
case 1 -> "已通过";
|
case 2 -> "已驳回";
|
case 3 -> "已解绑";
|
default -> "未知";
|
};
|
return new DependentCardApplyItemResponse(
|
number(row.get("id")), string(row.get("familyName")),
|
maskMobile(string(row.get("familyMobile"))), status, statusText,
|
string(row.get("relationship")), string(row.get("auditRemark"))
|
);
|
}
|
private Map<String, Object> ownerCard(Long talentId) {
|
List<Map<String, Object>> rows = jdbc.queryForList("""
|
select c.card_no, c.card_level, c.is_family_card_enabled, c.family_card_limit, u.name as talent_name, u.mobile as talent_mobile
|
from talent_card c join talent_user u on u.id = c.talent_id
|
where c.talent_id = ? and c.deleted = 0 and c.card_type = 'PERSONAL' and u.deleted = 0
|
order by c.id desc limit 1
|
""", talentId).stream().map(DatabaseRows::normalize).toList();
|
if (rows.isEmpty()) throw new BusinessException(ErrorCode.CONFLICT, "当前用户未开通人才卡");
|
return rows.get(0);
|
}
|
|
private void ensureTalent(Long talentId) { if (talentId == null || talentId <= 0 || !Boolean.TRUE.equals(jdbc.queryForObject("select exists(select 1 from talent_user where id = ? and deleted = 0)", Boolean.class, talentId))) throw new BusinessException(ErrorCode.NOT_FOUND, "人才用户不存在"); }
|
private Map<String, Object> view(Map<String, Object> row) {
|
Map<String, Object> result = new LinkedHashMap<>(row);
|
String status = string(row.get("applicationStatus"));
|
if (status.isBlank()) status = string(row.get("status"));
|
// Family-card APIs only expose display-safe fields to the mini program.
|
result.remove("idCardNo");
|
result.remove("familyMobile");
|
result.remove("talentMobile");
|
result.remove("familyName");
|
result.remove("talentName");
|
result.remove("talentCardNo");
|
result.put("name", maskName(string(row.get("familyName"))));
|
result.put("initial", string(row.get("familyName")).isBlank() ? "" : string(row.get("familyName")).substring(0, 1));
|
result.put("mobile", maskMobile(string(row.get("familyMobile"))));
|
result.put("statusText", statusText(status, row.get("cardStatus")));
|
result.put("canUnbind", canUnbind(row));
|
result.put("unbindReason", unbindReason(row));
|
return result;
|
}
|
private boolean canUnbind(Map<String, Object> row) { return "已通过".equals(string(row.get("applicationStatus"))) && number(row.get("familyCardId")) != null && number(row.get("cardStatus")) != null && number(row.get("cardStatus")) == 1; }
|
private String unbindReason(Map<String, Object> row) { if ("待审核".equals(string(row.get("applicationStatus")))) return "家属卡正在审核，暂不可解绑"; if (!"已通过".equals(string(row.get("applicationStatus")))) return "当前家属卡状态不可解绑"; if (number(row.get("cardStatus")) == null || number(row.get("cardStatus")) != 1) return "家属卡未处于有效状态"; return "可解除绑定"; }
|
private String statusText(String applicationStatus, Object cardStatus) { if ("待审核".equals(applicationStatus)) return "审核中"; if ("已驳回".equals(applicationStatus)) return "已驳回"; if ("已解绑".equals(applicationStatus)) return "已解除"; return number(cardStatus) != null && number(cardStatus) == 1 ? "已生效" : "已失效"; }
|
private Long number(Object value) { return value instanceof Number number ? number.longValue() : null; }
|
private boolean validIdCard(String value) { return value.matches("[0-9]{17}[0-9X]"); }
|
private String string(Object value) { return value == null ? "" : String.valueOf(value); }
|
private String maskName(String value) { if (value.isBlank()) return ""; return value.length() == 1 ? "*" : value.substring(0, 1) + "*".repeat(value.length() - 1); }
|
private String maskMobile(String value) { return value.length() == 11 ? value.substring(0, 3) + "****" + value.substring(7) : ""; }
|
}
|
