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.TicketPackage;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
@Mapper
|
public interface TicketPackageMapper extends BaseMapper<TicketPackage> {
|
|
@Select("""
|
select p.id,
|
p.package_no as packageNo,
|
p.package_name as packageName,
|
p.total_quantity as totalQuantity,
|
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(pm1.merchant_id order by pm1.sort_no asc, pm1.id asc separator ',')
|
from ticket_package_merchant pm1
|
where pm1.package_id = p.id and pm1.deleted = 0
|
) as merchantIdsText,
|
(
|
select group_concat(m1.merchant_name order by pm2.sort_no asc, pm2.id asc separator '、')
|
from ticket_package_merchant pm2
|
join merchant m1 on m1.id = pm2.merchant_id and m1.deleted = 0
|
where pm2.package_id = p.id and pm2.deleted = 0
|
) as merchantNames,
|
(
|
select group_concat(concat(r.talent_level, '|', r.enabled, '|', r.claimable_count, '|', r.reset_cycle) order by r.id asc separator ';;')
|
from ticket_package_level_rule r
|
where r.package_id = p.id and r.deleted = 0
|
) as levelRulesText,
|
p.cover_url as coverUrl,
|
p.talent_levels as talentLevels,
|
p.claim_price as claimPrice,
|
p.per_user_limit as perUserLimit,
|
p.valid_start as validStart,
|
p.valid_end as validEnd,
|
p.app_name as appName,
|
p.app_jump_url as appJumpUrl,
|
p.usage_text as usageText,
|
p.status,
|
p.sort_no as sortNo,
|
p.created_at as createdAt,
|
p.updated_at as updatedAt,
|
count(distinct c.id) as totalCodes,
|
count(distinct case when c.status = 'AVAILABLE' and c.deleted = 0 then c.id end) as availableCodes,
|
count(distinct case when c.status = 'LOCKED' and c.deleted = 0 then c.id end) as lockedCodes,
|
count(distinct case when c.status = 'ISSUED' and c.deleted = 0 then c.id end) as issuedCodes,
|
count(distinct case when c.status = 'USED' and c.deleted = 0 then c.id end) as usedCodes,
|
count(distinct case when c.status = 'VOID' and c.deleted = 0 then c.id end) as voidCodes,
|
count(distinct case when cl.status in ('ISSUED', 'USED') and cl.deleted = 0 then cl.id end) as claimedCount
|
from ticket_package p
|
left join merchant m on m.id = p.merchant_id and m.deleted = 0
|
left join ticket_package_code c on c.package_id = p.id and c.deleted = 0
|
left join ticket_package_claim cl on cl.package_id = p.id and cl.deleted = 0
|
where p.deleted = 0
|
and (#{merchantId} is null or 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 (#{status} is null or p.status = #{status})
|
and (#{keyword} is null or #{keyword} = '' or p.package_name like concat('%', #{keyword}, '%') or p.package_no like concat('%', #{keyword}, '%'))
|
and (#{merchantKeyword} is null or #{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}, '%')
|
) or exists (
|
select 1
|
from merchant mm
|
where mm.id = p.merchant_id and mm.deleted = 0 and mm.merchant_name like concat('%', #{merchantKeyword}, '%')
|
))
|
group by p.id
|
order by p.sort_no desc, p.id desc
|
limit #{size} offset #{offset}
|
""")
|
List<Map<String, Object>> selectAdminPage(@Param("merchantId") Long merchantId,
|
@Param("status") Integer status,
|
@Param("keyword") String keyword,
|
@Param("merchantKeyword") String merchantKeyword,
|
@Param("size") int size,
|
@Param("offset") int offset);
|
|
@Select("""
|
select count(*)
|
from ticket_package p
|
where p.deleted = 0
|
and (#{merchantId} is null or 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 (#{status} is null or p.status = #{status})
|
and (#{keyword} is null or #{keyword} = '' or p.package_name like concat('%', #{keyword}, '%') or p.package_no like concat('%', #{keyword}, '%'))
|
and (#{merchantKeyword} is null or #{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}, '%')
|
) or exists (
|
select 1
|
from merchant mm
|
where mm.id = p.merchant_id and mm.deleted = 0 and mm.merchant_name like concat('%', #{merchantKeyword}, '%')
|
))
|
""")
|
Long countAdminPage(@Param("merchantId") Long merchantId,
|
@Param("status") Integer status,
|
@Param("keyword") String keyword,
|
@Param("merchantKeyword") String merchantKeyword);
|
|
@Select("""
|
select p.id,
|
p.package_no as packageNo,
|
p.package_name as packageName,
|
p.total_quantity as totalQuantity,
|
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(pm1.merchant_id order by pm1.sort_no asc, pm1.id asc separator ',')
|
from ticket_package_merchant pm1
|
where pm1.package_id = p.id and pm1.deleted = 0
|
) as merchantIdsText,
|
(
|
select group_concat(m1.merchant_name order by pm2.sort_no asc, pm2.id asc separator '、')
|
from ticket_package_merchant pm2
|
join merchant m1 on m1.id = pm2.merchant_id and m1.deleted = 0
|
where pm2.package_id = p.id and pm2.deleted = 0
|
) as merchantNames,
|
(
|
select group_concat(concat(r.talent_level, '|', r.enabled, '|', r.claimable_count, '|', r.reset_cycle) order by r.id asc separator ';;')
|
from ticket_package_level_rule r
|
where r.package_id = p.id and r.deleted = 0
|
) as levelRulesText,
|
p.cover_url as coverUrl,
|
p.talent_levels as talentLevels,
|
p.claim_price as claimPrice,
|
p.per_user_limit as perUserLimit,
|
p.valid_start as validStart,
|
p.valid_end as validEnd,
|
p.app_name as appName,
|
p.app_jump_url as appJumpUrl,
|
p.usage_text as usageText,
|
p.status,
|
p.sort_no as sortNo,
|
p.created_at as createdAt,
|
p.updated_at as updatedAt,
|
count(distinct c.id) as totalCodes,
|
count(distinct case when c.status = 'AVAILABLE' and c.deleted = 0 then c.id end) as availableCodes,
|
count(distinct case when c.status = 'LOCKED' and c.deleted = 0 then c.id end) as lockedCodes,
|
count(distinct case when c.status = 'ISSUED' and c.deleted = 0 then c.id end) as issuedCodes,
|
count(distinct case when c.status = 'USED' and c.deleted = 0 then c.id end) as usedCodes,
|
count(distinct case when c.status = 'VOID' and c.deleted = 0 then c.id end) as voidCodes,
|
count(distinct case when cl.status in ('ISSUED', 'USED') and cl.deleted = 0 then cl.id end) as claimedCount
|
from ticket_package p
|
left join merchant m on m.id = p.merchant_id and m.deleted = 0
|
left join ticket_package_code c on c.package_id = p.id and c.deleted = 0
|
left join ticket_package_claim cl on cl.package_id = p.id and cl.deleted = 0
|
where p.id = #{id} and p.deleted = 0
|
group by p.id
|
limit 1
|
""")
|
Map<String, Object> selectAdminDetail(@Param("id") Long id);
|
|
@Select("""
|
select p.id,
|
p.package_no as packageNo,
|
p.package_name as packageName,
|
p.total_quantity as totalQuantity,
|
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.cover_url as coverUrl,
|
p.talent_levels as talentLevels,
|
p.claim_price as claimPrice,
|
p.per_user_limit as perUserLimit,
|
p.valid_start as validStart,
|
p.valid_end as validEnd,
|
p.app_name as appName,
|
p.app_jump_url as appJumpUrl,
|
p.usage_text as usageText,
|
p.status,
|
p.sort_no as sortNo,
|
count(distinct c.id) as totalCodes,
|
count(distinct case when c.status = 'AVAILABLE' and c.deleted = 0 then c.id end) as availableCodes,
|
count(distinct case when c.status = 'LOCKED' and c.deleted = 0 then c.id end) as lockedCodes,
|
count(distinct case when c.status = 'ISSUED' and c.deleted = 0 then c.id end) as issuedCodes,
|
count(distinct case when c.status = 'USED' and c.deleted = 0 then c.id end) as usedCodes,
|
count(distinct case when c.status = 'VOID' and c.deleted = 0 then c.id end) as voidCodes,
|
count(distinct case when cl.status in ('ISSUED', 'USED') and cl.deleted = 0 then cl.id end) as claimedCount
|
from ticket_package p
|
left join merchant m on m.id = p.merchant_id and m.deleted = 0
|
left join ticket_package_code c on c.package_id = p.id and c.deleted = 0
|
left join ticket_package_claim cl on cl.package_id = p.id and cl.deleted = 0
|
where p.deleted = 0
|
and p.status = 1
|
and (p.valid_start is null or p.valid_start <= current_date)
|
and (p.valid_end is null or p.valid_end >= current_date)
|
and (#{merchantId} is null or 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
|
))
|
group by p.id
|
order by p.sort_no desc, p.id desc
|
""")
|
List<Map<String, Object>> selectPortalPackages(@Param("merchantId") Long merchantId);
|
|
@Select("""
|
select p.*
|
from ticket_package p
|
where p.id = #{id} and p.deleted = 0
|
limit 1
|
for update
|
""")
|
TicketPackage selectByIdForUpdate(@Param("id") Long id);
|
|
@Select("""
|
select count(*)
|
from ticket_package p
|
where p.id = #{packageId}
|
and p.deleted = 0
|
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
|
))
|
""")
|
Long countPackageMerchant(@Param("packageId") Long packageId, @Param("merchantId") Long merchantId);
|
}
|
