<?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.zhyc.module.produce.breed.mapper.RawSpermRecordMapper">

    <resultMap type="com.zhyc.module.produce.breed.domain.RawSpermRecord" id="RawSpermRecordResult">
        <result property="id"    column="id"    />
        <result property="sheepId"    column="sheep_id"    />
        <result property="manageTags"    column="manage_tags"    />
        <result property="electronicTags"    column="electronic_tags"    />
        <result property="monthAge"    column="month_age"    />
        <result property="pickDate"    column="pick_date"    />
        <result property="amount"    column="amount"    />
        <result property="density"    column="density"    />
        <result property="vitallity"    column="vitallity"    />
        <result property="controlled"    column="controlled"    />
        <result property="sexualStatus"    column="sexual_status"    />
        <result property="info"    column="info"    />
        <result property="technician"    column="technician"    />
        <result property="comment"    column="comment"    />
        <result property="scrotumCircumference"    column="scrotum_circumference"    />
        <result property="semenQuality"    column="semen_quality"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
    </resultMap>

    <sql id="selectRawSpermRecordVo">
        select id, sheep_id, manage_tags, electronic_tags, month_age, pick_date, amount,
               density, vitallity, controlled, sexual_status, info, technician, comment,
               scrotum_circumference, semen_quality, create_by, create_time
        from raw_sperm_record
    </sql>

    <select id="selectRawSpermRecordList" parameterType="com.zhyc.module.produce.breed.domain.RawSpermRecord" resultMap="RawSpermRecordResult">
        <include refid="selectRawSpermRecordVo"/>
        <where>
            <if test="sheepId != null "> and sheep_id = #{sheepId}</if>
            <if test="manageTags != null  and manageTags != ''"> and manage_tags like concat('%', #{manageTags}, '%')</if>
            <if test="electronicTags != null  and electronicTags != ''"> and electronic_tags like concat('%', #{electronicTags}, '%')</if>
            <if test="monthAge != null "> and month_age = #{monthAge}</if>
            <if test="pickDate != null "> and DATE(pick_date) = DATE(#{pickDate})</if>
            <if test="amount != null "> and amount = #{amount}</if>
            <if test="density != null  and density != ''"> and density like concat('%', #{density}, '%')</if>
            <if test="vitallity != null  and vitallity != ''"> and vitallity like concat('%', #{vitallity}, '%')</if>
            <if test="controlled != null "> and controlled = #{controlled}</if>
            <if test="sexualStatus != null  and sexualStatus != ''"> and sexual_status like concat('%', #{sexualStatus}, '%')</if>
            <if test="info != null  and info != ''"> and info like concat('%', #{info}, '%')</if>
            <if test="technician != null  and technician != ''"> and technician like concat('%', #{technician}, '%')</if>
            <if test="comment != null  and comment != ''"> and comment like concat('%', #{comment}, '%')</if>
            <if test="scrotumCircumference != null "> and scrotum_circumference = #{scrotumCircumference}</if>
            <if test="semenQuality != null  and semenQuality != ''"> and semen_quality = #{semenQuality}</if>
        </where>
        order by pick_date desc, id desc
    </select>

    <select id="selectRawSpermRecordById" parameterType="Long" resultMap="RawSpermRecordResult">
        <include refid="selectRawSpermRecordVo"/>
        where id = #{id}
    </select>

    <insert id="insertRawSpermRecord" parameterType="com.zhyc.module.produce.breed.domain.RawSpermRecord" useGeneratedKeys="true" keyProperty="id">
        insert into raw_sperm_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">sheep_id,</if>
            <if test="manageTags != null and manageTags != ''">manage_tags,</if>
            <if test="electronicTags != null and electronicTags != ''">electronic_tags,</if>
            <if test="monthAge != null">month_age,</if>
            <if test="pickDate != null">pick_date,</if>
            <if test="amount != null">amount,</if>
            <if test="density != null and density != ''">density,</if>
            <if test="vitallity != null and vitallity != ''">vitallity,</if>
            <if test="controlled != null">controlled,</if>
            <if test="sexualStatus != null and sexualStatus != ''">sexual_status,</if>
            <if test="info != null and info != ''">info,</if>
            <if test="technician != null and technician != ''">technician,</if>
            <if test="comment != null and comment != ''">comment,</if>
            <if test="scrotumCircumference != null">scrotum_circumference,</if>
            <if test="semenQuality != null and semenQuality != ''">semen_quality,</if>
            <if test="createBy != null and createBy != ''">create_by,</if>
            <if test="createTime != null">create_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">#{sheepId},</if>
            <if test="manageTags != null and manageTags != ''">#{manageTags},</if>
            <if test="electronicTags != null and electronicTags != ''">#{electronicTags},</if>
            <if test="monthAge != null">#{monthAge},</if>
            <if test="pickDate != null">#{pickDate},</if>
            <if test="amount != null">#{amount},</if>
            <if test="density != null and density != ''">#{density},</if>
            <if test="vitallity != null and vitallity != ''">#{vitallity},</if>
            <if test="controlled != null">#{controlled},</if>
            <if test="sexualStatus != null and sexualStatus != ''">#{sexualStatus},</if>
            <if test="info != null and info != ''">#{info},</if>
            <if test="technician != null and technician != ''">#{technician},</if>
            <if test="comment != null and comment != ''">#{comment},</if>
            <if test="scrotumCircumference != null">#{scrotumCircumference},</if>
            <if test="semenQuality != null and semenQuality != ''">#{semenQuality},</if>
            <if test="createBy != null and createBy != ''">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
        </trim>
    </insert>

    <update id="updateRawSpermRecord" parameterType="com.zhyc.module.produce.breed.domain.RawSpermRecord">
        update raw_sperm_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="sheepId != null">sheep_id = #{sheepId},</if>
            <if test="manageTags != null">manage_tags = #{manageTags},</if>
            <if test="electronicTags != null">electronic_tags = #{electronicTags},</if>
            <if test="monthAge != null">month_age = #{monthAge},</if>
            <if test="pickDate != null">pick_date = #{pickDate},</if>
            <if test="amount != null">amount = #{amount},</if>
            <if test="density != null">density = #{density},</if>
            <if test="vitallity != null">vitallity = #{vitallity},</if>
            <if test="controlled != null">controlled = #{controlled},</if>
            <if test="sexualStatus != null">sexual_status = #{sexualStatus},</if>
            <if test="info != null">info = #{info},</if>
            <if test="technician != null">technician = #{technician},</if>
            <if test="comment != null">comment = #{comment},</if>
            <if test="scrotumCircumference != null">scrotum_circumference = #{scrotumCircumference},</if>
            <if test="semenQuality != null">semen_quality = #{semenQuality},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteRawSpermRecordById" parameterType="Long">
        delete from raw_sperm_record where id = #{id}
    </delete>

    <delete id="deleteRawSpermRecordByIds" parameterType="String">
        delete from raw_sperm_record where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 根据耳号查询羊只ID -->
    <select id="selectSheepIdByManageTags" parameterType="String" resultType="Integer">
        select id from sheep_file where bs_manage_tags = #{manageTags} and is_delete = 0
    </select>

    <!-- 根据耳号查询羊只完整信息（包含ID和性别） -->
    <select id="selectSheepInfoByManageTags" parameterType="String" resultType="java.util.Map">
        select id, gender, bs_manage_tags as manageTags, electronic_tags as electronicTags,
               name, variety, month_age as monthAge
        from sheep_file
        where bs_manage_tags = #{manageTags} and is_delete = 0
            limit 1
    </select>

    <select id="selectLatestByRamId" resultType="RawSpermRecord">
        SELECT * FROM raw_sperm_record
        WHERE sheep_id = #{sheepId}
        ORDER BY pick_date DESC
            LIMIT 1
    </select>
</mapper>