a.id AS "id",
a.dog_id AS "dogId",
a.show_code AS "showCode",
a.referee AS "referee",
a.pedigree_certified AS "pedigreeCertified",
a.other_certified AS "otherCertified",
a.race_lap AS "raceLap",
a.dog_breed_group AS "dogBreedGroup",
a.age_group AS "ageGroup",
a.dog_gender AS "dogGender",
a.fci_code AS "fciCode",
a.dog_owner_name_cn AS "dogOwnerNameCn",
a.dog_owner_name_en AS "dogOwnerNameEn",
a.dog_owner_member_code AS "dogOwnerMemberCode",
a.show_result AS "showResult",
a.show_breed_result AS "showBreedResult",
a.show_group_result AS "showGroupResult",
a.order_code AS "orderCode",
a.appraisal AS "appraisal",
a.result_cc AS "resultCc",
a.remarks_cc AS "remarksCc",
a.result_cacib AS "resultCacib",
a.remarks_cacib AS "remarksCacib",
a.result_bob AS "resultBob",
a.result_bis AS "resultBis",
a.result_big AS "resultBig",
a.result_bow AS "resultBow",
a.result_wb AS "resultWb",
a.result_boj AS "resultBoj",
a.result_jbig AS "resultJbig",
a.result_jbis AS "resultJbis",
a.result_apac_cc AS "resultApaccc",
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.result_bbe_bis AS "resultBbeBis",
a.result_bbe_big AS "resultBbeBig",
a.result_bbe_biss AS "resultBbeBiss",
a.result_bbe_winner AS "resultBbeWinner",
a.result_ohc_bis AS "resultOhcBis",
a.result_ohc_big AS "resultOhcBig",
a.result_ohc_biss AS "resultOhcBiss",
a.result_ohc_boh AS "resultOhcBoh",
a.ohc_score AS "ohcScore",
a.bbe_score AS "bbeScore",
a.result_bov AS "resultBov",
a.year AS "year",
a.qus_result AS "qusResult",
a.qus_code AS "qusCode"
INSERT INTO show_results(
id,
dog_id,
show_code,
year,
referee,
pedigree_certified,
other_certified,
race_lap,
dog_breed_group,
age_group,
dog_gender,
fci_code,
dog_owner_name_cn,
dog_owner_name_en,
dog_owner_member_code,
show_result,
order_code,
appraisal,
result_cc,
remarks_cc,
result_cacib,
remarks_cacib,
result_bob,
result_bis,
result_big,
result_bow,
result_wb,
result_boj,
result_jbig,
result_jbis,
result_apac_cc,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
result_bbe_bis,
result_bbe_big,
result_bbe_biss,
result_bbe_winner,
result_ohc_bis,
result_ohc_big,
result_ohc_biss,
result_ohc_boh,
ohc_score,
bbe_score,
result_bov
) VALUES (
#{id},
#{dogId},
#{showCode},
#{year},
#{referee},
#{pedigreeCertified},
#{otherCertified},
#{raceLap},
#{dogBreedGroup},
#{ageGroup},
#{dogGender},
#{fciCode},
#{dogOwnerNameCn},
#{dogOwnerNameEn},
#{dogOwnerMemberCode},
#{showResult},
#{orderCode},
#{appraisal},
#{resultCc},
#{remarksCc},
#{resultCacib},
#{remarksCacib},
#{resultBob},
#{resultBis},
#{resultBig},
#{resultBow},
#{resultWb},
#{resultBoj},
#{resultJbig},
#{resultJbis},
#{resultApaccc},
#{createBy.id},
#{createDate},
#{updateBy.id},
#{updateDate},
#{remarks},
#{delFlag},
#{resultBbeBis},
#{resultBbeBig},
#{resultBbeBiss},
#{resultBbeWinner},
#{resultOhcBis},
#{resultOhcBig},
#{resultOhcBiss},
#{resultOhcBoh},
#{ohcScore},
#{bbeScore},
#{resultBov}
)
UPDATE show_results SET
dog_id = #{dogId},
show_code = #{showCode},
referee = #{referee},
pedigree_certified = #{pedigreeCertified},
other_certified = #{otherCertified},
race_lap = #{raceLap},
dog_breed_group = #{dogBreedGroup},
age_group = #{ageGroup},
dog_gender = #{dogGender},
fci_code = #{fciCode},
dog_owner_name_cn = #{dogOwnerNameCn},
dog_owner_name_en = #{dogOwnerNameEn},
dog_owner_member_code = #{dogOwnerMemberCode},
show_result = #{showResult},
show_breed_result = #{showBreedResult},
order_code = #{orderCode},
appraisal = #{appraisal},
result_cc = #{resultCc},
remarks_cc = #{remarksCc},
result_cacib = #{resultCacib},
remarks_cacib = #{remarksCacib},
result_bob = #{resultBob},
result_bis = #{resultBis},
result_big = #{resultBig},
result_bow = #{resultBow},
result_boj = #{resultBoj},
result_jbig = #{resultJbig},
result_jbis = #{resultJbis},
result_apac_cc = #{resultApaccc},
result_wb = #{resultWb},
update_by = #{updateBy.id},
update_date = #{updateDate},
remarks = #{remarks},
result_bbe_bis = #{resultBbeBis},
result_bbe_big = #{resultBbeBig},
result_bbe_biss = #{resultBbeBiss},
result_bbe_winner = #{resultBbeWinner},
result_ohc_bis = #{resultOhcBis},
result_ohc_big = #{resultOhcBig},
result_ohc_biss = #{resultOhcBiss},
result_ohc_boh = #{resultOhcBoh},
ohc_score = #{ohcScore},
bbe_score = #{bbeScore},
result_bov = #{resultBov}
WHERE id = #{id}
UPDATE show_results SET
remarks = concat(IFNULL(remarks,''),#{remarks}),
del_flag = #{DEL_FLAG_DELETE}
WHERE id = #{id}
UPDATE show_results SET show_result = '0',show_breed_result = '0',show_group_result='0'
WHERE show_code = #{_parameter} AND del_flag = '0'
UPDATE show_results
SET bbe_score = #{bbeScore}
WHERE pedigree_certified = #{pedigreeCertified} AND del_flag = '0'
UPDATE show_results
SET ohc_score = #{ohcScore}
WHERE pedigree_certified = #{pedigreeCertified} AND del_flag = '0'
UPDATE show_results tb
RIGHT JOIN (
SELECT
fci_code AS fcicode,
(COUNT(fci_code) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{_parameter}
AND age_group in (1,2)
AND del_flag = '0'
GROUP BY
fci_code
HAVING
COUNT(fci_code) - 1 > 0
) ta ON tb.fci_code = ta.fcicode
SET tb.show_result = ta.classnum
WHERE
show_code = #{_parameter}
AND age_group in (1,2)
AND result_bob = 'BPBOB'
AND result_big not like 'BPBIG%'
AND result_bis not like 'BPBIS%'
AND del_flag = '0'
UPDATE show_results tb
RIGHT JOIN (
SELECT
fci_code AS fcicode,
(COUNT(fci_code) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{_parameter}
AND age_group in (3,4)
AND del_flag = '0'
GROUP BY
fci_code
HAVING
COUNT(fci_code) - 1 > 0
) ta ON tb.fci_code = ta.fcicode
SET tb.show_result = ta.classnum
WHERE
show_code = #{_parameter}
AND age_group in (3,4)
AND result_bob = 'PBOB'
AND result_big not like 'PBIG%'
AND result_bis not like 'PBIS%'
AND del_flag = '0'
UPDATE show_results tb
RIGHT JOIN (
SELECT
fci_code AS fcicode,
(COUNT(fci_code) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{_parameter}
AND age_group > 4
AND del_flag = '0'
GROUP BY
fci_code
HAVING
(COUNT(fci_code) - 1) > 0
) ta ON tb.fci_code = ta.fcicode
SET tb.show_result = ta.classnum
WHERE
show_code = #{_parameter}
AND age_group IN ('12','13','14')
AND result_bob = 'BOB'
AND del_flag = '0'
AND pedigree_certified IN (
SELECT
a.pedigree_certified
FROM
show_champion_login a
WHERE
a.login_type IN ('1', '2', '3', '4', '5')
AND a.del_flag = '0'
UNION
SELECT
d.pedigree_certified_code
FROM
show_champion_login a
LEFT JOIN
dog d
ON
d.pedigree_certified_en= a.pedigree_certified
WHERE
a.login_type IN ('1', '2', '3', '4', '5')
AND a.del_flag = '0'
AND d.del_flag = '0'
)
UPDATE show_results SET
show_result = #{showResult}
WHERE id = #{id} AND del_flag = '0'
UPDATE show_results SET
show_result = #{showResult},
show_group_result = #{showResult}
WHERE id = #{id} AND del_flag = '0'
AND age_group IN ('1','2')
AND age_group IN ('3','4')
AND age_group IN ('12','13','14')
AND pedigree_certified IN (
SELECT
a.pedigree_certified
FROM
show_champion_login a
WHERE
a.login_type IN ('1', '2', '3', '4', '5')
AND a.del_flag = '0'
UNION
SELECT
d.pedigree_certified_code
FROM
show_champion_login a
LEFT JOIN
dog d
ON
d.pedigree_certified_en= a.pedigree_certified
WHERE
a.login_type IN ('1', '2', '3', '4', '5')
AND a.del_flag = '0'
AND d.del_flag = '0'
)
UPDATE show_results tb
RIGHT JOIN (
SELECT
dog_breed_group AS dogbreedgroup,
(SUM(show_result) + COUNT(*) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{showCode}
AND age_group IN (1, 2)
AND (result_big IS NULL OR result_big = '' OR replace(result_big,' ','') >= 'BPBIG${index}')
AND result_bob = 'BPBOB'
AND del_flag = '0'
GROUP BY dog_breed_group
) ta ON tb.dog_breed_group = ta.dogbreedgroup
SET tb.show_result = ta.classnum
WHERE
show_code = #{showCode}
AND age_group IN (1, 2)
AND replace(result_big,' ','') = 'BPBIG${index}'
AND result_bis not like 'BPBIS%'
AND del_flag = '0'
UPDATE show_results tb
RIGHT JOIN (
SELECT
dog_breed_group AS dogbreedgroup,
(SUM(show_result) + COUNT(*) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{showCode}
AND age_group IN (3, 4)
AND (result_big IS NULL OR result_big = '' OR replace(result_big,' ','') >= 'PBIG${index}')
AND result_bob = 'PBOB'
AND del_flag = '0'
GROUP BY dog_breed_group
) ta ON tb.dog_breed_group = ta.dogbreedgroup
SET tb.show_result = ta.classnum
WHERE
show_code = #{showCode}
AND age_group IN (3, 4)
AND del_flag = '0'
AND replace(result_big,' ','') = 'PBIG${index}'
AND result_bis not like 'PBIS%'
AND del_flag = '0'
UPDATE show_results tb
RIGHT JOIN (
SELECT
dog_breed_group AS dogbreedgroup,
(SUM(show_result) + COUNT(*) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{showCode}
AND age_group > 4
AND (result_big IS NULL OR result_big = '' OR result_big >= 'BIG ${index}')
AND result_bob = 'BOB'
AND del_flag = '0'
GROUP BY dog_breed_group
) ta ON tb.dog_breed_group = ta.dogbreedgroup
SET tb.show_result = ta.classnum
WHERE
show_code = #{showCode}
AND age_group > 4
AND del_flag = '0'
AND result_big = 'BIG ${index}'
UPDATE show_results
SET show_result = #{showResult}
WHERE
show_code = #{showCode}
AND replace(result_bis,' ','') = #{resultBis}
AND del_flag = '0'
AND age_group IN ('1','2')
AND age_group IN ('3','4')
AND age_group IN ('12','13','14')
AND pedigree_certified IN (
SELECT
a.pedigree_certified
FROM
show_champion_login a
WHERE
a.login_type IN ('1', '2', '3', '4', '5')
AND a.del_flag = '0'
UNION
SELECT
d.pedigree_certified_code
FROM
show_champion_login a
LEFT JOIN
dog d
ON
d.pedigree_certified_en= a.pedigree_certified
WHERE
a.login_type IN ('1', '2', '3', '4', '5')
AND a.del_flag = '0'
AND d.del_flag = '0'
)
UPDATE show_results SET show_result = '0'
WHERE show_code = #{_parameter} AND show_result < 0
AND del_flag = '0'
UPDATE show_results
SET
show_result = '0'
WHERE show_code = #{_parameter}
AND remarks_cc LIKE '成绩取消%'
AND del_flag = '0'
UPDATE show_results SET
year = #{year}
WHERE id = #{id} AND del_flag = '0'
UPDATE
show_results tb
RIGHT JOIN
( SELECT
fci_code AS fcicode,
(COUNT(fci_code) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{_parameter}
AND age_group IN ('5','6','7','8','9','10','13','14','15','16','17','18')
AND del_flag = '0'
GROUP BY
fci_code
HAVING
COUNT(fci_code) - 1 > 0 ) ta
ON
tb.fci_code = ta.fcicode
SET
tb.show_breed_result = ta.classnum
WHERE
show_code = #{_parameter}
AND age_group IN ('12','13','14')
AND result_bob = 'BOB'
AND del_flag = '0'
UPDATE
show_results tb
RIGHT JOIN
( SELECT
fci_code AS fcicode,
(COUNT(fci_code) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{_parameter}
AND age_group IN ('5','6','7','8','9','10','13','14','15','16','17','18')
AND del_flag = '0'
GROUP BY
fci_code
HAVING
COUNT(fci_code) - 1 > 0 ) ta
ON
tb.fci_code = ta.fcicode
SET
tb.show_breed_result = tb.show_breed_result + ta.classnum,
tb.show_result = tb.show_result + ta.classnum
WHERE
show_code = #{_parameter}
AND age_group IN ('12','13','14')
AND result_bis = 'BISS'
AND result_bov = 'BOV'
AND remarks_cc NOT LIKE '成绩取消%'
AND del_flag = '0'
UPDATE
show_results tb
RIGHT JOIN
( SELECT
fci_code AS fcicode,
(COUNT(fci_code) - 1) AS classNum
FROM
show_results
WHERE
show_code = #{_parameter}
AND age_group IN ('3','4','5','6','7','8','9','10','13','14','15','16','17','18')
AND del_flag = '0'
GROUP BY
fci_code
HAVING
COUNT(fci_code) - 1 > 0 ) ta
ON
tb.fci_code = ta.fcicode
SET
tb.show_breed_result = tb.show_breed_result + ta.classnum,
tb.show_result = tb.show_result + ta.classnum
WHERE
show_code = #{_parameter}
AND age_group IN ('12','13','14')
AND result_bis = 'BISS'
AND (result_bov is null OR result_bov != 'BOV')
AND remarks_cc NOT LIKE '成绩取消%'
AND del_flag = '0'
UPDATE show_results SET
qus_result = #{qusResult},
qus_code = #{qusCode}
WHERE id = #{id} AND del_flag = '0'