package com.talentcard.server.business.portal;
|
|
import java.math.BigDecimal;
|
import java.util.ArrayList;
|
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 UsageRecordPortalService {
|
|
private static final List<String> SUPPORTED_STATUSES = List.of(
|
"pending_payment", "pending_verification", "completed", "cancelled", "processing", "received"
|
);
|
|
private final JdbcTemplate jdbcTemplate;
|
|
public UsageRecordPortalService(JdbcTemplate jdbcTemplate) {
|
this.jdbcTemplate = jdbcTemplate;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> list(Long talentId, String status, int page, int size) {
|
if (talentId == null || talentId <= 0) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "talentId不能为空");
|
}
|
if (page < 1 || size < 1 || size > 50) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "分页参数不正确");
|
}
|
if (status != null && !status.isBlank() && !SUPPORTED_STATUSES.contains(status)) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的记录状态");
|
}
|
Integer talentCount = jdbcTemplate.queryForObject(
|
"select count(*) from talent_user where id = ? and deleted = 0", Integer.class, talentId);
|
if (talentCount == null || talentCount == 0) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "人才用户不存在");
|
}
|
|
String sourceSql = sourceSql();
|
String where = status == null || status.isBlank() ? "" : " where usage_record.status = ?";
|
List<Object> arguments = sourceArguments(talentId);
|
if (!where.isEmpty()) arguments.add(status);
|
Long total = jdbcTemplate.queryForObject(
|
"select count(*) from (" + sourceSql + ") usage_record" + where,
|
Long.class,
|
arguments.toArray());
|
|
List<Object> pageArguments = new ArrayList<>(arguments);
|
pageArguments.add(size);
|
pageArguments.add((page - 1) * size);
|
String pageSql = "select * from (" + sourceSql + ") usage_record" + where
|
+ " order by usage_record.occurred_at desc, usage_record.record_id desc limit ? offset ?";
|
List<Map<String, Object>> records = jdbcTemplate.queryForList(pageSql, pageArguments.toArray()).stream()
|
.map(DatabaseRows::normalize)
|
.map(this::enrichRecord)
|
.toList();
|
return new PageResult<>(records, page, size, total == null ? 0 : total);
|
}
|
|
private List<Object> sourceArguments(Long talentId) {
|
return new ArrayList<>(List.of(talentId, talentId, talentId, talentId));
|
}
|
|
private String sourceSql() {
|
return """
|
select concat('order-', o.id) as record_id,
|
'ORDER' as record_type,
|
o.order_no as business_id,
|
o.id as target_detail_id,
|
o.merchant_id,
|
coalesce(nullif(m.merchant_name, ''), '人才权益服务') as merchant_name,
|
coalesce(nullif(m.merchant_type, ''), 'service') as merchant_type,
|
coalesce(nullif(b.benefit_name, ''), o.order_type, '人才权益订单') as detail_name,
|
o.created_at as occurred_at,
|
case
|
when o.order_status = 0 then 'pending_payment'
|
when o.order_status = 2 then 'cancelled'
|
when o.verify_status = 1 then 'completed'
|
else 'pending_verification'
|
end as status,
|
o.pay_amount as amount_cent,
|
'ORDER_DETAIL' as target_detail_type
|
from benefit_order o
|
left join merchant m on m.id = o.merchant_id
|
left join benefit b on b.id = o.benefit_id
|
where o.deleted = 0 and o.talent_id = ?
|
union all
|
select concat('service-', s.id) as record_id,
|
'SERVICE_APPLICATION' as record_type,
|
s.application_no as business_id,
|
s.id as target_detail_id,
|
null as merchant_id,
|
coalesce(nullif(s.merchant_name, ''), nullif(s.hospital_name, ''), nullif(s.parking_lot, ''), '人才服务') as merchant_name,
|
case
|
when s.application_type like 'MEDICAL%' then 'medical'
|
when s.application_type like 'HOTEL%' then 'hotel'
|
when s.application_type like 'PARKING%' then 'parking'
|
when s.application_type like 'FUEL%' then 'fuel'
|
else 'service'
|
end as merchant_type,
|
coalesce(nullif(s.product_name, ''), nullif(s.package_name, ''), s.application_type) as detail_name,
|
s.submitted_at as occurred_at,
|
case
|
when s.status in ('已处理', '已完成') then 'completed'
|
when s.status in ('已驳回', '已取消') then 'cancelled'
|
else 'processing'
|
end as status,
|
null as amount_cent,
|
'SERVICE_APPLICATION_DETAIL' as target_detail_type
|
from service_application s
|
where s.deleted = 0
|
and exists (
|
select 1 from talent_card c
|
where c.talent_id = ? and c.deleted = 0 and c.card_type = 'PERSONAL' and c.card_no = s.card_no
|
)
|
union all
|
select concat('movie-', r.id) as record_id,
|
'MOVIE_TICKET' as record_type,
|
r.receive_no as business_id,
|
cc.id as target_detail_id,
|
null as merchant_id,
|
'万达影城' as merchant_name,
|
'cinema' as merchant_type,
|
r.package_name as detail_name,
|
r.received_at as occurred_at,
|
'received' as status,
|
null as amount_cent,
|
'MOVIE_TICKET_DETAIL' as target_detail_type
|
from movie_ticket_record r
|
left join cinema_coupon_claim cc on cc.claim_no = r.receive_no and cc.deleted = 0
|
where r.deleted = 0
|
and exists (
|
select 1 from talent_card c
|
where c.talent_id = ? and c.deleted = 0 and c.card_type = 'PERSONAL' and c.card_no = r.card_no
|
)
|
union all
|
select concat('ticket-package-', cl.id) as record_id,
|
'MOVIE_TICKET' as record_type,
|
cl.claim_no as business_id,
|
cl.id as target_detail_id,
|
cl.merchant_id,
|
coalesce(m.merchant_name, (
|
select m0.merchant_name
|
from ticket_package_merchant pm0
|
join merchant m0 on m0.id = pm0.merchant_id and m0.deleted = 0
|
where pm0.package_id = p.id and pm0.deleted = 0
|
order by pm0.sort_no asc, pm0.id asc
|
limit 1
|
), '电影票兑换券') as merchant_name,
|
'cinema' as merchant_type,
|
p.package_name as detail_name,
|
cl.created_at as occurred_at,
|
'received' as status,
|
null as amount_cent,
|
'TICKET_PACKAGE_DETAIL' as target_detail_type
|
from ticket_package_claim cl
|
join ticket_package p on p.id = cl.package_id and p.deleted = 0
|
left join merchant m on m.id = cl.merchant_id and m.deleted = 0
|
where cl.deleted = 0 and cl.talent_id = ?
|
""";
|
}
|
|
private Map<String, Object> enrichRecord(Map<String, Object> record) {
|
Object amountCent = record.remove("amountCent");
|
if (amountCent instanceof Number number) {
|
record.put("amount", BigDecimal.valueOf(number.longValue(), 2));
|
} else {
|
record.put("amount", null);
|
}
|
record.put("merchantType", normalizedMerchantType(String.valueOf(record.getOrDefault("merchantType", "service"))));
|
return record;
|
}
|
|
private String normalizedMerchantType(String merchantType) {
|
return switch (merchantType) {
|
case "food", "餐饮服务" -> "food";
|
case "hotel", "酒店", "公寓" -> "hotel";
|
case "cinema", "影院" -> "cinema";
|
case "theatre", "剧院" -> "theatre";
|
case "medical", "医疗" -> "medical";
|
case "scenic", "景区" -> "scenic";
|
case "parking", "停车" -> "parking";
|
case "fuel", "加油" -> "fuel";
|
case "carWash", "洗车" -> "carWash";
|
case "shopping", "购物" -> "shopping";
|
default -> "service";
|
};
|
}
|
}
|
