a.id AS "id", a.apply_code AS "applyCode", a.main_show_id AS "mainShowId", a.show_code AS "showCode", a.organizer AS "organizer", a.dog_owner_name_cn AS "dogOwnerNameCn", a.dog_owner_name_en AS "dogOwnerNameEn", a.dog_owner_member_code AS "dogOwnerMemberCode", a.second_dog_owner AS "secondDogOwner", a.second_owner_member AS "secondOwnerMember", a.dog_breed_name_cn AS "dogBreedNameCn", a.dog_breed_name_en AS "dogBreedNameEn", a.dog_breed_group AS "dogBreedGroup", a.dog_name AS "dogName", a.dog_gender AS "dogGender", a.dog_generation AS "dogGeneration", a.dog_birthday AS "dogBirthday", a.pedigree_certified AS "pedigreeCertified", a.other_certified AS "otherCertified", a.fci_code AS "fciCode", a.chip_code AS "chipCode", a.fdog_pedigree_certified AS "fdogPedigreeCertified", a.fdog_name AS "fdogName", a.mdog_pedigree_certified AS "mdogPedigreeCertified", a.mdog_name AS "mdogName", a.breeder AS "breeder", a.age_group AS "ageGroup", a.is_owner AS isOwner, a.show_apply_appraisal AS "showApplyAppraisal", a.fee AS "fee", a.payment_amount AS "paymentAmount", a.order_price AS "orderPrice", a.refund_flag AS "refundFlag", a.absence_flag AS "absenceFlag", a.bbe_flag AS "bbeFlag", a.phone_apply_flag AS "phoneApplyFlag", a.phone_apply_fee AS "phoneApplyFee", a.handler_member_code AS "handlerMemberCode", a.payment_member_code AS "paymentMemberCode", a.sticker_flag AS "stickerFlag", a.review_state AS "reviewState", a.review_time AS "reviewTime", a.review_remarks AS "reviewRemarks", a.running_number AS "runningNumber", a.payment_state AS "paymentState", a.payment_time AS "paymentTime", a.proccess_time AS "proccessTime", a.proccess_state AS "proccessState", a.expire_flag AS "expireFlag", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.del_flag AS "delFlag", b.short_name AS "shortName", a.phone_apply_running_number as "phoneApplyRunningNumber", a.payment_charging_item_id as "paymentChargingItemId" LEFT JOIN payment_charging_item b ON a.payment_charging_item_id = b.id SELECT FROM show_apply a WHERE a.id = #{id} SELECT FROM show_apply a WHERE a.running_number = #{runningNumber} SELECT * FROM show_apply a WHERE a.del_flag = '0' AND a.pedigree_certified = #{pedigreeCertified} AND a.show_code LIKE concat('%',#{showCode},'%') AND a.apply_code LIKE concat(#{applyCode},'%') AND a.show_code LIKE concat('%',#{showCode},'%') AND a.dog_breed_name_cn LIKE concat('%',#{dogBreedNameCn},'%') AND (a.dog_owner_member_code = #{dogOwnerMemberCode} OR a.dog_owner_name_cn LIKE concat('%',#{dogOwnerMemberCode},'%')) AND a.remarks LIKE concat('%',#{remarks},'%') AND (a.pedigree_certified LIKE concat('%',#{pedigreeCertified},'%') OR a.chip_code = #{pedigreeCertified}) AND a.payment_state = #{paymentState} AND a.expire_flag = #{expireFlag} AND a.del_flag = #{delFlag} AND a.refund_flag = #{refundFlag} AND a.absence_flag = #{absenceFlag} AND date_format(a.create_date,'%Y-%m-%d') =]]> date_format(#{beginCreateDate},'%Y-%m-%d') AND date_format(a.create_date,'%Y-%m-%d') date_format(#{endCreateDate},'%Y-%m-%d') AND a.running_number = #{runningNumber} AND a.phone_apply_flag IS NOT NULL AND a.phone_apply_flag = #{phoneApplyFlag} AND a.organizer IN ${sqlMap.organizer} AND a.payment_state = '1' AND a.apply_code NOT LIKE 'WB%' AND a.apply_code NOT LIKE 'CAB%' AND a.id in( ${id} ) AND a.main_show_id = #{mainShowId} select count(1) from show_apply a SELECT FROM show_apply a ORDER BY ${page.orderBy} ORDER BY a.update_date DESC SELECT FROM show_apply a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO show_apply( id, apply_code, main_show_id, show_code, organizer, dog_owner_name_cn, dog_owner_name_en, dog_owner_member_code, second_dog_owner, second_owner_member, dog_breed_name_cn, dog_breed_name_en, dog_breed_group, dog_name, dog_gender, dog_generation, dog_birthday, pedigree_certified, other_certified, fci_code, chip_code, fdog_pedigree_certified, fdog_name, mdog_pedigree_certified, mdog_name, breeder, age_group, is_owner, show_apply_appraisal, fee, payment_charging_item_id, order_price, refund_flag, absence_flag, bbe_flag, phone_apply_flag, phone_apply_fee, handler_member_code, payment_member_code, sticker_flag, review_state, review_time, review_remarks, running_number, payment_state, payment_time, proccess_time, proccess_state, create_by, create_date, update_by, update_date, remarks, del_flag ) VALUES ( #{id}, #{applyCode}, #{mainShowId}, #{showCode}, #{organizer}, #{dogOwnerNameCn}, #{dogOwnerNameEn}, #{dogOwnerMemberCode}, #{secondDogOwner}, #{secondOwnerMember}, #{dogBreedNameCn}, #{dogBreedNameEn}, #{dogBreedGroup}, #{dogName}, #{dogGender}, #{dogGeneration}, #{dogBirthday}, #{pedigreeCertified}, #{otherCertified}, #{fciCode}, #{chipCode}, #{fdogPedigreeCertified}, #{fdogName}, #{mdogPedigreeCertified}, #{mdogName}, #{breeder}, #{ageGroup}, #{isOwner}, #{showApplyAppraisal}, #{fee}, #{paymentChargingItemId}, #{orderPrice}, #{refundFlag}, #{absenceFlag}, #{bbeFlag}, #{phoneApplyFlag}, #{phoneApplyFee}, #{handlerMemberCode}, #{paymentMemberCode}, #{stickerFlag}, #{reviewState}, #{reviewTime}, #{reviewRemarks}, #{runningNumber}, #{paymentState}, #{paymentTime}, #{proccessTime}, #{proccessState}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag} ) UPDATE show_apply SET apply_code = #{applyCode}, dog_owner_name_cn = #{dogOwnerNameCn}, dog_owner_name_en = #{dogOwnerNameEn}, dog_owner_member_code = #{dogOwnerMemberCode}, second_dog_owner = #{secondDogOwner}, second_owner_member = #{secondOwnerMember}, dog_breed_name_cn = #{dogBreedNameCn}, dog_breed_name_en = #{dogBreedNameEn}, dog_breed_group = #{dogBreedGroup}, dog_name = #{dogName}, dog_gender = #{dogGender}, dog_generation = #{dogGeneration}, dog_birthday = #{dogBirthday}, other_certified = #{otherCertified}, fci_code = #{fciCode}, chip_code = #{chipCode}, fdog_pedigree_certified = #{fdogPedigreeCertified}, fdog_name = #{fdogName}, mdog_pedigree_certified = #{mdogPedigreeCertified}, mdog_name = #{mdogName}, breeder = #{breeder}, age_group = #{ageGroup}, is_owner = #{isOwner}, show_apply_appraisal = #{showApplyAppraisal}, refund_flag = #{refundFlag}, absence_flag = #{absenceFlag}, bbe_flag = #{bbeFlag}, handler_member_code = #{handlerMemberCode}, payment_member_code = #{paymentMemberCode}, review_state = #{reviewState}, review_time = #{reviewTime}, review_remarks = #{reviewRemarks}, running_number = #{runningNumber}, payment_state = #{paymentState}, payment_time = #{paymentTime}, proccess_time = #{proccessTime}, proccess_state = #{proccessState}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE show_apply SET remarks = concat(IFNULL(remarks,''),#{remarks}), del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} SELECT pedigree_certified_code FROM dog WHERE pedigree_certified_code = #{_parameter} SELECT FROM show_apply a WHERE a.pedigree_certified = #{pedigreeCertified} AND a.payment_state = '1' AND a.del_flag = '0' AND a.id != #{id} UPDATE show_apply SET refund_flag = #{refundFlag}, absence_flag = #{absenceFlag}, update_by = #{updateBy.id}, update_date = #{updateDate} WHERE id = #{id} SELECT FROM show_apply a WHERE a.show_code LIKE concat(#{showCode},'%') AND a.del_flag = '0' Order by a.refund_flag asc, a.dog_breed_group * 1 asc,a.fci_code * 1 asc, LEFT ( a.age_group, INSTR(a.age_group, ',') - 1 )*1 ASC, LEFT ( a.is_owner, INSTR(a.is_owner, ',') - 1 )*1 ASC, a.dog_gender ASC,a.dog_birthday desc SELECT COUNT(t.id) AS 'countAll',SUM(t.isPubby) AS 'pubbyCount',SUM(IF(t.isPubby = 0 ,1,0)) AS 'adultDogCount' FROM ( SELECT a.id,IF(TIMESTAMPDIFF(MONTH,a.dog_birthday,sm.start_time) < 9 ,1,0) AS 'isPubby' FROM show_apply a LEFT JOIN show_main sm ON a.main_show_id = sm.id WHERE a.show_code LIKE concat('%',#{showCode},'%') AND a.del_flag = '0' ) t select count(1) from show_apply a where a.pedigree_certified = #{pedigreeCertified} and a.show_code LIKE concat('%',#{showCode},'%') and a.del_flag = '0' UPDATE show_apply SET expire_flag = '1' WHERE main_show_id = #{mainShowId} SELECT CASE WHEN COUNT(1)>0 THEN SUM(LENGTH(a.show_code)-LENGTH(replace(a.show_code,',',''))+1) ELSE 0 END FROM show_apply a WHERE a.dog_owner_member_code = #{memberCode} AND a.payment_state = '2' -- AND a.payment_time >= DATE_FORMAT(NOW(), '%Y-01-01') AND a.payment_time >= DATE_ADD(NOW(), INTERVAL -1 YEAR) AND a.del_flag = '0' SELECT COUNT(1) FROM show_apply a WHERE a.main_show_id = #{mainShowId} AND a.del_flag = '0' AND a.refund_flag = '0' AND a.absence_flag = '0' AND a.pedigree_certified = #{pedigreeCertified} SELECT FROM show_apply a a.del_flag = '0' AND a.pedigree_certified = #{pedigreeCertified} AND a.main_show_id = #{showMainId} select count(1) from show_apply del_flag = '0' and main_show_id = #{showMainId} and find_in_set(#{showCode},show_code) update show_apply a LEFT JOIN show_main m ON m.id = a.main_show_id set a.review_state ='3' , a.refund_flag =IF( now( ) m.closing_time, '1', '0' ), a.absence_flag =IF( now( ) = ]]> m.closing_time, '1', '0' ), a.review_remarks=#{vo.reviewRemarks}, a.update_date=#{vo.updateDate}, a.update_by=#{vo.updateBy.id} where a.id in #{item} /*前台提交有效*/ SELECT a.* FROM show_apply a LEFT JOIN payment_order po ON po.order_code=a.running_number LEFT JOIN payment_order_detail pod ON po.order_code=pod.order_code WHERE a.del_flag = '0' AND a.payment_state = '2' AND find_in_set(#{showCode},a.show_code) AND ( pod.business_refund_state IS NULL OR pod.business_refund_state ='2' OR pod.business_refund_state ='1') AND pod.price>=0 UNION /*前台未缴费*/ SELECT a.* FROM show_apply a LEFT JOIN payment_order po ON po.order_code=a.running_number WHERE a.del_flag = '0' AND a.payment_state='1' AND po.del_flag='0' AND find_in_set(#{showCode},a.show_code) UNION /*后台创建未缴费*/ SELECT a.* FROM show_apply a WHERE a.del_flag = '0' AND a.payment_state='1' AND a.running_number IS NULL AND find_in_set(#{showCode},a.show_code) UNION /*后台缴费成功*/ SELECT a.* FROM show_apply a LEFT JOIN payment_order po ON po.order_code=a.running_number WHERE a.del_flag = '0' AND a.payment_state = '2' AND po.id IS NULL AND find_in_set(#{showCode},a.show_code); SELECT a.* FROM show_apply a WHERE a.dog_owner_member_code = #{dogOwnerMemberCode} AND a.payment_state = '1' AND a.del_flag = '0' SELECT a.* FROM show_apply a WHERE a.pedigree_certified = #{pedigreeCertified} AND find_in_set(#{showCode},a.show_code) AND a.del_flag = '0' AND a.refund_flag = '0' LIMIT 1; SELECT count(1) FROM show_apply a LEFT JOIN show_main m ON m.id = a.main_show_id WHERE a.pedigree_certified = #{pedigreeCertified} AND a.payment_state = '2' AND a.refund_flag != '1' AND a.absence_flag != '1' AND a.del_flag = '0' AND m.end_time now() SELECT MIN(start_time) FROM show_child WHERE del_flag = '0' AND show_code IN #{item}