package com.talentcard.server.mapper;
|
|
import java.time.LocalDate;
|
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,
|
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
|
""")
|
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,
|
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,
|
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 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
|
""")
|
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 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
|
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 *
|
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(*)
|
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 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,
|
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,
|
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}, '%'))
|
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}
|
""")
|
List<Map<String, Object>> selectAdminPage(@Param("packageId") Long packageId,
|
@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);
|
|
@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}, '%'))
|
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,
|
@Param("packageKeyword") String packageKeyword,
|
@Param("talentKeyword") String talentKeyword,
|
@Param("cardLevel") String cardLevel,
|
@Param("claimedStart") LocalDate claimedStart,
|
@Param("claimedEnd") LocalDate claimedEnd);
|
}
|
