java/talent-card.git

			@@ -47,6 +47,24 @@
			""")
			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',
			@@ -79,7 +97,20 @@
			p.package_no as packageNo,
			p.package_name as packageName,
			p.merchant_id as merchantId,
			m.merchant_name as merchantName,
			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,
			@@ -121,7 +152,20 @@
			p.package_no as packageNo,
			p.package_name as packageName,
			p.merchant_id as merchantId,
			m.merchant_name as merchantName,
			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,
			@@ -162,12 +206,21 @@
			@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,
			@@ -177,18 +230,30 @@
			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 c.exchange_code like concat('%', #{keyword}, '%') or p.package_name like concat('%', #{keyword}, '%'))
			order by c.id desc
			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);

			@@ -196,14 +261,24 @@
			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 c.exchange_code like concat('%', #{keyword}, '%') or p.package_name like concat('%', #{keyword}, '%'))
			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("keyword") String keyword,
			@Param("packageKeyword") String packageKeyword,
			@Param("merchantKeyword") String merchantKeyword);
			}