a.id AS "id", a.post_date AS "postDate", a.chip_code AS "chipCode", a.address AS "address", a.org_id AS "orgId", a.create_by AS "createBy.id", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.del_flag AS "delFlag", a.blood_card AS "bloodCard", a.draw_blood AS "drawBlood", a.pedigree_certified AS "pedigreeCertified", a.pedigree_certified_mc AS "pedigreeCertifiedMc", a.pedigree_certified_dc AS "pedigreeCertifiedDc", a.appraisal_certified AS "appraisalCertified", a.dna_pedigree_certified AS "dnaPedigreeCertified", a.dna_breed_certified AS "dnaBreedCertified", a.create_date AS "createDate", a.invalid AS "invalid", o.name AS "org.name", b.birth_cer_reg_code AS "birthCerRegCode" LEFT JOIN sys_org o on o.id = a.org_id LEFT JOIN dog_birth_certificate b on b.identification_flag = a.chip_code SELECT FROM sys_org_chip a WHERE a.id = #{id} SELECT a.org_id AS "orgId", o.name AS "org.name", a.post_date AS "postDate", a.address AS "address", MAX(IFNULL(a.blood_card,0)) AS "bloodCard", MAX(IFNULL(a.draw_blood,0)) AS "drawBlood", MAX(IFNULL(a.pedigree_certified,0)) AS "pedigreeCertified", MAX(IFNULL(a.pedigree_certified_mc,0)) AS "pedigreeCertifiedMc", MAX(IFNULL(a.pedigree_certified_dc,0)) AS "pedigreeCertifiedDc", MAX(IFNULL(a.appraisal_certified,0)) AS "appraisalCertified", MAX(IFNULL(a.dna_pedigree_certified,0)) AS "dnaPedigreeCertified", MAX(IFNULL(a.dna_breed_certified,0)) AS "dnaBreedCertified", IFNULL(c.chip_count,0) AS "count" FROM sys_org_chip a LEFT JOIN sys_org o on a.org_id = o.id LEFT JOIN ( SELECT id, count(1) AS chip_count FROM sys_org_chip WHERE del_flag='0' AND chip_code IS NOT NULL and org_id = #{orgId} GROUP BY post_date ) c ON c.id = a.id a.del_flag='0' and a.org_id = #{orgId} GROUP BY a.post_date order by a.post_date desc SELECT FROM sys_org_chip a a.del_flag = #{DEL_FLAG_NORMAL} AND a.post_date = #{postDate} AND a.chip_code = #{chipCode} AND a.address = #{address} AND a.org_id = #{orgId} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC SELECT FROM sys_org_chip a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO sys_org_chip( id, org_id, post_date, chip_code, address, invalid, blood_card, draw_blood, pedigree_certified, pedigree_certified_mc, pedigree_certified_dc, appraisal_certified, dna_pedigree_certified, dna_breed_certified, create_by, create_date, update_by, update_date, remarks, del_flag ) VALUES ( #{id}, #{orgId}, #{postDate}, #{chipCode}, #{address}, #{invalid}, #{bloodCard}, #{drawBlood}, #{pedigreeCertified}, #{pedigreeCertifiedMc}, #{pedigreeCertifiedDc}, #{appraisalCertified}, #{dnaPedigreeCertified}, #{dnaBreedCertified}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag} ) UPDATE sys_org_chip SET org_id = #{orgId}, post_date = #{postDate}, chip_code = #{chipCode}, address = #{address}, invalid = #{invalid}, blood_card = #{bloodCard}, draw_blood = #{drawBlood}, pedigree_certified = #{pedigreeCertified}, pedigree_certified_mc = #{pedigreeCertifiedMc}, pedigree_certified_dc = #{pedigreeCertifiedDc}, appraisal_certified = #{appraisalCertified}, dna_pedigree_certified = #{dnaPedigreeCertified}, dna_breed_certified = #{dnaBreedCertified}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE sys_org_chip SET remarks = concat(IFNULL(remarks,''),#{remarks}), del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} UPDATE sys_org_chip SET remarks = concat(IFNULL(remarks,''),#{remarks}), del_flag = #{DEL_FLAG_DELETE} WHERE org_id = #{orgId} and post_date = #{postDate} SELECT id, org_id, post_date, chip_code, address, invalid, create_by, create_date, update_by, update_date, remarks, del_flag FROM sys_org_chip WHERE org_id=#{orgId} and chip_code=#{chipCode} and del_flag='0' SELECT id, org_id, post_date, chip_code, address, invalid, create_by, create_date, update_by, update_date, remarks, del_flag FROM sys_org_chip WHERE chip_code=#{chipCode} and del_flag='0' SELECT a.count_over, b.count_off FROM ( SELECT count(id) AS count_over FROM `sys_org_chip` WHERE org_id = #{orgId} AND del_flag = '0' AND invalid = '0' AND chip_code is not null ) AS a, ( SELECT count(o.id) AS count_off FROM sys_org_chip o LEFT JOIN dog_birth_certificate d ON d.identification_flag = o.chip_code WHERE d.del_flag = '0' AND o.del_flag = '0' AND o.invalid = '0' AND org_id = #{orgId} ) AS b SELECT sum(a.blood_card_sum) as blood_card_sum, sum(a.draw_blood_sum) as draw_blood_sum, IFNULL(b.chip_sum_one,'0') as dna_blood, IFNULL(c.chip_sum_two,'0') as dna_archive FROM ( SELECT blood_card AS blood_card_sum, draw_blood as draw_blood_sum FROM `sys_org_chip` WHERE org_id = #{orgId} AND del_flag = '0' AND invalid = '0' GROUP BY post_date ) AS a, ( SELECT count(id) AS chip_sum_one FROM dog_chip where del_flag='0' AND payment_state='2' AND dna_save_way='0' AND hospital_code=#{orgId} ) AS b, ( SELECT count(id) AS chip_sum_two FROM dog_chip where del_flag='0' AND payment_state='2' AND dna_save_way='1' AND hospital_code=#{orgId} ) AS c SELECT * FROM sys_org_chip WHERE del_flag='0' and chip_code IN #{chipCode}