t.id as 'id',
t.birth_code as 'birthCode',
t.pedigree_certified_code as 'pedigreeCertified',
t.sampling_code as 'samplingCode',
t.is_OEC as 'isOEC',
t.apply_channel,
t.payment_member_code as 'memberCode',
t.finish_time as 'finishTime',
t.type as type,
t.org_id as 'orgId',
t.tableName as tableName,
t.create_date as 'createDate',
t.name_en as "dogName",
t.dog_breed as "dogBreed",
t.gender as "dogGender",
t.birthdate as "dogBirthday",
t.is_canceled as "isCanceled",
t.dog_state AS "dogState"
select
AND (a.pedigree_certified LIKE concat('%',#{pedigreeCertified},'%') or a.birth_code LIKE concat('%',#{pedigreeCertified},'%'))
and (b.id is not null or dbc.id is not null )
UNION
SELECT
a.id,
a.birth_code,
a.pedigree_certified_code,
a.payment_member_code,
a.finish_time,
'' AS sampling_code,
a.is_OEC,
'0' AS apply_channel,
'0' AS type,
a.org_id,
'dog_blood_save' AS tableName,
a.create_date,
a.is_canceled,
b.name_en,
b.dog_breed,
b.gender,
b.birthdate,
b.dog_state
FROM
dog_blood_save a
LEFT JOIN
dog b
ON
b.del_flag='0'
AND a.pedigree_certified_code=b.pedigree_certified_code
AND b.member_code= #{memberCode}
WHERE
a.del_flag = '0'
AND a.payment_state='2'
AND a.is_oec !='1'
and a.pedigree_certified_code LIKE concat('%',#{pedigreeCertified},'%')
AND b.id IS NOT NULL
UNION
SELECT
a.id,
a.birth_code,
a.pedigree_certified_code,
a.payment_member_code,
a.finish_time,
'' AS sampling_code,
a.is_OEC,
'0' AS apply_channel,
'0' AS type,
a.org_id,
'dog_blood_save' AS tableName,
a.create_date,
a.is_canceled,
dbc.dog_en_name,
dbc.dog_breed,
dbc.dog_gender,
dbc.dog_birthday,
null AS dog_state
FROM
dog_blood_save a
LEFT JOIN
ckuoa.dog_birth_certificate dbc
ON
dbc.del_flag='0'
AND a.birth_code=dbc.birth_cer_reg_code
AND dbc.member_code= #{memberCode}
WHERE
a.del_flag = '0'
AND a.payment_state='2'
AND a.is_oec !='1'
AND a.pedigree_certified_code IS NULL
and a.birth_code LIKE concat('%',#{pedigreeCertified},'%')
AND dbc.id IS NOT NULL
) t
ORDER BY t.create_date desc,t.type asc
SELECT
a.id as "id",
a.pedigree_certified_code as "pedigreeCertified",
'' as 'bloodSampleCode',
'0' as 'isOEC',
a.name_en as "dogName",
a.call_name as "callName",
a.dog_breed as "dogBreed",
a.gender as "dogGender",
a.birthdate as "dogBirthday",
a.birth_certificate_level_code as "birthCode"
FROM
dog a
LEFT JOIN dog_blood_save b ON a.pedigree_certified_code = b.pedigree_certified_code and b.is_canceled='0' and b.del_flag='0'
LEFT JOIN dog_dna_archive c ON a.pedigree_certified_code = c.pedigree_certified and c.is_canceled='0' and c.del_flag='0'
WHERE
a.del_flag='0'
AND a.dog_state='0'
AND a.member_code = #{memberCode}
AND a.pedigree_certified_code LIKE concat('%',#{pedigreeCertified},'%')
AND (
((b.id is null or b.payment_state!='2') and (c.id is null) )
or
((c.id is null and b.id is null) or (c.id is not null and b.id is not null and c.payment_state!='2' and b.payment_state!='2'))
)
ORDER BY a.birthdate desc
select id as "id",birth_code as "birthCode" from dog_blood_save where del_flag='0' and birth_code is not null and birth_code <> '' and (pedigree_certified_code is null or pedigree_certified_code='')
select id as "id",birth_code as "birthCode" from dog_dna_archive where del_flag='0' and (birth_code is not null and birth_code <> '') and (pedigree_certified is null or pedigree_certified='');
update dog_blood_save set pedigree_certified_code = #{pedigreeCertified} where id=#{id}
update dog_dna_archive set pedigree_certified = #{pedigreeCertified} where id=#{id}