a.id AS "id", a.dog_id AS "dogId", c.pedigree_certified_code AS "pedigreeCertified", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.sampling_code AS "samplingCode", a.dna_code AS "dnaCode", a.birth_code AS "birthCode", a.genetic_disease_json AS "geneticDiseaseJson", IFNULL(IFNULL(c.member_code,d.temp_member_code),a.payment_member_code)AS "paymentMemberCode", IFNULL(IFNULL(c.dog_owner,d.temp_user_name),b.name) AS "memberName", a.operator AS "operator", a.price AS "price", 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.process_time AS "processTime", a.process_state AS "processState", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.print_state AS "printState", a.remarks AS "remarks", a.del_flag AS "delFlag", a.is_canceled AS "isCanceled", a.org_id AS "orgId", a.apply_channel AS "applyChannel", ifnull(c.name_en,d.dog_en_name) AS "dogName", c.birthdate AS "dogBirthday", c.gender AS "dogGender", ifnull(c.identification_flag,d.identification_flag) AS "chipCode", ifnull(c.dog_breed,d.dog_breed) AS "dogBreed", c.color_flag AS "dogColorFlag", c.pedigree_certified_code AS "pedigreeCertifiedCode", a.typeYes AS "typeYes", a.finish_time AS "finishTime", a.is_OEC AS "isOEC" a.id AS "id", a.dog_id AS "dogId", a.pedigree_certified AS "pedigreeCertified", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.dna_code AS "dnaCode", a.birth_code AS "birthCode", a.sampling_code AS "samplingCode", a.genetic_disease_json AS "geneticDiseaseJson", a.payment_member_code AS "paymentMemberCode", a.operator AS "operator", a.price AS "price", 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.process_time AS "processTime", a.process_state AS "processState", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.print_state AS "printState", a.remarks AS "remarks", a.del_flag AS "delFlag", a.org_id AS "orgId", a.apply_channel AS "applyChannel", a.is_canceled AS "isCanceled", a.typeYes AS "typeYes", a.is_OEC AS "isOEC" LEFT JOIN dog c ON a.dog_id = c.id AND c.del_flag = '0' LEFT JOIN dog_birth_certificate d ON a.birth_code = d.birth_cer_reg_code AND d.del_flag = '0' LEFT JOIN sys_member b ON a.payment_member_code = b.member_code SELECT so.name AS "organizationName", FROM dog_dna_archive a LEFT JOIN sys_org so ON so.id = a.org_id WHERE a.id = #{id} a.del_flag = #{DEL_FLAG_NORMAL} AND c.pedigree_certified_code LIKE concat('%',#{pedigreeCertified},'%') AND a.dna_code LIKE concat(#{dnaCode},'%') AND a.birth_code LIKE concat('%',#{birthCode},'%') AND a.overseas_pedigree_certified LIKE concat('%',#{overseasPedigreeCertifiedLike},'%') AND a.sampling_code LIKE concat('%',#{samplingCode},'%') AND (a.payment_member_code = #{paymentMemberCode} OR b.name LIKE concat(#{paymentMemberCode},'%')) AND a.review_state = #{reviewState} AND a.payment_state = #{paymentState} AND a.process_state = #{processState} AND a.apply_channel = #{applyChannel} 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 date_format(a.finish_time, '%Y-%m-%d') = ]]> date_format(#{beginFinishDate}, '%Y-%m-%d') AND date_format(a.finish_time, '%Y-%m-%d') date_format(#{endFinishDate}, '%Y-%m-%d') AND a.running_number = #{runningNumber} AND c.dog_breed ${sqlMap.dogBreedMap} AND a.is_canceled = #{isCanceled} AND a.dog_id = #{dogId} AND a.org_id = #{orgId} AND so.name LIKE concat('%',#{organizationName},'%') AND ifnull(c.identification_flag,d.identification_flag) = #{chipCode} AND (c.pedigree_certified_code LIKE concat('%',#{keyword},'%') or c.identification_flag LIKE concat('%',#{keyword},'%') ) select count(1) from dog_dna_archive a SELECT so.name AS "organizationName", FROM dog_dna_archive a LEFT JOIN sys_org so ON so.id = a.org_id ORDER BY ${page.orderBy} ORDER BY a.update_date DESC SELECT FROM dog_dna_archive a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO dog_dna_archive( id, dog_id, pedigree_certified, overseas_pedigree_certified, dna_code, birth_code, genetic_disease_json, payment_member_code, operator, price, review_state, review_time, review_remarks, running_number, payment_state, payment_time, process_time, process_state, print_state, create_by, create_date, update_by, update_date, remarks, org_id, typeYes, is_OEC, apply_channel, sampling_code, del_flag ) VALUES ( #{id}, #{dogId}, #{pedigreeCertified}, #{overseasPedigreeCertified}, #{dnaCode}, #{birthCode}, #{geneticDiseaseJson}, #{paymentMemberCode}, #{operator}, #{price}, #{reviewState}, #{reviewTime}, #{reviewRemarks}, #{runningNumber}, #{paymentState}, #{paymentTime}, #{processTime}, #{processState}, #{printState}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{orgId}, #{typeYes}, #{isOEC}, #{applyChannel}, #{samplingCode}, #{delFlag} ) UPDATE dog_dna_archive SET dog_id = #{dogId}, pedigree_certified = #{pedigreeCertified}, overseas_pedigree_certified = #{overseasPedigreeCertified}, dna_code = #{dnaCode}, operator = #{operator}, review_state = #{reviewState}, review_time = #{reviewTime}, review_remarks = #{reviewRemarks}, running_number = #{runningNumber}, payment_state = #{paymentState}, payment_time = #{paymentTime}, finish_time=#{finishTime}, process_time = #{processTime}, process_state = #{processState}, update_by = #{updateBy.id}, update_date = #{updateDate}, typeYes=#{typeYes}, org_id=#{orgId}, is_OEC=#{isOEC}, is_canceled=#{isCanceled}, sampling_code = #{samplingCode}, remarks = #{remarks} WHERE id = #{id} UPDATE dog_dna_archive SET remarks = concat(IFNULL(remarks,''),#{remarks}), del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} delete from dog_dna_archive where id=#{id} update dog_dna_archive SET print_state=#{printState} WHERE id=#{id} SELECT FROM dog_dna_archive a WHERE a.dog_id = #{dogId} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_dna_archive a WHERE a.birth_code = #{birthCode} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_dna_archive a WHERE a.birth_code = #{birthCode} AND a.running_number IS NULL LIMIT 1 SELECT FROM dog_dna_archive a WHERE a.dog_id = #{dogId} AND a.payment_state = '2' AND a.is_canceled != '1' AND a.del_flag = '0' limit 1 SELECT id, dog_id, pedigree_certified, overseas_pedigree_certified, dna_code, birth_code, payment_member_code, operator, price, review_state, review_time, review_remarks, running_number, payment_state, payment_time, process_time, process_state, print_state, create_by, create_date, update_by, update_date, remarks, del_flag FROM dog_dna_archive where del_flag = '0' and payment_state ='1' and overseas_pedigree_certified =#{overseasPedigreeCertified} SELECT FROM dog_dna_archive a WHERE a.overseas_pedigree_certified = #{overseasPedigreeCertified} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_dna_archive a WHERE a.del_flag ='0' and a.is_canceled = '0' and (a.overseas_pedigree_certified =#{pedigreeCertifiedCode} or c.pedigree_certified_code = #{pedigreeCertifiedCode} or c.identification_flag=#{pedigreeCertifiedCode}) LIMIT 1 SELECT FROM dog_dna_archive a WHERE a.del_flag ='0' and a.is_canceled = '0' and (a.overseas_pedigree_certified =#{keyword} or a.pedigree_certified = #{keyword} or a.birth_code=#{keyword}) LIMIT 1 SELECT * FROM dog_dna_archive a WHERE a.del_flag = '0' AND a.is_canceled = '0' and a.payment_state='2' AND( a.dog_id= #{vo.dogId} or a.overseas_pedigree_certified= #{vo.overseasPedigreeCertified} or a.pedigree_certified= #{vo.pedigreeCertified} or a.birth_code= #{vo.birthCode} ) LIMIT 1 SELECT FROM dog_dna_archive a WHERE a.del_flag = '0' AND a.create_by = #{createBy.id} and a.pedigree_certified= #{pedigreeCertified} and a.birth_code= #{birthCode} and a.running_number= #{runningNumber} SELECT FROM dog_dna_archive a WHERE a.pedigree_certified = #{pedigreeCertifiedCode} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 UPDATE dog_dna_archive SET org_id = #{orgId} WHERE birth_code = #{birthCode} AND finish_time IS NULL UPDATE dog_dna_archive SET finish_time = #{finishTime}, typeYes = #{typeYes}, sampling_code = #{samplingCode}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE dog_dna_archive SET apply_channel = #{applyChannel}, org_id = null, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE dog_dna_archive SET genetic_disease_json = #{geneticDisease} WHERE sampling_code = #{samplingCode} UPDATE dog_dna_archive SET is_OEC=#{isOEC}, update_by = #{updateBy.id}, update_date = #{updateDate} WHERE id = #{id} SELECT FROM dog_dna_archive a WHERE a.running_number = #{runningNumber} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_dna_archive a WHERE a.dna_code IS NOT NULL AND a.sampling_code IS NOT NULL AND a.review_state = '1' AND a.del_flag = '0' AND a.is_canceled = '0' AND a.genetic_disease_json IS NULL SELECT FROM dog_dna_archive a WHERE a.del_flag = 0 AND ifnull(a.sampling_code,'') = '' AND a.is_oec = 1 AND a.running_number is not null AND a.payment_state = 2 AND a.apply_channel = 1 AND a.create_date <= curdate() - interval 7 day AND a.payment_member_code = #{memberCode} SELECT FROM dog_dna_archive a JOIN sampling s ON a.sampling_code = s.sampling_code WHERE a.del_flag = 0 AND ifnull(a.sampling_code,'') != '' AND a.is_oec = 1 AND a.running_number is not null AND a.payment_state = 2 AND a.apply_channel = 1 AND s.bind_time < curdate() AND ifnull(a.review_state,'0') = '0' AND a.payment_member_code = #{memberCode} SELECT FROM dog_dna_archive a WHERE a.del_flag = 0 AND a.running_number = #{orderCode} update ${businessTable} set is_canceled ='1' , update_date=#{vo.updateDate}, update_by=#{vo.updateBy.id} where id in #{item}