a.id AS "id", a.birth_code AS "birthCode", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.show_appraisal_code AS "showAppraisalCode", a.blood_sample_code AS "bloodSampleCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", 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.org_id AS "orgId", a.is_canceled AS "isCanceled", CASE WHEN m.name IS NULL THEN b.temp_user_name ELSE m.name END AS "name", d.pedigree_certified_code AS "pedigreeCertified", ifnull(d.identification_flag,b.identification_flag) AS "chipCode", ifnull(d.name_en,b.dog_en_name) AS "dogName", d.birthdate AS "dogBirthday", d.gender AS "dogGender", ifnull(d.dog_breed,b.dog_breed) AS "dogBreed", a.typeYes AS "typeYes" a.id AS "id", a.birth_code AS "birthCode", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.show_appraisal_code AS "showAppraisalCode", a.blood_sample_code AS "bloodSampleCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", 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.org_id AS "orgId", a.typeYes AS "typeYes", a.del_flag AS "delFlag", a.is_canceled AS "isCanceled", CASE WHEN m.name IS NULL THEN b.temp_user_name ELSE m.name END AS "name", d.pedigree_certified_code AS "pedigreeCertified", d.name_en AS "dogName", d.birthdate AS "dogBirthday", d.gender AS "dogGender", a.finish_time AS "finishTime" a.id AS "id", a.birth_code AS "birthCode", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.show_appraisal_code AS "showAppraisalCode", a.blood_sample_code AS "bloodSampleCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", 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.org_id AS "orgId", a.typeYes AS "typeYes", a.is_canceled AS "isCanceled", da.dog_owner_name AS "name", d.pedigree_certified_code AS "pedigreeCertified", CASE WHEN d.name_en IS NULL THEN da.dog_name ELSE d.name_en END AS "dogName", CASE WHEN d.birthdate IS NULL THEN da.birthday ELSE d.birthdate END AS "dogBirthday", CASE WHEN d.gender IS NULL THEN da.dog_gender ELSE d.gender END AS "dogGender", a.finish_time AS "finishTime" a.id AS "id", a.birth_code AS "birthCode", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.show_appraisal_code AS "showAppraisalCode", a.blood_sample_code AS "bloodSampleCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", 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.org_id AS "orgId", a.typeYes AS "typeYes", a.is_canceled AS "isCanceled", m.name AS "name", d.pedigree_certified_code AS "pedigreeCertified", CASE WHEN d.name_en IS NULL THEN c.dog_name ELSE d.name_en END AS "dogName", CASE WHEN d.birthdate IS NULL THEN c.dog_birthday ELSE d.birthdate END AS "dogBirthday", CASE WHEN d.gender IS NULL THEN c.dog_gender ELSE d.gender END AS "dogGender", a.finish_time AS "finishTime" a.id AS "id", a.birth_code AS "birthCode", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.show_appraisal_code AS "showAppraisalCode", a.blood_sample_code AS "bloodSampleCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", 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.org_id AS "orgId", a.typeYes AS "typeYes", a.is_canceled AS "isCanceled", m.name AS "name", d.pedigree_certified_code AS "pedigreeCertified", d.name_en AS "dogName", d.birthdate AS "dogBirthday", d.gender AS "dogGender", a.finish_time AS "finishTime" a.id AS "id", a.birth_code AS "birthCode", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.show_appraisal_code AS "showAppraisalCode", a.blood_sample_code AS "bloodSampleCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", 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.org_id AS "orgId", a.is_canceled AS "isCanceled", a.typeYes AS "typeYes" a.del_flag = #{DEL_FLAG_NORMAL} AND a.birth_code LIKE concat('%',#{birthCode},'%') AND a.overseas_pedigree_certified LIKE concat('%',#{overseasPedigreeCertified},'%') AND a.show_appraisal_code LIKE concat('%',#{showAppraisalCode},'%') AND a.blood_sample_code LIKE concat('%',#{bloodSampleCode},'%') AND a.payment_state = #{paymentState} 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.running_number = #{runningNumber} AND a.create_by = #{createBy.id} LEFT JOIN dog d ON a.birth_code = d.birth_certificate_level_code AND d.del_flag = '0' AND d.birth_certificate_level_code is not null LEFT JOIN sys_member m ON d.member_code = m.member_code LEFT JOIN dog_birth_certificate b ON a.birth_code = b.birth_cer_reg_code AND b.del_flag = '0' AND b.birth_cer_reg_code is not null LEFT JOIN dog d ON a.pedigree_certified_code = d.pedigree_certified_code LEFT JOIN sys_member m ON d.member_code = m.member_code LEFT JOIN dog_birth_certificate b ON a.birth_code = b.birth_cer_reg_code SELECT FROM dog_blood_save a LEFT JOIN dog d ON a.pedigree_certified_code = d.pedigree_certified_code LEFT JOIN sys_member m ON d.member_code = m.member_code WHERE a.id = #{id} SELECT FROM dog_blood_save a LEFT JOIN dog d ON a.pedigree_certified_code = d.pedigree_certified_code LEFT JOIN sys_member m ON d.member_code = m.member_code WHERE a.id = #{id} ( SELECT a.fmdog_breed_certified_code AS "fmdogBreedCertifiedCode", so.name AS "organizationName", FROM dog_blood_save a LEFT JOIN dog d ON a.pedigree_certified_code = d.pedigree_certified_code AND d.del_flag = '0' LEFT JOIN sys_member m ON d.member_code = m.member_code LEFT JOIN sys_org so ON so.id = a.org_id AND a.birth_code = '' AND a.show_appraisal_code = '' AND a.overseas_pedigree_certified = '' AND d.dog_breed ${sqlMap.dogBreedMap} AND d.birth_certificate_level_code = #{birthCode} AND d.pedigree_certified_code = #{pedigreeCertified} AND so.name LIKE concat('%',#{organizationName},'%') AND so.org_id LIKE concat('%',#{orgId},'%') AND ( d.member_code = #{paymentMemberCode} OR m.name LIKE concat('%',#{paymentMemberCode},'%') OR a.payment_member_code = #{paymentMemberCode} ) ) UNION ( SELECT a.fmdog_breed_certified_code AS "fmdogBreedCertifiedCode", so.name AS "organizationName", FROM dog_blood_save a LEFT JOIN dog d ON a.birth_code = d.birth_certificate_level_code AND d.del_flag = '0' LEFT JOIN sys_member m ON d.member_code = m.member_code LEFT JOIN dog_birth_certificate b ON a.birth_code = b.birth_cer_reg_code AND b.del_flag = '0' LEFT JOIN sys_org so ON so.id = a.org_id AND a.birth_code != '' AND a.show_appraisal_code = '' AND a.overseas_pedigree_certified = '' AND b.dog_breed ${sqlMap.dogBreedMap} AND (a.birth_code LIKE concat('%',#{birthCode},'%') OR d.pedigree_certified_code LIKE concat('%',#{birthCode},'%')) AND so.name LIKE concat('%',#{organizationName},'%') AND so.org_id LIKE concat('%',#{orgId},'%') AND ( d.member_code LIKE concat('%',#{paymentMemberCode},'%') OR m.name LIKE concat('%',#{paymentMemberCode},'%') OR b.temp_member_code LIKE concat('%',#{paymentMemberCode},'%') OR b.temp_user_name LIKE concat('%',#{paymentMemberCode},'%') ) ) UNION ( SELECT a.fmdog_breed_certified_code AS "fmdogBreedCertifiedCode", so.name AS "organizationName", FROM dog_blood_save a LEFT JOIN dog_appraisal da on da.show_appraisal_code = a.show_appraisal_code LEFT JOIN sys_member m ON a.payment_member_code = m.member_code LEFT JOIN dog d ON da.pedigree_certified = d.pedigree_certified_code LEFT JOIN sys_org so ON so.id = a.org_id AND a.show_appraisal_code !='' AND a.birth_code = '' AND a.overseas_pedigree_certified = '' AND da.dog_breed ${sqlMap.dogBreedMap} AND ( d.member_code = #{paymentMemberCode} OR m.name LIKE concat('%',#{paymentMemberCode},'%') OR a.payment_member_code = #{paymentMemberCode} ) ) UNION ( SELECT a.fmdog_breed_certified_code AS "fmdogBreedCertifiedCode", so.name AS "organizationName", FROM dog_blood_save a LEFT JOIN dog_pedigree_certified_change c on c.overseas_pedigree_certified = a.overseas_pedigree_certified LEFT JOIN sys_member m ON a.payment_member_code = m.member_code LEFT JOIN dog d ON c.overseas_pedigree_certified = d.pedigree_certified_en LEFT JOIN sys_org so ON so.id = a.org_id AND a.overseas_pedigree_certified != '' AND a.show_appraisal_code ='' AND a.birth_code = '' AND c.dog_breed ${sqlMap.dogBreedMap} AND so.name LIKE concat('%',#{organizationName},'%') AND so.org_id LIKE concat('%',#{orgId},'%') AND ( d.member_code = #{paymentMemberCode} OR m.name LIKE concat('%',#{paymentMemberCode},'%') OR a.payment_member_code = #{paymentMemberCode} ) ) ORDER BY updateDate DESC SELECT a.id AS "id", a.birth_code AS "birthCode", a.overseas_pedigree_certified AS "overseasPedigreeCertified", a.show_appraisal_code AS "showAppraisalCode", a.blood_sample_code AS "bloodSampleCode", a.payment_member_code AS "paymentMemberCode", a.price AS "price", 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.org_id AS "orgId", a.typeYes AS "typeYes", a.del_flag AS "delFlag", a.is_canceled AS "isCanceled", a.pedigree_certified_code AS "pedigreeCertified", a.finish_time AS "finishTime", o.NAME AS "organizationName", a.is_OEC AS "isOEC" FROM dog_blood_save a LEFT JOIN sys_org o ON a.org_id = o.id a.del_flag = #{DEL_FLAG_NORMAL} AND a.birth_code LIKE concat('%',#{birthCode},'%') AND a.overseas_pedigree_certified LIKE concat('%',#{overseasPedigreeCertified},'%') AND a.show_appraisal_code LIKE concat('%',#{showAppraisalCode},'%') AND a.blood_sample_code LIKE concat('%',#{bloodSampleCode},'%') AND a.payment_state = #{paymentState} AND a.pedigree_certified_code = #{pedigreeCertified} 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 o.name LIKE concat('%',#{organizationName},'%') 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.is_canceled = #{isCanceled} AND a.running_number = #{runningNumber} AND a.create_by = #{createBy.id} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC SELECT FROM dog_blood_save a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO dog_blood_save( id, birth_code, overseas_pedigree_certified, show_appraisal_code, pedigree_certified_code, typeYes, is_OEC, blood_sample_code, payment_member_code, price, running_number, payment_state, payment_time, create_by, create_date, update_by, update_date, remarks, del_flag, org_id, fmdog_breed_certified_code ) VALUES ( #{id}, #{birthCode}, #{overseasPedigreeCertified}, #{showAppraisalCode}, #{pedigreeCertified}, #{typeYes}, #{isOEC}, #{bloodSampleCode}, #{paymentMemberCode}, #{price}, #{runningNumber}, #{paymentState}, #{paymentTime}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag}, #{orgId}, #{fmdogBreedCertifiedCode} ) UPDATE dog_blood_save SET birth_code = #{birthCode}, blood_sample_code = #{bloodSampleCode}, payment_member_code = #{paymentMemberCode}, price = #{price}, running_number = #{runningNumber}, payment_state = #{paymentState}, payment_time = #{paymentTime}, update_by = #{updateBy.id}, update_date = #{updateDate}, typeYes=#{typeYes}, finish_time=#{finishTime}, pedigree_certified_code=#{pedigreeCertified}, is_OEC=#{isOEC}, is_canceled = #{isCanceled}, org_id = #{orgId}, remarks = #{remarks} WHERE id = #{id} UPDATE dog_blood_save SET del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} delete from dog_blood_save where id=#{id} SELECT FROM dog_blood_save a WHERE a.birth_code = #{birthCode} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_blood_save a WHERE a.show_appraisal_code = #{showAppraisalCode} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_blood_save a WHERE a.overseas_pedigree_certified = #{overPeidgreeCode} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_blood_save a WHERE a.running_number = #{runningNumber} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 SELECT FROM dog_blood_save a WHERE a.birth_code = #{birthCode} AND a.running_number IS NULL LIMIT 1 update ${businessTable} set is_canceled ='1' , update_date=#{vo.updateDate}, update_by=#{vo.updateBy.id} where id in #{item} update dog_blood_save set is_canceled ='1' , update_date=#{vo.updateDate}, update_by=#{vo.updateBy.id} where running_number= #{vo.runningNumber} and fmdog_breed_certified_code= #{vo.fmdogBreedCertifiedCode} SELECT FROM dog_blood_save a a.del_flag = '0' AND a.is_canceled = '0' AND a.payment_state='2' AND ( 1=2 1=1 OR a.birth_code= #{vo.birthCode} OR a.show_appraisal_code= #{vo.showAppraisalCode} OR a.overseas_pedigree_certified= #{vo.overseasPedigreeCertified} OR a.pedigree_certified_code= #{vo.pedigreeCertified} ) LIMIT 1 SELECT FROM dog_blood_save a WHERE a.del_flag = '0' AND a.is_canceled = '0' and a.payment_state='2' AND a.birth_code= #{vo.birthCode} AND a.show_appraisal_code= #{vo.showAppraisalCode} AND a.overseas_pedigree_certified= #{vo.overseasPedigreeCertified} AND a.pedigree_certified_code= #{vo.pedigreeCertified} LIMIT 1 SELECT FROM dog_blood_save a WHERE a.del_flag = '0' and d.pedigree_certified_code= #{pedigreeCertified} and ifnull(d.identification_flag,b.identification_flag) = #{chipCode} and a.birth_code= #{birthCode} and a.org_id=#{orgId} and (a.payment_member_code= #{paymentMemberCode} OR m.name LIKE concat('%',#{paymentMemberCode},'%')) and a.typeYes= #{typeYes} and a.is_OEC= #{isOEC} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC select running_number from ( select dc.running_number from dog_chip dc left join dog_birth_certificate dbc on dc.birth_code=dbc.birth_cer_reg_code where dc.del_flag='0' and dbc.pedigree_certified =#{pedigreeCertifiedCode} union select running_number from dog_pedigree_certified_change where del_flag='0' and overseas_pedigree_certified=#{pedigreeCertifiedEn} union select running_number from dog_appraisal where del_flag='0' and pedigree_certified = #{pedigreeCertifiedCode} )a limit 1 SELECT FROM dog_blood_save a WHERE a.pedigree_certified_code = #{pedigreeCertifiedCode} AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1 UPDATE dog_blood_save SET org_id = #{orgId} WHERE birth_code = #{birthCode} AND finish_time IS NULL UPDATE dog_blood_save SET is_OEC=#{isOEC}, update_by = #{updateBy.id}, update_date = #{updateDate} WHERE id = #{id} SELECT a.* FROM dog_blood_save a WHERE ( 1=2 or a.birth_code = #{birthCode} or a.show_appraisal_code = #{showAppraisalCode} or a.pedigree_certified_code = #{pedigreeCertified} or a.overseas_pedigree_certified = #{overseasPedigreeCertified} ) AND a.del_flag = '0' AND a.is_canceled = '0' LIMIT 1