package com.talentcard.server.business.admin;
|
|
import java.time.LocalDate;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
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;
|
|
@Service
|
public class ChildInformationAdminService {
|
|
private static final List<String> AUDIT_STATUSES = List.of("待审核", "已通过", "审核不通过");
|
|
private final JdbcTemplate jdbc;
|
|
public ChildInformationAdminService(JdbcTemplate jdbc) {
|
this.jdbc = jdbc;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> page(
|
String childKeyword,
|
String talentKeyword,
|
String cardLevel,
|
String status,
|
LocalDate applyStart,
|
LocalDate applyEnd,
|
int page,
|
int size
|
) {
|
validateFilter(status, applyStart, applyEnd);
|
int safePage = Math.max(page, 1);
|
int safeSize = Math.min(Math.max(size, 1), 100);
|
List<Object> parameters = new ArrayList<>();
|
String where = where(childKeyword, talentKeyword, cardLevel, status, applyStart, applyEnd, parameters);
|
Long total = jdbc.queryForObject("select count(*) " + baseFrom() + where, Long.class, parameters.toArray());
|
if (total == null || total == 0) {
|
return new PageResult<>(List.of(), safePage, safeSize, 0);
|
}
|
|
List<Object> pageParameters = new ArrayList<>(parameters);
|
pageParameters.add(safeSize);
|
pageParameters.add((safePage - 1) * safeSize);
|
List<Map<String, Object>> records = DatabaseRows.normalize(jdbc.queryForList(
|
selectFields() + baseFrom() + where + " order by ci.submitted_at desc, ci.id desc limit ? offset ?",
|
pageParameters.toArray()
|
)).stream().map(this::view).toList();
|
return new PageResult<>(records, safePage, safeSize, total);
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> detail(Long id) {
|
List<Map<String, Object>> rows = DatabaseRows.normalize(jdbc.queryForList(
|
selectFields() + baseFrom() + " where ci.id = ? and ci.deleted = 0",
|
id
|
));
|
if (rows.isEmpty()) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "子女信息不存在");
|
}
|
return view(rows.get(0));
|
}
|
|
@Transactional
|
public Map<String, Object> audit(Long id, String status, String auditRemark) {
|
String targetStatus = clean(status);
|
if (!List.of("已通过", "审核不通过").contains(targetStatus)) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "审核状态只能为已通过或审核不通过");
|
}
|
String remark = clean(auditRemark);
|
if ("审核不通过".equals(targetStatus) && remark.isBlank()) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "审核不通过时必须填写原因");
|
}
|
int updated = jdbc.update("""
|
update child_information
|
set audit_status = ?, reject_reason = ?, updated_at = now()
|
where id = ? and deleted = 0 and audit_status = '待审核'
|
""", targetStatus, "审核不通过".equals(targetStatus) ? remark : null, id);
|
if (updated != 1) {
|
Integer exists = jdbc.queryForObject(
|
"select count(*) from child_information where id = ? and deleted = 0",
|
Integer.class,
|
id
|
);
|
if (exists == null || exists == 0) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "子女信息不存在");
|
}
|
throw new BusinessException(ErrorCode.CONFLICT, "该子女信息已审核，请刷新后重试");
|
}
|
return detail(id);
|
}
|
|
private String selectFields() {
|
return """
|
select ci.id,
|
ci.child_name,
|
ci.gender,
|
ci.birth_date,
|
ci.proof_file_ids,
|
ci.audit_status as status,
|
ci.reject_reason,
|
ci.submitted_at as apply_time,
|
ci.submitted_at as register_time,
|
u.name as talent_name,
|
u.id_card_no as talent_id_card_no,
|
u.gender as talent_gender,
|
u.mobile as talent_mobile,
|
u.contact_address,
|
u.work_address,
|
coalesce(tc.card_no, u.talent_card_no) as talent_card_no,
|
coalesce(tc.card_level, u.talent_level) as card_level
|
""";
|
}
|
|
private String baseFrom() {
|
return """
|
from child_information ci
|
join talent_user u on u.id = ci.talent_id and u.deleted = 0
|
left join talent_card tc on tc.id = (
|
select max(card.id)
|
from talent_card card
|
where card.talent_id = ci.talent_id
|
and card.card_type = 'PERSONAL'
|
and card.deleted = 0
|
)
|
""";
|
}
|
|
private String where(
|
String childKeyword,
|
String talentKeyword,
|
String cardLevel,
|
String status,
|
LocalDate applyStart,
|
LocalDate applyEnd,
|
List<Object> parameters
|
) {
|
StringBuilder sql = new StringBuilder(" where ci.deleted = 0");
|
String child = clean(childKeyword);
|
if (!child.isBlank()) {
|
sql.append(" and ci.child_name like ?");
|
parameters.add("%" + child + "%");
|
}
|
String talent = clean(talentKeyword);
|
if (!talent.isBlank()) {
|
sql.append(" and (u.name like ? or u.mobile like ? or u.talent_card_no like ? or tc.card_no like ?)");
|
String like = "%" + talent + "%";
|
parameters.addAll(List.of(like, like, like, like));
|
}
|
String level = clean(cardLevel);
|
if (!level.isBlank()) {
|
sql.append(" and coalesce(tc.card_level, u.talent_level) = ?");
|
parameters.add(level);
|
}
|
String auditStatus = clean(status);
|
if (!auditStatus.isBlank()) {
|
sql.append(" and ci.audit_status = ?");
|
parameters.add(auditStatus);
|
}
|
if (applyStart != null) {
|
sql.append(" and ci.submitted_at >= ?");
|
parameters.add(applyStart.atStartOfDay());
|
}
|
if (applyEnd != null) {
|
sql.append(" and ci.submitted_at < ?");
|
parameters.add(applyEnd.plusDays(1).atStartOfDay());
|
}
|
return sql.toString();
|
}
|
|
private Map<String, Object> view(Map<String, Object> row) {
|
Map<String, Object> result = new LinkedHashMap<>(row);
|
String proofFileIds = clean(row.get("proofFileIds"));
|
result.remove("proofFileIds");
|
result.put("images", proofFileIds.isBlank()
|
? List.of()
|
: Arrays.stream(proofFileIds.split(","))
|
.map(String::trim)
|
.filter(value -> !value.isBlank())
|
.toList());
|
return result;
|
}
|
|
private void validateFilter(String status, LocalDate applyStart, LocalDate applyEnd) {
|
String value = clean(status);
|
if (!value.isBlank() && !AUDIT_STATUSES.contains(value)) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "子女信息状态不正确");
|
}
|
if (applyStart != null && applyEnd != null && applyStart.isAfter(applyEnd)) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "申请开始时间不能晚于结束时间");
|
}
|
}
|
|
private String clean(Object value) {
|
return value == null ? "" : String.valueOf(value).trim();
|
}
|
}
|
