<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.cku.dao.CkuTabGradeMapper" >
  <resultMap id="BaseResultMap" type="com.cku.model.CkuTabGrade" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="catalogue_num" property="catalogueNum" jdbcType="VARCHAR" />
    <result column="CC" property="cc" jdbcType="CHAR" />
    <result column="CC_Remarks" property="ccRemarks" jdbcType="VARCHAR" />
    <result column="CACIB" property="cacib" jdbcType="CHAR" />
    <result column="CACIB_Remarks" property="cacibRemarks" jdbcType="VARCHAR" />
    <result column="BOB" property="bob" jdbcType="CHAR" />
    <result column="BIG" property="big" jdbcType="CHAR" />
    <result column="BOW" property="bow" jdbcType="CHAR" />
    <result column="WB" property="wb" jdbcType="CHAR" />
    <result column="BIS" property="bis" jdbcType="CHAR" />
    <result column="current_credits" property="currentCredits" jdbcType="INTEGER" />
    <result column="dog_owner_cn_name" property="dogOwnerCnName" jdbcType="VARCHAR" />
    <result column="dog_owner_eg_name" property="dogOwnerEgName" jdbcType="VARCHAR" />
    <result column="member_num" property="memberNum" jdbcType="VARCHAR" />
    <result column="fci_code" property="fciCode" jdbcType="VARCHAR" />
    <result column="gender" property="gender" jdbcType="VARCHAR" />
    <result column="age_group" property="ageGroup" jdbcType="INTEGER" />
    <result column="studbook_num" property="studbookNum" jdbcType="VARCHAR" />
    <result column="other_certifi_num" property="otherCertifiNum" jdbcType="VARCHAR" />
    <result column="show_num" property="showNum" jdbcType="VARCHAR" />
    <result column="judge" property="judge" jdbcType="VARCHAR" />
    <result column="show_ring" property="showRing" jdbcType="VARCHAR" />
    <result column="modifi_data" property="modifiData" jdbcType="TIMESTAMP" />
    <result column="del_tag" property="delTag" jdbcType="CHAR" />
    <result column="breed_group" property="breedGroup" jdbcType="VARCHAR" />
    <result column="print_cc" property="printCc" jdbcType="CHAR" />
    <result column="showSystem" property="showSystem" jdbcType="VARCHAR" />
	<result column="show_name" property="showName"/>
	<result column="starting_data" property="startingData"/>
	<result column="ending_data" property="endingData"/>
	<result column="gradeMonth" property="gradeMonth" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="com.cku.model.CkuTabGradeWithBLOBs" extends="BaseResultMap" >
    <result column="Remarks" property="remarks" jdbcType="LONGVARCHAR" />
    <result column="operater" property="operater" jdbcType="LONGVARCHAR" />
  </resultMap>
  <resultMap id="ScoreResultMap" type="com.cku.model.CkuDogScoreModel" >
  	<result column="CC" property="CC"/>
  	<result column="JCC" property="JCC"/>
  	<result column="RCC" property="RCC"/>
  	<result column="BOB" property="BOB"/>
  	<result column="BOW" property="BOW"/>
  	<result column="BIG" property="BIG"/>
  	<result column="BIS" property="BIS"/>
  	<result column="BOS" property="BOS"/>
  	<result column="CACIB" property="CACIB"/>
  </resultMap>
  <!-- 根据cku证书号查询犬只（全犬种）比赛结果 zhuoHeng -->
  <select id="getDogGradeList" resultMap="BaseResultMap" parameterType="java.lang.Long" >
 SELECT
		tab.id id,
		tab.秩序号 catalogue_num,
		tab.CC CC,
		tab.CC_Remarks CC_Remarks,
		tab.CACIB CACIB,
		tab.CACIB_Remarks CACIB_Remarks,
		tab.BOB BOB,
		tab.BIG BIG,
		tab.BOW BOW,
		tab.WB WB,
		tab.BIS BIS,
		tab.本场积分 current_credits,
		tab.犬主中文名 dog_owner_cn_name,
		tab.犬主英文名 dog_owner_eg_name,
		tab.会员号 member_num,
		tab.FCI代码 fci_code,
		tab.性别 gender,
		tab.年龄组别 age_group,
		tab.血统证书号 studbook_num,
		tab.其他证书号 other_certifi_num,
		tab.赛事编号 show_num,
		tab.裁判 judge,
		tab.赛圈 show_ring,
		tab.修改日期 modifi_data,
		tab.del_tag del_tag,
		tab.犬种组 breed_group,
		tab.print_cc print_cc,
		tab.Remarks Remarks,
		tab.operater operater,
		game.赛事名称  show_name,
		game.开始日期  starting_data,
		game.结束日期 ending_data,
		left(CONVERT(varchar(100), game.结束日期, 23),7) gradeMonth
	FROM
		tab_grade tab
	LEFT JOIN tab_games game ON tab.赛事编号 = game.赛事编号
	WHERE
		血统证书号 = #{pedigreeNum}
		and left(CONVERT(varchar(100), 结束日期, 23),7) = #{charTime}
  </select>
  <select id="getDogGradeMonth" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    SELECT
		left(CONVERT(varchar(100), game.结束日期, 23),7) gradeMonth
	FROM
		tab_grade tab
	LEFT JOIN tab_games game ON tab.赛事编号 = game.赛事编号
	WHERE
		血统证书号 = #{pedigreeNum}
	order by tab.id desc 
  </select>
  <select id="getGradeListByShowsNum" resultMap="BaseResultMap" parameterType="java.lang.Long" >
  <if test="showNumsQqz!='' and showNumsQqz!=null">
  SELECT
		'全犬种' showSystem,
		秩序号 catalogue_num,
		CC CC,
		年龄组别 age_group,
		CACIB CACIB,
		本场积分 current_credits,
		BIS,
		裁判 judge,
		tab_games.赛事名称 show_name,
		tab_games.开始日期  starting_data,
		tab_games.结束日期 ending_data
	FROM
		tab_grade left join tab_games on tab_grade.赛事编号 = tab_games.赛事编号
	WHERE
		tab_grade.赛事编号 IN (${showNumsQqz})
	AND 血统证书号 = #{studbookNum}
	</if>
	<if test="showNumsDdz!=''and showNumsDdz!=null">
	<if test="showNumsQqz!=''and showNumsQqz!=null and showNumsDdz!='' and showNumsDdz!=null">
	UNION ALL
	</if>
	SELECT
		'单独展' showSystem,
		serialNumber catalogue_num,
		CC CC,
		ageClass age_group,
		NULL CACIB,
		NULL current_credits,
		NULL BIS,
		judges judge,
		tab_games.赛事名称 show_name,
		tab_games.开始日期  starting_data,
		tab_games.结束日期 ending_data
	FROM
		tab_grade_ddz left join tab_games on tab_grade_ddz.showsId = tab_games.赛事编号
	WHERE
		tab_grade_ddz.showsId IN (${showNumsDdz})
	AND breedId = #{studbookNum}
	</if>
  </select>
  <!-- 查询犬只比赛成绩汇总 zhuoHeng -->
  <select id="getDogScore"  resultMap="ScoreResultMap" parameterType="java.lang.Long" >
  	select
	    count(CASE WHEN tab.CC='CC' THEN 'CC' ELSE null END) CC,
		count(CASE WHEN tab.JCC='JCC' THEN 'JCC' ELSE null END) JCC,
		count(CASE WHEN tab.RCC='RCC' THEN 'RCC' ELSE null END) RCC,
		count(CASE WHEN tab.BOB='BOB' THEN 'BOB' ELSE null END) BOB,
		count(CASE WHEN tab.BOS='BOS' THEN 'BOS' ELSE null END) BOS,
		count(CASE WHEN tab.BOW='BOW' THEN 'BOW' ELSE null END) BOW,
		count(CASE WHEN tab.BIG='BIG' THEN 'BIG' ELSE null END) BIG,
		count(CASE WHEN tab.BIS='BIS' THEN 'BIS' ELSE null END) BIS,
		count(CASE WHEN tab.CACIB='CACIB' THEN 'CACIB' ELSE null END) CACIB
	from (
		select 
			'CC' = max(CASE WHEN CC='CC' THEN 'CC' ELSE null END),
			'JCC' = max(CASE WHEN CC='JCC' THEN 'JCC' ELSE null END),
			'RCC' = max(CASE WHEN CC='RCC' THEN 'RCC' ELSE null END),
			'BOB' = max(CASE WHEN BOB='BOB' THEN 'BOB' ELSE null END),
			'BOS' = max(CASE WHEN BOB='BOS' THEN 'BOS' ELSE null END),
			'BOW' = max(CASE WHEN BOW='BOW' THEN 'BOW' ELSE null END),
			'BIG' = max(CASE WHEN BIG='BIG' THEN 'BIG' ELSE null END),
			'BIS' = max(CASE WHEN BIS='BIS' THEN 'BIS' ELSE null END),
			'CACIB' = max(CASE WHEN CACIB='CACIB' THEN 'CACIB' ELSE null END),
			裁判
		FROM tab_grade  
		 where 血统证书号 = #{studbookNum}
		group by 裁判) tab
  </select>
  <sql id="Base_Column_List" >
    id, catalogue_num, CC, CC_Remarks, CACIB, CACIB_Remarks, BOB, BIG, BOW, WB, BIS, 
    current_credits, dog_owner_cn_name, dog_owner_eg_name, member_num, fci_code, gender, 
    age_group, studbook_num, other_certifi_num, show_num, judge, show_ring, modifi_data, 
    del_tag, breed_group, print_cc
  </sql>
  <sql id="Blob_Column_List" >
    Remarks, operater
  </sql>
  <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from tab_grade
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from tab_grade
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.cku.model.CkuTabGradeWithBLOBs" >
    insert into tab_grade (id, catalogue_num, CC, 
      CC_Remarks, CACIB, CACIB_Remarks, 
      BOB, BIG, BOW, WB, 
      BIS, current_credits, dog_owner_cn_name, 
      dog_owner_eg_name, member_num, fci_code, 
      gender, age_group, studbook_num, 
      other_certifi_num, show_num, judge, 
      show_ring, modifi_data, del_tag, 
      breed_group, print_cc, Remarks, 
      operater)
    values (#{id,jdbcType=INTEGER}, #{catalogueNum,jdbcType=VARCHAR}, #{cc,jdbcType=CHAR}, 
      #{ccRemarks,jdbcType=VARCHAR}, #{cacib,jdbcType=CHAR}, #{cacibRemarks,jdbcType=VARCHAR}, 
      #{bob,jdbcType=CHAR}, #{big,jdbcType=CHAR}, #{bow,jdbcType=CHAR}, #{wb,jdbcType=CHAR}, 
      #{bis,jdbcType=CHAR}, #{currentCredits,jdbcType=INTEGER}, #{dogOwnerCnName,jdbcType=VARCHAR}, 
      #{dogOwnerEgName,jdbcType=VARCHAR}, #{memberNum,jdbcType=VARCHAR}, #{fciCode,jdbcType=VARCHAR}, 
      #{gender,jdbcType=VARCHAR}, #{ageGroup,jdbcType=INTEGER}, #{studbookNum,jdbcType=VARCHAR}, 
      #{otherCertifiNum,jdbcType=VARCHAR}, #{showNum,jdbcType=VARCHAR}, #{judge,jdbcType=VARCHAR}, 
      #{showRing,jdbcType=VARCHAR}, #{modifiData,jdbcType=TIMESTAMP}, #{delTag,jdbcType=CHAR}, 
      #{breedGroup,jdbcType=VARCHAR}, #{printCc,jdbcType=CHAR}, #{remarks,jdbcType=LONGVARCHAR}, 
      #{operater,jdbcType=LONGVARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.cku.model.CkuTabGradeWithBLOBs" >
    insert into tab_grade
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="catalogueNum != null" >
        catalogue_num,
      </if>
      <if test="cc != null" >
        CC,
      </if>
      <if test="ccRemarks != null" >
        CC_Remarks,
      </if>
      <if test="cacib != null" >
        CACIB,
      </if>
      <if test="cacibRemarks != null" >
        CACIB_Remarks,
      </if>
      <if test="bob != null" >
        BOB,
      </if>
      <if test="big != null" >
        BIG,
      </if>
      <if test="bow != null" >
        BOW,
      </if>
      <if test="wb != null" >
        WB,
      </if>
      <if test="bis != null" >
        BIS,
      </if>
      <if test="currentCredits != null" >
        current_credits,
      </if>
      <if test="dogOwnerCnName != null" >
        dog_owner_cn_name,
      </if>
      <if test="dogOwnerEgName != null" >
        dog_owner_eg_name,
      </if>
      <if test="memberNum != null" >
        member_num,
      </if>
      <if test="fciCode != null" >
        fci_code,
      </if>
      <if test="gender != null" >
        gender,
      </if>
      <if test="ageGroup != null" >
        age_group,
      </if>
      <if test="studbookNum != null" >
        studbook_num,
      </if>
      <if test="otherCertifiNum != null" >
        other_certifi_num,
      </if>
      <if test="showNum != null" >
        show_num,
      </if>
      <if test="judge != null" >
        judge,
      </if>
      <if test="showRing != null" >
        show_ring,
      </if>
      <if test="modifiData != null" >
        modifi_data,
      </if>
      <if test="delTag != null" >
        del_tag,
      </if>
      <if test="breedGroup != null" >
        breed_group,
      </if>
      <if test="printCc != null" >
        print_cc,
      </if>
      <if test="remarks != null" >
        Remarks,
      </if>
      <if test="operater != null" >
        operater,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="catalogueNum != null" >
        #{catalogueNum,jdbcType=VARCHAR},
      </if>
      <if test="cc != null" >
        #{cc,jdbcType=CHAR},
      </if>
      <if test="ccRemarks != null" >
        #{ccRemarks,jdbcType=VARCHAR},
      </if>
      <if test="cacib != null" >
        #{cacib,jdbcType=CHAR},
      </if>
      <if test="cacibRemarks != null" >
        #{cacibRemarks,jdbcType=VARCHAR},
      </if>
      <if test="bob != null" >
        #{bob,jdbcType=CHAR},
      </if>
      <if test="big != null" >
        #{big,jdbcType=CHAR},
      </if>
      <if test="bow != null" >
        #{bow,jdbcType=CHAR},
      </if>
      <if test="wb != null" >
        #{wb,jdbcType=CHAR},
      </if>
      <if test="bis != null" >
        #{bis,jdbcType=CHAR},
      </if>
      <if test="currentCredits != null" >
        #{currentCredits,jdbcType=INTEGER},
      </if>
      <if test="dogOwnerCnName != null" >
        #{dogOwnerCnName,jdbcType=VARCHAR},
      </if>
      <if test="dogOwnerEgName != null" >
        #{dogOwnerEgName,jdbcType=VARCHAR},
      </if>
      <if test="memberNum != null" >
        #{memberNum,jdbcType=VARCHAR},
      </if>
      <if test="fciCode != null" >
        #{fciCode,jdbcType=VARCHAR},
      </if>
      <if test="gender != null" >
        #{gender,jdbcType=VARCHAR},
      </if>
      <if test="ageGroup != null" >
        #{ageGroup,jdbcType=INTEGER},
      </if>
      <if test="studbookNum != null" >
        #{studbookNum,jdbcType=VARCHAR},
      </if>
      <if test="otherCertifiNum != null" >
        #{otherCertifiNum,jdbcType=VARCHAR},
      </if>
      <if test="showNum != null" >
        #{showNum,jdbcType=VARCHAR},
      </if>
      <if test="judge != null" >
        #{judge,jdbcType=VARCHAR},
      </if>
      <if test="showRing != null" >
        #{showRing,jdbcType=VARCHAR},
      </if>
      <if test="modifiData != null" >
        #{modifiData,jdbcType=TIMESTAMP},
      </if>
      <if test="delTag != null" >
        #{delTag,jdbcType=CHAR},
      </if>
      <if test="breedGroup != null" >
        #{breedGroup,jdbcType=VARCHAR},
      </if>
      <if test="printCc != null" >
        #{printCc,jdbcType=CHAR},
      </if>
      <if test="remarks != null" >
        #{remarks,jdbcType=LONGVARCHAR},
      </if>
      <if test="operater != null" >
        #{operater,jdbcType=LONGVARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.cku.model.CkuTabGradeWithBLOBs" >
    update tab_grade
    <set >
      <if test="catalogueNum != null" >
        catalogue_num = #{catalogueNum,jdbcType=VARCHAR},
      </if>
      <if test="cc != null" >
        CC = #{cc,jdbcType=CHAR},
      </if>
      <if test="ccRemarks != null" >
        CC_Remarks = #{ccRemarks,jdbcType=VARCHAR},
      </if>
      <if test="cacib != null" >
        CACIB = #{cacib,jdbcType=CHAR},
      </if>
      <if test="cacibRemarks != null" >
        CACIB_Remarks = #{cacibRemarks,jdbcType=VARCHAR},
      </if>
      <if test="bob != null" >
        BOB = #{bob,jdbcType=CHAR},
      </if>
      <if test="big != null" >
        BIG = #{big,jdbcType=CHAR},
      </if>
      <if test="bow != null" >
        BOW = #{bow,jdbcType=CHAR},
      </if>
      <if test="wb != null" >
        WB = #{wb,jdbcType=CHAR},
      </if>
      <if test="bis != null" >
        BIS = #{bis,jdbcType=CHAR},
      </if>
      <if test="currentCredits != null" >
        current_credits = #{currentCredits,jdbcType=INTEGER},
      </if>
      <if test="dogOwnerCnName != null" >
        dog_owner_cn_name = #{dogOwnerCnName,jdbcType=VARCHAR},
      </if>
      <if test="dogOwnerEgName != null" >
        dog_owner_eg_name = #{dogOwnerEgName,jdbcType=VARCHAR},
      </if>
      <if test="memberNum != null" >
        member_num = #{memberNum,jdbcType=VARCHAR},
      </if>
      <if test="fciCode != null" >
        fci_code = #{fciCode,jdbcType=VARCHAR},
      </if>
      <if test="gender != null" >
        gender = #{gender,jdbcType=VARCHAR},
      </if>
      <if test="ageGroup != null" >
        age_group = #{ageGroup,jdbcType=INTEGER},
      </if>
      <if test="studbookNum != null" >
        studbook_num = #{studbookNum,jdbcType=VARCHAR},
      </if>
      <if test="otherCertifiNum != null" >
        other_certifi_num = #{otherCertifiNum,jdbcType=VARCHAR},
      </if>
      <if test="showNum != null" >
        show_num = #{showNum,jdbcType=VARCHAR},
      </if>
      <if test="judge != null" >
        judge = #{judge,jdbcType=VARCHAR},
      </if>
      <if test="showRing != null" >
        show_ring = #{showRing,jdbcType=VARCHAR},
      </if>
      <if test="modifiData != null" >
        modifi_data = #{modifiData,jdbcType=TIMESTAMP},
      </if>
      <if test="delTag != null" >
        del_tag = #{delTag,jdbcType=CHAR},
      </if>
      <if test="breedGroup != null" >
        breed_group = #{breedGroup,jdbcType=VARCHAR},
      </if>
      <if test="printCc != null" >
        print_cc = #{printCc,jdbcType=CHAR},
      </if>
      <if test="remarks != null" >
        Remarks = #{remarks,jdbcType=LONGVARCHAR},
      </if>
      <if test="operater != null" >
        operater = #{operater,jdbcType=LONGVARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.cku.model.CkuTabGradeWithBLOBs" >
    update tab_grade
    set catalogue_num = #{catalogueNum,jdbcType=VARCHAR},
      CC = #{cc,jdbcType=CHAR},
      CC_Remarks = #{ccRemarks,jdbcType=VARCHAR},
      CACIB = #{cacib,jdbcType=CHAR},
      CACIB_Remarks = #{cacibRemarks,jdbcType=VARCHAR},
      BOB = #{bob,jdbcType=CHAR},
      BIG = #{big,jdbcType=CHAR},
      BOW = #{bow,jdbcType=CHAR},
      WB = #{wb,jdbcType=CHAR},
      BIS = #{bis,jdbcType=CHAR},
      current_credits = #{currentCredits,jdbcType=INTEGER},
      dog_owner_cn_name = #{dogOwnerCnName,jdbcType=VARCHAR},
      dog_owner_eg_name = #{dogOwnerEgName,jdbcType=VARCHAR},
      member_num = #{memberNum,jdbcType=VARCHAR},
      fci_code = #{fciCode,jdbcType=VARCHAR},
      gender = #{gender,jdbcType=VARCHAR},
      age_group = #{ageGroup,jdbcType=INTEGER},
      studbook_num = #{studbookNum,jdbcType=VARCHAR},
      other_certifi_num = #{otherCertifiNum,jdbcType=VARCHAR},
      show_num = #{showNum,jdbcType=VARCHAR},
      judge = #{judge,jdbcType=VARCHAR},
      show_ring = #{showRing,jdbcType=VARCHAR},
      modifi_data = #{modifiData,jdbcType=TIMESTAMP},
      del_tag = #{delTag,jdbcType=CHAR},
      breed_group = #{breedGroup,jdbcType=VARCHAR},
      print_cc = #{printCc,jdbcType=CHAR},
      Remarks = #{remarks,jdbcType=LONGVARCHAR},
      operater = #{operater,jdbcType=LONGVARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.cku.model.CkuTabGrade" >
    update tab_grade
    set catalogue_num = #{catalogueNum,jdbcType=VARCHAR},
      CC = #{cc,jdbcType=CHAR},
      CC_Remarks = #{ccRemarks,jdbcType=VARCHAR},
      CACIB = #{cacib,jdbcType=CHAR},
      CACIB_Remarks = #{cacibRemarks,jdbcType=VARCHAR},
      BOB = #{bob,jdbcType=CHAR},
      BIG = #{big,jdbcType=CHAR},
      BOW = #{bow,jdbcType=CHAR},
      WB = #{wb,jdbcType=CHAR},
      BIS = #{bis,jdbcType=CHAR},
      current_credits = #{currentCredits,jdbcType=INTEGER},
      dog_owner_cn_name = #{dogOwnerCnName,jdbcType=VARCHAR},
      dog_owner_eg_name = #{dogOwnerEgName,jdbcType=VARCHAR},
      member_num = #{memberNum,jdbcType=VARCHAR},
      fci_code = #{fciCode,jdbcType=VARCHAR},
      gender = #{gender,jdbcType=VARCHAR},
      age_group = #{ageGroup,jdbcType=INTEGER},
      studbook_num = #{studbookNum,jdbcType=VARCHAR},
      other_certifi_num = #{otherCertifiNum,jdbcType=VARCHAR},
      show_num = #{showNum,jdbcType=VARCHAR},
      judge = #{judge,jdbcType=VARCHAR},
      show_ring = #{showRing,jdbcType=VARCHAR},
      modifi_data = #{modifiData,jdbcType=TIMESTAMP},
      del_tag = #{delTag,jdbcType=CHAR},
      breed_group = #{breedGroup,jdbcType=VARCHAR},
      print_cc = #{printCc,jdbcType=CHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>