a.id AS "id", a.dog_dna_parents_compare_id AS "dogDnaParentsCompareId", a.fdog_pedigree_certified AS "fdogPedigreeCertified", a.mdog_pedigree_certified AS "mdogPedigreeCertified", a.fdog_member_code AS "fdogMemberCode", a.fdog_member_name AS "fdogMemberName", a.fdog_review_state AS "fdogReviewState", a.fdog_review_time AS "fdogReviewTime", a.fdog_review_remarks AS "fdogReviewRemarks", a.fdog_review_user_id AS "fdogReviewUserId", a.mdog_member_code AS "mdogMemberCode", a.mdog_member_name AS "mdogMemberName", a.mdog_review_state AS "mdogReviewState", a.mdog_review_time AS "mdogReviewTime", a.mdog_review_remarks AS "mdogReviewRemarks", a.mdog_review_user_id AS "mdogReviewUserId", 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" SELECT FROM dog_dna_parents_compare_audit a WHERE a.id = #{id} SELECT FROM dog_dna_parents_compare_audit a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC SELECT FROM dog_dna_parents_compare_audit a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO dog_dna_parents_compare_audit( id, dog_dna_parents_compare_id, fdog_pedigree_certified, mdog_pedigree_certified, fdog_member_code, fdog_member_name, fdog_review_state, fdog_review_time, fdog_review_remarks, fdog_review_user_id, mdog_member_code, mdog_member_name, mdog_review_state, mdog_review_time, mdog_review_remarks, mdog_review_user_id, create_by, create_date, update_by, update_date, remarks, del_flag ) VALUES ( #{id}, #{dogDnaParentsCompareId}, #{fdogPedigreeCertified}, #{mdogPedigreeCertified}, #{fdogMemberCode}, #{fdogMemberName}, #{fdogReviewState}, #{fdogReviewTime}, #{fdogReviewRemarks}, #{fdogReviewUserId}, #{mdogMemberCode}, #{mdogMemberName}, #{mdogReviewState}, #{mdogReviewTime}, #{mdogReviewRemarks}, #{mdogReviewUserId}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag} ) UPDATE dog_dna_parents_compare_audit SET dog_dna_parents_compare_id = #{dogDnaParentsCompareId}, fdog_pedigree_certified = #{fdogPedigreeCertified}, mdog_pedigree_certified = #{mdogPedigreeCertified}, fdog_member_code = #{fdogMemberCode}, fdog_member_name = #{fdogMemberName}, fdog_review_state = #{fdogReviewState}, fdog_review_time = #{fdogReviewTime}, fdog_review_remarks = #{fdogReviewRemarks}, fdog_review_user_id = #{fdogReviewUserId}, mdog_member_code = #{mdogMemberCode}, mdog_member_name = #{mdogMemberName}, mdog_review_state = #{mdogReviewState}, mdog_review_time = #{mdogReviewTime}, mdog_review_remarks = #{mdogReviewRemarks}, mdog_review_user_id = #{mdogReviewUserId}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE dog_dna_parents_compare_audit SET del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} SELECT ddpc.payment_time, ddpc.payment_member_code, sm.name as 'paymentMemberName', ifnull(ddpc.birth_code, d.birth_certificate_level_code) as 'birthCode', d.pedigree_certified_code as 'pedigreeCertified', ifnull(d.dog_owner,dbc.temp_user_name) as 'dogOwner', ifnull(d.birthdate,dbc.dog_birthday) as 'birthdate', ifnull(a.fdog_member_name,fd.dog_owner) as 'fdogMemberName', ifnull(a.mdog_member_name,md.dog_owner ) as 'mdogMemberName', CASE WHEN ifnull(a.fdog_member_code,fd.member_code ) = #{memberCode} AND ( a.fdog_review_state IS NULL OR a.fdog_review_state != '1' ) THEN true WHEN ifnull(a.mdog_member_code,md.member_code ) = #{memberCode} AND ( a.mdog_review_state IS NULL OR a.mdog_review_state != '1' ) THEN true ELSE false END AS is_review , IF ( ifnull(a.fdog_member_code,fd.member_code ) = #{memberCode} AND ( a.fdog_review_state IS NULL OR a.fdog_review_state != '1') ,true,false) AS is_fdog_review, IF ( ifnull(a.mdog_member_code,md.member_code ) = #{memberCode} AND ( a.mdog_review_state IS NULL OR a.mdog_review_state != '1' ) ,true,false) AS is_mdog_review, a.* FROM dog_dna_parents_compare_audit a LEFT JOIN dog_dna_parents_compare ddpc ON a.dog_dna_parents_compare_id =ddpc.id LEFT JOIN sys_member sm ON ddpc.payment_member_code =sm.member_code AND sm.del_flag='0' LEFT JOIN payment_order_detail pod ON pod.order_code=ddpc.running_number AND pod.business_ids =ddpc.id LEFT JOIN dog d ON ddpc.birth_code =d.birth_certificate_level_code LEFT JOIN dog_birth_certificate dbc ON ddpc.birth_code =dbc.birth_cer_reg_code LEFT JOIN dog fd ON a.fdog_pedigree_certified =fd.pedigree_certified_code LEFT JOIN dog md ON a.mdog_pedigree_certified =md.pedigree_certified_code WHERE a.del_flag='0' AND ddpc.del_flag='0' AND ddpc.is_canceled='0' AND ddpc.payment_state='2' AND ddpc.apply_source!='0' AND ( pod.business_refund_state IS NULL OR pod.business_refund_state ='2' ) AND ( ifnull(a.fdog_member_code,fd.member_code ) = #{memberCode} OR ifnull(a.mdog_member_code,md.member_code ) = #{memberCode}) ORDER BY is_review DESC , ddpc.payment_time DESC SELECT ifnull(a.fdog_member_code,fd.member_code ) as 'fdogMemberCode', ifnull(a.mdog_member_code,md.member_code ) as 'mdogMemberCode', ddpc.payment_time, ddpc.payment_member_code, sm.name as 'paymentMemberName', ifnull(ddpc.birth_code, d.birth_certificate_level_code) as 'birthCode', d.pedigree_certified_code as 'pedigreeCertified', ifnull(d.dog_owner,dbc.temp_user_name) as 'dogOwner', ifnull(d.birthdate,dbc.dog_birthday) as 'birthdate', ifnull(a.fdog_member_name,fd.dog_owner) as 'fdogMemberName', ifnull(a.mdog_member_name,md.dog_owner ) as 'mdogMemberName', a.* FROM dog_dna_parents_compare_audit a LEFT JOIN dog_dna_parents_compare ddpc ON a.dog_dna_parents_compare_id =ddpc.id LEFT JOIN sys_member sm ON ddpc.payment_member_code =sm.member_code AND sm.del_flag='0' LEFT JOIN payment_order_detail pod ON pod.order_code=ddpc.running_number AND pod.business_ids =ddpc.id LEFT JOIN dog_birth_certificate dbc ON ddpc.birth_code =dbc.birth_cer_reg_code LEFT JOIN dog d ON ddpc.birth_code =d.birth_certificate_level_code LEFT JOIN dog fd ON a.fdog_pedigree_certified = fd.pedigree_certified_code LEFT JOIN dog md ON a.mdog_pedigree_certified = md.pedigree_certified_code WHERE a.del_flag='0' AND a.id=#{id} AND ddpc.del_flag='0' AND ddpc.is_canceled='0' AND ddpc.payment_state='2' AND ddpc.apply_source!='0' AND ( pod.business_refund_state IS NULL OR pod.business_refund_state ='2' ) UPDATE dog_dna_parents_compare_audit SET fdog_member_code = #{fdogMemberCode}, fdog_member_name = #{fdogMemberName}, fdog_review_state = #{fdogReviewState}, fdog_review_time = #{fdogReviewTime}, fdog_review_remarks = #{fdogReviewRemarks}, fdog_review_user_id = #{fdogReviewUserId}, mdog_member_code = #{mdogMemberCode}, mdog_member_name = #{mdogMemberName}, mdog_review_state = #{mdogReviewState}, mdog_review_time = #{mdogReviewTime}, mdog_review_remarks = #{mdogReviewRemarks}, mdog_review_user_id = #{mdogReviewUserId}, update_by = #{updateBy.id}, update_date = #{updateDate} WHERE id = #{id} SELECT ifnull(a.fdog_member_code,fd.member_code ) as 'fdogMemberCode', ifnull(a.mdog_member_code,md.member_code ) as 'mdogMemberCode', ddpc.payment_time, ddpc.payment_member_code, ifnull(a.fdog_member_name,fd.dog_owner) as 'fdogMemberName', ifnull(a.mdog_member_name,md.dog_owner ) as 'mdogMemberName', ddpc.birth_code, a.* FROM dog_dna_parents_compare_audit a LEFT JOIN dog_dna_parents_compare ddpc ON a.dog_dna_parents_compare_id =ddpc.id LEFT JOIN dog fd ON a.fdog_pedigree_certified =fd.pedigree_certified_code LEFT JOIN dog md ON a.mdog_pedigree_certified =md.pedigree_certified_code WHERE a.del_flag='0' AND ddpc.id=#{dogDnaParentsCompareId} AND ddpc.del_flag='0' AND ddpc.running_number in #{item} SELECT ddpc.birth_code, ddpc.pedigree_certified, ddpc.running_number, a.* FROM dog_dna_parents_compare_audit a LEFT JOIN dog_dna_parents_compare ddpc ON a.dog_dna_parents_compare_id =ddpc.id WHERE a.del_flag='0' AND ddpc.del_flag='0' AND ddpc.running_number = #{orderCode} limit 1