From e52934f6527027bd6008c2ade61c3083159efd4a Mon Sep 17 00:00:00 2001
From: shinnosuke <tantao@1hoper.com>
Date: 星期五, 09 十月 2026 18:14:28 +0800
Subject: [PATCH] 更新....
---
talent-card-server/src/main/java/com/talentcard/server/mapper/TicketPackageClaimMapper.java | 273 ++++++++++++++++++++++++++++++++++++++++++++++++++++--
1 files changed, 262 insertions(+), 11 deletions(-)
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/TicketPackageClaimMapper.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/TicketPackageClaimMapper.java
index 0b9fcb9..3d1e1c6 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/mapper/TicketPackageClaimMapper.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/TicketPackageClaimMapper.java
@@ -1,5 +1,6 @@
package com.talentcard.server.mapper;
+import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
@@ -30,7 +31,14 @@
and (c.expire_time is null or c.expire_time > now())
and ((#{cardId} is not null and c.id = #{cardId}) or (#{cardId} is null and c.talent_id = #{talentId}))
and (#{talentId} is null or c.talent_id = #{talentId})
- order by case when c.card_type = 'PERSONAL' then 0 else 1 end, c.id desc
+ order by case when c.card_type = 'PERSONAL' then 0 else 1 end,
+ case
+ when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
+ when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
+ when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
+ else 9
+ end,
+ c.id desc
limit 1
for update
""")
@@ -53,10 +61,18 @@
and (c.expire_time is null or c.expire_time > now())
and ((#{cardId} is not null and c.id = #{cardId}) or (#{cardId} is null and c.talent_id = #{talentId}))
and (#{talentId} is null or c.talent_id = #{talentId})
- order by case when c.card_type = 'PERSONAL' then 0 else 1 end, c.id desc
+ order by case when c.card_type = 'PERSONAL' then 0 else 1 end,
+ case
+ when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
+ when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
+ when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
+ else 9
+ end,
+ c.id desc
limit 1
""")
Map<String, Object> selectActiveCardSnapshot(@Param("talentId") Long talentId, @Param("cardId") Long cardId);
+
@Select("""
select c.id as cardId,
c.talent_id as talentId,
@@ -76,7 +92,13 @@
and (c.valid_start is null or c.valid_start <= current_date)
and (c.valid_end is null or c.valid_end >= current_date)
and (c.expire_time is null or c.expire_time > now())
- order by c.id desc
+ order by case
+ when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
+ when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
+ when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
+ else 9
+ end,
+ c.id desc
limit 1
for update
""")
@@ -101,10 +123,17 @@
and (c.valid_start is null or c.valid_start <= current_date)
and (c.valid_end is null or c.valid_end >= current_date)
and (c.expire_time is null or c.expire_time > now())
- order by c.id desc
+ order by case
+ when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
+ when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
+ when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
+ else 9
+ end,
+ c.id desc
limit 1
""")
Map<String, Object> selectPrimaryCardByFamilyCard(@Param("familyCardId") Long familyCardId);
+
@Select("""
select *
from ticket_package_claim
@@ -112,6 +141,7 @@
limit 1
""")
TicketPackageClaim selectByIdempotencyKey(@Param("idempotencyKey") String idempotencyKey);
+
@Select("""
select *
from ticket_package_claim
@@ -120,6 +150,104 @@
limit 1
""")
TicketPackageClaim selectByCodeId(@Param("codeId") Long codeId);
+
+ @Select("""
+ select *
+ from ticket_package_claim
+ where exchange_code = #{exchangeCode}
+ and deleted = 0
+ limit 1
+ for update
+ """)
+ TicketPackageClaim selectByExchangeCodeForUpdate(@Param("exchangeCode") String exchangeCode);
+
+ @Select("""
+ select c.card_level as cardLevel
+ from talent_card c
+ where c.id = #{cardId}
+ and c.deleted = 0
+ limit 1
+ """)
+ Map<String, Object> selectCardLevelSnapshot(@Param("cardId") Long cardId);
+
+ @Select("""
+ select cl.id as claimId,
+ cl.claim_no as claimNo,
+ cl.package_id as packageId,
+ p.package_name as packageName,
+ p.total_quantity as totalQuantity,
+ cl.merchant_id as merchantId,
+ 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 merchantName,
+ (
+ select group_concat(m1.merchant_name order by pm1.sort_no asc, pm1.id asc separator '、')
+ from ticket_package_merchant pm1
+ join merchant m1 on m1.id = pm1.merchant_id and m1.deleted = 0
+ where pm1.package_id = p.id and pm1.deleted = 0
+ ) as merchantNames,
+ p.app_name as appName,
+ p.usage_text as usageText,
+ (
+ select count(*)
+ from ticket_package_code pc
+ where pc.package_id = p.id and pc.deleted = 0
+ ) as totalCodes,
+ (
+ select count(*)
+ from ticket_package_code pc
+ where pc.package_id = p.id and pc.status = 'AVAILABLE' and pc.deleted = 0
+ ) as availableCodes,
+ (
+ select count(*)
+ from ticket_package_code pc
+ where pc.package_id = p.id and pc.status = 'LOCKED' and pc.deleted = 0
+ ) as lockedCodes,
+ (
+ select count(*)
+ from ticket_package_code pc
+ where pc.package_id = p.id and pc.status = 'ISSUED' and pc.deleted = 0
+ ) as issuedCodes,
+ (
+ select count(*)
+ from ticket_package_code pc
+ where pc.package_id = p.id and pc.status = 'USED' and pc.deleted = 0
+ ) as usedCodes,
+ (
+ select count(*)
+ from ticket_package_code pc
+ where pc.package_id = p.id and pc.status = 'VOID' and pc.deleted = 0
+ ) as voidCodes,
+ (
+ select count(distinct cl2.id)
+ from ticket_package_claim cl2
+ where cl2.package_id = p.id
+ and cl2.deleted = 0
+ and cl2.status in ('ISSUED', 'USED')
+ ) as claimedCount,
+ cl.talent_id as talentId,
+ cl.card_id as cardId,
+ cl.primary_card_id as primaryCardId,
+ cl.card_level as cardLevel,
+ cl.exchange_code as exchangeCode,
+ cl.status,
+ cl.valid_end as validEnd,
+ cl.used_at as usedAt,
+ cl.created_at as createdAt
+ 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.id = #{claimId}
+ and cl.talent_id = #{talentId}
+ and cl.deleted = 0
+ limit 1
+ """)
+ Map<String, Object> selectPortalDetail(@Param("claimId") Long claimId, @Param("talentId") Long talentId);
@Select("""
select count(*)
@@ -146,12 +274,38 @@
@Param("windowStart") LocalDateTime windowStart);
@Select("""
+ select count(*)
+ from ticket_package_claim
+ where deleted = 0
+ and package_id = #{packageId}
+ and primary_card_id = #{primaryCardId}
+ and status in ('ISSUED', 'USED')
+ and created_at >= #{windowStart}
+ """)
+ Long countPackageClaimedInWindow(@Param("packageId") Long packageId,
+ @Param("primaryCardId") Long primaryCardId,
+ @Param("windowStart") LocalDateTime windowStart);
+
+ @Select("""
select cl.id,
cl.claim_no as claimNo,
cl.package_id as packageId,
p.package_name as packageName,
cl.merchant_id as merchantId,
- m.merchant_name as merchantName,
+ coalesce((
+ select group_concat(m1.merchant_name order by pm1.sort_no asc, pm1.id asc separator '、')
+ from ticket_package_merchant pm1
+ join merchant m1 on m1.id = pm1.merchant_id and m1.deleted = 0
+ where pm1.package_id = p.id and pm1.deleted = 0
+ ), m.merchant_name) as merchantNames,
+ 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 merchantName,
cl.talent_id as talentId,
cl.card_id as cardId,
cl.primary_card_id as primaryCardId,
@@ -173,8 +327,12 @@
and (#{packageId} is null or cl.package_id = #{packageId})
and (#{talentId} is null or cl.talent_id = #{talentId})
and (#{status} is null or #{status} = '' or cl.status = #{status})
- and (#{keyword} is null or #{keyword} = '' or cl.claim_no like concat('%', #{keyword}, '%') or cl.exchange_code like concat('%', #{keyword}, '%')
- or cl.talent_name like concat('%', #{keyword}, '%') or cl.talent_mobile like concat('%', #{keyword}, '%'))
+ and (#{keyword} is null or #{keyword} = '' or cl.claim_no like concat('%', #{keyword}, '%') or cl.exchange_code like concat('%', #{keyword}, '%'))
+ and (#{packageKeyword} is null or #{packageKeyword} = '' or p.package_name like concat('%', #{packageKeyword}, '%') or p.package_no like concat('%', #{packageKeyword}, '%'))
+ and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
+ and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
+ and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
+ and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
order by cl.created_at desc, cl.id desc
limit #{size} offset #{offset}
""")
@@ -182,6 +340,11 @@
@Param("talentId") Long talentId,
@Param("status") String status,
@Param("keyword") String keyword,
+ @Param("packageKeyword") String packageKeyword,
+ @Param("talentKeyword") String talentKeyword,
+ @Param("cardLevel") String cardLevel,
+ @Param("claimedStart") LocalDate claimedStart,
+ @Param("claimedEnd") LocalDate claimedEnd,
@Param("size") int size,
@Param("offset") int offset);
@@ -193,11 +356,99 @@
and (#{packageId} is null or cl.package_id = #{packageId})
and (#{talentId} is null or cl.talent_id = #{talentId})
and (#{status} is null or #{status} = '' or cl.status = #{status})
- and (#{keyword} is null or #{keyword} = '' or cl.claim_no like concat('%', #{keyword}, '%') or cl.exchange_code like concat('%', #{keyword}, '%')
- or cl.talent_name like concat('%', #{keyword}, '%') or cl.talent_mobile like concat('%', #{keyword}, '%'))
+ and (#{keyword} is null or #{keyword} = '' or cl.claim_no like concat('%', #{keyword}, '%') or cl.exchange_code like concat('%', #{keyword}, '%'))
+ and (#{packageKeyword} is null or #{packageKeyword} = '' or p.package_name like concat('%', #{packageKeyword}, '%') or p.package_no like concat('%', #{packageKeyword}, '%'))
+ and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
+ and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
+ and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
+ and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
""")
Long countAdminPage(@Param("packageId") Long packageId,
@Param("talentId") Long talentId,
@Param("status") String status,
- @Param("keyword") String keyword);
-}
\ No newline at end of file
+ @Param("keyword") String keyword,
+ @Param("packageKeyword") String packageKeyword,
+ @Param("talentKeyword") String talentKeyword,
+ @Param("cardLevel") String cardLevel,
+ @Param("claimedStart") LocalDate claimedStart,
+ @Param("claimedEnd") LocalDate claimedEnd);
+
+ /**
+ * 后台「核销记录」：只列已核销（status = USED）的领取记录。
+ *
+ * <p>核销商家就是领取记录上的 merchant_id——核销接口要求核销方必须等于该商户，所以不需要再关联
+ * benefit_verify_record。
+ */
+ @Select("""
+ select cl.id,
+ cl.claim_no as claimNo,
+ cl.package_id as packageId,
+ p.package_name as packageName,
+ coalesce((
+ select group_concat(m1.merchant_name order by pm1.sort_no asc, pm1.id asc separator '、')
+ from ticket_package_merchant pm1
+ join merchant m1 on m1.id = pm1.merchant_id and m1.deleted = 0
+ where pm1.package_id = p.id and pm1.deleted = 0
+ ), m.merchant_name) as merchantNames,
+ m.merchant_name as verifyMerchantName,
+ cl.talent_id as talentId,
+ cl.card_id as cardId,
+ cl.card_no as cardNo,
+ cl.card_level as cardLevel,
+ cl.talent_name as talentName,
+ cl.talent_mobile as talentMobile,
+ cl.status,
+ cl.created_at as createdAt,
+ cl.used_at as usedAt
+ 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.status = 'USED'
+ and (#{packageId} is null or cl.package_id = #{packageId})
+ and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
+ and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
+ and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
+ and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
+ and (#{verifiedStart} is null or cl.used_at >= #{verifiedStart})
+ and (#{verifiedEnd} is null or cl.used_at < date_add(cast(#{verifiedEnd} as date), interval 1 day))
+ and (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%'))
+ order by cl.used_at desc, cl.id desc
+ limit #{size} offset #{offset}
+ """)
+ List<Map<String, Object>> selectAdminVerificationPage(@Param("packageId") Long packageId,
+ @Param("talentKeyword") String talentKeyword,
+ @Param("cardLevel") String cardLevel,
+ @Param("claimedStart") LocalDate claimedStart,
+ @Param("claimedEnd") LocalDate claimedEnd,
+ @Param("verifiedStart") LocalDate verifiedStart,
+ @Param("verifiedEnd") LocalDate verifiedEnd,
+ @Param("merchantKeyword") String merchantKeyword,
+ @Param("size") int size,
+ @Param("offset") int offset);
+
+ @Select("""
+ select count(*)
+ 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.status = 'USED'
+ and (#{packageId} is null or cl.package_id = #{packageId})
+ and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
+ and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
+ and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
+ and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
+ and (#{verifiedStart} is null or cl.used_at >= #{verifiedStart})
+ and (#{verifiedEnd} is null or cl.used_at < date_add(cast(#{verifiedEnd} as date), interval 1 day))
+ and (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%'))
+ """)
+ Long countAdminVerificationPage(@Param("packageId") Long packageId,
+ @Param("talentKeyword") String talentKeyword,
+ @Param("cardLevel") String cardLevel,
+ @Param("claimedStart") LocalDate claimedStart,
+ @Param("claimedEnd") LocalDate claimedEnd,
+ @Param("verifiedStart") LocalDate verifiedStart,
+ @Param("verifiedEnd") LocalDate verifiedEnd,
+ @Param("merchantKeyword") String merchantKeyword);
+}
--
Gitblit v1.7.1