package com.talentcard.server.mapper;
|
|
import java.util.List;
|
import java.util.Map;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.talentcard.server.entity.TicketPackageCode;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
import org.apache.ibatis.annotations.Update;
|
|
@Mapper
|
public interface TicketPackageCodeMapper extends BaseMapper<TicketPackageCode> {
|
|
@Select("""
|
select *
|
from ticket_package_code
|
where id = #{id}
|
and deleted = 0
|
limit 1
|
for update
|
""")
|
TicketPackageCode selectByIdForUpdate(@Param("id") Long id);
|
|
@Select("""
|
select *
|
from ticket_package_code
|
where exchange_code = #{exchangeCode}
|
and deleted = 0
|
and (#{packageId} is null or package_id = #{packageId})
|
limit 1
|
for update
|
""")
|
TicketPackageCode selectByExchangeCodeForUpdate(@Param("exchangeCode") String exchangeCode,
|
@Param("packageId") Long packageId);
|
|
@Select("""
|
select *
|
from ticket_package_code
|
where package_id = #{packageId}
|
and status = 'AVAILABLE'
|
and deleted = 0
|
order by id asc
|
limit 1
|
for update
|
""")
|
TicketPackageCode selectAvailableCodeForUpdate(@Param("packageId") Long packageId);
|
|
@Select("""
|
select c.*
|
from ticket_package_code c
|
join ticket_package p on p.id = c.package_id and p.deleted = 0 and p.status = 1
|
where c.deleted = 0
|
and c.status = 'AVAILABLE'
|
and (p.merchant_id = #{merchantId} or exists (
|
select 1 from ticket_package_merchant pm
|
where pm.package_id = p.id and pm.merchant_id = #{merchantId} and pm.deleted = 0
|
))
|
and (p.valid_start is null or p.valid_start <= current_date)
|
and (p.valid_end is null or p.valid_end >= current_date)
|
order by c.id asc
|
limit 1
|
for update
|
""")
|
TicketPackageCode selectAvailableByMerchantForUpdate(@Param("merchantId") Long merchantId);
|
|
@Update("""
|
update ticket_package_code
|
set status = 'LOCKED',
|
locked_by_talent_id = #{talentId},
|
locked_by_card_id = #{cardId},
|
locked_at = now()
|
where id = #{codeId}
|
and status = 'AVAILABLE'
|
and deleted = 0
|
""")
|
int lockForIssue(@Param("codeId") Long codeId,
|
@Param("talentId") Long talentId,
|
@Param("cardId") Long cardId);
|
|
@Update("""
|
update ticket_package_code
|
set status = 'AVAILABLE',
|
locked_by_talent_id = null,
|
locked_by_card_id = null,
|
locked_at = null
|
where id = #{codeId}
|
and status = 'LOCKED'
|
and deleted = 0
|
""")
|
int releaseLock(@Param("codeId") Long codeId);
|
|
@Select("""
|
select c.id,
|
c.package_id as packageId,
|
p.package_no as packageNo,
|
p.package_name as packageName,
|
p.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.valid_start as validStart,
|
p.valid_end as validEnd,
|
p.status as packageStatus,
|
c.exchange_code as exchangeCode,
|
c.batch_no as batchNo,
|
c.status,
|
c.locked_by_talent_id as lockedByTalentId,
|
c.locked_by_card_id as lockedByCardId,
|
c.locked_at as lockedAt,
|
c.issued_claim_id as issuedClaimId,
|
c.issued_at as issuedAt,
|
c.used_at as usedAt,
|
c.remark,
|
c.created_at as createdAt,
|
c.updated_at as updatedAt,
|
cl.claim_no as claimNo,
|
cl.status as claimStatus,
|
cl.talent_id as claimTalentId,
|
cl.talent_name as claimTalentName,
|
cl.talent_mobile as claimTalentMobile,
|
cl.card_no as claimCardNo,
|
cl.card_level as claimCardLevel,
|
cl.idempotency_key as idempotencyKey,
|
cl.valid_end as claimValidEnd,
|
cl.used_at as claimUsedAt
|
from ticket_package_code c
|
join ticket_package p on p.id = c.package_id and p.deleted = 0
|
left join merchant m on m.id = p.merchant_id and m.deleted = 0
|
left join ticket_package_claim cl on cl.code_id = c.id and cl.deleted = 0
|
where c.id = #{id}
|
and c.deleted = 0
|
limit 1
|
""")
|
Map<String, Object> selectAdminDetailById(@Param("id") Long id);
|
|
@Select("""
|
select c.id,
|
c.package_id as packageId,
|
p.package_no as packageNo,
|
p.package_name as packageName,
|
p.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.valid_start as validStart,
|
p.valid_end as validEnd,
|
p.status as packageStatus,
|
c.exchange_code as exchangeCode,
|
c.batch_no as batchNo,
|
c.status,
|
c.locked_by_talent_id as lockedByTalentId,
|
c.locked_by_card_id as lockedByCardId,
|
c.locked_at as lockedAt,
|
c.issued_claim_id as issuedClaimId,
|
c.issued_at as issuedAt,
|
c.used_at as usedAt,
|
c.remark,
|
c.created_at as createdAt,
|
c.updated_at as updatedAt,
|
cl.claim_no as claimNo,
|
cl.status as claimStatus,
|
cl.talent_id as claimTalentId,
|
cl.talent_name as claimTalentName,
|
cl.talent_mobile as claimTalentMobile,
|
cl.card_no as claimCardNo,
|
cl.card_level as claimCardLevel,
|
cl.idempotency_key as idempotencyKey,
|
cl.valid_end as claimValidEnd,
|
cl.used_at as claimUsedAt
|
from ticket_package_code c
|
join ticket_package p on p.id = c.package_id and p.deleted = 0
|
left join merchant m on m.id = p.merchant_id and m.deleted = 0
|
left join ticket_package_claim cl on cl.code_id = c.id and cl.deleted = 0
|
where c.exchange_code = #{exchangeCode}
|
and c.deleted = 0
|
and (#{packageId} is null or c.package_id = #{packageId})
|
limit 1
|
""")
|
Map<String, Object> selectAdminDetailByExchangeCode(@Param("exchangeCode") String exchangeCode,
|
@Param("packageId") Long packageId);
|
|
@Select("""
|
select c.id,
|
c.package_id as packageId,
|
p.package_no as packageNo,
|
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,
|
c.exchange_code as exchangeCode,
|
c.batch_no as batchNo,
|
c.status,
|
c.locked_by_talent_id as lockedByTalentId,
|
c.locked_by_card_id as lockedByCardId,
|
coalesce(cl.talent_name, u.name) as lockedTalentName,
|
coalesce(cl.talent_mobile, u.mobile) as lockedTalentMobile,
|
c.locked_at as lockedAt,
|
c.issued_claim_id as issuedClaimId,
|
c.issued_at as issuedAt,
|
c.used_at as usedAt,
|
c.remark,
|
c.created_at as createdAt,
|
c.updated_at as updatedAt
|
from ticket_package_code c
|
join ticket_package p on p.id = c.package_id and p.deleted = 0
|
left join merchant m on m.id = p.merchant_id and m.deleted = 0
|
left join ticket_package_claim cl on cl.code_id = c.id and cl.deleted = 0
|
left join talent_user u on u.id = c.locked_by_talent_id and u.deleted = 0
|
where c.deleted = 0
|
and (#{packageId} is null or c.package_id = #{packageId})
|
and (#{status} is null or #{status} = '' or c.status = #{status})
|
and (#{batchNo} is null or #{batchNo} = '' or c.batch_no = #{batchNo})
|
and (#{keyword} is null or #{keyword} = '' or cast(c.id as char) like concat('%', #{keyword}, '%') or c.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 (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%') or exists (
|
select 1
|
from ticket_package_merchant pm
|
join merchant mm on mm.id = pm.merchant_id and mm.deleted = 0
|
where pm.package_id = p.id and pm.deleted = 0 and mm.merchant_name like concat('%', #{merchantKeyword}, '%')
|
))
|
order by c.created_at desc, c.id desc
|
limit #{size} offset #{offset}
|
""")
|
List<Map<String, Object>> selectAdminPage(@Param("packageId") Long packageId,
|
@Param("status") String status,
|
@Param("batchNo") String batchNo,
|
@Param("keyword") String keyword,
|
@Param("packageKeyword") String packageKeyword,
|
@Param("merchantKeyword") String merchantKeyword,
|
@Param("size") int size,
|
@Param("offset") int offset);
|
|
@Select("""
|
select count(*)
|
from ticket_package_code c
|
join ticket_package p on p.id = c.package_id and p.deleted = 0
|
left join merchant m on m.id = p.merchant_id and m.deleted = 0
|
where c.deleted = 0
|
and (#{packageId} is null or c.package_id = #{packageId})
|
and (#{status} is null or #{status} = '' or c.status = #{status})
|
and (#{batchNo} is null or #{batchNo} = '' or c.batch_no = #{batchNo})
|
and (#{keyword} is null or #{keyword} = '' or cast(c.id as char) like concat('%', #{keyword}, '%') or c.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 (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%') or exists (
|
select 1
|
from ticket_package_merchant pm
|
join merchant mm on mm.id = pm.merchant_id and mm.deleted = 0
|
where pm.package_id = p.id and pm.deleted = 0 and mm.merchant_name like concat('%', #{merchantKeyword}, '%')
|
))
|
""")
|
Long countAdminPage(@Param("packageId") Long packageId,
|
@Param("status") String status,
|
@Param("batchNo") String batchNo,
|
@Param("keyword") String keyword,
|
@Param("packageKeyword") String packageKeyword,
|
@Param("merchantKeyword") String merchantKeyword);
|
}
|
