package com.talentcard.server.mapper;
|
|
import java.time.LocalDateTime;
|
import java.util.List;
|
import java.util.Map;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.talentcard.server.entity.TicketPackageClaim;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
@Mapper
|
public interface TicketPackageClaimMapper extends BaseMapper<TicketPackageClaim> {
|
|
@Select("""
|
select c.id as cardId,
|
c.talent_id as talentId,
|
c.card_no as cardNo,
|
c.card_level as cardLevel,
|
c.card_type as cardType,
|
u.name as talentName,
|
u.mobile as talentMobile
|
from talent_card c
|
join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
|
where c.deleted = 0
|
and c.status = 1
|
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())
|
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
|
limit 1
|
for update
|
""")
|
Map<String, Object> selectActiveCardSnapshotForUpdate(@Param("talentId") Long talentId, @Param("cardId") Long cardId);
|
|
@Select("""
|
select c.id as cardId,
|
c.talent_id as talentId,
|
c.card_no as cardNo,
|
c.card_level as cardLevel,
|
c.card_type as cardType,
|
u.name as talentName,
|
u.mobile as talentMobile
|
from talent_card c
|
join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
|
where c.deleted = 0
|
and c.status = 1
|
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())
|
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
|
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,
|
c.card_no as cardNo,
|
c.card_level as cardLevel,
|
c.card_type as cardType,
|
u.name as talentName,
|
u.mobile as talentMobile
|
from family_card_application f
|
join talent_card c on c.talent_id = f.talent_id and c.card_type = 'PERSONAL'
|
join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
|
where f.deleted = 0
|
and f.status = '已通过'
|
and f.family_card_id = #{familyCardId}
|
and c.deleted = 0
|
and c.status = 1
|
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
|
limit 1
|
for update
|
""")
|
Map<String, Object> selectPrimaryCardByFamilyCardForUpdate(@Param("familyCardId") Long familyCardId);
|
|
@Select("""
|
select c.id as cardId,
|
c.talent_id as talentId,
|
c.card_no as cardNo,
|
c.card_level as cardLevel,
|
c.card_type as cardType,
|
u.name as talentName,
|
u.mobile as talentMobile
|
from family_card_application f
|
join talent_card c on c.talent_id = f.talent_id and c.card_type = 'PERSONAL'
|
join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
|
where f.deleted = 0
|
and f.status = '已通过'
|
and f.family_card_id = #{familyCardId}
|
and c.deleted = 0
|
and c.status = 1
|
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
|
limit 1
|
""")
|
Map<String, Object> selectPrimaryCardByFamilyCard(@Param("familyCardId") Long familyCardId);
|
@Select("""
|
select *
|
from ticket_package_claim
|
where idempotency_key = #{idempotencyKey} and deleted = 0
|
limit 1
|
""")
|
TicketPackageClaim selectByIdempotencyKey(@Param("idempotencyKey") String idempotencyKey);
|
@Select("""
|
select *
|
from ticket_package_claim
|
where code_id = #{codeId}
|
and deleted = 0
|
limit 1
|
""")
|
TicketPackageClaim selectByCodeId(@Param("codeId") Long codeId);
|
|
@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 countClaimedInWindow(@Param("packageId") Long packageId,
|
@Param("primaryCardId") Long primaryCardId,
|
@Param("windowStart") LocalDateTime windowStart);
|
|
@Select("""
|
select count(*)
|
from ticket_package_claim
|
where deleted = 0
|
and primary_card_id = #{primaryCardId}
|
and status in ('ISSUED', 'USED')
|
and created_at >= #{windowStart}
|
""")
|
Long countTotalClaimedInWindow(@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,
|
cl.talent_id as talentId,
|
cl.card_id as cardId,
|
cl.primary_card_id as primaryCardId,
|
cl.card_no as cardNo,
|
cl.card_level as cardLevel,
|
cl.talent_name as talentName,
|
cl.talent_mobile as talentMobile,
|
cl.exchange_code as exchangeCode,
|
cl.idempotency_key as idempotencyKey,
|
cl.status,
|
cl.valid_end as validEnd,
|
cl.used_at as usedAt,
|
cl.created_at as createdAt,
|
cl.updated_at as updatedAt
|
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 (#{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}, '%'))
|
order by cl.created_at desc, cl.id desc
|
limit #{size} offset #{offset}
|
""")
|
List<Map<String, Object>> selectAdminPage(@Param("packageId") Long packageId,
|
@Param("talentId") Long talentId,
|
@Param("status") String status,
|
@Param("keyword") String keyword,
|
@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
|
where cl.deleted = 0
|
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}, '%'))
|
""")
|
Long countAdminPage(@Param("packageId") Long packageId,
|
@Param("talentId") Long talentId,
|
@Param("status") String status,
|
@Param("keyword") String keyword);
|
}
|
