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.TransportParkingPlateRegistration;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
@Mapper
|
public interface TransportParkingPlateRegistrationMapper extends BaseMapper<TransportParkingPlateRegistration> {
|
|
@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,
|
u.id_card_no as talentIdCardNo
|
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 m.id as merchantId,
|
m.merchant_name as merchantName,
|
m.contact_mobile as contactMobile,
|
m.address,
|
m.cover_url as coverUrl,
|
m.status as merchantStatus,
|
b.id as benefitId,
|
b.action_mode as actionMode,
|
b.status as benefitStatus
|
from merchant m
|
left join transport_parking_benefit b on b.merchant_id = m.id and b.deleted = 0
|
where m.id = #{merchantId}
|
and m.deleted = 0
|
and m.merchant_type_code = 'parking'
|
limit 1
|
""")
|
Map<String, Object> selectParkingMerchant(@Param("merchantId") Long merchantId);
|
|
@Select("""
|
select count(*)
|
from transport_parking_plate_registration r
|
left join merchant m on m.id = r.merchant_id
|
left join transport_parking_lot l on l.id = r.parking_lot_id
|
and l.merchant_id = r.merchant_id and l.deleted = 0
|
left join talent_user u on u.id = r.talent_id
|
left join talent_card c on c.id = coalesce(r.card_id, (
|
select c2.id
|
from talent_card c2
|
where c2.talent_id = r.talent_id
|
and c2.deleted = 0
|
and c2.status = 1
|
order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
|
limit 1
|
))
|
where r.deleted = 0
|
and (#{merchantId} is null or r.merchant_id = #{merchantId})
|
and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
|
and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
|
and (#{status} is null or #{status} = '' or r.status = #{status})
|
and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
|
and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
|
and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
|
and (#{keyword} is null or #{keyword} = ''
|
or r.registration_no like concat('%', #{keyword}, '%')
|
or r.plate_number like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
|
or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%'))
|
and (#{talentName} is null or #{talentName} = '' or coalesce(r.talent_name, u.name) like concat('%', #{talentName}, '%'))
|
and (#{talentMobile} is null or #{talentMobile} = '' or coalesce(r.talent_mobile, u.mobile) like concat('%', #{talentMobile}, '%'))
|
and (#{plateNumber} is null or #{plateNumber} = '' or r.plate_number like concat('%', #{plateNumber}, '%'))
|
and (#{serviceKeyword} is null or #{serviceKeyword} = ''
|
or m.merchant_name like concat('%', #{serviceKeyword}, '%')
|
or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{serviceKeyword}, '%'))
|
""")
|
Long countAdminPage(@Param("merchantId") Long merchantId,
|
@Param("parkingLotId") Long parkingLotId,
|
@Param("keyword") String keyword,
|
@Param("talentName") String talentName,
|
@Param("talentMobile") String talentMobile,
|
@Param("plateNumber") String plateNumber,
|
@Param("serviceKeyword") String serviceKeyword,
|
@Param("cardLevel") String cardLevel,
|
@Param("status") String status,
|
@Param("submissionType") String submissionType,
|
@Param("submittedStart") LocalDateTime submittedStart,
|
@Param("submittedEnd") LocalDateTime submittedEnd);
|
|
@Select("""
|
select r.id,
|
r.registration_no as registrationNo,
|
r.merchant_id as merchantId,
|
m.merchant_name as merchantName,
|
r.benefit_id as benefitId,
|
r.parking_lot_id as parkingLotId,
|
coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
|
r.talent_id as talentId,
|
r.card_id as cardId,
|
coalesce(r.talent_name, u.name) as talentName,
|
coalesce(r.talent_mobile, u.mobile) as talentMobile,
|
coalesce(r.card_no, c.card_no) as cardNo,
|
coalesce(r.card_level, c.card_level) as cardLevel,
|
r.plate_number as plateNumber,
|
r.submission_type as submissionType,
|
r.submitted_at as submittedAt,
|
r.status,
|
r.handled_by as handledBy,
|
coalesce(op.display_name, mop.display_name) as handledByName,
|
r.handled_at as handledAt,
|
r.handle_remark as handleRemark,
|
r.created_at as createdAt,
|
r.updated_at as updatedAt
|
from transport_parking_plate_registration r
|
left join merchant m on m.id = r.merchant_id
|
left join transport_parking_lot l on l.id = r.parking_lot_id
|
and l.merchant_id = r.merchant_id and l.deleted = 0
|
left join talent_user u on u.id = r.talent_id
|
left join talent_card c on c.id = coalesce(r.card_id, (
|
select c2.id
|
from talent_card c2
|
where c2.talent_id = r.talent_id
|
and c2.deleted = 0
|
and c2.status = 1
|
order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
|
limit 1
|
))
|
left join admin_operator op on op.id = r.handled_by and op.deleted = 0
|
left join merchant_operator mop on mop.id = r.handled_by and mop.deleted = 0
|
where r.deleted = 0
|
and (#{merchantId} is null or r.merchant_id = #{merchantId})
|
and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
|
and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
|
and (#{status} is null or #{status} = '' or r.status = #{status})
|
and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
|
and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
|
and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
|
and (#{keyword} is null or #{keyword} = ''
|
or r.registration_no like concat('%', #{keyword}, '%')
|
or r.plate_number like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
|
or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%'))
|
and (#{talentName} is null or #{talentName} = '' or coalesce(r.talent_name, u.name) like concat('%', #{talentName}, '%'))
|
and (#{talentMobile} is null or #{talentMobile} = '' or coalesce(r.talent_mobile, u.mobile) like concat('%', #{talentMobile}, '%'))
|
and (#{plateNumber} is null or #{plateNumber} = '' or r.plate_number like concat('%', #{plateNumber}, '%'))
|
and (#{serviceKeyword} is null or #{serviceKeyword} = ''
|
or m.merchant_name like concat('%', #{serviceKeyword}, '%')
|
or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{serviceKeyword}, '%'))
|
order by r.submitted_at desc, r.id desc
|
limit #{size} offset #{offset}
|
""")
|
List<Map<String, Object>> selectAdminPage(@Param("merchantId") Long merchantId,
|
@Param("parkingLotId") Long parkingLotId,
|
@Param("keyword") String keyword,
|
@Param("talentName") String talentName,
|
@Param("talentMobile") String talentMobile,
|
@Param("plateNumber") String plateNumber,
|
@Param("serviceKeyword") String serviceKeyword,
|
@Param("cardLevel") String cardLevel,
|
@Param("status") String status,
|
@Param("submissionType") String submissionType,
|
@Param("submittedStart") LocalDateTime submittedStart,
|
@Param("submittedEnd") LocalDateTime submittedEnd,
|
@Param("size") int size,
|
@Param("offset") int offset);
|
|
@Select("""
|
select r.id,
|
r.registration_no as registrationNo,
|
r.merchant_id as merchantId,
|
m.merchant_name as merchantName,
|
m.contact_mobile as merchantContactMobile,
|
m.address as merchantAddress,
|
r.benefit_id as benefitId,
|
r.parking_lot_id as parkingLotId,
|
coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
|
coalesce(r.parking_lot_address_snapshot, l.address) as parkingLotAddress,
|
r.talent_id as talentId,
|
r.card_id as cardId,
|
coalesce(r.talent_name, u.name) as talentName,
|
coalesce(r.talent_mobile, u.mobile) as talentMobile,
|
coalesce(r.card_no, c.card_no) as cardNo,
|
coalesce(r.card_level, c.card_level) as cardLevel,
|
r.plate_number as plateNumber,
|
r.submission_type as submissionType,
|
r.submitted_at as submittedAt,
|
r.status,
|
r.handled_by as handledBy,
|
coalesce(op.display_name, mop.display_name) as handledByName,
|
r.handled_at as handledAt,
|
r.handle_remark as handleRemark,
|
r.created_at as createdAt,
|
r.updated_at as updatedAt
|
from transport_parking_plate_registration r
|
left join merchant m on m.id = r.merchant_id
|
left join transport_parking_lot l on l.id = r.parking_lot_id
|
and l.merchant_id = r.merchant_id and l.deleted = 0
|
left join talent_user u on u.id = r.talent_id
|
left join talent_card c on c.id = coalesce(r.card_id, (
|
select c2.id
|
from talent_card c2
|
where c2.talent_id = r.talent_id
|
and c2.deleted = 0
|
and c2.status = 1
|
order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
|
limit 1
|
))
|
left join admin_operator op on op.id = r.handled_by and op.deleted = 0
|
left join merchant_operator mop on mop.id = r.handled_by and mop.deleted = 0
|
where r.id = #{id} and r.deleted = 0
|
limit 1
|
""")
|
Map<String, Object> selectAdminDetail(@Param("id") Long id);
|
|
@Select("""
|
select *
|
from transport_parking_plate_registration
|
where id = #{id} and deleted = 0
|
limit 1
|
for update
|
""")
|
TransportParkingPlateRegistration selectByIdForUpdate(@Param("id") Long id);
|
|
@Select("""
|
select id, registration_no as registrationNo, status
|
from transport_parking_plate_registration
|
where idempotency_key = #{idempotencyKey} and deleted = 0
|
limit 1
|
""")
|
Map<String, Object> selectByIdempotencyKey(@Param("idempotencyKey") String idempotencyKey);
|
@Select("""
|
select count(*)
|
from transport_parking_plate_registration r
|
left join transport_parking_lot l on l.id = r.parking_lot_id
|
and l.merchant_id = r.merchant_id and l.deleted = 0
|
left join talent_user u on u.id = r.talent_id
|
left join talent_card c on c.id = coalesce(r.card_id, (
|
select c2.id
|
from talent_card c2
|
where c2.talent_id = r.talent_id
|
and c2.deleted = 0
|
and c2.status = 1
|
order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
|
limit 1
|
))
|
where r.deleted = 0
|
and r.merchant_id = #{merchantId}
|
and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
|
and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
|
and (#{status} is null or #{status} = '' or r.status = #{status})
|
and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
|
and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
|
and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
|
and (#{keyword} is null or #{keyword} = ''
|
or r.registration_no like concat('%', #{keyword}, '%')
|
or r.plate_number like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
|
or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%')
|
or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{keyword}, '%'))
|
""")
|
Long countMerchantPage(@Param("merchantId") Long merchantId,
|
@Param("parkingLotId") Long parkingLotId,
|
@Param("keyword") String keyword,
|
@Param("cardLevel") String cardLevel,
|
@Param("status") String status,
|
@Param("submissionType") String submissionType,
|
@Param("submittedStart") LocalDateTime submittedStart,
|
@Param("submittedEnd") LocalDateTime submittedEnd);
|
|
@Select("""
|
select r.id,
|
r.registration_no as registrationNo,
|
r.merchant_id as merchantId,
|
m.merchant_name as merchantName,
|
r.benefit_id as benefitId,
|
r.parking_lot_id as parkingLotId,
|
coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
|
r.talent_id as talentId,
|
r.card_id as cardId,
|
coalesce(r.talent_name, u.name) as talentName,
|
coalesce(r.talent_mobile, u.mobile) as talentMobile,
|
coalesce(r.card_no, c.card_no) as cardNo,
|
coalesce(r.card_level, c.card_level) as cardLevel,
|
r.plate_number as plateNumber,
|
r.submission_type as submissionType,
|
r.submitted_at as submittedAt,
|
r.status,
|
r.handled_by as handledBy,
|
mo.display_name as handledByName,
|
r.handled_at as handledAt,
|
r.handle_remark as handleRemark,
|
r.created_at as createdAt,
|
r.updated_at as updatedAt
|
from transport_parking_plate_registration r
|
left join merchant m on m.id = r.merchant_id
|
left join transport_parking_lot l on l.id = r.parking_lot_id
|
and l.merchant_id = r.merchant_id and l.deleted = 0
|
left join talent_user u on u.id = r.talent_id
|
left join talent_card c on c.id = coalesce(r.card_id, (
|
select c2.id
|
from talent_card c2
|
where c2.talent_id = r.talent_id
|
and c2.deleted = 0
|
and c2.status = 1
|
order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
|
limit 1
|
))
|
left join merchant_operator mo on mo.id = r.handled_by and mo.deleted = 0
|
where r.deleted = 0
|
and r.merchant_id = #{merchantId}
|
and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
|
and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
|
and (#{status} is null or #{status} = '' or r.status = #{status})
|
and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
|
and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
|
and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
|
and (#{keyword} is null or #{keyword} = ''
|
or r.registration_no like concat('%', #{keyword}, '%')
|
or r.plate_number like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
|
or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
|
or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%')
|
or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{keyword}, '%'))
|
order by r.submitted_at desc, r.id desc
|
limit #{size} offset #{offset}
|
""")
|
List<Map<String, Object>> selectMerchantPage(@Param("merchantId") Long merchantId,
|
@Param("parkingLotId") Long parkingLotId,
|
@Param("keyword") String keyword,
|
@Param("cardLevel") String cardLevel,
|
@Param("status") String status,
|
@Param("submissionType") String submissionType,
|
@Param("submittedStart") LocalDateTime submittedStart,
|
@Param("submittedEnd") LocalDateTime submittedEnd,
|
@Param("size") int size,
|
@Param("offset") int offset);
|
|
@Select("""
|
select r.id,
|
r.registration_no as registrationNo,
|
r.merchant_id as merchantId,
|
m.merchant_name as merchantName,
|
m.contact_mobile as merchantContactMobile,
|
m.address as merchantAddress,
|
r.benefit_id as benefitId,
|
r.parking_lot_id as parkingLotId,
|
coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
|
coalesce(r.parking_lot_address_snapshot, l.address) as parkingLotAddress,
|
r.talent_id as talentId,
|
r.card_id as cardId,
|
coalesce(r.talent_name, u.name) as talentName,
|
coalesce(r.talent_mobile, u.mobile) as talentMobile,
|
coalesce(r.card_no, c.card_no) as cardNo,
|
coalesce(r.card_level, c.card_level) as cardLevel,
|
r.plate_number as plateNumber,
|
r.submission_type as submissionType,
|
r.submitted_at as submittedAt,
|
r.status,
|
r.handled_by as handledBy,
|
mo.display_name as handledByName,
|
r.handled_at as handledAt,
|
r.handle_remark as handleRemark,
|
r.created_at as createdAt,
|
r.updated_at as updatedAt
|
from transport_parking_plate_registration r
|
left join merchant m on m.id = r.merchant_id
|
left join transport_parking_lot l on l.id = r.parking_lot_id
|
and l.merchant_id = r.merchant_id and l.deleted = 0
|
left join talent_user u on u.id = r.talent_id
|
left join talent_card c on c.id = coalesce(r.card_id, (
|
select c2.id
|
from talent_card c2
|
where c2.talent_id = r.talent_id
|
and c2.deleted = 0
|
and c2.status = 1
|
order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
|
limit 1
|
))
|
left join merchant_operator mo on mo.id = r.handled_by and mo.deleted = 0
|
where r.id = #{id} and r.merchant_id = #{merchantId} and r.deleted = 0
|
limit 1
|
""")
|
Map<String, Object> selectMerchantDetail(@Param("id") Long id, @Param("merchantId") Long merchantId);
|
}
|
