a.id AS "id",
a.birth_code AS "birthCode",
a.dna_save_way AS "dnaSaveWay",
a.is_dna_parents_compare AS "isDnaParentsCompare",
a.payment_member_code AS "paymentMemberCode",
a.operator AS "operator",
a.ecert_flag AS "ecertFlag",
a.price AS "price",
a.hospital_name AS "hospitalName",
a.hospital_code AS "hospitalCode",
a.blood_save_flag AS "bloodSaveFlag",
a.dog_chip_change_id AS "dogChipChangeId",
a.del_time AS "delTime",
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.send_date AS "sendDate",
a.sticker_flag AS "stickerFlag",
a.print_source AS "printSource",
a.fmdog_breed_certified_code AS "fmdogBreedCertifiedCode",
d.print_state AS "printState",
d.dog_owner as "dogOwner",
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",
a.call_name AS "callName",
a.chip_inject_flag AS "chipInjectFlag",
a.is_all AS "isAll",
a.haddle_way AS "haddleWay",
b.id AS "dogBirthCertificate.id",
b.pedigree_certified "dogBirthCertificate.pedigreeCertified",
b.birth_cer_reg_code "dogBirthCertificate.birthCerRegCode",
b.dog_owenr "dogBirthCertificate.dogOwenr",
b.member_code "dogBirthCertificate.memberCode",
b.identification_flag "dogBirthCertificate.identificationFlag",
b.cab_passport "dogBirthCertificate.cabPassport",
b.add_user "dogBirthCertificate.addUser",
b.dog_breed "dogBirthCertificate.dogBreed",
b.second_dog_owner "dogBirthCertificate.secondDogOwner",
b.second_owner_member_code "dogBirthCertificate.secondOwnerMemberCode",
b.breeder_member_code AS "dogBirthCertificate.breederMemberCode",
b.breeder AS "dogBirthCertificate.breeder",
b.second_breeder_member_code AS "dogBirthCertificate.secondBreederMemberCode",
b.second_breeder AS "dogBirthCertificate.secondBreeder",
b.temp_member_code AS "dogBirthCertificate.tempMemberCode",
b.temp_user_name AS "dogBirthCertificate.tempUserName",
b.dog_en_name AS "dogBirthCertificate.dogEnName",
b.dog_gender AS "dogBirthCertificate.dogGender",
b.dog_birthday AS "dogBirthCertificate.dogBirthday",
b.same_batch_num AS "dogBirthCertificate.sameBatchNum",
b.color_flag AS "dogBirthCertificate.colorFlag",
b.dna AS "dogBirthCertificate.dna",
b.fdog_pedigree_certified AS "dogBirthCertificate.fdogPedigreeCertified",
b.mdog_pedigree_certified AS "dogBirthCertificate.mdogPedigreeCertified",
b.fmdog_breed_certified_code AS "dogBirthCertificate.fmdogBreedCertifiedCode",
b.deduction AS "dogBirthCertificate.deduction",
b.urgent AS "dogBirthCertificate.urgent",
b.chip_inject_date AS "dogBirthCertificate.chipInjectDate",
b.sn_code AS "dogBirthCertificate.snCode",
b.business_ban AS "dogBirthCertificate.businessBan",
b.show_ban AS "dogBirthCertificate.showBan",
c.name "sysOrg.name",
d.dog_breed AS "dogBreed"
LEFT JOIN dog_birth_certificate b ON a.birth_code = b.birth_cer_reg_code -- AND b.del_flag = '0'
LEFT JOIN sys_org c ON a.hospital_code = c.id
LEFT JOIN dog d ON b.pedigree_certified = d.pedigree_certified_code AND d.del_flag = '0'
LEFT JOIN dog_nose n ON b.id = n.dog_birth_certificate_id AND n.del_flag = '0'
${sql}
AND a.del_flag = #{delFlag}
AND a.birth_code LIKE concat(#{birthCode},'%')
AND a.operator=#{operator}
AND b.pedigree_certified= #{dogBirthCertificate.pedigreeCertified}
AND b.identification_flag = #{identificationFlag}
AND b.cab_passport LIKE CONCAT('%',#{dogBirthCertificate.cabPassportLike},'%')
AND (
b.dog_owenr = #{dogBirthCertificate.dogOwenr}
OR b.member_code = #{dogBirthCertificate.dogOwenr}
OR b.temp_member_code = #{dogBirthCertificate.dogOwenr}
OR b.temp_user_name = #{dogBirthCertificate.dogOwenr}
)
AND a.review_state = #{reviewState}
AND a.payment_state = #{paymentState}
AND date_format(a.create_date,'%Y-%m-%d') =]]> date_format(#{createDate},'%Y-%m-%d')
AND date_format(a.create_date,'%Y-%m-%d') date_format(#{endDate},'%Y-%m-%d')
AND c.name like '%${sysOrg.name}%'
AND c.org_type = #{sysOrg.orgType}
AND a.payment_member_code = #{paymentMemberCode}
AND date_format(b.chip_inject_date,'%Y-%m-%d') =]]> date_format(#{chipInjectDate},'%Y-%m-%d')
AND date_format(b.chip_inject_date,'%Y-%m-%d') date_format(#{endInjectDate},'%Y-%m-%d')
AND a.running_number = #{runningNumber}
AND date_format(a.del_time,'%Y-%m-%d') =]]> date_format(#{canceledDate},'%Y-%m-%d')
AND date_format(a.del_time,'%Y-%m-%d') date_format(#{canceledEndDate},'%Y-%m-%d')
AND a.is_canceled = #{isCanceled}
AND (a.is_canceled is null or a.is_canceled ='' or a.is_canceled = '0')
AND b.dog_birthday = ]]> #{dogBirthCertificate.dogBirthday}
AND b.dog_birthday DATE_ADD(#{dogBirthCertificate.dogBirthday},interval 1 day)
AND a.create_date = ]]> #{applyDate}
AND a.create_date DATE_ADD(#{applyDate},interval 1 day)
AND a.process_state = '1'
AND d.print_state = #{printState}
AND date_format(a.process_time,'%Y-%m-%d') =]]> date_format(#{processStartDate},'%Y-%m-%d')
AND date_format(a.process_time,'%Y-%m-%d') date_format(#{processEndDate},'%Y-%m-%d')
AND d.create_by = #{processUserId}
AND b.dog_breed ${sqlMap.dogBreedMap}
AND a.blood_save_flag = #{bloodSaveFlag}
AND a.id in (${id})
AND a.fmdog_breed_certified_code = ${fmdogBreedCertifiedCode}
AND a.review_state != #{reviewStateExclud}
AND a.haddle_way = #{haddleWay}
AND n.nose_id IS NOT NULL AND n.nose_id != ''
AND (n.nose_id IS NULL OR n.nose_id = '')
AND b.identification_flag IS NOT NULL AND b.identification_flag != ''
AND (b.identification_flag IS NULL OR b.identification_flag = '')
AND a.process_state = #{processState}
INSERT INTO dog_chip(
id,
birth_code,
dna_save_way,
is_dna_parents_compare,
payment_member_code,
operator,
blood_save_flag,
dog_chip_change_id,
ecert_flag,
price,
hospital_name,
hospital_code,
del_time,
review_state,
review_time,
review_remarks,
running_number,
payment_state,
payment_time,
process_time,
process_state,
sticker_flag,
create_by,
create_date,
update_by,
update_date,
remarks,
print_state,
call_name,
chip_inject_flag,
is_all,
haddle_way,
del_flag,
fmdog_breed_certified_code
) VALUES (
#{id},
#{birthCode},
#{dnaSaveWay},
#{isDnaParentsCompare},
#{paymentMemberCode},
#{operator},
#{bloodSaveFlag},
#{dogChipChangeId},
#{ecertFlag},
#{price},
#{hospitalName},
#{hospitalCode},
#{delTime},
#{reviewState},
#{reviewTime},
#{reviewRemarks},
#{runningNumber},
#{paymentState},
#{paymentTime},
#{processTime},
#{processState},
#{stickerFlag},
#{createBy.id},
#{createDate},
#{updateBy.id},
#{updateDate},
#{remarks},
#{printState},
#{callName},
#{chipInjectFlag},
#{isAll},
#{haddleWay},
#{delFlag},
#{fmdogBreedCertifiedCode}
)
UPDATE dog_chip SET
birth_code = #{birthCode},
dna_save_way = #{dnaSaveWay},
is_dna_parents_compare = #{isDnaParentsCompare},
payment_member_code = #{paymentMemberCode},
operator = #{operator},
blood_save_flag = #{bloodSaveFlag},
price = #{price},
hospital_name = #{hospitalName},
hospital_code = #{hospitalCode},
dog_chip_change_id = #{dogChipChangeId},
del_time = #{delTime},
review_state = #{reviewState},
review_time = #{reviewTime},
review_remarks = #{reviewRemarks},
running_number = #{runningNumber},
payment_state = #{paymentState},
payment_time = #{paymentTime},
process_time = #{processTime},
process_state = #{processState},
send_date = #{sendDate},
print_source = #{printSource},
update_by = #{updateBy.id},
update_date = #{updateDate},
call_name = #{callName},
is_all = #{isAll},
sticker_flag = #{stickerFlag},
haddle_way = #{haddleWay},
ecert_flag = #{ecertFlag} ,
remarks = #{remarks}
WHERE id = #{id}
UPDATE dog_chip SET
remarks = concat(IFNULL(remarks,''),#{remarks}),
del_flag = #{DEL_FLAG_DELETE}
WHERE id = #{id}
UPDATE dog_chip SET
review_state = #{reviewState},
review_time = SYSDATE(),
review_remarks = #{reviewRemarks},
update_by = #{updateBy.id},
update_date = #{updateDate}
WHERE id = #{id}
UPDATE dog_chip SET
process_time = #{processTime},
process_state = #{processState},
print_state = #{printState},
update_by = #{updateBy.id},
update_date = #{updateDate}
WHERE id = #{id}
UPDATE dog_chip SET is_canceled = #{isCanceled},del_time = #{delTime} where id = #{id}
UPDATE dog_chip SET price = #{floatingPrice} where id = #{id}
delete from dog_chip where id=#{id}
UPDATE
dog_chip
SET
review_state = '3',
review_remarks = #{vo.reviewRemarks},
update_date = #{vo.updateDate},
update_by = #{vo.updateBy.id}
WHERE
del_flag = '0'
AND running_number =#{vo.runningNumber}
AND (fmdog_breed_certified_code in
#{item}
)or
(id in
#{item}
)
UPDATE
dog_chip
SET haddle_way = #{haddleWay} ,
hospital_code = #{hospitalCode},
hospital_name = #{hospitalName}
WHERE
id = #{id}
UPDATE dog_chip SET ecert_flag = '0',print_state='2' where id = #{id}