a.id AS "id",
a.birth_cer_reg_code AS "birthCerRegCode",
a.pedigree_certified AS "pedigreeCertified",
a.dog_owenr AS "dogOwenr",
a.member_code AS "memberCode",
a.second_dog_owner AS "secondDogOwner",
a.second_owner_member_code AS "secondOwnerMemberCode",
a.breeder_member_code AS "breederMemberCode",
a.breeder AS "breeder",
a.second_breeder_member_code AS "secondBreederMemberCode",
a.second_breeder AS "secondBreeder",
a.temp_member_code AS "tempMemberCode",
a.temp_user_name AS "tempUserName",
a.dog_en_name AS "dogEnName",
a.dog_gender AS "dogGender",
a.dog_breed AS "dogBreed",
a.dog_birthday AS "dogBirthday",
a.same_batch_num AS "sameBatchNum",
a.identification_flag AS "identificationFlag",
a.cab_passport AS "cabPassport",
a.cab_register_code AS "cabRegisterCode",
a.color_flag AS "colorFlag",
a.dna AS "dna",
a.fdog_pedigree_certified AS "fdogPedigreeCertified",
a.mdog_pedigree_certified AS "mdogPedigreeCertified",
a.fmdog_breed_certified_code AS "fmdogBreedCertifiedCode",
a.ecert_flag AS "ecertFlag",
a.price AS "price",
a.deduction AS "deduction",
a.add_user AS "addUser",
a.urgent AS "urgent",
a.chip_inject_date AS "chipInjectDate",
a.sn_code AS "snCode",
a.business_ban AS "businessBan",
a.show_ban AS "showBan",
a.print_type AS "printType",
a.class_print_status AS "classPrintStatus",
a.send_date AS "sendDate",
a.reget_new_birth_code AS "regetNewBirthCode",
a.reget_old_birth_code AS "regetOldBirthCode",
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.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.coupon_code AS "couponCode",
a.activation_states AS "activitionState",
a.process_state AS "processState",
a.is_all AS "isAll",
a.first_status as "firstStatus"
a.del_flag = #{DEL_FLAG_NORMAL}
AND (a.birth_cer_reg_code = #{birthCerRegCode} OR cab_passport=#{birthCerRegCode})
AND a.review_state != #{reviewStateExclud}
AND (a.dog_owenr LIKE concat(#{dogOwenr},'%') OR a.breeder LIKE concat(#{dogOwenr},'%')
OR a.member_code = #{dogOwenr} OR a.breeder_member_code = #{dogOwenr})
AND a.fmdog_breed_certified_code = #{fmdogBreedCertifiedCode}
AND a.urgent = #{urgent}
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.review_state = #{reviewState}
AND a.payment_state = #{paymentState}
AND a.running_number = #{runningNumber}
AND a.cab_passport = #{cabPassport}
AND a.cab_register_code = #{cabRegisterCode}
AND a.cab_passport LIKE CONCAT('%',#{cabPassportLike},'%')
AND b.province ${sqlMap.province}
AND a.activation_states = #{activitionState}
AND a.print_type = '9'
AND a.send_date < CURDATE()
AND a.activation_states = #{activitionState}
AND a.print_type = '9'
AND a.send_date < CURDATE()
AND a.activation_states = #{activitionState}
AND a.print_type = '9'
AND a.send_date < curdate()
AND a.activation_states IS NULL
AND CURDATE() DATE_ADD(a.send_date,interval 151 day)
AND a.create_date >= '2018-09-01'
AND(
a.birth_cer_reg_code NOT IN (
SELECT
da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
)
)
AND a.create_date >= '2018-09-01'
AND a.activation_states IS NULL
AND CURDATE() = ]]> DATE_ADD(a.send_date,interval 151 day)
AND(
a.birth_cer_reg_code NOT IN (
SELECT
da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
)
)
AND a.dog_birthday > DATE_SUB(CURDATE(), INTERVAL 24 MONTH)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '1'
AND da.upper_review = '1'
AND da.review_state = '1'
)
)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '0'
AND da.upper_review = '1'
AND da.review_state = '1'
)
)
AND(
a.birth_cer_reg_code NOT IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
)
AND a.print_type = '9'
)
AND (
(a.activation_states IS NULL
AND a.create_date < '2018-09-01')
OR (a.activation_states = '1'
AND a.create_date >= '2018-09-01')
)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '1'
AND da.upper_review = '0'
AND da.type = '2'
)
)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '1'
AND da.upper_review = '2'
AND da.type = '2'
)
)
AND a.dog_breed ${sqlMap.dogBreedMap}
AND a.member_code = #{memberCode}
AND a.sn_code = #{snCode}
AND a.identification_flag = #{identificationFlag}
AND a.print_type = #{printType}
AND a.print_type = '1'
AND a.class_print_status = '1'
AND a.print_type = '1'
AND a.class_print_status = '1'
AND a.print_type = '9'
AND (a.class_print_status = '9' OR a.class_print_status ='' )
AND a.add_user = #{addUser}
AND a.pedigree_certified=#{pedigreeCertified}
AND a.print_type=#{newbornPrintType}
AND a.class_print_status = #{classPrintStatus}
AND a.breeder_member_code=#{breederMemberCode}
AND a.dog_birthday = ]]> #{dogBirthday}
AND a.dog_birthday DATE_ADD(#{dogBirthday},interval 1 day)
AND a.is_all = #{isAll}
AND a.fmdog_breed_certified_code in (${id})
AND a.dog_breed = #{dogBreed}
a.del_flag = #{DEL_FLAG_NORMAL}
AND (a.birth_cer_reg_code = #{birthCerRegCode} OR cab_passport=#{birthCerRegCode})
AND a.review_state != #{reviewStateExclud}
AND (a.dog_owenr LIKE concat(#{dogOwenr},'%') OR a.breeder LIKE concat(#{dogOwenr},'%')
OR a.member_code = #{dogOwenr} OR a.breeder_member_code = #{dogOwenr})
AND a.fmdog_breed_certified_code = #{fmdogBreedCertifiedCode}
AND a.urgent = #{urgent}
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.review_state = #{reviewState}
AND a.payment_state = #{paymentState}
AND a.running_number = #{runningNumber}
AND a.cab_passport = #{cabPassport}
AND a.cab_register_code = #{cabRegisterCode}
AND a.cab_passport LIKE CONCAT('%',#{cabPassportLike},'%')
AND b.province ${sqlMap.province}
AND a.activation_states = #{activitionState}
AND a.print_type = '9'
AND a.send_date < CURDATE()
AND a.activation_states = #{activitionState}
AND a.print_type = '9'
AND a.send_date < CURDATE()
AND a.activation_states = #{activitionState}
AND a.print_type = '9'
AND a.send_date < curdate()
AND a.activation_states IS NULL
AND CURDATE() DATE_ADD(a.send_date,interval 151 day)
AND a.create_date >= '2018-09-01'
AND(
a.birth_cer_reg_code NOT IN (
SELECT
da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
)
)
AND a.create_date >= '2018-09-01'
AND a.activation_states IS NULL
AND CURDATE() = ]]> DATE_ADD(a.send_date,interval 151 day)
AND(
a.birth_cer_reg_code NOT IN (
SELECT
da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
)
)
AND a.dog_birthday > DATE_SUB(CURDATE(), INTERVAL 24 MONTH)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '1'
AND da.upper_review = '1'
AND da.review_state = '1'
)
)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '0'
AND da.upper_review = '1'
AND da.review_state = '1'
)
)
AND(
a.birth_cer_reg_code NOT IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
)
AND a.print_type = '9'
)
AND (
(a.activation_states IS NULL
AND a.create_date < '2018-09-01')
OR (a.activation_states = '1'
AND a.create_date >= '2018-09-01')
)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '1'
AND da.upper_review = '0'
AND da.type = '2'
)
)
AND(
a.birth_cer_reg_code IN (
SELECT da.birth_code
FROM
dog_activation da
WHERE
da.del_flag = '0'
AND da.state = '1'
AND da.upper_review = '2'
AND da.type = '2'
)
)
AND a.dog_breed ${sqlMap.dogBreedMap}
AND a.member_code = #{memberCode}
AND a.sn_code = #{snCode}
AND a.identification_flag = #{identificationFlag}
AND a.print_type = #{printType}
AND a.print_type = '1'
AND a.class_print_status = '1'
AND a.print_type = '1'
AND a.class_print_status = '1'
AND a.print_type = '9'
AND (a.class_print_status = '9' OR a.class_print_status ='' )
AND a.add_user = #{addUser}
AND a.pedigree_certified=#{pedigreeCertified}
AND a.print_type=#{newbornPrintType}
AND a.class_print_status = #{classPrintStatus}
AND a.breeder_member_code=#{breederMemberCode}
AND a.dog_birthday = ]]> #{dogBirthday}
AND a.dog_birthday DATE_ADD(#{dogBirthday},interval 1 day)
AND a.is_all = #{isAll}
AND a.fmdog_breed_certified_code in (${id})
AND a.dog_breed = #{dogBreed}
INSERT INTO dog_birth_certificate(
id,
birth_cer_reg_code,
pedigree_certified,
dog_owenr,
member_code,
second_dog_owner,
second_owner_member_code,
breeder_member_code,
breeder,
second_breeder_member_code,
second_breeder,
temp_member_code,
temp_user_name,
dog_en_name,
dog_gender,
dog_breed,
dog_birthday,
same_batch_num,
identification_flag,
color_flag,
dna,
fdog_pedigree_certified,
mdog_pedigree_certified,
fmdog_breed_certified_code,
ecert_flag,
price,
deduction,
add_user,
urgent,
chip_inject_date,
sn_code,
business_ban,
show_ban,
print_type,
class_print_status,
send_date,
reget_old_birth_code,
cab_passport,
cab_register_code,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
review_state,
review_time,
review_remarks,
running_number,
payment_state,
payment_time,
process_time,
process_state,
coupon_code,
activation_states,
is_all,
operator
) VALUES (
#{id},
#{birthCerRegCode},
#{pedigreeCertified},
#{dogOwenr},
#{memberCode},
#{secondDogOwner},
#{secondOwnerMemberCode},
#{breederMemberCode},
#{breeder},
#{secondBreederMemberCode},
#{secondBreeder},
#{tempMemberCode},
#{tempUserName},
#{dogEnName},
#{dogGender},
#{dogBreed},
#{dogBirthday},
#{sameBatchNum},
#{identificationFlag},
#{colorFlag},
#{dna},
#{fdogPedigreeCertified},
#{mdogPedigreeCertified},
#{fmdogBreedCertifiedCode},
#{ecertFlag},
#{price},
#{deduction},
#{addUser},
#{urgent},
#{chipInjectDate},
#{snCode},
#{businessBan},
#{showBan},
#{printType},
#{classPrintStatus},
#{sendDate},
#{regetOldBirthCode},
#{cabPassport},
#{cabRegisterCode},
#{createBy.id},
#{createDate},
#{updateBy.id},
#{updateDate},
#{remarks},
#{delFlag},
#{reviewState},
#{reviewTime},
#{reviewRemarks},
#{runningNumber},
#{paymentState},
#{paymentTime},
#{processTime},
#{processState},
#{couponCode},
#{activitionState},
#{isAll},
#{operator}
)
UPDATE dog_birth_certificate SET
birth_cer_reg_code = #{birthCerRegCode},
dog_owenr = #{dogOwenr},
member_code = #{memberCode},
identification_flag=#{identificationFlag},
cab_passport = #{cabPassport},
cab_register_code = #{cabRegisterCode},
second_dog_owner = #{secondDogOwner},
second_owner_member_code = #{secondOwnerMemberCode},
breeder_member_code = #{breederMemberCode},
breeder = #{breeder},
second_breeder_member_code = #{secondBreederMemberCode},
second_breeder = #{secondBreeder},
temp_member_code = #{tempMemberCode},
temp_user_name = #{tempUserName},
dog_en_name = #{dogEnName},
dog_gender = #{dogGender},
dog_breed = #{dogBreed},
dog_birthday = #{dogBirthday},
same_batch_num = #{sameBatchNum},
color_flag = #{colorFlag},
dna = #{dna},
fdog_pedigree_certified = #{fdogPedigreeCertified},
mdog_pedigree_certified = #{mdogPedigreeCertified},
fmdog_breed_certified_code = #{fmdogBreedCertifiedCode},
deduction = #{deduction},
add_user = #{addUser},
urgent = #{urgent},
chip_inject_date = #{chipInjectDate},
sn_code = #{snCode},
business_ban = #{businessBan},
show_ban = #{showBan},
print_type = #{printType},
class_print_status = #{classPrintStatus},
send_date = #{sendDate},
reget_new_birth_code = #{regetNewBirthCode},
update_by = #{updateBy.id},
update_date = #{updateDate},
remarks = #{remarks},
review_state = #{reviewState},
review_time = #{reviewTime},
review_remarks = #{reviewRemarks},
running_number = #{runningNumber},
payment_state = #{paymentState},
payment_time = #{paymentTime},
process_time = #{processTime},
coupon_code = #{couponCode},
is_all=#{isAll},
first_status=#{firstStatus},
activation_states = #{activitionState},
process_state = #{processState}
WHERE id = #{id}
UPDATE dog_birth_certificate SET
remarks = concat(IFNULL(remarks,''),#{remarks}),
del_flag = #{DEL_FLAG_DELETE}
WHERE id = #{id}
UPDATE dog_birth_certificate SET
review_state = #{reviewState},
review_remarks = #{reviewRemarks},
review_time = #{reviewTime}
WHERE fmdog_breed_certified_code = #{fmdogBreedCertifiedCode}
AND del_flag = '0'
AND review_state != '3'
update dog_birth_certificate set
pedigree_certified = #{pedigreeCertified}
where birth_cer_reg_code = #{birthCode}
UPDATE dog_birth_certificate SET
print_type = #{printType}
WHERE fmdog_breed_certified_code = #{fmdogBreedCertifiedCode}
UPDATE dog_birth_certificate SET
activation_states =#{activitionState}
WHERE birth_cer_reg_code = #{birthCode}
UPDATE dog_birth_certificate SET
class_print_status = #{classPrintStatus}
WHERE fmdog_breed_certified_code = #{fmdogBreedCertifiedCode}
and review_state = '1'
update dog_birth_certificate set
add_user=#{addUser} ,
identification_flag=#{identificationFlag}
where birth_cer_reg_code = #{birthCerRegCode}
update dog_birth_certificate set identification_flag=#{identificationFlag} where pedigree_certified=#{pedigreeCertified}
update dog_birth_certificate
set member_code = #{dogOwenr},
dog_owenr = #{hidDogOwenrName}
where birth_cer_reg_code = #{birthCode}
update dog_birth_certificate
set temp_member_code = #{dogOwenr},
temp_user_name = #{hidDogOwenrName}
where birth_cer_reg_code = #{birthCode}
update dog_birth_certificate set
add_user = #{addUser},
temp_member_code = #{dogOwenr},
temp_user_name = #{hidDogOwenrName}
where birth_cer_reg_code = #{birthCode}
UPDATE dog_activation AS a
LEFT JOIN dog_birth_certificate AS b ON a.birth_code = b.birth_cer_reg_code
a.state =#{state}
WHERE a.birth_code = #{id}
AND b.member_code = #{mcode}
INSERT INTO breeder_sold_dog_birth_certificate(
birth_code,
member_code
) VALUES (
#{birthCerRegCode},
#{breederMemberCode}
)
UPDATE dog_birth_certificate SET
business_ban = #{businessBan}
WHERE pedigree_certified = #{pedigreeCertified}
UPDATE dog_birth_certificate SET
del_flag = '1',
remarks = #{remarks}
WHERE fmdog_breed_certified_code = #{fmdogBreedCertifiedCode}
and review_state != '3'
UPDATE
dog_birth_certificate
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}
UPDATE
dog_birth_certificate
SET
review_state = '3',
review_remarks = #{vo.reviewRemarks},
update_date = #{vo.updateDate},
update_by = #{vo.updateBy.id}
WHERE
del_flag = '0'
AND is_all ='1'
AND fmdog_breed_certified_code in
#{item}
UPDATE dog_birth_certificate SET ecert_flag = '0',print_type='1' where id = #{id}
UPDATE dog_birth_certificate SET add_user = #{addUser} WHERE birth_cer_reg_code = #{birthCode}
UPDATE
dog_birth_certificate
SET
add_user = null
WHERE
del_flag = '0'
AND birth_cer_reg_code =
(select birth_code from dog_chip where id =#{id})