package com.talentcard.server.mapper;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.talentcard.server.entity.TransportCarWashCredential;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.time.LocalDateTime;
|
import java.util.List;
|
import java.util.Map;
|
|
@Mapper
|
public interface TransportCarWashCredentialMapper extends BaseMapper<TransportCarWashCredential> {
|
@Select("""
|
select c.*, m.merchant_name as merchantName,
|
u.name as talentName, u.mobile as talentMobile,
|
tc.card_no as cardNo, tc.card_type as cardType,
|
pc.card_no as primaryCardNo
|
from transport_car_wash_credential c
|
join merchant m on m.id = c.merchant_id and m.deleted = 0
|
join talent_user u on u.id = c.talent_id and u.deleted = 0
|
join talent_card tc on tc.id = c.card_id and tc.deleted = 0
|
join talent_card pc on pc.id = c.primary_card_id and pc.deleted = 0
|
where c.id = #{id} and c.deleted = 0
|
limit 1
|
""")
|
Map<String, Object> selectDetail(@Param("id") Long id);
|
|
@Select("""
|
<script>
|
select c.*, m.merchant_name as merchantName,
|
u.name as talentName, u.mobile as talentMobile,
|
tc.card_no as cardNo, tc.card_type as cardType,
|
pc.card_no as primaryCardNo
|
from transport_car_wash_credential c
|
join merchant m on m.id = c.merchant_id and m.deleted = 0
|
join talent_user u on u.id = c.talent_id and u.deleted = 0
|
join talent_card tc on tc.id = c.card_id and tc.deleted = 0
|
join talent_card pc on pc.id = c.primary_card_id and pc.deleted = 0
|
where c.deleted = 0
|
<if test="merchantId != null">and c.merchant_id = #{merchantId}</if>
|
<if test="status != null">and c.status = #{status}</if>
|
<if test="keyword != null and keyword != ''">and (c.credential_no like concat('%', #{keyword}, '%') or u.name like concat('%', #{keyword}, '%') or u.mobile like concat('%', #{keyword}, '%') or tc.card_no like concat('%', #{keyword}, '%'))</if>
|
<if test="startTime != null">and c.submitted_at >= #{startTime}</if>
|
<if test="endTime != null">and c.submitted_at < #{endTime}</if>
|
order by c.submitted_at desc, c.id desc
|
limit #{size} offset #{offset}
|
</script>
|
""")
|
List<Map<String, Object>> selectPage(@Param("merchantId") Long merchantId,
|
@Param("status") Integer status,
|
@Param("keyword") String keyword,
|
@Param("startTime") LocalDateTime startTime,
|
@Param("endTime") LocalDateTime endTime,
|
@Param("size") int size,
|
@Param("offset") int offset);
|
|
@Select("""
|
<script>
|
select count(*) from transport_car_wash_credential c
|
left join talent_user u on u.id = c.talent_id and u.deleted = 0
|
left join talent_card tc on tc.id = c.card_id and tc.deleted = 0
|
where c.deleted = 0
|
<if test="merchantId != null">and c.merchant_id = #{merchantId}</if>
|
<if test="status != null">and c.status = #{status}</if>
|
<if test="keyword != null and keyword != ''">and (c.credential_no like concat('%', #{keyword}, '%') or u.name like concat('%', #{keyword}, '%') or u.mobile like concat('%', #{keyword}, '%') or tc.card_no like concat('%', #{keyword}, '%'))</if>
|
<if test="startTime != null">and c.submitted_at >= #{startTime}</if>
|
<if test="endTime != null">and c.submitted_at < #{endTime}</if>
|
</script>
|
""")
|
Long countPage(@Param("merchantId") Long merchantId,
|
@Param("status") Integer status,
|
@Param("keyword") String keyword,
|
@Param("startTime") LocalDateTime startTime,
|
@Param("endTime") LocalDateTime endTime);
|
|
@Select("""
|
select c.id as cardId, c.talent_id as talentId, c.card_no as cardNo,
|
c.card_type as cardType, c.card_level as cardLevel,
|
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
|
where c.id = #{cardId} and c.talent_id = #{talentId} 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())
|
limit 1
|
""")
|
Map<String, Object> selectActiveCard(@Param("talentId") Long talentId, @Param("cardId") Long cardId);
|
|
@Select("""
|
select a.talent_id as primaryTalentId, pc.id as primaryCardId,
|
pc.card_no as primaryCardNo, pc.card_level as cardLevel
|
from family_card_application a
|
join talent_card pc on pc.talent_id = a.talent_id and pc.card_type = 'PERSONAL'
|
and pc.deleted = 0 and pc.status = 1
|
where a.family_card_id = #{familyCardId} and a.deleted = 0
|
order by a.id desc, pc.id desc limit 1
|
""")
|
Map<String, Object> selectPrimaryCard(@Param("familyCardId") Long familyCardId);
|
|
@Select("select count(*) from merchant where id = #{merchantId} and merchant_type_code = 'carWash' and deleted = 0 and status = 1")
|
Long countActiveMerchant(@Param("merchantId") Long merchantId);
|
|
@Select("""
|
select coalesce(sum(verify_amount), 0) from verify_record
|
where card_id = #{primaryCardId} and verify_type = 'CAR_WASH'
|
and status = 1 and verify_time >= #{windowStart}
|
""")
|
Long countUsedQuota(@Param("primaryCardId") Long primaryCardId,
|
@Param("windowStart") LocalDateTime windowStart);
|
|
@Select("select * from transport_car_wash_credential where id = #{id} and deleted = 0 limit 1 for update")
|
TransportCarWashCredential selectByIdForUpdate(@Param("id") Long id);
|
}
|
