a.id AS "id", a.apply_source AS "applySource", IF(a.apply_source=1,a.pedigree_certified,d.pedigree_certified_code) AS "pedigreeCertified", a.birth_code AS "birthCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", a.review_state AS "reviewState", a.review_time AS "reviewTime", a.review_remarks AS "reviewRemarks", a.gold_certified_state AS "goldCertifiedState", a.running_number AS "runningNumber", a.payment_state AS "paymentState", a.payment_time AS "paymentTime", 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", a.is_canceled AS "isCanceled", IFNULL(IFNULL(d.dog_owner,dp.dog_owner),b.temp_user_name) AS "name", IFNULL(d.name_en,dp.name_en) AS "dogName", IFNULL(d.birthdate,dp.birthdate) AS "dogBirthday", IFNULL(d.dog_breed,dp.dog_breed) AS "dogBreed", IFNULL(d.gender,dp.gender) AS "dogGender" a.id AS "id", a.apply_source AS "applySource", a.pedigree_certified AS "pedigreeCertified", a.payment_member_code AS "paymentMemberCode", a.price AS "price", a.review_state AS "reviewState", a.review_time AS "reviewTime", a.review_remarks AS "reviewRemarks", a.gold_certified_state AS "goldCertifiedState", a.running_number AS "runningNumber", a.payment_state AS "paymentState", a.payment_time AS "paymentTime", a.is_canceled AS "isCanceled", 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" a.id AS "id", a.apply_source AS "applySource", IF(a.apply_source=1,a.pedigree_certified,db.pedigree_certified_code) AS "pedigreeCertified", a.birth_code AS "birthCode", IFNULL(d.identification_flag,b.identification_flag) AS "identificationFlag", IFNULL(d.name_en,b.dog_en_name) AS "nameEn", IFNULL(IFNULL(d.call_name,db.call_name),dc.call_name) AS "nickname", IFNULL(d.dog_breed,b.dog_breed) AS "dogBreed", IFNULL(d.color_flag,b.color_flag) AS "colorFlag", IFNULL(d.gender,b.dog_gender) AS "gender", IFNULL(d.birthdate,b.dog_birthday) AS "birthdate", IFNULL(d.ecert_flag,dc.ecert_flag) AS "ecertFlag", IFNULL(d.dog_owner, b.temp_user_name) AS "memberName", IFNULL(d.member_code, b.temp_member_code) AS "memberCode", IFNULL(d.breeder_member_code,b.breeder_member_code) AS "breederMemberCode", IFNULL(d.breeder,b.breeder) AS "breederMemberName", IFNULL(d.fdog_pedigree_certified,b.fdog_pedigree_certified) AS "malePedigreeCertified", IFNULL(d.mdog_pedigree_certified,b.mdog_pedigree_certified) AS "femalePedigreeCertified", a.payment_state AS "paymentState", a.is_canceled AS "isCanceled", a.review_state AS "reviewState", a.create_date AS "createDate", a.review_remarks AS "reviewRemarks" LEFT JOIN dog d ON a.birth_code = d.birth_certificate_level_code AND a.apply_source != 1 AND d.del_flag = '0' LEFT JOIN dog dp ON a.pedigree_certified = dp.pedigree_certified_code AND a.apply_source = 1 AND dp.del_flag = '0' LEFT JOIN dog_birth_certificate b ON a.birth_code = b.birth_cer_reg_code AND a.apply_source != 1 AND b.del_flag = '0' LEFT JOIN sys_member m ON m.member_code = a.payment_member_code LEFT JOIN dog d ON a.pedigree_certified = d.pedigree_certified_code AND a.apply_source = 1 AND d.del_flag = '0' LEFT JOIN dog_birth_certificate b ON a.birth_code = b.birth_cer_reg_code AND a.apply_source = 2 AND b.del_flag = '0' LEFT JOIN dog_chip dc ON b.running_number = dc.running_number AND a.apply_source = 2 AND b.del_flag = '0' AND dc.del_flag = '0' LEFT JOIN dog db ON b.pedigree_certified = db.pedigree_certified_code AND a.apply_source = 2 AND b.pedigree_certified IS NOT NULL AND db.del_flag = '0' SELECT FROM dog_dna_parents_compare a WHERE a.id = #{id} SELECT ddpca.fdog_review_state, ddpca.mdog_review_state, ddpca.fdog_review_user_id, ddpca.mdog_review_user_id, FROM dog_dna_parents_compare a LEFT JOIN dog_dna_parents_compare_audit ddpca ON ddpca.dog_dna_parents_compare_id = a.id and ddpca.del_flag = '0' a.del_flag = #{DEL_FLAG_NORMAL} AND (a.birth_code LIKE concat('%',#{birthCode},'%') OR a.pedigree_certified LIKE concat('%',#{birthCode},'%') OR d.pedigree_certified_code LIKE concat('%',#{birthCode},'%')) AND (a.payment_member_code like concat('%',#{paymentMemberCode},'%') or d.member_code LIKE concat('%',#{paymentMemberCode},'%') OR d.dog_owner LIKE concat('%',#{paymentMemberCode},'%') OR b.temp_member_code LIKE concat('%',#{paymentMemberCode},'%') OR b.temp_user_name LIKE concat('%',#{paymentMemberCode},'%')) AND a.review_state = #{reviewState} AND a.payment_state = #{paymentState} AND a.apply_source = #{applySource} AND d.dog_breed ${sqlMap.dogBreedMap} 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.is_canceled = #{isCanceled} AND a.gold_certified_state = #{goldCertifiedState} AND a.running_number = #{runningNumber} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC SELECT FROM dog_dna_parents_compare a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO dog_dna_parents_compare( id, apply_source, pedigree_certified, birth_code, payment_member_code, price, review_state, review_time, review_remarks, gold_certified_state, running_number, payment_state, payment_time, create_by, create_date, update_by, update_date, remarks, del_flag ) VALUES ( #{id}, #{applySource}, #{pedigreeCertified}, #{birthCode}, #{paymentMemberCode}, #{price}, #{reviewState}, #{goldCertifiedState}, #{reviewTime}, #{reviewRemarks}, #{runningNumber}, #{paymentState}, #{paymentTime}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag} ) UPDATE dog_dna_parents_compare SET apply_source = #{applySource}, pedigree_certified = #{pedigreeCertified}, birth_code = #{birthCode}, payment_member_code = #{paymentMemberCode}, price = #{price}, review_state = #{reviewState}, review_time = #{reviewTime}, review_remarks = #{reviewRemarks}, gold_certified_state = #{goldCertifiedState}, running_number = #{runningNumber}, payment_state = #{paymentState}, payment_time = #{paymentTime}, update_by = #{updateBy.id}, update_date = #{updateDate}, is_canceled = #{isCanceled}, remarks = #{remarks} WHERE id = #{id} UPDATE dog_dna_parents_compare SET del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} delete from dog_dna_parents_compare where id=#{id} SELECT FROM dog_dna_parents_compare a WHERE a.birth_code = #{birthCode} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_dna_parents_compare a WHERE a.birth_code = #{birthCode} AND a.running_number IS NULL LIMIT 1 SELECT ddpca.fdog_review_state, ddpca.mdog_review_state, ddpca.fdog_review_remarks, ddpca.mdog_review_remarks, FROM dog_dna_parents_compare a LEFT JOIN dog_dna_parents_compare_audit ddpca ON ddpca.dog_dna_parents_compare_id = a.id and ddpca.del_flag = '0' a.del_flag = '0' AND a.payment_member_code = #{memberCode} AND a.apply_source = #{request.applySource} AND a.birth_code = #{request.birthCode} AND a.pedigree_certified = #{request.pedigreeCertified} ORDER BY a.update_date DESC SELECT FROM dog_dna_parents_compare a a.del_flag = '0' AND a.id = #{id} AND a.payment_member_code = #{memberCode} SELECT FROM dog_dna_parents_compare a WHERE a.pedigree_certified = #{pedigreeCertified} AND a.del_flag = '0' LIMIT 1 SELECT ddpca.fdog_review_state, ddpca.mdog_review_state, ddpca.fdog_review_user_id, ddpca.mdog_review_user_id, FROM dog_dna_parents_compare a LEFT JOIN dog_dna_parents_compare_audit ddpca ON ddpca.dog_dna_parents_compare_id = a.id and ddpca.del_flag = '0' a.del_flag = #{DEL_FLAG_NORMAL} AND (a.birth_code LIKE concat('%',#{birthCode},'%') OR a.pedigree_certified LIKE concat('%',#{birthCode},'%') OR d.pedigree_certified_code LIKE concat('%',#{birthCode},'%')) AND (a.payment_member_code like concat('%',#{paymentMemberCode},'%') or d.member_code LIKE concat('%',#{paymentMemberCode},'%') OR d.dog_owner LIKE concat('%',#{paymentMemberCode},'%') OR b.temp_member_code LIKE concat('%',#{paymentMemberCode},'%') OR b.temp_user_name LIKE concat('%',#{paymentMemberCode},'%')) AND a.review_state = #{reviewState} AND a.payment_state = #{paymentState} AND a.apply_source = #{applySource} AND d.dog_breed ${sqlMap.dogBreedMap} 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.is_canceled = #{isCanceled} AND a.gold_certified_state = #{goldCertifiedState} AND a.running_number = #{runningNumber} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC